Schema Conventions

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

Core vs Extension — Dependency & Naming

⚠️ Superseded — see PROJECT_DECISIONS "Nursery-Only Focus (2026-05-13)". There is no core/extension split. Masters live in owning modules (item in Inventory, customer in CRM, vendor in Purchasing). The dependency direction and naming conventions below still apply between modules — modules access each other through services, not direct table queries — but the core-vs-extension framing is retired. The "plant test" placement rule is retired. The "extensible attribute mechanism" (typed table or constrained JSONB with definition registry) remains a valid convention within any module that needs it.

Per the Architecture Redesign (PROJECT_DECISIONS, ARCHITECTURE.md), all schemas belong to one of two layers. The dependency direction between them is enforced.

Layer membership

Core schemas (the Core & Platform Layer + shared services):

• core — item, party (customer / vendor / contact), location, unit-of-measure, base pricing
• platform — tenant, feature-flags, connection registry
• identity — users, roles, permissions, sessions, 2FA, SSO
• payments — PaymentsService over Stripe
• integrations — unified external connectors
• ai — AIService over Bedrock
• files — Cloudflare R2 ownership (shared service)
• search — search abstraction (shared service)
• shared — reference data (countries, currencies, etc.)

Nursery vertical-extension schemas (nursery-specific only):

• The nursery plant taxonomy / plant_profile schema (home TBD)
• production — propagation, crop lots, mother plants
• Plant care attributes (home TBD; co-located with plant taxonomy)
• customer_app — consumer plant-care app data
• The multi-nursery consumer linkage (home in extension layer; specifics in revised "Global Tables" subsection below)

The remaining v1.0 schemas (inventory, pos, orders, purchasing, crm, reporting, multi_loc, admin, billing, audit, notifications, delivery, services) are nursery-facing modules that consume the Core & Platform Layer. They are not part of the vertical-extension layer themselves — they are tenant-facing modules being refactored onto core. Some columns/concepts within them will move (e.g., plant-specific fields in inventory move to plant_profile; cross-nursery customer linkage in crm moves to extension).

Dependency direction (enforced)

• Foreign keys: extension MAY have FKs to core. Core MUST NOT FK to extension.
• Service-layer calls: extension MAY call core services. Core MUST NOT call extension services.
• Imports / module includes: same rule — one-way only.

Placement rule — "the plant test"

For any new table, column, or service, ask: "Would this make sense for a business that has never heard of a plant?"

• Yes → core schema (core, platform, identity, payments, integrations, ai, files, search, shared)
• No → vertical-extension schema (nursery plant taxonomy, production, customer_app, etc.)

If a column is ambiguous, the test failing on either side is dispositive: if it would NOT make sense without plants, it goes to extension.

Naming (unchanged)

• Snake_case singular table names, regardless of layer
• Schema-qualified references in all cross-schema FKs (e.g., plant_profile.item_id REFERENCES core.item(id))
• Core tables live in core schemas; nursery tables live in the nursery-extension schema

Naming

• Table names: singular, snake_case → plant, variant, zone, NOT plants
• Column names: snake_case → created_at, tenant_id, plant_master_id
• Foreign keys: <referenced_table>_id → plant_id, zone_id
• Junction tables: <table1>_<table2> → plant_tag
• Materialized views: prefix with mv_ → reporting.mv_top_sellers
• Enums: stored as text with check constraints, not Postgres enums (easier to alter)

Required columns (every tenant-scoped table)

• id — UUID, primary key (uuid_generate_v4() 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)

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

Required columns (lookup/reference tables — non-tenant)

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

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

Status Fields

• Use status column with check constraint
• Common values: 'active', 'inactive', 'pending', 'archived'
• Module-specific statuses listed in module spec

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

Timestamps

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

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

Indexes

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

Foreign Keys Across Schemas

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

Migrations

• One migration per logical change, not bundled
• Migration scope: one module at a time
• File naming: YYYYMMDD_HHMM_<module>_<description>.sql
• Reversible: include -- DOWN section

Global Tables (Tenant Scope Exceptions)

Cross-reference: PROJECT_DECISIONS.md → "Global Tables (Tenant Scope Exceptions)" (annotated as superseded) and "Architecture Redesign (Locked 2026-05-13)".

⚠️ Superseded (Nursery-Only Focus 2026-05-13): Customer is tenant-scoped, lives in crm schema (no core.customer). It carries tenant_id, is RLS-policed, and uses the standard SET LOCAL cross-schema write pattern. The multi-nursery consumer linkage tables — plant library, garden plans, chat sessions — live in the customer_app module schema directly (no separate "extension layer"). They remain consumer-owned and cross-tenant; that property is unchanged. See PROJECT_DECISIONS "Nursery-Only Focus (2026-05-13)" — this is the current authority. The prior "Architecture Redesign" annotation that pointed at a separate extension layer is itself partially superseded.

Prior global tables — new homes

Prior global table	New home	New scope
crm.customer	core.customer	Tenant-scoped (carries tenant_id, RLS-policed)
crm.nursery_customer (join table)	Nursery vertical-extension layer (specific schema TBD)	Cross-tenant join — remains the "global, consumer-owned" exception, scoped to the extension layer
customer_app.plant_library	Vertical-extension layer (customer_app or nursery taxonomy schema)	Cross-tenant, consumer-owned
customer_app.garden_plan	Vertical-extension layer	Cross-tenant, consumer-owned
customer_app.chat_session	Vertical-extension layer	Cross-tenant, consumer-owned

The "global, consumer-owned" exception (retained, but only in the extension layer)

