Schema Conventions

Database conventions for all modules. Follow these rigorously for consistency.

1. Layers & Dependency Direction

All schemas belong to one of three layers. Dependency direction is enforced one-way.

Layer membership

Foundation / service layer: platform, identity, payments, integrations, ai, files, search, shared

Business layer: inventory, crm, pos, orders, purchasing, billing, pricing, multi_loc, admin, audit, notifications, reporting

Consumer layer: consumer, rewards, offers

Dependency direction (enforced)

Foreign keys: business and consumer MAY FK to foundation. Foundation MUST NOT FK to business or consumer.
Service-layer calls: business and consumer MAY call foundation services. Foundation MUST NOT call business or consumer services.
Imports / module includes: same rule — one-way only.

2. Placement Rule

For any new table, column, or service, ask: "Does this work for any retail business?"

Yes → foundation/shared or a generic business module.
Vertical-specific → express via item_type + JSONB attributes, a generic reusable table, or an add-on operational module beside the foundation.

NEVER put business-specific (nursery, pottery, etc.) columns or assumptions on foundation/shared schemas.

3. Naming

Table names: singular, snake_case → product, variant, zone, NOT products
Column names: snake_case → created_at, tenant_id, customer_id
Foreign keys: <referenced_table>_id → product_id, zone_id
Junction tables: <table1>_<table2> → product_tag
Materialized views: prefix with mv_ → reporting.mv_top_sellers
Enums: stored as text with check constraints, not Postgres enums (easier to alter)
Schema-qualified references in all cross-schema FKs (e.g., pricing.price_rule.item_variant_id REFERENCES inventory.item_variant(id))
Identifier length: Postgres truncates identifiers at 63 characters — keep table, index, and constraint names well under, or long composite names collide silently when truncated.
Index naming: idx_<table>_<col(s)>
Constraint naming: fk_<table>_<ref>, uq_<table>_<col(s)>, chk_<table>_<col>

4. Required Columns (every tenant-scoped table)

id — UUID, primary key (gen_random_uuid() default)
tenant_id — UUID, foreign key to platform.tenant, NOT NULL
created_at — timestamptz, default now()
updated_at — timestamptz, default now(); updated via trigger or app
deleted_at — timestamptz, nullable (soft delete pattern)

The `updated_at` trigger

updated_at is maintained by a database trigger, not application code — so it stays correct even when rows change via migration, admin SQL, or background jobs that bypass the app. One canonical function, applied to every table that has updated_at:

CREATE OR REPLACE FUNCTION public.set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON <schema>.<table>
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

Relying on the app means any non-app write path silently leaves a stale timestamp; the trigger makes it impossible to forget.

Multi-site columns (transactional tables only)

Transactional tables (those that represent operational/financial events) MUST include:

site_id — UUID, foreign key to multi_loc.site, NOT NULL

Master data tables (customer, vendor, plant catalog, etc.) MUST NOT include site_id. Master data is tenant-wide.

For orders and fulfillment, use both:

site_id — site where the record was created (e.g., where reservation made)
fulfillment_site_id — site where fulfillment will happen (may be same as site_id)

These columns are populated automatically:

For single-site tenants: from tenant.primary_site_id at insert time
For multi-site tenants: explicitly set by the application based on context

5. Required Columns (lookup/reference tables — non-tenant)

id — UUID or smallint primary key
created_at, updated_at — timestamps

6. Soft Delete

NEVER hard-delete rows in tenant-scoped tables
Set deleted_at = now() instead
All queries filter WHERE deleted_at IS NULL
Audit log captures the deletion

7. Status Fields & CHECK Constraints

Use status column with check constraint
Common values: 'active', 'inactive', 'pending', 'archived'
Module-specific statuses listed in module spec
Name every CHECK constraint: chk_<table>_<col>.
A CHECK enum MUST cover every value the application AND external systems produce — Stripe webhook states (e.g. incomplete, unpaid), Supabase Auth events, QuickBooks states. A missing value causes a runtime INSERT failure when that state arrives.
Document the allowed values in the module's schema doc so the CHECK and the docs never disagree.
Any partial-index WHERE clause referencing a status value must reference only values present in that column's CHECK constraint.

