Viewer DB read model redesign for large archives

[YusukeHirao]

Priority (absolute — consult this whenever a design decision is unclear; there should be no ambiguity once you check this)

1. Viewer compatibility — never break the existing viewer application (frontend UI in packages/@nitpicker/viewer/web/ and its API contract). Before shipping any endpoint/response-shape change, verify actual frontend consumers exist (check web/api/ and web/routes/, not just the backend src/) and update them in the same change.
2. Read performance — reads must always be fast, with no exceptions. All read-model construction happens at crawl completion or via the explicit nitpicker viewer-build command — never inline during a read-only open. Any on-open/opportunistic building that can block or slow down a read is a violation of this priority and must be removed wherever it exists (see Remove on-open opportunistic read model build (corrects #112) #177).
3. Archive DB storage — minimize the additional storage the read model adds to an archive.

These three are ranked in this order and are non-negotiable. No half-measures: pursue maximum read performance without compromise. Research prior art and existing techniques before implementing. Benchmark with real measurements (including real large archives, not just synthetic data) rather than assumptions. Implementation effort or time is never an acceptable reason to settle for a suboptimal design.

AS IS

The viewer can be slow on large archives because several endpoints still depend on request-time aggregation, broad scans, wide joins, redirect resolution, graph work, or JSON parsing. Existing archive data must not be treated as disposable, and customer/site/local archive details must not be recorded in public issues, comments, commits, benchmark logs, or test fixtures.

TO BE

Build a viewer read model strategy for large archives with hundreds of thousands of content records. Start with cache/sidecar read models for PDCA and benchmarking, then promote stable read models to persistent tables built after crawl completion.

The implementation must follow these design documents:

• docs/viewer-db-redesign-plan.md
• docs/viewer-sql-query-plan.md
• docs/viewer-implementation-plan.md

Branch Policy

• Integration branch: feature/impl-new-db.
• Sub-issue implementation branches should branch from feature/impl-new-db, not from dev.
• Sub-issue PRs should target feature/impl-new-db unless the maintainer explicitly decides otherwise.
• feature/impl-new-db is the staging branch for the Viewer DB read model redesign for large archives #103 project and will be merged to dev only after the read model plan is coherent and validated.
• Do not open endpoint migration PRs directly against dev while this project is still in PDCA.

Must

• Keep source archive data recoverable and do not discard observed facts for size reduction.
• Prefer SQL/read-model solutions before Node.js, Hono, React, JSON-file, or process-cache optimizations.
• Keep viewer GET paths away from huge source-table scans.
• Use limit-before-join patterns for URL/text/header joins.
• Use keyset cursor pagination for virtual scrolling.
• Use page anchors only for explicit page-number jumps.
• Precompute totals, counts, graph facts, duplicate groups, summaries, and diagnostics during read model build.
• Record benchmark methodology without customer/site names, real local file paths, or concrete archive identifiers.
• Keep stub-mode safety: viewer must not mutate a live or interrupted crawl tmpDir.
• Verify frontend (packages/@nitpicker/viewer/web/) consumers before assuming an endpoint is backend-only.

Don't

• Do not write customer names, site names, real archive file names, or local filesystem paths.
• Do not introduce large OFFSET based pagination for virtual scrolling.
• Do not add request-time GROUP BY, connected-component computation, duplicate detection, redirect-chain resolution, or broad JSON parsing to viewer GET paths.
• Do not make frontend or Hono caching the primary solution.
• Do not make persistent schema decisions before cache/sidecar read model benchmarks validate them.
• Do not build or write anything during a read-only open (see Priority 2 and Remove on-open opportunistic read model build (corrects #112) #177).

Recommended Execution Order

1. Build viewer read model cache infrastructure #108 Build viewer read model cache infrastructure
2. Move /api/pages to viewer_pages with cursor pagination #105 Move /api/pages to viewer_pages with cursor pagination
3. Benchmark and tune /api/pages on large archives #106 Benchmark and tune /api/pages on large archives
4. Implement directory tree read model and API #107 Implement directory tree read model and API
5. Move /api/summary to viewer_summary #104 Move /api/summary to viewer_summary
6. Move /api/error-kinds to viewer_error_kind tables #118 Move /api/error-kinds to viewer error-kind tables
7. Move broken and external links to viewer_anchor_facts #114 Move broken and external links to viewer_anchor_facts
8. Move page-link stats to viewer_page_stats #109 Move page-link stats to viewer_page_stats
9. Move resources, referrers, and unused resources to viewer resource tables #110 Move resources, referrers, and unused resources to viewer resource tables
10. Move image list to viewer_images #113 Move image list to viewer_images
11. Move header checks to viewer_header_checks #119 Move header checks to viewer_header_checks
12. Move duplicates and mismatches to viewer diagnostic tables #115 Move duplicates and mismatches to viewer diagnostic tables
13. Remove on-open opportunistic read model build (corrects #112) #177 Remove on-open opportunistic read model build (corrects Define persistent viewer read model build timing #112)
14. Move isolated pages and graph views to precomputed graph tables #117 Move isolated pages and graph views to precomputed graph tables
15. Move violation data to analysis_violations #116 Move violation data to analysis_violations
16. Define persistent viewer read model build timing #112 Define persistent viewer read model build timing
17. Design shared text/URL dedup ref tables for viewer read models #139 Design shared text/URL dedup ref tables for viewer read models
18. Plan write model refactor after read model stabilization #111 Plan write model refactor after read model stabilization
19. Phase 6-A: create write-model ref and header tables (additive DDL) #190 Phase 6-A: create write-model ref and header tables
20. Phase 6-B: populate ref tables from existing write-model data #191 Phase 6-B: populate ref tables
21. Phase 6-C: create new entity and edge tables (additive DDL) #192 Phase 6-C: create new entity and edge tables
22. Phase 6-D: populate new entity and edge tables (migration script) #193 Phase 6-D: populate new entity and edge tables
23. Phase 6-E: migration verification (8 invariant checks + .bak rollback) #194 Phase 6-E: migration verification
24. Phase 6-F: update reader code to use new tables (dual-read period) #195 Phase 6-F: update reader code
25. Phase 6-G: update crawler write path to target new tables #196 Phase 6-G: update crawler write path
26. Phase 6-H: drop old tables and migrate FK declarations #197 Phase 6-H: drop old tables and migrate FK declarations

Dependency Rules

• Build viewer read model cache infrastructure #108 is the root blocker for read model endpoint work.
• Move /api/pages to viewer_pages with cursor pagination #105 establishes cursor/profile/count/page-anchor patterns for list endpoints.
• Benchmark and tune /api/pages on large archives #106 validates the first read model with real benchmarks before broad rollout.
• Implement directory tree read model and API #107 may start after Build viewer read model cache infrastructure #108, but should reuse cursor/profile utilities from Move /api/pages to viewer_pages with cursor pagination #105 where applicable.
• Move page-link stats to viewer_page_stats #109 and Move isolated pages and graph views to precomputed graph tables #117 should not finalize until Move broken and external links to viewer_anchor_facts #114 establishes resolved anchor facts.
• Define persistent viewer read model build timing #112 should wait for cache/sidecar read model behavior and benchmark feedback.
• Remove on-open opportunistic read model build (corrects #112) #177 corrects Define persistent viewer read model build timing #112's on-open build path and should land before Move isolated pages and graph views to precomputed graph tables #117/Move violation data to analysis_violations #116 continue, since it changes the fallback contract every read-model endpoint relies on.
• Plan write model refactor after read model stabilization #111 is intentionally last and should not start until viewer read models are stable.
• Design shared text/URL dedup ref tables for viewer read models #139 should wait for Build viewer read model cache infrastructure #108 (and any table it deferred inline text storage on) to land, and should land before Plan write model refactor after read model stabilization #111 so the write-model refactor can build on a settled dedup ref design.
• Phase 6-A: create write-model ref and header tables (additive DDL) #190–Phase 6-H: drop old tables and migrate FK declarations #197 (Phase 6-A through 6-H) are blocked by Plan write model refactor after read model stabilization #111 and execute in sequence: 6-A → (6-B, 6-C in parallel) → 6-D → 6-E → 6-F → 6-G → 6-H.

Sub-issues

• Build viewer read model cache infrastructure #108 Build viewer read model cache infrastructure
• Move /api/pages to viewer_pages with cursor pagination #105 Move /api/pages to viewer_pages with cursor pagination
• Benchmark and tune /api/pages on large archives #106 Benchmark and tune /api/pages on large archives
• Implement directory tree read model and API #107 Implement directory tree read model and API
• Move /api/summary to viewer_summary #104 Move /api/summary to viewer_summary
• Move /api/error-kinds to viewer_error_kind tables #118 Move /api/error-kinds to viewer error-kind tables
• Move page-link stats to viewer_page_stats #109 Move page-link stats to viewer_page_stats
• Move resources, referrers, and unused resources to viewer resource tables #110 Move resources, referrers, and unused resources to viewer resource tables
• Move image list to viewer_images #113 Move image list to viewer_images
• Move broken and external links to viewer_anchor_facts #114 Move broken and external links to viewer_anchor_facts
• Move header checks to viewer_header_checks #119 Move header checks to viewer_header_checks
• Move duplicates and mismatches to viewer diagnostic tables #115 Move duplicates and mismatches to viewer diagnostic tables
• Remove on-open opportunistic read model build (corrects #112) #177 Remove on-open opportunistic read model build (corrects Define persistent viewer read model build timing #112)
• Move isolated pages and graph views to precomputed graph tables #117 Move isolated pages and graph views to precomputed graph tables
• Move violation data to analysis_violations #116 Move violation data to analysis_violations
• Define persistent viewer read model build timing #112 Define persistent viewer read model build timing
• Design shared text/URL dedup ref tables for viewer read models #139 Design shared text/URL dedup ref tables for viewer read models
• Plan write model refactor after read model stabilization #111 Plan write model refactor after read model stabilization
• Phase 6-A: create write-model ref and header tables (additive DDL) #190 Phase 6-A: create write-model ref and header tables (additive DDL)
• Phase 6-B: populate ref tables from existing write-model data #191 Phase 6-B: populate ref tables from existing write-model data
• Phase 6-C: create new entity and edge tables (additive DDL) #192 Phase 6-C: create new entity and edge tables (additive DDL)
• Phase 6-D: populate new entity and edge tables (migration script) #193 Phase 6-D: populate new entity and edge tables (migration script)
• Phase 6-E: migration verification (8 invariant checks + .bak rollback) #194 Phase 6-E: migration verification (8 invariant checks + .bak rollback)
• Phase 6-F: update reader code to use new tables (dual-read period) #195 Phase 6-F: update reader code to use new tables (dual-read period)
• Phase 6-G: update crawler write path to target new tables #196 Phase 6-G: update crawler write path to target new tables
• Phase 6-H: drop old tables and migrate FK declarations #197 Phase 6-H: drop old tables and migrate FK declarations

Acceptance

• Each sub-issue has a concrete read model/API scope, benchmark or validation criteria, and explicit safety constraints.
• The final design has measured read paths and documented build timing.
• The persistent read model is introduced only after cache/sidecar iteration proves the table/index/query shape.
• Every sub-issue respects the Priority order above; when in doubt, that order resolves the ambiguity.

[YusukeHirao]

Sub-issues: - #108 Build viewer read model cache infrastructure - #105 Move /api/pages to viewer_pages with cursor pagination - #106 Benchmark and tune /api/pages on large archives - #107 Implement directory tree read model and API - #104 Move /api/summary to viewer_summary - #118 Move /api/error-kinds to viewer_error_kind tables - #109 Move page-link stats to viewer_page_stats - #110 Move resources, referrers, and unused resources to viewer resource tables - #113 Move image list to viewer_images - #114 Move broken and external links to viewer_anchor_facts - #119 Move header checks to viewer_header_checks - #115 Move duplicates and mismatches to viewer diagnostic tables - #117 Move isolated pages and graph views to precomputed graph tables - #116 Move violation data to analysis_violations - #112 Define persistent viewer read model build timing - #111 Plan write model refactor after read model stabilization