Epic: Data integrity: systemic gaps across schema, validation, write paths

[OAGr]

Summary

A broad investigation triggered by the resourceId: null evidence bug found that this was not an isolated issue. There are 15 distinct categories of data integrity gaps across the codebase, falling into one underlying pattern: validation is opt-in, bypass is one line away, and "best-effort" is the default for what is actually primary data.

This epic tracks the work to systematically close those gaps.

Why This Matters

Several findings represent active data corruption happening right now, not theoretical risks:

• Every "0 employees" or "$0 funding" displayed in FactBase could actually be NULL data silently coerced to 0
• Source check verdicts and evidence are silently dropped on storage failure — users see "completed"
• Claim records exist with entityId = NULL, creating orphaned verification work
• The original resourceId: null bug repeats in 8 other code paths

The root cause is cultural, not local: a single fix will not move the needle. We need a coordinated push across schema, validation, bypass paths, and the build pipeline.

Investigation Findings

Full investigation in [internal notes — link TBD]. Summary of categories:

Tier 0 — Active data corruption (P0)

#	Issue	File
0.1	FactBase numeric values silently coerced to 0 (row.numeric ?? 0)	apps/wiki-server/src/routes/factbase/facts.ts:83,95,97
0.2	proposed_claims inserts allow entityId = NULL	apps/wiki-server/src/routes/claims/claims.ts:198,231
0.3	Source check verdicts dropped silently via .catch(() => warn) on primary writes	crux/commands/source-check-wiki-pages.ts:366,434,481,514; crux/lib/job-handlers/claim-verification.ts:320-337
0.4	Original resourceId: null bug + 8 similar bypass paths in evidence creation	crux/lib/source-check/verdict-handler.ts:42-44; crux/commands/factbase-source-check.ts:233

Tier 1 — Schema-level enforcement gaps

• Missing FK validation on 9 sync endpoints: grants.ts:675, divisions.ts:251, division-personnel.ts:187, entity-assessments.ts:141, equity-positions.ts:260, policy-stakeholders.ts:133, things.ts:590, research-areas.ts:507,596, entity-resources.ts:92. The validateEntityRefs() helper exists and is used by investments.ts, personnel.ts, benchmark-results.ts, citations.ts — pure inconsistency.
• No CHECK constraints on 15+ status fields: jobs.status, auto_update_results.status, sourceCheckVerdicts.verdict, grants.status, divisions.status, funding_programs.status, facts.verdict, resources.enrichmentStatus, resources.fetchStatus, incidents.status, political_races.status, political_candidates.status, agent_sessions.status, research_areas.status.
• onDelete: "set null" on critical FKs silently orphans records: citation_quotes.resourceId, pageCitations.resourceId, statements.propertyId, statements.valueEntityId, personnel.{personEntityId,orgEntityId}, grants.{orgEntityId,granteeEntityId}.
• Missing unique indexes on natural keys: personnel(personEntityId, orgEntityId, roleType, startDate), grants(orgEntityId, granteeEntityId, round, amount), investments(...), funding_rounds(...), policy_stakeholders(...). Concurrent requests can create duplicates the application thinks were prevented.

Tier 2 — Concurrency and atomicity bugs

• Two-table write without transaction: dualWriteToSourceCheck() in apps/wiki-server/src/routes/wikibase/citations.ts:48-167 writes to sourceCheckEvidence and sourceCheckVerdicts in sequence, called fire-and-forget at line 649. Tables can become permanently inconsistent.
• Check-then-write race in incident dedup: apps/wiki-server/src/routes/operational/monitoring.ts:351-399.
• Slug reassignment race: apps/wiki-server/src/routes/tablebase/entities.ts:930-964.
• ID sequence burning: apps/wiki-server/src/routes/tablebase/ids.ts:105-145 calls nextval() then onConflictDoNothing(). Concurrent allocations burn sequence values.
• Page deletion without related-table cleanup: apps/wiki-server/src/routes/wikibase/pages.ts:256-279.

Tier 3 — Type safety bypasses hiding data drift

• LLM responses parsed without validation: crux/lib/anthropic.ts:189-212, crux/lib/json-parsing.ts:82-150. Truncated responses get regex-extracted partial JSON, then cast as the expected type.
• External API responses cast without runtime validation: GitHub (monitoring.ts:525,543, github-issues.ts:65, crux/lib/github.ts:131,214), OpenRouter (crux/lib/llm.ts:199-204), Wikidata, ProPublica, Bluesky, Semantic Scholar, Exa.
• RSS feed parsing via regex: crux/auto-update/feed-fetcher.ts:131-188. Hand-rolled XML parsing with broken entity handling.
• JSONB columns written with z.record(z.unknown()) and read with no validation: jobs.{params,result}, page_improve_runs.{citationAudit,qualityMetrics}, tablebase_audit_log.{oldData,newData}, auto_update_runs.newPagesCreated (with a CSV-fallback hack at auto-update-runs.ts:83-91), active_agents.{metadata,filesTouched}, data_sources.verificationConfig, claims.{resourceIds,qualifiers}.

