ARCHITECTURE V1.3 IMPLEMENTATION FREEZE - COMPLETE DUMP READY_TO_IMPLEMENT_MIGRATIONS = YES Generated: 2026-07-27 ======== FILE: ARCHITECTURE_V1_3_FREEZE.md ======== # ARCHITECTURE v1.3 — IMPLEMENTATION FREEZE **Status:** FINAL PRE-IMPLEMENTATION FREEZE **Date:** Architecture v1.3 **Framework:** Laravel 13 | MySQL 8 | Redis | Typesense (projection only) --- ## 1. Files Reviewed All files under `docs/architecture/` including README, ARCHITECTURE_REVIEW, matrices (PRD, MODULE_OWNERSHIP, TABLE_REGISTRY, CONSISTENCY, SEARCH, CRITICAL_SCENARIO_TEST), 01–27 domain docs, API_CONVENTIONS, EVENT_CATALOG, ERD, INTEGRATION_CONTRACTS, all ADRs 001–011, and `docs/first-prompt.txt`, `docs/architecture-review.txt`. ## 2. Problems Discovered in v1.2 | # | Problem | Severity | |---|---|---| | P1 | Payment webhook implied synchronous Order CONFIRMED in same flow; Payments→Orders coupling ambiguous | High | | P2 | Reservation expiry could expire ACTIVE reservations for **paid** orders awaiting allocation | Critical | | P3 | Late payment webhook after legitimate expiry had no defined reconciliation path | Critical | | P4 | `CLOSED` order state undefined / optional | Medium | | P5 | `fulfillments.vendor_id` FK to non-migrated `vendors` table | High | | P6 | ALLOCATED cancellation / compensating ledger movements underspecified | Medium | | P7 | Checkout idempotency crash boundaries not fully specified | Medium | | P8 | No authoritative column-level schema spec for migrations | High | | P9 | No index registry before migration generation | Medium | | P10 | Consumer idempotency for payment.captured → confirm → fulfill chain not explicit | Medium | ## 3. Changes Made in v1.3 - Canonical async payment→order→fulfillment event chain (Payments never mutates Orders) - Reservation `checkout_expires_at` + expiry guards tied to `orders.status = PENDING_PAYMENT` - Post-payment hold via Inventory action triggered by ConfirmOrder consumer (not Payments) - Late-capture reconciliation workflow (`reReserveForPaidOrder` or refund) - Order terminal states: **COMPLETED** and **CANCELLED** only (remove CLOSED as domain state) - `order_items.qty_fulfilled`, `qty_cancelled` for quantity resolution - Phase-1 `fulfillments`: `inventory_location_id` only; **no `vendor_id`** until vendor migration - ALLOCATED atomic commit + cancellation compensating ledger table in docs - `PHASE1_SCHEMA_SPEC.md` (authoritative migration input) - `DATABASE_INDEX_REGISTRY.md` - `MIGRATION_IMPLEMENTATION_PLAN.md` - ADR-012, ADR-013 - Expanded test matrix (scenarios 31–45) - Updated state machines, payments, inventory, events, consistency matrix, table registry ## 4. Final Module Ownership Unchanged from v1.2 MODULE_OWNERSHIP_MATRIX. Checkout = orchestrator only. Store owns locations. Inventory owns stock. Payments owns payments. Orders owns orders. Fulfillment owns fulfillments/shipments. Shipping owns methods/config. ## 5. Final REQUIRED NOW Table Count **48** (unchanged). Schema columns refined; `vendor_id` removed from Phase-1 `fulfillments`. ## 6. Foundation Table Count **36** (unchanged) ## 7. Deferred Count **10** (unchanged) ## 8. New/Removed Tables and Justification | Change | Justification | |---|---| | No new tables | Consumer idempotency via domain guards + event_id dedupe | | `fulfillments.vendor_id` removed from P1 spec | Avoid FK to non-existent `vendors` table | | `inventory_reservations.checkout_expires_at` added | Distinguish unpaid checkout TTL from post-payment hold | | `order_items.qty_fulfilled`, `qty_cancelled` added | Deterministic order terminal semantics | ## 9. Final Transaction Boundaries | Operation | In DB TX | Outside DB TX | |---|---|---| | Checkout place order | reserve + order + idempotency complete + outbox | — | | Payment webhook capture | webhook dedup + payment CAPTURED + outbox `payment.captured` | HTTP verify only before TX | | Confirm order | order CONFIRMED + inventory clear checkout expiry + outbox `order.confirmed` | — | | Create fulfillment | fulfillment PENDING + outbox | — | | Allocate fulfillment | reservation COMMITTED + SALE + fulfillment ALLOCATED | — | | Payment provider API | attempt record create (short TX) | provider HTTP | ## 10. Final Inventory Commit Semantics SALE at **Fulfillment ALLOCATED** only. Atomic: reservation COMMITTED, reserved−, on_hand−, SALE ledger, fulfillment ALLOCATED. ## 11. Final Payment/Order Confirmation Semantics ``` Payment webhook TX → payment CAPTURED + outbox(payment.captured) Consumer ConfirmOrder (idempotent) → order CONFIRMED + Inventory.clearCheckoutExpiry + outbox(order.confirmed) Consumer CreateFulfillment (idempotent) → fulfillment PENDING + outbox(fulfillment.created) ``` Payments **never** UPDATE `orders`. ## 12. Final Reservation Expiry Race Strategy Expiry worker releases only when **ALL** true: - `reservation.status = ACTIVE` - `reservation.checkout_expires_at IS NOT NULL` - `reservation.checkout_expires_at <= NOW()` - `orders.status = PENDING_PAYMENT` Lock order: `inventory_reservations` → `inventory_levels` → `orders` (ascending id). Late capture after expiry: ConfirmOrder attempts `reReserveForPaidOrder`; on failure → refund workflow, order CANCELLED with reason, never silent confirm without stock. ## 13. Final Idempotency Strategy Checkout: claim PROCESSING in pre-TX or first step; atomic outer TX completes reserve+order+outbox; mark COMPLETED with `resource_type=order`, `resource_public_id`; replay returns stored response. Stuck PROCESSING > 5m → recovery job. ## 14. Final Order Terminal States | qty_ordered | qty_fulfilled | qty_cancelled | qty_open | Domain status | |---|---|---|---|---| | 10 | 10 | 0 | 0 | COMPLETED | | 10 | 0 | 10 | 0 | CANCELLED | | 10 | 7 | 3 | 0 | COMPLETED | | 10 | 7 | 0 | 3 | PROCESSING or PARTIALLY_FULFILLED | Terminal when `qty_open = 0`. COMPLETED if `qty_fulfilled > 0`. CANCELLED if `qty_fulfilled = 0` and all cancelled. Customer display status is a separate presentation layer. ## 15. Final Vendor Fulfillment Migration Strategy Phase 1: `fulfillments.inventory_location_id NOT NULL` (store fulfillment). Future additive migration: `ALTER TABLE fulfillments ADD vendor_id ...` when `vendors` schema ships; XOR constraint added then. ## 16. Final Index Strategy See `DATABASE_INDEX_REGISTRY.md`. Composite indexes for outbox polling, reservation expiry, order listing, webhook dedup. ## 17. Remaining CLIENT/BUSINESS Decisions B1–B7, C1–C10 from `21-open-decisions.md` (pre-arrival, pickup, guest checkout, compliance content, promotions, collections, USD, TTL defaults, etc.). Feature-gated; do not invent. ## 18. Remaining TECHNICAL Blockers **None** for core Phase-1 migration generation. Feature modules (vendors, promotions, compliance tables, pre-arrival) blocked only when implementing those features. ## 19. Implementation Readiness Verdict ``` READY_TO_IMPLEMENT_MIGRATIONS = YES ``` Migrations may be generated directly from `PHASE1_SCHEMA_SPEC.md` + `DATABASE_INDEX_REGISTRY.md` + `MIGRATION_IMPLEMENTATION_PLAN.md` without inventing schema decisions during implementation. **Prerequisite:** Client feature gates acknowledged for pickup (B2), pre-arrival (B1), promotions (B6) — these do not block the 48-table core migration set. --- ## Canonical Event Chain (v1.3) ``` POST /orders (checkout TX) → order.placed (async: audit, search hint, notifications) POST /payments → provider (outside TX) → webhook TX: payment.captured → consumer ConfirmOrder: order.confirmed → consumer CreateFulfillment: fulfillment.created Allocate fulfillment TX → inventory.sale + fulfillment.allocated Ship/pickup flows → shipment.* / fulfillment.picked_up → order qty updates → order.completed when qty_open=0 ``` ======== FILE: PHASE1_SCHEMA_SPEC.md ======== # PHASE1_SCHEMA_SPEC.md — Architecture v1.3 (Authoritative Migration Input) **48 REQUIRED NOW tables.** Money: `BIGINT` minor units + `CHAR(3) currency_code`. PK: `BIGINT UNSIGNED`. Public API: `CHAR(26) ULID` where noted. States: `VARCHAR(50)` + PHP backed enums. Timestamps: `created_at`, `updated_at` on mutable tables unless append-only. **FK policy summary:** RESTRICT on financial/history/ledger; CASCADE only on pure pivots (cart_items, role_permissions); SET NULL on optional refs (customer_id, brand_id). --- ## 1. Identity ### users (extends Laravel starter — additive migration) | Column | Type | Null | Default | Notes | |---|---|---|---|---| | id | BIGINT UNSIGNED | NO | AI | PK | | public_id | CHAR(26) | NO | — | ULID, UQ | | name | VARCHAR(255) | NO | — | existing | | email | VARCHAR(255) | NO | — | UQ | | email_verified_at | TIMESTAMP | YES | NULL | existing | | password | VARCHAR(255) | NO | — | existing | | remember_token | VARCHAR(100) | YES | NULL | existing | | is_active | TINYINT(1) | NO | 1 | | | last_login_at | TIMESTAMP | YES | NULL | | | created_at, updated_at | TIMESTAMP | YES | NULL | | FK: none. Soft delete: NO. ### customers | Column | Type | Null | Default | |---|---|---|---| | id | BIGINT UNSIGNED | NO | AI PK | | public_id | CHAR(26) | NO | UQ | | email | VARCHAR(255) | NO | UQ | | password | VARCHAR(255) | YES | NULL | | first_name | VARCHAR(100) | YES | NULL | | last_name | VARCHAR(100) | YES | NULL | | phone | VARCHAR(30) | YES | NULL | | is_active | TINYINT(1) | NO | 1 | | email_verified_at | TIMESTAMP | YES | NULL | | created_at, updated_at | TIMESTAMP | YES | NULL | ### roles / permissions | Column | Type | Notes | |---|---|---| | id | BIGINT UNSIGNED PK AI | | | name | VARCHAR(100) UQ | e.g. admin, manager | | guard_name | VARCHAR(50) | default web | | created_at, updated_at | TIMESTAMP | | ### role_permissions / user_roles Pivot: role_id FK RESTRICT, permission_id FK RESTRICT / user_id FK RESTRICT, role_id FK RESTRICT. Composite UQ. --- ## 2. Catalog ### brands | Column | Type | Null | Notes | |---|---|---|---| | id | BIGINT UNSIGNED | NO | PK | | public_id | CHAR(26) | NO | UQ | | name | VARCHAR(255) | NO | | | slug | VARCHAR(255) | NO | UQ | | description | TEXT | YES | | | is_active | TINYINT(1) | NO | default 1 | | deleted_at | TIMESTAMP | YES | soft delete | | created_at, updated_at | TIMESTAMP | YES | | ### categories Same pattern as brands + `parent_id BIGINT UNSIGNED NULL FK categories.id RESTRICT` ### products | Column | Type | Null | Notes | |---|---|---|---| | id | BIGINT UNSIGNED | NO | PK | | public_id | CHAR(26) | NO | UQ | | brand_id | BIGINT UNSIGNED | YES | FK brands RESTRICT | | name | VARCHAR(255) | NO | | | slug | VARCHAR(255) | NO | UQ | | description | TEXT | YES | | | status | VARCHAR(30) | NO | DRAFT, ACTIVE, INACTIVE | | deleted_at | TIMESTAMP | YES | | | created_at, updated_at | TIMESTAMP | YES | | ### product_variants (sellable unit) | Column | Type | Null | Notes | |---|---|---|---| | id | BIGINT UNSIGNED | NO | PK | | public_id | CHAR(26) | NO | UQ | | product_id | BIGINT UNSIGNED | NO | FK products RESTRICT | | sku | VARCHAR(100) | YES | UQ nullable | | upc_ean | VARCHAR(20) | YES | | | name | VARCHAR(255) | YES | variant label | | liquor_type | VARCHAR(50) | YES | filter | | country_code | CHAR(2) | YES | ISO | | region | VARCHAR(100) | YES | | | varietal | VARCHAR(100) | YES | | | vintage | SMALLINT UNSIGNED | YES | year | | abv | DECIMAL(5,2) | YES | CHECK abv>=0 | | bottle_size_ml | INT UNSIGNED | YES | | | pack_quantity | INT UNSIGNED | NO | default 1 | | status | VARCHAR(30) | NO | ACTIVE default | | deleted_at | TIMESTAMP | YES | | | created_at, updated_at | TIMESTAMP | YES | | ### product_categories product_id FK RESTRICT, category_id FK RESTRICT, UQ(product_id, category_id) ### media_assets | Column | Type | Notes | |---|---|---| | id, public_id | | | | storage_key | VARCHAR(500) UQ | S3 key | | disk | VARCHAR(50) | default s3 | | mime_type | VARCHAR(100) | | | size_bytes | BIGINT UNSIGNED | | | alt_text | VARCHAR(255) NULL | | | deleted_at | TIMESTAMP NULL | | ### catalog_media product_id NULL FK, product_variant_id NULL FK, media_asset_id FK, sort_order INT, is_primary TINYINT --- ## 3. Store ### stores | Column | Type | Notes | |---|---|---| | id, public_id | | | | name | VARCHAR(255) | | | code | VARCHAR(50) UQ | internal | | timezone | VARCHAR(50) | default UTC | | is_active | TINYINT(1) | | | created_at, updated_at | | | ### store_addresses store_id FK RESTRICT, line1, line2, city, state_code, postal_code, country_code CHAR(2), latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, is_primary TINYINT ### inventory_locations (Store module) | Column | Type | Notes | |---|---|---| | id, public_id | | | | store_id | BIGINT UNSIGNED NULL | FK stores RESTRICT | | code | VARCHAR(50) | UQ(store_id, code) | | name | VARCHAR(255) | | | is_active | TINYINT(1) | | | is_fulfillment_enabled | TINYINT(1) | default 1 | | created_at, updated_at | | | --- ## 4. Inventory ### inventory_levels | Column | Type | Notes | |---|---|---| | id | BIGINT UNSIGNED PK | | | inventory_location_id | FK RESTRICT | | | product_variant_id | FK RESTRICT | | | on_hand | INT UNSIGNED | NO default 0, CHECK>=0 | | reserved | INT UNSIGNED | NO default 0, CHECK>=0 | | CHECK reserved <= on_hand | | | | UQ(location_id, variant_id) | | | | updated_at | TIMESTAMP | | ### inventory_reservations (v1.3) | Column | Type | Notes | |---|---|---| | id | BIGINT UNSIGNED PK | | | inventory_level_id | FK RESTRICT | | | order_id | BIGINT UNSIGNED NULL | FK orders RESTRICT (added batch 6) | | quantity | INT UNSIGNED | CHECK>0 | | status | VARCHAR(30) | ACTIVE,COMMITTED,RELEASED,EXPIRED,CANCELLED | | checkout_expires_at | TIMESTAMP NULL | **NULL after payment confirmed** | | committed_at | TIMESTAMP NULL | | | released_at | TIMESTAMP NULL | | | release_reason | VARCHAR(50) NULL | | | idempotency_scope | VARCHAR(100) | | | idempotency_key | VARCHAR(64) | UQ(scope,key) | | created_at, updated_at | | | **Expiry rule:** worker only expires when checkout_expires_at NOT NULL AND order PENDING_PAYMENT. ### inventory_transactions (append-only) | Column | Type | Notes | |---|---|---| | id | BIGINT UNSIGNED PK | | | inventory_level_id | FK RESTRICT | | | type | VARCHAR(30) | RECEIVING,SALE,RETURN,... | | quantity_delta | INT | NOT NULL, CHECK!=0 | | reference_type | VARCHAR(50) NULL | order, fulfillment, adjustment | | reference_id | BIGINT UNSIGNED NULL | | | reason_code | VARCHAR(50) NULL | | | actor_type | VARCHAR(30) NULL | | | actor_id | BIGINT UNSIGNED NULL | | | correlation_id | CHAR(26) NULL | | | created_at | TIMESTAMP | NO updated_at | --- ## 5. Pricing & Cart ### price_lists id, public_id, name, code UQ, currency_code CHAR(3), is_default TINYINT, created_at, updated_at ### prices | Column | Type | Notes | |---|---|---| | price_list_id | FK RESTRICT | | | product_variant_id | FK RESTRICT | | | store_id | BIGINT NULL FK stores RESTRICT | | | amount_minor | BIGINT UNSIGNED | CHECK>=0 | | currency_code | CHAR(3) | | | effective_from | TIMESTAMP | | | effective_to | TIMESTAMP NULL | | | UQ(list, variant, store, effective_from) | | | ### carts | Column | Type | Notes | |---|---|---| | id | BIGINT UNSIGNED PK | | | customer_id | BIGINT NULL FK customers SET NULL | guest-ready | | guest_token | CHAR(36) NULL | UUID for guest | | store_id | BIGINT NULL FK stores RESTRICT | | | currency_code | CHAR(3) | default USD | | expires_at | TIMESTAMP NULL | | | created_at, updated_at | | | ### cart_items cart_id FK CASCADE, product_variant_id FK RESTRICT, quantity INT UNSIGNED CHECK>0, UQ(cart_id, variant_id) --- ## 6. Orders ### orders | Column | Type | Notes | |---|---|---| | id, public_id | | | | order_number | VARCHAR(50) UQ | human-readable | | customer_id | NULL FK SET NULL | | | store_id | FK RESTRICT | | | status | VARCHAR(50) | state machine | | currency_code | CHAR(3) | | | subtotal_minor | BIGINT UNSIGNED | | | discount_minor | BIGINT UNSIGNED | default 0 | | tax_minor | BIGINT UNSIGNED | default 0 | | shipping_minor | BIGINT UNSIGNED | default 0 | | total_minor | BIGINT UNSIGNED | | | payment_status_summary | VARCHAR(30) NULL | denormalized display only | | fulfillment_method | VARCHAR(30) NULL | SHIP, PICKUP | | compliance_decision_snapshot | JSON NULL | audit context | | placed_at | TIMESTAMP NULL | | | cancelled_at | TIMESTAMP NULL | | | cancel_reason | VARCHAR(100) NULL | | | correlation_id | CHAR(26) NULL | | | created_at, updated_at | | | ### order_items (immutable snapshots) | Column | Type | Notes | |---|---|---| | order_id | FK RESTRICT | | | product_variant_id | FK RESTRICT | historical ref | | product_name | VARCHAR(255) | snapshot | | variant_name | VARCHAR(255) | snapshot | | variant_sku | VARCHAR(100) NULL | snapshot | | liquor_type | VARCHAR(50) NULL | snapshot | | bottle_size_ml | INT NULL | snapshot | | quantity | INT UNSIGNED | ordered qty | | qty_fulfilled | INT UNSIGNED | default 0 | | qty_cancelled | INT UNSIGNED | default 0 | | qty_returned | INT UNSIGNED | default 0 | | unit_price_minor | BIGINT UNSIGNED | | | line_discount_minor | BIGINT UNSIGNED | default 0 | | line_tax_minor | BIGINT UNSIGNED | default 0 | | line_total_minor | BIGINT UNSIGNED | | | currency_code | CHAR(3) | | | snapshot_attributes | JSON NULL | extra attrs | CHECK: qty_fulfilled + qty_cancelled + qty_returned <= quantity ### order_addresses (immutable) order_id FK RESTRICT, type BILLING/SHIPPING, name, line1, line2, city, state, postal_code, country_code, phone — no updated_at semantics (insert only) ### order_status_history (append-only) order_id, from_status, to_status, actor_type, actor_id NULL, reason_code, reason_details TEXT NULL, correlation_id, created_at --- ## 7. Payments ### payments | Column | Type | Notes | |---|---|---| | id, public_id | | | | order_id | FK RESTRICT | | | status | VARCHAR(30) | PENDING,AUTHORIZED,CAPTURED,... | | amount_minor | BIGINT UNSIGNED | | | captured_minor | BIGINT UNSIGNED | default 0 | | refunded_minor | BIGINT UNSIGNED | default 0 | | currency_code | CHAR(3) | | | provider_code | VARCHAR(50) NULL | | | created_at, updated_at | | | ### payment_attempts (immutable outcomes) payment_id FK RESTRICT, attempt_number INT, status VARCHAR(30), provider_payment_ref VARCHAR(255) NULL, failure_code VARCHAR(100) NULL, idempotency_key VARCHAR(64) NULL, created_at — no updated_at ### payment_transactions (append-only) payment_attempt_id FK RESTRICT, type AUTHORIZE/CAPTURE/VOID/REFUND, amount_minor BIGINT, currency_code, provider_transaction_ref VARCHAR(255), created_at ### refunds (append-only) id, public_id, payment_id FK RESTRICT, amount_minor BIGINT CHECK>0, currency_code, reason VARCHAR(255) NULL, provider_refund_ref VARCHAR(255) NULL, status VARCHAR(30), created_at ### payment_webhook_events provider_code, provider_event_id UQ composite, payload JSON, signature_verified TINYINT, processing_status PENDING/PROCESSED/FAILED, processed_at NULL, created_at --- ## 8. Fulfillment & Shipping (Phase 1 — no vendor_id) ### shipping_methods id, public_id, code UQ, name, method_type SHIP/PICKUP, is_active, config JSON NULL, created_at, updated_at ### fulfillments | Column | Type | Notes | |---|---|---| | id, public_id | | | | order_id | FK RESTRICT | | | inventory_location_id | BIGINT UNSIGNED NOT NULL | FK inventory_locations RESTRICT | | shipping_method_id | BIGINT NULL FK shipping_methods RESTRICT | | | method_type | VARCHAR(30) | SHIP or PICKUP | | status | VARCHAR(50) | state machine | | created_at, updated_at | | | **No vendor_id in Phase 1.** Future: `ADD vendor_id` when vendors migrate. ### fulfillment_items fulfillment_id FK RESTRICT, order_item_id FK RESTRICT, quantity INT UNSIGNED CHECK>0 ### fulfillment_status_history append-only like order_status_history ### shipments (only for SHIP method) fulfillment_id FK RESTRICT, public_id, status, tracking_number NULL, carrier_code NULL, label_url NULL, shipped_at, delivered_at NULL, created_at, updated_at ### shipment_items / shipment_tracking_events standard FKs; tracking events append-only with provider_status, internal_status --- ## 9. CMS & SEO ### pages, content_sections, site_settings, seo_metadata, redirects Per v1.2 design; pages have slug UQ; content_sections JSON config; redirects from_path UQ, to_path, status_code --- ## 10. Platform ### outbox_messages (operational — status mutable) | Column | Type | Notes | |---|---|---| | event_id | CHAR(26) UQ | ULID | | event_type | VARCHAR(100) | | | aggregate_type | VARCHAR(50) | | | aggregate_id | BIGINT UNSIGNED | | | payload | JSON | | | status | VARCHAR(20) | PENDING,PROCESSING,PROCESSED,FAILED | | attempts | SMALLINT | default 0 | | available_at | TIMESTAMP | | | locked_at | TIMESTAMP NULL | | | processed_at | TIMESTAMP NULL | | | failure_reason | TEXT NULL | | | created_at | TIMESTAMP | | ### idempotency_keys per v1.2 + resource_public_id CHAR(26) NULL for replay ### audit_logs (append-only at app level) actor_type, actor_id, actor_public_id NULL, action, resource_type, resource_id NULL, resource_public_id NULL, before_state JSON NULL (redacted), after_state JSON NULL (redacted), correlation_id, request_id, ip_address, user_agent, created_at ### search_index_state entity_type VARCHAR(50), entity_id BIGINT, last_indexed_at, index_version INT, UQ(entity_type, entity_id) --- ## Compensating Ledger on Cancellation After ALLOCATED | Scenario | Ledger type | Effect | |---|---|---| | Before allocation | RELEASE reservation | reserved− | | After ALLOCATED, before ship | RETURN +N | on_hand+ (restock) | | After shipped | RETURN on receipt | on_hand+ when received | | Damage on cancel | ADJUSTMENT_OUT if not restocked | on_hand− | Never DELETE or UPDATE inventory_transactions rows. --- ## Snapshot Policy (frozen) Order items snapshot at placement: product_name, variant_name, sku, liquor attrs, all money fields, currency. Order addresses immutable. Compliance decision JSON on order header. Catalog changes never affect historical orders. --- ## State Storage VARCHAR + PHP backed enums. No MySQL ENUM. --- ## Table Count Validation 48 REQUIRED NOW tables — matches TABLE_REGISTRY.md v1.3 (vendor_id removed from fulfillments spec only; no count change). ======== FILE: DATABASE_INDEX_REGISTRY.md ======== # DATABASE_INDEX_REGISTRY.md — Architecture v1.3 Indexes for **48 REQUIRED NOW** tables. FK columns indexed unless noted. Avoid over-indexing write-heavy tables. **Legend:** UQ = UNIQUE, NQ = non-unique --- ## inventory_levels | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | uq_levels_location_variant | inventory_location_id, product_variant_id | UQ | Reserve, availability, adjust | Low | | idx_levels_variant | product_variant_id | NQ | Cross-location availability by variant | Low | ## inventory_reservations | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | uq_res_idempotency | idempotency_scope, idempotency_key | UQ | Checkout dedup | Low | | idx_res_expiry | status, checkout_expires_at | NQ | Expiry worker batch | Medium | | idx_res_order | order_id | NQ | Cancel/release by order | Low | | idx_res_level_status | inventory_level_id, status | NQ | Level reservation sum reconcile | Medium | ## inventory_transactions | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | idx_tx_level_created | inventory_level_id, created_at | NQ | Ledger history, reconcile | Medium (append) | | idx_tx_type_created | type, created_at | NQ | Admin reporting | Low | ## products | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | uq_products_public_id | public_id | UQ | API lookup | Low | | uq_products_slug | slug | UQ | Storefront URL | Low | | idx_products_brand | brand_id | NQ | Admin filter | Low | | idx_products_status | status, deleted_at | NQ | Active catalog list | Low | ## product_variants | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | uq_variants_public_id | public_id | UQ | API lookup | Low | | uq_variants_sku | sku | UQ | Ops lookup (nullable unique) | Low | | idx_variants_product | product_id | NQ | Product detail | Low | | idx_variants_filters | liquor_type, country_code, bottle_size_ml | NQ | Admin/catalog filters | Medium | ## categories | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | uq_categories_public_id | public_id | UQ | API | Low | | uq_categories_slug | slug | UQ | Storefront | Low | | idx_categories_parent | parent_id | NQ | Tree navigation | Low | ## prices | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | uq_prices_active | price_list_id, product_variant_id, store_id, effective_from | UQ | Active price resolution | Medium | | idx_prices_variant | product_variant_id, price_list_id | NQ | Checkout pricing | Low | ## carts | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | idx_carts_customer | customer_id | NQ | Logged-in cart | Low | | idx_carts_guest_token | guest_token | NQ | Guest cart (nullable) | Low | ## cart_items | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | uq_cart_items_line | cart_id, product_variant_id | UQ | One line per variant per cart | Low | | idx_cart_items_cart | cart_id | NQ | Cart load | Low | ## orders | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | uq_orders_public_id | public_id | UQ | API | Low | | idx_orders_customer_created | customer_id, created_at | NQ | Customer order history | Medium | | idx_orders_status_created | status, created_at | NQ | Admin queue | Medium | | idx_orders_store_created | store_id, created_at | NQ | Store ops | Low | ## order_items | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | idx_order_items_order | order_id | NQ | Order detail | Low | ## payments | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | uq_payments_public_id | public_id | UQ | API | Low | | idx_payments_order | order_id | NQ | Order payment lookup | Low | | idx_payments_status | status | NQ | Admin | Low | ## payment_attempts | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | idx_attempts_payment | payment_id | NQ | Attempt history | Low | | idx_attempts_provider_ref | provider_payment_ref | NQ | Webhook correlation | Low | ## payment_transactions | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | idx_tx_attempt | payment_attempt_id | NQ | Attempt txs | Low | ## payment_webhook_events | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | uq_webhook_provider_event | provider_code, provider_event_id | UQ | Webhook dedup | Low | | idx_webhook_status | processing_status, created_at | NQ | Failed webhook replay | Low | ## fulfillments | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | uq_fulfillments_public_id | public_id | UQ | API | Low | | idx_fulfillments_order | order_id | NQ | Order fulfillments | Low | | idx_fulfillments_location | inventory_location_id | NQ | Warehouse queue | Low | | idx_fulfillments_status | status, created_at | NQ | Ops dashboard | Medium | ## fulfillment_items | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | idx_fi_fulfillment | fulfillment_id | NQ | Fulfillment lines | Low | | idx_fi_order_item | order_item_id | NQ | Qty tracking | Low | ## shipments | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | uq_shipments_public_id | public_id | UQ | API | Low | | idx_shipments_fulfillment | fulfillment_id | NQ | Fulfillment shipments | Low | | idx_shipments_tracking | tracking_number | NQ | Tracking lookup | Low | ## shipment_tracking_events | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | idx_ste_shipment_created | shipment_id, created_at | NQ | Timeline | Medium (append) | | uq_ste_provider_event | shipment_id, provider_event_id | UQ | Webhook dedup (nullable provider_event_id) | Low | ## outbox_messages | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | uq_outbox_event_id | event_id | UQ | Dedup | Low | | idx_outbox_poll | status, available_at, id | NQ | Publisher SKIP LOCKED poll | High write | | idx_outbox_aggregate | aggregate_type, aggregate_id | NQ | Debug/replay | Low | ## idempotency_keys | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | uq_idem_scope | actor_scope, operation_scope, idempotency_key | UQ | Dedup | Low | | idx_idem_expires | expires_at | NQ | Cleanup job | Low | | idx_idem_resource | resource_type, resource_id | NQ | Recovery lookup | Low | ## audit_logs | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | idx_audit_resource | resource_type, resource_id | NQ | Resource history | Medium (append) | | idx_audit_actor | actor_type, actor_id | NQ | Actor history | Low | | idx_audit_correlation | correlation_id | NQ | Request trace | Low | | idx_audit_created | created_at | NQ | Retention/archive | Low | ## search_index_state | Index | Columns | Type | Query / Workflow | Write Cost | |---|---|---|---|---| | PRIMARY | id | UQ | PK | — | | uq_search_entity | entity_type, entity_id | UQ | Index lag per entity | Low | ## Other REQUIRED tables (standard) - **users/customers:** uq public_id, uq email - **roles/permissions:** uq name - **role_permissions/user_roles:** composite UQ pivots - **brands:** uq public_id, uq slug - **product_categories:** uq (product_id, category_id) - **media_assets:** uq public_id, uq storage_key - **catalog_media:** idx (attachable) - **seo_metadata:** uq (attachable_type, attachable_id) - **redirects:** uq from_path - **pages:** uq public_id, uq slug - **content_sections:** idx page_id, sort_order - **site_settings:** uq setting_key - **stores/inventory_locations:** uq public_id; uq (store_id, code) on locations - **store_addresses:** idx store_id - **price_lists:** uq public_id - **refunds:** uq public_id; idx payment_id - **order_addresses/order_status_history/fulfillment_status_history/shipment_items:** idx parent FK - **shipping_methods:** uq public_id, uq code ======== FILE: MIGRATION_IMPLEMENTATION_PLAN.md ======== # MIGRATION_IMPLEMENTATION_PLAN.md — Architecture v1.3 **Do NOT create migrations until explicitly approved to implement.** **Input:** `PHASE1_SCHEMA_SPEC.md`, `DATABASE_INDEX_REGISTRY.md`, `TABLE_REGISTRY.md` ## Principles - One migration batch per module group (FK-safe order) - Expand-only; no destructive changes in initial set - Laravel starter tables (`users`, `cache`, `jobs`) remain; domain `users` may extend or rename — **use domain `users` table as admin staff; starter `users` migration kept for Laravel auth baseline OR merge in implementation** (implementation decision: extend starter users vs separate admin_users — **v1.3 freeze: use single `users` for admin; extend starter migration additively with `public_id` column migration**) - Rollback: each batch `down()` drops tables in reverse order within batch ## Batch Order ### Batch 0 — Platform primitives (no domain FKs) | Tables | Dependencies | |---|---| | outbox_messages | none | | idempotency_keys | none | | audit_logs | none | | search_index_state | none | ### Batch 1 — Identity & RBAC | Tables | Dependencies | |---|---| | customers | none | | roles, permissions | none | | role_permissions, user_roles | roles, permissions, users* | | users (extend) | *starter users exists | **Note:** First domain migration may `ALTER users` add `public_id`, role fields OR create parallel structure. Freeze: **additive ALTER** on starter `users` for `public_id CHAR(26)`, keep Laravel compatibility. ### Batch 2 — Catalog & Media | Tables | Dependencies | |---|---| | brands | none | | categories | self parent_id nullable | | products | brands nullable | | product_variants | products | | product_categories | products, categories | | media_assets | none | | catalog_media | products/variants, media_assets | ### Batch 3 — Store & Location | Tables | Dependencies | |---|---| | stores | none | | store_addresses | stores | | inventory_locations | stores nullable | ### Batch 4 — Inventory | Tables | Dependencies | |---|---| | inventory_levels | inventory_locations, product_variants | | inventory_reservations | inventory_levels, orders* (orders FK added in batch 6 or nullable first) | | inventory_transactions | inventory_levels | **Strategy:** Create `inventory_reservations.order_id` nullable without FK in batch 4; add FK in batch 6 after orders exist. OR create reservations in batch 6. **Freeze: create table in batch 4 without order FK; add FK constraint migration in batch 6.** ### Batch 5 — Pricing & Cart | Tables | Dependencies | |---|---| | price_lists | none | | prices | price_lists, product_variants, stores nullable | | carts | customers nullable | | cart_items | carts, product_variants | ### Batch 6 — Orders | Tables | Dependencies | |---|---| | orders | customers nullable, stores | | order_items | orders, product_variants | | order_addresses | orders | | order_status_history | orders | | ALTER inventory_reservations ADD FK order_id | orders | ### Batch 7 — Payments | Tables | Dependencies | |---|---| | payments | orders | | payment_attempts | payments | | payment_transactions | payment_attempts | | refunds | payments | | payment_webhook_events | none (logical link payment_id nullable) | ### Batch 8 — Fulfillment & Shipping | Tables | Dependencies | |---|---| | shipping_methods | none | | fulfillments | orders, inventory_locations | | fulfillment_items | fulfillments, order_items | | fulfillment_status_history | fulfillments | | shipments | fulfillments | | shipment_items | shipments, fulfillment_items | | shipment_tracking_events | shipments | ### Batch 9 — CMS & SEO | Tables | Dependencies | |---|---| | pages | none | | content_sections | pages | | site_settings | none | | seo_metadata | polymorphic no FK | | redirects | none | ## Rollback Considerations - Drop in reverse batch order - Never drop starter Laravel infrastructure tables in domain rollback - Financial tables: rollback only in dev; production uses forward migrations ## Post-Migration Seed Order 1. roles, permissions 2. admin user 3. store + default inventory_location 4. shipping_methods (static: standard_ship, pickup if B2) 5. price_list + sample catalog (dev only) ## Future Additive Migrations (not Phase 1) - `fulfillments.vendor_id` when vendors module ships - promotions*, compliance*, vendors*, service_zones*, incoming_inventory* - shipping_providers, shipping_zones, shipping_rates ======== FILE: README.md ======== ## Architecture v1.3 — IMPLEMENTATION FREEZE **Status:** Ready for migration generation (see ARCHITECTURE_V1_3_FREEZE.md) ### Version History | Version | Notes | |---|---| | v1.0 | Initial architecture | | v1.1 | Foundational decisions + ADRs | | v1.2 | Consistency pass + matrices | | **v1.3** | **Implementation freeze — schema spec, indexes, migration plan, race resolution** | ### Start Here 1. `ARCHITECTURE_V1_3_FREEZE.md` — freeze report + READY verdict 2. `PHASE1_SCHEMA_SPEC.md` — authoritative column spec (48 tables) 3. `DATABASE_INDEX_REGISTRY.md` — indexes 4. `MIGRATION_IMPLEMENTATION_PLAN.md` — batch order 5. `TABLE_REGISTRY.md` — classification 6. `21-open-decisions.md` — business gates ### New v1.3 Artifacts - ARCHITECTURE_V1_3_FREEZE.md - PHASE1_SCHEMA_SPEC.md - DATABASE_INDEX_REGISTRY.md - MIGRATION_IMPLEMENTATION_PLAN.md - ADR-012, ADR-013 ### Key v1.3 Resolutions - Payment → Order confirmation async (Payments never mutates Orders) - Reservation expiry guarded by checkout_expires_at + PENDING_PAYMENT - Late payment reconciliation (reReserve or refund) - Order terminal states: COMPLETED / CANCELLED only (qty-based) - fulfillments: no vendor_id in Phase 1 - ALLOCATED atomic SALE commit + compensating ledger rules ``` READY_TO_IMPLEMENT_MIGRATIONS = YES ``` ======== FILE: TABLE_REGISTRY.md ======== # TABLE_REGISTRY.md — Architecture v1.3 (Canonical) **All table counts in architecture docs MUST derive from this registry.** ### Classification (v1.2) | Label | Meaning | Migrate now? | |---|---|---| | **REQUIRED NOW** | Needed for Phase-1 MVP path | YES | | **ARCHITECTURAL FOUNDATION** | Designed/contracts now; **no schema until feature approved** | NO (unless Exception) | | **DEFERRED** | No design urgency | NO | **Exception rule:** Migrate a foundation table now only if postponing requires **destructive redesign** of a REQUIRED table. Document each exception below. --- ## Registry | Table | Module Owner | Classification | Phase | Migration Now? | public_id? | Soft Delete? | Primary FKs | Purpose | |---|---|---|---|---|---|---|---|---| | users | Identity | REQUIRED NOW | P1 | YES | YES | NO | — | Admin/staff principals | | customers | Identity | REQUIRED NOW | P1 | YES | YES | NO | — | Storefront principals | | roles | Identity | REQUIRED NOW | P1 | YES | NO | NO | — | RBAC | | permissions | Identity | REQUIRED NOW | P1 | YES | NO | NO | — | RBAC | | role_permissions | Identity | REQUIRED NOW | P1 | YES | NO | NO | role, permission | RBAC pivot | | user_roles | Identity | REQUIRED NOW | P1 | YES | NO | NO | user, role | RBAC pivot | | products | Catalog | REQUIRED NOW | P1 | YES | YES | YES | brand_id? | Merchandising entity | | product_variants | Catalog | REQUIRED NOW | P1 | YES | YES | YES | product_id | Sellable unit | | brands | Catalog | REQUIRED NOW | P1 | YES | YES | YES | — | Brand | | categories | Catalog | REQUIRED NOW | P1 | YES | YES | YES | parent_id? | Taxonomy | | product_categories | Catalog | REQUIRED NOW | P1 | YES | NO | NO | product, category | Pivot | | media_assets | Media | REQUIRED NOW | P1 | YES | YES | YES | — | Stored files | | catalog_media | Media | REQUIRED NOW | P1 | YES | NO | NO | variant/product, media | Product media links | | seo_metadata | SEO | REQUIRED NOW | P1 | YES | NO | NO | attachable polymorphic | SEO | | redirects | SEO | REQUIRED NOW | P1 | YES | NO | NO | — | Slug/URL redirects | | pages | CMS | REQUIRED NOW | P1 | YES | YES | YES | — | CMS pages | | content_sections | CMS | REQUIRED NOW | P1 | YES | NO | NO | page_id | Homepage sections | | site_settings | CMS | REQUIRED NOW | P1 | YES | NO | NO | — | Key/value settings | | stores | Store | REQUIRED NOW | P1 | YES | YES | NO | — | Store entity (supports multi later) | | store_addresses | Store | REQUIRED NOW | P1 | YES | NO | NO | store_id | Store address | | inventory_locations | Store | REQUIRED NOW | P1 | YES | YES | NO | store_id nullable | Stock location identity | | inventory_levels | Inventory | REQUIRED NOW | P1 | YES | NO | NO | location_id, variant_id | on_hand/reserved | | inventory_transactions | Inventory | REQUIRED NOW | P1 | YES | NO | NO | level_id | Physical ledger | | inventory_reservations | Inventory | REQUIRED NOW | P1 | YES | NO | NO | level_id, order_id? | Physical reservations; `checkout_expires_at` for unpaid TTL | | price_lists | Pricing | REQUIRED NOW | P1 | YES | YES | NO | — | Price list | | prices | Pricing | REQUIRED NOW | P1 | YES | NO | NO | list_id, variant_id, store_id? | Retail amounts | | carts | Cart | REQUIRED NOW | P1 | YES | NO | NO | customer_id nullable | Cart header | | cart_items | Cart | REQUIRED NOW | P1 | YES | NO | NO | cart_id, variant_id | Cart lines | | orders | Orders | REQUIRED NOW | P1 | YES | YES | NO | customer_id?, store_id | Order header | | order_items | Orders | REQUIRED NOW | P1 | YES | NO | NO | order_id, variant_id | Snapshot lines | | order_addresses | Orders | REQUIRED NOW | P1 | YES | NO | NO | order_id | Address snapshots | | order_status_history | Orders | REQUIRED NOW | P1 | YES | NO | NO | order_id | Transitions | | payments | Payments | REQUIRED NOW | P1 | YES | YES | NO | order_id | Payment aggregate | | payment_attempts | Payments | REQUIRED NOW | P1 | YES | NO | NO | payment_id | Immutable attempts | | payment_transactions | Payments | REQUIRED NOW | P1 | YES | NO | NO | attempt_id | Append-only txs | | refunds | Payments | REQUIRED NOW | P1 | YES | YES | NO | payment_id | Append-only refunds | | payment_webhook_events | Payments | REQUIRED NOW | P1 | YES | NO | NO | — | Webhook dedup | | fulfillments | Fulfillment | REQUIRED NOW | P1 | YES | YES | NO | order_id, inventory_location_id NOT NULL (P1) | Fulfillment — no vendor_id until vendor migration | | fulfillment_items | Fulfillment | REQUIRED NOW | P1 | YES | NO | NO | fulfillment_id, order_item_id | Allocations | | fulfillment_status_history | Fulfillment | REQUIRED NOW | P1 | YES | NO | NO | fulfillment_id | Transitions | | shipments | Fulfillment | REQUIRED NOW | P1 | YES | YES | NO | fulfillment_id | Shipment (ship method only) | | shipment_items | Fulfillment | REQUIRED NOW | P1 | YES | NO | NO | shipment_id | Shipment lines | | shipment_tracking_events | Fulfillment | REQUIRED NOW | P1 | YES | NO | NO | shipment_id | Tracking | | shipping_methods | Shipping | REQUIRED NOW | P1 | YES | YES | NO | — | Delivery/pickup methods | | audit_logs | Audit | REQUIRED NOW | P1 | YES | NO | NO | soft refs | Append-only audit | | outbox_messages | Platform | REQUIRED NOW | P1 | YES | NO (event_id ULID) | NO | — | Transactional outbox | | idempotency_keys | Platform | REQUIRED NOW | P1 | YES | NO | NO | — | Write idempotency | | search_index_state | Search | REQUIRED NOW | P1 | YES | NO | NO | — | Index lag tracking | | collections | Catalog | ARCHITECTURAL FOUNDATION | Later | NO | YES | YES | — | Merchandising collections | | collection_products | Catalog | ARCHITECTURAL FOUNDATION | Later | NO | NO | NO | collection, product | Pivot | | product_attributes | Catalog | ARCHITECTURAL FOUNDATION | Later | NO | NO | NO | — | EAV schema (beyond dedicated cols) | | product_attribute_values | Catalog | ARCHITECTURAL FOUNDATION | Later | NO | NO | NO | attribute, variant | EAV values | | service_zones | Store | ARCHITECTURAL FOUNDATION | Multi-zone | NO | YES | NO | — | Geography eligibility | | service_zone_stores | Store | ARCHITECTURAL FOUNDATION | Multi-zone | NO | NO | NO | zone, store | Pivot | | serviceability_rules | Store | ARCHITECTURAL FOUNDATION | Multi-zone | NO | NO | NO | zone_id | Rules | | incoming_inventory | Inventory | ARCHITECTURAL FOUNDATION | Pre-arrival | NO* | YES | NO | variant, location, vendor? | Incoming stock | | incoming_inventory_reservations | Inventory | ARCHITECTURAL FOUNDATION | Pre-arrival | NO* | NO | NO | incoming, order | Pre-arrival holds | | promotions | Promotions | ARCHITECTURAL FOUNDATION | Promo launch | NO | YES | NO | — | Promotions | | promotion_rules | Promotions | ARCHITECTURAL FOUNDATION | Promo launch | NO | NO | NO | promotion_id | Rules | | promotion_actions | Promotions | ARCHITECTURAL FOUNDATION | Promo launch | NO | NO | NO | promotion_id | Actions | | coupons | Promotions | ARCHITECTURAL FOUNDATION | Promo launch | NO | YES | NO | promotion_id | Coupon codes | | promotion_redemptions | Promotions | ARCHITECTURAL FOUNDATION | Promo launch | NO | NO | NO | coupon, order | Redemptions | | shipping_zones | Shipping | ARCHITECTURAL FOUNDATION | Rate engine | NO | YES | NO | — | Rate geography | | shipping_rates | Shipping | ARCHITECTURAL FOUNDATION | Rate engine | NO | NO | NO | method, zone | Rates | | shipping_providers | Shipping | ARCHITECTURAL FOUNDATION | Multi-carrier | NO | YES | NO | — | Provider registry | | vendors | Vendors | ARCHITECTURAL FOUNDATION | Vendor phase | NO | YES | NO | — | Vendor registry | | vendor_accounts | Vendors | ARCHITECTURAL FOUNDATION | Vendor phase | NO | YES | NO | vendor_id | Accounts | | vendor_products | Vendors | ARCHITECTURAL FOUNDATION | Vendor phase | NO | NO | NO | account, variant? | Mapping | | vendor_offers | Vendors | ARCHITECTURAL FOUNDATION | Vendor phase | NO | NO | NO | vendor_product, variant | Cost/avail sync | | vendor_sync_runs | Vendors | ARCHITECTURAL FOUNDATION | Vendor phase | NO | NO | NO | account | Sync runs | | vendor_sync_errors | Vendors | ARCHITECTURAL FOUNDATION | Vendor phase | NO | NO | NO | sync_run | Errors | | compliance_rules | Compliance | ARCHITECTURAL FOUNDATION | Compliance | NO | NO | NO | jurisdiction? | Rules | | jurisdictions | Compliance | ARCHITECTURAL FOUNDATION | Compliance | NO | YES | NO | — | Jurisdictions | | integrations | Integration | ARCHITECTURAL FOUNDATION | First adapter | NO | YES | NO | — | Registry | | integration_credentials | Integration | ARCHITECTURAL FOUNDATION | First adapter | NO | NO | NO | integration_id | Encrypted secrets | | integration_settings | Integration | ARCHITECTURAL FOUNDATION | First adapter | NO | NO | NO | integration_id | Config | | integration_logs | Integration | ARCHITECTURAL FOUNDATION | First adapter | NO | NO | NO | integration_id | Logs | | webhook_endpoints | Integration | ARCHITECTURAL FOUNDATION | Outbound webhooks | NO | YES | NO | — | Endpoints | | webhook_subscriptions | Integration | ARCHITECTURAL FOUNDATION | Outbound webhooks | NO | NO | NO | endpoint | Subscriptions | | webhook_deliveries | Integration | ARCHITECTURAL FOUNDATION | Outbound webhooks | NO | NO | NO | subscription | Deliveries | | menus | CMS | ARCHITECTURAL FOUNDATION | Nav CMS | NO | YES | NO | — | Menus | | menu_items | CMS | ARCHITECTURAL FOUNDATION | Nav CMS | NO | NO | NO | menu | Items | | banners | CMS | ARCHITECTURAL FOUNDATION | Promo banners | NO | YES | NO | — | Banners | | tags | Catalog | DEFERRED | — | NO | YES | NO | — | Tags | | product_tags | Catalog | DEFERRED | — | NO | NO | NO | — | Pivot | | inventory_transfers | Inventory | DEFERRED | — | NO | YES | NO | — | Transfers | | inventory_transfer_items | Inventory | DEFERRED | — | NO | NO | NO | — | Transfer lines | | blog_posts | CMS | DEFERRED | — | NO | YES | YES | — | Blog | | blog_categories | CMS | DEFERRED | — | NO | YES | NO | — | Blog cats | | page_versions | CMS | DEFERRED | — | NO | NO | NO | page | Versioning | | theme_settings | CMS | DEFERRED | — | NO | NO | NO | — | Theme | | age_verification_providers | Compliance | DEFERRED | — | NO | YES | NO | — | Age verify registry | | admin_sessions | Identity | DEFERRED | — | NO | NO | NO | user | Extra session store if needed | | customer_addresses | Identity | ARCHITECTURAL FOUNDATION | Account UX | NO | NO | YES | customer_id | Saved addresses (guest-ready via carts) | \* Pre-arrival tables migrate when client confirms pre-arrival selling (feature gate). Contracts designed now. --- ## Exceptions (migrate foundation early) | Table | Exception? | Reason | |---|---|---| | `stores`, `inventory_locations` | Already REQUIRED | Multi-store foundation embedded in P1 FK design without speculative extra tables | | None other | — | No foundation tables require early migration to avoid destructive redesign | --- ## Counts (v1.3 Canonical) | Classification | Count | |---|---| | **REQUIRED NOW (migrate)** | **48** | | **ARCHITECTURAL FOUNDATION (no migrate)** | **36** | | **DEFERRED** | **10** | | **Total designed** | **94** | Note: v1.1 claimed 42/28/6 (~76) with speculative foundation migrations. v1.2 reduces **migrated** surface and adds missing designed tables (`incoming_inventory_reservations`, `redirects`, typed fulfillment FKs, etc.) without migrating them prematurely. ### Initial implementation migration set **Exactly the 48 REQUIRED NOW tables.** Laravel starter `users`/`cache`/`jobs` remain; domain migrations are additive. ======== FILE: 01-system-overview.md ======== ## 01. System Overview — v1.2 ### Goal API-first modular monolith for liquor commerce on **Laravel 13**, MySQL 8, Redis queues, Typesense projection, S3-compatible storage, Cloudflare edge. ### Non-goals (Phase 1) No microservices, Kafka, Elasticsearch, GraphQL, MongoDB, AI/MCP implementation, SGProof scraping implementation. ### Checkout Transaction Strategy (v1.2) Checkout is an **application orchestrator**, not a table owner. **Synchronously consistent (same DB transaction):** 1. Inventory physical and/or pre-arrival reservation 2. Order create in `PENDING_PAYMENT` with snapshots 3. Outbox `order.placed` **Invariant:** Order must not become `PENDING_PAYMENT` unless required reservations succeed. **Eventually consistent (after commit):** - Search indexing - Notifications - Webhook fan-out - Fulfillment creation (after `payment.captured` / `order.confirmed`) **Outside DB transactions:** - PaymentGateway / ShippingProvider / VendorConnector / Typesense / Storage network calls Module actions participate in an outer transaction via the shared DB connection; each module still enforces its own invariants. ### Inventory Commit SALE ledger + `on_hand` decrease occurs only at **fulfillment ALLOCATED**, not at payment capture. ### Search Typesense is advisory. Checkout re-validates MySQL. ### Framework Architecture baseline: **Laravel 13** (matches `composer.json`). ======== FILE: 02-domain-boundaries.md ======== ## 02. Domain Boundaries — v1.2 See `MODULE_OWNERSHIP_MATRIX.md` for canonical table ownership. ### Design Rules 1. Single MySQL database (Phase 1). 2. One owner per table; no cross-module direct mutation. 3. Interaction via contracts, orchestrators, and outbox events. 4. Checkout owns **no** commerce tables. 5. Store owns `inventory_locations`; Inventory owns quantities. 6. Fulfillment owns shipment aggregates; Shipping owns provider config/orchestration. 7. Search projection is not source of truth. ### Modules Identity, Catalog, Media, Store/Location, Inventory, Pricing, Promotions, Cart, Checkout (orchestrator), Orders, Payments, Fulfillment, Shipping, Vendors, Compliance, CMS, SEO, Search, Integration, Notification, Audit, Platform (outbox/idempotency). ### Sellable Unit Rule CartItem, Price, Inventory, Vendor mapping, OrderItem → **ProductVariant**. Product has no inventory qty or canonical retail price. ======== FILE: 03-database-architecture.md ======== ## 03. Database Architecture — v1.2 Canonical: MySQL 8. Projection: Typesense. Framework: Laravel 13. ### Identifiers (ADR-002) BIGINT UNSIGNED PK internal; ULID `public_id` on API-facing resources only (see TABLE_REGISTRY). ### Money (ADR-004) Integer minor units + ISO 4217 `currency_code`. ### Quantities INT bottles/units; no fractional Phase 1. ### Constraints See `27-database-constraints.md`. Classify invariants as DB / transactional domain / async reconcile. ### Soft delete Catalog/CMS may soft-delete. Never soft-delete orders, payments, refunds, ledger, audit, outbox. ### Historical FK `ON DELETE RESTRICT` for financial/order/ledger FKs. Discontinued variants remain for historical order_items snapshots. ### Table counts **Source of truth:** `TABLE_REGISTRY.md` — 48 migrate now / 36 foundation schema-less / 10 deferred. ======== FILE: 04-catalog.md ======== ## 04. Catalog Architecture — v1.2 (Attribute Freeze) ### Sellable Unit - **Product** = merchandising/logical entity - **ProductVariant** = sellable unit (SKU) ### Attribute Strategy (Frozen Hybrid) | Characteristic | Storage | Why | |---|---|---| | liquor_type / alcohol category | Dedicated column on variant (or product) | Core filter | | brand | Relationship `brands` | Taxonomy + SEO | | country_code | Dedicated column | Filter | | region | Dedicated column | Filter | | varietal | Dedicated nullable column | Wine filter | | vintage | Dedicated nullable column/year | Filter | | abv | Dedicated DECIMAL(5,2) | Filter/sort | | bottle_size_ml | Dedicated INT | Filter | | pack_quantity | Dedicated INT | Pack sells | | sku | Dedicated unique nullable | Ops | | upc_ean | Dedicated nullable | Ops | | producer | Configurable attribute or dedicated if always needed | Prefer attribute if sparse | | appellation | Configurable attribute | Sparse | | rating | Configurable / external | Sparse | | tasting notes | Metadata/CMS | Not filter-critical | Do not make everything nullable product columns. Do not make every filter EAV/JSON. Dedicated typed columns required for Phase-1 filters. EAV tables (`product_attributes*`) are foundation for extension — migrate when needed. ### Slugs Products, categories, brands, collections, CMS pages support SEO slugs. Unique per entity type. Slug changes create `redirects`. Canonical storefront routes use slugs; APIs use ULID; DB uses BIGINT. ### Collections Homepage featured collections may start as CMS section config referencing product/variant IDs; full `collections` tables when merchandising needs them. ======== FILE: 05-inventory.md ======== ## 05. Inventory Architecture — v1.3 See ADR-003, ADR-013, PHASE1_SCHEMA_SPEC. ### Reservations (v1.3) - `checkout_expires_at`: TTL for **unpaid** checkout only; cleared on `order.confirmed` - Expiry worker: only when `checkout_expires_at NOT NULL` AND `order.status = PENDING_PAYMENT` - Post-payment: ACTIVE reservation held until ALLOCATED (no checkout expiry) ### SALE commit Only at fulfillment ALLOCATED (atomic with reservation COMMITTED). ### Inventory.clearCheckoutExpiry(order_id) Called by ConfirmOrder consumer (Orders orchestration calling Inventory contract). Sets `checkout_expires_at = NULL` for order's ACTIVE reservations. ### reReserveForPaidOrder(order_id) Called when payment captured after prior expiry. Atomic reserve if stock available; else return failure for refund workflow. ### Adjustments Must not produce `on_hand < reserved`. Discrepancy workflow if detected. ======== FILE: 06-pricing-promotions.md ======== ## 06. Pricing & Promotions — v1.2 Pricing owns retail `prices.amount_minor`. Vendor cost lives on `vendor_offers.vendor_price_minor` and is never used as retail at checkout. Promotions/coupons are architectural foundation — migrate when launch requires them. Free-shipping eligibility is a promotion action + shipping quote interaction when enabled. Tax via TaxProvider; order snapshots store tax amounts. ======== FILE: 07-orders-fulfillment.md ======== ## 07. Orders & Fulfillment — v1.3 ### Order confirmation Async via `payment.captured` → ConfirmOrder consumer. Payments never updates orders directly. ### Terminal states COMPLETED / CANCELLED only. Quantity fields on order_items: qty_fulfilled, qty_cancelled, qty_returned. ### Fulfillments Phase 1 - `inventory_location_id NOT NULL` - **No `vendor_id`** until vendors module migrates - `method_type`: SHIP | PICKUP - ALLOCATED = physical stock commit (see §22) ### Shipments Only for SHIP method. Pickup uses fulfillment states only. ### Snapshots See PHASE1_SCHEMA_SPEC snapshot policy. ======== FILE: 08-payments.md ======== ## 08. Payments Architecture — v1.3 See ADR-012. ### Webhook Capture TX (Payments only) ``` BEGIN; INSERT payment_webhook_events (dedupe provider_event_id) SELECT payment FOR UPDATE INSERT payment_transactions (CAPTURE) UPDATE payments SET status=CAPTURED, captured_minor=... INSERT outbox payment.captured { order_public_id, payment_public_id, ... } COMMIT; ``` **Payments MUST NOT UPDATE orders.** ### Downstream (async, idempotent) 1. `ConfirmOrderOnPaymentCaptured` → order CONFIRMED + `Inventory.clearCheckoutExpiry` + outbox `order.confirmed` 2. `CreateFulfillmentOnOrderConfirmed` → fulfillment PENDING + outbox `fulfillment.created` ### Retry Failed attempt ≠ cancel order. Order stays PENDING_PAYMENT until expiry/cancel/success. ### Late webhook / reconciliation If reservations released but payment captured: `reReserveForPaidOrder` or refund path (ADR-013). ### SALE Not at payment capture. At fulfillment ALLOCATED (Inventory). ======== FILE: 09-shipping.md ======== ## 09. Shipping — v1.2 ### Ownership Shipping owns provider configuration and orchestration contracts. Fulfillment owns shipment rows/tracking. ### Capability Contracts Adapters may support subsets of: rates, createShipment, labels, track, cancel, sameDay/localDelivery, pickup coordination. Core Orders/Fulfillment never import provider SDKs. Provider metadata stays adapter/integration-owned. ### Future partners Add adapter + config rows; no core schema rewrite. ======== FILE: 10-vendors.md ======== ## 10. Vendors — v1.2 SGProof is not core domain. Capability-based VendorConnector (see INTEGRATION_CONTRACTS + §24). Schema foundation until vendor phase. Never merge vendor qty into `inventory_levels`. ======== FILE: 11-compliance.md ======== ## 11. Compliance — v1.3 (Technical Freeze) ### Technical model (not legal content) | Capability | Architecture support | |---|---| | Minimum age rule | ComplianceEngine rule type `MINIMUM_AGE` + config threshold | | Destination restrictions | `jurisdiction` + destination address on evaluation | | Pickup handoff verification | Checkpoint at fulfillment PICKUP handoff | | Delivery handoff verification | Checkpoint at shipment delivery | | Checkout verification | Checkpoint before PENDING_PAYMENT | | Provider-based age verification | `AgeVerificationProvider` adapter contract | | Versioned decisions | `ComplianceDecision` with rule_refs + version + effective dates | | Audit evidence | `orders.compliance_decision_snapshot` JSON (redacted PII) | ### ComplianceDecision contract ``` allowed: bool denial_codes: string[] required_actions: string[] // VERIFY_AGE, BLOCK_SHIPMENT, etc. rule_refs: [{ rule_id, version, effective_from, effective_to? }] evaluated_at: datetime context_snapshot: { destination, method, store_public_id, ... } ``` ### Checkpoints Catalog visibility (optional) → Checkout (required) → Fulfillment → Shipment/Pickup handoff. Checkout verification does **not** satisfy delivery/pickup handoff automatically. ### Schema `compliance_rules`, `jurisdictions` = ARCHITECTURAL FOUNDATION (migrate when legal rules supplied). Engine interfaces implemented in code; rules loaded when tables exist. ### Distinction **Technical architecture** = frozen above. **Client/legal policy** = open (B4, B5 in 21-open-decisions). ======== FILE: 12-api-standards.md ======== ## 12. API Standards — v1.2 See `API_CONVENTIONS.md` and ADR-009. Versioning `/api/v1/`. Resources/DTOs only. OpenAPI first-class. Idempotency on critical writes. Cursor pagination without mandatory totals. ======== FILE: 13-events-outbox.md ======== ## 13. Events & Transactional Outbox — v1.3 ### Payment → Order → Fulfillment chain (canonical) ``` payment.captured (Payments emits) → ConfirmOrder consumer (Orders): CONFIRMED + order.confirmed → CreateFulfillment consumer (Fulfillment): PENDING + fulfillment.created ``` Payments never mutates Orders directly. ### Consumer idempotency | Consumer | Guard | |---|---| | ConfirmOrder | `order.status == PENDING_PAYMENT` else no-op | | CreateFulfillment | existing non-terminal fulfillment for order → no-op | | Search/Audit | `event_id` dedupe table or processed flag | ### order.placed Async only: notifications, audit, search hints. Does NOT confirm payment or create fulfillment. ### Outbox PROCESSED After successful Redis enqueue. At-least-once delivery. SKIP LOCKED claim. See ADR-005, ADR-012. ======== FILE: 14-webhooks-integrations.md ======== ## 14. Webhooks & Integrations — v1.2 Outbound webhooks and integration registry are foundation (schema when needed). External webhook payloads use public ULIDs only. Signature verify + delivery retries + unhealthy endpoint disable remain as designed in v1.1 conceptually. ======== FILE: 15-auth-security.md ======== ## 15. Auth & Security — v1.2 ### Identity Model | Principal | Table | Purpose | |---|---|---| | Admin/staff | `users` + RBAC | Super Admin | | Storefront | `customers` | Customer accounts | | Integration | API credentials / OAuth | Machine clients | Separate auth stacks intentionally; shared password hashing primitives OK. ### Guest Checkout If required, carts support `customer_id` NULL + guest token; conversion attaches customer later. Confirm in open decisions. ======== FILE: 16-search.md ======== ## 16. Search — v1.2 See ADR-007 and `SEARCH_PROJECTION_MATRIX.md`. MySQL canonical. Typesense projection. Alias-swap rebuild. **Checkout must never trust Typesense for stock, price, serviceability, or compliance.** Field/facet definitions follow catalog attribute freeze (§04) + SEARCH_PROJECTION_MATRIX. ======== FILE: 17-cms-seo.md ======== ## 17. CMS & SEO — v1.2 CMS independent of commerce. Homepage sections REQUIRED. SEO metadata + redirects REQUIRED for slug changes. Address model: - `store_addresses` — store ops - `order_addresses` — immutable snapshots - `customer_addresses` — foundation for account UX Slugs for storefront; ULID for APIs; BIGINT for DB. ======== FILE: 18-observability.md ======== ## 18. Observability & Audit — v1.2 ### Audit Append-only **while retained**. Retention/archival/purge is a separate lifecycle under approved privacy policy — not “never delete forever” and “configurable retention” without explanation. Lifecycle: 1. Hot retain (queryable) 2. Archive (cold storage) after policy threshold 3. Purge only under legal/privacy approval ### Polymorphic actor/resource refs Intentionally **no hard FKs**. Historical evidence may outlive resources. Store public_ids + snapshots sufficient to interpret after deletion/deactivation. ### PII / Sensitive Classification Do **not** dump `model->toArray()`. | Class | Examples | Audit policy | |---|---|---| | Secrets | passwords, tokens, API keys, webhook secrets, card data | Never store; `[REDACTED]` | | PII sensitive | DOB, ID docs, verification payloads | Redact or hash; store decision outcome only | | PII operational | email, phone, address | Allowlist per action; prefer truncated/masked unless required | | Business | prices, qty, status | Allowed | Module-specific audit payloads (allowlists) required. ======== FILE: 19-testing.md ======== ## 19. Testing — v1.2 See `CRITICAL_SCENARIO_TEST_MATRIX.md` for required concurrency/failure scenarios. Layers: unit, domain, feature/API, contract (OpenAPI), adapter mocks, queue/outbox, search projection rebuild, inventory concurrency, payment idempotency, webhooks. ======== FILE: 20-deployment.md ======== ## 20. Deployment — v1.2 (Laravel 13) ### Processes | Process | Responsibility | |---|---| | Laravel HTTP/API | Sync requests | | Queue workers | Domain consumers, webhooks outbound, search jobs | | Outbox publisher worker | Claim outbox → Redis enqueue | | Scheduler | Expiry, stuck recovery, vendor sync triggers, reconcile | | MySQL | Canonical data | | Redis | Cache + queues | | Typesense | Search projection | ### Queue names (suggested) `outbox`, `default`, `search`, `webhooks`, `notifications`, `integrations` ### Ops Graceful worker restart on deploy; failed_jobs table; health/readiness: DB, Redis, queue lag, outbox lag, Typesense optional. Container-ready; Kubernetes not required. ### Migrations Expand-only compatible; avoid destructive changes without dual-write windows. ======== FILE: 21-open-decisions.md ======== ## 21. Open Decisions — v1.3 ### Resolved in v1.3 (technical — see ARCHITECTURE_V1_3_FREEZE.md) Payment→Order async confirmation; reservation expiry race; order terminal qty semantics; vendor_id deferred; ALLOCATED semantics; checkout idempotency boundaries; schema/index/migration plan frozen. ### Remaining Business Gates (unchanged) B1–B7, C1–C10 — see v1.2 list. Do not invent. ### Implementation ``` READY_TO_IMPLEMENT_MIGRATIONS = YES ``` for 48 REQUIRED tables per PHASE1_SCHEMA_SPEC.md. Feature gates block only their features. ======== FILE: 22-state-machines.md ======== ## 22. State Machines — v1.3 Statuses: `VARCHAR(50)` + PHP backed enums. History: from_status, to_status, actor_type (CUSTOMER|ADMIN|SYSTEM|PROVIDER), actor_id, reason_code, details, correlation_id, created_at. --- ### 22.1 Order (canonical domain states only) | State | Description | |---|---| | `PENDING_PAYMENT` | Placed; stock reserved; awaiting capture | | `CONFIRMED` | Payment captured; awaiting fulfillment ops | | `PROCESSING` | Fulfillment active | | `PARTIALLY_FULFILLED` | Some qty fulfilled; remainder open | | `COMPLETED` | Terminal: all units resolved, qty_fulfilled > 0 | | `CANCELLED` | Terminal: all units cancelled (qty_fulfilled = 0) | **Removed:** `CLOSED` as domain state. Use `COMPLETED` for mixed fulfill+cancel when fully resolved. #### Quantity resolution (on order_items) ``` qty_open = quantity - qty_fulfilled - qty_cancelled - qty_returned ``` | Scenario | qty_fulfilled | qty_cancelled | qty_open | Status | |---|---|---|---|---| | 10/10 fulfilled | 10 | 0 | 0 | COMPLETED | | 10/10 cancelled | 0 | 10 | 0 | CANCELLED | | 7 fulfilled / 3 cancelled | 7 | 3 | 0 | COMPLETED | | 7 fulfilled / 3 open | 7 | 0 | 3 | PROCESSING or PARTIALLY_FULFILLED | Terminal: `qty_open = 0`. COMPLETED if `qty_fulfilled > 0`. CANCELLED if `qty_fulfilled = 0`. Customer display status (e.g. "Partially Shipped") is a **presentation** mapping, not a domain state. #### Transitions | From | To | Trigger | Event | |---|---|---|---| | — | PENDING_PAYMENT | Checkout TX success | order.placed | | PENDING_PAYMENT | CONFIRMED | ConfirmOrder consumer on payment.captured | order.confirmed | | PENDING_PAYMENT | CANCELLED | Expiry/cancel | order.cancelled | | CONFIRMED | PROCESSING | Fulfillment created/allocated | — | | * | COMPLETED/CANCELLED | Qty resolution rules | order.completed / cancelled | Payment attempt FAILED does **not** cancel order. --- ### 22.2 Payment Aggregate: PENDING → AUTHORIZED → CAPTURED → REFUNDED variants. Attempts immutable. Webhook TX only updates Payments + outbox `payment.captured`. --- ### 22.3 Fulfillment | State | Meaning | |---|---| | PENDING | Created after order.confirmed | | ALLOCATED | **Stock committed** (SALE atomic) | | PICKING | Warehouse picking | | PACKED | Packed | | READY_FOR_PICKUP | Pickup only | | PICKED_UP | Customer collected | | COMPLETED | Handed off (shipped delivered or picked up) | | CANCELLED | Cancelled | #### ALLOCATED atomic TX (single authority) ``` BEGIN; lock reservation ACTIVE, level FOR UPDATE UPDATE reservation SET COMMITTED WHERE id=? AND status=ACTIVE (1 row) UPDATE level SET reserved-=qty, on_hand-=qty INSERT inventory_transactions SALE -qty UPDATE fulfillment SET ALLOCATED INSERT fulfillment_status_history outbox inventory.sale, fulfillment.allocated COMMIT; ``` #### Cancellation ledger | When | Inventory action | |---|---| | Before ALLOCATED | Release reservation (RELEASED) | | After ALLOCATED, restock | RETURN +qty ledger | | After ALLOCATED, no restock | ADJUSTMENT_OUT or DAMAGE | | After shipped | RETURN when goods received back | Never mutate SALE rows; compensating entries only. Phase 1: `inventory_location_id NOT NULL`. No `vendor_id`. --- ### 22.4 Shipment PENDING → READY → SHIPPED → IN_TRANSIT → DELIVERED | EXCEPTION (non-terminal) → FAILED | CANCELLED | RETURNED Pickup: no shipment row. --- ### 22.5 Reservation ACTIVE → COMMITTED (ALLOCATED) | RELEASED | EXPIRED | CANCELLED `checkout_expires_at` cleared on order.confirmed (ADR-013). ======== FILE: 23-idempotency.md ======== ## 23. Idempotency — v1.3 ### Checkout (POST /orders) flow ``` 1. BEGIN short TX INSERT idempotency_keys status=PROCESSING (or ON DUPLICATE: replay/completed/conflict) COMMIT 2. BEGIN outer checkout TX Inventory.reserve (idempotency scoped to checkout key) Orders.createPendingPayment Outbox order.placed UPDATE idempotency_keys SET status=COMPLETED, resource_type=order, resource_public_id=..., response_body=... COMMIT 3. Return response (or replay on retry) ``` ### Crash recovery | Crash point | Recovery | |---|---| | PROCESSING, no order | Stuck job >5m → FAILED_SAFE_TO_RETRY or allow retry same key | | Order committed, key not COMPLETED | Reconcile job completes idempotency from order by key | | COMPLETED | Replay stored response | | Same key, different fingerprint | IDEMPOTENCY_CONFLICT 422 | | Duplicate while PROCESSING | 409 IDEMPOTENCY_CONFLICT | ### Unique constraint `UNIQUE(actor_scope, operation_scope, idempotency_key)` prevents duplicate orders. Reservation idempotency scoped under same checkout key prevents double reserve. Never hold TX open during provider HTTP. ======== FILE: 24-pre-arrival-vendor-inventory.md ======== ## 24. Pre-Arrival & Vendor Inventory — v1.2 ### 24.1 Pre-Arrival Model Pre-arrival is **not** a product type. Physical `on_hand` never includes expected incoming stock. #### Tables **`incoming_inventory`** (aggregate expected shipment of a variant to a location) **`incoming_inventory_reservations`** (Option A — preferred for clarity) | Column (reservations) | Notes | |---|---| | id | BIGINT PK | | incoming_inventory_id | FK | | order_id | FK | | order_item_id | FK nullable | | quantity | > 0 | | status | ACTIVE, CONVERTED, RELEASED, EXPIRED, CANCELLED | | expires_at | nullable / policy-driven | | converted_reservation_id | FK to physical inventory_reservations when converted | | created_at | | #### Availability ``` available_prearrival = expected_quantity - reserved_quantity ``` `reserved_quantity` is denormalized aggregate of ACTIVE (and optionally CONVERTED-pending) reservation qty; updated atomically with row lock. #### Concurrency ``` BEGIN; SELECT * FROM incoming_inventory WHERE id=? FOR UPDATE; IF expected - reserved < qty THEN fail; INSERT incoming_inventory_reservations ACTIVE; UPDATE incoming_inventory SET reserved_quantity = reserved_quantity + qty; COMMIT; ``` #### Partial receiving (example: expected 100, pre-sold 80, received 50) 1. Lock incoming + destination inventory_level. 2. RECEIVING +50 to `on_hand`. 3. Allocate received units to ACTIVE pre-arrival reservations by **priority** (configurable: earliest paid order first). 4. For each allocated reservation qty Q: - Create physical `inventory_reservations` ACTIVE (or HELD) for Q against level. - Mark incoming reservation CONVERTED (or partially convert via split rows / remaining qty field). - Decrement incoming `reserved_quantity` for converted portion; decrement remaining expected. 5. Status → `PARTIALLY_RECEIVED` if expected remaining > 0. 6. Unfulfilled pre-sold qty remain on incoming until future receipts, cancellation, or refund path. 7. Notify customers of delays; audit each allocation; refunds via Payments if cancelled. Do not assume 100% arrival. #### Feature gate Tables are **ARCHITECTURAL FOUNDATION** — migrate when client confirms pre-arrival selling (`21-open-decisions`). --- ### 24.2 Vendor Inventory & Capabilities Vendor snapshot quantity ≠ our ledger guarantee. #### Capability-oriented VendorConnector (illustrative) ``` interface VendorConnector { healthCheck(): Health; capabilities(): VendorCapabilities; } interface VendorCapabilities { supportsCatalogFetch: bool; supportsAvailabilitySync: bool; // informational supportsReservation: bool; // hold at vendor supportsOrderPlacement: bool; // confirmable purchase supportsCancellation: bool; supportsTracking: bool; supportsRealtimePricing: bool; } ``` | Capability level | Oversell guarantee | |---|---| | Informational availability only | None — display/sync only; checkout must not treat as reserved stock | | Reservable | Hold via vendor API then local tracking | | Order-confirmable | Confirm vendor order before promising customer | #### Prices - `vendor_offers.vendor_price_minor` = **procurement/source cost** - `prices.amount_minor` = **customer retail** - Checkout never uses vendor cost as retail. Vendor qty never merges into `inventory_levels.on_hand`. ======== FILE: 25-multi-store-serviceability.md ======== ## 25. Multi-Store & Serviceability — v1.2 ### Entities Store ≠ InventoryLocation ≠ Address ≠ ServiceZone. ### SERVICEABILITY vs RANKING - **SERVICEABILITY** = which sources are eligible (destination, compliance, method, stock, vendor capability). - **RANKING** = preference among eligible sources (business priority, distance, completeness, SLA, cost, vendor fallback). Nearest store is **never** automatically canonical fulfillment logic. Distance is a ranking input for **nearby store visibility**, not sole selector. ### Eligibility Targets (no generic fulfillment_sources table unless later justified) | Source | Eligibility | |---|---| | Store / inventory location | Zone + stock + method (ship/pickup) | | Vendor | Capability + offer freshness + method | | Shipping provider/method | Zone + rates + compliance | ### Pickup Store pickup eligibility is a serviceability outcome; fulfillment method `PICKUP`. ### Phase 1 Single store operationally OK; schema includes stores + locations for expansion without rewrite. ======== FILE: 26-phase1-table-classification.md ======== ## 26. Phase-1 Table Classification — v1.2 **Superseded by `TABLE_REGISTRY.md`.** This file summarizes only. | Classification | Count | Migrate? | |---|---|---| | REQUIRED NOW | 48 | YES | | ARCHITECTURAL FOUNDATION | 36 | NO (design/contracts only) | | DEFERRED | 10 | NO | v1.1 incorrectly planned migrating ~28 foundation tables. v1.2 does **not**. Exceptions: none beyond stores/locations already REQUIRED for inventory FKs. ======== FILE: 27-database-constraints.md ======== ## 27. Database Constraints — v1.2 ### Invariant Classes | Class | Examples | |---|---| | DB enforced | PK/FK/UNIQUE/CHECK non-negative, reserved<=on_hand, qty>0, amount>0 | | Transactional domain | available enough to reserve; payment capture guards; double-commit prevention | | Async reconcile | levels vs ledger sum; search lag | ### Critical Checks - inventory_levels: on_hand>=0, reserved>=0, reserved<=on_hand; UNIQUE(location,variant) - reservations: quantity>0; UNIQUE(idempotency_scope,key) - prices/payments/refunds: amount_minor>=0 or >0 as applicable; currency NOT NULL - order_items: quantity>0 - fulfillments: (location_id IS NOT NULL) XOR (vendor_id IS NOT NULL) for Phase-1 sources - idempotency_keys: UNIQUE(actor, op, key) - outbox: UNIQUE(event_id) - payment_webhook_events: UNIQUE(provider_event_id) Do not use CHECK for cross-row aggregates MySQL cannot enforce (e.g., sum of reservations equals reserved) — maintain transactionally + reconcile. ======== FILE: API_CONVENTIONS.md ======== ## API_CONVENTIONS.md — v1.2 Framework: Laravel 13 APIs under `/api/v1/`. ### Identifiers - API: ULID `public_id` - Storefront SEO routes: `slug` where applicable - DB: BIGINT (never in public API) ### Success Envelope `{ data, meta?, request_id, correlation_id }` ### Error Envelope ```json { "error": { "code": "INVENTORY_UNAVAILABLE", "message": "...", "details": {} }, "request_id": "..." } ``` Codes: VALIDATION_ERROR, AUTHENTICATION_REQUIRED, FORBIDDEN, RESOURCE_NOT_FOUND, CONFLICT, INVENTORY_UNAVAILABLE, INVALID_STATE_TRANSITION, IDEMPOTENCY_CONFLICT, RATE_LIMITED, INTEGRATION_FAILURE, INTERNAL_ERROR. ### Pagination Cursor: `next_cursor`, `has_more`. **`total` optional** — do not require exact totals. Admin may use page+total where useful. Search may use Typesense pagination. ### Idempotency See `23-idempotency.md`. ======== FILE: ARCHITECTURE_CONSISTENCY_MATRIX.md ======== # Architecture Consistency Matrix — v1.2 Defines transaction boundaries, locks, events, and failure recovery for critical workflows. **Rules:** 1. Module actions may join an **outer DB transaction** opened by an application orchestrator (same MySQL connection). 2. Modules still own invariants; orchestrator does not UPDATE foreign tables directly. 3. **External provider network calls NEVER run inside an open DB transaction.** 4. Typesense is never used for transactional decisions. --- ## Workflow Matrix | Workflow | API/Command | Orchestrator | Owning Module(s) | DB TX Boundary | Tables Mutated | Locks | State Transition | Ledger | Outbox Event | Async Consumers | External Call | Idempotency | Failure Recovery | |---|---|---|---|---|---|---|---|---|---|---|---|---|---| | Product create/update | Admin Catalog API | Catalog | Catalog | Single TX | products, variants, media links, outbox | — | — | — | product.* | Search, Audit | Storage put (before TX or after media row) | Admin write key optional | Retry safe | | Price change | Admin Pricing API | Pricing | Pricing | Single TX | prices, outbox | row on price | — | — | price.updated | Search, Audit | — | Optional | Replay | | Inventory receiving | Admin receive | Inventory | Inventory | Single TX | levels, transactions, outbox | FOR UPDATE level | — | RECEIVING +N | inventory.adjusted | Search, Audit | — | Required | Unique receive key | | Inventory adjustment | Admin adjust | Inventory | Inventory | Single TX | levels, transactions, outbox | FOR UPDATE level | Validate on_hand≥reserved after | ADJUSTMENT_* | inventory.adjusted | Search, Audit | — | Required | Reject if violates reserved | | Physical reservation | PlaceOrder (inner) | Checkout | Inventory | Outer checkout TX | reservations, levels.reserved | FOR UPDATE levels ordered | ACTIVE | none | inventory.reservation.created | Audit | — | Part of order key | Rollback order if fail | | Pre-arrival reservation | PlaceOrder (inner) | Checkout | Inventory | Outer checkout TX | incoming_reservations, incoming.reserved_qty | FOR UPDATE incoming | ACTIVE | none | incoming.reservation.created | Audit | — | Part of order key | Rollback if fail | | Checkout / order placement | POST /orders | **Checkout** | Orders + Inventory (+ reads) | **Outer TX: reserve + create order + outbox** | reservations, orders*, outbox | inventory locks | Order→PENDING_PAYMENT | none | order.placed | Notifications, Audit, Search (non-critical) | **None inside TX** | Required | No PENDING_PAYMENT without reservation | | Payment initiation | POST /payments | Payments | Payments | Short TX create payment+attempt | payments, attempts | — | Payment PENDING | — | — | — | **After commit:** PaymentGateway | Required | Unknown→reconcile | | Payment retry | POST /payments retry | Payments | Payments | Short TX new attempt | payment_attempts | payment row | Attempt FAILED stays; new attempt | — | payment.failed (attempt) | Orders stay PENDING_PAYMENT | Provider after commit | Required | Order not cancelled | | Payment webhook capture | Webhook | Payments | Payments only | TX: webhook dedup + payment CAPTURED + outbox | webhook_events, payments, transactions, outbox | payment FOR UPDATE | Payment CAPTURED only | none | payment.captured | **Async:** ConfirmOrder → order.confirmed; CreateFulfillment | Verify signature only | provider_event_id unique | Payments never mutates orders | | Reservation expiry | Scheduler | Inventory | Inventory | TX per batch | reservations, levels | FOR UPDATE res→level→order | ACTIVE→EXPIRED only if checkout_expires_at set AND order PENDING_PAYMENT | none | inventory.reservation.released | Orders may cancel if unpaid | — | Job idempotent | Never expire paid/post-confirm holds | | Order cancel (unpaid) | Customer/Admin/System | Orders | Orders + Inventory | Outer TX | orders, reservations release | order + levels | CANCELLED | none | order.cancelled | — | — | Required | — | | Order cancel (paid) | Admin | Orders | Orders + Payments + Inventory/Fulfillment | Orchestrated steps | orders; refunds separate; releases | — | CANCELLED | reverse SALE if allocated | order.cancelled | Refund async | Refund provider **outside** TX | Required | Partial failure → reconcile | | Refund | Admin refund | Payments | Payments | Short TX + provider | refunds, payment status | payment FOR UPDATE | PARTIAL/FULL REFUNDED | — | payment.refunded | Orders display | Provider outside TX | Required | Provider idempotency | | Fulfillment create | On order.confirmed | Fulfillment | Fulfillment | TX | fulfillments, items, history, outbox | — | PENDING | none | fulfillment.created | — | — | By order id | — | | Inventory SALE commit | Allocate fulfillment | Fulfillment→Inventory | Inventory | TX | reservations COMMITTED, levels on_hand/reserved, transactions SALE, fulfillment ALLOCATED | FOR UPDATE level+reservation | Reservation COMMITTED; Fulfillment ALLOCATED | **SALE −N** | inventory.sale | Search | — | reservation id | Prevent double commit | | Pre-arrival receiving (partial) | Admin receive | Inventory | Inventory | TX | incoming, levels RECEIVING, convert reservations | FOR UPDATE incoming+levels | PARTIALLY_RECEIVED | RECEIVING | incoming.received | Convert to physical reservations | — | Required | Priority allocate paid orders | | Pickup ready/complete | Admin/Store | Fulfillment | Fulfillment | TX | fulfillment status | fulfillment | READY_FOR_PICKUP→PICKED_UP→COMPLETED | — | fulfillment.picked_up | Orders progress | — | Required | No shipment row | | Shipment create | After PACKED ship method | Fulfillment+Shipping | Fulfillment | Short TX then provider | shipments | — | PENDING | — | shipment.created | — | **Label create outside TX** | Required | Reconcile if timeout | | Tracking webhook | Carrier webhook | Shipping→Fulfillment | Fulfillment | TX dedup + status | tracking_events, shipment | shipment FOR UPDATE | Normalized status | — | shipment.* | Notifications | Verify signature | provider event id | Duplicate ignore | | Vendor sync | Scheduler | Vendors | Vendors | Per batch TX | vendor_offers, sync_runs | — | — | none | vendor.sync.* | Search availability flag | Connector **outside** long TX | Sync run id | Stale TTL | | Search reindex | Outbox consumer | Search | Search | Consumer TX optional | search_index_state | — | — | — | — | Typesense upsert | Typesense after claim | event_id | Retry; MySQL unaffected | --- ## Checkout Critical Invariant ``` BEGIN; Inventory.reserve(...) -- must succeed Orders.createPendingPayment(...) -- snapshots + PENDING_PAYMENT Outbox.write(order.placed) COMMIT; -- THEN payment provider call (outside TX) ``` If reserve fails → no order. If order insert fails → full rollback including reserve. `order.placed` async consumers **must not** enforce reservation or payment invariants. --- ## Inventory SALE Commit Point (Single Authority) | Moment | reserved | on_hand | Reservation status | Ledger | |---|---|---|---|---| | Checkout reserve | +qty | unchanged | ACTIVE | none | | Payment captured | unchanged | unchanged | ACTIVE (held) | none | | Fulfillment ALLOCATED | −qty | −qty | COMMITTED | SALE −qty | | Expiry/cancel before allocate | −qty | unchanged | EXPIRED/RELEASED | none | Double-commit prevention: `UPDATE reservations SET status='COMMITTED' WHERE id=? AND status='ACTIVE'` must affect 1 row. ======== FILE: ARCHITECTURE_REVIEW.md ======== ## ARCHITECTURE_REVIEW.md — v1.2 ### Executive Summary Architecture v1.2 makes v1.1 **internally consistent** and aligned with the architecture PRD (`first-prompt.txt`). Critical contradictions (ownership, SALE commit, payment retry, speculative migrations, order completion, outbox semantics, event IDs) are resolved. Speculative foundation table migration is rejected. **Implementation readiness: READY for core Phase-1 commerce path** (with feature gates for pre-arrival, pickup UX confirmation, promotions, compliance rule content, guest checkout confirmation). ### Decisions Changed from v1.1 1. Store owns `inventory_locations` 2. SALE/commit at fulfillment ALLOCATED only 3. Payment attempt failure ≠ order cancel 4. Order completion by quantities 5. Do not migrate 28 foundation tables 6. Pre-arrival uses `incoming_inventory_reservations` 7. Typed fulfillment source FKs 8. Pickup states on fulfillment 9. Outbox PROCESSED = Redis enqueue success 10. Event ID rules for internal vs external 11. Audit PII allowlists + retention lifecycle 12. Cursor totals optional 13. Laravel 13 locked in architecture docs 14. ADR-010 checkout TX; ADR-011 schema minimalism ### Final Module Ownership See MODULE_OWNERSHIP_MATRIX.md. ### Final Table Counts 48 REQUIRED migrate | 36 foundation schema-less | 10 deferred. ### Risks Remaining - High concurrency inventory under load (mitigated by tests) - Compliance content unknown (feature content, not structure) - Provider indeterminate outcomes (reconcile jobs required) - Pre-arrival partial receiving operational complexity when enabled ### Approval Gate - [x] Technical contradictions resolved - [ ] Client confirms feature gates in `21-open-decisions.md` as needed - [ ] Explicit go-ahead to implement migrations for 48 REQUIRED tables ======== FILE: CRITICAL_SCENARIO_TEST_MATRIX.md ======== # CRITICAL_SCENARIO_TEST_MATRIX.md — Architecture v1.3 | # | Scenario | Expected Invariant | Test Type | Lock / Idempotency | |---|---|---|---|---| | 1–30 | (see v1.2) | | | | | 31 | Expiry wins before payment | Reservation EXPIRED; order may cancel; no stock held | Concurrency | checkout_expires_at + PENDING_PAYMENT guard | | 32 | Payment capture wins before expiry | checkout_expires_at cleared; reservation ACTIVE | Concurrency | ConfirmOrder clears expiry | | 33 | Webhook after checkout_expires_at but before expiry job | ConfirmOrder clears expiry; no false expire | Race | Conditional expiry SQL | | 34 | Payment captured after reservation EXPIRED | reReserve or refund; never confirm without stock | Integration | reReserveForPaidOrder | | 35 | Concurrent expiry job vs ConfirmOrder | One wins via FOR UPDATE; other no-op | Concurrency | Lock reservations→levels→orders | | 36 | Duplicate payment.captured event | Payment captured once; ConfirmOrder no-op second time | Webhook | provider_event_id + order.status guard | | 37 | Duplicate order.confirmed event | Single fulfillment created | Consumer | fulfillment exists guard | | 38 | Duplicate fulfillment.created consumer | One fulfillment PENDING | Consumer | idempotent create | | 39 | Allocation vs cancellation race | ALLOCATED or CANCELLED consistently; no double SALE | Concurrency | reservation status conditional | | 40 | Allocation vs inventory adjustment | Adjustment rejected or queued if violates reserved | Domain | FOR UPDATE level | | 41 | Refund concurrency | sum(refunds) <= captured | Concurrency | payment FOR UPDATE | | 42 | Out-of-order provider webhooks | State advances only via valid transitions | Webhook | status guards | | 43 | Outbox duplicate enqueue | Consumer side effect once | Queue | event_id | | 44 | Idempotency PROCESSING crash mid-checkout | Retry completes or replays; no duplicate order | Feature | unique idempotency key | | 45 | Order 7 fulfilled 3 cancelled | COMPLETED not CLOSED; qty_open=0 | Domain | qty fields | Scenarios 1–30 retained from v1.2 (competing checkout, deadlock, duplicate webhook, payment retry, Typesense stale, pickup, double SALE, etc.). ======== FILE: ERD.md ======== ## ERD.md — v1.2 Canonical counts: `TABLE_REGISTRY.md` (48 migrate / 36 foundation / 10 deferred). ### Ownership highlights - Store → `inventory_locations` - Inventory → levels, transactions, reservations, incoming* - Fulfillment → fulfillments, shipments, tracking - Shipping → methods/rates/providers (config) ### Master (migrated core) ```mermaid erDiagram CUSTOMERS ||--o{ ORDERS : places CUSTOMERS ||--o{ CARTS : owns PRODUCTS ||--o{ PRODUCT_VARIANTS : has STORES ||--o{ INVENTORY_LOCATIONS : has INVENTORY_LOCATIONS ||--o{ INVENTORY_LEVELS : tracks PRODUCT_VARIANTS ||--o{ INVENTORY_LEVELS : stocked_as INVENTORY_LEVELS ||--o{ INVENTORY_RESERVATIONS : reserved INVENTORY_LEVELS ||--o{ INVENTORY_TRANSACTIONS : ledger ORDERS ||--o{ ORDER_ITEMS : contains ORDERS ||--o{ PAYMENTS : has ORDERS ||--o{ FULFILLMENTS : fulfilled_by FULFILLMENTS ||--o{ SHIPMENTS : ships FULFILLMENTS }o--|| INVENTORY_LOCATIONS : location_fk_required_p1 PRODUCT_VARIANTS ||--o{ PRICES : priced ``` ### Fulfillment source RI `fulfillments.inventory_location_id` XOR `fulfillments.vendor_id` (vendor FK when vendor tables exist). ### Pre-arrival (foundation) `incoming_inventory` 1—N `incoming_inventory_reservations` ======== FILE: EVENT_CATALOG.md ======== ## EVENT_CATALOG.md — v1.3 ### payment.captured - Producer: Payments (webhook TX) - Payload: `event_id`, `payment_public_id`, `order_public_id`, `captured_minor`, `currency_code`, `correlation_id` - Consumers: **ConfirmOrder** (sync critical path via queue), Audit - Idempotency: event_id; ConfirmOrder guards order.status - Webhook eligible: yes ### order.confirmed - Producer: Orders (ConfirmOrder consumer) - Payload: `order_public_id`, `confirmed_at`, `correlation_id` - Consumers: **CreateFulfillment**, Audit, Notifications - Idempotency: order already CONFIRMED → skip ### fulfillment.created - Producer: Fulfillment (CreateFulfillment consumer) - Payload: `fulfillment_public_id`, `order_public_id`, `inventory_location_public_id`, `method_type` - Consumers: Admin notifications, Audit - Idempotency: existing fulfillment → skip ### fulfillment.allocated - Producer: Fulfillment/Inventory (allocate TX) - Payload: `fulfillment_public_id`, `order_public_id`, quantities - Consumers: Search (availability), Audit ### inventory.sale - Producer: Inventory (allocate TX) - Payload: variant_public_id, location_public_id, quantity, order_public_id - Consumers: Search, Audit ### order.placed - Producer: Checkout (checkout TX) - Async non-critical: Audit, Search hint, Notifications - Does NOT confirm order or create fulfillment (Other events unchanged from v1.2 catalog.) ======== FILE: INTEGRATION_CONTRACTS.md ======== ## INTEGRATION_CONTRACTS.md — v1.2 Business modules depend on contracts, not SDKs. Laravel 13 container bindings. ### VendorCapabilities supportsCatalogFetch, supportsAvailabilitySync, supportsReservation, supportsOrderPlacement, supportsCancellation, supportsTracking, supportsRealtimePricing. ### PaymentGateway authorize, capture, refund, void, verifyWebhook, processWebhook — with idempotency keys; timeouts → indeterminate reconcile. ### Shipping ShippingRateProvider.quote; ShippingProvider create/cancel/track/labels; optional same-day/local capabilities. ### Others TaxProvider, AgeVerificationProvider, SearchProvider, StorageProvider, NotificationProvider. Provider-specific metadata remains adapter/integration-owned. ======== FILE: MODULE_OWNERSHIP_MATRIX.md ======== # Module Ownership Matrix — Architecture v1.2 **Rule:** Every table has exactly **one** canonical owning module. Other modules may **read** via contracts/queries; they **mutate** only through the owner’s public actions. **FK policy:** Cross-module FKs reference internal BIGINT PKs of the owned table. The referencing module never updates the owned table’s non-FK columns. --- ## Ownership Resolution (v1.1 Conflicts Fixed) | Conflict | v1.2 Canonical Owner | |---|---| | `inventory_locations` | **Store** (location identity). Inventory references `inventory_location_id`. | | `shipments` / tracking | **Fulfillment** owns shipment aggregates. **Shipping** owns provider config + orchestration adapters. | | Checkout | **Application orchestrator** — owns no commerce state tables. | | `outbox_messages` | **Shared Infrastructure** (Platform), written via OutboxPublisher helper used by all modules. | | `idempotency_keys` | **Shared Infrastructure** (Platform). | | `addresses` (operational) | **Identity/Store** for customer/store addresses; **Orders** owns immutable `order_addresses` snapshots. | --- ## Matrix | Table/Aggregate | Owning Module | Read By | Mutated Through | Public Contract | Events Published | Cross-module FK Policy | |---|---|---|---|---|---|---| | `users` | Identity | Admin, Audit | Identity actions | Auth/UserService | — | FK from audit actor refs (soft) | | `customers` | Identity | Orders, Cart, Checkout | Identity actions | CustomerService | customer.registered | Orders FK customer_id | | `roles`, `permissions`, `role_permissions`, `user_roles` | Identity | Admin middleware | Identity RBAC actions | AuthorizationService | — | Internal | | `products` | Catalog | Search, CMS, Admin | Catalog actions | CatalogProductService | product.created/updated/deleted | Soft-delete; historical FKs RESTRICT | | `product_variants` | Catalog | Inventory, Pricing, Cart, Orders, Vendors, Search | Catalog actions | CatalogVariantService | product.updated | Sellable unit FK target | | `brands`, `categories`, `product_categories` | Catalog | Search, SEO | Catalog actions | CatalogTaxonomyService | product.updated | — | | `collections`, `collection_products` | Catalog | CMS, Promotions | Catalog actions | CollectionService | — | Schema when needed | | `product_attributes`, `product_attribute_values` | Catalog | Search | Catalog actions | AttributeService | product.updated | Schema when needed | | `media_assets`, `catalog_media` | Media (Catalog-adjacent) | Catalog, CMS | Media actions | StorageProvider + MediaService | — | — | | `seo_metadata`, `redirects` | SEO | Storefront | SEO actions | SeoService | — | Polymorphic attachable | | `pages`, `content_sections`, `site_settings` | CMS | Storefront | CMS actions | CmsService | — | — | | `menus`, `menu_items`, `banners` | CMS | Storefront | CMS actions | CmsService | — | Schema when needed | | `stores` | Store | Inventory, Orders, Availability | Store actions | StoreService | — | — | | `store_addresses` | Store | Availability, Shipping | Store actions | StoreService | — | — | | `inventory_locations` | **Store** | Inventory, Fulfillment, Availability | Store actions | LocationService | — | Inventory FK location_id | | `service_zones`, `service_zone_stores`, `serviceability_rules` | Store/Location | Checkout, Shipping, Compliance | Location actions | ServiceabilityService | — | Schema when multi-zone | | `inventory_levels` | **Inventory** | Availability, Search, Checkout | Inventory actions only | InventoryLevelService | inventory.adjusted | UNIQUE(location, variant) | | `inventory_transactions` | Inventory | Admin, Audit | Inventory actions only | InventoryLedgerService | inventory.sale/adjusted | Append-only | | `inventory_reservations` | Inventory | Orders, Checkout | Inventory actions only | ReservationService | inventory.reservation.* | — | | `incoming_inventory` | Inventory | Checkout, Admin | Inventory actions | IncomingInventoryService | incoming.* | — | | `incoming_inventory_reservations` | Inventory | Orders, Checkout | Inventory actions | PreArrivalReservationService | incoming.reservation.* | — | | `price_lists`, `prices` | Pricing | Checkout, Search, Cart | Pricing actions | PricingEngine | price.updated | Variant + optional store | | `promotions`, `promotion_rules`, `promotion_actions`, `coupons`, `promotion_redemptions` | Promotions | Checkout | Promotion actions | PromotionEngine | — | Schema when needed | | `carts`, `cart_items` | Cart | Checkout | Cart actions | CartService | — | customer_id nullable (guest-ready) | | `orders`, `order_items`, `order_addresses`, `order_status_history` | **Orders** | Admin, Fulfillment, Payments (read) | Order actions only | OrderService | order.* | Snapshots; RESTRICT deletes | | `payments`, `payment_attempts`, `payment_transactions`, `refunds`, `payment_webhook_events` | **Payments** | Orders (read status) | Payment actions only | PaymentService / PaymentGateway | payment.* | order_id FK | | `fulfillments`, `fulfillment_items`, `fulfillment_status_history` | **Fulfillment** | Orders, Shipping, Admin | Fulfillment actions | FulfillmentService | fulfillment.* | Typed FKs location/vendor | | `shipments`, `shipment_items`, `shipment_tracking_events` | **Fulfillment** | Shipping adapters (write via Fulfillment), Notifications | Fulfillment + Shipping orchestration | ShipmentService | shipment.* | Shipping never owns rows | | `shipping_methods`, `shipping_zones`, `shipping_rates`, `shipping_providers` | **Shipping** | Checkout, Fulfillment | Shipping admin actions | ShippingRateProvider / ShippingProvider | — | Config only | | `vendors`, `vendor_accounts`, `vendor_products`, `vendor_offers`, `vendor_sync_*` | Vendors | Search, Fulfillment, Pricing (cost) | Vendor sync actions | VendorConnector | vendor.sync.* | Never merge into inventory_levels | | `compliance_rules`, `jurisdictions` | Compliance | Checkout, Fulfillment | Compliance admin | ComplianceEngine | — | Schema when rules exist | | `integrations`, `integration_credentials`, `integration_settings`, `integration_logs` | Integration | Adapters | Integration admin | IntegrationRegistry | — | Encrypted credentials | | `webhook_endpoints`, `webhook_subscriptions`, `webhook_deliveries` | Integration | Outbox consumers | Webhook delivery jobs | WebhookDispatcher | — | External IDs only | | `audit_logs` | Audit | Admin | AuditWriter (append-only) | AuditService | — | Soft polymorphic refs, no hard FK | | `outbox_messages` | Platform | Outbox worker | OutboxPublisher | OutboxPublisher | (payloads) | Written in same TX as domain | | `idempotency_keys` | Platform | Middleware | Idempotency middleware | IdempotencyStore | — | — | | `search_index_state` | Search | Admin | SearchIndexJob | SearchProvider | — | Projection only | --- ## Checkout Orchestrator (Non-Owner) Checkout **does not own** tables. It invokes, within a defined transaction boundary (see CONSISTENCY_MATRIX / system overview): 1. PricingEngine.quote 2. PromotionEngine.apply (optional) 3. ComplianceEngine.evaluate 4. Inventory.ReservationService.reserve (sync, must succeed) 5. Orders.OrderService.createPendingPayment (sync) 6. OutboxPublisher.write (`order.placed`) 7. After commit: Payments initiate (external network **outside** DB TX) --- ## Module Dependency Rules Allowed: Checkout → module contracts; Consumers → events. Forbidden: - Checkout/Orders directly UPDATE `inventory_levels` - Fulfillment directly UPDATE `payments` - Shipping SDK usage inside Orders - Inventory UPDATE `inventory_locations` rows (except reading FK) ======== FILE: PRD_TRACEABILITY_MATRIX.md ======== # PRD Traceability Matrix — Architecture v1.2 **Baseline sources:** `docs/first-prompt.txt` (architecture PRD / first-principles requirements), Architecture v1.1 docs, `docs/architecture-review.txt`. **Note:** No separate client marketing PRD file exists in the repository. This matrix traces the approved architecture prompt + review instructions. Where Phase-1 feature inclusion is not explicitly mandated, status reflects architectural coverage vs open business confirmation. **Status legend:** COVERED | PARTIALLY COVERED | NOT COVERED | CONFLICTING --- ## A. Catalog & Merchandising | PRD Requirement | Phase | Priority | Owning Module | Supporting | Tables | API/Contracts | Events | Provider | Arch Doc | Impl Phase | Status | |---|---|---|---|---|---|---|---|---|---|---|---| | Products ≠ inventory/price/vendor product | P1 | Critical | Catalog | Pricing, Inventory, Vendors | products, product_variants | Catalog DTOs | product.* | — | 04, ADR-008 | P1 | COVERED | | Product variants as sellable unit | P1 | Critical | Catalog | Cart, Pricing, Inventory, Orders | product_variants | CartItem→Variant | — | — | 04, O rule | P1 | COVERED | | Categories | P1 | High | Catalog | Search, SEO | categories, product_categories | Catalog list/filter | product.updated | — | 04 | P1 | COVERED | | Brands | P1 | High | Catalog | Search, SEO | brands | Catalog filter | product.updated | — | 04 | P1 | COVERED | | Collections | Foundation | Medium | Catalog | CMS, Promotions | collections, collection_products | Admin CMS | — | — | 04, 17 | Schema when needed | PARTIALLY COVERED | | Configurable + dedicated attributes | P1 | High | Catalog | Search | product_variants cols + product_attributes* | Filter APIs | product.updated | — | 04 (v1.2 freeze) | P1 | COVERED | | Media | P1 | High | Media/Catalog | CMS | media_assets, catalog_media | Media upload | — | StorageProvider | 04, 17 | P1 | COVERED | | Advanced filters (type, brand, region, country, varietal, price, promo) | P1 | High | Search + Catalog | Pricing, Promotions | Typesense projection | GET /search | product.*, price.*, inventory.* | Typesense | 16, SEARCH_MATRIX | P1 | PARTIALLY COVERED | | Sorting | P1 | High | Search | Catalog | Typesense | GET /search | — | Typesense | 16 | P1 | COVERED | | Bestseller/trending capability | Future | Low | Search | Orders analytics | — | Search sort modes | — | — | 16 | Deferred | NOT COVERED | | Slug/SEO URLs for products/categories/brands | P1 | High | Catalog + SEO | CMS | slug cols, seo_metadata, redirects | Storefront routes | — | — | 17, AG | P1 | COVERED | ## B. Inventory & Availability | PRD Requirement | Phase | Priority | Owning Module | Supporting | Tables | API/Contracts | Events | Provider | Arch Doc | Impl Phase | Status | |---|---|---|---|---|---|---|---|---|---|---|---| | Manual inventory (ledger + levels) | P1 | Critical | Inventory | Store | inventory_levels, inventory_transactions | Admin adjust/receive | inventory.adjusted | — | 05, ADR-003 | P1 | COVERED | | Multi-store inventory foundation | Foundation | Critical | Store + Inventory | Serviceability | stores, inventory_locations, inventory_levels | Availability API | inventory.* | — | 25 | Schema: stores+locations P1 | COVERED | | Location-aware availability | P1 | Critical | Inventory + Location | Compliance, Shipping | inventory_levels, service_zones* | GET /availability | inventory.* | — | 25 | P1 | COVERED | | Nearby store visibility | Foundation | Medium | Location | Inventory | stores, addresses, service_zones | Availability ranking | — | — | 25 (SERVICEABILITY vs RANKING) | Ranking P1 display optional | PARTIALLY COVERED | | Pickup availability by store | Open→P1 if confirmed | High | Fulfillment + Store | Compliance | fulfillments.method | Checkout options | fulfillment.* | — | 22, 07 | Blocks pickup feature if undecided | CONFLICTING→RESOLVED as open w/ design ready | | Pre-arrival inventory model | Foundation / feature open | High | Inventory | Orders | incoming_inventory, incoming_inventory_reservations | Admin + checkout | incoming.* | — | 24 | Schema when selling pre-arrival | PARTIALLY COVERED | | Inventory transfers | Deferred | Low | Inventory | Store | inventory_transfers* | Admin | — | — | 05 | Future | NOT COVERED (by design) | | POS readiness (SYNC_CORRECTION) | Deferred | Medium | Inventory | Integration | inventory_transactions type | Integration write | inventory.adjusted | POS adapter | 05, 10 | Future | PARTIALLY COVERED | | Oversell prevention | P1 | Critical | Inventory | Checkout | inventory_reservations | ReserveAction | inventory.reservation.* | — | 05, 23 | P1 | COVERED | ## C. Vendors & Integrations | PRD Requirement | Phase | Priority | Owning Module | Supporting | Tables | API/Contracts | Events | Provider | Arch Doc | Impl Phase | Status | |---|---|---|---|---|---|---|---|---|---|---|---| | Vendor architecture (not SGProof-centric) | Foundation | Critical | Vendors | Integration | vendors* | VendorConnector + capabilities | vendor.sync.* | VendorConnector | 10, 24, INTEGRATION_CONTRACTS | Contracts P1; schema later | COVERED | | Future SGProof (browser automation) | Deferred integration | High | Vendors | Integration | vendor_* | SGProofConnector | vendor.sync.* | Playwright | 10 | Separate approved phase | COVERED (foundation) | | Future vendor APIs / CSV | Deferred | Medium | Vendors | Integration | vendor_* | Api/Csv connectors | vendor.sync.* | — | 10 | Future | COVERED (foundation) | | Vendor cost ≠ retail | P1 design | Critical | Vendors + Pricing | Checkout | vendor_offers vs prices | PricingEngine | — | — | 24, 06 | P1 | COVERED | | Vendor availability not ledger guarantee | P1 design | Critical | Vendors | Fulfillment | vendor_offers | Capability contracts | — | — | 24 | P1 | COVERED | | Integration registry + encrypted credentials | Foundation | High | Integration | All adapters | integrations* | Integration registry | — | — | 14 | Schema when first adapter | COVERED | | Provider independence | P1 | Critical | Integration | All | — | Contracts | — | ADR-006 | INTEGRATION_CONTRACTS | P1 | COVERED | ## D. Pricing, Promotions, Tax | PRD Requirement | Phase | Priority | Owning Module | Supporting | Tables | API/Contracts | Events | Provider | Arch Doc | Impl Phase | Status | |---|---|---|---|---|---|---|---|---|---|---|---| | Independent pricing engine | P1 | Critical | Pricing | Catalog, Store | price_lists, prices | PricingEngine | price.updated | — | 06, ADR-004 | P1 | COVERED | | Coupons / promotions extensible | Foundation | Medium | Promotions | Checkout | promotions*, coupons* | PromotionEngine | — | — | 06 | Schema when first promo | PARTIALLY COVERED | | Free-shipping eligibility | Foundation | Medium | Promotions + Shipping | Checkout | promotion_actions | Checkout quote | — | — | 06, 09 | With promotions | PARTIALLY COVERED | | Tax | P1 | High | Tax (via Pricing/Checkout) | Compliance | order tax snapshot fields | TaxProvider | — | TaxProvider | 06, INTEGRATION | P1 | PARTIALLY COVERED | | Wholesale roadmap | Deferred | Low | Pricing | — | — | — | — | — | 06 | Future | NOT COVERED (by design) | ## E. Cart, Checkout, Orders, Payments | PRD Requirement | Phase | Priority | Owning Module | Supporting | Tables | API/Contracts | Events | Provider | Arch Doc | Impl Phase | Status | |---|---|---|---|---|---|---|---|---|---|---|---| | Cart | P1 | Critical | Cart | Catalog | carts, cart_items | Cart APIs | — | — | Cart docs | P1 | COVERED | | Checkout orchestration (not god service) | P1 | Critical | Checkout (app) | Orders, Inventory, Pricing, Compliance, Payment | — | PlaceOrderCommand | order.placed | — | CONSISTENCY_MATRIX | P1 | COVERED | | Guest checkout | Open | High | Cart/Identity | Orders | carts.customer_id nullable | Checkout | — | — | 21 | Schema supports guest | NOT COVERED (open) | | Orders with snapshots | P1 | Critical | Orders | Catalog, Pricing | orders, order_items, order_addresses, order_status_history | Order APIs | order.* | — | 07, 22 | P1 | COVERED | | Payment ≠ order | P1 | Critical | Payments | Orders | payments, attempts, transactions, refunds | PaymentGateway | payment.* | PaymentGateway | 08, ADR-008 | P1 | COVERED | | Payment retry without auto-cancel | P1 | Critical | Payments + Orders | Inventory | payment_attempts | Retry payment | payment.failed | — | 08, 22 | P1 | COVERED (v1.2 fix) | | Idempotent payments/refunds/orders | P1 | Critical | Shared | All write modules | idempotency_keys | Idempotency-Key | — | — | 23 | P1 | COVERED | | Auth: customer / admin / integration | P1 | Critical | Identity | All | users, customers, roles* | Auth contracts | customer.registered | — | 15 | P1 | COVERED | | Admin RBAC | P1 | Critical | Identity | Audit | roles, permissions | Admin APIs | — | — | 15 | P1 | COVERED | ## F. Fulfillment, Shipping, Pickup | PRD Requirement | Phase | Priority | Owning Module | Supporting | Tables | API/Contracts | Events | Provider | Arch Doc | Impl Phase | Status | |---|---|---|---|---|---|---|---|---|---|---|---| | Partial / multi-source fulfillment | Foundation→P1 capable | High | Fulfillment | Inventory, Vendors | fulfillments, fulfillment_items | Fulfillment APIs | fulfillment.* | — | 07, 22 | P1 single-source OK | COVERED | | Multiple shipments | Foundation | High | Fulfillment + Shipping | — | shipments, shipment_items, tracking | ShippingProvider | shipment.* | ShippingProvider | 07, 09 | P1 | COVERED | | Future delivery partners | Foundation | Critical | Shipping | Integration | shipping_methods, providers* | Capability contracts | — | Shipping* | 09, ADR-006 | Contracts P1 | COVERED | | Rapid shipping roadmap | Deferred | Low | Shipping | — | — | — | — | — | 09 | Future | NOT COVERED (by design) | | Pickup lifecycle | Open / design ready | High | Fulfillment | Store | fulfillments (method=PICKUP) | Pickup complete API | fulfillment.picked_up | — | 22 | Feature gated | PARTIALLY COVERED | | Order ≠ shipment | P1 | Critical | Orders / Fulfillment / Shipping | — | separate aggregates | — | — | — | ADR-008 | P1 | COVERED | ## G. Compliance, CMS, SEO, Search, Audit, Notifications | PRD Requirement | Phase | Priority | Owning Module | Supporting | Tables | API/Contracts | Events | Provider | Arch Doc | Impl Phase | Status | |---|---|---|---|---|---|---|---|---|---|---|---| | Extensible compliance engine | Foundation | Critical | Compliance | Checkout, Fulfillment | compliance_rules, jurisdictions | ComplianceDecision | — | AgeVerification* | 11 | Schema when rules exist | COVERED | | CMS homepage configurable | P1 | High | CMS | Media | pages, content_sections, site_settings | Admin CMS | — | — | 17 | P1 | COVERED | | SEO metadata + redirects | P1 | High | SEO | Catalog, CMS | seo_metadata, redirects | SEO APIs | — | — | 17 | P1 | COVERED | | Search as projection (Typesense) | P1 | Critical | Search | Catalog, Pricing, Inventory | search_index_state | SearchProvider | * → index | Typesense | 16, ADR-007 | P1 | COVERED | | Search never transactional SoT | P1 | Critical | Search | Checkout | — | — | — | — | 16 | P1 | COVERED | | Audit logging | P1 | Critical | Audit | All admin | audit_logs | — | — | — | 18 | P1 | COVERED | | Notifications | Foundation | Medium | Notification | Orders, Payments | — | NotificationProvider | order.*, payment.* | Email/SMS | INTEGRATION | Async P1 minimal | PARTIALLY COVERED | | API versioning / OpenAPI | P1 | Critical | Shared | All | — | /api/v1 | — | — | ADR-009 | P1 | COVERED | | Transactional outbox | P1 | Critical | Shared | All | outbox_messages | — | all domain events | — | 13, ADR-005 | P1 | COVERED | | Laravel 13 baseline | P1 | Critical | Platform | — | — | — | — | — | README, 20 | P1 | COVERED | | AI/MCP readiness (not implement) | Future | Low | API | — | — | Stable APIs | webhooks | — | first-prompt | Future | COVERED (design) | --- ## Conflicts Found vs Architecture v1.1 | Conflict | Severity | v1.2 Resolution | |---|---|---| | `inventory_locations` dual ownership (Store vs Inventory) | High | Store owns; Inventory references | | SALE commit on payment vs fulfillment allocation | Critical | Single commit at fulfillment ALLOCATED | | Payment FAILED auto-cancels order | High | Failed attempt ≠ cancel; expiry/abandon cancels | | Order COMPLETED when all fulfillments terminal (includes CANCELLED) | High | Quantity/outcome-based completion | | 28 FOUNDATION tables migrated speculatively | High | Migrate only REQUIRED; foundation = design/contracts | | Collections/attributes FOUNDATION while filters need attributes | Medium | Dedicated variant filter columns REQUIRED; EAV schema when needed | | Pre-arrival FOUNDATION while PRD models pre-arrival deeply | Medium | Design complete; migrate when client confirms selling | | Pickup open but PRD requires pickup availability architecture | Medium | Lifecycle designed; feature gated on client confirmation | | EVENT_CATALOG exposes internal BIGINT IDs | Medium | Event ID strategy split (internal vs external) | | Fulfillment owns shipments while Shipping owns shipping | Medium | Fulfillment owns shipment aggregates; Shipping owns provider orchestration | | Laravel version drift risk | Low | Lock Laravel 13 in architecture docs | --- ## Coverage Summary | Status | Count (approx) | |---|---| | COVERED | Majority of critical P1 commerce path | | PARTIALLY COVERED | Collections, promotions, nearby ranking, pickup, pre-arrival selling, tax provider, notifications | | NOT COVERED (deferred by design) | Bestseller analytics, transfers, wholesale, rapid shipping, SGProof implementation | | CONFLICTING | Resolved in v1.2 (see above) | ======== FILE: README.md ======== ## Architecture v1.3 — IMPLEMENTATION FREEZE **Status:** Ready for migration generation (see ARCHITECTURE_V1_3_FREEZE.md) ### Version History | Version | Notes | |---|---| | v1.0 | Initial architecture | | v1.1 | Foundational decisions + ADRs | | v1.2 | Consistency pass + matrices | | **v1.3** | **Implementation freeze — schema spec, indexes, migration plan, race resolution** | ### Start Here 1. `ARCHITECTURE_V1_3_FREEZE.md` — freeze report + READY verdict 2. `PHASE1_SCHEMA_SPEC.md` — authoritative column spec (48 tables) 3. `DATABASE_INDEX_REGISTRY.md` — indexes 4. `MIGRATION_IMPLEMENTATION_PLAN.md` — batch order 5. `TABLE_REGISTRY.md` — classification 6. `21-open-decisions.md` — business gates ### New v1.3 Artifacts - ARCHITECTURE_V1_3_FREEZE.md - PHASE1_SCHEMA_SPEC.md - DATABASE_INDEX_REGISTRY.md - MIGRATION_IMPLEMENTATION_PLAN.md - ADR-012, ADR-013 ### Key v1.3 Resolutions - Payment → Order confirmation async (Payments never mutates Orders) - Reservation expiry guarded by checkout_expires_at + PENDING_PAYMENT - Late payment reconciliation (reReserve or refund) - Order terminal states: COMPLETED / CANCELLED only (qty-based) - fulfillments: no vendor_id in Phase 1 - ALLOCATED atomic SALE commit + compensating ledger rules ``` READY_TO_IMPLEMENT_MIGRATIONS = YES ``` ======== FILE: SEARCH_PROJECTION_MATRIX.md ======== # SEARCH_PROJECTION_MATRIX.md — Architecture v1.2 **Rule:** Typesense availability/pricing fields are **advisory**. Checkout must re-validate MySQL inventory, pricing, serviceability, and compliance. --- ## Indexed Document (products / variants) Primary collection: `products` (one document per **sellable** `product_variant`, denormalized with product/brand/category fields). | Indexed Field | Canonical Source | Module | Refresh Event(s) | Transformation | Staleness Tolerance | |---|---|---|---|---|---| | `id` (ULID) | product_variants.public_id | Catalog | product.created/updated | Direct | 0 (identity) | | `product_id` | products.public_id | Catalog | product.* | Direct | seconds | | `name` | products.name + variant label | Catalog | product.updated | Concat/display | seconds | | `slug` | products.slug / variant slug | Catalog | product.updated | Direct | seconds | | `sku` | product_variants.sku | Catalog | product.updated | Direct | seconds | | `brand` | brands.name | Catalog | product.updated, brand update | Join | seconds | | `brand_id` | brands.public_id | Catalog | product.updated | Direct | seconds | | `category_ids` | categories via pivot | Catalog | product.updated | Array of ULIDs | seconds | | `category_names` | categories.name | Catalog | product.updated | Array | seconds | | `liquor_type` | product_variants.liquor_type | Catalog | product.updated | Dedicated col | seconds | | `country` | product_variants.country_code | Catalog | product.updated | Dedicated col | seconds | | `region` | product_variants.region | Catalog | product.updated | Dedicated col | seconds | | `varietal` | product_variants.varietal | Catalog | product.updated | Dedicated col nullable | seconds | | `vintage` | product_variants.vintage | Catalog | product.updated | Dedicated col nullable | seconds | | `abv` | product_variants.abv | Catalog | product.updated | Decimal→float for facet | seconds | | `bottle_size_ml` | product_variants.bottle_size_ml | Catalog | product.updated | Int | seconds | | `pack_quantity` | product_variants.pack_quantity | Catalog | product.updated | Int | seconds | | `price_minor` | prices.amount_minor (active list) | Pricing | price.updated | Min/active store price | seconds–minutes | | `currency_code` | prices.currency_code | Pricing | price.updated | Direct | seconds | | `on_sale` / promo flag | promotions evaluation snapshot or flag | Promotions | promo change events | Boolean | minutes (advisory) | | `available` | inventory_levels computed OR vendor_offers | Inventory / Vendors | inventory.adjusted, inventory.sale, reservation.* (optional), vendor.sync.* | `any location available>0` OR vendor available & not stale | seconds–minutes (**advisory**) | | `availability_by_store` (optional later) | per-location available | Inventory | inventory.* | Nested map | minutes | | `is_active` | product/variant status & deleted_at | Catalog | product.deleted/updated | Boolean | seconds | | `created_at` | products.created_at | Catalog | product.created | Epoch | — | Configurable EAV attributes (when enabled) map to dynamic facet fields via attribute code → Typesense field registry. --- ## Event → Index Actions | Event | Action | |---|---| | product.created / updated | Upsert variant docs | | product.deleted | Soft-remove / is_active=false | | price.updated | Partial update price fields | | inventory.adjusted / inventory.sale / reservation release impacting availability | Partial update `available` | | vendor.sync.completed | Partial update vendor availability flags | | Full rebuild | Admin `search:rebuild` alias swap | --- ## Failure / Staleness - Index lag is acceptable for browsing. - Checkout ignores Typesense for stock/price truth. - `search_index_state` tracks last successful index per entity. - Typesense outage: storefront may degrade search UX; commerce path continues. ======== FILE: TABLE_REGISTRY.md ======== # TABLE_REGISTRY.md — Architecture v1.3 (Canonical) **All table counts in architecture docs MUST derive from this registry.** ### Classification (v1.2) | Label | Meaning | Migrate now? | |---|---|---| | **REQUIRED NOW** | Needed for Phase-1 MVP path | YES | | **ARCHITECTURAL FOUNDATION** | Designed/contracts now; **no schema until feature approved** | NO (unless Exception) | | **DEFERRED** | No design urgency | NO | **Exception rule:** Migrate a foundation table now only if postponing requires **destructive redesign** of a REQUIRED table. Document each exception below. --- ## Registry | Table | Module Owner | Classification | Phase | Migration Now? | public_id? | Soft Delete? | Primary FKs | Purpose | |---|---|---|---|---|---|---|---|---| | users | Identity | REQUIRED NOW | P1 | YES | YES | NO | — | Admin/staff principals | | customers | Identity | REQUIRED NOW | P1 | YES | YES | NO | — | Storefront principals | | roles | Identity | REQUIRED NOW | P1 | YES | NO | NO | — | RBAC | | permissions | Identity | REQUIRED NOW | P1 | YES | NO | NO | — | RBAC | | role_permissions | Identity | REQUIRED NOW | P1 | YES | NO | NO | role, permission | RBAC pivot | | user_roles | Identity | REQUIRED NOW | P1 | YES | NO | NO | user, role | RBAC pivot | | products | Catalog | REQUIRED NOW | P1 | YES | YES | YES | brand_id? | Merchandising entity | | product_variants | Catalog | REQUIRED NOW | P1 | YES | YES | YES | product_id | Sellable unit | | brands | Catalog | REQUIRED NOW | P1 | YES | YES | YES | — | Brand | | categories | Catalog | REQUIRED NOW | P1 | YES | YES | YES | parent_id? | Taxonomy | | product_categories | Catalog | REQUIRED NOW | P1 | YES | NO | NO | product, category | Pivot | | media_assets | Media | REQUIRED NOW | P1 | YES | YES | YES | — | Stored files | | catalog_media | Media | REQUIRED NOW | P1 | YES | NO | NO | variant/product, media | Product media links | | seo_metadata | SEO | REQUIRED NOW | P1 | YES | NO | NO | attachable polymorphic | SEO | | redirects | SEO | REQUIRED NOW | P1 | YES | NO | NO | — | Slug/URL redirects | | pages | CMS | REQUIRED NOW | P1 | YES | YES | YES | — | CMS pages | | content_sections | CMS | REQUIRED NOW | P1 | YES | NO | NO | page_id | Homepage sections | | site_settings | CMS | REQUIRED NOW | P1 | YES | NO | NO | — | Key/value settings | | stores | Store | REQUIRED NOW | P1 | YES | YES | NO | — | Store entity (supports multi later) | | store_addresses | Store | REQUIRED NOW | P1 | YES | NO | NO | store_id | Store address | | inventory_locations | Store | REQUIRED NOW | P1 | YES | YES | NO | store_id nullable | Stock location identity | | inventory_levels | Inventory | REQUIRED NOW | P1 | YES | NO | NO | location_id, variant_id | on_hand/reserved | | inventory_transactions | Inventory | REQUIRED NOW | P1 | YES | NO | NO | level_id | Physical ledger | | inventory_reservations | Inventory | REQUIRED NOW | P1 | YES | NO | NO | level_id, order_id? | Physical reservations; `checkout_expires_at` for unpaid TTL | | price_lists | Pricing | REQUIRED NOW | P1 | YES | YES | NO | — | Price list | | prices | Pricing | REQUIRED NOW | P1 | YES | NO | NO | list_id, variant_id, store_id? | Retail amounts | | carts | Cart | REQUIRED NOW | P1 | YES | NO | NO | customer_id nullable | Cart header | | cart_items | Cart | REQUIRED NOW | P1 | YES | NO | NO | cart_id, variant_id | Cart lines | | orders | Orders | REQUIRED NOW | P1 | YES | YES | NO | customer_id?, store_id | Order header | | order_items | Orders | REQUIRED NOW | P1 | YES | NO | NO | order_id, variant_id | Snapshot lines | | order_addresses | Orders | REQUIRED NOW | P1 | YES | NO | NO | order_id | Address snapshots | | order_status_history | Orders | REQUIRED NOW | P1 | YES | NO | NO | order_id | Transitions | | payments | Payments | REQUIRED NOW | P1 | YES | YES | NO | order_id | Payment aggregate | | payment_attempts | Payments | REQUIRED NOW | P1 | YES | NO | NO | payment_id | Immutable attempts | | payment_transactions | Payments | REQUIRED NOW | P1 | YES | NO | NO | attempt_id | Append-only txs | | refunds | Payments | REQUIRED NOW | P1 | YES | YES | NO | payment_id | Append-only refunds | | payment_webhook_events | Payments | REQUIRED NOW | P1 | YES | NO | NO | — | Webhook dedup | | fulfillments | Fulfillment | REQUIRED NOW | P1 | YES | YES | NO | order_id, inventory_location_id NOT NULL (P1) | Fulfillment — no vendor_id until vendor migration | | fulfillment_items | Fulfillment | REQUIRED NOW | P1 | YES | NO | NO | fulfillment_id, order_item_id | Allocations | | fulfillment_status_history | Fulfillment | REQUIRED NOW | P1 | YES | NO | NO | fulfillment_id | Transitions | | shipments | Fulfillment | REQUIRED NOW | P1 | YES | YES | NO | fulfillment_id | Shipment (ship method only) | | shipment_items | Fulfillment | REQUIRED NOW | P1 | YES | NO | NO | shipment_id | Shipment lines | | shipment_tracking_events | Fulfillment | REQUIRED NOW | P1 | YES | NO | NO | shipment_id | Tracking | | shipping_methods | Shipping | REQUIRED NOW | P1 | YES | YES | NO | — | Delivery/pickup methods | | audit_logs | Audit | REQUIRED NOW | P1 | YES | NO | NO | soft refs | Append-only audit | | outbox_messages | Platform | REQUIRED NOW | P1 | YES | NO (event_id ULID) | NO | — | Transactional outbox | | idempotency_keys | Platform | REQUIRED NOW | P1 | YES | NO | NO | — | Write idempotency | | search_index_state | Search | REQUIRED NOW | P1 | YES | NO | NO | — | Index lag tracking | | collections | Catalog | ARCHITECTURAL FOUNDATION | Later | NO | YES | YES | — | Merchandising collections | | collection_products | Catalog | ARCHITECTURAL FOUNDATION | Later | NO | NO | NO | collection, product | Pivot | | product_attributes | Catalog | ARCHITECTURAL FOUNDATION | Later | NO | NO | NO | — | EAV schema (beyond dedicated cols) | | product_attribute_values | Catalog | ARCHITECTURAL FOUNDATION | Later | NO | NO | NO | attribute, variant | EAV values | | service_zones | Store | ARCHITECTURAL FOUNDATION | Multi-zone | NO | YES | NO | — | Geography eligibility | | service_zone_stores | Store | ARCHITECTURAL FOUNDATION | Multi-zone | NO | NO | NO | zone, store | Pivot | | serviceability_rules | Store | ARCHITECTURAL FOUNDATION | Multi-zone | NO | NO | NO | zone_id | Rules | | incoming_inventory | Inventory | ARCHITECTURAL FOUNDATION | Pre-arrival | NO* | YES | NO | variant, location, vendor? | Incoming stock | | incoming_inventory_reservations | Inventory | ARCHITECTURAL FOUNDATION | Pre-arrival | NO* | NO | NO | incoming, order | Pre-arrival holds | | promotions | Promotions | ARCHITECTURAL FOUNDATION | Promo launch | NO | YES | NO | — | Promotions | | promotion_rules | Promotions | ARCHITECTURAL FOUNDATION | Promo launch | NO | NO | NO | promotion_id | Rules | | promotion_actions | Promotions | ARCHITECTURAL FOUNDATION | Promo launch | NO | NO | NO | promotion_id | Actions | | coupons | Promotions | ARCHITECTURAL FOUNDATION | Promo launch | NO | YES | NO | promotion_id | Coupon codes | | promotion_redemptions | Promotions | ARCHITECTURAL FOUNDATION | Promo launch | NO | NO | NO | coupon, order | Redemptions | | shipping_zones | Shipping | ARCHITECTURAL FOUNDATION | Rate engine | NO | YES | NO | — | Rate geography | | shipping_rates | Shipping | ARCHITECTURAL FOUNDATION | Rate engine | NO | NO | NO | method, zone | Rates | | shipping_providers | Shipping | ARCHITECTURAL FOUNDATION | Multi-carrier | NO | YES | NO | — | Provider registry | | vendors | Vendors | ARCHITECTURAL FOUNDATION | Vendor phase | NO | YES | NO | — | Vendor registry | | vendor_accounts | Vendors | ARCHITECTURAL FOUNDATION | Vendor phase | NO | YES | NO | vendor_id | Accounts | | vendor_products | Vendors | ARCHITECTURAL FOUNDATION | Vendor phase | NO | NO | NO | account, variant? | Mapping | | vendor_offers | Vendors | ARCHITECTURAL FOUNDATION | Vendor phase | NO | NO | NO | vendor_product, variant | Cost/avail sync | | vendor_sync_runs | Vendors | ARCHITECTURAL FOUNDATION | Vendor phase | NO | NO | NO | account | Sync runs | | vendor_sync_errors | Vendors | ARCHITECTURAL FOUNDATION | Vendor phase | NO | NO | NO | sync_run | Errors | | compliance_rules | Compliance | ARCHITECTURAL FOUNDATION | Compliance | NO | NO | NO | jurisdiction? | Rules | | jurisdictions | Compliance | ARCHITECTURAL FOUNDATION | Compliance | NO | YES | NO | — | Jurisdictions | | integrations | Integration | ARCHITECTURAL FOUNDATION | First adapter | NO | YES | NO | — | Registry | | integration_credentials | Integration | ARCHITECTURAL FOUNDATION | First adapter | NO | NO | NO | integration_id | Encrypted secrets | | integration_settings | Integration | ARCHITECTURAL FOUNDATION | First adapter | NO | NO | NO | integration_id | Config | | integration_logs | Integration | ARCHITECTURAL FOUNDATION | First adapter | NO | NO | NO | integration_id | Logs | | webhook_endpoints | Integration | ARCHITECTURAL FOUNDATION | Outbound webhooks | NO | YES | NO | — | Endpoints | | webhook_subscriptions | Integration | ARCHITECTURAL FOUNDATION | Outbound webhooks | NO | NO | NO | endpoint | Subscriptions | | webhook_deliveries | Integration | ARCHITECTURAL FOUNDATION | Outbound webhooks | NO | NO | NO | subscription | Deliveries | | menus | CMS | ARCHITECTURAL FOUNDATION | Nav CMS | NO | YES | NO | — | Menus | | menu_items | CMS | ARCHITECTURAL FOUNDATION | Nav CMS | NO | NO | NO | menu | Items | | banners | CMS | ARCHITECTURAL FOUNDATION | Promo banners | NO | YES | NO | — | Banners | | tags | Catalog | DEFERRED | — | NO | YES | NO | — | Tags | | product_tags | Catalog | DEFERRED | — | NO | NO | NO | — | Pivot | | inventory_transfers | Inventory | DEFERRED | — | NO | YES | NO | — | Transfers | | inventory_transfer_items | Inventory | DEFERRED | — | NO | NO | NO | — | Transfer lines | | blog_posts | CMS | DEFERRED | — | NO | YES | YES | — | Blog | | blog_categories | CMS | DEFERRED | — | NO | YES | NO | — | Blog cats | | page_versions | CMS | DEFERRED | — | NO | NO | NO | page | Versioning | | theme_settings | CMS | DEFERRED | — | NO | NO | NO | — | Theme | | age_verification_providers | Compliance | DEFERRED | — | NO | YES | NO | — | Age verify registry | | admin_sessions | Identity | DEFERRED | — | NO | NO | NO | user | Extra session store if needed | | customer_addresses | Identity | ARCHITECTURAL FOUNDATION | Account UX | NO | NO | YES | customer_id | Saved addresses (guest-ready via carts) | \* Pre-arrival tables migrate when client confirms pre-arrival selling (feature gate). Contracts designed now. --- ## Exceptions (migrate foundation early) | Table | Exception? | Reason | |---|---|---| | `stores`, `inventory_locations` | Already REQUIRED | Multi-store foundation embedded in P1 FK design without speculative extra tables | | None other | — | No foundation tables require early migration to avoid destructive redesign | --- ## Counts (v1.3 Canonical) | Classification | Count | |---|---| | **REQUIRED NOW (migrate)** | **48** | | **ARCHITECTURAL FOUNDATION (no migrate)** | **36** | | **DEFERRED** | **10** | | **Total designed** | **94** | Note: v1.1 claimed 42/28/6 (~76) with speculative foundation migrations. v1.2 reduces **migrated** surface and adds missing designed tables (`incoming_inventory_reservations`, `redirects`, typed fulfillment FKs, etc.) without migrating them prematurely. ### Initial implementation migration set **Exactly the 48 REQUIRED NOW tables.** Laravel starter `users`/`cache`/`jobs` remain; domain migrations are additive. ======== FILE: decisions/ADR-001-modular-monolith.md ======== # ADR-001: Modular Monolith ## Status Accepted (Architecture v1.1) ## Context The platform must support multi-store inventory, vendor integrations, payments, fulfillment, compliance, and future scale without premature microservice complexity. ## Decision Adopt a **modular monolith** in a single Laravel application with strict domain module boundaries under `app/Modules/`. Modules communicate via contracts, application actions, and domain events (transactional outbox). No microservices in Phase 1. ## Alternatives Considered | Alternative | Why rejected | |---|---| | Microservices from day 1 | Operational overhead; premature for single-store launch | | Flat Laravel structure | No enforceable boundaries; cross-module coupling risk | | Separate services per integration | Violates prompt; adds infrastructure without concrete need | ## Consequences - Faster development and simpler deployment for Phase 1. - Module boundaries must be enforced by convention, code review, and dependency rules. - Extraction to services later requires clear module contracts (already designed). ## Risks - Boundaries erode over time without discipline. - Shared database can tempt cross-module direct table access. ## Future Migration Path - Extract high-load modules (Search projection workers, Vendor sync workers) to standalone processes while keeping canonical DB. - Promote module to service only when scale, team size, or deployment independence justifies the cost. ======== FILE: decisions/ADR-002-identifier-strategy.md ======== # ADR-002: Identifier Strategy ## Status Accepted (Architecture v1.1) ## Context Public APIs must not expose sequential database IDs. Internal relational joins require efficient FK performance. ## Decision - **Internal PK**: `BIGINT UNSIGNED` auto-increment on all tables. - **Public ID**: `CHAR(26)` ULID stored in `public_id` column, unique indexed. - **Foreign keys**: always reference internal `BIGINT` PKs. - **APIs**: expose only `public_id` (ULID) for business resources. ### Tables requiring `public_id` | Module | Tables | |---|---| | Identity | `users`, `customers` | | Catalog | `products`, `product_variants`, `brands`, `categories`, `collections` | | Store | `stores`, `inventory_locations` | | Orders | `orders`, `fulfillments`, `shipments` | | Payments | `payments`, `refunds` | | Vendors | `vendors`, `vendor_accounts` | | Integrations | `integrations` | | CMS | `pages`, `blog_posts` | Internal-only tables (no public API exposure): `inventory_levels`, `inventory_transactions`, `inventory_reservations`, `order_items`, `payment_attempts`, `payment_transactions`, `idempotency_keys`, `outbox_messages`, pivot/junction tables. ### ULID index strategy - Unique index on `public_id` for every table that has one. - ULIDs are generated in application layer (Laravel `Str::ulid()`). - ULID is time-sortable, 26 chars, URL-safe. ## Alternatives Considered | Alternative | Why rejected | |---|---| | UUIDv7 as PK | Larger indexes on all FKs; worse join performance | | ULID as PK everywhere | Same index bloat concern | | Sequential ID in APIs | Guessable; information leakage | ## Consequences - Every API lookup requires `public_id` → internal `id` resolution (cached where hot). - Migration/seeding must generate ULIDs consistently. - Admin and storefront URLs use ULIDs. ## Risks - Dual-identifier lookups add one indexed query per resource fetch. - Developers may accidentally expose internal IDs in API responses. ## Future Migration Path - If distributed ID generation is needed, ULID generation can move to a dedicated service without changing the column strategy. ======== FILE: decisions/ADR-003-inventory-consistency.md ======== # ADR-003: Inventory Consistency — v1.2 Update ## Status Accepted (v1.1), **Amended v1.2** ## Amendment **SALE commit point** is fulfillment **ALLOCATED**, not payment capture. | Moment | reserved | on_hand | Reservation | Ledger | |---|---|---|---|---| | Checkout | +N | — | ACTIVE | none | | Payment captured | — | — | ACTIVE | none | | Fulfillment ALLOCATED | −N | −N | COMMITTED | SALE −N | `inventory_locations` owned by **Store** module; Inventory references them. Adjustments must not create `on_hand < reserved`; discrepancy workflow required. ## Previous v1.1 content Still applies for: computed available, FOR UPDATE, lock ordering, deadlock retry, Redis not primary correctness. ======== FILE: decisions/ADR-004-money-representation.md ======== # ADR-004: Money Representation ## Status Accepted (Architecture v1.1) ## Context Financial data must be exact. FLOAT/DOUBLE cause rounding errors. Multiple money fields exist across pricing, orders, payments, refunds, promotions, and taxes. ## Decision Store all platform money as **integer minor units** (e.g., `$19.99` → `1999` cents). ### Schema convention | Column | Type | Example | |---|---|---| | `amount_minor` | `BIGINT` (signed for adjustments) | `1999` | | `currency_code` | `CHAR(3)` ISO 4217 | `USD` | Every table with money stores `currency_code` alongside amount columns. ### Where DECIMAL is permitted | Context | Type | Reason | |---|---|---| | Provider raw webhook payloads | `DECIMAL(19,4)` in `payment_webhook_events.raw_payload` JSON | Preserve exact provider values for reconciliation | | Vendor cost/wholesale (future) | `DECIMAL(19,4)` | Accounting precision beyond retail display | | Tax rate percentages | `DECIMAL(5,4)` | e.g., `0.0825` for 8.25% | ### Non-2-decimal currencies | Currency | Minor unit | Storage | |---|---|---| | USD, EUR, GBP | 2 decimals | multiply by 100 | | JPY, KRW | 0 decimals | store as-is (1 yen = 1 minor unit) | | BHD, KWD | 3 decimals | multiply by 1000 | Application layer uses a `Money` value object that handles conversion per currency exponent. Phase 1 operates in a single currency (USD assumed; confirm with client). ### Consistency rule All money in orders, order_items, payments, refunds, prices, promotion discounts, and tax lines use the same `amount_minor` + `currency_code` pattern. ## Alternatives Considered | Alternative | Why rejected | |---|---| | DECIMAL everywhere | Slower arithmetic; provider SDK mismatches | | FLOAT/DOUBLE | Rounding errors in financial calculations | | Store as string | No arithmetic; parsing overhead | ## Consequences - All API responses convert minor units to display format at the presentation layer. - Provider adapters must convert between provider formats and internal minor units. - Sum/aggregate queries use integer arithmetic (exact). ## Risks - Developer error converting between display and minor units. - Multi-currency requires exchange rate tables (deferred). ## Future Migration Path - Add `exchange_rates` table and `Money::convert()` when multi-currency is required. - DECIMAL columns for accounting exports can be generated views, not canonical storage. ======== FILE: decisions/ADR-005-transactional-outbox.md ======== # ADR-005: Transactional Outbox — v1.2 Update ## Status Accepted (v1.1), **Amended v1.2** ## Amendment 1. Claim with `FOR UPDATE SKIP LOCKED`. 2. `PROCESSED` means successful **enqueue to Redis** (handoff), not completion of all consumers. 3. Never hold DB locks during external side effects. 4. At-least-once delivery; consumers idempotent by `event_id`. Retry/backoff/stuck recovery from v1.1 remain. ======== FILE: decisions/ADR-006-provider-adapter-architecture.md ======== # ADR-006: Provider Adapter Architecture ## Status Accepted (Architecture v1.1) ## Context The platform integrates with vendors (SGProof), payment gateways (Stripe), shipping carriers (UPS/FedEx), tax providers, age verification, search (Typesense), and storage (S3). External providers must not dictate core database architecture. ## Decision All third-party integrations use **contract interfaces** with **concrete adapter implementations** registered via Laravel service container. ### Contract categories | Contract | Examples | |---|---| | `VendorConnector` | SGProofConnector, ApiVendorConnector, CsvVendorConnector | | `PaymentGateway` | StripeGateway, PayPalGateway | | `ShippingRateProvider` | UpsRateProvider, FedExRateProvider | | `ShippingProvider` | UpsShipmentProvider, LocalDeliveryProvider | | `TaxProvider` | TaxJarProvider, ManualTaxProvider | | `AgeVerificationProvider` | (future) | | `SearchProvider` | TypesenseProvider | | `StorageProvider` | S3StorageProvider | | `NotificationProvider` | SesNotificationProvider, TwilioNotificationProvider | ### Rules 1. Business modules depend on contracts, never concrete SDKs. 2. Provider-specific data stored in adapter-owned staging tables (`vendor_products`, `payment_webhook_events`), never in core domain tables. 3. Credentials encrypted at rest; never logged. 4. Each adapter implements `healthCheck()` for monitoring. 5. Capability interfaces used where providers cannot support all operations. ### Integration registry tables - `integrations` — provider type, status, config - `integration_credentials` — encrypted secrets - `integration_settings` — non-secret config - `integration_logs` — operation audit trail ## Alternatives Considered | Alternative | Why rejected | |---|---| | Direct SDK calls in services | Tight coupling; untestable; provider dictates schema | | Plugin system with dynamic loading | Over-engineered for Phase 1 | ## Consequences - New providers added by implementing contract + registering in container. - Mock adapters enable full test coverage without external dependencies. ## Risks - Contract design may not fit all future providers; may need capability splits. - Adapter maintenance burden grows with provider count. ## Future Migration Path - Extract high-traffic adapters (vendor sync) to standalone worker processes. - Add adapter versioning if provider APIs change frequently. ======== FILE: decisions/ADR-007-search-projection.md ======== # ADR-007: Search Projection (Typesense) ## Status Accepted (Architecture v1.1) ## Context Product search requires fast full-text and faceted queries. MySQL is the canonical source of truth. Typesense is approved as the search engine. ## Decision Typesense is a **read-only projection** of catalog and availability data. It is never authoritative. ### Index flow ``` MySQL state change → domain event written to outbox (same transaction) → outbox worker dispatches SearchIndexJob to Laravel Queue → SearchIndexJob upserts/deletes document in Typesense ``` ### Operations | Operation | Trigger | Action | |---|---|---| | Create/Update | `product.created`, `product.updated`, `inventory.adjusted` | Upsert document | | Delete | `product.deleted` (soft) | Remove or mark inactive in index | | Full rebuild | Admin command or schema version change | Rebuild collection from MySQL | ### Failure handling - Typesense failure does NOT roll back or affect MySQL transactions. - Failed indexing jobs retry via queue (max 5 attempts). - `search_index_state` table tracks last-indexed version per entity. - Admin dashboard shows indexing lag. ### Schema versioning - Collection name includes schema version: `products_v1`, `products_v2`. - Rebuild creates new collection, populates it, then swaps alias. - Alias swap (`products` → `products_v2`) enables zero-downtime rebuild. - Old collection deleted after successful swap. ### Phase 1 scope - Architectural strategy only. Exact field/facet definitions deferred until Catalog schema is approved. - Index: product name, variant SKU, brand, category, price range, availability flag. - Facets: category, brand, alcohol type (when catalog attributes are finalized). ## Alternatives Considered | Alternative | Why rejected | |---|---| | MySQL full-text search | Poor faceting; doesn't scale for complex liquor attributes | | Elasticsearch | Not approved infrastructure | | Typesense as source of truth | Violates canonical data principle | ## Consequences - Search results may be briefly stale (eventual consistency, typically < 5 seconds). - Rebuild capability is mandatory for disaster recovery. ## Risks - Schema changes require rebuild or migration strategy. - Availability in search index may lag behind real-time inventory. ## Future Migration Path - Location-aware availability in search index (computed projection). - Separate collections per store if multi-store search isolation is needed. ======== FILE: decisions/ADR-008-order-payment-fulfillment-separation.md ======== # ADR-008: Order/Payment/Fulfillment Separation — v1.2 Update ## Status Accepted (v1.1), **Amended v1.2** ## Amendments 1. Payment attempt failure does not auto-cancel order. 2. Order COMPLETED is quantity/outcome based, not “all fulfillments terminal”. 3. Pickup lives on fulfillment (READY_FOR_PICKUP / PICKED_UP); no fake shipment. 4. Fulfillment owns shipment rows; Shipping owns provider orchestration. 5. Inventory SALE commits at fulfillment ALLOCATED. 6. Fulfillment sources use typed FKs (`inventory_location_id` / `vendor_id`). ======== FILE: decisions/ADR-009-api-versioning-and-contracts.md ======== # ADR-009: API Versioning and Contracts ## Status Accepted (Architecture v1.1) ## Context The platform serves a customer storefront (Next.js), Super Admin (Next.js), and future automation/AI agents. APIs must be stable, versioned, and contract-documented from day one. ## Decision ### Versioning - All APIs under `/api/v1/` from launch. - Breaking changes require `/api/v2/` with deprecation period for v1. - Version in URL path, not headers. ### Response contract - Never expose Eloquent models directly. - Use explicit API Resources/DTOs. - Consistent envelope: `{ data, meta, request_id, correlation_id }`. - Consistent error envelope: `{ error: { code, message, details }, request_id }`. ### Error categories (stable codes) `VALIDATION_ERROR`, `AUTHENTICATION_REQUIRED`, `FORBIDDEN`, `RESOURCE_NOT_FOUND`, `CONFLICT`, `INVENTORY_UNAVAILABLE`, `INVALID_STATE_TRANSITION`, `IDEMPOTENCY_CONFLICT`, `RATE_LIMITED`, `INTEGRATION_FAILURE`, `INTERNAL_ERROR`. ### Idempotency Critical write endpoints require `Idempotency-Key` header. Scope: actor + route + request fingerprint. ### OpenAPI - OpenAPI 3.x spec generated and stored in-repo as first-class artifact. - Contract tests validate API responses against spec. ### Public identifiers APIs expose ULID `public_id` only. Internal BIGINT PKs never appear in responses. ## Alternatives Considered | Alternative | Why rejected | |---|---| | Header-based versioning | Harder to route; less visible | | GraphQL | Not approved; adds complexity | | No versioning from launch | Breaking changes become painful | ## Consequences - Every endpoint change requires spec update. - DTO layer adds boilerplate but ensures stability. ## Risks - Spec drift if not enforced in CI. - Over-versioning if v1 changes are too frequent. ## Future Migration Path - Automated OpenAPI generation from route definitions + DTO annotations. - MCP/AI agent layer wraps existing v1 APIs without rewriting business logic. ======== FILE: decisions/ADR-010-checkout-transaction-strategy.md ======== # ADR-010: Checkout Application Transaction Strategy ## Status Accepted (Architecture v1.2) ## Context Strict module ownership forbids Checkout mutating all tables, yet order placement requires atomic reserve + order create. ## Decision Checkout is an orchestrator that opens an outer MySQL transaction and invokes module actions (ReservationService, OrderService, OutboxPublisher) on the same connection. Each action enforces its module invariants. External provider calls occur only after COMMIT. ## Alternatives | Alt | Rejected because | |---|---| | Eventual reserve after order | Oversell / unpaid order without stock | | Checkout updates all tables | Breaks ownership | | Distributed 2PC | Over-engineered | ## Consequences Clear sync vs async boundary; testable module actions; no network in DB TX. ======== FILE: decisions/ADR-011-phase1-schema-minimalism.md ======== # ADR-011: Phase-1 Schema Minimalism ## Status Accepted (Architecture v1.2) ## Context v1.1 planned migrating ~28 foundation tables “just in case”. ## Decision Migrate only **REQUIRED NOW** tables (TABLE_REGISTRY). Architectural foundation remains design/contracts until feature approval. Exception only if postponing causes destructive redesign of a required table (none currently beyond stores/locations already required). ## Consequences Smaller initial schema; feature flags gate later migrations; less speculative complexity. ======== FILE: decisions/ADR-012-payment-order-async-confirmation.md ======== # ADR-012: Payment Capture → Order Confirmation (Async) ## Status Accepted (Architecture v1.3) ## Context Payments and Orders are separate aggregates. v1.2 ambiguously implied payment webhook could synchronously confirm orders. ## Decision Payment webhook handler **only** mutates Payments aggregate and writes `payment.captured` to outbox. ``` BEGIN; dedupe webhook (provider_code + provider_event_id) lock payment FOR UPDATE insert payment_transaction (CAPTURE) update payment.status = CAPTURED insert outbox payment.captured COMMIT; ``` **Payments MUST NOT UPDATE orders.** Consumer `ConfirmOrderOnPaymentCaptured`: - Idempotent: if order.status != PENDING_PAYMENT → no-op (already CONFIRMED or terminal) - Else: transition order → CONFIRMED - Call `Inventory.clearCheckoutExpiry(order_id)` to set reservation.checkout_expires_at = NULL - Write outbox `order.confirmed` Consumer `CreateFulfillmentOnOrderConfirmed`: - Idempotent: if fulfillment exists for order in non-terminal state → no-op - Else: create fulfillment PENDING + items - Write outbox `fulfillment.created` Duplicate `payment.captured` delivery: ConfirmOrder no-ops if already CONFIRMED. ## Consequences Eventual consistency between payment and order (seconds). Acceptable; consumers must be idempotent. ## Risks Consumer lag delays fulfillment start. Mitigate with monitoring on consumer lag. ======== FILE: decisions/ADR-013-reservation-expiry-payment-race.md ======== # ADR-013: Reservation Expiry vs Payment Capture Race ## Status Accepted (Architecture v1.3) ## Context ACTIVE reservations persist after payment until fulfillment ALLOCATED. Naive `expires_at <= NOW()` expiry would release paid orders' stock. ## Decision ### checkout_expires_at column - Set at reservation creation (checkout TTL, configurable) - Cleared by `Inventory.clearCheckoutExpiry(order_id)` when ConfirmOrder runs after payment.captured - When NULL: reservation is **not eligible** for checkout expiry worker ### Expiry worker guard (ALL required) ```sql UPDATE inventory_reservations r JOIN orders o ON o.id = r.order_id JOIN inventory_levels l ON l.id = r.inventory_level_id SET r.status = 'EXPIRED', r.released_at = NOW(), l.reserved = l.reserved - r.quantity WHERE r.status = 'ACTIVE' AND r.checkout_expires_at IS NOT NULL AND r.checkout_expires_at <= NOW() AND o.status = 'PENDING_PAYMENT'; ``` Lock order: reservations (asc id) → levels → orders. ### Race scenarios | Scenario | Outcome | |---|---| | A: Expiry wins before payment | Reservation EXPIRED; order may cancel; payment attempt can still fail or succeed without confirm | | B: Payment wins before expiry | ConfirmOrder clears checkout_expires_at; expiry skips | | C: Provider success, webhook after TTL | ConfirmOrder runs reReserveForPaidOrder; success→confirm; fail→refund+order CANCELLED | | D: CAPTURED, ConfirmOrder pending | checkout_expires_at cleared once confirm runs; expiry skipped | | E: Concurrent expiry vs webhook | FOR UPDATE on payment/reservation/order; first commit wins; second conditional no-op | ### Late payment after release Never auto-confirm without stock. Re-reserve atomically or initiate refund workflow. Emit audit + alert. ## Consequences Explicit column and join guards; no ambiguity on ACTIVE meaning. ## Risks Re-reserve failure requires ops/refund path — must be tested (matrix #31–35).