content:
   600→**Indexes:**
   601→
   602→- `idx_checkouts_store_id` on (`store_id`) - FK lookup
   603→- `idx_checkouts_cart_id` on (`cart_id`) - Finding checkout for a cart
   604→- `idx_checkouts_customer_id` on (`customer_id`) - Listing checkouts per customer
   605→- `idx_checkouts_status` on (`store_id`, `status`) - Filtering checkouts by status
   606→- `idx_checkouts_expires_at` on (`expires_at`) - Cleanup of expired checkouts
   607→
   608→**Notes:** `totals_json` contains a snapshot: `{"subtotal": 5000, "discount": 500, "shipping": 799, "tax": 430, "total": 5729}`. `tax_provider_snapshot_json` stores the full tax calculation response for audit purposes.
   609→
   610→---
   611→
   612→### shipping_zones
   613→
   614→Geographic shipping zones for a store.
   615→
   616→| Column | Type | Nullable | Default | Constraints | Notes |
   617→|---|---|---|---|---|---|
   618→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   619→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
   620→| name | TEXT | No | - | - | Zone display name |
   621→| countries_json | TEXT | No | '[]' | - | JSON array of ISO 3166-1 alpha-2 codes, e.g. ["US","CA"] |
   622→| regions_json | TEXT | No | '[]' | - | JSON array of region/state codes, e.g. ["US-NY","US-CA"] |
   623→
   624→**Indexes:**
   625→
   626→- `idx_shipping_zones_store_id` on (`store_id`) - FK lookup, listing zones per store
   627→
   628→**Notes:** `countries_json` is an array of ISO 3166-1 alpha-2 codes. `regions_json` is an array of region/state codes.
   629→
   630→---
   631→
   632→### shipping_rates
   633→
   634→Shipping rate options within a zone.
   635→
   636→| Column | Type | Nullable | Default | Constraints | Notes |
   637→|---|---|---|---|---|---|
   638→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   639→| zone_id | INTEGER | No | - | FK -> shipping_zones(id) ON DELETE CASCADE | Parent zone |
   640→| name | TEXT | No | - | - | Rate display name |
   641→| type | TEXT | No | 'flat' | CHECK IN ('flat', 'weight', 'price', 'carrier') | Calculation method |
   642→| config_json | TEXT | No | '{}' | - | Type-dependent configuration (see notes) |
   643→| is_active | INTEGER | No | 1 | - | Boolean: active flag |
   644→
   645→**Indexes:**
   646→
   647→- `idx_shipping_rates_zone_id` on (`zone_id`) - FK lookup, listing rates per zone
   648→- `idx_shipping_rates_zone_active` on (`zone_id`, `is_active`) - Finding active rates per zone
   649→
   650→**Notes:** `config_json` structure depends on `type`:
   651→- Flat: `{"amount": 799}`
   652→- Weight: `{"ranges": [{"min_g": 0, "max_g": 500, "amount": 499}]}`
   653→- Price: `{"ranges": [{"min_amount": 0, "max_amount": 5000, "amount": 799}]}`
   654→- Carrier: `{"carrier": "ups", "service": "ground"}`
   655→
   656→---
   657→
   658→### tax_settings
   659→
   660→Per-store tax configuration.
   661→
   662→| Column | Type | Nullable | Default | Constraints | Notes |
   663→|---|---|---|---|---|---|
   664→| store_id | INTEGER | No | - | PRIMARY KEY, FK -> stores(id) ON DELETE CASCADE | One-to-one with stores |
   665→| mode | TEXT | No | 'manual' | CHECK IN ('manual', 'provider') | Tax calculation mode |
   666→| provider | TEXT | No | 'none' | CHECK IN ('stripe_tax', 'none') | External tax provider |
   667→| prices_include_tax | INTEGER | No | 0 | - | Boolean: tax-inclusive pricing |
   668→| config_json | TEXT | No | '{}' | - | Provider-specific config |
   669→
   670→---
   671→
   672→### discounts
   673→
   674→Discount codes and automatic discounts.
   675→
   676→| Column | Type | Nullable | Default | Constraints | Notes |
   677→|---|---|---|---|---|---|
   678→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   679→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
   680→| type | TEXT | No | 'code' | CHECK IN ('code', 'automatic') | Activation method |
   681→| code | TEXT | Yes | NULL | UNIQUE per (store_id, code) | Discount code (null for automatic) |
   682→| value_type | TEXT | No | - | CHECK IN ('fixed', 'percent', 'free_shipping') | Discount calculation type |
   683→| value_amount | INTEGER | No | 0 | - | See notes for interpretation |
   684→| starts_at | TEXT | No | - | - | ISO-8601 start timestamp |
   685→| ends_at | TEXT | Yes | NULL | - | ISO-8601 end timestamp; null = no expiry |
   686→| usage_limit | INTEGER | Yes | NULL | - | Max redemptions; null = unlimited |
   687→| usage_count | INTEGER | No | 0 | - | Current redemption count |
   688→| rules_json | TEXT | No | '{}' | - | Eligibility rules (min purchase, products, etc.) |
   689→| status | TEXT | No | 'active' | CHECK IN ('draft', 'active', 'expired', 'disabled') | |
   690→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   691→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   692→
   693→**Indexes:**
   694→
   695→- `idx_discounts_store_code` UNIQUE on (`store_id`, `code`) - Unique discount code per store
   696→- `idx_discounts_store_id` on (`store_id`) - FK lookup
   697→- `idx_discounts_store_status` on (`store_id`, `status`) - Filtering discounts by status
   698→- `idx_discounts_store_type` on (`store_id`, `type`) - Filtering by code vs automatic
   699→
   700→**Notes:** `value_amount` interpretation depends on `value_type`. For `percent`, it is a whole number (e.g. 15 = 15%). For `fixed`, it is in minor units (cents). For `free_shipping`, it is ignored. `rules_json` may contain minimum purchase amounts, applicable product/collection IDs, customer eligibility, etc.
   701→
   702→---
   703→
   704→## Epic 6 - Payments, Orders, Fulfillment, and Customers
   705→
   706→### customers
   707→
   708→Storefront customer accounts, scoped to a store.
   709→
   710→| Column | Type | Nullable | Default | Constraints | Notes |
   711→|---|---|---|---|---|---|
   712→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   713→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
   714→| email | TEXT | No | - | UNIQUE per (store_id, email) | Customer email |
   715→| password_hash | TEXT | Yes | NULL | - | Nullable for guest checkout customers |
   716→| name | TEXT | Yes | NULL | - | Display name |
   717→| marketing_opt_in | INTEGER | No | 0 | - | Boolean: marketing consent |
   718→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   719→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   720→
   721→**Indexes:**
   722→
   723→- `idx_customers_store_email` UNIQUE on (`store_id`, `email`) - Unique email per store
   724→- `idx_customers_store_id` on (`store_id`) - FK lookup, listing customers per store
   725→
   726→**Notes:** `password_hash` is nullable to allow guest checkout customers who may later claim their account. Email is unique per store, not globally.
   727→
   728→---
   729→
   730→### customer_addresses
   731→
   732→Saved addresses for a customer.
   733→
   734→| Column | Type | Nullable | Default | Constraints | Notes |
   735→|---|---|---|---|---|---|
   736→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   737→| customer_id | INTEGER | No | - | FK -> customers(id) ON DELETE CASCADE | Owning customer |
   738→| label | TEXT | Yes | NULL | - | User label, e.g. "Home", "Work" |
   739→| address_json | TEXT | No | '{}' | - | JSON address object (see notes) |
   740→| is_default | INTEGER | No | 0 | - | Boolean: default address flag |
   741→
   742→**Indexes:**
   743→
   744→- `idx_customer_addresses_customer_id` on (`customer_id`) - FK lookup, listing addresses per customer
   745→- `idx_customer_addresses_default` on (`customer_id`, `is_default`) - Quick lookup of default address
   746→
   747→**Notes:** `address_json` structure: `{"first_name": "", "last_name": "", "company": "", "address1": "", "address2": "", "city": "", "province": "", "province_code": "", "country": "", "country_code": "", "zip": "", "phone": ""}`.
   748→
   749→---
   750→
   751→### orders
   752→
   753→Placed orders with financial and fulfillment tracking.
   754→
   755→| Column | Type | Nullable | Default | Constraints | Notes |
   756→|---|---|---|---|---|---|
   757→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   758→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
   759→| customer_id | INTEGER | Yes | NULL | FK -> customers(id) ON DELETE SET NULL | Nullable for guest orders |
   760→| order_number | TEXT | No | - | UNIQUE per (store_id, order_number) | Human-readable order number |
   761→| payment_method | TEXT | No | - | CHECK IN ('credit_card', 'paypal', 'bank_transfer') | Payment method used for this order |
   762→| status | TEXT | No | 'pending' | CHECK IN ('pending', 'paid', 'fulfilled', 'cancelled', 'refunded') | Overall order status |
   763→| financial_status | TEXT | No | 'pending' | CHECK IN ('pending', 'authorized', 'paid', 'partially_refunded', 'refunded', 'voided') | Payment status |
   764→| fulfillment_status | TEXT | No | 'unfulfilled' | CHECK IN ('unfulfilled', 'partial', 'fulfilled') | Shipping status |
   765→| currency | TEXT | No | 'USD' | - | ISO 4217 currency code |
   766→| subtotal_amount | INTEGER | No | 0 | - | Sum of line items in minor units |
   767→| discount_amount | INTEGER | No | 0 | - | Total discount in minor units |
   768→| shipping_amount | INTEGER | No | 0 | - | Shipping cost in minor units |
   769→| tax_amount | INTEGER | No | 0 | - | Total tax in minor units |
   770→| total_amount | INTEGER | No | 0 | - | Grand total in minor units |
   771→| email | TEXT | Yes | NULL | - | Contact email at time of order |
   772→| billing_address_json | TEXT | Yes | NULL | - | JSON address snapshot |
   773→| shipping_address_json | TEXT | Yes | NULL | - | JSON address snapshot |
   774→| placed_at | TEXT | Yes | NULL | - | ISO-8601 timestamp of placement |
   775→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   776→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   777→
   778→**Indexes:**
   779→
   780→- `idx_orders_store_order_number` UNIQUE on (`store_id`, `order_number`) - Unique order number per store
   781→- `idx_orders_store_id` on (`store_id`) - FK lookup
   782→- `idx_orders_customer_id` on (`customer_id`) - Listing orders per customer
   783→- `idx_orders_store_status` on (`store_id`, `status`) - Filtering by overall status
   784→- `idx_orders_store_financial` on (`store_id`, `financial_status`) - Filtering by payment status
   785→- `idx_orders_store_fulfillment` on (`store_id`, `fulfillment_status`) - Filtering by shipping status
   786→- `idx_orders_placed_at` on (`store_id`, `placed_at`) - Date-range order queries
   787→
   788→---
   789→
   790→### order_lines
   791→
   792→Individual items within an order. Contains snapshots of product data at time of purchase.
   793→
   794→| Column | Type | Nullable | Default | Constraints | Notes |
   795→|---|---|---|---|---|---|
   796→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   797→| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |
   798→| product_id | INTEGER | Yes | NULL | FK -> products(id) ON DELETE SET NULL | Nullable: survives product deletion |
   799→| variant_id | INTEGER | Yes | NULL | FK -> product_variants(id) ON DELETE SET NULL | Nullable: survives variant deletion |
   800→| title_snapshot | TEXT | No | - | - | Product title at time of purchase |
   801→| sku_snapshot | TEXT | Yes | NULL | - | SKU at time of purchase |
   802→| quantity | INTEGER | No | 1 | - | Number of units |
   803→| unit_price_amount | INTEGER | No | 0 | - | Price per unit in minor units |
   804→| total_amount | INTEGER | No | 0 | - | Line total in minor units |
   805→| tax_lines_json | TEXT | No | '[]' | - | JSON array of tax line items |
   806→| discount_allocations_json | TEXT | No | '[]' | - | JSON array of discount allocations |
   807→
   808→**Indexes:**
   809→
   810→- `idx_order_lines_order_id` on (`order_id`) - FK lookup, listing lines per order
   811→- `idx_order_lines_product_id` on (`product_id`) - Finding order lines by product
   812→- `idx_order_lines_variant_id` on (`variant_id`) - Finding order lines by variant
   813→
   814→**Notes:** `product_id` and `variant_id` are nullable with SET NULL on delete so that order history survives product deletion. `tax_lines_json` example: `[{"title": "State Tax", "rate": 800, "amount": 400}]` (rate in basis points, e.g. 800 = 8%). `discount_allocations_json` example: `[{"discount_id": 5, "amount": 250}]`.
   815→
   816→---
   817→
   818→### payments
   819→
   820→Payment attempts and captures for an order.
   821→
   822→| Column | Type | Nullable | Default | Constraints | Notes |
   823→|---|---|---|---|---|---|
   824→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   825→| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |
   826→| provider | TEXT | No | 'mock' | CHECK IN ('mock') | Payment provider (mock PSP only) |
   827→| method | TEXT | No | - | CHECK IN ('credit_card', 'paypal', 'bank_transfer') | Payment method used |
   828→| provider_payment_id | TEXT | Yes | NULL | - | Mock payment reference ID |
   829→| status | TEXT | No | 'pending' | CHECK IN ('pending', 'captured', 'failed', 'refunded') | |
   830→| amount | INTEGER | No | 0 | - | Payment amount in minor units |
   831→| currency | TEXT | No | 'USD' | - | ISO 4217 currency code |
   832→| raw_json_encrypted | TEXT | Yes | NULL | - | Encrypted provider response (PCI compliance) |
   833→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   834→
   835→**Indexes:**
   836→
   837→- `idx_payments_order_id` on (`order_id`) - FK lookup, listing payments per order
   838→- `idx_payments_provider_id` on (`provider`, `provider_payment_id`) - Payment reconciliation by provider ID
   839→- `idx_payments_method` on (`method`) - Filtering payments by method
   840→- `idx_payments_status` on (`status`) - Filtering payments by status
   841→
   842→**Notes:** `raw_json_encrypted` stores the mock payment provider response, encrypted at the application layer. `amount` is in minor units (cents). The `provider` is always `mock` since the platform uses a mock PSP (no real payment gateway integration). The `method` column records which payment method the customer selected (credit_card, paypal, or bank_transfer).
   843→
   844→---
   845→
   846→### refunds
   847→
   848→Refund records linked to an order and payment.
   849→
   850→| Column | Type | Nullable | Default | Constraints | Notes |
   851→|---|---|---|---|---|---|
   852→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   853→| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |
   854→| payment_id | INTEGER | No | - | FK -> payments(id) ON DELETE CASCADE | Source payment |
   855→| amount | INTEGER | No | 0 | - | Refund amount in minor units |
   856→| reason | TEXT | Yes | NULL | - | Refund reason |
   857→| status | TEXT | No | 'pending' | CHECK IN ('pending', 'processed', 'failed') | |
   858→| provider_refund_id | TEXT | Yes | NULL | - | External refund ID |
   859→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   860→
   861→**Indexes:**
   862→
   863→- `idx_refunds_order_id` on (`order_id`) - FK lookup, listing refunds per order
   864→- `idx_refunds_payment_id` on (`payment_id`) - Finding refunds per payment
   865→- `idx_refunds_status` on (`status`) - Filtering refunds by status
   866→
   867→---
   868→
   869→### fulfillments
   870→
   871→Shipment tracking for order fulfillment.
   872→
   873→| Column | Type | Nullable | Default | Constraints | Notes |
   874→|---|---|---|---|---|---|
   875→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   876→| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |
   877→| status | TEXT | No | 'pending' | CHECK IN ('pending', 'shipped', 'delivered') | |
   878→| tracking_company | TEXT | Yes | NULL | - | Carrier name |
   879→| tracking_number | TEXT | Yes | NULL | - | Tracking number |
   880→| tracking_url | TEXT | Yes | NULL | - | Tracking page URL |
   881→| shipped_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   882→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   883→
   884→**Indexes:**
   885→
   886→- `idx_fulfillments_order_id` on (`order_id`) - FK lookup, listing fulfillments per order
   887→- `idx_fulfillments_status` on (`status`) - Filtering fulfillments by status
   888→- `idx_fulfillments_tracking` on (`tracking_company`, `tracking_number`) - Tracking number lookup
   889→
   890→---
   891→
   892→### fulfillment_lines
   893→
   894→Links specific order lines and quantities to a fulfillment.
   895→
   896→| Column | Type | Nullable | Default | Constraints | Notes |
   897→|---|---|---|---|---|---|
   898→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   899→| fulfillment_id | INTEGER | No | - | FK -> fulfillments(id) ON DELETE CASCADE | Parent fulfillment |
   900→| order_line_id | INTEGER | No | - | FK -> order_lines(id) ON DELETE CASCADE | Source order line |
   901→| quantity | INTEGER | No | 1 | - | Units fulfilled from this line |
   902→
   903→**Indexes:**
   904→
   905→- `idx_fulfillment_lines_fulfillment_id` on (`fulfillment_id`) - FK lookup, listing lines per fulfillment
   906→- `idx_fulfillment_lines_fulfillment_order_line` UNIQUE on (`fulfillment_id`, `order_line_id`) - Prevents duplicate line entries per fulfillment
   907→
   908→**Notes:** An order with multiple line items may be partially fulfilled. This table tracks how many units of each line item are included in each shipment.
   909→
   910→---
   911→
   912→## Epic 7 - Analytics
   913→
   914→### analytics_events
   915→
   916→Raw event stream for storefront analytics.
   917→
   918→| Column | Type | Nullable | Default | Constraints | Notes |
   919→|---|---|---|---|---|---|
   920→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   921→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
   922→| type | TEXT | No | - | - | Event type (see notes) |
   923→| session_id | TEXT | Yes | NULL | - | Browser session identifier |
   924→| customer_id | INTEGER | Yes | NULL | FK -> customers(id) ON DELETE SET NULL | Nullable for anonymous visitors |
   925→| properties_json | TEXT | No | '{}' | - | Event-specific data as JSON |
   926→| client_event_id | TEXT | Yes | NULL | UNIQUE per (store_id, client_event_id) | Client-provided idempotency key for deduplication |
   927→| occurred_at | TEXT | Yes | NULL | - | ISO-8601 timestamp of when the event actually occurred on the client |
   928→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   929→
   930→**Indexes:**
   931→
   932→- `idx_analytics_events_store_id` on (`store_id`) - FK lookup
   933→- `idx_analytics_events_store_type` on (`store_id`, `type`) - Filtering events by type per store
   934→- `idx_analytics_events_store_created` on (`store_id`, `created_at`) - Time-range event queries
   935→- `idx_analytics_events_session` on (`session_id`) - Session-based event grouping
   936→- `idx_analytics_events_customer` on (`customer_id`) - Customer activity history
   937→- `idx_analytics_events_client_event` UNIQUE on (`store_id`, `client_event_id`) - Client-side event deduplication
   938→
   939→**Notes:** Event types include: `page_view`, `product_view`, `add_to_cart`, `remove_from_cart`, `checkout_started`, `checkout_completed`, `search`. This table will grow fast - consider periodic archiving or partitioning by date for production workloads beyond SQLite.
   940→
   941→---
   942→
   943→### analytics_daily
   944→
   945→Pre-aggregated daily metrics per store. Composite primary key.
   946→
   947→| Column | Type | Nullable | Default | Constraints | Notes |
   948→|---|---|---|---|---|---|
   949→| store_id | INTEGER | No | - | PK (composite), FK -> stores(id) ON DELETE CASCADE | |
   950→| date | TEXT | No | - | PK (composite) | ISO-8601 date (YYYY-MM-DD) |
   951→| orders_count | INTEGER | No | 0 | - | Number of orders placed |
   952→| revenue_amount | INTEGER | No | 0 | - | Total revenue in minor units |
   953→| aov_amount | INTEGER | No | 0 | - | Average order value in minor units |
   954→| visits_count | INTEGER | No | 0 | - | Unique sessions |
   955→| add_to_cart_count | INTEGER | No | 0 | - | Add-to-cart events |
   956→| checkout_started_count | INTEGER | No | 0 | - | Checkout-started events |
   957→| checkout_completed_count | INTEGER | No | 0 | - | Checkout-completed events |
   958→
   959→**Primary Key:** (`store_id`, `date`)
   960→
   961→**Indexes:**
   962→
   963→- `idx_analytics_daily_store_date` on (`store_id`, `date`) - Time-range metric queries (covered by PK but explicit for clarity)
   964→
   965→**Notes:** `date` is an ISO-8601 date string (YYYY-MM-DD). `revenue_amount` and `aov_amount` (average order value) are in minor units (cents). This table is populated by a scheduled aggregation job.
   966→
   967→---
   968→
   969→## Epic 8 - Apps and Webhooks
   970→
   971→### apps
   972→
   973→Third-party or first-party application registry.
   974→
   975→| Column | Type | Nullable | Default | Constraints | Notes |
   976→|---|---|---|---|---|---|
   977→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   978→| name | TEXT | No | - | - | App display name |
   979→| status | TEXT | No | 'active' | CHECK IN ('active', 'disabled') | |
   980→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   981→
   982→**Indexes:**
   983→
   984→- `idx_apps_status` on (`status`) - Filtering apps by status
   985→
   986→---
   987→
   988→### app_installations
   989→
   990→Installation of an app on a specific store.
   991→
   992→| Column | Type | Nullable | Default | Constraints | Notes |
   993→|---|---|---|---|---|---|
   994→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   995→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | |
   996→| app_id | INTEGER | No | - | FK -> apps(id) ON DELETE CASCADE | |
   997→| scopes_json | TEXT | No | '[]' | - | JSON array of granted permission scopes |
   998→| status | TEXT | No | 'active' | CHECK IN ('active', 'suspended', 'uninstalled') | |
   999→| installed_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

