# Module Schemas

Concrete tables for all 20 modules from `../architecture/04-domain-modules.md`, following the conventions in `01-database-design-principles.md` (schema-per-module, ULID primary keys, snapshot-not-live-reference across module boundaries, no tenant column). This is a reference for what to build, not exhaustive DDL — indexes, constraints, and full column lists are implementation detail.

Cross-schema references are stored as plain ID columns (no DB-level foreign key), per `01-database-design-principles.md`.

---

## Catalog `schema: catalog`

| Table | Key columns | Notes |
|---|---|---|
| `product` | name, slug, description, category_id, status, seo_title, seo_description | `status`: draft / active / archived |
| `product_variant` | product_id, sku, name, price_amount, price_currency, attributes (jsonb), position | Attributes hold variant options (size, color) |
| `category` | name, slug, parent_category_id, position | Self-referencing for nested categories |
| `collection` | name, slug, description | Merchandising groupings, not taxonomy |
| `collection_product` | collection_id, product_id, position | Join table |
| `product_image` | product_id, media_asset_id, alt_text, position | `media_asset_id` references `media.asset` |

## Inventory `schema: inventory`

| Table | Key columns | Notes |
|---|---|---|
| `location` | name, type (warehouse/store), is_default | Exists from day one, even for single-location merchants — resolves `../ux/04-open-questions.md` #1: the schema supports multi-location natively, while the Products UI simply hides the location dimension when only one `is_default` location exists. No migration needed if a merchant later adds a second location. |
| `stock_level` | product_variant_id, location_id, quantity_on_hand, quantity_reserved, reorder_point | Composite-unique on (product_variant_id, location_id) |
| `stock_movement` | product_variant_id, location_id, quantity_delta, reason (sale/restock/adjustment/return), reference_id | Append-only ledger; `stock_level` is a derived/maintained cache of movement history |

## Orders `schema: orders`

| Table | Key columns | Notes |
|---|---|---|
| `order` | customer_id, status, currency, subtotal_amount, discount_amount, tax_amount, shipping_amount, total_amount, placed_at | `status`: pending / paid / fulfilled / cancelled / refunded |
| `order_line_item` | order_id, product_id, variant_id, sku_snapshot, name_snapshot, unit_price_amount, quantity, line_total_amount | `*_snapshot` fields are frozen at purchase time — never joined live against `catalog.product` |
| `order_fulfillment` | order_id, status, carrier, tracking_number, shipped_at, delivered_at | |
| `order_return` | order_id, status, reason, refund_amount | |

## Customers `schema: customers`

| Table | Key columns | Notes |
|---|---|---|
| `customer` | email, first_name, last_name, phone, accepts_marketing | |
| `customer_address` | customer_id, label, line1, line2, city, region, postal_code, country, is_default | |
| `customer_segment` | name, definition (jsonb) | Rule-based (e.g. "ordered in last 90 days") |
| `customer_segment_member` | segment_id, customer_id | Materialized membership, recomputed by a background job, not live-evaluated per read |
| `wishlist_item` | customer_id, product_id, added_at | |

## Payments `schema: payments`

| Table | Key columns | Notes |
|---|---|---|
| `payment` | order_id, status (authorized/captured/failed/refunded), amount, currency, gateway, gateway_reference | No card data — `gateway_reference` points to the processor's own record |
| `payment_method` | customer_id, gateway, token_reference, brand, last4, expires_at | `token_reference` only — the actual card data lives with the gateway, never here. Hard constraint per `../architecture/06-open-questions.md` #4. |
| `refund` | payment_id, amount, status | |

## Shipping `schema: shipping`

| Table | Key columns | Notes |
|---|---|---|
| `shipping_zone` | name, regions (jsonb) | |
| `shipping_rate` | zone_id, name, price_amount, condition (jsonb) | Condition covers weight/price thresholds |
| `shipment` | order_id, carrier, tracking_number, status, shipped_at, delivered_at | |

## Tax `schema: tax`

| Table | Key columns | Notes |
|---|---|---|
| `tax_class` | name | e.g. standard, reduced, exempt |
| `tax_rate` | jurisdiction, tax_class_id, rate_percent | Self-hosted default rate tables |
| `tax_exemption` | customer_id, reason, certificate_reference | |

Per `../architecture/06-open-questions.md` #3 (resolved), the Tax module's **calling interface** (`04-module-interfaces.md`) must not assume these tables are the only source of truth — the interface signature is `calculate_tax(order_context) -> tax_result`, satisfied by these tables today and swappable for an external provider call later without changing the interface or its callers.

## Marketing `schema: marketing`

| Table | Key columns | Notes |
|---|---|---|
| `campaign` | name, type (manual/automated_recovery/automated_reengagement), status, audience_segment_id, scheduled_at, sent_at | `audience_segment_id` references `customers.customer_segment` |
| `campaign_message` | campaign_id, subject, body, channel | Campaign-specific content |
| `campaign_send` | campaign_id, customer_id, notification_id, status | `notification_id` references `notifications.notification` — Marketing decides *what* and *to whom*, Notification Service handles dispatch |

