Schema Design Runbook

The mandatory process for designing or modifying any database table in Vrida. Follow it for every new module, new table, or column change.

This document exists so schema quality stays consistent without re-explaining the standards each time. It captures the process, the column standards, the audit checklist, and the recurring bug classes we keep hitting.

This runbook describes actual practice, reconciled against the 4 modules actually locked so far (platform, identity, shared, multi_loc) as of 2026-07-06. Where an earlier version of this document described a step none of the 4 builds followed, it has been corrected to match what was actually done, or the gap has been logged to docs/open-items/OPEN_ITEMS.md rather than silently asserted as done. See that reconciliation pass for details.

Cross-references:

docs/database/SCHEMA_CONVENTIONS.md — the rules (naming, RLS pattern, soft-delete pattern, money storage, etc.).
docs/database/schema_docs/<module>.md — the definitions (authoritative tables, columns, constraints, one file per module). There is no single project-wide SCHEMA.md — each locked module gets its own file in this directory.
docs/modules/module_spec/<module>.md — the module spec (purpose, ownership, layer/dependencies, capabilities, service contract, data-flow, cross-module seams). Distinct from schema_docs/<module>.md — every real lock has produced both.
docs/decisions/PROJECT_DECISIONS.md — locked product / business / schema decisions, one numbered entry per module lock. This is also where module-level design rationale lives (see Section 4, Item P).
docs/ai/AI_CAPABILITY_PLANE.md — the canonical AI capability contract. Part D (15-question module-walk) is the capability-discovery half of every module's AI Capability Plane pass (Section 2.2.1); Parts A/B/C are what Part D's questions trigger and reference. docs/ai/AI_CAPABILITY_PLANE_READING.md is the plain-language companion for understanding it — the canonical .md is the contract to build against.
docs/ai/AI_CAPABILITY_GAPS.md — competitive gap analysis (2026-07-06) against the Plane; motivated Section 2.2.2's question 7 (agent/tenant memory) and the memory-poisoning bridge note in 2.2.1. Most of its 11 gaps belong to not-yet-built API/testing/runtime layers, not this runbook — see docs/open-items/OPEN_ITEMS.md for their triggers.
docs/open-items/OPEN_ITEMS.md — every deferral, gap, and known limitation, each with an explicit trigger. Updated at every lock (Section 6).

0. The Governing Rule — Autonomy First

Vrida's goal is to operate autonomously: AI agents and automated processes do the work, with minimal intervention from real people, wherever autonomous operation is possible. This is not one consideration among several — it is the most important schema rule in this document, ranking above convenience, above precedent, above "that's how the last module did it." It applies to every table in every module. It must not be skipped, deferred, or treated as optional for any module — including modules that feel purely administrative or reference-only.

This rule is enforced by one AI design pass with two halves, run together (full mechanics in Section 2.2) — not two separate steps, and not something either half can satisfy alone:

Capability discovery — run the module through docs/ai/AI_CAPABILITY_PLANE.md Part D's 15-question module-walk (capture targets, routing, maintenance, error-prevention, negative-space, decision-support, autonomy boundary, evidence sources, reconciliation, rollback, adversarial surface, offline behavior, channel sync, lifecycle, capture modality). This is the canonical source for what this module's AI capabilities are and what autonomy boundary applies to each action (D7: may-act-alone / draft-only / needs-approval / never) — not a step to guess at independently.
Schema translation — for every capability Part D says applies, make sure the schema actually carries what it needs. This is the six-question checklist below: it asks how the schema supports what discovery found, not what the module's capabilities are (that's answered by step 1).

The six schema-translation questions (checked at every audit as part of Item P):

What work here should be autonomous? — Answered by step 1 (Part D), not asked independently here. The module-walk determines this action by action; D7 records the per-action autonomy boundary.
Can an agent BE the actor? Every action-attribution column (created_by, completed_by, approved_by, *_by_user_id, and similar) must resolve to identity.actor — the polymorphic root that covers human, AI agent, and service account alike — never assume the actor is human by FK'ing straight to identity.identity_user. This must be consistent across every module, not decided ad hoc per table.
Autonomy metadata — does the table need a provenance flag (data_source, automation_source, created_autonomously), a confidence_score, or an is_verified flag, so an autonomously-created row can be told apart from a human-created one? (shared.plant.data_source / is_verified is the existing precedent.)
Human-in-the-loop seam — informed directly by D7's per-action answer: the cases Part D says genuinely need a person must be explicit and narrow (requires_human_review, flagged_for_review, approval_required), so that everything else is free to flow autonomously. This generalizes identity's existing "detect-and-flag, never-block" SoD pattern — the default is autonomous flow; a human checkpoint is the deliberate exception, not the default.
Agent-safe state machines — any status/lifecycle a table drives must be safely operable by an agent, not just by a human clicking through a UI: transitions enumerated and CHECK-enforced, operations idempotent (re-applying the same transition doesn't corrupt state), and reversible wherever the business logic allows it. A one-way-door transition that's fine for a careful human is a real risk for an autonomous caller — flag it explicitly if reversibility isn't possible.
Decision provenance — when an agent acts autonomously, can the schema capture why — what inputs, what confidence, what rule fired — so the action can be audited or reversed later if it turns out wrong? An outcome log ("X happened") is not the same as a decision log ("X happened because Y, with confidence Z").

Also honor these AI_CAPABILITY_PLANE.md Part C governing rules where schema-relevant (they aren't new work — they're why questions 2, 4, 5, and 6 above matter):

C5 (human accountable, AI attributable) → question 2's actor-attribution and question 6's provenance are how the schema keeps this true.
C7 (explainability before autonomy — sequencing law) → question 6's provenance must exist before an action is allowed to run autonomously, not be bolted on after.
C8 (financial actions are independently controlled and reversible) → has two schema-relevant halves, not one: the "independently controlled" half (an independent approval/SoD check on money movement) is question 4's approval_required seam; the "reversible" half is question 5's reversibility requirement. Both are non-negotiable for any table touching money — don't satisfy only one and call C8 done.

The valid outcomes of this pass are, per table: (a) concrete fields/flags added to support one or more of the schema-translation questions, informed by Part D's capability/autonomy-boundary findings, or (b) an explicit, recorded decision that this data is human-only, with a stated reason. "We didn't think about it" is never a valid outcome. Outcome (b) is a legitimate design choice — not every table needs every autonomy feature — but it must be a deliberate, justified exception recorded in the module's PROJECT_DECISIONS.md entry or module_spec/<module>.md, not a silent default.

This rule was formalized 2026-07-06, after platform, identity, shared, and multi_loc were already locked. All 4 have been retro-audited against the schema-translation half only (flag-and-log only — locked schemas were not reopened); see docs/open-items/OPEN_ITEMS.md for the findings and the module-by-module gap list. None of the 4 has ever been run through Part D's capability-discovery walk — that gap is logged separately in OPEN_ITEMS.md and is not resolved by the schema-translation retro-audit. Every module locked from this point forward runs the complete two-half pass for real, before Section 4's audit, not retroactively.

0.5. The Governing Rule — Preserve v1 By Default (Design-Phase Integrity)

Why this exists. Multiple audits in this project found real, expensive problems that all trace to the same root cause: a design proposal showed the DESTINATION (the v2 table list) but never showed the justified DIFF from v1 — so nobody could see what was actually being given up until much later. Concretely: pos's 19→9 table consolidation hid column-level erosions (cart-hold/resume, B2B fields, a loyalty-points snapshot, price-override tracking, hardware-pairing config, a cash-entry vocabulary narrowed from 6 values to 3) inside tables that still read as "BUILT." crm.customer_tax_certificate shipped with a live bug — a tax exemption could be recorded status='active' with no verifier and no timestamp at all. pos.sale_payment shipped with a live bug — a gift-card/store-credit tender was accepted with zero balance validation. pricing.price_rule silently dropped rule_kind/name, and price_list_assignment silently dropped its own uniqueness guarantee, with no record anywhere that a reduction had even happened. Separately, a 2026-07-06 OPEN_ITEMS completeness audit found 9 confirmed cases where a module's own docs claimed something was "deferred" or "logged to OPEN_ITEMS" and no such row actually existed. Every one of these was invisible at design review — caught only by a later, expensive audit, forcing an already-locked module to reopen, rebuild, and re-verify. The rules below exist to make all of this visible and user-approved AT DESIGN TIME, not discovered afterward.

THE GOVERNING PRINCIPLE: preserve v1 by default. Every reduction from v1 — a merged table, a dropped column, a narrowed enum, a removed index or CHECK, a lost uniqueness guarantee — must be individually justified and its cost disclosed at design time, and approved by the user before any table is built. "Fewer tables is cleaner" is not, by itself, a valid reason to consolidate. This ranks alongside Section 0's autonomy-first rule as a non-negotiable, document-wide requirement — it applies to every module, including ones that feel like an obvious simplification.

Enforced by two mechanisms, both mandatory, both non-skippable:

The 4-block Design-Phase Integrity requirement — authored as part of every design proposal, before the table list (Section 2.3.6, below). A proposal missing any of the 4 blocks is incomplete and cannot proceed to Audit (2.4) or Build (2.6).
Section 6's lock-gate verification (item 15) — confirms the blocks' claims against the actual files (OPEN_ITEMS.md, PROJECT_DECISIONS.md, module_spec/<module>.md) by direct grep, never against a build report's own say-so.

Trigger Quality Bar — applies to every OPEN_ITEMS.md row this document produces, not only rows this rule's own blocks generate. Every trigger must be one of: a checkable event, a numeric/data threshold, or an explicit "HUMAN DECISION / HOUSEKEEPING, no auto-trigger" label. No "later." No circular/self-referential triggers — a trigger that just restates the item's own name (e.g. "when AIService is built" as the trigger for "AIService doesn't exist yet") proves nothing and does not meet this bar. Section 6 item 12 states this same bar in full detail with worked examples; it is restated here, once, as a document-wide standard so it is never read as scoped only to that item.

1. Purpose

Every database table in Vrida — new or modified — runs through this runbook. The process is the same for a 1-table change as for a 10-table module. In order:

Read conventions, precedent, and dependency schemas.
Run the AI Capability Plane pass (Section 0's governing rule) — discover this module's AI capabilities via Part D's module-walk, then translate the findings into schema. Non-skippable.
Propose a column-level design — including the 4 Design-Phase Integrity blocks (Section 2.3.6): v1→v2 delta summary, consolidation justification, full table/column fate, dependency-blocked register — then stop for human review before building anything.
Audit the proposal (Section 4) — read-only, before any build.
Fix all FAILs and high-priority GAPs in the proposal.
Build: Drizzle schema → hand-written migration → apply → verify shape against the live DB.
Verify independently: tests, plus a re-audit ideally run by a reviewer with no stake in the original design.
Update every doc in the same pass, rebuild + deploy the docs site.
Lock and record.

The runbook is not optional or "for big changes only." Small changes hit the same recurring bug classes (Section 5) that big changes do — usually faster, because no one is paying attention.

2. The Process (run in order)

2.1 Read First

Before proposing anything, read:

docs/database/SCHEMA_CONVENTIONS.md — the naming/RLS/soft-delete/money rules this module must follow.
Any superseded v1 design for this module or table (docs/old/schema/schema_modules/) — proven patterns to reconcile with, not confirmed v2 design. State explicitly what carries forward and what's being deliberately replaced (e.g. multi_loc.site's v1 address JSONB column was identified here and replaced with flat lines + FKs into shared).
The actual, current column types of any table in another module this design will FK to — verify live against the database, don't assume from a doc. (multi_loc.site's FK design verified shared.country.iso_alpha2 was char(2)/bpchar and shared.administrative_region.iso_3166_2 was text via a live psql query before writing a single FK column.)