content:
**Indexes:**

- `idx_checkouts_store_id` on (`store_id`) - FK lookup
- `idx_checkouts_cart_id` on (`cart_id`) - Finding checkout for a cart
- `idx_checkouts_customer_id` on (`customer_id`) - Listing checkouts per customer
- `idx_checkouts_status` on (`store_id`, `status`) - Filtering checkouts by status
- `idx_checkouts_expires_at` on (`expires_at`) - Cleanup of expired checkouts

**Notes:** `totals_json` contains a snapshot: `{"subtotal": 5000, "discount": 500, "shipping": 799, "tax": 430, "total": 5729}`. `tax_provider_snapshot_json` stores the full tax calculation response for audit purposes.

---

### shipping_zones

Geographic shipping zones for a store.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| name | TEXT | No | - | - | Zone display name |
| countries_json | TEXT | No | '[]' | - | JSON array of ISO 3166-1 alpha-2 codes, e.g. ["US","CA"] |
| regions_json | TEXT | No | '[]' | - | JSON array of region/state codes, e.g. ["US-NY","US-CA"] |

**Indexes:**

- `idx_shipping_zones_store_id` on (`store_id`) - FK lookup, listing zones per store

**Notes:** `countries_json` is an array of ISO 3166-1 alpha-2 codes. `regions_json` is an array of region/state codes.

