content:
<retrieval_status>not_ready</retrieval_status>

<task_id>a53c4c5</task_id>

<task_type>local_agent</task_type>

<status>running</status>

<output>
[Truncated. Full output: /private/tmp/claude-501/-Users-fabianwesner-Herd-shop/tasks/a53c4c5.output]

→|---|---|---|---|---|---|\n  1102→| id | TEXT | No | - | PRIMARY KEY | Session ID |\n  1103→| user_id | INTEGER | Yes | NULL | - | Associated user |\n  1104→| ip_address | TEXT | Yes | NULL | - | Client IP |\n  1105→| user_agent | TEXT | Yes | NULL | - | Client user agent |\n  1106→| payload | TEXT | No | - | - | Serialized session data |\n  1107→| last_activity | INTEGER | No | - | - | Unix timestamp |\n  1108→\n  1109→### cache\n  1110→\n  1111→| Column | Type | Nullable | Default | Constraints | Notes |\n  1112→|---|---|---|---|---|---|\n  1113→| key | TEXT | No | - | PRIMARY KEY | Cache key |\n  1114→| value | TEXT | No | - | - | Serialized cache value |\n  1115→| expiration | INTEGER | No | - | - | Unix timestamp |\n  1116→\n  1117→### cache_locks\n  1118→\n  1119→| Column | Type | Nullable | Default | Constraints | Notes |\n  1120→|---|---|---|---|---|---|\n  1121→| key | TEXT | No | - | PRIMARY KEY | Lock key |\n  1122→| owner | TEXT | No | - | - | Lock owner identifier |\n  1123→| expiration | INTEGER | No | - | - | Unix timestamp |\n  1124→\n  1125→### jobs\n  1126→\n  1127→| Column | Type | Nullable | Default | Constraints | Notes |\n  1128→|---|---|---|---|---|---|\n  1129→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n  1130→| queue | TEXT | No | - | - | Queue name |\n  1131→| payload | TEXT | No | - | - | Serialized job payload |\n  1132→| attempts | INTEGER | No | - | - | Number of attempts |\n  1133→| reserved_at | INTEGER | Yes | NULL | - | Unix timestamp |\n  1134→| available_at | INTEGER | No | - | - | Unix timestamp |\n  1135→| created_at | INTEGER | No | - | - | Unix timestamp |\n  1136→\n  1137→### job_batches\n  1138→\n  1139→| Column | Type | Nullable | Default | Constraints | Notes |\n  1140→|---|---|---|---|---|---|\n  1141→| id | TEXT | No | - | PRIMARY KEY | Batch UUID |\n  1142→| name | TEXT | No | - | - | Batch name |\n  1143→| total_jobs | INTEGER | No | - | - | Total jobs in batch |\n  1144→| pending_jobs | INTEGER | No | - | - | Pending count |\n  1145→| failed_jobs | INTEGER | No | - | - | Failed count |\n  1146→| failed_job_ids | TEXT | No | - | - | Serialized list of failed IDs |\n  1147→| options | TEXT | Yes | NULL | - | Serialized batch options |\n  1148→| cancelled_at | INTEGER | Yes | NULL | - | Unix timestamp |\n  1149→| created_at | INTEGER | No | - | - | Unix timestamp |\n  1150→| finished_at | INTEGER | Yes | NULL | - | Unix timestamp |\n  1151→\n  1152→### failed_jobs\n  1153→\n  1154→| Column | Type | Nullable | Default | Constraints | Notes |\n  1155→|---|---|---|---|---|---|\n  1156→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n  1157→| uuid | TEXT | No | - | UNIQUE | Job UUID |\n  1158→| connection | TEXT | No | - | - | Queue connection name |\n  1159→| queue | TEXT | No | - | - | Queue name |\n  1160→| payload | TEXT | No | - | - | Serialized job payload |\n  1161→| exception | TEXT | No | - | - | Exception trace |\n  1162→| failed_at | TEXT | No | CURRENT_TIMESTAMP | - | ISO-8601 timestamp |\n  1163→\n  1164→### personal_access_tokens\n  1165→\n  1166→| Column | Type | Nullable | Default | Constraints | Notes |\n  1167→|---|---|---|---|---|---|\n  1168→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n  1169→| tokenable_type | TEXT | No | - | - | Polymorphic type |\n  1170→| tokenable_id | INTEGER | No | - | - | Polymorphic ID |\n  1171→| name | TEXT | No | - | - | Token name |\n  1172→| token | TEXT | No | - | UNIQUE | SHA-256 hash of the token |\n  1173→| abilities | TEXT | Yes | NULL | - | JSON array of abilities |\n  1174→| last_used_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n  1175→| expires_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n  1176→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n  1177→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n  1178→\n  1179→### password_reset_tokens\n  1180→\n  1181→| Column | Type | Nullable | Default | Constraints | Notes |\n  1182→|---|---|---|---|---|---|\n  1183→| email | TEXT | No | - | PRIMARY KEY | Admin user email |\n  1184→| token | TEXT | No | - | - | Hashed reset token |\n  1185→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n  1186→\n  1187→---\n  1188→\n  1189→### customer_password_reset_tokens\n  1190→\n  1191→| Column | Type | Nullable | Default | Constraints | Notes |\n  1192→|---|---|---|---|---|---|\n  1193→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n  1194→| email | TEXT | No | - | - | Customer email |\n  1195→| token | TEXT | No | - | - | Hashed reset token |\n  1196→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n  1197→\n  1198→**Primary Key:** (`store_id`, `email`)\n  1199→\n  1200→---\n  1201→\n  1202→## Entity Relationship Diagrams\n  1203→\n  1204→### Epic 1 - Foundation and Tenancy\n  1205→\n  1206→```mermaid\n  1207→erDiagram\n  1208→    organizations ||--o{ stores : \"owns\"\n  1209→    stores ||--o{ store_domains : \"has\"\n  1210→    stores ||--o{ store_users : \"links\"\n  1211→    users ||--o{ store_users : \"links\"\n  1212→    stores ||--|| store_settings : \"has\"\n  1213→\n  1214→    organizations {\n  1215→        INTEGER id PK\n  1216→        TEXT name\n  1217→        TEXT billing_email\n  1218→    }\n  1219→    stores {\n  1220→        INTEGER id PK\n  1221→        INTEGER organization_id FK\n  1222→        TEXT name\n  1223→        TEXT handle\n  1224→        TEXT status\n  1225→    }\n  1226→    store_domains {\n  1227→        INTEGER id PK\n  1228→        INTEGER store_id FK\n  1229→        TEXT hostname\n  1230→        TEXT type\n  1231→        INTEGER is_primary\n  1232→    }\n  1233→    users {\n  1234→        INTEGER id PK\n  1235→        TEXT email\n  1236→        TEXT password_hash\n  1237→        TEXT name\n  1238→        TEXT status\n  1239→    }\n  1240→    store_users {\n  1241→        INTEGER store_id PK\n  1242→        INTEGER user_id PK\n  1243→        TEXT role\n  1244→    }\n  1245→    store_settings {\n  1246→        INTEGER store_id PK\n  1247→        TEXT settings_json\n  1248→    }\n  1249→```\n  1250→\n  1251→### Epic 2 - Catalog, Collections, Inventory, and Media\n  1252→\n  1253→```mermaid\n  1254→erDiagram\n  1255→    stores ||--o{ products : \"has\"\n  1256→    stores ||--o{ collections : \"has\"\n  1257→    products ||--o{ product_options : \"defines\"\n  1258→    product_options ||--o{ product_option_values : \"contains\"\n  1259→    products ||--o{ product_variants : \"has\"\n  1260→    product_variants }o--o{ product_option_values : \"variant_option_values\"\n  1261→    product_variants ||--|| inventory_items : \"tracked by\"\n  1262→    stores ||--o{ inventory_items : \"has\"\n  1263→    collections }o--o{ products : \"collection_products\"\n  1264→    products ||--o{ product_media : \"has\"\n  1265→\n  1266→    products {\n  1267→        INTEGER id PK\n  1268→        INTEGER store_id FK\n  1269→        TEXT title\n  1270→        TEXT handle\n  1271→        TEXT status\n  1272→    }\n  1273→    product_options {\n  1274→        INTEGER id PK\n  1275→        INTEGER product_id FK\n  1276→        TEXT name\n  1277→        INTEGER position\n  1278→    }\n  1279→    product_option_values {\n  1280→        INTEGER id PK\n  1281→        INTEGER product_option_id FK\n  1282→        TEXT value\n  1283→        INTEGER position\n  1284→    }\n  1285→    product_variants {\n  1286→        INTEGER id PK\n  1287→        INTEGER product_id FK\n  1288→        TEXT sku\n  1289→        INTEGER price_amount\n  1290→        TEXT status\n  1291→    }\n  1292→    inventory_items {\n  1293→        INTEGER id PK\n  1294→        INTEGER store_id FK\n  1295→        INTEGER variant_id FK\n  1296→        INTEGER quantity_on_hand\n  1297→        INTEGER quantity_reserved\n  1298→        TEXT policy\n  1299→    }\n  1300→    collections {\n  1301→        INTEGER id PK\n  1302→        INTEGER store_id FK\n  1303→        TEXT title\n  1304→        TEXT handle\n  1305→        TEXT type\n  1306→        TEXT status\n  1307→    }\n  1308→    product_media {\n  1309→        INTEGER id PK\n  1310→        INTEGER product_id FK\n  1311→        TEXT type\n  1312→        TEXT storage_key\n  1313→        INTEGER position\n  1314→        TEXT status\n  1315→    }\n  1316→```\n  1317→\n  1318→### Epic 3 - Themes, Storefront, and Localization\n  1319→\n  1320→```mermaid\n  1321→erDiagram\n  1322→    stores ||--o{ themes : \"has\"\n  1323→    themes ||--o{ theme_files : \"contains\"\n  1324→    themes ||--|| theme_settings : \"configured by\"\n  1325→    stores ||--o{ pages : \"has\"\n  1326→    stores ||--o{ navigation_menus : \"has\"\n  1327→    navigation_menus ||--o{ navigation_items : \"contains\"\n  1328→\n  1329→    themes {\n  1330→        INTEGER id PK\n  1331→        INTEGER store_id FK\n  1332→        TEXT name\n  1333→        TEXT status\n  1334→    }\n  1335→    theme_files {\n  1336→        INTEGER id PK\n  1337→        INTEGER theme_id FK\n  1338→        TEXT path\n  1339→        TEXT storage_key\n  1340→    }\n  1341→    theme_settings {\n  1342→        INTEGER theme_id PK\n  1343→        TEXT settings_json\n  1344→    }\n  1345→    pages {\n  1346→        INTEGER id PK\n  1347→        INTEGER store_id FK\n  1348→        TEXT title\n  1349→        TEXT handle\n  1350→        TEXT status\n  1351→    }\n  1352→    navigation_menus {\n  1353→        INTEGER id PK\n  1354→        INTEGER store_id FK\n  1355→        TEXT handle\n  1356→        TEXT title\n  1357→    }\n  1358→    navigation_items {\n  1359→        INTEGER id PK\n  1360→        INTEGER menu_id FK\n  1361→        TEXT type\n  1362→        TEXT label\n  1363→        INTEGER position\n  1364→    }\n  1365→```\n  1366→\n  1367→### Epics 5-6 - Cart, Checkout, Orders, and Payments\n  1368→\n  1369→```mermaid\n  1370→erDiagram\n  1371→    stores ||--o{ customers : \"has\"\n  1372→    customers ||--o{ customer_addresses : \"has\"\n  1373→    stores ||--o{ carts : \"has\"\n  1374→    customers ||--o{ carts : \"owns\"\n  1375→    carts ||--o{ cart_lines : \"contains\"\n  1376→    product_variants ||--o{ cart_lines : \"referenced by\"\n  1377→    stores ||--o{ checkouts : \"has\"\n  1378→    carts ||--o{ checkouts : \"converted to\"\n  1379→    stores ||--o{ orders : \"has\"\n  1380→    customers ||--o{ orders : \"placed\"\n  1381→    orders ||--o{ order_lines : \"contains\"\n  1382→    orders ||--o{ payments : \"paid via\"\n  1383→    orders ||--o{ refunds : \"refunded via\"\n  1384→    payments ||--o{ refunds : \"source of\"\n  1385→    orders ||--o{ fulfillments : \"shipped via\"\n  1386→    fulfillments ||--o{ fulfillment_lines : \"contains\"\n  1387→    order_lines ||--o{ fulfillment_lines : \"fulfilled by\"\n  1388→\n  1389→    customers {\n  1390→        INTEGER id PK\n  1391→        INTEGER store_id FK\n  1392→        TEXT email\n  1393→        TEXT name\n  1394→    }\n  1395→    carts {\n  1396→        INTEGER id PK\n  1397→        INTEGER store_id FK\n  1398→        INTEGER customer_id FK\n  1399→        TEXT status\n  1400→    }\n  1401→    orders {\n  1402→        INTEGER id PK\n  1403→        INTEGER store_id FK\n  1404→        INTEGER customer_id FK\n  1405→        TEXT order_number\n  1406→        TEXT payment_method\n  1407→        TEXT status\n  1408→        TEXT financial_status\n  1409→        TEXT fulfillment_status\n  1410→        INTEGER total_amount\n  1411→    }\n  1412→    payments {\n  1413→        INTEGER id PK\n  1414→        INTEGER order_id FK\n  1415→        TEXT provider\n  1416→        TEXT method\n  1417→        TEXT status\n  1418→        INTEGER amount\n  1419→    }\n  1420→    fulfillments {\n  1421→        INTEGER id PK\n  1422→        INTEGER order_id FK\n  1423→        TEXT status\n  1424→        TEXT tracking_number\n  1425→    }\n  1426→```\n  1427→\n  1428→### Epics 7-8 - Analytics, Apps, and Webhooks\n  1429→\n  1430→```mermaid\n  1431→erDiagram\n  1432→    stores ||--o{ analytics_events : \"records\"\n  1433→    stores ||--o{ analytics_daily : \"aggregates\"\n  1434→    customers ||--o{ analytics_events : \"generates\"\n  1435→    apps ||--o{ app_installations : \"installed via\"\n  1436→    stores ||--o{ app_installations : \"has\"\n  1437→    apps ||--o{ oauth_clients : \"authenticates via\"\n  1438→    app_installations ||--o{ oauth_tokens : \"issues\"\n  1439→    stores ||--o{ webhook_subscriptions : \"registers\"\n  1440→    app_installations ||--o{ webhook_subscriptions : \"owns\"\n  1441→    webhook_subscriptions ||--o{ webhook_deliveries : \"delivers\"\n  1442→\n  1443→    analytics_events {\n  1444→        INTEGER id PK\n  1445→        INTEGER store_id FK\n  1446→        TEXT type\n  1447→        TEXT session_id\n  1448→    }\n  1449→    analytics_daily {\n  1450→        INTEGER store_id PK\n  1451→        TEXT date PK\n  1452→        INTEGER orders_count\n  1453→        INTEGER revenue_amount\n  1454→    }\n  1455→    apps {\n  1456→        INTEGER id PK\n  1457→        TEXT name\n  1458→        TEXT status\n  1459→    }\n  1460→    app_installations {\n  1461→        INTEGER id PK\n  1462→        INTEGER store_id FK\n  1463→        INTEGER app_id FK\n  1464→        TEXT status\n  1465→    }\n  1466→    webhook_subscriptions {\n  1467→        INTEGER id PK\n  1468→        INTEGER store_id FK\n  1469→        TEXT event_type\n  1470→        TEXT target_url\n  1471→    }\n  1472→    webhook_deliveries {\n  1473→        INTEGER id PK\n  1474→        INTEGER subscription_id FK\n  1475→        TEXT event_id\n  1476→        TEXT status\n  1477→    }\n  1478→```\n  1479→\n  1480→---\n  1481→\n  1482→## SQLite-Specific Configuration Requirements\n  1483→\n  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.\n  1485→\n  1486→### Required PRAGMA Settings\n  1487→\n  1488→| Pragma | Value | Purpose |\n  1489→|---|---|---|\n  1490→| `foreign_keys` | ON | Enforce referential integrity on every connection. Without this, foreign key constraints are silently ignored by SQLite. |\n  1491→| `journal_mode` | WAL | Enable Write-Ahead Logging to allow concurrent reads during writes. Critical for web application performance. |\n  1492→| `busy_timeout` | 5000 | Wait up to 5 seconds for a write lock before throwing an error. Prevents immediate failures under concurrent load. |\n  1493→| `synchronous` | NORMAL | Balances durability and performance. Safe when used with WAL mode; avoids fsync on every commit. |\n  1494→| `cache_size` | -20000 | Use up to 20 MB of page cache. Set in AppServiceProvider if needed. Negative value means kilobytes. |\n  1495→\n  1496→### Laravel Configuration Requirements\n  1497→\n  1498→- The SQLite driver connection must set `foreign_key_constraints` to `true` so that every new database connection enables foreign key enforcement.\n  1499→- The `journal_mode` must be set to `wal` (Write-Ahead Logging) for concurrent read support.\n  1500→- The `busy_timeout` must be set to `5000` (milliseconds) to handle write contention gracefully.\n  1501→- The `synchronous` mode must be set to `normal` for balanced durability with WAL mode.\n  1502→- The `transaction_mode` should be set to `DEFERRED` (the default), meaning transactions acquire locks lazily.\n  1503→- The `cache_size` pragma (set to `-20000` for 20 MB) should be applied via the AppServiceProvider boot method if needed for performance tuning.\n  1504→\n  1505→---\n  1506→\n  1507→## Migration Dependency Graph\n  1508→\n  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.\n  1510→\n  1511→```mermaid\n  1512→flowchart TD\n  1513→    subgraph \"Batch 1 - No Dependencies\"\n  1514→        organizations\n  1515→        apps\n  1516→    end\n  1517→\n  1518→    subgraph \"Batch 2 - Depends on Batch 1\"\n  1519→        stores\n  1520→        users\n  1521→    end\n  1522→\n  1523→    subgraph \"Batch 3 - Depends on Batch 2\"\n  1524→        store_domains\n  1525→        store_users\n  1526→        store_settings\n  1527→        customers\n  1528→        themes\n  1529→        pages\n  1530→        navigation_menus\n  1531→        search_settings\n  1532→        shipping_zones\n  1533→        tax_settings\n  1534→        discounts\n  1535→        products\n  1536→        collections\n  1537→        app_installations\n  1538→        oauth_clients\n  1539→    end\n  1540→\n  1541→    subgraph \"Batch 4 - Depends on Batch 3\"\n  1542→        product_options\n  1543→        product_variants\n  1544→        product_media\n  1545→        collection_products\n  1546→        customer_addresses\n  1547→        carts\n  1548→        navigation_items\n  1549→        theme_files\n  1550→        theme_settings\n  1551→        search_queries\n  1552→        shipping_rates\n  1553→        oauth_tokens\n  1554→        webhook_subscriptions\n  1555→    end\n  1556→\n  1557→    subgraph \"Batch 5 - Depends on Batch 4\"\n  1558→        product_option_values\n  1559→        inventory_items\n  1560→        cart_lines\n  1561→        checkouts\n  1562→        orders\n  1563→        analytics_events\n  1564→        analytics_daily\n  1565→        webhook_deliveries\n  1566→    end\n  1567→\n  1568→    subgraph \"Batch 6 - Depends on Batch 5\"\n  1569→        variant_option_values\n  1570→        order_lines\n  1571→        payments\n  1572→        fulfillments\n  1573→    end\n  1574→\n  1575→    subgraph \"Batch 7 - Depends on Batch 6\"\n  1576→        refunds\n  1577→        fulfillment_lines\n  1578→    end\n  1579→\n  1580→    organizations --> stores\n  1581→    apps --> app_installations\n  1582→    apps --> oauth_clients\n  1583→    stores --> store_domains\n  1584→    stores --> store_users\n  1585→    users --> store_users\n  1586→    stores --> store_settings\n  1587→    stores --> customers\n  1588→    stores --> themes\n  1589→    stores --> pages\n  1590→    stores --> navigation_menus\n  1591→    stores --> search_settings\n  1592→    stores --> shipping_zones\n  1593→    stores --> tax_settings\n  1594→    stores --> discounts\n  1595→    stores --> products\n  1596→    stores --> collections\n  1597→    stores --> app_installations\n  1598→    products --> product_options\n  1599→    products --> product_variants\n  1600→    products --> product_media\n  1601→    collections --> collection_products\n  1602→    products --> collection_products\n  1603→    customers --> customer_addresses\n  1604→    stores --> carts\n  1605→    customers --> carts\n  1606→    navigation_menus --> navigation_items\n  1607→    themes --> theme_files\n  1608→    themes --> theme_settings\n  1609→    stores --> search_queries\n  1610→    shipping_zones --> shipping_rates\n  1611→    app_installations --> oauth_tokens\n  1612→    stores --> webhook_subscriptions\n  1613→    app_installations --> webhook_subscriptions\n  1614→    product_options --> product_option_values\n  1615→    stores --> inventory_items\n  1616→    product_variants --> inventory_items\n  1617→    carts --> cart_lines\n  1618→    product_variants --> cart_lines\n  1619→    stores --> checkouts\n  1620→    carts --> checkouts\n  1621→    customers --> checkouts\n  1622→    stores --> orders\n  1623→    customers --> orders\n  1624→    stores --> analytics_events\n  1625→    customers --> analytics_events\n  1626→    stores --> analytics_daily\n  1627→    webhook_subscriptions --> webhook_deliveries\n  1628→    product_variants --> variant_option_values\n  1629→    product_option_values --> variant_option_values\n  1630→    orders --> order_lines\n  1631→    products --> order_lines\n  1632→    product_variants --> order_lines\n  1633→    orders --> payments\n  1634→    orders --> fulfillments\n  1635→    orders --> refunds\n  1636→    payments --> refunds\n  1637→    fulfillments --> fulfillment_lines\n  1638→    order_lines --> fulfillment_lines\n  1639→```\n  1640→\n  1641→### Batch Details\n  1642→\n  1643→- **Batch 1** (no dependencies): `organizations`, `apps`\n  1644→- **Batch 2** (depends on Batch 1): `stores` (references organizations), `users` (standalone, but logically grouped here)\n  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`\n  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`\n  1647→- **Batch 5** (depends on Batch 4): `product_option_values`, `inventory_items`, `cart_lines`, `checkouts`, `orders`, `analytics_events`, `analytics_daily`, `webhook_deliveries`\n  1648→- **Batch 6** (depends on Batch 5): `variant_option_values`, `order_lines`, `payments`, `fulfillments`\n  1649→- **Batch 7** (depends on Batch 6): `refunds`, `fulfillment_lines`\n  1650→\n  1651→---\n  1652→\n  1653→## Index Strategy Rationale\n  1654→\n  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.\n  1656→\n  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.\n  1658→\n  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.\n  1660→\n  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.\n  1662→\n  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.\n  1664→\n  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.\n  1666→\n  1667→---\n  1668→\n  1669→## Table Count Summary\n  1670→\n  1671→| Epic | Tables | Count |\n  1672→|---|---|---|\n  1673→| 1 - Foundation and Multi-Store Tenancy | organizations, stores, store_domains, users, store_users, store_settings | 6 |\n  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 |\n  1675→| 3 - Themes, Storefront, Localization | themes, theme_files, theme_settings, pages, navigation_menus, navigation_items | 6 |\n  1676→| 4 - Search | search_settings, search_queries | 2 |\n  1677→| 5 - Cart, Checkout, Discounts, Shipping, Taxes | carts, cart_lines, checkouts, shipping_zones, shipping_rates, tax_settings, discounts | 7 |\n  1678→| 6 - Payments, Orders, Fulfillment, Customers | customers, customer_addresses, orders, order_lines, payments, refunds, fulfillments, fulfillment_lines | 8 |\n  1679→| 7 - Analytics | analytics_events, analytics_daily | 2 |\n  1680→| 8 - Apps and Webhooks | apps, app_installations, oauth_clients, oauth_tokens, webhook_subscriptions, webhook_deliveries | 6 |\n  1681→| **Total application tables** | | **46** |\n  1682→| Laravel framework tables | sessions, cache, cache_locks, jobs, job_batches, failed_jobs, personal_access_tokens, password_reset_tokens, customer_password_reset_tokens | 9 |\n  1683→| **Grand total** | | **55** |\n  1684→"}]},"uuid":"8ba694ae-76a1-43f5-bb6e-4a0430c76338","timestamp":"2026-02-13T15:24:03.656Z","sourceToolAssistantUUID":"9f08165f-8925-4736-9d96-ec4d2ac5a1ee"}
{"parentUuid":"8ba694ae-76a1-43f5-bb6e-4a0430c76338","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","message":{"model":"claude-opus-4-6","id":"msg_015JmDnhpuCL9T1ibvYQNUiN","type":"message","role":"assistant","content":[{"type":"tool_use","id":"toolu_01GJVqXw5YNphE2nBfqxFtys","name":"Read","input":{"file_path":"/Users/fabianwesner/Herd/shop/specs/09-IMPLEMENTATION-ROADMAP.md"}}],"stop_reason":null,"stop_sequence":null,"usage":{"input_tokens":2,"cache_creation_input_tokens":5681,"cache_read_input_tokens":16158,"cache_creation":{"ephemeral_5m_input_tokens":5681,"ephemeral_1h_input_tokens":0},"output_tokens":2,"service_tier":"standard","inference_geo":"not_available"}},"requestId":"req_011CY6JJGj71Af8sf94q29QN","type":"assistant","uuid":"c5ff7107-e269-40f3-ab3e-0d35a1aa406f","timestamp":"2026-02-13T15:24:03.993Z"}
{"parentUuid":"c5ff7107-e269-40f3-ab3e-0d35a1aa406f","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","type":"user","message":{"role":"user","content":[{"type":"tool_result","content":"File content (32506 tokens) exceeds maximum allowed tokens (25000). Please use offset and limit parameters to read specific portions of the file, or use the GrepTool to search for specific content.","is_error":true,"tool_use_id":"toolu_01GJVqXw5YNphE2nBfqxFtys"}]},"uuid":"cec11991-3c33-4116-835e-0f488e847774","timestamp":"2026-02-13T15:24:04.276Z","toolUseResult":"Error: File content (32506 tokens) exceeds maximum allowed tokens (25000). Please use offset and limit parameters to read specific portions of the file, or use the GrepTool to search for specific content.","sourceToolAssistantUUID":"c5ff7107-e269-40f3-ab3e-0d35a1aa406f"}
{"parentUuid":"cec11991-3c33-4116-835e-0f488e847774","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","message":{"model":"claude-opus-4-6","id":"msg_015JmDnhpuCL9T1ibvYQNUiN","type":"message","role":"assistant","content":[{"type":"tool_use","id":"toolu_01KuW7xk1sGCYhFTCYdY2Cfo","name":"Read","input":{"file_path":"/Users/fabianwesner/Herd/shop/specs/04-STOREFRONT-UI.md"}}],"stop_reason":null,"stop_sequence":null,"usage":{"input_tokens":2,"cache_creation_input_tokens":5681,"cache_read_input_tokens":16158,"cache_creation":{"ephemeral_5m_input_tokens":5681,"ephemeral_1h_input_tokens":0},"output_tokens":2,"service_tier":"standard","inference_geo":"not_available"}},"requestId":"req_011CY6JJGj71Af8sf94q29QN","type":"assistant","uuid":"971bdb3f-ec0f-4682-b642-1724c58c3141","timestamp":"2026-02-13T15:24:04.470Z"}
{"parentUuid":"971bdb3f-ec0f-4682-b642-1724c58c3141","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","type":"user","message":{"role":"user","content":[{"type":"tool_result","content":"<tool_use_error>Sibling tool call errored</tool_use_error>","is_error":true,"tool_use_id":"toolu_01KuW7xk1sGCYhFTCYdY2Cfo"}]},"uuid":"c7a15f66-f68a-4b61-8962-fee68132e6c6","timestamp":"2026-02-13T15:24:04.471Z","toolUseResult":"Sibling tool call errored","sourceToolAssistantUUID":"971bdb3f-ec0f-4682-b642-1724c58c3141"}
{"parentUuid":"c7a15f66-f68a-4b61-8962-fee68132e6c6","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","message":{"model":"claude-opus-4-6","id":"msg_015JmDnhpuCL9T1ibvYQNUiN","type":"message","role":"assistant","content":[{"type":"tool_use","id":"toolu_01Vheb2kXmibctGRDeb1ja4G","name":"Read","input":{"file_path":"/Users/fabianwesner/Herd/shop/specs/05-BUSINESS-LOGIC.md"}}],"stop_reason":null,"stop_sequence":null,"usage":{"input_tokens":2,"cache_creation_input_tokens":5681,"cache_read_input_tokens":16158,"cache_creation":{"ephemeral_5m_input_tokens":5681,"ephemeral_1h_input_tokens":0},"output_tokens":2,"service_tier":"standard","inference_geo":"not_available"}},"requestId":"req_011CY6JJGj71Af8sf94q29QN","type":"assistant","uuid":"e9a30af6-c154-4a6f-ab41-2c05a1bbd7e0","timestamp":"2026-02-13T15:24:05.036Z"}
{"parentUuid":"e9a30af6-c154-4a6f-ab41-2c05a1bbd7e0","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","type":"user","message":{"role":"user","content":[{"type":"tool_result","content":"<tool_use_error>Sibling tool call errored</tool_use_error>","is_error":true,"tool_use_id":"toolu_01Vheb2kXmibctGRDeb1ja4G"}]},"uuid":"e65faad8-81c0-44ae-a50a-48047ebdd7f6","timestamp":"2026-02-13T15:24:05.036Z","toolUseResult":"Sibling tool call errored","sourceToolAssistantUUID":"e9a30af6-c154-4a6f-ab41-2c05a1bbd7e0"}
</output>

