Vrida — Locked Project Decisions

Brand & Identity

Brand name: Vrida (locked 2026-05-13)
Domains: vrida.app (primary), vrida.io (secondary)
Trademark: USPTO 1(b) Intent-to-Use application pending filing
Legal entity: CodeCraft Solutions LLC, French Valley, CA
History: see BRAND_NAME_LOG.md for the candidates that were evaluated and rejected

Target Customers

Anchor segment for v1.0: Mid-size growth-stage retail nurseries ($500K–$5M revenue, 800–3,000 SKUs, 5–20 staff, 1 site now with multi-site growth path)
Customer mix target: ~70% retail walk-in, ~20% landscape contractors, ~10% commercial/HOA
All sizes addressable: Starter for mom-and-pop, Pro for mid-size, Enterprise for multi-location and growing
Explicitly NOT target: wholesale-only growers (Picas territory), big-box garden centers, landscape design firms without retail, florists with no live-plant inventory

Pricing (Locked)

Starter: $49.99/month or $499/year
Pro: $99.99/month or $999/year
Enterprise: $199.99/month or $2,999/year
Trial: 30-day free trial, NO credit card required at signup
Trial-end behavior: App goes read-only if no payment method added; data preserved; reactivation requires only adding a card
Customer-facing AI plant care app: Free forever for end consumers
Strategic rationale: Enterprise intentionally underpriced vs Picas ($1,500–$5,000+/mo) and Rapid Garden POS ($300+/mo) for market capture
Starter capacity: Unlimited SKUs, 1 user, 1 site, 1 zone. Capacity caps are on users/sites/zones, not SKUs. Users is the natural upgrade gate to Pro.

Feature Philosophy (Locked)

Enterprise tier delivers feature parity with Picas/MARS/Rapid Garden POS
Tier gates control VOLUME (max SKUs, max users, max locations, max sites) — NOT capability
Same codebase, same database; tier differences enforced by application-layer feature flags and usage metering
No artificial feature gating to "justify" higher tiers
AI features distributed across modules as cross-cutting capabilities

Tech Stack (Locked Direction)

Mobile: Flutter (phone primary, tablet secondary)
Backend: NestJS on AWS App Runner
Database: PostgreSQL via Supabase (dev_*/prod_* schema separation)
ORM: Prisma
Payments: Stripe Connect for platform + Stripe Terminal for in-person
Hosting: AWS App Runner (backend), Supabase (database), Cloudflare (CDN, R2)
AI: AWS Bedrock with Claude Haiku 4.5
File storage: Cloudflare R2
Real-time: Supabase Realtime
Mobile offline cache: SQLite via Drift in Flutter
Email: Resend
SMS (later): Twilio
Digital signatures: HelloSign (Dropbox Sign) for legal-binding documents + in-app canvas for casual signatures
Plant database: licensed third-party (TBD vendor) + USDA PLANTS, 30K+ cultivars pre-loaded

Architecture Principles (Locked)

Tenancy: Pattern 2 (shared multi-tenant Postgres + Row-Level Security) for v1.0–v1.5
Pattern 4 hybrid (dedicated Supabase project per top Enterprise customer) added as $500–$2,000/mo add-on when first customer needs it
Schema-per-module: Each module has its own Postgres schema (inventory, pos, orders, purchasing, crm, reporting, production, customer_app, multi_loc, admin, billing, audit, notifications, delivery, services, platform, shared)
Cross-module access via service layer: modules call other modules' service classes, not their tables
Architecture-first: build schema and infrastructure for all three tiers in v1.0 even though only Pro tier ships publicly; Starter and Enterprise activate later via feature flags + UI rollout
Soft delete everywhere: status/deleted_at instead of DELETE
Audit log from day one: every change captured for all tenants
Snapshot history from day one: daily snapshots for inventory + audit data

Launch Path (Locked)

v1.0: Pro tier publicly launched. Schema and architecture support all three tiers; Starter and Enterprise codepaths exist but UI is hidden.
v1.1: Starter tier publicly launched (capped Pro features).
v1.5: Enterprise tier publicly launched with multi-location UI + advanced features.
v2.0: Full platform maturity, all modules complete.

Documentation Discipline (Locked)

Update PROJECT_DECISIONS.md, FEATURE_CATALOG.md, ROADMAP.md on every decision/scope change.
Every CC task must include explicit doc update step.
CC never claims "done" without docs updated.
Show updated section in chat after every doc update.
Read DOCS_INDEX.md to know where to update.

Product Direction — Generic Multi-Vertical ERP (Locked 2026-06-10)

This decision refines, does not reverse, "Nursery-Only Focus (Locked 2026-06-09)". The shared base (items, customers, vendors, sites, orders, payments, billing, identity, audit, notifications) was already declared vertical-neutral in the Item Model lock. This elevates that tendency to a standing scoping principle for every module and re-centers the GTM framing. See the annotation at "Nursery-Only Focus" for what survives and what changes.

Vrida is a generic retail ERP/POS designed to work for most business types. Nursery is the first and reference vertical — it drives concrete requirements and go-to-market — but the platform is built generic-first. Generic-first's real risk is over-abstracting and losing the concrete nursery requirements that make v1 shippable; "nursery = reference vertical drives requirements + GTM" is the mitigation.

Standing principle for all module design

Default scoping question: "Does this work for any retail business?" — NOT "Does this work for a nursery?"

Rule	Detail
No vertical-specific columns on foundation/shared schemas	No nursery, pottery, food/bev, or other business-type columns or assumptions on the foundation/shared schemas (`platform`, `shared`, `identity`, `multi_loc`) or on cross-cutting modules.
Vertical-specific needs via the already-locked mechanisms only	(1) `item_type` + JSONB `attributes` on the item layer. (2) Generic, reusable table design (e.g. `pos.guarantee` with free-text `guarantee_type`, not a `plant_guarantee` table). (3) Clearly-marked vertical add-on modules that sit beside the foundation schemas. Foundation/cross-cutting modules must not depend on add-ons.
NOT a revival of the retired generic-core + vertical-extension model	There is no `core` schema. This is not the multi-schema generic-core + vertical-extension split that was evaluated and rejected in "Inventory Item Model." Masters stay in their owning modules (item → Inventory, customer → CRM, vendor → Purchasing). The mechanism is discriminator + JSONB, not schema multiplication.
Apply forward	All future module design (Orders, Purchasing, Billing, Admin, etc.) starts with generic scope; any vertical assumption is flagged explicitly in the spec and schema at design time.

Reclassifications under this principle

These are notes only — no locked schema or spec is rebuilt. They make existing treatment explicit and guide future work.

docs/modules/07_production.md — formally reclassified as VERTICAL ADD-ON Production/propagation (growing from cuttings, crop schedules, mother plants) is nursery-specific. Most retail businesses do not produce their own goods. Already documented in SCHEMA.md as "pure vertical — non-nursery deployment omits it entirely." Formally reclassified here: not part of the foundation; not a dependency for any generic module. Build only when the nursery vertical is explicitly prioritized. Status: deferred indefinitely.

shared.plant, shared.plant_common_name — nursery vertical reference data in the generic shared schema Two nursery-specific reference tables that ended up in the generic shared schema. Locked and not being rebuilt — accepted as a known wart. Going forward: vertical reference data must NOT default into shared. When a second vertical is real, consider a per-vertical reference namespace rather than adding more vertical content to shared.

Note: shared.usda_hardiness_zone is borderline — climate zone data has broader use than plants (landscaping, agriculture, outdoor retail). Left in shared with no flag.

pos.guarantee — already generic/vertical-neutral — THE REFERENCE PATTERN guarantee_type is free text driven by item_variant.guarantee_terms.type (e.g. 'plant_guarantee'). Any vertical can use it with its own type label. terms_snapshot JSONB is shape-agnostic. This is the canonical example of the right approach. When designing any similar feature that has a nursery-specific flavor, apply this pattern.

What this means for upcoming modules

Module	Generic-first scope note
Orders (03)	Any retail business takes non-counter orders (online/phone/pickup/quote/back-order). Contractor/B2B flavor from `03_orders.md` is configurable, not baked-in tables.
Purchasing (04)	Vendor POs, receiving, supplier management — generic.
Billing (11)	Customer A/R, charge accounts, invoicing — generic.
Admin (10)	Staff, roles, settings, tax config — generic.
Reporting (06)	Reports and analytics over foundation data — generic.
Production (07)	Vertical add-on only — not a foundation dependency.

Deferred Decisions

Per-feature tier classification

Decision deferred until: All 15 module specs exist at strategic depth.

What's deferred: Classifying each individual feature within each module into a tier bucket (Starter / Pro / Enterprise / All-tiers).

Why deferred:

Tier decisions must be internally consistent across modules (e.g., "all AI features are Pro+" applies cross-module)
Easier to do in a single focused pass than incrementally
Doesn't block module spec progress

Storage: Per-feature classification lives in each module's feature table (a new "Tier" column will be added). High-level tier summaries remain in docs/TIER_FEATURE_MATRIX.md.

Status: Inventory module currently has only group-level tier behavior documented. Modules 2–15 will also be spec'd without per-feature tier classification until this deferred work happens.

Invitation expiry policy

Decision deferred until: Before building the invitation flow (part of the Identity module implementation).

What's deferred: The default validity period for a staff invitation. identity.invitation.expires_at is NOT NULL with no schema-level default — the application must compute and supply it on every insert. A locked policy is needed (e.g. "7 days from issue", or "configurable per tenant within a 1–30 day band").

Why deferred: Not a schema gap — expires_at already exists on the column. The policy is an application-level decision (UX + security tradeoff), best made when the invitation UI/flow is designed.

Storage: When locked, record the value here and reference it from the Identity Module section above. The expiry default lives in application code (or tenant settings), not in the schema migration.

Multi-Nursery Customer Accounts (Locked v1.0)

⚠️ Superseded — see Architecture Redesign (2026-05-13). The mechanism — one consumer account spanning multiple nurseries — is preserved as a product goal. The implementation now moves to the nursery-extension layer rather than living in the generic core. core.customer is tenant-scoped by default; multi-nursery linkage is achieved via a nursery-layer join table. See the Architecture Redesign section below for the new model.

Decision: Customer-facing App supports one customer account spanning multiple connected nurseries (tenants).

Rationale:

Customers naturally shop at multiple nurseries (local + on vacation + niche)
One unified app = better adoption and stickiness
Network effect: more nurseries on Vrida = more customers download
Network effect benefits nurseries too: more touchpoints, richer data, switching cost
Privacy preserved: each nursery sees only its own customer's orders

Implementation:

crm.customer table is global (not tenant-scoped) — one record per consumer email/phone
crm.nursery_customer table links customers to tenants (many-to-many)
Customer's plant library, garden plans, AI chats are global (per customer)
Orders, loyalty, notifications are per-tenant
Privacy enforced at API level

Deferred Decision: Customer-facing App Access Controls

Decision deferred until: After v1.0 launch and customer adoption data is available.

What's deferred:

Free tier vs paid tier for end consumers (if any)
Feature limits for non-connected users (e.g., chat usage caps)
Nursery-tier benefits for connected customers (e.g., unlimited chat at premium tier)
Subscription model for consumers (if any)
What features require nursery connection vs which are universal

For v1.0:

All features available to all customers EXCEPT white-label theming, which is gated to consumers connected to Enterprise-tier nurseries.
No other feature gating beyond what's natural (must connect to nursery to order from it).

Status: Open for tier-and-pricing details. Will be addressed in product strategy planning post-v1.0.

Multi-Site Architecture (Locked v1.0)

Decision: Schema-level multi-site support is built into v1.0, even though multi-site UI activates in v1.5.

Rationale:

Customers grow into multi-site over time; we want zero-migration upgrade path
Retrofitting multi-site into single-site architecture is expensive and risky
Forward-compatible architecture is cheap if designed upfront

Implementation:

Every transactional table includes site_id (UUID, NOT NULL)
Single-site tenants auto-populate site_id from tenant.primary_site_id
Master data tables (customer, vendor, catalog) are tenant-wide; no site_id
Inter-site operations use multi_loc.transfer table
Site-specific permissions, pricing, and policies are configurable

Tier behavior:

All tiers: Schema supports multi-site
Pro tier: 1 site UI in v1.0; multi-site UI in v1.5+
Enterprise tier: Multi-site UI from v1.5; full multi-site features

Status: Locked. All schema work from this point on must include site_id on transactional tables.

Global Tables (Tenant Scope Exceptions)

⚠️ Superseded — see Architecture Redesign (2026-05-13). The global-customer / global-plant-library / global-garden-plan / global-chat-session tables move OUT of the generic core and INTO the nursery-extension layer. core.customer becomes tenant-scoped by default. Cross-nursery consumer linkage and the consumer app's global library/plans/chats become extension-layer concepts. The downstream patterns (SECURITY DEFINER for cross-schema writes touching these tables) will be revised when the extension-layer schema is defined in a later phase.

The following tables are GLOBAL — they do not carry tenant_id and are not RLS-policed at the row level for tenant isolation:

crm.customer
crm.nursery_customer (join table linking global customers to tenants)
customer_app.plant_library
customer_app.garden_plan
customer_app.chat_session

These tables do NOT carry tenant_id. RLS does not apply at the row level for tenant isolation. Access control is enforced at the API layer via the crm.nursery_customer join.

Customer LTV Scope Rule

Customer LTV is computed per (tenant, customer) pair. Tenant A never sees Tenant B's revenue contribution to the same customer. CRM feature 7.10 reflects this scope.

Tenant Lifecycle and Data Retention

Decision (Locked v1.0): At 90 days post-cancel, operational data is deleted; audit log and financial records are retained for 7 years in archived form; global tables (consumer-owned) are NOT deleted on tenant cancellation.

Operational data (deleted at 90 days post-cancel):

pos.*, orders.*, inventory.*, purchasing.*, production.*
crm.* (except crm.customer and crm.nursery_customer, which are global)
multi_loc.transfer
notifications.*
customer_app.order_link (the tenant-scoped link table)

Archived for 7 years (retained in archived/cold form):

audit.* — full retention
Financial records derivable from audit (sales totals, refunds, tax collected, payments)
billing.invoice

Global tables (NOT deleted on tenant cancellation):

crm.customer, crm.nursery_customer
customer_app.plant_library, customer_app.garden_plan, customer_app.chat_session
These belong to the consumer, not the tenant. The crm.nursery_customer join is removed for the cancelled tenant, but the consumer's global records remain.

90-day window: Within 90 days, tenant can reactivate and resume with no data loss. After 90 days, operational data is irreversibly purged; archived records remain for compliance and statutory obligations.

Stripe Architecture

Each tenant has ONE Stripe Customer for SaaS subscription billing (Module 11). Each tenant can have N Stripe Connect Accounts, one per site, for in-person payment routing (Module 9 feature 1.14). These are independent Stripe entities.

White-Label Definition

White-label (Enterprise tier) means in-app theming of the Customer App: logo, colors, copy when a connected nursery is Enterprise tier. White-label does NOT mean separate app binaries in the App Store / Play Store. There is one Customer App; theming is per-connected-tenant.

Customer App Schema Ownership

crm schema owns the global customer record. customer_app schema owns global plant library, garden plans, and AI chat sessions. Both schemas may contain global tables per the Global Tables Exception above.

AI Capacity and Cost (header reference)

AI inference budgets, fallback paths, and per-tier rate limits are documented in ARCHITECTURE.md under "AI Capacity & Cost".

Audit Immutability Implementation (header reference)

Audit log immutability implementation pattern is documented in ARCHITECTURE.md under "Audit Architecture".

Deferred Modules: Delivery & Logistics (Module 14) and Services & Jobs (Module 15)

Decision: Modules 14 (Delivery & Logistics) and 15 (Services & Jobs) are deferred indefinitely from v1.0 specification. v1.0 ships with 13 modules.

Module 14: Delivery & Logistics

Rationale:

Only ~40% of mid-size nurseries operate delivery as meaningful revenue stream
Many nurseries use third-party couriers or contract with landscape companies
Delivery operations are operationally complex (drivers, vehicles, routes, scheduling, mobile driver app)
Spec may go stale by time we actually build; better to spec freshly when v1.0 customer reality is known

Schema readiness in v1.0:

Basic delivery tracking implied in Module 3 (Order Fulfillment, Group 5)
Customer addresses captured in CRM
Multi-loc supports inter-site movement (Module 9)
These provide foundation when full delivery module is built

Module 15: Services & Jobs

Rationale:

Only ~20% of retail nurseries offer service-type revenue (consulting hours, garden design, planting installation)
Service-revenue nurseries are typically Enterprise-tier and have existing tools
Different operational pattern (time-based vs product-based) requires distinct UX
Spec may go stale by time we actually build

Schema readiness in v1.0:

Customer charge accounts support service-type invoicing (Module 5)
Time tracking foundation in Module 10 (Admin)
Billing supports tier customization (Module 11)

When to revisit

For both modules:

Customer feedback shows clear need (data from v1.0 deployment)
Specific tenant requests features (Enterprise customer-driven)
Picas migration customers indicate essential capability
Pattern emerges where v1.0 customers struggle without these features

Status: Both modules deferred. v1.0 specification considered complete at 13 modules. Will be addressed in future versions based on customer demand and operational signals.

Open Gaps from Market Research

Status: 28 gaps identified from market research analysis (October 2025). All gaps are currently Open — pending decision. Master registry: docs/MARKET_RESEARCH_GAPS.md.

Decision process: Each gap requires one of:

Accept — Gap becomes scope. Update relevant module spec with new features/groups; update ROADMAP.md, FEATURE_CATALOG.md, TIER_FEATURE_MATRIX.md, ARCHITECTURE.md, GLOSSARY.md, SCHEMA_CONVENTIONS.md as needed.
Defer — Gap postponed to specified future version. Update ROADMAP.md.
Reject — Gap is out of scope. Update OUT_OF_SCOPE.md.

In all cases, update docs/MARKET_RESEARCH_GAPS.md status column and the relevant module spec "Gaps Identified" section to reflect the resolution.

Why this section exists: Per CLAUDE.md doc discipline, locked decisions and deferred decisions live in this file. Gaps are pre-decisions awaiting review. Tracking them here ensures they are not lost between identification and resolution.

Architecture Redesign (Locked 2026-05-13)

⚠️ Partially superseded — see "Nursery-Only Focus (Locked 2026-06-09)" below. The generic-core + vertical-extension model, item-first inventory, and plant test are retired. New module layers (Platform, Identity, Payments, Integrations, AI), routing annotations, customer tenant-scoping, and all infrastructure conventions survive unchanged.

This section records architectural decisions that supersede earlier choices. Schema, table definitions, and module-spec edits are explicitly out of scope for this phase. Downstream docs requiring updates are listed at the end.

1. Architecture Model: Generic-Core + Vertical-Extension

A generic core layer owns business primitives usable by any business: item, party (unified customer / vendor / contact), location, unit-of-measure, base pricing, tenant, feature-flags.
Nursery-specific concepts — plant master / taxonomy, propagation / crop lots, plant care attributes, consumer plant-care app — are an extension layer ON TOP of core, not part of core.
Hard rule: the generic core NEVER references the nursery layer; the nursery layer references core. The shape test for any column or table: "Would this make sense for a business that has never heard of a plant?" — Yes → core; No → nursery layer.

2. Item-First (Replaces Plant-First)

The central inventory entity is a generic item (core), not the plant master.
Plant becomes a profile / extension that references the generic item.
Supersedes the prior Module 1 plant-first model (plant master + size variants as the central inventory entity). The Module 1 spec text will be updated in a later phase to reflect: generic item in core, plant profile in nursery extension; variants become item-level UoM/SKU constructs rather than plant-master children.

3. Customer Model: Tenant-Scoped Core