---

### shipping_rates

Shipping rate options within a zone.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| zone_id | INTEGER | No | - | FK -> shipping_zones(id) ON DELETE CASCADE | Parent zone |
| name | TEXT | No | - | - | Rate display name |
| type | TEXT | No | 'flat' | CHECK IN ('flat', 'weight', 'price', 'carrier') | Calculation method |
| config_json | TEXT | No | '{}' | - | Type-dependent configuration (see notes) |
| is_active | INTEGER | No | 1 | - | Boolean: active flag |

**Indexes:**

- `idx_shipping_rates_zone_id` on (`zone_id`) - FK lookup, listing rates per zone
- `idx_shipping_rates_zone_active` on (`zone_id`, `is_active`) - Finding active rates per zone

**Notes:** `config_json` structure depends on `type`:
- Flat: `{"amount": 799}`
- Weight: `{"ranges": [{"min_g": 0, "max_g": 500, "amount": 499}]}`
- Price: `{"ranges": [{"min_amount": 0, "max_amount": 5000, "amount": 799}]}`
- Carrier: `{"carrier": "ups", "service": "ground"}`

---

### tax_settings

Per-store tax configuration.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| store_id | INTEGER | No | - | PRIMARY KEY, FK -> stores(id) ON DELETE CASCADE | One-to-one with stores |
| mode | TEXT | No | 'manual' | CHECK IN ('manual', 'provider') | Tax calculation mode |
| provider | TEXT | No | 'none' | CHECK IN ('stripe_tax', 'none') | External tax provider |
| prices_include_tax | INTEGER | No | 0 | - | Boolean: tax-inclusive pricing |
| config_json | TEXT | No | '{}' | - | Provider-specific config |

---

### discounts

Discount codes and automatic discounts.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| type | TEXT | No | 'code' | CHECK IN ('code', 'automatic') | Activation method |
| code | TEXT | Yes | NULL | UNIQUE per (store_id, code) | Discount code (null for automatic) |
| value_type | TEXT | No | - | CHECK IN ('fixed', 'percent', 'free_shipping') | Discount calculation type |
| value_amount | INTEGER | No | 0 | - | See notes for interpretation |
| starts_at | TEXT | No | - | - | ISO-8601 start timestamp |
| ends_at | TEXT | Yes | NULL | - | ISO-8601 end timestamp; null = no expiry |
| usage_limit | INTEGER | Yes | NULL | - | Max redemptions; null = unlimited |
| usage_count | INTEGER | No | 0 | - | Current redemption count |
| rules_json | TEXT | No | '{}' | - | Eligibility rules (min purchase, products, etc.) |
| status | TEXT | No | 'active' | CHECK IN ('draft', 'active', 'expired', 'disabled') | |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_discounts_store_code` UNIQUE on (`store_id`, `code`) - Unique discount code per store
- `idx_discounts_store_id` on (`store_id`) - FK lookup
- `idx_discounts_store_status` on (`store_id`, `status`) - Filtering discounts by status
- `idx_discounts_store_type` on (`store_id`, `type`) - Filtering by code vs automatic

**Notes:** `value_amount` interpretation depends on `value_type`. For `percent`, it is a whole number (e.g. 15 = 15%). For `fixed`, it is in minor units (cents). For `free_shipping`, it is ignored. `rules_json` may contain minimum purchase amounts, applicable product/collection IDs, customer eligibility, etc.

---

## Epic 6 - Payments, Orders, Fulfillment, and Customers

### customers

Storefront customer accounts, scoped to a store.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| email | TEXT | No | - | UNIQUE per (store_id, email) | Customer email |
| password_hash | TEXT | Yes | NULL | - | Nullable for guest checkout customers |
| name | TEXT | Yes | NULL | - | Display name |
| marketing_opt_in | INTEGER | No | 0 | - | Boolean: marketing consent |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_customers_store_email` UNIQUE on (`store_id`, `email`) - Unique email per store
- `idx_customers_store_id` on (`store_id`) - FK lookup, listing customers per store

