Vrida — Project Orientation

Vrida is a multi-tenant SaaS ERP/POS for US retail plant nurseries (vertical-neutral core), built by CodeCraft Solutions LLC. This file orients a session fast. For where docs live and current status, see docs/DOCS_INDEX.md.

How this project works

  • v2 is a fresh, AI-native redesign. The v1 docs (the 13 specs in docs/modules/, and prior schema docs) are SUPERSEDED reference/templates — proven patterns, not confirmed v2 design.
  • Modules are built one at a time, end-to-end, through a fixed pipeline. A module is fully proven (designed, locked, built, tested, shipped) before the next begins.
  • Platform is module #1 — it ran through the pipeline first as the process prototype, confirming the pipeline and the per-module doc templates.

The module pipeline (in docs/process/runbooks/)

Every module runs the same three runbooks:

  • module-design.md — four parts, in order:
    • Part A — Features + gaps (what the module must do)
    • Part B — AI-plane application (which AI capabilities, authority, ON/OFF, state needed)
    • Part C — Schema design + audit (tables supporting A + B)
    • Part D — Lock gate → DESIGN-LOCK (verifies completeness, docs updated, seams hold, counts reconcile)
  • smoke-test.md — E2E, AI-OFF acceptance, RLS cross-tenant leak; run repeatedly.
  • ship.md — ship gate: UI live, smoke green, regression.

Two gates, one hard lock

  • Parts A–C are committed-but-revisable checkpoints.
  • Part D (DESIGN-LOCK) and Ship are the gates. Features, AI plane, and schema freeze together at Part D.

Core rules

  • Single source of truth for counts/status: docs/DOCS_INDEX.md. No other doc (including this one) restates counts.
  • READMEs describe purpose/rules, never live contents/counts.
  • AI is first-class and applied before schema (AI state drives table design).
  • Every module must work fully with AI OFF (graceful degradation).
  • Before implementing: give a concise overview and wait for go-ahead.
  • CC tasks are given as explicit instructions.
  • After ANY change to docs (.md files), rebuild the HTML viewer by running node apps/docs/tools/build-docs.js. The .md files are the source of truth; apps/docs/output/ is the readable, navigable view derived from them. Folder descriptions and file counts must be DERIVED from the filesystem at build time, never hardcoded, so they never drift. This rebuild is part of completing any doc-writing task — not a separate step to remember. Deploy with npx vercel --prod --cwd apps/docs.

Folder map

See docs/DOCS_INDEX.md for the full file map. Top level: docs/ai, docs/architecture, docs/database, docs/modules, docs/decisions, docs/business-requirements, docs/process (+ runbooks).

Current focus

Authoritative Inventory correction — Stock Transfer schema lock, 2026-07-16 task run. Inventory is now 29 base tables / 425 base-table columns / 1 view. The Transfer subset is exactly 3 tables / 63 columns / 28 CHECKs / 20 FKs / 11 triggers, built on the independently verified Inventory Core boundary. It supports one atomic shipment, partial receipts, and one optional lot per line. Consequential wrappers are built but dormant to every runtime role; no Transfer service, API, UI, worker, module wiring, or trusted credential/session gateway exists. See docs/decisions/PROJECT_DECISIONS.md #75–#76. Historical Inventory counts later in this long status narrative are superseded by this correction.