2.1a Reopening or Reviving a Design-Locked-But-Never-Built Module

Added 2026-07-19, first applied to the notifications module — a module can be schema-locked (a full column-level design, reviewed and approved) and then never actually migrated, sitting dormant in docs/old/schema/schema_modules/ for months while the rest of the codebase moves on. This is a distinct case from both a fresh design (nothing exists yet) and a normal reopen (the module is live in the DB and being extended) — the design is real and was genuinely approved, but every convention that postdates its lock date is missing from it by construction, not by oversight.

The 4-block treatment (Section 2.3.6) still applies in full — do not skip or lighten it just because "nothing has been built yet to consolidate away from." A stale locked design can still be years behind on real, load-bearing conventions (actor-attribution targets, autonomy-pack columns, composite-FK prerequisites, tables built by OTHER modules since the lock date that the stale design could not have known about) — Block 2's capability-cost accounting and Block 3's table/column fate both have real work to do here, not nothing.

Treat the old lock date as a reconciliation checkpoint, not a baseline to preserve unexamined. Before presenting any table as "KEEP, unchanged," verify it against every convention this codebase has adopted since the lock date, the same way a genuine v1→v2 migration would — the fact that nothing was ever built doesn't make the stale design current.

2.2 AI Capability Plane Pass — NON-SKIPPABLE

This step exists to enforce Section 0's governing rule. It runs before schema — a table designed without this pass and only patched afterward has already failed the point of the rule, because "add it later" means reopening a locked module. It has two halves, run together as one pass, not two separate steps:

2.2.1 Capability discovery — run the module through Part D