**Notes:** `password_hash` is nullable to allow guest checkout customers who may later claim their account. Email is unique per store, not globally.

---

### customer_addresses

Saved addresses for a customer.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| customer_id | INTEGER | No | - | FK -> customers(id) ON DELETE CASCADE | Owning customer |
| label | TEXT | Yes | NULL | - | User label, e.g. "Home", "Work" |
| address_json | TEXT | No | '{}' | - | JSON address object (see notes) |
| is_default | INTEGER | No | 0 | - | Boolean: default address flag |

**Indexes:**

- `idx_customer_addresses_customer_id` on (`customer_id`) - FK lookup, listing addresses per customer
- `idx_customer_addresses_default` on (`customer_id`, `is_default`) - Quick lookup of default address

**Notes:** `address_json` structure: `{"first_name": "", "last_name": "", "company": "", "address1": "", "address2": "", "city": "", "province": "", "province_code": "", "country": "", "country_code": "", "zip": "", "phone": ""}`.

---

### orders

Placed orders with financial and fulfillment tracking.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| customer_id | INTEGER | Yes | NULL | FK -> customers(id) ON DELETE SET NULL | Nullable for guest orders |
| order_number | TEXT | No | - | UNIQUE per (store_id, order_number) | Human-readable order number |
| payment_method | TEXT | No | - | CHECK IN ('credit_card', 'paypal', 'bank_transfer') | Payment method used for this order |
| status | TEXT | No | 'pending' | CHECK IN ('pending', 'paid', 'fulfilled', 'cancelled', 'refunded') | Overall order status |
| financial_status | TEXT | No | 'pending' | CHECK IN ('pending', 'authorized', 'paid', 'partially_refunded', 'refunded', 'voided') | Payment status |
| fulfillment_status | TEXT | No | 'unfulfilled' | CHECK IN ('unfulfilled', 'partial', 'fulfilled') | Shipping status |
| currency | TEXT | No | 'USD' | - | ISO 4217 currency code |
| subtotal_amount | INTEGER | No | 0 | - | Sum of line items in minor units |
| discount_amount | INTEGER | No | 0 | - | Total discount in minor units |
| shipping_amount | INTEGER | No | 0 | - | Shipping cost in minor units |
| tax_amount | INTEGER | No | 0 | - | Total tax in minor units |
| total_amount | INTEGER | No | 0 | - | Grand total in minor units |
| email | TEXT | Yes | NULL | - | Contact email at time of order |
| billing_address_json | TEXT | Yes | NULL | - | JSON address snapshot |
| shipping_address_json | TEXT | Yes | NULL | - | JSON address snapshot |
| placed_at | TEXT | Yes | NULL | - | ISO-8601 timestamp of placement |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_orders_store_order_number` UNIQUE on (`store_id`, `order_number`) - Unique order number per store
- `idx_orders_store_id` on (`store_id`) - FK lookup
- `idx_orders_customer_id` on (`customer_id`) - Listing orders per customer
- `idx_orders_store_status` on (`store_id`, `status`) - Filtering by overall status
- `idx_orders_store_financial` on (`store_id`, `financial_status`) - Filtering by payment status
- `idx_orders_store_fulfillment` on (`store_id`, `fulfillment_status`) - Filtering by shipping status
- `idx_orders_placed_at` on (`store_id`, `placed_at`) - Date-range order queries

---

### order_lines

Individual items within an order. Contains snapshots of product data at time of purchase.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |
| product_id | INTEGER | Yes | NULL | FK -> products(id) ON DELETE SET NULL | Nullable: survives product deletion |
| variant_id | INTEGER | Yes | NULL | FK -> product_variants(id) ON DELETE SET NULL | Nullable: survives variant deletion |
| title_snapshot | TEXT | No | - | - | Product title at time of purchase |
| sku_snapshot | TEXT | Yes | NULL | - | SKU at time of purchase |
| quantity | INTEGER | No | 1 | - | Number of units |
| unit_price_amount | INTEGER | No | 0 | - | Price per unit in minor units |
| total_amount | INTEGER | No | 0 | - | Line total in minor units |
| tax_lines_json | TEXT | No | '[]' | - | JSON array of tax line items |
| discount_allocations_json | TEXT | No | '[]' | - | JSON array of discount allocations |

**Indexes:**

- `idx_order_lines_order_id` on (`order_id`) - FK lookup, listing lines per order
- `idx_order_lines_product_id` on (`product_id`) - Finding order lines by product
- `idx_order_lines_variant_id` on (`variant_id`) - Finding order lines by variant

**Notes:** `product_id` and `variant_id` are nullable with SET NULL on delete so that order history survives product deletion. `tax_lines_json` example: `[{"title": "State Tax", "rate": 800, "amount": 400}]` (rate in basis points, e.g. 800 = 8%). `discount_allocations_json` example: `[{"discount_id": 5, "amount": 250}]`.

---

### payments

Payment attempts and captures for an order.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |
| provider | TEXT | No | 'mock' | CHECK IN ('mock') | Payment provider (mock PSP only) |
| method | TEXT | No | - | CHECK IN ('credit_card', 'paypal', 'bank_transfer') | Payment method used |
| provider_payment_id | TEXT | Yes | NULL | - | Mock payment reference ID |
| status | TEXT | No | 'pending' | CHECK IN ('pending', 'captured', 'failed', 'refunded') | |
| amount | INTEGER | No | 0 | - | Payment amount in minor units |
| currency | TEXT | No | 'USD' | - | ISO 4217 currency code |
| raw_json_encrypted | TEXT | Yes | NULL | - | Encrypted provider response (PCI compliance) |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_payments_order_id` on (`order_id`) - FK lookup, listing payments per order
- `idx_payments_provider_id` on (`provider`, `provider_payment_id`) - Payment reconciliation by provider ID
- `idx_payments_method` on (`method`) - Filtering payments by method
- `idx_payments_status` on (`status`) - Filtering payments by status

**Notes:** `raw_json_encrypted` stores the mock payment provider response, encrypted at the application layer. `amount` is in minor units (cents). The `provider` is always `mock` since the platform uses a mock PSP (no real payment gateway integration). The `method` column records which payment method the customer selected (credit_card, paypal, or bank_transfer).

---

### refunds

Refund records linked to an order and payment.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |
| payment_id | INTEGER | No | - | FK -> payments(id) ON DELETE CASCADE | Source payment |
| amount | INTEGER | No | 0 | - | Refund amount in minor units |
| reason | TEXT | Yes | NULL | - | Refund reason |
| status | TEXT | No | 'pending' | CHECK IN ('pending', 'processed', 'failed') | |
| provider_refund_id | TEXT | Yes | NULL | - | External refund ID |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_refunds_order_id` on (`order_id`) - FK lookup, listing refunds per order
- `idx_refunds_payment_id` on (`payment_id`) - Finding refunds per payment
- `idx_refunds_status` on (`status`) - Filtering refunds by status

---

### fulfillments

Shipment tracking for order fulfillment.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |
| status | TEXT | No | 'pending' | CHECK IN ('pending', 'shipped', 'delivered') | |
| tracking_company | TEXT | Yes | NULL | - | Carrier name |
| tracking_number | TEXT | Yes | NULL | - | Tracking number |
| tracking_url | TEXT | Yes | NULL | - | Tracking page URL |
| shipped_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_fulfillments_order_id` on (`order_id`) - FK lookup, listing fulfillments per order
- `idx_fulfillments_status` on (`status`) - Filtering fulfillments by status
- `idx_fulfillments_tracking` on (`tracking_company`, `tracking_number`) - Tracking number lookup

---

### fulfillment_lines

Links specific order lines and quantities to a fulfillment.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| fulfillment_id | INTEGER | No | - | FK -> fulfillments(id) ON DELETE CASCADE | Parent fulfillment |
| order_line_id | INTEGER | No | - | FK -> order_lines(id) ON DELETE CASCADE | Source order line |
| quantity | INTEGER | No | 1 | - | Units fulfilled from this line |

**Indexes:**

- `idx_fulfillment_lines_fulfillment_id` on (`fulfillment_id`) - FK lookup, listing lines per fulfillment
- `idx_fulfillment_lines_fulfillment_order_line` UNIQUE on (`fulfillment_id`, `order_line_id`) - Prevents duplicate line entries per fulfillment

**Notes:** An order with multiple line items may be partially fulfilled. This table tracks how many units of each line item are included in each shipment.

---

## Epic 7 - Analytics

### analytics_events

