Batch tag fetching to avoid N+1 queries in useVideos and exportService

[kmch4n]

Summary

useVideos and exportService.exportAllToJSON both walk the full video list and call getTagsForVideo once per row, producing 2N SQLite round-trips for N videos. With a few hundred imports this becomes the dominant cost of every focus refresh on home / search / calendar, and of every export.

A single batched query joining video_tags and tags would collapse this to O(1) round-trips.

Problem

// src/hooks/useVideos.ts:62-72
const videosWithTags = await Promise.all(
    rawVideos.map(async (video) => {
        return {
            ...video,
            tags: await getTagsForVideo(video.id),
            techniques: parseTechniques(video.techniques as string | null),
        };
    })
);

getTagsForVideo itself is two queries (select video_tags then select tags WHERE id IN ...):

// src/database/repositories/tagRepository.ts:55-65
const rows = await db.select({ tagId: videoTags.tagId }).from(videoTags).where(eq(videoTags.videoId, videoId));
if (rows.length === 0) return [];
const tagIds = rows.map((r) => r.tagId);
const result = await db.select().from(tags).where(inArray(tags.id, tagIds));

So one focus refresh of 300 videos issues ~600 SQLite queries. The same pattern exists in:

• src/services/exportService.ts:32-38 — full backup
• src/hooks/useDashboard.ts:60-68 — fortunately only recentVideos.length (≤5), so not a problem there

Relevant files

• src/hooks/useVideos.ts:62-72
• src/services/exportService.ts:32-47
• src/database/repositories/tagRepository.ts:55-65 — current per-video helper

Proposed fix

Add a batched repository helper:

// tagRepository.ts
export async function getTagsForVideos(videoIds: string[]): Promise<Map<string, Tag[]>> {
    if (videoIds.length === 0) return new Map();
    const rows = await db
        .select({
            videoId: videoTags.videoId,
            id: tags.id,
            name: tags.name,
            type: tags.type,
        })
        .from(videoTags)
        .innerJoin(tags, eq(videoTags.tagId, tags.id))
        .where(inArray(videoTags.videoId, videoIds))
        .orderBy(asc(tags.id)); // also addresses #59

    const map = new Map<string, Tag[]>();
    for (const row of rows) {
        const list = map.get(row.videoId) ?? [];
        list.push(asTag(row));
        map.set(row.videoId, list);
    }
    return map;
}

Call it once in useVideos and exportService and look up tags from the map. Drops to two SQLite round-trips total.

Acceptance criteria

• Home / search / calendar focus refresh issues a small constant number of queries regardless of library size.
• Export of a large library (~1000 videos) completes noticeably faster.
• Per-video getTagsForVideo keeps working for useVideoDetail and other singleton consumers.
• No regression in tag display.

Related

• videoListEquality compares tags by index, causing spurious re-renders when tag order shifts #59 — tag ordering. The batched query should sort by tags.id to fix both at once.