Canonicalize a table

🚨 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).

Make <schema>.<table> (token <token>) fully conform to the platform standard. Read ../db-change/TOOLKIT.md (live signatures, gotchas) + ../db-change/SKILL.md first. Project: brsgrqvjdzwihsvnfqkf. Order matters — do the steps in sequence.

This is the ONE recipe for getting any table onto the base standard ("retrofit", "base retrofit", "Wave 3", "bring <X> onto the platform base entity"), preserving existing behavior exactly: take it all the way to certified, or stop at the transition-state floor (zero FAIL + only the legacy WARNs — see the certify bar below). Relocating a table → db-move-table-schema; retiring one → db-deprecate-table.

🛑 Before you write one line — obey db-change's PRIME DIRECTIVE (§top of ../db-change/SKILL.md)

The app is DOWN until fully canonical; there is no safe half-state. So:

• Meet EVERY canonical requirement for this table in ONE pass across ALL layers (SQL → db/generate.py → Python usages → FE types → Next.js usages → both repos → commit → push). Never start a table you won't finish this pass. Never leave one layer repointed and another not — that broken middle is the whole disaster.
• First, look at the real table AND its data (row counts — live or test?). Then write a SHORT bullet checklist of what canonical requires here. Not a report.
• "Behavior preserved exactly" is NOT a license to skip a canonical requirement. If preserving today's behavior would keep a non-canonical structure, that is a conflict you RAISE, not resolve on your own.
• 🔴 NO bespoke *_versions table survives, and you do NOT get to keep one silently. Canonical versioning = history.row_versions via the _history trigger + the generic version_* RPCs. A per-entity *_versions table (code_file_versions, note_versions, …) is legacy drift to RETIRE — migrate its rows into history.row_versions, repoint promote_version→version_restore + every reader, then deprecate it. BUT versioning is not right for every table — so if you think this table should keep its table, or shouldn't be versioned at all, that is a QUESTION you ask the human BEFORE starting (state your view + the fact, e.g. row count), get an explicit yes/no, and only then proceed. Leaving the old version table because you decided it was safer — with no ask — is the exact failure that has cost this project weeks. (Setting is_versioned=false to silence the gate while the bespoke table lives on is that same failure in disguise.)
• Any other requirement you want to deviate from (skip a drop, keep an old column, defer a repoint) → same rule: ask up front, explicit yes/no, no clear answer = stop and ask again. Decisions are made BEFORE you start, then you execute 100%.

📼 Retiring a bespoke *_versions table

The one-pass recipe (backfill → capture → repoint the legacy version RPCs → deprecated), its gotchas, and the two live CRACKS in the canonical version system. Entity carries a per-entity *_versions table (or the Prime Directive versioning question above was answered "retire it") → read retire-versions-table.md before Step 5.

⚡ Field realities — read these first, they each cost a round-trip (verified 2026-07-05)

0. This is where a DB-wide finding on an uncertified table should land. Under canonical-first triage, a problem that appears only on uncertified tables is fixed by converting the table here — never by patching the old shape.

1. A table is usually FAR more canonical than "not started." A prior "canonicalize + move" migration often already added created_by/visibility/deleted_at + canonical RLS + _stamp_actor/_touch_row, and left only: the kill-list columns (user_id/is_public/is_deleted) undropped + the base FK constraints missing. Run iam.verify_canonical(s,t,tok) for EVERY involved table BEFORE writing anything — it prints the exact remaining gap. Don't assume; the delta is often tiny.

   • The gate keys on the TOKEN, never the table name — and the token is often singular while the table is plural (code_file ⟷ code_files, code_folder ⟷ code_file_folders). Passing the table name as the token yields a signature TRIO of FALSE fails — entity_registered ("no entity_types row for token=…"), policy_uses_has_access ("std_select does not call has_access('…')"), sharing_token ("registry resource_type=… != token=…"). If you see exactly those three, you passed the wrong token — get it from platform.entity_types/audit.summary, not by pluralizing the table name. (The admin verify UI now warns on this mismatch.)

2. canonical_certify reads a CACHED snapshot — audit.refresh() is MANDATORY before it's truthful. After you CREATE OR REPLACE a broken dependent function or fix anything, canonical_certify_ok KEEPS reporting the old broken_dependent_fn/finding until you cd aidream && uv run python scripts/audit_refresh.py (session mode, never port 6543). A "still broken" you already fixed is the #1 false failure — refresh, then re-check.