Raw event stream for storefront analytics.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| type | TEXT | No | - | - | Event type (see notes) |
| session_id | TEXT | Yes | NULL | - | Browser session identifier |
| customer_id | INTEGER | Yes | NULL | FK -> customers(id) ON DELETE SET NULL | Nullable for anonymous visitors |
| properties_json | TEXT | No | '{}' | - | Event-specific data as JSON |
| client_event_id | TEXT | Yes | NULL | UNIQUE per (store_id, client_event_id) | Client-provided idempotency key for deduplication |
| occurred_at | TEXT | Yes | NULL | - | ISO-8601 timestamp of when the event actually occurred on the client |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_analytics_events_store_id` on (`store_id`) - FK lookup
- `idx_analytics_events_store_type` on (`store_id`, `type`) - Filtering events by type per store
- `idx_analytics_events_store_created` on (`store_id`, `created_at`) - Time-range event queries
- `idx_analytics_events_session` on (`session_id`) - Session-based event grouping
- `idx_analytics_events_customer` on (`customer_id`) - Customer activity history
- `idx_analytics_events_client_event` UNIQUE on (`store_id`, `client_event_id`) - Client-side event deduplication

**Notes:** Event types include: `page_view`, `product_view`, `add_to_cart`, `remove_from_cart`, `checkout_started`, `checkout_completed`, `search`. This table will grow fast - consider periodic archiving or partitioning by date for production workloads beyond SQLite.

---

### analytics_daily

Pre-aggregated daily metrics per store. Composite primary key.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| store_id | INTEGER | No | - | PK (composite), FK -> stores(id) ON DELETE CASCADE | |
| date | TEXT | No | - | PK (composite) | ISO-8601 date (YYYY-MM-DD) |
| orders_count | INTEGER | No | 0 | - | Number of orders placed |
| revenue_amount | INTEGER | No | 0 | - | Total revenue in minor units |
| aov_amount | INTEGER | No | 0 | - | Average order value in minor units |
| visits_count | INTEGER | No | 0 | - | Unique sessions |
| add_to_cart_count | INTEGER | No | 0 | - | Add-to-cart events |
| checkout_started_count | INTEGER | No | 0 | - | Checkout-started events |
| checkout_completed_count | INTEGER | No | 0 | - | Checkout-completed events |

**Primary Key:** (`store_id`, `date`)

**Indexes:**

- `idx_analytics_daily_store_date` on (`store_id`, `date`) - Time-range metric queries (covered by PK but explicit for clarity)

**Notes:** `date` is an ISO-8601 date string (YYYY-MM-DD). `revenue_amount` and `aov_amount` (average order value) are in minor units (cents). This table is populated by a scheduled aggregation job.

---

## Epic 8 - Apps and Webhooks

### apps

Third-party or first-party application registry.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| name | TEXT | No | - | - | App display name |
| status | TEXT | No | 'active' | CHECK IN ('active', 'disabled') | |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_apps_status` on (`status`) - Filtering apps by status

---

### app_installations

Installation of an app on a specific store.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | |
| app_id | INTEGER | No | - | FK -> apps(id) ON DELETE CASCADE | |
| scopes_json | TEXT | No | '[]' | - | JSON array of granted permission scopes |
| status | TEXT | No | 'active' | CHECK IN ('active', 'suspended', 'uninstalled') | |
| installed_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |


userType: external

gitBranch: claude-subagents

slug: wise-shimmying-pretzel

type: message

role: assistant

service_tier: standard

type: assistant


type: progress

hookEvent: PostToolUse

command: callback


type: user

role: user

