Epic: TableBase Sync Handler Factory — Implementation Plan

[OAGr]

TableBase Sync Handler Factory — Implementation Plan

TL;DR

30 TableBase POST /sync handlers in apps/wiki-server/src/routes/tablebase/ share ~750 lines of identical post-upsert boilerplate but only 3/30 have audit logging — 8/11 recent bugs were directly caused by this duplication. Build a createSyncHandler<T>() factory that returns a Hono handler function (preserving RPC type inference), pilot it on 3 routes spanning all tiers, then migrate routes tier-by-tier behind a per-route feature flag. Top open question: should entities/things stay permanently hand-rolled, or push for "factory or bust"?

Problem

Each new sync route requires copy-pasting ~150-200 lines of boilerplate: parse → validate → natural-key dedup → transaction { build values → fetch existing → upsert → audit → resolveEntityFKs → upsertThingsInTx → writeInlineVerdicts } → link claims → return counts. Developers must remember which features to include, leading to systematic gaps (3/30 audit, 2/30 source-check, 12/30 still using per-item loops with values listed twice). Recent bugs traced to missing utility calls in routes that nobody enforced consistency on (PR #4029 added validateEntityRefs to 8 missing endpoints; PR #4057/#4059 fixed inconsistent SQL array parameterization).

Current State

Asset	Status	Gap
apps/wiki-server/src/routes/tablebase/	30 routes with POST /sync	All hand-rolled, 3 tiers of feature maturity
Tier 1 (full features)	3 routes: grants, personnel, equity-positions	Reference impl
Tier 2 (batch upsert, missing audit)	14 routes: investments, funding-rounds, divisions, benchmark-results, secondary-market-prices, prediction-markets, division-personnel, entity-resources, data-sources, funding-programs, platform-accounts, things, research-areas, bluesky	Missing audit logging, source-check enforcement
Tier 3 (per-item loops)	13 routes: campaign-finance, political-races, political-scores, political-votes, political-offices, entity-events, entity-assessments, policy-stakeholders, website-sources, benchmarks, ids, publications, entities	Missing batch upsert AND audit AND source-check
crux/validate/validate-tablebase-completeness.ts	Already a blocking CI check	Doesn't yet check audit logging or factory adoption
apps/wiki-server/src/routes/tablebase/audit-log.ts	logAuditEntries() shared utility	Used by 3 routes
apps/wiki-server/src/routes/tablebase/write-inline-verdicts.ts	writeInlineVerdicts() shared utility	Used by 11 routes
apps/wiki-server/src/routes/shared/resolve-entity-fks.ts	resolveEntityFKs() shared utility	Used by 11 routes
apps/wiki-server/src/routes/shared/validate-entity-refs.ts	validateEntityRefs() shared utility	Used by 12 routes (recently added to 8 via PR #4029)
apps/wiki-server/src/routes/shared/source-check-enforcement.ts	enforceSourceCheck() shared utility	Used by only 2 routes
crux/wiki-server/sync-common.ts	Client-side batchSync<T>() factory	Server-side has no equivalent

Key Decisions

• Factory returns handler function, not sub-app. Confirmed via PR feat: tablebase factories — registry, client delete, paginated query helper #4067's paginatedQuery precedent — returning raw shapes preserves InferResponseType<>. Type test in pre-Phase-1 audit (#0) must verify this empirically before any code changes.

• Pilot spans all tiers, not just Tier 1. Phase 1 must convert 1 Tier 1 + 1 Tier 2 + 1 Tier 3 route to stress phase-omission paths and escape hatches simultaneously — picking only Tier 1 routes gives false confidence because they exercise every phase.

• Hard cap: 1 escape hatch per route. preValidate, postUpsert, conflictSet are escape hatches, not extension points. Routes needing >1 hook stay hand-rolled. Validator enforces the budget. Without this, the factory drifts toward god-object.

• Per-route feature flag for safe migration. USE_SYNC_FACTORY_ROUTES=grants,personnel env var lets each route fall back to legacy handler. Legacy handler kept alive for 7 days after each migration; deletion is its own commit.

• Phase 4 (source-check enforcement rollout) cut from this plan. It's a policy rollout, not deduplication. Filed as separate epic. This plan ends at Phase 3 + validator promotion.

• Excluded routes (~2 stay hand-rolled): entities.ts (slug displacement), things.ts (it IS the things table). bluesky.ts /sync/:did, research-areas.ts /sync-organizations//sync-papers are secondary endpoints, not the main /sync — those primary endpoints DO migrate to the factory.

• Phase ordering inverted from initial proposal. Original "audit logging first" is backwards: adding audit to per-item loops creates N+1 query patterns. Factory adoption happens first; audit/verdicts/enforcement arrive as side effects.

Architecture

File	Action	Purpose
apps/wiki-server/src/routes/tablebase/sync-factory.ts	create	createSyncHandler<T>() + SyncConfig interface (~250 lines). Auto-chunks batches based on Postgres param limit (floor(65535 / columnCount)). Wraps every phase in SyncPhaseError({ route, phase, cause }) for stack-trace context.
apps/wiki-server/src/routes/tablebase/sync-factory.test.ts	create	Unit + adversarial tests against real test postgres. Adversarial fixtures cover bug classes from PR #4057, #4029, #3400.
apps/wiki-server/src/routes/tablebase/sync-factory.test-d.ts	create	TypeScript-level test using expectTypeOf asserting InferResponseType<> resolves to the exact expected shape on a factory-built route.
crux/commands/tablebase/sync-scaffold.ts	create	pnpm crux tb sync scaffold <table> emits a starter route file with minimum config. Without this, agents will copy-paste instead of adopting the factory.
crux/validate/validate-tablebase-completeness.ts	modify	Add 4 checks: factory adoption, audit coverage, hook budget (max 1 per route), per-route opt-out comments must include issue # and expiration date
crux/validate/validate-insert-set-parity.ts	create	One-shot validator for Phase 3 batch conversion (catches PR #4057 bug class). Not a permanent CI gate.
apps/wiki-server/src/routes/tablebase/{3 pilot routes}.ts	modify	Phase 1 pilot
apps/wiki-server/src/routes/tablebase/{14 Tier 2 + 13 Tier 3 routes}.ts	modify	Phase 2-3 migration
apps/wiki-server/src/config/feature-flags.ts	modify	Add USE_SYNC_FACTORY_ROUTES env var parsing
.claude/rules/tablebase-sync-factory.md	create after Phase 3	Rule doc deferred until API stabilizes
content/docs/internal/sync-factory-reference.mdx	create after Phase 3	Deep-dive reference with worked examples per tier

Implementation Phases

Phase 0: Pre-Phase-1 Audit Session — S

Goal: Resolve the 6 blocking technical gaps from red team review BEFORE writing any factory code.

• Generate definitive route inventory: rg -l "\.post\(\"/sync\"" apps/wiki-server/src/routes/tablebase/ and classify each file exactly once into Tier 1/2/3/excluded
• Audit audit-log.ts for batch safety: confirm logAuditEntries() batches the existing-row pre-fetch in a single SELECT ... WHERE id IN (...). If per-item, fix it.
• Compute Postgres param limit per route: wc columns × default batch size. Document any route exceeding ~200 rows × 30 cols and design chunking strategy.
• Custom logic inventory: read every route's POST /sync handler and produce a table of unique pre/post-upsert behaviors. If >5 routes need custom hooks, factory contract is wrong — stop and redesign.
• Define hook rollback contract: hooks receive tx handle, MUST only do DB work on it, external side effects forbidden, throwing rolls back the entire sync.
• Write the type-level test (sync-factory.test-d.ts) FIRST against a hand-mocked factory signature to verify InferResponseType<> works before implementing.
• Baseline LOC: wc -l apps/wiki-server/src/routes/tablebase/*.ts > /tmp/tb-baseline.txt for measuring savings.

Quality gates: Audit document committed to PR description before any code changes. Type-level test compiles. No factory code written yet.

Exit criteria: All 6 blocking gaps resolved. Route inventory matches between this plan and reality.

Phase 1: Factory + 3 Tier-Spanning Pilot Routes — M

Goal: Working createSyncHandler<T>() factory adopted by 3 routes spanning all tiers (1 Tier 1 + 1 Tier 2 + 1 Tier 3). Net code change negative. Validates the design before any tier sweep.

• Implement sync-factory.ts with the 7-phase pipeline. Every phase wraps errors in SyncPhaseError({ route, phase, cause }). Auto-chunks batches per pre-Phase-1 calculation.
• Implement scaffolder: pnpm crux tb sync scaffold <table> emits minimum-config route file
• Add USE_SYNC_FACTORY_ROUTES env var; factory routes check the flag and fall back to legacy handler if excluded
• Convert personnel.ts (Tier 1, exercises all phases including postUpsert for new: prefix backfill)
• Convert divisions.ts (Tier 2, exercises phase-omission — no claims, no FK resolve)
• Convert political-scores.ts (Tier 3, exercises batch conversion from per-item loop)
• Keep legacy handlers in the same file (commented out or behind flag) for 7-day rollback window
• Write per-route round-trip tests: happy path + FK-invalid + duplicate-id + 500-item batch (chunking) + concurrency race
• Write adversarial regression tests reproducing PR fix: entity sync unnest() SQL error + stabilize e2e dropdown test #4057, B1: Add validateEntityRefs to 8 missing sync endpoints (epic #4017) #4029, feat: centralize entity FK resolution into shared resolveEntityFKs() #3400 bug classes — must run for every factory-adopted route via parameterized test
• Verify pnpm tsc --noEmit compile time hasn't regressed >10% (DX gate)

Quality gates:

• pnpm test apps/wiki-server passes
• pnpm crux w validate gate --fix passes
• npx tsc --noEmit shows no new errors AND compile time within 10% of baseline
• All 3 pilot routes round-trip via tests
• Type test confirms InferResponseType<> resolves on a factory-built route
• Manual smoke test: curl -X POST localhost:3001/api/personnel/sync -d @fixture.json returns expected shape
• Stack trace from a deliberately-thrown phase error includes route name and phase name

Exit criteria: 3 pilot routes use factory, RPC inference works, scaffolder works, feature flag works, compile time acceptable. STOP-AND-JUDGE GATE: produce a Phase 1 retrospective answering "did the factory save what we expected? did escape hatches stay ≤1 per route? did RPC inference survive? compile time impact?" Phase 2 only proceeds with explicit user sign-off.

Phase 2: Migrate 14 Tier 2 Routes — L

Goal: All Tier 2 routes use the factory. Each migration adds audit logging + verdicts as side effects. Net code change: ~2,800 line reduction.

• Migrate routes in dependency order via feature flag (legacy handler stays alive 7 days):
  • Batch A: data-sources, division-personnel, entity-resources, things (simple FK)
  • Batch B: investments, funding-rounds, funding-programs, secondary-market-prices (cross-table)
  • Batch C: benchmark-results, prediction-markets, platform-accounts, research-areas main /sync, bluesky main /sync (mixed)
• After Batch A: stop-and-judge — line count, hook count, audit log growth rate. Continue only if metrics hold.
• After Batch B: same gate
• Round-trip + adversarial tests added BEFORE each conversion (not after)
• Per migration PR includes a Revert Plan section: exact steps if route misbehaves
• Delete legacy handlers in a separate commit per route, 7+ days after migration

Quality gates:

• Each migration PR reviewed against round-trip test
• pnpm test apps/wiki-server passes after each batch
• Validator coverage grid shows monotonic progress
• Audit_log table growth rate ≤ 2x baseline (sanity check)
• No emergency feature-flag rollbacks

Exit criteria: 17 of 28 non-excluded routes (3 pilot + 14 Tier 2) use the factory. Hook usage ≤ 1 per route. STOP-AND-JUDGE GATE before Phase 3.

Phase 3: Migrate 12 Tier 3 Per-Item-Loop Routes — L

Goal: All Tier 3 routes use the factory. Batch conversion happens during factory adoption. Net code change: ~2,500 line reduction PLUS performance improvement.

• Write validate-insert-set-parity.ts BEFORE converting any route. Run on full codebase to find existing drift bugs.
• Migrate in batches of 3-4 with feature flag:
  • Batch D: benchmarks, entity-events, entity-assessments, website-sources (simplest)
  • Batch E: political-scores, political-votes, political-offices, policy-stakeholders
  • Batch F: campaign-finance, political-races, publications, ids (special cases)
• Performance smoke test per batch: 500-item sync completes within 2x baseline
• Revert plan + 7-day legacy keep-alive per route

Quality gates:

• Round-trip + adversarial tests pass for each route
• Column-drift validator finds zero violations after each PR
• 500-item performance test passes
• No new error logs in wiki-server for 24h after each deploy

Exit criteria: 28 of 28 non-excluded routes use the factory. Validator promoted to blocking for factory adoption + hook budget. Documentation written: .claude/rules/tablebase-sync-factory.md + content/docs/internal/sync-factory-reference.mdx.

Scope Cuts

Explicitly NOT included:

• Source-check enforcement rollout — separate epic. Per-route mode: "off" | "warn" | "block" flag flipping is a policy rollout, not deduplication.
• Audit log retention / partitioning — must be resolved as a separate issue BEFORE Phase 3 ships (not after), since 25 routes writing audit entries will grow the table fast.
• Stats endpoint factory — already rejected in PR feat: tablebase factories — registry, client delete, paginated query helper #4067 as "too much variation"
• GET /all + count factory — paginatedQuery already exists; widening adoption is separate
• Schema-driven Zod auto-generation — too risky
• Single generic POST /api/tablebase/sync endpoint — breaks Hono RPC types
• entities.ts migration — slug displacement too unique
• Natural-key unique indexes (B3: Natural key unique indexes need upsert conflict target changes #4049) — separate epic, blocked on coordinated migration
• Frontend table dedup (Extract shared table component factory to eliminate duplication #1254) — different system

Quality & Verification Infrastructure

Area	Specification
Tests	sync-factory.test.ts (factory unit tests against real test postgres). sync-factory.test-d.ts (TypeScript-level RPC inference test). Per-route round-trip tests written BEFORE conversion: happy path + FK-invalid + duplicate-id + 500-item batch + concurrency race. Parameterized adversarial tests reproducing PR #4057, #4029, #3400 bug classes — run for every factory-adopted route.
Migration verification	rg "for \(const item of items\)" apps/wiki-server/src/routes/tablebase/ must return 0 after Phase 3. rg "createSyncHandler" apps/wiki-server/src/routes/tablebase/ must show 28 routes. Validator's coverage grid must show 28/28 factory adoption.
Integration smoke test	Per migration PR: pnpm crux tb sync <table> --batch=500 fixtures/sample.json then psql -c "SELECT COUNT(*) FROM tablebase_audit_log WHERE record_type=$1 AND created_at > now() - interval '1 minute'" — verify audit count matches insert count.
UI verification	N/A — pure backend refactor. Run cd apps/web && PLAYWRIGHT_BASE_URL=https://www.longtermwiki.com npx playwright test e2e/render-audit.spec.ts after each phase to confirm directory pages still render.
Deploy steps	New env var: USE_SYNC_FACTORY_ROUTES=<csv> (Phase 1 onward). No DB migrations, no new tables. Standard deploy via image build + ArgoCD. Per-route rollback = remove route from env var CSV.
Documentation	DEFERRED until Phase 3 exit (per scope critic — premature docs calcify wrong decisions). Then: .claude/rules/tablebase-sync-factory.md (terse, <200 lines, auto-loaded), content/docs/internal/sync-factory-reference.mdx (deep-dive with per-tier worked examples), JSDoc on every SyncConfig field annotated with "Used by phase N".
Monitoring	Wiki-server already logs sync coverage via logSourceCheckCoverage(). SyncPhaseError carries route+phase context into structured logs. Audit log table queryable for change history. Compile-time impact tracked in CI.

Risks & Mitigations

Risk	Severity	Mitigation
Audit logging in per-item loops creates N+1 queries	Blocking	Phase ordering inverted: factory adoption happens first; per-item loops batch-converted as part of adoption (not before with audit on top). Phase 0 verifies logAuditEntries batches its pre-fetch.
Postgres parameter limit (65535) exceeded on large batches	Blocking	Phase 0 computes per-route limit. Factory auto-chunks: chunkSize = floor(65535 / columnCount). Test with 1000-item fixture.
Hook rollback semantics undefined	Blocking	Explicit contract documented in Phase 0: hooks receive tx, DB-only side effects, throwing = full rollback. Test asserts zero rows persist when postUpsert throws.
Column drift between .values() and .set() (PR #4057 class)	Significant	validate-insert-set-parity.ts runs in Phase 3 BEFORE any conversion. Factory auto-derives SET from toRow() keys, making drift structurally impossible in adopted routes.
Factory becomes 15-field god-object → framework fatigue	Significant	Hard hook budget (max 1 per route) enforced by validator. Scaffolder command provides path of least resistance. Stop-and-judge gates between phases.
Factory breaks Hono RPC type inference	Blocking	Phase 0 type-level test against hand-mocked signature. Phase 1 quality gate verifies on real factory. Compile time also tracked.
Stack traces eat call-site context	Significant	Every phase wraps errors in SyncPhaseError({ route, phase, cause }). Phase 1 test asserts error message contains route name.
Cumulative bloat if project stalls	Significant	Phase ordering ensures every phase removes code on net. Stop-and-judge gates after Phase 1, Batch A, Batch B.
Sparse test coverage on existing handlers	Blocking	Round-trip + adversarial tests written BEFORE each conversion. Per-route fixtures committed alongside route migrations.
Legacy fields like granteeId look like FKs but aren't	Significant	Custom logic inventory in Phase 0. Field contracts documented per route. entityRefFields callback excludes legacy fields explicitly.
Slug displacement re-creep	Significant	Factory explicitly does NOT handle slug displacement. entities.ts excluded permanently. Validator: if route imports displaceEntitySlug, cannot use factory.
LLM agents confused by 15-field generic factory	Significant	Scaffolder + discriminated-union config (`{ kind: "batch" }
Compile time regression from generic inference	Moderate	Phase 1 quality gate: tsc --noEmit time must stay within 10% of baseline. Factory's exported handler type stays non-generic.
Route inventory bugs	Blocking	Phase 0 produces definitive inventory before any code changes.
Audit log table grows fast under 25 routes writing	Significant	Audit log retention/partitioning strategy must be resolved as separate issue BEFORE Phase 3 ships. Listed as scope cut.

Open Questions

1. Excluded route boundary — Should entities and things permanently stay hand-rolled, or push for "factory or bust" with custom escape hatches per route?
2. Audit log retention strategy — Listed as scope cut but blocking for Phase 3. Partitioning, retention TTL, or accept the growth?
3. Hook budget enforcement granularity — Strict 1-hook max, or allow 2 with justification comment?
4. Validator promotion timing — Wait for 100% coverage before any blocking promotion, or stage per-tier?
5. Performance baseline — Where to publish per-route sync latency baselines so regressions are detectable?

Rejected Approaches

• Approach A (Minimalist runPostSyncPipeline() only) — Doesn't enforce parity; developers still must remember to call it. Preserved as fallback if Phase 1 factory pilot fails.
• Approach C2 (Schema-driven auto-sync from Drizzle metadata) — Loses business-rule constraints; fights Drizzle's type system.
• Approach C3 (Single generic endpoint) — Breaks Hono RPC type inference; violates RPC migration rule.
• Approach D-original (Audit logging first) — Phase ordering creates N+1 queries and temporarily increases LOC. Risk agent flagged as backwards.

Red Team Log

Technical critic (24 issues, 6 blocking): route inventory contradictions, hook rollback contract undefined, audit pre-fetch N+1 risk, Postgres param limit ignored, enforcement window assumes weekly cadence, RPC type inference unverified, secondary sync endpoints unaddressed, non-entities FK targets, conflictSet defeats validator, test harness too thin, round-trip tests don't catch the targeted bugs, custom logic inventory missing, no rollback plan, audit log retention scope creep. Resolutions: Phase 0 audit session added; chunking required; type test added; hook contract specified; Phase 4 cut and audit retention listed as blocking-for-Phase-3 separate issue.

Scope critic (10 cuts): Factory may be unnecessary (minimalist gets 80%); Phase 4 is scope creep; test harness premature; column-drift validator unnecessary if factory generates SET; Phase 1 not truly independent (Tier 1 only doesn't stress phase-omission); rules doc premature; ship Phase 1 alone option; 5-route exclusion creep; publications double-listed; no go/no-go between phases. Resolutions: Phase 4 cut; pilot now spans tiers (1/2/3); test harness simplified to per-route plain tests; column-drift validator scoped to one-shot Phase 3 use; rules doc deferred to Phase 3 exit; explicit stop-and-judge gates added; exclusions reduced from 5 to 2; route inventory bug fixed.

DX critic (8 issues): Cognitive cost vs typing cost (need scaffolder); stack traces eat context (need per-phase error wrapping); escape hatch slippery slope (need hook budget); fixed response shape (need extendResponse); docs underspecified (need split rule + reference); regression revert cost (need feature flag); LLM agents confused (need discriminated unions + examples); compile time (need non-generic handler type). Resolutions: Scaffolder added as Phase 1 deliverable; per-phase error wrapping required; hook budget validator added; USE_SYNC_FACTORY_ROUTES feature flag added; documentation strategy expanded; compile time gate added.

Tasks

• Phase 0: TableBase factory pre-flight audit #4089 — Phase 0: TableBase factory pre-flight audit

• Phase 1: Factory + 3 tier-spanning pilot routes #4090 — Phase 1: Factory + 3 tier-spanning pilot routes

[OAGr]

Branch: claude/tablebase-factory-phase0-audit

Phase 0 audit corrections to this plan

The Phase 0 audit (#4089) found several inventory inaccuracies in the original plan:

Route counts:

• 27 main /sync POST handlers (plan said 30)
• 23 factory candidates after exclusions (plan said 25)

Updated tier classification:

Tier	Count	Routes
Tier 1	3	grants, personnel, equity-positions
Tier 2 (batch upsert)	12	investments, funding-rounds, funding-programs, divisions, division-personnel, things, prediction-markets, benchmark-results, secondary-market-prices, entity-resources, research-areas main /sync, platform-accounts
Tier 3 (per-item loops)	12	political-scores, political-votes, political-races, political-offices, policy-stakeholders, campaign-finance, entity-events, entity-assessments, publications, benchmarks, website-sources, ids
Excluded permanently	4	entities, bluesky (/sync/:did only), data-sources (multi-table writes), ids (nextval() semantics)

New exclusions (not in original plan):

• bluesky.ts — only has /sync/:did endpoint (external Bluesky API fetch). NO main /sync. Was incorrectly listed in Tier 2.
• data-sources.ts — /sync writes to multiple tables (resources + resourceTabularSources + sourceSnapshots) per call. Already uses zv("json", ...) middleware. Not a standard batch sync pattern.
• ids.ts — uses nextval('entity_id_seq') for ID allocation. Fundamentally different semantics from batch upsert.

Pilot route selection corrected:

• Tier 1: personnel.ts (exercises postUpsert escape hatch + all 7 phases)
• Tier 2: divisions.ts (exercises phase-omission: no claims, no FK resolve, plus conflictSet COALESCE override)
• Tier 3: political-scores.ts (simplest per-item loop; clean batch conversion)

Other audit findings (all green):

• ✅ audit-log.ts is already batch-safe (single INSERT...VALUES). The 3 routes using it batch-fetch existing rows in a single inArray() query. The N+1 risk identified by the risk agent doesn't exist for current routes.
• ✅ Postgres parameter limit: largest table campaignFinance has 25 columns → max 2621 rows per upsert. Current default batch sizes (100-500) have 5×-30× headroom. Factory must auto-chunk for safety against future schema growth: chunkSize = floor(60000 / columnCount).
• ✅ Hook budget of 1 per route is achievable: 4 routes need preValidate, 1 needs postUpsert, 4 need conflictSet overrides for COALESCE preservation, 0 need >1 hook.
• ✅ Hook rollback contract specified: hooks receive tx, external side effects forbidden, throwing rolls back the entire sync.
• ✅ Type-level test (apps/wiki-server/src/routes/tablebase/sync-factory.test-d.ts) compiles via full wiki-server tsc --noEmit. Verified InferResponseType<> resolves to exact { upserted; verdictsWritten; claimsLinked } shape (not widened to any/unknown). Negative test correctly produces error TS2353.

Phase 1 design is unblocked. See full audit comment on #4089.

Baseline LOC: 14,160 total tablebase route LOC. ~8,500 in factory candidates. Target reduction across Phases 1-3: ~4,500 lines (~53% of candidate routes).