<!-- SYNCED COPY — do not edit here. Canonical: common-docs/skills/drift-audit/SKILL.md This file is distributed to every consuming repo by common-docs/meta/scripts/sync_skills.py. Edit the canonical, run the sync, and commit each repo. Edits made here are overwritten and lost. -->

drift-audit — refresh the numbers, then act

<!-- rules:stamp -->

Rules from common-docs/systems/architecture/database/FEATURE.md — edit there, never here (python3 common-docs/meta/scripts/one_home.py --write).

1. One token ↔ one table. Every entity is registered in platform.entity_types; reference it by token (+ id), never schema.table.
2. Every entity table carries its variant's base contract (§2, §6d-3). No exemptions — only classifications.
3. Relationships are rows in platform.associations (§3). A new x_y junction table is a bug.
4. Growing vocabularies are FKs into platform.categories (category_id), never enums or CHECK arrays.
5. Access is RLS generated by iam.apply_rls, keyed on the token (§6d). Never hand-write policies. Every RLS table keeps platform_admin_read — never remove, narrow or suppress it (our own admin database access).
6. Nothing is done until iam.canonical_certify_ok is true (§11).
7. Never DROP. Retire to the deprecated schema; migrated rows carry metadata.legacy_table + metadata.legacy_id; log in platform.deprecated_relations.
8. Never hand-build tables or hand-write version/soft-delete/association logic — use platform.provision / platform.create_entity_table and the generic RPCs.
9. No NULL org, no database-assigned org. organization_id is NOT NULL and explicit on every write; the database never chooses, creates, copies, inherits or defaults one (§2).
10. Configuration is a primitive. Any setting, limit, threshold, cadence, mode or default is a platform.feature_knob row (+ platform.knob_override), never a *_config/*_settings/*_preferences table, settings blob or per-feature resolver. Not knobs: billing allowances (billing.plan_limit), consent, statutory rules, true columns of one entity. Contract: feature knobs.
11. Test on the one live database as admin@admin.com. No test or branch databases by default; the data is test data and admin's own records are disposable. The nightly clone and any branch (which costs money, so it needs his yes) are only for rehearsing destructive migrations or jobs that lock live for 10+ minutes; never for tests, previews or checks (his account and a few others hold real-but-test data). Any clone cost change (size, copies) is his call. For a CMS or other database with long locks, apply in the 01:00-04:00 PT window, then prove it live on admin-owned rows and restore them.
12. Apply database changes yourself, directly, the same session. Through the Supabase MCP, with a ledger row so nothing re-applies (§9): no migration ceremony, no waiting lane, no runner gate. Never hand Arman a migration or a command, and never ask permission for a change that improves the system; stop only for real data destruction or a lock wait that would freeze live users (measure it; 30 s lock_timeout). Report each change as an improvement with a plain example of what a user gains, never as a request. No .sql file and no migration file: prove it, apply it with the MCP, and the ledger row is the record. He does not need to watch it. Mechanics below the block.
13. Only long jobs wait for night. A job that runs 10+ minutes or locks live writes waits for 01:00–04:00 PT; short DDL runs now under lock_timeout. No job holds a long lock on the live database; bulk work runs in bounded batches under lock_timeout/statement_timeout, and anything routine and heavy is scheduled 01:00–04:00 PT (95% of activity is US time zones, so 8 AM Pacific is not overnight). The window never applies to anything done with Arman working: do it now, live, directly through the MCP.
14. Empty is not junk. Outside graveyard, an empty or unused table, schema or function goes to Arman for review, never dropped on your own; an unbuilt design goes on a finish list, never a drop list, and "finish or remove" is never offered. The measure is schema reduction (tables and functions dropped; data in graveyard tables means nothing). Find candidates by asking whether the table has a matching high-quality feature in the app, never by row count (mandates had 371 rows and were junk).
15. Every drop gets a text sweep first. Search all function bodies, views, policies, defaults, cron and registries plus every repo (bare names and fragments) and dry-run plpgsql_check (DROP in a DO block, compile, RAISE to roll back); buried text references are what breaks. A graveyard table being written to: find out why, decide whether it should be, and clean up the writer; never drop under a live writer.
16. Fix the code, not the data. A bad row is reported and tracked, never repaired by SQL, API calls, scripts or the admin dashboard; data changes only through the product's own clicks and keys, except a cleanup he explicitly asks for (junk-data cleanup, 2026-09-30: deletions still get one GO from him first). A status value the UI does not show clearly (a "draft" with no big, color-coded indicator) is meaningless: fix the UI first. Every subagent brief says no data fixes via SQL, API, scripts or admin tools, report data issues; verification data changes are headless UI clicks as the test accounts.
17. Anything a person creates is durable on the server from day one, through ONE shared primitive. Browser-only storage (localStorage, Redux-only) for something a person makes is a defect, never a caveat; "saved in localStorage" from a lane is reported as a defect, and briefs say "durable server-side via the canonical primitive". Build or reuse ONE shared primitive (block or kind state keyed to the record) so every new interactive thing gets persistence and agent visibility for free.

<!-- rules:stamp-end -->

Invocation: /drift-audit. The deliverable is never just numbers: it is numbers → diff → chips fired → decision list. Ending at a report with no chips is an incomplete run — that gap is exactly why this skill exists (Arman, 2026-08-21).

0. Ground rules

• Live project ONLY: brsgrqvjdzwihsvnfqkf (db.matrxserver.com). Never access the retired project txzxabzwovsujtloxrus, including for reads or historical comparison; use archived evidence for historical comparisons instead.
• Verify against live state, never against a brief. Audit briefs go stale and get re-issued: the 8-finding "Canonical Architecture Drift Audit" brief is from 2026-08-15 and was fully adjudicated the same day. If a finding doesn't reproduce live, say so — that is a result, not a failure.
• Canon = db-rules FEATURE.md; operating doctrine = database-changeover-doctrine (measurement traps §4: certified-vs-all universes, is_component vs rls_variant, cached certify, counts are not work lists).
• The sentinels below measure the whole database on purpose. When one of them surfaces a specific table, say which kind of table it is before reporting it — canonical-first triage: deprecated is never mentioned, and a problem on a certified or foundation table outranks the same problem on an uncertified one.

1. Measure (read-only, via Supabase MCP)

Run these against live; record each number with its query framing so the next run diffs apples to apples:

#	Sentinel	Query sketch
1	Certification	count(*) filter (where certified) / total from audit.summary (run cd aidream && uv run python scripts/audit_refresh.py (session mode, never port 6543) first if stale)
2	Component created_by policies	policies on rls_variant='component' active tokens whose qual/with_check mention created_by — must be 0
3	Nullable org (registered)	active entity_types rows whose table's organization_id is nullable (~34 known; diff the SET, not the count)
4	Event triggers	count(*) from pg_event_trigger (expect 5 platform + Supabase built-ins; any restore silently drops them)
5	pg_cron	count(*) from cron.job (restore also drops these)
6	ddl_guard_log	total, unacknowledged, and unacknowledged hand_rolled_entity rows + new object_refs since last run
7	Versioned-without-capture	active is_versioned + version_store='history' tokens with no trigger whose tgfoid resolves to platform._version_capture (never match by trigger name)
8	Grant holes	tables with authenticated policies but no SELECT grant — check role_table_grants ∪ role_column_grants (column-grant designs are deliberate: files/secrets/docproc)
9	RLS-off / zero-policy	pg_class.relrowsecurity false, or RLS on with 0 policies but live SIUD grants (the D184 class)
10	Reachability	count(distinct refreshed_at) (1 = recent full rebuild; several = incremental upkeep visible); run platform.reachability_drift() if it exists

2. Diff against the last recorded state

Sources, in order: the standing report artifact (update it in place — ask Arman for the link or use the artifact list; 2026-08-21 baseline: https://claude.ai/code/artifact/15a5737b-075a-4307-81a0-2e26c2e3cbc0), the archived 08-15 adjudication, matrx-frontend/FOUND_DEFECTS.md (D146/D182/D184/D232 family), and the session-memory note project_drift_audit_adjudication. For every worsened number, find the cause (new tables? a lost trigger? a restore?) before reporting it. Republish the same artifact — never a new URL (if it was deleted, publish fresh and repoint this line + the memory note in the same session).

3. Act — this is the point

Split every open item three ways:

1. Chip it (default): anything a focused session can finish end-to-end — a guard, a gate, a bounded sweep, a ≤3-table fix. Fire spawn_task chips immediately. Every chip prompt must be self-contained: live project id + never-touch-retired rule, the canon and doctrine pointers, the exact end state, the non-breaking constraints, the full-change contract (doctrine §8a: DB + ORM + types + consumers + commit/push), and the retreat-cycle rule (§3a: finish forward).
2. Split it: too big for one session → chip the first bounded piece with a self-contained brief; never chip "do the whole backlog".
3. Arman's list: openness calls, machinery ratifications, schedule approvals, anything db-rules marks Arman-only. Direct question + your recommendation each (his standing format — never a doc pointer). Do NOT chip these.

4. Report

One message: headline numbers with deltas, what improved, what worsened and why, chips fired (titles), decisions awaiting Arman. Update the artifact, update the project_drift_audit_adjudication memory, log the run in common-docs if any doc changed.

Known standing items (groom this list each run — remove what's done)

As of 2026-08-21 evening: the first enforcement wave is DONE and verified live (birth-gate ERROR + provisioner marker, ddl_guard_log ack contract + readers + full triage → D232, blocking ratchets + strict org-backstop gate, column-grant guard, component-created_by blocking check, canon residue trio fixed, 10/11 pg_cron jobs restored, reachability_drift() built, four rules in both CLAUDE.mds). All six Arman rulings are DECIDED — NO NULL ORG ever (platform-wide, enforced at every layer) · no machinery exemptions, fix classification instead (per-variant column contract) · batch.* is user-visible (canonical model) · reachability check daily + self-heal. Second-wave chips in flight: seo backstop pair + ack, last 9 versioned-without-capture, D232 residue, component created_by neutralization, NULL-org annihilation + screamers, batch access, drift schedule. DONE 2026-08-21: variant-contract reclassification — iam.verify_canonical v4 (db-rules §6d-3) makes the base contract per-variant, so "machinery exemption" is retired as a concept for base columns; FAILs 799→445, certified 242→281, 42 tables fixed / 0 regressed, and the seo+runtime families are FAIL-free. Read §6d-3 before reading any base-column finding. Next run: verify those landed, then groom this list down.