Read docs/ai/AI_CAPABILITY_PLANE.md Part D and answer its 15 questions for this module (use AI_CAPABILITY_PLANE_READING.md, the plain-language companion, if a question's intent is unclear — it explains the same items in plainer terms; the canonical .md is the contract, the reading edition is for understanding it). Do not restate Part D's text here — read it directly, it is the source of truth:

#	Question (from Part D)	What it triggers
D1	Capture targets — what transactions can be created here?	B1
D2	Routing rules — what smart table/status placement does this module own?	B1c
D3	Maintenance — what master data rots here, what's the hygiene agent?	B3
D4	Error-prevention — what wrong action must be blocked before commit?	B4
D5	Negative-space — what record should have a sibling and doesn't?	B5
D6	Decision-support — any forecast/anomaly genuinely worth surfacing?	B6
D7	Autonomy boundary — what may AI do alone / draft-only / needs approval / never?	A4, A13
D8	Evidence sources — what real-world evidence can create records, what's its abuse risk?	B1d, A11
D9	Reconciliation pairs — which records should reconcile with each other?	B13
D10	Failure/rollback — if an AI-assisted action is wrong, how is it reversed?	A12, B11, C8
D11	Adversarial/abuse surface — what untrusted input can attack this module?	A11, A13
D12	Offline behavior — what works offline, what queues, what falls back to manual?	A2, A3, A10
D13	Channel sync — does this module's data need to stay in sync externally?	B13, A2
D14	Lifecycle/perishability — does anything here age, expire, or lapse?	B5, B6, B13
D15	Capture modality — scan/photo/voice/form, and the offline fallback?	B1, B1d, A2, A11

D7 is the load-bearing one for schema design — it produces, per action, one of four answers (may-act-alone / draft-only / needs-approval / never), and that answer is the direct input to the schema-translation half below (question 4, the human-in-the-loop seam, and question 1, which this step answers).

A "none of this applies" answer to any Dx is valid and expected for many modules — most modules will not trigger all 19 B-capabilities. Record which ones DO apply and which were considered and ruled out, not just the ones that apply.

Known gap in the source doc, tracked here, not fixed here: AI_CAPABILITY_PLANE.md's A11 (what D11 triggers) currently covers untrusted document/tool-content only — it does not yet name memory-poisoning (an attack on a future agent-memory subsystem, question 7 below) as its own category. Updating the Plane doc itself is a documentation change outside this runbook's scope (logged to OPEN_ITEMS.md). Until it's updated: treat D11 as if it also covers memory-poisoning wherever a module answers "yes" to question 7 — any write path into agent/tenant memory needs the same trusted-source discipline A11 requires for documents and tools.

2.2.2 Schema translation — for every table, work through these questions

What work should be autonomous? — Already answered by 2.2.1's D7, not asked independently here.
Can an agent be the actor? Every action-attribution column this table introduces must FK to identity.actor, not identity.identity_user — decide this per column, not once for the whole module. (identity.actor is the polymorphic root: actor_type IN ('user','service_account','agent').) If the actor was itself an agent acting on another agent's delegation (not a human's), record that chain in question 7's decision_provenance extension below — not a new column. No delegation/handoff table exists yet, and multi-agent orchestration (AI_CAPABILITY_PLANE.md B8) is currently deferred, so there's nothing concrete to build against until it isn't.
Autonomy metadata — does the table need a data_source/automation_source/created_autonomously flag, a confidence_score, or is_verified?
Human-in-the-loop seam — for the operations D7 said need a person, is there a requires_human_review/flagged_for_review/approval_required column?
Agent-safe state machines — if the table drives a status/lifecycle, are its transitions CHECK-enumerated, idempotent, and reversible (or is a one-way door explicitly flagged as one)? (Ties to D10/D14.)
Decision provenance — if an agent will act on this table autonomously, can the schema capture why (inputs, confidence, rule fired), not just that it happened? (Ties to D8; required before autonomy per Part C's C7, below.)
Agent/tenant memory — does this module's data get read from, or written to, agent/tenant operating memory (AI_CAPABILITY_PLANE.md B12)? No dedicated memory table exists yet — if an autonomous action here was informed by a memory entry, or by another agent's delegation (question 2), record it inside question 6's decision_provenance jsonb (extend its documented shape with memory_refs/delegated_by_actor_id keys — not new columns). If this module's own data would be worth remembering for other modules' future actions, note that as a candidate for the memory subsystem whenever it's built — do not invent a memory table to hold it now.

Question 7 is new (2026-07-06) — it was not part of the six-question set the 2026-07-06 retro-audit ran against the 4 locked modules, so none of them has been checked against it. That's a separate, smaller open item (see OPEN_ITEMS.md), not something this edit resolves retroactively.

Also honor, where schema-relevant: AI_CAPABILITY_PLANE.md Part C's C5 (human-accountable/AI-attributable — questions 2 + 6), C7 (explainability before autonomy — question 6 must exist before an action runs autonomously, not after), C8 (financial actions independently controlled AND reversible — question 4 for the independent-approval/SoD half, question 5 for the reversibility half; both non-negotiable for money-touching tables, not just one).

Either outcome is valid, but only if recorded — in the module's PROJECT_DECISIONS.md entry or module_spec/<module>.md: (a) which Part B capabilities apply to this module (from 2.2.1), (b) the per-action autonomy boundary from D7, and (c) the schema columns from 2.2.2 that support them — or an explicit "this data is human-only, because X." Silence is not a valid outcome for either half — an unrecorded "we didn't think about it" fails this step even if the resulting schema happens to be fine.

Real precedent, done well (schema-translation half only): identity's Batch C (agent_type_catalog, agent_identity, agent_skill, agent_skill_assignment) is machine + AI-agent identity added specifically for the agent-as-actor capability, plus an explicit "Agent Authorization" section (skill check + permission check before any agent dispatch). shared.plant.data_source / is_verified were added specifically for the AI-enrichment seam, documented in module_spec/shared.md's Cross-Module Seams section — this is question 3, done for one table. Neither of these came from an actual Part D walk — both predate this integration and were done informally; there is no real precedent yet for 2.2.1 having been run against any locked module.
Retro-audit finding (2026-07-06): none of the 4 modules locked before this rule was formalized fully satisfy the schema-translation half — including identity, despite being the most autonomy-forward of the four. A structured retro-audit against the six schema-translation questions found real, specific gaps in all 4 (e.g. platform.operator_audit_log.operator_user_id and tenant_internal_activity.performed_by_user_id were FK'd to identity_user not actor before the 2026-07-06 backfill, so the compliance log itself could not attribute an action to an agent; shared.plant_common_name/plant_climate_zone — both named as AI-write targets in the module's own seam docs — had no data_source/is_verified at all, unlike plant itself; multi_loc.site had zero attribution columns of any kind, human or agent — all since backfilled, see PROJECT_DECISIONS #19). Full findings and prioritized gap lists per module are in docs/open-items/OPEN_ITEMS.md. The retro-audit and backfill covered only the schema-translation half (2.2.2) — none of the 4 modules has ever been run through the capability-discovery half (2.2.1, Part D), a separate, still-open gap logged in OPEN_ITEMS.md. Every module locked from 2026-07-06 onward must run the complete two-half pass for real, not retroactively, and not just one half.

2.3 Design Proposal → Stop for Review

Author the full column-level design, then explicitly stop — no Drizzle, no migration, no lock — until a human reviews and approves it. Every real module lock so far (shared, multi_loc) used this gate: a complete column-level proposal was delivered and review was explicitly requested before any code was written. This is the standard, not an optional extra step.

The proposal itself is built from:

2.3.1 Scope — state what the module / table owns and explicitly what it does NOT own — name the other module that owns each adjacent concern.

Default scoping question (Locked 2026-06-10 — see PROJECT_DECISIONS "Product Direction — Generic Multi-Vertical ERP"): "Does this work for any retail business?" NOT "Does this work for a nursery?" Scope every module generic-first; flag any vertical assumption explicitly in the spec and schema at design time.
The item / catalog layer is vertical-neutral (Locked 2026-06-09 — see PROJECT_DECISIONS "Inventory Item Model"). One inventory.item table, item_type discriminator, JSONB attributes for type-specific data. Do NOT add business-specific columns to shared / catalog tables; use item_type + JSONB attributes instead.
Operational modules MAY be business-specific. The vertical-neutrality rule applies to the item layer and the shared base (customers, vendors, sites, orders, payments, billing, identity, audit, notifications) — not to every module.
Global-from-day-1 for anything address/currency/units/climate-shaped. A nursery site, a customer address, a plant's climate zone — none of these may assume US shape. Resolve country-specific concerns via FKs into shared's natural-key reference tables (country, administrative_region, currency, locale, unit_of_measure, climate_zone), not free-text or JSONB blobs shaped like a US address. (multi_loc.site is the concrete precedent.)
Ownership boundaries must be explicit. Example: "Identity owns the seam to Supabase Auth and tenant-scoped roles / permissions. Identity does NOT own raw login / password / MFA — those stay in Supabase Auth."

2.3.2 Table List — propose tables with a one-line purpose each. For each, justify its existence: distinct lifecycle? distinct cardinality (1:N or M:N vs. its parent)? If the answer to both is "no, it just adds columns," it should be columns (or JSONB) on the parent, not its own table.

2.3.3 Consolidation Pass — before locking the table list, ask: can any two tables merge (same lifecycle, same owner, same cardinality → one table with a type discriminator)? Can any table fold into JSONB or extra columns on a sibling? Is any table really a subset of another that should live there with a flag? Only keep a separate table if it has its own lifecycle OR its own cardinality. Over-tabling (Section 5, bug class 7) is the most common design failure.

2.3.4 Adversarial Review — before column design, list what's likely missing: access control (RLS vs. service_role), compliance/audit needs, implications of prior PROJECT_DECISIONS locks, edge cases (empty states, soft-deleted parents, mixed-tenant access, NULL tenant_id), cross-module FKs (what will reference this, are the names stable), external system states (what does Stripe / Supabase Auth / QuickBooks produce that a CHECK needs to accept).

2.3.5 Column Design — author full column definitions per Section 3 standards, one markdown table per database table matching the schema_docs/<module>.md format (Column | Type | Nullable | Default | Constraints / Notes). Each table section begins with a purpose paragraph stating what it stores and any 1-line caveat (RLS exception, append-only invariant, key uniqueness constraint). A bare column table with no purpose paragraph is a FAIL at Section 6.

2.3.6 Design-Phase Integrity — The 4 Required Blocks

Enforces Section 0.5's governing rule. Authored FIRST in the proposal document, before 2.3.2's table list — despite this subsection's number, these blocks appear at the top of the page, immediately after 2.3.1's scope statement. A proposal missing any of the 4 blocks is INCOMPLETE and may not proceed to 2.4 (Audit) or 2.6 (Build).

BLOCK 1 — v1→v2 Delta Summary (counts). State explicitly, up front: "v1: N tables / X columns → v2 proposed: M tables / Y columns."

BLOCK 2 — Consolidation Justification (preserve-by-default). For every case where v1 tables or columns are merged, folded, or otherwise reduced in the v2 proposal:

WHAT is consolidated — which v1 table(s)/column(s) map to which v2 table/column.
WHY it's needed HERE — a concrete reason (v1 over-normalized with no independent lifecycle or cardinality of its own; a genuinely dead capability with no live use anywhere). If there is no real need, keep v1's shape — "fewer tables is cleaner" is not a valid reason on its own (Section 0.5).
CAPABILITY COST — every column, enum value, index, CHECK, or uniqueness guarantee that will NOT survive the merge, named explicitly. "Nothing lost" must be proven — walk the actual v1 column/enum list against the proposed v2 shape — not merely asserted.

BLOCK 3 — Full Table + Column Fate. Classify every v1 table as exactly one of: BUILT (→ its v2 name) / CONSOLIDATED (→ per Block 2) / DEFERRED (→ a concrete trigger) / DROPPED (→ a stated reason) / STALE-BUT-UNBUILT (added 2026-07-19 — the module was schema-locked but never migrated at all; this table's shape is being reconciled against every convention adopted since its lock date, per 2.1a, before it counts as BUILT). For every CONSOLIDATED, STALE-BUT-UNBUILT, or otherwise-reshaped table, also state column/enum-level fate — a table marked BUILT is not proof every one of its v1 columns survived with it (Recurring Bug Class #11).

Task-supplied current requirements (clarification, added 2026-07-19): when a design pass is driven partly by a task-supplied list of current requirements (capabilities the module must now serve, not derived from a v1→v2 diff), treat each such requirement as its own justified addition — score it (served/partial/missing) against the stale or proposed design, and let it drive its own Block 2/3 entries where it causes a change. Do not force a task-supplied requirement into the v1-diff framing if it has no v1 antecedent to diff against — it is a 5th input to the design, addressed on its own terms, not a retroactive reinterpretation of what v1 "should have" included.

BLOCK 4 — Dependency-Blocked Register. Everything this module cannot fully design or build because a required module or capability doesn't exist yet. For each item:

WHAT is blocked — the table/column/FK/constraint/capability that is incomplete or stubbed as a result.
WHICH missing dependency blocks it (e.g. Files, Consumer, Tax, Payments, Notifications, Purchasing, Search, a not-yet-built service layer, the connection-role/GRANT decision).
THE PLACEHOLDER in the meantime — a forward-ref UUID with no FK, a deferred table, a temporary CHECK, or a documented service-layer requirement.
THE CONCRETE TRIGGER to complete it (e.g. "when the Files module is built → add the real FK"). Covers both cross-module dependencies (a missing module) and same-module dependencies (this module's own schema waiting on its future service layer). No blocked item may be left implicit — if it's incomplete because something else doesn't exist yet, it belongs in this register.

Closing summary, required on every proposal — plain language, the part the user actually reads before approving: "Here's what's merged and why, what it costs, what's deferred/dropped, and what's blocked by missing dependencies. Net capability change vs v1: [explicit list, or 'none']. Decisions you should review before we build: [the judgment calls]."

2.3.7 Independent Adversarial Verification — MANDATORY, Evidenced (Non-Skippable)

Before ANY design proposal is presented for approval, AND before ANY module locks (see Section 6, item 1a for the lock-side twin of this gate):

A SEPARATE agent — not the one that authored the design or did the build — must independently re-derive and audit the work from the source docs. Re-read the same "Read first" list, re-run the relevant runbook sections, and check the claims against the actual files and (where applicable) the live database — not against the original author's summaries.
The proposal (or, post-build, the module's PROJECT_DECISIONS.md entry) MUST include that separate agent's ACTUAL findings — pasted and attributed to the separate pass. A statement like "independently verified, zero FAILs" or "verification passed" with no pasted output from the separate agent is NOT acceptable, and the proposal/lock is INCOMPLETE regardless of how complete every other section looks.
If the adversarial pass found zero issues, that zero-finding result must itself be the separate agent's own output — proving a real, independent pass actually ran — not the designer's or builder's self-assessment dressed up as confirmation.
Self-grading one's own Section 4 audit (2.4 / 2.7) does NOT satisfy this requirement, no matter how thorough the self-audit reads. The grader must be a different agent than the designer/builder.

Why this is a hard gate, not a continued practice. On the payments module (2026-07-07), this exact pass — which had already caught real BLOCKERs in orders, purchasing, and tax+billing before it — was skipped. The designer authored the proposal solo, self-graded its own Section 4 audit as "Zero FAILs found," and substituted memory for the required "Read first" list rather than reading the files fresh. Only after the user directly asked "did you follow all the runbook rules" did an adversarial pass run — and it found 2 real FAILs (a source_pair CHECK the self-audit had asserted already existed but was never actually specified in the schema; an arithmetic error in an autonomy-tier column count) plus 2 further gaps a still-more-skeptical second pass caught. The discipline had worked every prior time it ran — it just wasn't written down as a rule, so it was the first thing dropped under instruction-length or time pressure. This section, plus Section 6 item 1a and Bug Class 12 (Section 5), make it structural: evidenced, separate-agent verification, or the work does not proceed.

2.3.8 Propose-Gate Compliance Checklist (required at the end of every proposal)

Every design proposal must END with this checklist, each item explicitly confirmed — not merely implied by the surrounding prose:

Read the full "Read first" list in full — not substituted from memory or prior-session recall
Read the runbook sections cited (Section 0, 2.2, 2.3.6, Section 4) in full — not just grepped headers or section titles
All 4 mandatory Design-Phase Integrity blocks present (2.3.6)
Part D capability-discovery walk run (Section 2.2)
Section 4 self-audit done (2.4)
INDEPENDENT adversarial pass run by a SEPARATE agent — its findings pasted below, attributed, not paraphrased

Any unchecked item means the proposal is INCOMPLETE and must not be presented as ready for approval. A checklist with every box checked but no actual pasted adversarial findings beneath it is itself non-compliant — the checklist attests that evidence exists; it is not a substitute for the evidence.

2.4 Section 4 Audit (on the proposal, before any build)

Run the audit checklist (Section 4) read-only against the proposed design — nothing has been built yet. Report each item as PASS, FAIL, or GAP. Output as a table, then a prioritized fix list. Do not proceed to Build (2.6) with an unresolved FAIL.

2.5 Fix

Resolve all FAILs and all high-priority GAPs found in 2.4. Re-audit if anything moved. Low-priority GAPs may be explicitly deferred with a written trigger in OPEN_ITEMS.md (e.g. "add usage-vs-cap index when the alerting query is defined").

2.6 Build

Only after the proposal is approved (2.3) and audited clean (2.4–2.5):

Write the Drizzle schema file(s) matching the approved proposal exactly — column-for-column, constraint-for-constraint.
Write a hand-written, timestamped SQL migration file (packages/db/migrations/<timestamp>_<module>.sql). Do not use drizzle-kit generate for new work. Note the actual history honestly: platform and identity's original foundational migrations (0000_zippy_luckman.sql, 0001_true_loners.sql) genuinely were drizzle-kit generate output — that predates the snapshot-drift problem being discovered. Every migration since — every later patch to platform/identity (e.g. 20260628000000_platform_triggers.sql, 20260629000000_identity_trigger.sql), and the entirety of shared and multi_loc's builds — has been hand-written SQL, because the snapshot lineage (migrations/meta) went stale after those two foundational files and was never re-baselined, so generate now emits phantom/incorrect statements (e.g. "undefined"."inet"). This is tracked as a known, non-urgent issue in OPEN_ITEMS.md — fix the snapshot lineage in a dedicated re-baselining pass; until then, hand-write every migration, new or patch.
Apply the migration to the local Supabase instance.
Verify the shape landed as designed against the live database — \d <schema>.<table>, information_schema.columns, pg_constraint, pg_policies — do not assume the SQL file is what actually exists. Where a new CHECK constraint is genuinely load-bearing, spot-check its behavior directly (a BEGIN; ...; ROLLBACK; insert that should be rejected).

2.7 Independent Verification

After the build lands, verify it two ways, and treat both as gates before proceeding to lock:

Tests — write and run schema/seed-integrity tests exercising the built table: existence, RLS, FK resolution, CHECK-constraint behavior (including rejection cases), any partial-unique behavior. Run the full existing suite too, to catch regressions.
An independent re-audit — MANDATORY, evidenced, run by a SEPARATE agent than the one that built the module (see 2.3.7 / Section 6 item 1a — this is the post-build twin of that same gate, not a distinct lighter-weight step). Re-run the Section 4 checklist against what was actually built (not the proposal), by an agent with no involvement in the original build decisions, so the result is a genuine adversarial check rather than the same reasoning re-stated. The lock report MUST include that separate agent's actual findings — pasted and attributed — not a paraphrase or a bare "verification passed" claim. Self-grading one's own Section 4 audit does NOT satisfy this, no matter how thorough. This pattern emerged at the multi_loc lock (an independent audit pass plus an independent test-writing pass, both blind to the design rationale) and is now a hard requirement for every lock, not just that one — elevated from "ideally" to mandatory-and-evidenced on 2026-07-07 after it was skipped on the payments module's design proposal (see 2.3.7).

Do not proceed to Lock (2.9) if either surfaces a real FAIL — fix and re-verify.

2.8 Docs (same pass) + Rebuild/Deploy

Update every doc below in the same pass as the schema work — see Section 6 for the authoritative gate list. In practice, every real lock has touched: docs/database/schema_docs/<module>.md, docs/modules/module_spec/<module>.md, docs/modules/MODULE_INDEX.md, docs/modules/MODULE_BUILD_STATUS.md, docs/decisions/PROJECT_DECISIONS.md, docs/open-items/OPEN_ITEMS.md, docs/modules/CROSS_MODULE_CONTRACTS.md (if the module closes or adds any seam), docs/DOCS_INDEX.md, and CLAUDE.md's "Current focus" line.

Then rebuild and deploy the docs site in the same pass: node apps/docs/tools/build-docs.js (regenerates the HTML viewer from the .md source) followed by cd apps/docs && vercel deploy --prod (ships it to docs.vrida.app). This is not a separate step to remember later — a lock is not complete until the deployed site reflects it.

2.9 Lock

A table or module is locked when Section 6 criteria are met.

3. Column Standards (apply to every table)

These standards are the contract every table follows. Deviations require an explicit note in docs/database/schema_docs/<module>.md and a PROJECT_DECISIONS.md entry explaining why.

Primary keys

UUID PK with default gen_random_uuid(). Never auto-increment.
Known, accepted deviation: shared module's non-tenant reference tables (currency, country, language, locale, administrative_region, unit_of_measure, climate_zone) use their ISO/short code as PK instead — a deliberate exception, explicitly documented as a conflict with this rule in PROJECT_DECISIONS #17. shared.plant keeps UUID (taxonomic names aren't permanently stable). Any future deviation must be flagged with the same rigor — a live precedent survey and an explicit PROJECT_DECISIONS entry — not silently applied.

Enums

text + CHECK constraint. Never Postgres enum types.
Reason: schema migrations against Postgres enum types require ALTER TYPE gymnastics; text + CHECK is editable in a single migration.

Money

_cents (bigint) + currency_code (char(3)). ISO 4217, default 'USD'. Never float, never numeric without explicit reason.
Derived money columns (total, amount_due, amount_remaining) must carry their derivation formula in the Notes column.

Timestamps

timestamptz, stored UTC. Tenant display timezone lives on platform.tenant.timezone (IANA). Tenant-scoped operational tables that need their own timezone independent of the tenant default (e.g. a multi-site tenant with sites in different timezones) carry their own IANA timezone column — see multi_loc.site.timezone.

Tenant-scoped tables (the default)

Required columns on every tenant-scoped table:

id (UUID, PK, gen_random_uuid())
tenant_id (UUID, NOT NULL, FK → platform.tenant)
created_at, updated_at (timestamptz NOT NULL default now(); updated_at maintained by a BEFORE UPDATE trigger calling platform.set_updated_at() — the shared trigger function used across every module, not application code, so it stays correct even for writes that bypass the app)
deleted_at (timestamptz nullable)
RLS enabled, with a tenant_isolation policy: USING/WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid).

Transactional tables

Also carry site_id (UUID, NOT NULL, FK → multi_loc.site).
Master data does NOT carry site_id — items, customers, vendors are tenant-scoped but site-agnostic.

Global-address / country-dependent data

Never assume a US address shape (street, state, zip). Use flexible address_line1/2/3 + city + postal_code (free text) + FKs into shared.country (char(2)) and shared.administrative_region (text, format <country_code>-<region>).
Currency, locale, and unit-of-measure system for a given country-scoped record should be resolved via a join to shared.country wherever shared.country already carries the relevant default column (default_currency_code, default_locale_code), not duplicated onto the referencing table.
Where shared has no lookup for a concern (e.g. no per-country measurement-system table exists as of this writing), a plain CHECK-constrained column on the referencing table, service-layer-defaulted from the country code, is an accepted fallback — document why no FK exists.
When a region code embeds its country prefix (as shared.administrative_region.iso_3166_2 always does, e.g. 'DE-BW'), enforce region↔country consistency with a real DB CHECK (left(region_code, 2) = country_code) rather than leaving it app-enforced — this is cheaper than it looks and closes a real class of bug.

Soft delete

Never hard delete. Set deleted_at.
Every unique constraint on a soft-delete table MUST be a partial index: UNIQUE (col) WHERE deleted_at IS NULL. A standard UNIQUE is a bug — re-creating a previously soft-deleted row will collide.

Append-only / immutable tables

For logs, events, acceptances, audit records:

No updated_at, no deleted_at. Once written, never modified or removed.
Examples: agreement_acceptance, tenant_usage_summary, tenant_lifecycle_event, tenant_internal_activity, identity_access_event, role_permission.

Reference / global tables (no `tenant_id`)

State **RLS: not applied — reference data** in the descriptor.
Examples: tier_definition, agreement_version, permission, and all of shared's natural-key reference tables.

Secrets

Store *_ref (reference to a secret manager — Supabase Vault, AWS Secrets Manager, etc.). Never raw secrets.
Examples: sso_provider.client_secret_ref, sso_provider.certificate_ref.

Tokens

Store token_hash. Never raw tokens.
Validation: hash the inbound token and compare to token_hash.
Example: invitation.token_hash.

Emails

Store both email (as entered) and email_normalized (lowercased / trimmed).
Uniqueness lives on email_normalized, not email.

Indexes

Always index tenant_id on tenant-scoped tables — as the first column of at least one index (alone or composite).
Add indexes for the actual query patterns the module will run: by status, by date range, system-wide sweeps, by any FK a downstream module will filter on.

4. The Audit Checklist (run every time, read-only)

Standard Audit Invocation

When an instruction says "run the Standard Audit on <module>", it means ALL of the following without restating them:

Run the full A-P checklist (Section 4) over every table in the named module.
READ-ONLY — no edits to any file during the audit pass.
Per item A-P: report PASS / FAIL / GAP with specifics; a bare PASS is not enough — state what was checked.
Verify column-count arithmetic: per-table counts + module total + running grand total across all locked schemas. Flag any drift between schema_docs/<module>.md's header, the column tables, and the totals in docs/modules/MODULE_INDEX.md / docs/DOCS_INDEX.md / CLAUDE.md.
Verify every cross-module FK name against the actual locked target table name (not assumed) — for natural-key targets (e.g. shared.*), verify the actual column type too (char(2), text, etc.), not just the name.
Apply the recurring-trap checks by default (do not wait to be asked):
- NULL-in-multi-column-unique: does a nullable column in a UNIQUE defeat dedup? Two-partial-index pattern needed (one WHERE col IS NULL, one WHERE col IS NOT NULL)?
- Append-only vs insert-once-status-updated mislabel: any table labeled "append-only" that has post-insert mutating columns? Label must be accurate.
- Maintained-cache without documented reconciliation formula: any column that "summarises" or "mirrors" another table's data without a documented update rule?
- Secrets stored raw: any column that might hold a raw secret, token, or key instead of *_ref / token_hash?
- Enum CHECK completeness including nullable-enum NULL allowance: do CHECK constraints cover all states that application code AND external systems (Stripe, Supabase Auth, etc.) will produce?
- Soft-delete partial uniques: every UNIQUE on a soft-delete table must be WHERE deleted_at IS NULL.
- Forward-ref FKs documented: cross-phase FKs that cannot be enforced until a later migration must be explicitly noted as deferred (not silently absent) in CROSS_MODULE_CONTRACTS.md and OPEN_ITEMS.md.
- Query-pattern indexes present: status filters, due-date sweeps, by-FK lookups, and time-range queries per tenant each need a supporting index.
Cross-reference locked PROJECT_DECISIONS (Item N): confirm no boundary violation (no cut feature rebuilt, no duplication of another module's ownership, no contradiction of a locked decision).
Confirm module-level rationale is drafted (Item P): identify every non-obvious design choice and confirm it has (or will have, by lock time) a recorded Decision/Why/Rejected/Guard note in the module's PROJECT_DECISIONS.md entry. Finer-grained, per-choice rationale may instead be recorded inline in module_spec/<module>.md at the point of the design choice (the "DR-N" citation convention already used across identity.md and others) — either location satisfies Item P; an undocumented non-obvious choice does not.
Confirm the AI Capability Plane pass is recorded, both halves (Section 0 / Section 2.2 / Item P): for this module, verify (a) the Part D capability-discovery walk was run and recorded — which AI_CAPABILITY_PLANE.md Part B capabilities apply, and the per-action autonomy boundary from D7 — and (b) each of the six schema-translation questions has an answer recorded in PROJECT_DECISIONS.md or module_spec/<module>.md, either concrete fields/flags added or an explicit "human-only, because X" exception. An audit that finds no record of either half is a Section 0 compliance FAIL, not a GAP to defer — recording only the schema-translation half and skipping Part D is an incomplete pass, not a complete one. Non-skippable for any module locked after 2026-07-06.
Confirm Design-Phase Integrity (Section 0.5 / 2.3.6 / Item U): verify all 4 blocks are present and genuine, not just present-in-form — Block 2's capability-cost claims are proven by an actual v1-column/enum diff (not asserted), Block 3 classifies every v1 table including column/enum-level fate for anything CONSOLIDATED, and Block 4's dependency-blocked register has no implicit gaps. Missing or unproven blocks are a FAIL, same severity class as item 9.
Output:
- Full A-P+T+U findings table (Item | Check | Result | Details / Fix needed)
- Audit-specific focus areas (if any were provided in the instruction)
- Prioritized must-fix list: FAIL (blocks lock) → medium GAP (resolve or defer with trigger) → low GAP (defer with trigger)
Do NOT lock — triage and fix pass follow in a separate instruction.

A per-module audit instruction using this invocation needs to carry only the module-specific focus areas — the standard frame above is implicit.

Run this against the proposed column-level design before locking, and again independently after the build lands (Section 2.7). For each item, report PASS, FAIL, or GAP.

#	Check	What FAIL looks like
A	Column drift — populated columns match design intent; no silent additions or omissions. Report column count per table.	Spec said 18 columns; the built table has 19.
B	RLS per table — every tenant-scoped table has an explicit RLS line stating tenant-isolation policy on `tenant_id`. Reference / global tables marked "RLS: not applied". Mixed-scope tables (nullable `tenant_id`) have an explicit policy that handles the NULL case.	Descriptor implies RLS but doesn't say so; mixed-scope table has only the tenant-isolation policy and silently hides global rows from all tenants.
C	NULL-`tenant_id` uniqueness trap — if a table has nullable `tenant_id` and a `UNIQUE (tenant_id, code)`, Postgres treats NULL ≠ NULL, so global rows (NULL `tenant_id`) will not collide. FAIL unless there is a separate partial unique on (`code`) WHERE `tenant_id` IS NULL.	One `UNIQUE (tenant_id, role_code)` on `role`, no separate handler — two built-in roles with the same `role_code` would both insert successfully.
D	Soft-delete partial uniques — every unique on a soft-delete table is `WHERE deleted_at IS NULL`.	`UNIQUE (tenant_id)` on a table that has `deleted_at`.
E	Partial-index enum references — any index WHERE clause referencing a status / enum value must reference only values that exist in that column's CHECK constraint.	`WHERE status NOT IN ('cancelled','ended')` when `'ended'` isn't in the CHECK enum.
F	CHECK completeness — every enum CHECK covers all states the application and external systems (Stripe webhooks, Supabase Auth events, QuickBooks states) will produce.	`subscription.status` missing Stripe's `'incomplete'` / `'incomplete_expired'` / `'unpaid'`.
G	Forward-ref FKs — FKs to not-yet-built schemas are documented as deferred (column created without constraint; constraint added in the target schema's migration phase) in `CROSS_MODULE_CONTRACTS.md` and `OPEN_ITEMS.md`. External refs (e.g. Supabase `auth.users`) are marked never-enforced. When a module's lock makes a previously-deferred target available, flag the newly-unblocked FKs explicitly — wiring them is a separate, deliberate pass through the modules that hold the plain-UUID columns, not part of this lock.	Phase 1 migration tries to CREATE a column with FK → `multi_loc.site` before `multi_loc` schema exists; or `multi_loc` locks and nobody notes that 4 pre-existing deferred FKs are now unblocked.
H	Cross-module name consistency — any FK that another module wrote pointing at this module must match the actual table name and column type here.	Platform tables FK to `identity.user`, but the actual table is `identity.identity_user`; or a module FKs to a `shared` natural-key column assuming `uuid` when it's actually `char(2)`.
I	Money derivation — derived money columns (`total`, `amount_due`, `amount_remaining`) have documented formulas.	`amount_due_cents` annotated "Total after credits" with no `credits_cents` column anywhere.
J	JSONB shape — every JSONB column has a documented example shape, not just "JSONB".	`feature_flags JSONB` with no example — downstream readers guess.
K	`tenant_id` index — present (as first column of some index) on every tenant-scoped table.	Tenant-scoped table with only a PK index.
L	Query-pattern indexes — common queries (by status, by date range, system-wide sweeps) have supporting indexes; flag the table-scans.	Nightly job sweeps `subscription_invoice` by `(status, due_date)` with no supporting index.
M	Conditional-column consistency — when one column's meaning depends on another (e.g. `scope_type` determines whether `scope_id` or `scope_code` is used, or `region_code` is only valid for a matching `country_code`), a CHECK or written rule enforces it. Prefer a real DB CHECK when the dependent column's value structurally encodes the relationship (e.g. a region code prefixed with its country); accept an app-enforced/documented rule only when no such structural check is possible.	`scope_type='module'` rows have a `scope_id` UUID instead of `scope_code` — silently wrong; or a region/country mismatch that could have been a CHECK is left entirely app-enforced with no note.
N	Cross-reference locked decisions — defaults / values match PROJECT_DECISIONS (pricing tiers, trial length, retention period, caps).	`tier_definition.price_monthly_cents` defaults don't match the locked $49.99 / $99.99 / $199.99 pricing.
O	Access control for non-RLS tables — root / global tables without RLS have a documented access-control approach (who can read / write, when `service_role` is required).	`platform.tenant` has no RLS and no documented access rule — does any logged-in user get to scan all tenants?
P	Module-level rationale drafted — every non-obvious design choice in this module has a corresponding Decision / Why / Rejected (if applicable) / Guard (if applicable) note, recorded in the module's `PROJECT_DECISIONS.md` entry or inline in `module_spec/<module>.md` at the point of the choice (the DR-N citation convention).	A table is labeled "append-only" but has mutable columns, or a unique constraint is deliberately absent, with no recorded rationale anywhere — future sessions will "fix" it.
U	Design-Phase Integrity blocks present and genuine (Section 0.5 / 2.3.6) — the proposal (or, post-build, `PROJECT_DECISIONS.md`) carries all 4 required blocks: v1→v2 delta summary, consolidation justification with a proven (diffed, not asserted) capability cost, full table + column/enum fate, and the dependency-blocked register.	A proposal jumps straight to a table list with no v1 comparison anywhere; a CONSOLIDATED table's write-up asserts "nothing lost" without walking the actual column/enum diff; a table reads as "BUILT" with no check that its v1 columns all survived; a dependency-blocked item (missing module or unbuilt service layer) is nowhere in the proposal.
T	Trigger audit — for every DB trigger introduced or modified in this module: (a) fires on the correct events (BEFORE vs. AFTER; INSERT + UPDATE where both are needed — a trigger that only fires on INSERT misses actor_id changes on UPDATE); (b) logic is sound under edge inputs (NULL `NEW` columns, missing referenced rows — a BEFORE trigger fires before FK constraint checking, so a SELECT on a missing-actor row returns NULL, not an FK violation); (c) the deferred-CHECK or app-enforced invariant the trigger replaces is fully retired (no stale "deferred" or "app-enforced" language remains in column notes or purpose paragraphs); (d) trigger is listed in the module's Design Patterns Summary / Triggers note; (e) a rationale note (Item P) covers Decision / Why trigger (not CHECK or app-only) / Rejected / Guard. Prefer reusing the existing shared `platform.set_updated_at()` function for `updated_at` maintenance rather than writing a new one per module — `multi_loc.site` calls it directly, as do platform's and identity's tenant-scoped tables. `shared`'s reference tables have `updated_at` columns but no maintenance trigger at all (verified live — zero triggers on any `shared.*` table); `updated_at` is set once at insert and never touched again, an accepted gap since this reference data is rarely mutated after seed. Don't assume every table with an `updated_at` column has this trigger — check.	Trigger fires INSERT-only when UPDATE also modifies the constrained column; BEFORE-trigger SELECT on a missing referenced row produces a misleading exception message; old "deferred to Batch N" wording not removed from column notes after the trigger is added; no rationale note for a non-obvious trigger choice; a new one-off `set_updated_at`-equivalent function written instead of reusing `platform.set_updated_at()`.

Output format

Produce a findings table with columns: Item # | Check | Result (PASS / FAIL / GAP) | Details / Fix needed. Follow it with a prioritized "must fix before lock" list separating real bugs (FAIL) from design questions (GAP) from benign notes.

Item U is checked on every audit run, same as A-P+T — it is not a new, separate procedure; it's part of the same read-only checklist run in Section 2.4 (pre-build) and Section 2.7 (post-build).

5. Recurring Bug Classes (always check)

These bugs have each appeared at least once. Treat them as always-check items, not edge cases.

NULL tenant_id defeats unique constraints. A UNIQUE (tenant_id, code) does not constrain rows where tenant_id IS NULL — Postgres treats each NULL as distinct. Caught in identity.role (mixed-scope built-in roles).
Partial-index WHERE clauses referencing non-existent enum values. WHERE status NOT IN ('cancelled', 'ended') silently no-ops for 'ended' if it isn't in the CHECK enum. Caught in platform.subscription (the partial unique referenced 'ended'; the CHECK enum did not include it).
Standard uniques on soft-delete tables. UNIQUE (tenant_id) on a table with deleted_at blocks legitimate re-creates after soft delete. Caught in platform.tenant_profile, platform.billing_account, platform.subscription_invoice.
Table renames breaking other modules' FK references. Platform tables wrote FKs to identity.user; the actual table is identity.identity_user. Both ends must be updated together — and cross-module name consistency must be audited at every module lock.
Mixed-scope RLS hiding global rows from all tenants. A table with nullable tenant_id (global rows + tenant-custom rows) needs an RLS policy that handles both — a single tenant_id = current_setting(...) policy hides global rows from everyone. Caught in identity.role.
Incomplete status enums missing external-system states. subscription.status initially had 5 values; Stripe webhooks produce 'incomplete', 'incomplete_expired', 'unpaid'. Missing values mean runtime INSERT failures when those webhooks fire.
Tables added that should have been columns or JSONB. The strongest pressure during table-list design is to over-table — every "thing the user thinks about" feels like it deserves a table. Most don't. Apply Section 2.3.3 ruthlessly.
Forward-ref FKs that would break if migrated before the target exists. Phase 1 cannot create an enforced FK to a Phase 4 schema. Cross-phase FKs must be documented as deferred and added by the target phase's migration.
An enum CHECK list that's too narrow for real-world diversity. shared.administrative_region.region_type initially had 6 values; ISO 3166-2's actual type diversity (and the need to represent a country-level top-level entry, e.g. the UK) required 21. Caught by the Section 4 audit on the proposal, then again live during seeding when a value outside even the expanded list appeared — fixed at both the Drizzle schema and the live DB constraint together.
A DB CHECK that reads as "closed" but silently passes on a partial NULL. A two-sided CHECK like region_code IS NULL OR left(region_code,2) = country_code looks like it enforces consistency, but if country_code is NULL while region_code is set, the comparison evaluates NULL (not FALSE) and Postgres treats the whole CHECK as satisfied — the mismatch case that matters most silently passes. Write the guard explicitly: region_code IS NULL OR (country_code IS NOT NULL AND left(region_code,2) = country_code).
Claimed-but-unlogged deferral — applies at TABLE, COLUMN, and ENUM granularity. A module's own docs (PROJECT_DECISIONS.md, module_spec/<module>.md, schema_docs/<module>.md) assert that something was "deferred," "logged to OPEN_ITEMS," or "an open question" — and no matching row actually exists in OPEN_ITEMS.md. This isn't a hypothetical risk: a 2026-07-06 completeness audit of all 8 then-locked modules found 9 confirmed table-level instances (identity ×1 — the updated_at-trigger gap on every table but agent_duty_grant; shared ×4 — a SCHEMA_CONVENTIONS.md amendment, AU/EU climate_zone re-verification, a plant_climate_zone coverage gap, and seed-scope shortfalls; multi_loc ×3 — a missing "no MultiLocService" row plus two Part D findings; inventory ×1 — item_image.file_id sharing stock_movement.photo_ref's forward-ref deferral without its own mention). The failure mode is subtle because the logging step itself (Section 6, item 12) was followed correctly for each lock's own new findings — what was missing was ever going back and verifying that older cross-references, scattered across a module's other docs, still resolved to a real row. See Section 6, item 12's standing rule for the fix: verify against the actual file, not against the module report's own claim.

Extended 2026-07-07, after the same failure recurred one level down. A 19→9 v1-consolidation audit of pos found 8 MORE instances of the identical pattern, this time at column/enum granularity rather than table granularity: sale silently lost cart-hold/resume (held_at/hold_expires_at), B2B/contractor fields (po_number/job_reference/delivery_date/pickup_window), and a loyalty-points receipt snapshot; sale_line silently lost its comp/sample/replacement line-type distinction and manual price-override tracking; register silently lost hardware-pairing config; register_cash_entry's entry_type CHECK silently narrowed from 6 values to 3 — none of these got their own OPEN_ITEMS row, because each table they lived on still counted as "BUILT" overall, and nobody diffed the column/enum list underneath that label. A table reading as "BUILT" is not evidence every one of its v1 columns survived — see Section 6, item 12's extended rule for the fix: diff the full column/enum list against v1, not just the table-existence claim.

Three prevention mechanisms, all verified at lock (added 2026-07-07, Section 0.5):
- (a) Block 2's consolidation-justification requirement prevents the loss from happening silently in the first place — the capability cost of any merge must be proven (a real column/enum diff) and disclosed at design time, before a table is built, not discovered after the fact by a later audit.
- (b) Column/enum-granularity OPEN_ITEMS logging (this item's own extended rule, above) tracks any loss that is deliberately, knowingly accepted — so a real, approved reduction stays visible even though it happened, rather than vanishing once the table reads as "BUILT."
- (c) Block 4's dependency-blocked register prevents work that's blocked by a missing module or an unbuilt service layer from going untracked — the same class of invisibility this bug class describes for consolidation, applied to the "we can't finish this because X doesn't exist yet" case instead. All three are checked directly against the files at Section 6 lock (item 15) — never against what a build report claims.
Self-graded or skipped adversarial verification. A designer's or builder's own Section 4 self-audit reporting "Zero FAILs" is not evidence a real independent check happened — the person (or agent) who made the design decisions is the least likely party to catch their own blind spots. Caught on the payments module (2026-07-07): the design proposal was authored and self-audited solo, skipping the independent verification pass that had already caught real BLOCKERs in orders, purchasing, and tax+billing; the proposal also substituted memory for the required "Read first" file list. A subsequent adversarial pass — run only after the user directly asked whether the rules had been followed — found 2 real FAILs the self-graded pass had missed entirely: a source_pair CHECK constraint the Section 4 self-audit asserted already existed (by analogy to tax.tax_calculation/billing.ar_charge) but was never actually specified in the proposed schema, and an arithmetic error in an autonomy-tier column count ("4 more tables — 5 total" when the correct count was 3 more / 4 total). Fix: Section 2.3.7 (propose-gate) and Section 2.7 / Section 6 item 1a (lock-gate) now make independent, evidenced, separate-agent verification a hard gate — not a practice that can be silently dropped under time or instruction-length pressure, and not satisfied by a designer's own audit however thorough it reads.

6. Lock Criteria

A module or table is locked only when all of the following are true:

Audit gate (correctness)

Zero FAILs remaining in the audit (both the pre-build audit on the proposal, and the independent post-build re-audit — Section 2.4 and 2.7).

1a. Independent Adversarial Verification evidenced, not claimed (added 2026-07-07, after the payments module's design proposal skipped it). Both the propose-gate pass (2.3.7) and the post-build re-audit (2.7) must have been run by a SEPARATE agent from the one that designed/built the module, and their actual findings — not a summary, not a "verification passed" assertion — must be present in the proposal and/or the module's PROJECT_DECISIONS.md entry, attributed to that separate pass. A lock report that asserts "independently verified, zero FAILs" without the separate agent's own pasted output is INCOMPLETE, and the module may not lock. If the separate pass found real issues, the fixes and the corrected verdict must also be recorded — including cases where the separate pass corrected a designer's own inaccurate "zero FAILs" self-grading (see Bug Class 12, Section 5).
All high-priority GAPs resolved — or explicitly deferred with a written trigger in OPEN_ITEMS.md (e.g. "add this index when the alerting query is defined").

Doc-update gate (completeness — all mandatory, same lock pass)

A module is NOT considered locked until every item below is updated in the same pass as the schema fix. The audit gates correctness; this doc set gates completeness.

docs/database/schema_docs/<module>.md — one file per module (there is no single project-wide SCHEMA.md). Module section marked locked (with date), full column tables and indexes for every table, design-notes / patterns-summary block, per-table column counts, and section-level total column count recorded and verified against the spec.
docs/modules/module_spec/<module>.md — purpose, ownership, layer & dependencies, tables (high-level), capabilities, service contract (or "not built this pass" if no service layer exists yet), data-flow / population model, and cross-module seams. Distinct from item 3 above — every real lock has produced both files.
docs/decisions/PROJECT_DECISIONS.md — "<Module> (Locked <date>)" numbered entry covering: scope summary, table-by-table breakdown with col counts and purpose, key design decisions (Decision / Why / Rejected / Guard for anything non-obvious — this is where Item P's module-level rationale lives), the Section 0 / 2.2 AI Capability Plane pass outcome for this module — both halves: which AI_CAPABILITY_PLANE.md Part B capabilities apply and the per-action autonomy boundary from Part D's D7 (capability discovery), plus what was made agent-capable, what autonomy metadata/human-review seams were added, or an explicit human-only exception with reason (schema translation), seams closed, touches to previously locked modules, and deferred items with explicit triggers. Finer-grained per-choice rationale may instead live inline in module_spec/<module>.md at the point of the choice (the "DR-N" citation convention).
CLAUDE.md (project ROOT — vrida-erp-architect/CLAUDE.md) — "Current focus" line updated: which modules are schema-locked, which have a service layer, what's next.
docs/DOCS_INDEX.md (inside docs/ — there is no project-ROOT DOCS_INDEX.md; do not confuse the two) — module row added or updated (schema locked date, table count, col count); schema-locked module count updated; grand-total table count updated; verify internal consistency (sum of per-module table/col counts = grand total, or the discrepancy is explicitly flagged if it can't yet be reconciled — see the note below).

Root-level gate docs reminder: CLAUDE.md lives at the project root; DOCS_INDEX.md lives inside docs/, not at the project root. They are easy to conflate — verify both explicitly at every lock, and verify the path, not just the filename.

Header/row arithmetic reminder: if docs/modules/MODULE_INDEX.md's header total doesn't match the sum of its own rows (a pre-existing gap first flagged 2026-06-30, still open — see OPEN_ITEMS.md), do not silently "fix" the header to make it balance. Recompute honestly for the module actually being locked, verify that module's row independently against the live DB, and re-flag the remaining discrepancy with updated numbers rather than guessing which side is wrong.

Locked-module touches — if this module added an additive column or table to a previously locked module, re-note it (with date) in that module's schema_docs/<module>.md section header and column-count table, update that module's counts in DOCS_INDEX.md and CLAUDE.md, and record the touch in PROJECT_DECISIONS.md under the locking module's entry.
Cross-module name consistency verified — every FK from another module that references a table in this module uses the correct, current table name and column type.
docs/modules/MODULE_INDEX.md — add or refresh the locked module's row: schema, table count, col count, lock date, one-line owns statement, and dependencies (including any new dependency on shared or another module via FK). Update the grand-total line (schemas, tables, cols) and the build-order section if the module changes the recommended sequence.
docs/modules/CROSS_MODULE_CONTRACTS.md — add the locked module's seams to the Seam Catalog if it closes or introduces any (direction From → To, mechanism, what flows). Not every lock adds a seam — skip only if genuinely none exist.
docs/open-items/OPEN_ITEMS.md — log every deferral, known gap, and accepted limitation surfaced during this lock, each with a specific trigger ("when X is built", not "later"). Every real lock has added rows here; treat it as mandatory, not optional cleanup.

Non-skippable standing rule (added 2026-07-06, after a completeness audit found 9 confirmed gaps): "OPEN_ITEMS updated: every deferral, ruled-out item, and cross-module dependency identified this module is logged to OPEN_ITEMS.md with a concrete trigger BEFORE lock. Verify the rows actually exist in the file — do not rely on the module report claiming it." This is a distinct verification step from the logging itself:
- Before considering this item satisfied, re-scan this module's own PROJECT_DECISIONS.md entry, module_spec/<module>.md, and schema_docs/<module>.md for every place they say something was "deferred," "logged to OPEN_ITEMS," or "an open question" — then confirm each one has an actual matching row in OPEN_ITEMS.md by reading the file directly. Do not assume that adding a batch of new rows for this lock's own findings is the same as verifying nothing else was claimed-but-missed.
- Why this is now a distinct check, not folded into the paragraph above: a 2026-07-06 audit of all 8 then-locked modules found 9 confirmed cases across identity (1), shared (4), multi_loc (3), and inventory (1) where a module's own docs asserted something was "logged to OPEN_ITEMS" and it simply wasn't there — the logging step above was followed for that lock's own new findings, but nothing had ever verified older cross-references against the live file. See the row-by-row detail already logged in OPEN_ITEMS.md itself (dated 2026-07-06) and docs/decisions/PROJECT_DECISIONS.md.
- Trigger concreteness bar: a trigger of "later," "someday," "TBD," "once decided" with no owner, or one that just restates the item's own name (e.g. "when AIService is built" as the trigger for "AIService doesn't exist") does not meet this bar on its own. Every trigger must be one of: (a) a checkable external event or module dependency ("when the Files module is built"), (b) a checkable numeric/data threshold ("when violations exceed 10K rows per tenant"), or (c) explicitly labeled "HUMAN DECISION, no auto-trigger" / "HUMAN-SIGNALED, no automatic detection" when the item genuinely has no technical firing condition and requires an explicit human call — labeling it this way is a legitimate, complete answer; leaving it as an unlabeled vague phrase is not.
- Applies at COLUMN and ENUM granularity too, not just table granularity (added 2026-07-07, after pos's own 19→9 v1-consolidation audit found this exact gap one level down). When a v1 table's capability survives into v2 as a BUILT table (not deferred, not consolidated into a different table), that does not automatically mean every column/enum-value survived with it. Before lock, for every table carried forward from a larger v1 design, explicitly diff the v1 column list (and every enum's value list) against the built v2 column list — any column dropped, or any CHECK-enum value narrowed (e.g. a 6-value status collapsed to 3), is itself a capability removal and needs its own OPEN_ITEMS row with a concrete trigger, exactly like a fully-deferred table would. Do not let a column-level drop hide inside a table that otherwise reads as "BUILT." A module report that says "table X survived" is not evidence that X survived with all its columns — verify the column diff directly, the same way item 12's file-verify already requires for table-level claims.
- A consolidated table (a v1 concept folded into fewer columns on a built table, not its own table) needs its OPEN_ITEMS row to state the true severity of what was lost, not just that a simplification happened. pos.sale_line_tax (a 2026-07-07 finding) is the cautionary example: the original row read as "ships flat tax fields on sale_line only," which undersold that per-jurisdiction tax stacking becomes structurally unrecoverable (a combined rate has infinitely many decompositions; jurisdiction names were never captured) — a genuine audit-trail loss, not a cosmetic rename. When logging a CONSOLIDATED item, name the specific capability that cannot be reconstructed from the narrower form, not just that a reduction occurred.
Deferred forward-ref FK tracking — as of this writing, there is no dedicated docs/FORWARD_FK_REGISTRY.md file; deferred/forward-ref FKs are tracked via CROSS_MODULE_CONTRACTS.md seam rows (annotated "deferred FK") plus OPEN_ITEMS.md rows of type FK. If this module's lock makes a previously-deferred target available (i.e. another module's plain-UUID column can now be FK'd to a table this lock just created), note that explicitly in both places — the FK constraint itself is not wired as part of this lock (that reopens the module holding the plain-UUID column, which is a separate, deliberate pass) but the fact that it's now unblocked must be recorded. Whether a dedicated registry file gets created is an open process question — see OPEN_ITEMS.md.
Docs site rebuilt and deployed — node apps/docs/tools/build-docs.js then cd apps/docs && vercel deploy --prod, in the same pass as the doc edits above. A lock is not complete until docs.vrida.app reflects it — spot-check at least one changed page in a browser before calling the lock done.
Design-Phase Integrity verified in-file (Section 0.5 / 2.3.6) — at lock, confirm each of the following by direct grep of the actual files — never by trusting a build report's claim that it was done: a. Every DEFERRED item from Block 3 has a real OPEN_ITEMS.md row with a concrete trigger. b. Every DEPENDENCY-BLOCKED item from Block 4 has a real OPEN_ITEMS.md row with a concrete trigger. c. Every CONSOLIDATION CAPABILITY COST from Block 2 that isn't restored is either an OPEN_ITEMS.md row or an explicitly-accepted-tradeoff DR entry in module_spec/<module>.md or PROJECT_DECISIONS.md — a cost that is neither logged nor accepted-with-rationale is a FAIL, not a GAP. d. The full Blocks 1-4 record itself is retained in PROJECT_DECISIONS.md as the permanent v1→v2 accounting for this module — not just delivered in the chat proposal and then discarded once the module is built. A module cannot lock until all four are verified present in-file. This item exists because three different modules this project already had to reopen after locking (pos, crm, pricing, inventory) specifically because this accounting either never happened or was never retained anywhere durable — see PROJECT_DECISIONS #27's addendum and #28.

Until items 1–15 are all true, the module is in design, not locked. The CLAUDE.md cheat-sheet should only list a module as "Locked" once all criteria are met.

Note — docs NOT in the per-lock gate: docs/ARCHITECTURE.md, docs/business-requirements/FEATURE_CATALOG.md, and docs/process/runbooks/README.md's own higher-level pipeline docs (module-design.md, smoke-test.md, ship.md) are intentionally excluded from this gate — the first two reflect phase-boundary decisions reviewed at phase transitions, not every schema lock. The three files under docs/process/runbooks/ are referenced by CLAUDE.md and that folder's own README.md as the higher-level module pipeline, but do not currently exist as files; all 4 real locks so far were run entirely through this runbook plus direct build instructions, not through a followed module-design.md. That's a real, pre-existing gap between the documented pipeline and what's actually been used — flagged in OPEN_ITEMS.md, out of scope to resolve here since it's a process-architecture question broader than this runbook.

Last modified: Jul 14, 2026, 10:41 AM PT