content:
     1→# 01 - Database Schema Specification
     2→
     3→> E-commerce platform - SQLite database schema for Laravel 12
     4→
     5→---
     6→
     7→## Conventions
     8→
     9→| Convention | Detail |
    10→|---|---|
    11→| Primary keys | Auto-incrementing integer primary key on every table (column: `id`) |
    12→| Tenant isolation | `store_id` foreign key on every tenant-scoped table |
    13→| Monetary amounts | Stored as **integer in minor units** (cents). Column names end in `_amount`. |
    14→| Booleans | Integer, not null, default 0 (0 = false, 1 = true) |
    15→| JSON data | Text column - Laravel casts to/from JSON at the application layer |
    16→| Enumerations | Check constraints on text columns |
    17→| Timestamps | Text (ISO-8601) via Laravel's `created_at` / `updated_at` |
    18→| Soft deletes | Only where explicitly noted (`deleted_at` text, nullable) |
    19→| Encrypted columns | Suffixed `_encrypted` - encrypted at application layer, stored as text |
    20→
    21→---
    22→
    23→## Epic 1 - Foundation and Multi-Store Tenancy
    24→
    25→### organizations
    26→
    27→Billing entity that owns one or more stores.
    28→
    29→| Column | Type | Nullable | Default | Constraints | Notes |
    30→|---|---|---|---|---|---|
    31→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
    32→| name | TEXT | No | - | - | Organization display name |
    33→| billing_email | TEXT | No | - | - | Primary billing contact |
    34→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
    35→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
    36→
    37→**Indexes:**
    38→
    39→- `idx_organizations_billing_email` on (`billing_email`) - Supports lookup by billing email
    40→
    41→---
    42→
    43→### stores
    44→
    45→Individual storefront belonging to an organization.
    46→
    47→| Column | Type | Nullable | Default | Constraints | Notes |
    48→|---|---|---|---|---|---|
    49→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
    50→| organization_id | INTEGER | No | - | FK -> organizations(id) ON DELETE CASCADE | Parent organization |
    51→| name | TEXT | No | - | - | Store display name |
    52→| handle | TEXT | No | - | UNIQUE per table | URL-friendly slug |
    53→| status | TEXT | No | 'active' | CHECK IN ('active', 'suspended') | |
    54→| default_currency | TEXT | No | 'USD' | - | ISO 4217 currency code |
    55→| default_locale | TEXT | No | 'en' | - | IETF locale tag |
    56→| timezone | TEXT | No | 'UTC' | - | IANA timezone |
    57→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
    58→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
    59→
    60→**Indexes:**
    61→
    62→- `idx_stores_handle` UNIQUE on (`handle`) - Ensures globally unique store handles for URL routing
    63→- `idx_stores_organization_id` on (`organization_id`) - FK lookup and listing stores by org
    64→- `idx_stores_status` on (`status`) - Filtering stores by active/suspended status
    65→
    66→---
    67→
    68→### store_domains
    69→
    70→Custom domains attached to a store.
    71→
    72→| Column | Type | Nullable | Default | Constraints | Notes |
    73→|---|---|---|---|---|---|
    74→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
    75→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
    76→| hostname | TEXT | No | - | UNIQUE per table | e.g. "shop.example.com" |
    77→| type | TEXT | No | 'storefront' | CHECK IN ('storefront', 'admin', 'api') | Domain purpose |
    78→| is_primary | INTEGER | No | 0 | - | Boolean: primary domain flag |
    79→| tls_mode | TEXT | No | 'managed' | CHECK IN ('managed', 'bring_your_own') | TLS certificate mode |
    80→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
    81→
    82→**Indexes:**
    83→
    84→- `idx_store_domains_hostname` UNIQUE on (`hostname`) - Globally unique hostname for routing
    85→- `idx_store_domains_store_id` on (`store_id`) - FK lookup, listing domains per store
    86→- `idx_store_domains_store_primary` on (`store_id`, `is_primary`) - Quick lookup of a store's primary domain
    87→
    88→---
    89→
    90→### users
    91→
    92→Admin/staff users who manage stores (not storefront customers).
    93→
    94→| Column | Type | Nullable | Default | Constraints | Notes |
    95→|---|---|---|---|---|---|
    96→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
    97→| email | TEXT | No | - | UNIQUE per table | Login email |
    98→| password_hash | TEXT | No | - | - | bcrypt/argon2 hash; maps to Laravel's `password` field |
    99→| name | TEXT | No | - | - | Display name |
   100→| status | TEXT | No | 'active' | CHECK IN ('active', 'disabled') | |
   101→| email_verified_at | TEXT | Yes | NULL | - | ISO-8601 timestamp; required for `verified` middleware |
   102→| last_login_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   103→| two_factor_secret | TEXT | Yes | NULL | - | TOTP secret |
   104→| two_factor_recovery_codes | TEXT | Yes | NULL | - | JSON array of recovery codes |
   105→| two_factor_confirmed_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   106→| remember_token | TEXT | Yes | NULL | - | Laravel remember-me token |
   107→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   108→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   109→
   110→**Indexes:**
   111→
   112→- `idx_users_email` UNIQUE on (`email`) - Enforces unique email, supports login lookup
   113→- `idx_users_status` on (`status`) - Filtering users by active/disabled
   114→
   115→**Notes:** The `two_factor_*` columns align with the existing migration `2025_08_14_170933_add_two_factor_columns_to_users_table.php`. The `password_hash` column maps to Laravel's default `password` field internally but stores a bcrypt/argon2 hash.
   116→
   117→---
   118→
   119→### store_users
   120→
   121→Pivot table linking users to stores with a role. Uses composite primary key.
   122→
   123→| Column | Type | Nullable | Default | Constraints | Notes |
   124→|---|---|---|---|---|---|
   125→| store_id | INTEGER | No | - | PK (composite), FK -> stores(id) ON DELETE CASCADE | |
   126→| user_id | INTEGER | No | - | PK (composite), FK -> users(id) ON DELETE CASCADE | |
   127→| role | TEXT | No | 'staff' | CHECK IN ('owner', 'admin', 'staff', 'support') | |
   128→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   129→
   130→**Primary Key:** (`store_id`, `user_id`)
   131→
   132→**Indexes:**
   133→
   134→- `idx_store_users_user_id` on (`user_id`) - Reverse lookup: find all stores for a user
   135→- `idx_store_users_role` on (`store_id`, `role`) - Listing users by role within a store
   136→
   137→---
   138→
   139→### store_settings
   140→
   141→Single-row-per-store JSON settings bag.
   142→
   143→| Column | Type | Nullable | Default | Constraints | Notes |
   144→|---|---|---|---|---|---|
   145→| store_id | INTEGER | No | - | PRIMARY KEY, FK -> stores(id) ON DELETE CASCADE | One-to-one with stores |
   146→| settings_json | TEXT | No | '{}' | - | JSON settings object |
   147→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   148→
   149→**Notes:** No additional indexes needed - primary key on `store_id` covers all queries.
   150→
   151→---
   152→
   153→## Epic 2 - Catalog, Collections, Inventory, and Media
   154→
   155→### products
   156→
   157→Core product record, scoped to a store.
   158→
   159→| Column | Type | Nullable | Default | Constraints | Notes |
   160→|---|---|---|---|---|---|
   161→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   162→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
   163→| title | TEXT | No | - | - | Product display title |
   164→| handle | TEXT | No | - | UNIQUE per (store_id, handle) | URL-friendly slug |
   165→| status | TEXT | No | 'draft' | CHECK IN ('draft', 'active', 'archived') | |
   166→| description_html | TEXT | Yes | NULL | - | Rich-text description |
   167→| vendor | TEXT | Yes | NULL | - | Manufacturer/brand |
   168→| product_type | TEXT | Yes | NULL | - | Category classification |
   169→| tags | TEXT | No | '[]' | - | JSON array, e.g. ["summer","sale"] |
   170→| published_at | TEXT | Yes | NULL | - | ISO-8601 timestamp; null = unpublished |
   171→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   172→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   173→
   174→**Indexes:**
   175→
   176→- `idx_products_store_handle` UNIQUE on (`store_id`, `handle`) - Unique handle per store for URL routing
   177→- `idx_products_store_id` on (`store_id`) - FK lookup, listing all products in a store
   178→- `idx_products_store_status` on (`store_id`, `status`) - Filtering products by status within a store
   179→- `idx_products_published_at` on (`store_id`, `published_at`) - Sorting/filtering by publication date
   180→- `idx_products_vendor` on (`store_id`, `vendor`) - Filtering products by vendor
   181→- `idx_products_product_type` on (`store_id`, `product_type`) - Filtering products by type
   182→
   183→**Notes:** `tags` is a JSON-encoded array (e.g. `["summer","sale"]`). The `handle` is unique per store, not globally.
   184→
   185→---
   186→
   187→### product_options
   188→
   189→Named option dimensions for a product (e.g. Size, Color).
   190→
   191→| Column | Type | Nullable | Default | Constraints | Notes |
   192→|---|---|---|---|---|---|
   193→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   194→| product_id | INTEGER | No | - | FK -> products(id) ON DELETE CASCADE | Parent product |
   195→| name | TEXT | No | - | - | Option name, e.g. "Size", "Color" |
   196→| position | INTEGER | No | 0 | UNIQUE per (product_id, position) | Display ordering |
   197→
   198→**Indexes:**
   199→
   200→- `idx_product_options_product_id` on (`product_id`) - FK lookup, listing options per product
   201→- `idx_product_options_product_position` UNIQUE on (`product_id`, `position`) - Enforces unique ordering per product
   202→
   203→---
   204→
   205→### product_option_values
   206→
   207→Individual values within a product option (e.g. "Small", "Medium", "Large").
   208→
   209→| Column | Type | Nullable | Default | Constraints | Notes |
   210→|---|---|---|---|---|---|
   211→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   212→| product_option_id | INTEGER | No | - | FK -> product_options(id) ON DELETE CASCADE | Parent option |
   213→| value | TEXT | No | - | - | Display value, e.g. "Small" |
   214→| position | INTEGER | No | 0 | UNIQUE per (product_option_id, position) | Display ordering |
   215→
   216→**Indexes:**
   217→
   218→- `idx_product_option_values_option_id` on (`product_option_id`) - FK lookup, listing values per option
   219→- `idx_product_option_values_option_position` UNIQUE on (`product_option_id`, `position`) - Enforces unique ordering per option
   220→
   221→---
   222→
   223→### product_variants
   224→
   225→Purchasable SKU-level variant of a product.
   226→
   227→| Column | Type | Nullable | Default | Constraints | Notes |
   228→|---|---|---|---|---|---|
   229→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   230→| product_id | INTEGER | No | - | FK -> products(id) ON DELETE CASCADE | Parent product |
   231→| sku | TEXT | Yes | NULL | - | Stock keeping unit |
   232→| barcode | TEXT | Yes | NULL | - | UPC/EAN barcode |
   233→| price_amount | INTEGER | No | 0 | - | Price in minor units (cents) |
   234→| compare_at_amount | INTEGER | Yes | NULL | - | Original/compare price in minor units |
   235→| currency | TEXT | No | 'USD' | - | ISO 4217 currency code |
   236→| weight_g | INTEGER | Yes | NULL | - | Weight in grams |
   237→| requires_shipping | INTEGER | No | 1 | - | Boolean: physical product flag |
   238→| is_default | INTEGER | No | 0 | - | Boolean: default variant flag |
   239→| position | INTEGER | No | 0 | - | Display ordering |
   240→| status | TEXT | No | 'active' | CHECK IN ('active', 'archived') | |
   241→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   242→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   243→
   244→**Indexes:**
   245→
   246→- `idx_product_variants_product_id` on (`product_id`) - FK lookup, listing variants per product
   247→- `idx_product_variants_sku` on (`sku`) - SKU lookup across all variants
   248→- `idx_product_variants_barcode` on (`barcode`) - Barcode scanning lookup
   249→- `idx_product_variants_product_position` on (`product_id`, `position`) - Ordered variant listing
   250→- `idx_product_variants_product_default` on (`product_id`, `is_default`) - Quick lookup of default variant
   251→
   252→**Notes:** `price_amount` and `compare_at_amount` are in minor currency units (cents). A product always has at least one variant (the default).
   253→
   254→---
   255→
   256→### variant_option_values
   257→
   258→Maps a variant to its chosen option values (e.g. variant #5 = Size:Large + Color:Red). Junction table with composite primary key.
   259→
   260→| Column | Type | Nullable | Default | Constraints | Notes |
   261→|---|---|---|---|---|---|
   262→| variant_id | INTEGER | No | - | PK (composite), FK -> product_variants(id) ON DELETE CASCADE | |
   263→| product_option_value_id | INTEGER | No | - | PK (composite), FK -> product_option_values(id) ON DELETE CASCADE | |
   264→
   265→**Primary Key:** (`variant_id`, `product_option_value_id`)
   266→
   267→**Indexes:**
   268→
   269→- `idx_variant_option_values_value_id` on (`product_option_value_id`) - Reverse lookup: find all variants with a given option value
   270→
   271→**Notes:** This is a junction table that links variants to the specific option values they represent. Required to resolve which combination of options each variant corresponds to.
   272→
   273→---
   274→
   275→### inventory_items
   276→
   277→Stock tracking per variant per store.
   278→
   279→| Column | Type | Nullable | Default | Constraints | Notes |
   280→|---|---|---|---|---|---|
   281→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   282→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
   283→| variant_id | INTEGER | No | - | FK -> product_variants(id) ON DELETE CASCADE, UNIQUE | One inventory record per variant |
   284→| quantity_on_hand | INTEGER | No | 0 | - | Total physical stock |
   285→| quantity_reserved | INTEGER | No | 0 | - | Stock allocated to open orders |
   286→| policy | TEXT | No | 'deny' | CHECK IN ('deny', 'continue') | Oversell policy |
   287→
   288→**Indexes:**
   289→
   290→- `idx_inventory_items_variant_id` UNIQUE on (`variant_id`) - Enforces one inventory record per variant, fast variant lookup
   291→- `idx_inventory_items_store_id` on (`store_id`) - Listing all inventory for a store
   292→
   293→**Notes:** `quantity_on_hand` is the total physical stock. `quantity_reserved` is stock allocated to open orders not yet fulfilled. Available = on_hand - reserved. Policy `deny` prevents overselling; `continue` allows selling below zero.
   294→
   295→---
   296→
   297→### collections
   298→
   299→Curated or rule-based product groupings.
   300→
   301→| Column | Type | Nullable | Default | Constraints | Notes |
   302→|---|---|---|---|---|---|
   303→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   304→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
   305→| title | TEXT | No | - | - | Collection display title |
   306→| handle | TEXT | No | - | UNIQUE per (store_id, handle) | URL-friendly slug |
   307→| description_html | TEXT | Yes | NULL | - | Rich-text description |
   308→| type | TEXT | No | 'manual' | CHECK IN ('manual', 'automated') | Collection type: manual curation or rule-based automation |
   309→| status | TEXT | No | 'active' | CHECK IN ('draft', 'active', 'archived') | |
   310→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   311→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   312→
   313→**Indexes:**
   314→
   315→- `idx_collections_store_handle` UNIQUE on (`store_id`, `handle`) - Unique handle per store for URL routing
   316→- `idx_collections_store_id` on (`store_id`) - FK lookup, listing all collections in a store
   317→- `idx_collections_store_status` on (`store_id`, `status`) - Filtering collections by status
   318→
   319→---
   320→
   321→### collection_products
   322→
   323→Pivot linking products to collections with ordering. Composite primary key.
   324→
   325→| Column | Type | Nullable | Default | Constraints | Notes |
   326→|---|---|---|---|---|---|
   327→| collection_id | INTEGER | No | - | PK (composite), FK -> collections(id) ON DELETE CASCADE | |
   328→| product_id | INTEGER | No | - | PK (composite), FK -> products(id) ON DELETE CASCADE | |
   329→| position | INTEGER | No | 0 | - | Display ordering within collection |
   330→
   331→**Primary Key:** (`collection_id`, `product_id`)
   332→
   333→**Indexes:**
   334→
   335→- `idx_collection_products_product_id` on (`product_id`) - Reverse lookup: find all collections containing a product
   336→- `idx_collection_products_position` on (`collection_id`, `position`) - Ordered product listing within a collection
   337→
   338→---
   339→
   340→### product_media
   341→
   342→Images and videos attached to a product.
   343→
   344→| Column | Type | Nullable | Default | Constraints | Notes |
   345→|---|---|---|---|---|---|
   346→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   347→| product_id | INTEGER | No | - | FK -> products(id) ON DELETE CASCADE | Parent product |
   348→| type | TEXT | No | 'image' | CHECK IN ('image', 'video') | Media type |
   349→| storage_key | TEXT | No | - | - | File storage path/key |
   350→| alt_text | TEXT | Yes | NULL | - | Accessibility alt text |
   351→| width | INTEGER | Yes | NULL | - | Pixel width |
   352→| height | INTEGER | Yes | NULL | - | Pixel height |
   353→| mime_type | TEXT | Yes | NULL | - | e.g. "image/jpeg" |
   354→| byte_size | INTEGER | Yes | NULL | - | File size in bytes |
   355→| position | INTEGER | No | 0 | - | Display ordering |
   356→| status | TEXT | No | 'processing' | CHECK IN ('processing', 'ready', 'failed') | Processing status |
   357→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   358→
   359→**Indexes:**
   360→
   361→- `idx_product_media_product_id` on (`product_id`) - FK lookup, listing media per product
   362→- `idx_product_media_product_position` on (`product_id`, `position`) - Ordered media listing
   363→- `idx_product_media_status` on (`status`) - Finding media by processing status
   364→
   365→---
   366→
   367→## Epic 3 - Themes, Storefront, and Localization
   368→
   369→### themes
   370→
   371→Storefront theme definitions per store.
   372→
   373→| Column | Type | Nullable | Default | Constraints | Notes |
   374→|---|---|---|---|---|---|
   375→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   376→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
   377→| name | TEXT | No | - | - | Theme display name |
   378→| version | TEXT | Yes | NULL | - | Semantic version string |
   379→| status | TEXT | No | 'draft' | CHECK IN ('draft', 'published') | |
   380→| published_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   381→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   382→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   383→
   384→**Indexes:**
   385→
   386→- `idx_themes_store_id` on (`store_id`) - FK lookup, listing themes per store
   387→- `idx_themes_store_status` on (`store_id`, `status`) - Finding published/draft themes per store
   388→
   389→---
   390→
   391→### theme_files
   392→
   393→Individual files belonging to a theme (templates, assets, snippets).
   394→
   395→| Column | Type | Nullable | Default | Constraints | Notes |
   396→|---|---|---|---|---|---|
   397→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   398→| theme_id | INTEGER | No | - | FK -> themes(id) ON DELETE CASCADE | Parent theme |
   399→| path | TEXT | No | - | UNIQUE per (theme_id, path) | File path within theme |
   400→| storage_key | TEXT | No | - | - | File storage path/key |
   401→| sha256 | TEXT | No | - | - | Content hash for cache busting |
   402→| byte_size | INTEGER | No | 0 | - | File size in bytes |
   403→
   404→**Indexes:**
   405→
   406→- `idx_theme_files_theme_path` UNIQUE on (`theme_id`, `path`) - Enforces unique file paths per theme
   407→- `idx_theme_files_theme_id` on (`theme_id`) - FK lookup, listing files per theme
   408→
   409→---
   410→
   411→### theme_settings
   412→
   413→Single-row JSON configuration per theme.
   414→
   415→| Column | Type | Nullable | Default | Constraints | Notes |
   416→|---|---|---|---|---|---|
   417→| theme_id | INTEGER | No | - | PRIMARY KEY, FK -> themes(id) ON DELETE CASCADE | One-to-one with themes |
   418→| settings_json | TEXT | No | '{}' | - | JSON settings object |
   419→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   420→
   421→---
   422→
   423→### pages
   424→
   425→Static CMS pages (About Us, Contact, etc.).
   426→
   427→| Column | Type | Nullable | Default | Constraints | Notes |
   428→|---|---|---|---|---|---|
   429→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   430→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
   431→| title | TEXT | No | - | - | Page title |
   432→| handle | TEXT | No | - | UNIQUE per (store_id, handle) | URL-friendly slug |
   433→| body_html | TEXT | Yes | NULL | - | Rich-text content |
   434→| status | TEXT | No | 'draft' | CHECK IN ('draft', 'published', 'archived') | |
   435→| published_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   436→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   437→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   438→
   439→**Indexes:**
   440→
   441→- `idx_pages_store_handle` UNIQUE on (`store_id`, `handle`) - Unique handle per store for URL routing
   442→- `idx_pages_store_id` on (`store_id`) - FK lookup, listing pages per store
   443→- `idx_pages_store_status` on (`store_id`, `status`) - Filtering pages by status
   444→
   445→---
   446→
   447→### navigation_menus
   448→
   449→Named menus (e.g. main-menu, footer-menu).
   450→
   451→| Column | Type | Nullable | Default | Constraints | Notes |
   452→|---|---|---|---|---|---|
   453→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   454→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
   455→| handle | TEXT | No | - | UNIQUE per (store_id, handle) | e.g. "main-menu" |
   456→| title | TEXT | No | - | - | Display title |
   457→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   458→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
   459→
   460→**Indexes:**
   461→
   462→- `idx_navigation_menus_store_handle` UNIQUE on (`store_id`, `handle`) - Unique handle per store
   463→- `idx_navigation_menus_store_id` on (`store_id`) - FK lookup, listing menus per store
   464→
   465→---
   466→
   467→### navigation_items
   468→
   469→Individual links within a navigation menu (supports nesting via position).
   470→
   471→| Column | Type | Nullable | Default | Constraints | Notes |
   472→|---|---|---|---|---|---|
   473→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
   474→| menu_id | INTEGER | No | - | FK -> navigation_menus(id) ON DELETE CASCADE | Parent menu |
   475→| type | TEXT | No | 'link' | CHECK IN ('link', 'page', 'collection', 'product') | Item type |
   476→| label | TEXT | No | - | - | Display label |
   477→| url | TEXT | Yes | NULL | - | Direct URL (used when type = 'link') |
   478→| resource_id | INTEGER | Yes | NULL | - | FK to pages/collections/products (polymorphic) |
   479→| position | INTEGER | No | 0 | - | Display ordering |
   480→
   481→**Indexes:**
   482→
   483→- `idx_navigation_items_menu_id` on (`menu_id`) - FK lookup, listing items per menu
   484→- `idx_navigation_items_menu_position` on (`menu_id`, `position`) - Ordered item listing
   485→
   486→**Notes:** When `type` is `link`, the `url` field is used directly. When `type` is `page`, `collection`, or `product`, the `resource_id` points to the respective table's `id`. The URL is resolved at render time.
   487→
   488→---
   489→
   490→## Epic 4 - Search
   491→
   492→### search_settings
   493→
   494→Per-store search configuration (synonyms, stop words).
   495→
   496→| Column | Type | Nullable | Default | Constraints | Notes |
   497→|---|---|---|---|---|---|
   498→| store_id | INTEGER | No | - | PRIMARY KEY, FK -> stores(id) ON DELETE CASCADE | One-to-one with stores |
   499→| synonyms_json | TEXT | No | '[]' | - | JSON array of synonym groups |
   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 |
  1000→
  1001→**Indexes:**
  1002→
  1003→- `idx_app_installations_store_app` UNIQUE on (`store_id`, `app_id`) - One installation per app per store
  1004→- `idx_app_installations_store_id` on (`store_id`) - Listing installations per store
  1005→- `idx_app_installations_app_id` on (`app_id`) - Finding all stores with an app installed
  1006→
  1007→---
  1008→
  1009→### oauth_clients
  1010→
  1011→OAuth2 client credentials for apps.
  1012→
  1013→| Column | Type | Nullable | Default | Constraints | Notes |
  1014→|---|---|---|---|---|---|
  1015→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
  1016→| app_id | INTEGER | No | - | FK -> apps(id) ON DELETE CASCADE | Parent app |
  1017→| client_id | TEXT | No | - | UNIQUE per table | OAuth2 client identifier |
  1018→| client_secret_encrypted | TEXT | No | - | - | Encrypted client secret |
  1019→| redirect_uris_json | TEXT | No | '[]' | - | JSON array of allowed redirect URIs |
  1020→
  1021→**Indexes:**
  1022→
  1023→- `idx_oauth_clients_client_id` UNIQUE on (`client_id`) - OAuth2 client lookup
  1024→- `idx_oauth_clients_app_id` on (`app_id`) - FK lookup, listing clients per app
  1025→
  1026→---
  1027→
  1028→### oauth_tokens
  1029→
  1030→Issued access/refresh tokens for app installations.
  1031→
  1032→| Column | Type | Nullable | Default | Constraints | Notes |
  1033→|---|---|---|---|---|---|
  1034→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
  1035→| installation_id | INTEGER | No | - | FK -> app_installations(id) ON DELETE CASCADE | Parent installation |
  1036→| access_token_hash | TEXT | No | - | UNIQUE per table | Hashed access token |
  1037→| refresh_token_hash | TEXT | Yes | NULL | - | Hashed refresh token |
  1038→| expires_at | TEXT | No | - | - | ISO-8601 expiration timestamp |
  1039→
  1040→**Indexes:**
  1041→
  1042→- `idx_oauth_tokens_installation_id` on (`installation_id`) - FK lookup, listing tokens per installation
  1043→- `idx_oauth_tokens_access_hash` UNIQUE on (`access_token_hash`) - Token validation lookup
  1044→- `idx_oauth_tokens_expires_at` on (`expires_at`) - Cleanup of expired tokens
  1045→
  1046→---
  1047→
  1048→### webhook_subscriptions
  1049→
  1050→Registered webhook endpoints for event delivery.
  1051→
  1052→| Column | Type | Nullable | Default | Constraints | Notes |
  1053→|---|---|---|---|---|---|
  1054→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
  1055→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
  1056→| app_installation_id | INTEGER | Yes | NULL | FK -> app_installations(id) ON DELETE CASCADE | Nullable for store-level hooks |
  1057→| event_type | TEXT | No | - | - | Event to subscribe to |
  1058→| target_url | TEXT | No | - | - | Delivery endpoint URL |
  1059→| signing_secret_encrypted | TEXT | No | - | - | Encrypted HMAC signing secret |
  1060→| status | TEXT | No | 'active' | CHECK IN ('active', 'paused', 'disabled') | |
  1061→
  1062→**Indexes:**
  1063→
  1064→- `idx_webhook_subscriptions_store_id` on (`store_id`) - FK lookup
  1065→- `idx_webhook_subscriptions_store_event` on (`store_id`, `event_type`) - Finding subscriptions for a given event
  1066→- `idx_webhook_subscriptions_installation` on (`app_installation_id`) - Listing subscriptions per app installation
  1067→
  1068→---
  1069→
  1070→### webhook_deliveries
  1071→
  1072→Delivery log for webhook attempts.
  1073→
  1074→| Column | Type | Nullable | Default | Constraints | Notes |
  1075→|---|---|---|---|---|---|
  1076→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
  1077→| subscription_id | INTEGER | No | - | FK -> webhook_subscriptions(id) ON DELETE CASCADE | Parent subscription |
  1078→| event_id | TEXT | No | - | - | Unique event identifier |
  1079→| attempt_count | INTEGER | No | 1 | - | Number of delivery attempts |
  1080→| status | TEXT | No | 'pending' | CHECK IN ('pending', 'success', 'failed') | |
  1081→| last_attempt_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
  1082→| response_code | INTEGER | Yes | NULL | - | HTTP response code |
  1083→| response_body_snippet | TEXT | Yes | NULL | - | Truncated response body for debugging |
  1084→
  1085→**Indexes:**
  1086→
  1087→- `idx_webhook_deliveries_subscription_id` on (`subscription_id`) - FK lookup, listing deliveries per subscription
  1088→- `idx_webhook_deliveries_event_id` on (`event_id`) - Deduplication and lookup by event
  1089→- `idx_webhook_deliveries_status` on (`status`) - Finding pending/failed deliveries for retry
  1090→- `idx_webhook_deliveries_last_attempt` on (`last_attempt_at`) - Time-based retry scheduling
  1091→
  1092→---
  1093→
  1094→## Laravel Framework Tables
  1095→
  1096→The following tables are managed by Laravel's built-in migrations and should not be modified.
  1097→
  1098→### sessions
  1099→
  1100→| Column | Type | Nullable | Default | Constraints | Notes |
  1101→|---|---|---|---|---|---|
  1102→| id | TEXT | No | - | PRIMARY KEY | Session ID |
  1103→| user_id | INTEGER | Yes | NULL | - | Associated user |
  1104→| ip_address | TEXT | Yes | NULL | - | Client IP |
  1105→| user_agent | TEXT | Yes | NULL | - | Client user agent |
  1106→| payload | TEXT | No | - | - | Serialized session data |
  1107→| last_activity | INTEGER | No | - | - | Unix timestamp |
  1108→
  1109→### cache
  1110→
  1111→| Column | Type | Nullable | Default | Constraints | Notes |
  1112→|---|---|---|---|---|---|
  1113→| key | TEXT | No | - | PRIMARY KEY | Cache key |
  1114→| value | TEXT | No | - | - | Serialized cache value |
  1115→| expiration | INTEGER | No | - | - | Unix timestamp |
  1116→
  1117→### cache_locks
  1118→
  1119→| Column | Type | Nullable | Default | Constraints | Notes |
  1120→|---|---|---|---|---|---|
  1121→| key | TEXT | No | - | PRIMARY KEY | Lock key |
  1122→| owner | TEXT | No | - | - | Lock owner identifier |
  1123→| expiration | INTEGER | No | - | - | Unix timestamp |
  1124→
  1125→### jobs
  1126→
  1127→| Column | Type | Nullable | Default | Constraints | Notes |
  1128→|---|---|---|---|---|---|
  1129→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
  1130→| queue | TEXT | No | - | - | Queue name |
  1131→| payload | TEXT | No | - | - | Serialized job payload |
  1132→| attempts | INTEGER | No | - | - | Number of attempts |
  1133→| reserved_at | INTEGER | Yes | NULL | - | Unix timestamp |
  1134→| available_at | INTEGER | No | - | - | Unix timestamp |
  1135→| created_at | INTEGER | No | - | - | Unix timestamp |
  1136→
  1137→### job_batches
  1138→
  1139→| Column | Type | Nullable | Default | Constraints | Notes |
  1140→|---|---|---|---|---|---|
  1141→| id | TEXT | No | - | PRIMARY KEY | Batch UUID |
  1142→| name | TEXT | No | - | - | Batch name |
  1143→| total_jobs | INTEGER | No | - | - | Total jobs in batch |
  1144→| pending_jobs | INTEGER | No | - | - | Pending count |
  1145→| failed_jobs | INTEGER | No | - | - | Failed count |
  1146→| failed_job_ids | TEXT | No | - | - | Serialized list of failed IDs |
  1147→| options | TEXT | Yes | NULL | - | Serialized batch options |
  1148→| cancelled_at | INTEGER | Yes | NULL | - | Unix timestamp |
  1149→| created_at | INTEGER | No | - | - | Unix timestamp |
  1150→| finished_at | INTEGER | Yes | NULL | - | Unix timestamp |
  1151→
  1152→### failed_jobs
  1153→
  1154→| Column | Type | Nullable | Default | Constraints | Notes |
  1155→|---|---|---|---|---|---|
  1156→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
  1157→| uuid | TEXT | No | - | UNIQUE | Job UUID |
  1158→| connection | TEXT | No | - | - | Queue connection name |
  1159→| queue | TEXT | No | - | - | Queue name |
  1160→| payload | TEXT | No | - | - | Serialized job payload |
  1161→| exception | TEXT | No | - | - | Exception trace |
  1162→| failed_at | TEXT | No | CURRENT_TIMESTAMP | - | ISO-8601 timestamp |
  1163→
  1164→### personal_access_tokens
  1165→
  1166→| Column | Type | Nullable | Default | Constraints | Notes |
  1167→|---|---|---|---|---|---|
  1168→| id | INTEGER | No | autoincrement | PRIMARY KEY | |
  1169→| tokenable_type | TEXT | No | - | - | Polymorphic type |
  1170→| tokenable_id | INTEGER | No | - | - | Polymorphic ID |
  1171→| name | TEXT | No | - | - | Token name |
  1172→| token | TEXT | No | - | UNIQUE | SHA-256 hash of the token |
  1173→| abilities | TEXT | Yes | NULL | - | JSON array of abilities |
  1174→| last_used_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
  1175→| expires_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
  1176→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
  1177→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
  1178→
  1179→### password_reset_tokens
  1180→
  1181→| Column | Type | Nullable | Default | Constraints | Notes |
  1182→|---|---|---|---|---|---|
  1183→| email | TEXT | No | - | PRIMARY KEY | Admin user email |
  1184→| token | TEXT | No | - | - | Hashed reset token |
  1185→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
  1186→
  1187→---
  1188→
  1189→### customer_password_reset_tokens
  1190→
  1191→| Column | Type | Nullable | Default | Constraints | Notes |
  1192→|---|---|---|---|---|---|
  1193→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
  1194→| email | TEXT | No | - | - | Customer email |
  1195→| token | TEXT | No | - | - | Hashed reset token |
  1196→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
  1197→
  1198→**Primary Key:** (`store_id`, `email`)
  1199→
  1200→---
  1201→
  1202→## Entity Relationship Diagrams
  1203→
  1204→### Epic 1 - Foundation and Tenancy
  1205→
  1206→```mermaid
  1207→erDiagram
  1208→    organizations ||--o{ stores : "owns"
  1209→    stores ||--o{ store_domains : "has"
  1210→    stores ||--o{ store_users : "links"
  1211→    users ||--o{ store_users : "links"
  1212→    stores ||--|| store_settings : "has"
  1213→
  1214→    organizations {
  1215→        INTEGER id PK
  1216→        TEXT name
  1217→        TEXT billing_email
  1218→    }
  1219→    stores {
  1220→        INTEGER id PK
  1221→        INTEGER organization_id FK
  1222→        TEXT name
  1223→        TEXT handle
  1224→        TEXT status
  1225→    }
  1226→    store_domains {
  1227→        INTEGER id PK
  1228→        INTEGER store_id FK
  1229→        TEXT hostname
  1230→        TEXT type
  1231→        INTEGER is_primary
  1232→    }
  1233→    users {
  1234→        INTEGER id PK
  1235→        TEXT email
  1236→        TEXT password_hash
  1237→        TEXT name
  1238→        TEXT status
  1239→    }
  1240→    store_users {
  1241→        INTEGER store_id PK
  1242→        INTEGER user_id PK
  1243→        TEXT role
  1244→    }
  1245→    store_settings {
  1246→        INTEGER store_id PK
  1247→        TEXT settings_json
  1248→    }
  1249→```
  1250→
  1251→### Epic 2 - Catalog, Collections, Inventory, and Media
  1252→
  1253→```mermaid
  1254→erDiagram
  1255→    stores ||--o{ products : "has"
  1256→    stores ||--o{ collections : "has"
  1257→    products ||--o{ product_options : "defines"
  1258→    product_options ||--o{ product_option_values : "contains"
  1259→    products ||--o{ product_variants : "has"
  1260→    product_variants }o--o{ product_option_values : "variant_option_values"
  1261→    product_variants ||--|| inventory_items : "tracked by"
  1262→    stores ||--o{ inventory_items : "has"
  1263→    collections }o--o{ products : "collection_products"
  1264→    products ||--o{ product_media : "has"
  1265→
  1266→    products {
  1267→        INTEGER id PK
  1268→        INTEGER store_id FK
  1269→        TEXT title
  1270→        TEXT handle
  1271→        TEXT status
  1272→    }
  1273→    product_options {
  1274→        INTEGER id PK
  1275→        INTEGER product_id FK
  1276→        TEXT name
  1277→        INTEGER position
  1278→    }
  1279→    product_option_values {
  1280→        INTEGER id PK
  1281→        INTEGER product_option_id FK
  1282→        TEXT value
  1283→        INTEGER position
  1284→    }
  1285→    product_variants {
  1286→        INTEGER id PK
  1287→        INTEGER product_id FK
  1288→        TEXT sku
  1289→        INTEGER price_amount
  1290→        TEXT status
  1291→    }
  1292→    inventory_items {
  1293→        INTEGER id PK
  1294→        INTEGER store_id FK
  1295→        INTEGER variant_id FK
  1296→        INTEGER quantity_on_hand
  1297→        INTEGER quantity_reserved
  1298→        TEXT policy
  1299→    }
  1300→    collections {
  1301→        INTEGER id PK
  1302→        INTEGER store_id FK
  1303→        TEXT title
  1304→        TEXT handle
  1305→        TEXT type
  1306→        TEXT status
  1307→    }
  1308→    product_media {
  1309→        INTEGER id PK
  1310→        INTEGER product_id FK
  1311→        TEXT type
  1312→        TEXT storage_key
  1313→        INTEGER position
  1314→        TEXT status
  1315→    }
  1316→```
  1317→
  1318→### Epic 3 - Themes, Storefront, and Localization
  1319→
  1320→```mermaid
  1321→erDiagram
  1322→    stores ||--o{ themes : "has"
  1323→    themes ||--o{ theme_files : "contains"
  1324→    themes ||--|| theme_settings : "configured by"
  1325→    stores ||--o{ pages : "has"
  1326→    stores ||--o{ navigation_menus : "has"
  1327→    navigation_menus ||--o{ navigation_items : "contains"
  1328→
  1329→    themes {
  1330→        INTEGER id PK
  1331→        INTEGER store_id FK
  1332→        TEXT name
  1333→        TEXT status
  1334→    }
  1335→    theme_files {
  1336→        INTEGER id PK
  1337→        INTEGER theme_id FK
  1338→        TEXT path
  1339→        TEXT storage_key
  1340→    }
  1341→    theme_settings {
  1342→        INTEGER theme_id PK
  1343→        TEXT settings_json
  1344→    }
  1345→    pages {
  1346→        INTEGER id PK
  1347→        INTEGER store_id FK
  1348→        TEXT title
  1349→        TEXT handle
  1350→        TEXT status
  1351→    }
  1352→    navigation_menus {
  1353→        INTEGER id PK
  1354→        INTEGER store_id FK
  1355→        TEXT handle
  1356→        TEXT title
  1357→    }
  1358→    navigation_items {
  1359→        INTEGER id PK
  1360→        INTEGER menu_id FK
  1361→        TEXT type
  1362→        TEXT label
  1363→        INTEGER position
  1364→    }
  1365→```
  1366→
  1367→### Epics 5-6 - Cart, Checkout, Orders, and Payments
  1368→
  1369→```mermaid
  1370→erDiagram
  1371→    stores ||--o{ customers : "has"
  1372→    customers ||--o{ customer_addresses : "has"
  1373→    stores ||--o{ carts : "has"
  1374→    customers ||--o{ carts : "owns"
  1375→    carts ||--o{ cart_lines : "contains"
  1376→    product_variants ||--o{ cart_lines : "referenced by"
  1377→    stores ||--o{ checkouts : "has"
  1378→    carts ||--o{ checkouts : "converted to"
  1379→    stores ||--o{ orders : "has"
  1380→    customers ||--o{ orders : "placed"
  1381→    orders ||--o{ order_lines : "contains"
  1382→    orders ||--o{ payments : "paid via"
  1383→    orders ||--o{ refunds : "refunded via"
  1384→    payments ||--o{ refunds : "source of"
  1385→    orders ||--o{ fulfillments : "shipped via"
  1386→    fulfillments ||--o{ fulfillment_lines : "contains"
  1387→    order_lines ||--o{ fulfillment_lines : "fulfilled by"
  1388→
  1389→    customers {
  1390→        INTEGER id PK
  1391→        INTEGER store_id FK
  1392→        TEXT email
  1393→        TEXT name
  1394→    }
  1395→    carts {
  1396→        INTEGER id PK
  1397→        INTEGER store_id FK
  1398→        INTEGER customer_id FK
  1399→        TEXT status
  1400→    }
  1401→    orders {
  1402→        INTEGER id PK
  1403→        INTEGER store_id FK
  1404→        INTEGER customer_id FK
  1405→        TEXT order_number
  1406→        TEXT payment_method
  1407→        TEXT status
  1408→        TEXT financial_status
  1409→        TEXT fulfillment_status
  1410→        INTEGER total_amount
  1411→    }
  1412→    payments {
  1413→        INTEGER id PK
  1414→        INTEGER order_id FK
  1415→        TEXT provider
  1416→        TEXT method
  1417→        TEXT status
  1418→        INTEGER amount
  1419→    }
  1420→    fulfillments {
  1421→        INTEGER id PK
  1422→        INTEGER order_id FK
  1423→        TEXT status
  1424→        TEXT tracking_number
  1425→    }
  1426→```
  1427→
  1428→### Epics 7-8 - Analytics, Apps, and Webhooks
  1429→
  1430→```mermaid
  1431→erDiagram
  1432→    stores ||--o{ analytics_events : "records"
  1433→    stores ||--o{ analytics_daily : "aggregates"
  1434→    customers ||--o{ analytics_events : "generates"
  1435→    apps ||--o{ app_installations : "installed via"
  1436→    stores ||--o{ app_installations : "has"
  1437→    apps ||--o{ oauth_clients : "authenticates via"
  1438→    app_installations ||--o{ oauth_tokens : "issues"
  1439→    stores ||--o{ webhook_subscriptions : "registers"
  1440→    app_installations ||--o{ webhook_subscriptions : "owns"
  1441→    webhook_subscriptions ||--o{ webhook_deliveries : "delivers"
  1442→
  1443→    analytics_events {
  1444→        INTEGER id PK
  1445→        INTEGER store_id FK
  1446→        TEXT type
  1447→        TEXT session_id
  1448→    }
  1449→    analytics_daily {
  1450→        INTEGER store_id PK
  1451→        TEXT date PK
  1452→        INTEGER orders_count
  1453→        INTEGER revenue_amount
  1454→    }
  1455→    apps {
  1456→        INTEGER id PK
  1457→        TEXT name
  1458→        TEXT status
  1459→    }
  1460→    app_installations {
  1461→        INTEGER id PK
  1462→        INTEGER store_id FK
  1463→        INTEGER app_id FK
  1464→        TEXT status
  1465→    }
  1466→    webhook_subscriptions {
  1467→        INTEGER id PK
  1468→        INTEGER store_id FK
  1469→        TEXT event_type
  1470→        TEXT target_url
  1471→    }
  1472→    webhook_deliveries {
  1473→        INTEGER id PK
  1474→        INTEGER subscription_id FK
  1475→        TEXT event_id
  1476→        TEXT status
  1477→    }
  1478→```
  1479→
  1480→---
  1481→
  1482→## SQLite-Specific Configuration Requirements
  1483→
  1484→The application requires specific SQLite PRAGMA settings configured in the Laravel database configuration file (`config/database.php`). These settings must be applied for correct and performant operation.
  1485→
  1486→### Required PRAGMA Settings
  1487→
  1488→| Pragma | Value | Purpose |
  1489→|---|---|---|
  1490→| `foreign_keys` | ON | Enforce referential integrity on every connection. Without this, foreign key constraints are silently ignored by SQLite. |
  1491→| `journal_mode` | WAL | Enable Write-Ahead Logging to allow concurrent reads during writes. Critical for web application performance. |
  1492→| `busy_timeout` | 5000 | Wait up to 5 seconds for a write lock before throwing an error. Prevents immediate failures under concurrent load. |
  1493→| `synchronous` | NORMAL | Balances durability and performance. Safe when used with WAL mode; avoids fsync on every commit. |
  1494→| `cache_size` | -20000 | Use up to 20 MB of page cache. Set in AppServiceProvider if needed. Negative value means kilobytes. |
  1495→
  1496→### Laravel Configuration Requirements
  1497→
  1498→- The SQLite driver connection must set `foreign_key_constraints` to `true` so that every new database connection enables foreign key enforcement.
  1499→- The `journal_mode` must be set to `wal` (Write-Ahead Logging) for concurrent read support.
  1500→- The `busy_timeout` must be set to `5000` (milliseconds) to handle write contention gracefully.
  1501→- The `synchronous` mode must be set to `normal` for balanced durability with WAL mode.
  1502→- The `transaction_mode` should be set to `DEFERRED` (the default), meaning transactions acquire locks lazily.
  1503→- The `cache_size` pragma (set to `-20000` for 20 MB) should be applied via the AppServiceProvider boot method if needed for performance tuning.
  1504→
  1505→---
  1506→
  1507→## Migration Dependency Graph
  1508→
  1509→Migrations must be created in an order that satisfies foreign key dependencies. The following flowchart shows the required sequence grouped into batches. Tables within the same batch have no inter-dependencies and can appear in any order.
  1510→
  1511→```mermaid
  1512→flowchart TD
  1513→    subgraph "Batch 1 - No Dependencies"
  1514→        organizations
  1515→        apps
  1516→    end
  1517→
  1518→    subgraph "Batch 2 - Depends on Batch 1"
  1519→        stores
  1520→        users
  1521→    end
  1522→
  1523→    subgraph "Batch 3 - Depends on Batch 2"
  1524→        store_domains
  1525→        store_users
  1526→        store_settings
  1527→        customers
  1528→        themes
  1529→        pages
  1530→        navigation_menus
  1531→        search_settings
  1532→        shipping_zones
  1533→        tax_settings
  1534→        discounts
  1535→        products
  1536→        collections
  1537→        app_installations
  1538→        oauth_clients
  1539→    end
  1540→
  1541→    subgraph "Batch 4 - Depends on Batch 3"
  1542→        product_options
  1543→        product_variants
  1544→        product_media
  1545→        collection_products
  1546→        customer_addresses
  1547→        carts
  1548→        navigation_items
  1549→        theme_files
  1550→        theme_settings
  1551→        search_queries
  1552→        shipping_rates
  1553→        oauth_tokens
  1554→        webhook_subscriptions
  1555→    end
  1556→
  1557→    subgraph "Batch 5 - Depends on Batch 4"
  1558→        product_option_values
  1559→        inventory_items
  1560→        cart_lines
  1561→        checkouts
  1562→        orders
  1563→        analytics_events
  1564→        analytics_daily
  1565→        webhook_deliveries
  1566→    end
  1567→
  1568→    subgraph "Batch 6 - Depends on Batch 5"
  1569→        variant_option_values
  1570→        order_lines
  1571→        payments
  1572→        fulfillments
  1573→    end
  1574→
  1575→    subgraph "Batch 7 - Depends on Batch 6"
  1576→        refunds
  1577→        fulfillment_lines
  1578→    end
  1579→
  1580→    organizations --> stores
  1581→    apps --> app_installations
  1582→    apps --> oauth_clients
  1583→    stores --> store_domains
  1584→    stores --> store_users
  1585→    users --> store_users
  1586→    stores --> store_settings
  1587→    stores --> customers
  1588→    stores --> themes
  1589→    stores --> pages
  1590→    stores --> navigation_menus
  1591→    stores --> search_settings
  1592→    stores --> shipping_zones
  1593→    stores --> tax_settings
  1594→    stores --> discounts
  1595→    stores --> products
  1596→    stores --> collections
  1597→    stores --> app_installations
  1598→    products --> product_options
  1599→    products --> product_variants
  1600→    products --> product_media
  1601→    collections --> collection_products
  1602→    products --> collection_products
  1603→    customers --> customer_addresses
  1604→    stores --> carts
  1605→    customers --> carts
  1606→    navigation_menus --> navigation_items
  1607→    themes --> theme_files
  1608→    themes --> theme_settings
  1609→    stores --> search_queries
  1610→    shipping_zones --> shipping_rates
  1611→    app_installations --> oauth_tokens
  1612→    stores --> webhook_subscriptions
  1613→    app_installations --> webhook_subscriptions
  1614→    product_options --> product_option_values
  1615→    stores --> inventory_items
  1616→    product_variants --> inventory_items
  1617→    carts --> cart_lines
  1618→    product_variants --> cart_lines
  1619→    stores --> checkouts
  1620→    carts --> checkouts
  1621→    customers --> checkouts
  1622→    stores --> orders
  1623→    customers --> orders
  1624→    stores --> analytics_events
  1625→    customers --> analytics_events
  1626→    stores --> analytics_daily
  1627→    webhook_subscriptions --> webhook_deliveries
  1628→    product_variants --> variant_option_values
  1629→    product_option_values --> variant_option_values
  1630→    orders --> order_lines
  1631→    products --> order_lines
  1632→    product_variants --> order_lines
  1633→    orders --> payments
  1634→    orders --> fulfillments
  1635→    orders --> refunds
  1636→    payments --> refunds
  1637→    fulfillments --> fulfillment_lines
  1638→    order_lines --> fulfillment_lines
  1639→```
  1640→
  1641→### Batch Details
  1642→
  1643→- **Batch 1** (no dependencies): `organizations`, `apps`
  1644→- **Batch 2** (depends on Batch 1): `stores` (references organizations), `users` (standalone, but logically grouped here)
  1645→- **Batch 3** (depends on Batch 2): `store_domains`, `store_users`, `store_settings`, `customers`, `themes`, `pages`, `navigation_menus`, `search_settings`, `shipping_zones`, `tax_settings`, `discounts`, `products`, `collections`, `app_installations`, `oauth_clients`
  1646→- **Batch 4** (depends on Batch 3): `product_options`, `product_variants`, `product_media`, `collection_products`, `customer_addresses`, `carts`, `navigation_items`, `theme_files`, `theme_settings`, `search_queries`, `shipping_rates`, `oauth_tokens`, `webhook_subscriptions`
  1647→- **Batch 5** (depends on Batch 4): `product_option_values`, `inventory_items`, `cart_lines`, `checkouts`, `orders`, `analytics_events`, `analytics_daily`, `webhook_deliveries`
  1648→- **Batch 6** (depends on Batch 5): `variant_option_values`, `order_lines`, `payments`, `fulfillments`
  1649→- **Batch 7** (depends on Batch 6): `refunds`, `fulfillment_lines`
  1650→
  1651→---
  1652→
  1653→## Index Strategy Rationale
  1654→
  1655→1. **Tenant-scoped composite indexes** - Most queries filter by `store_id` first, then by status, handle, or date. Composite indexes like (`store_id`, `status`) and (`store_id`, `handle`) serve these access patterns efficiently.
  1656→
  1657→2. **Unique constraints on business keys** - Handles, codes, email+store, and external identifiers (SKUs, barcodes, hostnames, OAuth client IDs) have unique indexes to enforce data integrity at the database level.
  1658→
  1659→3. **Foreign key indexes** - Every foreign key column has an index to avoid full table scans during JOIN and CASCADE operations. SQLite does not auto-create indexes for foreign keys like some other databases.
  1660→
  1661→4. **Composite primary keys for pivot tables** - Tables like `store_users`, `collection_products`, `variant_option_values`, and `analytics_daily` use composite primary keys to naturally enforce uniqueness and serve as their own covering index for the most common query pattern.
  1662→
  1663→5. **Date-based indexes for time series** - Tables with temporal queries (orders, analytics, search queries, webhook deliveries) include indexes on date/timestamp columns scoped by store for efficient range scans.
  1664→
  1665→6. **Selective indexing** - Not every column is indexed. Columns queried only via their parent's primary key (e.g., `cart_lines` always accessed through `cart_id`) rely on the foreign key index alone. This keeps write overhead and database size manageable.
  1666→
  1667→---
  1668→
  1669→## Table Count Summary
  1670→
  1671→| Epic | Tables | Count |
  1672→|---|---|---|
  1673→| 1 - Foundation and Multi-Store Tenancy | organizations, stores, store_domains, users, store_users, store_settings | 6 |
  1674→| 2 - Catalog, Collections, Inventory, Media | products, product_options, product_option_values, product_variants, variant_option_values, inventory_items, collections, collection_products, product_media | 9 |
  1675→| 3 - Themes, Storefront, Localization | themes, theme_files, theme_settings, pages, navigation_menus, navigation_items | 6 |
  1676→| 4 - Search | search_settings, search_queries | 2 |
  1677→| 5 - Cart, Checkout, Discounts, Shipping, Taxes | carts, cart_lines, checkouts, shipping_zones, shipping_rates, tax_settings, discounts | 7 |
  1678→| 6 - Payments, Orders, Fulfillment, Customers | customers, customer_addresses, orders, order_lines, payments, refunds, fulfillments, fulfillment_lines | 8 |
  1679→| 7 - Analytics | analytics_events, analytics_daily | 2 |
  1680→| 8 - Apps and Webhooks | apps, app_installations, oauth_clients, oauth_tokens, webhook_subscriptions, webhook_deliveries | 6 |
  1681→| **Total application tables** | | **46** |
  1682→| Laravel framework tables | sessions, cache, cache_locks, jobs, job_batches, failed_jobs, personal_access_tokens, password_reset_tokens, customer_password_reset_tokens | 9 |
  1683→| **Grand total** | | **55** |
  1684→

prompt:
Read the database schema spec at /Users/fabianwesner/Herd/shop/specs/01-DATABASE-SCHEMA.md and extract ONLY the table definitions for the following tables that belong to Phase 4 (Cart, Checkout, Discounts, Shipping, Taxes):
- carts
- cart_lines
- checkouts
- shipping_zones
- shipping_rates
- tax_settings
- discounts

Return the complete table definitions including columns, types, constraints, indexes, and any notes.


teamName: wild-sleeping-shell

userType: external

slug: iterative-seeking-pine

type: progress

type: assistant

type: message

role: assistant

service_tier: standard

prompt:
Read the database schema spec at /Users/fabianwesner/Herd/shop/specs/01-DATABASE-SCHEMA.md and extract ONLY the table definitions for the following tables that belong to Phase 5 (Payments, Orders, Fulfillment):
- customers
- customer_addresses
- orders
- order_lines
- payments
- refunds
- fulfillments
- fulfillment_lines

Return the complete table definitions including columns, types, constraints, indexes, and any notes.


type: user

role: user

prompt:
Read the database schema spec at /Users/fabianwesner/Herd/shop/specs/01-DATABASE-SCHEMA.md and extract ONLY the table definitions for the following tables that belong to Phase 5 (Payments, Orders, Fulfillment):
- customers
- customer_addresses
- orders
- order_lines
- payments
- refunds
- fulfillments
- fulfillment_lines

Return the complete table definitions including columns, types, constraints, indexes, and any notes.


Task

description: Read business logic spec

prompt:
Read the business logic spec at /Users/fabianwesner/Herd/shop/specs/05-BUSINESS-LOGIC.md and extract ALL the rules and details related to:
1. Cart operations (versioning, session binding, merge on login)
2. Checkout state machine (all transitions, validation rules)
3. Pricing engine (pipeline steps, rounding rules)
4. Discount system (validation rules, allocation, types)
5. Shipping calculator (zone matching, rate types, calculations)
6. Tax calculator (exclusive vs inclusive, basis points)

Return the complete business logic rules with all details.


type: text

text:
Read the business logic spec at /Users/fabianwesner/Herd/shop/specs/05-BUSINESS-LOGIC.md and extract ALL the rules and details related to:
1. Cart operations (versioning, session binding, merge on login)
2. Checkout state machine (all transitions, validation rules)
3. Pricing engine (pipeline steps, rounding rules)
4. Discount system (validation rules, allocation, types)
5. Shipping calculator (zone matching, rate types, calculations)
6. Tax calculator (exclusive vs inclusive, basis points)

Return the complete business logic rules with all details.

prompt:
Read the business logic spec at /Users/fabianwesner/Herd/shop/specs/05-BUSINESS-LOGIC.md and extract ALL the rules and details related to:
1. Cart operations (versioning, session binding, merge on login)
2. Checkout state machine (all transitions, validation rules)
3. Pricing engine (pipeline steps, rounding rules)
4. Discount system (validation rules, allocation, types)
5. Shipping calculator (zone matching, rate types, calculations)
6. Tax calculator (exclusive vs inclusive, basis points)

Return the complete business logic rules with all details.


prompt:
Read the business logic spec at /Users/fabianwesner/Herd/shop/specs/05-BUSINESS-LOGIC.md and extract ALL the rules and details related to:
1. Cart operations (versioning, session binding, merge on login)
2. Checkout state machine (all transitions, validation rules)
3. Pricing engine (pipeline steps, rounding rules)
4. Discount system (validation rules, allocation, types)
5. Shipping calculator (zone matching, rate types, calculations)
6. Tax calculator (exclusive vs inclusive, basis points)

Return the complete business logic rules with all details.