content:
   300→8. Set `line_discount_amount = 0` (recalculated by discount engine during checkout).
   301→9. Calculate: `line_total_amount = line_subtotal_amount - line_discount_amount`.
   302→10. Increment `cart_version`.
   303→
   304→**Update quantity:**
   305→- Validate new quantity > 0. If 0, remove the line instead.
   306→- Re-check inventory policy for the new quantity.
   307→- Recalculate line amounts.
   308→- Increment `cart_version`.
   309→
   310→**Remove line:**
   311→- Delete the `cart_lines` record.
   312→- Increment `cart_version`.
   313→
   314→### 4.3 Cart Version (Optimistic Concurrency)
   315→
   316→Every cart mutation increments `cart_version` by 1.
   317→
   318→**For API clients:**
   319→- Client sends `expected_version` with each mutation request.
   320→- If cart's current version does not match the expected version, return HTTP 409 Conflict with the current cart state in the response body.
   321→- This prevents race conditions when multiple browser tabs or devices modify the same cart.
   322→
   323→**For Livewire/session-based flows:**
   324→- Still implemented for consistency, but less critical since requests are serialized per session.
   325→
   326→### 4.4 Line Amount Calculation
   327→
   328→```
   329→line_subtotal_amount = unit_price_amount * quantity
   330→line_discount_amount = 0  (updated by discount engine during checkout)
   331→line_total_amount    = line_subtotal_amount - line_discount_amount
   332→```
   333→
   334→All values are integers in minor currency units.
   335→
   336→### 4.5 Cart Expiration
   337→
   338→**Job:** `App\Jobs\CleanupAbandonedCarts`
   339→
   340→- Schedule: daily
   341→- Finds carts where status = `active` AND updated_at < (now - 14 days). The 14-day threshold is configurable via `store_settings`.
   342→- Marks them as `abandoned`.
   343→- Releases any reserved inventory associated with active checkouts for these carts.
   344→
   345→---
   346→
   347→## 5. Pricing Engine
   348→
   349→### 5.1 Calculation Order (deterministic pipeline)
   350→
   351→**Service:** `App\Services\PricingEngine`
   352→
   353→The pricing engine follows a strict, deterministic sequence. The same inputs must always produce the same output.
   354→
   355→```mermaid
   356→flowchart TD
   357→    A["Step 1: Line Subtotals
   358→    FOR EACH line: subtotal = unit_price * quantity"] --> B["Step 2: Cart Subtotal
   359→    SUM of all line subtotals"]
   360→    B --> C["Step 3: Discount
   361→    Apply discount rules (Section 7)
   362→    Allocate proportionally across qualifying lines"]
   363→    C --> D["Step 4: Discounted Subtotal
   364→    subtotal - total_discount"]
   365→    D --> E["Step 5: Shipping
   366→    Based on selected method and rate rules (Section 9)"]
   367→    E --> F["Step 6: Tax
   368→    Calculated on discounted line amounts
   369→    + shipping if taxable (Section 8)"]
   370→    F --> G["Step 7: Total
   371→    discounted_subtotal + shipping + tax_total"]
   372→```
   373→
   374→### 5.2 Output Structure
   375→
   376→**Value Object:** `App\ValueObjects\PricingResult`
   377→
   378→| Field | Type | Description |
   379→|---|---|---|
   380→| `subtotal` | integer (cents) | Sum of all line subtotals before discount |
   381→| `discount` | integer (cents) | Total discount amount |
   382→| `shipping` | integer (cents) | Shipping cost |
   383→| `taxLines` | array of TaxLine | Individual tax line items |
   384→| `taxTotal` | integer (cents) | Sum of all tax line amounts |
   385→| `total` | integer (cents) | Final amount: discounted_subtotal + shipping + taxTotal |
   386→| `currency` | string | ISO 4217 currency code |
   387→
   388→**Value Object:** `App\ValueObjects\TaxLine`
   389→
   390→| Field | Type | Description |
   391→|---|---|---|
   392→| `name` | string | Tax label (e.g., "State Tax") |
   393→| `rate` | integer | Tax rate in basis points (e.g., 800 = 8.00%) |
   394→| `amount` | integer (cents) | Calculated tax amount |
   395→
   396→### 5.3 Rounding Strategy
   397→
   398→- Each line's discount allocation, tax, and total are rounded independently.
   399→- Totals are computed by summing already-rounded line amounts.
   400→- This prevents accumulation of fractional cents across lines.
   401→- Any remainder from proportional discount allocation is added to the last qualifying line (largest-remainder method).
   402→
   403→### 5.4 Determinism and Snapshotting
   404→
   405→- After each calculation, store the full result in `checkouts.totals_json` as a JSON snapshot.
   406→- Recalculate on every significant state change: address change (may affect tax/shipping zones), shipping method change, discount code change.
   407→- The snapshot structure mirrors the PricingResult value object.
   408→
   409→---
   410→
   411→## 6. Checkout State Machine
   412→
   413→### 6.1 States
   414→
   415→**Enum:** `App\Enums\CheckoutStatus` - values: `started`, `addressed`, `shipping_selected`, `payment_selected`, `completed`, `expired`
   416→
   417→```mermaid
   418→stateDiagram-v2
   419→    [*] --> started : Checkout created from cart
   420→    started --> addressed : Address provided
   421→    addressed --> shipping_selected : Shipping method chosen
   422→    shipping_selected --> payment_selected : Payment method selected
   423→    payment_selected --> completed : Payment confirmed
   424→    payment_selected --> expired : Timeout (24h)
   425→
   426→    started --> expired : Timeout (24h)
   427→    addressed --> expired : Timeout (24h)
   428→    shipping_selected --> expired : Timeout (24h)
   429→
   430→    note right of addressed : Triggers pricing recalculation
   431→    note right of shipping_selected : Triggers pricing recalculation
   432→    note right of payment_selected : Inventory reserved
   433→    note right of completed : Order created, inventory committed
   434→    note right of expired : Reserved inventory released
   435→```
   436→
   437→### 6.2 Transitions and Validation
   438→
   439→**Service:** `App\Services\CheckoutService`
   440→
   441→#### Transition: started -> addressed
   442→
   443→**Method:** `setAddress(checkout, addressData)`
   444→
   445→**Required data:**
   446→- `email` - valid email address
   447→- `shipping_address` with required fields:
   448→
   449→| Field | Required | Type | Notes |
   450→|---|---|---|---|
   451→| `first_name` | Yes | string | |
   452→| `last_name` | Yes | string | |
   453→| `address1` | Yes | string | |
   454→| `address2` | No | string | |
   455→| `company` | No | string | |
   456→| `city` | Yes | string | |
   457→| `province` | No | string | |
   458→| `province_code` | No | string | Used for zone matching |
   459→| `country` | Yes | string | 2-letter ISO 3166-1 code |
   460→| `postal_code` | Yes | string | |
   461→| `phone` | No | string | |
   462→
   463→**Actions:**
   464→1. Validate address fields using a dedicated Form Request (`SetCheckoutAddressRequest`).
   465→2. Save email and shipping_address_json on the checkout.
   466→3. Copy shipping address to billing_address_json by default (can be overridden later).
   467→4. Recalculate pricing (shipping zones and tax regions may change based on address).
   468→5. Transition status to `addressed`.
   469→6. Dispatch `CheckoutAddressed` event.
   470→
   471→#### Transition: addressed -> shipping_selected
   472→
   473→**Method:** `setShippingMethod(checkout, shippingRateId)`
   474→
   475→**Requires:**
   476→- A valid shipping_rate_id that belongs to a shipping zone matching the checkout's shipping address.
   477→- **Skip condition:** If no cart lines have `requires_shipping = true` on their variant, skip this step. Set status to `shipping_selected` with shipping_method_id = null and shipping_amount = 0.
   478→
   479→**Actions:**
   480→1. Resolve available shipping zones for the address (see Section 9).
   481→2. Verify the selected rate belongs to an applicable zone.
   482→3. Save shipping_method_id on the checkout.
   483→4. Recalculate pricing with the selected shipping rate.
   484→5. Transition status to `shipping_selected`.
   485→6. Dispatch `CheckoutShippingSelected` event.
   486→
   487→#### Transition: shipping_selected -> payment_selected
   488→
   489→**Method:** `selectPaymentMethod(checkout, paymentMethod)` - validates and stores the selected payment method
   490→
   491→**Requires:**
   492→- All previous data is valid (address, shipping method).
   493→- Totals have been calculated and stored.
   494→- `paymentMethod` must be one of: `credit_card`, `paypal`, `bank_transfer`.
   495→
   496→**Actions:**
   497→1. Store `payment_method` on the checkout record.
   498→2. Reserve inventory for all cart lines (call InventoryService.reserve for each line).
   499→3. Set `expires_at` on the checkout to current time + 24 hours.
   500→4. Transition status to `payment_selected`.
   501→
   502→#### Transition: payment_selected -> completed
   503→
   504→**Method:** `completeCheckout(checkout, paymentMethodData)` - returns an Order
   505→
   506→**Triggered by:** Customer submitting the payment form (onsite, no external redirect).
   507→
   508→**CRITICAL: This transition must be idempotent.** If called multiple times for the same checkout (e.g., double-click), it must not create duplicate orders. Always check if an order already exists for this checkout before proceeding.
   509→
   510→**Actions (all within a single database transaction):**
   511→
   512→```
   513→PROCEDURE completeCheckout(checkout, paymentMethodData):
   514→    BEGIN TRANSACTION
   515→
   516→    1. IF order already exists for this checkout:
   517→           RETURN existing order (idempotent)
   518→
   519→    2. CALL MockPaymentProvider.charge(checkout, paymentMethodData)
   520→       IF result.success = false:
   521→           Release reserved inventory
   522→           THROW PaymentFailedException(result.errorCode)
   523→
   524→    3. Generate next sequential order number for this store
   525→
   526→    4. DETERMINE order statuses based on payment method:
   527→       IF checkout.payment_method IN ('credit_card', 'paypal'):
   528→           orderStatus = 'paid'
   529→           financialStatus = 'paid'
   530→           paymentStatus = 'captured'
   531→           inventoryAction = 'commit'
   532→       ELSE IF checkout.payment_method = 'bank_transfer':
   533→           orderStatus = 'pending'
   534→           financialStatus = 'pending'
   535→           paymentStatus = 'pending'
   536→           inventoryAction = 'keep_reserved'
   537→
   538→    5. CREATE order record with:
   539→       - All checkout data (addresses, email, totals snapshot)
   540→       - payment_method = checkout.payment_method
   541→       - status = orderStatus, financial_status = financialStatus
   542→       - fulfillment_status = 'unfulfilled'
   543→       - placed_at = now
   544→
   545→    6. FOR EACH cart line:
   546→       CREATE order_line with snapshot data:
   547→       - title_snapshot = product title + variant option labels
   548→       - sku_snapshot = variant SKU at time of purchase
   549→       - unit_price_amount, quantity, total_amount
   550→       - tax_lines_json, discount_allocations_json
   551→
   552→    7. CREATE payment record:
   553→       - Link to order, provider = 'mock', method = checkout.payment_method
   554→       - provider_payment_id = mock reference ID
   555→       - status = paymentStatus, amount = order total
   556→
   557→    8. IF inventoryAction = 'commit':
   558→       FOR EACH cart line:
   559→           CALL InventoryService.commit(variant, quantity)
   560→       // Note: for bank_transfer, inventory stays reserved until admin confirms payment
   561→
   562→    9. IF checkout has a discount code:
   563→       INCREMENT discount's usage_count by 1
   564→
   565→    10. SET cart status = 'converted'
   566→
   567→    11. SET checkout status = 'completed'
   568→
   569→    12. IF inventoryAction = 'commit' AND all order lines are digital:
   570→        Auto-create fulfillment (see Section 11.7)
   571→
   572→    13. DISPATCH OrderCreated event
   573→
   574→    COMMIT TRANSACTION
   575→    RETURN order
   576→```
   577→
   578→#### Transition: Any active state -> expired
   579→
   580→**Triggered by:** `App\Jobs\ExpireAbandonedCheckouts` scheduled job (every 15 minutes).
   581→
   582→**Conditions:** Checkouts where status is NOT `completed` or `expired`, AND `updated_at` < now - 24 hours.
   583→
   584→**Actions:**
   585→1. If inventory was reserved (status was `payment_selected`): release all reserved inventory via InventoryService.release.
   586→2. Transition status to `expired`.
   587→3. Dispatch `CheckoutExpired` event.
   588→
   589→### 6.3 Events
   590→
   591→| Event | Dispatched When |
   592→|---|---|
   593→| `CheckoutAddressed` | Address saved on checkout |
   594→| `CheckoutShippingSelected` | Shipping method selected |
   595→| `CheckoutCompleted` | Payment confirmed and order created |
   596→| `CheckoutExpired` | Checkout expired due to inactivity |
   597→
   598→---
   599→
   600→## 7. Discount Engine
   601→
   602→### 7.1 Discount Types
   603→
   604→**Enum:** `App\Enums\DiscountType` - values: `code`, `automatic`
   605→
   606→| Type | Behavior |
   607→|---|---|
   608→| `code` | Customer enters a code at checkout. Stored in `discounts.code`, matched case-insensitively. |
   609→| `automatic` | Applied automatically when rule conditions are met. Evaluated on every pricing calculation. |
   610→
   611→**Stacking rules:**
   612→- Only one code discount per checkout.
   613→- Multiple automatic discounts can stack (each applied sequentially to the remaining undiscounted amount).
   614→
   615→### Discount Status Lifecycle
   616→
   617→| Status | Description | Transition |
   618→|--------|-------------|------------|
   619→| `draft` | Created but not yet active. Not visible to customers, not applicable to carts. | Admin creates discount -> `draft`. Admin activates -> `active`. |
   620→| `active` | Live and applicable to carts (subject to date range and usage limits). | Automatic transition when `starts_at` is reached (if set). Admin can manually activate. |
   621→| `expired` | Past the `ends_at` date. No longer applicable. | Automatic transition when `ends_at` passes. Irreversible. |
   622→| `disabled` | Manually deactivated by admin. Not applicable to carts. | Admin disables an `active` discount. Admin can re-enable -> `active`. |
   623→
   624→**Allowed transitions:**
   625→- `draft` -> `active`
   626→- `active` -> `disabled`
   627→- `active` -> `expired` (automatic)
   628→- `disabled` -> `active`
   629→
   630→### 7.2 Value Types
   631→
   632→**Enum:** `App\Enums\DiscountValueType` - values: `percent`, `fixed`, `free_shipping`
   633→
   634→| Value Type | Calculation | Notes |
   635→|---|---|---|
   636→| `percent` | `discount = applicable_subtotal * value_amount / 100` | `value_amount` stores whole percentage (e.g., 15 = 15%) |
   637→| `fixed` | `discount = value_amount` (cents) | Cannot exceed the applicable subtotal |
   638→| `free_shipping` | Override shipping amount to 0 | No monetary discount on items. `value_amount` is ignored. |
   639→
   640→### 7.3 Rules
   641→
   642→Rules are stored in `discounts.rules_json` with the following structure:
   643→
   644→| Rule Key | Type | Description |
   645→|---|---|---|
   646→| `min_purchase_amount` | integer (cents) or null | Cart subtotal must be >= this value. Null means no minimum. |
   647→| `applicable_product_ids` | array of integers or null | Discount only applies to lines with these product IDs. Null/empty means all products. |
   648→| `applicable_collection_ids` | array of integers or null | Discount applies to products belonging to these collections. Null/empty means all. |
   649→| `customer_eligibility` | string | `"all"` for all customers. Future expansion: `"returning"`, `"specific"`. |
   650→
   651→**Applicability logic:** When both `applicable_product_ids` and `applicable_collection_ids` are set, a product qualifies if it matches **either** condition (union, not intersection). When both are null or empty, the discount applies to the entire order.
   652→
   653→### 7.4 Validation Flow
   654→
   655→**Service:** `App\Services\DiscountService`
   656→
   657→**Method:** `validate(code, cart)` - returns a DiscountValidationResult value object
   658→
   659→```mermaid
   660→flowchart TD
   661→    A[Input: discount code + cart] --> B{Code exists in store?
   662→    Case-insensitive lookup}
   663→    B -->|No| C["Error: discount_not_found"]
   664→    B -->|Yes| D{Status = active?}
   665→    D -->|No| E["Error: discount_expired"]
   666→    D -->|Yes| F{starts_at <= now?}
   667→    F -->|No| G["Error: discount_not_yet_active"]
   668→    F -->|Yes| H{ends_at is null OR ends_at >= now?}
   669→    H -->|No| I["Error: discount_expired"]
   670→    H -->|Yes| J{usage_limit set AND usage_count >= usage_limit?}
   671→    J -->|Yes| K["Error: discount_usage_limit_reached"]
   672→    J -->|No| L{min_purchase_amount set AND subtotal < minimum?}
   673→    L -->|Yes| M["Error: discount_min_purchase_not_met"]
   674→    L -->|No| N{Product/collection restrictions set AND no cart lines qualify?}
   675→    N -->|Yes| O["Error: discount_not_applicable"]
   676→    N -->|No| P["Valid - return discount"]
   677→```
   678→
   679→### 7.5 Error Codes
   680→
   681→| Code | Description |
   682→|---|---|
   683→| `discount_not_found` | Code does not exist in this store |
   684→| `discount_expired` | Past `ends_at` date or status is not active |
   685→| `discount_not_yet_active` | Before `starts_at` date |
   686→| `discount_usage_limit_reached` | `usage_count >= usage_limit` |
   687→| `discount_min_purchase_not_met` | Cart subtotal < `min_purchase_amount` |
   688→| `discount_not_applicable` | No qualifying products in cart |
   689→
   690→### 7.6 Discount Allocation
   691→
   692→For `percent` and `fixed` discounts with applicable product restrictions:
   693→
   694→```
   695→PROCEDURE allocateDiscount(discount, cartLines):
   696→    qualifyingLines = FILTER cartLines where product matches applicable_product_ids
   697→                      OR product belongs to applicable_collection_ids
   698→    qualifyingSubtotal = SUM(qualifyingLines.line_subtotal_amount)
   699→

type: text

content:
8. Set `line_discount_amount = 0` (recalculated by discount engine during checkout).
9. Calculate: `line_total_amount = line_subtotal_amount - line_discount_amount`.
10. Increment `cart_version`.

**Update quantity:**
- Validate new quantity > 0. If 0, remove the line instead.
- Re-check inventory policy for the new quantity.
- Recalculate line amounts.
- Increment `cart_version`.

**Remove line:**
- Delete the `cart_lines` record.
- Increment `cart_version`.

### 4.3 Cart Version (Optimistic Concurrency)

Every cart mutation increments `cart_version` by 1.

**For API clients:**
- Client sends `expected_version` with each mutation request.
- If cart's current version does not match the expected version, return HTTP 409 Conflict with the current cart state in the response body.
- This prevents race conditions when multiple browser tabs or devices modify the same cart.

**For Livewire/session-based flows:**
- Still implemented for consistency, but less critical since requests are serialized per session.

### 4.4 Line Amount Calculation

```
line_subtotal_amount = unit_price_amount * quantity
line_discount_amount = 0  (updated by discount engine during checkout)
line_total_amount    = line_subtotal_amount - line_discount_amount
```

All values are integers in minor currency units.

### 4.5 Cart Expiration

**Job:** `App\Jobs\CleanupAbandonedCarts`

- Schedule: daily
- Finds carts where status = `active` AND updated_at < (now - 14 days). The 14-day threshold is configurable via `store_settings`.
- Marks them as `abandoned`.
- Releases any reserved inventory associated with active checkouts for these carts.

---

## 5. Pricing Engine

### 5.1 Calculation Order (deterministic pipeline)

**Service:** `App\Services\PricingEngine`

The pricing engine follows a strict, deterministic sequence. The same inputs must always produce the same output.

```mermaid
flowchart TD
    A["Step 1: Line Subtotals
    FOR EACH line: subtotal = unit_price * quantity"] --> B["Step 2: Cart Subtotal
    SUM of all line subtotals"]
    B --> C["Step 3: Discount
    Apply discount rules (Section 7)
    Allocate proportionally across qualifying lines"]
    C --> D["Step 4: Discounted Subtotal
    subtotal - total_discount"]
    D --> E["Step 5: Shipping
    Based on selected method and rate rules (Section 9)"]
    E --> F["Step 6: Tax
    Calculated on discounted line amounts
    + shipping if taxable (Section 8)"]
    F --> G["Step 7: Total
    discounted_subtotal + shipping + tax_total"]
```

### 5.2 Output Structure

**Value Object:** `App\ValueObjects\PricingResult`

| Field | Type | Description |
|---|---|---|
| `subtotal` | integer (cents) | Sum of all line subtotals before discount |
| `discount` | integer (cents) | Total discount amount |
| `shipping` | integer (cents) | Shipping cost |
| `taxLines` | array of TaxLine | Individual tax line items |
| `taxTotal` | integer (cents) | Sum of all tax line amounts |
| `total` | integer (cents) | Final amount: discounted_subtotal + shipping + taxTotal |
| `currency` | string | ISO 4217 currency code |

**Value Object:** `App\ValueObjects\TaxLine`

| Field | Type | Description |
|---|---|---|
| `name` | string | Tax label (e.g., "State Tax") |
| `rate` | integer | Tax rate in basis points (e.g., 800 = 8.00%) |
| `amount` | integer (cents) | Calculated tax amount |

### 5.3 Rounding Strategy

- Each line's discount allocation, tax, and total are rounded independently.
- Totals are computed by summing already-rounded line amounts.
- This prevents accumulation of fractional cents across lines.
- Any remainder from proportional discount allocation is added to the last qualifying line (largest-remainder method).

### 5.4 Determinism and Snapshotting

- After each calculation, store the full result in `checkouts.totals_json` as a JSON snapshot.
- Recalculate on every significant state change: address change (may affect tax/shipping zones), shipping method change, discount code change.
- The snapshot structure mirrors the PricingResult value object.

---

## 6. Checkout State Machine

### 6.1 States

**Enum:** `App\Enums\CheckoutStatus` - values: `started`, `addressed`, `shipping_selected`, `payment_selected`, `completed`, `expired`

```mermaid
stateDiagram-v2
    [*] --> started : Checkout created from cart
    started --> addressed : Address provided
    addressed --> shipping_selected : Shipping method chosen
    shipping_selected --> payment_selected : Payment method selected
    payment_selected --> completed : Payment confirmed
    payment_selected --> expired : Timeout (24h)

    started --> expired : Timeout (24h)
    addressed --> expired : Timeout (24h)
    shipping_selected --> expired : Timeout (24h)

    note right of addressed : Triggers pricing recalculation
    note right of shipping_selected : Triggers pricing recalculation
    note right of payment_selected : Inventory reserved
    note right of completed : Order created, inventory committed
    note right of expired : Reserved inventory released
```

### 6.2 Transitions and Validation

**Service:** `App\Services\CheckoutService`

#### Transition: started -> addressed

**Method:** `setAddress(checkout, addressData)`

**Required data:**
- `email` - valid email address
- `shipping_address` with required fields:

| Field | Required | Type | Notes |
|---|---|---|---|
| `first_name` | Yes | string | |
| `last_name` | Yes | string | |
| `address1` | Yes | string | |
| `address2` | No | string | |
| `company` | No | string | |
| `city` | Yes | string | |
| `province` | No | string | |
| `province_code` | No | string | Used for zone matching |
| `country` | Yes | string | 2-letter ISO 3166-1 code |
| `postal_code` | Yes | string | |
| `phone` | No | string | |

**Actions:**
1. Validate address fields using a dedicated Form Request (`SetCheckoutAddressRequest`).
2. Save email and shipping_address_json on the checkout.
3. Copy shipping address to billing_address_json by default (can be overridden later).
4. Recalculate pricing (shipping zones and tax regions may change based on address).
5. Transition status to `addressed`.
6. Dispatch `CheckoutAddressed` event.

#### Transition: addressed -> shipping_selected

**Method:** `setShippingMethod(checkout, shippingRateId)`

**Requires:**
- A valid shipping_rate_id that belongs to a shipping zone matching the checkout's shipping address.
- **Skip condition:** If no cart lines have `requires_shipping = true` on their variant, skip this step. Set status to `shipping_selected` with shipping_method_id = null and shipping_amount = 0.

**Actions:**
1. Resolve available shipping zones for the address (see Section 9).
2. Verify the selected rate belongs to an applicable zone.
3. Save shipping_method_id on the checkout.
4. Recalculate pricing with the selected shipping rate.
5. Transition status to `shipping_selected`.
6. Dispatch `CheckoutShippingSelected` event.

#### Transition: shipping_selected -> payment_selected

**Method:** `selectPaymentMethod(checkout, paymentMethod)` - validates and stores the selected payment method

**Requires:**
- All previous data is valid (address, shipping method).
- Totals have been calculated and stored.
- `paymentMethod` must be one of: `credit_card`, `paypal`, `bank_transfer`.

**Actions:**
1. Store `payment_method` on the checkout record.
2. Reserve inventory for all cart lines (call InventoryService.reserve for each line).
3. Set `expires_at` on the checkout to current time + 24 hours.
4. Transition status to `payment_selected`.

#### Transition: payment_selected -> completed

**Method:** `completeCheckout(checkout, paymentMethodData)` - returns an Order

**Triggered by:** Customer submitting the payment form (onsite, no external redirect).

**CRITICAL: This transition must be idempotent.** If called multiple times for the same checkout (e.g., double-click), it must not create duplicate orders. Always check if an order already exists for this checkout before proceeding.

**Actions (all within a single database transaction):**

```
PROCEDURE completeCheckout(checkout, paymentMethodData):
    BEGIN TRANSACTION

    1. IF order already exists for this checkout:
           RETURN existing order (idempotent)

    2. CALL MockPaymentProvider.charge(checkout, paymentMethodData)
       IF result.success = false:
           Release reserved inventory
           THROW PaymentFailedException(result.errorCode)

    3. Generate next sequential order number for this store

    4. DETERMINE order statuses based on payment method:
       IF checkout.payment_method IN ('credit_card', 'paypal'):
           orderStatus = 'paid'
           financialStatus = 'paid'
           paymentStatus = 'captured'
           inventoryAction = 'commit'
       ELSE IF checkout.payment_method = 'bank_transfer':
           orderStatus = 'pending'
           financialStatus = 'pending'
           paymentStatus = 'pending'
           inventoryAction = 'keep_reserved'

    5. CREATE order record with:
       - All checkout data (addresses, email, totals snapshot)
       - payment_method = checkout.payment_method
       - status = orderStatus, financial_status = financialStatus
       - fulfillment_status = 'unfulfilled'
       - placed_at = now

    6. FOR EACH cart line:
       CREATE order_line with snapshot data:
       - title_snapshot = product title + variant option labels
       - sku_snapshot = variant SKU at time of purchase
       - unit_price_amount, quantity, total_amount
       - tax_lines_json, discount_allocations_json

    7. CREATE payment record:
       - Link to order, provider = 'mock', method = checkout.payment_method
       - provider_payment_id = mock reference ID
       - status = paymentStatus, amount = order total

    8. IF inventoryAction = 'commit':
       FOR EACH cart line:
           CALL InventoryService.commit(variant, quantity)
       // Note: for bank_transfer, inventory stays reserved until admin confirms payment

    9. IF checkout has a discount code:
       INCREMENT discount's usage_count by 1

    10. SET cart status = 'converted'

    11. SET checkout status = 'completed'

    12. IF inventoryAction = 'commit' AND all order lines are digital:
        Auto-create fulfillment (see Section 11.7)

    13. DISPATCH OrderCreated event

    COMMIT TRANSACTION
    RETURN order
```

#### Transition: Any active state -> expired

**Triggered by:** `App\Jobs\ExpireAbandonedCheckouts` scheduled job (every 15 minutes).

**Conditions:** Checkouts where status is NOT `completed` or `expired`, AND `updated_at` < now - 24 hours.

**Actions:**
1. If inventory was reserved (status was `payment_selected`): release all reserved inventory via InventoryService.release.
2. Transition status to `expired`.
3. Dispatch `CheckoutExpired` event.

### 6.3 Events

| Event | Dispatched When |
|---|---|
| `CheckoutAddressed` | Address saved on checkout |
| `CheckoutShippingSelected` | Shipping method selected |
| `CheckoutCompleted` | Payment confirmed and order created |
| `CheckoutExpired` | Checkout expired due to inactivity |

---

## 7. Discount Engine

### 7.1 Discount Types

**Enum:** `App\Enums\DiscountType` - values: `code`, `automatic`

| Type | Behavior |
|---|---|
| `code` | Customer enters a code at checkout. Stored in `discounts.code`, matched case-insensitively. |
| `automatic` | Applied automatically when rule conditions are met. Evaluated on every pricing calculation. |

**Stacking rules:**
- Only one code discount per checkout.
- Multiple automatic discounts can stack (each applied sequentially to the remaining undiscounted amount).

### Discount Status Lifecycle

| Status | Description | Transition |
|--------|-------------|------------|
| `draft` | Created but not yet active. Not visible to customers, not applicable to carts. | Admin creates discount -> `draft`. Admin activates -> `active`. |
| `active` | Live and applicable to carts (subject to date range and usage limits). | Automatic transition when `starts_at` is reached (if set). Admin can manually activate. |
| `expired` | Past the `ends_at` date. No longer applicable. | Automatic transition when `ends_at` passes. Irreversible. |
| `disabled` | Manually deactivated by admin. Not applicable to carts. | Admin disables an `active` discount. Admin can re-enable -> `active`. |

**Allowed transitions:**
- `draft` -> `active`
- `active` -> `disabled`
- `active` -> `expired` (automatic)
- `disabled` -> `active`

### 7.2 Value Types

**Enum:** `App\Enums\DiscountValueType` - values: `percent`, `fixed`, `free_shipping`

| Value Type | Calculation | Notes |
|---|---|---|
| `percent` | `discount = applicable_subtotal * value_amount / 100` | `value_amount` stores whole percentage (e.g., 15 = 15%) |
| `fixed` | `discount = value_amount` (cents) | Cannot exceed the applicable subtotal |
| `free_shipping` | Override shipping amount to 0 | No monetary discount on items. `value_amount` is ignored. |

### 7.3 Rules

Rules are stored in `discounts.rules_json` with the following structure:

| Rule Key | Type | Description |
|---|---|---|
| `min_purchase_amount` | integer (cents) or null | Cart subtotal must be >= this value. Null means no minimum. |
| `applicable_product_ids` | array of integers or null | Discount only applies to lines with these product IDs. Null/empty means all products. |
| `applicable_collection_ids` | array of integers or null | Discount applies to products belonging to these collections. Null/empty means all. |
| `customer_eligibility` | string | `"all"` for all customers. Future expansion: `"returning"`, `"specific"`. |

**Applicability logic:** When both `applicable_product_ids` and `applicable_collection_ids` are set, a product qualifies if it matches **either** condition (union, not intersection). When both are null or empty, the discount applies to the entire order.

### 7.4 Validation Flow

**Service:** `App\Services\DiscountService`

**Method:** `validate(code, cart)` - returns a DiscountValidationResult value object

```mermaid
flowchart TD
    A[Input: discount code + cart] --> B{Code exists in store?
    Case-insensitive lookup}
    B -->|No| C["Error: discount_not_found"]
    B -->|Yes| D{Status = active?}
    D -->|No| E["Error: discount_expired"]
    D -->|Yes| F{starts_at <= now?}
    F -->|No| G["Error: discount_not_yet_active"]
    F -->|Yes| H{ends_at is null OR ends_at >= now?}
    H -->|No| I["Error: discount_expired"]
    H -->|Yes| J{usage_limit set AND usage_count >= usage_limit?}
    J -->|Yes| K["Error: discount_usage_limit_reached"]
    J -->|No| L{min_purchase_amount set AND subtotal < minimum?}
    L -->|Yes| M["Error: discount_min_purchase_not_met"]
    L -->|No| N{Product/collection restrictions set AND no cart lines qualify?}
    N -->|Yes| O["Error: discount_not_applicable"]
    N -->|No| P["Valid - return discount"]
```

### 7.5 Error Codes

| Code | Description |
|---|---|
| `discount_not_found` | Code does not exist in this store |
| `discount_expired` | Past `ends_at` date or status is not active |
| `discount_not_yet_active` | Before `starts_at` date |
| `discount_usage_limit_reached` | `usage_count >= usage_limit` |
| `discount_min_purchase_not_met` | Cart subtotal < `min_purchase_amount` |
| `discount_not_applicable` | No qualifying products in cart |

### 7.6 Discount Allocation

For `percent` and `fixed` discounts with applicable product restrictions:

```
PROCEDURE allocateDiscount(discount, cartLines):
    qualifyingLines = FILTER cartLines where product matches applicable_product_ids
                      OR product belongs to applicable_collection_ids
    qualifyingSubtotal = SUM(qualifyingLines.line_subtotal_amount)


type: thinking

thinking: Phase 1 agent is still running. Let me continue reading the rest of the business logic spec and other specs to prepare for subsequent phases.


text: Let me continue reading the remaining specs to prepare for the next phases.