The vertical-extension layer may contain cross-tenant tables that represent a single consumer's identity spanning multiple nurseries (the multi-nursery linkage, plant library, garden plans, AI chat history). These tables:

• Do not carry tenant_id
• Are not RLS-policed at the row level
• Rely on API-layer access control (typically a join against the multi-nursery consumer linkage table)

This exception is scoped to the extension layer only. The core layer has no global tables — every core table is either tenant-scoped or a lookup/reference table.

What changed and why

• Before: crm.customer was global so that one consumer could shop at multiple nurseries.
• After: core.customer is tenant-scoped. Each nursery (tenant) owns its own customer rows. A separate nursery-extension table links cross-tenant consumer identities to the per-tenant customer rows.
• Why: keeps core generic (a customer is just a tenant's customer), localizes nursery-specific cross-tenant linkage to the extension layer, and lets non-nursery deployments omit the extension entirely without inheriting an unwanted cross-tenant pattern.

Cross-Schema Writes

Decision (Locked v1.0, revised 2026-05-13 per Architecture Redesign): Hybrid pattern, scoped by layer.

Standard pattern — SET LOCAL + RLS

Applies to all writes within the core layer and between core layer schemas, and to cross-schema writes between tenant-scoped tables in nursery-facing modules.

• Application sets SET LOCAL app.current_tenant_id = ? before every cross-schema insert.
• RLS stays on for all tenant-scoped tables.
• This now includes writes touching core.customer (which is tenant-scoped post-redesign — see "Global Tables (Tenant Scope Exceptions)" above).

SECURITY DEFINER pattern — scoped to the extension layer only

Applies only to cross-schema FKs that touch the remaining cross-tenant / consumer-owned tables that live in the nursery vertical-extension layer (the multi-nursery consumer linkage, plant library, garden plans, chat sessions).

• The function runs as a privileged role with RLS bypassed inside.
• The function enforces tenant isolation in code — validating app.current_tenant_id against the consumer's tenant linkage via the multi-nursery join table.
• Core schemas MUST NOT use this pattern; if a core schema needs SECURITY DEFINER to talk to extension tables, the dependency direction has been violated (core depending on extension).

Rationale

• Core tables are tenant-scoped by default, so RLS + SET LOCAL is sufficient and consistent everywhere.
• The "consumer-owned across tenants" exception is real for the nursery plant-care app, but it's a vertical concern. Localizing SECURITY DEFINER to the extension layer means the pattern doesn't leak into core, and a non-nursery deployment never has to reason about it.

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.

Snapshot Storage

Decision (Locked v1.0): Daily deltas in Postgres + weekly full snapshots in R2.

• Daily deltas: stored in audit.snapshot_delta, partitioned by tenant_id + date. Retained for 90 days then aged out.
• Weekly full snapshots: exported to Cloudflare R2 as Parquet, partitioned by tenant_id + week. 7-year retention.
• Long-term record: R2 weekly fulls are the system of record beyond 90 days; Postgres deltas are working/short-term.
• Restore: latest R2 weekly full + forward-replay of Postgres deltas to the target date.

See ARCHITECTURE.md → "Snapshot Architecture" for the restore procedure outline.

Item-First Inventory

⚠️ Superseded — see PROJECT_DECISIONS "Nursery-Only Focus (2026-05-13)". Item model is nursery-first: item can carry plant-specific fields directly. No plant_profile indirection. No separate core.item. The extensible attribute mechanism for descriptive attributes (care attributes, reason codes, grades) remains a valid convention within any module that needs it — it is not retired, only the core/extension placement rule is.

Convention (added 2026-05-13 per Architecture Redesign):

The central inventory entity is core.item (Core & Platform Layer), not the nursery plant master. Plant becomes a profile in the vertical-extension layer that references core.item.

Rules

1. core.item is the single inventory entity for every business — generic, no plant-specific columns.
2. Variants and SKUs are item-level constructs on core.item. A nursery "1-gal lavender" and "3-gal lavender" are two item variants, not two children of a plant master. The same mechanism handles size/color/spec variants for any other vertical.
3. plant_profile (vertical-extension) references core.item with a nullable one-to-one relationship: items that are plants have a profile; items that are hard goods (pots, soil, tools) do not. The presence/absence of a plant_profile row defines "is-a-plant" — there is no is_plant boolean on core.item.
4. Plant care attributes attach via the extension, not as columns on core.item. USDA zone, sun exposure, water needs, mature size, bloom time, deer-resistant — all live in or off of plant_profile, never on the generic item.
5. No reverse references: core.item MUST NOT carry any column that points to plant_profile or knows about plant taxonomy. The dependency arrow runs extension → core, never core → extension.

Extensible attribute mechanism

Descriptive nursery attributes that don't warrant a dedicated table — plant care attributes, reason codes (dead, damaged, theft, etc.), quality grades (A/B/C/Cull), customer types (retail, contractor, commercial, HOA), etc. — SHOULD use an extensible attribute mechanism so adding a new one 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). Both apply inside the vertical-extension layer — core does not host extensible attributes for vertical concepts.

Relational concepts get real tables

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

• Crop lots, mother plants, propagation cycles → real tables in production
• Plant taxonomy (genus, species, cultivar, family) → real tables in the plant taxonomy schema
• Plant guarantee / warranty records → real tables

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

Migration note

This convention supersedes the prior Module 1 "plant-master + size variants" model. Table definitions are explicitly out of scope for this phase — the Module 1 spec will be refactored in a later phase to reflect: core.item as the central entity, plant_profile as the nursery extension, variants as item-level constructs, plant care attributes via the extensible attribute mechanism.