8. Currency

Store as ISO 4217 code + amount in smallest unit (cents)
Columns: amount_cents (bigint), currency_code (char(3))
Never use float/numeric for money

8.1 Money-unit column-suffix convention (canonical, added 2026-07-08, Remediation Phase 3 Item 13)

Three suffixes exist across the schema. They are internally consistent within each module, but were never written down in one place before this section — this is documentation only, no column was renamed to produce it.

_cents — the default, used everywhere except pos. ISO 4217 smallest unit (e.g. USD cents). bigint. This is the suffix to use for any NEW money column unless it lives on a pos-module offline-sync table (see next).
_minor_units — pos module (pos.sale.total_minor_units, pos.sale_line.charged_amount_minor_units, pos.sale_refund. refunded_amount_minor_units, etc.), PLUS orders.order_line. resolved_amount_minor_units / charged_amount_minor_units — the one cross-module exception, present there because order_line carries Pricing's Hard Contract 1 snapshot fields verbatim, matching pos.sale_ line's own column names exactly (PROJECT_DECISIONS #26/#29). Semantically IDENTICAL to _cents — same ISO 4217 smallest-unit value, same bigint type, same precision. Pure naming-style divergence carried over from pos's own original build; not a different unit, not a different precision. A single sale's reconciliation formula mixes both suffixes on purpose (e.g. pos.sale.total_minor_units = SUM(sale_line.charged_amount_minor_units) + SUM(sale_line.tax_amount_cents)) — this is expected, not a bug, and both sides of that sum are the same unit.
_millicents — ai module only (ai.agent_execution.cost_millicents, ai.ai_request.cost_millicents). A DELIBERATE, documented exception: 1 millicent = 1/1000 of a cent, because individual LLM calls can cost fractions of a cent (e.g. $0.0003) and _cents alone would round these to zero. Reconciles to _cents via total_cost_cents = SUM(cost_millicents) / 1000 (see ai module's own schema doc for the full formula).

When adding a money column: default to _cents. Only use _minor_units if the table is a pos-module table participating in pos's existing offline-sync column family. Only use _millicents (or another explicit sub-cent suffix) if the value can genuinely be a fraction of a cent — do not invent a 4th suffix for a plain whole-cent amount.

9. Timestamps

All stored UTC
Tenant timezone stored on platform.tenant.timezone
Display layer converts to tenant timezone

10. Row-Level Security (RLS)

ENABLE RLS on every tenant-scoped table
Policy: USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
Policy: WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid)
Application sets SET LOCAL app.current_tenant_id = ? per request

11. Indexes

Always index tenant_id (most queries filter by it)
Always index FKs
Composite indexes for common query patterns (document in module spec)

12. Foreign Keys Across Schemas

Allowed and encouraged for referential integrity
Use ON DELETE RESTRICT for critical relationships (e.g., item_variant → item)
Use ON DELETE SET NULL for optional relationships
Document cross-schema FKs in MODULE_INDEX.md

13. Tenant Scope & the Consumer-Owned Exception

Default: every business and foundation table is either:

Tenant-scoped — carries tenant_id, RLS-policed, or
Non-tenant reference/lookup — shared across all tenants, no RLS.

Exception — consumer's own identity tables only, NOT "the consumer layer" as a whole. rewards and offers are fully tenant-scoped, same as any business schema: every table in both carries tenant_id and is RLS-policed. A consumer_id column on a rewards/offers row is a dimension (which consumer this reward/offer row is about) — it is NOT the RLS scope, and must not be treated as one.

The genuine non-tenant exception is narrower: only consumer's own core identity tables (e.g. consumer.consumer, consumer.consumer_identifier) are non-tenant-scoped. A consumer identity spans multiple tenants (e.g., a shopper with accounts at multiple stores), which is why these specific tables:

Do not carry tenant_id
Are not RLS-policed on tenant_id
Rely on service-layer access control (via ConsumerService)

Exception to the exception: within consumer itself, consumer.consumer_merchant_link and consumer.event ARE tenant-scoped (carry tenant_id, RLS-policed) — a merchant-link or event row is inherently about one consumer's relationship with, or activity at, one specific tenant, not a cross-tenant identity fact.

Tenant-scoped code (business and foundation schemas) MUST NOT query consumer's non-tenant-scoped identity tables directly.

14. Cross-Schema Writes and Reads

Standard pattern — `SET LOCAL` + RLS

Applies to all writes (and reads) between tenant-scoped tables across any schemas in the foundation or business layers — including rewards and offers, which are fully tenant-scoped (see §13).

Application sets SET LOCAL app.current_tenant_id = ? before every cross-schema insert.
RLS stays on for all tenant-scoped tables.

SECURITY DEFINER write pattern — `consumer`'s non-tenant identity tables only

Applies only to writes touching consumer's own non-tenant-scoped, cross-tenant identity tables (see §13's exception — NOT rewards/offers, which are tenant-scoped and use the standard pattern above).

