Finding
File: src/lib/haven-catalog.ts, lines 147–188 (loadLiveCollection), 64–85 (buildCatalogSummary), 88–122 (buildWorkDetail)
Reviewer persona: Performance Critic
Severity: P2
loadLiveCollection fetches all early-church books, then loops over each book and issues a separate query per book to fetch its episodes — a classic N+1 query pattern. With the current 8 seed works this is 9 queries, but it grows linearly with collection size.
Additionally:
-
buildWorkDetail over-fetches the entire collection for a single-work request. It calls loadLiveCollection() (which loads ALL collection books + ALL their episodes) and then .get(slug) to pick one work. The /api/haven/catalog/[slug] endpoint should query only the requested book and its episodes — not the entire collection.
-
buildCatalogSummary fetches the transcript column for every ready episode but never uses it. The summary response contains no episodes and no transcript data — it only needs counts and durations. Full transcript text (potentially thousands of characters per episode) is pulled from the DB into memory and discarded.
Potential Impact
- Unnecessary DB load and memory allocation on every catalog request, scaling with collection size and episode count.
- The detail endpoint is O(N×M) in DB rows fetched when it should be O(1×M) for one work.
- Transcript over-fetch wastes bandwidth between Postgres and the Next.js server on every summary request.
Linked PR
#9
Root Cause Analysis
DevOps perspective
The CDN cache (s-maxage=300) absorbs most repeat traffic, so the DB impact is bounded to cache-miss requests (~1 per 5 min per edge POP). This is why the issue is P2 not P1 — production won't degrade with 8 works. But there's no query-count or slow-query observability on these endpoints, so regression as the collection grows would be silent.
Engineering perspective
loadLiveCollection was designed as a single shared loader for both the summary and detail paths, which is elegant but means the detail path inherits the full-collection load. The N+1 exists because Drizzle's relational query API (with: { episodes: ... }) wasn't used — instead a manual loop was written. The transcript over-fetch exists because the same LiveWork interface and query shape serves both paths, and the summary path doesn't need transcripts but gets them anyway.
Architecture perspective
The root cause is a single "load everything" function serving two callers with different data needs. The correct pattern is:
- A
loadLiveWork(slug) for the detail endpoint (one book + its episodes, including transcript).
- A
loadLiveSummaries() for the collection endpoint (all books + episode counts/durations only, no transcript text).
- Both can use a single Drizzle relational query with
db.query.books.findMany({ with: { episodes: ... } }) to eliminate the N+1, selecting only needed columns.
Recommended Resolution
- Replace the manual loop in
loadLiveCollection with a single Drizzle relational query (db.query.books.findMany({ where: eq(books.collection, ...), with: { episodes: { where: isNotNull(episodes.audioUrlFull), orderBy: asc(episodes.episodeNumber) } } })) to eliminate the N+1.
- Split into two functions:
loadLiveSummaries() (select only slug, title, author, description, coverImageUrl from books + count/sum from episodes — no transcript column) and loadLiveWork(slug) (query one book by slug + its episodes with full columns including transcript).
- For the summary, use a
COUNT(*) + SUM(duration_seconds) aggregate query instead of loading all episode rows into memory just to count them.
Finding
File:
src/lib/haven-catalog.ts, lines 147–188 (loadLiveCollection), 64–85 (buildCatalogSummary), 88–122 (buildWorkDetail)Reviewer persona: Performance Critic
Severity: P2
loadLiveCollectionfetches all early-church books, then loops over each book and issues a separate query per book to fetch its episodes — a classic N+1 query pattern. With the current 8 seed works this is 9 queries, but it grows linearly with collection size.Additionally:
buildWorkDetailover-fetches the entire collection for a single-work request. It callsloadLiveCollection()(which loads ALL collection books + ALL their episodes) and then.get(slug)to pick one work. The/api/haven/catalog/[slug]endpoint should query only the requested book and its episodes — not the entire collection.buildCatalogSummaryfetches thetranscriptcolumn for every ready episode but never uses it. The summary response contains no episodes and no transcript data — it only needs counts and durations. Full transcript text (potentially thousands of characters per episode) is pulled from the DB into memory and discarded.Potential Impact
Linked PR
#9
Root Cause Analysis
DevOps perspective
The CDN cache (
s-maxage=300) absorbs most repeat traffic, so the DB impact is bounded to cache-miss requests (~1 per 5 min per edge POP). This is why the issue is P2 not P1 — production won't degrade with 8 works. But there's no query-count or slow-query observability on these endpoints, so regression as the collection grows would be silent.Engineering perspective
loadLiveCollectionwas designed as a single shared loader for both the summary and detail paths, which is elegant but means the detail path inherits the full-collection load. The N+1 exists because Drizzle's relational query API (with: { episodes: ... }) wasn't used — instead a manual loop was written. The transcript over-fetch exists because the sameLiveWorkinterface and query shape serves both paths, and the summary path doesn't need transcripts but gets them anyway.Architecture perspective
The root cause is a single "load everything" function serving two callers with different data needs. The correct pattern is:
loadLiveWork(slug)for the detail endpoint (one book + its episodes, including transcript).loadLiveSummaries()for the collection endpoint (all books + episode counts/durations only, no transcript text).db.query.books.findMany({ with: { episodes: ... } })to eliminate the N+1, selecting only needed columns.Recommended Resolution
loadLiveCollectionwith a single Drizzle relational query (db.query.books.findMany({ where: eq(books.collection, ...), with: { episodes: { where: isNotNull(episodes.audioUrlFull), orderBy: asc(episodes.episodeNumber) } } })) to eliminate the N+1.loadLiveSummaries()(select onlyslug, title, author, description, coverImageUrlfrom books + count/sum from episodes — notranscriptcolumn) andloadLiveWork(slug)(query one book by slug + its episodes with full columns includingtranscript).COUNT(*)+SUM(duration_seconds)aggregate query instead of loading all episode rows into memory just to count them.