status: running

description: Phase 8: Search with FTS5

output:
{"parentUuid":null,"isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","type":"user","message":{"role":"user","content":"You are implementing Phase 8 (Search) of an e-commerce shop in Laravel 12 with Livewire v4, Flux UI Free v2, SQLite, and Pest v4.\n\n## Context\n- Phases 1-4 complete. Working directory: /Users/fabianwesner/Herd/shop\n- 208 existing tests pass. DO NOT modify existing tests.\n- Read specs: `specs/01-DATABASE-SCHEMA.md`, `specs/09-IMPLEMENTATION-ROADMAP.md`\n- Read existing code patterns before implementing.\n\n## What to implement\n\n### Step 8.1: Migrations (numbered 000500-000520)\n- `search_settings` - PK: store_id, synonyms_json, stop_words_json\n- `search_queries` - store_id, query_text, results_count, customer_id (nullable), searched_at\n- FTS5 virtual table migration (raw SQL): `products_fts` with columns: title, description, vendor, product_type, tags; content synced from products table\n\n### Step 8.2: Search Service (`App\\Services\\SearchService`)\n- `search(Store $store, string $query, array $filters, int $perPage): LengthAwarePaginator`\n  - FTS5 query with store scoping, filter by vendor/collection/price range\n- `autocomplete(Store $store, string $prefix, int $limit): Collection`\n  - Prefix matching for search-as-you-type\n- `syncProduct(Product $product): void` - upsert into FTS5 index\n- `removeProduct(int $productId): void` - delete from FTS5\n- Create `App\\Observers\\ProductObserver` that calls syncProduct/removeProduct on create/update/delete\n- Register observer in AppServiceProvider\n\n### Step 8.3: Search UI\nUpdate existing storefront search components:\n- `Storefront\\Search\\Index` - full search results page with filters (vendor, price range, collection), sort (relevance, price, newest), pagination\n- Update header search to use autocomplete\n\n### Models\n- SearchSettings (PK: store_id)\n- SearchQuery (BelongsToStore)\n\n### Tests (target 15+ new tests)\n- SearchServiceTest: FTS5 search, filters, sorting, store isolation\n- AutocompleteTest: prefix matching, result limiting\n- ProductObserverTest: sync on create/update, remove on delete\n\n## Rules\n- Use existing patterns from Phase 1-4 code\n- Run `vendor/bin/pint --dirty --format agent` before finishing\n- Run `php artisan test --compact` to verify ALL tests pass\n- DO NOT modify existing tests. DO NOT create git commits.\n"},"uuid":"6027c54b-7191-4748-bfef-714440cfd9da","timestamp":"2026-02-13T15:24:00.292Z"}
{"parentUuid":"6027c54b-7191-4748-bfef-714440cfd9da","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","message":{"model":"claude-opus-4-6","id":"msg_015JmDnhpuCL9T1ibvYQNUiN","type":"message","role":"assistant","content":[{"type":"text","text":"\n\nI'll start by reading the specs and understanding the existing codebase patterns before implementing Phase 8."}],"stop_reason":null,"stop_sequence":null,"usage":{"input_tokens":2,"cache_creation_input_tokens":5681,"cache_read_input_tokens":16158,"cache_creation":{"ephemeral_5m_input_tokens":5681,"ephemeral_1h_input_tokens":0},"output_tokens":2,"service_tier":"standard","inference_geo":"not_available"}},"requestId":"req_011CY6JJGj71Af8sf94q29QN","type":"assistant","uuid":"b5742d01-7ae4-46f1-b0a3-d2c583ac14b5","timestamp":"2026-02-13T15:24:02.841Z"}
{"parentUuid":"b5742d01-7ae4-46f1-b0a3-d2c583ac14b5","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","message":{"model":"claude-opus-4-6","id":"msg_015JmDnhpuCL9T1ibvYQNUiN","type":"message","role":"assistant","content":[{"type":"tool_use","id":"toolu_01GNEcTy7RJcZJfKFT1mx1RN","name":"Read","input":{"file_path":"/Users/fabianwesner/Herd/shop/specs/01-DATABASE-SCHEMA.md"}}],"stop_reason":null,"stop_sequence":null,"usage":{"input_tokens":2,"cache_creation_input_tokens":5681,"cache_read_input_tokens":16158,"cache_creation":{"ephemeral_5m_input_tokens":5681,"ephemeral_1h_input_tokens":0},"output_tokens":2,"service_tier":"standard","inference_geo":"not_available"}},"requestId":"req_011CY6JJGj71Af8sf94q29QN","type":"assistant","uuid":"9f08165f-8925-4736-9d96-ec4d2ac5a1ee","timestamp":"2026-02-13T15:24:03.406Z"}
{"parentUuid":"9f08165f-8925-4736-9d96-ec4d2ac5a1ee","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","type":"progress","data":{"type":"hook_progress","hookEvent":"PostToolUse","hookName":"PostToolUse:Read","command":"callback"},"parentToolUseID":"toolu_01GNEcTy7RJcZJfKFT1mx1RN","toolUseID":"toolu_01GNEcTy7RJcZJfKFT1mx1RN","timestamp":"2026-02-13T15:24:03.657Z","uuid":"a492edf6-9d4c-4673-b62c-ac597b91bd45"}
{"parentUuid":"9f08165f-8925-4736-9d96-ec4d2ac5a1ee","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","type":"user","message":{"role":"user","content":[{"tool_use_id":"toolu_01GNEcTy7RJcZJfKFT1mx1RN","type":"tool_result","content":"     1→# 01 - Database Schema Specification\n     2→\n     3→> E-commerce platform - SQLite database schema for Laravel 12\n     4→\n     5→---\n     6→\n     7→## Conventions\n     8→\n     9→| Convention | Detail |\n    10→|---|---|\n    11→| Primary keys | Auto-incrementing integer primary key on every table (column: `id`) |\n    12→| Tenant isolation | `store_id` foreign key on every tenant-scoped table |\n    13→| Monetary amounts | Stored as **integer in minor units** (cents). Column names end in `_amount`. |\n    14→| Booleans | Integer, not null, default 0 (0 = false, 1 = true) |\n    15→| JSON data | Text column - Laravel casts to/from JSON at the application layer |\n    16→| Enumerations | Check constraints on text columns |\n    17→| Timestamps | Text (ISO-8601) via Laravel's `created_at` / `updated_at` |\n    18→| Soft deletes | Only where explicitly noted (`deleted_at` text, nullable) |\n    19→| Encrypted columns | Suffixed `_encrypted` - encrypted at application layer, stored as text |\n    20→\n    21→---\n    22→\n    23→## Epic 1 - Foundation and Multi-Store Tenancy\n    24→\n    25→### organizations\n    26→\n    27→Billing entity that owns one or more stores.\n    28→\n    29→| Column | Type | Nullable | Default | Constraints | Notes |\n    30→|---|---|---|---|---|---|\n    31→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n    32→| name | TEXT | No | - | - | Organization display name |\n    33→| billing_email | TEXT | No | - | - | Primary billing contact |\n    34→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n    35→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n    36→\n    37→**Indexes:**\n    38→\n    39→- `idx_organizations_billing_email` on (`billing_email`) - Supports lookup by billing email\n    40→\n    41→---\n    42→\n    43→### stores\n    44→\n    45→Individual storefront belonging to an organization.\n    46→\n    47→| Column | Type | Nullable | Default | Constraints | Notes |\n    48→|---|---|---|---|---|---|\n    49→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n    50→| organization_id | INTEGER | No | - | FK -> organizations(id) ON DELETE CASCADE | Parent organization |\n    51→| name | TEXT | No | - | - | Store display name |\n    52→| handle | TEXT | No | - | UNIQUE per table | URL-friendly slug |\n    53→| status | TEXT | No | 'active' | CHECK IN ('active', 'suspended') | |\n    54→| default_currency | TEXT | No | 'USD' | - | ISO 4217 currency code |\n    55→| default_locale | TEXT | No | 'en' | - | IETF locale tag |\n    56→| timezone | TEXT | No | 'UTC' | - | IANA timezone |\n    57→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n    58→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n    59→\n    60→**Indexes:**\n    61→\n    62→- `idx_stores_handle` UNIQUE on (`handle`) - Ensures globally unique store handles for URL routing\n    63→- `idx_stores_organization_id` on (`organization_id`) - FK lookup and listing stores by org\n    64→- `idx_stores_status` on (`status`) - Filtering stores by active/suspended status\n    65→\n    66→---\n    67→\n    68→### store_domains\n    69→\n    70→Custom domains attached to a store.\n    71→\n    72→| Column | Type | Nullable | Default | Constraints | Notes |\n    73→|---|---|---|---|---|---|\n    74→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n    75→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n    76→| hostname | TEXT | No | - | UNIQUE per table | e.g. \"shop.example.com\" |\n    77→| type | TEXT | No | 'storefront' | CHECK IN ('storefront', 'admin', 'api') | Domain purpose |\n    78→| is_primary | INTEGER | No | 0 | - | Boolean: primary domain flag |\n    79→| tls_mode | TEXT | No | 'managed' | CHECK IN ('managed', 'bring_your_own') | TLS certificate mode |\n    80→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n    81→\n    82→**Indexes:**\n    83→\n    84→- `idx_store_domains_hostname` UNIQUE on (`hostname`) - Globally unique hostname for routing\n    85→- `idx_store_domains_store_id` on (`store_id`) - FK lookup, listing domains per store\n    86→- `idx_store_domains_store_primary` on (`store_id`, `is_primary`) - Quick lookup of a store's primary domain\n    87→\n    88→---\n    89→\n    90→### users\n    91→\n    92→Admin/staff users who manage stores (not storefront customers).\n    93→\n    94→| Column | Type | Nullable | Default | Constraints | Notes |\n    95→|---|---|---|---|---|---|\n    96→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n    97→| email | TEXT | No | - | UNIQUE per table | Login email |\n    98→| password_hash | TEXT | No | - | - | bcrypt/argon2 hash; maps to Laravel's `password` field |\n    99→| name | TEXT | No | - | - | Display name |\n   100→| status | TEXT | No | 'active' | CHECK IN ('active', 'disabled') | |\n   101→| email_verified_at | TEXT | Yes | NULL | - | ISO-8601 timestamp; required for `verified` middleware |\n   102→| last_login_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   103→| two_factor_secret | TEXT | Yes | NULL | - | TOTP secret |\n   104→| two_factor_recovery_codes | TEXT | Yes | NULL | - | JSON array of recovery codes |\n   105→| two_factor_confirmed_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   106→| remember_token | TEXT | Yes | NULL | - | Laravel remember-me token |\n   107→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   108→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   109→\n   110→**Indexes:**\n   111→\n   112→- `idx_users_email` UNIQUE on (`email`) - Enforces unique email, supports login lookup\n   113→- `idx_users_status` on (`status`) - Filtering users by active/disabled\n   114→\n   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.\n   116→\n   117→---\n   118→\n   119→### store_users\n   120→\n   121→Pivot table linking users to stores with a role. Uses composite primary key.\n   122→\n   123→| Column | Type | Nullable | Default | Constraints | Notes |\n   124→|---|---|---|---|---|---|\n   125→| store_id | INTEGER | No | - | PK (composite), FK -> stores(id) ON DELETE CASCADE | |\n   126→| user_id | INTEGER | No | - | PK (composite), FK -> users(id) ON DELETE CASCADE | |\n   127→| role | TEXT | No | 'staff' | CHECK IN ('owner', 'admin', 'staff', 'support') | |\n   128→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   129→\n   130→**Primary Key:** (`store_id`, `user_id`)\n   131→\n   132→**Indexes:**\n   133→\n   134→- `idx_store_users_user_id` on (`user_id`) - Reverse lookup: find all stores for a user\n   135→- `idx_store_users_role` on (`store_id`, `role`) - Listing users by role within a store\n   136→\n   137→---\n   138→\n   139→### store_settings\n   140→\n   141→Single-row-per-store JSON settings bag.\n   142→\n   143→| Column | Type | Nullable | Default | Constraints | Notes |\n   144→|---|---|---|---|---|---|\n   145→| store_id | INTEGER | No | - | PRIMARY KEY, FK -> stores(id) ON DELETE CASCADE | One-to-one with stores |\n   146→| settings_json | TEXT | No | '{}' | - | JSON settings object |\n   147→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   148→\n   149→**Notes:** No additional indexes needed - primary key on `store_id` covers all queries.\n   150→\n   151→---\n   152→\n   153→## Epic 2 - Catalog, Collections, Inventory, and Media\n   154→\n   155→### products\n   156→\n   157→Core product record, scoped to a store.\n   158→\n   159→| Column | Type | Nullable | Default | Constraints | Notes |\n   160→|---|---|---|---|---|---|\n   161→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   162→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n   163→| title | TEXT | No | - | - | Product display title |\n   164→| handle | TEXT | No | - | UNIQUE per (store_id, handle) | URL-friendly slug |\n   165→| status | TEXT | No | 'draft' | CHECK IN ('draft', 'active', 'archived') | |\n   166→| description_html | TEXT | Yes | NULL | - | Rich-text description |\n   167→| vendor | TEXT | Yes | NULL | - | Manufacturer/brand |\n   168→| product_type | TEXT | Yes | NULL | - | Category classification |\n   169→| tags | TEXT | No | '[]' | - | JSON array, e.g. [\"summer\",\"sale\"] |\n   170→| published_at | TEXT | Yes | NULL | - | ISO-8601 timestamp; null = unpublished |\n   171→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   172→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   173→\n   174→**Indexes:**\n   175→\n   176→- `idx_products_store_handle` UNIQUE on (`store_id`, `handle`) - Unique handle per store for URL routing\n   177→- `idx_products_store_id` on (`store_id`) - FK lookup, listing all products in a store\n   178→- `idx_products_store_status` on (`store_id`, `status`) - Filtering products by status within a store\n   179→- `idx_products_published_at` on (`store_id`, `published_at`) - Sorting/filtering by publication date\n   180→- `idx_products_vendor` on (`store_id`, `vendor`) - Filtering products by vendor\n   181→- `idx_products_product_type` on (`store_id`, `product_type`) - Filtering products by type\n   182→\n   183→**Notes:** `tags` is a JSON-encoded array (e.g. `[\"summer\",\"sale\"]`). The `handle` is unique per store, not globally.\n   184→\n   185→---\n   186→\n   187→### product_options\n   188→\n   189→Named option dimensions for a product (e.g. Size, Color).\n   190→\n   191→| Column | Type | Nullable | Default | Constraints | Notes |\n   192→|---|---|---|---|---|---|\n   193→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   194→| product_id | INTEGER | No | - | FK -> products(id) ON DELETE CASCADE | Parent product |\n   195→| name | TEXT | No | - | - | Option name, e.g. \"Size\", \"Color\" |\n   196→| position | INTEGER | No | 0 | UNIQUE per (product_id, position) | Display ordering |\n   197→\n   198→**Indexes:**\n   199→\n   200→- `idx_product_options_product_id` on (`product_id`) - FK lookup, listing options per product\n   201→- `idx_product_options_product_position` UNIQUE on (`product_id`, `position`) - Enforces unique ordering per product\n   202→\n   203→---\n   204→\n   205→### product_option_values\n   206→\n   207→Individual values within a product option (e.g. \"Small\", \"Medium\", \"Large\").\n   208→\n   209→| Column | Type | Nullable | Default | Constraints | Notes |\n   210→|---|---|---|---|---|---|\n   211→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   212→| product_option_id | INTEGER | No | - | FK -> product_options(id) ON DELETE CASCADE | Parent option |\n   213→| value | TEXT | No | - | - | Display value, e.g. \"Small\" |\n   214→| position | INTEGER | No | 0 | UNIQUE per (product_option_id, position) | Display ordering |\n   215→\n   216→**Indexes:**\n   217→\n   218→- `idx_product_option_values_option_id` on (`product_option_id`) - FK lookup, listing values per option\n   219→- `idx_product_option_values_option_position` UNIQUE on (`product_option_id`, `position`) - Enforces unique ordering per option\n   220→\n   221→---\n   222→\n   223→### product_variants\n   224→\n   225→Purchasable SKU-level variant of a product.\n   226→\n   227→| Column | Type | Nullable | Default | Constraints | Notes |\n   228→|---|---|---|---|---|---|\n   229→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   230→| product_id | INTEGER | No | - | FK -> products(id) ON DELETE CASCADE | Parent product |\n   231→| sku | TEXT | Yes | NULL | - | Stock keeping unit |\n   232→| barcode | TEXT | Yes | NULL | - | UPC/EAN barcode |\n   233→| price_amount | INTEGER | No | 0 | - | Price in minor units (cents) |\n   234→| compare_at_amount | INTEGER | Yes | NULL | - | Original/compare price in minor units |\n   235→| currency | TEXT | No | 'USD' | - | ISO 4217 currency code |\n   236→| weight_g | INTEGER | Yes | NULL | - | Weight in grams |\n   237→| requires_shipping | INTEGER | No | 1 | - | Boolean: physical product flag |\n   238→| is_default | INTEGER | No | 0 | - | Boolean: default variant flag |\n   239→| position | INTEGER | No | 0 | - | Display ordering |\n   240→| status | TEXT | No | 'active' | CHECK IN ('active', 'archived') | |\n   241→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   242→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   243→\n   244→**Indexes:**\n   245→\n   246→- `idx_product_variants_product_id` on (`product_id`) - FK lookup, listing variants per product\n   247→- `idx_product_variants_sku` on (`sku`) - SKU lookup across all variants\n   248→- `idx_product_variants_barcode` on (`barcode`) - Barcode scanning lookup\n   249→- `idx_product_variants_product_position` on (`product_id`, `position`) - Ordered variant listing\n   250→- `idx_product_variants_product_default` on (`product_id`, `is_default`) - Quick lookup of default variant\n   251→\n   252→**Notes:** `price_amount` and `compare_at_amount` are in minor currency units (cents). A product always has at least one variant (the default).\n   253→\n   254→---\n   255→\n   256→### variant_option_values\n   257→\n   258→Maps a variant to its chosen option values (e.g. variant #5 = Size:Large + Color:Red). Junction table with composite primary key.\n   259→\n   260→| Column | Type | Nullable | Default | Constraints | Notes |\n   261→|---|---|---|---|---|---|\n   262→| variant_id | INTEGER | No | - | PK (composite), FK -> product_variants(id) ON DELETE CASCADE | |\n   263→| product_option_value_id | INTEGER | No | - | PK (composite), FK -> product_option_values(id) ON DELETE CASCADE | |\n   264→\n   265→**Primary Key:** (`variant_id`, `product_option_value_id`)\n   266→\n   267→**Indexes:**\n   268→\n   269→- `idx_variant_option_values_value_id` on (`product_option_value_id`) - Reverse lookup: find all variants with a given option value\n   270→\n   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.\n   272→\n   273→---\n   274→\n   275→### inventory_items\n   276→\n   277→Stock tracking per variant per store.\n   278→\n   279→| Column | Type | Nullable | Default | Constraints | Notes |\n   280→|---|---|---|---|---|---|\n   281→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   282→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n   283→| variant_id | INTEGER | No | - | FK -> product_variants(id) ON DELETE CASCADE, UNIQUE | One inventory record per variant |\n   284→| quantity_on_hand | INTEGER | No | 0 | - | Total physical stock |\n   285→| quantity_reserved | INTEGER | No | 0 | - | Stock allocated to open orders |\n   286→| policy | TEXT | No | 'deny' | CHECK IN ('deny', 'continue') | Oversell policy |\n   287→\n   288→**Indexes:**\n   289→\n   290→- `idx_inventory_items_variant_id` UNIQUE on (`variant_id`) - Enforces one inventory record per variant, fast variant lookup\n   291→- `idx_inventory_items_store_id` on (`store_id`) - Listing all inventory for a store\n   292→\n   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.\n   294→\n   295→---\n   296→\n   297→### collections\n   298→\n   299→Curated or rule-based product groupings.\n   300→\n   301→| Column | Type | Nullable | Default | Constraints | Notes |\n   302→|---|---|---|---|---|---|\n   303→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   304→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n   305→| title | TEXT | No | - | - | Collection display title |\n   306→| handle | TEXT | No | - | UNIQUE per (store_id, handle) | URL-friendly slug |\n   307→| description_html | TEXT | Yes | NULL | - | Rich-text description |\n   308→| type | TEXT | No | 'manual' | CHECK IN ('manual', 'automated') | Collection type: manual curation or rule-based automation |\n   309→| status | TEXT | No | 'active' | CHECK IN ('draft', 'active', 'archived') | |\n   310→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   311→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   312→\n   313→**Indexes:**\n   314→\n   315→- `idx_collections_store_handle` UNIQUE on (`store_id`, `handle`) - Unique handle per store for URL routing\n   316→- `idx_collections_store_id` on (`store_id`) - FK lookup, listing all collections in a store\n   317→- `idx_collections_store_status` on (`store_id`, `status`) - Filtering collections by status\n   318→\n   319→---\n   320→\n   321→### collection_products\n   322→\n   323→Pivot linking products to collections with ordering. Composite primary key.\n   324→\n   325→| Column | Type | Nullable | Default | Constraints | Notes |\n   326→|---|---|---|---|---|---|\n   327→| collection_id | INTEGER | No | - | PK (composite), FK -> collections(id) ON DELETE CASCADE | |\n   328→| product_id | INTEGER | No | - | PK (composite), FK -> products(id) ON DELETE CASCADE | |\n   329→| position | INTEGER | No | 0 | - | Display ordering within collection |\n   330→\n   331→**Primary Key:** (`collection_id`, `product_id`)\n   332→\n   333→**Indexes:**\n   334→\n   335→- `idx_collection_products_product_id` on (`product_id`) - Reverse lookup: find all collections containing a product\n   336→- `idx_collection_products_position` on (`collection_id`, `position`) - Ordered product listing within a collection\n   337→\n   338→---\n   339→\n   340→### product_media\n   341→\n   342→Images and videos attached to a product.\n   343→\n   344→| Column | Type | Nullable | Default | Constraints | Notes |\n   345→|---|---|---|---|---|---|\n   346→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   347→| product_id | INTEGER | No | - | FK -> products(id) ON DELETE CASCADE | Parent product |\n   348→| type | TEXT | No | 'image' | CHECK IN ('image', 'video') | Media type |\n   349→| storage_key | TEXT | No | - | - | File storage path/key |\n   350→| alt_text | TEXT | Yes | NULL | - | Accessibility alt text |\n   351→| width | INTEGER | Yes | NULL | - | Pixel width |\n   352→| height | INTEGER | Yes | NULL | - | Pixel height |\n   353→| mime_type | TEXT | Yes | NULL | - | e.g. \"image/jpeg\" |\n   354→| byte_size | INTEGER | Yes | NULL | - | File size in bytes |\n   355→| position | INTEGER | No | 0 | - | Display ordering |\n   356→| status | TEXT | No | 'processing' | CHECK IN ('processing', 'ready', 'failed') | Processing status |\n   357→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   358→\n   359→**Indexes:**\n   360→\n   361→- `idx_product_media_product_id` on (`product_id`) - FK lookup, listing media per product\n   362→- `idx_product_media_product_position` on (`product_id`, `position`) - Ordered media listing\n   363→- `idx_product_media_status` on (`status`) - Finding media by processing status\n   364→\n   365→---\n   366→\n   367→## Epic 3 - Themes, Storefront, and Localization\n   368→\n   369→### themes\n   370→\n   371→Storefront theme definitions per store.\n   372→\n   373→| Column | Type | Nullable | Default | Constraints | Notes |\n   374→|---|---|---|---|---|---|\n   375→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   376→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n   377→| name | TEXT | No | - | - | Theme display name |\n   378→| version | TEXT | Yes | NULL | - | Semantic version string |\n   379→| status | TEXT | No | 'draft' | CHECK IN ('draft', 'published') | |\n   380→| published_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   381→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   382→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   383→\n   384→**Indexes:**\n   385→\n   386→- `idx_themes_store_id` on (`store_id`) - FK lookup, listing themes per store\n   387→- `idx_themes_store_status` on (`store_id`, `status`) - Finding published/draft themes per store\n   388→\n   389→---\n   390→\n   391→### theme_files\n   392→\n   393→Individual files belonging to a theme (templates, assets, snippets).\n   394→\n   395→| Column | Type | Nullable | Default | Constraints | Notes |\n   396→|---|---|---|---|---|---|\n   397→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   398→| theme_id | INTEGER | No | - | FK -> themes(id) ON DELETE CASCADE | Parent theme |\n   399→| path | TEXT | No | - | UNIQUE per (theme_id, path) | File path within theme |\n   400→| storage_key | TEXT | No | - | - | File storage path/key |\n   401→| sha256 | TEXT | No | - | - | Content hash for cache busting |\n   402→| byte_size | INTEGER | No | 0 | - | File size in bytes |\n   403→\n   404→**Indexes:**\n   405→\n   406→- `idx_theme_files_theme_path` UNIQUE on (`theme_id`, `path`) - Enforces unique file paths per theme\n   407→- `idx_theme_files_theme_id` on (`theme_id`) - FK lookup, listing files per theme\n   408→\n   409→---\n   410→\n   411→### theme_settings\n   412→\n   413→Single-row JSON configuration per theme.\n   414→\n   415→| Column | Type | Nullable | Default | Constraints | Notes |\n   416→|---|---|---|---|---|---|\n   417→| theme_id | INTEGER | No | - | PRIMARY KEY, FK -> themes(id) ON DELETE CASCADE | One-to-one with themes |\n   418→| settings_json | TEXT | No | '{}' | - | JSON settings object |\n   419→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   420→\n   421→---\n   422→\n   423→### pages\n   424→\n   425→Static CMS pages (About Us, Contact, etc.).\n   426→\n   427→| Column | Type | Nullable | Default | Constraints | Notes |\n   428→|---|---|---|---|---|---|\n   429→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   430→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n   431→| title | TEXT | No | - | - | Page title |\n   432→| handle | TEXT | No | - | UNIQUE per (store_id, handle) | URL-friendly slug |\n   433→| body_html | TEXT | Yes | NULL | - | Rich-text content |\n   434→| status | TEXT | No | 'draft' | CHECK IN ('draft', 'published', 'archived') | |\n   435→| published_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   436→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   437→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   438→\n   439→**Indexes:**\n   440→\n   441→- `idx_pages_store_handle` UNIQUE on (`store_id`, `handle`) - Unique handle per store for URL routing\n   442→- `idx_pages_store_id` on (`store_id`) - FK lookup, listing pages per store\n   443→- `idx_pages_store_status` on (`store_id`, `status`) - Filtering pages by status\n   444→\n   445→---\n   446→\n   447→### navigation_menus\n   448→\n   449→Named menus (e.g. main-menu, footer-menu).\n   450→\n   451→| Column | Type | Nullable | Default | Constraints | Notes |\n   452→|---|---|---|---|---|---|\n   453→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   454→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n   455→| handle | TEXT | No | - | UNIQUE per (store_id, handle) | e.g. \"main-menu\" |\n   456→| title | TEXT | No | - | - | Display title |\n   457→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   458→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   459→\n   460→**Indexes:**\n   461→\n   462→- `idx_navigation_menus_store_handle` UNIQUE on (`store_id`, `handle`) - Unique handle per store\n   463→- `idx_navigation_menus_store_id` on (`store_id`) - FK lookup, listing menus per store\n   464→\n   465→---\n   466→\n   467→### navigation_items\n   468→\n   469→Individual links within a navigation menu (supports nesting via position).\n   470→\n   471→| Column | Type | Nullable | Default | Constraints | Notes |\n   472→|---|---|---|---|---|---|\n   473→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   474→| menu_id | INTEGER | No | - | FK -> navigation_menus(id) ON DELETE CASCADE | Parent menu |\n   475→| type | TEXT | No | 'link' | CHECK IN ('link', 'page', 'collection', 'product') | Item type |\n   476→| label | TEXT | No | - | - | Display label |\n   477→| url | TEXT | Yes | NULL | - | Direct URL (used when type = 'link') |\n   478→| resource_id | INTEGER | Yes | NULL | - | FK to pages/collections/products (polymorphic) |\n   479→| position | INTEGER | No | 0 | - | Display ordering |\n   480→\n   481→**Indexes:**\n   482→\n   483→- `idx_navigation_items_menu_id` on (`menu_id`) - FK lookup, listing items per menu\n   484→- `idx_navigation_items_menu_position` on (`menu_id`, `position`) - Ordered item listing\n   485→\n   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.\n   487→\n   488→---\n   489→\n   490→## Epic 4 - Search\n   491→\n   492→### search_settings\n   493→\n   494→Per-store search configuration (synonyms, stop words).\n   495→\n   496→| Column | Type | Nullable | Default | Constraints | Notes |\n   497→|---|---|---|---|---|---|\n   498→| store_id | INTEGER | No | - | PRIMARY KEY, FK -> stores(id) ON DELETE CASCADE | One-to-one with stores |\n   499→| synonyms_json | TEXT | No | '[]' | - | JSON array of synonym groups |\n   500→| stop_words_json | TEXT | No | '[]' | - | JSON array of stop words |\n   501→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   502→\n   503→---\n   504→\n   505→### search_queries\n   506→\n   507→Search query log for analytics and autocomplete.\n   508→\n   509→| Column | Type | Nullable | Default | Constraints | Notes |\n   510→|---|---|---|---|---|---|\n   511→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   512→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n   513→| query | TEXT | No | - | - | Search query text |\n   514→| filters_json | TEXT | Yes | NULL | - | JSON object of applied filters |\n   515→| results_count | INTEGER | No | 0 | - | Number of results returned |\n   516→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   517→\n   518→**Indexes:**\n   519→\n   520→- `idx_search_queries_store_id` on (`store_id`) - FK lookup\n   521→- `idx_search_queries_store_created` on (`store_id`, `created_at`) - Time-range queries per store\n   522→- `idx_search_queries_store_query` on (`store_id`, `query`) - Autocomplete and popular search lookups\n   523→\n   524→---\n   525→\n   526→## Epic 5 - Cart, Checkout, Discounts, Shipping, and Taxes\n   527→\n   528→### carts\n   529→\n   530→Shopping cart, optionally linked to a customer.\n   531→\n   532→| Column | Type | Nullable | Default | Constraints | Notes |\n   533→|---|---|---|---|---|---|\n   534→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   535→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n   536→| customer_id | INTEGER | Yes | NULL | FK -> customers(id) ON DELETE SET NULL | Nullable for guest carts |\n   537→| currency | TEXT | No | 'USD' | - | ISO 4217 currency code |\n   538→| cart_version | INTEGER | No | 1 | - | Optimistic concurrency version counter |\n   539→| status | TEXT | No | 'active' | CHECK IN ('active', 'converted', 'abandoned') | |\n   540→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   541→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   542→\n   543→**Indexes:**\n   544→\n   545→- `idx_carts_store_id` on (`store_id`) - FK lookup\n   546→- `idx_carts_customer_id` on (`customer_id`) - Finding carts by customer\n   547→- `idx_carts_store_status` on (`store_id`, `status`) - Filtering carts by status (e.g. abandoned cart recovery)\n   548→\n   549→**Notes:** `cart_version` is incremented on every mutation and used for optimistic concurrency control.\n   550→\n   551→---\n   552→\n   553→### cart_lines\n   554→\n   555→Individual line items within a cart.\n   556→\n   557→| Column | Type | Nullable | Default | Constraints | Notes |\n   558→|---|---|---|---|---|---|\n   559→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   560→| cart_id | INTEGER | No | - | FK -> carts(id) ON DELETE CASCADE | Parent cart |\n   561→| variant_id | INTEGER | No | - | FK -> product_variants(id) ON DELETE CASCADE | Variant being purchased |\n   562→| quantity | INTEGER | No | 1 | - | Number of units |\n   563→| unit_price_amount | INTEGER | No | 0 | - | Price per unit in minor units |\n   564→| line_subtotal_amount | INTEGER | No | 0 | - | unit_price * quantity |\n   565→| line_discount_amount | INTEGER | No | 0 | - | Discount applied to this line |\n   566→| line_total_amount | INTEGER | No | 0 | - | subtotal - discount |\n   567→\n   568→**Indexes:**\n   569→\n   570→- `idx_cart_lines_cart_id` on (`cart_id`) - FK lookup, listing lines per cart\n   571→- `idx_cart_lines_cart_variant` UNIQUE on (`cart_id`, `variant_id`) - Prevents duplicate variants in the same cart\n   572→\n   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`.\n   574→\n   575→---\n   576→\n   577→### checkouts\n   578→\n   579→Checkout session progressing through address, shipping, payment steps.\n   580→\n   581→| Column | Type | Nullable | Default | Constraints | Notes |\n   582→|---|---|---|---|---|---|\n   583→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   584→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n   585→| cart_id | INTEGER | No | - | FK -> carts(id) ON DELETE CASCADE | Source cart |\n   586→| customer_id | INTEGER | Yes | NULL | FK -> customers(id) ON DELETE SET NULL | Nullable for guest checkout |\n   587→| status | TEXT | No | 'started' | CHECK IN ('started', 'addressed', 'shipping_selected', 'payment_selected', 'completed', 'expired') | |\n   588→| payment_method | TEXT | Yes | NULL | CHECK IN ('credit_card', 'paypal', 'bank_transfer') | Selected payment method |\n   589→| email | TEXT | Yes | NULL | - | Contact email |\n   590→| shipping_address_json | TEXT | Yes | NULL | - | JSON address object |\n   591→| billing_address_json | TEXT | Yes | NULL | - | JSON address object |\n   592→| shipping_method_id | INTEGER | Yes | NULL | - | Selected shipping rate |\n   593→| discount_code | TEXT | Yes | NULL | - | Applied discount code |\n   594→| tax_provider_snapshot_json | TEXT | Yes | NULL | - | Full tax calculation response for audit |\n   595→| totals_json | TEXT | Yes | NULL | - | Snapshot: subtotal, discount, shipping, tax, total |\n   596→| expires_at | TEXT | Yes | NULL | - | ISO-8601 expiration timestamp |\n   597→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   598→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   599→\n   600→**Indexes:**\n   601→\n   602→- `idx_checkouts_store_id` on (`store_id`) - FK lookup\n   603→- `idx_checkouts_cart_id` on (`cart_id`) - Finding checkout for a cart\n   604→- `idx_checkouts_customer_id` on (`customer_id`) - Listing checkouts per customer\n   605→- `idx_checkouts_status` on (`store_id`, `status`) - Filtering checkouts by status\n   606→- `idx_checkouts_expires_at` on (`expires_at`) - Cleanup of expired checkouts\n   607→\n   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.\n   609→\n   610→---\n   611→\n   612→### shipping_zones\n   613→\n   614→Geographic shipping zones for a store.\n   615→\n   616→| Column | Type | Nullable | Default | Constraints | Notes |\n   617→|---|---|---|---|---|---|\n   618→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   619→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n   620→| name | TEXT | No | - | - | Zone display name |\n   621→| countries_json | TEXT | No | '[]' | - | JSON array of ISO 3166-1 alpha-2 codes, e.g. [\"US\",\"CA\"] |\n   622→| regions_json | TEXT | No | '[]' | - | JSON array of region/state codes, e.g. [\"US-NY\",\"US-CA\"] |\n   623→\n   624→**Indexes:**\n   625→\n   626→- `idx_shipping_zones_store_id` on (`store_id`) - FK lookup, listing zones per store\n   627→\n   628→**Notes:** `countries_json` is an array of ISO 3166-1 alpha-2 codes. `regions_json` is an array of region/state codes.\n   629→\n   630→---\n   631→\n   632→### shipping_rates\n   633→\n   634→Shipping rate options within a zone.\n   635→\n   636→| Column | Type | Nullable | Default | Constraints | Notes |\n   637→|---|---|---|---|---|---|\n   638→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   639→| zone_id | INTEGER | No | - | FK -> shipping_zones(id) ON DELETE CASCADE | Parent zone |\n   640→| name | TEXT | No | - | - | Rate display name |\n   641→| type | TEXT | No | 'flat' | CHECK IN ('flat', 'weight', 'price', 'carrier') | Calculation method |\n   642→| config_json | TEXT | No | '{}' | - | Type-dependent configuration (see notes) |\n   643→| is_active | INTEGER | No | 1 | - | Boolean: active flag |\n   644→\n   645→**Indexes:**\n   646→\n   647→- `idx_shipping_rates_zone_id` on (`zone_id`) - FK lookup, listing rates per zone\n   648→- `idx_shipping_rates_zone_active` on (`zone_id`, `is_active`) - Finding active rates per zone\n   649→\n   650→**Notes:** `config_json` structure depends on `type`:\n   651→- Flat: `{\"amount\": 799}`\n   652→- Weight: `{\"ranges\": [{\"min_g\": 0, \"max_g\": 500, \"amount\": 499}]}`\n   653→- Price: `{\"ranges\": [{\"min_amount\": 0, \"max_amount\": 5000, \"amount\": 799}]}`\n   654→- Carrier: `{\"carrier\": \"ups\", \"service\": \"ground\"}`\n   655→\n   656→---\n   657→\n   658→### tax_settings\n   659→\n   660→Per-store tax configuration.\n   661→\n   662→| Column | Type | Nullable | Default | Constraints | Notes |\n   663→|---|---|---|---|---|---|\n   664→| store_id | INTEGER | No | - | PRIMARY KEY, FK -> stores(id) ON DELETE CASCADE | One-to-one with stores |\n   665→| mode | TEXT | No | 'manual' | CHECK IN ('manual', 'provider') | Tax calculation mode |\n   666→| provider | TEXT | No | 'none' | CHECK IN ('stripe_tax', 'none') | External tax provider |\n   667→| prices_include_tax | INTEGER | No | 0 | - | Boolean: tax-inclusive pricing |\n   668→| config_json | TEXT | No | '{}' | - | Provider-specific config |\n   669→\n   670→---\n   671→\n   672→### discounts\n   673→\n   674→Discount codes and automatic discounts.\n   675→\n   676→| Column | Type | Nullable | Default | Constraints | Notes |\n   677→|---|---|---|---|---|---|\n   678→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   679→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n   680→| type | TEXT | No | 'code' | CHECK IN ('code', 'automatic') | Activation method |\n   681→| code | TEXT | Yes | NULL | UNIQUE per (store_id, code) | Discount code (null for automatic) |\n   682→| value_type | TEXT | No | - | CHECK IN ('fixed', 'percent', 'free_shipping') | Discount calculation type |\n   683→| value_amount | INTEGER | No | 0 | - | See notes for interpretation |\n   684→| starts_at | TEXT | No | - | - | ISO-8601 start timestamp |\n   685→| ends_at | TEXT | Yes | NULL | - | ISO-8601 end timestamp; null = no expiry |\n   686→| usage_limit | INTEGER | Yes | NULL | - | Max redemptions; null = unlimited |\n   687→| usage_count | INTEGER | No | 0 | - | Current redemption count |\n   688→| rules_json | TEXT | No | '{}' | - | Eligibility rules (min purchase, products, etc.) |\n   689→| status | TEXT | No | 'active' | CHECK IN ('draft', 'active', 'expired', 'disabled') | |\n   690→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   691→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   692→\n   693→**Indexes:**\n   694→\n   695→- `idx_discounts_store_code` UNIQUE on (`store_id`, `code`) - Unique discount code per store\n   696→- `idx_discounts_store_id` on (`store_id`) - FK lookup\n   697→- `idx_discounts_store_status` on (`store_id`, `status`) - Filtering discounts by status\n   698→- `idx_discounts_store_type` on (`store_id`, `type`) - Filtering by code vs automatic\n   699→\n   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.\n   701→\n   702→---\n   703→\n   704→## Epic 6 - Payments, Orders, Fulfillment, and Customers\n   705→\n   706→### customers\n   707→\n   708→Storefront customer accounts, scoped to a store.\n   709→\n   710→| Column | Type | Nullable | Default | Constraints | Notes |\n   711→|---|---|---|---|---|---|\n   712→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   713→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n   714→| email | TEXT | No | - | UNIQUE per (store_id, email) | Customer email |\n   715→| password_hash | TEXT | Yes | NULL | - | Nullable for guest checkout customers |\n   716→| name | TEXT | Yes | NULL | - | Display name |\n   717→| marketing_opt_in | INTEGER | No | 0 | - | Boolean: marketing consent |\n   718→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   719→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   720→\n   721→**Indexes:**\n   722→\n   723→- `idx_customers_store_email` UNIQUE on (`store_id`, `email`) - Unique email per store\n   724→- `idx_customers_store_id` on (`store_id`) - FK lookup, listing customers per store\n   725→\n   726→**Notes:** `password_hash` is nullable to allow guest checkout customers who may later claim their account. Email is unique per store, not globally.\n   727→\n   728→---\n   729→\n   730→### customer_addresses\n   731→\n   732→Saved addresses for a customer.\n   733→\n   734→| Column | Type | Nullable | Default | Constraints | Notes |\n   735→|---|---|---|---|---|---|\n   736→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   737→| customer_id | INTEGER | No | - | FK -> customers(id) ON DELETE CASCADE | Owning customer |\n   738→| label | TEXT | Yes | NULL | - | User label, e.g. \"Home\", \"Work\" |\n   739→| address_json | TEXT | No | '{}' | - | JSON address object (see notes) |\n   740→| is_default | INTEGER | No | 0 | - | Boolean: default address flag |\n   741→\n   742→**Indexes:**\n   743→\n   744→- `idx_customer_addresses_customer_id` on (`customer_id`) - FK lookup, listing addresses per customer\n   745→- `idx_customer_addresses_default` on (`customer_id`, `is_default`) - Quick lookup of default address\n   746→\n   747→**Notes:** `address_json` structure: `{\"first_name\": \"\", \"last_name\": \"\", \"company\": \"\", \"address1\": \"\", \"address2\": \"\", \"city\": \"\", \"province\": \"\", \"province_code\": \"\", \"country\": \"\", \"country_code\": \"\", \"zip\": \"\", \"phone\": \"\"}`.\n   748→\n   749→---\n   750→\n   751→### orders\n   752→\n   753→Placed orders with financial and fulfillment tracking.\n   754→\n   755→| Column | Type | Nullable | Default | Constraints | Notes |\n   756→|---|---|---|---|---|---|\n   757→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   758→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n   759→| customer_id | INTEGER | Yes | NULL | FK -> customers(id) ON DELETE SET NULL | Nullable for guest orders |\n   760→| order_number | TEXT | No | - | UNIQUE per (store_id, order_number) | Human-readable order number |\n   761→| payment_method | TEXT | No | - | CHECK IN ('credit_card', 'paypal', 'bank_transfer') | Payment method used for this order |\n   762→| status | TEXT | No | 'pending' | CHECK IN ('pending', 'paid', 'fulfilled', 'cancelled', 'refunded') | Overall order status |\n   763→| financial_status | TEXT | No | 'pending' | CHECK IN ('pending', 'authorized', 'paid', 'partially_refunded', 'refunded', 'voided') | Payment status |\n   764→| fulfillment_status | TEXT | No | 'unfulfilled' | CHECK IN ('unfulfilled', 'partial', 'fulfilled') | Shipping status |\n   765→| currency | TEXT | No | 'USD' | - | ISO 4217 currency code |\n   766→| subtotal_amount | INTEGER | No | 0 | - | Sum of line items in minor units |\n   767→| discount_amount | INTEGER | No | 0 | - | Total discount in minor units |\n   768→| shipping_amount | INTEGER | No | 0 | - | Shipping cost in minor units |\n   769→| tax_amount | INTEGER | No | 0 | - | Total tax in minor units |\n   770→| total_amount | INTEGER | No | 0 | - | Grand total in minor units |\n   771→| email | TEXT | Yes | NULL | - | Contact email at time of order |\n   772→| billing_address_json | TEXT | Yes | NULL | - | JSON address snapshot |\n   773→| shipping_address_json | TEXT | Yes | NULL | - | JSON address snapshot |\n   774→| placed_at | TEXT | Yes | NULL | - | ISO-8601 timestamp of placement |\n   775→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   776→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   777→\n   778→**Indexes:**\n   779→\n   780→- `idx_orders_store_order_number` UNIQUE on (`store_id`, `order_number`) - Unique order number per store\n   781→- `idx_orders_store_id` on (`store_id`) - FK lookup\n   782→- `idx_orders_customer_id` on (`customer_id`) - Listing orders per customer\n   783→- `idx_orders_store_status` on (`store_id`, `status`) - Filtering by overall status\n   784→- `idx_orders_store_financial` on (`store_id`, `financial_status`) - Filtering by payment status\n   785→- `idx_orders_store_fulfillment` on (`store_id`, `fulfillment_status`) - Filtering by shipping status\n   786→- `idx_orders_placed_at` on (`store_id`, `placed_at`) - Date-range order queries\n   787→\n   788→---\n   789→\n   790→### order_lines\n   791→\n   792→Individual items within an order. Contains snapshots of product data at time of purchase.\n   793→\n   794→| Column | Type | Nullable | Default | Constraints | Notes |\n   795→|---|---|---|---|---|---|\n   796→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   797→| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |\n   798→| product_id | INTEGER | Yes | NULL | FK -> products(id) ON DELETE SET NULL | Nullable: survives product deletion |\n   799→| variant_id | INTEGER | Yes | NULL | FK -> product_variants(id) ON DELETE SET NULL | Nullable: survives variant deletion |\n   800→| title_snapshot | TEXT | No | - | - | Product title at time of purchase |\n   801→| sku_snapshot | TEXT | Yes | NULL | - | SKU at time of purchase |\n   802→| quantity | INTEGER | No | 1 | - | Number of units |\n   803→| unit_price_amount | INTEGER | No | 0 | - | Price per unit in minor units |\n   804→| total_amount | INTEGER | No | 0 | - | Line total in minor units |\n   805→| tax_lines_json | TEXT | No | '[]' | - | JSON array of tax line items |\n   806→| discount_allocations_json | TEXT | No | '[]' | - | JSON array of discount allocations |\n   807→\n   808→**Indexes:**\n   809→\n   810→- `idx_order_lines_order_id` on (`order_id`) - FK lookup, listing lines per order\n   811→- `idx_order_lines_product_id` on (`product_id`) - Finding order lines by product\n   812→- `idx_order_lines_variant_id` on (`variant_id`) - Finding order lines by variant\n   813→\n   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}]`.\n   815→\n   816→---\n   817→\n   818→### payments\n   819→\n   820→Payment attempts and captures for an order.\n   821→\n   822→| Column | Type | Nullable | Default | Constraints | Notes |\n   823→|---|---|---|---|---|---|\n   824→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   825→| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |\n   826→| provider | TEXT | No | 'mock' | CHECK IN ('mock') | Payment provider (mock PSP only) |\n   827→| method | TEXT | No | - | CHECK IN ('credit_card', 'paypal', 'bank_transfer') | Payment method used |\n   828→| provider_payment_id | TEXT | Yes | NULL | - | Mock payment reference ID |\n   829→| status | TEXT | No | 'pending' | CHECK IN ('pending', 'captured', 'failed', 'refunded') | |\n   830→| amount | INTEGER | No | 0 | - | Payment amount in minor units |\n   831→| currency | TEXT | No | 'USD' | - | ISO 4217 currency code |\n   832→| raw_json_encrypted | TEXT | Yes | NULL | - | Encrypted provider response (PCI compliance) |\n   833→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   834→\n   835→**Indexes:**\n   836→\n   837→- `idx_payments_order_id` on (`order_id`) - FK lookup, listing payments per order\n   838→- `idx_payments_provider_id` on (`provider`, `provider_payment_id`) - Payment reconciliation by provider ID\n   839→- `idx_payments_method` on (`method`) - Filtering payments by method\n   840→- `idx_payments_status` on (`status`) - Filtering payments by status\n   841→\n   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).\n   843→\n   844→---\n   845→\n   846→### refunds\n   847→\n   848→Refund records linked to an order and payment.\n   849→\n   850→| Column | Type | Nullable | Default | Constraints | Notes |\n   851→|---|---|---|---|---|---|\n   852→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   853→| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |\n   854→| payment_id | INTEGER | No | - | FK -> payments(id) ON DELETE CASCADE | Source payment |\n   855→| amount | INTEGER | No | 0 | - | Refund amount in minor units |\n   856→| reason | TEXT | Yes | NULL | - | Refund reason |\n   857→| status | TEXT | No | 'pending' | CHECK IN ('pending', 'processed', 'failed') | |\n   858→| provider_refund_id | TEXT | Yes | NULL | - | External refund ID |\n   859→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   860→\n   861→**Indexes:**\n   862→\n   863→- `idx_refunds_order_id` on (`order_id`) - FK lookup, listing refunds per order\n   864→- `idx_refunds_payment_id` on (`payment_id`) - Finding refunds per payment\n   865→- `idx_refunds_status` on (`status`) - Filtering refunds by status\n   866→\n   867→---\n   868→\n   869→### fulfillments\n   870→\n   871→Shipment tracking for order fulfillment.\n   872→\n   873→| Column | Type | Nullable | Default | Constraints | Notes |\n   874→|---|---|---|---|---|---|\n   875→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   876→| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |\n   877→| status | TEXT | No | 'pending' | CHECK IN ('pending', 'shipped', 'delivered') | |\n   878→| tracking_company | TEXT | Yes | NULL | - | Carrier name |\n   879→| tracking_number | TEXT | Yes | NULL | - | Tracking number |\n   880→| tracking_url | TEXT | Yes | NULL | - | Tracking page URL |\n   881→| shipped_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   882→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   883→\n   884→**Indexes:**\n   885→\n   886→- `idx_fulfillments_order_id` on (`order_id`) - FK lookup, listing fulfillments per order\n   887→- `idx_fulfillments_status` on (`status`) - Filtering fulfillments by status\n   888→- `idx_fulfillments_tracking` on (`tracking_company`, `tracking_number`) - Tracking number lookup\n   889→\n   890→---\n   891→\n   892→### fulfillment_lines\n   893→\n   894→Links specific order lines and quantities to a fulfillment.\n   895→\n   896→| Column | Type | Nullable | Default | Constraints | Notes |\n   897→|---|---|---|---|---|---|\n   898→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   899→| fulfillment_id | INTEGER | No | - | FK -> fulfillments(id) ON DELETE CASCADE | Parent fulfillment |\n   900→| order_line_id | INTEGER | No | - | FK -> order_lines(id) ON DELETE CASCADE | Source order line |\n   901→| quantity | INTEGER | No | 1 | - | Units fulfilled from this line |\n   902→\n   903→**Indexes:**\n   904→\n   905→- `idx_fulfillment_lines_fulfillment_id` on (`fulfillment_id`) - FK lookup, listing lines per fulfillment\n   906→- `idx_fulfillment_lines_fulfillment_order_line` UNIQUE on (`fulfillment_id`, `order_line_id`) - Prevents duplicate line entries per fulfillment\n   907→\n   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.\n   909→\n   910→---\n   911→\n   912→## Epic 7 - Analytics\n   913→\n   914→### analytics_events\n   915→\n   916→Raw event stream for storefront analytics.\n   917→\n   918→| Column | Type | Nullable | Default | Constraints | Notes |\n   919→|---|---|---|---|---|---|\n   920→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   921→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n   922→| type | TEXT | No | - | - | Event type (see notes) |\n   923→| session_id | TEXT | Yes | NULL | - | Browser session identifier |\n   924→| customer_id | INTEGER | Yes | NULL | FK -> customers(id) ON DELETE SET NULL | Nullable for anonymous visitors |\n   925→| properties_json | TEXT | No | '{}' | - | Event-specific data as JSON |\n   926→| client_event_id | TEXT | Yes | NULL | UNIQUE per (store_id, client_event_id) | Client-provided idempotency key for deduplication |\n   927→| occurred_at | TEXT | Yes | NULL | - | ISO-8601 timestamp of when the event actually occurred on the client |\n   928→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   929→\n   930→**Indexes:**\n   931→\n   932→- `idx_analytics_events_store_id` on (`store_id`) - FK lookup\n   933→- `idx_analytics_events_store_type` on (`store_id`, `type`) - Filtering events by type per store\n   934→- `idx_analytics_events_store_created` on (`store_id`, `created_at`) - Time-range event queries\n   935→- `idx_analytics_events_session` on (`session_id`) - Session-based event grouping\n   936→- `idx_analytics_events_customer` on (`customer_id`) - Customer activity history\n   937→- `idx_analytics_events_client_event` UNIQUE on (`store_id`, `client_event_id`) - Client-side event deduplication\n   938→\n   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.\n   940→\n   941→---\n   942→\n   943→### analytics_daily\n   944→\n   945→Pre-aggregated daily metrics per store. Composite primary key.\n   946→\n   947→| Column | Type | Nullable | Default | Constraints | Notes |\n   948→|---|---|---|---|---|---|\n   949→| store_id | INTEGER | No | - | PK (composite), FK -> stores(id) ON DELETE CASCADE | |\n   950→| date | TEXT | No | - | PK (composite) | ISO-8601 date (YYYY-MM-DD) |\n   951→| orders_count | INTEGER | No | 0 | - | Number of orders placed |\n   952→| revenue_amount | INTEGER | No | 0 | - | Total revenue in minor units |\n   953→| aov_amount | INTEGER | No | 0 | - | Average order value in minor units |\n   954→| visits_count | INTEGER | No | 0 | - | Unique sessions |\n   955→| add_to_cart_count | INTEGER | No | 0 | - | Add-to-cart events |\n   956→| checkout_started_count | INTEGER | No | 0 | - | Checkout-started events |\n   957→| checkout_completed_count | INTEGER | No | 0 | - | Checkout-completed events |\n   958→\n   959→**Primary Key:** (`store_id`, `date`)\n   960→\n   961→**Indexes:**\n   962→\n   963→- `idx_analytics_daily_store_date` on (`store_id`, `date`) - Time-range metric queries (covered by PK but explicit for clarity)\n   964→\n   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.\n   966→\n   967→---\n   968→\n   969→## Epic 8 - Apps and Webhooks\n   970→\n   971→### apps\n   972→\n   973→Third-party or first-party application registry.\n   974→\n   975→| Column | Type | Nullable | Default | Constraints | Notes |\n   976→|---|---|---|---|---|---|\n   977→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   978→| name | TEXT | No | - | - | App display name |\n   979→| status | TEXT | No | 'active' | CHECK IN ('active', 'disabled') | |\n   980→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n   981→\n   982→**Indexes:**\n   983→\n   984→- `idx_apps_status` on (`status`) - Filtering apps by status\n   985→\n   986→---\n   987→\n   988→### app_installations\n   989→\n   990→Installation of an app on a specific store.\n   991→\n   992→| Column | Type | Nullable | Default | Constraints | Notes |\n   993→|---|---|---|---|---|---|\n   994→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n   995→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | |\n   996→| app_id | INTEGER | No | - | FK -> apps(id) ON DELETE CASCADE | |\n   997→| scopes_json | TEXT | No | '[]' | - | JSON array of granted permission scopes |\n   998→| status | TEXT | No | 'active' | CHECK IN ('active', 'suspended', 'uninstalled') | |\n   999→| installed_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n  1000→\n  1001→**Indexes:**\n  1002→\n  1003→- `idx_app_installations_store_app` UNIQUE on (`store_id`, `app_id`) - One installation per app per store\n  1004→- `idx_app_installations_store_id` on (`store_id`) - Listing installations per store\n  1005→- `idx_app_installations_app_id` on (`app_id`) - Finding all stores with an app installed\n  1006→\n  1007→---\n  1008→\n  1009→### oauth_clients\n  1010→\n  1011→OAuth2 client credentials for apps.\n  1012→\n  1013→| Column | Type | Nullable | Default | Constraints | Notes |\n  1014→|---|---|---|---|---|---|\n  1015→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n  1016→| app_id | INTEGER | No | - | FK -> apps(id) ON DELETE CASCADE | Parent app |\n  1017→| client_id | TEXT | No | - | UNIQUE per table | OAuth2 client identifier |\n  1018→| client_secret_encrypted | TEXT | No | - | - | Encrypted client secret |\n  1019→| redirect_uris_json | TEXT | No | '[]' | - | JSON array of allowed redirect URIs |\n  1020→\n  1021→**Indexes:**\n  1022→\n  1023→- `idx_oauth_clients_client_id` UNIQUE on (`client_id`) - OAuth2 client lookup\n  1024→- `idx_oauth_clients_app_id` on (`app_id`) - FK lookup, listing clients per app\n  1025→\n  1026→---\n  1027→\n  1028→### oauth_tokens\n  1029→\n  1030→Issued access/refresh tokens for app installations.\n  1031→\n  1032→| Column | Type | Nullable | Default | Constraints | Notes |\n  1033→|---|---|---|---|---|---|\n  1034→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n  1035→| installation_id | INTEGER | No | - | FK -> app_installations(id) ON DELETE CASCADE | Parent installation |\n  1036→| access_token_hash | TEXT | No | - | UNIQUE per table | Hashed access token |\n  1037→| refresh_token_hash | TEXT | Yes | NULL | - | Hashed refresh token |\n  1038→| expires_at | TEXT | No | - | - | ISO-8601 expiration timestamp |\n  1039→\n  1040→**Indexes:**\n  1041→\n  1042→- `idx_oauth_tokens_installation_id` on (`installation_id`) - FK lookup, listing tokens per installation\n  1043→- `idx_oauth_tokens_access_hash` UNIQUE on (`access_token_hash`) - Token validation lookup\n  1044→- `idx_oauth_tokens_expires_at` on (`expires_at`) - Cleanup of expired tokens\n  1045→\n  1046→---\n  1047→\n  1048→### webhook_subscriptions\n  1049→\n  1050→Registered webhook endpoints for event delivery.\n  1051→\n  1052→| Column | Type | Nullable | Default | Constraints | Notes |\n  1053→|---|---|---|---|---|---|\n  1054→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n  1055→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n  1056→| app_installation_id | INTEGER | Yes | NULL | FK -> app_installations(id) ON DELETE CASCADE | Nullable for store-level hooks |\n  1057→| event_type | TEXT | No | - | - | Event to subscribe to |\n  1058→| target_url | TEXT | No | - | - | Delivery endpoint URL |\n  1059→| signing_secret_encrypted | TEXT | No | - | - | Encrypted HMAC signing secret |\n  1060→| status | TEXT | No | 'active' | CHECK IN ('active', 'paused', 'disabled') | |\n  1061→\n  1062→**Indexes:**\n  1063→\n  1064→- `idx_webhook_subscriptions_store_id` on (`store_id`) - FK lookup\n  1065→- `idx_webhook_subscriptions_store_event` on (`store_id`, `event_type`) - Finding subscriptions for a given event\n  1066→- `idx_webhook_subscriptions_installation` on (`app_installation_id`) - Listing subscriptions per app installation\n  1067→\n  1068→---\n  1069→\n  1070→### webhook_deliveries\n  1071→\n  1072→Delivery log for webhook attempts.\n  1073→\n  1074→| Column | Type | Nullable | Default | Constraints | Notes |\n  1075→|---|---|---|---|---|---|\n  1076→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n  1077→| subscription_id | INTEGER | No | - | FK -> webhook_subscriptions(id) ON DELETE CASCADE | Parent subscription |\n  1078→| event_id | TEXT | No | - | - | Unique event identifier |\n  1079→| attempt_count | INTEGER | No | 1 | - | Number of delivery attempts |\n  1080→| status | TEXT | No | 'pending' | CHECK IN ('pending', 'success', 'failed') | |\n  1081→| last_attempt_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n  1082→| response_code | INTEGER | Yes | NULL | - | HTTP response code |\n  1083→| response_body_snippet | TEXT | Yes | NULL | - | Truncated response body for debugging |\n  1084→\n  1085→**Indexes:**\n  1086→\n  1087→- `idx_webhook_deliveries_subscription_id` on (`subscription_id`) - FK lookup, listing deliveries per subscription\n  1088→- `idx_webhook_deliveries_event_id` on (`event_id`) - Deduplication and lookup by event\n  1089→- `idx_webhook_deliveries_status` on (`status`) - Finding pending/failed deliveries for retry\n  1090→- `idx_webhook_deliveries_last_attempt` on (`last_attempt_at`) - Time-based retry scheduling\n  1091→\n  1092→---\n  1093→\n  1094→## Laravel Framework Tables\n  1095→\n  1096→The following tables are managed by Laravel's built-in migrations and should not be modified.\n  1097→\n  1098→### sessions\n  1099→\n  1100→| Column | Type | Nullable | Default | Constraints | Notes |\n  1101→|---|---|---|---|---|---|\n  1102→| id | TEXT | No | - | PRIMARY KEY | Session ID |\n  1103→| user_id | INTEGER | Yes | NULL | - | Associated user |\n  1104→| ip_address | TEXT | Yes | NULL | - | Client IP |\n  1105→| user_agent | TEXT | Yes | NULL | - | Client user agent |\n  1106→| payload | TEXT | No | - | - | Serialized session data |\n  1107→| last_activity | INTEGER | No | - | - | Unix timestamp |\n  1108→\n  1109→### cache\n  1110→\n  1111→| Column | Type | Nullable | Default | Constraints | Notes |\n  1112→|---|---|---|---|---|---|\n  1113→| key | TEXT | No | - | PRIMARY KEY | Cache key |\n  1114→| value | TEXT | No | - | - | Serialized cache value |\n  1115→| expiration | INTEGER | No | - | - | Unix timestamp |\n  1116→\n  1117→### cache_locks\n  1118→\n  1119→| Column | Type | Nullable | Default | Constraints | Notes |\n  1120→|---|---|---|---|---|---|\n  1121→| key | TEXT | No | - | PRIMARY KEY | Lock key |\n  1122→| owner | TEXT | No | - | - | Lock owner identifier |\n  1123→| expiration | INTEGER | No | - | - | Unix timestamp |\n  1124→\n  1125→### jobs\n  1126→\n  1127→| Column | Type | Nullable | Default | Constraints | Notes |\n  1128→|---|---|---|---|---|---|\n  1129→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n  1130→| queue | TEXT | No | - | - | Queue name |\n  1131→| payload | TEXT | No | - | - | Serialized job payload |\n  1132→| attempts | INTEGER | No | - | - | Number of attempts |\n  1133→| reserved_at | INTEGER | Yes | NULL | - | Unix timestamp |\n  1134→| available_at | INTEGER | No | - | - | Unix timestamp |\n  1135→| created_at | INTEGER | No | - | - | Unix timestamp |\n  1136→\n  1137→### job_batches\n  1138→\n  1139→| Column | Type | Nullable | Default | Constraints | Notes |\n  1140→|---|---|---|---|---|---|\n  1141→| id | TEXT | No | - | PRIMARY KEY | Batch UUID |\n  1142→| name | TEXT | No | - | - | Batch name |\n  1143→| total_jobs | INTEGER | No | - | - | Total jobs in batch |\n  1144→| pending_jobs | INTEGER | No | - | - | Pending count |\n  1145→| failed_jobs | INTEGER | No | - | - | Failed count |\n  1146→| failed_job_ids | TEXT | No | - | - | Serialized list of failed IDs |\n  1147→| options | TEXT | Yes | NULL | - | Serialized batch options |\n  1148→| cancelled_at | INTEGER | Yes | NULL | - | Unix timestamp |\n  1149→| created_at | INTEGER | No | - | - | Unix timestamp |\n  1150→| finished_at | INTEGER | Yes | NULL | - | Unix timestamp |\n  1151→\n  1152→### failed_jobs\n  1153→\n  1154→| Column | Type | Nullable | Default | Constraints | Notes |\n  1155→|---|---|---|---|---|---|\n  1156→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n  1157→| uuid | TEXT | No | - | UNIQUE | Job UUID |\n  1158→| connection | TEXT | No | - | - | Queue connection name |\n  1159→| queue | TEXT | No | - | - | Queue name |\n  1160→| payload | TEXT | No | - | - | Serialized job payload |\n  1161→| exception | TEXT | No | - | - | Exception trace |\n  1162→| failed_at | TEXT | No | CURRENT_TIMESTAMP | - | ISO-8601 timestamp |\n  1163→\n  1164→### personal_access_tokens\n  1165→\n  1166→| Column | Type | Nullable | Default | Constraints | Notes |\n  1167→|---|---|---|---|---|---|\n  1168→| id | INTEGER | No | autoincrement | PRIMARY KEY | |\n  1169→| tokenable_type | TEXT | No | - | - | Polymorphic type |\n  1170→| tokenable_id | INTEGER | No | - | - | Polymorphic ID |\n  1171→| name | TEXT | No | - | - | Token name |\n  1172→| token | TEXT | No | - | UNIQUE | SHA-256 hash of the token |\n  1173→| abilities | TEXT | Yes | NULL | - | JSON array of abilities |\n  1174→| last_used_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n  1175→| expires_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n  1176→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n  1177→| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n  1178→\n  1179→### password_reset_tokens\n  1180→\n  1181→| Column | Type | Nullable | Default | Constraints | Notes |\n  1182→|---|---|---|---|---|---|\n  1183→| email | TEXT | No | - | PRIMARY KEY | Admin user email |\n  1184→| token | TEXT | No | - | - | Hashed reset token |\n  1185→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n  1186→\n  1187→---\n  1188→\n  1189→### customer_password_reset_tokens\n  1190→\n  1191→| Column | Type | Nullable | Default | Constraints | Notes |\n  1192→|---|---|---|---|---|---|\n  1193→| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |\n  1194→| email | TEXT | No | - | - | Customer email |\n  1195→| token | TEXT | No | - | - | Hashed reset token |\n  1196→| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |\n  1197→\n  1198→**Primary Key:** (`store_id`, `email`)\n  1199→\n  1200→---\n  1201→\n  1202→## Entity Relationship Diagrams\n  1203→\n  1204→### Epic 1 - Foundation and Tenancy\n  1205→\n  1206→```mermaid\n  1207→erDiagram\n  1208→    organizations ||--o{ stores : \"owns\"\n  1209→    stores ||--o{ store_domains : \"has\"\n  1210→    stores ||--o{ store_users : \"links\"\n  1211→    users ||--o{ store_users : \"links\"\n  1212→    stores ||--|| store_settings : \"has\"\n  1213→\n  1214→    organizations {\n  1215→        INTEGER id PK\n  1216→        TEXT name\n  1217→        TEXT billing_email\n  1218→    }\n  1219→    stores {\n  1220→        INTEGER id PK\n  1221→        INTEGER organization_id FK\n  1222→        TEXT name\n  1223→        TEXT handle\n  1224→        TEXT status\n  1225→    }\n  1226→    store_domains {\n  1227→        INTEGER id PK\n  1228→        INTEGER store_id FK\n  1229→        TEXT hostname\n  1230→        TEXT type\n  1231→        INTEGER is_primary\n  1232→    }\n  1233→    users {\n  1234→        INTEGER id PK\n  1235→        TEXT email\n  1236→        TEXT password_hash\n  1237→        TEXT name\n  1238→        TEXT status\n  1239→    }\n  1240→    store_users {\n  1241→        INTEGER store_id PK\n  1242→        INTEGER user_id PK\n  1243→        TEXT role\n  1244→    }\n  1245→    store_settings {\n  1246→        INTEGER store_id PK\n  1247→        TEXT settings_json\n  1248→    }\n  1249→```\n  1250→\n  1251→### Epic 2 - Catalog, Collections, Inventory, and Media\n  1252→\n  1253→```mermaid\n  1254→erDiagram\n  1255→    stores ||--o{ products : \"has\"\n  1256→    stores ||--o{ collections : \"has\"\n  1257→    products ||--o{ product_options : \"defines\"\n  1258→    product_options ||--o{ product_option_values : \"contains\"\n  1259→    products ||--o{ product_variants : \"has\"\n  1260→    product_variants }o--o{ product_option_values : \"variant_option_values\"\n  1261→    product_variants ||--|| inventory_items : \"tracked by\"\n  1262→    stores ||--o{ inventory_items : \"has\"\n  1263→    collections }o--o{ products : \"collection_products\"\n  1264→    products ||--o{ product_media : \"has\"\n  1265→\n  1266→    products {\n  1267→        INTEGER id PK\n  1268→        INTEGER store_id FK\n  1269→        TEXT title\n  1270→        TEXT handle\n  1271→        TEXT status\n  1272→    }\n  1273→    product_options {\n  1274→        INTEGER id PK\n  1275→        INTEGER product_id FK\n  1276→        TEXT name\n  1277→        INTEGER position\n  1278→    }\n  1279→    product_option_values {\n  1280→        INTEGER id PK\n  1281→        INTEGER product_option_id FK\n  1282→        TEXT value\n  1283→        INTEGER position\n  1284→    }\n  1285→    product_variants {\n  1286→        INTEGER id PK\n  1287→        INTEGER product_id FK\n  1288→        TEXT sku\n  1289→        INTEGER price_amount\n  1290→        TEXT status\n  1291→    }\n  1292→    inventory_items {\n  1293→        INTEGER id PK\n  1294→        INTEGER store_id FK\n  1295→        INTEGER variant_id FK\n  1296→        INTEGER quantity_on_hand\n  1297→        INTEGER quantity_reserved\n  1298→        TEXT policy\n  1299→    }\n  1300→    collections {\n  1301→        INTEGER id PK\n  1302→        INTEGER store_id FK\n  1303→        TEXT title\n  1304→        TEXT handle\n  1305→        TEXT type\n  1306→        TEXT status\n  1307→    }\n  1308→    product_media {\n  1309→        INTEGER id PK\n  1310→        INTEGER product_id FK\n  1311→        TEXT type\n  1312→        TEXT storage_key\n  1313→        INTEGER position\n  1314→        TEXT status\n  1315→    }\n  1316→```\n  1317→\n  1318→### Epic 3 - Themes, Storefront, and Localization\n  1319→\n  1320→```mermaid\n  1321→erDiagram\n  1322→    stores ||--o{ themes : \"has\"\n  1323→    themes ||--o{ theme_files : \"contains\"\n  1324→    themes ||--|| theme_settings : \"configured by\"\n  1325→    stores ||--o{ pages : \"has\"\n  1326→    stores ||--o{ navigation_menus : \"has\"\n  1327→    navigation_menus ||--o{ navigation_items : \"contains\"\n  1328→\n  1329→    themes {\n  1330→        INTEGER id PK\n  1331→        INTEGER store_id FK\n  1332→        TEXT name\n  1333→        TEXT status\n  1334→    }\n  1335→    theme_files {\n  1336→        INTEGER id PK\n  1337→        INTEGER theme_id FK\n  1338→        TEXT path\n  1339→        TEXT storage_key\n  1340→    }\n  1341→    theme_settings {\n  1342→        INTEGER theme_id PK\n  1343→        TEXT settings_json\n  1344→    }\n  1345→    pages {\n  1346→        INTEGER id PK\n  1347→        INTEGER store_id FK\n  1348→        TEXT title\n  1349→        TEXT handle\n  1350→        TEXT status\n  1351→    }\n  1352→    navigation_menus {\n  1353→        INTEGER id PK\n  1354→        INTEGER store_id FK\n  1355→        TEXT handle\n  1356→        TEXT title\n  1357→    }\n  1358→    navigation_items {\n  1359→        INTEGER id PK\n  1360→        INTEGER menu_id FK\n  1361→        TEXT type\n  1362→        TEXT label\n  1363→        INTEGER position\n  1364→    }\n  1365→```\n  1366→\n  1367→### Epics 5-6 - Cart, Checkout, Orders, and Payments\n  1368→\n  1369→```mermaid\n  1370→erDiagram\n  1371→    stores ||--o{ customers : \"has\"\n  1372→    customers ||--o{ customer_addresses : \"has\"\n  1373→    stores ||--o{ carts : \"has\"\n  1374→    customers ||--o{ carts : \"owns\"\n  1375→    carts ||--o{ cart_lines : \"contains\"\n  1376→    product_variants ||--o{ cart_lines : \"referenced by\"\n  1377→    stores ||--o{ checkouts : \"has\"\n  1378→    carts ||--o{ checkouts : \"converted to\"\n  1379→    stores ||--o{ orders : \"has\"\n  1380→    customers ||--o{ orders : \"placed\"\n  1381→    orders ||--o{ order_lines : \"contains\"\n  1382→    orders ||--o{ payments : \"paid via\"\n  1383→    orders ||--o{ refunds : \"refunded via\"\n  1384→    payments ||--o{ refunds : \"source of\"\n  1385→    orders ||--o{ fulfillments : \"shipped via\"\n  1386→    fulfillments ||--o{ fulfillment_lines : \"contains\"\n  1387→    order_lines ||--o{ fulfillment_lines : \"fulfilled by\"\n  1388→\n  1389→    customers {\n  1390→        INTEGER id PK\n  1391→        INTEGER store_id FK\n  1392→        TEXT email\n  1393→        TEXT name\n  1394→    }\n  1395→    carts {\n  1396→        INTEGER id PK\n  1397→        INTEGER store_id FK\n  1398→        INTEGER customer_id FK\n  1399→        TEXT status\n  1400→    }\n  1401→    orders {\n  1402→        INTEGER id PK\n  1403→        INTEGER store_id FK\n  1404→        INTEGER customer_id FK\n  1405→        TEXT order_number\n  1406→        TEXT payment_method\n  1407→        TEXT status\n  1408→        TEXT financial_status\n  1409→        TEXT fulfillment_status\n  1410→        INTEGER total_amount\n  1411→    }\n  1412→    payments {\n  1413→        INTEGER id PK\n  1414→        INTEGER order_id FK\n  1415→        TEXT provider\n  1416→        TEXT method\n  1417→        TEXT status\n  1418→        INTEGER amount\n  1419→    }\n  1420→    fulfillments {\n  1421→        INTEGER id PK\n  1422→        INTEGER order_id FK\n  1423→        TEXT status\n  1424→        TEXT tracking_number\n  1425→    }\n  1426→```\n  1427→\n  1428→### Epics 7-8 - Analytics, Apps, and Webhooks\n  1429→\n  1430→```mermaid\n  1431→erDiagram\n  1432→    stores ||--o{ analytics_events : \"records\"\n  1433→    stores ||--o{ analytics_daily : \"aggregates\"\n  1434→    customers ||--o{ analytics_events : \"generates\"\n  1435→    apps ||--o{ app_installations : \"installed via\"\n  1436→    stores ||--o{ app_installations : \"has\"\n  1437→    apps ||--o{ oauth_clients : \"authenticates via\"\n  1438→    app_installations ||--o{ oauth_tokens : \"issues\"\n  1439→    stores ||--o{ webhook_subscriptions : \"registers\"\n  1440→    app_installations ||--o{ webhook_subscriptions : \"owns\"\n  1441→    webhook_subscriptions ||--o{ webhook_deliveries : \"delivers\"\n  1442→\n  1443→    analytics_events {\n  1444→        INTEGER id PK\n  1445→        INTEGER store_id FK\n  1446→        TEXT type\n  1447→        TEXT session_id\n  1448→    }\n  1449→    analytics_daily {\n  1450→        INTEGER store_id PK\n  1451→        TEXT date PK\n  1452→        INTEGER orders_count\n  1453→        INTEGER revenue_amount\n  1454→    }\n  1455→    apps {\n  1456→        INTEGER id PK\n  1457→        TEXT name\n  1458→        TEXT status\n  1459→    }\n  1460→    app_installations {\n  1461→        INTEGER id PK\n  1462→        INTEGER store_id FK\n  1463→        INTEGER app_id FK\n  1464→        TEXT status\n  1465→    }\n  1466→    webhook_subscriptions {\n  1467→        INTEGER id PK\n  1468→        INTEGER store_id FK\n  1469→        TEXT event_type\n  1470→        TEXT target_url\n  1471→    }\n  1472→    webhook_deliveries {\n  1473→        INTEGER id PK\n  1474→        INTEGER subscription_id FK\n  1475→        TEXT event_id\n  1476→        TEXT status\n  1477→    }\n  1478→```\n  1479→\n  1480→---\n  1481→\n  1482→## SQLite-Specific Configuration Requirements\n  1483→\n  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.\n  1485→\n  1486→### Required PRAGMA Settings\n  1487→\n  1488→| Pragma | Value | Purpose |\n  1489→|---|---|---|\n  1490→| `foreign_keys` | ON | Enforce referential integrity on every connection. Without this, foreign key constraints are silently ignored by SQLite. |\n  1491→| `journal_mode` | WAL | Enable Write-Ahead Logging to allow concurrent reads during writes. Critical for web application performance. |\n  1492→| `busy_timeout` | 5000 | Wait up to 5 seconds for a write lock before throwing an error. Prevents immediate failures under concurrent load. |\n  1493→| `synchronous` | NORMAL | Balances durability and performance. Safe when used with WAL mode; avoids fsync on every commit. |\n  1494→| `cache_size` | -20000 | Use up to 20 MB of page cache. Set in AppServiceProvider if needed. Negative value means kilobytes. |\n  1495→\n  1496→### Laravel Configuration Requirements\n  1497→\n  1498→- The SQLite driver connection must set `foreign_key_constraints` to `true` so that every new database connection enables foreign key enforcement.\n  1499→- The `journal_mode` must be set to `wal` (Write-Ahead Logging) for concurrent read support.\n  1500→- The `busy_timeout` must be set to `5000` (milliseconds) to handle write contention gracefully.\n  1501→- The `synchronous` mode must be set to `normal` for balanced durability with WAL mode.\n  1502→- The `transaction_mode` should be set to `DEFERRED` (the default), meaning transactions acquire locks lazily.\n  1503→- The `cache_size` pragma (set to `-20000` for 20 MB) should be applied via the AppServiceProvider boot method if needed for performance tuning.\n  1504→\n  1505→---\n  1506→\n  1507→## Migration Dependency Graph\n  1508→\n  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.\n  1510→\n  1511→```mermaid\n  1512→flowchart TD\n  1513→    subgraph \"Batch 1 - No Dependencies\"\n  1514→        organizations\n  1515→        apps\n  1516→    end\n  1517→\n  1518→    subgraph \"Batch 2 - Depends on Batch 1\"\n  1519→        stores\n  1520→        users\n  1521→    end\n  1522→\n  1523→    subgraph \"Batch 3 - Depends on Batch 2\"\n  1524→        store_domains\n  1525→        store_users\n  1526→        store_settings\n  1527→        customers\n  1528→        themes\n  1529→        pages\n  1530→        navigation_menus\n  1531→        search_settings\n  1532→        shipping_zones\n  1533→        tax_settings\n  1534→        discounts\n  1535→        products\n  1536→        collections\n  1537→        app_installations\n  1538→        oauth_clients\n  1539→    end\n  1540→\n  1541→    subgraph \"Batch 4 - Depends on Batch 3\"\n  1542→        product_options\n  1543→        product_variants\n  1544→        product_media\n  1545→        collection_products\n  1546→        customer_addresses\n  1547→        carts\n  1548→        navigation_items\n  1549→        theme_files\n  1550→        theme_settings\n  1551→        search_queries\n  1552→        shipping_rates\n  1553→        oauth_tokens\n  1554→        webhook_subscriptions\n  1555→    end\n  1556→\n  1557→    subgraph \"Batch 5 - Depends on Batch 4\"\n  1558→        product_option_values\n  1559→        inventory_items\n  1560→        cart_lines\n  1561→        checkouts\n  1562→        orders\n  1563→        analytics_events\n  1564→        analytics_daily\n  1565→        webhook_deliveries\n  1566→    end\n  1567→\n  1568→    subgraph \"Batch 6 - Depends on Batch 5\"\n  1569→        variant_option_values\n  1570→        order_lines\n  1571→        payments\n  1572→        fulfillments\n  1573→    end\n  1574→\n  1575→    subgraph \"Batch 7 - Depends on Batch 6\"\n  1576→        refunds\n  1577→        fulfillment_lines\n  1578→    end\n  1579→\n  1580→    organizations --> stores\n  1581→    apps --> app_installations\n  1582→    apps --> oauth_clients\n  1583→    stores --> store_domains\n  1584→    stores --> store_users\n  1585→    users --> store_users\n  1586→    stores --> store_settings\n  1587→    stores --> customers\n  1588→    stores --> themes\n  1589→    stores --> pages\n  1590→    stores --> navigation_menus\n  1591→    stores --> search_settings\n  1592→    stores --> shipping_zones\n  1593→    stores --> tax_settings\n  1594→    stores --> discounts\n  1595→    stores --> products\n  1596→    stores --> collections\n  1597→    stores --> app_installations\n  1598→    products --> product_options\n  1599→    products --> product_variants\n  1600→    products --> product_media\n  1601→    collections --> collection_products\n  1602→    products --> collection_products\n  1603→    customers --> customer_addresses\n  1604→    stores --> carts\n  1605→    customers --> carts\n  1606→    navigation_menus --> navigation_items\n  1607→    themes --> theme_files\n  1608→    themes --> theme_settings\n  1609→    stores --> search_queries\n  1610→    shipping_zones --> shipping_rates\n  1611→    app_installations --> oauth_tokens\n  1612→    stores --> webhook_subscriptions\n  1613→    app_installations --> webhook_subscriptions\n  1614→    product_options --> product_option_values\n  1615→    stores --> inventory_items\n  1616→    product_variants --> inventory_items\n  1617→    carts --> cart_lines\n  1618→    product_variants --> cart_lines\n  1619→    stores --> checkouts\n  1620→    carts --> checkouts\n  1621→    customers --> checkouts\n  1622→    stores --> orders\n  1623→    customers --> orders\n  1624→    stores --> analytics_events\n  1625→    customers --> analytics_events\n  1626→    stores --> analytics_daily\n  1627→    webhook_subscriptions --> webhook_deliveries\n  1628→    product_variants --> variant_option_values\n  1629→    product_option_values --> variant_option_values\n  1630→    orders --> order_lines\n  1631→    products --> order_lines\n  1632→    product_variants --> order_lines\n  1633→    orders --> payments\n  1634→    orders --> fulfillments\n  1635→    orders --> refunds\n  1636→    payments --> refunds\n  1637→    fulfillments --> fulfillment_lines\n  1638→    order_lines --> fulfillment_lines\n  1639→```\n  1640→\n  1641→### Batch Details\n  1642→\n  1643→- **Batch 1** (no dependencies): `organizations`, `apps`\n  1644→- **Batch 2** (depends on Batch 1): `stores` (references organizations), `users` (standalone, but logically grouped here)\n  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`\n  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`\n  1647→- **Batch 5** (depends on Batch 4): `product_option_values`, `inventory_items`, `cart_lines`, `checkouts`, `orders`, `analytics_events`, `analytics_daily`, `webhook_deliveries`\n  1648→- **Batch 6** (depends on Batch 5): `variant_option_values`, `order_lines`, `payments`, `fulfillments`\n  1649→- **Batch 7** (depends on Batch 6): `refunds`, `fulfillment_lines`\n  1650→\n  1651→---\n  1652→\n  1653→## Index Strategy Rationale\n  1654→\n  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.\n  1656→\n  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.\n  1658→\n  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.\n  1660→\n  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.\n  1662→\n  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.\n  1664→\n  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.\n  1666→\n  1667→---\n  1668→\n  1669→## Table Count Summary\n  1670→\n  1671→| Epic | Tables | Count |\n  1672→|---|---|---|\n  1673→| 1 - Foundation and Multi-Store Tenancy | organizations, stores, store_domains, users, store_users, store_settings | 6 |\n  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 |\n  1675→| 3 - Themes, Storefront, Localization | themes, theme_files, theme_settings, pages, navigation_menus, navigation_items | 6 |\n  1676→| 4 - Search | search_settings, search_queries | 2 |\n  1677→| 5 - Cart, Checkout, Discounts, Shipping, Taxes | carts, cart_lines, checkouts, shipping_zones, shipping_rates, tax_settings, discounts | 7 |\n  1678→| 6 - Payments, Orders, Fulfillment, Customers | customers, customer_addresses, orders, order_lines, payments, refunds, fulfillments, fulfillment_lines | 8 |\n  1679→| 7 - Analytics | analytics_events, analytics_daily | 2 |\n  1680→| 8 - Apps and Webhooks | apps, app_installations, oauth_clients, oauth_tokens, webhook_subscriptions, webhook_deliveries | 6 |\n  1681→| **Total application tables** | | **46** |\n  1682→| Laravel framework tables | sessions, cache, cache_locks, jobs, job_batches, failed_jobs, personal_access_tokens, password_reset_tokens, customer_password_reset_tokens | 9 |\n  1683→| **Grand total** | | **55** |\n  1684→"}]},"uuid":"8ba694ae-76a1-43f5-bb6e-4a0430c76338","timestamp":"2026-02-13T15:24:03.656Z","sourceToolAssistantUUID":"9f08165f-8925-4736-9d96-ec4d2ac5a1ee"}
{"parentUuid":"8ba694ae-76a1-43f5-bb6e-4a0430c76338","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","message":{"model":"claude-opus-4-6","id":"msg_015JmDnhpuCL9T1ibvYQNUiN","type":"message","role":"assistant","content":[{"type":"tool_use","id":"toolu_01GJVqXw5YNphE2nBfqxFtys","name":"Read","input":{"file_path":"/Users/fabianwesner/Herd/shop/specs/09-IMPLEMENTATION-ROADMAP.md"}}],"stop_reason":null,"stop_sequence":null,"usage":{"input_tokens":2,"cache_creation_input_tokens":5681,"cache_read_input_tokens":16158,"cache_creation":{"ephemeral_5m_input_tokens":5681,"ephemeral_1h_input_tokens":0},"output_tokens":2,"service_tier":"standard","inference_geo":"not_available"}},"requestId":"req_011CY6JJGj71Af8sf94q29QN","type":"assistant","uuid":"c5ff7107-e269-40f3-ab3e-0d35a1aa406f","timestamp":"2026-02-13T15:24:03.993Z"}
{"parentUuid":"c5ff7107-e269-40f3-ab3e-0d35a1aa406f","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","type":"user","message":{"role":"user","content":[{"type":"tool_result","content":"File content (32506 tokens) exceeds maximum allowed tokens (25000). Please use offset and limit parameters to read specific portions of the file, or use the GrepTool to search for specific content.","is_error":true,"tool_use_id":"toolu_01GJVqXw5YNphE2nBfqxFtys"}]},"uuid":"cec11991-3c33-4116-835e-0f488e847774","timestamp":"2026-02-13T15:24:04.276Z","toolUseResult":"Error: File content (32506 tokens) exceeds maximum allowed tokens (25000). Please use offset and limit parameters to read specific portions of the file, or use the GrepTool to search for specific content.","sourceToolAssistantUUID":"c5ff7107-e269-40f3-ab3e-0d35a1aa406f"}
{"parentUuid":"cec11991-3c33-4116-835e-0f488e847774","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","message":{"model":"claude-opus-4-6","id":"msg_015JmDnhpuCL9T1ibvYQNUiN","type":"message","role":"assistant","content":[{"type":"tool_use","id":"toolu_01KuW7xk1sGCYhFTCYdY2Cfo","name":"Read","input":{"file_path":"/Users/fabianwesner/Herd/shop/specs/04-STOREFRONT-UI.md"}}],"stop_reason":null,"stop_sequence":null,"usage":{"input_tokens":2,"cache_creation_input_tokens":5681,"cache_read_input_tokens":16158,"cache_creation":{"ephemeral_5m_input_tokens":5681,"ephemeral_1h_input_tokens":0},"output_tokens":2,"service_tier":"standard","inference_geo":"not_available"}},"requestId":"req_011CY6JJGj71Af8sf94q29QN","type":"assistant","uuid":"971bdb3f-ec0f-4682-b642-1724c58c3141","timestamp":"2026-02-13T15:24:04.470Z"}
{"parentUuid":"971bdb3f-ec0f-4682-b642-1724c58c3141","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","type":"user","message":{"role":"user","content":[{"type":"tool_result","content":"<tool_use_error>Sibling tool call errored</tool_use_error>","is_error":true,"tool_use_id":"toolu_01KuW7xk1sGCYhFTCYdY2Cfo"}]},"uuid":"c7a15f66-f68a-4b61-8962-fee68132e6c6","timestamp":"2026-02-13T15:24:04.471Z","toolUseResult":"Sibling tool call errored","sourceToolAssistantUUID":"971bdb3f-ec0f-4682-b642-1724c58c3141"}
{"parentUuid":"c7a15f66-f68a-4b61-8962-fee68132e6c6","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","message":{"model":"claude-opus-4-6","id":"msg_015JmDnhpuCL9T1ibvYQNUiN","type":"message","role":"assistant","content":[{"type":"tool_use","id":"toolu_01Vheb2kXmibctGRDeb1ja4G","name":"Read","input":{"file_path":"/Users/fabianwesner/Herd/shop/specs/05-BUSINESS-LOGIC.md"}}],"stop_reason":null,"stop_sequence":null,"usage":{"input_tokens":2,"cache_creation_input_tokens":5681,"cache_read_input_tokens":16158,"cache_creation":{"ephemeral_5m_input_tokens":5681,"ephemeral_1h_input_tokens":0},"output_tokens":2,"service_tier":"standard","inference_geo":"not_available"}},"requestId":"req_011CY6JJGj71Af8sf94q29QN","type":"assistant","uuid":"e9a30af6-c154-4a6f-ab41-2c05a1bbd7e0","timestamp":"2026-02-13T15:24:05.036Z"}
{"parentUuid":"e9a30af6-c154-4a6f-ab41-2c05a1bbd7e0","isSidechain":true,"userType":"external","cwd":"/Users/fabianwesner/Herd/shop","sessionId":"f0c99e5d-9537-48ab-9c23-2e8487ca1cb4","version":"2.1.41","gitBranch":"claude-subagents","agentId":"a53c4c5","slug":"wise-shimmying-pretzel","type":"user","message":{"role":"user","content":[{"type":"tool_result","content":"<tool_use_error>Sibling tool call errored</tool_use_error>","is_error":true,"tool_use_id":"toolu_01Vheb2kXmibctGRDeb1ja4G"}]},"uuid":"e65faad8-81c0-44ae-a50a-48047ebdd7f6","timestamp":"2026-02-13T15:24:05.036Z","toolUseResult":"Sibling tool call errored","sourceToolAssistantUUID":"e9a30af6-c154-4a6f-ab41-2c05a1bbd7e0"}