The function runs as a privileged role with RLS bypassed inside.
The function enforces isolation in code — validating app.current_consumer_id against the consumer's tenant linkage.
Foundation and business schemas MUST NOT use this pattern. If a foundation schema needs SECURITY DEFINER to write to consumer tables, the dependency direction has been violated.

SECURITY DEFINER read pattern — parameter-scoped, never ambient-GUC-scoped

A tenant-scoped caller (e.g. rewards/offers resolving a consumer's cross-tenant activity) sometimes needs a narrowly-scoped read across consumer's non-tenant identity tables. This build introduced the pattern:

The function takes the id it looks up as an explicit SQL parameter — e.g. consumer.get_cross_tenant_activity(p_consumer_id uuid) (packages/db/migrations/20260711000000_consumer_rewards_offers.sql) — and never reads an ambient session GUC (e.g. current_setting('app.current_consumer_id')) to decide what it selects.
The function runs SECURITY DEFINER (RLS bypassed inside, same as the write pattern above), but its blast radius is bounded to exactly the row(s) matching the parameter it was called with.
The leak scenario this closes: an ambient-GUC-reading SECURITY DEFINER function reads whatever app.current_consumer_id (or app.current_tenant_id) happens to be set to for the standing duration of the enclosing transaction — or, worse, connection — since the function has no independent check that the caller is actually asking about the entity the GUC names. If that GUC is set at the wrong scope (e.g. once per pooled-connection checkout rather than per statement, or left over from a prior request on a reused connection), every subsequent call inside that window silently returns another consumer's or tenant's cross-tenant data. A parameter-scoped function has no such window: the value it acts on is exactly the value the caller passed explicitly in the SQL call, never implicit in connection or transaction state.
Foundation and business schemas MUST NOT call this pattern directly either — the same one-way dependency rule from §1 applies; only rewards/offers (or ConsumerService itself) may call into it.

Rationale

Tenant-scoped tables (including rewards and offers) use RLS + SET LOCAL everywhere — consistent and sufficient. The consumer-owned cross-tenant exception is real but localized to consumer's own non-tenant identity tables, so both the write and read SECURITY DEFINER patterns never leak into foundation or business schemas, and a deployment without a consumer layer never encounters either.

15. Soft Delete and Uniqueness Constraints

All unique constraints on tenant-scoped tables are partial unique indexes:

CREATE UNIQUE INDEX ... ON table (col) WHERE deleted_at IS NULL;

This applies to SKU, email, slug, code, and any other unique-by-tenant column. Standard B-tree unique constraints (UNIQUE (col) without a WHERE clause) are not used on soft-deleted tables — they would block re-creating a value after soft-delete.

16. Migrations

Tooling:

Drizzle Kit (drizzle-kit) — generates SQL migration files from the TypeScript schema (drizzle-kit generate) and applies them (drizzle-kit migrate). The Drizzle schema (TypeScript, pgSchema/pgTable) is the single source of truth.
Drizzle ORM (drizzle-orm) — typed queries in the NestJS API.
Migrations are plain SQL files (readable, editable, runnable in any Postgres client). RLS policies, triggers, functions, and seed data are authored as SQL — either via Drizzle's native RLS (pgPolicy, .enableRLS()) in the schema, or as SQL in the generated/companion migration files.
Supabase is the database host. Drizzle owns app-table migrations — do NOT use Supabase CLI migrations for application tables.

Required extensions (installed once in public, per the search_path policy):

pg_trgm — trigram fuzzy search; added when the Search module lands.
UUID generation uses Postgres-native gen_random_uuid() — no extension required. (Do not install uuid-ossp.)

Rules:

One logical change per migration; one module's schema per migration (cross-schema FK additions that touch two modules are the only exception)
Never migrate a downstream schema before its upstream dependencies have migrated
Every migration includes a -- DOWN section. Drizzle (like most migration tools) does NOT auto-generate down migrations — author the reversing SQL (drop/alter) by hand in the -- DOWN block; RLS/trigger/seed rollback is likewise manual SQL.
Connections: direct 5432 for tenant-scoped app queries; pooled 6543 (PgBouncer/Supabase pooler) for service_role/migrations. Note: the Supabase pooler in transaction mode does not support prepared statements — set prepare: false on the postgres client.
search_path = public only — all references fully schema-qualified (e.g. inventory.product, not product). Schemas are declared in TypeScript via pgSchema('inventory').

Naming:

File type	Pattern
Drizzle migration	`NNNN_description.sql` (drizzle-kit default, sequential + snapshot in `meta/`)
Companion raw SQL (RLS/trigger/seed, if separate)	`YYYYMMDD_HHMM_<module>_<description>.sql`

17. Extensible Attribute Mechanism

Descriptive attributes that don't warrant a dedicated table SHOULD use an extensible attribute mechanism so adding a new attribute is data, not a migration:

Option A: typed attribute table (entity_id, attribute_key, attribute_value, value_type) with a definition registry (attribute_definition describing valid keys, types, constraints).
Option B: constrained JSONB column with a definition registry validating shape on insert/update.

Either approach is acceptable; pick one per use case based on query patterns (typed-table for filterable attributes, JSONB for sparse/heterogeneous attributes). This applies inside any module that owns the concept.

18. Relational Concepts Get Real Tables

Concepts that are relational (have their own lifecycle, FKs from many places, time-series, audit needs) get real tables, not extensible attributes:

Fulfillment batches, work orders, production cycles → real tables in the owning module
Product taxonomy (category, subcategory, brand) → real tables in inventory
Warranty records, service agreements → real tables

The extensible attribute mechanism is for descriptive, definitional data, not relational entities.

19. JSONB Columns

Document an example shape inline on every JSONB column — never a bare JSONB:
```
feature_flags JSONB  -- {"offline_pos": true, "ai_pack": false}
```
Use JSONB when the data is sparse, heterogeneous, tenant-defined, or read as a whole.
Use real columns when the field is filtered, sorted, or joined — JSONB is not a substitute for a column you will put in a WHERE clause.
For registry-defined attributes, use the extensible attribute mechanism (§17); JSONB there is Option B.

20. Append-Only Tables (added 2026-07-18, Phase 1 remediation, Item 3)

A table is append-only only when both of these are true, added in the SAME migration that creates the table:

REVOKE UPDATE, DELETE ON <schema>.<table> FROM authenticated;
A BEFORE UPDATE OR DELETE ... FOR EACH ROW EXECUTE FUNCTION platform.reject_append_only_mutation() trigger, named trg_<table>_append_only.

Neither layer alone is sufficient. The GRANT layer closes the ordinary write path; the trigger is the backstop against any connection that still holds elevated privileges (migrations, the postgres superuser, a future accidental re-grant) — this codebase has twice found tables where the trigger existed but a later, broader GRANT ... ON ALL TABLES IN SCHEMA silently re-granted UPDATE/DELETE, undoing an earlier REVOKE (approvals.approval_event, returns.return_resolution_line — see PROJECT_DECISIONS #67). Whenever a later migration issues a schema-wide GRANT ... ON ALL TABLES IN SCHEMA x TO authenticated, re-run the REVOKE for every append-only table in that schema in the SAME migration, after the blanket grant.

Retiring the "no updated_at column" heuristic. Omitting updated_at on a new table is a naming convention signaling intent, not enforcement — Phase 1's own structural sweep found 16 tables missing updated_at that had never received either enforcement layer, live for anywhere from days to weeks. Do not treat the column's absence as proof a table is protected.

Not every "mostly-immutable" table is append-only. Before adding the enforcement pair, check for a genuine correction path — some tables allow a validated UPDATE (e.g. a header-reconciliation trigger checking a running sum) rather than hard rejection; that is a distinct, valid design ("validated-mutable"), not append-only, and should NOT get reject_append_only_mutation() layered on top (it would silently turn the validated-UPDATE branch into dead code). Decide and document the classification explicitly — don't infer it from column shape alone. A status/resolved_at/used_at/completed_at-style column is decisive evidence of genuine mutability.

An undocumented JSONB column forces every reader to guess its structure — and they guess differently, so the shape drifts. The when-to-use rules keep JSONB from becoming a dumping ground for data that should be queryable columns.

21. Vertical-Neutral Core — How Vertical Data Enters (added 2026-07-18, Phase 2 nursery vertical extraction)

The core (platform, identity, shared, multi_loc, and every other foundation/business-layer schema) must stay vertical-neutral. Three independent external design reviews found the same finding at the same time: nursery-specific concepts (a botanical taxonomy, climate/hardiness zones, plant-flavored discriminator values) had leaked into schemas that must serve every future vertical, not just nurseries. Phase 2 relocated all of it; this section codifies the rule so it doesn't recur.

A generic core table must NEVER gain a vertical-only column or FK when the same relationship can be represented by a tenant-scoped vertical extension table. Vertical data enters ONLY via one of:

(a) <vertical>_ref global dictionaries — non-tenant-scoped, Vrida/AI-curated reference data for the vertical (e.g. nursery_ref.plant, nursery_ref.climate_zone). Same access shape as shared: no tenant_id, no RLS, GRANT-based SELECT-only for authenticated (§ below on shared/nursery_ref lockdown).

(b) <vertical>.<entity>_profile tenant extension tables keyed to the core entity via a composite (entity_id, tenant_id) FK — e.g. nursery.item_profile.item_id → inventory.item(id, tenant_id), nursery.site_profile.site_id → multi_loc.site(id, tenant_id). The FK direction is always vertical-extension-points-into-core; the core schema must never FK, import, or otherwise depend on a vertical schema (this is the existing §1 one-way dependency rule, restated for the vertical case specifically — a business-layer core table pointing into nursery would be exactly backwards).

(c) Tenant-defined variant options — free-form, tenant-authored data a tenant sets up for their own catalog (e.g. inventory.category, tenant-defined attribute sets) — already vertical-neutral by construction since the tenant defines the content, not the schema.

(d) Generic attribute-typed branches — a discriminator column with a small, genuinely vertical-neutral vocabulary, paired with a JSONB attribute bag for open extensibility (e.g. inventory.item.attributes, nursery.item_profile.care_attributes). A discriminator CHECK must never itself carry a vertical-specific value ('plant' on inventory.item.item_type was exactly this violation) — vertical classification belongs on the extension table's own discriminator (nursery.item_profile.profile_type), never on the core table's.

Enum vs. catalog table for a core discriminator. A closed CHECK enum on a core discriminator (e.g. inventory.item.item_type) is the right call when the enum's own values must never need vertical-specific additions — under this rule, they structurally can't, since verticals extend via mechanism (b)/(d) above, not by widening the core enum. A catalog table's usual benefit ("future verticals extend without a migration") doesn't apply here, because future verticals are never supposed to touch the core enum at all. Reach for a catalog table only when the values themselves are genuinely tenant- or config-driven (see §17's existing enum→catalog guidance), not as a hedge against vertical contamination — that hedge is this section's own rule, applied at design time, not a runtime extensibility mechanism.

Cross-schema FKs from a <vertical>_ref dictionary into shared are fine. Both are foundation-layer, non-tenant-scoped global data (e.g. nursery_ref.plant_common_name.locale_code → shared.locale.code) — this is foundation-to-foundation, not foundation-to-business/consumer, so §1's one-way rule is satisfied. Don't move a genuinely vertical-neutral dependency (like locale) into the vertical schema just because a vertical table happens to reference it.

Last modified: Jul 12, 2026, 2:28 PM PT