Deprecate a table

Once a table is here it is dead to every database check: findings on deprecated tables are never reported or fixed — canonical-first triage.

Goal: get the table offline and reversible (SET SCHEMA deprecated, never DROP), then erase every reference. Getting it offline is priority #1; reference cleanup follows and must not block the move. Read ../db-change/TOOLKIT.md + ../db-change/SKILL.md first. Project: brsgrqvjdzwihsvnfqkf.

Step 1 — Discover every reference (this is the real work)

🚨 THE ORACLE FIRST — audit.relation_usage(schema, table) (built 2026-08-20, deprecated-sweep postmortem). One call returns EVERY DB-side mention: inbound/outbound FKs, dependent views/matviews recursively (each dependent view joins the closure, so its consumers are found too), triggers on it, every function/procedure body (qualified vs bare matches labeled), RLS policy expressions on other tables, trigger string-arguments (_version_capture tokens, org-inherit args), cron jobs, realtime publications, and the entity/shareable/deprecation registries.

select * from audit.relation_usage('<schema>', '<table>') order by via, kind, ref;

The transitive hop is the whole point: single-hop name searches missed deprecated.prompt_builtins → public.context_menu_unified_view → get_ssr_shell_data (a LIVE session hydrator) in the 2026-08-20 sweep — the table name appears nowhere in the function, only the view's name does. Never substitute a plain pg_proc ilike scan for this call. function_body_bare rows can be same-named objects in another schema — read each; function_body_qualified rows are certain.

Two checks the oracle does not cover:

-- is anything still actually reading it? (needs pg_stat_statements)
select calls, query from pg_stat_statements where query ~* '\m<table>\M' order by calls desc limit 20;

Code (both repos): grep <table> for .from('<table>'), .schema(...).from('<table>'), generated type names, Python model/manager names (aidream/db/models*.py, db/managers/**), package wiring in aidream/package_integration.py, and raw SQL strings (from <table>, SELECT 1 FROM <table> ACL joins in .py/.sql). Also grep for every ref the oracle returned — a dependent view or RPC it surfaces can be called from code by ITS name with the table name appearing nowhere in the repo.

A 0-row table can still be LIVE. Verified: note_shares had 0 rows but was joined by RAG-search ACL (matrx-rag/search.py: SELECT 1 FROM public.note_shares) — deprecating it turned an empty result into a missing-relation error, breaking search. Row count ≠ usage. The query string is what breaks; grep it before you move. (Recovery: alter table deprecated.<t> set schema public — reversible, which is why we never DROP.)

Step 2 — Confirm it's truly dead

If reads remain: repoint or delete those consumers if quick; otherwise deprecate now (reversible) and track the remaining cleanup in the Open items queue of ../common-docs/operations/db-changeover-board.md (the tracker that replaced the deleted docs/db_rebuild/CHANGEOVER_PROGRESS.md). Do not block the move on a long repoint — but never deprecate a table with live, load-bearing reads you haven't accounted for.

Step 3 — Resolve FKs in BOTH directions

Inbound (tables that point AT this one): a cross-schema FK keeps working after the move, so the move won't break — but a dead table shouldn't be referenced. Drop or repoint inbound FK constraints that shouldn't exist. If an inbound FK represents real data you can't yet sever, that table isn't dead — reconsider.

🚨 Outbound (this table pointing at LIVE tables) — the step that gets forgotten, and it breaks live pages. SET SCHEMA carries every outbound FK along, so the retired table keeps advertising itself in pg_constraint as a child of live tables. Any catalog-walking function then discovers it and queries it as the signed-in user, who has no USAGE on deprecated → 42501 permission denied for schema deprecated, and the whole call dies. That is exactly how every /projects/<id> page broke on 2026-08-13: get_project_references walked the FKs into projects.projects and hit two retired flashcard tables. Always finish the move with the idempotent sweep — it is safe and expected to re-run:

do $$ declare r record; begin
  for r in select conname, conrelid::regclass as from_table from pg_constraint
    where contype='f' and conrelid::regclass::text like 'deprecated.%'
      and confrelid::regclass::text not like 'deprecated.%'
  loop execute format('alter table %s drop constraint %I', r.from_table, r.conname); end loop;
end $$;

migrations/drop_graveyard_to_live_fks.sql is this sweep. It was run ONCE (2026-07-28) and treated as done; every table retired afterwards re-created the problem — 78 such FKs had accumulated by 2026-08-13. It is not a one-time migration, it is a post-condition of every deprecated move.

Step 4 — Deactivate the registration FIRST (enforced, 2026-08-12)

platform._enforce_entity_is_table now ERRORs when an ACTIVE entity_types row ends up pointing at deprecated — the DDL-sync trigger repoints schema_name during your SET SCHEMA, so moving a still-active registered table into deprecated fails with a check_violation. Deactivate before you move (this is the guard doing its job, not a bug):

update platform.entity_types set is_active=false where token='<token>';   -- or delete the row

Step 5 — Move it (idempotent, verify no data lost)

do $$ begin
  if to_regclass('public.<table>') is not null then
    execute 'alter table public.<table> set schema deprecated';
  end if;
end $$;
select count(*) from deprecated.<table>;   -- equals the pre-move count

Step 6 — De-register the rest

Remove the remaining platform footprint so nothing resolves to it:

delete from platform.entity_relationships where child_type='<token>' or parent_type='<token>';
delete from platform.shareable_resource_registry where table_name='<table>';

Leave satellite rows (associations/comments/…) keyed by the token in place unless they're now orphaned — sweep separately; they're harmless and reversible.

Step 7 — Cross-repo cleanup + finalize

deprecated IS in the db-types schema list, so the table still appears under the deprecated schema in FE types — that's fine; the point is to delete every code usage. Then run the finalize SOP (db-change/SKILL.md): pnpm db-types → remove FE usages → pnpm sync-types (fix TS); python db/generate.py → remove aidream usages + package_integration.py entry → python db/detect_applied.py → python run.py clean boot. Record the migration in the ledger. Commit + push main on both repos.

Clean cut — no silent shim (SKILL db-change → THE CUT)

SET SCHEMA deprecated makes public.<t> vanish → stale refs error (correct). Register it in scripts/dead-relations.json + platform.deprecated_relations and clear pnpm check:dead-relations (it finds the raw-SQL/Python/comment refs tsc misses). Never leave a readable old table or a compat view as a "fallback"; if it can't be moved yet, tripwire it (platform.deprecate_relation — data preserved, reads/writes RAISE).

NEVER

• DROP TABLE — SET SCHEMA deprecated only (DROP is a separate PITR-gated step).
• Leave the old name readable as a silent fallback — move it (vanishes → errors) or tripwire it (RAISES). Never a passthrough view.
• Deprecate a table that still has live reads you haven't repointed or tracked.
• Leave a generated type / Python model / package_integration.py reference pointing at the moved table.