Vrida ERP — Database Catalog

Generated 2026-07-17, for external design review. Source of truth: the live local Supabase Postgres instance (127.0.0.1:54322) cross-checked against the Drizzle source (packages/db/src/schema/**/*.ts) and the existing narrative docs (docs/database/schema_docs/*.md). Where the three disagreed, the live database wins as fact; the disagreement itself is recorded as a discrepancy in that schema's section.

This is a schema catalog, not a system description. See §7 — What is NOT built before reading anything else if you're forming an opinion on production-readiness.

Stock Transfer addendum — 2026-07-16 task run. Inventory is now re-certified at 29 base tables / 425 base-table columns / 1 view after adding the exact 3-table / 63-column Transfer schema to the 26/362 Inventory Core baseline. The current application-schema total is 28 schemas / 488 base tables / 6,954 base-table columns.

1. Overview

The 332/4,586 overview and 25-schema table below are the catalog's original 2026-07-17 snapshot. They are retained as lineage, not current totals. The authoritative current application-schema total for this task run is the 28 schemas / 488 base tables / 6,954 base-table columns addendum above; the authoritative current Inventory row is 29/425.

332 tables, 4,586 columns, across 25 Postgres schemas, built over roughly 6 weeks (first commit 2026-06-XX through 2026-07-17), each schema corresponding to one ERP module in this codebase's own numbering. Every schema listed below is genuinely built and live-migrated on the reference database — none of this is a design document rendered as if it were real.

A note on the "312 tables / 4,691 columns" figure that appears in this session's own prior final report (2026-07-17): that figure was a manual arithmetic error made while summarizing 25 rows by hand. Re-derived directly from information_schema/pg_class for this catalog, the correct totals are 332 tables / 4,586 columns. This document's own totals are the ones to trust — they were computed by a SQL aggregate query against the live catalog, not summed by eye.

The 25-schema summary

Schema	Layer	Tables	Cols	One-line purpose
`platform`	foundation	35	518	Tenant lifecycle, billing/subscriptions/entitlements, module registry, AI capacity/regional policy, legal entities, fiscal periods, outbox.
`identity`	foundation	36	410	Every principal (staff, service accounts, agents), RBAC, sessions, SoD governance, access requests, agent authority passport (`agent_duty_grant`).
`shared`	foundation	12	136	Cross-module reference data — currency, country/locale, UOM, the botanical `plant` catalog, payment terms, exchange rates.
`multi_loc`	foundation	1	37	The tenant's physical sites (single table — deliberately thin).
`files`	foundation	6	89	Generic file/document metadata registry, R2-backed, plus the vector-search "spine" (`document_index`/`document_chunk`) for future RAG.
`admin`	foundation	10	122	Tenant-facing operational config — branding, integrations, webhooks, custom fields, hardware devices, compliance documents.
`approvals`	foundation	8	97	The tenant-side, cross-module approval-workflow engine (routing rules, steps, delivery, tokens).
`crm`	business	13	193	Customers, contacts, addresses, tax-exemption certificates — the "who are we selling to" record.
`inventory`	business	29	425	Vertical-neutral catalog plus protected stock, reservation, movement, lot, count, and adjustment storage.
`pricing`	business	4	78	Price lists, rules, campaign pricing — deliberately thin (4 tables), the first module of the sell path.
`pos`	business	10	160	Point-of-sale: registers, sales, refunds, tenders, gift cards/store credit — offline-first by design.
`orders`	business	7	175	Non-POS sales orders — the online/phone/wholesale counterpart to `pos`, completing the sell path (crm→pricing→inventory→orders→pos).
`purchasing`	business	15	353	Vendors, purchase orders, 3-way match, vendor credits/returns — the buy path.
`receiving`	business	2	62	Goods receipt against a PO — extracted out of `purchasing` into its own schema.
`tax`	business	3	47	Tax calculation + per-jurisdiction breakdown — the first module with zero v1 precedent (v1 outsourced tax entirely to Stripe Tax).
`billing`	business	10	176	A/R and A/P settlement — charges, payments, adjustments, write-offs.
`payments`	business	9	159	Money-movement execution layer — payment intents, terminal readers — distinct from `billing`, which only records what's owed.
`returns`	business	9	152	Customer RMA — authorizations, resolutions, receipts, plant-guarantee claims, proportional loyalty/offer clawback.
`consumer`	consumer	10	104	End-consumer identity, across the tenant boundary — a dedicated Postgres role (`consumer_authenticated`), not `authenticated`.
`rewards`	consumer	7	109	Consumer loyalty points program and ledger.
`offers`	consumer	7	123	Consumer-facing promotional offers/redemption, budget-capped.
`ai`	intelligence	21	235	Model registry, deployment, prompt/routing config, the partitioned `agent_execution`/`agent_memory` ledgers — the AI-runtime substrate underneath `agents`.
`semantics`	intelligence	14	114	The shared business ontology (metrics, entities, dimensions, goals, constraints) that `agents`/`signals` read from rather than each inventing their own vocabulary.
`signals`	intelligence	11	111	Partitioned, bitemporal feature/forecast/outcome-observation store — the highest-risk schema in the build (tenant-spoof-resistant `SECURITY DEFINER` reads, split-authority pattern).
`agents`	intelligence	47	475	The agent-orchestration runtime: tasks, kill switch, saga gate, skill/tool grants, evidence trail, shadow-mode — the single biggest schema.
Total		332	4,586

Module numbering: this codebase's own build history refers to some of these by an explicit "module #N" (e.g. platform=#1, crm=#5, ai=#6, inventory=#12, pricing=#13 … agents=#28), assigned in build order including several unbuilt v1-planned modules (search, audit, notifications, reporting, consumer_app as a separate concept from consumer, integrations) that occupy numbers in the sequence without a schema existing for them. Not every live schema above has a confirmed historical number in the record reviewed for this catalog (identity, shared, multi_loc, admin, approvals, semantics, signals don't have an explicit "(module #N)" citation found in this build's own narrative docs) — rather than guess, this table omits the column. If exact numbering matters for your review, cross-reference docs/modules/MODULE_BUILD_STATUS.md, which itself has known staleness (see §6).

2. Layer rules

Layer order is foundation → business → consumer → intelligence, and the rule is a one-way dependency DAG within the first three layers: a schema in a lower layer must never hold a real foreign key into a higher layer.

Foundation (platform, identity, shared, multi_loc, files, admin, approvals) — owns cross-cutting concerns every other schema depends on (tenant identity, principals, reference data, sites, file storage, tenant config, the approval engine). Foundation schemas may FK into each other (e.g. identity→platform, admin→identity) but never into business/consumer/intelligence. files is the strictest example: file.consumer_id and file_access_grant.grantee_customer_id are deliberately loose, unenforced columns — a real FK from a foundation-layer table into the consumer layer is structurally forbidden, confirmed live via pg_constraint that none exists.
Business (crm, inventory, pricing, pos, orders, purchasing, receiving, tax, billing, payments, returns) — the sell path (crm→pricing→inventory→orders→pos) and buy path (purchasing→receiving) plus their financial settlement (tax/billing/payments). May FK into foundation and into each other; never into consumer.
Consumer (consumer, rewards, offers) — the cross-tenant-boundary layer, gated behind its own dedicated Postgres role (consumer_authenticated, structurally separate from the merchant authenticated role — a genuinely different connection, not just a different RLS predicate). May FK into foundation and read business-layer identifiers, but the reverse is forbidden (business/foundation never FK into consumer).
Intelligence (ai, semantics, signals, agents) — a cross-cutting plane, not a fourth rung of the same DAG. It reads across every layer (evidence snapshots typed-FK into signals/semantics/files/ai) and — unlike the strict one-way layers above — has genuine bidirectional references within itself: agents↔ai and agents↔signals both have live FKs in both directions (confirmed via the cross-schema FK inventory below). This is disclosed as intentional in this codebase's own build record, not a DAG violation — the 4 intelligence schemas are one coupled subsystem (the AI Capability Plane's own runtime), not 4 independently-layered modules.

The polymorphic-pointer convention — where a real FK is structurally impossible (a column that can point at more than one target table, e.g. ar_charge.source_ref → either pos.sale or orders.order_header) or structurally forbidden (a foundation-layer table referencing a business/consumer-layer row), the codebase uses a plain, untyped uuid column with a paired discriminator (source_module/source_type + source_ref, or entity_type/entity_ref) and validates the shape via a CHECK constraint instead of a REFERENCES clause. There is no reciprocal column on the target side in most cases — the pointer is one-directional and the target table has no idea it's being referenced. This pattern recurs in billing.ar_charge.source_ref, tax.tax_calculation.source_ref, files.attachment.entity_type/entity_ref, signals.feature_value.entity_type/entity_ref, and others.

3. Cross-cutting conventions

A reviewer needs these to correctly interpret almost every table below — they are used dozens to hundreds of times across the schema and are NOT re-explained per table.

Composite tenant-scoped FKs, (child_col, tenant_id) → parent(id, tenant_id). The default FK shape for any tenant-scoped-to-tenant-scoped reference. The reason is structural, not stylistic: Postgres foreign-key constraint checks bypass Row-Level Security — a bare child_col uuid REFERENCES parent(id) lets a caller reference ANY row in parent regardless of tenant, RLS notwithstanding, because the FK check runs as the table owner, not the querying role. A composite FK against a UNIQUE(id, tenant_id) constraint on the parent forces the referenced row's tenant_id to equal the child's own tenant_id — the FK itself becomes a cross-tenant guard, independent of RLS. This codebase found and fixed this exact bug class repeatedly (a dedicated 2-batch "Header/Line Remediation" effort existed specifically to retrofit bare FKs into composite ones); a handful of known-bare FKs remain and are individually disclosed in OPEN_ITEMS (see §6).

The partition-key-aware variant, (id, tenant_id, <partition_key>). On a partitioned table (see below), a child FK targeting it needs the partition key in the parent's own unique constraint too — Postgres requires every unique/PK constraint on a partitioned table to include the partition key. Tables like agents.decision_context_snapshot (partitioned by created_at) carry UNIQUE(id, tenant_id, created_at) for exactly this reason, not merely UNIQUE(id, tenant_id).

Append-only enforcement, platform.reject_append_only_mutation(). A single shared trigger function, reused verbatim across every genuinely immutable ledger table in the codebase (sale lines, stock movements, agent decisions, evidence snapshots, tax jurisdiction breakdowns, and more). Enforcement is 2-layered, not just the trigger: a REVOKE UPDATE, DELETE FROM authenticated closes the ordinary write path, and the trigger is the backstop against any connection that still holds those privileges (migrations, the postgres superuser). A table is "append-only" in this catalog only when both layers are confirmed present.

The atomic single-statement counter pattern. Any running total (loyalty point balances, offer redemption budgets, stock reservation counts, kill-switch reversal caps) is maintained via a single UPDATE ... WHERE <cap check> RETURNING ... statement — never a separate read, application-layer check, then write. This codebase has a documented history of read-then-check-then-write races being found and fixed (the rewards/offers proportional-reversal bug is the canonical example: a fabricated $1,000 "reversal" against a $10 original redemption once silently succeeded because the old function had no magnitude check at all).

Header/line conventions. Three distinct sub-patterns, not one:

Decomposition (a total on a header, decomposed into lines that must reconcile to it) — e.g. orders.order_header/order_line, pos.sale/sale_line, purchasing.vendor_credit/vendor_credit_line. Either the header is the reconciliation source of truth (a trigger rejects a line write that would push SUM(lines) over the header's own stated total) or the lines are (a trigger recalculates the header from SUM(lines) after every line write) — both patterns exist in this codebase and are individually noted per table below.
Lifecycle (a request/authorization header with fulfillment lines) — e.g. returns.return_authorization/return_authorization_line.
Ledger fact (an append-only line is itself the fact; the header is just a grouping) — e.g. pos.sale/sale_line, where sale_line is append-only and immutable. Unit-price columns are named consistently unit_price_cents/unit_cost_cents (or _minor_units on the handful of tables using fractional-cent precision, per the money-unit-suffix convention in SCHEMA_CONVENTIONS.md §8.1: _cents is the default, _minor_units appears on pos and orders.order_line's own Hard-Contract-1 columns, _millicents on ai's sub-cent LLM cost columns).

The autonomy column pack (automation_source, the review_status quintet, decision_provenance). The standard 3-part pattern for any table an AI agent might write to autonomously: automation_source (who/what created this row — 'human' by convention almost everywhere, 'system' is a rare, individually-justified exception, e.g. tax.tax_calculation), a review_status quintet of related columns (status + reason + reviewer + reviewed-at, gating human sign-off on an agent-originated row), and decision_provenance (a jsonb column capturing what the agent knew/considered when it made the write). Present on any table where autonomy-first design applied — a cross-cutting retrofit across platform/identity/shared/multi_loc plus every module built afterward.

The dedicated-role isolation pattern (identity.operator, consumer_authenticated, agent_reader). Three distinct non-authenticated Postgres roles, each closing a different principal-isolation boundary:

identity.operator — Vrida-staff (cross-tenant) identity. Its own 2 tables get a belt-and-suspenders backstop: explicit REVOKE from authenticated plus ENABLE ROW LEVEL SECURITY with zero policies — Postgres denies every row to authenticated even if a future migration accidentally re-GRANTs table access, since an RLS-enabled table with no matching policy denies by default regardless of GRANT.
consumer_authenticated — the end-consumer boundary. NOLOGIN NOINHERIT, connects via a dedicated consumerDB() helper (SET LOCAL ROLE + set_config('app.current_consumer_id', ...)), holds zero grant on any merchant/tenant-scoped table.
agent_reader — the newest (2026-07-17), narrowly scoped to exactly 2 read surfaces (files.document_chunk/document_index SELECT, 3 signals.*_as_of() functions EXECUTE) via agentReaderDB(). A genuine, previously-undocumented Postgres behavior was found building this: a role-scoped RLS policy does not extend to a role outside its own TO clause — the pre-existing authenticated-scoped policies on document_chunk/document_index do not cover agent_reader queries at all, so 2 brand-new, agent_reader-scoped FOR SELECT policies had to be added alongside the GRANT, or every agent_reader query would have silently returned zero rows forever.

4. The 4 principal populations

Every table's tenant-isolation story reduces to one of 4 distinct principals, each isolated by a genuinely different mechanism — not 4 flavors of the same RLS predicate:

Principal	Home schema/table	Isolation mechanism	Connects via
Tenant staff	`identity.actor` → `identity.tenant_user`/`service_account`/`agent_identity` (subtype tables sharing the `actor` PK)	RLS: `tenant_id = current_setting('app.current_tenant_id')::uuid`, scoped to the `authenticated` role	`tenantDB()` — `SET LOCAL ROLE authenticated` + `set_config('app.current_tenant_id', ...)`
Vrida operators	`identity.operator` + `identity.operator_role_assignment`	Structural: `REVOKE` from `authenticated` and RLS-enabled-zero-policy (denies even a future accidental re-GRANT) — operators are cross-tenant by design, not scoped to any one `tenant_id`	`AdminAuthGuard`, resolved against `identity.operator` exclusively (not the older `identity_user.is_platform_user` boolean, deprecated in place)
Consumers (end customers)	`consumer.consumer` (10-table schema)	A dedicated, separate Postgres role (`consumer_authenticated`) — structurally distinct from `authenticated`, not just a different RLS predicate on the same role	`consumerDB()` — `SET LOCAL ROLE consumer_authenticated` + `set_config('app.current_consumer_id', ...)`
AI agents	`identity.agent_identity` (an `actor` subtype, so agents are tenant-scoped staff-like principals for RBAC/RLS purposes) + `agent_reader` for the narrow read-only path described in §3	Same RLS as tenant staff for anything routed through `tenantDB()`; the newer `agent_reader` role is a STRICTER, narrower-GRANT alternative for specific read paths, not a replacement	`agentReaderDB()` for the 2 scoped read surfaces; otherwise (today) whatever connection the calling service uses — no `AgentsService` exists yet to have made this choice for real traffic (see §7)

Agents are the one principal that spans two rows of this table: they're a tenant-scoped actor subtype (RBAC/duty-grant identical to a human staff member) for authorization purposes, but get their OWN narrower connection role (agent_reader) for the 2 specific high-volume read surfaces named above — a deliberate belt-and-suspenders narrowing on top of, not instead of, the standard tenant-staff RLS boundary.

5. Known open items / disclosed gaps

docs/open-items/OPEN_ITEMS.md currently holds 283 open rows and 21 closed rows (304 total) across the whole build. That count is not a defect in itself — it is this codebase's own discipline of logging every deferred decision rather than silently dropping it (a documented "Bug Class #11: claimed-but-unlogged deferral" is treated as a build defect in its own right). A reviewer should know the shape of what's open rather than rediscover it independently:

The agent_autonomy_profile.current_mode PROHIBITION (agents schema, 2026-07-17). current_mode — the column declaring how autonomous an agent is permitted to be — has NO structural database enforcement; it's a label. This is formally logged as a PROHIBITION, not a note: no agent may execute in production until this binding is designed against a real execution path and live-reproduced (an agent claiming draft_only structurally unable to commit an autonomous write). Not currently exploitable (no agent runtime exists) and not the only gate (duty grants, tool grants, skill certification, the saga boundary, and the kill switch are all independently, structurally enforced already) — a disclosed redundancy gap, not an unguarded crown jewel. See docs/modules/module_spec/agents.md §5 for the cross-referenced callout.
Bare (non-composite) cross-tenant FKs, individually logged as they're found — this codebase ran 2 dedicated remediation batches specifically hunting this bug class (PROJECT_DECISIONS #46–#53) and still has known stragglers, e.g. agents.agent_skill_assignment.agent_identity_id (found 2026-07-17, root cause: identity.agent_identity itself lacks the UNIQUE(id, tenant_id) prerequisite; not currently exploitable because the sole write path enforces the tenant match at the application layer) and purchasing.vendor_return_line's 3 other bare FKs (disclosed, not yet fixed).
Missing indexes, all individually disclosed as low-severity and deliberately deferred until a real query pattern needing them exists — e.g. 12 tenant-scoped tables in agents with no tenant_id-leading index (agent_eval_case, agent_event_log, agent_shadow_decision, agent_thread, 5 decision_context_* tables, evidence_retention_policy, kill_switch_event, rollback_execution — RLS correctness is unaffected either way, since RLS enforcement doesn't depend on index presence), plus several smaller ones in identity (role_assignment, actor.status, actor_group, api_key.last_used_at).
A pre-existing, environment-level Postgres connection-pool-exhaustion flake, documented since PROJECT_DECISIONS #37 and re-confirmed independently at least twice since (#66, #67): npx jest --forceExit --runInBand intermittently fails a batch of tests with "remaining connection slots are reserved for roles with the SUPERUSER attribute" when run back-to-back with other heavy DB activity on the local dev instance; a clean re-run after the connection pool settles is reliably green. Confirmed NOT a code defect — one verification pass isolated it via git stash against the identical already-migrated DB and still reproduced it with the build's own code fully reverted.
No SharedService was ever built, despite inventory, pricing, AND crm all independently becoming real FK consumers of shared over the course of the build — the original "build it when the first consumer arrives" trigger condition fired 3 times with nothing built. Logged as a retired trigger condition, not silently dropped (see shared open items).
A standing, not-yet-run "final cross-module integration audit" is logged as a required pre-migration/pre-launch step: a one-time, system-wide walk of every cross-schema FK across all 25 schemas plus every locked cross-module decision, to catch drift the per-module Section-4 audits (which check internal + adjacent seams only) would miss. Has not been run.

Full detail for any of the above, plus everything not summarized here, lives in docs/open-items/OPEN_ITEMS.md — grep by schema name.

6. Known documentation staleness (found while building this catalog)

docs/modules/MODULE_INDEX.md carries its own explicit, self-flagged "Known header/row discrepancy (flagged 2026-06-30, not yet resolved)" — its own summary header undercounts against summing its own listed rows, and has not been reconciled since.
docs/modules/MODULE_BUILD_STATUS.md's module table has at least 2 confirmed staleness gaps found while assembling this catalog: consumer shows unbuilt (⬜) despite the schema existing live with 10 tables (built the same day as rewards/offers, which DO show built — an update that was made for 2 of 3 modules built together but not the third); receiving has no row at all despite being a live, locked schema.
This session's own prior final report (delivered earlier today, before this catalog) stated "312 tables, 4,691 columns" — both numbers were arithmetic errors from manually summing a 25-row table by eye. This catalog's own totals (332 tables / 4,586 columns) were computed by a SQL aggregate against the live catalog and supersede that figure.
Any further discrepancies found by the per-schema fan-out below are listed in that schema's own "Discrepancies found" subsection, not summarized here.

7. What is NOT built

This is a schema, not a running system. Nothing in this catalog has ever executed against real production traffic — everything below is inferred from the live structure, not from observed behavior, because there is no observed behavior yet.

Service layer: 2 of 25 modules. Only identity.service.ts and platform.service.ts exist as real NestJS service implementations (confirmed via find apps/api/src -iname "*.service.ts"). Every other schema — including large, structurally complex ones like inventory (25 tables), agents (47 tables), and purchasing (15 tables) — has zero service-layer code. Reading a table's trigger/CHECK/RLS shape tells you what the DATABASE will enforce if something writes to it; it tells you nothing about what any application code actually does, because for 23 of 25 modules, no application code exists.
3 schemas have no application-layer scaffold at all: agents, semantics, and signals have no corresponding directory under apps/api/src/ whatsoever — not even an empty NestJS module stub. (Their own regression tests live under apps/api/src/platform/__tests__/, borrowed space, not their own module.)
No HTTP controller layer for the vast majority of the built API surface. PlatformService and IdentityService have partial controller coverage; nothing else does.
No agent has ever executed. The entire agents/ai/signals/semantics intelligence plane — kill switch, saga gate, skill grants, evidence trail, the newly-added agent_reader role — is proven correct only in the sense that its OWN guards were live-reproduced directly against the database via test fixtures and psql sessions, never via a real agent making a real decision. The current_mode PROHIBITION in §5 is the sharpest instance of this: the reason it's a hard prohibition rather than a scheduled fix is that there is no real execution path yet to design the fix against.
Both apps/web/admin and apps/web/tenant are, per this codebase's own build history, "mostly sample-data mockups." Real data wiring exists for a subset of platform/identity pages; the rest render mock data.
Practical implication for this review: every guard described in this catalog (RLS policy, CHECK constraint, append-only trigger, kill switch, saga gate) has been independently, adversarially verified to work AT THE DATABASE LAYER, live, against the schema as built — that discipline is real and consistent across all 25 schemas. What has NOT been verified, because it doesn't exist yet, is that any application code actually calls the database the way these guards assume it will.

8. Per-schema catalog

8.1 Foundation layer

`platform`

Owns: The platform schema is Vrida's own SaaS operating layer — everything about running Vrida-as-a-business on top of its tenants, as distinct from what a tenant sells to its own customers. It covers tenant identity and lifecycle (tenant, tenant_profile, tenant_contact, legal_entity), Vrida's commercial relationship with each tenant (billing_account, subscription, subscription_invoice/_line, payment, tier_definition, promo_code, contract, tenant_entitlement), the AI-credit/AI-capacity governance layer (ai_credit_account/_transaction, ai_capacity_policy, tenant_ai_capacity_usage), the module registry and per-tenant module on/off switch (module_catalog, module_dependency, tier_module_entitlement, tenant_module_activation), legal/compliance/audit plumbing (agreement_version/_acceptance, tenant_data_lifecycle, tenant_lifecycle_event, tenant_internal_activity, operator_audit_log, accounting_period, tenant_regional_policy), and cross-cutting infrastructure (outbox, platform_setting, tenant_setup_task, tenant_usage_summary, announcement, polymorphic_target_registry). Layer: foundation Tables: 35 · Columns: 518 Depends on: identity, shared Depended on by: see cross-schema FK inventory, except where Drizzle discloses it explicitly: legal_entity is consumed by a nullable entity_id FK on 10 header tables across admin (compliance_document), tax (tax_calculation), billing (ar_account, vendor_payable), purchasing (vendor_invoice, purchase_order), orders (order_header), and pos (sale) (per PROJECT_DECISIONS #40), plus platform.contract/billing_account themselves; and accounting_period's flag-not-reject business-date check is consumed by pos.sale/sale_refund/register_cash_entry via the shared trigger function platform.flag_closed_period_business_date().

Table	Cols	Purpose	Key characteristics
`accounting_period`	9	Tenant fiscal-period closing; a closed period doesn't block transactions, it flags out-of-period ones for review (offline-sync safe).	tenant-scoped, RLS: `tenant_id=current_setting('app.current_tenant_id')`, EXCLUDE overlap guard (no two periods for a tenant may overlap — raw-SQL `EXCLUDE USING gist`, not visible in Drizzle's table builder), flag-not-reject fiscal-close pattern
`agreement_acceptance`	11	Immutable record of a tenant user accepting a specific legal agreement version (TOS/MSA/DPA/privacy policy).	tenant-scoped, RLS, structurally append-only (no `updated_at`/`deleted_at`, PK via `platform.uuid_generate_v7()`) but not REVOKE/trigger-enforced — `authenticated` retains full CRUD live
`agreement_version`	11	Version registry for Vrida's own legal documents — one row per published version.	global, no RLS, version-row pattern (self-FK `supersedes_agreement_version_id` chains prior versions), deactivate-not-delete (`is_active`, no `deleted_at`)
`ai_capacity_policy`	10	One shared policy-binding model implementing the 6-level AI-capacity precedence chain (platform/tier/tenant/workload_class/skill/task ceiling on concurrency/cost/steps/tokens).	global, no RLS ("RLS cannot itself interpret a polymorphic column's meaning" per Drizzle comment), polymorphic ref (`scope_ref` deliberately bare uuid, meaning depends on `scope_type`, no FK possible)
`ai_credit_account`	13	Per-tenant AI usage prepaid-credit wallet (granted + purchased balance, spend limit, active/suspended/closed status).	tenant-scoped, RLS, one-per-tenant (partial unique on `tenant_id` WHERE `deleted_at IS NULL`)
`ai_credit_transaction`	11	Ledger of every AI credit movement (grant/purchase/consumption/refund/adjustment) against a tenant's `ai_credit_account`.	tenant-scoped, RLS, structurally append-only (no `updated_at`/`deleted_at`, PK via `platform.uuid_generate_v7()`) but not REVOKE-enforced, cached counter source for `ai_credit_account.balance_cents`
`announcement`	13	Vrida-authored broadcast messages shown to tenants (info/maintenance/critical), globally or targeted to one tenant.	mixed-scope (`tenant_id` nullable — NULL = all tenants), RLS: `tenant_id IS NULL OR tenant_id=current_setting(...)` (the NULL branch is load-bearing per comment), skeleton table (no write endpoint yet)
`billing_account`	22	Vrida's own billing relationship with the tenant — Stripe customer/payment-method, tax-exempt status, invoice delivery, payment terms.	tenant-scoped, RLS, one-per-tenant (partial unique on `tenant_id`), optional `legal_entity` scoping (nullable `entity_id`)
`contract`	23	Enterprise sales-led contracts governing a tenant's subscription terms.	tenant-scoped, RLS, review seam (`review_status`/`review_reason`/`reviewed_by_actor_id` — human-in-the-loop gate, future-proofed for agent-drafted terms), optional `legal_entity` scoping
`legal_entity`	8	A tenant's legal incorporation(s) — lets one tenant span multiple LLCs without splitting into separate Vrida tenants.	tenant-scoped, RLS, exactly-one-primary-per-tenant (partial unique WHERE `is_primary=true`), widely depended-on (see "Depended on by")
`module_catalog`	8	Global registry of every Vrida module (identity + lifecycle only) — replaces the closed CHECK-enum module-tag columns.	global, no RLS, forward-only lifecycle guard (`trg_module_catalog_lifecycle_transition`: planned→designed→in_build→active→deprecated→retired)
`module_dependency`	5	Prerequisite graph between modules (module X requires module Y active before it may itself activate).	global, no RLS, cycle-prevention guard (`trg_module_dependency_no_cycle`, a real recursive check)
`operator_audit_log`	13	Immutable, cross-tenant compliance log of every Vrida-operator action (tenant status changes, credit grants, impersonation, etc.).	operator-only (rowsecurity=false but `authenticated` has zero GRANT at all — REVOKEd; reachable only via the superuser/service-role connection), structurally append-only, `tenant_id` nullable (some actions are global)
`outbox`	13	Transactional-outbox event table — written in the same transaction as the domain change it describes, delivered asynchronously by a not-yet-built consumer.	tenant-scoped, RLS, deliberately NOT append-only (row mutates as delivery is attempted: status/attempts/delivered_at/error), no dispatcher service exists yet
`payment`	19	Each payment the tenant makes to Vrida against a `subscription_invoice` (distinct from `billing.ar_payment`, merchant-side A/R).	tenant-scoped, RLS, header/line-adjacent: composite FK `(invoice_id, tenant_id) → subscription_invoice(id, tenant_id)` (upgraded from a bare FK 2026-07-10)
`platform_setting`	6	Vrida-wide key-value configuration; grows by new row, not new column.	global, no RLS, skeleton table (no write endpoint yet)
`polymorphic_target_registry`	4	Validator/lookup for genuinely-unavoidable polymorphic FK references elsewhere in the codebase — maps a `target_type` to its real `(schema, table, tenant_column)` so `platform.validate_polymorphic_reference()` can dynamically verify a referenced row exists in-tenant.	global, no RLS, polymorphic ref (the table's whole purpose; consumed by `ai.agent_execution`/`agents.decision_context_snapshot`/`files.document_chunk`/`signals.outcome_observation` entries), absent from Drizzle source entirely — see Discrepancies
`promo_code`	19	Vrida-issued subscription discount/promo codes, redeemable across signups.	global, no RLS, soft-delete (`deleted_at`, unlike sibling reference tables here), automation_source provenance column
`subscription`	28	The tenant's active Vrida SaaS plan — tier, billing cycle, trial/period dates, Stripe subscription ID, seasonal pause, dunning state.	tenant-scoped, RLS, one-active-per-tenant (partial unique WHERE `status NOT IN ('cancelled','ended') AND deleted_at IS NULL`), `tier_code` is a soft (non-FK) ref to `tier_definition.tier_code`
`subscription_invoice`	26	One Vrida billing-cycle invoice issued to the tenant.	tenant-scoped, RLS, header/line: `subscription_invoice` ↔ `subscription_invoice_line`, deprecated-in-place column (`line_items` JSONB, superseded by the line table)
`subscription_invoice_line`	10	Per-line decomposition of a `subscription_invoice`'s total (base subscription, AI-credit overage, seats, proration, discount, tax, etc.).	tenant-scoped, RLS, header/line: `subscription_invoice` ↔ `subscription_invoice_line` (LINES ARE TRUTH — `trg_subscription_invoice_line_sync_totals` recomputes the header's subtotal/discount/tax/total from `SUM(lines)`), write-once (no status/updated_at/deleted_at), PK via `platform.uuid_generate_v7()`
`tenant`	15	Root record for every Vrida tenant — identity, subscription tier, timezone, feature flags, module prefs, lifecycle status.	global (this IS the root — no `tenant_id`, no RLS, no `deleted_at`), lifecycle-via-status (`trial`→`active`→`past_due`/`suspended`→`cancelled`→`pending_deletion`→`deleted`), forward-ref column (`primary_site_id` → `multi_loc.site`, still unwired)
`tenant_ai_capacity_usage`	10	Per-tenant, per-period rolling counters (spend, concurrency, queued tasks, memory/vector storage bytes) against `ai_capacity_policy` ceilings.	tenant-scoped, RLS, cached counter (atomic-upsert running totals, enforced via `platform.try_increment_ai_capacity_spend()` — "NEVER an application-level read-then-check-then-write" per Drizzle comment)
`tenant_contact`	20	Named business contacts for a tenant (owner/billing/legal/admin/technical/CS-owner) plus CS fields (NPS, churn risk) and marketing-consent flags.	tenant-scoped, RLS, optional link to `identity.identity_user` (not all contacts are app users)
`tenant_data_lifecycle`	20	Post-cancellation data handling workflow (export/retention/deletion/legal-hold) for a tenant.	tenant-scoped, RLS, permanent record (no `deleted_at` — compliance/audit record)
`tenant_entitlement`	24	Source of truth for tenant feature access — absorbs add-ons as entitlements (`source_type`=tier/addon/override/beta/contract); answers "why does this tenant have access to X?"	tenant-scoped, RLS, automation_source provenance + `metadata` doubling as decision-provenance carrier for agent-granted entitlements, cached counter (`used_value` against `limit_value`)
`tenant_internal_activity`	13	Internal CS/admin activity log for a tenant (notes, admin actions, NPS/churn updates, credit/trial/tier changes).	tenant-scoped, RLS, structurally append-only (no `updated_at`/`deleted_at`, PK via `platform.uuid_generate_v7()`) but not REVOKE-enforced
`tenant_lifecycle_event`	12	Immutable event log of every tenant status transition (created/activated/suspended/cancelled/etc.).	tenant-scoped, RLS, structurally append-only (no `updated_at`/`deleted_at`, PK via `platform.uuid_generate_v7()`) but not REVOKE-enforced
`tenant_module_activation`	10	The real per-tenant module on/off switch (active/inactive/beta/suspended), one row per (tenant, module) — replaces the dead `is_toggleable` mechanism.	tenant-scoped, RLS: `tenant_module_activation_tenant_isolation` (live but absent from Drizzle source — see Discrepancies), dependent-module guard (`trg_tenant_module_activation_check_dependents` blocks deactivation while another active module still hard-depends on it)
`tenant_profile`	39	Extended tenant business profile — legal identity (name/DBAs/EIN vault ref), addresses, firmographics, ecommerce flags, onboarding/acquisition attribution; single source of truth for tenant identity (absorbed from `admin.tenant_business_profile`, PROJECT_DECISIONS #34/#35).	tenant-scoped, RLS, one-per-tenant (partial unique WHERE `deleted_at IS NULL`), 2 deprecated-in-place columns (`tax_id`→`ein_ref`, `logo_url`→future `admin.tenant_branding.logo_ref`, comment-only, still live/writable)
`tenant_regional_policy`	14	One resolvable regional-placement policy per tenant — where processing/model/object-storage/vector-storage may occur, cross-region replication, retention floors, encryption-key region.	tenant-scoped, RLS, one-per-tenant (unique on `tenant_id`), documented-shape JSONB columns (shapes given in Drizzle comment, not schema-enforced)
`tenant_setup_task`	20	Tracks technical provisioning + business onboarding tasks per tenant (schema created, storage initialized, business profile completed, go-live, etc.).	tenant-scoped, RLS, permanent record (no `deleted_at`), one row per (tenant, task_code)
`tenant_usage_summary`	12	Monthly usage snapshot per tenant (active users, sites, SKUs, POS sales, orders, AI calls, storage bytes) for billing/reporting.	tenant-scoped, RLS, structurally append-only (no `updated_at`/`deleted_at`, PK via `platform.uuid_generate_v7()`, one row per (tenant, period_start)) but not REVOKE-enforced, cached counter (aggregates sourced from other schemas)
`tier_definition`	20	Reference data for each subscription tier (Starter/Pro/Enterprise) — pricing, seat/site/SKU caps, entitled modules, permitted add-ons.	global, no RLS, deactivate-not-delete (`is_active`, no `deleted_at`), `entitled_modules` JSONB now deprecated in favor of `tier_module_entitlement`
`tier_module_entitlement`	7	Which modules a subscription tier includes by default — replaces `tier_definition.entitled_modules` (whose JSONB shape "never matched its own seed data" per Drizzle comment).	global, no RLS, join table (`tier_id`, `module_id`), unique(`tier_id`, `module_id`)

Discrepancies found (platform)

polymorphic_target_registry (35th table, 4 cols) exists live but has zero Drizzle source anywhere in packages/db/src/schema/platform/*.ts. It was created by raw SQL directly in the agents module's migration (packages/db/migrations/20260716000000_agents_module_new_schema.sql, Section 3), physically placed in the platform schema even though the module being built was agents. packages/db/src/schema/platform/index.ts's barrel export has no corresponding file/table object for it — anyone generating a schema diagram from Drizzle alone would miss this table entirely.
docs/database/schema_docs/platform.md is one table behind live. The doc's most recent dated entry (2026-07-12, "6th reopen — agents-v2/v3 build, Phase 1 of 6") brings it to 34 tables / 514 cols, and it has exactly 34 ### platform.* sections. Live DB has 35 tables / 518 cols — polymorphic_target_registry (added 2026-07-16 by the agents-module migration referenced above) has no entry, no changelog line, and no delta accounting anywhere in the doc.
tenant_module_activation's Drizzle definition is missing its own RLS policy. packages/db/src/schema/platform/module_registry.ts calls .enableRLS() on this table but its constraints array contains no pgPolicy(...) call — yet the live DB (per the fact file's RLS section) has a real, active policy tenant_module_activation_tenant_isolation, and the hand-written migration (20260712000000_platform_module_registry_capacity.sql) does CREATE POLICY tenant_module_activation_tenant_isolation ... explicitly. Every sibling table built in the same migration (tenant_ai_capacity_usage, tenant_regional_policy) has its policy correctly mirrored in Drizzle; this one table's Drizzle source is out of sync with the migration that actually shipped.
polymorphic_target_registry's live grants exceed its own migration's stated intent. The migration explicitly does GRANT SELECT ON platform.polymorphic_target_registry TO authenticated (read-only reference data, matching the comment's framing as a lookup/validator table). But the fact file shows authenticated actually holds INSERT,UPDATE,DELETE,SELECT on it live — full CRUD — unlike every other reference-data sibling in this schema (module_catalog, module_dependency, tier_definition, tier_module_entitlement, ai_capacity_policy, all correctly SELECT-only live). This is consistent with a pre-existing blanket per-schema default-privilege grant on platform (established in Remediation Phase 1) silently superseding the migration's own narrower GRANT SELECT — a genuine, live over-permission on a table meant to be tenant-read-only.
Everything else independently cross-checked (table list, column-level types/nullability, RLS scoping shape, FK targets including the two composite FKs, and the append-only-by-convention-but-not-REVOKE-enforced pattern on agreement_acceptance/ai_credit_transaction/tenant_internal_activity/tenant_lifecycle_event/tenant_usage_summary) is consistent across the live DB, Drizzle source, and docs/database/schema_docs/platform.md.

`identity`

Owns: The identity schema is Vrida's foundation-layer identity and access-management system. It defines the polymorphic actor root that every principal — human tenant staff, Vrida cross-tenant operators, AI agents, and machine service accounts — shares as a common identity anchor via class-table inheritance, then layers on: tenant membership and grouping (tenant_user, actor_group, actor_group_member), multi-site access (user_site_assignment, invitation_site_assignment), a global RBAC engine (permission, permission_group(_permission), role, role_permission(_group), role_template(_permission_group), role_assignment) plus Segregation-of-Duties detection (sod_rule(_permission), sod_violation), self-service/support access grants (access_request, support_access_grant), session and audit trails (identity_session, identity_access_event), enterprise-auth integrations (sso_provider, scim_config, password_policy, tenant_security_policy), machine credentials (service_account, api_key), AI-agent governance (agent_type_catalog, agent_identity with its kill-switch, agent_duty_grant — the A5 per-permission autonomy passport), the invitation lifecycle (invitation), and a fully isolated, backend-only Vrida-operator identity system (operator, operator_role_assignment). Layer: foundation Tables: 36 · Columns: 410 Depends on: multi_loc, platform, shared Depended on by: see cross-schema FK inventory

Table	Cols	Purpose	Key characteristics
`access_request`	19	Self-service request from an actor for a role grant or a permission override, routed for approval.	tenant-scoped, RLS: tenant_id=current_setting(...), polymorphic ref (requested_scope_id bare uuid, no FK — deferred to multi_loc.site), guardrail: request-type/scope consistency CHECKs
`actor`	7	Polymorphic identity root shared by every principal type (user/service_account/agent/operator) via shared-PK class-table inheritance.	global, no RLS
`actor_group`	10	Tenant-defined named collection of actors (team/department/custom) for group-level role assignment.	tenant-scoped, RLS: tenant_id=current_setting(...)
`actor_group_member`	9	Join: actor ↔ actor_group membership.	tenant-scoped, RLS: tenant_id=current_setting(...)
`agent_duty_grant`	25	Per-(agent, permission) autonomy passport: authority level (may_act_alone/draft_only/needs_approval) plus spend/quantity ceiling and scope, additive to role-based allow/deny.	tenant-scoped, RLS: tenant_id=current_setting(...), guardrail: self-issue block (granted_by_actor_id ≠ agent_identity_id), review/provenance columns (automation_source, review_status, decision_provenance)
`agent_identity`	16	AI-agent actor detail record (shared PK with actor); carries the agent kill-switch.	tenant-scoped, RLS: tenant_id=current_setting(...), guardrail: kill-switch (status active/suspended/killed + suspended_at consistency CHECK)
`agent_type_catalog`	8	Vrida-seeded catalog of AI agent types with default config templates.	global, no RLS (SELECT-only grant, reference data)
`api_key`	15	Hashed API key belonging to a service_account for machine-to-machine auth.	tenant-scoped, RLS: tenant_id=current_setting(...)
`identity_access_event`	16	Append-only audit log of every auth/authorization event (login, MFA, role change, SoD, access-request, support-access, agent-duty lifecycle, etc.).	tenant-scoped (nullable tenant_id for platform-level events), RLS: tenant_id=current_setting(...), append-only (trigger `reject_append_only_mutation`), polymorphic ref (resource_type/resource_id pair, no FK)
`identity_session`	13	Vrida's own session record (start/last-active/end), distinct from Supabase Auth's internal session state.	global, no RLS
`identity_user`	11	Global human user record, shared PK with actor; bridges to Supabase Auth via supabase_auth_user_id.	global, no RLS
`invitation`	18	Pending invite to join a tenant with a pre-assigned role; only a token hash is stored.	tenant-scoped, RLS: tenant_id=current_setting(...)
`invitation_site_assignment`	6	Pre-acceptance staging of intended per-site access recorded at invite time.	tenant-scoped, RLS: tenant_id=current_setting(...)
`operator`	14	Vrida-operator (cross-tenant SaaS staff) identity, isolated from tenant users, shared PK with actor; carries its own kill-switch. Backend-created only, no self-registration.	global, operator-only (REVOKE + zero-policy RLS)
`operator_role_assignment`	8	Time-bounded operator→role grant from a small fixed CHECK-enum (super_admin/admin/support).	operator-only (REVOKE + zero-policy RLS), guardrail: self-issue block
`password_policy`	12	Vrida-wide singleton password policy (length/complexity/MFA/rotation) governing email+password users.	global, no RLS (SELECT-only grant, reference data)
`permission`	9	Global catalog of permission codes (module.resource.action) used at every authorization check.	global, no RLS (SELECT-only grant, reference data)
`permission_group`	8	Named bundle of permissions.	global, no RLS (SELECT-only grant, reference data)
`permission_group_permission`	4	Join: permission_group ↔ permission.	global, no RLS (SELECT-only grant, reference data); documented append-only convention in Drizzle, not trigger-enforced
`role`	14	Role definition; mixed-scope (tenant_id NULL = built-in/Vrida-internal, populated = tenant custom), single-parent self-FK inheritance.	tenant-scoped/global mixed, RLS: tenant_id IS NULL OR tenant_id=current_setting(...), guardrail: self-loop prevention CHECK; `requires_approval_for_agents` flag feeds the agent-elevation approval gate
`role_assignment`	13	Time-bounded assignment of a role to an actor OR an actor_group.	tenant-scoped, RLS: tenant_id=current_setting(...), guardrail: polymorphic-assignee CHECK (exactly one of actor_id/actor_group_id)
`role_permission`	5	Join: role ↔ permission with an allow/deny effect.	RLS: subquery via role.tenant_id (no direct tenant_id column on this table); documented append-only convention in Drizzle, not trigger-enforced
`role_permission_group`	7	Tenant-scoped wiring attaching a permission_group bundle to a tenant custom role.	tenant-scoped, RLS: tenant_id=current_setting(...)
`role_template`	8	Vrida-seeded catalog of role templates tenants can clone (fully independent post-clone).	global, no RLS (SELECT-only grant, reference data)
`role_template_permission_group`	4	Join: role_template ↔ permission_group.	global, no RLS (SELECT-only grant, reference data); documented append-only convention in Drizzle, not trigger-enforced
`scim_config`	11	Per-tenant SCIM provisioning configuration (one row per tenant).	tenant-scoped, RLS: tenant_id=current_setting(...)
`service_account`	9	Non-human machine actor for API access, shared PK with actor.	tenant-scoped, RLS: tenant_id=current_setting(...)
`sod_rule`	9	Vrida-defined Segregation-of-Duties rule catalog; detect-and-flag only, never blocks.	global, no RLS (SELECT-only grant, reference data)
`sod_rule_permission`	4	Join: sod_rule ↔ permission (the conflicting permission set for a rule).	global, no RLS (SELECT-only grant, reference data); documented append-only convention in Drizzle, not trigger-enforced
`sod_violation`	17	Detected SoD rule violation for an actor with ack/waive/resolve lifecycle and a decision_snapshot for forensic reconstruction.	tenant-scoped, RLS: tenant_id=current_setting(...)
`sso_provider`	19	Per-tenant SSO configuration (SAML/OIDC); secrets stored as vault references only, never raw values.	tenant-scoped, RLS: tenant_id=current_setting(...), guardrail: at most one active/testing provider per tenant (partial unique)
`support_access_grant`	15	Time-boxed Vrida-support access grant to a tenant, combining authorization and audit.	tenant-scoped, RLS: tenant_id=current_setting(...)
`tenant_security_policy`	8	Per-tenant singleton security overrides (session timeout, MFA floor, concurrent-session cap, API-key rotation).	tenant-scoped, RLS: tenant_id=current_setting(...)
`tenant_user`	13	Membership join — "this actor belongs to this tenant" — for human staff only.	tenant-scoped, RLS: tenant_id=current_setting(...), guardrail: trigger-enforced actor_type='user' check (`trg_tenant_user_actor_type_check`)
`user_permission_override`	15	Per-user permission allow/deny that overrides the role-derived result, scoped to tenant/site/module.	tenant-scoped, RLS: tenant_id=current_setting(...), polymorphic ref (scope_id bare uuid, no FK — deferred to multi_loc.site), guardrail: scope-consistency CHECK
`user_site_assignment`	11	Per-tenant-user site access grant, optionally with a site-specific role override.	tenant-scoped, RLS: tenant_id=current_setting(...)

Discrepancies found (identity)

identity_access_event.session_id: docs/database/schema_docs/identity.md documents this column as FK → identity.identity_session, but neither the live DB (the fact file's FOREIGN KEYS section lists no constraint on this column) nor the Drizzle source (session_id: uuid('session_id'), in packages/db/src/schema/identity/events.ts — no .references() call) actually enforces it. It is a bare, unvalidated uuid reference in practice, not a real FK as the docs claim.
Everything else checked (table count, column count, RLS shapes, the operator/operator_role_assignment REVOKE-plus-zero-policy backstop, the multi_loc.site composite-FK wiring on tenant_user.default_site_id/user_site_assignment.site_id/invitation_site_assignment.site_id, and the still-deferred bare access_request.requested_scope_id/user_permission_override.scope_id columns) is consistent across the live-DB fact file, the Drizzle source, and the docs.

`shared`

Owns: Global, non-tenant reference/dictionary data used across every other module: ISO standards for geography (country, administrative subdivisions), currency and exchange rates, language/locale (BCP 47), units of measure, horticultural hardiness/climate zones, payment-terms vocabulary, and a thin global botanical (plant) reference with AI-enrichable common names and hardiness ranges. Nothing here is tenant data — it's the shared vocabulary tenant-scoped modules (crm, pricing, inventory, purchasing, consumer) hang their own rows off of.

Layer: foundation Tables: 12 · Columns: 136 Depends on: identity (plant, plant_climate_zone, plant_common_name each have created_by_actor_id/reviewed_by_actor_id → identity.actor) Depended on by: see cross-schema FK inventory — the one consumer named directly in this schema's own Drizzle source is ai (plant.ts: "required by the already-locked AI→Shared seam... AIService writes rows with data_source='ai_generated', is_verified=false" into plant/plant_common_name/plant_climate_zone); payment_terms_catalog.ts's comments additionally name purchasing.vendor, purchasing.purchase_order, and crm.customer.credit_terms as the legacy CHECK-enum columns this catalog is meant to eventually replace (not yet a live FK per this schema's own source).

Table	Cols	Purpose	Key characteristics
administrative_region	10	ISO 3166-2 subdivision dictionary (states/provinces/etc.), replacing v1's US-only `us_state`.	global (no tenant_id, no RLS), self-referencing hierarchy (`parent_region_code`, DB self-loop CHECK only — deeper cycle prevention is app-enforced)
climate_zone	10	Multi-system hardiness/climate zone dictionary (USDA, RHS, AHS Heat, Australian, EU).	global (no tenant_id, no RLS)
country	12	ISO 3166-1 country dictionary.	global (no tenant_id, no RLS), circular ref with `locale` (`default_locale_code` ⇄ `locale.country_code`, both nullable, two-pass seed)
currency	9	ISO 4217 currency dictionary.	global (no tenant_id, no RLS)
exchange_rate	7	Currency-pair exchange rates by effective date.	global (no tenant_id, no RLS), uuid surrogate PK (deliberate exception to this schema's natural-key convention — no composite-PK precedent exists codebase-wide)
language	8	ISO 639 language dictionary.	global (no tenant_id, no RLS)
locale	8	BCP 47 locale dictionary (language × country).	global (no tenant_id, no RLS), circular ref with `country`
payment_terms_catalog	9	Structured payment-terms vocabulary (net days + optional early-payment discount, e.g. "2/10 Net 30") — an enum→catalog additive-interim step.	global (no tenant_id, no RLS); Drizzle source discloses it is NOT yet kept in sync with the legacy CHECK-enum `payment_terms`/`credit_terms` columns it's meant to replace
plant	22	Thin global botanical reference (facts + names, no rich care content) — the AI-enrichment write target.	global (no tenant_id, no RLS), uuid PK (deliberate exception — taxonomic names aren't stable identifiers), review gate (`chk_plant_verified_review_consistency`: `is_verified` can't be true unless `review_status` is `not_required`/`approved`), AI-write target (`data_source`/`is_verified`/`created_by_actor_id` per the locked AI→Shared seam)
plant_climate_zone	14	Per-system hardiness range for a plant (a plant can be hardy across multiple zone systems at once).	global (no tenant_id, no RLS), same review-gate pattern as `plant`, denormalized `system` column (copied from the zone-code prefix, Drizzle source discloses this is NOT DB-enforced consistent — app/service-layer validated only)
plant_common_name	15	Locale-scoped common name(s) per plant (e.g. eggplant/aubergine), with a designated primary name per locale.	global (no tenant_id, no RLS), same review-gate pattern as `plant`, AI-write target
unit_of_measure	12	Global unit-of-measure vocabulary (the units that exist; per-item conversion logic lives in `inventory`).	global (no tenant_id, no RLS), self-FK (`base_unit_code`)

Discrepancies found (shared)

Documented write-access restriction is not DB-enforced. docs/database/schema_docs/shared.md and the Drizzle source both state this schema is "writable only via service_role and seed migration; app code never writes at runtime, except the locked AI→Shared seam" (limited to plant/plant_common_name/plant_climate_zone). The live GRANTS fact file shows the opposite in practice: the authenticated role (the tenant application role) holds full INSERT, UPDATE, DELETE, SELECT on all 12 shared tables — including pure ISO dictionaries like currency, country, language, and unit_of_measure that have no AI-write exception at all. Since none of these tables have RLS enabled (rowsecurity=false on all 12) and none carry the REVOKE-plus-trigger append-only pattern this codebase uses elsewhere (e.g. platform.reject_append_only_mutation()), there is currently no structural DB control stopping ordinary tenant application code from mutating global reference data — the restriction is a stated convention only. Worth flagging for the design review as a gap between documented intent and enforced reality.
Table/column counts, FK shapes, RLS status, and trigger absence otherwise all reconcile cleanly across the live-DB fact file, the Drizzle source, and docs/database/schema_docs/shared.md (12 tables / 136 columns confirmed by direct per-table column count in both the fact file and Drizzle; all 12 cross-schema/intra-schema FKs match the docs' "Cross-Phase Foreign Keys" table; zero triggers live, consistent with the docs' own disclosed "not DB-enforced, would need a trigger" notes on the country⇄locale circular ref and plant_climate_zone.system denormalization).

`multi_loc`

Owns: The physical/operational location entity for a tenant — a nursery's retail stores, yards, greenhouses, warehouses, farms, offices, or pop-ups. Models a global (non-US-shaped) address (flat lines + natural-key FKs into shared rather than a US-shaped JSONB blob), climate zone, measurement system, geo coordinates, operating hours, and the standard agent-actor-attribution/human-review provenance columns. Currently a single-table schema.

Layer: foundation Tables: 1 · Columns: 37 Depends on: identity, platform, shared Depended on by: identity.user_site_assignment.site_id, identity.tenant_user.default_site_id, identity.invitation_site_assignment.site_id — all real composite (site_id, tenant_id) FKs as of the 2026-07-10 reopen, explicitly named in site.ts's own comment as the rationale for its UNIQUE(id, tenant_id) constraint. Also named (but per cross-checked docs, deliberately still deferred/unwired): platform.tenant.primary_site_id, identity.user_permission_override.scope_id, identity.access_request.requested_scope_id.

Table	Cols	Purpose	Key characteristics
`site`	37	A tenant's physical/operational location (retail/yard/greenhouse/warehouse/farm/office/popup) with a global, country-code-driven address, climate zone, and unit system.	tenant-scoped, RLS: `tenant_id = current_setting('app.current_tenant_id')::uuid` (policy `site_tenant_isolation`, ALL commands), at-most-one-primary-site-per-tenant guard (partial unique index + `chk_site_is_primary_active`), region/country consistency guardrail (`chk_site_region_country_match` checks `left(region_code,2) = country_code`), human-in-the-loop review seam (`review_status`/`review_reason`/`reviewed_by_actor_id`/`reviewed_at`), agent-actor attribution (`created_by_actor_id`/`updated_by_actor_id`/`automation_source`/`decision_provenance`)

Discrepancies found (multi_loc)

No discrepancies found — live DB, Drizzle source, and docs agree. (Table/column count, column list and order, all 7 FKs, the single RLS policy, and the set_updated_at trigger all cross-check cleanly across the fact file, packages/db/src/schema/multi_loc/site.ts, and docs/database/schema_docs/multi_loc.md.)

`files`

Owns: The files schema is a generic, module-agnostic file-metadata registry: one row per stored object (file) tracking its location on Cloudflare R2 (the sole storage of record; AWS S3 is transient Textract staging only), lifecycle/scan/extraction status, a polymorphic many-to-many attachment join (attachment) linking files to business entities, fine-grained per-file access grants (file_access_grant) for cases visibility alone can't express, per-tenant cached storage-usage accounting (tenant_storage_usage), and a "vector spine" (document_index + document_chunk) that chunks extracted text and (eventually) embeddings for full-text/semantic search. It never stores file bytes itself — only metadata, status, and search artifacts pointing at externally-stored objects. Layer: foundation Tables: 6 · Columns: 89 Depends on: identity, platform Depended on by: see cross-schema FK inventory — no files.* Drizzle file names specific consuming schemas explicitly (by foundation-layer design, SCHEMA_CONVENTIONS.md §1 forbids files FK-ing outward into any business/consumer schema, so the seam only ever runs inbound). The existing docs (docs/database/schema_docs/files.md) additionally assert that admin, crm, pos, receiving, inventory, and ai each hold a forward-ref column pointing at files.file (mostly still unwired to a real FK), plus the consumer layer via the consumer.get_files_for_consumer() function — stated for context, not independently confirmed from this schema's own FK data.

Table	Cols	Purpose	Key characteristics
`attachment`	17	Polymorphic many-to-many join: which file(s) attach to which business entity, in what role (e.g. product photo, PO backup), with sort order.	tenant-scoped, polymorphic ref (`entity_type` closed-CHECK enum + `entity_id` bare uuid, no FK), RLS: tenant_id=current_tenant, review workflow (review_status/review_reason/decision_provenance autonomy pack, reviewer≠creator CHECK guard)
`document_chunk`	11	Chunked extracted text per file plus an optional embedding vector — the FTS/semantic-search unit ("vector spine," part 2).	tenant-scoped, RLS: tenant_id=current_tenant (plus a dedicated `agent_reader` SELECT-only policy), NOT append-only (embedding populated later via UPDATE; re-indexing soft-deletes the prior generation rather than hard-deleting), generated `tsvector` column GIN-indexed for FTS, `vector(1024)` embedding column left NULL until semantic search activates
`document_index`	10	Per-file indexing status/lifecycle for the search pipeline ("vector spine," part 1) — distinct from `file`'s own owner_module/owner_type.	tenant-scoped, one active row per file (unique `tenant_id,file_id` where not deleted), RLS: tenant_id=current_tenant (plus a dedicated `agent_reader` SELECT-only policy)
`file`	27	The core registry row — one per stored object: its R2/S3 location, lifecycle/visibility/scan/extraction status, and polymorphic owner backref.	tenant-scoped (nullable `tenant_id` — a NULL-tenant non-public row is service_role-only), polymorphic ref (`owner_module`/`owner_type`/`owner_ref` trio, no FK; also loose `consumer_id`, no FK — foundation layer can't FK into consumer), RLS: `visibility='public' OR tenant_id=current_tenant` (public-bypass special case)
`file_access_grant`	16	Fine-grained per-file access grant (user/customer/external/public-link) for cases `visibility` alone can't express, e.g. a named DSR delivery recipient.	tenant-scoped, RLS: tenant_id=current_tenant, discriminated-grantee shape (`grantee_type` selects among `grantee_user_id` [FK'd → identity.identity_user], loose `grantee_customer_id` [no FK], or `grantee_identifier`), cached counter (`access_count`, self-maintained, no external ledger)
`tenant_storage_usage`	8	Per-tenant cached storage usage (bytes/object count) — the tier limit itself lives in `platform.tenant_entitlement`.	tenant-scoped, one active row per tenant (unique `tenant_id` where not deleted), RLS: tenant_id=current_tenant, cached counter (`bytes_used`/`object_count` — disclosed estimate with expected drift, no atomic same-transaction ledger since the authoritative "upload succeeded" event is an external R2 confirmation)

Discrepancies found (files)

No discrepancies found — live DB, Drizzle source, and docs agree. Table count (6), column count (89, matching the per-table sum 17+11+10+27+16+8), all FKs (including the composite (file_id, tenant_id) → files.file(id, tenant_id) pattern on all 4 child tables and the loose/unenforced consumer_id/grantee_customer_id/owner_ref/entity_id columns), RLS policy shapes (including the agent_reader SELECT-only policies on document_chunk/document_index added in the 2026-07-17 Phase 6 pass), and grants (no consumer_authenticated grant appears anywhere in this schema, consistent with the docs' stated explicit REVOKE) all reconcile cleanly across the three sources.

`admin`

Owns: Vrida-tenant's own technical/operational configuration and presentational surface — generic key-value settings (with a config-key catalog), per-site hardware device registry, third-party integration and outbound-webhook configuration (with a provider catalog), tenant API-key issuance, compliance/license documents, tenant branding, and a tenant-scoped custom-field registry governing free-form attributes JSONB columns elsewhere in the codebase. It explicitly does not own tenant identity (that's platform.tenant_profile) or auth policy (identity). Layer: foundation Tables: 10 · Columns: 122 Depends on: identity, multi_loc, platform Depended on by: see cross-schema FK inventory (no live inbound FKs into admin today); Drizzle comments on integration_config/webhook_config name the not-yet-built integrations module as the intended future consumer (config-in-owner / runtime-in-consumer), not a real dependency yet

Table	Cols	Purpose	Key characteristics
`api_key`	14	Tenant-issued API keys for external/integration access (Enterprise tier); raw key shown once, stored only as a hash.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id')::uuid; token stored hashed (SHA-256), never raw
`compliance_document`	15	Tenant's own business licenses, insurance certs, and permits, with expiry tracking; optionally scoped to one of a tenant's legal entities via `entity_id`.	tenant-scoped, RLS: tenant_id=current_setting(...); forward-ref `document_ref`→`files.file` (still plain text, no FK — see discrepancies)
`custom_field_definition`	12	Tenant-defined custom-field registry (type/default/required) for 7 named `attributes` JSONB columns codebase-wide (crm.customer, inventory.item/item_variant, orders.order_header, receiving.goods_receipt, purchasing.vendor/vendor_item); not DB-enforced against those columns.	tenant-scoped, RLS: tenant_id=current_setting(...); no soft-delete (uses `is_active` instead)
`hardware_device`	13	Per-site physical device registry — register, printer, cash drawer, card reader, scanner.	tenant-scoped, RLS: tenant_id=current_setting(...)
`integration_config`	14	Per-tenant 3rd-party integration settings (QuickBooks/Stripe/Mailchimp/Twilio/Resend) — config only; sync/delivery runtime deferred to the not-yet-built Integrations module.	tenant-scoped, RLS: tenant_id=current_setting(...); `credentials_ref` is an unresolved vault reference (no vault-encryption service exists yet)
`integration_provider_catalog`	7	Global reference catalog of known integration providers — an additive-interim step toward eventually replacing the `integration_type` CHECK-enum; deliberately not kept in sync with it.	global (no tenant_id, no RLS); full CRUD still granted to `authenticated` despite being global reference data (disclosed, cross-cutting grant-model gap)
`setting_definition`	12	Config-key catalog for `admin.tenant_setting` — canonical registry of valid (category, key) pairs, declared value type, default, and tenant-editability/site-scopability flags.	global (no tenant_id, no RLS); not DB-enforced against `tenant_setting` (no FK/trigger — a CHECK cannot cross tables)
`tenant_branding`	12	One row per tenant — logo, brand colors, font, slogan, social handles for customer-facing theming.	tenant-scoped, RLS: tenant_id=current_setting(...); UNIQUE(tenant_id) WHERE deleted_at IS NULL enforces one row per tenant
`tenant_setting`	11	Generic (tenant, category, key) → JSONB config catch-all with an optional site-level override; the default/fallback branch of admin's config-surface decision tree.	tenant-scoped, RLS: tenant_id=current_setting(...); NULL-safe dedup via 2 partial-unique indexes (tenant-wide vs. site-level)
`webhook_config`	12	Tenant-configured outbound webhook endpoints; delivery runtime deferred to the not-yet-built Integrations module.	tenant-scoped, RLS: tenant_id=current_setting(...); `secret_ref` is an unresolved vault reference (no vault-encryption service exists yet)

Discrepancies found (admin)

Stale "Files module not built" claim. docs/database/schema_docs/admin.md states in 4 places (the FK-inventory table and prose around tenant_branding.logo_ref/compliance_document.document_ref, plus the OPEN_ITEMS rollup) that these two forward-refs point at files.file.id with "Files module not built." The Files module is now schema-locked and live (packages/db/migrations/20260711030000_files_module.sql, Drizzle source at packages/db/src/schema/files/). The practical effect is unchanged — the live-DB FOREIGN KEYS section confirms neither column has a real FK yet, so the forward-refs genuinely are still unwired — but the doc's stated reason is factually wrong: Files exists, admin is simply one of the modules awaiting a follow-up FK-wiring bundle, not blocked on a nonexistent module.
Internal header/body mismatch on compliance_document. The section header reads "(14 cols...)" but the column table beneath it lists 15 rows (including entity_id), which matches both the live-DB fact file (col_count=15) and the doc's own bottom-of-file reconciliation table (15). The header text is stale by one column.
Internal header/body mismatch on integration_config. The section header reads "(13 cols...)" but the column table beneath it lists 14 rows (including provider_id), which matches both the live-DB fact file (col_count=14) and the doc's own reconciliation table (14). Header stale by one column.
integration_provider_catalog.updated_at is documented as trigger-maintained but isn't. The doc states three times ("updated_at: trigger-maintained via platform.set_updated_at()") that this table's updated_at is trigger-maintained, matching the pattern used elsewhere in the module. The live-DB fact file's TRIGGERS section, however, shows a set_updated_at trigger on all 9 other admin tables but none on integration_provider_catalog. Confirmed by inspecting the originating migration (20260709030000_phase4_item17_enum_to_catalog.sql): it creates 4 new catalog tables across 4 schemas (pos.tender_type_catalog, shared.payment_terms_catalog, tax.jurisdiction_level_catalog, admin.integration_provider_catalog), each with an updated_at column, but installs a maintaining trigger on none of them. This is a real, live gap — updated_at on this table will not actually update on row modification, contrary to what the docs claim.

`approvals`

Owns: A cross-cutting, tenant-side business-process approval-workflow engine: configurable approval-chain definitions and routing rules, the in-flight/resolved request-and-step runtime that executes them, approver-notification delivery, one-click approve/reject tokens, and an append-only audit trail of every event in an approval's lifecycle. It is a pre-execution gate other modules route financially/operationally sensitive decisions (POs, discounts, refunds, agent actions, etc.) through via a polymorphic (source_module, source_type, source_ref) reference — it holds no FK into any consuming domain module.

Layer: foundation Tables: 8 · Columns: 97 Depends on: identity, multi_loc, platform Depended on by: see cross-schema FK inventory — by design any module may depend on approvals (the reverse-dependency rule is stated directly in _schema.ts's own header comment: "any module may depend on approvals; approvals depends on NO domain module"), but no specific consumer table is named in the Drizzle source itself.

Table	Cols	Purpose	Key characteristics
`approval_delivery`	12	Tracks outbound approver notifications (email/in-app/SMS) for a request or step, from send through delivery/open/failure.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), disclosed temporary overlap with the not-yet-built `notifications` module's own planned scope
`approval_event`	8	Append-only audit log of everything that happens to a request/step (created, routed, notified, approved, expired, agent recommendation/decision, etc.).	tenant-scoped, append-only (trigger `trg_approval_event_append_only` → `platform.reject_append_only_mutation()`), RLS: tenant_id=current_setting('app.current_tenant_id') — see discrepancy below re: the paired REVOKE
`approval_policy`	10	Tenant-level self-approval/segregation-of-duties configuration (`allow_self_approval`, `min_distinct_approvers`, `require_role_separation`) consumed by `approval_step`'s guard triggers.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id')
`approval_request`	20	The runtime instance of one approval-workflow invocation for a polymorphic source record — status, current step, initiator, resolution.	tenant-scoped, polymorphic ref (`source_module`/`source_type`/`source_ref`, no FK on `source_ref`), RLS: tenant_id=current_setting('app.current_tenant_id'), guardrail: self-approval block + "approved-by-nobody" presence CHECK
`approval_routing_rule`	11	Routes a workflow trigger (`workflow_type` + amount threshold + optional site) to the correct `approval_workflow`.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id')
`approval_step`	14	Per-request step instance — assigned/acting approver, decision, sequencing/parallel-resolution mode; the actual decision surface.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), guardrails: self-approval block trigger, distinct-approvers quorum trigger, C8 agent-approver block trigger (`trg_approval_step_blocks_agent_approver`)
`approval_token`	10	One-click hash-only approve/reject tokens for out-of-band (e.g. email-link) step resolution.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), guardrail: hash-only storage + atomic single-use redemption + sibling-token supersession trigger (security-critical)
`approval_workflow`	12	Configurable approval-chain definition per tenant/`workflow_type` — ordered steps, step mode, whether agent actors are blocked from approving.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), guardrail: `blocks_agent_approver` column write-locked at the grant layer (C8 financial-autonomy anchor)

Discrepancies found (approvals)

approval_event's documented "belt-and-suspenders" REVOKE does not hold live. The docs (and the migration's own comment at packages/db/migrations/20260709070000_approvals_module.sql:443-451) claim REVOKE UPDATE, DELETE ON approvals.approval_event FROM authenticated is in effect alongside the append-only trigger. The live-DB fact file's GRANTS section instead shows authenticated currently holds INSERT,SELECT,UPDATE,DELETE on approval_event — the full set, including UPDATE/DELETE. Tracing the migration confirms why: the targeted REVOKE executes at line 448, but a later, broader statement at line 459 — GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA approvals TO authenticated; — re-grants UPDATE/DELETE to every table in the schema, silently undoing the earlier REVOKE on approval_event (an ordering bug, not a docs typo). This is the same class of "narrower REVOKE undone by a broader GRANT" issue the docs describe finding and fixing for approval_workflow.blocks_agent_approver (Guard 4) — but there the fix correctly REVOKEs after the blanket grant (line 473, after line 459); the approval_event REVOKE at line 448 was never re-applied after the blanket grant and was missed. Practical impact is likely limited — the BEFORE UPDATE OR DELETE trigger (trg_approval_event_append_only → platform.reject_append_only_mutation()) still blocks the mutation for every role including superuser — but the grant-layer half of the documented "belt-and-suspenders" Guard 6 is not actually in effect, contrary to what both the docs and the migration's own inline comment claim.
Minor/cosmetic: the live FK constraint on approval_request.initiator_actor_id is still named approval_request_requested_by_actor_id_fkey, a leftover from before the documented rename (requested_by_actor_id → initiator_actor_id). Postgres doesn't auto-rename constraints on a column rename, so this doesn't affect behavior, but a reviewer grepping constraint names by current column name would miss it.

Aside from the above, table/column counts (8 tables / 97 columns), CHECK constraints, trigger inventory (7 functions / 11 trigger objects), FK composite-vs-bare shapes, and RLS policy shapes all reconcile cleanly across the live-DB fact file, the Drizzle source, and docs/database/schema_docs/approvals.md.

8.2 Business layer

`crm`

Owns: The tenant's system of record for customers (individual or business buyers) — core profile plus credit-worthiness, contacts, addresses (global flat-address shape), marketing/communication consent history, tax-exemption certificates, staff/agent notes and follow-up tasks, a duplicate-customer merge workflow (candidate review → executed-merge audit), and customer segmentation (both a defined, agent-computed segment vocabulary and free-form staff tags). Layer: business Tables: 13 · Columns: 193 Depends on: identity, multi_loc, platform, shared Depended on by: billing (reads customer.credit_limit_cents/credit_terms, per crm.customer's own Drizzle comment — billing never duplicates them) and pricing (customer_group_id, per crm.customer_group's own Drizzle comment, a forward-looking reference since pricing didn't exist yet at crm's lock); see cross-schema FK inventory for the complete picture (orders/pos/rewards/offers/returns/consumer plausibly reference crm.customer downstream but that isn't named in this schema's own source).

Table	Cols	Purpose	Key characteristics
`address`	15	Billing/shipping addresses for a customer, using a global flat-address shape (country/region FK, not US-only).	tenant-scoped, RLS: tenant_id=current_setting(...), guardrail: region/country consistency CHECK (mirrors multi_loc.site)
`contact`	15	Additional named contacts at a customer (e.g. AP contact, site manager), subordinate to customer.	tenant-scoped, RLS: tenant_id=current_setting(...), guardrail: at-most-one-primary-contact partial unique
`customer`	36	The core CRM entity — a tenant's individual or business buyer record, including credit terms, tax-exempt status, and full autonomy/review provenance.	tenant-scoped, RLS: tenant_id=current_setting(...), cached counter: `tax_exempt` (source: `customer_tax_certificate`), cached counter: `marketing_opt_in` (source: `customer_consent`), polymorphic ref: `consumer_id` (bare uuid, no FK — deferred forward-ref to a not-yet-built `consumer` schema), review/provenance seam (`is_verified`/`review_status`/`decision_provenance`)
`customer_consent`	12	Event log of a customer's opt-in/opt-out actions per channel; backs the `customer.marketing_opt_in` cache.	tenant-scoped, RLS: tenant_id=current_setting(...), no `updated_at`/`deleted_at` (event-log shape; NOT DB-enforced append-only — full CRUD still GRANTed to `authenticated`, no reject-mutation trigger present)
`customer_group`	10	Tenant-defined customer grouping catalog, shared with Pricing (`price_list_assignment.customer_group_id`).	tenant-scoped, RLS: tenant_id=current_setting(...), no autonomy columns (human/Vrida-curated catalog)
`customer_merge`	11	Post-execution audit record of an executed customer dedup merge (source merged into target).	tenant-scoped, RLS: tenant_id=current_setting(...), no `updated_at`/`deleted_at` (post-execution audit shape; NOT DB-enforced append-only)
`customer_merge_candidate`	15	A pending duplicate-customer proposal awaiting review — the gate before a merge executes.	tenant-scoped, RLS: tenant_id=current_setting(...), review/approval gate (`review_status` defaults `pending`; execution always needs-approval, never may-act-alone)
`customer_note`	10	Free-text staff/agent observation log against a customer, optionally attributed to a site.	tenant-scoped, RLS: tenant_id=current_setting(...), no `updated_at`/`deleted_at` (event-log shape; NOT DB-enforced append-only)
`customer_segment_definition`	10	Catalog of segment codes (Vrida built-in or tenant-custom) that `customer_segment_membership` rows are drawn from.	global/tenant mixed-scope (nullable `tenant_id`, mirrors `identity.role`), RLS: tenant_id IS NULL OR tenant_id=current_setting(...), no autonomy columns
`customer_segment_membership`	17	Customer × segment assignment — agent-computed (confidence-scored) or human-applied, with start/expiry.	tenant-scoped, RLS: tenant_id=current_setting(...), review/provenance seam, guardrail: `status` stored not derived (avoids non-IMMUTABLE `now()` in a partial index)
`customer_tag_assignment`	8	Free-form staff-typed labels on a customer, with no catalog or review (distinct from segment membership).	tenant-scoped, RLS: tenant_id=current_setting(...)
`customer_task`	15	A follow-up task against a customer, human- or agent-suggested; open/completed/dismissed lifecycle.	tenant-scoped, RLS: tenant_id=current_setting(...), guardrail: status lifecycle IS the review mechanism (no separate `review_status`)
`customer_tax_certificate`	19	A customer-provided tax-exemption certificate; source of truth for the `customer.tax_exempt` cache.	tenant-scoped, RLS: tenant_id=current_setting(...), review seam reused (`status`/`verified_by_actor_id`/`verified_at`, no parallel `review_status`), guardrail: fail-closed CHECK requiring a verifier when `status='active'`; two partial-unique indexes avoid the NULL-uniqueness trap

Discrepancies found (crm)

No discrepancies found — live DB, Drizzle source, and docs agree. Table/column counts reconcile exactly (13 tables / 193 columns, matching both the fact file's TABLES sum and the doc's own "Column-count reconciliation" table); the set_updated_at trigger list, RLS policy shapes (including customer_segment_definition's mixed-scope disjunction), FK targets, and the DR-48/DR-49 CHECK/NOT-NULL fixes are all consistently represented across all three sources.

`inventory`

Owns: The vertical-neutral product catalog — items (one row per SKU-family, discriminated by item_type), sellable/stockable variants, categories, tags, barcodes, images, kit bills-of-materials, and the variant option/value matrix — together with the physical stock layer built on top of it: inventory locations within a site, current stock-on-hand snapshots, the append-only stock-movement ledger that feeds them, lot/batch tracking with per-lot-per-location splits and condition grading, stock reservations against open orders/holds/transfers, a propose/execute stock-adjustment review gateway, cycle/full/spot stock counts, and a duplicate-item merge workflow. It is the first real v2 consumer of shared.plant (item.plant_id) — the point where the vertical-neutral core meets the nursery vertical. Layer: business Tables: 26 · Columns: 362 · Views: 1 Depends on: identity, multi_loc, platform, shared Depended on by: receiving (goods_receipt_line composite FKs into item_variant and stock_movement, per the UNIQUE(id,tenant_id) prerequisites added on both tables' own Drizzle definitions) — otherwise see cross-schema FK inventory.

Table	Cols	Purpose	Key characteristics
`barcode`	10	UPC/EAN/Code128/own barcodes for a variant, one designated primary.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id')
`brand`	7	Tenant-scoped item-brand catalog added by the Gap-Fill Batch.	tenant-scoped, RLS policy narrowed to `authenticated`
`category`	10	Self-referencing, tenant-defined hierarchical product category tree.	tenant-scoped, RLS: tenant_id=current_setting(...)
`inventory_location`	14	Self-referencing physical sub-location within a `multi_loc.site` (zone/bin/bench/row/shelf) that stock/counts/movements attach to.	tenant-scoped, RLS: tenant_id=current_setting(...)
`item`	22	Master catalog entity, one row per SKU-family, now vertical-neutral after the Nursery extraction.	tenant-scoped, RLS: tenant_id=current_setting(...), full autonomy pack (automation_source/review_status/decision_provenance + approved-requires-reviewer CHECK), generated `search_vector` (GIN, tsvector)
`item_category`	6	M:N join of item ↔ category.	tenant-scoped, RLS: tenant_id=current_setting(...), hard-delete join (no updated_at/deleted_at)
`item_image`	20	Image metadata for an item OR a variant (mutually exclusive owner via CHECK); AI photo-matching is the one review-worthy judgment call in the catalog group.	tenant-scoped, RLS: tenant_id=current_setting(...), full autonomy pack, polymorphic ref (`file_id` — no FK, `files` module deferred)
`item_merge`	11	Post-execution audit record of an item merge (source→target); a merge only lands here after `item_merge_candidate` review.	tenant-scoped, RLS: tenant_id=current_setting(...), no updated_at/deleted_at (immutable by convention — no reject-mutation trigger present)
`item_merge_candidate`	15	Propose/execute duplicate-item detection queue with review workflow.	tenant-scoped, RLS: tenant_id=current_setting(...), review seam (review_status defaults 'pending'), dedup guard: canonical pair-order CHECK + partial-unique on pending pair
`item_tag`	6	M:N join of item ↔ tag.	tenant-scoped, RLS: tenant_id=current_setting(...), hard-delete join (no updated_at/deleted_at)
`item_variant`	35	The actual sellable/stockable SKU — price, cost, UOM conversions, kit flag, guarantee terms.	tenant-scoped, RLS: tenant_id=current_setting(...), full autonomy pack, generated `search_vector` (GIN), cached counter (`avg_cost_cents`, weighted-average maintained from `stock_movement_line`), `UNIQUE(id,tenant_id)` (cross-schema composite-FK target for receiving)
`kit_component`	10	Bill-of-materials for a kit variant (`item_variant.is_kit=true`) — component variants + quantities.	tenant-scoped, RLS: tenant_id=current_setting(...), human-only (light attribution, no autonomy columns)
`lot`	16	A received lot/batch of inventory — provenance, expiry, supplier lot numbers (master data, no `site_id`).	tenant-scoped, RLS: tenant_id=current_setting(...), polymorphic ref (`source_type`/`source_id` — no FK), `UNIQUE(id,tenant_id)` (receiving composite-FK prerequisite)
`option_type`	9	Variant option axis definition scoped to one item (e.g. "Pot Size", "Color").	tenant-scoped, RLS: tenant_id=current_setting(...)
`stock`	22	Current stock-on-hand snapshot per (tenant, site, variant, location) — the resolved current-state row the movement ledger feeds.	tenant-scoped, RLS: tenant_id=current_setting(...), full autonomy pack, cached counter (`available_qty` = on_hand_qty − reserved_qty, generated), reconciliation watermark (`last_movement_at`/`last_movement_id` → `stock_movement`)
`stock_adjustment_batch`	10	Header grouping multiple related `stock_adjustment_request` rows for joint review/approval.	tenant-scoped, RLS: tenant_id=current_setting(...), header/line: `stock_adjustment_batch` ↔ `stock_adjustment_request`, `UNIQUE(id,tenant_id)`
`stock_adjustment_reason`	10	Tenant-defined lookup of adjustment reason codes (shrinkage, damage, cycle-count correction).	tenant-scoped, RLS: tenant_id=current_setting(...)
`stock_adjustment_request`	19	Propose/execute review gateway for stock adjustments — execution is always needs-approval, never may-act-alone.	tenant-scoped, RLS: tenant_id=current_setting(...), review seam (review_status defaults 'pending'), cached snapshot (`estimated_impact_cents` = quantity_delta × avg_cost at propose time), composite FK to `stock_adjustment_batch`
`stock_count`	16	A stock-count session (cycle/full/spot).	tenant-scoped, RLS: tenant_id=current_setting(...), review seam reused as the table's own `status` state machine (reconciled-requires-actor CHECK)
`stock_count_line`	14	One line in a stock count — expected vs. actual quantity for a variant/location/lot.	tenant-scoped, RLS: tenant_id=current_setting(...), header/line: `stock_count` ↔ `stock_count_line`, cached counter (`variance` = counted_qty − system_qty, generated), bespoke conditional-immutability trigger (`trg_stock_count_line_lock_after_reconciled`, not the shared append-only trigger)
`stock_lot`	10	Quantity of a specific lot at a specific site + location slot; must reconcile in aggregate to `stock.on_hand_qty`.	tenant-scoped, RLS: tenant_id=current_setting(...)
`stock_movement`	20	Append-only movement-event header with complete-vs-legacy classification, exact expected child count, and deterministic request hash.	tenant-scoped; protected posting only for new complete rows; historical rows remain `legacy_unverified`; `UNIQUE(id,tenant_id)`
`stock_movement_line`	15	Line-level detail of a movement — variant/location/lot/quantity/cost, carrying cost-provenance for average-cost recalculation.	tenant-scoped, append-only (trigger `trg_stock_movement_line_append_only` → `reject_append_only_mutation`), RLS: tenant_id=current_setting(...), header/line: `stock_movement` ↔ `stock_movement_line`, `UNIQUE(id,tenant_id)`
`stock_reservation`	17	Reserves stock against an open source with database-enforced stock coupling and retry identity.	tenant-scoped; protected creation/transition; `idempotency_key` + `request_hash`; Transfer creation is reachable only from dormant locked-row Transfer wrappers
`tag`	9	Tenant-defined free-form item tag catalog.	tenant-scoped, RLS: tenant_id=current_setting(...)
`transfer`	26	Cross-site Transfer header and lifecycle evidence.	tenant-scoped; one atomic shipment; consequential wrappers dormant to runtime roles
`transfer_line`	21	Transfer quantity with optional pinned lot, exact reservation, exact outbound movement child, and cumulative receipt counters.	tenant-scoped composite FKs; one lot per line; immutable post-draft structure and posting pointers
`transfer_reconciliation_event`	16	Append-only partial-receipt event with exact cumulative outcomes.	UUIDv7; tenant-unique caller key; deterministic retry; clean/restock-only destination credit
`variant_option`	9	The value of one option type for one variant (e.g. variant X → "Pot Size" → "3 gal").	tenant-scoped, RLS: tenant_id=current_setting(...)

Inventory Core recertification (2026-07-15 task run)

Inventory Core's certified baseline was 26 base tables / 362 base-table columns / 1 view. Stock Transfer adds exactly 3/63, producing the current live and Drizzle-reconciled 29 base tables / 425 columns / 1 view, with 90 CHECKs, 138 FKs, 164 indexes, 46 policies, 41 non-internal triggers, and RLS on all 29 tables. Ordinary roles cannot mutate protected operational rows or execute Transfer commands.

`pricing`

Owns: The per-variant pricing-decision engine that sits between inventory.item_variant's catalog base price and every selling channel (POS/Orders): a rule table (price_rule) expressing fixed/percent/amount-off/cost-plus pricing scoped by price tier, customer, customer group, site, and quantity break, with a supersede-don't-edit change history (price_change_log); a small tenant-curated price-tier catalog (price_level, e.g. retail/wholesale/member); and the assignment of a customer or customer group to one of those tiers over a date window (price_list_assignment). Precedence resolution itself (which of several matching rules wins) is deliberately NOT a DB constraint — that's PricingService logic (not yet built). Layer: business Tables: 4 · Columns: 78 Depends on: crm, identity, inventory, multi_loc, platform, shared Depended on by: see cross-schema FK inventory (pricing's own fact file only shows outbound FKs; the module's docs describe a still-unsatisfied "Hard Contract" requiring pos.sale_line/orders.order_line to snapshot resolving_price_rule_id, but no inbound FK evidence for that is available from this schema's own data)

Table	Cols	Purpose	Key characteristics
`price_change_log`	12	Audit trail of `price_rule` lifecycle events (created/updated/superseded/deleted/expired) and `item_variant.base_price_cents` changes.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), append-only by convention only — no `updated_at`/`deleted_at` and no trigger guards it (unlike other ledgers in this codebase that pair a REVOKE with `reject_append_only_mutation`); live GRANTS show `authenticated` still holds DELETE/UPDATE on this table
`price_level`	11	Human-curated, rarely-changing price-tier catalog (retail/wholesale/member) — the tenant's pricing-tier vocabulary.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), global reference data (no autonomy/review columns at all — never an AI-mutation target), guardrail: partial-unique caps `is_default=true` at one row per tenant (mirrors `multi_loc.site.is_primary`)
`price_list_assignment`	20	Assigns a customer OR a customer group to a `price_level` tier for a date window.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), full review seam (automation_source/review_status/decision_provenance + approved-requires-reviewer CHECK), guardrail: customer_id XOR customer_group_id, at-most-one open-ended active assignment per customer/group (partial-unique)
`price_rule`	35	The workhorse — per-variant pricing rule (fixed_price/percent_off/amount_off/cost_plus_percent), optionally scoped to a price level, customer, customer group and/or site, with quantity break and date window.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), full review/autonomy seam + self-approval block (reviewer ≠ creator CHECK), supersede-don't-edit self-referential history via `superseded_by_id` guarded by a dedicated trigger (`trg_price_rule_validate_supersession`, blocks cross-tenant supersession), margin-cap guardrail on `cost_plus_percent` (bounded −100..1000)

Discrepancies found (pricing)

The docs' own "Column-count reconciliation" table (docs/database/schema_docs/pricing.md, under "pricing — Design Patterns Summary") states 75 total columns (price_rule=33, price_list_assignment=19) and labels this "Verified live." That figure is stale — it's the pre-2026-07-07-reopen count. The live DB (and the same doc's own top-of-file summary and later "Fixed 2026-07-07" section) show 78 columns (price_rule=35, price_list_assignment=20), matching the fact file exactly. The doc never updated its own reconciliation table after folding in the DR-17/DR-18 columns (rule_kind, name, is_active).
The doc's verification narrative flags an open cross-cutting gap as of 2026-07-07: "the authenticated Postgres role has no schema-level GRANT on pricing... RLS policies are structurally correct but currently unreachable by any real authenticated connection." The live fact file's GRANTS section contradicts this — authenticated currently holds DELETE,INSERT,SELECT,UPDATE on all 4 pricing tables. This is consistent with CLAUDE.md's later Remediation Phase 1 (2026-07-08, GRANT-closing all 15 schemas for authenticated), but the pricing doc itself still reads as if the gap were open/unresolved in this file.
Minor: the doc's narrative text describes price_rule_pending_scope_dedup_unique as an "8-column key," but both the doc's own index listing and the Drizzle source (rule.ts) show 9 key columns (tenant_id, item_variant_id, scope_type, price_type, plus COALESCE'd site_id/price_level_id/customer_id/customer_group_id/min_qty).

`pos`

Owns: The in-store, offline-first point-of-sale transaction spine — physical/virtual registers and their open→closed cash-drawer shift sessions, paid-in/paid-out/count cash events, sale headers and line items (snapshotting Pricing's resolved price verbatim per Hard Contract 1), tenders applied to a sale, refunds and their lines (including a no-receipt/anonymous-walk-in path), the offline-sync collision queue for irreconcilable client_uuid conflicts arising from two different devices, and a global tender-type reference catalog. Layer: business Tables: 10 · Columns: 160 Depends on: crm, identity, inventory, multi_loc, platform, pricing, shared Depended on by: see cross-schema FK inventory — but Drizzle source comments explicitly name orders.order_line.sale_line_id, rewards.loyalty_point_ledger.sale_id, and offers.offer_redemption.sale_id as composite-FK consumers of pos.sale/pos.sale_line

Table	Cols	Purpose	Key characteristics
`pos_sync_conflict`	10	The offline-sync collision queue — created when an offline sync produces something that can't be auto-resolved (stock oversell, duplicate sale, price mismatch, payment dup).	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), polymorphic ref (`sale_id` nullable, no autonomy/automation_source column — conflict is always system-detected)
`register`	8	A physical/virtual till at a site.	tenant-scoped, RLS: tenant_id=current_setting(...), no autonomy columns (mirrors pricing.price_level precedent)
`register_cash_entry`	15	Append-only cash-drawer event log (paid-in/paid-out/count).	tenant-scoped, append-only (trigger `trg_register_cash_entry_append_only` → `reject_append_only_mutation`), RLS: tenant_id=current_setting(...), fiscal-period flag guard (`trg_register_cash_entry_flag_closed_period`, BEFORE INSERT only)
`register_session`	18	One open→closed cash-drawer shift per register.	tenant-scoped, RLS: tenant_id=current_setting(...), no deleted_at (permanent record, terminal state is closed_at), partial-unique "at most one open session per register" guard
`sale`	27	The transaction header; offline-first — `client_uuid` (device-generated) is the row's true identity, not the server `id`.	tenant-scoped, RLS: tenant_id=current_setting(...), header/line: `sale` ↔ `sale_line`, immutable-gross total (no in-place mutation post-completion), session-immutability guard (`trg_pos_sale_requires_open_session`), fiscal-period flag guard
`sale_line`	14	Line items — snapshots the 6 fields Pricing's Hard Contract 1 requires (resolved/charged amount, currency, tax treatment, resolving price rule, quantity).	tenant-scoped, append-only (trigger `trg_sale_line_append_only` → `reject_append_only_mutation`), RLS: tenant_id=current_setting(...), header/line: `sale` ↔ `sale_line`, no independent autonomy columns
`sale_payment`	27	One row per tender applied to a sale; same offline-sync shape as `sale`.	tenant-scoped, RLS: tenant_id=current_setting(...), fail-closed tender gate (`chk_sale_payment_no_unbacked_tender_type` blocks gift_card/store_credit/reward until their subsystems exist), polymorphic-ish forward-refs (`stripe_payment_intent_id`, `gift_card_id`, `store_credit_id` — no FK)
`sale_refund`	24	Refund header — always a new row, never mutates `sale`/`sale_payment`; `sale_id` nullable to support anonymous walk-in returns.	tenant-scoped, RLS: tenant_id=current_setting(...), fiscal-period flag guard, identification guard (`chk_sale_refund_identification`: linked sale OR documented reason required)
`sale_refund_line`	10	Which `sale_line`(s) a refund applies to, or (no-receipt path) which `item_variant` it identifies directly.	tenant-scoped, append-only (trigger `trg_sale_refund_line_append_only` → `reject_append_only_mutation`, added 2026-07-10), RLS: tenant_id=current_setting(...), header/line: `sale_refund` ↔ `sale_refund_line`, composite FK to `sale_line`
`tender_type_catalog`	7	Global tender-type reference catalog (card/cash/check/charge_account/gift_card/reward/store_credit), an additive-interim scaffold alongside `sale_payment.payment_method`'s CHECK-enum.	global (no tenant_id, no RLS — rowsecurity=false, zero policies live)

Discrepancies found (pos)

tender_type_catalog's claimed updated_at trigger does not exist in the live DB. The docs (docs/database/schema_docs/pos.md) state explicitly, twice — once in the table's own subsection ("updated_at: trigger-maintained via platform.set_updated_at()") and again in the "Column-count reconciliation" section ("7 triggers total (set_updated_at × 6 — the pre-existing 5 plus tender_type_catalog's own...)") — that a set_updated_at trigger fires on pos.tender_type_catalog. The live-DB fact file's TRIGGERS section lists no trigger at all for pos.tender_type_catalog (only 5 set_updated_at triggers exist, on register, register_session, sale, sale_payment, sale_refund) — the live trigger count is 12, not the 11 implied by the docs' own arithmetic (7 + 3 flag triggers + 1 append-only fix). Either the migration that was supposed to add this trigger (20260709030000_phase4_item17_enum_to_catalog.sql) never actually created it, or a later change dropped it — either way updated_at on this table is currently NOT trigger-maintained despite the column existing and the docs asserting otherwise.

`orders`

Owns: The commercial "sell path" order-management layer that sits between CRM/pricing/inventory and POS — the root order/quote header (draft → quote → order → fulfilled/closed/cancelled), its priced line items (carrying Pricing's Hard Contract 1 resolved-price snapshot), payment installment schedules (deposit/milestone/balance), fulfillment batches (pick/stage/ship/handoff) and their per-line split tracking, and reusable order templates for recurring orders. It completes the crm → pricing → inventory → orders → pos chain by linking (not converting) into pos.sale/pos.sale_line at fulfillment. Layer: business Tables: 7 · Columns: 175 Depends on: crm, identity, inventory, multi_loc, platform, pos, pricing, purchasing, shared Depended on by: see cross-schema FK inventory

Table	Cols	Purpose	Key characteristics
`order_fulfillment`	26	Fulfillment batch header — one row per pick/pack/ship/handoff event; an order can span multiple batches (partial ship + later pickup).	tenant-scoped, header/line: `order_fulfillment` ↔ `order_fulfillment_line`, RLS: tenant_id=current_setting('app.current_tenant_id')
`order_fulfillment_line`	13	Per-order-line split/tracking row within a fulfillment batch — quantity requested vs. picked vs. fulfilled.	tenant-scoped, header/line: `order_fulfillment` ↔ `order_fulfillment_line`, RLS: tenant_id=current_setting('app.current_tenant_id') — deliberately zero autonomy columns (pure denormalized tracking, per Drizzle comment)
`order_header`	45	Root commercial-order record — quote, order, special order, or preorder; anchors the sell path.	tenant-scoped, header/line: `order_header` ↔ `order_line`/`order_payment`/`order_fulfillment`, cached counter (`subtotal_cents`/`estimated_total_cents`/`balance_due_cents` — service-maintained from `order_line`/`order_payment` sums per Drizzle comment), RLS: tenant_id=current_setting('app.current_tenant_id')
`order_line`	39	One row per item on an order; carries Pricing's Hard Contract 1 price-resolution snapshot verbatim-matching `pos.sale_line`.	tenant-scoped, header/line: `order_header` ↔ `order_line`, cached counter (`line_subtotal_cents`/`line_total_cents` — service-maintained), RLS: tenant_id=current_setting('app.current_tenant_id')
`order_payment`	27	Payment schedule/installment record — one row per deposit, milestone, or balance payment on an order.	tenant-scoped, header/line: `order_header` ↔ `order_payment`, RLS: tenant_id=current_setting('app.current_tenant_id'), money-touching review/approval seam (per Drizzle comment, Part C's C8)
`order_template`	16	Named recurring order template, applied to pre-populate a new `order_header` with lines; prices resolved fresh at apply time, never cached.	tenant-scoped, header/line: `order_template` ↔ `order_template_line`, RLS: tenant_id=current_setting('app.current_tenant_id')
`order_template_line`	9	Default line items (variant + quantity) for an order template, adjustable at apply time.	tenant-scoped, header/line: `order_template` ↔ `order_template_line`, RLS: tenant_id=current_setting('app.current_tenant_id') — deliberately zero autonomy columns

Discrepancies found (orders)

FK constraint count mismatch. docs/database/schema_docs/orders.md states "45 FK constraints total (+1, order_line.sale_line_id → pos.sale_line, composite)" (line 401). Counting the live-DB fact file's FOREIGN KEYS section row-by-row per table gives 7 (order_fulfillment) + 4 (order_fulfillment_line) + 12 (order_header) + 10 (order_line) + 6 (order_payment) + 4 (order_template) + 3 (order_template_line) = 46, not 45. The doc's own arithmetic appears to be off by one somewhere in its running total, not the fix #6 delta itself (the composite order_line_sale_line_id_tenant_fkey is confirmed present and correctly composite in both the fact file and Drizzle source).
All other cross-checked facts agree: 7 tables / 175 columns (fact file, Drizzle, and docs all match, including the per-table breakdown); RLS enabled with a permissive <table>_tenant_isolation policy on all 7 tables restricted to authenticated (fact file and Drizzle pgPolicy blocks agree); only trigger present on any table is set_updated_at (no append-only enforcement in this schema, consistent with docs); order_header.entity_id → platform.legal_entity and order_line.sale_line_id → pos.sale_line (composite) both confirmed live and in Drizzle, matching the docs' Remediation Phase 4 / Header-Line Remediation narrative.

`purchasing`

Owns: The BUY-path paperwork and commitment layer of the supply chain — vendor master data (vendor, contacts, addresses, supplier item/cost catalog), purchase orders and PO templates, vendor invoices plus the 3-way match (PO line ↔ goods-receipt line ↔ invoice line), and vendor credits/returns. It stops short of the physical receiving event (moved to receiving on 2026-07-10) and stops short of actual payment execution (owned by billing) — vendor_invoice.status deliberately excludes 'paid'. Layer: business Tables: 15 · Columns: 353 Depends on: crm, identity, inventory, multi_loc, orders, platform, receiving, shared Depended on by: see cross-schema FK inventory — Drizzle comments explicitly confirm receiving (goods_receipt/goods_receipt_line composite FKs pointing back into vendor/vendor_address/purchase_order/purchase_order_line) and orders (order_header.draft_po_id → purchasing.purchase_order) as real, named consumers.

Table	Cols	Purpose	Key characteristics
`purchase_order`	46	PO commitment header — the reorder-agent draft target and buy-side counterpart to `orders.order_header`.	tenant-scoped, header/line: purchase_order ↔ purchase_order_line, cached counter (`amount_paid_cents` — Billing write-back, no FK), C8 financial-autonomy guardrails (`chk_purchase_order_sent_requires_approval`, `chk_purchase_order_no_self_approval`), RLS: tenant_id=current_setting('app.current_tenant_id')
`purchase_order_line`	30	PO line items with a frozen dual-UOM cost snapshot (purchase vs. stock unit cost) — the 3-way-match input.	tenant-scoped, header/line: purchase_order ↔ purchase_order_line, cached counter (received/invoiced/cancelled_qty rollup, CHECK-capped at ordered_qty), RLS: tenant_id=current_setting(...)
`purchase_order_template`	15	Recurring-PO template an agent can propose.	tenant-scoped, header/line: purchase_order_template ↔ purchase_order_template_line, RLS: tenant_id=current_setting(...)
`purchase_order_template_line`	10	Template line items (item + qty), zero autonomy columns.	tenant-scoped, header/line: purchase_order_template ↔ purchase_order_template_line, RLS: tenant_id=current_setting(...)
`vendor`	38	Vendor/supplier master — buy-side counterpart to `crm.customer`, deliberately kept a separate entity, with an optional link to a linked customer record.	tenant-scoped, RLS: tenant_id=current_setting(...), cost-trend/reliability review seam (FULL pack)
`vendor_address`	17	Vendor billing/shipping/remittance addresses.	tenant-scoped, RLS: tenant_id=current_setting(...), region/country consistency CHECK
`vendor_contact`	16	Named contacts per vendor.	tenant-scoped, RLS: tenant_id=current_setting(...)
`vendor_credit`	24	Credit memo issued by a vendor, applied against invoices/returns.	tenant-scoped, header/line: vendor_credit ↔ vendor_credit_line, cached counter (`applied_amount_cents`/`remaining_amount_cents`), RLS: tenant_id=current_setting(...)
`vendor_credit_line`	8	Write-once decomposition of a vendor_credit's total against a specific invoice or return line.	tenant-scoped, header/line: vendor_credit ↔ vendor_credit_line, header-is-truth reconciliation trigger (blocks INSERT/UPDATE of amount_cents pushing SUM(lines) over credit_amount_cents; DELETE not guarded by any trigger), RLS: tenant_id=current_setting(...)
`vendor_invoice`	40	Vendor invoice document header — the AI-OCR draft surface; excludes `'paid'` status (Billing owns payment).	tenant-scoped, header/line: vendor_invoice ↔ vendor_invoice_line, cached counter (`amount_matched_cents`/`amount_credited_cents`), Billing payment write-back columns (no FK), RLS: tenant_id=current_setting(...)
`vendor_invoice_line`	20	Invoice line items — item/freight/tax/misc, matched against PO lines.	tenant-scoped, header/line: vendor_invoice ↔ vendor_invoice_line, RLS: tenant_id=current_setting(...)
`vendor_invoice_match`	18	N:M 3-way-match junction linking invoice lines to goods-receipt lines; `match_status` is its own variance/resolution state machine.	tenant-scoped, cross-module composite FK → receiving.goods_receipt_line (3-way-match seam), RLS: tenant_id=current_setting(...)
`vendor_item`	29	Supplier-item catalog — vendor SKU/cost/UOM per item; the cost-change-alert surface for a cost-trend agent.	tenant-scoped, cached counter (`last_cost_cents`/`last_cost_updated_at` track drift vs. `cost_cents`), RLS: tenant_id=current_setting(...)
`vendor_return`	23	RMA/return authorization + outbound-shipment header to a vendor.	tenant-scoped, header/line: vendor_return ↔ vendor_return_line, RLS: tenant_id=current_setting(...)
`vendor_return_line`	19	Return line items; each writes an outbound `inventory.stock_movement` on ship.	tenant-scoped, header/line: vendor_return ↔ vendor_return_line, cross-module composite FK → receiving.goods_receipt_line, bare deferred→real FK → inventory.stock_movement, RLS: tenant_id=current_setting(...)

Discrepancies found (purchasing)

No discrepancies found — live DB, Drizzle source, and docs agree. Table/column counts (15 tables / 353 cols), per-table column counts, FK targets (including which are composite (col, tenant_id) vs. bare single-column), RLS policy shapes, and trigger coverage (14 of 15 tables via set_updated_at, vendor_credit_line the sole exception with its own reconciliation trigger) all cross-check cleanly against docs/database/schema_docs/purchasing.md.

`receiving`

Owns: The physical act of receiving goods against a purchase order — the receipt document (carrier/tracking/freight, FULL-tier review and void workflow) and its line-level accept/reject/damage/quarantine disposition, including receiving-time over-receipt tolerance enforcement, the lot a receipt creates, and the stock-movement linkage receiving originates into Inventory (which alone executes the actual stock mutation). Layer: business Tables: 2 · Columns: 62 Depends on: identity, inventory, multi_loc, platform, purchasing, shared Depended on by: purchasing (vendor_invoice_match.goods_receipt_line_id, vendor_return_line.goods_receipt_line_id — reciprocal composite FKs back into goods_receipt_line, explicitly named in the Drizzle source's own comments); otherwise see cross-schema FK inventory.

Table	Cols	Purpose	Key characteristics
`goods_receipt`	33	Receipt header — one row per physical delivery event against a PO, carrying carrier/tracking/freight and a FULL-tier review + void workflow.	tenant-scoped, header/line: `goods_receipt` ↔ `goods_receipt_line`, RLS: tenant_id=current_setting('app.current_tenant_id'), void-attribution guard (`chk_goods_receipt_voided_requires_actor_at`)
`goods_receipt_line`	29	Per-line receiving disposition (accept/reject/damage/quarantine) against a `purchase_order_line`, driving the inventory stock-movement post and the PO-line quantity rollup.	tenant-scoped, header/line: `goods_receipt` ↔ `goods_receipt_line`, RLS: tenant_id=current_setting('app.current_tenant_id'), over-receipt tolerance guard with capped PO-line write-back (trigger `trg_goods_receipt_line_check_over_receipt_tolerance`), self-referencing reversal link (`reversal_of_goods_receipt_line_id`)

Discrepancies found (receiving)

No discrepancies found — live DB, Drizzle source, and docs agree (table/column counts, RLS policy shape, composite-vs-bare FK split, and trigger set all cross-check cleanly across all three sources).

`tax`

Owns: tax's tables durably record the tax owed on a sale line, order line, or refund line as computed by an external rate provider (Stripe Tax) — this module never computes a rate itself — and decompose that result into a per-jurisdiction (country/state/county/city/district/special) breakdown for remittance reporting. It also tracks refund-driven tax reversals (sign-aware, so SUM(original+reversal) nets to zero) and same-line supersessions/corrections, plus a small global catalog of jurisdiction levels feeding an in-progress enum→catalog migration. Layer: business Tables: 3 · Columns: 47 Depends on: crm, identity, multi_loc, platform, shared Depended on by: billing (ar_charge.tax_calculation_id, ar_charge_line.tax_calculation_id — confirmed via Drizzle comments in tax/calculation.ts and CLAUDE.md history); otherwise see cross-schema FK inventory

Table	Cols	Purpose	Key characteristics
`jurisdiction_level_catalog`	6	Global catalog of jurisdiction levels (country/state/county/city/district/special) — an additive-interim step toward replacing the `jurisdiction_level` CHECK-enum on `tax_calculation_jurisdiction` with a real FK-backed catalog; deliberately not yet kept in sync with that enum.	global (no `tenant_id`, RLS disabled — `rowsecurity=f`), catalog/reference table
`tax_calculation`	31	Header — one row per tax-calculation event sourced from a POS sale line, order line, refund line, or manual adjustment; records what an external provider (Stripe Tax) computed, including exemptions, same-line corrections, and refund reversals.	tenant-scoped, RLS: `tenant_id = current_setting('app.current_tenant_id')`, header/line: `tax_calculation` ↔ `tax_calculation_jurisdiction`, polymorphic ref: `source_module`/`source_type`/`source_ref` (no FK on `source_ref`), self-FK supersede-don't-edit (`supersedes_calculation_id`, trigger-scoped to same `source_ref`) + separate self-FK reversal link (`reversed_calculation_id`), reviewer≠creator self-approval block, `automation_source` defaults `'system'` (only non-`'human'` default in the codebase), sign-aware CHECKs for original-vs-reversal amounts
`tax_calculation_jurisdiction`	10	Line — one row per (`tax_calculation`, jurisdiction), decomposing the header's total tax into a per-jurisdiction breakdown (the granularity `pos.sale_line`'s own collapse had lost).	tenant-scoped, RLS: `tenant_id = current_setting('app.current_tenant_id')`, append-only (`trg_tax_calculation_jurisdiction_append_only` → `reject_append_only_mutation`, no UPDATE/DELETE grant to `authenticated`), header/line: `tax_calculation` ↔ `tax_calculation_jurisdiction`, sign-validated via trigger (`trg_tax_calculation_jurisdiction_validate_sign`, mirrors parent's `calculation_type` sign convention — not a CHECK since it reads the parent row)

Discrepancies found (tax)

docs/database/schema_docs/tax.md states tax_calculation has "14 CHECK + 12 FK constraints" (and separately frames it as "11→12 FK, Remediation Phase 4: +entity_id"). The live-DB fact file's FOREIGN KEYS section lists only 10 FK constraints on tax_calculation (applied_exemption_certificate_id, created_by_actor_id, currency_code, customer_id, entity_id, reversed_calculation_id, reviewed_by_actor_id, site_id, supersedes_calculation_id, tenant_id), and the Drizzle source (packages/db/src/schema/tax/calculation.ts) confirms only 10 FK-eligible columns exist (8 via inline .references() + 2 self-FKs — supersedes_calculation_id/reversed_calculation_id — added outside the column builder). The 14-CHECK count is independently confirmed correct by counting check(...) calls in the Drizzle source, so only the FK figure (12) appears to be stale/wrong.
The docs' "Column-count reconciliation" section asserts "3 triggers total" for the schema, then immediately lists 5 (tax_calculation: set_updated_at, trg_tax_calculation_validate_supersession, trg_tax_calculation_validate_reversal; tax_calculation_jurisdiction: trg_tax_calculation_jurisdiction_append_only, trg_tax_calculation_jurisdiction_validate_sign) — matching the live-DB TRIGGERS section's 5 total. The enumerated triggers are correct; only the "3 triggers total" summary phrase is internally inconsistent with the list right below it and with live DB.
All other facts checked — table/column counts (3 tables / 47 cols, 6+31+10), RLS policy names and scope (authenticated-only tenant isolation on the two tenant-scoped tables, RLS disabled on the global catalog), GRANTS (full CRUD on tax_calculation/jurisdiction_level_catalog, SELECT+INSERT-only append-only shape on tax_calculation_jurisdiction), and the FK target list for tax_calculation_jurisdiction — all agree between live DB, Drizzle source, and docs.

`billing`

Owns: The customer-receivables and vendor-payables control surface for the SETTLE half of Vrida's financial layer (tax calculates what's owed, billing records what actually moved). It tracks one AR account per (tenant, customer) with charges sourced from POS sales or orders, payments received against those charges, write-off/dispute adjustments, and generated customer statements — plus the mirror-image AP side (vendor payables and payments against purchasing.vendor_invoice). It is explicitly never a GL/journal; it's a control layer over receivables/payables, not double-entry bookkeeping. Layer: business Tables: 10 · Columns: 176 Depends on: crm, identity, platform, purchasing, shared, tax Depended on by: see cross-schema FK inventory. (vendor_payable is documented in the Drizzle source as the intended write-back target for purchasing.vendor_invoice's billing_ap_ref/payment_status_ref/paid_at placeholder columns — no live FK exists for this, it's a deferred service-layer write-back, not built yet.)

Table	Cols	Purpose	Key characteristics
`ap_payment`	17	A payment made to a vendor (LIGHT — judgment already happened upstream at `purchasing.vendor_invoice`'s approval gate).	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), cached counter (applied_amount_cents, CHECK-capped ≤ amount_cents, sourced from ap_payment_application), soft delete
`ap_payment_application`	9	Junction ledger recording how much of an `ap_payment` was applied against a specific `vendor_payable`.	tenant-scoped, append-only (REVOKE UPDATE/DELETE + trigger reject_append_only_mutation), RLS: tenant_id=current_setting
`ar_account`	20	One row per (tenant, customer) — the customer's receivables account and balance; the FULL-autonomy collections/dunning control surface.	tenant-scoped, RLS: tenant_id=current_setting, cached counter (current_balance_cents), review/approval seam (review_status/reviewed_by/decision_provenance) with self-approval block (reviewer != creator CHECK), soft delete
`ar_adjustment`	22	Write-off/dispute-resolution/goodwill-credit adjustment against an AR account or a specific charge (draft→posted→reversed).	tenant-scoped, RLS: tenant_id=current_setting, review/approval seam + self-approval block, soft delete
`ar_charge`	26	A customer receivable charge sourced from a POS sale or an order (or manual) — the core AR ledger row billing collects against.	tenant-scoped, RLS: tenant_id=current_setting, polymorphic ref (source_ref/source_payment_ref → pos.sale\|orders.order_header and pos.sale_payment\|orders.order_payment, no FK, validated by CHECK), review/approval seam + self-approval block, cached counter (applied_amount_cents), idempotency dedup (two-partial-unique split for NULL-distinctness)
`ar_charge_line`	12	Per-line decomposition of an `ar_charge`'s total, optionally tied to a source POS/orders line and a per-line tax calculation.	tenant-scoped, RLS: tenant_id=current_setting, header/line: ar_charge ↔ ar_charge_line, polymorphic ref (source_line_ref/source_line_type → pos.sale_line\|orders.order_line, no FK), write-once by convention but NOT append-only-enforced (full CRUD grant; only a header-is-truth sum-cap trigger constrains it)
`ar_payment`	23	A payment received from a customer against their AR account — the payment-matching-ambiguity and refund/dispute flagging surface.	tenant-scoped, RLS: tenant_id=current_setting, cached counter (applied_amount_cents), review/approval seam + self-approval block, soft delete
`ar_payment_application`	9	Junction ledger recording how much of an `ar_payment` was applied against a specific `ar_charge`.	tenant-scoped, append-only (REVOKE UPDATE/DELETE + trigger reject_append_only_mutation), RLS: tenant_id=current_setting, cross-table currency-agreement guardrail (trigger checks payment/charge/account currency all match)
`ar_statement`	21	A generated customer statement snapshot for a billing period (opening/closing balance, included charges).	tenant-scoped, RLS: tenant_id=current_setting, cached counter (charges_total_cents/payments_total_cents/closing_balance_cents), soft delete
`vendor_payable`	17	One row per vendor invoice — amount owed to a vendor and how much has been paid (LIGHT — judgment already happened at `purchasing.vendor_invoice`'s approval gate).	tenant-scoped, RLS: tenant_id=current_setting, cached counter (paid_amount_cents/open_amount_cents), soft delete

Discrepancies found (billing)

Stale column-count comments in Drizzle source (live DB and docs.md already agree with each other; only the inline .ts comment is wrong): ap.ts/ar.ts header comments understate 3 tables' current column counts, evidently left over from before later reopens added columns — ar_account comment says "(19, FULL)" but is actually 20 cols live (matches docs.md's stated 20, from the Remediation Phase 4 entity_id addition); vendor_payable comment says "(16, LIGHT)" but is actually 17 cols live (matches docs.md's 17, same entity_id addition); ar_charge_line comment says "(11, header/line remediation fix #2...)" but is actually 12 cols live (matches docs.md's 12).
docs.md's own trigger inventory undercounts the append-only enforcement triggers. The live DB (TRIGGERS section) shows trg_ap_payment_application_append_only on ap_payment_application and trg_ar_payment_application_append_only on ar_payment_application (both calling reject_append_only_mutation, corroborated by GRANTS showing no UPDATE/DELETE to authenticated on either table). docs/database/schema_docs/billing.md's per-table sections never name either trigger — ap_payment_application's section has no "Triggers" listing at all, and ar_payment_application's section lists only trg_ar_payment_application_validate_currency. The doc's own module-level reconciliation summary compounds this, stating "2 triggers total" (beyond the 7 set_updated_at ones) when the live DB actually shows 4 (missing exactly these same two append-only triggers). This matches CLAUDE.md's own claim that Remediation Phase 1 added append-only REVOKE+trigger enforcement to 9 ledger tables across 6 schemas — billing's schema doc simply never credited it to these two junction tables.

`payments`

Owns: The payments schema is Vrida's money-movement execution layer over Stripe (direct charges plus Stripe Connect for merchant payouts): the central incoming-charge ledger (payment_intent) and its refund/dispute/payout lifecycle records, saved cards-on-file (payment_method), each tenant's Stripe Connect account state (stripe_connect_account), Stripe Terminal physical card-reader registrations (terminal_reader), and Stripe webhook idempotency/dead-letter logs (stripe_event_log, stripe_event_dead_letter). It executes what billing has already recorded as owed/settled, and is deliberately structured so no agent can ever initiate a charge, refund, or payout — agents may only flag anomalies for human review. Layer: business Tables: 9 · Columns: 159 Depends on: crm, identity, multi_loc, platform, pos, shared Depended on by: No enforced inbound FKs exist (nothing outside payments holds a real FK into it). Drizzle source comments explicitly name pos.sale_payment, orders.order_payment, and billing.ar_payment as conceptual consumers of payment_intent via the polymorphic source_ref column, and those same 3 tables' stripe_payment_intent_id text columns as passive, unenforced mirrors populated once PaymentsService exists — none of this is a DB-level FK. See cross-schema FK inventory for anything not disclosed here.

Table	Cols	Purpose	Key characteristics
`dispute`	21	Chargeback lifecycle tied to a `payment_intent` — tracks Stripe dispute status, evidence deadlines, and resolution.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), self-approval block (reviewer ≠ creator), terminal-state evidence guard (chk_dispute_terminal_requires_evidence blocks won/lost/closed without resolved_at + evidence_submitted_at + payment_intent_id)
`payment_intent`	33	Central ledger for every incoming charge, sourced polymorphically from POS, Orders, or Billing.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), polymorphic ref (source_module/source_type/source_ref → pos.sale_payment / orders.order_payment / billing.ar_payment, no FK, validated by chk_payment_intent_source_pair), self-approval block (reviewer ≠ creator), idempotency/double-charge guard (partial-unique stripe_payment_intent_id + (tenant_id, idempotency_key)), never-move-money boundary (agent may only flag; refund/charge execution gated by review_status), vendor anchor column (processor, CHECK-limited to 'stripe' only)
`payment_method`	15	Saved card-on-file reference — Stripe is the card vault, only display-safe metadata (last4/brand) and a PM reference live here.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), one-default-per-customer guard (partial unique on customer_id WHERE is_default), no review seam (no autonomy/decision-provenance columns at all)
`payment_refund`	21	A partial or full refund against a `payment_intent`.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), polymorphic ref (source_refund_ref, bare uuid, no FK — traceback to pos.sale_refund/billing), self-approval block (reviewer ≠ creator), never-move-money boundary (agent-drafted refund must clear review_status before execution), idempotency guard (partial-unique (tenant_id, idempotency_key))
`payout`	20	Thin reference to a Stripe payout — Stripe itself is the ledger of what's included.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), self-approval block (reviewer ≠ creator), review seam is reconciliation-flagging only (automation_source defaults 'system')
`stripe_connect_account`	16	Per-tenant Stripe Connect account — one row per tenant, tracks onboarding state and the capability flags gating live payment processing.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), one-active-account-per-tenant guard (partial unique on tenant_id), onboarding-completeness guard (chk_stripe_connect_account_enabled_requires_complete blocks charges/payouts-enabled while onboarding ≠ complete)
`stripe_event_dead_letter`	10	Webhook events that failed processing after retries — one row per failed event, updated in place across retries.	tenant-scoped (nullable tenant_id / mixed-scope RLS: tenant_id=current_setting('app.current_tenant_id')), no soft delete, single-open-per-event guard (partial unique on stripe_event_id WHERE resolved_at IS NULL, prevents double-drive reprocessing)
`stripe_event_log`	8	Webhook idempotency log — insert-once, status-updated (not append-only; status/processed_at mutate post-insert).	tenant-scoped (nullable tenant_id / mixed-scope RLS: tenant_id=current_setting('app.current_tenant_id')), no soft delete, global (cross-tenant) unique on stripe_event_id — deliberate deviation from this codebase's usual tenant-scoped-unique convention, since Stripe event IDs are globally unique
`terminal_reader`	15	A physical Stripe Terminal card reader, registered per site with an optional POS register link.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), no reciprocal column on pos.register (link-don't-require-reciprocal)

Discrepancies found (payments)

No discrepancies found — live DB, Drizzle source, and docs agree. (Verified: 9 tables / 159 columns reconcile exactly against the fact file's TABLES section and docs/database/schema_docs/payments.md's own column-count table; the 32 FK rows counted in the fact file's FOREIGN KEYS section match the doc's stated "32 FK constraints"; RLS policy names, the payment_intent 3-way source_module/source_type widening, the processor anchor column, and the global non-tenant-scoped stripe_event_log unique are all consistent across all three sources.)

`returns`

Owns: The customer RMA (return-merchandise-authorization) lifecycle end to end: the authorization event itself (with risk-tiering for agent escalation), a frozen per-line discount/tax allocation and an aggregate-cap tracker that prevents over-returning a single source sale/order line across multiple separate RMAs, the physical return-receipt event that posts inventory movements, the resolution outcome (refund/store credit/replacement/repair/warranty credit/reject) including loyalty-point and offer-budget clawback, a tenant-configurable return-reason catalog, and a revived plant/product warranty-claim table. Layer: business Tables: 9 · Columns: 153 Depends on: crm, identity, inventory, multi_loc, offers, orders, platform, pos, rewards Depended on by: see cross-schema FK inventory (no other schema's Drizzle source names itself as a consumer of returns)

Table	Cols	Purpose	Key characteristics
`return_authorization`	29	RMA header — one row per return request, carrying source sale/order, customer, return type/channel, status lifecycle, and risk-tiering fields for agent escalation.	tenant-scoped, header/line: return_authorization ↔ return_authorization_line, RLS: tenant_id=current_setting('app.current_tenant_id'), self-approval block (reviewer≠creator CHECK, per Drizzle)
`return_authorization_line`	28	Freezes the per-unit discount/tax allocation and eligible-refund ceiling at RA-creation time for one returned line item; never trust `authorized_qty` alone as a cap.	tenant-scoped, header/line: return_authorization ↔ return_authorization_line, RLS: tenant_id=current_setting(...), cached counter (`received_qty`/`credited_qty` are peer counters maintained by sibling-table triggers `check_and_reserve_source_line`/`post_and_cap_return_receipt_line`/`validate_and_apply_resolution_line`, not self-maintained), self-approval block
`return_reason`	10	Tenant-scoped catalog of return reason codes, FK'd rather than a CHECK-enum.	tenant-scoped, RLS: tenant_id=current_setting(...)
`return_receipt`	14	Physical return-receipt header — one row per delivery event of returned goods against an RA.	tenant-scoped, header/line: return_receipt ↔ return_receipt_line, RLS: tenant_id=current_setting(...)
`return_receipt_line`	16	Per-item physical receipt line; an `AFTER INSERT` trigger re-reads the RLS/FK-validated row, derives the absorbable quantity from locked authoritative parents, and posts `inventory.stock_movement`/`stock_movement_line` atomically.	tenant-scoped, header/line: return_receipt ↔ return_receipt_line, RLS: tenant_id=current_setting(...), narrow owner SELECT/UPDATE policies for protected posting, guardrail: over-tolerance receipt is BLOCKed not flagged, self-referencing reversal FK
`return_resolution`	21	Outcome header for a return: refund, store credit, replacement, repair, warranty credit, or reject; links out to `pos.sale_refund`/loyalty/offer reversal rows rather than duplicating money.	tenant-scoped, header/line: return_resolution ↔ return_resolution_line, RLS: tenant_id=current_setting(...), self-approval block, polymorphic-ish clawback ref (`loyalty_reversal_ledger_id`/`offer_reversal_redemption_id`, real composite FKs though, not bare polymorphic)
`return_resolution_line`	8	Write-once financial fact recording how much of a return-authorization-line's eligible refund was resolved by a given resolution event.	tenant-scoped, header/line: return_resolution ↔ return_resolution_line, append-only (trigger `reject_append_only_mutation` present per fact file), RLS: tenant_id=current_setting(...), guardrail: cap+reconcile trigger (`validate_and_apply_resolution_line`) sums siblings against `eligible_refund_cents` and reconciles against `pos.sale_refund_line` when populated
`return_source_line_tracker`	10	Aggregate-cap cache — one row per original sale/order line, tracking cumulative authorized qty and eligible refund across every RMA ever filed against that line, since the source lines are append-only and can't carry their own counter.	tenant-scoped, RLS: tenant_id=current_setting(...), cached counter (`total_authorized_qty`/`total_eligible_refund_cents`, authoritative source is `pos.sale_line`/`orders.order_line` snapshotted at first touch; maintained by `check_and_reserve_source_line` trigger fired from `return_authorization_line`, not a trigger on this table itself)
`warranty`	16	Plant/product warranty (guarantee) instance — issued at time of sale, later claimable via a `return_resolution` (revived from v1 `pos.guarantee`).	tenant-scoped, RLS: tenant_id=current_setting(...)

Discrepancies found (returns)

Resolved by Inventory Core Write Protection (PROJECT_DECISIONS #75). Live grants now expose only SELECT, INSERT on returns.return_resolution_line to authenticated; UPDATE and DELETE are revoked while the append-only trigger remains as the second enforcement layer. The companion receipt-line posting trigger is AFTER INSERT, and its protected owner policies are represented in Drizzle.
Everything else checked — table count (9), column count (152, verified by summing the fact file's own col_count column), table names, column existence/nullability, composite-vs-bare FK shapes (including the two disclosed exceptions: bare customer_id → crm.customer and bare *_actor_id → identity.actor), RLS policy shape (tenant_id = current_setting('app.current_tenant_id')::uuid, uniform FOR ALL TO authenticated on all 9 tables), and trigger inventory (set_updated_at on 8/9 tables, absent only on the append-only return_resolution_line; the 3 named business-logic triggers check_and_reserve_source_line/post_and_cap_return_receipt_line/validate_and_apply_resolution_line) — all agree between the live-DB fact file, the Drizzle source, and the existing docs.
One minor, non-contradicting gap: the docs state return_source_line_tracker is "updated_at: trigger-maintained via platform.set_updated_at()" (schema_docs/returns.md, in the table's own summary line), but the fact file's TRIGGERS section shows zero triggers of any kind registered on return_source_line_tracker — no set_updated_at trigger exists on this table live. This isn't necessarily wrong in effect (the row's updated_at may instead be set inline by check_and_reserve_source_line(), which is the trigger that actually mutates this table, firing from return_authorization_line rather than from return_source_line_tracker itself), but the specific mechanism the docs name (a set_updated_at trigger on this table) is not present in the live DB.

8.3 Consumer layer

`consumer`

Owns: Vrida's platform-level, cross-tenant shopper identity: one consumer account per real-world person (linked to Supabase Auth, not to any single tenant), their multi-valued identifiers (email/phone/social/device), addresses, plant interests, formal platform-level consent, a maintained RFM/feature cache for personalization, the anonymous-to-identified event-resolution join, an identity-merge audit trail, and the two tenant-scoped tables (consumer_merchant_link, event) that let a merchant see which consumers are linked to it and what those consumers did in its store. Layer: consumer Tables: 10 · Columns: 104 Depends on: multi_loc, platform, shared Depended on by: see cross-schema FK inventory

Table	Cols	Purpose	Key characteristics
`consumer`	17	The core account — one row per shopper across all tenants, with Supabase-auth linkage, a status lifecycle (unclaimed→active→suspended→closed), and email/phone as a display cache only (not synced from `consumer_identifier`).	consumer-scoped, RLS: `id = current_setting('app.current_consumer_id')`, guardrail: dedicated `consumer_authenticated` role — merchant `authenticated` REVOKEd entirely
`consumer_address`	13	Platform-level shopper addresses (home/shipping/billing), distinct from `crm.address` (the merchant's own delivery address for a customer).	consumer-scoped, RLS: `consumer_id = current_setting('app.current_consumer_id')`
`consumer_consent`	9	Platform-level formal consent record (cross-merchant profiling, platform marketing, AI personalization) — a status change is a new row, never an update.	consumer-scoped, append-only (`trg_consumer_consent_append_only` → `reject_append_only_mutation`), RLS: `consumer_id = current_setting('app.current_consumer_id')`
`consumer_feature`	11	RFM/derived-feature store for the recommendation/targeting engine — a wholesale-recomputed cache, not a source of truth.	consumer-scoped, cached counter (recency/frequency/monetary/RFM fields — the computing batch job is disclosed as not yet built), RLS: `consumer_id = current_setting('app.current_consumer_id')`
`consumer_identifier`	9	Multi-valued identifier model (email/phone/google_sub/apple_sub/device_id/card_token) per consumer, real columns rather than JSONB since identifiers are individually filtered/joined.	consumer-scoped, RLS: `consumer_id = current_setting('app.current_consumer_id')`, guardrail: partial-unique one-active-claim-per-identifier lock (`WHERE superseded_at IS NULL`)
`consumer_interest`	8	Multi-valued plant/care interests per consumer, feeding the recommendation engine.	consumer-scoped, RLS: `consumer_id = current_setting('app.current_consumer_id')`
`consumer_merchant_link`	12	The consumer's own record of which tenant nurseries they're linked to (renamed from v1's `consumer_tenant_link`) — the mirror of `crm.customer.consumer_id`, not the same record.	tenant-scoped, RLS: `tenant_id = current_setting('app.current_tenant_id')`, polymorphic ref (`crm_customer_id`, loose, no FK, deliberate cross-RLS-domain gap)
`event`	10	The engagement spine — one row per purchase/cart-add/view/email/loyalty/offer/search touchpoint, supporting both identified (`consumer_id`) and anonymous (`anonymous_id`) shoppers.	tenant-scoped, append-only (`trg_event_append_only` → `reject_append_only_mutation`), RLS: `tenant_id = current_setting('app.current_tenant_id')`, guardrail: reader-enforced (not DB-enforced) contract requiring `identity_map` join to catch pre-login activity
`identity_map`	6	Resolves an `anonymous_id` to the `consumer_id` it was later matched to — the join `event`'s own read contract depends on.	consumer-scoped, RLS: `consumer_id = current_setting('app.current_consumer_id')` (pre-resolution rows invisible to everyone)
`identity_merge_event`	9	Append-only audit of a consumer-identity merge (unclaimed stub claimed by login, or two accounts matched on a shared identifier) — audits, does not itself prevent, mis-merges.	consumer-scoped, append-only (`trg_identity_merge_event_append_only` → `reject_append_only_mutation`), RLS: `source_consumer_id = current_setting(...) OR target_consumer_id = current_setting(...)`

Discrepancies found (consumer)

Docs file is stale on lock status. docs/database/schema_docs/consumer.md states the module is "not yet locked" and "PROJECT_DECISIONS entry: pending (not yet logged — this build has not reached the lock gate)." Per CLAUDE.md, consumer was actually schema-locked on 2026-07-11 (PROJECT_DECISIONS #56) and was subsequently referenced again in a same-day rewards/offers fix pass (PROJECT_DECISIONS #60). The table/column inventory and structural details in the doc otherwise match the live-DB fact file exactly (10 tables / 104 columns), so this is a status-line staleness issue, not a structural drift.
Minor: Drizzle inline comments overstate 3 tables' column counts — consumer_identifier.ts says "(10 cols)" but the live table has 9; engagement.ts's event says "(11 cols)" but the live table has 10; consumer_feature says "(13 cols, non-tenant, maintained cache)" but the live table has 11. This is already caught and correctly reconciled in the docs' own "Column-count reconciliation" section (which explicitly flags these as source-comment bookkeeping slips, not build errors), so it's not a live discrepancy — just a trap for anyone reading the .ts comments in isolation.
Minor: docs' own trigger-section header count is internally inconsistent. "Triggers, full list (3 functions, 7 trigger objects)" is followed by a table that only exercises 2 distinct trigger functions (platform.set_updated_at(), platform.reject_append_only_mutation()) — matching the fact file's TRIGGERS section exactly (7 objects, 2 functions). The "3rd function" appears to count consumer.get_cross_tenant_activity(), which the same doc explicitly labels "not a trigger" one paragraph later.

`rewards`

Owns: The rewards schema owns the per-merchant points/loyalty engine — program configuration (rates, rounding, expiry policy, optional site-scoping), the rules that decide how points are earned, a tier catalog, a redeemable-reward catalog, per-consumer point accounts, and an append-only point ledger (plus a small companion table that caps proportional/partial reversals against it) that is the sole source of truth for point balances. Layer: consumer Tables: 7 · Columns: 109 Depends on: consumer, identity, multi_loc, platform, pos Depended on by: see cross-schema FK inventory

Table	Cols	Purpose	Key characteristics
`loyalty_account`	14	The per-(consumer, tenant) loyalty account — point balance, lifetime totals, tier status, enrollment/activity timestamps.	tenant-scoped, cached counter (`balance_points`/`lifetime_points`, maintained by `loyalty_point_ledger`'s sync trigger), RLS: tenant_id=current_setting('app.current_tenant_id')
`loyalty_accrual_rule`	22	Defines how points are earned per program — multiplier or flat-bonus rules scoped to purchase/category/item/threshold/birthday/signup/etc., with priority and an active window.	tenant-scoped, RLS: tenant_id=current_setting(...), agent review seam (automation_source/review_status/reviewed_by_actor_id)
`loyalty_point_ledger`	18	Append-only fact table of every point-affecting event (earn/redeem/expire/adjust/reverse/promo_bonus) per account; the sole source of truth for balances.	tenant-scoped, append-only (trigger reuses `reject_append_only_mutation`), polymorphic ref (source_type/source_ref for non-sale sources, no FK), self-referencing lineage (expired_from_ledger_id/reversed_ledger_id), RLS: tenant_id=current_setting(...), balance_after_points is trigger-derived, never caller-supplied
`loyalty_point_ledger_reversal_tracker`	7	Cumulative cap counter for proportional/partial reversals against one ledger entry — exists only because the ledger itself is append-only and can't host a maintained running total.	tenant-scoped, RLS, cached counter (`total_reversed_points`, source: `loyalty_point_ledger` via `sync_loyalty_account_balance()`), atomic row-locking reversal cap guardrail
`loyalty_program`	14	Per-tenant (optionally per-site) loyalty program configuration — points-per-dollar rate, rounding, minimum redemption, expiry policy.	tenant-scoped, RLS, no autonomy pack (static config)
`loyalty_reward_tier`	12	Static tier catalog per program — tier code, minimum-points threshold, sort order, earn multiplier, benefit label.	tenant-scoped, RLS, no autonomy pack
`reward_option`	22	Redeemable reward catalog per program — discount amount/percent, free item, or perk, priced in points, optionally tier-gated.	tenant-scoped, RLS, agent review seam (automation_source/review_status/reviewed_by_actor_id)

Discrepancies found (rewards)

No discrepancies found — live DB, Drizzle source, and docs agree (table/column counts, FK shapes, RLS policies, triggers, and grants all reconcile exactly across all three sources).

`offers`

Owns: Merchant-issued (and optionally AI-suggested) discount and promo offers for the consumer-facing storefront: offer definitions (percent/amount/free-item/BOGO discounts with budget, redemption, and margin ceilings), coupon codes, per-consumer assignment lifecycle (issued → viewed → claimed → redeemed), an append-only redemption ledger tied to POS sales, a targeting-rule engine (segment/category/engagement/geography/growing-zone/visit-frequency), and two maintained counter tables (a per-consumer discount-exposure rollup and a per-redemption cumulative-reversal tracker). Layer: consumer Tables: 7 · Columns: 123 Depends on: consumer, crm, identity, multi_loc, platform, pos, shared Depended on by: see cross-schema FK inventory

Table	Cols	Purpose	Key characteristics
`customer_discount_exposure`	9	Rolling-period rollup of a consumer's total discount exposure, for future fraud/cap/RFM analysis — not itself a redemption record.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), cached counter (`total_discount_cents`/`redemption_count`, atomic-UPSERT maintained per Drizzle comment, mirrors `ai.agent_usage_period`)
`offer`	43	The offer definition: discount type/value, budget and redemption caps, distribution/targeting config, site scoping, and AI provenance/review/guardrail metadata.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), cached counter (`budget_used_cents`/`redemption_count`, trigger-maintained by `check_and_sync_offer_budget`), margin/discount guardrail (`chk_offer_ai_requires_guardrail` — any AI-touched offer must carry a discount ceiling)
`offer_assignment`	15	Per-consumer lifecycle of a targeted offer: issued → viewed → claimed → redeemed/expired/cancelled.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id')
`offer_code`	13	Coupon/promo codes for `distribution_type='code'` offers, with case-insensitive `normalized_code` lookup.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), cached counter (`redeemed_count`, trigger-maintained by `check_and_sync_offer_budget`)
`offer_redemption`	15	Ledger fact of each offer redemption/reversal against a POS sale (and optionally a sale line), driving budget/margin guardrail enforcement.	tenant-scoped, append-only (`trg_offer_redemption_append_only` → `reject_append_only_mutation`; GRANTS confirm no UPDATE/DELETE to `authenticated`), RLS: tenant_id=current_setting('app.current_tenant_id'), guardrail trigger (`check_and_sync_offer_budget` — atomic margin-floor + discount-ceiling + budget-cap check, zero-cost fail-closed)
`offer_redemption_reversal_tracker`	7	Mutable cumulative-reversed-amount counter per original redemption — exists because `offer_redemption` itself is append-only and can't host a running total.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), cached counter (`total_reversed_cents`, atomically capped against `offer_redemption.discount_amount_applied_cents`), guardrail (magnitude cap blocking over-reversal)
`offer_targeting_rule`	21	AI/merchant-defined targeting rules (segment, category affinity, engagement level, geography, growing zone, visit frequency) determining which consumers see an offer.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id'), type-discriminated 6-branch column set enforced by `chk_offer_targeting_rule_type_coherence`, cross-tenant guard trigger (`trg_offer_targeting_rule_validate_segment` — `segment_definition_id` is a plain, non-composite FK since `crm.customer_segment_definition` lacks `UNIQUE(id,tenant_id)`, so same-tenant-or-global is enforced by trigger instead)

Discrepancies found (offers)

The docs' own "Triggers, full list" summary states "3 functions, 7 trigger objects" — the live-DB fact file's TRIGGERS section shows 9 trigger objects and 4 distinct functions: set_updated_at (6 objects, one per table: offer, offer_code, offer_assignment, offer_targeting_rule, customer_discount_exposure, offer_redemption_reversal_tracker), reject_append_only_mutation (1, on offer_redemption), check_and_sync_offer_budget (1, on offer_redemption), and validate_offer_targeting_rule_segment (1, on offer_targeting_rule) = 9 objects / 4 functions, not 7/3. The docs' own detailed table row-by-row is actually consistent with 9/4 — only the summary header text undercounts.
All other checks agree: table count (7) and total column count (123) match exactly between the fact file and docs; offer's 43-column count matches; all composite/bare FK targets (platform.tenant, multi_loc.site, consumer.consumer, pos.sale/sale_line, crm.customer_segment_definition, shared.climate_zone, identity.actor, self-referencing offer_redemption) match; RLS policy names and tenant-isolation shape match on all 7 tables; offer_redemption's append-only GRANTS (SELECT, INSERT only, no UPDATE/DELETE) match the docs' claimed REVOKE.

8.4 Intelligence layer

`ai`

Owns: This schema is the agent-runtime substrate: it logs and governs every AI-driven action and inference call (ai_request the mechanical Bedrock-call log; agent_execution the business-language, partitioned, append-only execution ledger carrying kill-switch/runaway-ceiling/idempotency/single-resolver guards), meters agent usage and cost (agent_usage_period), stores a governed, sourced tenant operating-memory (agent_memory + agent_memory_source), houses the legacy AI-onboarding import pipeline (import_job/import_file/import_record), and — since the Phase 2 reopen — owns a 3-level LLM provider/model/deployment registry (provider_registry → model_family → model_version → model_deployment + its _limit/_region/_policy/_override/_status_observation satellites) plus an A2b-shaped prompt registry (prompt_definition/prompt_version/prompt_model_compatibility) and a routing-policy table. It is the infrastructure every other module's autonomy columns (automation_source, review_status, decision_provenance) ultimately report back to, and the not-yet-built agents orchestration layer's own runtime substrate. Layer: intelligence Tables: 21 · Columns: 235 Depends on: agents, approvals, identity, platform Depended on by: see cross-schema FK inventory (not derivable from ai's own outbound-FK data). One explicit exception: packages/db/src/schema/ai/memory.ts's own comment states agent_memory is "the real target decision_provenance.memory_refs" (documented on several crm/inventory tables) "finally points at" — a disclosed, non-enforced documentation link, not a real FK.

Table	Cols	Purpose	Key characteristics
`agent_execution`	28	Business-language, append-only ledger of every agent action — what it did, why, evidence, cost, and (via self-FK) what earlier proposal it resolves.	tenant-scoped, append-only, partitioned by created_at (RANGE, 14), polymorphic ref (target_module/target_table/target_row_id), RLS: tenant_id=current_setting('app.current_tenant_id'), idempotency dedup (advisory-lock trigger, not a native index — can't survive partitioning), single-resolver guard (composite self-FK + row-locking trigger), runaway-loop/cost ceiling (BLOCK not flag), kill-switch/blocked-policy gate, retired-tool-version rejection, C8 needs_approval-requires-resolution CHECK
`agent_memory`	21	Governed, sourced, confidence-rated tenant operating-memory store.	tenant-scoped, partitioned by created_at (RANGE, 14), RLS: tenant_id=current_setting(...), single-active-per-key guard (advisory-lock trigger, replaces a partial-unique index a partitioned table can't carry), GDPR/erasure fields (retention_until, subject_type/subject_ref polymorphic ref, expires_at)
`agent_memory_source`	7	Join table linking one memory entry to the (possibly multiple) source records it was derived from.	tenant-scoped, polymorphic ref (source_type/source_ref, 4 possible targets, no FK), composite FK into partitioned agent_memory (shadow created_at column), RLS: tenant_id=current_setting(...)
`agent_usage_period`	12	Cumulative cost/token/quantity usage meter per agent per period — closes the volume-abuse gap in `identity.agent_duty_grant`'s per-action-only spend limit.	tenant-scoped, cached counter (atomic ON-CONFLICT UPSERT of agent_execution.cost_millicents + ai_request.total_token_count — app-level read-then-write explicitly forbidden), RLS: tenant_id=current_setting(...)
`ai_request`	19	Mechanical Bedrock inference-call log (tokens, cost, latency, status) — never stores prompt/response content.	tenant-scoped (tenant_id nullable for platform-level calls), RLS: tenant_id=current_setting(...), two-partial-unique NULL-tenant-safe idempotency dedup
`import_file`	15	One uploaded file within an import job — detected type/entity, inferred column mapping, parse status.	tenant-scoped, RLS: tenant_id=current_setting(...), polymorphic/deferred forward-ref (file_id → `files` schema, no FK)
`import_job`	17	Import batch header — one row per tenant import run (onboarding-gated or manual re-import).	tenant-scoped, RLS: tenant_id=current_setting(...)
`import_record`	21	One parsed row per file — the load-bearing unit of the import pipeline (raw/mapped/corrected data, review + load status).	tenant-scoped, polymorphic ref (target_module/target_table/target_row_id, all-or-nothing CHECK, no FK), RLS: tenant_id=current_setting(...)
`model_deployment`	14	Durable, never-overwritten model-deployment config — routes a model_version + provider to a region at a traffic weight.	global, chained/immutable (previous_deployment_id/rollback_target_deployment_id self-FKs; a traffic-weight change is a new row, not an edit)
`model_deployment_limit`	6	Rate-limit/concurrency satellite per deployment.	global
`model_deployment_override`	9	Small, frequently-mutated administrative override surface (manual disable, forced shadow, emergency traffic weight, circuit-open) kept separate from the durable deployment row.	global, residency-compliance guard trigger (emergency_traffic_weight can't force traffic toward a non-compliant region even under incident pressure)
`model_deployment_policy`	6	Fallback eligibility + traffic policy (JSONB) for a deployment.	global
`model_deployment_region`	5	Approved regions + residency compatibility per deployment, feeding the (not-yet-built) routing resolver's candidate filter.	global
`model_deployment_status_observation`	8	Transient runtime health observation (latency, error rate, queue depth, circuit state) — interim in-DB stand-in for external observability.	global, mutable/purgeable by design (not append-only, not partitioned; cleanup via a documented-but-unbuilt retention job)
`model_family`	6	A model lineage (e.g. "claude-sonnet") under a provider — identity half of a definition/version split.	global, version-row: model_family ↔ model_version
`model_version`	9	A specific, immutable model release under a model_family (capabilities JSONB, context window, status).	global, version-row: model_family ↔ model_version
`prompt_definition`	6	A named prompt — identity half of a definition/version split.	global, version-row: prompt_definition ↔ prompt_version
`prompt_model_compatibility`	6	Many-to-many compatibility mapping between prompt_version and model_version.	global
`prompt_version`	7	An immutable prompt template text version under a prompt_definition.	global, version-row: prompt_definition ↔ prompt_version
`provider_registry`	6	LLM provider catalog (Bedrock, direct Anthropic, etc.) — root of the 3-level provider→family→version registry.	global
`routing_policy`	7	Routing eligibility/priority policy (criteria JSONB) feeding a not-yet-built model-selection resolution function.	global (tenant_id nullable — NULL=platform-wide default, set=tenant-specific override; no RLS by design, both scopes must stay visible to the resolver)

Discrepancies found (ai)

docs/database/schema_docs/ai.md is stale relative to live DB / Drizzle for the Phase 5 (2026-07-16) build. It still states "21 logical tables, 227 columns" and documents agent_execution at 19→20 columns (Phase 2 state), with no mention of the 8 Phase-5 columns (agent_task_id, sequence_index, tool_version_id, approval_request_id, retry_count, blocked_by_policy, confidence_threshold, escalation_reason). Live DB confirms agent_execution now has 28 columns, and the schema-wide total is 21 tables / 235 columns, not 227.
Same doc still describes routing_policy.workload_class_id as a "deferred forward-ref, no FK" to agents.workload_class (see its lines ~40, 826, 993). Live DB's FOREIGN KEYS section shows a real, enforced FK (routing_policy_workload_class_id_fkey → agents.workload_class), and the Drizzle source (routing.ts) itself already documents this was "WIRED (Phase 5 landed 2026-07-15 and closed this forward-ref same-day)." The docs file was not updated for this closure.
packages/db/src/schema/ai/index.ts (the module's own barrel) doesn't re-export 4 of its 10 source files. It exports only _schema.ts, import.ts, request.ts, execution.ts, usage.ts, memory.ts — omitting memory_source.ts, prompt.ts, registry.ts, and routing.ts. Since the top-level packages/db/src/schema/index.ts re-exports ai/index.js verbatim, the Drizzle definitions for agent_memory_source, the prompt registry (3 tables), the model registry/deployment chain (9 tables), and routing_policy — 14 of this schema's 21 live, migrated tables — are not reachable through the package's normal public import path, even though they exist and are populated in the database.

`semantics`

Owns: The shared business-ontology/vocabulary layer that lets agent decisions and tenant configuration reference well-defined business concepts as data instead of free text — named metric definitions and their versioned, function-backed computations (e.g. "SKU margin"), named entities and their canonical tables/aliases (e.g. "customer" → crm.customer), named dimensions a metric can be sliced by, named goals and hard/soft constraints a tenant can prioritize (e.g. "protect cash over margin"), named attribution methodologies, an approved-function registry that all versioned computations resolve through (never raw SQL in a data row), and three tenant-scoped "binding" tables that activate a specific definition/version for a tenant over a time range. It has no v1 antecedent — this is a brand-new foundation-layer ontology schema built for the agents-v2/v3 effort. Layer: intelligence Tables: 14 · Columns: 114 Depends on: identity, platform Depended on by: see cross-schema FK inventory — no other schema's live FKs point into semantics yet per this fact file. The Drizzle source documents an intended future consumer: attribution.ts's header comment states signals.outcome_observation.attribution_model_version_id is expected to FK into attribution_model_version, replacing v2's original free-text attribution_method column (not yet confirmed live in this fact file, which covers semantics only). The _schema.ts header also frames the whole schema as "the vocabulary layer agent decisions read," implying agents/signals as its intended readers generally.

Table	Cols	Purpose	Key characteristics
`approved_function_registry`	11	Allowlist of approved SQL functions that metric/attribution-model versions point to (a function reference, never raw SQL in a row); carries a `definition_hash` so bindings can be re-verified against the function's live definition on every use.	global, RLS: none, write-restricted (SELECT-only to `authenticated`; INSERT/UPDATE/DELETE not granted — service_role-authored), supply-chain re-verification guardrail (backs `verify_function_still_matches_approval()`, invoked by `tenant_metric_binding`'s trigger)
`attribution_model_definition`	4	Names an attribution methodology (e.g. last-touch vs. linear) — the identity-row half of a definition/version pair.	global, version-row (sibling: `attribution_model_version`), RLS: none, write-restricted (SELECT-only to `authenticated`)
`attribution_model_version`	8	A versioned revision of an attribution model; `computation_reference` points into `approved_function_registry` rather than storing free text.	global, version-row, RLS: none, write-restricted (SELECT-only to `authenticated`)
`constraint_definition`	7	A hard or soft limit, optionally tied to a `goal_definition` (e.g. "never let a single discount exceed 30%").	global, RLS: none, write-restricted (SELECT-only to `authenticated`)
`dimension_definition`	5	Names a dimension a metric can be sliced/grouped by (e.g. site, customer segment).	global, RLS: none, write-restricted (SELECT-only to `authenticated`)
`entity_alias`	5	Declares that an alternate table/view represents the same or a related entity as its parent `entity_definition` (e.g. `consumer.consumer` `same_as` `crm.customer`).	global, RLS: none, write-restricted (SELECT-only to `authenticated`), write-once by shape (`created_at` only, no `updated_at` column — not DB-enforced append-only)
`entity_definition`	6	Names a canonical business entity concept and its table (e.g. "customer" → `crm.customer`); makes deferred/unresolved cross-schema FK gaps (e.g. `crm.customer.consumer_id`) a queryable fact rather than prose in two docs.	global, RLS: none, write-restricted (SELECT-only to `authenticated`)
`goal_definition`	8	A named tenant-priority goal (e.g. "protect cash over margin") that `agents.agent_task.goal_description` can point to instead of free text; optionally scoped to a business domain via `applies_to_domain`.	global, RLS: none, write-restricted (SELECT-only to `authenticated`)
`metric_definition`	6	Names a business metric concept (e.g. "SKU margin") — the identity-row half of the metric definition/version pair.	global, version-row (sibling: `metric_version`), RLS: none, write-restricted (SELECT-only to `authenticated`)
`metric_dependency`	4	Declares which metrics depend on which other metrics (e.g. a ratio metric's numerator/denominator).	global, RLS: none, write-restricted (SELECT-only to `authenticated`), cycle-detection guardrail (`trg_metric_dependency_no_cycle` / `check_metric_dependency_no_cycle`, a recursive-CTE trigger; direct self-reference separately blocked by CHECK)
`metric_version`	8	A versioned, computable revision of a metric definition; `resolved_function_reference` points into `approved_function_registry` rather than storing free text.	global, version-row, RLS: none, write-restricted (SELECT-only to `authenticated`, no UPDATE — platform-authored)
`tenant_constraint_binding`	15	Binds a tenant to a specific `constraint_definition` at a target value/priority for a time range — the tenant-specific activation of a catalog constraint.	tenant-scoped, RLS: `tenant_constraint_binding_tenant_isolation` policy (ALL cmds) on `authenticated`, `tenant_id = current_setting('app.current_tenant_id')`, EXCLUDE overlap guard (`EXCLUDE USING gist` on tenant+constraint+time-range, hard-reject — no flag-not-reject leniency, unlike `platform.accounting_period`), approval-status gate (`pending`/`approved`/`rejected`)
`tenant_goal_binding`	15	Binds a tenant to a specific `goal_definition` at a target value/priority for a time range — mirrors `tenant_constraint_binding` exactly.	tenant-scoped, RLS: `tenant_goal_binding_tenant_isolation` policy (ALL cmds) on `authenticated`, `tenant_id = current_setting(...)`, EXCLUDE overlap guard (hard-reject), approval-status gate
`tenant_metric_binding`	12	Declares which `metric_version` is authoritative for a tenant over a time range — the tenant's own choice of "which computation revision, since when."	tenant-scoped, RLS: `tenant_metric_binding_tenant_isolation` policy (ALL cmds) on `authenticated`, `tenant_id = current_setting(...)`, EXCLUDE overlap guard (hard-reject), supply-chain re-verification guardrail (`trg_tenant_metric_binding_verify_function` rejects a binding write if the referenced function's live definition no longer matches its approved hash), approval-status gate

Discrepancies found (semantics)

No discrepancies found — live DB, Drizzle source, and docs agree. Table count (14), column count (114, verified by summing the fact file's TABLES section), RLS shape (exactly tenant_metric_binding/tenant_goal_binding/tenant_constraint_binding have rowsecurity=true, all others false), the full FK list, and the trigger set (set_updated_at on exactly the 7 tables the docs name, trg_metric_dependency_no_cycle, trg_tenant_metric_binding_verify_function) all match across the fact file, the Drizzle source, and docs/database/schema_docs/semantics.md.

`signals`

Owns: The observational/bitemporal telemetry substrate for agent-autonomy measurement — computed feature/forecast/anomaly-score values against arbitrary polymorphic entities over time (business-truth time via as_of vs. ERP-knowledge time via recorded_at), an experiment/causal-inference subsystem (experiment definitions/versions, subject-to-treatment-arm assignment, exposure events, contamination tracking), and a split-authority outcome-measurement ledger (outcome_observation + outcome_authority) that records repeated remeasurements of an agent action's effect while pinning exactly one row as authoritative per scope. This is the substrate the agents module reads/writes to determine whether an autonomous agent decision actually produced its intended effect. Layer: intelligence Tables: 11 · Columns: 111 Depends on: agents, platform, semantics Depended on by: agents module is a named consumer per the Drizzle source and docs — via the agent_reader role calling the get_feature_as_of() / get_forecast_as_of() / get_anomaly_score_as_of() SECURITY DEFINER functions, and via the composite FKs outcome_observation.agent_action_id / outcome_authority.agent_action_id → agents.agent_action. Otherwise see cross-schema FK inventory.

Table	Cols	Purpose	Key characteristics
`anomaly_score`	10	Bitemporal, partitioned store of anomaly-detection scores per polymorphic entity (e.g. isolation-forest output per the `lineage` JSONB example); a leaf table nothing else FKs into.	tenant-scoped, partitioned by recorded_at (RANGE), bitemporal, polymorphic ref (entity_type/entity_ref, no FK), RLS: disabled (rowsecurity=false) — REVOKE ALL from authenticated, function-only read path
`experiment`	6	Global catalog/definition row for an experiment — the stable concept an `experiment_version` row revises.	global, version-row (paired with experiment_version), RLS: none, updated_at trigger (set_updated_at)
`experiment_assignment`	19	Records which subject (polymorphic subject_type/subject_ref) was assigned to which treatment arm of an experiment version, with randomization, eligibility, exposure-linkage and contamination-status fields for causal-inference integrity.	tenant-scoped, RLS: tenant_id=current_setting('app.current_tenant_id')::uuid, polymorphic ref (subject_type/subject_ref, no FK), timestamp-forgery guard (force_assignment_timestamp trigger unconditionally overwrites assigned_at)
`experiment_exposure_event`	5	Append-only exposure-occurrence log, tied to a pre-existing experiment_assignment and validated to occur after assignment.	tenant-scoped, RLS: tenant_id=current_setting(...)::uuid, append-only guard (validate_exposure_after_assignment BEFORE INSERT trigger + mandatory composite FK to experiment_assignment), cached-counter-adjacent trigger (update_first_eligible_exposure maintains experiment_assignment.first_eligible_exposure_at)
`experiment_version`	9	Immutable versioned revision of an experiment — treatment logic, re-entry/washout rules.	global, version-row (sibling of experiment), RLS: none
`feature_definition`	6	Global catalog/definition row for a computed feature — the stable concept a `feature_version` row revises.	global, version-row (paired with feature_version), RLS: none, updated_at trigger (set_updated_at)
`feature_value`	10	Bitemporal, partitioned store of computed feature values per polymorphic entity — the input signal other tables/models consume.	tenant-scoped, partitioned by recorded_at (RANGE), bitemporal, polymorphic ref, RLS: disabled — REVOKE ALL from authenticated, function-only read (get_feature_as_of())
`feature_version`	7	Versioned revision of a feature_definition — how the feature is actually computed.	global, version-row (paired with feature_definition), RLS: none
`forecast`	11	Bitemporal, partitioned store of forecasted values (plus a `confidence_interval` numrange) per polymorphic entity.	tenant-scoped, partitioned by recorded_at (RANGE), bitemporal, polymorphic ref, RLS: disabled — REVOKE ALL from authenticated, function-only read
`outcome_authority`	9	Split-authority table: exactly one row per (tenant, agent_action, outcome_type, measurement_window) naming which `outcome_observation` row currently counts — the real enforcement a partitioned unique index can't express.	tenant-scoped, RLS: tenant_id=current_setting(...)::uuid, composite PK is the authority mechanism, cross-schema FK → agents.agent_action (composite, wired), FK → signals.outcome_observation (3-col composite incl. partition key), authenticated has SELECT/INSERT/UPDATE (broad for this schema — populated via native upsert)
`outcome_observation`	19	Append-only-by-convention, remeasurable ledger of individual outcome-measurement events for an agent action, versioned per remeasurement, feeding `outcome_authority`'s promotion.	tenant-scoped, partitioned by created_at (RANGE), RLS: tenant_id=current_setting(...)::uuid, append-only (REVOKE UPDATE + narrowed UPDATE(status,validated_at) grant — not the shared reject-mutation trigger), cross-schema FK → agents.agent_action (composite, wired) and → semantics.attribution_model_version, cross-partition duplicate-version guard trigger, promotion trigger populating outcome_authority

Discrepancies found (signals)

Stale forward-ref documentation. docs/database/schema_docs/signals.md (as currently written) still describes outcome_observation.agent_action_id and outcome_authority.agent_action_id as "DISCLOSED FORWARD-REF, no FK" and lists them as an open item pending the not-yet-built agents module. But both the live-DB fact file's FOREIGN KEYS section (outcome_authority_agent_action_id_tenant_fkey, outcome_observation_agent_action_id_tenant_fkey, both → agents.agent_action) and the Drizzle source (packages/db/src/schema/signals/outcome.ts, which contains real foreignKey() blocks with comments explicitly marked "WIRED (Phase 5, 2026-07-15)") confirm these are now genuine composite FKs. This matches the project's own build narrative (Agents/Phase 5, 2026-07-16) closing this exact forward-ref — the schema doc simply hasn't been refreshed to reflect it.

`agents`

Owns: The full agent-orchestration/runtime module — content-hashed identity+version catalogs for tools, toolsets, and skills (certifiable, replayable behavior); a marketplace/deployment layer (catalog listings, per-tenant deployments, per-agent tool grants); the task-orchestration loop (schedule/trigger → task → thread → event_log) with atomic lease/fencing claim semantics and a runaway-cost/step ceiling; an append-only decision/action ledger (decision_context_snapshot → agent_decision → agent_action) backed by a typed "evidence envelope" (5 direct-link join tables + a manifest pointing at archived evidence in object storage); rollback/incident/certification/eval machinery for governing, scoring, and reversing what agents do; a tenant-or-global kill-switch (split append-only history + upserted current-state tables); and policy tables (skill_execution_policy, workload_class) bounding cost, concurrency, and latency class. It is the full v1 21-table orchestration baseline merged with the v2/v3 amendment and correction passes into one build. Layer: intelligence Tables: 47 · Columns: 475 Depends on: ai, approvals, files, identity, platform, semantics, signals Depended on by: see cross-schema FK inventory

Table	Cols	Purpose	Key characteristics
`agent_action`	15	Append-only fact-record of a single write an agent claims to have made against some `source_module`/`source_type`/`source_ref`, linked to the decision/execution that authorized it.	tenant-scoped, append-only, polymorphic ref (`source_type`/`source_ref`, no FK on `source_ref`), saga gate (`enforce_single_module_autonomy` trigger — single-module claim unless `approval_request_id`/`orchestration_id` set), decision/execution consistency trigger, RLS: tenant_id=current_setting(...)
`agent_autonomy_profile`	12	Per-agent-identity, per-domain current autonomy mode plus rolling-window promotion criteria.	tenant-scoped, RLS: tenant_id=current_setting(...); `current_mode` is disclosed advisory/tracking-only — not DB-enforced (the enforced source, `identity.agent_duty_grant.authority_level`, has no REVOKE/trigger yet)
`agent_catalog_entry`	14	Global (Vrida/partner-authored) marketplace listing a tenant can deploy an agent from.	global, RLS: none
`agent_catalog_entry_required_tool`	4	Join: which `tool_version` a catalog entry requires.	global
`agent_decision`	9	Append-only, partitioned; one row per distinct decision an `agent_execution` reaches (an execution may make several), linked to the `decision_context_snapshot` used.	tenant-scoped, append-only, partitioned by created_at (RANGE), 14 partitions, RLS: tenant_id=current_setting(...)
`agent_eval_case`	9	A single test-case result (pass/fail + actual vs. expected trajectory) within an `agent_eval_run`.	tenant-scoped, RLS: tenant_id=current_setting(...)
`agent_eval_run`	14	One execution of an eval-suite version against an agent identity, capturing aggregate tool-selection/goal-completion/trajectory/human-input scores.	tenant-scoped, RLS: tenant_id=current_setting(...)
`agent_eval_suite`	10	Identity row for a named eval suite; may be tenant-owned or Vrida-global.	mixed tenant/global (`tenant_id` nullable), version-row (↔ `agent_eval_suite_version`), RLS: select own-or-NULL / write own-only (2-policy)
`agent_eval_suite_version`	9	Immutable, content-hashed version row holding the suite's actual `scenario_definition` payload.	global, version-row
`agent_event_log`	7	Append-only event-sourced log of `agent_task`/`agent_thread` state transitions.	tenant-scoped, append-only, RLS: tenant_id=current_setting(...)
`agent_incident`	14	Human-facing narrative of an agent-caused loss/incident — distinct from `rollback_execution`'s mechanical compensation record.	tenant-scoped, RLS: tenant_id=current_setting(...)
`agent_performance_profile`	14	Per-agent-identity, per-domain rolled-up performance stats used to recommend an autonomy mode.	tenant-scoped, cached counter (avg scores/`sample_size` aggregated from `agent_eval_run`), RLS: tenant_id=current_setting(...)
`agent_schedule`	20	Cron-like recurring task originator; stamps a `task_template` onto every `agent_task` it spawns.	tenant-scoped, full autonomy pack (`automation_source`/`review_status`/`decision_provenance`), RLS: tenant_id=current_setting(...)
`agent_shadow_decision`	9	One side-by-side comparison of what an agent would have recommended vs. what a human actually did, within a shadow run.	tenant-scoped, RLS: tenant_id=current_setting(...)
`agent_shadow_run`	10	A shadow-mode (non-live) evaluation session for an agent identity against a real workflow.	tenant-scoped, RLS: tenant_id=current_setting(...)
`agent_skill_assignment`	10	Tenant instantiation point: "this skill version is active for this agent identity, for this tenant." Relocated here from `identity` to avoid a foundation-layer dependency cycle.	tenant-scoped, skill-activation guard (`validate_skill_activation()` trigger folding certification + duty-grant authority + spend-ceiling checks), RLS: tenant_id=current_setting(...)
`agent_task`	34	The core unit-of-work row: goal, target module, risk tier, autonomy mode, atomic lease/fencing claim state, and running step/cost counters. Largest table in the module.	tenant-scoped, kill switch (`check_agent_task_not_blocked()` on INSERT OR UPDATE OF status), cached counter (`step_count`/`cost_cents_accrued`, atomically capped against `max_steps`/`max_cost_cents`), self-approval block (reviewer≠creator), saga gate (`allowed_mutating_module_id`), fencing (lease_owner/lease_expires_at/fencing_token, not bumped on renewal), RLS: tenant_id=current_setting(...)
`agent_thread`	9	Durable checkpoint/state-blob for one run of an `agent_task` (a task may retry across several threads).	tenant-scoped, RLS: tenant_id=current_setting(...)
`agent_tool_grant`	14	Per-agent-identity, per-tool-version grant with optional spend cap/rate limit; read by the execution-blocking guard.	tenant-scoped, RLS: tenant_id=current_setting(...)
`agent_trigger`	19	Event-based task originator matching `platform.outbox`-shaped events by naming convention only (no FK — a listener service, unbuilt, does the join).	tenant-scoped, full autonomy pack, RLS: tenant_id=current_setting(...)
`decision_context_feature`	6	Typed join: links a `decision_context_snapshot` to one `signals.feature_value` it drew on.	tenant-scoped, RLS: tenant_id=current_setting(...)
`decision_context_forecast`	6	Typed join: links a `decision_context_snapshot` to one `signals.forecast` it drew on.	tenant-scoped, RLS: tenant_id=current_setting(...)
`decision_context_knowledge_source`	5	Typed join: links a `decision_context_snapshot` to one `files.document_chunk` (RAG source) it drew on.	tenant-scoped, RLS: tenant_id=current_setting(...)
`decision_context_manifest`	14	Append-only, partitioned; the self-contained "evidence envelope" pointer (`manifest_hash`/`storage_reference`/`retention_policy_id`) for a snapshot's archived evidence bundle.	tenant-scoped, append-only, partitioned by created_at (RANGE), 14 partitions, RLS: tenant_id=current_setting(...)
`decision_context_metric`	5	Typed join: links a `decision_context_snapshot` to one `semantics.metric_version` it drew on.	tenant-scoped, RLS: tenant_id=current_setting(...)
`decision_context_policy`	5	Typed join: links a `decision_context_snapshot` to the `platform.ai_capacity_policy` in force.	tenant-scoped, RLS: tenant_id=current_setting(...)
`decision_context_snapshot`	16	Append-only, partitioned; the immutable evidence root every downstream join table points back to — frozen model/prompt/skill/toolset versions, resolved policy/goal profile.	tenant-scoped, append-only, partitioned by created_at (RANGE), 14 partitions, bitemporal (`business_effective_time` + `knowledge_cutoff_time`), version-row consumer, RLS: tenant_id=current_setting(...)
`evidence_retention_policy`	7	Tenant-defined retention/legal-hold rule that the manifest's bucket-lifecycle infra (deployment-layer, unbuilt) is meant to read.	tenant-scoped, RLS: tenant_id=current_setting(...)
`kill_switch_event`	8	Append-only history of kill/suspend/resume directives at global/tenant/agent_identity/skill/tool/model scope.	mixed tenant/global (`tenant_id` nullable), append-only, polymorphic ref (`scope_type`/`scope_ref`, no FK), RLS: select own-or-NULL / write own-only (2-policy, added post-launch to close a live cross-tenant read/forge gap)
`kill_switch_scope_state`	6	Exactly one row per scope holding the CURRENT resolved directive/fencing generation, maintained solely by a native upsert trigger off `kill_switch_event`.	global, cached counter (`current_directive`/`current_fencing_generation` derived from `kill_switch_event`), polymorphic ref (`scope_type`/`scope_ref`, no FK), write-locked (GRANT SELECT only — writes via trigger only)
`rollback_execution`	12	The mechanical compensating-transaction record for one `agent_action`, tracking status pending→completed/failed.	tenant-scoped, polymorphic ref (`compensating_source_*`, all-or-nothing CHECK, no FK), RLS: tenant_id=current_setting(...) — see discrepancy below re: "append-only" label
`rollback_recipe`	17	Catalog of which compensating action/service method applies to which `action_type`; may be tenant-override or Vrida-global.	mixed tenant/global (`tenant_id` nullable), full autonomy pack, RLS: select own-or-NULL / write own-only (2-policy)
`skill_certification`	13	Global, immutable record that a skill_version+toolset_version(+optional agent_identity/model_version) passed/failed an eval; read by activation/execution-time revalidation triggers.	global, RLS: none
`skill_definition`	8	Stable identity row for a skill; supersedes `identity.agent_skill`.	global, version-row (↔ `skill_version`)
`skill_execution_policy`	7	Tenant concurrency/cost policy for a skill version, bounded at activation time against the lowest reachable `agent_duty_grant.spend_limit_cents`.	tenant-scoped, RLS: tenant_id=current_setting(...)
`skill_version`	11	Immutable, content-hashed version row holding a skill's actual certifiable behavior.	global, version-row
`skill_version_module`	2	Join: which `platform.module_catalog` modules a skill version applies to (version-scoped, replaces v1's identity-scoped `skill_module`).	global
`skill_version_required_duty`	3	Join: which `identity.permission` + minimum authority level a skill version requires to activate.	global
`skill_version_toolset`	2	Join: which toolset version a skill version requires.	global
`tenant_agent_deployment`	11	A tenant "employing" a catalog agent, producing a deployed agent identity. Cost tracking rides `platform.ai_credit_transaction` — no parallel ledger.	tenant-scoped, RLS: tenant_id=current_setting(...)
`tool_definition`	7	Stable identity row for a tool.	global, version-row (↔ `tool_version`)
`tool_version`	17	Immutable, content-hashed version row holding a tool's certifiable payload (I/O JSON Schema, risk_tier, is_write, requires_approval).	global, version-row, guardrail (high-risk write requires approval CHECK)
`tool_version_module`	2	Join: which modules a tool version applies to.	global
`toolset_definition`	6	Stable identity row for a reusable, named bundle of tool versions.	global, version-row (↔ `toolset_version`)
`toolset_version`	10	Immutable, content-hashed version row for a toolset.	global, version-row
`toolset_version_member`	2	Join: which tool_version rows belong to a toolset_version.	global
`workload_class`	11	Global taxonomy of latency/timeout/retry/queue-priority profiles (interactive_copilot, batch_enrichment, …), referenced by `platform.ai_capacity_policy`.	global, seed-data catalog

Discrepancies found (agents)

rollback_execution is labeled "append-only" in both the Drizzle source comment and docs/database/schema_docs/agents.md's own section heading, but neither the live DB nor the table's own schema shape actually enforces or matches that. The live GRANTS row is agents.rollback_execution → authenticated → INSERT,DELETE,UPDATE,SELECT (full CRUD, no REVOKE), and the TRIGGERS section has zero reject_append_only_mutation-style trigger on this table — unlike the module's 6 genuinely append-only tables (agent_action, agent_decision, agent_event_log, decision_context_manifest, decision_context_snapshot, kill_switch_event), which all show either a narrowed GRANT (SELECT,INSERT only) or an explicit append-only trigger, or both. This matches the table's own design: status transitions pending → completed/failed and completed_at gets set later — an ordinary mutable status-tracking row, not an insert-once ledger. The "append-only" label appears to be a carried-over misnomer from the v1 baseline rather than a live discrepancy in the schema's actual behavior.
No other mismatches found — live DB (table/column counts, RLS policies, FK composite shapes, partition counts, trigger names) and the Drizzle source agree with each other and with docs/database/schema_docs/agents.md throughout (47 tables / 475 columns confirmed identical across all three sources; the doc's own disclosed gaps — e.g. agent_autonomy_profile.current_mode being advisory-only, missing updated_at triggers on the A2b identity-row catalog tables — are consistent with what the fact file and Drizzle comments show).

Last modified: Jul 16, 2026, 9:05 AM PT