prompt:
You are implementing Phase 8 (Search) of an e-commerce shop in Laravel 12 with Livewire v4, Flux UI Free v2, SQLite, and Pest v4.

## Context
- Phases 1-4 complete. Working directory: /Users/fabianwesner/Herd/shop
- 208 existing tests pass. DO NOT modify existing tests.
- Read specs: `specs/01-DATABASE-SCHEMA.md`, `specs/09-IMPLEMENTATION-ROADMAP.md`
- Read existing code patterns before implementing.

## What to implement

### Step 8.1: Migrations (numbered 000500-000520)
- `search_settings` - PK: store_id, synonyms_json, stop_words_json
- `search_queries` - store_id, query_text, results_count, customer_id (nullable), searched_at
- FTS5 virtual table migration (raw SQL): `products_fts` with columns: title, description, vendor, product_type, tags; content synced from products table

### Step 8.2: Search Service (`App\Services\SearchService`)
- `search(Store $store, string $query, array $filters, int $perPage): LengthAwarePaginator`
  - FTS5 query with store scoping, filter by vendor/collection/price range
- `autocomplete(Store $store, string $prefix, int $limit): Collection`
  - Prefix matching for search-as-you-type
- `syncProduct(Product $product): void` - upsert into FTS5 index
- `removeProduct(int $productId): void` - delete from FTS5
- Create `App\Observers\ProductObserver` that calls syncProduct/removeProduct on create/update/delete
- Register observer in AppServiceProvider

### Step 8.3: Search UI
Update existing storefront search components:
- `Storefront\Search\Index` - full search results page with filters (vendor, price range, collection), sort (relevance, price, newest), pagination
- Update header search to use autocomplete

### Models
- SearchSettings (PK: store_id)
- SearchQuery (BelongsToStore)

### Tests (target 15+ new tests)
- SearchServiceTest: FTS5 search, filters, sorting, store isolation
- AutocompleteTest: prefix matching, result limiting
- ProductObserverTest: sync on create/update, remove on delete

## Rules
- Use existing patterns from Phase 1-4 code
- Run `vendor/bin/pint --dirty --format agent` before finishing
- Run `php artisan test --compact` to verify ALL tests pass
- DO NOT modify existing tests. DO NOT create git commits.