Tier 4 — Cached/denormalized data drift

• things.verdict populated at migration time and never updated: apps/wiki-server/drizzle/0087_populate_things_from_domain_tables.sql:203-221. Search results show stale verdicts.
• research_area_scores only updated by manual recompute: apps/wiki-server/src/routes/tablebase/research-areas.ts:850-929.
• Wiki page metrics drift on build failure: apps/wiki-server/src/routes/operational/build-metrics.ts:146-247 syncs fire-and-forget; partial syncs leave metrics inconsistent across pages.
• Bluesky/Semantic Scholar counts have no scheduled refresh.

Tier 5 — Bypass paths and inconsistent enforcement

• skipEntityValidation=true is a mainline feature: crux/lib/wiki-server/personnel.ts:59-66. Used by normalize-ids.ts and sync-careers-to-personnel.ts.
• Direct apiRequest() calls bypass typed clients: factbase-source-check.ts:233, verify-entity.ts:26, verify-stakeholders.ts:27-28, tablebase.ts:628, import-quri-personnel.ts, claim-verification.ts:223.
• Batch claim-linking is fire-and-forget after commit: same pattern in grants.ts:770-787, personnel.ts:543-557, investments.ts:381-396, benchmark-results.ts:281-296, funding-rounds.ts:385-400. Records committed; claim links fail silently; caller can't tell.
• Batch sync silently aborts after 3 consecutive failures: crux/wiki-server/sync-common.ts:300-310.

Tier 6 — Read-time enrichment hiding write-time gaps

The codebase is full of fallback chains like entityTitle ?? (displayName && !isSid(displayName) ? displayName : null) ?? (rawId && !isSid(rawId) ? rawId : null) — a sign that none of the layers can be trusted so the read code tries them all. Locations: entity-ref.ts:36-41, personnel, funding-rounds, investments, grants, things, record-lookup.

Tier 7 — Build pipeline fail-open behavior

• YAML parse errors don't fail the build: apps/web/scripts/build-data.mjs:191-193,1309-1315. database.json is written first, then the script exits non-zero — too late.
• Wiki-server unreachable → empty data accepted: apps/web/scripts/lib/wiki-server-data.mjs:75-89,303-312. buildEditLogDateMap(), fetchAssessments(), fetchResourcesFromPG() return empty maps on error; build still succeeds.
• Snapshot staleness warned but accepted: 25-hour-old snapshots used if wiki-server is down.
• FactBase entity files have nondeterministic load order: readdirSync() in packages/factbase/data/things/. Different machines build different databases.

The Underlying Pattern

This is not 15 unrelated bugs. It's one engineering culture problem showing up in 15 places:

1. "Best-effort" is the default for things that are actually primary data (evidence, verdicts, claim links, FK resolution)
2. Validation is opt-in, bypass is one line away (skipEntityValidation=true, raw apiRequest(), as T casts)
3. Type annotations substitute for runtime checks ($type<string[]>(), as T, hand-written interfaces for external APIs)
4. Schema constraints are aspirational (no CHECK constraints, no unique indexes on natural keys, onDelete: set null everywhere)
5. Read-time fallback chains compensate for write-time corruption instead of fixing it at the source
6. Build pipeline fails open, so partial data ships to production silently

A single PR will not fix this. It needs a coordinated push.

Proposed Path Forward

Phase A — Stop the bleeding (week 1)

• Add a gate check rejecting dangerous patterns in new code: .catch(() => {}), .catch(() => console.warn(...)) on data writes, as any in route files, ?? null on FK columns, JSON.parse without subsequent Zod validation
• Make skipEntityValidation=true fail loudly without an explicit suppression comment
• Fix all Tier 0 issues — these are losing data right now
  • Reject inserts to FactBase facts where the value type requires a non-null numeric/text
  • Reject proposed_claims inserts with NULL entityId
  • Replace silent .catch() on evidence/verdict storage with proper error propagation
  • Route factbase-source-check.ts through storeSourceCheckEvidence() instead of bypassing it

Phase B — Tighten the schema (weeks 2-3, one PR per category)

