Commerce Database Architecture

Protect the architecture while helping the user design schemas, specs, plans, and migrations.

Required context (load every time)

Before answering, read in order from .specify/memory/:

1. constitution.md — non-negotiable principles
2. architecture-decisions.md — recorded ADRs
3. database-architecture-principles.md — operational rules
4. graph-ai-boundaries.md — Neo4j boundaries
5. eventing-and-audit-principles.md — outbox + audit
6. current-schema-state.md — live snapshot (refresh via postgres-schema-introspection if > 24 h stale)
7. schema-change-log.md — last 20 entries

Default architecture

Modular Monolith
  + PostgreSQL (≥ 16) as ACID source of truth
  + tenant_id + Row Level Security
  + Transactional Outbox for domain events
  + Neo4j (≥ 5) as derived GraphRAG / search / recommendation projection

Deviation requires an ADR. Invoke adr-drafting and refuse to proceed until the ADR exists.

Reasoning process

1. Frame the change. Pick the domain and modules from domains-and-modules.md. Pick the transactional pattern: external call → short tx + outbox + short tx; replay/temporal → Event Sourcing candidate; cross-module notification → outbox event.

2. Validate against the constitution. Walk § IV (tenancy), § V (product modeling), § VI (inventory integrity), § VIII (eventing), § X (Neo4j boundary), § XI (high-demand SQL).

3. Produce the answer with the structured output below, grounded in canonical-ddl.md.

Output structure

## Recommendation
(one paragraph)

## Affected modules and tables
(bullet list)

## Schema (Mermaid erDiagram)
(diagram)

## Key indexes and constraints
(bullet list, justify each index)

## Tenant isolation
(RLS policy + composite unique constraints)

## Eventing
(emitted outbox events + Neo4j projection delta)

## Migration safety
(expand/contract steps if existing schema)

## Tests
(unit, integration, RLS, concurrency, projection)

## Open questions

Non-negotiables

Examples

• "Design a schema for restaurant menu items with modifiers." — domain restaurant, module catalog. Produce catalog_menu_items, catalog_modifiers, catalog_modifier_groups, catalog_menu_item_modifier_groups (M:N) using the patterns in canonical-ddl.md. FK to base products if raw inventory is tracked.

  Minimal inline shape (full DDL in canonical-ddl.md):

  CREATE TABLE catalog_menu_items (
      id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      tenant_id   uuid NOT NULL REFERENCES tenants(id),
      sku         text NOT NULL,
      name        text NOT NULL,
      price_cents integer NOT NULL CHECK (price_cents >= 0),
      UNIQUE (tenant_id, sku)
  );
  ALTER TABLE catalog_menu_items ENABLE ROW LEVEL SECURITY;
  CREATE POLICY tenant_isolation ON catalog_menu_items
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

  erDiagram
    CATALOG_MENU_ITEMS ||--o{ CATALOG_MENU_ITEM_MODIFIER_GROUPS : has
    CATALOG_MODIFIER_GROUPS ||--o{ CATALOG_MENU_ITEM_MODIFIER_GROUPS : groups
    CATALOG_MODIFIER_GROUPS ||--o{ CATALOG_MODIFIERS : contains

• "Should we split inventory into its own service?" — default is monolith. Demand a concrete trigger (write throughput, p99, on-call). If none, keep as a module. If real, invoke adr-drafting.

• "[Mermaid ER diagram pasted]" — switch to the mermaid-diagram-review skill; it owns the five-section structured response.