DB Change — the transition SOP (read first)

🚨 Never remove, narrow, supersede or suppress platform_admin_read — it is our own admin database access (the admin dashboard reads every row through it). Check before and after any policy change: ../common-docs/policies/our-own-admin-database-access.md (the query must return 0).

⚠️ FIRST: is anyone USING the system right now?

This SOP is written for a scheduled outage ("the app is ALREADY DOWN"). That assumption is load-bearing for everything below it — especially rule 5's "no compat views, the old name must VANISH." Ask before you start: is the app live, and could a human be mid-session?

• Down / no traffic → follow this SOP exactly as written. Cut clean, no safety nets.
• LIVE traffic → rule 5 does NOT apply to a rename, and this is not a judgment call. The old name keeps working until the new code is deployed (rename + CREATE VIEW <old> AS SELECT * FROM <new>, drop the alias once the running SHA carries the new code — or invert the order and rename last). See common-docs/policies/database-changeover-doctrine.md §8a-2.

Why this block exists (2026-08-17): a live rename of platform.expertise_pack → platform.rulebook landed while production still ran the old code. The DDL→deploy gap is tens of minutes, and Arman was inside it dictating expertise. Every tool call died on 42P01; 11 finished expert rules were lost and were recoverable only by reading raw tool-call traces out of the database. A 42P01 is invisible to the human it happens to — the agent above it reports success in prose. Nothing in this file told that agent to wait, because this file assumes the lights are off.

Is this change fixing something broken? If not, it waits.

A wanted change that nothing is failing on — a redundant column, a dead enum value, a name that lies — is not applied when noticed. It goes on the cross-repo DB change queue with its consumer census, and one session applies the batch end-to-end in a window (Arman, 2026-09-10). Every change pays the full multi-layer sweep below; batching pays it once.

Came here from a DB-wide check? Triage the finding by table first — canonical-first triage: deprecated is dead, public is a queue, and a problem that shows only on uncertified tables (while certified ones are clean) is not a fix — the work is canonicalizing that table (db-canonicalize-table).

🛑 THE PRIME DIRECTIVE — read this before you touch anything, or you will hurt the project

This migration is past the point of no return. The app is ALREADY DOWN and stays down until the system is 100% canonical. There is no safe partial state, nothing to protect, and no going back — the only exit is forward, completely. Everything below exists because agents keep half-doing the job and leaving the app in the broken middle. Do not be one of them.

1. The canonical list is the spec and the authority — not your judgment. There is a defined target state (the roadmap: where each table starts, its new schema, its new columns, user_id→created_by, org handling, M2M→associations, where versioning goes). You do not get to decide a canonical requirement is optional, "nice to keep for now," or "better my way." You meet the requirements.

2. ONE change lands in EVERY layer, in ONE pass — SQL + generated Python + Python usages + Next.js types + Next.js usages + every repo — TOGETHER, before the lights come back on. If a name changes everywhere at once, turning the app on is as if it never changed — clean. If it changes in one layer and not the others, the app is broken. Touching one layer but not all of them ("the circle") is the single thing that has kept this app down for weeks. It is the cardinal sin. Touch nothing, or touch it ALL — never in between.

3. Decide EVERYTHING before you start. Never begin a table you will not finish 100% this pass. Sequence: (a) read the canonical list, (b) look at the ACTUAL table — schema, columns, triggers, dependents, and the real data (row counts; is it live or just test rows?), (c) write a SHORT bullet checklist of every requirement to reach canonical (not a report — a checklist). Then execute the entire checklist without stopping. Starting with open decisions, or starting and silently skipping a requirement, is forbidden.

4. A canonical requirement that seems wrong for this table is a QUESTION FOR THE HUMAN, made BEFORE you start — never a call you make alone and never a thing you skip quietly. Not every rule fits every table (versioning, for one, is not right for every table). When you think a requirement shouldn't apply, or you're tempted to keep an old structure: STATE your opinion out loud, give the fact behind it, and ASK. "code_file has a bespoke code_file_versions table (20 rows, all test) — canonicalize it onto history.row_versions, or drop versioning for this table entirely? I lean X because Y." Yes → do it that way. No → follow the rule. No clear/blocking answer → STOP and ask again; do not start. Your opinion is wanted — the human almost always has a fact you don't (that's how a weak "keep it just in case" flips to "those are throwaway rows, kill it"). Keeping the opinion to yourself and acting on the assumption is the exact move that destroys the codebase.