core.customer is tenant-scoped by default.
The multi-nursery linkage (one consumer account across nurseries; the consumer app's global plant library, garden plans, AI chat history) becomes a nursery-layer extension, not a core global table.
Supersedes the prior "Global Tables (Tenant Scope Exceptions)" decision for crm.customer, crm.nursery_customer, customer_app.plant_library, customer_app.garden_plan, customer_app.chat_session. Those sections have been annotated above. The product goal (one consumer account spans nurseries) is preserved — only the mechanism moves.

4. Vertical Strategy: Deferred

v1.0 GTM remains nursery-only; no change to launch focus.
The architecture intentionally builds generic-core + nursery-extension so that BOTH futures stay open:
- multi-vertical-single-deployment (one Vrida instance serving nurseries + other verticals), OR
- per-vertical-instances (separate deployments per vertical, sharing a common core).
Engineering default: generic core, tenant-scoped customer, multi-nursery linkage as extension. Pick the deployment model later when a second vertical is real.

5. New Modules / Layers

Numbering is not yet assigned — append as needed. Each is a first-class layer in the architecture map.

Core / Platform — owns generic primitives (item, party, location, UoM, pricing, tenant, feature-flags). No nursery-specific knowledge.
Identity / Auth — users, roles, permissions, sessions, 2FA, SSO. Extracted FROM Admin; Admin retains tenant configuration only (settings, branding, integrations config, labor & scheduling).
Integrations — unified external-connector layer under one connector pattern. QuickBooks now; Shopify / Sage / Xero / workforce tools later. Extracted from Admin and Purchasing.
AI / Intelligence — single AIService abstraction over AWS Bedrock; model-as-config; owns routing, rate limits, fallback, future RAG corpus. No module calls Bedrock directly.
Payments — single PaymentsService abstraction over Stripe (Terminal + Customer/subscriptions + Connect behind one service). Stripe remains the only provider.

6. Supporting Services (Not Full Modules)

Each gets a single owner as a shared service:

Files / Documents — Cloudflare R2 ownership consolidated under one shared service.
Search — single owner / abstraction; per-module search calls go through it.

7. Reaffirmed Boundaries (No Change — Restated for Clarity)

No GL / accounting built; QuickBooks integration only — now owned by the new Integrations module (previously implied to live in Admin Group 10 + Purchasing).
Production remains a pure vertical module (correct pattern; a non-nursery deployment omits the Production module entirely without breaking core).

Downstream Docs Requiring Updates (Later Phases)

This phase records decisions only. The following docs will be updated in subsequent phases to reflect the redesign:

Doc	Expected updates
`docs/FEATURE_CATALOG.md`	Add new Core / Identity / Integrations / AI / Payments modules to the catalog table; renumber or annotate as needed
`docs/MODULE_INDEX.md`	Add new modules to the dependency graph; update Inventory / Admin / Purchasing dependencies; mark Production as vertical-only
`docs/ARCHITECTURE.md`	Replace "Schema-Per-Module Organization" listing with the layered model (Core / Identity / Integrations / AI / Payments / shared services / nursery extension); add "Generic-Core + Vertical-Extension" section near the top
`docs/SCHEMA_CONVENTIONS.md`	Revise "Global Tables (Tenant Scope Exceptions)" subsection — global tables likely move to extension layer; cross-schema-write pattern revised accordingly
`docs/ROADMAP.md`	Add the architecture redesign as a v1.0 build prerequisite (or pre-v1.0 refactor checkpoint, depending on sequencing)
`docs/DOCS_INDEX.md`	Add entries for any new module spec files; update module-table when new module numbers are assigned
`CLAUDE.md`	Add layered architecture to the locked-decisions cheat sheet; add new module boundaries to the "Module boundaries" rules
`docs/GLOSSARY.md`	Add: core, vertical extension, party, generic item, plant profile, AIService, PaymentsService, connector
`docs/modules/01_inventory.md`	Refactor to item-first: generic item in core, plant profile/taxonomy as nursery extension; variants restructured as item-level constructs
`docs/modules/05_crm.md`	Customer becomes tenant-scoped; cross-nursery linkage moves to a nursery-extension table; revise "Table Scope" section
`docs/modules/08_customer_app.md`	Global plant library / garden plans / chat sessions move to nursery-extension layer; revise "Table Scope" section and Realtime pattern
`docs/modules/10_admin.md`	Extract Identity/Auth (Group 3 user & staff account management → new Identity module) and Integrations (Group 10 → new Integrations module); Admin retains tenant configuration only

Note: Module specs 02 / 03 / 04 / 06 / 07 / 09 / 11 / 12 / 13 are likely also affected (POS → PaymentsService, Reporting → AIService, Production stays pure vertical, etc.) but the depth of their changes is TBD until the layered model is drawn. Treat the list above as the minimum impact set.

Platform Module (Locked 2026-06-09)

Platform is Vrida's control plane — manages nurseries as Vrida's customers. Handles tenant identity, subscriptions, payments, legal agreements, usage tracking, onboarding, entitlements, and Vrida's internal account management. Everything in platform is from Vrida's perspective as the SaaS operator.

16 tables locked:

tenant — Nursery identity, system status, tier, timezone, default site, feature flags (JSONB), module on/off prefs (JSONB), lifecycle timestamps
tenant_profile — Business details, firmographics, USDA zone, scale, services, ecommerce, onboarding source, acquisition attribution (utm / referral / partner)
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.
billing_account — Billing identity separate from plan: billing address, tax-exempt status, tax ID, Stripe Customer ID (moved here from subscription), payment method ref, invoice delivery method, payment terms
subscription — Vrida SaaS plan, billing cycle, status, trial / period dates, Stripe Subscription ID, seasonal pause flag, source contract for sales-led deals. Stripe Customer ID now lives in billing_account.
subscription_invoice — Vrida's invoices to the nursery, one per billing cycle, amount, status, line items, Stripe invoice ref
payment — Nursery's payments to Vrida against invoices, with status (pending / succeeded / failed / refunded), provider ref
tier_definition — Reference data: Starter / Pro / Enterprise caps, entitled modules, permitted add-ons
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?"
agreement_version — Legal document version registry: type, version, URL, content hash, effective date
agreement_acceptance — Who accepted which agreement_version, when, from where (IP, user agent), signature ref. FKs to agreement_version and Identity's user table.
tenant_usage_summary — Per-tenant per-month usage: active users, sites, SKUs, transactions, AI calls. Append-only, never overwritten.
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_data_lifecycle — Post-cancellation data handling: retention status, deletion workflow (scheduled / in_progress / completed / failed), legal hold flag, export request tracking
tenant_lifecycle_event — Every status transition: what changed, why (reason code), who triggered it, when. Single source of truth for churn / suspension reasons.
tenant_internal_activity — Vrida's internal activity log: CS notes, admin actions (tier changes, trial extensions, feature grants, credits), NPS updates, churn-risk updates. activity_type column distinguishes kinds. Before / after JSONB for admin changes.

Key design decisions within platform

feature_flags and module_prefs are JSONB on tenant, not separate tables
Usage is append-only per period (tenant_usage_summary), not stored on tenant
Stripe Customer ID lives in billing_account, not subscription
subscription_addon is absorbed into tenant_entitlement with source_type=addon
tenant_cs is absorbed into tenant_contact (current-state fields) + tenant_internal_activity (history / notes / actions)
tenant_provisioning + tenant_onboarding_task merged into tenant_setup_task
tenant_data_retention + export merged into tenant_data_lifecycle
marketing_preference absorbed into tenant_contact as per-contact columns
agreement_version kept separate from agreement_acceptance (different cardinality)

Identity Module (Locked 2026-06-09)

Identity is Vrida's authorization layer — manages all staff who log in: nursery employees and Vrida's own employees. Hybrid module: Supabase Auth owns authentication (login, sessions, passwords, MFA); Identity owns authorization (users, tenant membership, roles, permissions, site access). Seam: identity.identity_user.supabase_auth_user_id references Supabase auth.users (UUID reference, never an enforced FK).

11 tables, 140 columns, locked:

identity_user — Global login person, linked to Supabase Auth; not tenant-scoped; is_platform_user flag distinguishes Vrida staff from tenant staff
tenant_user — Tenant membership + role; Vrida platform users do NOT get rows here
role — Built-in, Vrida-internal, and tenant-custom roles; tenant_id IS NULL = global, populated = custom
permission — Granular action + resource permissions (reference data)
role_permission — Role-to-permission mapping with allow / deny effect
user_site_assignment — Per-site access; site role can differ from tenant role
identity_access_event — App-level authorization events (NOT raw Supabase Auth); append-only
sso_provider — Enterprise SSO config; secrets stored as references (*_ref)
invitation — Pending staff invites; token stored as token_hash; site assignments as JSONB
support_access_grant — Time-boxed, auditable Vrida access to tenant data
user_permission_override — Per-user allow / deny exceptions; used rarely

Key locked rules

Vrida platform users access tenant data ONLY through active, unexpired support_access_grant records (status = 'active' AND now() BETWEEN starts_at AND ends_at — status alone is insufficient). Every use logged in identity_access_event as support_access_used.
Permission resolution order: base (role_permission via tenant_user.role_id + site_role_id) → override (user_permission_override) → deny beats allow; more-specific scope (site / module) wins over tenant.
deny overrides allow throughout (both role_permission and user_permission_override).
role uses NULL-or-match RLS so global roles are visible to all tenants: USING (tenant_id IS NULL OR tenant_id = current_setting('app.current_tenant_id')::uuid). The role_permission policy is the same pattern via subquery on role.
role has a second partial unique WHERE tenant_id IS NULL AND deleted_at IS NULL to prevent duplicate global / built-in role codes (Postgres NULL ≠ NULL, so the standard (tenant_id, role_code) partial does NOT constrain global rows).
identity_access_event logs application-level authorization events, NOT raw Supabase Auth — logins, passwords, MFA challenges, session refreshes are Supabase's domain.
Secrets and tokens stored as references / hashes, never raw: sso_provider.client_secret_ref, sso_provider.certificate_ref, invitation.token_hash.

Forward-reference FKs (operational note)

Deferred to Phase 4 (multi_loc): FK constraints on tenant_user.default_site_id, user_site_assignment.site_id, user_permission_override.scope_id (when scope_type = 'site'). Columns are created as plain UUIDs in the Phase 3 identity migration; FK constraint added in the Phase 4 multi_loc migration.
Addable now that names are consistent: Platform's 7 FK constraints to identity.identity_user — tenant_contact.identity_user_id, tenant_entitlement.granted_by_user_id, agreement_acceptance.accepted_by_user_id, tenant_setup_task.completed_by_user_id, tenant_data_lifecycle.requested_by_user_id, tenant_lifecycle_event.actor_user_id, tenant_internal_activity.performed_by_user_id. These were initially written pointing at the old identity.user name; the rename to identity.identity_user is now propagated through docs/SCHEMA.md, and the FK constraints can be added when the Phase 3 identity migration runs.

Inventory Item Model (Locked 2026-06-09)

The Inventory item layer is vertical-neutral: a single inventory.item table with an item_type discriminator and a JSONB attributes column for type-specific data. Not nursery-specific columns. Not a generic-core + vertical-extension multi-schema split.

Decision

One inventory.item table. All sellable / stockable things — plants, hard goods, services, future product types — live in this table.
item_type discriminator — 'plant' / 'hard_good' / 'service' in v1.0, extensible (e.g. 'food' / 'pottery' later). The discriminator drives UI, validation, and reporting groupings.
Type-specific attributes live in JSONB attributes. Plant care (USDA zone, sun, water, mature size), pot material, fertilizer NPK ratio, food allergens, etc. — none of these get their own columns on inventory.item. They live in the attributes JSONB bag keyed by attribute code.
Universal attributes stay as real columns. Infrastructure (id, tenant_id, sku, name, description, price, stock, uom, is_active) and cross-type physical attributes used by many types (weight, dimensions) stay as scalar columns where they're queried universally.

Graduation rule (JSONB → real column)

A type-specific attribute moves from JSONB to a real column only when query / filter performance demands it — and that is a deliberate, later, per-attribute decision. The default is JSONB. Graduation criteria when the question arises:

The attribute is queried in WHERE / ORDER BY by a high-traffic code path (consumer-app filter, reporting MV refresh).
A JSONB GIN index doesn't perform well enough for the access pattern.
The attribute applies to a large enough fraction of rows that JSONB sparsity isn't the right trade-off.

No graduations in v1.0.

Scope of the vertical-neutrality

The item / catalog LAYER is vertical-neutral. A nursery can sell plants + pots + fertilizer on day one without schema gymnastics, and a future vertical (e.g. food retail) needs no item-table redesign.
Operational modules remain business-specific. Production / propagation (production schema — mother plants, crop lots, propagation runs) is nursery-only and ships only when relevant. Other verticals get their own operational modules as needed. The shared base is items, customers, vendors, sites, orders, payments, billing, identity, audit, notifications.

Relationship to Nursery-Only Focus

This refines, does not reverse, the Nursery-Only Focus (Locked 2026-06-09) decision:

Nursery-FIRST stands: nurseries are Vrida's launch customer; pottery / food / other verticals are planning-not-committed and explicitly out of scope for v1.0 GTM.
The item LAYER is vertical-neutral by design — so a nursery selling a mix of plants and hard goods works natively (the common case), and so a future vertical does not force a schema redesign.
The generic-core + vertical-EXTENSION multi-schema split was evaluated and NOT adopted. One flexible table replaces it. The Architecture Redesign (Locked 2026-05-13) generic-core layer is still retired — this decision keeps the single-schema item layer of Nursery-Only Focus and adds the vertical-neutrality via discriminator + JSONB instead of through schema multiplication.

What this changes in subsequent schema work

When inventory schema is designed (Phase 7):

inventory.item carries scalar columns for the universal set (id, tenant_id, sku, name, description, item_type, uom, price, stock, weight, dimensions, is_active, timestamps).
inventory.item.attributes is a JSONB column with documented shape per item_type (e.g. plant attributes JSON shape; hard_good attributes JSON shape; service attributes JSON shape).
item_type is a text + CHECK constraint listing the allowed values in v1.0 (extensible).
NO plant_profile table, NO inventory.plant_item subclass table, NO separate nursery.item schema. One item, discriminated.
Production / propagation stays in its own production schema as a nursery-specific operational module — items still live in inventory; production references inventory.item for what it's growing.

What this does NOT change

The 4 already-locked Foundation schemas (platform, shared, identity, multi_loc) are vertical-agnostic by design and are unaffected.
The 13 v1.0 module feature specs are nursery-facing in their feature listing — the vertical-neutrality applies to the data model, not the v1.0 feature scope.
shared.plant and shared.plant_common_name remain — botanical knowledge stays in the shared reference layer (it's reference data, not a per-tenant item record). Nursery items will reference shared.plant for botanical identity where applicable.

Shared Module (Locked 2026-06-09)

Module: shared (Phase 2 reference data). 7 tables, 57 columns.

All tables NOT tenant-scoped — global reference data:

RLS not applied — no tenant_id on any table.
Readable by all authenticated users (app_user and service_role).
Writable only via service_role and seed migrations — application code never writes to shared.* at runtime (with one exception: the AI enrichment pipeline writes to plant and plant_common_name through a service role).
No deleted_at anywhere — deactivation is via is_active = false. Reference rows have permanent identity (an ISO code is the same code forever, even when retired).

Tables

country — ISO 3166-1 dictionary; US only active in v1.0; other countries seeded inactive for forward compatibility.
us_state — US states / territories; FK to country.
currency — ISO 4217 dictionary; USD only active in v1.0; multi-currency is schema-ready but not feature-enabled.
unit_of_measure — global UoM vocabulary (each, lb, gal, cu_yd, etc.). Conversions live in inventory.item / inventory.package, NOT here.
usda_hardiness_zone — USDA Plant Hardiness Zone dictionary (1a–13b).
plant — thin global botanical reference (see Option C below).
plant_common_name — many common names per plant; name_normalized powers AI import fuzzy matching.

Plant Database — Option C (Locked)

Vrida's plant database is a thin global botanical reference. Locked option after evaluating three alternatives:

Option	What it is	Status
A — Full curated plant DB	Build / license rich plant content (care guides, images, pests, companions, toxicity) and curate at scale	❌ Rejected — content project, not a software project; doesn't scale; recreates work better-done by AI
B — No plant DB	Skip the table entirely; let AI generate everything per query, store nothing globally	❌ Rejected — weakens AI moat (every query starts from zero) and breaks import matching (no canonical plant entities to match messy tenant input against)
C — Thin global reference	Store botanical facts (name, genus/species/family, type, USDA zone range, basic sun/water/size) + common names with normalized matching. Rich care content generated at query time by the consumer plant-care AI.	✅ Locked

What v1.0 holds:

Botanical facts: botanical_name (truth), slug (consumer-app URLs), taxonomy (genus / species / family), plant_type, USDA zone range, basic care attributes (sun_exposure, water_needs, mature_height_cm, mature_width_cm).
Common names: plant_common_name with name_normalized for AI import matching ("jap maple", "JAPANESE MAPLE", "Japanese Maple " → same plant_id).
Provenance and trust: data_source (seed / ai_generated / manual) records where a row came from; is_verified (boolean) records whether to trust it for authoritative display. UI must surface unverified data with a disclaimer.

What v1.0 deliberately does NOT hold:

Care guides (planting depth, watering schedule, fertilization, pruning)
Images / photos
Pest / disease lists
Companion-plant relationships
Toxicity / pet-safety data
Cultivars beyond the species level (where they need their own row)

Rationale: Rich plant content is a moving target — best maintained by an AI that learns and updates continuously, not by a curated relational table that goes stale. The consumer plant-care AI generates this content at query time via Bedrock and caches popular queries (likely in customer_app or ai schema — not bloating shared).

Plant matching

plant_common_name.name_normalized (lowercased / trimmed / whitespace-collapsed) powers two flows:

AI onboarding import: tenant uploads inventory CSV with messy plant names ("jap maple", "JAPANESE MAPLE", "Japanese Maple "). The import pipeline normalizes input and looks up name_normalized to resolve to a known plant_id — or, if unmatched, creates a candidate row for review.
Consumer search: consumer types a common name in the app; the same name_normalized index serves the lookup.

USDA zone on `plant` — loose-coupled (not FK)

plant.usda_zone_min and usda_zone_max are text, not FKs to usda_hardiness_zone.zone_code. Deliberately loose-coupled — tolerates seed ordering and allows future non-USDA notation if other hardiness systems are ever added. A CHECK constraint (~ '^(1[0-3]|[1-9])[ab]?$') guards format / catches typos. Existence validation against zone_code happens in the service layer, not via DB FK.

v1.5 deferrals (do NOT build now)

Plant-browsing filter indexes (plant_type, usda_zone, is_verified) — needed when plant grows to thousands of rows; build with the v1.5 consumer-app plant browser.
Multi-currency UI / settings — schema is ready; UI deferred.
Provinces / states of countries other than US — single-table extension when needed.
Cultivar-level entries on plant beyond species level — currently one row per botanical (species) name.

Multi-Location Module (Locked 2026-06-09)

Module: multi_loc. v1.0 scope: 1 table (site, 21 columns). Owns the site concept — a physical nursery property — and defines what site_id points to system-wide. Multi-site operational features (transfers, cross-site fulfillment, per-site pricing) are deferred to v1.5.

v1.0 table

site — A physical nursery property (retail, yard, greenhouse, warehouse, farm, office, popup). Carries name, slug (URL-safe public identifier for consumer-app URLs), code (short tenant-internal code), site_type, status, is_primary, JSONB address and operating_hours, timezone override, lat/lon for mapping, and opened_at / closed_at dates.

v1.5 deferrals (do NOT build in v1.0)

transfer (cross-site stock movement header) + transfer_line (per-line items / quantities)
Site-specific pricing overrides
Site-specific permission overrides beyond identity.user_site_assignment
Fulfillment / sales routing toggles per site
Cross-site inventory visibility rules

The deferred items arrive with the multi-site UI in v1.5.

Cross-phase FK consolidation

multi_loc.site is now the FK target for four columns that were created as plain UUIDs in earlier phases. The FK constraints can be added in the Phase 4 multi_loc migration (not yet — they're operational artifacts, not schema decisions):

platform.tenant.primary_site_id
identity.tenant_user.default_site_id
identity.user_site_assignment.site_id
identity.user_permission_override.scope_id (when scope_type = 'site')

Forward decisions (3) — apply when designing subsequent modules

Forward decision 1: `site_id` requirement rule

site_id is REQUIRED (NOT NULL) on operational tables where activity happens at a physical location: sales, stock movements, orders, purchase receiving, production activity, transfers.

site_id is NOT required on tenant-wide master data: customers, vendors, items, roles, agreements, tenant settings.

Why: This determines which tables carry site_id at all. Applies when building Inventory, CRM, Purchasing, POS, Production, etc. Master data is tenant-scoped but site-agnostic; transactional data records where activity happened. The runbook column-standard already lists this rule (Section 3 "Transactional tables") — this lock makes it the official scope-level decision.

Forward decision 2: Site access control rule

Tenant isolation is enforced by RLS via tenant_id. Site-level access is enforced at the application / service layer via identity.user_site_assignment.

Do NOT add site_id as a second RLS isolation dimension in v1.0.

Why: RLS is a sharp tool — once a policy filters by (tenant_id, site_id), every cross-site query (admin, reporting, transfer, audit) has to bypass it via service_role. Site-level filtering is cheaper and clearer at the service layer using user_site_assignment. If site-level data isolation (not just access) becomes necessary in v1.5, it stays app-layer — not a retrofit of RLS.

Forward decision 3: Pricing / tax future-scope rule

Pricing and tax must be designed so scope can be tenant-level OR site-level. v1.0 may implement tenant-level only, but schemas MUST NOT assume pricing / tax is permanently tenant-only.

Why: Applies when designing CRM / pricing tables and Admin / tax setup. Do NOT build site-level pricing machinery in v1.0 — but the schema (likely a scope_type column + nullable site_id) must leave room for site-level rows. The cost of a scope_type column today is trivial; the cost of retrofitting it in v1.5 is high.

Nursery-Only Focus (Locked 2026-06-09)

⚠️ Refined by "Product Direction — Generic Multi-Vertical ERP (2026-06-10)". The "nursery-only product" framing is updated to "generic multi-vertical, nursery = first/reference vertical." What SURVIVES: masters in owning modules, no core schema, customer tenant-scoped, schema-per-module, RLS, all conventions. What CHANGES: product framing — the platform is now explicitly generic-first, with nursery driving requirements and GTM. Original retained for traceability.

Vrida was scoped nursery-only at this lock (refined 2026-06-10 to generic multi-vertical, nursery = first/reference vertical — see annotation above). The generic-core + vertical-extension architecture from the earlier Architecture Redesign (Locked 2026-05-13) remains SUPERSEDED for the following decisions, all of which still stand:

NO separate "core" module. There is no generic business-primitives layer.
Masters live in their owning modules: item / product in Inventory, customer in CRM, vendor in Purchasing. Cross-module access goes through service classes per CROSS_MODULE_CONTRACTS.md.
The item model is nursery-first (not item-first). Item can carry plant-specific fields directly. No plant_profile indirection.
Customer is tenant-scoped (this decision STANDS from the redesign). But the reason is clean multi-tenancy, not generic-core neutrality.
The "plant test" placement rule is retired.
The "generic core never references nursery layer" hard rule is retired.

What SURVIVES from the Architecture Redesign unchanged

Platform module as the control plane (now locked at 16 tables — see above)
Identity module (extracted from Admin) — users, roles, permissions
Payments module (PaymentsService abstraction over Stripe)
Integrations module (unified connector pattern)
AI module (AIService abstraction over Bedrock)
Files and Search as shared services
All routing annotations (PaymentsService, AIService, Integrations) on module specs remain valid
Supabase Auth for authentication, Identity for authorization
Schema-per-module, service-layer boundaries, RLS, soft delete, UUIDs, and all SCHEMA_CONVENTIONS

Billing Ownership Split (Locked 2026-06-09)

Two billing concerns, separated by who bills whom:

SaaS subscription (Vrida → tenant) lives in Platform module (subscription, subscription_invoice, payment, billing_account tables).
Customer-facing A/R (tenant → their customers) lives in Billing module (Module 11). charge_account moves from CRM to Billing.
Stripe Architecture reconciliation: ONE Stripe Customer per tenant = Platform's billing_account (Vrida billing them). N Stripe Connect Accounts per site = Billing / POS / Multi-loc (tenant collecting from customers).
Module 11 is renamed from "Billing & Subscription" to "Billing" (customer-facing A/R only).

AI-Powered Self-Serve Onboarding (Locked 2026-06-09)

Nursery onboarding uses AI to read uploaded files (CSV, Excel, PDF, photos) and auto-load data into tenant tables. The import pipeline lives in the Integrations module (import_job, import_file, import_record — 3 tables). Platform tracks onboarding milestones via tenant_setup_task. AI processing routes via AIService.

Flow: tenant uploads files → AIService detects data type and maps fields → records extracted and validated → flagged records shown for tenant review → tenant resolves flags → confirmed records loaded into target module tables (Inventory, CRM, Purchasing) → tenant_setup_task milestone marked complete.

Database Infrastructure Decisions (Locked 2026-06-09)

Six database infrastructure decisions resolved. These are design decisions, not code — no table/column definitions.

1. Connection Strategy

Direct connection (Supabase port 5432) for all tenant-scoped application queries. SET LOCAL works reliably here with no session leakage risk.
Pooled connection (Supabase port 6543 via PgBouncer) for service_role background work only (migrations, reporting MV refresh, provisioning, cross-tenant admin). These bypass RLS intentionally.
Connection exhaustion threshold: ~500+ concurrent connections. At that point add Supavisor session-mode pooling or move largest tenants to Pattern 4 dedicated infra.

2. Prisma Multi-Schema Setup

Use Prisma's multiSchema preview feature with a single schema file.
Each model tagged with @@schema("module_name") and @@map("table_name").
All 19 schemas declared in datasource.schemas array.
Cross-schema relations handled natively via @@schema directive.
RLS policies are NOT managed by Prisma — applied via raw SQL migration files alongside Prisma migrations.
SET LOCAL is not native to Prisma — handled by the tenant-context middleware (decision 3).
Documented fallback: if Prisma multi-schema friction becomes blocking, switch to Drizzle ORM (SQL-first, native multi-schema support, raw SQL control). Start with Prisma; switch only on real friction, not theoretical.

3. NestJS Tenant-Context Middleware

Global TenantGuard (NestJS Guard): validates JWT via Supabase Auth, extracts auth.uid, looks up identity.user → identity.tenant_user → tenant_id, attaches tenant_id to request context.
Global TenantInterceptor (NestJS Interceptor): wraps every request handler in a Prisma $transaction with SET LOCAL app.current_tenant_id = tenant_id. All service calls within the request receive the transactional Prisma client (tx), never the root prisma client.
@CurrentTenant() decorator: convenience for controllers to access tenant_id from request context.
Critical rules:
- Services NEVER use the root prisma client for tenant-scoped queries.
- Background jobs (cron, webhooks) must explicitly set tenant context before processing — they don't go through the HTTP interceptor.
- service_role queries bypass the interceptor entirely — used only for cross-tenant operations (Vrida admin, reporting refresh, provisioning).

4. Site Creation as First Provisioning Step

Default site is created as provisioning step 2, immediately after the tenant record itself.
Provisioning order:
1. tenant_record_created
2. default_site_created
3. storage_initialized
4. identity_initialized
5. stripe_setup
6. seed_data_loaded
This guarantees site_id NOT NULL is always satisfiable on transactional tables because no transactional record can be written before the site exists.
Default site is created with minimal data (auto-generated name from tenant name, no address required). Tenant fills in details during the site_configured onboarding step later.

5. Search Path Policy

Default search_path set to public only. No module schemas in search_path.
All application code, migrations, functions, triggers, and views MUST use fully schema-qualified table references: inventory.item, platform.tenant, crm.customer — never unqualified.
Prisma handles this automatically via @@schema() directive.
Rationale: if module schemas are in search_path, unqualified table names could resolve to wrong schema. Narrow search_path + required qualification eliminates this class of bug.
Exception: Postgres extensions (uuid-ossp, pgvector, pg_trgm) installed in public schema, accessible without qualification.

6. Migration Strategy

Prisma Migrate for schema structure changes (generates SQL from schema diff).
Raw SQL migration files for RLS policies, triggers, functions, and seed data — alongside Prisma migrations.
Scope: one migration per logical change, each touching one module's schema only (except cross-schema FK additions which touch two).
Naming:
- YYYYMMDDHHMMSS_description (Prisma default) for schema migrations
- YYYYMMDD_HHMM_module_description.sql for raw SQL (per SCHEMA_CONVENTIONS)
Ordering: timestamp-ordered, single runner. Cross-schema dependencies handled by building modules in dependency order.

Migration dependency order:

Phase	Modules	Why
1	`platform`	Root — tenant table required by everything
2	`shared`	Reference data
3	`identity`	Users / roles
4	`multi_loc`	Sites — needed for `site_id`
5	`payments`, `integrations`, `ai`	Service layers
6	`admin`	Tenant config
7	`crm`, `inventory`	Master data
8	`pos`, `orders`, `purchasing`, `billing`	Transactional
9	`customer_app`, `production`	Vertical
10	`reporting`, `audit`, `notifications`	Cross-cutting

Rollback: every migration includes a -- DOWN section per SCHEMA_CONVENTIONS. Prisma handles schema rollbacks; RLS/trigger rollbacks are manual SQL.
Environments: dev_* / prod_* schema separation per existing convention.
Critical rule: never run a downstream schema migration before its upstream dependencies have migrated.

Inventory Module (Locked 2026-06-09)

Module: inventory (Phase 7). 21 tables, 234 columns. (+2 cols added 2026-06-10: item_variant.has_guarantee + item_variant.guarantee_terms for POS guarantee support.)

Schema overview

Six groups of tables, all tenant-scoped, all with RLS enabled:

CATALOG (8) — item, item_variant, category, item_category, tag, item_tag, barcode, item_image
VARIANT STRUCTURE (2) — option_type, variant_option
LOCATIONS (1) — inventory_location
STOCK (5) — stock, stock_movement, stock_movement_line, stock_reservation, stock_adjustment_reason
COUNTS (2) — stock_count, stock_count_line
LOTS (2) + KITS (1) — lot, stock_lot, kit_component

Item model

Vertical-neutral per "Inventory Item Model (Locked 2026-06-09)":

Single inventory.item table; item_type discriminator ('plant','hard_good','service','kit').
JSONB attributes for type-specific data (plants: botanical facts; hard goods: manufacturer/model; etc.).
inventory.item_variant is the sellable / stockable unit — carries SKU, price (base_price_cents), cost (avg_cost_cents), UoMs (sell_uom_id, stock_uom_id), weight, dimensions, and the kit flag.

Stock model

Three stock states: stock.on_hand_qty (physical), stock.reserved_qty (committed), stock.available_qty (generated: on_hand − reserved).
Granularity: stock tracked per (tenant_id, site_id, variant_id, optional inventory_location_id).
Nullable location: inventory_location_id = NULL = "unlocated at site level." Two partial uniques (with-location, without-location) handle the Postgres NULL ≠ NULL trap on both stock and stock_lot.
Immutable audit trail: stock_movement (header) + stock_movement_line (lines) are append-only — no updated_at, no deleted_at. All quantity changes go through InventoryService transactionally, never by direct writes.
Cost: weighted-average per variant. Formula: new_avg = (prev_avg × existing_qty + unit_cost × received_qty) / (existing_qty + received_qty). Recalculated by InventoryService on every receiving movement; snapshotted on stock_movement_line.

Lot model

lot tracks provenance (supplier lot number, received date, expiry date, source type) and status (active / depleted / expired / quarantined).
stock_lot holds per-(site × location × lot) quantities. Lot tracking is separate from cost valuation — weighted-average cost lives on item_variant; lots carry unit_cost_cents for reference only.
When lots are in use: stock.on_hand_qty = SUM(stock_lot.quantity) for the same (tenant_id, site_id, variant_id, inventory_location_id) slot.

Key invariants (operationally enforced, not DB constraints)

All qty changes via InventoryService transactionally — never direct writes to stock.on_hand_qty.
stock.on_hand_qty = SUM(stock_lot.quantity) per slot when lots are in use.
stock.reserved_qty = SUM(active stock_reservation.quantity) per slot.
stock_movement + stock_movement_line are immutable once written.

Module boundaries

Rich pricing (tiered, promotional) → Pricing module (future). base_price_cents is list price only.
Receiving / purchase orders → Purchasing module. Purchasing calls InventoryService to register received stock.
Customer-facing reservations → created by Orders module, stored in stock_reservation (owned here).
Production / propagation → Production module. Production references inventory.item_variant for what it is growing; submits movements via InventoryService with source_module = 'production'.
UoM conversions (line-level, purchase ↔ stock ↔ sell) → variant_uom_conversion table (deferred to v1.5); v1.0 uses purchase_to_stock_factor / sell_to_stock_factor on item_variant.

Locked decisions

Decision	Value
Cost valuation method	Weighted-average (not FIFO, not standard cost)
Lot-to-cost coupling	Decoupled — lots track provenance / expiry; cost lives on variant
Kit stock mode	Per-variant flag: `explode_at_sale` (components deducted at sale) or `stocked_kit` (kit variant tracked as a single SKU)
Negative stock	Configurable per tenant (not locked at schema level)
UoM conversion v1.0	Via `purchase_to_stock_factor` / `sell_to_stock_factor` on `item_variant`; line-level conversion table deferred to v1.5

v1.5 deferrals (do NOT build in v1.0)

inventory.variant_uom_conversion — line-level UoM conversion table
stock_movement_line.from_site_id / to_site_id — line-level site for multi-site transfer accounting
stock_movement.movement_type = 'produced' — review when Production module is designed
barcode.barcode_type = 'QR' — add when QR scanning is spec'd

Reporting deferred-index delivered (2026-06-11)

inventory.stock_movement_line (tenant_id, item_variant_id, created_at DESC) BTree composite index delivered as part of the Reporting module lock. Index-only additive touch — module stays locked. Serves cost-history and COGS aggregation queries for mv_gross_margin and period_close_snapshot. See SCHEMA.md § reporting § Deferred Source-Table Indices.

Pricing Module (Locked 2026-06-09; `price_list_assignment` revised 2026-06-09)

Module: pricing (Phase 7). 4 tables, 56 columns (revised from 54 — price_list_assignment +2 cols).

Revision note (2026-06-09): price_list_assignment was extended to support both customer-level and customer-group-level price assignments. Added assignment_scope ('customer' / 'customer_group'), customer_group_id (FK → crm.customer_group), and a scope-enforcement CHECK. The single open-ended-active partial unique was replaced by two scope-aware partial uniques. All forward-refs in price_rule.customer_id, price_list_assignment.customer_id, and price_list_assignment.customer_group_id are now enforceable against the locked CRM schema.

Design model

Odoo-style price-list model: named price levels (lists), per-variant rules that absorb level prices, customer-specific prices, quantity breaks, date-windowed sales, and scheduled changes — all in one flexible price_rule table.

Tables

price_level — Named pricing tiers (e.g. "retail", "wholesale", "member"). Exactly one per tenant is is_default (used when a customer has no explicit assignment).
price_rule — The workhorse. One row per variant + scope + quantity break + date window. scope_type determines whether the rule applies to a price level or a specific customer. price_type is fixed / percent_off / amount_off. site_id is nullable (NULL = tenant-wide; non-NULL = site-specific, v1.5 only).
price_list_assignment — Customer ↔ price-level assignment with optional date window. Owned by Pricing, not CRM. Records full assignment history; at most one open-ended active assignment per customer (enforced by partial unique + PricingService overlap check).
price_change_log — Immutable audit trail. Records price_rule lifecycle events (created, updated, deleted, activated, deactivated) and optionally base_price_changed events when inventory.item_variant.base_price_cents changes — for unified pricing history.

Base price boundary

inventory.item_variant.base_price_cents is the source of truth for base price. PricingService layers rules on top; it never writes to Inventory. price_change_log may record base_price_changed entries (written by InventoryService calling PricingService.logBaseChange) for unified history — the value lives in Inventory.

Checkout discounts boundary

price_type = 'percent_off' / 'amount_off' on price_rule are predefined pricing policy (e.g. "20% off all perennials this weekend"), not transaction-time discounts. Cashier overrides and coupon codes applied at checkout are POS/Orders concerns and live in those schemas.

Resolution precedence (PricingService — not DB-enforced)

Given (variant, customer, qty, date, site):

Candidates: tenant_id match + variant_id match + is_active = true + date window contains now (or open) + min_qty ≤ requested qty + (site_id matches OR site_id IS NULL).
Specificity: customer-scoped > sale (dated) > quantity break > level > variant base price.
Within same specificity: highest matching min_qty wins.
Then site-specific rule (site_id NOT NULL) beats tenant-wide (site_id IS NULL).
Then highest priority; then newest created_at.

Key decisions

Decision	Value
Base price ownership	`inventory.item_variant` — Pricing layers on top
Discount units	`percent_off`: value is 0–100 %; result = `ROUND(base × (1 − v/100))`. `amount_off`: value is in cents; result = `base − v` (floored at 0)
Customer assignment overlap	`PricingService`-enforced (no `btree_gist` / `tsrange`); partial unique guards open-ended case
v1.0 site scope	`site_id` always NULL (tenant-wide only); site-specific pricing is v1.5
Forward-refs to CRM	`price_rule.customer_id` and `price_list_assignment.customer_id` are plain UUIDs; FK constraints added when CRM locks

v1.5 deferrals (do NOT build in v1.0)

Site-specific pricing rules (site_id non-NULL on price_rule)
Tiered / matrix pricing
price_change_log.change_type = 'expired' — add when auto-expiry scheduler is built

Consumer Layer Architecture (Direction agreed 2026-06-09 — not yet locked)

Status: DIRECTION ONLY. Architecture agreed but not yet designed through the schema runbook. No tables locked. No module specs created. Recorded here so the direction is not rediscovered or relitigated when the consumer phase begins.

Model decision: One Vrida consumer account (Model B)

A consumer signs up once on the Vrida platform and sees all Vrida stores they've shopped at. Platform-level identity — not tenant-scoped — lives in its own consumer schema beside platform and shared.

Consumers are a separate user population from tenant staff. Staff auth = identity module (email/password + 2FA + SSO). Consumer auth = social login (Google / Apple).

Two populations, one link

crm.customer (tenant-scoped — a business's record of a buyer) links to consumer.consumer (platform-level — the Vrida account) via a nullable consumer_id on crm.customer. Same human = one consumer.consumer row + many crm.customer rows (one per business). The link is optional in both directions:

Anonymous buyer at POS → crm.customer with no consumer_id
App-only signup with no purchases → consumer.consumer with no linked crm.customer

The consumer_id hook is added to crm.customer when CRM is designed, so the link point exists when the consumer phase arrives.

Consumer creation paths (3)

Self-register via app — consumer signs up with Google/Apple; consumer row created.
Cashier-create at checkout — cashier enters email/phone; unclaimed consumer stub created and linked to new crm.customer. Consumer claims the stub later via the app.
Cashier-link — cashier matches email/phone to existing consumer; links to the existing crm.customer.

Consumer status lifecycle: unclaimed → active.

Rewards model

Per-business — each business sets its own earning rules, tiers, and expiry. Points balance is per (consumer, tenant).
Platform-wide shared balance is possible later (different aggregation query, not a schema redesign) — the per-business schema keeps this door open without building it.

Offers model

Business-issued OR Vrida-issued offers.
Each offer carries a funding-source flag (vrida / business).
Settlement economics of Vrida-funded offers: RESOLVED at offers lock 2026-06-12 — offers records the offer + funding_source + redemption facts; the Vrida→merchant credit (funding_source = 'vrida') is platform's domain (fed-not-owned by offers). v1 is merchant-funded only; Vrida-funded + settlement deferred to v1.5 against a future platform credit concept. See "Offers Module (Locked 2026-06-12)".

Purchases

Stay in each tenant's POS / Orders schemas (tenant-scoped). The consumer app does not duplicate purchase records — it reads across the consumer's linked crm.customer rows to aggregate "your purchases everywhere," respecting the privacy boundary.

Privacy boundary: a business sees only its own relationship with a consumer — never the consumer's activity at other businesses. Cross-store visibility belongs to the consumer alone (visible in the consumer app only).

v1 consumer scope

Rewards + informational: view my stores, points balances, offers, AI care chat, purchase history. In-app commerce (buying inside the app) is deferred to a later sub-phase.

POS seams (built when consumer layer is built)

Cross-module contracts to add at consumer-layer build time:

Create / link consumer at checkout — POSService calls ConsumerService.findOrCreate(email|phone) and attaches consumer_id to the sale.
Redeem rewards / offers at checkout — POSService makes two parallel service calls: RewardsService.redeem(consumer_id, reward_option_id, amount) (points redemption) and OffersService.redeem(consumer_id, offer_id, sale_id) (coupon/promo redemption). OffersService is NOT a sub-service of RewardsService — they are parallel consumer-layer services, both called by POSService. (Pre-design notation updated 2026-06-12 at offers lock — original note predated the rewards/offers design as separate modules when consolidation was still open.)

POS also supports anonymous sales (customer_id nullable on the sale record) — consumer presence is optional.

Estimated module map (~4 modules — to be designed, NOT locked)

Module	Schema	Scope
`consumer`	`consumer`	Platform-level identity; social-login auth; profile; unclaimed-stub lifecycle
`rewards` / loyalty	`rewards`	Per-business points; earning rules; tiers; expiry; redemptions; bridges consumer × tenant
`offers`	`offers`	Business-issued or Vrida-issued discount/promo offers; funding flag; redemption tracking
`consumer_app`	`customer_app` (existing)	App experience — read-aggregation across linked stores; AI care chat; in-app commerce (deferred sub-phase)

offers may consolidate into rewards — decide at design time.

Sequencing

Built as a dedicated phase after the merchant core (CRM → POS → Orders → Purchasing → Billing), because rewards earn from purchases that happen in POS. The consumer layer structurally depends on POS existing.

Do NOT build any consumer-layer schema before POS is locked.

Open questions for the consumer-phase design

Record so they are not rediscovered at design time:

~~Vrida-funded offer settlement economics (who absorbs the cost? how is it tracked?)~~ → RESOLVED at offers lock 2026-06-12: offers records offer + funding_source + redemption facts; settlement (Vrida→merchant credit for funding_source = 'vrida' offers) is platform's domain — platform owns the Vrida↔tenant financial relationship. Offers feeds settlement; it does not build a settlement engine. v1 is merchant-funded only; Vrida-funded + settlement deferred to v1.5 against a future platform credit concept. See "Offers Module (Locked 2026-06-12)".
Unclaimed-stub point handling (do unclaimed stubs accumulate points? when claimed, are prior points transferred?)
Reward expiry mechanics (calendar-year reset? rolling window? inactivity-based?)
Consumer auth provider specifics (which social-login library; Supabase Social Auth vs. custom)

CRM Module (Locked 2026-06-09)

Module: crm (Phase 7). 7 tables, 90 columns.

Design model

One customer table handles both individuals and businesses via customer_type discriminator. B2B fields (company_name, credit_terms, credit_limit_cents) are nullable — they exist for business customers without imposing constraints on individuals. Email is not hard-unique (retail reality: shared household emails, anonymous walk-ins, multiple accounts for the same person are all legitimate; dedup is a service-layer operation, not a DB constraint).

Tables

customer — Core buyer record. customer_type = 'individual' / 'business'. Email indexed but not unique; customer_number uniquely constrained (partial — only when present). consumer_id is a nullable forward-ref to the future consumer.consumer platform account (Consumer Layer phase). marketing_opt_in is a current-state cache; customer_consent is the audit trail.
contact — Additional contacts per customer (e.g. AP department, site manager). At most one is_primary per customer.
address — Billing / shipping / both addresses per customer. At most one is_default per (customer, address_type).
customer_group — Tenant-defined customer segments. Referenced by two distinct consumers: crm.customer.customer_group_id (CRM segmentation) and pricing.price_list_assignment.customer_group_id (group → price level assignment).
customer_note — Append-only staff notes (call logs, visit notes, general). No updated_at / deleted_at.
customer_merge — Append-only dedup audit trail. Records which source customer was merged into which target. Source record is soft-deleted (not physically removed) so FK references and historical records remain valid.
customer_consent — Append-only consent event log (marketing email, SMS, phone, privacy terms, loyalty opt-in/out). Current state cached on customer.marketing_opt_in for fast reads.

Key decisions

Decision	Value
Email uniqueness	NOT hard-unique — service-layer dedup via `customer_merge`
Merge pattern	Source soft-deleted; activity moved to target; `customer_merge` retains FK-valid history
Consumer link	`consumer_id` nullable forward-ref — plain UUID now; FK added when `consumer` schema locks
Loyalty / rewards	Not in CRM — Consumer Layer `rewards` module owns it
B2C vs B2B	One table; `customer_type` discriminator; B2B fields nullable

Module boundaries

Pricing rules → pricing schema (PricingService)
Transactions (sales, orders) → pos / orders schemas
Loyalty / rewards → Consumer Layer rewards module (not yet built)
Tax calculation → admin schema (tax config) + service layer
Charge accounts / A/R → billing schema (Billing Ownership Split — not in CRM)

Forward-ref: consumer link

customer.consumer_id is a plain UUID (no FK) until the consumer schema is built in the Consumer Layer phase. When locked, add:

ALTER TABLE crm.customer
  ADD CONSTRAINT customer_consumer_id_fkey
  FOREIGN KEY (consumer_id) REFERENCES consumer.consumer(id);

Search touch (2026-06-11)

crm.customer gained search_vector (tsvector GENERATED ALWAYS AS, source: display_name + customer_number + company_name) + GIN on search_vector + GIN on display_name gin_trgm_ops. Col count: 107 → 108 (+1 generated column). Lock remains valid — additive, non-breaking.

POS Module (Locked 2026-06-10)

Module: pos (Phase 8). 19 tables, 278 columns. The merchant in-store transaction module and the only true offline-first module in v1.0.

Groups

Group	Tables	Notes
Transaction	`sale`, `sale_line`, `sale_line_tax`	`sale_line_tax` is append-only (legal record)
Payment	`sale_payment`	Split tender = multiple rows per sale
Refund	`sale_refund`, `sale_refund_line`	Restock decision (`restock`/`write_off`/`discount_restock`) per line
Receipt	`receipt`	Delivery tracking; 7-year retention
Register / Cash	`register`, `register_session`, `register_cash_entry`	`register_cash_entry` is append-only (drawer audit)
Layaway	`layaway_payment`	Schedule only — actual money always in `sale_payment`
Stored Value	`gift_card`, `store_credit`	SEPARATE tables — bearer instrument vs customer liability; each with immutable ledger
Templates	`sale_template`, `sale_template_line`	Contractor recurring worklists; prices resolved fresh on apply
Offline	`pos_sync_conflict`	Manager resolution queue for offline collisions
Guarantee	`guarantee`	Generic/vertical-neutral; terms snapshotted at sale
Total	19 tables	278 cols (Group A 150 + Group B 128)

Key decisions

Decision	Value
Offline model	`client_uuid` (tenant-scoped UUID v4 dedup) + `sync_status` + `origin` + `idempotency_key` on `sale` + `sale_payment`. Online: decrements stock immediately via `InventoryService` at `completeSale`. Offline: decrements on sync. Collision (two tills sold last unit offline) → `pos_sync_conflict` for manager resolution.
Idempotency	Pervasive — `idempotency_key` threads through to `inventory.stock_movement.idempotency_key` to prevent double-decrement on retry.
Client UUID dedup	`UNIQUE (tenant_id, client_uuid) WHERE deleted_at IS NULL` on `sale` and `sale_payment` — tenant-scoped for correctness and index selectivity.
Actor stamping	`cashier_id` on every `sale`; manager FK on every approval action. No shift/labor tables — Admin/HR module owns those.
Gift card vs store credit	Separate tables. Gift card = bearer instrument (anonymous, code-redeemable). Store credit = customer-tied (one account per customer per tenant). Each has its own immutable ledger with `balance_after_cents` snapshot per row.
Refund model	Dedicated tables (`sale_refund` + `sale_refund_line`) — not negative sale lines. Restock decision captured per line. `guarantee_claim` refund type links back to `guarantee.claimed_refund_id` (two-way link).
Comp / sample lines	`sale_line.line_type` discriminator (`comp`/`sample`/`replacement`) — not separate tables.
Guarantee model	Generic — `guarantee_type` is free text from `item_variant.guarantee_terms.type`. `terms_snapshot` JSONB preserves catalog terms immutably at sale time. Two-way link: `sale_line.guarantee_id` → guarantee; `guarantee.claimed_refund_id` → refund.
Layaway money	`layaway_payment` = installment schedule only. Actual money always in `sale_payment`. `layaway_payment.sale_payment_id` links schedule entry to tender.
Split tender	Multiple `sale_payment` rows per `sale`; sum enforced at service layer.

Module seams

Seam	Pattern
Inventory	Stock decrement via `InventoryService` at `completeSale` (online) or sync (offline). `idempotency_key` prevents double-decrement on retry.
Pricing	`PricingService` resolves price at checkout; stored on `sale_line.unit_price_cents`. Templates resolve fresh on `applyTemplate`.
Payments	Stripe Terminal via `PaymentsService`. POS stores `stripe_payment_intent_id` / `stripe_offline_intent_id`. POS never calls Stripe directly.
CRM	`customer_id` nullable (anonymous sales OK). `tax_exemption_cert_id` is a text seam (no standalone cert entity in CRM v1.0; FK added when CRM adds a `tax_cert` table).
Consumer / Rewards	`sale.points_earned` + `sale.points_redeemed` forward-ready seam columns. Ledger in future `rewards` module (Consumer Layer).
Billing	`sale_payment.charge_account_ref` text seam. A/R entry + credit validation owned by future Billing module; FK added when Billing locks.

Deferred items (explicit triggers)

L-2: pos.sale_line — add (tenant_id, line_type, created_at) composite index when accounting-module period queries for comp/sample lines are defined.
M-2: pos.register_session — add service-layer close invariants documentation (closed_by / closing_cash_cents / expected_cash_cents / variance_cents / closed_at all non-null when status = 'closed') when POSService.closeRegister() is implemented.
F-2: pos.sale_payment.status — add 'cancelled' to CHECK when Payments module maps Stripe PaymentIntent cancellation events.

Search touch (2026-06-11)

pos.sale gained search_vector (tsvector GENERATED ALWAYS AS, source: sale_number + po_number + job_reference) + GIN on search_vector + GIN on sale_number gin_trgm_ops. Note: no customer-name snapshot on pos.sale — customer-name order search routes through CRM (see Search module, DR6). Col count: 279 → 280 (+1 generated column). Lock remains valid — additive, non-breaking.

Inventory touch (2026-06-10)

inventory.item_variant gained has_guarantee (boolean, default false) + guarantee_terms (JSONB, nullable) to support POS guarantee creation at sale time. CHECK added: (has_guarantee = false OR guarantee_terms IS NOT NULL). Inventory module col count updated: 232 → 234 columns. The inventory lock remains valid — this is an additive, non-breaking touch driven by the dependent POS module.

Search touch (2026-06-11)

inventory.item gained search_vector (tsvector GENERATED ALWAYS AS, source: name + description + item_type) + GIN on search_vector + GIN on name gin_trgm_ops. inventory.item_variant gained search_vector (source: sku + name) + GIN on search_vector + GIN on sku gin_trgm_ops + GIN on name gin_trgm_ops (name trgm added at Search lock — F1 fix for partial variant-name search). Col count: 236 → 238 (+2 generated columns; indexes add no cols). Lock remains valid — additive, non-breaking.

Orders Module (Locked 2026-06-10)

Module: orders (Phase 8). 7 tables, 132 columns. First module designed and locked under the generic-first principle (see "Product Direction — Generic Multi-Vertical ERP (2026-06-10)").

Groups

Group	Tables	Notes
Order header	`order_header`	Commercial lifecycle root — draft through fulfilled/closed/cancelled
Order lines	`order_line`	Per-item quantities + fulfillment tracking; estimated line tax (display only)
Payments	`order_payment`	Deposit/installment schedule; payments against order balance
Fulfillment	`order_fulfillment`, `order_fulfillment_line`	Header/line operational handoff; light routing; full routing deferred to Module 14
Templates	`order_template`, `order_template_line`	Recurring order templates; prices resolved fresh on apply
Total	7 tables	132 cols

Key decisions

Decision	Value
Table naming	`order_header` (not `order`) — avoids SQL reserved word, signals header/line pattern
Own payment schedule	`order_payment` owns deposits and installments. A deposit is a payment against an order balance, not a taxable event. Final tender (and legal tax) finalize at POS.
Link-don't-convert	`order_header.fulfilled_sale_id → pos.sale`. Orders are linked to the POS sale created at handoff; they do not convert to or become sales.
Tax model	`estimated_tax_cents` (header) + `estimated_line_tax_cents` (line) are DISPLAY ONLY for quote/order display. Legal taxable transaction and jurisdiction-level tax lines finalize at POS via `pos.sale` + `pos.sale_line_tax`.
Reserve-on-confirm	`InventoryService` writes `inventory.stock_reservation` at confirmation, not at quote/draft. Backordered lines hold `stock_reservation_id = NULL`; qty in `order_line.backordered_qty`.
Order type discriminator	`order_type` CHECK IN (`'quote'`,`'order'`,`'special_order'`,`'preorder'`). Back-order status is a line-level fulfillment state, not an order type.
Actor stamping	`created_by_user_id` (who took the order), `confirmed_by_user_id`, `cancelled_by_user_id`, `picked_by` — consistent with POS actor-stamping pattern.
Light fulfillment	`order_fulfillment` header + `order_fulfillment_line` cover picking, staging, and handoff. Full carrier routing, delivery tracking, and proof-of-delivery deferred to Module 14.
Templates	`order_template` / `order_template_line` — contractor/recurring worklists; prices resolved fresh by `PricingService` on apply. Mirrors `pos.sale_template` pattern.

Tax decision (authoritative)

"Orders may collect deposits and scheduled payments, but those are payments against an order balance. Orders stores estimated tax for display/quote purposes only. The legal taxable transaction and jurisdiction-level tax lines are finalized by POS at fulfillment/handoff via pos.sale + pos.sale_line_tax. Basis: CDTFA present-vs-future-sale intent — a deposit is a payment against a future sale, not a taxable event."

Module seams

Seam	Pattern
Inventory	Reserve via `InventoryService` at confirmation. `stock_reservation_id` on `order_line` links to `inventory.stock_reservation`. All qty changes via `InventoryService`.
Pricing	`PricingService` resolves price at order creation; stored on `order_line.unit_price_cents`. Templates resolve fresh on `applyTemplate`.
POS	`order_header.fulfilled_sale_id → pos.sale` + `order_payment.pos_sale_payment_id → pos.sale_payment`. Link-don't-convert.
CRM	`customer_id` nullable (anonymous quotes OK). `ship_to_address_id → crm.address` for ship fulfillments.
Purchasing	`order_header.draft_po_id` — forward-ref text seam; FK added when purchasing locks.
Billing	`order_payment.charge_account_ref` — text seam; FK added when billing locks.
Payments	Stripe card payments via `PaymentsService`; `stripe_payment_intent_id` on `order_payment`.
Module 14	`tracking_number` + `carrier` on `order_fulfillment` — plain text deferred forward-refs; no FK until Module 14 locks.

Deferred items (explicit triggers)

F-1: store_credit / gift_card excluded from order_payment.payment_method in v1.0. Both live in pos schema. Boundary assumption: v1.0 final tender at POS via fulfilled_sale_id. Trigger: when online-only (non-POS) order tender flow is designed.

Search touch (2026-06-11)

search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(order_number,'') || ' ' || coalesce(po_number,'') || ' ' || coalesce(job_reference,''))) STORED added to orders.order_header. GIN index on search_vector + GIN on (order_number gin_trgm_ops) added. Orders module: 7 tables, 133 cols (+1). Customer-name limitation: order_header has no denormalized customer name — customer linked via customer_id FK only. Customer-name order search requires a two-step compose in SearchService: CRM search → customer_id[] → filter order_header. See Search module "SearchService Composition Notes".

Admin Module Scope (Decided 2026-06-10)

HR / workforce management: OUT OF SCOPE for Vrida

Vrida is a retail transaction ERP. It does not do HR or workforce management. Staff scheduling, time clock, attendance tracking, wage management, commissions, tips, payroll calculation, and payroll export are out of scope entirely — not v1, not deferred, not planned. Businesses use dedicated tools for this (Gusto, ADP, Homebase, etc.).

The only staff data Vrida holds:

User identity + role: owned by identity.identity_user, identity.role, identity.user_site_assignment — who can log in and what they can do.
Actor attribution: operational records stamp who performed an action (e.g. pos.sale.cashier_id, purchasing.purchase_order.created_by_user_id). These are read references to identity.identity_user, not HR records.

There are no Admin schema tables for: shifts, schedules, time entries, breaks, time-off requests/balances, wage rates, commission records, tip distribution, bonus tracking, staff certifications, staff disciplinary records, staff HR documents, or payroll exports.

Spec groups formally out of scope: Group 4 (Staff Scheduling & Shifts), Group 5 (Time Clock & Attendance), Group 6 (Labor Cost & Payroll), and the tenant HR portion of Group 3 (features 3.15–3.19: onboarding workflow, training tracking, certifications, staff documents, disciplinary records).

Internal communications: out of v1

Bulletin board, knowledge base, and staff calendar (Group 17) are not core to merchant operations. Out of v1 scope. No schema tables.

Admin module owns: tenant CONFIGURATION only

Post-scope-reduction, the admin schema is a pure configuration module. It owns:

Cluster	What it owns
Tenant business profile	Legal name, business type, EIN (encrypted), operating addresses, business hours, DBAs, branding assets (logo, colors, fonts, slogan, social handles)
Compliance documents	Business license, insurance certs (with expiry tracking), tenant-level compliance docs
Tenant settings	Generic key-value config per category (Operations / Financial / Tech / etc.) + per-site override layer — absorbs all config deferred from POS, Orders, and Billing (hold expiry, receipt config, discount-approval thresholds, etc.)
Tax configuration	Tax jurisdictions, rates with effective dates, scheduled rate changes, tax holidays, tax-exempt rules, resale certificates (tenant-level — distinct from CRM customer-level certs), 1099 vendor tracking. `pos.sale_line_tax` is downstream of this config.
Hardware registry	Hardware device registry per site (register, receipt printer, cash drawer, Stripe Terminal device, barcode scanner)
Integration config	Per-tenant enable/disable + API keys + sync schedule for each integration (config only; runtime lives in Integrations/Payments modules)
API / webhook config	API key management (generate/revoke/audit); webhook endpoint + event subscription configuration (Enterprise tier)
Approval workflow definitions	Multi-step approval chains + routing rules consumed by Purchasing (PO approval) and POS (discount-approval threshold). Approval request instances tracked here or in Audit — TBD at design.
Data privacy + retention config	Data retention policies, GDPR/CCPA compliance status config, customer data export/deletion workflow triggers
Audit log config	Retention period, access control, export — configuration only; Audit module owns the log
White-label branding	Custom domain, email/SMS templates, branded customer-app theming (Enterprise tier)
Tenant onboarding wizard	First-time setup walkthrough (UI shell; Platform's `tenant_setup_task` owns task state)

Admin does NOT own (resolved boundaries)

Concern	Owner	Admin's role
Users / roles / permissions	`identity` module	Config UI only — calls `IdentityService`
Subscription / tiers	`platform` module	UI pass-through — calls `PlatformService`
The audit log itself	`audit` module	Configures retention/access only
Security policy enforcement	`identity` owns `sso_provider`; auth tables	Admin is config UI; `password_policy`, SSO config, session timeout, IP whitelist tables live in identity, not admin — resolved at schema design
HR / scheduling / payroll	Out of scope (see above)	None
Internal comms	Out of v1 (see above)	None

Generic-first requirement

All Admin config tables must be vertical-neutral. Tax config in particular must carry country_code (not assume US) to support future multi-country deployments. No nursery-specific columns on the admin schema.

Admin Module (Locked 2026-06-10)

11 tables, 145 columns, schema admin

Tenant configuration module only. HR/scheduling/labor is permanently out of scope for Vrida (see "Admin Module Scope" above). Tax calculation is delegated to Stripe Tax — no jurisdiction tables, no rate tables in Admin.

Tables

Table	Cols	Group / Purpose
`tenant_business_profile`	17	Legal identity, addresses, EIN vault-ref, trade names (one-per-tenant singleton)
`tenant_branding`	12	Logo R2 forward-ref, colors, fonts, slogan, social handles (one-per-tenant singleton)
`compliance_document`	14	Tenant's own permits, insurance, resale certs — expiry tracking
`tenant_setting`	11	Generic key-value config store (absorbs deferred config from POS / Orders / Billing / Notifications); site-level overrides via nullable `site_id`
`hardware_device`	13	Physical device registry per site (registers, printers, card readers, scanners)
`integration_config`	13	Per-tenant integration config; credentials vault-ref; sync schedule (QuickBooks, Stripe, etc.)
`webhook_config`	12	Outbound webhook endpoints; HMAC signing secret vault-ref
`api_key`	14	Tenant API keys — `token_hash` (SHA-256, raw shown once); `key_prefix` for display
`approval_workflow`	10	Configurable approval chain definition per workflow type
`approval_routing_rule`	11	Routes an approval trigger to the right workflow by type, threshold, and optional site
`approval_request`	18	In-flight and resolved approval instances; cross-module engine

Key decisions

Decision	Rationale
Tax calculation = Stripe Tax (no rate tables)	Stripe Tax handles jurisdiction + compliance; Admin stores only product tax codes (`inventory.item.stripe_tax_code`) and customer exemptions (`crm.customer_tax_certificate`). No admin.tax_rate, admin.tax_jurisdiction, admin.tax_holiday tables.
`tenant_setting` is the single generic config table	All module settings live here, keyed by (category, key). Avoids per-module config tables. `AdminService.getSetting()` is the single retrieval API.
`tenant_setting` uniqueness: two partial indexes	A single UNIQUE on (tenant_id, category, key, site_id) cannot block duplicate tenant-wide settings (NULL ≠ NULL). Fix: separate partial UNIQUE on (tenant_id, category, key) WHERE site_id IS NULL + separate partial UNIQUE on (tenant_id, category, key, site_id) WHERE site_id IS NOT NULL.
Secrets as `*_ref` vault references	`ein_ref`, `credentials_ref`, `secret_ref` are vault references — never raw secrets in any column. `api_key.token_hash` stores SHA-256 hash; raw value shown once at key-creation UX, never persisted.
Security policy in `identity`, not `admin`	Roles, permissions, user management live in `identity`. Admin is config UI surface only.
Approval engine is config + routing	`approval_workflow` defines steps; `approval_routing_rule` routes triggers; `approval_request` is the in-flight instance. Source records (`pos.sale`, `purchasing.purchase_order`) record the outcome — Admin engine makes no writes to locked schemas. `source_ref` is a plain UUID (polymorphic by `source_module`), not an enforced FK.
One-active-workflow-per-type: DB-enforced	Partial UNIQUE on (tenant_id, workflow_type) WHERE is_active = true AND deleted_at IS NULL. Not just service-layer.
Hardware registry coexists with POS register	`admin.hardware_device` is the physical device inventory. `pos.register` is the software-POS register. POS register may cross-reference a hardware_device for its card reader; no conflict.

Touches to locked modules

Module	Change	New count
`inventory.item`	+ `stripe_tax_code text nullable` — Stripe Tax product tax code; NULL = inherits tenant default	inventory: 21 tables, 235 cols
`crm.customer_tax_certificate`	New table (17 cols) — customer tax exemption certs; source of truth for `customer.tax_exempt` cache	crm: 8 tables, 107 cols
`crm.customer.tax_exempt`	Redocumented as maintained cache of `customer_tax_certificate` (≥1 active, non-expired row)	(no col change)
`crm.customer.tax_exempt_id`	DEPRECATED 2026-06-10 — cert number now on `customer_tax_certificate.certificate_number`; retained for backward-compat	(no col change)
`pos.sale` seam note	Updated: `sale.tax_exemption_cert_id` stays `text` by design (offline-first — cert identified by value at the till, not by UUID FK); reconciliation is service-layer	(no schema change)

Deferred items (with explicit triggers)

Deferred	Trigger
`tenant_business_profile.business_type` CHECK enum vs. free-text	When business_profile service is built
`compliance_document.expires_at` partial index (WHERE deleted_at IS NULL)	When expiry alert job is written
`tenant_setting.value` JSONB shapes per (category, key)	Documented at service layer when each consuming module reads the setting
`approval_workflow.steps` / `approval_request.step_history` JSONB schemas	Formal validation at implementation

Billing Module (Locked 2026-06-10)

8 tables, 113 columns, schema billing

Merchant-side A/R + A/P control layer — state tracking and payment application only. NOT GL / accounting. NOT Vrida's SaaS subscription billing (Platform owns that — see "Billing Module Boundary" below).

Tables

Group	Table	Cols	Purpose
A/R	`ar_account`	12	One per customer; maintains `current_balance_cents` cache
A/R	`ar_charge`	19	Receivable from POS/Orders charge-account tender; idempotent on source refs
A/R	`ar_payment`	17	Customer payment received; unapplied portion sits as account credit
A/R	`ar_payment_application`	8	Append-only N:M junction: payment → charge; reversals via negative rows
A/R	`ar_statement`	19	Lightweight period snapshot; `included_charge_ids` JSONB avoids child table
A/P	`vendor_payable`	14	Payable referencing `purchasing.vendor_invoice`; one-per-invoice (idempotent)
A/P	`ap_payment`	16	Vendor payment issued; unapplied portion tracked via `applied_amount_cents`
A/P	`ap_payment_application`	8	Append-only N:M junction: payment → payable; triggers Purchasing write-back

Key decisions

Decision	Choice	Rationale
A/R and A/P symmetric but separate	Two independent sub-graphs (`ar_` + `ap_` + shared `vendor_payable`)	Different wiring: A/R sources from POS/Orders events; A/P sources from Purchasing invoices. Shared direction-discriminator table would obscure both.
Maintained balance caches	`ar_account.current_balance_cents`, `vendor_payable.open_amount_cents`	Same pattern as `pos.gift_card` / `pos.store_credit`. Charges/payments/applications are source of truth; caches enable fast balance reads without aggregating the full trail.
Application tables append-only	No `updated_at` / `deleted_at` on `ar_payment_application` and `ap_payment_application`	Financial allocation records must be immutable (audit trail). Wrong applications reversed via new offsetting row (negative `applied_amount_cents`). Matches `pos.gift_card_transaction` pattern.
Event-driven A/R charge creation	POS/Orders emit `AccountChargeCreated`; Billing creates `ar_charge` async	Checkout never blocks on Billing. Idempotency makes retries safe.
Idempotency DB-enforced	Composite CHECK constraints on `ar_charge` (source refs nullability + module/type pairing) + partial UNIQUE on source refs	A POS/Orders charge with null refs would bypass the unique dedup — the CHECK prevents that at the DB layer. Manual charges have null refs intentionally (no idempotency guard needed).
References, never duplicates, CRM/Purchasing	Credit terms / credit limit referenced from `crm.customer`; vendor payment terms from `purchasing.vendor`	Duplication would drift out of sync; single source of truth stays in the owning module.
Payment execution → Payments module	`stripe_payment_intent_id` columns are text seams (forward-ref); Billing records state, does not call Stripe	Payments module owns the Stripe API surface. Billing only tracks applied state.

Seams closed by this module

Seam	Direction	Status
`pos.sale_payment.charge_account_ref`	POS → Billing	CLOSED — `ar_charge.source_ref` / `source_payment_ref` FK back to `pos.sale` / `pos.sale_payment`
`orders.order_payment.charge_account_ref`	Orders → Billing	CLOSED — same `ar_charge` source ref columns
`purchasing.vendor_invoice.billing_ap_ref` / `payment_status_ref` / `paid_at`	Billing → Purchasing	CLOSED — `BillingService` writes these three columns on `vendor_invoice` when a `vendor_payable` is paid; `PurchasingService` writes no value
`ar_payment.stripe_payment_intent_id`, `ap_payment.stripe_payment_intent_id`	Billing → Payments	FORWARD-REF — plain text seam; FK added when Payments module locks

Deferred items (explicit triggers)

ar_adjustment table: v1 uses manual ar_charge with source_type = 'adjustment'. Add formal table when write-offs are a product requirement.
Formal customer_invoice, customer_credit_memo, AP payment batches/runs, dunning/collections, payment plans, GL posting, multi-currency — trigger: concrete product requirement names one.
ar_charge (charged_at) index — add when AR aging date-range report query is written.
ar_statement (period_start, period_end) index — add when statement generation query is written.
ap_payment open/unapplied partial status index — add when AP reconciliation query is written.

Reporting deferred-index delivered (2026-06-11)

Two indices delivered as part of the Reporting module lock. Index-only additive touches — module stays locked. See SCHEMA.md § reporting § Deferred Source-Table Indices.

billing.ar_charge (tenant_id, charged_at) BTree — serves mv_ar_aging aging bucket computation.
billing.ap_payment (tenant_id, status) WHERE status IN ('pending','partial') BTree partial — serves mv_ap_aging open/unapplied payables filter.

Billing Module Boundary (Clarified 2026-06-10)

The billing schema is merchant-side A/R + A/P control layer only. The prior 11_billing.md spec (115 features on SaaS subscription) described Vrida's internal billing of tenants — that responsibility belongs to Platform, which already has the tables. The feature spec file is marked superseded; see docs/modules/11_billing.md header.

Platform owns: Vrida → tenant (SaaS subscription billing)

Platform already has all the schema needed:

Platform table	Purpose
`billing_account`	Stripe Customer per tenant; payment method refs
`subscription`	Plan, tier, status, billing cycle, Stripe subscription ID
`subscription_invoice`	Per-cycle invoice; Stripe invoice ID
`payment`	Per-invoice payment attempt; status
`tier_definition`	Starter $49.99 / Pro $99.99 / Enterprise $199.99 monthly
`tenant_entitlement`	Per-feature/capability gating per tenant

Platform scope: SaaS tier plans, signup/trial, recurring subscription charges, prorations, usage metering/tier enforcement, SaaS dunning (failed payment retry + account suspension), plan changes (upgrade/downgrade/pause/cancel), subscription invoices, MRR/churn analytics, promo codes, Enterprise contracts/quotes. None of this is the billing schema's job.

Billing (`billing` schema) owns: merchant-side control layer

Tenant charges to / from its own customers and vendors. Not GL / accounting — state tracking and payment application only.

Customer A/R:

On-account charges sourced from POS (pos.sale_payment.charge_account_ref) and Orders (orders.order_payment.charge_account_ref)
Customer account balance; payments received + application against charges
Customer statements; aging
References (never duplicates) crm.customer credit terms / credit limit

Vendor A/P:

Payables referencing purchasing.vendor_invoice
Vendor payments + application against invoices
Write-back to purchasing.vendor_invoice: billing_ap_ref, payment_status_ref, paid_at (these three columns are written by BillingService only — PurchasingService writes no value)
References (never duplicates) purchasing.vendor payment terms

Payment execution: belongs to Payments module (PaymentsService). Billing records state; it does not call Stripe / ACH directly.

Seams into Billing (already designed in locked modules):

Seam column	Source module	Direction
`pos.sale_payment.charge_account_ref`	POS	POS → Billing (on-account sale creates AR charge)
`orders.order_payment.charge_account_ref`	Orders	Orders → Billing (order deposit/payment against charge account)
`purchasing.vendor_invoice.billing_ap_ref`	Purchasing	Billing → Purchasing (write-back: AP record ref)
`purchasing.vendor_invoice.payment_status_ref`	Purchasing	Billing → Purchasing (write-back: payment state mirror)
`purchasing.vendor_invoice.paid_at`	Purchasing	Billing → Purchasing (write-back: cleared timestamp)
`purchasing.purchase_order.amount_paid_cents`	Purchasing	Billing → Purchasing (write-back cache)

Deferred by default (v1 lean)

Explicit decisions to NOT build until required:

Formal customer invoice documents (customer sees a statement, not a formatted invoice)
AP payment batches / payment runs (one-off vendor payments only in v1)
Customer credit memos / write-offs / adjustments
Dunning / collections for merchant customers
Payment plans / installment financing for merchant customers
GL / accounting / journal posting / chart of accounts (QuickBooks integration via Integrations module)
Multi-currency settlement

Trigger: each deferred item is unlocked when a concrete product requirement names it. "We should have it" is not a trigger.

Payments module placeholder: `stripe_event_log` + `stripe_event_dead_letter`

The stale 11_billing.md spec referenced two tables: billing.stripe_event_log (Stripe webhook idempotency dedup — persists event.id to prevent reprocessing) and billing.stripe_event_dead_letter (failed event queue). These are Payments module tables, not Billing tables. The webhook pipeline (signature verification, idempotency, retry, dead-letter) belongs to PaymentsService. These tables land in the payments schema when the Payments module is designed. Do not create them in billing.

Purchasing Module (Locked 2026-06-10)

16 tables, 340 columns, schema purchasing

Groups

Group	Tables
Vendor Master	`vendor`, `vendor_contact`, `vendor_address`, `vendor_item`
Purchase Orders	`purchase_order`, `purchase_order_line`, `purchase_order_template`, `purchase_order_template_line`
Receiving	`purchase_receipt`, `purchase_receipt_line`
Invoice + 3-Way Match	`vendor_invoice`, `vendor_invoice_line`, `vendor_invoice_match`
Credit + Return	`vendor_credit`, `vendor_return`, `vendor_return_line`

Key decisions

Decision	Choice	Rationale
Vendor master location	`purchasing` schema	Masters live in owning module. Vendor is not a CRM concern.
`vendor.status` vocabulary	`active` / `inactive` / `on_hold`	`suspended` / `blocked` are tenant-account lifecycle states (platform layer) — wrong layer for a vendor.
`vendor_invoice.status`	No `'paid'` value	Billing owns payment state. `billing_ap_ref`, `payment_status_ref`, `paid_at` are Billing write-back columns; `PurchasingService` writes no value to them.
`vendor_invoice_match` mutability	Mutable	Match decisions can be revised. Audit module owns immutable history.
Billing write-back seam	Three columns on `vendor_invoice`	`billing_ap_ref` (AP record ref), `payment_status_ref` (payment state mirror), `paid_at` (cleared timestamp). Written by `BillingService` only.
`orders.draft_po_id` seam	FK enforced in this migration	`draft_po_id` was a plain UUID forward-ref in the Orders lock. Now that `purchasing.purchase_order` exists, the FK constraint is added in the purchasing migration: `ALTER TABLE orders.order_header ADD CONSTRAINT fk_draft_po FOREIGN KEY (draft_po_id) REFERENCES purchasing.purchase_order(id)`. Complemented by `purchase_order.source_order_id` (nullable FK → `orders.order_header`) — the bidirectional seam close.
Approval workflow	`approval_status` on `purchase_order`	CHECK IN (`'pending'`,`'approved'`,`'rejected'`); NULL when not triggered. `approved_by_user_id` and `approved_at` are NOT NULL when `approval_status = 'approved'` (service-enforced).
RMA enforcement	`rma_number` required when status ≠ 'draft'	`vendor_return.rma_number` is service-enforced NOT NULL for `authorized` / `shipped` / `vendor_received` / `completed` statuses.
Inventory integration	Receipt and return lines trigger `InventoryService`	`purchase_receipt_line` creation triggers stock inbound; `vendor_return_line` shipping triggers stock outbound. `inventory_movement_id` is a deferred forward-ref (plain UUID) until the movement log FK is stable.

Module seams

Seam	Other module	Status
`orders.order_header.draft_po_id` → `purchasing.purchase_order`	Orders	CLOSED — FK enforced in purchasing migration; `purchase_order.source_order_id` → `orders.order_header` closes the reverse direction
`purchase_receipt_line.inventory_movement_id` → `inventory.stock_movement`	Inventory	DEFERRED — plain UUID; FK added when movement log is stable. Trigger: first receiving flow implementation.
`vendor_return_line.inventory_movement_id` → `inventory.stock_movement`	Inventory	DEFERRED — plain UUID; FK added when movement log is stable. Trigger: first return flow implementation.
`vendor_invoice.billing_ap_ref` → Billing	Billing	NEVER ENFORCED — plain text reference; Billing writes this column.
`purchase_order.amount_paid_cents`	Billing	Billing write-back cache; read-only from Purchasing's perspective.
`purchase_order.ship_to_address_id`	—	References `purchasing.vendor_address` (our address on file with vendor), not `crm.address`.

Deferred items (explicit triggers)

F-1 (vendor.blackout_config): blackout_config JSONB is present but enforcement (blocking PO creation during blackout periods) is app-layer, not schema. Trigger: when vendor order creation flow is implemented.
F-2 (inventory_movement_id FKs): purchase_receipt_line.inventory_movement_id and vendor_return_line.inventory_movement_id are plain UUIDs until inventory.stock_movement table name and PK are confirmed stable. Trigger: first receiving or return flow implementation.
F-3 (Billing integration): The three write-back columns (billing_ap_ref, payment_status_ref, paid_at) are empty until Billing module (Module 11) is designed and BillingService is implemented.

Search touch (2026-06-11)

search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(name,'') || ' ' || coalesce(code,'') || ' ' || coalesce(legal_name,''))) STORED added to purchasing.vendor. GIN index on search_vector + GIN on (name gin_trgm_ops) added. Purchasing module: 16 tables, 341 cols (+1). legal_name is nullable (B2B/1099 vendor search); coalesce handles NULL. See Search module "Touch Manifest".

Payments Module (Locked 2026-06-10)

Schema payments, 8 tables, 112 cols. Tenant MERCHANT card processing via Stripe Connect — the tenant's customers pay the tenant (POS card sales, A/R payments). NOT Vrida SaaS subscriptions (= Platform). Vrida earns a per-transaction application fee (subscription + transaction-fee model). See scope boundary rationale in "Payments Module Scope (Decided 2026-06-10)" below.

Table summary

Table	Cols	Purpose
`stripe_connect_account`	14	Per-tenant Connect account; `charges_enabled` gates all live payments
`payment_intent`	25	Central incoming-money ledger — one row per charge attempt; many-per-source-ref (partial/multi-tender); full offline lifecycle; `tip_amount_cents` + `application_fee_amount_cents` separate from `amount_cents`; both-direction idempotency
`payment_refund`	15	Payments owns refund execution; partial or full; `source_refund_ref` traces back to `pos.sale_refund` / billing record
`payout`	13	Stripe-to-bank payout reference ("when did I get paid")
`dispute`	14	Chargeback lifecycle; `evidence_due_by` deadline sweep index
`payment_method`	13	Light card-on-file — Stripe is the vault; Vrida stores ref + display-safe `last4`/`brand` only; one-default-per-customer enforced
`stripe_event_log`	8	Webhook idempotency — insert-once, status-updated; global `UNIQUE (stripe_event_id)` (NOT tenant-scoped); `service_role` writer
`stripe_event_dead_letter`	10	Failed-webhook queue — one row per event, updated in place; `UNIQUE (stripe_event_id) WHERE resolved_at IS NULL` prevents double-drive

Key design decisions

Decision	Detail
Many payment_intents per source_ref	Partial payments and multi-tender sales require N charge attempts per source record. No unique on `source_ref`; index on `(source_module, source_ref)` for fan-in query.
stripe_event_id globally unique	Stripe event IDs are globally unique across all accounts; webhooks arrive at the account level before tenant resolution. The unique is unconditional — do NOT make it tenant-scoped. This is the deliberate deviation from the tenant-scoped uniqueness rule.
tip + application_fee separate columns	Reporting must distinguish: what the customer paid (`amount_cents`), what was tipped (`tip_amount_cents`), what Vrida earned (`application_fee_amount_cents`). `total_charged_cents = amount_cents + tip_amount_cents`; application fee is Vrida's portion of the charge, not added to it.
Offline-charge-fail lifecycle	`origin = 'offline'`, `status = 'queued'` at creation; `PaymentsService` executes on reconnect; `status = 'failed'` + `failure_reason` + writeback to `pos.sale_payment.status` on decline. Riskiest flow in the system — customer has already left.
event tables insert-once, NOT append-only	`stripe_event_log`: dedup on insert (global unique), record outcome on update (`status`, `processed_at`). `stripe_event_dead_letter`: one-row-per-event, updated in place. Standard webhook-idempotency pattern. "Append-only" label was a misnomer — corrected in schema.
payment_method light	Stripe is the card vault. `last4` and `brand` are display-safe; no raw card data ever enters Vrida.

Seam closures

Seam	Status
`pos.sale_payment.stripe_payment_intent_id` (text forward-ref)	CLOSED 2026-06-10 — Payments creates `payment_intent`; `PaymentsService` writes status back to `sale_payment.status`
`billing.ar_payment.stripe_payment_intent_id` (text forward-ref)	CLOSED 2026-06-10 — same writeback pattern
`billing.ap_payment.stripe_payment_intent_id`	NOT CLOSED — vendor A/P is manual (check/ACH); Payments does not execute A/P

POS touch (remains locked, dated additive)

pos.sale.tip_amount_cents added 2026-06-10 (+1 col). POS is now 19 tables, 279 cols. Still locked. Touch note in SCHEMA.md § pos header.

Deferred items

Item	Trigger
`payment_method` (exp_year, exp_month) expiry index	When card-expiry alert job is written

Audit Module (Locked 2026-06-10)

Schema audit, 7 tables, 118 cols. The system's cross-cutting record of WHAT HAPPENED + the compliance-workflow platform. Two clusters: (A) audit_log — the integrity-chained append-only event log that captures entity changes, config changes, access events, and permission changes that have no authoritative trail in another module; (B) compliance workflow — data_subject_request, data_breach_incident, compliance_task, dpa_agreement, subprocessor, audit_export_job.

Table summary

Table	Cols	Purpose
`audit_log`	22	Cross-cutting integrity-chained event log. Per-tenant hash chain (async). Points at existing authoritative trails via `source_table`/`source_ref`; never duplicates them.
`data_subject_request`	17	GDPR/CCPA rights workflow — access, erasure, rectification, restriction, objection, portability, opt-out. Statutory deadline tracked (`due_date NOT NULL`).
`data_breach_incident`	20	Data breach lifecycle — detection through regulator/subject notification and resolution. 72h GDPR clock enforced by CHECK on `regulator_notification_due_at`.
`compliance_task`	15	Deadline-tracked compliance obligations (SAR deadlines, breach notifications, DPA reviews, etc.). `'overdue'` is computed at query, not stored.
`dpa_agreement`	15	DPA version management — one row per version, `supersedes_dpa_id` chain. Covers Vrida–tenant, tenant–customer, and tenant–subprocessor DPAs.
`subprocessor`	13	GDPR Article 28 subprocessor register — maintained list of third-party processors. Own table (not JSONB on DPA) because regulators request it as a standalone list.
`audit_export_job`	16	Tamper-evident signed export jobs — scheduled weekly exports, on-demand, SAR data packages, regulator-bound bundles.

Key design decisions

Decision	Detail
Per-tenant async hash chain	`sequence_number` + `row_hash` + `prev_hash` chained per tenant (not global — tenants don't serialize against each other). `row_hash`/`prev_hash` are NULL on insert; set once by the async ingest step, never mutated after. Unchained-row contract: `row_hash IS NULL` is OK only at the tail of a tenant's sequence within the async SLA; a NULL row with a chained successor is a chain break — the daily verification job must alert.
Reference-don't-copy	`audit_log` captures events that have NO existing authoritative trail elsewhere. For domains with their own trails (`crm.customer_consent`, `identity.identity_access_event`, `inventory.stock_movement`, `billing.ar_payment_application`, `payments.stripe_event_log`, `pos.sale_line_tax`), `audit_log` POINTS at them via `source_table`/`source_ref`. DB-enforced: CHECK prevents populating `before_state`/`after_state` when `source_table` is set — pointer mode and copy mode are mutually exclusive.
Store full, mask at read	`before_state`/`after_state` stored complete for forensic integrity. PII masking is a read-time access-control layer applied by `AuditService`. `granularity_level` controls WHAT is logged (full/sensitive/minimal per tenant config in `admin.tenant_setting`), not whether it is masked.
Audit owns compliance reporting	GDPR/SAR responses, breach notifications, and regulator-bound exports live in Audit. The Reporting module (Module 6) owns operational BI only. Different audiences (compliance officers/regulators vs. operations staff).
`subprocessor` as own table	GDPR Article 28 requires tenants to maintain and disclose the subprocessor list. Regulators request it explicitly; it has its own lifecycle (added, reviewed, removed). JSONB on `dpa_agreement` was rejected: no queryable lifecycle, no per-subprocessor indexing.
`'overdue'` is computed, not stored	`compliance_task.status` CHECK does NOT include `'overdue'`. Overdue state is computed at query: `status IN ('open','in_progress') AND due_date < now()`. A stored derived status silently lies when the batch job lags.
Audit owns no config tables	`AuditService` reads retention period, granularity, and access-level config from `admin.tenant_setting` (the catch-all key-value store). No `audit.config` table.
audit.* not subscribed by operational modules	`audit.log_entry_created` and similar events are internal to the Audit module. Operational modules do not subscribe — prevents event cycles.
`audit_log` append-only, one-time hash link	No `updated_at`, no `deleted_at`. The sole post-insert write is the async chain step: `row_hash`/`prev_hash` transition NULL → value once. This is insert-then-link-once — same class as `payments.stripe_event_log`. "Flatly append-only/immutable" would be a misnomer; "insert-then-link-once" is the correct label.

Seam closures / boundary

Boundary	Status
`identity.identity_access_event`	Audit CONSUMES (pointer), does not duplicate
`crm.customer_consent`	Audit CONSUMES (pointer), does not duplicate
`inventory.stock_movement`	Audit CONSUMES (pointer), does not duplicate
`billing.ar_payment_application`	Audit CONSUMES (pointer), does not duplicate
`payments.stripe_event_log`	Audit CONSUMES (pointer), does not duplicate
`data_subject_request.response_artifact_ref`	NOT CLOSED — Files module not built; text seam
`dpa_agreement.document_ref`	NOT CLOSED — Files module not built; text seam
`audit_export_job.export_ref`	NOT CLOSED — Files module not built; text seam

Cut (not deferred — wrong for Vrida)

Cut item	Reason
HR / labor audit	HR is permanently out of Vrida scope
Nursery pesticide / USDA compliance records	Nursery-specific; generic-first ERP
Tax-rate-change audit	Stripe Tax owns calculation; no rate tables in Vrida
Tenant compliance docs	Already `admin.compliance_document`
Vrida's own certification display	Platform concern, not tenant-audit tables

Deferred items

Item	Trigger
AI anomaly / suspicious-activity detection (spec 8.5, 11.1)	When AI module is built
SOC 2 Type II readiness report	Year 2
`subprocessor` index on `dpa_id`	When "subprocessors by DPA" query is needed
`audit_export_job` `(tenant_id, status)` composite index	When export-job dashboard query is defined
`data_subject_request` / `compliance_task` / `data_breach_incident` `(tenant_id, due_date/detected_at)` composites	When per-tenant time-window queries are profiled

Reporting deferred-index delivered (2026-06-11)

audit.audit_export_job (tenant_id, status) BTree index delivered as part of the Reporting module lock. Index-only additive touch — module stays locked. Serves the export-monitoring operational dashboard (pending / running / completed export jobs) via ReportingService. See SCHEMA.md § reporting § Deferred Source-Table Indices.

Payments Module Scope (Decided 2026-06-10)

What Payments is — and is not

Payments is the tenant's merchant payment processing layer via Stripe Connect: the tenant's customers pay the tenant (POS card swipes, customer A/R payments). It is NOT Vrida subscription billing (tenant pays Vrida) — that is Platform's, already built. The two never touch. This is the same subscription-vs-merchant trap that made the original 11_billing.md spec stale; record this boundary now before schema design begins.

Direction	Owner	Examples
Tenant's customers → tenant (incoming money)	Payments	POS card sale, A/R payment, online order payment
Tenant → Vrida (Vrida SaaS subscription)	Platform	`platform.subscription_invoice`, `platform.payment`
Tenant → vendor (outgoing A/P)	Billing (`ap_payment`)	Vendor invoice settlement — manual/check/ACH; Payments does NOT execute these

Payments OWNS

Stripe Connect account state — per-tenant Stripe Connect account (charges-enabled, payouts-enabled flags). Gates whether a tenant can take live payments. Onboarding flow drives transitions; Payments module surfaces the state.

Vrida-side Stripe ledger — a full-fidelity (not thin) record of Stripe activity tied to this tenant:

Table (tentative)	Purpose
`payment_intent`	Unified record for every incoming charge. Links to source via `source_module` / `source_ref` (e.g. `pos` → `pos.sale_payment`, `billing` → `billing.ar_payment`). Carries offline-queue state (origin: online vs. offline; sync status). Single table covers both online and offline paths.
`payment_refund`	Payments owns refund execution against Stripe. POS and Billing reference the refund by id; they do not call Stripe directly.
`payout`	Records Stripe → tenant bank payouts ("when did I get paid"). Reconciliation surface for the tenant.
`dispute`	Stripe chargeback lifecycle. Payments owns response and evidence tracking.
`payment_method`	Saved card-on-file (light). Stripe stores the card; Vrida stores a vault reference / Stripe payment method id. Used for recurring A/R payments and saved cards at POS.
`stripe_event_log`	Webhook idempotency table. Dedup by Stripe `event.id`. Every inbound Stripe webhook is upserted here before processing.
`stripe_event_dead_letter`	Webhooks that failed processing after retries. Manual investigation surface.

Note: stripe_event_log and stripe_event_dead_letter were floating items from the stale billing spec. Their confirmed home is the payments schema.

Status writeback — on Stripe webhook receipt, PaymentsService updates its own payment_intent AND writes the resolved status back to the calling module: pos.sale_payment.status and billing.ar_payment.status. This is the only direction of write-back (Payments → POS/Billing status fields); Payments does not own the source records.

Payments DOES NOT OWN

Item	Owner
Vrida subscription revenue (tenant pays Vrida)	`platform` — already built
Why a payment happened (business context)	`pos.sale` / `billing.ar_account`
The tender record on a sale	`pos.sale_payment` — POS owns; Payments executes
Tax calculation	Stripe Tax — no rate tables here
Customer / vendor identity	`crm` / `purchasing`
Vendor A/P payment execution	`billing.ap_payment` — vendor payments are manual (check / ACH). `billing.ap_payment.stripe_payment_intent_id` is a nullable text seam for the rare card-paid vendor; Payments does not drive this flow.
Integration runtime / connector orchestration	`integrations` module (webhooks, OAuth flows, etc.) — Payments sits above the transport layer

Incoming-money Stripe seams this module closes

Seam	Location	Resolution
`pos.sale_payment.stripe_payment_intent_id` (text)	`pos` schema	Payments creates a `payment_intent` row; POS stores its UUID as a plain reference. Status writeback on webhook.
`pos.sale_payment` offline queue state	`pos` schema	Offline origin + sync status are now fields on `payments.payment_intent` (not a separate offline-queue table). Payments resolves the fail-after-customer-leaves risk.
`billing.ar_payment.stripe_payment_intent_id` (text)	`billing` schema	Same pattern — Payments creates; Billing references.
`admin.integration_config` (Stripe Connect `credentials_ref`)	`admin` schema	Payments reads the Connect account config from `AdminService`; does not own the config row.

The offline-charge-fails-after-customer-leaves path (sale synced, payment never captured) is the riskiest flow in the system. Payments owns the resolution logic and records the terminal outcome on payment_intent.

Module ownership

payments is its own schema — not folded into integrations. Reason: payments are core money flow with direct business-financial consequences, strict compliance requirements, and non-trivial state machines (payment_intent lifecycle, dispute response deadlines). Integrations owns the connector transport layer; Payments owns the money outcomes. The two interact via PaymentsService calling out to Stripe via the Stripe SDK (not via Integrations module service methods).

Notifications Module (Locked 2026-06-10)

Schema notifications, 11 tables, 166 cols. Event-driven delivery orchestrator. Consumes events from all modules; decides who gets notified on which channel; dispatches via providers (through IntegrationsService); tracks delivery status and engagement. All tables tenant-scoped with RLS.

Table summary

Table	Cols	Purpose
`notification_template`	13	Per-tenant template definitions with per-channel copy variants, versioning, `is_transactional` flag.
`notification`	26	The convergence point — one row per send intent per recipient regardless of origin (transactional event, campaign, journey, manual). Fans out to `delivery_attempt` rows. `origin_type` CHECK-enforced with discriminator ref consistency.
`delivery_attempt`	19	Provider-channel (email/sms/push) execution record. Mutable: status + engagement timestamps update as provider webhooks arrive. Journey branches read `opened_at`/`clicked_at` here.
`notification_preference`	13	Per-recipient product preference (channel/category/quiet-hours). NOT legal marketing consent — that's `crm.customer_consent`.
`in_app_notification`	17	Stateful persistent inbox item per recipient. Separate from `delivery_attempt` — in-app bypasses the delivery_attempt pipeline entirely.
`notification_quota_usage`	9	Per-tenant per-channel (email/sms/push) usage counter. Platform owns the tier limit; this table owns the count. In-app unmetered.
`inbound_message`	14	Incoming SMS/email replies. Single-message intake (v1); threaded conversation deferred.
`notification_journey`	11	Journey blueprint: trigger type, trigger config, status, audience.
`journey_step`	14	Ordered steps within a journey (send/wait/branch/exit). Branch logic in `branch_condition_json` JSONB. `step_type` CHECK-enforced with required-config consistency.
`journey_enrollment`	15	Per-recipient journey execution state. `next_action_at` is the scheduler hook.
`campaign`	15	Bulk send to a CRM-queried audience. Recipients = `notification` rows with `campaign_id`; no `campaign_recipient` table in v1.

Key design decisions

Decision	Rationale
Consent is CRM's — zero consent tables in `notifications`	Marketing consent lives in `crm.customer_consent` / `crm.customer.marketing_opt_in`. Queried at send-time; opt-outs written to CRM via `CRMService`. `notification_preference` is product preference (channel/category/quiet-hours only). `notification.status = 'consent_blocked'` is an outcome flag, not a consent store. Legally load-bearing: TCPA/CAN-SPAM/GDPR exposure from two sources of truth.
`notification` is the convergence point	Every send origin (transactional event, campaign, journey step, manual) creates ONE `notification` row per recipient. `origin_type` CHECK ensures the correct refs are set for each origin type. Rejected: per-origin send tables (campaign_send, journey_send) which would duplicate the dispatch pipeline.
`delivery_attempt` mutable, not append-only	Status and engagement timestamps update post-insert as provider webhooks arrive. Journey branch conditions evaluate `opened_at`/`clicked_at`. Correctly has `updated_at`/`deleted_at`. This is the OPPOSITE of the audit/event-log pattern — do not relabel append-only.
In-app bypasses `delivery_attempt` entirely	In-app sends fan out to `in_app_notification` (the persistent inbox item), not a `delivery_attempt`. In-app has no external provider, no provider ref, no webhook engagement tracking. `delivery_attempt.channel` covers email/sms/push only. `notification_quota_usage` excludes `'in_app'` (unmetered, no provider cost).
Branch logic in JSONB, not child tables	v1 branching (opened/clicked within N days) is simple enough for `branch_condition_json` JSONB. `journey_branch`/`journey_step_condition` child tables are a documented deferred item — add only if branching grows AND/OR trees.
Quota: platform owns LIMIT; notifications owns USAGE	`notification_quota_usage.sent_count` is a maintained counter. Platform is authoritative for the limit value (read via `PlatformService` at dispatch). Do not copy quota limits into this table.
Exactly-one-recipient enforced by `num_nonnulls` CHECK	Four tables (`notification`, `notification_preference`, `in_app_notification`, `journey_enrollment`) use `CHECK (num_nonnulls(col_a, col_b) = 1)`. The weaker `OR NOT NULL` pattern allows both to be set simultaneously — that's an ambiguous row.
`journey_step.step_order` UNIQUE per journey	`UNIQUE (journey_id, step_order) WHERE deleted_at IS NULL`. Duplicate step orders would make engine ordering non-deterministic.

Read seams (not copied tables)

Seam	Via	Purpose
`crm.customer_consent` + `crm.customer.marketing_opt_in`	`CRMService` (read at dispatch)	Marketing consent gate
`platform.tenant_entitlement`	`PlatformService` (read at dispatch)	Quota limit lookup
`IntegrationsService` (provider send)	Text seam — Integrations not built	Email/SMS/push delivery
`audit.audit_log` via `AuditService`	Write seam — not a FK	Send logging

Deferred items

Item	Trigger
`campaign_recipient` table	Pre-send audience snapshots / suppression reporting needed
`message_conversation` (threaded inbox, assignment, SLA)	Staff need threaded two-way conversations
`journey_branch` / `journey_step_condition` child tables	Branch logic outgrows JSONB (AND/OR trees)
AI features (9 total: predictive send-time, cost-optimized channel, spam score, etc.)	AI module ships
Advanced analytics (open/click dashboards, campaign ROI, cross-channel)	v1.1 / Reporting module integration
Loyalty notifications	Consumer Layer `rewards` module ships

Integrations Module Scope (Decided 2026-06-11)

Integrations = the generic external-connector RUNTIME. One connector framework reused across all integrations — NOT per-integration tables. It executes API calls, runs sync jobs, processes inbound webhooks for its own connectors, delivers outbound webhooks, and logs/retries — reading config from Admin, never duplicating it.

What Integrations OWNS

Responsibility	Details
Generic connector runtime	Connector instance + runtime state per tenant per integration type. Reads `admin.integration_config` for credentials_ref + sync_schedule. Writes back `last_sync_at` / `last_sync_status` to `admin.integration_config` — controlled cross-schema write (same pattern as Billing → `purchasing.vendor_invoice` writeback).
Sync execution + history	`sync_run` (one per sync job execution), per-item results, sync error/dead-letter log. Framework is reused: QuickBooks v1.0; Sage/Xero/Shopify/WooCommerce v1.5 via the same tables.
Provider execution log for Notifications	Mechanical API-call record: did the Resend/Twilio/FCM call succeed, rate-limit, retry, dead-letter? This is the connector's operational state — NOT the business delivery outcome (that is `notifications.delivery_attempt`, which is authoritative). Reference-don't-copy: Integrations owns the mechanical call layer; Notifications owns business delivery + engagement. Flow: `NotificationsService` calls `IntegrationsService.send()`, receives a `provider_message_ref`, stores it on `delivery_attempt`. Integrations does NOT write to `delivery_attempt`.
Inbound webhook receipt for its own connectors	One generic idempotent connector webhook event table (e.g. QuickBooks change notifications). Rule: each module processes the webhooks for the integrations IT owns. Payments owns Stripe inbound webhooks (`payments.stripe_event_log`). Integrations owns QuickBooks', Shopify's, etc. There is NO central webhook table spanning all modules.
Outbound webhook delivery runtime	Reads `admin.webhook_config` (tenant-configured outbound endpoints + event subscriptions). Executes delivery; records outcome + retry per endpoint.
Field mapping / transformation config	Per-connector field mapping (e.g. QuickBooks chart-of-accounts field map). Generic mapping tables reused across connectors.

What Integrations DOES NOT OWN

Item	Owner
Integration CONFIG (enabled/disabled, credentials vault ref, sync schedule)	`admin.integration_config` — Admin owns; Integrations reads + writes back sync status only
Outbound webhook CONFIG (URLs, event subscriptions)	`admin.webhook_config` — Admin owns; Integrations reads + delivers
Stripe (any of it)	Payments owns Stripe Connect + Terminal + payments + its inbound Stripe webhooks. "Stripe owns tax." Integrations has NO Stripe connector.
Business delivery records	`notifications.delivery_attempt` — Notifications owns delivery status + engagement
Tier limits	`platform.tenant_entitlement` — Integrations reads via PlatformService; no tier tables here

Import pipeline relocated OUT of Integrations (2026-06-11)

import_job / import_file / import_record are NOT part of the Integrations schema.

The AI-powered onboarding import pipeline (tenant uploads CSV/Excel/PDF/photo → AI identifies data type + maps fields → records extracted and validated → tenant resolves flags → data loaded into Inventory/CRM/Purchasing) is one-time onboarding data ingestion driven by AI — not a recurring external-system connector. The zero-mapping magic IS the AI (Claude identifies and maps the data); this pipeline has a hard AI module dependency for its core value. The import pipeline will be designed with the AI module or as a dedicated onboarding cluster. The Integrations connector framework is shaped around external APIs, OAuth, batch sync, and retry — none of which characterize the import pipeline.

Previous assignment (PROJECT_DECISIONS "AI-Powered Self-Serve Onboarding", SCHEMA.md integrations placeholder, FEATURE_CATALOG.md, ARCHITECTURE.md, ROADMAP.md) pointed import_job/import_file/import_record at Integrations. That assignment is superseded by this decision. Those references remain in older docs as historical artifacts; this decision is authoritative.

v1.0 connectors actually built

Connector	Status	Notes
QuickBooks	v1.0	The reference connector — shapes the framework (OAuth creds via Admin, batch sync_run, inbound QB webhook, retry/dead-letter). GL export: journal entries, chart of accounts sync via BillingService/PurchasingService.
Notification providers (Resend email, Twilio SMS, FCM/APNS push)	v1.0	Thin provider-execution layer. Called by NotificationsService; returns provider_message_ref.
Picas CSV	v1.0 (post-pivot: mapping config only)	After the multi-vertical pivot, Picas is a mapping config, not special tables. Connector type = 'picas_csv'; field mapping config stored in the generic field-mapping tables. No dedicated Picas schema tables.
Outbound webhook delivery	v1.0	Runtime for `admin.webhook_config` endpoints.

Deferred connectors (framework supports; not built in v1.0)

Connector	Target	Notes
Shopify / WooCommerce / Wix	v1.5	eCommerce sync — inventory, orders, customers
Sage / Xero / Sage Intacct	v1.5	Accounting sync; same QB pattern
Mailchimp / SendGrid	v1.5	Marketing sync (listed in Admin §10.7; not in ROADMAP v1.0)
Workforce sync (Gusto / ADP / Deputy)	Likely not built	HR is permanently out of scope for Vrida (cut 2026-06-10). Workforce integrations lose their purpose without the HR module. Flag if a customer requires it — but do not plan for it.

Generic-first principle

The connector framework is vertical-neutral. Picas is a mapping config, not a table. QuickBooks is the v1.0 reference connector that shapes the framework. Every future connector (Shopify, Xero, etc.) reuses the same connector / sync_run / connector_webhook_event / connector_error tables with a different connector_type value. No connector gets its own schema tables unless its data model is categorically different from the batch-sync + OAuth + webhook pattern.

Integrations Module (Locked 2026-06-11)

Schema integrations. 9 tables, 127 cols. Generic external-connector RUNTIME — one connector framework reused across all integration types (NOT per-integration tables). Executes API calls, runs sync jobs, processes inbound webhooks for its own connectors, delivers outbound webhooks, and logs/retries. Reads config from Admin; writes back sync status.

Tables

Table	Cols	Purpose
`connector`	13	Runtime anchor per integration instance. Operational counterpart to `admin.integration_config`. Tracks connection health, runtime status, `source_type` (api/file/webhook), and a denormalized pointer to the most recent sync run. `connector_type` text discriminator routes connector-specific logic.
`connector_credential`	13	Rotating OAuth token state (access_token_ref + refresh_token_ref — vault refs, never raw). `expires_at` drives the proactive refresh sweep. Distinct from `admin.integration_config.credentials_ref` (static initial grant pointer). One active credential set per connector enforced.
`sync_run`	18	One row per sync job execution. Mutable: status progresses queued→running→terminal; record counts update as processing proceeds. `run_type` discriminator (scheduled/manual/initial_import/webhook_triggered/file_import). File-import runs must carry `file_ref` (enforced by CHECK). Integrations writes `last_sync_at`/`last_sync_status` back to `admin.integration_config` on completion.
`sync_run_item`	14	Per-record sync result. `connector_id` denormalized for direct connector-level queries. `local_ref` (polymorphic Vrida entity id) + `external_record_id` form the cross-system identity map.
`sync_error`	14	Error and dead-letter log per connector. Retryable: `retry_count` + `last_retry_at` drive the retry loop; `resolved_at` marks closure. Indexed for both open-error sweep and retry-worker sweep.
`provider_call`	16	Thin mechanical API-call log for provider sends (Resend/Twilio/FCM). Status state machine: queued → sent (202 Accepted, async) → succeeded (confirmed). `provider_message_ref` must be non-null when status='succeeded' (enforced by CHECK) — this is the value Notifications cross-looks-up. NOT a delivery record.
`connector_webhook_event`	12	Inbound webhook idempotency log. Insert-then-update-status (no `deleted_at`, no `updated_at`). `tenant_id` nullable (resolved post-receipt). GLOBAL UNIQUE on `external_event_id` (not tenant-scoped, not partial — webhooks arrive pre-tenant-resolution; Guard: never change this). Written via `service_role`.
`webhook_delivery`	16	Outbound webhook delivery execution. Reads `admin.webhook_config` for endpoint. `target_url` snapshotted at send-time. `next_retry_at` drives retry sweep.
`field_mapping`	11	Transformation config (external field → Vrida field, optional transform). Used by API-sync (QB chart-of-accounts) and file-import (Picas CSV) connectors. Picas-specific config is DATA stored here — generic-first in action.

Key design decisions

Generic connector framework, not per-integration tables. One connector_type text discriminator routes logic. QuickBooks is the v1.0 reference connector. Shopify, Sage, Xero use the same tables in v1.5.
Config-vs-runtime split + writeback. admin.integration_config owns enabled/disabled toggle, credentials_ref, sync_schedule. Integrations owns runtime. Reads Admin at execution time; writes back last_sync_at/last_sync_status — controlled cross-schema write (same pattern as Billing→Purchasing). No Admin config columns duplicated into Integrations.
provider_call mechanical-only (reference-don't-copy). provider_call holds API-call state: did the call succeed, rate-limit, retry? notifications.delivery_attempt is the authoritative business delivery + engagement record. provider_message_ref is a handoff (Integrations returns it; Notifications stores it on delivery_attempt). No recipient, content, delivery_outcome, or engagement columns on provider_call.
Owner-processes-own-webhooks. Payments owns Stripe inbound webhooks (payments.stripe_event_log). Integrations owns QuickBooks', Twilio's, etc. via connector_webhook_event. No central cross-module webhook table.
connector_credential is rotating OAuth state, not static config. admin.integration_config.credentials_ref is the static vault pointer to the initial OAuth grant. connector_credential holds the live rotating access/refresh tokens. These serve different lifecycles: Admin's ref is written once at setup; connector_credential rotates hourly.
Global webhook unique on external_event_id. Not tenant-scoped, not partial. Webhooks arrive before tenant resolution; provider event IDs are globally unique. Guard: do not change to a partial or tenant-scoped unique.
source_type = 'file' = deterministic known-format import. field_mapping + sync_run handle Picas CSV and other templated imports. AI zero-mapping (arbitrary file → AI infers structure) is NOT here — relocated to AI module.
Auth providers not here. SSO/SAML/social-login = Supabase Auth + identity.sso_provider. Integrations connects to business systems only.

Seams closed

Seam	Direction	How closed
Notifications provider sends (Resend/Twilio/FCM)	Notifications → Integrations	`NotificationsService` calls `IntegrationsService.send()` → receives `provider_message_ref` → stores on `delivery_attempt`
`admin.integration_config` runtime anchor	Integrations reads; writes back	`connector.integration_config_id` FK; `last_sync_at`/`last_sync_status` cross-schema writeback via `AdminService`
`admin.webhook_config` outbound delivery	Integrations reads	`webhook_delivery.webhook_config_id` FK
Twilio inbound SMS → Notifications	Integrations receives; routes	`connector_webhook_event.routed_to` = `'notifications.inbound_message'`; `NotificationsService` processes

Deferred

Item	Status
Shopify / WooCommerce / Wix connectors	v1.5 — framework supports via `connector_type`; not built in v1.0
Sage / Xero / Sage Intacct connectors	v1.5 — same QB pattern
Mailchimp / SendGrid connectors	v1.5
Workforce sync (Gusto / ADP / Deputy)	Likely not built — HR permanently out of scope (cut 2026-06-10)
AI zero-mapping import pipeline	Relocated to AI module — not here
`sync_run` composite index `ON (connector_id, started_at)`	Add when sync-run dashboard time-range queries built

Reporting deferred-index delivered (2026-06-11)

integrations.sync_run (tenant_id, connector_id, started_at DESC) BTree composite index delivered as part of the Reporting module lock. Index-only additive touch — module stays locked. Serves mv_sync_health connector-health aggregation queries. See SCHEMA.md § reporting § Deferred Source-Table Indices.

Files Module (Locked 2026-06-11)

Schema files. 3 tables, 43 cols. Generic file-metadata registry over Cloudflare R2. R2 owns the bytes; Files owns metadata + access control + lifecycle + storage quota. Closes 11 R2 seams across 8 modules (+ inventory.item_image via file_id additive touch).

Tables

Table	Cols	Purpose
`file`	19	Generic metadata registry — one row per R2 object. Stores R2 key, display metadata, visibility tier, upload status, polymorphic owner discriminator, lifecycle retention floor. `tenant_id` nullable (platform-level/Vrida-own assets). `status` tracks presigned-upload lifecycle (pending→uploaded).
`file_access_grant`	16	Fine-grained sharing for the cases `visibility` alone cannot express. Grantee discriminated by `grantee_type` (user / customer / external / public_link). DSR-to-subject case (GDPR legal requirement): `grantee_type='external'`, `grantee_identifier=email`, `expires_at` set. Discriminator CHECK enforces exactly one grantee ref. Access valid only when `revoked_at IS NULL AND deleted_at IS NULL AND (expires_at IS NULL OR expires_at > now())`.
`file_storage_usage`	8	Per-tenant storage counter (maintained cache). `total_bytes` + `file_count` incremented on upload-complete; decremented on delete. Reconcilable from `file` rows. Platform's `tenant_entitlement` owns the tier storage LIMIT; this owns USAGE. One usage row per active tenant.

Key design decisions

Bytes in R2, metadata here. files.file stores the R2 object key + metadata only — never file content in Postgres. Reference-don't-copy for blobs. R2 is the system of record for bytes; Files is the system of record for metadata.
One generic file table with polymorphic owner backref. All modules' files in one file table. Owner tracked via owner_module / owner_type / owner_ref discriminator (same pattern as audit_log.source_table). Owning modules hold file_id FK; Files holds the backref for lifecycle/orphan-cleanup. NOT enforced FK back to owners (polymorphic cross-module). owner_module + owner_ref are all-or-nothing (enforced by CHECK).
Three visibility tiers with PUBLIC-RLS-BYPASS. public = CDN read, no auth (logos, product images — deliberately bypasses tenant isolation for reads). private = tenant-RLS. signed = time-limited URL generated by FilesService.getSignedUrl() on demand. RLS read policy: WHERE visibility = 'public' OR tenant_id = current_setting('app.current_tenant_id')::UUID. NULL-tenant + non-public files are service_role only. Writes always tenant-scoped; never public write.
item_image FK-not-absorbed. inventory.item_image stays in the Inventory schema for display ownership (primary flag, sort order, alt text, direct CDN URL). files.file owns the R2 object + lifecycle + quota. item_image.file_id is the linking FK. item_image.storage_key and files.file.r2_key should match on the same object.
file_access_grant for what visibility cannot express. DSR package delivery to exactly one data subject is a GDPR legal requirement — visibility cannot do per-recipient time-limited access. file_access_grant handles: DSR-to-subject, external-auditor, customer-specific documents, revocable public links.
Storage usage here / limit in Platform. file_storage_usage tracks per-tenant USAGE (maintained cache). Platform's tenant_entitlement owns the tier storage LIMIT. Upload flow checks limit vs usage, increments on completion, decrements on deletion. Same pattern as notification_quota_usage.
Platform R2 exception — documented, not a violation. platform.tenant_data_lifecycle.download_url (tenant data exports) and platform.agreement_version.document_url (legal docs) are Platform-managed R2 refs — generated inline by Platform jobs, not routed through FilesService. Documented exception; do not move them to Files.
Presigned upload is service-layer only. FilesService generates the presigned PUT URL; caller uploads directly to R2; FilesService records metadata on completion and advances status from 'pending' to 'uploaded'. No presigned-URL table.

Seams closed

Owning column	File kind	Visibility
`admin.tenant_branding.logo_ref` → `files.file.id`	Brand logo	`public`
`admin.compliance_document.document_ref` → `files.file.id`	Compliance PDF	`private`
`crm.customer_tax_certificate.document_ref` → `files.file.id`	Tax cert PDF	`private`
`pos.sale.signature_ref` → `files.file.id`	Canvas signature PNG	`private`
`pos.guarantee.signature_ref` → `files.file.id`	Guarantee canvas signature PNG	`private`
`purchasing.purchase_receipt.shipment_photo_ref` → `files.file.id`	Receiving-dock photo	`private`
`integrations.sync_run.file_ref` → `files.file.id`	Import file (Picas CSV)	`private`
`audit.audit_export_job.export_ref` → `files.file.id`	Audit export	`signed`, retain 7yr
`audit.data_subject_request.response_artifact_ref` → `files.file.id`	DSR data package	`signed` + `file_access_grant`
`audit.dpa_agreement.document_ref` → `files.file.id`	Signed DPA	`private`, long retention
`inventory.item_image.file_id` → `files.file.id`	Product images — additive col	`public`

NOT closed (documented exceptions): platform.tenant_data_lifecycle.download_url + platform.agreement_version.document_url (platform-managed R2 — not Files-routed). platform.agreement_acceptance.signature_ref (HelloSign envelope ID — not R2). platform.subscription_invoice.invoice_pdf_url (Stripe-hosted — not R2). Vault *_ref cols (secrets manager — not object storage).

Additive touches (locked-module changes at Files lock)

Module	Touch	Col change	Date
`inventory`	`inventory.item_image.file_id UUID nullable FK files.file`	+1 col → inventory now 236 cols	2026-06-11
`purchasing`	`purchasing.purchase_receipt.shipment_photo_ref` FORWARD-REF label added	None (doc only)	2026-06-11

Deferred

Item	Status
Image thumbnails / resize	v1.5 — no seam requires multiple sizes
Virus scanning / content validation	v1.5 — service-layer hook, no table
File versioning (`previous_file_id`)	Deferred — no seam references document version history
Platform inline R2 refs (`tenant_data_lifecycle`, `agreement_version`)	Documented exception — Platform-managed; revisit only if it causes real operational problems
Storage quota tier limit (`storage_bytes_limit` in `tenant_entitlement`)	Trigger: when `file_storage_usage` enforcement is wired into the upload flow

AI Module (Locked 2026-06-11)

Schema ai. 4 tables, 71 cols. AI owns two clusters: (1) the onboarding import pipeline — zero-mapping AI ingestion from arbitrary files (AI infers structure; human reviews; loads to tenant tables); (2) the AI feature runtime — just ai_request, the Bedrock-call log. All other AI features (Vrida Sage, send-time optimization, plant enrichment, anomaly detection) are service-layer over existing module data; they generate NO new tables. Closes the platform.tenant_setup_task onboarding-import dependency (3 milestone codes: inventory_imported, customers_imported, vendors_imported).

Tables

Table	Cols	Purpose
`import_job`	17	Import batch header — one row per tenant import run. Owns the import lifecycle status (`uploading`→`processing`→`review_pending`→`loading`→`completed`/`failed`/`cancelled`) independently of `platform.tenant_setup_task`, which owns task-level retry (`retry_count`/`next_retry_at`). `source` discriminator: `'onboarding'` (milestone-gated) vs `'manual'` (post-onboarding re-import). DB-enforced: `source='onboarding'` requires `setup_task_code IS NOT NULL`.
`import_file`	15	One uploaded file within an import job. AI-inferred column mapping stored as per-file JSONB (`inferred_mapping`) — ephemeral, never reused across files (zero-mapping = one inference per file, NEVER shared with `integrations.field_mapping`). `file_id` FKs to `files.file`; bytes accessed via `FilesService`.
`import_record`	21	The load-bearing human-in-the-loop table. One row per parsed file row. Carries: AI proposal (`raw_data`, `mapped_data`, `confidence_score`), human decision (`review_status`, `corrected_data`, `reviewed_by_user_id`), load outcome (`load_status`, polymorphic target ref). Two DB-enforced safety-rail CHECKs: (1) `load_status='loaded'` requires `review_status IN ('auto_accepted','accepted')` AND full target ref — prevents unreviewed AI data loading to live tables; (2) polymorphic target all-or-nothing (module/table/id coherent). `corrected_data ?? mapped_data` is what loads.
`ai_request`	18	Bedrock-call log — mechanical metadata only (model, caller, token counts, cost, latency, status). NEVER stores prompt text or response content (reference-don't-copy — same principle as `integrations.provider_call`). `tenant_id` nullable (platform-level AI calls, e.g. `shared.plant` enrichment, have no tenant context). Aggregated per tenant per period to increment `platform.tenant_usage_summary.ai_calls_count`.

Key design decisions

Zero-mapping vs. deterministic (the Integrations boundary). import_file.inferred_mapping is per-file JSONB — one inference per file, never reused. Deliberately NOT integrations.field_mapping (reusable config). Deterministic known-format imports (Picas CSV, structured exports) stay in Integrations. The boundary: if AI must identify what's in the file, it belongs here; if a human has pre-configured the mapping, it belongs in Integrations.
Human-in-the-loop (DB-enforced). The two CHECKs on import_record are the safety rail: a row cannot reach load_status='loaded' without review acceptance (review_status IN ('auto_accepted','accepted')) and a full target ref. AI-inferred data never auto-loads into live tenant tables.
Cluster 2 is service-layer + call log only. Vrida Sage, send-time optimization, plant enrichment, anomaly detection are AIService logic over existing data. Zero new tables. The ONLY cluster 2 schema artifact is ai_request. All AI features route through AIService; no module calls Bedrock directly.
ai_request reference-don't-copy. Logs call metadata only — model ID, token counts, cost, latency, status, caller. Never prompt text, response content, or business data. Parallel to integrations.provider_call. Aggregated to platform.tenant_usage_summary.ai_calls_count.
import_job status vs. tenant_setup_task retry — no duplication. import_job owns the import lifecycle; platform.tenant_setup_task owns task-level retry (retry_count/next_retry_at) and milestone tracking. setup_task_code is a text seam (not FK). AIService writes completion back to tenant_setup_task on import success.
ai_response_cache forward-decision (cache-home = ai schema). When built (consumer phase), the query-result cache for plant-care AI belongs in the ai schema (AI infrastructure), not consumer_app (consumer feature data). Resolves the open "customer_app OR ai schema" question in PROJECT_DECISIONS Consumer Layer Architecture.

Seams closed

Seam	Direction	How closed
`platform.tenant_setup_task` onboarding-import milestone	Import pipeline writes completion	`AIService` writes `completed_at` + result to `tenant_setup_task` via service layer on import job completion.
`import_file.file_id → files.file`	AI reads R2 bytes via Files	FK enforced at AI migration 2026-06-11. Bytes accessed via `FilesService.getSignedUrl()`. AI never stores file content in Postgres.
`shared.plant` + `shared.plant_common_name` enrichment	AI writes via `service_role`	`AIService` writes enriched care data (`data_source='ai_generated'`, `is_verified=false`) + new common-name aliases. Service-layer seam — no AI table.
`notifications.delivery_attempt` send-time optimization	AI reads engagement timestamps	`AIService` queries `opened_at`/`clicked_at` and returns timing recommendation to `NotificationsService`. Service-layer read seam — no AI table.
`import_record` → `inventory.item` / `crm.customer` / `purchasing.vendor`	AI loads accepted rows into owning module	Polymorphic loose backref on `import_record` (`target_module`/`target_table`/`target_row_id`). Not enforced FK — same pattern as `audit_log.source_ref`.
`ai_request` count → `platform.tenant_usage_summary.ai_calls_count`	Aggregation job increments usage counter	Platform's `tenant_entitlement` (`entitlement_code='ai_pack'`) owns the tier cap.

Deferred

Item	Status
`enrichment_job` (batch re-enrichment tracking)	v1.5 — v1.0 plant enrichment is service-layer; add when scheduled batch re-enrichment is a product requirement
`ai_response_cache` (query result cache)	Consumer phase — belongs in `ai` schema (forward-decision recorded above)
`ai_feedback` (thumbs-up/down quality signals)	v1.1 — add when AI feature quality needs structured user-feedback collection
`anomaly_alert` (persisted anomaly detection results)	v1.1 — v1.0 anomaly detection is service-layer; add when persistence/acknowledgement workflow is a product requirement

Search Module (Locked 2026-06-11)

Schema search. Zero tables. Postgres-native FTS (Option A): tsvector generated columns + pg_trgm dual GIN indexes, delivered as 6 additive FTS touches to locked modules + SearchService abstraction layer. No external search engine in v1.0.

Architecture decision

Option A locked. Rejected alternatives:

Option B (external engine — Typesense / Algolia / OpenSearch): tenant-isolation risk in external index (FTS engines index globally; tenant filtering is application-layer, not DB-enforced RLS); sync complexity (change-data-capture pipeline per touched table); infra cost and operational burden for v1.0 scale that Postgres handles natively; no v1 benefit.
Option C (denormalized search_document table): violates reference-don't-copy — duplicates source-row content into a separate table, creating two sources of truth; invalidation complexity; not needed when generated columns keep the index co-located with the source row.

Escape hatch: SearchService abstracts the engine; callers never query search_vector directly. When scale demands, swap the engine (Typesense / Algolia) inside SearchService without changing call sites.

The 6 FTS touches (dual-indexed: tsvector GIN + trgm GIN)

Module	Table	`search_vector` sources	Trgm GIN on
`inventory`	`inventory.item`	`name`, `description`, `item_type`	`name`
`inventory`	`inventory.item_variant`	`sku`, `name`	`sku` + `name` (two trgm GINs — POS ring-up hot path + fuzzy variant-name search)
`crm`	`crm.customer`	`display_name`, `customer_number`, `company_name`	`display_name`
`purchasing`	`purchasing.vendor`	`name`, `code`, `legal_name`	`name`
`orders`	`orders.order_header`	`order_number`, `po_number`, `job_reference`	`order_number`
`pos`	`pos.sale`	`sale_number`, `po_number`, `job_reference`	`sale_number`

Total: 6 search_vector generated columns (+6 cols across 5 modules), 6 FTS GIN indexes, 7 trgm GIN indexes.

Key design decisions

Postgres FTS not external (Option A). Tenant isolation + reference-don't-copy justify staying in-DB for v1.0. See DR1.
tsvector generated column is a derived index, not a copy. Source row stays authoritative; search_vector is DB-maintained and never written directly. Not a reference-don't-copy violation. See DR2.
Dual index: tsvector + trgm — both required. tsvector handles multi-word / stemmed / stop-word FTS. trgm handles partial-match ("JM-0"), fuzzy typos, and prefix lookups. Both needed for real staff search input. tsvector alone silently fails on partial SKUs and variant names. See DR3.
Zero-table schema — touches + service-layer only. The stub imagined indexing-trigger tables, session-log tables, and ranking-config tables. Option A needs none of those. All search state is in source rows. See DR4.
pgvector / semantic search is AIService, not SearchService. AIService.semanticSearch() uses pgvector for embedding-based semantic lookup. SearchService is lexical + trigram only. Do not conflate. See DR5.
Customer-name order/sale search routes through CRM. order_header and sale carry no denormalized customer-name column — customer is linked via FK only. A single-pass search_vector @@ query for a customer name on order/sale returns nothing silently. SearchService MUST compose: CRM search → customer_id[] → filter orders/pos. See DR6.

Seams

SearchService reads	Mechanism	What flows
`inventory.item` + `inventory.item_variant`	`search_vector @@ query` + `sku gin_trgm_ops`	Product catalog and variant search
`crm.customer`	`search_vector @@ query` + `display_name gin_trgm_ops`	Customer name / number / company search
`purchasing.vendor`	`search_vector @@ query` + `name gin_trgm_ops`	Vendor name / code search
`orders.order_header`	`search_vector @@ query` + `order_number gin_trgm_ops`	Order number / PO / reference search
`pos.sale`	`search_vector @@ query` + `sale_number gin_trgm_ops`	Sale number / PO / reference search
CRM → Orders / POS	Two-step compose (CRM FTS → `customer_id[]` → filter)	Customer-name order/sale search

No module calls a search engine directly — all search routes through SearchService.

Deferred

Item	Status
`saved_search` table	v1.5 — add when save-search UX is a product requirement
Faceted / aggregation search	v1.5 — facet counts, range buckets, category drilldown
Consumer plant browse (attribute + zone facets)	Consumer phase — `plant_common_name.name_normalized` already serves import fuzzy-match; consumer attribute/zone browse deferred with Consumer Layer
External engine swap (Typesense / Algolia)	At scale — `SearchService` is the swap point
Semantic / pgvector search	`AIService.semanticSearch()` — not `SearchService`; see AI module

Reporting Module (Locked 2026-06-11)

Schema reporting. 3 tables, 43 cols. Terminal module — operational BI over ~15 locked schemas; nothing in v1.0 depends on it. Architecture already locked (30s-poll-from-matviews per ARCHITECTURE.md; service_role refresh; site_id first-class; MV-invalidation via CROSS_MODULE_CONTRACTS Rule 6). Schema = the must-freeze snapshot tables only; matviews + live queries are derived/service-layer.

Tables

Table	Cols	Purpose
`report_snapshot_run`	16	Idempotent month-end job header — one `status='completed'` row per `(tenant_id, snapshot_type, as_of_date)`. Prevents double-close. Audit trail for every snapshot execution.
`inventory_valuation_snapshot`	12	Frozen `avg_cost_cents` and `valuation_cents` as-of-then — unrecoverable after later receipts update weighted-average cost. Insert-once.
`period_close_snapshot`	15	Cross-module AR + AP + COGS freeze in one record. Reads `billing.ar_statement` for AR figure; adds AP + COGS. Insert-once. Approximation — not an accounting system.

Key design decisions

Snapshots freeze COMPUTED VALUES not rows (reference-don't-copy reconciliation). FK references stay live (item_variant_id, snapshot_run_id). No item name, SKU, customer name, or vendor name is copied. Only numeric computed values freeze — these become unrecoverable later (avg_cost changes with every receipt; period-close totals change as avg_cost drifts). Snapshots = intentional frozen results, not drift-prone caches. See DR1.
Idempotent period close. report_snapshot_run partial UNIQUE (tenant_id, snapshot_type, as_of_date) WHERE status = 'completed' AND deleted_at IS NULL prevents double-closing. Failed runs retry freely. See DR2.
Matviews are the primary aggregation pattern (not tables). 10 matview specs defined; each carries a Rule-6 refresh strategy (aggressive | staleness-window) + triggering *_changed event. Base tables only for must-freeze records. 30s-poll-from-matviews is the locked architecture. See DR4.
period_close_snapshot reads-not-shadows billing.ar_statement. Billing owns AR period snapshots; Reporting adds the cross-module summary (AR + AP + COGS together). See DR3.
site_id first-class dimension. On every snapshot table and every matview — not a join filter. Locked decision. See DR5.
Compliance reporting is Audit's. No compliance tables here. See DR6.
advanced_reporting entitlement gates RFM / vendor-performance / cross-module P&L. Checked at service layer.
Insert-once frozen snapshots. inventory_valuation_snapshot and period_close_snapshot rows are NEVER mutated. A correction produces a new report_snapshot_run. See DR7.

Non-table deliverables

10 matview specs (Rule-6 refresh strategy + triggering event — see SCHEMA.md § reporting § Materialized View Specifications): mv_sales_summary (aggressive, sales_changed), mv_sales_by_customer (30s, sales_changed), mv_inventory_valuation_current (aggressive, stock_changed), mv_gross_margin (30s, sales_changed), mv_customer_rfm (daily, sales_changed — entitlement-gated), mv_ar_aging (30s, ar_changed), mv_ap_aging (30s, ap_changed), mv_vendor_performance (daily, receipt_changed — entitlement-gated), mv_sync_health (30s, sync_changed), mv_notification_summary (30s, notification_changed).

5 deferred source-table indices delivered (index-only additive touches to locked source schemas — see SCHEMA.md § reporting § Deferred Source-Table Indices):

inventory.stock_movement_line (tenant_id, item_variant_id, created_at DESC) — cost-history / COGS
billing.ar_charge (tenant_id, charged_at) — AR aging
billing.ap_payment (tenant_id, status) WHERE partial — AP aging
integrations.sync_run (tenant_id, connector_id, started_at DESC) — sync health
audit.audit_export_job (tenant_id, status) — export monitoring

Reads from ~15 schemas (service_role, read seams, not FKs)

pos, orders, crm, inventory, purchasing, billing, payments, admin, integrations, notifications, audit, files, ai, platform, multi_loc. No FK from any of these to reporting.*. Terminal: zero forward-refs out.

Deferred

Item	Status
`tax_period_snapshot`	DEFERRED — Stripe Tax owns calculation and is system-of-record; Vrida is not a tax-filing system in v1.0. Build when/if direct tax filing is added.
Saved / scheduled report delivery (email, PDF)	v1.1
Notification advanced analytics (open/click, campaign ROI)	v1.1
Multi-site consolidated dashboards	v1.5
Consumer-side analytics	Consumer Layer phase (post-v1.0)
Streaming / CDC real-time push	v2.0
Custom report builder	v2+

Rewards Module (Locked 2026-06-12)

6 tables, 89 cols: loyalty_program (12), earning_rule (17), tier_level (12), reward_option (17), loyalty_account (14), points_ledger (17).

Second module of the Consumer Layer. Schema rewards. TENANT-SCOPED — the nursery owns its loyalty program. consumer_id is an FK dimension on loyalty_account (not the RLS scope) — structural inverse of consumer_tenant_link. Per-business loyalty: each nursery configures its own earning rules, tiers, expiry policy, and redemption catalog. Points balance is per (consumer, tenant) pair. Platform-wide aggregation is a future query over per-tenant data, not a schema redesign. Closes the pos.sale.points_earned/points_redeemed POS seam.

Architecture

Decision	Value
Schema	`rewards`
Tenancy	TENANT-SCOPED — `tenant_id` on all 6 tables, RLS `WHERE tenant_id = current_setting('app.current_tenant_id')::UUID`
Consumer boundary	`consumer_id` is a FK dimension on `loyalty_account` only. `ConsumerService` aggregates per-tenant balances for consumer-side display. Tenants NEVER query `consumer.*` directly.
RLS inversion	Inverse of `consumer_tenant_link`: that table is consumer-scoped with `tenant_id` as dimension; this schema is tenant-scoped with `consumer_id` as dimension.
Per-business loyalty	One loyalty program per tenant (v1). Earning rules, tiers, expiry policy, and redemption catalog are all tenant-owned. No cross-store pooling.
Seam closure	`points_ledger.sale_id → pos.sale` closes the POS seam. POS snapshots `points_earned`/`points_redeemed` on the sale row (receipt level); `rewards` owns the authoritative ledger.

Tables

rewards.loyalty_program (12 cols): Per-tenant loyalty program configuration. One program per tenant v1 (partial unique (tenant_id) WHERE deleted_at IS NULL). Owns base earn rate (points_per_dollar), rounding policy, minimum redemption threshold, and expiry policy type + value. Statuses: 'active'/'paused'/'archived'. Expiry types: 'never'/'rolling_months'/'calendar_year'/'inactivity_months'; value IS NOT NULL iff type is 'rolling_months' or 'inactivity_months' (CHECK enforced).

rewards.earning_rule (17 cols): Scoped bonus earning rules layered on the base earn rate. Queried at every earn event by RewardsService. rule_scope discriminator (8 values: purchase/item_category/item_variant/sale_threshold/birthday/signup/review/manual). earn_type coherence: 'multiplier' requires multiplier IS NOT NULL, flat_bonus_points IS NULL; 'flat_bonus' requires the inverse (CHECK enforced). 'sale_threshold' scope requires threshold_cents IS NOT NULL and scope_ref IS NULL (CHECK enforced). scope_ref is a loose text ref (category code or item_variant sku/slug).

rewards.tier_level (12 cols): Tier definitions per program (e.g. Bronze/Silver/Gold). Achievement threshold is min_points against loyalty_account.lifetime_points. Partial unique on (loyalty_program_id, min_points) WHERE deleted_at IS NULL prevents ambiguous tier resolution. earn_multiplier applies a per-tier bonus on top of all other earn rates.

rewards.reward_option (17 cols): The nursery's points-funded redemption catalog. Always nursery-funded. reward_type 4-way coherence CHECK: 'discount_amount' / 'discount_percent' / 'free_item' / 'perk'. tier_level_id gates redemption to members at or above a specific tier (real FK — not a loose code). free_item_variant_ref is a loose text ref. discount_percent range CHECK (> 0 AND <= 100) enforced. Boundary: points-funded redemption here; coupon/promo/Vrida-funded-settlement in offers.

rewards.loyalty_account (14 cols): Per-(consumer, tenant) loyalty account. One row per consumer per nursery. consumer_id NOT NULL — no stub accounts. balance_points and lifetime_points are maintained caches (reconcilable against points_ledger). current_tier_level_id + tier_achieved_at are maintained caches (tier-history-lite). Partial unique (tenant_id, consumer_id) WHERE deleted_at IS NULL. Index on (consumer_id) serves ConsumerService cross-store aggregation.

rewards.points_ledger (17 cols): Append-only source of truth. Insert-once: no updated_at, no deleted_at. Every earn, redemption, expiry, adjustment, and reversal is a new row. entry_type 5-way: 'earn'/'redeem'/'expire'/'adjust'/'reverse'. Full CHECK set: 7 conditional entry-type CHECKs + source coherence (sale_id XOR source_ref by source_type) + balance_after_points >= 0 (authoritative non-negative enforcement) + entry_type='expire' ↔ source_type='expiry' alignment. Self-referential FKs: expired_from_ledger_id (expire references earn row), reversed_ledger_id (reverse references original entry).

Key Decisions

Tenant-scoped / consumer-as-dimension. The nursery must be able to query its own loyalty program data — impossible under consumer-scoped RLS. consumer_id on loyalty_account identifies which consumer holds the account; the nursery still owns the data. See DESIGN_RATIONALE Rewards DR1.

points_ledger append-only source of truth. Balances are maintained caches: balance_points = SUM(amount_points) over the ledger; lifetime_points = SUM(amount_points) WHERE entry_type = 'earn'. Identical pattern to pos.gift_card/pos.store_credit/billing.ar_account. lifetime_points is never decremented by redemption or expiry — it is the tier-threshold value. See DESIGN_RATIONALE Rewards DR2, DR3.

No stub accounts — reconcile at claim. loyalty_account.consumer_id is NOT NULL. Unclaimed stubs receive no loyalty account. At claim, ConsumerService issues back-dated 'earn' rows for eligible historical sales, creating the account at claim time. Rejected: stub-points holding table (gaming risk + merge burden). See DESIGN_RATIONALE Rewards DR4.

Consent is CRM's. RewardsService reads crm.customer_consent (consent_type = 'loyalty') before crediting points. Zero consent tables in rewards. Same pattern as notifications reading crm.customer_consent. See DESIGN_RATIONALE Rewards DR5.

reward_option vs offers boundary. reward_option = points-funded redemption catalog ("500 points → $5 off") — always nursery-funded. offers module = coupon/promo/Vrida-funded discounts with settlement economics. Settlement economics forces separation. See DESIGN_RATIONALE Rewards DR6.

'reverse' vs 'adjust' distinction. 'reverse' = system-generated sign-flip for refund/voided sale/return; references original via reversed_ledger_id. 'adjust' = manual staff correction; note required; created_by_user_id set. Never conflate. See DESIGN_RATIONALE Rewards DR7.

Source coherence — sale_id XOR source_ref. source_type = 'sale' forces sale_id IS NOT NULL AND source_ref IS NULL; non-sale non-manual non-expiry types force source_ref IS NOT NULL AND sale_id IS NULL; 'manual'/'expiry' force sale_id IS NULL. Both-populated state is impossible. entry_type='expire' ↔ source_type='expiry' alignment enforced by CHECK pair. See DESIGN_RATIONALE Rewards DR8.

Hard non-negative balances (v1). CHECK (balance_points >= 0) on loyalty_account, CHECK (balance_after_points >= 0) on points_ledger. Over-redemption handled by capping or 'reverse' row, not debt.

Tier FKs are real FKs. loyalty_account.current_tier_level_id → rewards.tier_level and reward_option.tier_level_id → rewards.tier_level are enforced UUID foreign keys, not loose text codes.

Configurable expiry + per-row expires_at. loyalty_program.expiry_policy_type governs the program-level policy. points_ledger.expires_at carries per-earn-row expiry (set by RewardsService at earn time for 'rolling_months' policy). Expiry sweep reads this index to issue 'expire' rows.

THE SEAM CLOSURE: `points_ledger.sale_id → pos.sale`

pos.sale.points_earned and pos.sale.points_redeemed are receipt-level SNAPSHOTS on the sale row — the POS records how many points were involved at time of sale. RewardsService owns the authoritative ledger: every earn and redemption is a points_ledger row with sale_id pointing to the triggering sale. The FK points_ledger.sale_id → pos.sale is ENFORCED at rewards schema creation (POS locked 2026-06-10, FK is DONE). In FORWARD_FK_REGISTRY this FK moves to DONE at rewards lock.

Cross-Phase FK Seams

Column	Target	Status
`rewards.*.tenant_id` (6 FKs)	`platform.tenant`	ENFORCED FK (platform locked 2026-06-09; enforced at rewards schema creation) — DONE
`loyalty_account.consumer_id`	`consumer.consumer`	ENFORCED FK (consumer locked 2026-06-11; enforced at rewards schema creation) — DONE
`points_ledger.sale_id`	`pos.sale`	ENFORCED FK (pos locked 2026-06-10; enforced at rewards schema creation) — THE SEAM CLOSURE — DONE
`points_ledger.created_by_user_id`	`identity.identity_user`	ENFORCED FK (identity locked 2026-06-09; enforced at rewards schema creation) — DONE
`earning_rule.scope_ref`	`inventory.item_variant` / category code	LOOSE ref (polymorphic; `RewardsService` validates) — NEVER
`reward_option.free_item_variant_ref`	`inventory.item_variant`	LOOSE ref (variant may be archived; `RewardsService` validates) — NEVER
`points_ledger.source_ref`	various non-sale sources	LOOSE ref (polymorphic: review ID, etc.) — NEVER

Deferred

Item	Status
Referral rewards (`loyalty_referral`)	Future — friend-invite earn mechanics
Missions / challenges / badges / gamification	Future — Consumer App / Engagement phase
Household / family account pooling	Future — per-consumer-per-tenant v1; grouping is a separate relationship
Full tier-transition history (`loyalty_tier_history`)	Future — v1 uses `current_tier_level_id` + `tier_achieved_at` (history-lite)
Per-lot FIFO expiry (`points_bucket`)	Future — v1 uses `points_ledger.expires_at` (per-earn-row expiry sufficient for rolling/inactivity)
Multiple programs per tenant	Future — one-per-tenant v1; relax for seasonal/wholesale/VIP (remove partial unique, add `program_type`)
Platform-wide pooled points	Future query / view over per-tenant balances — not a schema change
`sale_line`-level earn granularity	Future — v1 earns on `sale` total; per-line earn uses `earning_rule.rule_scope = 'item_category'` at service layer
`consumer_app` module	Consumer Layer phase (post-v1.0)

Offers Module (Locked 2026-06-12)

4 tables, 68 cols: offer (27), offer_code (13), offer_assignment (15), offer_redemption (13).

Third module of the Consumer Layer. Schema offers. TENANT-SCOPED — every offer is AT a specific nursery (tenant_id NOT NULL on all tables). consumer_id is a targeting dimension. MERCHANT-FUNDED v1: funding_source = 'vrida' is enum-valid but service-guarded/deferred to v1.5. Closes the long-open settlement-economics question from Consumer Layer Architecture.

Architecture

Decision	Value
Schema	`offers`
Tenancy	TENANT-SCOPED — `tenant_id` on all 4 tables, RLS `WHERE tenant_id = current_setting('app.current_tenant_id')::UUID`
Consumer boundary	`consumer_id` is a FK targeting dimension (nullable on `offer`, NOT NULL on `offer_assignment`/`offer_redemption`). `ConsumerService` aggregates available offers cross-store for consumer app.
Funding	`funding_source` flag: `'business'` (nursery bears cost) / `'vrida'` (Vrida co-funds; v1 service-guarded). v1 = merchant-funded only.
Settlement	Settlement (Vrida→merchant credit when `funding_source='vrida'`) is PLATFORM's domain. Offers records facts and FEEDS platform; does not own money-movement. Deferred to v1.5.
Redemption seam	`offer_redemption.sale_id → pos.sale` (FK on offers side; `pos.sale` untouched — no POS additive touch; reference-don't-copy, same pattern as `rewards.points_ledger.sale_id`).

Tables

offers.offer (27 cols): The offer definition. Discount mechanics (discount_type 4-way: 'percent_off'/'amount_off'/'free_item'/'bogo'), max_discount_cents cap (percent-off only), funding_source, distribution type ('broadcast'/'targeted'/'code'), stacking policy ('exclusive'/'stackable'/'best_price'), exclusive_group, priority, eligibility_json, validity window, budget controls (budget_cents, budget_used_cents maintained cache). Full 4-way discount-type ↔ field coherence CHECK. distribution_type='targeted' OR consumer_id IS NULL enforced. budget_used_cents reconcilable from SUM(offer_redemption.discount_amount_applied_cents).

offers.offer_code (13 cols): Coupon codes for code-based distribution. normalized_code (uppercased/trimmed, plain text — coupons are typed/shared, not bearer secrets). Shared broadcast code (consumer_id NULL) vs per-consumer single-use code (consumer_id set). redeemed_count is a maintained cache reconciling to net redemptions: COUNT(redeem rows) - COUNT(reverse rows) for this code. Partial unique on (tenant_id, normalized_code) WHERE deleted_at IS NULL.

offers.offer_assignment (15 cols): The issued→viewed→claimed→redeemed/expired/cancelled lifecycle per consumer. The "before-redemption" state table — answers "which offers has this consumer been issued and not yet used?" NOT redundant with offer_redemption (post-use ledger). source records why assigned: 'targeted'/'broadcast_optin'/'birthday'/'campaign'/'manual'. eligibility_snapshot_json captures eligibility at issue time. Index on (consumer_id) serves ConsumerService cross-store available-offers read. Partial unique (tenant_id, offer_id, consumer_id) WHERE deleted_at IS NULL.

offers.offer_redemption (13 cols): Append-only redemption ledger. No updated_at, no deleted_at. redemption_type = 'redeem' (positive amount) or 'reverse' (negative amount, reversed_redemption_id required, note required). A voided-sale reversal is a NEW negative row — the original row is never mutated. Source of truth for offer.budget_used_cents. Idempotency guard: partial unique (tenant_id, offer_id, consumer_id, sale_id) WHERE redemption_type = 'redeem' (reversals exempt). THE SEAM: sale_id → pos.sale (DONE at lock).

Key Decisions

Tenant-scoped + funding_source flag. Every offer is at a specific nursery. funding_source records who absorbs the cost, not where the offer is redeemable. Cross-tenant Vrida offers (tenant_id NULL) are a future feature. See DESIGN_RATIONALE Offers DR-O1.

Merchant-funded v1 — settlement closes the open question. Settlement (Vrida→merchant credit for Vrida-funded offers) is platform's domain — platform already owns the Vrida↔tenant financial relationship. Offers records the redemption fact and funding_source; it feeds settlement, does not build a settlement engine. 'vrida' in the CHECK enum exists for forward-compat; v1 service layer rejects it. Deferred to v1.5. See DESIGN_RATIONALE Offers DR-O2.

offer_redemption append-only with reverse-rows. A refund/void creates a new 'reverse' row with signed-negative amount and reversed_redemption_id. Original row never mutated. Maintains budget_used_cents reconcilability. Same discipline as rewards.points_ledger. See DESIGN_RATIONALE Offers DR-O3.

budget_used_cents and redeemed_count as reconcilable caches. budget_used_cents reconciles to SUM(discount_amount_applied_cents) (signed, net of reversals). redeemed_count reconciles to COUNT(redeem rows) - COUNT(reverse rows) (net). Both are reversal-aware — a voided sale restores budget and code availability. See DESIGN_RATIONALE Offers DR-O4.

offer_assignment before-redemption lifecycle. Not redundant with offer_redemption. Assignment tracks issued→claimed→available state (pre-use). Redemption records the use event (post-use). Together they answer both "what can this consumer use?" and "what has this consumer used?" See DESIGN_RATIONALE Offers DR-O5.

Redemption seam on offers side — no POS touch. offer_redemption.sale_id → pos.sale is the seam. The FK lives on the offers side; pos.sale has no offer seam columns. Cleaner than rewards (which had points_earned/points_redeemed pre-wired at POS lock). See DESIGN_RATIONALE Offers DR-O6.

Consent is CRM's. OffersService reads crm.customer_consent (consent_type = 'offers') before targeting or issuing. Zero consent tables in offers. Third instance of the pattern (after notifications and rewards). See DESIGN_RATIONALE Offers DR-O7.

Distinct from pricing and rewards; stacking_policy governs. Pricing = predefined auto-applied rules. Rewards = points catalog. Offers = consumer-presents-and-claims coupon/promo. All three can stack at checkout; stacking_policy + exclusive_group governs combination. OffersService and RewardsService are parallel services — both called by POSService at checkout. See DESIGN_RATIONALE Offers DR-O8.

Plain coupon codes — not hashed. normalized_code is plain text (uppercased/trimmed). Coupons are typed or shared instruments — they are not bearer secrets. Contrast with identity.api_key.token_hash (a secret that is hashed). See DESIGN_RATIONALE Offers DR-O9.

THE SEAM: `offer_redemption.sale_id → pos.sale`

offers.offer_redemption.sale_id references pos.sale via enforced FK (pos locked 2026-06-10; FK enforced at offers schema creation). The FK lives entirely on the offers side — pos.sale requires no new columns and was untouched at offers lock. This is the reference-don't-copy pattern: the redemption references the sale; it does not copy sale data. In FORWARD_FK_REGISTRY this FK is DONE at offers lock.

Cross-Phase FK Seams

Column	Target	Status
`offers.*.tenant_id` (4 FKs)	`platform.tenant`	ENFORCED FK (platform locked 2026-06-09; enforced at offers schema creation) — DONE
`offer.consumer_id`	`consumer.consumer`	ENFORCED FK nullable (consumer locked 2026-06-11; enforced at offers schema creation) — DONE
`offer_code.consumer_id`	`consumer.consumer`	ENFORCED FK nullable — DONE
`offer_assignment.consumer_id`	`consumer.consumer`	ENFORCED FK NOT NULL — DONE
`offer_redemption.consumer_id`	`consumer.consumer`	ENFORCED FK NOT NULL — DONE
`offer_redemption.sale_id`	`pos.sale`	ENFORCED FK NOT NULL (pos locked 2026-06-10; enforced at offers schema creation) — THE SEAM — DONE
`offer.free_item_variant_ref`	`inventory.item_variant`	LOOSE text ref (variant may be archived; `OffersService` validates at redemption) — NEVER

Deferred

Item	Status
Vrida-funded offers + settlement	v1.5 — `funding_source = 'vrida'` guarded in v1; settlement is platform's domain when built (CLOSES open question from Consumer Layer Architecture 2026-06-09)
Cross-tenant Vrida offers (`tenant_id` NULL)	Future — v1 all offers tenant-scoped
`offer_eligibility_rule` table	Future — v1 uses `eligibility_json`; add when rules need query-heavy structure
A/B testing, suppression logs, audience snapshots, conversion events	Future — analytics/experimentation
Offer settlement ledger / funding invoice	NEVER in this module — offers feeds platform; platform owns Vrida→tenant credit
`consumer_app` module	Consumer Layer phase (post-v1.0) — last remaining consumer-layer module

Consumer Module (Locked 2026-06-11)

4 tables, 47 cols: consumer (16), consumer_tenant_link (10), consumer_address (13), consumer_interest (8).

First module of the Consumer Layer (Phase D). Third non-tenant-scoped schema in Vrida after platform and shared. Platform-level person identity — one account per real-world person usable across all Vrida nurseries. Closes the crm.customer.consumer_id forward-ref (OPEN → READY in FORWARD_FK_REGISTRY).

Architecture

Decision	Value
Schema	`consumer`
Tenancy	NON-TENANT-SCOPED — no `tenant_id` column on any table (except `consumer_tenant_link.tenant_id` which is an FK dimension, not the RLS scope)
RLS pattern	`WHERE id = current_setting('app.current_consumer_id')::UUID` (on `consumer`) or `WHERE consumer_id = current_setting('app.current_consumer_id')::UUID` (on related tables). NOT tenant-scoped.
Auth pattern	New `ConsumerGuard` / `ConsumerInterceptor` analogous to `TenantGuard` / `TenantInterceptor`. Sets `app.current_consumer_id` in each request context.
Access boundary	Tenants NEVER query `consumer.*` directly. Only via `ConsumerService` (SECURITY DEFINER / service-role boundary). A tenant sees only its own relationship with a consumer (its `crm.customer` row + `consumer_tenant_link` rows scoped to that tenant). Never sees cross-store activity.
Model	Model B — one account per real-world person. Same consumer identity usable at Nursery A and Nursery B.

Tables

consumer.consumer (16 cols): Platform-level person identity. id (UUID PK), created_at / updated_at / deleted_at (no tenant_id), auth_provider (text nullable, CHECK IN ('google','apple','email')), auth_provider_sub (text nullable, text seam to Supabase Social Auth), email (text nullable, canonical), email_normalized (text nullable), phone (text nullable, E.164), phone_normalized (text nullable), display_name (text nullable), status (text NOT NULL default 'unclaimed', CHECK IN ('unclaimed','active','suspended','closed')), claimed_at (timestamptz nullable), usda_zone (text nullable), notification_opt_in (JSONB nullable), locale (text nullable).

consumer.consumer_tenant_link (10 cols): Consumer's view of linked nurseries. id, created_at, updated_at, deleted_at, consumer_id (FK consumer — RLS scope), tenant_id (FK platform.tenant — DIMENSION, not RLS scope), link_source (CHECK IN ('app_follow','cashier_create','cashier_link','purchase')), crm_customer_id (loose UUID ref to crm.customer — NOT enforced FK), linked_at, consumer_opt_in (bool default false). Note: tenant_id is the only consumer.* column with a tenant reference; it is a FK dimension (which nursery) not the RLS scope.

consumer.consumer_address (13 cols): Consumer mailing/shipping addresses. id, created_at, updated_at, deleted_at, consumer_id (FK — RLS), address_type (CHECK IN ('home','shipping','billing','other') default 'home'), is_default (bool default false), line1, line2, city, state_code (loose text ref — international-safe), postal_code, country_code (FK shared.country — enforced).

consumer.consumer_interest (8 cols): Multi-valued plant interests for recommendation engine. id, created_at, updated_at, deleted_at, consumer_id (FK — RLS), interest_type (CHECK IN ('plant_category','plant_specific','care_topic')), interest_ref (loose text ref to shared.plant.slug — validated by ConsumerService, not FK-enforced), interest_label. Table (not JSONB) because recommendation queries search WHERE interest_type = ? AND interest_ref = ? server-side.

Key Constraints

consumer.consumer:

CHECK (auth_provider_sub IS NOT NULL OR status = 'unclaimed') — active/suspended/closed accounts necessarily went through social login; only unclaimed stubs may have NULL auth_provider_sub.
CHECK ((auth_provider IS NULL) = (auth_provider_sub IS NULL)) — provider and sub always either both NULL (unclaimed) or both NOT NULL (claimed).
PARTIAL UNIQUE (auth_provider, auth_provider_sub) WHERE NOT NULL AND NOT deleted — one account per social-auth identity.
PARTIAL UNIQUE (email_normalized) WHERE status = 'active' AND NOT NULL AND NOT deleted — email uniqueness scoped to active only; multiple unclaimed stubs with same email across nurseries is INTENDED (merge on claim).

Cross-Phase FK Seams

Column	Apparent target	Status
`consumer_tenant_link.tenant_id`	`platform.tenant`	ENFORCED FK (platform locked; enforced at consumer schema creation)
`consumer_address.country_code`	`shared.country`	ENFORCED FK (shared locked; enforced at consumer schema creation)
`consumer_tenant_link.crm_customer_id`	`crm.customer`	LOOSE ref (NOT enforced — cross-schema, different RLS domains; validated by `ConsumerService` at write time)
`consumer_address.state_code`	`shared.us_state`	LOOSE ref (international-safe; many consumers not US-based)
`consumer_interest.interest_ref`	`shared.plant.slug`	LOOSE ref (plant slugs may not always exist; validated by `ConsumerService` at write time)

Unclaimed Stub Lifecycle

Cashier creates stub during in-person sale → status = 'unclaimed', auth_provider_sub = NULL, consumer_tenant_link with link_source = 'cashier_create'.
Consumer downloads Vrida app and logs in via social auth → social-auth subject matched against email_normalized.
ConsumerService executes merge: all consumer_tenant_link rows pointing to unclaimed stubs with matching email are consolidated under the authenticated consumer identity. Status becomes 'active', claimed_at is set.
Multiple unclaimed stubs with the same email across different nurseries is normal and intended. All are merged on claim.

FK Closure (crm.customer.consumer_id)

crm.customer.consumer_id (nullable UUID forward-ref) moves from OPEN → READY in FORWARD_FK_REGISTRY. Target (consumer.consumer) is now locked. The FK constraint (ALTER TABLE crm.customer ADD CONSTRAINT fk_customer_consumer FOREIGN KEY (consumer_id) REFERENCES consumer.consumer(id)) can be applied in a dedicated FK-add migration.

Identity-Canonical Rule

When crm.customer.consumer_id is set, consumer.consumer is the canonical source of truth for name, email, and phone. Read via ConsumerService — never copy fields down to crm.customer (drift risk). For anonymous buyers (consumer_id IS NULL), crm.customer carries the only copy (no change).

Cross-Store Privacy Contract

Tenants NEVER read consumer.* directly. ConsumerService is the boundary — SECURITY DEFINER or service-role execution. A tenant sees only:

Its own crm.customer row (tenant-scoped)
Consumer profile fields proxied through ConsumerService (name, email, phone — not cross-store history)

Cross-store visibility belongs to the consumer alone (consumer app only). This is a legal and commercial requirement.

Deferred Items

Item	Status
`rewards.*` module	Locked 2026-06-12 — see "Rewards Module (Locked 2026-06-12)" above
`offers.*` module	Locked 2026-06-12 — see "Offers Module (Locked 2026-06-12)" above
`consumer_app.*` module	Consumer Layer phase (post-v1.0) — consumer-facing mobile surface; last remaining consumer-layer module
Consumer-side analytics (matviews for consumer behavior)	Consumer Layer phase
Platform-wide rewards aggregation (cross-nursery points)	Post-v1.0 extension — per-nursery first
AI narrative summaries	v1.0 hook to AIService (no schema change needed)

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

Vrida — Locked Project Decisions

Brand & Identity

Target Customers

Pricing (Locked)

Feature Philosophy (Locked)

Tech Stack (Locked Direction)

Architecture Principles (Locked)

Launch Path (Locked)

Documentation Discipline (Locked)

Product Direction — Generic Multi-Vertical ERP (Locked 2026-06-10)

Standing principle for all module design

Reclassifications under this principle

What this means for upcoming modules

Deferred Decisions

Per-feature tier classification

Invitation expiry policy

Multi-Nursery Customer Accounts (Locked v1.0)

Deferred Decision: Customer-facing App Access Controls

Multi-Site Architecture (Locked v1.0)

Global Tables (Tenant Scope Exceptions)

Customer LTV Scope Rule

Tenant Lifecycle and Data Retention

Stripe Architecture

White-Label Definition

Customer App Schema Ownership

AI Capacity and Cost (header reference)

Audit Immutability Implementation (header reference)

Deferred Modules: Delivery & Logistics (Module 14) and Services & Jobs (Module 15)

Module 14: Delivery & Logistics

Module 15: Services & Jobs

When to revisit

Open Gaps from Market Research

Architecture Redesign (Locked 2026-05-13)

1. Architecture Model: Generic-Core + Vertical-Extension

2. Item-First (Replaces Plant-First)

3. Customer Model: Tenant-Scoped Core

4. Vertical Strategy: Deferred

5. New Modules / Layers

6. Supporting Services (Not Full Modules)

7. Reaffirmed Boundaries (No Change — Restated for Clarity)

Downstream Docs Requiring Updates (Later Phases)

Platform Module (Locked 2026-06-09)

Key design decisions within platform

Identity Module (Locked 2026-06-09)

Key locked rules

Forward-reference FKs (operational note)

Inventory Item Model (Locked 2026-06-09)

Decision

Graduation rule (JSONB → real column)

Scope of the vertical-neutrality

Relationship to Nursery-Only Focus

What this changes in subsequent schema work

What this does NOT change

Shared Module (Locked 2026-06-09)

Tables

Plant Database — Option C (Locked)

Plant matching

USDA zone on plant — loose-coupled (not FK)

v1.5 deferrals (do NOT build now)

Multi-Location Module (Locked 2026-06-09)

v1.0 table

v1.5 deferrals (do NOT build in v1.0)

Cross-phase FK consolidation

Forward decisions (3) — apply when designing subsequent modules

Forward decision 1: site_id requirement rule

Forward decision 2: Site access control rule

Forward decision 3: Pricing / tax future-scope rule

Nursery-Only Focus (Locked 2026-06-09)

What SURVIVES from the Architecture Redesign unchanged

Billing Ownership Split (Locked 2026-06-09)

AI-Powered Self-Serve Onboarding (Locked 2026-06-09)

Database Infrastructure Decisions (Locked 2026-06-09)

1. Connection Strategy

2. Prisma Multi-Schema Setup

3. NestJS Tenant-Context Middleware

4. Site Creation as First Provisioning Step

5. Search Path Policy

6. Migration Strategy

Inventory Module (Locked 2026-06-09)

Schema overview

USDA zone on `plant` — loose-coupled (not FK)

Forward decision 1: `site_id` requirement rule

Pricing Module (Locked 2026-06-09; `price_list_assignment` revised 2026-06-09)

Billing (`billing` schema) owns: merchant-side control layer