• Add CHECK constraints to all 15 status fields (mechanical sweep)
• Add unique indexes on the 5+ natural keys missing them
• Replace onDelete: "set null" with restrict or cascade based on intent — every one needs an explicit decision
• Apply Zod validation to every JSONB column on both read and write
• Add the missing validateEntityRefs() calls to the 9 endpoints that don't have them

Phase C — Eliminate bypass paths (weeks 3-4, one PR per data type)

• Route ALL evidence creation through storeSourceCheckEvidence(); delete storeEvidenceApi direct callers
• Route ALL personnel sync through syncPersonnel() with validation always on
• Create storeClaimVerdict() wrapper; delete the raw apiRequest path
• Wrap dualWriteToSourceCheck() in a transaction; stop calling it fire-and-forget
• Fix the 5 batch sync claim-linking endpoints to surface partial failures

Phase D — Concurrency and pipeline (weeks 4-5)

• Fix incident dedup race using INSERT ... ON CONFLICT + unique index
• Fix ID allocation race in ids.ts to avoid burning sequence values
• Fix entity-sync slug reassignment to be atomic
• Make build pipeline fail closed: YAML parse errors fail the build before writing database.json; wiki-server unreachable fails the build (with explicit --allow-stale override); snapshots > 24h old fail the build
• Sort YAML file load order deterministically

Phase E — Catch the cached drift (week 5)

• Add triggers/sync for things.verdict when source verdicts change
• Add automatic recompute for research_area_scores on evaluation insert
• Add monitoring for cache-vs-source drift on the top 5 cached aggregates

Out of Scope

• The read-time enrichment patterns in Tier 6. Those should be fixed after the write-time gaps are closed; otherwise we're just rearranging the workarounds.
• Migration of historical bad data. That's a separate epic — first stop creating new bad data.

Success Criteria

• Zero .catch(() => {}) or equivalent on primary data writes (gate-enforced)
• Zero Zod schemas with z.record(z.unknown()) for JSONB columns that have known shapes
• All status fields have CHECK constraints
• All natural keys have unique indexes
• All sync endpoints validate FKs before insert
• Build fails closed when source data is incomplete or wiki-server is unreachable
• A re-run of this audit finds no new instances of the patterns above

Related Verification Subsystem Discussions

The verification / source-check subsystem is a major sub-area of this data integrity epic. The post-cleanup canonical surviving discussions in that cluster (per the 2026-04-08 discussions review) are:

• Rename source-check → sourcing: Architecture + Migration Plan #3993 — Rename source-check → sourcing: Architecture + Migration Plan (architectural rename umbrella; supersedes the dot-placement RFCs)
• Source-Check Quality Push: Defensive Pipeline + Data Cleanup #3958 — Source-Check Quality Push: Defensive Pipeline + Data Cleanup (active write-time enforcement plan; supersedes Verified-by-Default Data Enrichment — Incremental Rollout Plan #3875's claims-pipeline approach)
• Verification Dots Everywhere — Three-Tier Progressive Disclosure #3802 — Verification Dots Everywhere — Three-Tier Progressive Disclosure (canonical UI dots plan with concrete Phase 1 file list)
• Comprehensive Source-Check Expansion: TableBase, FactBase, and WikiBase Verification #3119 — Comprehensive Source-Check Expansion (narrowed to Phase 3: WikiBase verification, the only piece not absorbed elsewhere)
• Source Check System: Audit Findings & Fix Proposals #3558 — Source Check System: Audit Findings & Fix Proposals (standalone catalog of dashboard/evidence bugs)
• Verified-by-Default Data Pipeline: Requiring Verification at Write Time #3209 — Verified-by-Default Data Pipeline (vision anchor; concrete plan lives in Source-Check Quality Push: Defensive Pipeline + Data Cleanup #3958)
• Deterministic Wikidata Source-Check Matcher #3979 — Deterministic Wikidata Source-Check Matcher (narrow tactical, no overlap)

Adjacent (different scope but related):

• Resource/Sources System: Page Duplication, Missing Automation, and Pipeline Gaps #3499 — Resource/Sources System: Page Duplication, Missing Automation, and Pipeline Gaps (resource pipeline, not verification)
• Coverage (Data-Completeness) Dots Everywhere — Implementation Plan #3901 — Coverage (Data-Completeness) Dots (distinct concept from verification dots — data completeness, not source-checking)

Discussions closed as superseded on 2026-04-08: #3567, #3586, #3741, #3875, #3880, #3930.

How to Help

If you're picking up a sub-issue from this epic, read the underlying file first — the line numbers above are accurate as of investigation time but may have shifted. The pattern matters more than the exact line.

Tasks

