Phase D risk reduction: split code from SQL for wiki_pages PK swap

[OAGr]

Follow-up to #1497 (Epic: Convert wiki_pages.id from Text Slug to Integer PK).

Phases A+B are merged and deployed. Phase C verification is complete (integer columns backfilled by manual Phase A SQL + Phase B dual-write). This discussion tracks the lower-risk approach to Phase D.

---

The Problem with the Original Phase D Plan

The original plan merges code changes and SQL migration in a single PR that must deploy atomically:

• If code deploys before migration runs → breakage
• DROP COLUMN is irreversible
• One big transaction holds locks across 12+ tables
• Raw SQL JOINs (explore.ts, integrity.ts) must be fixed at exactly the right moment

---

Revised Plan: 3 Independent PRs

PR A — Pre-fix raw SQL JOINs (zero risk)

Three places use wp.id in JOINs that will break after Phase D:

-- explore.ts
LEFT JOIN entities e ON wp.id = e.id     -- breaks when wp.id becomes integer

-- integrity.ts
WHERE c.entity_id = wp.id               -- breaks when wp.id becomes integer

Fix: change to wp.slug — works right now (slug exists from Phase A) and works after Phase D. Ship as a standalone PR with zero migration dependency. Verifies the fix in production before anything risky.

Risk: Zero. Fully reversible. No migration needed.

---

PR B — Switch all reads from text to integer columns (low risk)

page_id_int already exists on all FK tables (Phase A). Switch every SELECT/WHERE/JOIN in all 12 route files from the text page_id column to the integer page_id_int column. Both columns still exist at deploy time, so this is safe.

Files: pages.ts, citations.ts, edit-logs.ts, claims.ts, hallucination-risk.ts, sessions.ts, auto-update-runs.ts, auto-update-news.ts, artifacts.ts, references.ts, resources.ts, links.ts

Also update schema.ts to make page_id_int non-nullable (since it should be fully populated).

Deploy and verify for at least a day before proceeding to PR C.

Risk: Low. Both columns still exist. Reversible by reverting the PR.

---

PR C — SQL only: rename and drop text columns (medium risk)

After PR B is deployed and verified, the text page_id columns are no longer read by any code. PR C is SQL-only — no code changes needed at deploy time. This eliminates the "code and SQL must deploy together" constraint.

Migration steps (one transaction):

1. Run pre-validation script — fail loudly if any integer column has NULLs
2. Drop all 11 FK constraints (text)
3. Drop composite PKs on session_pages, resource_citations
4. DROP old text columns (page_id, source_id, target_id, wiki_pages.id, wiki_pages.numeric_id)
5. RENAME integer columns (page_id_int → page_id, integer_id → id, etc.)
6. Add integer PK on wiki_pages.id
7. Recreate FK constraints (integer)
8. Recreate composite PKs

Additional safety measures:

• Rename-before-drop: rename text columns to _deprecated_page_id first, verify a day later, then drop
• Pre-written rollback script: tested against a DB clone before running on production
• Staging test: dump production, restore to staging, run migration, run full test suite

Risk: Medium — but isolated to pure SQL, no code changes at deploy time.

---

Pre-Validation Queries (must all return 0 before PR C)

SELECT COUNT(*) FROM wiki_pages WHERE integer_id IS NULL;
SELECT COUNT(*) FROM wiki_pages WHERE slug IS NULL;
SELECT COUNT(*) FROM edit_logs WHERE page_id_int IS NULL AND page_id IS NOT NULL;
SELECT COUNT(*) FROM hallucination_risk_snapshots WHERE page_id_int IS NULL AND page_id IS NOT NULL;
SELECT COUNT(*) FROM session_pages WHERE page_id_int IS NULL AND page_id IS NOT NULL;
SELECT COUNT(*) FROM auto_update_results WHERE page_id_int IS NULL AND page_id IS NOT NULL;
SELECT COUNT(*) FROM claim_page_references WHERE page_id_int IS NULL AND page_id IS NOT NULL;
SELECT COUNT(*) FROM resource_citations WHERE page_id_int IS NULL AND page_id IS NOT NULL;
SELECT COUNT(*) FROM page_improve_runs WHERE page_id_int IS NULL AND page_id IS NOT NULL;
SELECT COUNT(*) FROM page_citations WHERE page_id_int IS NULL AND page_id IS NOT NULL;
SELECT COUNT(*) FROM citation_quotes WHERE page_id_int IS NULL AND page_id IS NOT NULL;
SELECT COUNT(*) FROM citation_accuracy_snapshots WHERE page_id_int IS NULL AND page_id IS NOT NULL;
SELECT COUNT(*) FROM page_links WHERE source_id_int IS NULL AND source_id IS NOT NULL;
SELECT COUNT(*) FROM page_links WHERE target_id_int IS NULL AND target_id IS NOT NULL;

---

Status

• Phase A+B: Schema expansion + dual-write merged and deployed
• Phase C: Integer columns backfilled and verified
• PR A: Pre-fix raw SQL JOINs in explore.ts + integrity.ts
• PR B: Switch all reads to integer columns
• PR C: SQL rename/drop migration