platform — Phase 1

2026-06-28 (identity Batch A actor model): 4 of the 8 identity-deferred FK columns below are retargeted to identity.actor (not identity.identity_user) per the polymorphic actor model decision. The other 4 stay → identity.identity_user (human-semantics columns). FK constraints, Drizzle schema, and migration changes are deferred to identity-complete — see OPEN_ITEMS. This note is a doc-only update to the locked platform schema.

23 tables / 408 cols (16 original + contract + promo_code + operator_audit_log + ai_credit_account + ai_credit_transaction + announcement + platform_setting) per PROJECT_DECISIONS "Platform Module (Locked 2026-06-09)" plus the 2026-06-30 skeleton-table addendum. This is platform's authoritative schema definition — all tables and column-level definitions.

2026-06-30 (skeleton tables, admin.vrida.app wrap-up): announcement and platform_setting added, both skeletal — schema locked, no write endpoint yet (read-only service stubs listAnnouncements/getSettings exist). Ran the Section 4 audit against the existing platform conventions before locking; see PROJECT_DECISIONS for the audit summary and rationale. Both FKs to identity.actor are live/enforced immediately (no deferral needed — identity is already migrated).

2026-07-06 (autonomy-first backfill): +6 cols across 4 tables (23 tables unchanged; 397 → 403 cols) — tenant_entitlement gains automation_source; promo_code gains automation_source; contract gains review_status/review_reason/reviewed_by_actor_id/reviewed_at. Plus a same-pass rename (tenant_entitlement.granted_by_user_id → granted_by_actor_id, no col-count change) and two FK retargets (operator_audit_log.operator_user_id, tenant_internal_activity.performed_by_user_id, both now → identity.actor, no col-count change). See PROJECT_DECISIONS #19 for the full canonical pattern and rationale.

2026-07-07 (4th reopen — identity absorption from Admin ↔ Platform data-ownership boundary): Executes PROJECT_DECISIONS #34 (the governing rule: platform owns tenant identity, admin owns tenant technical/operational config). tenant_profile gains 5 cols (business_email, legal_address, mailing_address, business_classification_code, ein_ref) and has trading_name (text) retyped to dbas (jsonb array, NOT NULL DEFAULT '[]') — 23 tables unchanged; 403 → 408 cols. tax_id and logo_url on tenant_profile are deprecated in place (column comments only, no DDL change) — superseded by ein_ref (vault reference) and admin.tenant_branding.logo_ref (once Admin v2 exists) respectively. Migration: packages/db/migrations/20260707110000_platform_reopen_identity_absorption.sql (hand-written, applied live, verified). Full code cutover done and tested: PlatformService.updateTenantProfile()/provisionTenant(), UpdateTenantProfileDto, packages/types TenantProfile, and apps/web/admin/app/tenants/[id]/page.tsx — 10 new tests in platform-identity-absorption.spec.ts, full apps/api suite 539/539 passing (up from 529). See PROJECT_DECISIONS #35 for the full build record (#34 is the governing rule; #35 records this reopen's execution of it).

2026-07-08 (Remediation Plan Phase 1): CHECK/RLS/REVOKE/trigger-only — 23 tables, 408 cols unchanged. See "Remediation Phase 1 (2026-07-08)" below the Design Patterns Summary for the full detail; referenced cross-module in PROJECT_DECISIONS #37.

2026-07-08 (Remediation Plan Phase 2): PK-generation-strategy change only — 23 tables, 408 cols unchanged. id DEFAULT changed from gen_random_uuid() to platform.uuid_generate_v7() on 6 append-only event/audit/ledger tables (agreement_acceptance, ai_credit_transaction, operator_audit_log, tenant_internal_activity, tenant_lifecycle_event, tenant_usage_summary). platform.uuid_generate_v7() itself is also new — a shared cross-cutting function (not a table/column) referenced by column defaults across 9 other schemas. See "Remediation Phase 2 (2026-07-08)" below the Design Patterns Summary for the full detail; referenced cross-module in PROJECT_DECISIONS #38.

2026-07-08 (Remediation Plan Phase 4): +3 tables / +32 cols — 23 → 26 tables, 408 → 440 cols. New tables: accounting_period (Item 14 — fiscal periods, this codebase's first EXCLUDE USING gist), legal_entity (Item 15 — 1:N legal entities per tenant), outbox (Item 20a — transactional outbox, deliberately mutable). contract and billing_account each gained a nullable entity_id (+1 col each, part of Item 15's 10-table rollout). See "Remediation Phase 4 (2026-07-08)" below the Design Patterns Summary for the full detail; referenced cross-module in PROJECT_DECISIONS #40.