5. NO safety nets. NO "just in case." NO leaving the old way. No compat views, no leftover legacy tables "for now," no old columns kept beside new ones, no dormant fallbacks, no "I'll migrate it later." The old name/column/table must VANISH so a stale reference errors loudly and gets fixed this pass. A safety net here is not safety — it silently splits data across two shapes and is the bug. The ONE exception is the live-traffic rename alias in the block at the top of this file — a deliberately temporary view, dropped the moment the new code is deployed, which is the opposite of "leaving the old way."

6. SHIP IT — committing and pushing IS the job, not a decision to agonize over. During a scheduled outage the win condition is: fully canonical + all layers repointed + committed + pushed + lights on. Do not pause to ask "is it OK to release?" — the app is down; the only way to lose is to sit on finished work. Commit, push, deploy, in the same pass.

7. DISCOVERING MORE THAN YOU EXPECTED IS NORMAL — FINISH, NEVER REVERT. Halfway through you WILL find consumers you didn't know about: a second parallel feature module, a generated wiring hub, a raw-SQL string, a lazy import buried in a function. That is not a signal that "this was a mistake, undo it." It is the migration working — you found a consumer, now repoint it. Re-adding a dropped table / un-deprecating / reverting a rename to escape a bigger-than-expected blast radius is THE cardinal panic-failure (it happened on skill.project/skill.resource: the drop was correct, the revert was cowardice, and it cost hours). The moment you're tempted to revert, do the opposite: run the discovery protocol below, list every remaining consumer, and grind through them. The ONLY thing that ever stops you is a genuine human decision (rule 4) — and that is a loud flag with the drop still in place, never a silent revert. "Broken for five minutes while every layer changes" is the migration; a revert is the failure.

One-line test before you stop: If the lights came on right now, would every layer agree and the app work? If no, you are not done — and you do not stop, hand off, revert, or "leave it for the next pass." You finish.

---

🔎 Consumer discovery — find EVERY usage BEFORE you cut (this is where agents keep failing)

A dropped/renamed table or field breaks silently in the places the compiler can't see. type-check / tsc / db/generate.py are a backstop, NOT the search — they miss raw-SQL strings, getattr(row,"field",default) (returns the default forever — a silent wrong value), dynamic .from("<str>"), and anything in a generated file that only errors at runtime. Before you drop or rename ANYTHING, enumerate consumers yourself. The recurring misses and how to not repeat them:

1. Search the SYMBOL in EVERY name format, and inspect each hit — do not trust an import scan. A field/table/model appears as foo_bar · FooBar · fooBar · foo_bars/FooBars (plural) · the manager instance (foo_bars_manager_instance) · the wire/label alias. Import-graph tools only see top-of-file import lines and completely miss mid-file / lazy / inside-a-function imports (this is the #1 leak). So grep the raw text across the whole tree and open every hit:

   rg -n 'foo_bar|FooBar|fooBar|foo_bars|FooBars|foo_bars_manager|"is_public"|\bis_public\b|isPublic'
   

   For each hit ask: is this the same table/field, and does it read/write a column I'm changing? A wrong-format miss (e.g. SklResource when you searched skill_resource) is exactly how consumers slip through.
2. OPEN the auto-generated consumer hubs — they are NOT boilerplate to skip. These list tables by name and break generation or wiring when stale: aidream aidream/_generated/package_db_wiring.py, db/helpers/auto_config*.py, every packages/*/db/db_requirements.py manifest (a dropped table left in a manifest hard-fails db/generate.py — this bit skill.resource). FE types/database.types.ts; the entity vocabulary in @ai-matrx/associations (pnpm check:entity-types). Grep them for the name; fix the manifest, then regenerate.
3. Grep raw-SQL strings for the table AND field names — FROM <table>, .from("<table>"), .eq("<field>", filter_items(<field>=, getattr(row,"<field>", raw UPDATE/INSERT/SELECT literals. The ORM/type system never sees these. getattr(row,"is_public",False) is the worst: it silently returns the default when the column is gone — grep getattr( for every changed field.
4. Run the repo discovery tools — they help but DON'T replace the multi-format grep above:
   • aidream: python db/table_refs.py <table> [token] (git-grep report, buckets hits incl. package_integration.py) · python db/schema_analysis/check_schema.py --refresh (live DB vs yaml/models/managers/raw-SQL string literals/imports — the import check does see lazy/mid-file imports; the raw-SQL check uses an allow-list of roots so it can miss excluded dirs) · python docs/database/generate_handwritten_sql_inventory.py (every runtime raw-SQL site) · python scripts/audit_orm_getattr.py (the getattr silent-column trap) · python scripts/check_package_wiring.py (manifest↔generated drift).
   • matrx-frontend: node scripts/db-table-refs.mjs <table> [token] · pnpm tsx scripts/schema-check/check-schema.ts (live DB vs generated types + consumers) · pnpm check:dead-relations (bare .from("<old>") / public.<old> / Database[...]["<old>"]) · pnpm type-check (typed refs only — the backstop, run LAST).
5. The workflow: grep every name-variant → open every hit + every generated hub → fix raw-SQL strings → repoint typed refs → THEN type-check / generate.py / check:dead-relations to confirm you missed nothing. Tools last, not first. "The compiler will catch it" is how the silent misses ship.

Add the retired relation to scripts/dead-relations.json + db/check_dead_relations.py BEFORE repointing — the guard then becomes your live checklist of what's still pointing at the old name.

---

Structural changes to Matrx Main (brsgrqvjdzwihsvnfqkf) during scheduled downtime. 🚨 DDL goes through pnpm db:apply migrations/<file>.sql — the ONE apply path, downtime included (CLAUDE.md § Migrations): the whole file in one transaction, and the applier writes the public._schema_migrations row with the SHA-256 of the bytes it executed. Never paste DDL into the database tool by hand, and never write that ledger row yourself — on that path a statement you leave out of a payload stays missing while pnpm check:migrations reads green. The file is therefore not a convenience: it is the unit that gets applied (a file still changes nothing until applied + verified live). Write it whole and apply it in one call; chunking is what prolongs the outage.

Before any change, read TOOLKIT.md (verified live signatures, registry shapes, constants, gotchas) and the rulebook db-rules/FEATURE.md (what a canonical table IS). Where a table belongs: changeover doctrine §7 (one feature, one schema; public keeps no tables) + PLATFORM-CONVENTIONS §E (every live schema's purpose, verified 2026-08-25); re-check live with obj_description(oid,'pg_namespace') — the workbench comment carries the workbench (user-constructed materials) vs projects (coordination) split. Where any doc and the live DB disagree, the DB wins — re-verify with execute_sql before betting a migration on it.

Propose first — for multi-table, data-migrating, or consumer-facing changes

A change is rarely one table. Before executing anything risky (a cluster, a data migration, a schema move, a retire/drop), do the homework and fill PROPOSAL_TEMPLATE.md — scope the whole cluster, quantify the repoint cost across both repos (+ extend/local) with the db-table-refs helpers, list the decisions with recommendations — save it to docs/db_rebuild/proposals/<slug>.md, and get a go before mutating. A single-column tweak or an obviously-safe additive step doesn't need one; anything that could lose data or break a production consumer does.

THE LAW: zero data loss, always

1. Never DROP TABLE (or DROP COLUMN with data) during the transition. Retirement = ALTER TABLE … SET SCHEMA deprecated (reversible). Hard DROP is a separate, later, PITR-gated step.
2. Additive-first, cut over, then retire. Add new structure → backfill → dual-write/mirror if needed → repoint consumers → verify counts match → only then retire the old.
3. Getting a table offline is reversible and is the first priority; resolving every dependency is required but must not block the move — deprecate it, then finish the cleanup. (DROP is what's gated, not the schema move.)
4. Verify live, not on faith. After every DDL, execute_sql to confirm the object exists and SELECT count(*) to confirm no rows were lost. Compare pre/post counts.
5. Loud recovery. Any bridge/backfill/mirror you add must scream (RAISE / log) when it fires on data it shouldn't — a silent fallback hides the bug it's papering over.

THE CUT — no silent shim (read twice; this is the #1 source of disasters)

When a table is MOVED or RETIRED, the old name MUST stop working — abruptly. AI agents do not reliably catch lingering references, so a "nice fallback" old table is how reads/writes silently split across two tables and burn a day to debug. A clean cut + 15 minutes of repointing beats a silent shim every time.

• Default = make the old name vanish. SET SCHEMA workbench / SET SCHEMA deprecated / rename — the data is preserved at the NEW location, but public.<old> no longer resolves, so every stale ref errors loudly (PostgREST 404 in the browser console = red; a raised exception in server logs = red). That IS the desired behavior.
• NEVER leave a compat VIEW or a still-readable old table that silently passes through. That is the forbidden shim. (Reconciles with Law #1: "data preserved, reversible" ≠ "old name still readable." Deprecated/move preserves data AND kills the old name.)
• If a table genuinely can't move yet (consumers can't all be cut in the window), do NOT leave it readable — install a tripwire: select platform.deprecate_relation('public','<t>','<new.ref>','<reason>') renames the data aside (zero loss) and replaces the old name with a view + INSTEAD-OF triggers that RAISE on any read or write with a message naming the new location (TOOLKIT.md §9). A shim that errors loudly is acceptable; one that silently works is not.
• 🚨 THE EXCEPTION, AND IT IS THE ONE THAT BIT US: a RENAME of a table that LIVE USERS ARE USING RIGHT NOW. This whole section assumes a downtime window — no traffic, so a hard cut costs a red log line. Outside a window it costs a person their work. The old name must keep resolving until the new code is DEPLOYED (CREATE VIEW old_name AS SELECT * FROM new_name, dropped once the running SHA has the new code), or the rename goes LAST, after the code is live. A "loud" 42P01 is not loud to the human it happens to: the agent above it reports success in prose and the content simply never existed. 2026-08-17: platform.expertise_pack → platform.rulebook landed ahead of the deployed code while an Expert was an hour into dictating his SEO method; five calls died, one carrying 11 finished expert rules (8,773 chars), recovered only because chat.tool_trace happens to keep args. Full rule: common-docs/policies/database-changeover-doctrine.md §8a-2.
• Light up the terminal RED until refs are gone. Every move/retire MUST: (1) add the relation to scripts/dead-relations.json + platform.deprecated_relations, and (2) leave pnpm check:dead-relations green. 🚨 Nothing runs it for you — there is no pre-commit hook and no CI in this repo; YOU run pnpm check:dead-relations (or :strict) before you call the move done. It scans for bare .from("<old>"), public.<old>, and Database["public"][…]["<old>"] and screams until every one is repointed. Add the manifest entry before repointing so the guard becomes your checklist. (aidream has the parallel db/check_dead_relations.py.)

Pick the change (route here)

Task	Skill
Take a table offline / retire it (no longer used)	db-deprecate-table
Relocate a table to another schema, references intact	db-move-table-schema
Bring a table/feature onto the platform standard — base cols + FKs, RLS, registry, satellites, versioning (a.k.a. "retrofit" / "base retrofit" / "Wave 3"; take it to certified or stop at the transition floor)	db-canonicalize-table
Drop / merge / modify-logic / find stragglers / anything else	playbooks.md
Sharing cascade / containment rules / new association edge shape ("share X and its contents come along")	TOOLKIT.md §3 (association_types + reachability) + docs/db_changes/REACHABILITY-ROLLOUT.md; manage rules via /administration/relationships, never raw SQL

Constants (full table in TOOLKIT.md §0)

• Project: Matrx Main · project_id brsgrqvjdzwihsvnfqkf.
• System org ("Matrx System"): 39c38960-d30c-4840-b0c1-c9960de95582 (ownerless-row fallback).
• Exposed-schema trap: pnpm db-types only pulls public, context, files, workflow, workspace, app, skill, tool, agent, chat, ai, deprecated. A FE-read table in any other schema needs its schema added to the db-types --schema list + PostgREST exposure, or the FE gets no types and 404s.

Cross-repo finalize (run for EVERY change — TOOLKIT.md §8 has detail)

1. DB — write migrations/<name>.sql (idempotent) with -- draft: <you> <what is unfinished> as its FIRST line (the release sweep ships anything in migrations/ every 30 minutes; a draft is held — JUDGMENT.md §1c) and remove that line only when the file is done, then pnpm db:apply migrations/<name>.sql — the ONE apply path: whole file, one transaction, and it writes the public._schema_migrations row itself with the SHA-256 of the bytes it executed. Never hand-apply through the MCP and never write that row yourself (CLAUDE.md § Migrations). Then verify live with a read.
2. Frontend — pnpm db-types; update every usage; pnpm sync-types; fix all TS.
3. aidream — python db/generate.py; new schema → db/matrx_orm.yaml (additional_schemas + generate block); sub-package table → aidream/package_integration.py; python db/detect_applied.py; update usages; python run.py → clean boot.
4. matrx-extend / matrx-local — update if referenced; never block production.
5. Commit + push main on matrx-frontend and aidream.

Other playbooks (drop, merge, modify logic, stragglers, anything else)

Drop a table, merge two tables, modify logic (function / RPC / trigger / policy), find stragglers, or anything else (split, partition, rename, backfill-only) → read playbooks.md.

Document as you go

Add one line to the Done log of the DB changeover board (doctrine §8 step 5) and update the matching FEATURE.md for a canonicalized feature — what changed, counts, what's still open. A change that lives only in a chat log will be redone or broken by the next agent.