Platform, Identity, Shared, and Multi-Location (modules #1–#4) are all schema-locked and migrated; Platform and Identity have complete service layers (PlatformService, IdentityService) while Shared and Multi-Location are schema-only so far (no service layer yet). On 2026-07-06 all four modules also received a cross-cutting autonomy-first backfill establishing the canonical agent-actor/review/provenance pattern future modules will reuse (see docs/decisions/PROJECT_DECISIONS.md #19). Identity was reopened again the same day to build the A5 agent-authority passport (agent_duty_grant — per-agent, per-permission draft/execute/needs-approval authority + spend/quantity limits, complementing the agent-elevation gate) — schema-only, no service methods yet (see docs/decisions/PROJECT_DECISIONS.md #22). CRM (module #5) is now also schema-locked (2026-07-06) — the first of the 13 nursery-vertical product modules to actually build (13 tables / 189 cols), designed autonomy-native from the start with agent authority mapped straight onto agent_duty_grant rather than inventing a new mechanism (see docs/decisions/PROJECT_DECISIONS.md #23); schema-only so far — CrmService doesn't exist yet, the next gap for this module. Inventory (module #12) is also now schema-locked (2026-07-06) — the biggest module built so far and the first real consumer of shared.plant (24 tables / 336 cols, up from 335 after a 2026-07-07 erosion fix, schema-only — no InventoryService yet), see docs/decisions/PROJECT_DECISIONS.md #24. AI (module #6, cross-cutting) is also now schema-locked (2026-07-06) — the agent-runtime schema layer, adding an append-only action ledger (agent_execution), an atomic-upsert usage meter (agent_usage_period), and a durable memory store (agent_memory) on top of v1's import pipeline + Bedrock-call log (7 tables / 118 cols total, up from v1's 4/71 baseline; the 3 new tables are schema-only — no AIService extensions yet), see docs/decisions/PROJECT_DECISIONS.md #25. Pricing (module #13) is now also schema-locked (2026-07-06) — the first module of the sell path, resolving 6 gaps an outside-expert critique found against standard commerce/pricing-engine patterns directly in schema (supersede-don't-edit price history, explicit site-scoping for customer rules, a currency-precision convention, tax_treatment, cost_plus_percent markup pricing, campaign_label) plus 3 binding cross-module hard contracts for pos/orders and PricingService (4 tables / 78 cols, up from 75 after a 2026-07-07 erosion fix; v1's baseline was 4-table/56-col), see docs/decisions/PROJECT_DECISIONS.md #26; schema-only so far — no PricingService yet. POS (module #14) is now also schema-locked (2026-07-07) — offline-first, honoring pricing's Hard Contract 1 (9 tables / 138 cols, down from v1's 19), see docs/decisions/PROJECT_DECISIONS.md #27; schema-only so far — no POSService yet. A same-day pos reopen (2026-07-07) closed a live gift-card/store-credit silent-acceptance bug via 2 fail-closed CHECKs (see PROJECT_DECISIONS #27 addendum). A 2026-07-07 crm/inventory/pricing v1→v2 erosion audit — the same column-level reconciliation check pos's own audit pioneered — then found and fixed 1 more live bug (crm's customer_tax_certificate could be 'active' with no verifier) plus 6 unlogged erosions across the 3 modules (crm: address country-code integrity; pricing: rule_kind/name/price_list_assignment uniqueness; inventory: stock_reservation agent attribution), all independently verified and now schema-locked again — see docs/decisions/PROJECT_DECISIONS.md #28. Orders (module #15) is now also schema-locked (2026-07-07) — completing the sell path (crm → pricing → inventory → orders → pos), 7 tables / 173 cols (up from v1's 7/133 baseline, zero tables consolidated or dropped). It is the first module designed AND built under the new Design-Phase Integrity rules (SCHEMA_DESIGN_RUNBOOK Section 0.5/2.3.6) — the retained 4-block v1→v2 delta-accounting record lives in PROJECT_DECISIONS #29 and was grep-verified in-file at lock (the new Section 6 item 15 gate's first real application). 3 real (not deferred) cross-module seams — order_line.stock_reservation_id → inventory.stock_reservation, order_header.fulfilled_sale_id → pos.sale, order_payment.pos_sale_payment_id → pos.sale_payment — mean Pricing's Hard Contract 1 is now SATISFIED by both pos and orders; schema-only so far — no OrderService yet, see docs/decisions/PROJECT_DECISIONS.md #29. Purchasing (module #16) is now also schema-locked (2026-07-07) — the BUY path, completing the supply loop opposite orders/pos (16 tables / 398 cols, up from v1's 16/341 baseline, +57, zero tables consolidated or dropped). Second module built under the Design-Phase Integrity rules (retained delta record in PROJECT_DECISIONS #30, grep-verified in-file at lock). Vendor master (with a new vendor.linked_customer_id → crm.customer link), POs (reorder-agent draft target; dual-UOM cost snapshot), receiving (writes an inventory.stock_movement — the inbound counterpart to a sale's decrement — + updates weighted-average cost, with a NULL-safe idempotency_key dedup), 3-way match (invoice document owned here, payment owned by Billing — vendor_invoice.status has no 'paid'), and credit/return (all LIGHT). The receiving/return inventory_movement_id → inventory.stock_movement FKs and purchase_order.source_order_id → orders.order_header are REAL now (v1 deferred them), and this module closes orders' draft_po_id forward-ref (a real FK, both directions). The reorder→PO draft loop is the flagship buy-side agent path, but PO-send stays needs_approval ALWAYS (the C8 financial-autonomy boundary); the cumulative agent spend-ceiling is deferred (cross-referenced, not reopened). Schema-only so far — no PurchasingService yet, see docs/decisions/PROJECT_DECISIONS.md #30. Tax (module #17) is now also schema-locked (2026-07-07) — the first module in this entire build with zero v1 precedent (v1 fully outsourced tax to Stripe Tax, confirmed via find docs/old -iname "*tax*" returning nothing; no local schema existed to diff against). 2 tables / 37 cols: tax_calculation (header, the nexus/rate-anomaly-flag surface; automation_source defaults 'system', the first justified deviation from the universal 'human' default anywhere in this codebase) and tax_calculation_jurisdiction (append-only per-jurisdiction breakdown, restoring the decomposition pos's own 19-to-9 audit found lost — no schema capability to mutate or delete a jurisdiction row exists at all). Polymorphic source_ref → pos.sale_line/orders.order_line (no reciprocal column, mirroring billing.ar_charge.source_ref); supersedes_calculation_id scoped to same-source_ref corrections only — an orders-time estimate and its later pos-time final calculation deliberately COEXIST rather than auto-link, since no line-level seam between order_line and sale_line exists (a disclosed design decision, not a schema gap). Designed together with Billing in one pass (tax calculates, billing settles); an adversarial verification pass on that joint proposal found and fixed 2 real BLOCKERs before either module was built — see docs/decisions/PROJECT_DECISIONS.md #31. A post-lock independent verification pass the same day found the same-source_ref supersession scoping was documented but not DB-enforced — closed via a new trigger, trg_tax_calculation_validate_supersession. Schema-only so far — no TaxService yet. Billing (module #18) is now also schema-locked (2026-07-07, same day, immediately after tax) — the SETTLE half of the financial layer: 9 tables / 162 cols (up from v1's locked 8 tables/113 cols baseline — all 8 v1 tables preserved 1:1, +1 NEW table ar_adjustment — the write-off/dispute-resolution lifecycle v1 deferred, built now with a full autonomy pack). ar_charge.tax_calculation_id → tax.tax_calculation.id is the tax seam; A/R (ar_account/ar_charge/ar_payment/ar_adjustment) gets FULL autonomy, A/P (vendor_payable/ap_payment) stays LIGHT — the real judgment already happened upstream at purchasing.vendor_invoice's own FULL gate, a disclosed asymmetry. ar_charge.source_ref/.source_payment_ref are polymorphic → pos.sale/orders.order_header and pos.sale_payment/orders.order_payment — plain uuid, no single-table FK (structurally impossible for a two-target column), validated by CHECK instead, mirroring tax's own same-day-established pattern. A live NULL-distinctness bug in ar_charge's idempotency dedup was caught and fixed during the build's own test-writing pass (a two-partial-unique split, mirroring orders.order_header's precedent) — see docs/decisions/PROJECT_DECISIONS.md #32. Schema-only so far — no BillingService yet. The financial layer (tax + billing) is now complete, closing the tax decomposition gap pos's own audit found. Payments (module #19) is now also schema-locked (2026-07-07, same day, immediately after billing) — the money-MOVEMENT layer, executing what billing records: 9 tables / 159 cols (up from v1's locked 8 tables/112 cols baseline — all 8 v1 tables preserved, +1 NEW table terminal_reader closing a pos.register hardware-pairing gap). payment_intent.source_ref polymorphic → pos.sale_payment/orders.order_payment/billing.ar_payment (plain uuid, no FK, 3-way widened from v1's pos/billing-only pair). chk_payment_intent_source_pair is a genuine fix caught by independent adversarial verification during the design-phase pass — not a v1-preserved feature; v1 never had this CHECK either. This is the first module built under a new hard, evidenced independent-verification gate (SCHEMA_DESIGN_RUNBOOK Section 2.3.7/2.7/6 item 1a, added 2026-07-07 directly because this same module's design proposal was originally self-graded "Zero FAILs" and found wrong by a later adversarial pass — see Bug Class 12, Section 5): both the design-phase and post-build verification passes are pasted, attributed findings from a separate agent, not claims. A separate agent's post-build re-audit found zero FAILs, full findings in PROJECT_DECISIONS #33. The money boundary (billing records, payments executes, platform is Vrida's own unrelated SaaS revenue) required a corrective column-comment fix on platform.subscription.status, folded into this build. Schema-only so far — no PaymentsService yet. The financial + money-movement layer (tax + billing + payments) is now complete. Platform was then reopened a 4th time, same session (2026-07-07) — prior reopens were 2026-06-30 (announcement/platform_setting) and 2026-07-06 (autonomy-first backfill, #19) — this time to absorb tenant-identity columns out of admin's tenant_business_profile, executing the governing ownership rule already recorded in PROJECT_DECISIONS #34 (platform owns tenant identity, admin owns tenant technical/operational config); this reopen's actual build is recorded in PROJECT_DECISIONS #35. platform.tenant_profile gained new business-email, legal-address, mailing-address, business-classification-code, and EIN-vault-reference columns, and its old free-text trading_name was retyped to a dbas JSONB string array (multiple DBAs per tenant, shape pinned down live before migrating, zero-risk retype confirmed against live data); tax_id and logo_url are deprecated in place (column comments only, still readable/writable) — superseded by ein_ref and by admin's future tenant_branding.logo_ref respectively. Platform's tenant_profile is now the single source of truth for tenant identity (legal name, EIN, business type/classification, addresses, DBAs), with admin's copy of those fields retired in favor of it. Application code (PlatformService, its DTOs, the shared TenantProfile type, and the admin tenant page) was cut over in the same pass, with new tests added and the full apps/api suite green. Schema-only aspects of this reopen (the vault-encryption service ein_ref awaits, and the two eventual column DROPs for tax_id/logo_url) are logged as open items pending their respective triggers. Admin is now also schema-locked (2026-07-07, same day, immediately after the platform reopen) — its first real v2 pass (v1 was 11 tables/145 cols, locked 2026-06-10, never rebuilt until now): 10 tables/128 cols, tenant_business_profile dropped entirely (content absorbed into platform per the reopen above), a faithful port of v1's other 10 tables plus the module-wide actor-attribution retarget (5 cols across 3 tables, identity.identity_user → identity.actor), and a tenant-side approval engine (approval_workflow/approval_routing_rule/approval_request) that stays separate from platform.contract's own review gate by design (Option B) — see docs/decisions/PROJECT_DECISIONS.md #34/#36. Schema-only so far — no AdminService yet. ORM is Drizzle (drizzle-orm + drizzle-kit); database host is Supabase. Both admin.vrida.app and tenant.vrida.app have page shells, mostly sample-data mockups. A senior-architect 8-lens schema review plus a 15-module validation sweep (both 2026-07-08) fed a 4-phase remediation plan; Remediation Phase 1 (enforcement foundation) is now complete (see docs/decisions/PROJECT_DECISIONS.md #37): real RLS wiring — a genuine non-superuser authenticated Postgres role now exists, GRANT-closed across all 15 schemas, replacing the prior superuser-only app connection that bypassed RLS entirely (and fixing a previously-undiscovered tenantDB() bind-parameter syntax bug, latent since the function had zero real call sites before this phase) — the C8 financial-autonomy boundary (purchasing.purchase_order/ai.agent_execution CHECKs), the agent-authority-passport self-issue guard (identity.agent_duty_grant), append-only enforcement on 9 ledger tables across 6 schemas (REVOKE + a shared trigger, platform.reject_append_only_mutation()), and a 19-constraint fail-open review-flag sweep across 8 modules (billing/inventory/pricing/payments/purchasing/tax/admin). The phase's own live-reproduction and independent-verification steps (3 separate adversarial lenses) found and fixed 3 real bugs along the way: a NULL-bypass in the PO sent-requires-approval CHECK, and 2 RLS coverage gaps (identity.identity_access_event needed RLS added; platform.operator_audit_log needed authenticated's access REVOKEd entirely, since it's Vrida-operator-only, not tenant-scoped) — plus surfaced 3 pre-existing, Phase-1-unrelated test-concurrency flakes, logged to OPEN_ITEMS rather than fixed (out of scope). Inventory is now 24 tables / 337 cols (+1, stock_count.reconciled_at — the only column-count change in the phase). Remediation Phase 2 (non-additive decisions) is now also complete (see docs/decisions/PROJECT_DECISIONS.md #38): a custom platform.uuid_generate_v7() function (mandatory on this Postgres 17.6 — native uuidv7() ships PG 18, no pg_uuidv7 extension available) is now the PK-generation strategy on all 25 tables independently confirmed genuinely append-only (widened from the plan's 4 named "hot ledgers" — stock_movement/stock_movement_line/sale_line/agent_execution — at near-zero marginal cost), keeping future time-range partitioning possible on these ledgers before any accumulate production data. payments.payment_intent also gained a processor anchor column (NOT NULL DEFAULT 'stripe', CHECK-constrained to ('stripe') for now) — the minimal-now step of a vendor de-primitivization; the full scope (19 other stripe_* columns across 6 schemas, 2 table renames, this CHECK's own widening, tax.provider's CHECK widening) is deferred to OPEN_ITEMS, triggered "before a second payment processor is integrated." Payments is now 9 tables / 159 cols (+1) — the only column-count change this phase. Both items independently verified with zero BLOCKERs. Remediation Phase 3 (missing capabilities) is now also complete (see docs/decisions/PROJECT_DECISIONS.md #39): 6 items across 6 modules. crm.customer gained credit_limit_cents/credit_terms (Item 8) — closing a live crm↔billing orphan: billing.ar_account's own comment already said it read these from crm, but crm never actually had them, an incomplete v1→v2 migration; crm now OWNS the credit policy, billing reads it. tax.tax_calculation gained calculation_type/reversed_calculation_id (Item 9) — a refund's tax reversal is now representable with sign-aware CHECKs so SUM(original+reversal) nets to zero for remittance reporting; a same-day post-verification addendum added trg_tax_calculation_jurisdiction_validate_sign, upgrading a disclosed-but-unenforced per-jurisdiction sign requirement to a real DB trigger. pos.sale restored sale_number (register-session-prefixed, honoring offline-first — not a global gapless sequence) and pos.sale_refund_line/sale_refund gained a no-receipt-refund identifier (item_variant_id, sale_line_id now nullable) plus tax-capture columns (Item 10) — pos.sale_refund.sale_id itself stays NOT NULL; the anonymous-walk-in-return question is explicitly flagged for a human decision, not decided or bundled in, logged to OPEN_ITEMS. The fail-closed tender gate was widened to also block 'reward' (renamed chk_sale_payment_no_unbacked_tender_type) and inventory.stock_movement.movement_type gained 'produced' (Item 11). identity.agent_identity gained a kill-switch — status (active/suspended/killed) + suspended_at/suspended_by_actor_id/suspension_reason (Item 12) — one link in a documented precedence chain: tenant status > platform.ai_credit_account status > agent status > agent_duty_grant > skill assignment > role assignment > feature flags. Admin gained an 11th table, setting_definition (Item 13) — the config-key catalog for tenant_setting only (4 other free-form config surfaces deliberately out of scope, logged to OPEN_ITEMS), alongside a canonical money-unit-suffix convention now documented in docs/database/SCHEMA_CONVENTIONS.md §8.1 (_cents default, _minor_units on pos + orders.order_line's Hard Contract 1 columns, _millicents on ai's sub-cent LLM costs). Column deltas: identity 396→400, crm 189→191, tax 37→39, pos 138→143, admin 128→140 (+1 table); inventory unchanged (pure CHECK-widen). Independently verified by 2 adversarial lenses, both CLEAN — every NOTE-level finding addressed same-day, not deferred. Remediation Phase 4 (futureproofing) is now also complete — this closes the entire 4-phase remediation plan (see docs/decisions/PROJECT_DECISIONS.md #40). 7 items (14-20) across 11 modules, plus closing the Phase 3-deferred anonymous-walk-in-return decision: DECIDED = ALLOW, pos.sale_refund.sale_id relaxed nullable + chk_sale_refund_identification requires a linked sale or a documented reason (audit controls — reason quality, attribution, approval — deferred to service layer). Platform reopened a 5th time: +3 tables — accounting_period (this codebase's first EXCLUDE USING gist, preventing overlapping fiscal periods per tenant, paired with a FLAG-NOT-REJECT trigger on pos.sale/sale_refund/register_cash_entry since offline-sync needs a late sale to still land), legal_entity (1:N from tenant, backfilled 1 primary row per tenant, entity_id added to 10 header tables codebase-wide — platform.contract/billing_account, admin.compliance_document, tax.tax_calculation, billing.ar_account/vendor_payable, purchasing.vendor_invoice/purchase_order, orders.order_header, pos.sale), outbox (a genuinely mutable transactional-outbox event table, gen_random_uuid() PK not uuid_generate_v7()). Shared gained 2 tables — exchange_rate (uuid surrogate PK, no composite-PK precedent exists anywhere in this codebase) + payment_terms_catalog (10 seeded rows incl. 2_10_net_30) — plus a cross-table currency-agreement trigger on billing.ar_payment_application; this pass also corrected a pre-existing, Phase-4-unrelated 12-column drift in shared's own stated baseline (108→120, missing entry #21's own FIX 1 review-seam addition). 4 enum→catalog additive-interim additions (Item 17a-d: POS tender types, payment terms, tax jurisdiction levels — with a real CHECK-widen adding 'country', closing the VAT/GST gap — and integration providers; the old CHECK-enum and new catalog FK are disclosed as NOT yet kept in sync). crm.customer.pii_vault_ref + ai.agent_memory's GDPR/erasure seam (Item 18, shares rather than duplicates the existing ein_ref vault-service OPEN_ITEM); admin.custom_field_definition governing the 7 confirmed ungoverned attributes JSONB columns codebase-wide (Item 19); inventory.stock.last_movement_id + this codebase's FIRST CREATE VIEW (stock_reconciliation_shell, Item 20b — deliberately a minimal aggregation-free shell, the real drift-detection join deferred as a named follow-up). Independently verified by 2 adversarial lenses, both CLEAN with zero BLOCKERs — the one substantive finding (no dedicated regression tests existed yet for these items, a real deviation from every prior phase's own practice) was closed same-day: 105 new tests written across all 11 touched modules (10 existing schema-spec files extended, 1 new file for platform) before the phase was declared complete, full apps/api suite green. Next: design the fuller agent-authority enforcement service methods and build out the HTTP controller layer — Identity has none yet; Platform has a partial set. See docs/modules/MODULE_BUILD_STATUS.md for full status. Approvals is now also schema-locked (2026-07-09) — a new cross-cutting module consolidating the tenant-side approval-workflow engine out of Admin (8 tables / 97 cols): approval_workflow, approval_routing_rule, and approval_request MOVED and extended, plus 5 net-new tables (approval_policy, approval_step, approval_delivery, approval_token, approval_event) — the first time in this codebase a table set has moved out of an already-locked module into a module that didn't exist yet, rather than into one that already existed (unlike the admin→platform tenant-identity move, #34/#35). This reverses PROJECT_DECISIONS #34 Section 5's Option B ("stays Admin-internal") now that a codebase-wide sweep found 13 other business-process-approval candidates across 6 modules and zero real consumers of the engine while it sat inside Admin — #34 Sections 1–4 stand untouched. Design verification found and fixed a null-approver bypass (requested_by_actor_id renamed initiator_actor_id and made NOT NULL, plus a new presence CHECK) and a live C8 financial-autonomy-boundary gap — blocks_agent_approver's intended column-level REVOKE did nothing against a broader table-level GRANT, since Postgres ACLs are additive across granularities, fixed by revoking the table-level GRANT and re-granting column-by-column — with all 6 critical guards live-reproduced against the local DB, see docs/decisions/PROJECT_DECISIONS.md #44; schema-only so far — no ApprovalsService yet. Admin was reopened again the same day to give up the 3 moved tables, dropping from 13 tables / 161 cols to 10 tables / 122 cols and losing its "Tenant-Side Approval Engine" concern-group entirely. Identity was reopened a 3rd time the same day (2026-07-09) — an isolated Vrida-operator identity system, replacing the prior identity_user.is_platform_user=true boolean overlay: +identity.operator (14 cols) + identity.operator_role_assignment (8 cols), actor.actor_type CHECK widened to add 'operator' (the pre-existing trg_tenant_user_actor_type_check needed zero modification — 'operator' != 'user' was already rejected, live-reproduced). Both new tables get a belt-and-suspenders security backstop — explicit REVOKE + RLS-enabled-with-zero-policies — live-reproduced against the real authenticated role. AdminAuthGuard now resolves the signed-in operator against identity.operator exclusively (401 = no operator row at all; 403 = a real operator row whose status != 'active', a deliberate behavior change from the old always-403 model). 7 same-pass code retargets (AdminAuthGuard, recordLogin, grantSupportAccess, listSupportAccessGrants, getUser, create-admin-user.ts, 7 test spec files) plus 3 more found and fixed during the build itself (platform.service.ts's activity-log name-resolution join, admin-console-seed.ts's seeded operator, the admin login page's 401/403 copy). Admin login proven end-to-end against the real local Supabase stack: sign-in → POST /admin/auth/login → a support-access route → POST /admin/auth/logout, all against a freshly recreated admin@vrida.app operator. identity_user.is_platform_user is NOT dropped this pass (still readable/writable, no longer read by any auth path) — see OPEN_ITEMS for the drop-trigger. Identity is now 37 tables / 422 cols (up from 35/400). OperatorService.createOperator() (the recommended production creation path) is not built this pass — see docs/decisions/PROJECT_DECISIONS.md #45. A new, coordinated "header/line remediation" effort then began (2026-07-10, design doc vrida-header-line-remediation-design-2026-07-10.md — a systemic sweep for missing header/line reconciliation, unenforced immutability claims, and bare non-composite cross-tenant FKs across the codebase), with pos reopened first: fix #8 closes a real gap where sale_refund_line's long-documented write-once immutability had zero DB enforcement (now real via a REVOKE + a trigger reusing platform.reject_append_only_mutation() verbatim, matching sale_line's own precedent), bundled with 2 opportunistic additions (sale_line gained UNIQUE(id, tenant_id), a prerequisite for a still-deferred Orders-side composite FK; sale_refund_line.sale_line_id was upgraded from a bare to a composite FK) — table/column counts unchanged (still 10 tables / 160 cols), see docs/decisions/PROJECT_DECISIONS.md #46. purchasing was reopened next under the same effort (2026-07-10, 2nd of 3) for 3 bundled fixes: chk_purchase_order_line_quantity_rollup on purchase_order_line (fix #10, must-fix — mandatory pre-migration audit found zero violating rows live, table was empty); line_number partial-unique indexes on purchase_receipt_line/vendor_invoice_line/vendor_return_line/purchase_order_template_line (fix #12 — mandatory dedup audit found zero duplicates on all 4, also zero-row at the time); and a brand-new table, vendor_credit_line (fix #4 — a write-once, 8-column per-line decomposition of vendor_credit, with 3 composite (col, tenant_id) FKs into vendor_credit/vendor_invoice_line/vendor_return_line, each of which needed its own new prerequisite UNIQUE(id, tenant_id) confirmed missing, plus a header-is-truth reconciliation trigger, trg_vendor_credit_line_validate_against_credit, rejecting any line write that would push SUM(lines.amount_cents) over the parent's own credit_amount_cents). purchasing is now 17 tables / 410 cols (up from 16/402), see docs/decisions/PROJECT_DECISIONS.md #47. Platform reopened last under the same effort (2026-07-10, 3rd and final of 3: POS → Purchasing → Platform) for fix #1: a new table, subscription_invoice_line (10 cols, write-once per-line decomposition of a subscription_invoice's total), reconciled LINES-ARE-TRUTH via a new trigger, trg_subscription_invoice_line_sync_totals (the opposite pattern from purchasing's own header-is-truth vendor_credit_line), which required a new prerequisite UNIQUE(id, tenant_id) on subscription_invoice for the line table's composite FK. Design-phase independent verification caught and corrected the single most serious finding of the whole 3-module effort — the original draft would have installed the sync trigger before the backfill-and-verify steps, which since the trigger unconditionally overwrites the header from SUM(lines) would have made the "verify against stored totals" check tautological (always passing, even where the original data genuinely diverged); the corrected order snapshots each invoice's pre-backfill totals first and installs the trigger only after backfill and verification against that snapshot. Pre-migration inspection also disclosed, not silently fixed, that all 5 pre-existing live invoices held an empty line_items blob despite non-zero subtotal_cents — a genuine pre-existing data-quality gap logged via RAISE NOTICE; line_items is now deprecated in place. platform is now 27 tables / 450 cols (up from 26/440), see docs/decisions/PROJECT_DECISIONS.md #48. This closes out the entire 3-reopen header/line remediation effort (POS, Purchasing, Platform). A second batch of the same header/line remediation effort then began the same day (2026-07-10), with inventory reopened first for fixes #5 and #9: a new table, stock_adjustment_batch (10 cols, a header grouping multiple stock_adjustment_request rows for joint review, reusing stock_count's own status-as-review-seam convention plus a partially_approved value), with stock_adjustment_request gaining a nullable batch_id composite FK into it (fix #5); and stock_count_line gaining reconciled_at/reconciled_by_actor_id plus a NULL-safe CHECK and a bespoke conditional-immutability trigger (trg_stock_count_line_lock_after_reconciled) — deliberately NOT the shared blanket platform.reject_append_only_mutation(), since a count line must stay editable pre-reconciliation (fix #9). inventory is now 25 tables / 351 cols (up from 24/338), see docs/decisions/PROJECT_DECISIONS.md #49. Documenting this reopen also surfaced 2 disclosed-not-fixed gaps, logged to OPEN_ITEMS: stock_movement_line still lacks UNIQUE(id, tenant_id) despite the design doc naming it as belonging to this reopen (it's the prerequisite for Purchasing's own still-deferred fix #7, movement-line linkage, itself deferred to the upcoming Receiving extraction), and stock_adjustment_batch.updated_at has no maintaining trigger, unlike every sibling soft-delete table in this module. orders was reopened next in this same second batch (2026-07-10) for fix #6 — order_line.sale_line_id, a new nullable composite FK → pos.sale_line(id, tenant_id) (order_line_sale_line_id_tenant_fkey) extending the header-level pos-fulfillment seam (order_header.fulfilled_sale_id) down to the line level, link-don't-convert and mirroring that same asymmetry (no reciprocal column on POS's side either); the prerequisite UNIQUE(id, tenant_id) on pos.sale_line had already been laid in the first batch's own POS reopen (fix #8) anticipating exactly this. orders is now 7 tables / 175 cols (up from 174), see docs/decisions/PROJECT_DECISIONS.md #50. Independent verification confirmed the core fix fully correct and disclosed 2 findings, neither invalidating it: a test-suite cleanup bug (the new test section's afterAll originally attempted a hard DELETE against append-only pos.sale_line, since fixed via a tryDelete() helper mirroring pos-schema.spec.ts's own precedent) and a new, previously undisclosed finding — 5 bare (non-composite) FKs still sit inside orders' own internals (order_line.order_id/order_payment.order_id/order_fulfillment.order_id → order_header, order_fulfillment_line.order_fulfillment_id → order_fulfillment, order_template_line.order_template_id → order_template), none of whose parent tables yet carry UNIQUE(id, tenant_id) — logged to OPEN_ITEMS as a future Orders reopen candidate, not fixed in this pass. billing was reopened next in this same second batch (2026-07-10) for fix #2 — a new table, ar_charge_line (12 cols, write-once per-line decomposition of ar_charge.charge_amount_cents), reconciled HEADER-IS-TRUTH via trg_ar_charge_line_validate_against_charge (mirroring purchasing.vendor_credit_line's own precedent), with 2 new composite FKs (→ billing.ar_charge, → tax.tax_calculation) each needing a new prerequisite UNIQUE(id, tenant_id) — the tax.tax_calculation one a cross-module prerequisite this fix needed and added (no column/table impact on tax itself). Unlike Platform's own subscription_invoice_line fix, no JSONB blob existed to convert — rows were RECONSTRUCTED by joining back through ar_charge.source_ref to pos.sale/pos.sale_line; a mandatory pre-migration dry run found zero live pos-sourced ar_charge rows share a tenant with any seeded pos.sale row (disconnected seed datasets, not a reconciliation ambiguity), and the full reconstructive backfill query was still written and actually run, confirmed INSERT 0 0. billing is now 10 tables / 176 cols (up from 164), see docs/decisions/PROJECT_DECISIONS.md #51. Independent verification found the fix fully correct and disclosed 2 findings, neither invalidating it: ar_charge.tax_calculation_id was ALSO found to be a bare FK (closed in a LATER, separate migration/docs pass, not credited to this fix) and a minor, informational rounding-methodology note (unreachable in today's data — every live pos.sale row has exactly 1 line). identity was reopened a 4th time next in this same second batch (2026-07-10) for fix #3 — a new table, invitation_site_assignment (6 cols, pre-acceptance staging of intended site access, mirroring user_site_assignment's own shape; site_id deliberately carries no FK, matching user_site_assignment.site_id's own pre-existing, disclosed gap), plus user_site_assignment.created_from_invitation_site_assignment_id (a nullable composite FK back to the new table, corrected from the design draft's own bare-FK mistake before build). identity.invitation gained a prerequisite UNIQUE(id, tenant_id), and its site_assignments JSONB is now deprecated in place (confirmed zero live rows, so no backfill was needed or possible). identity is now 38 tables / 429 cols (up from 422), see docs/decisions/PROJECT_DECISIONS.md #52. A mandatory pre-migration orphan-check audit found ONE live orphaned row in user_site_assignment.site_id (absent from multi_loc.site) — because of this, the optional bundle wiring all 3 site_id-shaped columns to real multi_loc.site FKs was deliberately NOT taken; this is flagged for a human decision (null out the orphan vs. correct the reference), explicitly not resolved — see OPEN_ITEMS. Independent verification found zero findings of concern and zero scope creep. This second batch's fifth and final item then landed the same day: 3 standalone bare-cross-tenant-FK fixes in one migration, each closing a gap disclosed during an EARLIER fix's own independent verification rather than its own build — platform.payment.invoice_id (bare since fix #1, PROJECT_DECISIONS #48), purchasing.vendor_invoice_match.vendor_invoice_line_id (bare since the first batch's own Purchasing reopen, fix #4, PROJECT_DECISIONS #47), and billing.ar_charge.tax_calculation_id (bare since fix #2, PROJECT_DECISIONS #51) all upgraded to composite (col, tenant_id) FKs, each reusing a UNIQUE(id, tenant_id) prerequisite that already existed from its respective earlier fix — no new prerequisites needed. vendor_invoice_match.purchase_receipt_line_id deliberately stays bare (purchase_receipt_line still lacks UNIQUE(id, tenant_id), out of this fix's own named scope) — disclosed via a code comment and logged to OPEN_ITEMS. The standout independent-verification finding: vendor_invoice_match had zero live rows at build time, so the cross-tenant-rejection claim couldn't be reproduced by the build itself — the independent verifier built a full 2-tenant fixture chain from scratch (vendor → PO → PO-line → receipt → receipt-line, plus vendor_invoice → vendor_invoice_line) and proved it for real, the one claim genuinely unproven until that pass. All 3 fixes are constraint-shape-only — platform/purchasing/billing table/column counts are unchanged (27/450, 17/410, 10/176 respectively), see docs/decisions/PROJECT_DECISIONS.md #53. This closes the entire 2-batch Header/Line Remediation effort (batch 1: POS/Purchasing/Platform, #46-48; batch 2: Inventory/Orders/Billing/Identity/this fix, #49-53) — every fix in both batches' own scoped lists is now done; remaining named gaps (Purchasing's fix #7/#11 pending the Receiving extraction, the multi_loc site_id FK-wiring bundle pending a human decision, Orders' 5 internal bare FKs, purchase_receipt_line's own missing UNIQUE(id, tenant_id)) are disclosed follow-on candidates for a future reopen, not outstanding work from either batch. The multi_loc site_id FK-wiring bundle was then itself closed the same day (2026-07-10) — a direct follow-up to entry #52's own flagged human decision. The orphaned user_site_assignment row was investigated: its tenant had exactly 5 rows total anywhere in the schema, all boilerplate/system rows, zero business data — isolated dev-seed fixture junk, independently confirmed via a DB-wide tenant-isolation scan, not just the original claim. The row was DELETED. user_site_assignment.site_id, tenant_user.default_site_id, and invitation_site_assignment.site_id are now real composite FKs → multi_loc.site(id, tenant_id) (multi_loc reopened for the 1st time since its 2026-06-29 lock, gaining the prerequisite UNIQUE(id, tenant_id); identity reopened a 5th time). Pure constraint-shape change — multi_loc stays 1 table / 37 cols, identity stays 38 tables / 429 cols, see docs/decisions/PROJECT_DECISIONS.md #54. platform.tenant.primary_site_id and identity.user_permission_override.scope_id remain genuinely deferred, and a previously-untracked 4th sibling surfaced during this pass's own docs update, identity.access_request.requested_scope_id (same shape/target, never named in any prior audit) — none of these 3 were touched, all logged to OPEN_ITEMS. Independent verification: CLEAN, zero findings, including independent re-justification of the irreversible DELETE via its own from-scratch DB scan. The Receiving module extraction (Part B of the same design doc) is now also built (2026-07-10, same day) — a 3-module coordinated reopen (receiving new, purchasing reopened, inventory reopened a 3rd time), the 2nd instance in this codebase of moving tables out of an already-locked module into a brand-new one (the 1st was approvals out of admin, #44). purchasing.purchase_receipt/purchase_receipt_line moved to the new receiving schema, renamed goods_receipt/goods_receipt_line (33/29 cols, up from 30/27), folding in header/line remediation fixes #7 (movement-line linkage) and #11 (over-receipt tolerance, a new trigger reading admin.tenant_setting/setting_definition with site>tenant>catalog precedence). The design's own pre-build independent verification had already caught 2 BLOCKERs (a capped-write-back fix keeping the tolerance trigger's "flag, don't block" path from colliding with fix #10's own zero-slack CHECK; and a 2nd, initially-missed vendor_return_line dependency alongside vendor_invoice_match, both retargeted to the moved table in the same migration) — both verified correct via live-reproduction during this build. The build's own live-reproduction pass (32 guard assertions, all pass) then found and fixed 3 MORE real bugs beyond those 2 BLOCKERs: a missing UNIQUE(id,tenant_id) on goods_receipt itself (the migration's first apply attempt failed on it), a CHECK-widen-vs-backfill ordering bug in the admin.custom_field_definition vocabulary migration (2 failed attempts before landing on drop-backfill-readd), and a trigger crash-on-corrupted-catalog-data gap (the tolerance trigger's ::numeric cast had no defensive handling — fixed live with a BEGIN/EXCEPTION wrapper). purchasing is now 15 tables / 353 cols (down from 17/410), inventory stays 25 tables / 351 cols (constraint-only). Two separate, genuinely independent lock-gate verification passes (a Section-4 re-audit and an adversarial from-scratch reproduction) then converged on the same further gap from different angles: the capped write-back was only a documented convention for the not-yet-built ReceivingService, not actually DB-enforced — the rollup CHECK protected against silent corruption either way, but neither the absorbed_qty formula nor the cap itself lived in the trigger. Fixed a 2nd time: the trigger now derives over_short_qty itself and performs the PO-line write-back atomically, in the same invocation, closing the gap for good; its own UPDATE scope was corrected from OF over_short_qty to OF accepted_qty to match (the derived output isn't the column to watch — its input is). The adversarial pass also surfaced one previously-undisclosed finding: vendor_return_line carries 3 other bare FKs into tables that gained their UNIQUE(id,tenant_id) prerequisite in this same migration, now logged to OPEN_ITEMS (genuinely deferred, not fixed this pass). admin-schema.spec.ts's (I2b) fixture was corrected to the new CHECK vocabulary; full suite 918/918. New regression suite receiving-schema.spec.ts (42/42, +2 for the strengthened trigger); purchasing-schema.spec.ts (59/59, down from 60) and inventory-schema.spec.ts (59/59, up from 55) both updated. Receiving is now locked (module #22); purchasing and inventory re-locked. See docs/decisions/PROJECT_DECISIONS.md #55. Consumer, Rewards, and Offers (modules #23–#25) are now also schema-locked (2026-07-11) — 3 new schemas (22 tables total) — the first cross-tenant-boundary modules in this codebase (a dedicated consumer_authenticated Postgres role, structurally separate from the merchant authenticated role), built with a corrected mechanism (a consumerDB() connection helper mirroring the existing tenantDB() pattern, not a Supabase Auth Hook), closing the concurrency-race and AI-margin-bypass findings an earlier adversarial design verification surfaced; schema-only so far, no service layer yet, see docs/decisions/PROJECT_DECISIONS.md #56. Files (module #26) is now also schema-locked (2026-07-11, same day) — the generic file-metadata registry, another module whose v1 design (3 tables/43 cols, locked 2026-06-11) was never actually built until now: 6 tables / 89 cols. Cloudflare R2 is the sole storage of record; AWS S3 is used only as transient staging for AWS Textract's async extraction path (never a durable tier this module models) — the full storage-architecture decision, including the cost model that drove it, is recorded separately in docs/decisions/PROJECT_DECISIONS.md #58, ahead of the build itself in #59. Every v1 table/column survives (2 disclosed renames — r2_key/r2_bucket→storage_key/storage_bucket, file_storage_usage→tenant_storage_usage; 1 widened CHECK — file.status gains ready; 1 FK retarget — uploaded_by_user_id→uploaded_by_actor_id onto identity.actor) plus 3 genuinely new tables: attachment (the polymorphic many-to-many join, entity_type a closed CHECK enum mirroring approvals.approval_request.source_module's own precedent) and the "vector spine," built now and populated later — document_index + document_chunk (embedding vector(1024), sized for Amazon Titan Text Embeddings V2, left NULL until semantic search activates; FTS works immediately via a generated search_vector column). document_chunk is deliberately NOT append-only since embedding is updated post-insert; re-indexing soft-deletes the prior chunk generation rather than hard-deleting it. files.file carries UNIQUE(id, tenant_id) from this build's own day one — a first for this codebase, built in rather than retrofitted via a later reopen, since 8 other modules (admin, crm, pos, receiving, inventory, ai) hold forward-ref columns waiting to become real composite FKs against it (a dedicated follow-up bundle, not part of this build). Foundation-layer discipline is the load-bearing design constraint here: file.consumer_id and file_access_grant.grantee_customer_id are deliberately loose, unenforced columns, since a foundation-layer schema must never FK into the consumer or business layers — independently confirmed live via pg_constraint that zero such FK exists. The dual-principal (merchant/consumer) boundary closes at the GRANT layer, not just RLS: consumer_authenticated gets zero grant of any kind on the files schema; the sole consumer read path is consumer.get_files_for_consumer(p_consumer_id), owned by the consumer schema itself (the permitted calling direction), a parameter-scoped SECURITY DEFINER function mirroring consumer.get_cross_tenant_activity()'s own established shape exactly. A 3-lens independent adversarial verification of the design caught a real layer-boundary violation in the pre-verification draft (the wrong FK direction, the wrong function schema placement, and a factually non-executable join) and fixed all three before the schema was ever built; a further mandatory evidenced lock-gate verification then ran against the completed build itself. Schema-only so far — no FilesService yet. Rewards and Offers were then both reopened again the same day (2026-07-11, Pass 1 of a 2-pass effort whose Pass 2 builds the returns module) to fix their reversal-validation triggers: rewards.sync_loyalty_account_balance() had hard-enforced exact-full-negation-only on a reversal (a genuine partial return was rejected outright), and offers.check_and_sync_offer_budget() had no magnitude check at all on a reversal — a real, independent bug live-reproduced by temporarily reinstalling the pre-fix function: a $10 redemption "reversed" by a fabricated $1,000 row succeeded, silently consuming an unrelated redemption's budget. Both are now fixed via 2 new small mutable tracker tables (rewards.loyalty_point_ledger_reversal_tracker, offers.offer_redemption_reversal_tracker) — since both loyalty_point_ledger and offer_redemption are append-only, a maintained counter can't live on the ledger/redemption row itself — each capping cumulative reversals against one original atomically (the same proven UPDATE ... WHERE ... RETURNING row-locking pattern used elsewhere in this codebase, not the read-then-check-then-write shape this codebase already found racy once before). Proportional/partial reversal is now a first-class capability in both modules, live-reproduced sequentially and under genuine 3-way concurrent load (rewards: 2 of 3 concurrent partial reversals correctly succeed, capped; offers: same, plus the exact pre-fix bug scenario now correctly rejected). rewards is now 7 tables / 109 cols (up from 102), offers is now 7 tables / 123 cols (up from 115, of which +1 is a pre-existing, this-fix-unrelated column-count drift disclosed rather than silently corrected) — see docs/decisions/PROJECT_DECISIONS.md #60. Full apps/api suite: 1052/1052. Both modules remain schema-only — no RewardsService/OffersService yet. Pass 2 of this same 2-pass session then built the returns module (module #27) — the customer RMA module, 9 tables / 152 cols (return_authorization 29, return_authorization_line 28, return_source_line_tracker 10 — a new aggregate-cap tracker closing a real design-phase-verification finding, return_resolution 21, return_resolution_line 8 append-only, return_receipt 14, return_receipt_line 16, return_reason 10, warranty 16 revived from v1's pos.guarantee) — per the design proposal's own corrected, 3-lens-verified blueprint. Because Pass 1 landed first in the same session, proportional loyalty/offer clawback — the design's own Block 4 deferred item — is now a REAL, WORKING capability rather than out-of-scope: return_resolution links to 'reverse'-typed rows written via rewards/offers' own (now proportional) mechanism, live-reproduced end-to-end (buy 5 shrubs with 100 points earned + a $10 offer redeemed → return 2 of 5 → exactly 40 points clawed back + $4.00 of offer budget released, additive on a second partial return, over-clawback correctly rejected). Also live-reproduced: proportional basket-discount allocation ($150 basket/20% off/return the $100 line refunds $80, not $100); the aggregate over-return cap across multiple RMAs against one sale line, both sequentially and under a genuine 3-way concurrent race (exactly 2 of 3 succeed, correctly capped); idempotent inventory posting with a BLOCK-not-flag (not receiving's own "flag") over-receipt tolerance policy; the no-physical-receipt plant-guarantee claim flow (resolves as a replacement, credited_qty reaches authorized_qty while received_qty stays 0 via independent peer counters); restocking-fee arithmetic (fee reduces the refund, never separately taxed); unreferenced/blind returns; and cross-tenant RLS rejection. 5 small companion reopens bundled into the same migration (pos/orders each gain 2 UNIQUE(id,tenant_id) constraints; inventory/approvals/files each get a CHECK widened for a returns-related value) — the design's own originally-planned rewards/offers reopens are obsolete given Pass 1's fix and were deliberately not applied. See docs/decisions/PROJECT_DECISIONS.md #61. Full apps/api suite: 1083/1083 (serially; one pre-existing, unrelated cross-file concurrency flake intermittently reproduces under parallel workers). A 3-lens independent lock-gate verification then found and fixed 2 real issues before locking: return_authorization/return_authorization_line.reason_id were bare (non-tenant-scoped) FKs into the tenant-scoped return_reason catalog, live-exploited for cross-tenant reference despite the module's own stated composite-FK convention — now real composite (reason_id, tenant_id) FKs; and validate_and_apply_resolution_line()'s cap checks were not NULL-safe (masked only by column-level NOT NULL, unlike the sibling check_and_reserve_source_line()'s NULL-safe-by-construction UPDATE...WHERE...RETURNING idiom) — now guarded by an explicit NULL check. The verification also disclosed, not fixed, that the per-line discount/restocking-fee derivation formulas are DB-unenforced beyond non-negativity and the aggregate ceiling — matching the same convention already used by pos.sale_line/orders.order_line, flagged for whoever builds ReturnsService next. returns is now schema-locked (2026-07-11) at 9 tables / 152 cols (unchanged — both fixes were constraint/trigger-only); full suite re-verified green at 1088/1088. Schema-only — no ReturnsService yet. This closes the entire 2-pass session. Semantics is now also schema-locked (2026-07-14) as Phase 3 of a 6-phase agents-v2/v3 build authorization (Phase 1 reopened platform for the module registry + AI-capacity layer, PROJECT_DECISIONS #62; Phase 2 reopened ai for the model-registry/deployment/prompt/routing layer, PROJECT_DECISIONS #63) — a brand-new foundation-layer schema with zero v1 antecedent (14 tables / 114 cols: approved_function_registry, metric_definition/_version/tenant_metric_binding/metric_dependency, entity_definition/entity_alias, dimension_definition, goal_definition/constraint_definition/tenant_goal_binding/tenant_constraint_binding, attribution_model_definition/_version), the shared business ontology future agents/signals modules will read from rather than each inventing their own metric/goal/constraint vocabulary, featuring the I2 approved-function-registry supply-chain-hole-closing re-verification pattern (verify_function_still_matches_approval() re-resolves and re-hashes a registered function fresh on every call, never trusting a one-time approval or a cached OID) and EXCLUDE USING gist hard-reject overlap prevention on 3 tenant-scoped binding tables (a genuine, disclosed departure from platform.accounting_period's own flag-not-reject pairing, since no offline-sync reason applies to a synchronous tenant-admin action like setting an authoritative binding); a Section 4 self-audit found and fixed 2 real gaps before lock (3 lifecycle_status columns missing their CHECK entirely; tenant_goal_binding/tenant_constraint_binding missing created_at/updated_at + a maintaining trigger their sibling tenant_metric_binding already had, which is why the schema landed at 114 cols not the original 110), and tenant_goal_binding/tenant_constraint_binding.module_id/.site_id are deliberately left bare with no FK, a disclosed judgment call logged to OPEN_ITEMS, not a deferred-FK debt of the ordinary kind; schema-only, no SemanticsService yet; full apps/api suite green at 1150/1150 — see docs/decisions/PROJECT_DECISIONS.md #64. Signals is now also schema-locked (2026-07-15) as Phase 4 of 6 of the agents-v2/v3 build, the highest-risk phase — a brand-new foundation-layer schema (11 tables/111 cols) implementing partitioned bitemporal feature/forecast/outcome storage with 4 live-reproduced critical guards (SECURITY-DEFINER tenant-isolation via platform.current_tenant_id(), the outcome_observation/outcome_authority split-authority pattern for partition-safe exactly-one-authoritative-observation enforcement, assigned_at forgery prevention + a retroactive-exposure-contamination race fix, and enforced bitemporal reads); schema-only, no SignalsService yet; full apps/api suite green at 1172/1172 — see docs/decisions/PROJECT_DECISIONS.md #65. Phase 5 (agents, module #28, ~30 tables) is next. Agents (module #28) is now also schema-locked (2026-07-16) as Phase 5 of 6 of the agents-v2/v3 build — the full v1 orchestration baseline (21 tables: agent_task, agent_thread, agent_event_log, agent_schedule, agent_trigger, agent_eval_suite/_run/_case, agent_performance_profile, agent_shadow_run/_decision, agent_autonomy_profile, agent_action, rollback_recipe/_execution, tool_catalog, agent_tool_grant, agent_catalog_entry/_required_tool, tenant_agent_deployment, agent_incident) merged with v2's amendments (A1-A8) and v3's corrections (BLOCKER 1-8, I1-I8) into 47 tables / 475 cols, live-reproducing 8 distinct trigger-backed guards (disclosed honestly as 8, not forced down to the originally-named 7): E3 (atomic task claim + fencing, lease renewal never bumps the fencing token), BLOCKER7 (kill-switch history/state split + resume-safe propagation + a Section-4-audit-found RLS gap fix), BLOCKER4+I1 (the saga gate enforced against real writes rather than a static toolset-shape proxy, plus agent_action/agent_decision consistency), the runaway-ceiling guard (BLOCK not flag, closing v1's own Gap 1), A5 corrected (the kill-switch check fires on UPDATE OF status — i.e. task claim — not just INSERT), A3+BLOCKER5+A8R4 (skill activation: certification + duty-grant authority + spend-ceiling policy, folded into one function, validate_skill_activation()), the A2b retirement-version-check trigger (reject_retired_decision_context_versions), and T1 (a 6th guard added during the Section 4 audit pass specifically to test the A2b trigger). The build's own live guard-reproduction (not the read-only audit) caught 2 genuine PL/pgSQL bugs: a record-typed variable's IS NOT NULL check silently failing when used directly as an IF condition — even on a fully-populated, genuinely non-null record — which had swallowed the entire A8 Rule 4 spend-ceiling check inside validate_skill_activation() until rewritten to scalar variables checked individually; and SELECT MIN(...) ... FOR UPDATE being syntactically rejected by Postgres ("FOR UPDATE is not allowed with aggregate functions"), fixed by splitting the lock (a plain PERFORM ... FOR UPDATE) from the aggregate (a separate SELECT MIN(...) INTO over the now-locked rows). The same-day Section 4 self-audit separately found and fixed a live security gap — agents.kill_switch_event had tenant_id + GRANT but zero RLS policies, letting any tenant session read every other tenant's kill-switch history and forge a kill/suspend/resume event — closed with the same 2-policy mixed-scope RLS pattern already used by agent_eval_suite/rollback_recipe, live-reproduced (cross-tenant SELECT returns 0 rows, forged INSERT rejected, legitimate own-tenant INSERT succeeds) plus a new regression test (I5). The same day also closed 3 pre-existing forward-ref OPEN_ITEMS rows now that Phase 5 landed their targets — ai.routing_policy.workload_class_id → agents.workload_class(id) (plain FK) and signals.outcome_observation/outcome_authority.agent_action_id → agents.agent_action(id, tenant_id) (composite FKs), all 3 confirmed zero-orphan pre-migration, which required retrofitting Phase 4's own signals-schema.spec.ts (previously 5 hardcoded placeholder UUIDs) with a real actor → agent_identity → decision_context_snapshot → agent_execution → agent_decision → agent_action fixture chain. Full apps/api suite green at 1198/1198 (serially; a known, pre-existing connection-pool-exhaustion flake in database/__tests__/rls-cross-tenant.spec.ts fails only under parallel workers, unrelated to this build); schema-only so far — no AgentsService yet — see docs/decisions/PROJECT_DECISIONS.md #66. Identity was reopened a 6th time (2026-07-17) for Phase 6 of 6 of the agents-v2/v3 build — CLOSING THE ENTIRE BUILD — dropping the now-superseded identity.agent_skill/agent_skill_assignment (0 and 13-test-debris live rows respectively, cleaned up in the same migration, not real data) and cutting IdentityService's skill methods over to agents.skill_definition/skill_version/agent_skill_assignment at skill-VERSION granularity (skillId renamed skillVersionId throughout, listSkillCatalog's return shape flattened to {id, code, name, lifecycleStatus}, zero live HTTP consumers affected, grep-confirmed), leaving identity at 36 tables / 410 cols (down from 38/429); the same reopen also built a new agent_reader Postgres role (mirroring consumer_authenticated) plus an agentReaderDB() connection helper, narrowly GRANTed to files.document_chunk/document_index SELECT and the 3 signals.*_as_of() EXECUTE functions, catching and fixing a real gap live during the build — the pre-existing authenticated-scoped RLS policies on those files tables don't extend to a new role, so 2 dedicated agent_reader-scoped SELECT policies were added — plus a new ESLint rule banning adminDb/tenantDB/raw set_config from future src/agents/** code paths; honestly disclosed that agent_reader/agentReaderDB() have zero real call sites yet since no AgentsService exists, logged to OPEN_ITEMS rather than treated as done; full apps/api suite green at 1205/1205 — see docs/decisions/PROJECT_DECISIONS.md #67. A dedicated Phase 1: Security & Integrity Remediation pass then ran (2026-07-18) — a live-DB structural sweep across all 25 business schemas, governed throughout by "can a caller with ordinary privileges do the wrong thing anyway?", with every claim live-reproduced via before/after demonstration. 10 items: platform.polymorphic_target_registry lockdown (root-caused to a 2026-07-08 ALTER DEFAULT PRIVILEGES statement silently over-granting future tables); a 25-schema GRANT/REVOKE structural sweep (3 groups — 2 GRANT-additivity bugs on already-append-only tables, 8 reference catalogs narrowed to SELECT-only, 4 cross-tenant-leak identity/tenant tables fully REVOKEd from authenticated, confirmed zero behavioral impact since IdentityService/PlatformService use getAdminDb()); append-only enforcement completed on 16 more tables (found via a "no updated_at" structural sweep, with 3 candidates correctly reclassified "validated-mutable" instead after column-shape inspection — now codified as SCHEMA_CONVENTIONS.md §20); consumer.event consent enforcement via the pre-existing consumer_merchant_link.consumer_opt_in seam; a new platform.processor_catalog reference table replacing closed-vocabulary CHECKs on payments.payment_intent.processor/tax.tax_calculation.provider (platform now 36 tables / 524 cols, up from 35/518 — the only table/column-count change this phase); a cross-module bare-FK audit fixing 2 judged-safe cases (closing the exact agents.agent_skill_assignment.agent_identity_id gap PROJECT_DECISIONS #67's own independent verification had deferred) while disclosing a broader 115-row finding and a 99.2%-orphaned identity_access_event.session_id ruling, both logged to OPEN_ITEMS rather than force-fixed; a new drift CI gate (packages/db/scripts/drift-check.ts + .github/workflows/db-drift-check.yml) diffing live DB metadata against Drizzle/docs, live-reproduced by deliberately reintroducing a real bug and confirming the gate caught it; an ai barrel-export fix (14 missing table re-exports) and a tenant_module_activation RLS Drizzle-drift fix; and 4 missing set_updated_at triggers. shared schema's own write-permission lockdown is deliberately deferred to this same effort's Phase 2, not overlooked — its 12 tables lack tenant_id on most rows, needing its own dedicated design pass rather than this phase's tenant-scoped GRANT/REVOKE patterns. Full apps/api suite unchanged at 1205/1205. See docs/decisions/PROJECT_DECISIONS.md #68. A same-day pre-Phase-2 confirmation pass (2026-07-18) then closed the 2 loose ends #68 disclosed plus triaged the 115-bare-FK finding: the drift CI gate (Item 7) was found to never actually fire or block today — this repo lands 100% of changes via direct commits to main (zero PRs ever, confirmed via gh pr list), main has zero branch protection, and the CI's vanilla Postgres service can't even get past migration #1 of 90 (missing Supabase-provisioned roles/extensions) — 3 stacked gaps logged to OPEN_ITEMS as decisions for Srini, while the underlying drift-check.ts script itself was independently re-confirmed genuinely fail-closed. The identity_access_event.session_id 99%+ orphan rate was fully root-caused: 100% test-fixture debris from identity-session.spec.ts's own teardown hard-deleting identity_session rows (violating that table's own "permanent audit history" design) — fixed (teardown corrected, identity.md's DR-21 corrected from its original, never-true "enforced FK + index" claim). The 115-bare-FK estimate was reconciled to a real, re-derived 230 and categorized into 5 buckets (12 correctly-bare global-parent references, 13 + 27 cheap-but-dormant agent_identity_id/site_id fixes, 61 cheap missing-composite-FK fixes, and 117 expensive ones needing a new UNIQUE(id,tenant_id) on 39 parent tables first) — 2 of the 117 turned out to be concrete live-code gaps in PlatformService (recordInvoice/createSubscription inserting a caller-supplied cross-tenant-capable id with zero check) and were fixed immediately with regression tests; the remaining ~228-row finding is recommended as its own future dedicated multi-batch phase, not folded into the nursery-extraction Phase 2. Full apps/api suite: 1209/1209. See docs/decisions/PROJECT_DECISIONS.md #69. Phase 2 (Nursery Vertical Extraction + shared Lockdown) is now complete (2026-07-18, same day) — three independent external design reviews had found nursery-specific concepts inside the vertical-neutral core; this phase relocated them via one coordinated migration, zero new business capability. Two new schemas: nursery_ref (global, Vrida/AI-curated botanical reference — climate_zone/plant/plant_common_name/plant_climate_zone, moved verbatim out of shared via ALTER TABLE ... SET SCHEMA) and nursery (tenant-scoped extension — item_profile replacing the dropped inventory.item.plant_id, site_profile replacing the dropped multi_loc.site.climate_zone_code + 3 nursery-only site_type values). inventory.item.item_type neutralized to ('product','service','kit') (the hard_good/plant values folded into product); consumer.consumer_interest.interest_type generalized to ('category','item','topic'); offers.offer_targeting_rule.growing_zone_code renamed attribute_ref ('growing_zone' → 'attribute_match'), its FK to shared.climate_zone dropped — the generic offer engine no longer structurally depends on any vertical's reference data. returns.warranty was inspected and confirmed already fully generic — nothing extracted. shared and nursery_ref are now both write-locked down to authenticated (REVOKE INSERT/UPDATE/DELETE + ALTER DEFAULT PRIVILEGES for future tables), live-reproduced via a genuine A/B (a scoped, rolled-back GRANT proving the REVOKE, not RLS, is what rejects the write). The governing rule — a generic core table must never gain a vertical-only column/FK when a tenant-scoped extension table can represent the same relationship — is now codified in docs/database/SCHEMA_CONVENTIONS.md §21. Full apps/api suite: 1236/1237 (the 1 failure is a confirmed pre-existing, unrelated admin-catalog.spec.ts pagination flake). See docs/decisions/PROJECT_DECISIONS.md #70. Phase 3 (Gift Card + Store Credit — the stored-value money-ledger build) is now also complete (2026-07-18, same day) — the first phase of this remediation sequence to add real business capability, unblocking a launch blocker: pos.sale_payment's fail-closed gate had rejected gift_card/store_credit tenders since 2026-07-07 ("until their subsystems exist"). Billing was reopened for 6 new tables (10→16 tables, 176→260 cols): gift_card (bearer instrument, code_hash SHA-256-only — plaintext never stored — anonymous-capable, expires_at default NULL), gift_card_transaction (append-only ledger, v7 PK), gift_card_reversal_tracker, store_credit_account (customer-tied, one per customer — TWO instruments, never merged, honoring v1's own named GUARD), store_credit_transaction, store_credit_reversal_tracker, plus the stored_value_liability view (security_invoker = true). Ledger-first with trigger-maintained cached balances (billing.sync_gift_card_balance()/sync_store_credit_balance() — the atomic row-locking UPDATE...WHERE...RETURNING idiom, never read-then-check-then-write): negative balance structurally impossible at 3 layers, live-reproduced including a genuine 3-way concurrent overdraw race (naive shape shown double-spending; real trigger let exactly 1 of 3 land) and a doubly-capped clawback (remaining balance + per-original tracker cap). POS wired in the same migration: real composite FKs on sale_payment.gift_card_id/store_credit_id (the OPEN_ITEMS hard contract — FKs replace the gate, not just a CHECK drop), the gate narrowed to payment_method != 'reward' (reward stays blocked — Part 0.3 confirmed the rewards module does NOT back a reward TENDER: no pos linkage, no points→money bridge, redemption structurally forced through reward_option), the companion CHECK strengthened to full coherence, and a new online-only CHECK (v1's offline-first boundary). Returns wired: return_resolution.store_credit_transaction_id (real composite FK + presence CHECK, closing the disclosed refund-asymmetry; store_credit_reference deprecated in place). crm.customer gained UNIQUE(id, tenant_id) (prerequisite; counts unchanged, 13/193 — note this corrects the stale 191 earlier in this file). Returns is now 9 tables / 153 cols (+1). The Section 4 self-audit found and fixed 2 real issues pre-lock (tracker-DELETE clawback-cap reset → REVOKE DELETE; stranded liability on partial void/expire → trigger must-zero gate + chk_gift_card_terminal_zero_balance), both live-reproduced A/B. Cash-out and gift-card-expiry are tenant policy settings (admin.setting_definition stored_value/* rows), never schema assumptions; escheatment has no schema surface (logged to OPEN_ITEMS). Full apps/api suite: 1281/1282 (the 1 failure is the same pre-existing admin-catalog.spec.ts flake). Schema-only — no BillingService/PosService stored-value methods yet. See docs/decisions/PROJECT_DECISIONS.md #71. A read-only capability-gap validation pass then ran (2026-07-19) against all 15 module specs, confirming 6 real schema gaps (no category/brand-scoped pricing rules; no held/parked POS carts; no orders amendment history; no gift-receipt representation; receiving.goods_receipt requires a PO with no walk-in path; orders.order_payment.status has no forfeited value) plus one genuinely new blocker not in the original 15-item list: pos.validate_sale_requires_open_session() hard-rejected a genuinely offline sale that synced after its register session closed, permanently stranding it (register_session_id is immutable after insert) — see `/Downloads/vrida-gap-validation-2026-07-19.md. A same-day gap-fix pass (PROJECT_DECISIONS #72) closed that POS blocker (flag-not-reject, reusing platform.flag_closed_period_business_date()'s own precedent) and, separately, closed a real unenforced cap the validation pass also found — offers.offer.max_per_consumerwas declared but never checked — WITHOUT the new tracker table the module's own original OPEN_ITEMS row assumed would be needed, since positioning a plainCOUNT(*)` after the trigger's existing row lock made it race-safe by construction (live-reproduced with a genuine 3-way concurrent race, exactly 2 of 3 succeeded). Both fixes are trigger-body only, zero schema/column-count change; the remaining 6 confirmed gaps plus the offline-tax architect decision (A5) are logged to OPEN_ITEMS, not built this pass.

Last modified: Jul 16, 2026, 9:17 AM PT
← PreviousSERVICE_LAYER_RUNBOOK
On this page
Esc
↑↓ navigate↵ openEsc close