• Tier 0 data integrity fixes (epic #4017) #4020 — Tier 0 data integrity fixes (epic Epic: Data integrity: systemic gaps across schema, validation, write paths #4017)

• Phase A continuation: gate check + skipEntityValidation hardening (epic #4017) #4023 — Phase A continuation: gate check + skipEntityValidation hardening (epic Epic: Data integrity: systemic gaps across schema, validation, write paths #4017)

• B1: Add validateEntityRefs to 8 missing sync endpoints (epic #4017) #4029 — B1: Add validateEntityRefs to 8 missing sync endpoints (epic Epic: Data integrity: systemic gaps across schema, validation, write paths #4017)

• B2: Add CHECK constraints on 14 status/enum columns (epic #4017) #4030 — B2: Add CHECK constraints on 14 status/enum columns (epic Epic: Data integrity: systemic gaps across schema, validation, write paths #4017)

• B3: Add unique indexes on natural keys — grants, funding_rounds, policy_stakeholders (epic #4017) #4032 — B3: Add unique indexes on natural keys — grants, funding_rounds, policy_stakeholders (epic Epic: Data integrity: systemic gaps across schema, validation, write paths #4017)

• B4: Tighten onDelete policy — 6 critical FKs from SET NULL to RESTRICT (epic #4017) #4033 — B4: Tighten onDelete policy — 6 critical FKs from SET NULL to RESTRICT (epic Epic: Data integrity: systemic gaps across schema, validation, write paths #4017)

• B5: Add typed Zod schema for verificationConfig JSONB column (epic #4017) #4034 — B5: Add typed Zod schema for verificationConfig JSONB column (epic Epic: Data integrity: systemic gaps across schema, validation, write paths #4017)

• C1-C3: Eliminate 6 bypass callers — route through typed wrappers (epic #4017) #4038 — C1-C3: Eliminate 6 bypass callers — route through typed wrappers (epic Epic: Data integrity: systemic gaps across schema, validation, write paths #4017)

• C4: Wrap dualWriteToSourceCheck in transaction + await (epic #4017) #4039 — C4: Wrap dualWriteToSourceCheck in transaction + await (epic Epic: Data integrity: systemic gaps across schema, validation, write paths #4017)

• C5: Surface claim-linking failures in 5 batch sync responses (epic #4017) #4040 — C5: Surface claim-linking failures in 5 batch sync responses (epic Epic: Data integrity: systemic gaps across schema, validation, write paths #4017)

• B3 (narrowed): Unique index on policy_stakeholders natural key (epic #4017) #4041 — B3 (narrowed): Unique index on policy_stakeholders natural key (epic Epic: Data integrity: systemic gaps across schema, validation, write paths #4017)

• D1+D2+D5: Incident dedup race, ID allocation, deterministic YAML load (epic #4017) #4043 — D1+D2+D5: Incident dedup race, ID allocation, deterministic YAML load (epic Epic: Data integrity: systemic gaps across schema, validation, write paths #4017)

• D4: Build pipeline fail-closed — abort on YAML errors, surface wiki-server failures (epic #4017) #4044 — D4: Build pipeline fail-closed — abort on YAML errors, surface wiki-server failures (epic Epic: Data integrity: systemic gaps across schema, validation, write paths #4017)

• D3: Fix entity slug reassignment race — bulk UPDATE replaces per-slug loop (epic #4017) #4045 — D3: Fix entity slug reassignment race — bulk UPDATE replaces per-slug loop (epic Epic: Data integrity: systemic gaps across schema, validation, write paths #4017)

[OAGr]

Branch: claude/tier0-data-integrity

Tier 0 fixes shipped in PR #4020 — covers FactBase numeric coercion, proposed_claims entityId validation, source check verdict storage error propagation, and factbase-source-check resource resolution.

[OAGr]

Branch: claude/phase-a-prevention

Phase A continuation shipped in PR #4023 — adds the validate-dangerous-patterns gate check (silent .catch, warn-only .catch on data writes, as any in routes, skipEntityValidation without reason) and hardens skipEntityValidation to require a justification at every layer (server logs, client wrapper throws, CLI flag enforces). Phase A is now complete.

[OAGr]

Branch: claude/b3-natural-key-uniqueness

Phase B sub-PRs 1-3 shipped: PR #4029 (B1: validateEntityRefs on 8 endpoints), PR #4030 (B2: CHECK constraints on 14 status fields), PR #4032 (B3: unique indexes on natural keys for grants/funding_rounds/policy_stakeholders). Phase B4 (onDelete policy) and B5 (JSONB Zod validation) remain.

[OAGr]

Branch: claude/b5-jsonb-validation

Phase B complete — all 5 sub-PRs open and CI green:

• B1: Add validateEntityRefs to 8 missing sync endpoints (epic #4017) #4029 B1: validateEntityRefs on 8 endpoints (0 failures, mergeable)
• B2: Add CHECK constraints on 14 status/enum columns (epic #4017) #4030 B2: CHECK constraints on 14 status fields (0 failures, mergeable)
• B3: Add unique indexes on natural keys — grants, funding_rounds, policy_stakeholders (epic #4017) #4032 B3: Unique indexes on grants/funding_rounds/policy_stakeholders (0 failures, mergeable)
• B4: Tighten onDelete policy — 6 critical FKs from SET NULL to RESTRICT (epic #4017) #4033 B4: onDelete policy — 6 FKs from SET NULL to RESTRICT (0 failures, mergeable)
• B5: Add typed Zod schema for verificationConfig JSONB column (epic #4017) #4034 B5: Typed Zod schema for verificationConfig JSONB (CI pending, mergeable)

Phase A+B now covers: Tier 0 active-corruption fixes, prevention gate, skipEntityValidation hardening, FK validation on all sync endpoints, CHECK constraints, natural-key uniqueness, FK delete policy, and JSONB schema for the highest-value column. Remaining epic items (Tiers 2-7) are tracked separately.

[OAGr]

Branch: claude/c5-claim-linking-errors

Phase C complete — all 3 sub-PRs open and ready for review:

• C1-C3: Eliminate 6 bypass callers — route through typed wrappers (epic #4017) #4038 C1-C3: Route 6 bypass callers through typed wrappers (evidence, claim verdicts, personnel)
• C4: Wrap dualWriteToSourceCheck in transaction + await (epic #4017) #4039 C4: Wrap dualWriteToSourceCheck in Drizzle transaction + await (was fire-and-forget two-table write)
• C5: Surface claim-linking failures in 5 batch sync responses (epic #4017) #4040 C5: Surface claim-linking failures in 5 batch sync responses (was silent claimsLinked: 0)

Phase A (prevention) + Phase B (schema) + Phase C (bypass elimination) are now complete. Remaining: Phase D (concurrency/pipeline) and Phase E (cache drift monitoring).

[OAGr]

Branch: claude/d3-slug-reassignment-race

Phase D complete — all 3 sub-PRs open, reviewed, and CI green:

• D1+D2+D5: Incident dedup race, ID allocation, deterministic YAML load (epic #4017) #4043 D1+D2+D5: Incident dedup race (atomic upsert + unique index), ID allocation simplified (INSERT-first), deterministic YAML load order (.sort())
• D4: Build pipeline fail-closed — abort on YAML errors, surface wiki-server failures (epic #4017) #4044 D4: Build pipeline fail-closed (YAML error check before writing database.json, wiki-server failures surfaced in full-build mode)
• D3: Fix entity slug reassignment race — bulk UPDATE replaces per-slug loop (epic #4017) #4045 D3: Slug reassignment race (N individual UPDATEs → 1 bulk unnest UPDATE)

Epic status: Phases A through D are now complete. 13 PRs shipped across 4 phases:

• Phase A: Tier 0 data integrity fixes (epic #4017) #4020, Phase A continuation: gate check + skipEntityValidation hardening (epic #4017) #4023 (merged)
• Phase B: B1: Add validateEntityRefs to 8 missing sync endpoints (epic #4017) #4029, B2: Add CHECK constraints on 14 status/enum columns (epic #4017) #4030, B4: Tighten onDelete policy — 6 critical FKs from SET NULL to RESTRICT (epic #4017) #4033, B5: Add typed Zod schema for verificationConfig JSONB column (epic #4017) #4034, B3 (narrowed): Unique index on policy_stakeholders natural key (epic #4017) #4041 (open, ready)
• Phase C: C1-C3: Eliminate 6 bypass callers — route through typed wrappers (epic #4017) #4038, C4: Wrap dualWriteToSourceCheck in transaction + await (epic #4017) #4039, C5: Surface claim-linking failures in 5 batch sync responses (epic #4017) #4040 (open, ready)
• Phase D: D1+D2+D5: Incident dedup race, ID allocation, deterministic YAML load (epic #4017) #4043, D4: Build pipeline fail-closed — abort on YAML errors, surface wiki-server failures (epic #4017) #4044, D3: Fix entity slug reassignment race — bulk UPDATE replaces per-slug loop (epic #4017) #4045 (open, ready)

Remaining: Phase E (cache drift monitoring) — lowest urgency, monitoring infrastructure.