content:
   500→| stop_words_json | TEXT | No | '[]' | - | JSON array of stop words |
   501→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   502→
   503→---
   504→
   505→### search_queries
   506→
   507→Search query log for analytics and autocomplete.
   508→
   509→| Column | Type | Nullable | Default | Constraints | Notes |
   510→|---|---|---|---|---|---|
   511→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   512→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
   513→| query | TEXT | No | - | - | Search query text |
   514→| filters_json | TEXT | Yes | NULL | - | JSON object of applied filters |
   515→| results_count | INTEGER | No | 0 | - | Number of results returned |
   516→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   517→
   518→**Indexes:**
   519→
   520→- `idx_search_queries_store_id` on (`store_id`) - FK lookup
   521→- `idx_search_queries_store_created` on (`store_id`, `created_at`) - Time-range queries per store
   522→- `idx_search_queries_store_query` on (`store_id`, `query`) - Autocomplete and popular search lookups
   523→
   524→---
   525→
   526→## Epic 5 - Cart, Checkout, Discounts, Shipping, and Taxes
   527→
   528→### carts
   529→
   530→Shopping cart, optionally linked to a customer.
   531→
   532→| Column | Type | Nullable | Default | Constraints | Notes |
   533→|---|---|---|---|---|---|
   534→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   535→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
   536→| customer_id | INTEGER | Yes | NULL | FK -> customers(id) ON DELETE SET NULL | Nullable for guest carts |
   537→| currency | TEXT | No | 'USD' | - | ISO 4217 currency code |
   538→| cart_version | INTEGER | No | 1 | - | Optimistic concurrency version counter |
   539→| status | TEXT | No | 'active' | CHECK IN ('active', 'converted', 'abandoned') | |
   540→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   541→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   542→
   543→**Indexes:**
   544→
   545→- `idx_carts_store_id` on (`store_id`) - FK lookup
   546→- `idx_carts_customer_id` on (`customer_id`) - Finding carts by customer
   547→- `idx_carts_store_status` on (`store_id`, `status`) - Filtering carts by status (e.g. abandoned cart recovery)
   548→
   549→**Notes:** `cart_version` is incremented on every mutation and used for optimistic concurrency control.
   550→
   551→---
   552→
   553→### cart_lines
   554→
   555→Individual line items within a cart.
   556→
   557→| Column | Type | Nullable | Default | Constraints | Notes |
   558→|---|---|---|---|---|---|
   559→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   560→| cart_id | INTEGER | No | - | FK -> carts(id) ON DELETE CASCADE | Parent cart |
   561→| variant_id | INTEGER | No | - | FK -> product_variants(id) ON DELETE CASCADE | Variant being purchased |
   562→| quantity | INTEGER | No | 1 | - | Number of units |
   563→| unit_price_amount | INTEGER | No | 0 | - | Price per unit in minor units |
   564→| line_subtotal_amount | INTEGER | No | 0 | - | unit_price * quantity |
   565→| line_discount_amount | INTEGER | No | 0 | - | Discount applied to this line |
   566→| line_total_amount | INTEGER | No | 0 | - | subtotal - discount |
   567→
   568→**Indexes:**
   569→
   570→- `idx_cart_lines_cart_id` on (`cart_id`) - FK lookup, listing lines per cart
   571→- `idx_cart_lines_cart_variant` UNIQUE on (`cart_id`, `variant_id`) - Prevents duplicate variants in the same cart
   572→
   573→**Notes:** All `_amount` columns are in minor units (cents). `line_subtotal_amount = unit_price_amount * quantity`. `line_total_amount = line_subtotal_amount - line_discount_amount`.
   574→
   575→---
   576→
   577→### checkouts
   578→
   579→Checkout session progressing through address, shipping, payment steps.
   580→
   581→| Column | Type | Nullable | Default | Constraints | Notes |
   582→|---|---|---|---|---|---|
   583→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   584→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
   585→| cart_id | INTEGER | No | - | FK -> carts(id) ON DELETE CASCADE | Source cart |
   586→| customer_id | INTEGER | Yes | NULL | FK -> customers(id) ON DELETE SET NULL | Nullable for guest checkout |
   587→| status | TEXT | No | 'started' | CHECK IN ('started', 'addressed', 'shipping_selected', 'payment_selected', 'completed', 'expired') | |
   588→| payment_method | TEXT | Yes | NULL | CHECK IN ('credit_card', 'paypal', 'bank_transfer') | Selected payment method |
   589→| email | TEXT | Yes | NULL | - | Contact email |
   590→| shipping_address_json | TEXT | Yes | NULL | - | JSON address object |
   591→| billing_address_json | TEXT | Yes | NULL | - | JSON address object |
   592→| shipping_method_id | INTEGER | Yes | NULL | - | Selected shipping rate |
   593→| discount_code | TEXT | Yes | NULL | - | Applied discount code |
   594→| tax_provider_snapshot_json | TEXT | Yes | NULL | - | Full tax calculation response for audit |
   595→| totals_json | TEXT | Yes | NULL | - | Snapshot: subtotal, discount, shipping, tax, total |
   596→| expires_at | TEXT | Yes | NULL | - | ISO-8601 expiration timestamp |
   597→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   598→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   599→
   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 |

prompt: Read the file /Users/fabianwesner/Herd/shop/specs/01-DATABASE-SCHEMA.md in chunks (500 lines at a time) and provide a comprehensive summary of ALL table definitions including: table names, column names with types, foreign keys, indexes, constraints, and enums. I need every detail for implementation.


userType: external

slug: iterative-seeking-pine

type: progress

type: assistant

type: message

role: assistant

service_tier: standard

