Design Rationale — Foundations & Cross-Cutting
Cross-cutting patterns and project-level architectural decisions.
Design Rationale
Captures the WHY behind non-obvious schema design choices. Read this before questioning a pattern that looks like a mistake — it may be a deliberate decision with a recorded reason.
Guard lines mark choices that look wrong but are intentional. They exist to prevent "fixing" something that is correct by design.
Sections: Cross-Cutting Patterns → Project-Level Architectural Decisions → per-module sections in schema lock order. New modules append at the bottom. Comprehensive as of 2026-06-12: cross-cutting patterns, project-level architecture, and all 23 locked schemas (platform, identity, multi_loc, shared, inventory, pricing, crm, pos, orders, purchasing, billing, admin, payments, audit, notifications, integrations, files, ai, search, reporting, consumer, rewards, offers). Future modules add their rationale at lock time.
Cross-Cutting Patterns
Decisions that recur across many modules — recorded once here rather than repeated per-module.
Forward-ref FK pattern
Decision: When a column references a not-yet-locked schema, add the column now (UUID or text) and document the intended FK target; add the real FK constraint when the target schema locks.
Why: Modules lock in dependency order over multiple sessions. Blocking a module on every unresolved downstream schema would force all schemas to design simultaneously. Seams are tracked explicitly in each module's Cross-Phase FK table and closed when the target locks.
Guard: An unenforced cross-schema reference is intentional, not an oversight — check the module's Cross-Phase FK table before "adding the missing FK." Text columns that look like they should be UUIDs are often offline-first value references (e.g. pos.sale.tax_exemption_cert_id) — these stay text by design even after the target entity exists.
Maintained-cache + immutable-ledger (dual-representation)
Decision: Running totals (stock balance, gift-card balance, A/R account balance, applied refund amounts) are maintained caches on a summary row; the truth is an immutable sequence of movement / transaction / application rows. Both exist together.
Why: Fast reads without summing history on every query, while keeping a full audit trail. The cache is always reconcilable from the ledger — if they diverge, the ledger wins.
Guard: Do not "simplify" by dropping the cache (acceptable for correctness, disastrous for performance) OR by dropping the ledger and trusting the cache alone (no audit trail, no reversal path). Both halves are required. Examples: inventory.stock + stock_movement; pos.gift_card.balance_cents + gift_card_transaction; billing.ar_account.current_balance_cents + ar_charge/ar_payment.
Idempotency on retry paths — and the NULL-in-unique trap
Decision: Any operation that can be retried (offline POS sync, webhook processing, event-driven charge creation, scheduled refund) carries a dedup key or global unique that prevents double-execution.
Why: Retries and at-least-once delivery are inherent to offline-first and webhook-driven systems. Without dedup, a retry creates duplicate sales, charges, or receivables.
Guard: NULL != NULL in Postgres — a multi-column UNIQUE (tenant_id, key) does NOT block two rows where key IS NULL. When the dedup column is nullable, use the two-partial-index pattern: one UNIQUE (..., key) WHERE key IS NULL and one UNIQUE (..., key) WHERE key IS NOT NULL. This is the system's #1 recurring schema trap (hit independently in tenant_setting, customer_tax_certificate, payment_intent.idempotency_key, payment_refund.idempotency_key).
Discriminator + shared table over table-per-variant sprawl
Decision: Variations of one concept share a table with a type discriminator (item_type, customer_type, order_type, sale.sale_type, price_rule.scope_type), not a table per variant.
Why: One query path, one set of constraints, less duplicated logic. Variants differ by a column value, not a schema object.
Rejected: Separate tables per variant — proliferates tables, duplicates FK/RLS/lifecycle, and creates "which table do I query?" confusion.
Merge-same-thing vs. separate-different-things
Decision: MERGE when it is the same thing with variation (one customer table + type discriminator). SEPARATE when things rhyme but wire differently (gift_card vs. store_credit; A/R vs. A/P; ar_payment vs. ap_payment).
Why: Merging reduces sprawl; separating keeps FKs, RLS, lifecycle, and consumer code clean. The test is: "do they share consumers, FKs, and lifecycle?" — not "do they sound similar?"
Examples: gift_card and store_credit are both value stores but gift_card is a bearer instrument (no customer, transferable) while store_credit is a customer liability (RLS-scoped, non-transferable) — separated. customer (individual + business) shares all FK targets and RLS rules — merged.
Generic-first / vertical-neutral schema
Decision: Every schema is vertical-neutral (valid for any retail business). The nursery vertical is expressed through configuration, item_type/JSONB attributes, and type codes — never hardcoded columns.
Why: Vrida is a multi-vertical ERP; nursery-specific column names on shared tables would structurally block other verticals.
Guard: Nursery concepts appear as generic mechanisms: plant attributes live in item.attributes JSONB (not a plant_* column), guarantee terms live in item_variant.guarantee_terms (generic text), tax exemption certs carry issuing_country_code (not assumed US). If a column name is nursery-specific, it belongs in a nursery add-on module, not the foundation schema.
Append-only vs. insert-once-status-updated — distinct patterns
Decision: Financial audit trails are append-only (no updated_at / deleted_at; corrections = new reversing rows). Webhook/event idempotency logs are insert-once, status-updated (inserted once, then status and outcome columns mutate).
Why: A ledger entry must never change after the fact — the history is the record. An idempotency log must record "seen" first and "processed/failed" second; those are two states on one logical event.
Guard: Do not conflate the two. An append-only table with a mutable status column is mislabeled. An idempotency log forced to be strictly immutable cannot record outcomes without a second insert that the global unique would block. Check the table's purpose before applying either label.
Additive touches to locked modules — when reopening is allowed
Decision: "Locked" means locked, except for additive, non-breaking columns or tables justified by a confirmed downstream need. Each touch is flagged in the touching module's design session and re-noted on the locked module's header with a date. Touches to date: inventory +stripe_tax_code (Payments/Stripe Tax), inventory +has_guarantee/guarantee_terms (POS), crm +customer_tax_certificate (Admin), pos.sale +tip_amount_cents (Payments).
Why: Strict immutability would force either premature over-design (add every possible column upfront) or module re-opens for every downstream seam. Controlled additive touches let later modules complete seams without breaking locked contracts.
Guard: A touch must be additive — a new nullable column or a new table. It must never change existing column semantics, rename anything, or alter an existing constraint. The touch must be re-noted at the locked module (dated) — never silent. A touch that would change existing behavior is not a touch; it is a redesign requiring a full unlock.
Read the spec before designing — specs may be stale
Decision: Before designing any module, read its spec doc AND cross-check against all locked decisions. Treat the spec as a starting point that may be outdated, not as the authoritative source.
Why: Multiple specs described retired scope: 11_billing.md was Vrida's SaaS subscription billing (superseded by the Platform/Billing boundary split); 10_admin.md had HR, scheduling, and 165 features (HR permanently cut; scope reduced to config-only); several specs predated the generic-first pivot and the Stripe Tax / HR-cut decisions.
Guard: When a spec and a locked PROJECT_DECISIONS.md entry conflict, the locked decision wins. Annotate the spec as superseded; do not silently follow a stale spec. The spec is a feature-list artifact; PROJECT_DECISIONS.md is the architectural record.
Size to spec; don't rebuild what a platform dependency already owns
Decision: Default lean. Never build what Stripe, Supabase, or a Vrida platform module already owns: tax calculation → Stripe Tax; authentication → Supabase Auth; SaaS billing → Platform; authorization → Identity; HR/payroll → external tools (Gusto, ADP, Homebase).
Why: Rebuilding owned capabilities adds maintenance burden (stale tax rates, duplicated auth logic), introduces reconciliation drift (two systems with the same fact), and produces no competitive differentiation.
Guard: Before adding any subsystem, ask "does a dependency already own this?" If yes, store only the thin reference Vrida needs to coordinate (e.g. stripe_tax_code on the item, not a rate table; supabase_auth_user_id on identity_user, not a password column). The reference is the seam, not the capability.
Judge the cumulative design, not each table in isolation
Decision: Scope decisions must consider what the module and its seams add up to collectively, not just whether each table looks reasonable in isolation.
Why: A module can pass a table-by-table review while collectively re-absorbing something owned elsewhere. Admin nearly re-absorbed HR (individually reasonable tables for scheduling/shifts) and auth policy. Billing nearly drifted into GL (individually reasonable journal-entry tables). The audit's cross-reference-locked-decisions check exists precisely for this: "does the set of tables collectively respect module boundaries?"
Project-Level Architectural Decisions
Decisions that shaped the overall product architecture — pivots, boundary choices, and infrastructure patterns that apply above any single module.
Nursery-only → generic multi-vertical ERP (the pivot, 2026-06-10)
Decision: Vrida is a generic multi-vertical retail ERP. Nursery is the first vertical and the GTM anchor, not a constraint on the data model. This refined (not reversed) the earlier "nursery-only" lock.
Why: The schema decisions that matter — generic item model, reusable masters, config-driven behavior — work for any retail vertical. Locking to nursery-only would have blocked expansion (a greenhouse supply store, a garden center chain, a hardware shop) for zero design benefit. Nursery remains the proving ground; the schema carries no nursery-only assumptions.
Rejected: Nursery-only schema (artificially narrow — blocked expansion for no benefit). Also rejected: the earlier generic-core + per-vertical-extension multi-schema architecture (e.g. core.item + nursery.plant_detail) — multi-schema join complexity for no v1.0 benefit; vertical variation goes via item_type + JSONB + config, not extension tables.
Guard: The default scoping question for every new column or table is "does this work for ANY retail business?" not "does this work for a nursery?" Nursery concepts appear only as generic mechanisms (guarantee = product warranty; plant = item with botanical attributes). Never add nursery-named columns to foundation or shared schemas.
Consumer layer = platform-wide non-tenant schema (Model B), built after merchant core
Decision: The future consumer-facing layer is a platform-wide Vrida consumer account — one account per shopper, vertical-neutral, visible across all tenant stores — in its own non-tenant-scoped consumer schema with separate social-login auth (Google/Apple). crm.customer links to it via a nullable consumer_id. Four modules: consumer, rewards, offers, consumer_app. Built after the merchant core (structurally depends on POS for loyalty earn).
Why: A shopper is one person who shops at many tenant stores. Modeling them inside a tenant-scoped crm.customer (RLS-isolated per tenant) can't represent a platform-wide identity — the same shopper would be duplicated per store with no shared account. Model B keeps the consumer outside tenant isolation, structurally analogous to platform and shared.
Rejected: Model A — extend tenant-scoped crm.customer to carry platform-level identity. A tenant-scoped record is RLS-isolated; it cannot be the authoritative cross-tenant identity. A shopper would be duplicated per tenant with no shared wallet, points balance, or purchase history.
Guard: Consumer identity is non-tenant-scoped by design — like platform and shared, it deliberately lacks a tenant_id. crm.customer.consumer_id is the nullable bridge (tenant side pointing to platform side), not the other way around. v1.0 consumer scope = loyalty rewards + informational app; in-app purchasing and cross-store commerce deferred.
One backend, schema-per-module, RLS tenant isolation
Decision: One NestJS backend, one Postgres database, schema-per-module, tenant isolation via tenant_id + RLS (TenantGuard + TenantInterceptor, SET LOCAL app.current_tenant_id). Non-tenant schemas (platform, shared, future consumer) deliberately lack tenant_id.
Why: Schema-per-module gives clean ownership boundaries, meaningful FK discipline, and schema-level access control — without the operational overhead of database-per-tenant or service-per-module microservices. RLS enforces isolation at the data layer so application bugs can't leak cross-tenant data.
Guard: Cross-schema FKs are allowed and are tracked via each module's Cross-Phase FK table (forward-ref pattern). Non-tenant schemas lacking tenant_id is intentional — not an RLS oversight.
Deferred vs. cut — two different scope exits with different design implications
Decision: Distinguish deferred (planned for a later phase; schema leaves room) from cut (out of the product entirely; no hooks needed). Deferred: multi-location stock transfers (v1.5), site-level pricing, formal customer invoices/credit memos, production module (nursery vertical add-on v1.2), delivery module, services module, in-app commerce, Consumer Layer. Cut entirely: HR/labor/scheduling/payroll, internal comms (bulletin/KB/calendar).
Why: A future reader or session needs to know whether a missing capability is "not yet — leave room" or "never — stop proposing it." The schema reflects this: deferred items often have forward-ref columns or nullable slots (e.g. site_id nullable on pricing tables, consumer_id nullable on crm.customer); cut items have no hooks whatsoever.
Guard: Do not build cut items even if they seem useful in a specific context (HR is the standing example — always declined). Do not assume deferred items are unwanted — the schema often already anticipates them. When in doubt, check PROJECT_DECISIONS.md and OUT_OF_SCOPE.md before adding a new table.
Stripe usage is split by direction across two owners — Platform and Payments
Decision: Stripe is used two ways by two owners. Platform uses Stripe to charge tenants (Vrida is the merchant; subscription revenue). Payments uses Stripe Connect so each tenant receives money from their shoppers (the tenant is the merchant; Vrida takes an application fee as platform).
Why: These are opposite money-flow directions using different Stripe products — direct charges for platform subscription billing vs. Connect for tenant merchant processing. Conflating them is the same trap that made 11_billing.md stale.
Guard: Platform = Vrida's revenue (tenant pays Vrida). Payments = tenant's revenue (shopper pays tenant). They never share tables, services, or logic. If a concern involves Vrida collecting from a tenant, it is Platform. If it involves a tenant collecting from a shopper, it is Payments.