3. A broken dependent function blocks certify even when its breakage is unrelated to your table. (promote_version referenced a deprecated note_versions and blocked code_file certify.) Fixing it — e.g. neutralizing a dead branch that points at a retired table — is part of "done." Find them with audit.table_impact(s,t) (currently_broken=true).

4. Components (is_component=true) still need organization_id + version + _touch_row + the base FKs. The gate FAILs base_organization_id/base_version/trg_touch_row/base_*_fk on components too. TWO traps: (a) the moment you ADD organization_id to a component, base_org_not_null flips SKIP→FAIL (column now exists, nullable) → SET NOT NULL after backfilling from the parent; (b) a soft_delete WARN blocks certify → add deleted_at + UPDATE entity_types SET has_soft_delete=true.

5. is_versioned=true with no _history trigger is now a hard trg_version_capture FAIL. If the table versions via a bespoke *_versions table (or doesn't version at all), the honest, behavior-preserving fix is UPDATE platform.entity_types SET is_versioned=false — it makes the check SKIP without adding redundant double-capture. Only attach _history if you genuinely want it on history.row_versions.

6. organization_id NOT NULL and every write names its organization — no trigger fills one. public._stamp_org_default no longer exists (verified live 2026-09-26) and must not come back: a database trigger choosing an organization is the substitution CLAUDE.md forbids (common-docs/systems/architecture/database/projects/no-db-assigned-org/PLAN.md). An org-forgetting write fails loudly with 23502 at the caller — that is correct.

7. Service-role (Python) inserts MUST set created_by explicitly — _stamp_actor can't fill it without auth.uid()/app.user_id. A supabase-py service-client insert that dropped user_id must now pass created_by=<owner> and organization_id=<owning org> explicitly. (A JWT/RLS-authed FE write is stamped automatically — drop the owner column there.)

8. Dropping a column/table is a clean cut, and finding MORE consumers than you expected is NORMAL — grind through them, NEVER revert. Before you drop, run the full Consumer discovery protocol (../db-change/SKILL.md §🔎): grep the name in EVERY format (snake · Pascal · camel · plural · manager-instance), open every hit including lazy/mid-file imports and generated hubs (package_db_wiring.py, auto_config*.py, db_requirements.py manifests, database.types.ts), and fix raw-SQL strings + getattr(row,"field",default) (the silent-column trap) — tsc/db/generate.py/dead-relations are the LAST-step backstop, not the search. When the blast radius turns out bigger than "this table is fairly new" implied (a second feature module, a whole router, a manifest), that is the migration working — repoint it, do not re-add the table (that panic-revert cost hours on skill.project/resource). The only stop is a genuine human decision (Prime Directive rule 4), flagged with the drop still in place.

Step 0 — Classify the variant and the level

The level first — platform.entity_types.data_class, by the law common-docs/policies/access-ladder.md: every table starts at organization (canonical examples: Agent, Sales deal); public only when it is meant to be seen outside the organization without being shared; confidential / private only with Arman's approval. A component carries no level of its own — it has its parent's. The variant below is the RLS shape, not the level.

• entity — independent business object (its own owner/visibility). Most tables.
• component — child whose access IS its parent's, full depth (versions, events, line-items). RLS defers to a composition parent; no own visibility.
• ledger — append-only org-scoped log. No user writes, no version/soft-delete.
• restricted — secret-bearing system tables (AI vendor config — ai.endpoint/ai.api/ai.offering). std_select = owner OR super-admin only — deliberately no iam.has_access path, because has_access would leak viewer access through the global-readable system org.
• system — catalog/shared-definition tables. Requires a visibility column. std_select = public-visibility OR owner OR has_access. Check existing columns/policies/triggers first: \d-style via information_schema.columns, pg_policy, pg_trigger (see TOOLKIT.md §5–6).

Step 1 — Base columns (additive, idempotent)

public.* table → use the driver (adds org/created_by/updated_by/updated_at/version, backfills, attaches _touch_row+_stamp_actor):

select platform.retrofit_entity('<table>','<token>',
  '<personal|parent|keep>', '<owner_col>',           -- e.g. 'personal','user_id'
  '<parent_table_or_null>','<parent_fk_or_null>',     -- for 'parent' strategy
  '<legacy_updated_at_trigger_or_null>');             -- dropped so backfill doesn't bump updated_at

Ordering (verified): retrofit_entity backfills created_by + org before attaching the triggers, so its backfills don't churn version/updated_at. The visibility/deleted_at columns it does NOT add — so add visibility and run the is_public→visibility / is_deleted→deleted_at normalization before calling retrofit_entity (triggers not yet attached). Doing them after leaves freshly-migrated rows at version=2 with updated_at=now() (looks edited when it was only migrated). Schema-homed table (NOT public) → retrofit_entity does NOT work; hand-roll:

alter table <schema>.<table> add column if not exists organization_id uuid;
alter table <schema>.<table> add column if not exists created_by uuid;       -- if a non-uuid created_by exists, rename it to created_by_kind first
alter table <schema>.<table> add column if not exists updated_by uuid;
alter table <schema>.<table> add column if not exists updated_at timestamptz not null default now();
alter table <schema>.<table> add column if not exists version int not null default 1;
alter table <schema>.<table> add column if not exists deleted_at timestamptz;
alter table <schema>.<table> add column if not exists metadata jsonb not null default '{}';
alter table <schema>.<table> add column if not exists visibility platform.visibility not null default '<TIER>';  -- entity/shareable only
-- <TIER> is a DECISION, never a reflex (db-rules §6a): 'personal' ONLY for an individual
-- person's own artifacts (chats, DMs — almost nothing qualifies); org work product → 'internal';
-- scraped/derived/catalog content → 'public'. Defaulting to the lowest tier without a
-- personal-artifact justification is the documented anti-pattern that locked out whole orgs.
-- normalize legacy → canonical (guard each on IS NULL so re-runs are no-ops)
update <schema>.<table> set created_by = coalesce(created_by, <owner_col>) where created_by is null;
update <schema>.<table> set visibility = 'public' where <is_public_col> is true and visibility <> 'public';
update <schema>.<table> set deleted_at = <deleted_ts_or_now> where <is_deleted_col> is true and deleted_at is null;
-- backfill org: copy it from the parent, or name the owning organization explicitly per row set.
-- Never pick one from the is_personal flag (being deleted — organizations are equal, access-ladder law).
-- drop legacy updated_at trigger, attach the shared triggers
drop trigger if exists <legacy_updated_at_trigger> on <schema>.<table>;
drop trigger if exists _touch_row on <schema>.<table>;
drop trigger if exists _stamp_actor on <schema>.<table>;
create trigger _touch_row  before insert or update on <schema>.<table> for each row execute function platform._touch_row();
create trigger _stamp_actor before insert or update on <schema>.<table> for each row execute function platform._stamp_actor();

Verify 0 null org and 0 null created_by (where an owner existed) before continuing. Backend-written tables: ensure the engine writes created_by (service role doesn't stamp it — TOOLKIT.md §5).

Step 1.5 — Base FK constraints + organization_id NOT NULL (REQUIRED — nothing else adds these)

retrofit_entity adds the columns but NOT the FK constraints or the NOT-NULL, and the gate checks all four. Skip this and verify_canonical returns four hard FAILs — base_org_fk, base_created_by_fk, base_updated_by_fk, base_org_not_null (verified live 2026-07-05). Run after org is backfilled to 0 nulls:

alter table <schema>.<table> alter column organization_id set not null;
alter table <schema>.<table> add constraint <table>_org_fk foreign key (organization_id) references iam.organizations(id);
alter table <schema>.<table> add constraint <table>_created_by_fk foreign key (created_by) references auth.users(id);
alter table <schema>.<table> add constraint <table>_updated_by_fk foreign key (updated_by) references auth.users(id);

Idempotent guard for re-runs: wrap each in do $$ begin if not exists (select 1 from pg_constraint where conname='<table>_org_fk') then … end if; end $$; or check information_schema.table_constraints first. SET NOT NULL fails if ANY org is null — fix Step 1's backfill, never weaken this. (FK targets: organization_id → iam.organizations(id), created_by/updated_by → auth.users(id) — these exact targets are what the gate matches.)

Safety assert (put it at the end of the Steps 1–1.5 migration so the whole thing rolls back on any miss):

do $$ begin
  if exists (select 1 from <schema>.<table> where organization_id is null) then raise exception 'null organization_id remains'; end if;
  if not exists (select 1 from pg_trigger where tgrelid='<schema>.<table>'::regclass and tgname='_touch_row') then raise exception '_touch_row not attached'; end if;
  if not exists (select 1 from pg_trigger where tgrelid='<schema>.<table>'::regclass and tgname='_stamp_actor') then raise exception '_stamp_actor not attached'; end if;
end $$;

Step 2 — Register the entity (idempotent)

insert into platform.entity_types (token, schema_name, table_name, label, default_visibility, is_component, is_versioned, is_active)
select '<token>','<schema>','<table>','<Label>','<TIER>', <is_component:false|true>, <is_versioned:true|false>, true  -- <TIER>: same decision as Step 1 (db-rules §6a)
where not exists (select 1 from platform.entity_types where token='<token>');

If component, add the composition edge (required before apply_rls):

insert into platform.entity_relationships (child_type, parent_type, fk_column, kind)
select '<token>','<parent_token>','<fk_col>','composition'
where not exists (select 1 from platform.entity_relationships where child_type='<token>' and kind='composition');

(Optional kind='containment' edges add read-cascade for visibility>=internal — additive.)

Step 3 — Sharing registry (entity is user-shareable)

The registry lives in platform.shareable_resource_registry — it moved out of public in the 2026 reorg (verified live 2026-07-05; a public. insert errors with table-not-found).

insert into platform.shareable_resource_registry
  (resource_type, schema_name, table_name, id_column, owner_column, is_public_column, display_label, url_path_template, rls_uses_has_permission)
values ('<token>','<schema>','<table>','id','created_by','visibility','<Label>','/<path>/{id}',true);

resource_type MUST equal <token> (mismatch → grants silently ignored).

Step 4 — Canonical RLS (the only policy authority)

Inventory existing policies first (apply_rls drops them all); fold anything load-bearing into the standard model. Then:

select iam.apply_rls('<schema>','<table>','<token>','<entity|component|ledger|restricted|system>');

(entity requires created_by + organization_id present; component requires the composition edge; restricted is owner-or-super-admin-only with no has_access path (secret-bearing system tables); system requires a visibility column (catalog/shared-definition tables) — see Step 0. There is no join variant — TOOLKIT.md §2. A pure M2M join table (a_id+b_id, no lifecycle) is best collapsed into platform.associations (db-rules §3); if it must stay a table, policy it as component (composition edge) or hand-write org-gated policies like platform.associations — never invent a join variant.)

Step 5 — Versioning

• Table already versions (history/audit today) → put it on the central system: attach the capture trigger.
• Table does NOT version today → ASK the PM (the user) whether to add versioning at canonicalization. Do not silently add or skip.

-- when versioned: set is_versioned=true in entity_types AND attach the trigger (registration alone captures nothing)
create trigger _history after insert or update or delete on <schema>.<table>
for each row execute function platform._version_capture('<token>');

Confirm the current month's history.row_versions partition exists (capture starts only when _history is attached — pre-existing rows are NOT auto-backfilled). Backfilling old versions (retiring a per-feature *_versions table): history.row_versions is monthly RANGE-partitioned, so first pre-create a partition for EVERY month in the source's date range (min(created_at)..max) or the INSERT fails with a no-partition error (verified — note_versions spanned 7 months, only 2 partitions existed). Map row_id/version/occurred_at/actor_id; stash extra fields under reserved _* keys in row_data; verify count(history)=count(source). (Optional _gc → platform._gc_entity_associations('<token>') cleans association edges on delete.)

Step 6 — Replace the feature's bespoke subsystems with the platform satellites

If the feature has its OWN comments / associations-relationships / categories / activity-log / favorites-pins, migrate the rows into the platform tables keyed by (entity_type='<token>', entity_id) and deprecate the old tables (use db-deprecate-table). Behavior must be identical afterward — verify the UI shows the same comments/tags/relationships. (platform.comments, platform.associations, platform.categories, platform.activity_log, platform.user_entity_state — shapes in TOOLKIT.md §1.) Permissions/shares → migrate into iam.permissions (moved from public in the reorg; TOOLKIT.md §3).

Step 6.5 — Drop the legacy columns (reach zero-WARN)

In downtime, once consumers are repointed, drop user_id/owner_id, is_public, is_deleted, shared_with to clear the legacy WARNs. Each drop has couplings — check first (all bit us on notes):

• Backfill with triggers OFF: wrap the is_deleted→deleted_at / is_public→visibility UPDATE in alter table … disable trigger user; … ; enable trigger user;, else _touch_row churns version/updated_at and the version/sync/ingest triggers fire on every touched row.
• Functions/triggers that read the column (the silent killers) — use audit.table_impact first, then a text scan: select * from audit.table_impact('<schema>','<table>'); returns every dependent function with dependency (precise|text), currently_broken, and the exact referenced_columns[] it touches — the blast radius, purpose-built for this. THEN belt-and-suspenders with a raw scan of ALL functions (prokind in ('f','p') to skip aggregates) for BOTH (from|join|update)\s+<table> AND new\.<col>/old\.<col>, because a FROM/JOIN scan MISSES trigger functions that read NEW.<col> (notify/ingest/sync/version triggers) and SECURITY DEFINER functions aren't caught by tsc. Verified casualties of skipping this: _notify_auto_ingest_note (NEW.is_deleted → every note write failed silently) and get_user_dashboard_metrics (is_deleted → dashboard broke). Patch each to the canonical column before dropping.
• RLS on OTHER tables: a child policy may reference this column via subquery (… WHERE notes.user_id = auth.uid()). DROP COLUMN fails 2BP01 and lists them — repoint each to created_by; never blind-CASCADE (it silently drops the policy).
• Indexes: DROP COLUMN auto-drops indexes that include it. Recreate the useful composites on created_by (owner/sync/folder lookups) first.
• Prove safe first: count(*) filter (where created_by is distinct from <owner_col>)=0, is_public/shared_with empty — then the drop loses nothing.

Step 7 — Verify (the two-gate acceptance)

There are two gates and you must clear BOTH. verify_canonical checks the table's own structure; canonical_certify also checks that no dependent function is broken — it is the "done" gate per db-rules FEATURE.md §11 (nothing is done until canonical_certify_ok is true).

select * from iam.verify_canonical('<schema>','<table>','<token>');    -- read EVERY row
select iam.verify_canonical_ok('<schema>','<table>','<token>');         -- gate 1: no structural FAIL
select * from iam.canonical_certify('<schema>','<table>','<token>');    -- blocking rows = FAIL/WARN + any broken dependent fn
select iam.canonical_certify_ok('<schema>','<table>','<token>');        -- gate 2 (THE done gate): true = done

Bar (verified live 2026-07-05): these must all reach PASS — base_org_not_null, base_org_fk, base_created_by_fk, base_updated_by_fk (Step 1.5), col_visibility, soft_delete, timestamps, plus the registration/RLS/policy checks. Skip Step 1.5 and you get four base_*_fk/not_null FAILs. The three legacy WARNs — legacy_owner_col (user_id/owner_id), legacy_is_public, legacy_is_deleted — clear only when those columns are dropped (Step 6.5). Full canonical = both gates true with zero FAIL + zero WARN, achievable in one pass when you do the drops (proven live on a probe + on notes+note_folders); if the drops must wait, the transition-state floor is zero FAIL + only those legacy WARNs. Don't report "canonical" with any FAIL or a col_visibility/soft_delete WARN showing, or with canonical_certify_ok=false.

Refresh the audit store so the table shows up certified in the platform-wide hit list (drives audit.summary; loud + non-blocking in release.sh):

-- run from a shell (session mode, never 6543): cd aidream && uv run python scripts/audit_refresh.py
select fails, warns, certified from audit.summary where token='<token>';

Then impersonate a normal user and confirm they still read their own rows (RLS didn't hide data):

select set_config('request.jwt.claims', json_build_object('sub','<a real user uuid>')::text, true);
select count(*) from <schema>.<table>;   -- expect their visible rows, not 0

Step 8 — Cross-repo finalize + document

db-change SOP: pnpm db-types → update all usages (new columns, .schema() if needed, RPC names) → pnpm sync-types (fix TS); python db/generate.py → update usages + package_integration.py → python db/detect_applied.py → python run.py clean boot. Ledger the migration. Update the feature's FEATURE.md + add a line to the Done log in ../common-docs/operations/db-changeover-board.md. Commit + push main on both repos.

NEVER

• apply_rls before org is backfilled (0 nulls) or before entity_types (+ composition edge for components) exists.
• Skip Step 1.5 — retrofit_entity adds the base columns but NOT the FK constraints or organization_id NOT NULL; without them the table carries four permanent base_* FAILs no matter how clean it looks.
• Trust verify_canonical_ok alone — WARNs are unfinished canonicalization, and it does NOT check dependent functions. iam.canonical_certify_ok=true is the only "done" signal (db-rules FEATURE.md §11).
• Assume is_versioned=true captures history — it doesn't without the _history trigger.
• Leave the feature reading its old comments/associations table after migrating the rows — repoint the code, then deprecate the table.
• Change behavior. Canonicalization preserves what the user sees; if anything differs, it's a bug.
• Touch out-of-scope litter as if it were legacy tagging: sch_* (scheduler), wf_*/workflow, code_*, wc_* — their project_id/task_id columns are real FKs, NOT association litter to migrate. Leave them.