prompt: Read the file /Users/fabianwesner/Herd/shop/specs/06-AUTH-AND-SECURITY.md in chunks (500 lines at a time) and provide a comprehensive summary of ALL auth and security specifications including: guards, providers, middleware, rate limiting, permission matrices, CSRF, and session management. I need every detail for implementation.


type: user

role: user

content:
     1→# 06 - Authentication and Security Specification
     2→
     3→> E-commerce platform - Auth, authorization, and security controls for Laravel 12 + Livewire v4 + SQLite
     4→
     5→---
     6→
     7→## 1. Authentication
     8→
     9→The platform supports three distinct authentication mechanisms: session-based admin auth, session-based customer auth (store-scoped), and token-based API auth via Sanctum.
    10→
    11→### 1.1 Admin Authentication
    12→
    13→| Setting | Value |
    14→|---------|-------|
    15→| Guard name | `web` (default Laravel guard) |
    16→| Provider name | `users` (Eloquent driver) |
    17→| Model | `App\Models\User` |
    18→| Driver | Session (cookie-based) |
    19→| Login route | `GET /admin/login` |
    20→| Login form | Livewire component `Admin\Auth\Login` |
    21→
    22→#### Admin Login Flow
    23→
    24→```mermaid
    25→sequenceDiagram
    26→    participant B as Browser
    27→    participant LW as Livewire Component
    28→    participant Auth as Auth Guard (web)
    29→    participant DB as Database
    30→    participant S as Session
    31→
    32→    B->>LW: Submit email + password
    33→    LW->>LW: Validate input (email required, password required)
    34→    LW->>LW: Check rate limiter (5/min per IP)
    35→    alt Rate limit exceeded
    36→        LW-->>B: Flash "Too many attempts. Try again in X seconds."
    37→    end
    38→    LW->>Auth: attempt(credentials, remember)
    39→    Auth->>DB: SELECT from users WHERE email = ?
    40→    Auth->>Auth: Verify bcrypt hash
    41→    alt Credentials valid
    42→        Auth->>S: Regenerate session ID (prevent fixation)
    43→        Auth-->>LW: Success
    44→        LW-->>B: Redirect to /admin (dashboard)
    45→    else Credentials invalid
    46→        Auth-->>LW: Failure
    47→        LW-->>B: Flash "Invalid credentials" (generic, never reveal which field)
    48→    end
    49→```
    50→
    51→**Requirements:**
    52→
    53→- The login form collects `email` and `password`.
    54→- On successful login, the session MUST be regenerated to prevent session fixation attacks.
    55→- On failed login, only the generic message "Invalid credentials" is shown. The system must not indicate whether the email or password was wrong.
    56→- Rate limiting: maximum 5 attempts per minute, keyed by IP address. Uses the `login` rate limiter.
    57→- After login: redirect to `/admin` (the dashboard).
    58→
    59→#### Remember Me
    60→
    61→- An optional "Remember me" checkbox on the login form.
    62→- When enabled, Laravel sets a long-lived remember token cookie.
    63→- The `remember_token` column on the `users` table stores the hashed token.
    64→- Cookie name follows Laravel default convention: `remember_web_{hash}`.
    65→
    66→#### Admin Password Reset Flow
    67→
    68→```mermaid
    69→sequenceDiagram
    70→    participant B as Browser
    71→    participant App as Application
    72→    participant DB as Database
    73→    participant Mail as Mail Service
    74→
    75→    B->>App: GET /admin/forgot-password
    76→    App-->>B: Render forgot-password form
    77→    B->>App: POST /admin/forgot-password (email)
    78→    App->>DB: Look up user by email
    79→    alt User exists
    80→        App->>DB: Store reset token in password_reset_tokens table
    81→        App->>Mail: Send reset link email
    82→    end
    83→    App-->>B: "If that email exists, we sent a reset link." (generic)
    84→    B->>App: GET /admin/reset-password/{token}
    85→    App-->>B: Render reset-password form
    86→    B->>App: POST /admin/reset-password (token, email, password, password_confirmation)
    87→    App->>DB: Validate token (exists, not expired, matches email)
    88→    alt Valid token
    89→        App->>DB: Update user password (bcrypt hash)
    90→        App->>DB: Delete used token
    91→        App-->>B: Redirect to /admin/login with success flash
    92→    else Invalid/expired token
    93→        App-->>B: Error message
    94→    end
    95→```
    96→
    97→**Requirements:**
    98→
    99→- Uses Laravel's built-in password broker (`users` broker).
   100→- Reset tokens stored in the `password_reset_tokens` table.
   101→- Token expiration: 60 minutes (configurable in `config/auth.php` under `passwords.users.expire`).
   102→- Throttle: one reset email per 60 seconds per email address.
   103→- The "forgot password" response must be generic (do not reveal whether email exists).
   104→
   105→#### Admin Logout
   106→
   107→- Route: `POST /admin/logout` (via Livewire action or standard route).
   108→- Steps: invalidate the entire session, regenerate the CSRF token, redirect to `/admin/login`.
   109→- The response must not cache.
   110→
   111→#### Session Lifetime
   112→
   113→| Setting | Value |
   114→|---------|-------|
   115→| Lifetime | 120 minutes (configurable via `SESSION_LIFETIME` env var) |
   116→| Expire on close | No (session persists until lifetime expires) |
   117→
   118→---
   119→
   120→### 1.2 Customer Authentication
   121→
   122→| Setting | Value |
   123→|---------|-------|
   124→| Guard name | `customer` (custom guard) |
   125→| Provider name | `customers` (Eloquent driver) |
   126→| Model | `App\Models\Customer` |
   127→| Driver | Session (cookie-based) |
   128→| Login route | `GET /account/login` |
   129→| Registration route | `GET /account/register` |
   130→
   131→#### Customer Login Flow
   132→
   133→```mermaid
   134→sequenceDiagram
   135→    participant B as Browser
   136→    participant LW as Livewire Component
   137→    participant Auth as Auth Guard (customer)
   138→    participant Store as ResolveStore Middleware
   139→    participant DB as Database
   140→
   141→    Note over Store: Store already resolved from hostname
   142→    B->>LW: Submit email + password
   143→    LW->>LW: Validate input
   144→    LW->>LW: Check rate limiter (5/min per IP)
   145→    LW->>Auth: attempt(credentials + store_id, remember)
   146→    Auth->>DB: SELECT from customers WHERE email = ? AND store_id = ?
   147→    Auth->>Auth: Verify bcrypt hash
   148→    alt Valid
   149→        Auth-->>LW: Success (regenerate session)
   150→        LW-->>B: Redirect to /account (or intended URL)
   151→    else Invalid
   152→        Auth-->>LW: Failure
   153→        LW-->>B: Flash "Invalid credentials"
   154→    end
   155→```
   156→
   157→**Requirements:**
   158→
   159→- Customer login is always scoped to the current store. The store is resolved from the request hostname by the `ResolveStore` middleware before the login attempt.
   160→- The custom `CustomerUserProvider` must inject `store_id` into the credential check so the query filters by both `email` and `store_id`.
   161→- The same email address can exist in different stores (multi-tenant isolation).
   162→- Rate limiting: maximum 5 attempts per minute per IP, using the same `login` rate limiter.
   163→- After login: redirect to `/account` or the intended URL (if the customer was redirected from a protected page).
   164→
   165→#### Customer Registration Flow
   166→
   167→```mermaid
   168→flowchart TD
   169→    A[Customer visits /account/register] --> B[Fill form: name, email, password, password_confirmation, marketing_opt_in]
   170→    B --> C{Validate via RegisterCustomerRequest}
   171→    C -- Invalid --> D[Show validation errors]
   172→    C -- Valid --> E[Create Customer record scoped to current store]
   173→    E --> F[Auto-login via customer guard]
   174→    F --> G[Redirect to /account]
   175→```
   176→
   177→**Registration fields:**
   178→
   179→| Field | Type | Required | Validation |
   180→|-------|------|:--------:|------------|
   181→| name | string | Y | max:255 |
   182→| email | string | Y | valid email, unique per store (composite unique on email + store_id) |
   183→| password | string | Y | min:8, confirmed |
   184→| password_confirmation | string | Y | must match password |
   185→| marketing_opt_in | boolean | N | defaults to false |
   186→
   187→#### Customer Store Scoping
   188→
   189→- The `customers` auth provider requires a custom `CustomerUserProvider` class.
   190→- This provider overrides the default Eloquent provider to always include `store_id` in credential queries.
   191→- The `store_id` is obtained from the container-bound current store (resolved by `ResolveStore` middleware).
   192→- This ensures that `Auth::guard('customer')->attempt()` never crosses store boundaries.
   193→
   194→#### Customer Password Reset
   195→
   196→- Uses a separate password broker: `customers` broker.
   197→- Tokens stored in the `customer_password_reset_tokens` table (separate from admin tokens).
   198→- Token records include `store_id` to scope resets to the correct store.
   199→- Routes: `GET /forgot-password`, `POST /forgot-password`, `GET /reset-password/{token}`, `POST /reset-password`.
   200→- Token expiration: 60 minutes.
   201→- Throttle: one reset email per 60 seconds.
   202→- Generic response on the "forgot password" form (do not reveal whether email exists in the store).
   203→
   204→---
   205→
   206→### 1.3 API Authentication (Sanctum)
   207→
   208→| Setting | Value |
   209→|---------|-------|
   210→| Guard name | `sanctum` |
   211→| Provider | `users` (admin users only) |
   212→| Token type | Personal access tokens |
   213→| Token prefix | `shop_` |
   214→| Hashing | SHA-256 |
   215→| Default expiration | 1 year |
   216→| Storage | `personal_access_tokens` table |
   217→
   218→#### Token Lifecycle
   219→
   220→```mermaid
   221→stateDiagram-v2
   222→    [*] --> Created: Admin creates token in Developers section
   223→    Created --> Active: Plain-text shown once, hash stored
   224→    Active --> InUse: Token sent as Bearer header
   225→    InUse --> Active: Request processed
   226→    Active --> Revoked: Admin revokes token
   227→    Active --> Expired: Expiration date reached
   228→    Revoked --> [*]: Token row deleted
   229→    Expired --> [*]: Token row deleted (cleanup job)
   230→```
   231→
   232→**Requirements:**
   233→
   234→- Tokens are created in the admin panel, under the Developers section.
   235→- Each token has: a human-readable name, an optional expiration date, and a list of abilities (scopes).
   236→- On creation, the plain-text token (prefixed with `shop_`) is displayed exactly once. Only the SHA-256 hash is persisted.
   237→- Tokens are sent as `Authorization: Bearer shop_xxxxxxxx`.
   238→- Sanctum middleware validates the hash and resolves the associated user.
   239→- Ability checks are performed per-endpoint (e.g., the products list endpoint requires `read-products`).
   240→- Revoking a token deletes its row from the `personal_access_tokens` table.
   241→
   242→#### Token Abilities (Scopes)
   243→
   244→| Ability | Description |
   245→|---------|-------------|
   246→| `read-products` | List and view products |
   247→| `write-products` | Create, update, delete products |
   248→| `read-orders` | List and view orders |
   249→| `write-orders` | Update orders, create fulfillments |
   250→| `read-customers` | List and view customers |
   251→| `write-customers` | Update customers |
   252→| `read-collections` | List and view collections |
   253→| `write-collections` | Create, update, delete collections |
   254→| `read-discounts` | List and view discounts |
   255→| `write-discounts` | Create, update, delete discounts |
   256→| `read-analytics` | View analytics data |
   257→| `read-settings` | View store settings (general, shipping, taxes, checkout, notifications) |
   258→| `write-settings` | Update store settings (replaces the legacy `manage-store`, `manage-shipping`, `manage-taxes` scopes) |
   259→| `read-themes` | View themes and theme files |
   260→| `write-themes` | Create, update, delete, publish themes |
   261→| `read-content` | View pages and navigation menus |
   262→| `write-content` | Create, update, delete pages and navigation items |
   263→| `manage-platform` | Platform-level management (super-admin only) |
   264→
   265→Each API endpoint must check the token's abilities before processing the request. If the token lacks the required ability, the endpoint returns `403 Forbidden`.
   266→
   267→---
   268→
   269→### 1.4 Guard Configuration
   270→
   271→File to configure: `config/auth.php`
   272→
   273→#### Guards Data Dictionary
   274→
   275→| Guard Name | Driver | Provider | Purpose |
   276→|------------|--------|----------|---------|
   277→| `web` | session | `users` | Admin panel session auth (default guard) |
   278→| `customer` | session | `customers` | Storefront customer session auth |
   279→| `sanctum` | sanctum | `users` | Admin REST API token auth |
   280→
   281→#### Providers Data Dictionary
   282→
   283→| Provider Name | Driver | Model | Purpose |
   284→|---------------|--------|-------|---------|
   285→| `users` | eloquent | `App\Models\User` | Admin users |
   286→| `customers` | eloquent | `App\Models\Customer` | Storefront customers (store-scoped via custom provider) |
   287→
   288→#### Password Brokers Data Dictionary
   289→
   290→| Broker Name | Provider | Token Table | Expiry | Throttle |
   291→|-------------|----------|-------------|--------|----------|
   292→| `users` | `users` | `password_reset_tokens` | 60 min | 60 sec |
   293→| `customers` | `customers` | `customer_password_reset_tokens` | 60 min | 60 sec |
   294→
   295→**Defaults:** The default guard is `web` and the default password broker is `users`.
   296→
   297→---
   298→
   299→## 2. Authorization
   300→
   301→### 2.1 Role Enum
   302→
   303→File: `app/Enums/StoreUserRole.php`
   304→
   305→A backed string enum with four values:
   306→
   307→| Case | Stored Value | Description |
   308→|------|-------------|-------------|
   309→| Owner | `'owner'` | Full access. Can transfer ownership, manage billing, delete store. One per store. |
   310→| Admin | `'admin'` | Near-full access. Cannot transfer ownership or delete store. |
   311→| Staff | `'staff'` | Operational access. Can manage products, orders, collections, discounts, pages. Cannot access settings, themes, staff management, or developers. |
   312→| Support | `'support'` | Read-only access to products, orders, and customers. For customer service agents. |
   313→
   314→**Constraints:**
   315→
   316→- Each store must have exactly one Owner.
   317→- Ownership transfer is a dedicated action (not a simple role change).
   318→- A User can hold different roles in different stores (via the `store_users` pivot table).
   319→
   320→---
   321→
   322→### 2.2 Permission Matrix
   323→
   324→The `store_users` pivot table links a `User` to a `Store` with a `role`. A user can have different roles in different stores.
   325→
   326→| Resource | Action | Owner | Admin | Staff | Support |
   327→|----------|--------|:-----:|:-----:|:-----:|:-------:|
   328→| Store Settings | View | Y | Y | N | N |
   329→| Store Settings | Update | Y | Y | N | N |
   330→| Store | Delete | Y | N | N | N |
   331→| Products | List / View | Y | Y | Y | Y |
   332→| Products | Create | Y | Y | Y | N |
   333→| Products | Update | Y | Y | Y | N |
   334→| Products | Delete / Archive | Y | Y | N | N |
   335→| Products | Restore | Y | Y | N | N |
   336→| Collections | List / View | Y | Y | Y | Y |
   337→| Collections | Create | Y | Y | Y | N |
   338→| Collections | Update | Y | Y | Y | N |
   339→| Collections | Delete | Y | Y | N | N |
   340→| Orders | List / View | Y | Y | Y | Y |
   341→| Orders | Update | Y | Y | Y | N |
   342→| Orders | Cancel | Y | Y | N | N |
   343→| Fulfillments | Create | Y | Y | Y | N |
   344→| Fulfillments | Update / Cancel | Y | Y | Y | N |
   345→| Refunds | Create | Y | Y | N | N |
   346→| Customers | List / View | Y | Y | Y | Y |
   347→| Customers | Update | Y | Y | Y | N |
   348→| Discounts | List / View | Y | Y | Y | Y |
   349→| Discounts | Create | Y | Y | Y | N |
   350→| Discounts | Update | Y | Y | Y | N |
   351→| Discounts | Delete | Y | Y | N | N |
   352→| Themes | List / View | Y | Y | N | N |
   353→| Themes | Create / Upload | Y | Y | N | N |
   354→| Themes | Customize | Y | Y | N | N |
   355→| Themes | Publish | Y | Y | N | N |
   356→| Themes | Delete | Y | Y | N | N |
   357→| Pages | List / View | Y | Y | Y | N |
   358→| Pages | Create | Y | Y | Y | N |
   359→| Pages | Update | Y | Y | Y | N |
   360→| Pages | Delete | Y | Y | N | N |
   361→| Navigation | List / View | Y | Y | Y | N |
   362→| Navigation | Manage | Y | Y | N | N |
   363→| Apps | List / View | Y | Y | N | N |
   364→| Apps | Install / Remove | Y | Y | N | N |
   365→| Analytics | View | Y | Y | Y | N |
   366→| Developers / API | Manage | Y | Y | N | N |
   367→| Staff | List / View | Y | Y | N | N |
   368→| Staff | Invite | Y | Y | N | N |
   369→| Staff | Update Role | Y | Y | N | N |
   370→| Staff | Remove | Y | Y | N | N |
   371→| Shipping Zones | Manage | Y | Y | N | N |
   372→| Tax Settings | Manage | Y | Y | N | N |
   373→| Search Settings | Manage | Y | Y | N | N |
   374→
   375→#### Role Group Shorthand
   376→
   377→The permission matrix reveals three natural groupings that the authorization layer should use as reusable helpers:
   378→
   379→| Group Name | Roles Included | Used For |
   380→|------------|---------------|----------|
   381→| OwnerOnly | Owner | Store deletion, ownership transfer |
   382→| OwnerOrAdmin | Owner, Admin | Settings, themes, staff, developers, navigation, apps, shipping, taxes, search, delete/archive actions |
   383→| OwnerAdminOrStaff | Owner, Admin, Staff | Create/update products, collections, discounts, pages, fulfillments, order updates |
   384→| AnyRole | Owner, Admin, Staff, Support | Read-only listing/viewing of products, orders, customers, collections, discounts |
   385→
   386→---
   387→
   388→### 2.3 Role Checking Trait
   389→
   390→File: `app/Traits/ChecksStoreRole.php`
   391→
   392→A reusable trait included in all policies and used by gate definitions. Must provide the following methods:
   393→
   394→| Method | Parameters | Returns | Description |
   395→|--------|-----------|---------|-------------|
   396→| `getStoreRole` | User, store_id (int) | StoreUserRole or null | Looks up the user's role for the given store via the `store_users` relationship. Returns null if the user has no role in that store. |
   397→| `hasRole` | User, store_id (int), roles (array of StoreUserRole) | bool | Returns true if the user's role for the store is in the provided list. Returns false if user has no role. |
   398→| `isOwnerOrAdmin` | User, store_id (int) | bool | Shorthand: checks if role is Owner or Admin. |
   399→| `isOwnerAdminOrStaff` | User, store_id (int) | bool | Shorthand: checks if role is Owner, Admin, or Staff. |
   400→| `isAnyRole` | User, store_id (int) | bool | Returns true if the user has any role at all in the store. |
   401→
   402→**Store ID resolution:** When the policy method receives a model instance (e.g., Product), the store_id comes from the model's `store_id` attribute. When the policy method does not receive a model (e.g., `viewAny`, `create`), the store_id is resolved from the container-bound `current_store`.
   403→
   404→---
   405→
   406→### 2.4 Laravel Policies
   407→
   408→All policies use the `ChecksStoreRole` trait. Laravel 12 auto-discovers policies by convention (`App\Policies\{Model}Policy`), so no manual registration is needed.
   409→
   410→Every policy method follows this pattern:
   411→1. Determine the `store_id` (from the model if available, otherwise from the resolved current store).
   412→2. Use the appropriate role-checking helper method.
   413→3. Return a boolean.
   414→
   415→#### ProductPolicy
   416→
   417→File: `app/Policies/ProductPolicy.php`
   418→
   419→| Method | Receives Model? | Required Roles | Store ID Source |
   420→|--------|:--------------:|----------------|-----------------|
   421→| `viewAny(User)` | No | AnyRole | current_store |
   422→| `view(User, Product)` | Yes | AnyRole | product.store_id |
   423→| `create(User)` | No | OwnerAdminOrStaff | current_store |
   424→| `update(User, Product)` | Yes | OwnerAdminOrStaff | product.store_id |
   425→| `delete(User, Product)` | Yes | OwnerOrAdmin | product.store_id |
   426→| `archive(User, Product)` | Yes | OwnerOrAdmin | product.store_id |
   427→| `restore(User, Product)` | Yes | OwnerOrAdmin | product.store_id |
   428→
   429→#### OrderPolicy
   430→
   431→File: `app/Policies/OrderPolicy.php`
   432→
   433→| Method | Receives Model? | Required Roles | Store ID Source |
   434→|--------|:--------------:|----------------|-----------------|
   435→| `viewAny(User)` | No | AnyRole | current_store |
   436→| `view(User, Order)` | Yes | AnyRole | order.store_id |
   437→| `update(User, Order)` | Yes | OwnerAdminOrStaff | order.store_id |
   438→| `cancel(User, Order)` | Yes | OwnerOrAdmin | order.store_id |
   439→| `createFulfillment(User, Order)` | Yes | OwnerAdminOrStaff | order.store_id |
   440→| `createRefund(User, Order)` | Yes | OwnerOrAdmin | order.store_id |
   441→
   442→#### CollectionPolicy
   443→
   444→File: `app/Policies/CollectionPolicy.php`
   445→
   446→| Method | Receives Model? | Required Roles | Store ID Source |
   447→|--------|:--------------:|----------------|-----------------|
   448→| `viewAny(User)` | No | AnyRole | current_store |
   449→| `view(User, Collection)` | Yes | AnyRole | collection.store_id |
   450→| `create(User)` | No | OwnerAdminOrStaff | current_store |
   451→| `update(User, Collection)` | Yes | OwnerAdminOrStaff | collection.store_id |
   452→| `delete(User, Collection)` | Yes | OwnerOrAdmin | collection.store_id |
   453→
   454→#### DiscountPolicy
   455→
   456→File: `app/Policies/DiscountPolicy.php`
   457→
   458→| Method | Receives Model? | Required Roles | Store ID Source |
   459→|--------|:--------------:|----------------|-----------------|
   460→| `viewAny(User)` | No | AnyRole | current_store |
   461→| `view(User, Discount)` | Yes | AnyRole | discount.store_id |
   462→| `create(User)` | No | OwnerAdminOrStaff | current_store |
   463→| `update(User, Discount)` | Yes | OwnerAdminOrStaff | discount.store_id |
   464→| `delete(User, Discount)` | Yes | OwnerOrAdmin | discount.store_id |
   465→
   466→#### CustomerPolicy
   467→
   468→File: `app/Policies/CustomerPolicy.php`
   469→
   470→| Method | Receives Model? | Required Roles | Store ID Source |
   471→|--------|:--------------:|----------------|-----------------|
   472→| `viewAny(User)` | No | AnyRole | current_store |
   473→| `view(User, Customer)` | Yes | AnyRole | customer.store_id |
   474→| `update(User, Customer)` | Yes | OwnerAdminOrStaff | customer.store_id |
   475→
   476→#### ThemePolicy
   477→
   478→File: `app/Policies/ThemePolicy.php`
   479→
   480→| Method | Receives Model? | Required Roles | Store ID Source |
   481→|--------|:--------------:|----------------|-----------------|
   482→| `viewAny(User)` | No | OwnerOrAdmin | current_store |
   483→| `view(User, Theme)` | Yes | OwnerOrAdmin | theme.store_id |
   484→| `create(User)` | No | OwnerOrAdmin | current_store |
   485→| `update(User, Theme)` | Yes | OwnerOrAdmin | theme.store_id |
   486→| `delete(User, Theme)` | Yes | OwnerOrAdmin | theme.store_id |
   487→| `publish(User, Theme)` | Yes | OwnerOrAdmin | theme.store_id |
   488→
   489→#### PagePolicy
   490→
   491→File: `app/Policies/PagePolicy.php`
   492→
   493→| Method | Receives Model? | Required Roles | Store ID Source |
   494→|--------|:--------------:|----------------|-----------------|
   495→| `viewAny(User)` | No | OwnerAdminOrStaff | current_store |
   496→| `view(User, Page)` | Yes | OwnerAdminOrStaff | page.store_id |
   497→| `create(User)` | No | OwnerAdminOrStaff | current_store |
   498→| `update(User, Page)` | Yes | OwnerAdminOrStaff | page.store_id |
   499→| `delete(User, Page)` | Yes | OwnerOrAdmin | page.store_id |
   500→

