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.

Cross-references:

docs/SCHEMA_CONVENTIONS.md — the rules (naming, RLS pattern, soft-delete pattern, money storage, etc.).
docs/SCHEMA.md — the definitions (authoritative tables, columns, constraints per module).
docs/PROJECT_DECISIONS.md — locked product / business decisions whose values flow into defaults and seed data.

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 an 11-table module:

Define what the module owns and what it does not.
Propose a table list with one-line purposes.
Consolidate before committing to columns.
Adversarially review what's likely missing.
Design columns to the standards in Section 3.
Audit using Section 4.
Fix all FAILs and high-priority GAPs.
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 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. Vertical-specific needs go via item_type+JSONB, generic reusable tables, or add-on modules beside the foundation — never business-specific columns on foundation/shared schemas.
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. The item layer SHOULD be reusable across product types — a nursery selling plants + pots + fertilizer works natively, and future verticals require no item-table redesign. Do NOT add business-specific columns to shared / catalog tables; use item_type + JSONB attributes instead.
Operational modules MAY be business-specific. Production / propagation is nursery-only; other verticals will get their own operational modules as needed. 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.
The generic-core + vertical-extension multi-schema split is NOT adopted (one flexible item table replaces it). Module ownership still stands: item lives in Inventory, customer in CRM, vendor in Purchasing.
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.2 Table List

Propose tables with a one-line purpose each. For each proposed table, justify its existence:

Distinct lifecycle? Does it have its own create / update / delete cadence?
Distinct cardinality? Is it 1:N or M:N relative to 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 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.4 Adversarial Review

Before column design, list what you're likely missing. Surface gaps here, not after columns are written:

Access control — who reads, who writes, RLS vs. service_role.
Compliance / audit needs — does this need a hash chain, append-only, retention policy, legal hold?
Implications of prior decisions — does this contradict or constrain a PROJECT_DECISIONS lock?
Edge cases — empty states, soft-deleted parents, mixed-tenant access, NULL tenant_id.
Cross-module FKs — what other modules will reference this, and are their names stable?
External system states — what statuses do Stripe / Supabase Auth / QuickBooks webhooks produce that this column needs to accept?

2.5 Column Design

Author full column definitions per Section 3 standards. One markdown table per database table in SCHEMA.md format (Column | Type | Nullable | Default | Constraints / Notes).

Each table section MUST begin with a purpose paragraph — placed between the ### \schema.table`` header and the column table — stating what the table stores, its purpose, and any 1-line caveat (e.g. RLS exception, append-only invariant, key uniqueness constraint). Already the de facto standard across all 17 locked schemas (see any existing module for examples); required for every future module's populate step. A bare column table with no purpose paragraph is a FAIL at the Section 6 audit gate.

2.6 Audit

Run the audit checklist (Section 4) read-only. Report each item as PASS, FAIL, or GAP. Output as a table, then a prioritized fix list.

2.7 Fix

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

2.8 Lock

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

Zero FAILs.
High-priority GAPs resolved or deferred with a trigger.
SCHEMA.md populated to column level.
Column counts recorded (per table and total).
PROJECT_DECISIONS updated if any decision was locked during the process.

3. Column Standards (apply to every table)

These standards are the contract every table follows. Deviations require an explicit note in SCHEMA.md.

Primary keys

UUID PK with default uuid_generate_v4(). Never auto-increment.

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 tables (the default)

Required columns on every tenant-scoped table:

tenant_id (UUID, NOT NULL, FK → platform.tenant)
created_at, updated_at (timestamptz NOT NULL default now())
deleted_at (timestamptz nullable)

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.

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.

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.

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 the SCHEMA.md header, the column tables, and the totals in DOCS_INDEX.md / CLAUDE.md.
Verify every cross-module FK name against the actual locked target table name (not assumed). Cross-check against CLAUDE.md locked-module lines.
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 (e.g., "insert-then-link-once" when a hash column transitions NULL → value once).
- 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).
- 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 DESIGN_RATIONALE entries are drafted (Item P): identify every non-obvious design choice and confirm a formal Decision/Why/Rejected/Guard entry is drafted and ready to add to docs/DESIGN_RATIONALE.md at lock time. Inline notes in the schema section do not satisfy Item P — formal entries are required.
Output:
- Full A-P 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. 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; SCHEMA.md 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). External refs (e.g. Supabase `auth.users`) are marked never-enforced.	Phase 1 migration tries to CREATE a column with FK → `multi_loc.site` before `multi_loc` schema exists.
H	Cross-module name consistency — any FK that another module wrote pointing at this module must match the actual table name here.	Platform tables FK to `identity.user`, but the actual table is `identity.identity_user`.
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), a CHECK or written rule enforces it.	`scope_type='module'` rows have a `scope_id` UUID instead of `scope_code` — silently wrong.
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	DESIGN_RATIONALE entries drafted — every non-obvious design choice in this module has a corresponding entry drafted (Decision / Why / Rejected if applicable / Guard if applicable) ready to add to `docs/DESIGN_RATIONALE.md` at lock time.	A table is labeled "append-only" but has mutable columns, or a unique constraint is deliberately absent, with no recorded rationale — future sessions will "fix" it.

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.

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

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.
All high-priority GAPs resolved — or explicitly deferred with a written trigger (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/SCHEMA.md — module section header marked locked (with date), full column tables and indexes for every table, design-notes blocks, per-table column counts, and section-level total column count recorded and verified against the spec.
docs/PROJECT_DECISIONS.md — "<Module> (Locked <date>)" entry covering: scope summary, table-by-table breakdown with col counts and purpose, key design decisions, seams closed, touches to previously locked modules, and deferred items with explicit triggers.
docs/DESIGN_RATIONALE.md — module rationale section added with every non-obvious design choice recorded as Decision / Why / Rejected / Guard (Guard on any choice that looks like a bug or mistake but is deliberate). This is required at lock time — not a later back-fill. Item P of the audit checklist gates this.
CLAUDE.md (project ROOT — sprig-erp/CLAUDE.md) — cheat-sheet line added for the module: schema name, table count, col count, the most important design rule, key seams closed.
DOCS_INDEX.md (project ROOT — sprig-erp/DOCS_INDEX.md — NOT docs/MODULE_INDEX.md; they are different files) — module row added or updated (schema locked date, table count, col count, link to SCHEMA.md section and PROJECT_DECISIONS entry); schema-locked module count updated; grand-total table count and col count updated; verify internal consistency (sum of per-schema col counts = grand total).

Root-level gate docs reminder: CLAUDE.md and DOCS_INDEX.md live at the project root, not in docs/. They are easy to skip or conflate with docs/ files — verify both explicitly at every lock. This was the root cause of AI + Search both missing DOCS_INDEX.md updates.

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.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.
docs/MODULE_INDEX.md — add or refresh the locked module's row: schema, table count, col count, lock date, one-line owns statement, and dependencies. Update the grand-total line (schemas, tables, cols) and the build-order section if the module changes the recommended sequence.
docs/CROSS_MODULE_CONTRACTS.md — add the locked module's seams to the Seam Catalog. Each seam must document: direction (From → To), mechanism (service call or FK reference), and what flows.
docs/FORWARD_FK_REGISTRY.md — add any new deferred/forward-ref FK constraints introduced by this module (columns created as plain UUIDs pointing to not-yet-locked schemas). Flip any existing OPEN entries to READY if this module's lock makes the target available. Add DONE entries for any FKs enforced in this module's migration.

Until items 1–12 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 and docs/FEATURE_CATALOG.md are intentionally excluded. ARCHITECTURE.md reflects phase-boundary architectural decisions and is reviewed at phase transitions, not at every schema lock. FEATURE_CATALOG.md derives from DOCS_INDEX.md and is updated on product-direction changes, not per-lock. Excluding them from the gate is a deliberate choice to avoid process bloat for routine locks.

Last modified: Jun 17, 2026, 6:57 PM PT