2026-07-10 (Header/Line Remediation — reopen, fix #1): +1 table / +10 cols — 26 → 27 tables, 440 → 450 cols. New table subscription_invoice_line (per-line decomposition of a subscription_invoice's total; LINES-ARE-TRUTH reconciliation via a new trigger, trg_subscription_invoice_line_sync_totals). subscription_invoice itself gains a prerequisite UNIQUE(id, tenant_id) (no new column) and its line_items JSONB column is deprecated in place (comment-only). This is the 3rd and final reopen (of 3: POS → Purchasing → Platform) in the coordinated "Header/Line Remediation" effort. See platform.subscription_invoice_line's own section below and "Header/Line Remediation Fix #1 (2026-07-10)" under the Design Patterns Summary for full detail; referenced cross-module in PROJECT_DECISIONS #48.

2026-07-10 (Header/Line Remediation batch 2 — closing bare-FK fix): Constraint-only, 27 tables / 450 cols unchanged. payment.invoice_id upgraded from a bare FK to a composite payment_invoice_id_tenant_fkey (invoice_id, tenant_id) → subscription_invoice(id, tenant_id) — the prerequisite UNIQUE(id, tenant_id) on subscription_invoice already existed from fix #1 above, so no new prerequisite was needed here. This is the LAST item in the entire 2-batch Header/Line Remediation effort (batch 1: POS/Purchasing/Platform, PROJECT_DECISIONS #46-48; batch 2: Inventory/Orders/Billing/Identity/this fix, PROJECT_DECISIONS #49-53). See platform.payment's own section below and PROJECT_DECISIONS #53.

2026-07-12 (6th reopen — agents-v2/v3 build, Phase 1 of 6): +7 tables / +64 cols — 27 → 34 tables, 450 → 514 cols. New tables: module_catalog (A1 — the global module registry, replacing the closed CHECK-enum module-tag pattern; lifecycle_status forward-only via trg_module_catalog_lifecycle_transition), tier_module_entitlement (which modules a tier includes by default, replacing the dead tier_definition.entitled_modules JSONB), tenant_module_activation (the real per-tenant module switch, replacing the functionally-dead is_toggleable mechanism — zero callers found), module_dependency (module prerequisite graph, real recursive cycle detection + a reverse-dependency-on-deactivation guard), ai_capacity_policy / tenant_ai_capacity_usage (A8/E4 — the 6-level AI-capacity precedence chain's atomic enforcement half, platform.try_increment_ai_capacity_spend()), tenant_regional_policy (E7 — one resolvable regional-placement policy per tenant). Zero existing tables altered. An independent lock-gate verification found and fixed 4 issues before Phase 2 was authorized to proceed: a live GRANT-additivity bug letting authenticated freely mutate 4 global reference tables including the platform-wide AI cost ceiling (now REVOKEd), a refund/correction bypass in the spend-increment function (negative deltas now always succeed, unconditionally), a vestigial priority tie-break column (now honored), and a module_catalog seed misclassification (multi_loc corrected to layer='business'). Migration: packages/db/migrations/20260712000000_platform_module_registry_capacity.sql. See PROJECT_DECISIONS #62 for the full build record (incl. the addendum + Section 4 self-audit).

Cross-Phase Foreign Keys

Nine platform columns reference tables that are built in later migration phases. To avoid forward-reference failures at migration time, these columns are created in the Phase 1 platform migration as plain UUID columns WITHOUT the FK constraint. The FK constraint is added in a later migration once the target schema exists.

#	Column	Target	FK added in
1	tenant.primary_site_id	multi_loc.site	Phase 4 (multi_loc migration)
2	tenant_contact.identity_user_id	identity.identity_user	Phase 3 (identity migration)
3	tenant_entitlement.granted_by_actor_id ²	identity.actor ¹	Phase 3 (identity migration)
4	agreement_acceptance.accepted_by_user_id	identity.identity_user	Phase 3 (identity migration)
5	tenant_setup_task.completed_by_user_id	identity.actor ¹	Phase 3 (identity migration)
6	tenant_data_lifecycle.requested_by_user_id	identity.actor ¹	Phase 3 (identity migration)
7	tenant_lifecycle_event.actor_user_id	identity.actor ¹	Phase 3 (identity migration)
8	tenant_internal_activity.performed_by_user_id	identity.actor ¹	Phase 3 (identity migration)
9	operator_audit_log.operator_user_id	identity.actor ¹	Phase 3 (identity migration)

¹ Rows 3, 5, 6, 7 retargeted to identity.actor (2026-06-28, identity Batch A). Rows 8 and 9 (tenant_internal_activity.performed_by_user_id, operator_audit_log.operator_user_id) retargeted to identity.actor separately (2026-07-06, autonomy-first backfill — see PROJECT_DECISIONS #19); this was a zero-risk constraint change (no col-count impact, no backfill needed) since identity_user.id is itself a FK to actor.id (shared-PK pattern). Any actor type (user, service account, or agent) may perform these operations. The remaining 2 (tenant_contact.identity_user_id, agreement_acceptance.accepted_by_user_id) stay → identity.identity_user because they are human-semantics records (named human contact, legal acceptance). ² Renamed from granted_by_user_id (2026-07-06, autonomy-first backfill — see PROJECT_DECISIONS #19); name-only change, already correctly FK'd to identity.actor since Batch A.

Each of these nine columns is marked (FK constraint deferred — see Cross-Phase FKs) in its column-definition row below.

platform.tenant

Root record for every tenant in Vrida — establishes the tenant's identity, subscription tier, default site, module preferences, and lifecycle state. One row per tenant; no tenant_id (this IS the root). Tenants never soft-delete; they transition through status states (trial → active → past_due / suspended / cancelled → pending_deletion → deleted).

NOT tenant-scoped — this IS the root. No tenant_id, no deleted_at. Tenants go through lifecycle (status column), never soft-deleted.

RLS: not applied — this is the root record.

Access control: the tenant table has no RLS. The app_user role may read only a single tenant row by id or slug resolved from the authenticated request's tenant context (via identity.tenant_user). Cross-tenant scans of this table require service_role and occur only in trusted server-side code (provisioning, admin, reporting).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
name	text	NOT NULL	—	Display name
slug	text	NOT NULL	—	UNIQUE — URL-safe identifier
status	text	NOT NULL	'trial'	CHECK (status IN ('trial','active','past_due','suspended','cancelled','pending_deletion','deleted')). Lifecycle: trial → active → past_due / suspended → cancelled → pending_deletion (retention window expired, data wipe scheduled but not yet executed) → deleted (operational data wiped; tombstone record retained for audit). Dunning grace is NOT a tenant status — it is tracked at the subscription layer via subscription.dunning_status.
tier	text	NOT NULL	'pro'	CHECK (tier IN ('starter','pro','enterprise'))
timezone	text	NOT NULL	'America/New_York'	IANA timezone
primary_site_id	UUID	nullable	—	FK → multi_loc.site (FK constraint deferred — see Cross-Phase FKs). Nullable at creation, set at provisioning step 2.
feature_flags	JSONB	NOT NULL	'{}'	System-controlled boolean toggles, key = flag name. Example: {"beta_ai_chat": true, "new_dashboard": false}
module_prefs	JSONB	NOT NULL	'{}'	Tenant on/off per module, key = module name. Example: {"inventory": true, "production": false, "purchasing": true}
activated_at	timestamptz	nullable	—
suspended_at	timestamptz	nullable	—
cancelled_at	timestamptz	nullable	—
retention_expires_at	timestamptz	nullable	—	90-day post-cancel
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on slug
• on (status) — billing/lifecycle jobs scanning tenants by lifecycle state

platform.tenant_profile

Extended business profile for a tenant — legal name, firmographics, scale indicators, service offerings, ecommerce flag, and acquisition attribution (UTM / referral / partner). 1:1 with tenant; populated during onboarding and kept current by the business profile setup task.

Tenant-scoped, 1:1 with tenant.

RLS: enabled — tenant isolation policy on tenant_id.

2026-07-07 (4th reopen — identity absorption, PROJECT_DECISIONS #34/#35): platform now owns tenant identity data absorbed from admin.tenant_business_profile (full 17-column fate mapping recorded in PROJECT_DECISIONS #34 — not re-derived here). 5 new columns added (business_email, legal_address, mailing_address, business_classification_code, ein_ref); trading_name retyped to dbas; tax_id/logo_url deprecated in place. Migration: packages/db/migrations/20260707110000_platform_reopen_identity_absorption.sql.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant (uniqueness enforced as partial unique index — see Indexes)
legal_name	text	nullable	—
dbas	JSONB	NOT NULL	'[]'	Retyped from trading_name (text) 2026-07-07, 4th reopen — see PROJECT_DECISIONS #35. Bare string array of doing-business-as names. Example: ["Acme Garden Co","Rose Garden Nursery"]. Confirmed live via fresh query immediately before the migration: 12 total rows, 0 non-null trading_name at retype time — zero-risk retype.
brand_name	text	nullable	—
website_url	text	nullable	—
phone	text	nullable	—
support_email	text	nullable	—
business_email	text	nullable	—	Added 2026-07-07, 4th reopen (identity absorption) — see PROJECT_DECISIONS #35.
tax_id	text	nullable	—	EIN. DEPRECATED (2026-07-07, 4th reopen) — superseded by ein_ref (vault reference). Column retained as-is (still present/readable/writable); no DDL change, comment-only via COMMENT ON COLUMN. See PROJECT_DECISIONS #35.
ein_ref	text	nullable	—	Added 2026-07-07, 4th reopen (identity absorption) — see PROJECT_DECISIONS #35. Vault-encryption reference superseding tax_id. Schema-only: no vault-encryption service exists yet anywhere in the codebase (confirmed via grep) — see OPEN_ITEMS.
logo_url	text	nullable	—	Basic branding. DEPRECATED (2026-07-07, 4th reopen) — superseded by admin.tenant_branding.logo_ref (once Admin v2 exists). Column retained as-is (still present/readable/writable); no DDL change, comment-only via COMMENT ON COLUMN. See PROJECT_DECISIONS #35.
legal_address	JSONB	nullable	—	Added 2026-07-07, 4th reopen (identity absorption) — see PROJECT_DECISIONS #35. {street,city,state,zip,country}
mailing_address	JSONB	nullable	—	Added 2026-07-07, 4th reopen (identity absorption) — see PROJECT_DECISIONS #35. {street,city,state,zip,country}. NULL-fallback behavior documented-not-enforced — see OPEN_ITEMS.
business_classification_code	text	nullable	—	Added 2026-07-07, 4th reopen (identity absorption) — see PROJECT_DECISIONS #35.
country_code	char(2)	nullable	—	ISO 3166-1 alpha-2; future i18n
language_code	char(5)	nullable	—	e.g. en-US; future localization
billing_address	JSONB	nullable	—	{street,city,state,zip,country}
primary_store_address	JSONB	nullable	—
state_region	text	nullable	—	Normalized US state/region
revenue_band	text	nullable	—	CHECK (revenue_band IN ('<100k','100-400k','400k-2M','2-5M','5-10M','10M+'))
employee_count_band	text	nullable	—	CHECK (employee_count_band IN ('<5','5-10','10-25','25-50','50+'))
locations_count	integer	nullable	—
business_type	text	nullable	—	Tenant-defined business type (no CHECK constraint — open text)
services_offered	JSONB	nullable	'[]'	Array of tenant-defined service codes. Example: ["service_a","service_b"]
has_ecommerce	boolean	nullable	false
ecommerce_platform	text	nullable	—	CHECK (ecommerce_platform IN ('none','shopify','woo','wix','other'))
online_sales_share_band	text	nullable	—	CHECK IN ('<5%','5-15%','15-30%','30%+')
approx_sku_count_band	text	nullable	—	CHECK IN ('<1k','1-3k','3-7k','7k+')
current_system_before_vrida	text	nullable	—	Prior system, free text (no CHECK constraint — open text)
onboarding_step	text	nullable	'created'	CHECK IN ('created','email_verified','business_profile_done','payment_added','activated')
onboarding_source	text	nullable	'self_serve'	CHECK IN ('self_serve','sales_led','partner_referral')
utm_source	text	nullable	—
utm_campaign	text	nullable	—
referral_code	text	nullable	—
partner_id	text	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• UNIQUE on (tenant_id) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)

platform.tenant_contact

Named business contacts (owner, billing, legal, admin, technical, internal CS owner). Also holds current-state CS fields (NPS, churn_risk) and per-contact marketing consent columns. Not all contacts are app users.

Tenant-scoped, many per tenant.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
contact_type	text	NOT NULL	—	CHECK IN ('owner','billing','legal','admin','technical','cs_owner')
name	text	NOT NULL	—
email	text	nullable	—
phone	text	nullable	—
title	text	nullable	—	Job title at the tenant/business
is_primary	boolean	NOT NULL	false
identity_user_id	UUID	nullable	—	FK → identity.identity_user (FK constraint deferred — see Cross-Phase FKs). Not all contacts are app users.
nps_score	integer	nullable	—	0–10. Only for cs_owner type.
nps_last_response_at	timestamptz	nullable	—
churn_risk	text	nullable	—	CHECK IN ('low','medium','high')
receive_product_emails	boolean	NOT NULL	false
receive_feature_updates	boolean	NOT NULL	false
receive_webinar_invites	boolean	NOT NULL	false
marketing_consent_updated_at	timestamptz	nullable	—
marketing_consent_source	text	nullable	—	CHECK IN ('app','email_link','support')
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id, contact_type)

platform.billing_account

Vrida's billing relationship with the tenant — holds the Stripe Customer ID, billing address, tax-exempt status, tax ID, default payment method, invoice delivery preference, and payment terms. Kept separate from the subscription plan row so billing details can update without touching the plan.

Tenant-scoped, 1:1 with tenant.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant (uniqueness enforced as partial unique index — see Indexes)
billing_name	text	nullable	—
billing_email	text	nullable	—
billing_address	JSONB	nullable	—	{street,city,state,zip,country}
tax_exempt_status	text	NOT NULL	'taxable'	CHECK IN ('taxable','exempt','pending')
tax_id	text	nullable	—
stripe_customer_id	text	nullable	—	Stripe Customer object ID
stripe_default_payment_method_id	text	nullable	—	Stripe PM ID
payment_method_brand	text	nullable	—	visa / mastercard / amex
payment_method_last4	text	nullable	—
payment_method_exp_month	integer	nullable	—	1–12
payment_method_exp_year	integer	nullable	—	2026, 2027, …
invoice_delivery_method	text	NOT NULL	'email'	CHECK IN ('email','mail','portal_only')
payment_terms_days	integer	NOT NULL	0	0 = immediate; 30/60/90 for Enterprise
currency_code	char(3)	NOT NULL	'USD'
last_payment_failure_at	timestamptz	nullable	—
last_payment_failure_reason	text	nullable	—
entity_id	UUID	nullable	—	FK → platform.legal_entity. Added 2026-07-08, Remediation Phase 4 (Item 15) — see PROJECT_DECISIONS #40. NULL = the tenant's primary legal entity (today's implicit, unchanged behavior). Header-only placement — see legal_entity's own section for the disclosed scoping rule.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• UNIQUE on (tenant_id) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)
• on stripe_customer_id

platform.subscription

Tracks the tenant's active Vrida SaaS plan — tier, billing cycle, trial and current-period dates, Stripe Subscription ID, seasonal pause flag, source contract for sales-led deals, and dunning state (inline). At most one active subscription per tenant (partial unique enforced). Stripe Customer ID lives in billing_account.

Tenant-scoped, 1 active per tenant (enforced by partial unique index).

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
tier_code	text	NOT NULL	—	starter / pro / enterprise. References tier_definition.tier_code.
billing_cycle	text	NOT NULL	'monthly'	CHECK IN ('monthly','annual')
status	text	NOT NULL	'trial'	CHECK IN ('trial','active','past_due','cancelled','paused','ended','incomplete','incomplete_expired','unpaid'). Notes: 'ended' = subscription fully terminated (paired with ended_at). 'incomplete' / 'incomplete_expired' / 'unpaid' = Stripe webhook lifecycle states ingested via PaymentsService.
trial_start_at	timestamptz	nullable	—
trial_end_at	timestamptz	nullable	—	30 days from start
current_period_start	timestamptz	nullable	—
current_period_end	timestamptz	nullable	—
stripe_subscription_id	text	nullable	—
is_seasonal_pause_allowed	boolean	NOT NULL	false
paused_at	timestamptz	nullable	—
resume_at	timestamptz	nullable	—
cancel_at_period_end	boolean	NOT NULL	false	Customer cancelled but access continues until period end
cancelled_at	timestamptz	nullable	—	When cancellation was requested
cancellation_reason	text	nullable	—
ended_at	timestamptz	nullable	—	When subscription actually ended
source_contract_id	UUID	nullable	—	FK → platform.contract. For sales-led Enterprise deals.
promo_code_id	UUID	nullable	—	FK → platform.promo_code. Applied promo code, if any.
dunning_status	text	nullable	—	CHECK IN ('retrying','grace','suspended','recovered'). Null = not in dunning.
dunning_stage	integer	nullable	—	Retry attempt number
dunning_started_at	timestamptz	nullable	—
next_retry_at	timestamptz	nullable	—
last_retry_at	timestamptz	nullable	—
grace_period_ends_at	timestamptz	nullable	—	Access cutoff if unrecovered
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• UNIQUE on (tenant_id) WHERE status NOT IN ('cancelled','ended') AND deleted_at IS NULL — partial unique, enforces one active non-deleted subscription
• on (trial_end_at) WHERE status = 'trial' — trial-end-approaching sweep
• on stripe_subscription_id
• on (next_retry_at) WHERE dunning_status = 'retrying' — dunning retry sweep

platform.subscription_invoice

One Vrida billing-cycle invoice issued to the tenant/business — tracks amount, payment status, line-item breakdown, and the Stripe invoice reference. Platform-managed; tenants view but cannot edit these rows.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

2026-07-10 (Header/Line Remediation reopen, fix #1 — PROJECT_DECISIONS #48): gained a prerequisite UNIQUE(id, tenant_id) (subscription_invoice_id_tenant_id_unique, no new column), needed by the new subscription_invoice_line's composite FK — see that table's own section below. line_items is now deprecated in place (comment-only, no DDL change) — superseded by subscription_invoice_line, which is now the reconciliation source of truth for subtotal_cents/discount_cents/tax_cents/total_cents via a sync trigger on the line table.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
subscription_id	UUID	NOT NULL	—	FK → platform.subscription
invoice_number	text	NOT NULL	—	Uniqueness enforced as partial unique index — see Indexes
period_start	timestamptz	NOT NULL	—
period_end	timestamptz	NOT NULL	—
subtotal_cents	bigint	NOT NULL	—	Before tax / discount
discount_cents	bigint	NOT NULL	0
tax_cents	bigint	NOT NULL	0
total_cents	bigint	NOT NULL	—	subtotal - discount + tax
credits_cents	bigint	NOT NULL	0	Account credits applied to this invoice
amount_due_cents	bigint	NOT NULL	—	total - credits
amount_paid_cents	bigint	NOT NULL	0
amount_remaining_cents	bigint	NOT NULL	—	amount_due - amount_paid
currency_code	char(3)	NOT NULL	'USD'
status	text	NOT NULL	'draft'	CHECK IN ('draft','open','paid','void','failed','written_off')
due_date	date	NOT NULL	—
issued_at	timestamptz	nullable	—
paid_at	timestamptz	nullable	—
line_items	JSONB	NOT NULL	'[]'	Array of line item objects with description, amount_cents, type (plan / addon / credit / adjustment). Example: [{"description":"Pro plan monthly","amount_cents":9999,"type":"plan"},{"description":"Extra users x5","amount_cents":2500,"type":"addon"}]. DEPRECATED (2026-07-10, Header/Line Remediation fix #1) — superseded by subscription_invoice_line. Column retained as-is (still present/readable/writable); no DDL change, comment-only via COMMENT ON COLUMN. Confirmed live at migration time that zero code path anywhere in this repo ever populated this column — all 5 pre-existing rows held only the '[]' default despite each having a non-zero subtotal_cents (9900/4900/9900/5000/5000), a genuine pre-existing data-quality gap, disclosed via the migration's own RAISE NOTICE reconciliation log rather than silently fixed. See PROJECT_DECISIONS #48.
stripe_invoice_id	text	nullable	—
hosted_invoice_url	text	nullable	—	Stripe hosted invoice page
invoice_pdf_url	text	nullable	—	Stripe PDF download
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• UNIQUE on (invoice_number) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)
• on (tenant_id, status)
• on (status, due_date) — system-wide overdue-invoice sweep
• UNIQUE on (id, tenant_id) — subscription_invoice_id_tenant_id_unique. Added 2026-07-10, Header/Line Remediation fix #1 — prerequisite for subscription_invoice_line's composite FK below. Confirmed missing before this migration, not hypothetical (design doc §7 finding V1). See PROJECT_DECISIONS #48.

platform.subscription_invoice_line

Per-line decomposition of a subscription_invoice's total — e.g. base subscription, AI-credit overage, additional seats/sites, proration, one-time addons, discounts, tax. New 2026-07-10, Header/Line Remediation fix #1 (3rd and final reopen of 3 in this effort: POS → Purchasing → Platform) — see PROJECT_DECISIONS #48. Write-once (Decision B case 1, matching tax.tax_calculation_jurisdiction's precedent): no status, no updated_at/deleted_at.

Tenant-scoped, many per invoice.

RLS: enabled — tenant isolation policy on tenant_id.

LINES ARE TRUTH — the opposite reconciliation pattern from purchasing.vendor_credit_line (header-is-truth). trg_subscription_invoice_line_sync_totals (function platform.sync_subscription_invoice_totals_from_lines()), AFTER INSERT OR UPDATE OR DELETE on this table, unconditionally recomputes on the parent subscription_invoice: subtotal_cents = SUM(amount_cents) WHERE line_type NOT IN ('tax','discount'), discount_cents = -SUM(amount_cents) WHERE line_type='discount' (stored as a positive magnitude on the header even though the line's own amount_cents is negative), tax_cents = SUM(amount_cents) WHERE line_type='tax', total_cents = subtotal_cents - discount_cents + tax_cents. The header's amount_due_cents/amount_paid_cents bookkeeping is untouched (payment tracking stays where it is).

Corrected 8-step migration sequencing — a genuine bug caught by independent verification (design doc §7 finding V3-1), the single most serious finding of the whole 3-module Header/Line Remediation effort. The original draft's order was create table + install the sync trigger + backfill + verify. Since the trigger is AFTER INSERT/UPDATE/DELETE and unconditionally overwrites the header's totals from SUM(lines), it would have fired during the backfill itself — meaning the "verify against the existing stored subtotal_cents" step would have compared against totals the trigger had already overwritten, a tautological check that always "reconciles" even where the original data genuinely diverged. Corrected order actually used: (1) inspect the real line_items shape first — confirmed zero code path anywhere in the repo ever populated it; (2) add the prerequisite UNIQUE(id, tenant_id); (3) create the table EMPTY, no trigger yet; (4) snapshot each invoice's pre-backfill subtotal_cents/discount_cents/tax_cents/total_cents into a temp table; (5) backfill via jsonb_array_elements, guarded by a jsonb_typeof(...) = 'array' check first (a stray non-array value throws a hard error otherwise); (6) verify the backfill against the snapshot (not the live, possibly-already-overwritten columns), logging any mismatch via RAISE NOTICE rather than silently fixing it; (7) only then install the sync trigger; (8) deprecate line_items in place via COMMENT ON COLUMN (not dropped).

Disclosed reconciliation-mismatch finding. All 5 pre-existing live subscription_invoice rows held line_items = '[]' (the column default) despite each having a non-zero subtotal_cents (9900/4900/9900/5000/5000) — the backfill correctly produced zero new lines for all 5, and step 6's verification correctly logged all 5 as mismatches against the pre-backfill snapshot (via RAISE NOTICE, not an error). A genuine pre-existing gap between the header and a JSONB blob nothing ever actually wrote to — disclosed, not silently fixed. See PROJECT_DECISIONS #48.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	platform.uuid_generate_v7()	PK. Not gen_random_uuid() — Decision B case 1's write-once-decomposition-line convention (matches tax.tax_calculation_jurisdiction, billing.ar_payment_application), per Remediation Phase 2's append-only-ledger convention.
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
subscription_invoice_id	UUID	NOT NULL	—	No bare FK — the real FK is the composite (subscription_invoice_id, tenant_id) constraint below, subscription_invoice_line_invoice_tenant_fkey
line_type	text	NOT NULL	—	CHECK IN ('base_subscription','ai_credit_overage','additional_seats','additional_sites','proration','one_time_addon','discount','tax')
description	text	NOT NULL	—
quantity	numeric	nullable	—	Both-null-or-both-set with unit_amount_cents — see CHECK below
unit_amount_cents	bigint	nullable	—	Both-null-or-both-set with quantity — see CHECK below
amount_cents	bigint	NOT NULL	—	Negative for line_type='discount'
currency_code	char(3)	NOT NULL	'USD'	FK → shared.currency
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on tenant_id
• on subscription_invoice_id

Foreign keys / cross-column CHECK constraints:

• FOREIGN KEY (subscription_invoice_id, tenant_id) REFERENCES subscription_invoice (id, tenant_id) — subscription_invoice_line_invoice_tenant_fkey (composite, cross-tenant-safe)
• CHECK (line_type IN (...)) — chk_subscription_invoice_line_line_type
• CHECK ((quantity IS NULL AND unit_amount_cents IS NULL) OR (quantity IS NOT NULL AND unit_amount_cents IS NOT NULL AND amount_cents = quantity * unit_amount_cents)) — chk_subscription_invoice_line_amount_matches_qty. NULL-safe: explicit both-null-OR-both-populated-and-consistent, no bare nullable boolean term.

platform.payment

Records each payment the tenant/business makes to Vrida against a subscription invoice — amount, status (pending / succeeded / failed / refunded), and Stripe payment reference. Distinct from billing.ar_payment (merchant-side A/R); this is Vrida's own revenue collection.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
invoice_id	UUID	NOT NULL	—	No bare FK — the real FK is the composite (invoice_id, tenant_id) constraint below, payment_invoice_id_tenant_fkey (upgraded from a bare FK 2026-07-10, Header/Line Remediation batch 2)
amount_cents	bigint	NOT NULL	—
currency_code	char(3)	NOT NULL	'USD'
status	text	NOT NULL	'pending'	CHECK IN ('pending','succeeded','failed','refunded')
payment_method_type	text	nullable	—	card / ach / check / manual
provider	text	NOT NULL	'stripe'	CHECK IN ('stripe','manual','check','ach')
provider_payment_id	text	nullable	—	Stripe PaymentIntent ID
failure_code	text	nullable	—	Structured: card_declined, insufficient_funds, expired_card
failed_reason	text	nullable	—	Human-readable detail
provider_fee_cents	bigint	nullable	—	Stripe processing fee
net_amount_cents	bigint	nullable	—	amount - provider_fee
received_at	timestamptz	nullable	—
refunded_at	timestamptz	nullable	—
refund_amount_cents	bigint	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id, invoice_id)
• on provider_payment_id

Header/Line Remediation batch 2 bare-FK fix (2026-07-10): invoice_id was a bare (non-composite) FK to subscription_invoice(id) since the original migration — payment carries its own tenant_id, so a bare FK let a row point at a same-shaped subscription_invoice row belonging to a DIFFERENT tenant (RLS hides it on SELECT, but doesn't block the write). This gap was found and disclosed during fix #1's own independent verification (PROJECT_DECISIONS #48) and tracked in OPEN_ITEMS until this pass. Now upgraded to composite payment_invoice_id_tenant_fkey (invoice_id, tenant_id) → subscription_invoice(id, tenant_id); the old bare constraint (payment_invoice_id_subscription_invoice_id_fk) was DROPPED, not left dangling alongside the new one. The prerequisite UNIQUE(id, tenant_id) on subscription_invoice already existed (added by fix #1, subscription_invoice_id_tenant_id_unique) — no new prerequisite needed. Pre-migration audit: 3 live rows, 0 cross-tenant mismatches, 0 orphans — safe direct ADD, no NOT VALID fallback. Migration: packages/db/migrations/20260710070000_headerline_bare_fk_fixes.sql. No column/table-count impact — constraint-shape change only. See PROJECT_DECISIONS #53.

platform.tier_definition

Reference data defining each subscription tier — Starter / Pro / Enterprise base price, included locations, per-additional-location fee, feature caps, entitled modules, and permitted add-ons. Base tier price covers included_locations sites; each site beyond that is billed at price_per_additional_location_cents per billing cycle, tracked per-tenant via tenant_entitlement and counted via tenant_usage_summary.sites_count. Read by entitlement resolution at feature-gate check time; not tenant-scoped.

NOT tenant-scoped — reference data. No tenant_id, no deleted_at, no RLS. Shared reference.

RLS: not applied — reference data.

Access control: reference data, no RLS. Readable by any authenticated app user — needed to render tier pricing, feature caps, and upgrade UI. Writable only by service_role (Vrida platform operators via admin tooling). No tenant writes.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tier_code	text	NOT NULL	—	UNIQUE — starter / pro / enterprise
name	text	NOT NULL	—	Display name
description	text	nullable	—	Short description for UI / marketing
display_order	integer	NOT NULL	—
price_monthly_cents	bigint	NOT NULL	—	4999 / 9999 / 19999
price_annual_cents	bigint	NOT NULL	—	49900 / 99900 / 299900
trial_days	integer	NOT NULL	30
is_public	boolean	NOT NULL	true	Visible to new signups
is_active	boolean	NOT NULL	true	Can new tenants select
max_users	integer	NOT NULL	—	-1 = unlimited
max_sites	integer	NOT NULL	—
max_zones	integer	NOT NULL	—
max_skus	integer	NOT NULL	—
entitled_modules	JSONB	NOT NULL	—	{"inventory":true,"production":false}
permitted_addons	JSONB	NOT NULL	'[]'	Array of addon codes. Example: ["extra_users","extra_sites","ai_pack","white_label","priority_support"]
included_locations	integer	NOT NULL	1	Locations included in base subscription price
price_per_additional_location_cents	bigint	nullable	—	Fee per location beyond included_locations per billing cycle, in cents (uses tier's currency). Example: 2500 = $25/location
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on tier_code

platform.promo_code

Vrida-issued subscription promo/discount codes. Platform-managed reference data — not tenant-scoped (a code is issued by Vrida, redeemable across signups).

NOT tenant-scoped — reference data. No tenant_id, no RLS. Shared reference.

RLS: not applied — reference data.

Access control: reference data, no RLS. Readable by any authenticated app user — needed to validate a promo code at signup and checkout (check active, not expired, within redemption limit). Writable only by service_role (Vrida platform operators creating or deactivating codes). No tenant writes.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
code	text	NOT NULL	—	The redeemable code. Uniqueness enforced as partial unique index — see Indexes.
description	text	nullable	—
discount_type	text	NOT NULL	—	CHECK IN ('percent','fixed')
discount_value	integer	NOT NULL	—	Percent (1–100) or amount in cents, per discount_type. CHECK enforces 1–100 range when discount_type='percent' — see Cross-column CHECKs below.
currency_code	char(3)	nullable	—	For fixed discounts. Required (NOT NULL) when discount_type='fixed' — enforced by cross-column CHECK below.
applies_to	text	NOT NULL	'subscription'	CHECK IN ('subscription','addon','any')
duration	text	NOT NULL	—	CHECK IN ('once','repeating','forever')
duration_months	integer	nullable	—	For 'repeating' duration. Required (NOT NULL) when duration='repeating' — enforced by cross-column CHECK below.
max_redemptions	integer	nullable	—	Null = unlimited
redemption_count	integer	NOT NULL	0
valid_from	timestamptz	nullable	—
valid_until	timestamptz	nullable	—
is_active	boolean	NOT NULL	true
stripe_coupon_id	text	nullable	—
automation_source	text	NOT NULL	'human'	CHECK IN ('human','agent','system','seed'). Added 2026-07-06, autonomy-first backfill — see PROJECT_DECISIONS #19.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• UNIQUE on (code) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)
• on (is_active, valid_until) — active-code validation scan

Cross-column CHECK constraints:

• CHECK (discount_type <> 'fixed' OR currency_code IS NOT NULL) — currency is required when discount is a fixed amount
• CHECK (duration <> 'repeating' OR duration_months IS NOT NULL) — duration_months is required for repeating discounts
• CHECK (discount_type <> 'percent' OR (discount_value BETWEEN 1 AND 100)) — percent must be in valid range

platform.tenant_entitlement

Source of truth for feature access. Absorbs subscription_addon: add-ons are entitlements with source_type=addon, plus price / quantity. Other source types: tier, override, beta, contract. Answers "why does this tenant have access to X?"

Tenant-scoped, many per tenant.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
entitlement_code	text	NOT NULL	—	production_module / extra_users / ai_pack / white_label / advanced_reporting / custom_roles / etc.
source_type	text	NOT NULL	—	CHECK IN ('tier','addon','override','beta','contract')
source_id	text	nullable	—	tier_code, contract_id, etc.
quantity	integer	nullable	—	For add-ons: 5 extra users. Null for boolean entitlements.
limit_value	integer	nullable	—	Cap: 10000 AI calls. Null for boolean entitlements.
used_value	integer	nullable	—	Current usage against limit. Null if no limit.
reset_period	text	nullable	—	CHECK IN ('monthly','annual','never'). When used_value resets. Null if no limit.
renews_at	timestamptz	nullable	—	Next reset date. Null if no limit.
price_cents	bigint	nullable	—	For add-ons
currency_code	char(3)	nullable	—
billing_cycle	text	nullable	—	CHECK IN ('monthly','annual'). For add-ons only.
status	text	NOT NULL	'active'	CHECK IN ('active','expired','revoked')
is_enabled	boolean	NOT NULL	true
starts_at	timestamptz	NOT NULL	—
ends_at	timestamptz	nullable	—	Null = no expiry
granted_by_actor_id	UUID	nullable	—	FK → identity.actor (FK constraint deferred — see Cross-Phase FKs). Renamed from granted_by_user_id (2026-07-06, autonomy-first backfill — see PROJECT_DECISIONS #19); it was already correctly FK'd to identity.actor (per Batch A actor model 2026-06-28), so this is a name-only change, no col-count impact.
reason	text	nullable	—
metadata	JSONB	nullable	—	{"granted_for":"beta_program","internal_ref":"CS-1234"}. Doubles as this table's decision-provenance carrier (2026-07-06, autonomy-first backfill — see PROJECT_DECISIONS #19): when automation_source <> 'human', the deciding factors are recorded here rather than in a new dedicated column.
automation_source	text	NOT NULL	'human'	CHECK IN ('human','agent','system','seed'). Added 2026-07-06, autonomy-first backfill — see PROJECT_DECISIONS #19.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id, entitlement_code)
• on (tenant_id, status)

platform.ai_credit_account

AI usage credit wallet for a tenant. A row exists ONLY if the tenant uses AI (manual-only tenants have no row). Holds the prepaid balance (Vrida-granted + purchased credits), the optional spend limit, and the credit-use flag. Drawn down by AI consumption; topped up by purchases. Enforcement and consumption writes are performed by the AI module via PlatformService.

Tenant-scoped. RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant. One account per tenant — see Indexes.
balance_cents	bigint	NOT NULL	0	Current available credit (granted + purchased − consumed)
granted_credit_cents	bigint	NOT NULL	0	Total Vrida-granted credit (e.g. $50 signup bonus)
purchased_credit_cents	bigint	NOT NULL	0	Total tenant-purchased credit, lifetime
lifetime_spent_cents	bigint	NOT NULL	0	Total credit consumed by AI usage, lifetime
currency_code	char(3)	NOT NULL	'USD'
use_credits	boolean	NOT NULL	true	When true, draw down credits before billing overage
spend_limit_cents	bigint	nullable	—	Max overage spend ceiling. Null = no cap (unlimited billed usage). When set, AI usage halts at this ceiling.
status	text	NOT NULL	'active'	CHECK IN ('active','suspended','closed')
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• UNIQUE on (tenant_id) WHERE deleted_at IS NULL — one credit account per tenant (soft-delete pattern)

platform.ai_credit_transaction

Append-only ledger of every AI credit movement — grants, purchases, per-call consumption, refunds, adjustments. One row per movement. Consumption rows are written per AI call (for billing-dispute precision) by the AI module via PlatformService. The running balance is reflected in balance_after_cents.

Tenant-scoped, immutable. No updated_at, no deleted_at — append-only.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
account_id	UUID	NOT NULL	—	FK → platform.ai_credit_account
type	text	NOT NULL	—	CHECK IN ('grant','purchase','consumption','refund','adjustment'). amount_cents is positive for grant/purchase/refund, negative for consumption.
amount_cents	bigint	NOT NULL	—	Signed movement amount — positive (grant/purchase/refund) or negative (consumption)
balance_after_cents	bigint	NOT NULL	—	Account balance after this movement
currency_code	char(3)	NOT NULL	'USD'
ai_call_ref	text	nullable	—	Text correlation ref to the AI call/job that consumed credit (for dispute lookup). Not an FK — the AI module uses this for cross-system lookup.
payment_ref	text	nullable	—	Vendor-neutral reference for a purchase. Vrida-billing vendor not yet decided — do NOT assume Stripe.
description	text	nullable	—	Human-readable detail
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id, created_at)
• on (account_id, created_at)
• on (type, created_at)

platform.contract

Enterprise sales-led contracts — the negotiated agreement behind a sales-led subscription. Referenced by subscription.source_contract_id and tenant_entitlement (source_type='contract').

Tenant-scoped, many per tenant.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
contract_number	text	NOT NULL	—	Uniqueness enforced as partial unique index — see Indexes
status	text	NOT NULL	'draft'	CHECK IN ('draft','sent','signed','active','expired','terminated')
tier_code	text	NOT NULL	—	Contracted tier
billing_cycle	text	nullable	—	CHECK IN ('monthly','annual','custom')
contract_value_cents	bigint	nullable	—
currency_code	char(3)	NOT NULL	'USD'
start_date	date	nullable	—
end_date	date	nullable	—
signed_at	timestamptz	nullable	—
auto_renew	boolean	NOT NULL	false
payment_terms_days	integer	NOT NULL	0
document_ref	text	nullable	—	R2 ref to signed contract (Files seam)
notes	text	nullable	—
review_status	text	NOT NULL	'not_required'	CHECK IN ('not_required','pending','approved','rejected'). Human-in-the-loop review seam, modeled on identity.access_request's shape. Added 2026-07-06, autonomy-first backfill — see PROJECT_DECISIONS #19.
review_reason	text	nullable	—	Added 2026-07-06, autonomy-first backfill — see PROJECT_DECISIONS #19.
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor. Added 2026-07-06, autonomy-first backfill — see PROJECT_DECISIONS #19.
reviewed_at	timestamptz	nullable	—	Added 2026-07-06, autonomy-first backfill — see PROJECT_DECISIONS #19.
entity_id	UUID	nullable	—	FK → platform.legal_entity. Added 2026-07-08, Remediation Phase 4 (Item 15) — see PROJECT_DECISIONS #40. NULL = the tenant's primary legal entity (today's implicit, unchanged behavior). Header-only placement — see legal_entity's own section for the disclosed scoping rule.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• UNIQUE on (contract_number) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)
• on (tenant_id, status)

platform.agreement_version

Version registry for Vrida's published legal documents (TOS, MSA, DPA) — one row per published version, with document type, content hash, effective date, and the R2 document URL. Platform-managed reference data; not tenant-scoped.

NOT tenant-scoped — reference data. No tenant_id, no deleted_at, no RLS. Shared reference.

RLS: not applied — reference data.

Access control: reference data, no RLS. Readable by any authenticated app user — needed to display the current legal version at signup, login, and agreement-acceptance prompts. Writable only by service_role (Vrida legal/platform operators publishing new document versions). No tenant writes.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
agreement_type	text	NOT NULL	—	CHECK IN ('tos','msa','privacy_policy','dpa')
version	text	NOT NULL	—	e.g. "2026-06-01"
effective_date	date	NOT NULL	—
document_url	text	NOT NULL	—	R2 or public URL
content_hash	text	NOT NULL	—	SHA-256
is_active	boolean	NOT NULL	true
requires_acceptance	boolean	NOT NULL	true	Some docs are informational only
supersedes_agreement_version_id	UUID	nullable	—	FK → self. Which prior version this replaces.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (agreement_type, version)
• on (agreement_type, is_active) — current-active-version lookup per document type

platform.agreement_acceptance

Immutable record of a tenant user accepting a specific agreement version — timestamp, IP address, user-agent string, and HelloSign envelope ref for legally-binding documents. FKs to agreement_version and Identity's user table. Append-only; a new row is written each time a user accepts an updated version.

Tenant-scoped, immutable. No updated_at, no deleted_at. Once accepted, never changes.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
agreement_version_id	UUID	NOT NULL	—	FK → platform.agreement_version
accepted_by_user_id	UUID	NOT NULL	—	FK → identity.identity_user (FK constraint deferred — see Cross-Phase FKs)
accepted_at	timestamptz	NOT NULL	—
acceptance_method	text	NOT NULL	—	CHECK IN ('clickwrap','wet_sign','hellosign','docusign')
ip_address	inet	nullable	—
user_agent	text	nullable	—
signature_ref	text	nullable	—	HelloSign envelope ID
evidence_json	JSONB	nullable	—	{"checkbox_text":"I agree to..."}
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id, agreement_version_id)

platform.tenant_usage_summary

Monthly usage snapshot per tenant — active user count, site count, SKU count, transaction count, and AI call count for a billing period. Used for billing cap enforcement and upgrade prompts. Append-only, never overwritten.

Tenant-scoped, append-only. No updated_at (append-only, one row per period). No deleted_at.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
period_start	date	NOT NULL	—
period_end	date	NOT NULL	—
active_users_count	integer	NOT NULL	0
sites_count	integer	NOT NULL	0
skus_count	integer	NOT NULL	0
pos_sales_count	integer	NOT NULL	0
orders_count	integer	NOT NULL	0
ai_calls_count	integer	NOT NULL	0
storage_bytes	bigint	NOT NULL	0
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (tenant_id, period_start)

platform.tenant_setup_task

Combines technical provisioning (schema created, storage initialized, Stripe setup) and business onboarding (email verified, business profile, inventory imported via AI, POS configured, first sale, go-live). Each row: task_category, task_code, status, is_required, is_customer_visible, display_order, dependencies, failure tracking, metadata.

Tenant-scoped, many per tenant. No deleted_at — setup tasks are permanent records.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
task_category	text	NOT NULL	—	CHECK IN ('provisioning','onboarding')
task_code	text	NOT NULL	—	CHECK IN ('schema_created', 'default_site_created', 'storage_initialized', 'identity_initialized', 'stripe_setup', 'seed_data_loaded', 'email_verified', 'business_profile_completed', 'payment_method_added', 'legal_agreements_accepted', 'first_user_invited', 'site_configured', 'inventory_imported', 'customers_imported', 'vendors_imported', 'pos_configured', 'branding_configured', 'first_sale_completed', 'go_live')
status	text	NOT NULL	'pending'	CHECK IN ('pending','in_progress','completed','failed','skipped','blocked')
is_required	boolean	NOT NULL	true
is_customer_visible	boolean	NOT NULL	false
display_order	integer	NOT NULL	0
depends_on	text	nullable	—	task_code that must complete first
started_at	timestamptz	nullable	—
completed_at	timestamptz	nullable	—
completed_by_user_id	UUID	nullable	—	FK → identity.actor (FK constraint deferred — see Cross-Phase FKs; retargeted from identity.identity_user per Batch A actor model 2026-06-28)
failed_at	timestamptz	nullable	—
failure_reason	text	nullable	—
blocked_reason	text	nullable	—	Why task is blocked
retry_count	integer	NOT NULL	0
next_retry_at	timestamptz	nullable	—	Scheduled retry
metadata	JSONB	nullable	—	{"imported_sku_count":243}
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (tenant_id, task_code)

platform.tenant_data_lifecycle

Governs post-cancellation data handling for a tenant — retention period, deletion workflow state (requested → pending_approval → scheduled → in_progress → completed, with blocked and failed as off-ramps), legal-hold flag, and export request tracking. One row per tenant; written by the data-lifecycle job and Vrida ops.

Tenant-scoped. No deleted_at — compliance / audit records.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
lifecycle_type	text	NOT NULL	—	CHECK IN ('retention','export','deletion','legal_hold')
status	text	NOT NULL	'scheduled'	CHECK IN ('requested','pending_approval','scheduled','in_progress','blocked','completed','failed'). Workflow order: requested → pending_approval (awaiting operator approval before deletion is queued) → scheduled → in_progress → completed. Off-ramps: blocked (legal hold; paired with legal_hold_flag=true) and failed (execution error; job retries or ops intervenes).
requested_by_user_id	UUID	nullable	—	FK → identity.actor (FK constraint deferred — see Cross-Phase FKs; retargeted from identity.identity_user per Batch A actor model 2026-06-28)
started_at	timestamptz	nullable	—
completed_at	timestamptz	nullable	—
expires_at	timestamptz	nullable	—
deletion_scheduled_at	timestamptz	nullable	—	When deletion will run
export_type	text	nullable	—	full / inventory / customers / transactions
download_url	text	nullable	—	R2 signed URL
download_expires_at	timestamptz	nullable	—
export_file_hash	text	nullable	—	SHA-256 of export for integrity
file_size_bytes	bigint	nullable	—
legal_hold_flag	boolean	NOT NULL	false
legal_hold_reason	text	nullable	—
failure_reason	text	nullable	—
metadata	JSONB	nullable	—	{"export_format":"json","legal_hold_ref":"LH-2026-01"}
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id, lifecycle_type)

platform.tenant_lifecycle_event

Every status transition: what changed, why (reason code), who triggered it, when. Single source of truth for churn / suspension reasons.

Tenant-scoped, immutable. No updated_at, no deleted_at. Append-only.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
event_type	text	NOT NULL	—	CHECK IN ('created','activated','suspended','resumed','cancelled','paused','unpaused','retention_expired','deleted','past_due','recovered'). 'past_due' added in migration 20260629030000 (billing-driven active→past_due). 'recovered' added same migration (dunning recovery: past_due→active).
from_status	text	nullable	—	Previous tenant.status value
to_status	text	nullable	—	New tenant.status value
reason_code	text	nullable	—	CHECK IN ('non_payment','requested_by_customer','abuse','internal_testing','seasonal_pause','trial_expired','other')
actor_type	text	NOT NULL	'system'	CHECK IN ('system','admin','customer','webhook')
actor_user_id	UUID	nullable	—	FK → identity.actor (FK constraint deferred — see Cross-Phase FKs; retargeted from identity.identity_user per Batch A actor model 2026-06-28)
correlation_id	text	nullable	—	Stripe webhook event ID, job ID
notes	text	nullable	—
metadata	JSONB	nullable	—	{"previous_tier":"starter","triggered_by":"billing_job"}
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id, created_at)

platform.tenant_internal_activity

Vrida's append-only internal activity log for CS and admin operations — support notes, tier changes, trial extensions, feature grants, credits, NPS updates, and churn-risk flags. activity_type discriminates record kinds; before / after JSONB captures state for admin changes. Never visible to tenants.

Tenant-scoped, immutable. No updated_at, no deleted_at. Append-only.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
activity_type	text	NOT NULL	—	CHECK IN ('note','admin_action','nps_update','churn_update','credit_applied','trial_extended','tier_changed','feature_granted','feature_revoked')
performed_by_user_id	UUID	NOT NULL	—	FK → identity.actor (FK constraint deferred — see Cross-Phase FKs; retargeted from identity.identity_user 2026-07-06, autonomy-first backfill — see PROJECT_DECISIONS #19)
subject	text	nullable	—	Short summary
notes	text	nullable	—
before_value	JSONB	nullable	—	{"tier":"starter"}
after_value	JSONB	nullable	—	{"tier":"pro"}
reason_code	text	nullable	—
related_entity_type	text	nullable	—	subscription_invoice, tenant_entitlement, subscription, etc.
related_entity_id	UUID	nullable	—	FK to the related record
correlation_id	text	nullable	—	Ties to source event
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id, created_at)

platform.operator_audit_log

Append-only compliance log of every privileged operator action in the admin console (admin.vrida.app). Distinct from tenant_internal_activity (which is per-tenant CS notes and actions): this is an immutable, cross-tenant record of all operator actions with before/after state and request context, for security and compliance audit.

Non-tenant-scoped. tenant_id is nullable — some operator actions are global (e.g., publishing an agreement). No tenant_id-based RLS.

RLS: not applied — Vrida-internal compliance log. service_role write; admin read.

Append-only — no updated_at, no deleted_at. Immutable once written.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
operator_user_id	UUID	NOT NULL	—	FK → identity.actor (FK constraint deferred — see Cross-Phase FKs; retargeted from identity.identity_user 2026-07-06, autonomy-first backfill — see PROJECT_DECISIONS #19)
operator_role	text	nullable	—	Operator's role at time of action (snapshot, not a live FK)
action_type	text	NOT NULL	—	CHECK IN ('tenant_status_change','credit_applied','entitlement_override','impersonation_start','impersonation_end','bulk_action','data_export','contract_edit','promo_edit','agreement_publish','other')
target_type	text	NOT NULL	—	Entity acted on: 'tenant','subscription','entitlement','contract','promo_code','agreement', etc.
target_id	UUID	nullable	—	The target record's id
tenant_id	UUID	nullable	—	FK → platform.tenant. Affected tenant; null for global actions (e.g. publishing an agreement).
before_state	JSONB	nullable	—	State snapshot before action. Example: {"status":"active"}
after_state	JSONB	nullable	—	State snapshot after action. Example: {"status":"suspended"}
reason	text	nullable	—	Operator-supplied reason
source_ip	inet	nullable	—	Request IP address
user_agent	text	nullable	—	Request user agent
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id, created_at) — tenant-scoped audit history
• on (operator_user_id, created_at) — actions by a specific operator
• on (action_type, created_at) — compliance sweeps by action type

---

platform.announcement

Vrida-authored broadcast messages shown in the tenant-facing app and/or the admin console. Skeleton table (2026-06-30) — schema locked, no write endpoint yet; PlatformService.listAnnouncements() is the only service method. Mixed-scope: tenant_id nullable — NULL means "all tenants" (global broadcast), a set value means "targeted to one tenant." Unlike operator_audit_log (nullable tenant_id, no RLS), announcement DOES carry RLS — see below.

Mixed-scope RLS (first platform table to use this pattern — mirrors identity.role DR-3): a row is visible if tenant_id IS NULL (global) OR tenant_id matches the caller's tenant. The NULL branch is load-bearing — a plain tenant-isolation policy would hide every global announcement from every tenant (bug class #5 in SCHEMA_DESIGN_RUNBOOK).

Soft-delete tenant-scoped default applies despite mixed scope — deleted_at present.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	nullable	—	FK → platform.tenant. NULL = all tenants.
title	text	NOT NULL	—
body	text	NOT NULL	—
severity	text	NOT NULL	'info'	CHECK IN ('info','maintenance','critical')
audience	text	NOT NULL	'tenants'	CHECK IN ('tenants','public','both')
starts_at	timestamptz	NOT NULL	now()
ends_at	timestamptz	nullable	—	NULL = no scheduled end
status	text	NOT NULL	'draft'	CHECK IN ('draft','published','expired')
created_by_actor_id	UUID	NOT NULL	—	FK → identity.actor — any actor type (human operator, service account, or agent) may create an announcement
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on tenant_id — required on every tenant-scoped (or mixed-scope) table per SCHEMA_CONVENTIONS
• on status — "list published"/"list draft" query pattern
• (deferred, low-priority GAP — add when the active-announcements sweep query is built): composite on (status, starts_at, ends_at) for WHERE status='published' AND starts_at <= now() AND (ends_at IS NULL OR ends_at > now())

---

platform.platform_setting

Vrida-wide key-value configuration store. Skeleton table (2026-06-30) — schema locked, no write endpoint yet; PlatformService.getSettings() is the only service method. Deliberately generic: a new setting is a new row, not a new column — grows without a migration.

NOT tenant-scoped — reference/global table. RLS: not applied. Access control mirrors tier_definition/agreement_version: operator-only reads/writes via adminDb (service_role); no tenant-facing read path exists.

No deleted_at — deliberate deviation from the tenant-scoped-table default (this table isn't tenant-scoped in the first place). A stale key is overwritten or left in place; there's no soft-delete concept for a bare key-value row, mirroring how tier_definition/agreement_version deactivate rather than soft-delete.

value has no fixed JSONB shape — caller-defined per key (e.g. {"currency":"USD"}, {"enabled":true}, or a bare JSON scalar). This is intentional for a generic key-value store; callers must know their own key's expected shape.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
key	text	NOT NULL	—	UNIQUE — the setting's identity
value	JSONB	nullable	—	Caller-defined shape per key — see note above
updated_by_actor_id	UUID	NOT NULL	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on key — plain unique (not partial) since there's no deleted_at to guard against

---

platform.accounting_period

Fiscal-period tracker — lets a tenant mark a date range 'closed' for accounting purposes. New 2026-07-08, Remediation Phase 4 (Item 14) — see PROJECT_DECISIONS #40. This codebase's first use of Postgres EXCLUDE USING gist (requires the btree_gist extension, enabled by this migration): excl_accounting_period_no_overlap prevents two periods for the same tenant from overlapping, while leaving different tenants' identical date ranges unaffected. Consumed by a shared FLAG-NOT-REJECT trigger function, platform.flag_closed_period_business_date() (see below), attached to pos.sale, pos.sale_refund, and pos.register_cash_entry.

Tenant-scoped, many per tenant.

RLS: enabled — tenant isolation policy on tenant_id.

platform.flag_closed_period_business_date() — a shared trigger function defined here but consumed by 3 triggers in pos (not platform's own tables). Deliberately FLAG-NOT-REJECT: it never raises, only sets review_status='pending' (plus a default review_reason) on the inserting/updating row when that row's business_date falls inside a closed period for the same tenant. This is intentional, not a gap — POS is offline-first, so a genuine June-30 sale can sync in July after June's period has already closed; rejecting it would silently lose the sale, while flagging it preserves both signals (the period is closed, and the sale is real). Consuming triggers: trg_sale_flag_closed_period / trg_sale_refund_flag_closed_period (BEFORE INSERT OR UPDATE OF business_date) and trg_register_cash_entry_flag_closed_period (BEFORE INSERT only — pos.register_cash_entry already has an unconditional append-only trigger from Remediation Phase 1 that rejects every UPDATE, so an OR UPDATE OF business_date clause there would be dead code). pos.register_cash_entry had no review-seam columns at all before this migration; the full 5-column seam (review_status/review_reason/reviewed_by_actor_id/reviewed_at/decision_provenance) was added there specifically so this trigger would have something to set.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
period_start	date	NOT NULL	—
period_end	date	NOT NULL	—	CHECK (period_end >= period_start)
status	text	NOT NULL	'open'	CHECK IN ('open','closed')
closed_at	timestamptz	nullable	—	Cross-column CHECK: set iff status='closed' — see below
closed_by_actor_id	UUID	nullable	—	FK → identity.actor
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on tenant_id
• on (status) WHERE status = 'closed' — closed-period lookup for the flag trigger

Cross-column / exclusion constraints:

• CHECK ((status = 'open' AND closed_at IS NULL) OR (status = 'closed' AND closed_at IS NOT NULL)) — closed_at consistency
• EXCLUDE USING gist (tenant_id WITH =, daterange(period_start, period_end, '[]') WITH &&) — excl_accounting_period_no_overlap; no two periods for the same tenant may overlap (inclusive range on both ends). Different tenants may hold identical or overlapping date ranges — the exclusion is scoped per-tenant, not global.

---

platform.legal_entity

1:N legal-entity registry per tenant — lets a tenant incorporate a 2nd (or 3rd) LLC without splitting into two separate Vrida tenants. New 2026-07-08, Remediation Phase 4 (Item 15) — see PROJECT_DECISIONS #40. Backfilled with exactly 1 row per pre-existing platform.tenant at build time (1997 rows — the plan's original draft figure of "176 rows" was found stale and corrected live before the backfill ran; tenant_profile.legal_name was checked as an alternative name source and rejected, 0-for-12 non-null at the time), name sourced from tenant.name, is_primary=true.

Tenant-scoped, many per tenant.

RLS: enabled — tenant isolation policy on tenant_id.

Exactly one primary per tenant, enforced. legal_entity_tenant_id_primary_unique (partial unique index WHERE is_primary = true) allows exactly one is_primary=true row per tenant while permitting unlimited is_primary=false rows. This is a point-in-time guarantee established by the backfill, not an ongoing DB-enforced invariant for new tenants — no trigger or service-layer hook creates a legal_entity row when a tenant is created going forward, so tenants created after this migration ran start with zero rows here (harmless today; nothing yet reads this table assuming 1:1 coverage). See OPEN_ITEMS.

Header-only entity_id rollout — a disclosed scoping rule. Unlike tenant_id, which this codebase applies uniformly to every table including line items, the nullable entity_id FK added by this same migration was placed on 10 HEADER tables only, never on their line-item children or on purchasing.vendor itself: platform.contract, platform.billing_account, admin.compliance_document, tax.tax_calculation, billing.ar_account, billing.vendor_payable, purchasing.vendor_invoice, purchasing.purchase_order, orders.order_header, pos.sale. A line item's entity is always inherited via its header's FK and never needs its own column — deliberate, not an oversight.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
name	text	NOT NULL	—	CHECK (length(trim(name)) > 0) — blank/whitespace-only name rejected
ein_ref	text	nullable	—	Vault-encryption reference, same pattern as tenant_profile.ein_ref
is_primary	boolean	NOT NULL	false	Exactly one true per tenant — see partial unique index below
is_active	boolean	NOT NULL	true
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on tenant_id
• UNIQUE on (tenant_id) WHERE is_primary = true — legal_entity_tenant_id_primary_unique, exactly one primary entity per tenant

Cross-column CHECK constraints:

• CHECK (length(trim(name)) > 0) — chk_legal_entity_name_not_blank

---

platform.outbox

Durable transactional-outbox event table. New 2026-07-08, Remediation Phase 4 (Item 20a) — see PROJECT_DECISIONS #40. No consumer/dispatcher service exists yet — schema-only, same as every other Phase 4 addition.

Tenant-scoped, many per tenant.

RLS: enabled — tenant isolation policy on tenant_id.

Deliberately mutable — the one genuine exception among this phase's new tables. Uses gen_random_uuid() for its PK, NOT platform.uuid_generate_v7() — a deliberate departure from Remediation Phase 2's own established UUIDv7-for-append-only-ledgers rule, because this table is genuinely mutable: status, attempts, last_attempted_at, and delivered_at are all expected to change after insert as a dispatcher processes and retries the event (live-confirmed: a real UPDATE ... SET status='delivered' succeeds against this table, unlike this codebase's actual append-only ledgers). No append-only trigger, no REVOKE UPDATE/DELETE — this table does not follow the append-only pattern used elsewhere in platform (contrast with ai_credit_transaction, operator_audit_log, etc., listed under "Immutable tables" below).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK — deliberately NOT platform.uuid_generate_v7(); see note above
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
aggregate_type	text	NOT NULL	—	The domain aggregate this event concerns, e.g. 'order', 'sale' (caller-defined; no CHECK)
aggregate_id	UUID	NOT NULL	—	The aggregate's own id (not an FK — polymorphic across every module's aggregates)
event_type	text	NOT NULL	—	Caller-defined event name, e.g. 'order.created' (no CHECK)
payload	JSONB	NOT NULL	—	The event body
status	text	NOT NULL	'pending'	CHECK IN ('pending','processing','delivered','failed')
attempts	integer	NOT NULL	0	CHECK (attempts >= 0)
last_attempted_at	timestamptz	nullable	—
delivered_at	timestamptz	nullable	—	Cross-column CHECK: set iff status='delivered' — see below
error	text	nullable	—	Last delivery-attempt error, if any
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on tenant_id
• on (status) WHERE status IN ('pending','processing') — dispatcher poll query
• on (aggregate_type, aggregate_id)

Cross-column CHECK constraints:

• CHECK (attempts >= 0) — chk_outbox_attempts_nonneg
• CHECK ((status = 'delivered') = (delivered_at IS NOT NULL)) — chk_outbox_delivered_consistency

---

platform.module_catalog

The global module registry — identity + lifecycle only. New 2026-07-12, agents-v2/v3 build Phase 1 (platform's 6th reopen) — see PROJECT_DECISIONS #62. Replaces the closed CHECK-enum module-tag pattern every polymorphic module column in this codebase has used until now (e.g. approvals.approval_request.source_module, ai.agent_execution's own module tag) — those columns are unaffected by this table (no retrofit in this phase), but any new module-tag need should reference this table going forward. Entitlement/activation/dependency deliberately live on the 3 companion tables below, not here — a prior single-table design was rejected for conflating "what a module is" with "who gets it."

NOT tenant-scoped — reference data. No tenant_id, no deleted_at, no RLS. Shared reference, same treatment as tier_definition.

RLS: not applied — reference data.

Access control: reference data, no RLS. Readable by any authenticated app user. Writable only by service_role — authenticated has SELECT only (explicit REVOKE INSERT, UPDATE, DELETE, added post-independent-verification after a live exploit was found — see PROJECT_DECISIONS #62 addendum finding 1).

lifecycle_status is forward-only, DB-enforced. trg_module_catalog_lifecycle_transition (BEFORE UPDATE OF lifecycle_status) rejects any transition that doesn't advance exactly one rank (planned → designed → in_build → active), except a jump to deprecated or retired, which is legal from any state. Live-reproduced: designed → in_build → active succeeds; active → designed (illegal backward transition) is rejected.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
module_code	text	NOT NULL	—	UNIQUE. The module's short code, e.g. 'crm', 'pos', 'agents'
display_name	text	NOT NULL	—	e.g. "CRM", "Point of Sale"
description	text	NOT NULL	—
layer	text	NOT NULL	—	CHECK IN ('foundation','business','consumer'), per SCHEMA_CONVENTIONS.md §1
lifecycle_status	text	NOT NULL	'planned'	CHECK IN ('planned','designed','in_build','active','deprecated','retired'). Forward-only transition enforced by trg_module_catalog_lifecycle_transition — see above
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on module_code

Cross-column CHECK constraints:

• CHECK (layer IN ('foundation','business','consumer')) — chk_module_catalog_layer
• CHECK (lifecycle_status IN ('planned','designed','in_build','active','deprecated','retired')) — chk_module_catalog_lifecycle_status

Triggers:

• set_updated_at — BEFORE UPDATE
• trg_module_catalog_lifecycle_transition — BEFORE UPDATE OF lifecycle_status — rejects an illegal (non-forward-adjacent) transition; deprecated/retired reachable from any state

Seed data: 25 rows — the 22 already-locked modules as of this build (platform/identity at lifecycle_status='in_build', matching their real service-layer status; the other 20 at 'designed') plus agents/semantics/signals (this build authorization's own 3 target modules) at 'designed'. layer classification per SCHEMA_CONVENTIONS.md §1, as amended by this build for tax/approvals/receiving/returns (never in that doc's static list — a pre-existing, disclosed drift, not introduced by this migration). The seeded multi_loc row was corrected to layer='business' post-independent-verification (originally miscoded 'foundation', contradicting that same doc — PROJECT_DECISIONS #62 addendum finding 4).

---

platform.tier_module_entitlement

Which modules a subscription tier includes by default. New 2026-07-12, agents-v2/v3 build Phase 1 — see PROJECT_DECISIONS #62. Replaces tier_definition.entitled_modules (a JSONB blob whose documented shape never matched its own seed data — disclosed in the design, not silently fixed here; entitled_modules is left in place on tier_definition, deprecated in favor of this table going forward).

NOT tenant-scoped — reference data. No tenant_id, no deleted_at, no RLS. Shared reference, same treatment as tier_definition/module_catalog.

RLS: not applied — reference data.

Access control: reference data, no RLS. Readable by any authenticated app user. Writable only by service_role — authenticated has SELECT only (explicit REVOKE INSERT, UPDATE, DELETE, same fix as module_catalog above — see PROJECT_DECISIONS #62 addendum finding 1).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tier_id	UUID	NOT NULL	—	FK → platform.tier_definition
module_id	UUID	NOT NULL	—	FK → platform.module_catalog
is_included	boolean	NOT NULL	—	Whether tier_id includes module_id by default
default_limit_value	bigint	nullable	—	Optional per-module usage limit at this tier. NULL = unbounded, this codebase's own established convention (e.g. identity.agent_duty_grant.spend_limit_cents)
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (tier_id, module_id)

Triggers:

• set_updated_at — BEFORE UPDATE

---

platform.tenant_module_activation

The REAL per-tenant module switch — closes a functionally-dead mechanism (is_toggleable's hasEntitlement() was found to have zero callers anywhere in the codebase during this build's own research pass). New 2026-07-12, agents-v2/v3 build Phase 1 — see PROJECT_DECISIONS #62. One row per (tenant, module); status distinguishes tier-granted-active from tenant-opted-out, beta access, and admin suspension.

Tenant-scoped, many per tenant.

RLS: enabled — tenant isolation policy on tenant_id.

Reverse-dependency-on-deactivation guard. trg_tenant_module_activation_check_dependents (BEFORE UPDATE OF status) rejects a transition away from status='active' if another module, still active for the same tenant, hard-depends on module_id via module_dependency — reject, not auto-cascade. Live-reproduced: with crm (hard-depends on inventory) and inventory both active for a real tenant, deactivating inventory is correctly rejected (an active module still hard-depends on it); deactivating a module with no active dependents succeeds.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
module_id	UUID	NOT NULL	—	FK → platform.module_catalog
status	text	NOT NULL	—	CHECK IN ('active','inactive','beta','suspended')
activated_at	timestamptz	nullable	—
deactivated_at	timestamptz	nullable	—
activated_by_actor_id	UUID	nullable	—	FK → identity.actor
source	text	NOT NULL	—	CHECK IN ('tier_default','addon_purchase','beta_opt_in','contract_override','admin_override')
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (tenant_id, module_id)
• on tenant_id

Cross-column CHECK constraints:

• CHECK (status IN ('active','inactive','beta','suspended')) — chk_tenant_module_activation_status
• CHECK (source IN ('tier_default','addon_purchase','beta_opt_in','contract_override','admin_override')) — chk_tenant_module_activation_source

Triggers:

• set_updated_at — BEFORE UPDATE
• trg_tenant_module_activation_check_dependents — BEFORE UPDATE OF status — see reverse-dependency-on-deactivation guard above

---

platform.module_dependency

module_id (the dependent) requires depends_on_module_id (the prerequisite) to be active before it may itself be activated for a tenant. New 2026-07-12, agents-v2/v3 build Phase 1 — see PROJECT_DECISIONS #62. Real recursive cycle detection, not just the direct self-reference the CHECK below already covers.

NOT tenant-scoped — reference data (global dependency graph). No tenant_id, no deleted_at, no RLS. Shared reference, same treatment as module_catalog.

RLS: not applied — reference data.

Access control: reference data, no RLS. Readable by any authenticated app user. Writable only by service_role — authenticated has SELECT only (explicit REVOKE INSERT, UPDATE, DELETE, same fix as module_catalog above — see PROJECT_DECISIONS #62 addendum finding 1).

Recursive cycle detection (I7). trg_module_dependency_no_cycle (BEFORE INSERT OR UPDATE) walks a WITH RECURSIVE reachability query from the proposed depends_on_module_id and rejects the edge if it would ever reach back to module_id — not just the direct A → A case chk_module_dependency_no_self_reference already covers. Live-reproduced: a 3-hop chain (crm → inventory → pos, all 'hard') builds successfully; the cycle-closing edge (pos → crm) is correctly rejected (would create a dependency cycle).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
module_id	UUID	NOT NULL	—	FK → platform.module_catalog. The dependent module
depends_on_module_id	UUID	NOT NULL	—	FK → platform.module_catalog. The prerequisite module
dependency_type	text	NOT NULL	—	CHECK IN ('hard','soft')
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (module_id, depends_on_module_id)
• on depends_on_module_id — module_dependency_depends_on_module_id_idx, added at Section 4 audit (item L): supports the reverse lookup (WHERE depends_on_module_id = ...) used by tenant_module_activation's own deactivation guard — the unique index above leads with module_id, not this column

Cross-column CHECK constraints:

• CHECK (dependency_type IN ('hard','soft')) — chk_module_dependency_type
• CHECK (module_id != depends_on_module_id) — chk_module_dependency_no_self_reference

Triggers:

• trg_module_dependency_no_cycle — BEFORE INSERT OR UPDATE — see recursive cycle detection above

---

platform.polymorphic_target_registry

The real validator lookup for platform.validate_polymorphic_reference() — genuinely-unavoidable polymorphic references (e.g. ai.agent_memory_source.source_ref, spanning 4 target tables that cannot be typed as a single FK) resolve their target schema/table through this table rather than a hardcoded CASE in application code. target_schema/target_table are split columns, not a single free-text 'schema.table' string — the agents-v2/v3 design's own independent review found and fixed a live SQL-injection vector in the single-string form before this table was ever built; validate_polymorphic_reference() uses format('... %I.%I ...', v_schema, v_table) on the two separate columns. New 2026-07-16, agents-v2/v3 build Phase 5 (I6) — see PROJECT_DECISIONS #66.

NOT tenant-scoped — reference data (global lookup). No tenant_id, no deleted_at, no RLS. Shared reference, same treatment as module_catalog/module_dependency above. target_type is the natural key and sole PK — no surrogate id column, matching this table's role as a small closed lookup, not a growing ledger.

RLS: not applied — reference data.

Access control: reference data, writable only by service_role. Phase 1 remediation (2026-07-18, Item 1) fix: the creating migration wrote a narrow GRANT SELECT ... TO authenticated, but by the time that GRANT ran, authenticated had already received INSERT/UPDATE/DELETE for free — platform's own schema-level ALTER DEFAULT PRIVILEGES rule (set 2026-07-08, Remediation Phase 1 RLS wiring) auto-grants SELECT, INSERT, UPDATE, DELETE to authenticated on every future table in the schema at CREATE TABLE time, regardless of what the creating migration's own GRANT line says. Unlike module_catalog/module_dependency (which got an explicit REVOKE INSERT, UPDATE, DELETE at build time, see above), this table did not, and sat with live write access to a security-critical validator table for 2 days undetected. Root-caused and fixed via an explicit REVOKE INSERT, UPDATE, DELETE ON platform.polymorphic_target_registry FROM authenticated, live-reproduced (pre-fix: SET ROLE authenticated INSERT/UPDATE/DELETE all succeeded; post-fix: all 3 rejected 42501, SELECT unaffected). Also missing a Drizzle model until this same remediation pass (the table was hand-written directly in a migration and never modeled) — now polymorphicTargetRegistry in packages/db/src/schema/platform/module_registry.ts.

Column	Type	Nullable	Default	Constraints / Notes
target_type	text	NOT NULL	—	PK. The polymorphic discriminator value (e.g. 'agent_execution')
target_schema	text	NOT NULL	—	The real Postgres schema name the discriminator resolves to
target_table	text	NOT NULL	—	The real Postgres table name the discriminator resolves to
tenant_column	text	NOT NULL	'tenant_id'	The tenant-scoping column name on the target table, for the tenant-match check inside validate_polymorphic_reference()

Indexes:

• PK on target_type

Functions:

• platform.validate_polymorphic_reference(p_target_type text, p_target_id uuid, p_tenant_id uuid) RETURNS boolean — looks up the row by target_type, then EXECUTE format('SELECT EXISTS (SELECT 1 FROM %I.%I WHERE id = $1 AND %I = $2)', v_schema, v_table, v_tenant_col). Raises if target_type is unregistered.

Seed rows (4, all from the agents-v2/v3 build): agent_execution → ai.agent_execution, decision_context_snapshot → agents.decision_context_snapshot, document_chunk → files.document_chunk, outcome_observation → signals.outcome_observation.

---

platform.processor_catalog

Replaces the single-value/narrow CHECK-enum "which vendor" columns on payments.payment_intent.processor and tax.tax_calculation.provider with a real FK-backed reference table — adding a new processor is now an INSERT here, not a schema migration. New 2026-07-18, Phase 1 remediation, Item 5.

Deliberately not a reuse of admin.integration_provider_catalog (which already happened to carry a 'stripe' row, category='payments') — that table represents tenant-configurable third-party integrations (a merchant opts into QuickBooks/Mailchimp/Stripe via admin.integration_config). Which vendor Vrida's own payment/tax infrastructure uses is a platform-level infrastructure fact, uniform across every tenant today, not a per-tenant toggle — conflating the two would incorrectly imply a tenant could "configure" their own payment processor via the integrations UI.

NOT tenant-scoped — reference data (global catalog). No tenant_id, no RLS. Reference/config data, migration-maintained only — authenticated gets SELECT only, matching module_catalog/tier_definition's own established convention.

RLS: not applied — reference data.

Column	Type	Nullable	Default	Notes
code	text	NOT NULL	—	PK. The vendor discriminator value (e.g. 'stripe')
kind	text	NOT NULL	—	CHECK IN ('payment_processor','tax_provider')
display_name	text	NOT NULL	—
is_active	boolean	NOT NULL	true
created_at/updated_at	timestamptz	NOT NULL	now()	updated_at trigger-maintained via platform.set_updated_at()

Seed rows (4): stripe (payment_processor), stripe_tax/manual/exempt (tax_provider, matching tax.tax_calculation.provider's own former 3-value CHECK exactly).

Referenced by: payments.payment_intent.processor (retargeted from chk_payment_intent_processor, a single-value CHECK (processor = 'stripe')), tax.tax_calculation.provider (retargeted from chk_tax_calculation_provider, a 3-value CHECK). Both plain bare FKs — processor_catalog is global reference data with no tenant_id, so no composite FK is possible or needed. Pre-migration audit: payment_intent.processor had 0 live rows; tax_calculation.provider had 2 distinct live values (manual, stripe_tax), both within the seed set — zero orphan risk.

---

platform.ai_capacity_policy

A single shared policy-binding table for the A8/E4 6-level AI-capacity precedence chain (platform hard ceiling → tier → tenant → workload_class → skill → task) — one row per scope, not 6 separate tables. New 2026-07-12, agents-v2/v3 build Phase 1 — see PROJECT_DECISIONS #62. NULL on a limit column means "no limit at this layer," this codebase's own established convention (e.g. identity.agent_duty_grant.spend_limit_cents).

Structurally not tenant-scoped — no tenant_id column. platform/tier/workload_class/skill/task-scoped rows are effectively global reference/policy data; tenant-scoped rows are filtered by scope_ref = current tenant at the query layer, since RLS cannot itself interpret a polymorphic column's meaning. No RLS.

RLS: not applied — see above.

Access control: policy data. authenticated has SELECT only. Writes are service_role/admin-only — REVOKE INSERT, UPDATE, DELETE was added post-independent-verification after a live exploit found the platform-wide AI cost ceiling was otherwise freely deletable by any ordinary tenant-scoped authenticated session (PROJECT_DECISIONS #62 addendum finding 1, the phase's own BLOCKER).

scope_ref is polymorphic — tier_id / tenant_id / workload_class_id / skill_version_id / task_id, depending on scope_type. No single-table FK target is possible (the same disclosed shape as billing.ar_charge.source_ref); the NULL/NOT-NULL half is CHECK-enforced (chk_ai_capacity_policy_scope_ref_consistency), the polymorphic-target half is application-enforced.

priority is the tie-break when multiple rows resolve for the same scope. Corrected post-independent-verification (addendum finding 3 — originally vestigial, the resolver always took MIN(max_cost_cents) regardless): each scope now resolves ORDER BY priority DESC, max_cost_cents ASC LIMIT 1 (highest priority wins; ties break toward the stricter/smaller ceiling) inside platform.try_increment_ai_capacity_spend() — see tenant_ai_capacity_usage below.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
scope_type	text	NOT NULL	—	CHECK IN ('platform','tier','tenant','workload_class','skill','task')
scope_ref	UUID	nullable	—	Polymorphic — see note above. CHECK: NULL iff scope_type='platform'
max_concurrency	bigint	nullable	—	NULL = unbounded
max_cost_cents	bigint	nullable	—	NULL = unbounded
max_steps	bigint	nullable	—	NULL = unbounded
max_tokens	bigint	nullable	—	NULL = unbounded
priority	integer	NOT NULL	0	Tie-break when multiple rows resolve for the same scope — see note above
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (scope_type, scope_ref) — ai_capacity_policy_scope_idx

Cross-column CHECK constraints:

• CHECK (scope_type IN ('platform','tier','tenant','workload_class','skill','task')) — chk_ai_capacity_policy_scope_type
• CHECK ((scope_type = 'platform' AND scope_ref IS NULL) OR (scope_type != 'platform' AND scope_ref IS NOT NULL)) — chk_ai_capacity_policy_scope_ref_consistency

Triggers:

• set_updated_at — BEFORE UPDATE

Seed data: 1 interim platform-wide ceiling (scope_type='platform', scope_ref NULL, max_cost_cents=100000000, priority=0) — a disclosed placeholder, not a production-tuned value, added post-independent-verification (addendum finding 2) so positive spends aren't perpetually blocked pending real business limits.

Function — platform.try_increment_ai_capacity_spend(p_tenant_id, p_tier_id, p_delta_cents): the atomic enforcement half of A8/E4, operating against tenant_ai_capacity_usage below. A single UPDATE ... WHERE current_spend_cents + p_delta_cents <= (LEAST of up to 3 correlated-subquery-resolved ceilings) ... RETURNING current_spend_cents — never a preceding SELECT. Returns the new balance on success, NULL if a positive increment would exceed the resolved ceiling (the caller checks for NULL; it does not retry with a smaller value). p_delta_cents <= 0 (a refund/correction) always succeeds unconditionally, bypassing the ceiling check entirely — corrected post-independent-verification (addendum finding 2): the original version gated refunds behind the same NULL-ceiling logic as a real spend, so a refund attempted when no policy row resolved for any scope silently failed exactly like a real over-budget rejection. Live-reproduced: a platform-wide 1000-cent ceiling, 3 genuinely concurrent 400-cent spend attempts (sum 1200 > ceiling) — exactly 2 of 3 succeed (running total 800), the 3rd correctly returns NULL; final current_spend_cents never exceeds 1000.

---

platform.tenant_ai_capacity_usage

Mirrors ai.agent_usage_period's own proven atomic-upsert shape — one row per (tenant, calendar-month period), incremented exclusively via platform.try_increment_ai_capacity_spend(), never an application-level read-then-check-then-write. New 2026-07-12, agents-v2/v3 build Phase 1 (E4) — see PROJECT_DECISIONS #62.

Tenant-scoped, many per tenant (one row per calendar-month period).

RLS: enabled — tenant isolation policy on tenant_id.

No created_at column — deliberately: rows are upserted via INSERT ... ON CONFLICT (tenant_id, period_start) DO NOTHING inside try_increment_ai_capacity_spend() itself, and only updated_at is meaningfully tracked thereafter (mirrors ai.agent_usage_period's own shape).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
period_start	date	NOT NULL	—	First day of the usage period (date_trunc('month', now()), computed by try_increment_ai_capacity_spend())
period_end	date	NOT NULL	—	Last day of the usage period
current_spend_cents	bigint	NOT NULL	0	Maintained exclusively via platform.try_increment_ai_capacity_spend()
current_concurrency	integer	NOT NULL	0
current_queued_tasks	integer	NOT NULL	0
memory_bytes_used	bigint	NOT NULL	0
vector_storage_bytes_used	bigint	NOT NULL	0
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (tenant_id, period_start)

---

platform.tenant_regional_policy

Each tenant's ONE effective regional-placement policy (E7). New 2026-07-12, agents-v2/v3 build Phase 1 — see PROJECT_DECISIONS #62. Every routing/fallback/storage/retrieval/archival decision in the later phases of this build must resolve THROUGH this table — region columns without a common resolver are reference data, not enforcement (E7's own governing rule).

Tenant-scoped, exactly one row per tenant — UNIQUE on tenant_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant. UNIQUE — exactly one policy row per tenant
home_region	text	NOT NULL	—	Provider region code, e.g. "us-east-1"
allowed_processing_regions	JSONB	NOT NULL	'[]'	Flat array of provider region codes, e.g. ["us-east-1","us-west-2"]
allowed_model_regions	JSONB	NOT NULL	'[]'	Same shape as allowed_processing_regions
allowed_object_storage_regions	JSONB	NOT NULL	'[]'	Same shape as allowed_processing_regions
allowed_vector_storage_regions	JSONB	NOT NULL	'[]'	Same shape as allowed_processing_regions
cross_region_replication_allowed	boolean	NOT NULL	false
provider_restrictions	JSONB	nullable	—	e.g. {"aws":{"allowed":true},"azure":{"allowed":false,"reason":"no BAA on file"}}
retention_requirements	JSONB	nullable	—	Per-table retention floor in days, e.g. {"agent_execution_days":2555,"decision_context_days":2555}
encryption_key_region	text	nullable	—
classification_restrictions	JSONB	nullable	—	e.g. {"pii":{"allowed_regions":["us-east-1"]},"phi":{"allowed_regions":[]}}
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on tenant_id

Triggers:

• set_updated_at — BEFORE UPDATE

---

Platform — Design Patterns Summary

Patterns applied across the 34 Platform tables. Use this as a cheat-sheet when reviewing or extending the schema.

Tables WITHOUT tenant_id or with nullable tenant_id — no RLS (10 tables)

These tables do not carry a mandatory tenant_id and are not subject to tenant-level RLS.

• tenant — IS the root; everything else's tenant_id FKs here
• tier_definition — reference data; shared across all tenants
• agreement_version — reference data; shared across all tenants
• promo_code — reference data; Vrida-issued codes, redeemable across signups
• operator_audit_log — compliance log; tenant_id nullable (some actions are global); no RLS
• platform_setting — reference data; Vrida-wide config, no tenant_id at all
• module_catalog — reference data; global module registry (agents-v2/v3 build Phase 1, 2026-07-12)
• tier_module_entitlement — reference data; tier → module defaults (agents-v2/v3 build Phase 1, 2026-07-12)
• module_dependency — reference data; global module dependency graph (agents-v2/v3 build Phase 1, 2026-07-12)
• ai_capacity_policy — policy data; platform/tier/workload_class/skill/task-scoped rows are global, tenant-scoped rows are filtered by scope_ref at the query layer since RLS cannot interpret a polymorphic column (agents-v2/v3 build Phase 1, 2026-07-12)

Remediation Phase 1 (2026-07-08)

CHECK/RLS/REVOKE/trigger-only — no column or table count change (23 tables / 408 cols unchanged). Two fixes landed in this module as part of a cross-cutting senior-architect-review remediation pass; full cross-module record in PROJECT_DECISIONS #37.

• platform.operator_audit_log — REVOKE, not a new RLS policy. This table's original design (see its own section above) assumed "No RLS — intentionally non-RLS Vrida-internal compliance log," reachable only by service_role/superuser. This phase's own blanket per-schema GRANT to the authenticated role invalidated that assumption — it would otherwise have made this Vrida-operator-only, cross-tenant compliance log newly reachable by any tenant-scoped connection. Fix: REVOKE SELECT, INSERT, UPDATE, DELETE ON platform.operator_audit_log FROM authenticated — a full revoke on this one table, restoring the original intended access model. A tenant-scoped RLS policy was deliberately not added, since it wouldn't fit this table's actual cross-tenant, operator-facing usage pattern (tenant_id is nullable and many rows are global).
• platform.reject_append_only_mutation() — new shared trigger function, defined here. Belt-and-suspenders enforcement for Item 4's append-only ledger tables (9 tables spanning ai/tax/identity/billing/inventory/pos). Body: RAISE EXCEPTION using TG_TABLE_SCHEMA/TG_TABLE_NAME/TG_OP in the message, SQLSTATE P0001. Grant-level protection alone (REVOKE UPDATE, DELETE FROM authenticated) only stops the authenticated role; this trigger blocks UPDATE/DELETE against those 9 ledger tables for every role, including superuser/service_role. Defined in platform as a shared/reusable function; the 9 tables it's attached to live in other modules' own schemas.

Remediation Phase 2 (2026-07-08)

PK-generation-strategy change only — no column or table count change (23 tables / 408 cols unchanged). Full cross-module record in PROJECT_DECISIONS #38.

• 6 append-only event/audit/ledger tables retargeted to UUIDv7 PKs. agreement_acceptance, ai_credit_transaction, operator_audit_log, tenant_internal_activity, tenant_lifecycle_event, and tenant_usage_summary each had their id column DEFAULT changed from gen_random_uuid() to platform.uuid_generate_v7(). Why: UUIDv7 is time-ordered (a timestamp prefix, not fully random like v4), which keeps future time-range partitioning possible on these append-only ledgers without a PK rewrite — a rewrite that becomes impossible once data has already landed on a random UUIDv4 PK. No other column changed on any of these 6 tables; this is purely a default-generation-strategy swap on id, matching every other table's existing id UUID NOT NULL, PK column definition above.
• platform.uuid_generate_v7() — new shared function, defined here. Lives in packages/db/migrations/20260708180000_phase2_uuidv7_function.sql. A cross-cutting utility referenced by column defaults across 9 other schemas (not just platform's own 6 tables above), matching the precedent set by platform.reject_append_only_mutation() in Remediation Phase 1 — both are shared functions defined in platform but consumed schema-wide. The function itself is not a table or column and does not affect platform's 23-table/408-column count.

Remediation Phase 4 (2026-07-08)

+3 tables / +32 cols — 23 → 26 tables, 408 → 440 cols. All additive; zero DROP TABLE/DROP COLUMN. Full cross-module record in PROJECT_DECISIONS #40.

• platform.accounting_period (Item 14, 9 cols) — fiscal-period tracker. This codebase's first use of Postgres EXCLUDE USING gist (btree_gist extension newly enabled): excl_accounting_period_no_overlap EXCLUDE USING gist (tenant_id WITH =, daterange(period_start, period_end, '[]') WITH &&) prevents two overlapping periods for the same tenant while leaving different tenants' identical date ranges unaffected. A new shared trigger function, platform.flag_closed_period_business_date(), is FLAG-NOT-REJECT (sets review_status='pending', never raises) — consumed by 3 triggers in pos: trg_sale_flag_closed_period / trg_sale_refund_flag_closed_period (BEFORE INSERT OR UPDATE OF business_date) and trg_register_cash_entry_flag_closed_period (BEFORE INSERT only, since Phase 1's own append-only trigger on pos.register_cash_entry already blocks every UPDATE unconditionally — an UPDATE clause there would be dead code). pos.register_cash_entry gained a full 5-column review seam (review_status/review_reason/reviewed_by_actor_id/reviewed_at/decision_provenance) as part of this item, since it had none before and the flag trigger needed something to set.
• platform.legal_entity (Item 15, 8 cols) — 1:N legal entities per tenant. Lets a tenant incorporate a 2nd LLC without splitting into two tenants. legal_entity_tenant_id_primary_unique (partial unique WHERE is_primary=true) allows exactly one primary entity per tenant. Backfilled 1 row per existing platform.tenant (1997 rows at build time — the plan's original "176 rows" figure was found stale and corrected live before the backfill ran), name sourced from tenant.name (tenant_profile.legal_name was checked and rejected as a source, 0-for-12 non-null). A nullable entity_id FK to this table was added to 10 HEADER tables codebase-wide (never to line items or to purchasing.vendor): platform.contract, platform.billing_account, admin.compliance_document, tax.tax_calculation, billing.ar_account, billing.vendor_payable, purchasing.vendor_invoice, purchasing.purchase_order, orders.order_header, pos.sale — the 2 platform-owned columns (contract.entity_id, billing_account.entity_id) are this module's own +2 of the 32 new cols; the other 8 tables' entity_id additions belong to their own modules' column counts, not platform's.
• platform.outbox (Item 20a, 13 cols) — durable transactional-outbox event table. Deliberately mutable, unlike this module's append-only ledgers — status/attempts/last_attempted_at/delivered_at all change after insert as a dispatcher processes an event. Uses gen_random_uuid() for its PK, NOT platform.uuid_generate_v7() (Phase 2's own UUIDv7-for-append-only-ledgers rule deliberately does not apply here, since this table isn't append-only). No consumer/dispatcher service exists yet — schema-only.

Column-count reconciliation: accounting_period (9) + legal_entity (8) + outbox (13) = 30, plus contract.entity_id (+1) and billing_account.entity_id (+1) = 32. 408 + 32 = 440.

Header/Line Remediation Fix #1 (2026-07-10)

+1 table / +10 cols — 26 → 27 tables, 440 → 450 cols. 3rd and final reopen (of 3: POS → Purchasing → Platform) in the coordinated "Header/Line Remediation" effort (~/Downloads/vrida-header-line-remediation-design-2026-07-10.md, §1). Full cross-module record in PROJECT_DECISIONS #48.

• platform.subscription_invoice_line (10 cols) — per-line decomposition of a subscription_invoice's total. Write-once (no status/updated_at/deleted_at), PK default platform.uuid_generate_v7() per Remediation Phase 2's append-only-ledger convention. Composite FK subscription_invoice_line_invoice_tenant_fkey → subscription_invoice(id, tenant_id), which required a new prerequisite UNIQUE(id, tenant_id) on subscription_invoice itself (confirmed missing, not hypothetical).
• LINES ARE TRUTH reconciliation — trg_subscription_invoice_line_sync_totals (function platform.sync_subscription_invoice_totals_from_lines()), AFTER INSERT OR UPDATE OR DELETE on the line table, recomputes subscription_invoice.subtotal_cents/discount_cents/tax_cents/total_cents from SUM(lines.amount_cents) grouped by line_type. The opposite pattern from purchasing.vendor_credit_line's header-is-truth trigger (PROJECT_DECISIONS #47).
• Corrected migration-sequencing (design doc §7 finding V3-1) — the single most serious finding of the whole 3-module effort's independent verification pass. Since the sync trigger unconditionally overwrites the header from SUM(lines), installing it before the backfill+verify steps would have made the "verify against stored totals" check tautological (always "passes," even where the original data genuinely diverged). Corrected order: inspect real data → add the prerequisite UNIQUE → create the table empty (no trigger) → snapshot pre-backfill totals → backfill (guarded against non-array line_items values) → verify against the snapshot (log mismatches via RAISE NOTICE, don't silently fix) → install the trigger only now → deprecate line_items in place.
• Disclosed finding, not a migration bug: all 5 pre-existing live invoices had line_items='[]' despite non-zero subtotal_cents — the backfill correctly produced zero lines and the verification step correctly logged all 5 as mismatches, a genuine pre-existing gap in a column nothing ever populated.

Header/Line Remediation Batch 2 — Closing Bare-FK Fix (2026-07-10)

Constraint-only — 27 tables / 450 cols unchanged. This is the LAST item in the entire 2-batch "Header/Line Remediation" effort (batch 1: POS/Purchasing/Platform, PROJECT_DECISIONS #46-48; batch 2: Inventory/Orders/Billing/Identity/this fix, PROJECT_DECISIONS #49-53), landing in a single 3-module migration alongside purchasing.vendor_invoice_match and billing.ar_charge (see those modules' own schema_docs for their own legs). Full cross-module record in PROJECT_DECISIONS #53.

• platform.payment.invoice_id upgraded from a bare FK to composite payment_invoice_id_tenant_fkey (invoice_id, tenant_id) → subscription_invoice(id, tenant_id). This gap was disclosed (not fixed) at fix #1's own independent verification pass (PROJECT_DECISIONS #48) since subscription_invoice didn't yet have UNIQUE(id, tenant_id) — that prerequisite was added by fix #1 itself, making this a small, low-risk follow-up ALTER once it existed.
• Pre-migration audit: 3 live rows, 0 cross-tenant mismatches, 0 orphaned invoice_id references — safe direct ADD (not NOT VALID).
• Old bare constraint (payment_invoice_id_subscription_invoice_id_fk) confirmed DROPPED, not left dangling alongside the new composite one.
• Migration: packages/db/migrations/20260710070000_headerline_bare_fk_fixes.sql. Schema file: packages/db/src/schema/platform/billing.ts (payment's FK definition retargeted to the composite form).
• Tests: apps/api/src/platform/__tests__/platform-billing.spec.ts, new Group K (2 tests: K1 cross-tenant rejection, K2 valid same-tenant success).

agents-v2/v3 Build — Phase 1: Module Registry + AI Capacity/Regional Policy (2026-07-12)

+7 tables / +64 cols — 27 → 34 tables, 450 → 514 cols. All additive; zero existing tables altered. Phase 1 of a 6-phase build authorization (design of record: vrida-agents-v2-design-amendment-2026-07-11.md as amended by vrida-agents-v3-correction-pass-2026-07-12.md, v3 winning on conflict). Full cross-module record in PROJECT_DECISIONS #62.

• module_catalog (A1, 8 cols) — the global module registry, replacing the closed CHECK-enum module-tag pattern every polymorphic module column in this codebase has used until now. lifecycle_status is a single column (not split lifecycle/implementation — this codebase's module pipeline is strictly linear), forward-only via trg_module_catalog_lifecycle_transition (BEFORE UPDATE OF lifecycle_status), with deprecated/retired reachable from any state. Seeded with all 25 modules — the 22 already-locked modules (platform/identity at in_build, the rest at designed) plus agents/semantics/signals at designed.
• tier_module_entitlement (7 cols) — which modules a tier includes by default, replacing tier_definition.entitled_modules (a JSONB blob whose documented shape never matched its own seed data — disclosed, not silently fixed; entitled_modules stays in place, deprecated in favor of this table going forward).
• tenant_module_activation (10 cols) — the REAL per-tenant module switch, closing a functionally-dead mechanism (is_toggleable's hasEntitlement() had zero callers anywhere in the codebase). trg_tenant_module_activation_check_dependents (BEFORE UPDATE OF status) rejects deactivating a module for a tenant while another module still active for that tenant hard-depends on it via module_dependency — reject, not auto-cascade.
• module_dependency (5 cols) — module prerequisite graph. Real recursive cycle detection (WITH RECURSIVE, trg_module_dependency_no_cycle), not just the direct self-reference the chk_module_dependency_no_self_reference CHECK already covers.
• ai_capacity_policy (10 cols) / tenant_ai_capacity_usage (10 cols) — the A8/E4 6-level precedence chain's (platform hard ceiling → tier → tenant → workload_class → skill → task) atomic enforcement half. A single shared policy-binding table, not 6 separate ones; NULL on a limit column means unbounded at that layer (this codebase's own established convention). The atomic spend-increment function, platform.try_increment_ai_capacity_spend(), embeds the resolved ceiling as a correlated subquery inside the SAME UPDATE ... WHERE ... RETURNING statement — never a preceding SELECT.
• tenant_regional_policy (14 cols) — each tenant's ONE effective regional-placement policy (E7); every routing/fallback/storage/retrieval/archival decision in the later phases of this build must resolve THROUGH this table.

Column-count reconciliation: module_catalog (8) + tier_module_entitlement (7) + tenant_module_activation (10) + module_dependency (5) + ai_capacity_policy (10) + tenant_ai_capacity_usage (10) + tenant_regional_policy (14) = 64. 450 + 64 = 514.

Independent lock-gate verification (4 findings, all fixed same-pass, before Phase 2 was authorized to proceed): (1) BLOCKER — a pre-existing schema-wide default-privilege GRANT (20260708150000_phase1_rls_wiring.sql) applies to every new platform table at CREATE TABLE time, before this migration's own narrower GRANT SELECT-only statements ran; since Postgres GRANTs are additive, module_catalog/tier_module_entitlement/module_dependency/ai_capacity_policy were live-exploitably INSERT/UPDATE/DELETE-able by any ordinary authenticated session — including deleting the platform-wide AI cost ceiling outright. Fixed via explicit REVOKE INSERT, UPDATE, DELETE ... FROM authenticated on all 4 tables. (2) MAJOR — try_increment_ai_capacity_spend()'s ceiling check applied unconditionally to every delta including negative ones (refunds/corrections), so a refund attempted with zero resolvable policy rows silently failed exactly like a real over-budget rejection; fixed so p_delta_cents <= 0 always succeeds, unconditionally. (3) MAJOR — ai_capacity_policy.priority, documented as the tie-break mechanism (A8), was vestigial; fixed so each scope resolves ORDER BY priority DESC, max_cost_cents ASC LIMIT 1. (4) MAJOR — the seeded module_catalog row for multi_loc was misclassified layer='foundation', contradicting SCHEMA_CONVENTIONS.md §1's own explicit Business-layer list; reseeded layer='business'. See PROJECT_DECISIONS #62 addendum for the full findings.

Mixed-scope table WITH RLS (1 table)

Unlike the tables above, announcement has a nullable tenant_id and an RLS policy — the first platform table to combine the two. The policy's NULL branch (tenant_id IS NULL OR tenant_id = current_setting('app.current_tenant_id')::uuid) makes global rows visible to every tenant instead of hiding them from all of them (bug class #5). Pattern borrowed from identity.role (DR-3).

• announcement — tenant_id NULL = global broadcast, set = targeted to one tenant

Immutable tables — no updated_at AND no deleted_at (7 tables)

Append-only. Once written, never modified or removed.

• agreement_acceptance
• tenant_usage_summary
• tenant_lifecycle_event
• tenant_internal_activity
• operator_audit_log
• ai_credit_transaction
• subscription_invoice_line (Header/Line Remediation fix #1, 2026-07-10) — no updated_at/deleted_at columns (write-once by convention), but unlike this list's other 6 members it has no REVOKE/append-only trigger: trg_subscription_invoice_line_sync_totals fires on AFTER INSERT OR UPDATE OR DELETE, so a genuine correction DELETE is a supported, tested path (re-syncs the header), not blocked at the DB layer like the hard-enforced ledgers above — see its own section for detail

Tables WITHOUT deleted_at — permanent records (23 tables total)

Includes the 7 immutable tables above plus 16 more that are mutable or permanent records:

• tenant (status-driven lifecycle, not soft delete)
• tier_definition (reference data; deactivate via is_active)
• agreement_version (reference data; deactivate via is_active)
• agreement_acceptance (immutable)
• tenant_usage_summary (immutable)
• tenant_lifecycle_event (immutable)
• tenant_internal_activity (immutable)
• operator_audit_log (immutable)
• ai_credit_transaction (immutable)
• tenant_setup_task — mutable but permanent (setup history is preserved)
• tenant_data_lifecycle — mutable but permanent (compliance/audit records)
• platform_setting — mutable, no soft-delete concept for a bare key-value row (deactivate by overwriting, not deleting)
• accounting_period (Phase 4, Item 14) — mutable (status/closed_at transition open→closed), no soft-delete concept for a fiscal period
• legal_entity (Phase 4, Item 15) — mutable but permanent; deactivate via is_active, mirroring tier_definition/agreement_version
• outbox (Phase 4, Item 20a) — mutable by design (status/attempts/delivered_at all change post-insert); no soft-delete concept for an event row
• module_catalog (agents-v2/v3 build Phase 1, 2026-07-12) — reference data; deactivate via a lifecycle_status transition to deprecated/retired, not soft delete
• tier_module_entitlement (Phase 1) — reference data; toggle via is_included, no soft-delete concept
• tenant_module_activation (Phase 1) — mutable but permanent; deactivate via status, mirroring accounting_period's no-soft-delete pattern
• module_dependency (Phase 1) — reference data; dependency edges are added/removed via hard INSERT/DELETE, no soft-delete concept
• ai_capacity_policy (Phase 1) — policy data; no soft-delete concept, mirrors platform_setting's own no-soft-delete precedent
• tenant_ai_capacity_usage (Phase 1) — mutable counter row per (tenant, period); no soft-delete concept, mirrors ai.agent_usage_period's own shape
• tenant_regional_policy (Phase 1) — mutable, one row per tenant; no soft-delete concept, updated in place as policy changes

JSONB columns by purpose

Table	Column	Purpose
tenant	feature_flags	{"beta_ai_chat": true, "new_dashboard": false} — system boolean toggles, key = flag name
tenant	module_prefs	{"inventory": true, "production": false, "purchasing": true} — tenant on/off per module, key = module name
tenant_profile	billing_address	{street,city,state,zip,country}
tenant_profile	primary_store_address	{street,city,state,zip,country}
tenant_profile	services_offered	["service_a","service_b"] — array of tenant-defined service codes
billing_account	billing_address	{street,city,state,zip,country}
subscription_invoice	line_items	[{"description":"Pro plan monthly","amount_cents":9999,"type":"plan"},…] — array; type ∈ plan/addon/credit/adjustment. DEPRECATED 2026-07-10 (Header/Line Remediation fix #1) — superseded by subscription_invoice_line; confirmed zero code path ever populated this column live
tier_definition	entitled_modules	{"inventory":true,"production":false}
tier_definition	permitted_addons	["extra_users","extra_sites","ai_pack","white_label","priority_support"] — array of addon codes
tenant_entitlement	metadata	{"granted_for":"beta_program","internal_ref":"CS-1234"}
agreement_acceptance	evidence_json	{"checkbox_text":"I agree to..."}
tenant_setup_task	metadata	Task-specific data, e.g. {"imported_sku_count":243}
tenant_data_lifecycle	metadata	{"export_format":"json","legal_hold_ref":"LH-2026-01"}
tenant_lifecycle_event	metadata	{"previous_tier":"starter","triggered_by":"billing_job"}
tenant_internal_activity	before_value	State before admin change
tenant_internal_activity	after_value	State after admin change
operator_audit_log	before_state	{"status":"active"} — entity state before operator action
operator_audit_log	after_state	{"status":"suspended"} — entity state after operator action
platform_setting	value	No fixed shape — caller-defined per key, e.g. {"currency":"USD"} or {"enabled":true}
tenant_regional_policy	allowed_processing_regions / allowed_model_regions / allowed_object_storage_regions / allowed_vector_storage_regions	["us-east-1","us-west-2"] — flat array of provider region codes
tenant_regional_policy	provider_restrictions	{"aws":{"allowed":true},"azure":{"allowed":false,"reason":"no BAA on file"}}
tenant_regional_policy	retention_requirements	{"agent_execution_days":2555,"decision_context_days":2555} — per-table retention floor in days
tenant_regional_policy	classification_restrictions	{"pii":{"allowed_regions":["us-east-1"]},"phi":{"allowed_regions":[]}}

Money columns (always _cents bigint + currency_code char(3))

Table	Column
subscription_invoice	subtotal_cents, discount_cents, tax_cents, total_cents, credits_cents, amount_due_cents, amount_paid_cents, amount_remaining_cents
payment	amount_cents, provider_fee_cents, net_amount_cents, refund_amount_cents
tier_definition	price_monthly_cents, price_annual_cents, price_per_additional_location_cents
tenant_entitlement	price_cents
contract	contract_value_cents
ai_credit_account	balance_cents, granted_credit_cents, purchased_credit_cents, lifetime_spent_cents, spend_limit_cents
ai_credit_transaction	amount_cents, balance_after_cents
subscription_invoice_line	unit_amount_cents (nullable), amount_cents

currency_code is char(3) ISO 4217, default 'USD', present on every table holding money.

promo_code.discount_value is NOT in the _cents money list — it holds either a percent (1–100) or an amount in cents, discriminated by discount_type. Treat it accordingly rather than assuming pure cents.

tier_definition.included_locations is a count (integer), not a money column. price_per_additional_location_cents is the associated money column for per-location overage billing.

Other patterns

• All PKs: UUID with default gen_random_uuid()
• All enums: stored as text with CHECK constraints (per SCHEMA_CONVENTIONS — no Postgres enum types)
• All timestamps: timestamptz, stored UTC
• Partial unique indexes (soft-delete pattern per SCHEMA_CONVENTIONS):
  • tenant_profile: UNIQUE (tenant_id) WHERE deleted_at IS NULL
  • billing_account: UNIQUE (tenant_id) WHERE deleted_at IS NULL
  • subscription_invoice: UNIQUE (invoice_number) WHERE deleted_at IS NULL
  • subscription: UNIQUE (tenant_id) WHERE status NOT IN ('cancelled','ended') AND deleted_at IS NULL — enforces one active non-deleted subscription per tenant
  • contract: UNIQUE (contract_number) WHERE deleted_at IS NULL
  • promo_code: UNIQUE (code) WHERE deleted_at IS NULL
  • ai_credit_account: UNIQUE (tenant_id) WHERE deleted_at IS NULL — one credit account per tenant
  • tenant_usage_summary, tenant_setup_task use composite UNIQUE (no deleted_at — tables have no soft delete)
  • agreement_version uses composite UNIQUE (agreement_type, version) (reference data — no soft delete)

---