## Discount Engine `schema: discounts`

| Table | Key columns | Notes |
|---|---|---|
| `discount` | name, type (percentage/fixed/free_shipping/bundle_price), value, code, starts_at, ends_at, status | |
| `discount_eligibility` | discount_id, scope_type (product/category/segment), scope_id | |
| `discount_usage` | discount_id, order_id, customer_id, amount_applied | Append-only, used for reporting and per-customer usage limits |

## Automation Engine `schema: automation`

| Table | Key columns | Notes |
|---|---|---|
| `automation_rule` | rule_type, config (jsonb), status | e.g. abandoned_cart, low_stock, duplicate_detection |
| `detection_result` | rule_type, subject_type, subject_id, severity, detected_at, resolved_at | The "something is wrong/notable" side |
| `recommendation` | rec_type, subject_type, subject_id, payload (jsonb), status (pending/accepted/dismissed), rank_score | The "here's what to do" side; `rank_score` feeds the suggestion-density cap from `../ux/04-open-questions.md` #3 |

Full internal sub-structure (how detection, recommendation, and trigger execution are organized as distinct concerns within this one module) is specified in `05-automation-engine-substructure.md`.

## Notification Service `schema: notifications`

| Table | Key columns | Notes |
|---|---|---|
| `notification` | recipient_type (customer/staff), recipient_id, channel, subject, body, status (queued/sent/delivered/failed), sent_at | The dispatch record — content is passed in fully rendered, not templated here |
| `transactional_template` | name, channel, subject, body | Default templates for system-triggered messages (order confirmation, shipped, password reset) — distinct from Marketing's campaign content, since these aren't marketing |

## Media Manager `schema: media`

| Table | Key columns | Notes |
|---|---|---|
| `asset` | filename, mime_type, size_bytes, storage_path, alt_text, width, height | |
| `asset_variant` | asset_id, variant_type (thumbnail/medium/large/original), storage_path, width, height | Generated automatically on upload, per `../ux/02-core-user-flows.md` #2 |

## Content Manager `schema: content`

| Table | Key columns | Notes |
|---|---|---|
| `page` | title, slug, body, status, seo_title, seo_description | |
| `banner` | title, media_asset_id, link_url, placement, starts_at, ends_at, status | |

## Analytics `schema: analytics`

| Table | Key columns | Notes |
|---|---|---|
| `event_log` | event_type, subject_type, subject_id, payload (jsonb), occurred_at | Raw capture, append-only, partitioned by month at implementation time for volume management |
| `metric_daily` | metric_key, date, value | Pre-aggregated rollups computed by a background job, not queried live from `event_log` for dashboard reads |

## Reporting `schema: reporting`

| Table | Key columns | Notes |
|---|---|---|
| `report_definition` | name, metric_keys (jsonb), filters (jsonb) | |
| `report_schedule` | report_definition_id, frequency, recipient_staff_id, next_run_at | |
| `report_export` | report_definition_id, format, generated_at, storage_path | |

## Merchant Health `schema: merchant_health`

| Table | Key columns | Notes |
|---|---|---|
| `health_score` | score, computed_at | One row per computation run, latest is current |
| `health_signal` | signal_type, status (good/attention), subject_type, subject_id, detail (jsonb), computed_at | Feeds the Home screen's prioritized list per `../ux/02-core-user-flows.md` #5 |

## User Management `schema: users`

| Table | Key columns | Notes |
|---|---|---|
| `staff_user` | email, name, password_hash, status | |
| `role` | name | Merchant-legible names (see `06-permissions-model.md`) |
| `role_permission` | role_id, permission_key | |
| `staff_user_role` | staff_user_id, role_id | |
| `session` | staff_user_id, token_hash, expires_at | |
| `audit_log_entry` | staff_user_id, action, subject_type, subject_id, occurred_at | |

## Settings `schema: settings`

| Table | Key columns | Notes |
|---|---|---|
| `store_setting` | key (text, PK), value (jsonb) | Heterogeneous config as key-value, not one column per setting |
| `feature_flag` | key (text, PK), enabled (boolean) | |

## Search Engine `schema: search`

| Table | Key columns | Notes |
|---|---|---|
| `search_index_document` | entity_type, entity_id, search_vector (tsvector), search_text (trigram-indexed) | Maintained by a background job subscribed to Catalog/Content publish events, not written to directly by those modules |

## Commerce Core

No schema — pure orchestration over Settings and the checkout state machine, which is held in-memory/session-scoped rather than persisted as its own entity (a completed checkout becomes an `orders.order` row; an abandoned one is detected by Automation Engine from cart state, not from a Commerce Core table).