prompt: Read the file /Users/fabianwesner/Herd/shop/specs/06-AUTH-AND-SECURITY.md in chunks (500 lines at a time) and provide a comprehensive summary of ALL auth and security specifications including: guards, providers, middleware, rate limiting, permission matrices, CSRF, and session management. I need every detail for implementation.


prompt: Read the file /Users/fabianwesner/Herd/shop/specs/05-BUSINESS-LOGIC.md in chunks (500 lines at a time) and provide a comprehensive summary of ALL business logic rules including: services, state machines, validation rules, pricing calculations, inventory management, payment processing, and fulfillment rules. I need every detail for implementation.


content:
   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→
   700→    IF discount.value_type = 'percent':
   701→        totalDiscount = qualifyingSubtotal * discount.value_amount / 100
   702→    ELSE IF discount.value_type = 'fixed':
   703→        totalDiscount = MIN(discount.value_amount, qualifyingSubtotal)
   704→
   705→    remainingDiscount = totalDiscount
   706→
   707→    FOR EACH qualifyingLine (except last):
   708→        lineDiscount = ROUND(totalDiscount * lineSubtotal / qualifyingSubtotal)
   709→        remainingDiscount -= lineDiscount
   710→        SET line.line_discount_amount = lineDiscount
   711→
   712→    // Last line gets the remainder (largest-remainder method)
   713→    SET lastLine.line_discount_amount = remainingDiscount
   714→
   715→    RECALCULATE line_total_amount for each affected line
   716→```
   717→
   718→**Storage of allocations:**
   719→- During checkout: update `cart_lines.line_discount_amount`.
   720→- On order creation: store in `order_lines.discount_allocations_json` as an array of `{discount_id, amount}`.
   721→
   722→### 7.7 Usage Tracking
   723→
   724→When an order is successfully created with a discount code:
   725→- Increment `discounts.usage_count` by 1.
   726→- This happens within the order creation transaction (see Section 6.2, completeCheckout procedure, step 7).
   727→
   728→---
   729→
   730→## 8. Tax Calculation
   731→
   732→### 8.1 Tax Provider Architecture
   733→
   734→**Interface:** `App\Contracts\TaxProvider`
   735→
   736→- Must implement a `calculate(request)` method that accepts a TaxCalculationRequest and returns a TaxCalculationResult.
   737→- The TaxCalculationRequest contains: line items (with discounted amounts), shipping amount, shipping address, store tax settings.
   738→- The TaxCalculationResult contains: array of TaxLine value objects, total tax amount.
   739→
   740→**Service:** `App\Services\TaxCalculator` - orchestrates provider selection and delegates to the appropriate implementation.
   741→
   742→**Implementations:**
   743→- `App\Services\Tax\ManualTaxProvider` - default, uses manually configured rates
   744→- `App\Services\Tax\StripeTaxProvider` - stub for Stripe Tax API integration
   745→
   746→### 8.2 Manual Tax Mode
   747→
   748→**Rate resolution:**
   749→1. Match shipping address against shipping zones (same zone matching algorithm as shipping - see Section 9.1).
   750→2. Use the tax rate defined in `tax_settings.config_json` for the matched zone, or the global default rate.
   751→3. Rate stored as integer basis points (e.g., 1900 = 19.00%).
   752→
   753→**Calculation per line:**
   754→
   755→```
   756→line_tax = ROUND(line_total_after_discount * rate / 10000)
   757→```
   758→
   759→Round each line's tax independently, then sum for the total tax amount.
   760→
   761→### 8.3 Prices Include Tax
   762→
   763→Controlled by `tax_settings.prices_include_tax` (boolean).
   764→
   765→#### Tax-inclusive pricing (prices_include_tax = true)
   766→
   767→- Displayed prices on the storefront are gross (include tax).
   768→- Subtotal shown to customer is gross.
   769→- Tax is "extracted" from the gross amount:
   770→
   771→```
   772→net_amount = intdiv(gross_amount * 10000, 10000 + rate_bps)
   773→tax = gross_amount - net_amount
   774→```
   775→
   776→> **Note:** Integer division (truncation toward zero) is used rather than rounding to ensure deterministic tax extraction. This matches PHP's `intdiv()` function.
   777→
   778→**Example:** Price = 1190 cents, rate = 1900 (19%).
   779→- net = intdiv(1190 * 10000, 11900) = intdiv(11900000, 11900) = 1000
   780→- tax = 1190 - 1000 = 190
   781→
   782→#### Tax-exclusive pricing (prices_include_tax = false)
   783→
   784→- Displayed prices are net (excluding tax).
   785→- Tax added on top:
   786→
   787→```
   788→tax = ROUND(net_amount * rate / 10000)
   789→total = net_amount + tax
   790→```
   791→
   792→**Example:** Price = 1000 cents, rate = 1900.
   793→- tax = ROUND(1000 * 1900 / 10000) = 190
   794→- total = 1190
   795→
   796→### 8.4 Stripe Tax Provider (Stub)
   797→
   798→**Implementation:** `App\Services\Tax\StripeTaxProvider`
   799→
   800→- Sends line items and shipping address to Stripe Tax API.
   801→- Stores the full response in `checkouts.tax_provider_snapshot_json` for audit.
   802→- Fallback behavior (configurable per store in `tax_settings.config_json`):
   803→
   804→| Fallback Setting | Behavior |
   805→|---|---|
   806→| `"block"` | Block checkout progression if Stripe Tax API errors |
   807→| `"allow"` | Allow checkout to proceed without tax (log a warning) |
   808→
   809→### 8.5 Discount-Before-Tax Semantics
   810→
   811→Discounts are always applied before tax calculation. Tax is calculated on the discounted amount. This aligns with standard accounting practice and Stripe Tax behavior.
   812→
   813→For tax-inclusive pricing (`prices_include_tax = true`): The discount is subtracted from the gross (tax-inclusive) subtotal. Tax is then extracted from the post-discount gross amount: `tax = post_discount_gross * rate / (1 + rate)`. This means the tax portion decreases proportionally when a discount is applied.
   814→
   815→**Calculation order within the pricing pipeline:**
   816→1. Calculate line subtotals.
   817→2. Apply discount allocations to lines.
   818→3. Calculate tax on discounted line amounts.
   819→4. Sum everything for the total.
   820→
   821→---
   822→
   823→## 9. Shipping Calculation
   824→
   825→### 9.1 Zone Matching
   826→
   827→**Service:** `App\Services\ShippingCalculator`
   828→
   829→**Zone matching algorithm:**
   830→
   831→```
   832→PROCEDURE getMatchingZone(address, store):
   833→    zones = load all shipping zones for store
   834→
   835→    bestMatch = null
   836→    bestSpecificity = -1
   837→
   838→    FOR EACH zone IN zones:
   839→        countryMatch = address.country_code IN zone.countries_json
   840→        regionMatch = address.province_code IN zone.regions_json
   841→
   842→        IF countryMatch AND regionMatch:
   843→            specificity = 2  // Most specific
   844→        ELSE IF countryMatch:
   845→            specificity = 1  // Country-only match
   846→        ELSE:
   847→            CONTINUE  // No match
   848→
   849→        IF specificity > bestSpecificity:
   850→            bestMatch = zone
   851→            bestSpecificity = specificity
   852→        ELSE IF specificity = bestSpecificity AND zone.id < bestMatch.id:
   853→            bestMatch = zone  // Tie-break: lowest ID wins
   854→
   855→    RETURN bestMatch  // null if no zone matches
   856→```
   857→
   858→**When no zone matches:** The address is unserviceable. Block checkout progression with error "Cannot ship to this address".
   859→
   860→### 9.2 Rate Types
   861→
   862→For the matched zone, return all active shipping rates (`is_active = 1`). Calculate cost for each:
   863→
   864→#### Flat rate
   865→
   866→Config structure: `{"amount": 500}`
   867→
   868→- Cost = fixed amount regardless of cart contents.
   869→
   870→#### Weight-based rate
   871→
   872→Config structure: `{"ranges": [{"min_g": 0, "max_g": 1000, "amount": 500}, {"min_g": 1001, "max_g": 5000, "amount": 1000}]}`
   873→
   874→```
   875→PROCEDURE calculateWeightRate(config, cart):
   876→    totalWeight = 0
   877→    FOR EACH line IN cart.lines:
   878→        IF line.variant.requires_shipping = true:
   879→            totalWeight += line.variant.weight_g * line.quantity
   880→
   881→    FOR EACH range IN config.ranges:
   882→        IF range.min_g <= totalWeight AND totalWeight <= range.max_g:
   883→            RETURN range.amount
   884→
   885→    RETURN null  // Rate not available for this weight
   886→```
   887→
   888→#### Price-based rate
   889→
   890→Config structure: `{"ranges": [{"min_amount": 0, "max_amount": 5000, "amount": 500}, {"min_amount": 5001, "amount": 0}]}`
   891→
   892→```
   893→PROCEDURE calculatePriceRate(config, cartSubtotal):
   894→    FOR EACH range IN config.ranges:
   895→        IF range.min_amount <= cartSubtotal:
   896→            IF range.max_amount IS NOT SET OR cartSubtotal <= range.max_amount:
   897→                RETURN range.amount
   898→
   899→    RETURN null  // Rate not available for this subtotal
   900→```
   901→
   902→A range without `max_amount` matches all subtotals above `min_amount` (useful for "free shipping over X" rules).
   903→
   904→#### Carrier-calculated rate (stub)
   905→
   906→Config structure: `{"carrier": "ups", "service": "ground"}`
   907→
   908→- Placeholder for future carrier API integration.
   909→- Returns a hardcoded rate or error until implemented.
   910→
   911→### 9.3 Requires Shipping Check
   912→
   913→- If no line in the cart has `requires_shipping = true` on its variant, the shipping step is skipped entirely.
   914→- shipping_amount = 0 in the pricing result.
   915→- The checkout transitions directly past `shipping_selected` (set status to `shipping_selected` with shipping_method_id = null).
   916→
   917→**Mixed carts** (some physical, some digital):
   918→- Shipping is calculated only for physical items' weight.
   919→- Digital items are excluded from weight calculations.
   920→
   921→### 9.4 Free Shipping Discount Override
   922→
   923→If a validated discount has `value_type = 'free_shipping'`:
   924→- Override the calculated shipping amount to 0.
   925→- The shipping method is still selected (for fulfillment routing), but the customer is not charged.
   926→
   927→---
   928→
   929→## 10. Payment Processing (Mock PSP)
   930→
   931→### 10.1 Mock Payment Provider Architecture
   932→
   933→The platform uses a **Mock Payment Service Provider (PSP)** instead of real payment gateways. All payment processing happens in-process with no external API calls, no webhooks, and no redirects. This enables a fully functional checkout flow for development, testing, and demonstration purposes.
   934→
   935→**Interface:** `App\Contracts\PaymentProvider`
   936→
   937→Must implement:
   938→- `charge(checkout, paymentMethodData)` - returns a PaymentResult value object (success/failure, reference ID)
   939→- `refund(payment, amount)` - initiates a mock refund, returns a RefundResult
   940→
   941→**Service:** `App\Services\PaymentService` - orchestrates payment processing, delegates to MockPaymentProvider.
   942→
   943→**Implementation:** `App\Services\Payment\MockPaymentProvider`
   944→
   945→### 10.2 Supported Payment Methods
   946→
   947→**Enum:** `App\Enums\PaymentMethod` - values: `CreditCard` (`credit_card`), `Paypal` (`paypal`), `BankTransfer` (`bank_transfer`)
   948→
   949→| Method | Behavior | Capture Timing |
   950→|---|---|---|
   951→| `credit_card` | Simulates card authorization and immediate capture. Uses magic card numbers for test scenarios. | Auto-capture on checkout (instant) |
   952→| `paypal` | Simulates PayPal approval and immediate capture. Always succeeds (no magic numbers). | Auto-capture on checkout (instant) |
   953→| `bank_transfer` | No charge at checkout. Order created with `financial_status = pending`. Admin manually confirms payment receipt. | Deferred - admin confirms payment |
   954→
   955→### 10.3 Magic Card Numbers (Credit Card Testing)
   956→
   957→When payment_method is `credit_card`, the mock provider inspects the card number submitted in the checkout form to determine the outcome:
   958→
   959→| Card Number | Outcome | PaymentResult |
   960→|---|---|---|
   961→| `4242 4242 4242 4242` | Success | `success = true`, status = `captured` |
   962→| `4000 0000 0000 0002` | Decline | `success = false`, error = `card_declined` |
   963→| `4000 0000 0000 9995` | Insufficient funds | `success = false`, error = `insufficient_funds` |
   964→| Any other valid-looking number | Success | `success = true`, status = `captured` |
   965→
   966→The mock provider does **not** validate card expiry, CVC, or cardholder name. These fields exist in the UI for realism but are ignored by the backend.
   967→
   968→### 10.4 Mock Payment Flow
   969→
   970→```mermaid
   971→sequenceDiagram
   972→    participant Customer
   973→    participant Storefront
   974→    participant Backend
   975→    participant MockPSP
   976→
   977→    Customer->>Storefront: Select payment method + enter details
   978→    Storefront->>Backend: POST /checkouts/{id}/pay (payment_method, card details if applicable)
   979→    Backend->>Backend: Reserve inventory (if not already reserved)
   980→    Backend->>MockPSP: charge(checkout, paymentMethodData)
   981→
   982→    alt Credit Card / PayPal (instant capture)
   983→        MockPSP-->>Backend: PaymentResult (success/failure)
   984→        alt Success
   985→            Backend->>Backend: Create order (financial_status = paid)
   986→            Backend->>Backend: Commit inventory, mark cart converted
   987→            Backend-->>Storefront: 200 OK (order confirmation)
   988→        else Failure (decline / insufficient funds)
   989→            Backend->>Backend: Release reserved inventory
   990→            Backend-->>Storefront: 422 (error code + message)
   991→        end
   992→    else Bank Transfer (deferred)
   993→        MockPSP-->>Backend: PaymentResult (success = true, status = pending)
   994→        Backend->>Backend: Create order (financial_status = pending)
   995→        Backend->>Backend: Inventory remains reserved (not committed)
   996→        Backend-->>Storefront: 200 OK (order confirmation with bank transfer instructions)
   997→    end
   998→```
   999→

prompt: Read the file /Users/fabianwesner/Herd/shop/specs/05-BUSINESS-LOGIC.md in chunks (500 lines at a time) and provide a comprehensive summary of ALL business logic rules including: services, state machines, validation rules, pricing calculations, inventory management, payment processing, and fulfillment rules. I need every detail for implementation.


prompt: Read the file /Users/fabianwesner/Herd/shop/specs/06-AUTH-AND-SECURITY.md in chunks (500 lines at a time) and provide a comprehensive summary of ALL auth and security specifications including: guards, providers, middleware, rate limiting, permission matrices, CSRF, and session management. I need every detail for implementation.