command: rg -n -C 6 "### (users|customer_password_reset_tokens|pages|product_media|analytics_events|analytics_daily|payments|order_lines|theme_files|navigation_items|webhook_deliveries)" specs/01-DATABASE-SCHEMA.md

type: search

cmd: rg -n -C 6 '### (users|customer_password_reset_tokens|pages|product_media|analytics_events|analytics_daily|payments|order_lines|theme_files|navigation_items|webhook_deliveries)' specs/01-DATABASE-SCHEMA.md

query: ### (users|customer_password_reset_tokens|pages|product_media|analytics_events|analytics_daily|payments|order_lines|theme_files|navigation_items|webhook_deliveries)

stdout:
84-- `idx_store_domains_hostname` UNIQUE on (`hostname`) - Globally unique hostname for routing
85-- `idx_store_domains_store_id` on (`store_id`) - FK lookup, listing domains per store
86-- `idx_store_domains_store_primary` on (`store_id`, `is_primary`) - Quick lookup of a store's primary domain
87-
88----
89-
90:### users
91-
92-Admin/staff users who manage stores (not storefront customers).
93-
94-| Column | Type | Nullable | Default | Constraints | Notes |
95-|---|---|---|---|---|---|
96-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
334-
335-- `idx_collection_products_product_id` on (`product_id`) - Reverse lookup: find all collections containing a product
336-- `idx_collection_products_position` on (`collection_id`, `position`) - Ordered product listing within a collection
337-
338----
339-
340:### product_media
341-
342-Images and videos attached to a product.
343-
344-| Column | Type | Nullable | Default | Constraints | Notes |
345-|---|---|---|---|---|---|
346-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
385-
386-- `idx_themes_store_id` on (`store_id`) - FK lookup, listing themes per store
387-- `idx_themes_store_status` on (`store_id`, `status`) - Finding published/draft themes per store
388-
389----
390-
391:### theme_files
392-
393-Individual files belonging to a theme (templates, assets, snippets).
394-
395-| Column | Type | Nullable | Default | Constraints | Notes |
396-|---|---|---|---|---|---|
397-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
417-| theme_id | INTEGER | No | - | PRIMARY KEY, FK -> themes(id) ON DELETE CASCADE | One-to-one with themes |
418-| settings_json | TEXT | No | '{}' | - | JSON settings object |
419-| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
420-
421----
422-
423:### pages
424-
425-Static CMS pages (About Us, Contact, etc.).
426-
427-| Column | Type | Nullable | Default | Constraints | Notes |
428-|---|---|---|---|---|---|
429-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
461-
462-- `idx_navigation_menus_store_handle` UNIQUE on (`store_id`, `handle`) - Unique handle per store
463-- `idx_navigation_menus_store_id` on (`store_id`) - FK lookup, listing menus per store
464-
465----
466-
467:### navigation_items
468-
469-Individual links within a navigation menu (supports nesting via position).
470-
471-| Column | Type | Nullable | Default | Constraints | Notes |
472-|---|---|---|---|---|---|
473-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
784-- `idx_orders_store_financial` on (`store_id`, `financial_status`) - Filtering by payment status
785-- `idx_orders_store_fulfillment` on (`store_id`, `fulfillment_status`) - Filtering by shipping status
786-- `idx_orders_placed_at` on (`store_id`, `placed_at`) - Date-range order queries
787-
788----
789-
790:### order_lines
791-
792-Individual items within an order. Contains snapshots of product data at time of purchase.
793-
794-| Column | Type | Nullable | Default | Constraints | Notes |
795-|---|---|---|---|---|---|
796-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
812-- `idx_order_lines_variant_id` on (`variant_id`) - Finding order lines by variant
813-
814-**Notes:** `product_id` and `variant_id` are nullable with SET NULL on delete so that order history survives product deletion. `tax_lines_json` example: `[{"title": "State Tax", "rate": 800, "amount": 400}]` (rate in basis points, e.g. 800 = 8%). `discount_allocations_json` example: `[{"discount_id": 5, "amount": 250}]`.
815-
816----
817-
818:### payments
819-
820-Payment attempts and captures for an order.
821-
822-| Column | Type | Nullable | Default | Constraints | Notes |
823-|---|---|---|---|---|---|
824-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
908-**Notes:** An order with multiple line items may be partially fulfilled. This table tracks how many units of each line item are included in each shipment.
909-
910----
911-
912-## Epic 7 - Analytics
913-
914:### analytics_events
915-
916-Raw event stream for storefront analytics.
917-
918-| Column | Type | Nullable | Default | Constraints | Notes |
919-|---|---|---|---|---|---|
920-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
937-- `idx_analytics_events_client_event` UNIQUE on (`store_id`, `client_event_id`) - Client-side event deduplication
938-
939-**Notes:** Event types include: `page_view`, `product_view`, `add_to_cart`, `remove_from_cart`, `checkout_started`, `checkout_completed`, `search`. This table will grow fast - consider periodic archiving or partitioning by date for production workloads beyond SQLite.
940-
941----
942-
943:### analytics_daily
944-
945-Pre-aggregated daily metrics per store. Composite primary key.
946-
947-| Column | Type | Nullable | Default | Constraints | Notes |
948-|---|---|---|---|---|---|
949-| store_id | INTEGER | No | - | PK (composite), FK -> stores(id) ON DELETE CASCADE | |
--
1064-- `idx_webhook_subscriptions_store_id` on (`store_id`) - FK lookup
1065-- `idx_webhook_subscriptions_store_event` on (`store_id`, `event_type`) - Finding subscriptions for a given event
1066-- `idx_webhook_subscriptions_installation` on (`app_installation_id`) - Listing subscriptions per app installation
1067-
1068----
1069-
1070:### webhook_deliveries
1071-
1072-Delivery log for webhook attempts.
1073-
1074-| Column | Type | Nullable | Default | Constraints | Notes |
1075-|---|---|---|---|---|---|
1076-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
1183-| email | TEXT | No | - | PRIMARY KEY | Admin user email |
1184-| token | TEXT | No | - | - | Hashed reset token |
1185-| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
1186-
1187----
1188-
1189:### customer_password_reset_tokens
1190-
1191-| Column | Type | Nullable | Default | Constraints | Notes |
1192-|---|---|---|---|---|---|
1193-| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
1194-| email | TEXT | No | - | - | Customer email |
1195-| token | TEXT | No | - | - | Hashed reset token |


text:
Script completed
Wall time 0.1 seconds
Output:

text:
84-- `idx_store_domains_hostname` UNIQUE on (`hostname`) - Globally unique hostname for routing
85-- `idx_store_domains_store_id` on (`store_id`) - FK lookup, listing domains per store
86-- `idx_store_domains_store_primary` on (`store_id`, `is_primary`) - Quick lookup of a store's primary domain
87-
88----
89-
90:### users
91-
92-Admin/staff users who manage stores (not storefront customers).
93-
94-| Column | Type | Nullable | Default | Constraints | Notes |
95-|---|---|---|---|---|---|
96-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
334-
335-- `idx_collection_products_product_id` on (`product_id`) - Reverse lookup: find all collections containing a product
336-- `idx_collection_products_position` on (`collection_id`, `position`) - Ordered product listing within a collection
337-
338----
339-
340:### product_media
341-
342-Images and videos attached to a product.
343-
344-| Column | Type | Nullable | Default | Constraints | Notes |
345-|---|---|---|---|---|---|
346-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
385-
386-- `idx_themes_store_id` on (`store_id`) - FK lookup, listing themes per store
387-- `idx_themes_store_status` on (`store_id`, `status`) - Finding published/draft themes per store
388-
389----
390-
391:### theme_files
392-
393-Individual files belonging to a theme (templates, assets, snippets).
394-
395-| Column | Type | Nullable | Default | Constraints | Notes |
396-|---|---|---|---|---|---|
397-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
417-| theme_id | INTEGER | No | - | PRIMARY KEY, FK -> themes(id) ON DELETE CASCADE | One-to-one with themes |
418-| settings_json | TEXT | No | '{}' | - | JSON settings object |
419-| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
420-
421----
422-
423:### pages
424-
425-Static CMS pages (About Us, Contact, etc.).
426-
427-| Column | Type | Nullable | Default | Constraints | Notes |
428-|---|---|---|---|---|---|
429-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
461-
462-- `idx_navigation_menus_store_handle` UNIQUE on (`store_id`, `handle`) - Unique handle per store
463-- `idx_navigation_menus_store_id` on (`store_id`) - FK lookup, listing menus per store
464-
465----
466-
467:### navigation_items
468-
469-Individual links within a navigation menu (supports nesting via position).
470-
471-| Column | Type | Nullable | Default | Constraints | Notes |
472-|---|---|---|---|---|---|
473-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
784-- `idx_orders_store_financial` on (`store_id`, `financial_status`) - Filtering by payment status
785-- `idx_orders_store_fulfillment` on (`store_id`, `fulfillment_status`) - Filtering by shipping status
786-- `idx_orders_placed_at` on (`store_id`, `placed_at`) - Date-range order queries
787-
788----
789-
790:### order_lines
791-
792-Individual items within an order. Contains snapshots of product data at time of purchase.
793-
794-| Column | Type | Nullable | Default | Constraints | Notes |
795-|---|---|---|---|---|---|
796-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
812-- `idx_order_lines_variant_id` on (`variant_id`) - Finding order lines by variant
813-
814-**Notes:** `product_id` and `variant_id` are nullable with SET NULL on delete so that order history survives product deletion. `tax_lines_json` example: `[{"title": "State Tax", "rate": 800, "amount": 400}]` (rate in basis points, e.g. 800 = 8%). `discount_allocations_json` example: `[{"discount_id": 5, "amount": 250}]`.
815-
816----
817-
818:### payments
819-
820-Payment attempts and captures for an order.
821-
822-| Column | Type | Nullable | Default | Constraints | Notes |
823-|---|---|---|---|---|---|
824-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
908-**Notes:** An order with multiple line items may be partially fulfilled. This table tracks how many units of each line item are included in each shipment.
909-
910----
911-
912-## Epic 7 - Analytics
913-
914:### analytics_events
915-
916-Raw event stream for storefront analytics.
917-
918-| Column | Type | Nullable | Default | Constraints | Notes |
919-|---|---|---|---|---|---|
920-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
937-- `idx_analytics_events_client_event` UNIQUE on (`store_id`, `client_event_id`) - Client-side event deduplication
938-
939-**Notes:** Event types include: `page_view`, `product_view`, `add_to_cart`, `remove_from_cart`, `checkout_started`, `checkout_completed`, `search`. This table will grow fast - consider periodic archiving or partitioning by date for production workloads beyond SQLite.
940-
941----
942-
943:### analytics_daily
944-
945-Pre-aggregated daily metrics per store. Composite primary key.
946-
947-| Column | Type | Nullable | Default | Constraints | Notes |
948-|---|---|---|---|---|---|
949-| store_id | INTEGER | No | - | PK (composite), FK -> stores(id) ON DELETE CASCADE | |
--
1064-- `idx_webhook_subscriptions_store_id` on (`store_id`) - FK lookup
1065-- `idx_webhook_subscriptions_store_event` on (`store_id`, `event_type`) - Finding subscriptions for a given event
1066-- `idx_webhook_subscriptions_installation` on (`app_installation_id`) - Listing subscriptions per app installation
1067-
1068----
1069-
1070:### webhook_deliveries
1071-
1072-Delivery log for webhook attempts.
1073-
1074-| Column | Type | Nullable | Default | Constraints | Notes |
1075-|---|---|---|---|---|---|
1076-| id | INTEGER | No | autoincrement | PRIMARY KEY | |
--
1183-| email | TEXT | No | - | PRIMARY KEY | Admin user email |
1184-| token | TEXT | No | - | - | Hashed reset token |
1185-| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
1186-
1187----
1188-
1189:### customer_password_reset_tokens
1190-
1191-| Column | Type | Nullable | Default | Constraints | Notes |
1192-|---|---|---|---|---|---|
1193-| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
1194-| email | TEXT | No | - | - | Customer email |
1195-| token | TEXT | No | - | - | Hashed reset token |


exec

status: completed

input:
const r = await tools.exec_command({cmd: "sed -n '90,128p' specs/01-DATABASE-SCHEMA.md; sed -n '340,375p' specs/01-DATABASE-SCHEMA.md; sed -n '391,452p' specs/01-DATABASE-SCHEMA.md; sed -n '467,520p' specs/01-DATABASE-SCHEMA.md; sed -n '790,840p' specs/01-DATABASE-SCHEMA.md; sed -n '914,965p' specs/01-DATABASE-SCHEMA.md; sed -n '1070,1115p' specs/01-DATABASE-SCHEMA.md; sed -n '1180,1210p' specs/01-DATABASE-SCHEMA.md", workdir: "/Users/wesner/Herd/shop", max_output_tokens: 26000, yield_time_ms: 10000});
text(r.output);


type: CommandExecution

command: sed -n '90,128p' specs/01-DATABASE-SCHEMA.md; sed -n '340,375p' specs/01-DATABASE-SCHEMA.md; sed -n '391,452p' specs/01-DATABASE-SCHEMA.md; sed -n '467,520p' specs/01-DATABASE-SCHEMA.md; sed -n '790,840p' specs/01-DATABASE-SCHEMA.md; sed -n '914,965p' specs/01-DATABASE-SCHEMA.md; sed -n '1070,1115p' specs/01-DATABASE-SCHEMA.md; sed -n '1180,1210p' specs/01-DATABASE-SCHEMA.md

type: read

status: completed

stdout:
### users

Admin/staff users who manage stores (not storefront customers).

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| email | TEXT | No | - | UNIQUE per table | Login email |
| password_hash | TEXT | No | - | - | bcrypt/argon2 hash; maps to Laravel's `password` field |
| name | TEXT | No | - | - | Display name |
| status | TEXT | No | 'active' | CHECK IN ('active', 'disabled') | |
| email_verified_at | TEXT | Yes | NULL | - | ISO-8601 timestamp; required for `verified` middleware |
| last_login_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| two_factor_secret | TEXT | Yes | NULL | - | TOTP secret |
| two_factor_recovery_codes | TEXT | Yes | NULL | - | JSON array of recovery codes |
| two_factor_confirmed_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| remember_token | TEXT | Yes | NULL | - | Laravel remember-me token |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_users_email` UNIQUE on (`email`) - Enforces unique email, supports login lookup
- `idx_users_status` on (`status`) - Filtering users by active/disabled

**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.

---

### store_users

Pivot table linking users to stores with a role. Uses composite primary key.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| store_id | INTEGER | No | - | PK (composite), FK -> stores(id) ON DELETE CASCADE | |
| user_id | INTEGER | No | - | PK (composite), FK -> users(id) ON DELETE CASCADE | |
| role | TEXT | No | 'staff' | CHECK IN ('owner', 'admin', 'staff', 'support') | |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
### product_media

Images and videos attached to a product.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| product_id | INTEGER | No | - | FK -> products(id) ON DELETE CASCADE | Parent product |
| type | TEXT | No | 'image' | CHECK IN ('image', 'video') | Media type |
| storage_key | TEXT | No | - | - | File storage path/key |
| alt_text | TEXT | Yes | NULL | - | Accessibility alt text |
| width | INTEGER | Yes | NULL | - | Pixel width |
| height | INTEGER | Yes | NULL | - | Pixel height |
| mime_type | TEXT | Yes | NULL | - | e.g. "image/jpeg" |
| byte_size | INTEGER | Yes | NULL | - | File size in bytes |
| position | INTEGER | No | 0 | - | Display ordering |
| status | TEXT | No | 'processing' | CHECK IN ('processing', 'ready', 'failed') | Processing status |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_product_media_product_id` on (`product_id`) - FK lookup, listing media per product
- `idx_product_media_product_position` on (`product_id`, `position`) - Ordered media listing
- `idx_product_media_status` on (`status`) - Finding media by processing status

---

## Epic 3 - Themes, Storefront, and Localization

### themes

Storefront theme definitions per store.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
### theme_files

Individual files belonging to a theme (templates, assets, snippets).

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| theme_id | INTEGER | No | - | FK -> themes(id) ON DELETE CASCADE | Parent theme |
| path | TEXT | No | - | UNIQUE per (theme_id, path) | File path within theme |
| storage_key | TEXT | No | - | - | File storage path/key |
| sha256 | TEXT | No | - | - | Content hash for cache busting |
| byte_size | INTEGER | No | 0 | - | File size in bytes |

**Indexes:**

- `idx_theme_files_theme_path` UNIQUE on (`theme_id`, `path`) - Enforces unique file paths per theme
- `idx_theme_files_theme_id` on (`theme_id`) - FK lookup, listing files per theme

---

### theme_settings

Single-row JSON configuration per theme.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| theme_id | INTEGER | No | - | PRIMARY KEY, FK -> themes(id) ON DELETE CASCADE | One-to-one with themes |
| settings_json | TEXT | No | '{}' | - | JSON settings object |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

---

### pages

Static CMS pages (About Us, Contact, etc.).

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| title | TEXT | No | - | - | Page title |
| handle | TEXT | No | - | UNIQUE per (store_id, handle) | URL-friendly slug |
| body_html | TEXT | Yes | NULL | - | Rich-text content |
| status | TEXT | No | 'draft' | CHECK IN ('draft', 'published', 'archived') | |
| published_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_pages_store_handle` UNIQUE on (`store_id`, `handle`) - Unique handle per store for URL routing
- `idx_pages_store_id` on (`store_id`) - FK lookup, listing pages per store
- `idx_pages_store_status` on (`store_id`, `status`) - Filtering pages by status

---

### navigation_menus

Named menus (e.g. main-menu, footer-menu).

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
### navigation_items

Individual links within a navigation menu (supports nesting via position).

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| menu_id | INTEGER | No | - | FK -> navigation_menus(id) ON DELETE CASCADE | Parent menu |
| type | TEXT | No | 'link' | CHECK IN ('link', 'page', 'collection', 'product') | Item type |
| label | TEXT | No | - | - | Display label |
| url | TEXT | Yes | NULL | - | Direct URL (used when type = 'link') |
| resource_id | INTEGER | Yes | NULL | - | FK to pages/collections/products (polymorphic) |
| position | INTEGER | No | 0 | - | Display ordering |

**Indexes:**

- `idx_navigation_items_menu_id` on (`menu_id`) - FK lookup, listing items per menu
- `idx_navigation_items_menu_position` on (`menu_id`, `position`) - Ordered item listing

**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.

---

## Epic 4 - Search

### search_settings

Per-store search configuration (synonyms, stop words).

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| store_id | INTEGER | No | - | PRIMARY KEY, FK -> stores(id) ON DELETE CASCADE | One-to-one with stores |
| synonyms_json | TEXT | No | '[]' | - | JSON array of synonym groups |
| stop_words_json | TEXT | No | '[]' | - | JSON array of stop words |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

---

### search_queries

Search query log for analytics and autocomplete.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| query | TEXT | No | - | - | Search query text |
| filters_json | TEXT | Yes | NULL | - | JSON object of applied filters |
| results_count | INTEGER | No | 0 | - | Number of results returned |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_search_queries_store_id` on (`store_id`) - FK lookup
### order_lines

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

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

**Indexes:**

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

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

---

### payments

Payment attempts and captures for an order.

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

**Indexes:**

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

Raw event stream for storefront analytics.

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

**Indexes:**

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

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

---

### analytics_daily

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

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

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

**Indexes:**

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

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

Delivery log for webhook attempts.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| subscription_id | INTEGER | No | - | FK -> webhook_subscriptions(id) ON DELETE CASCADE | Parent subscription |
| event_id | TEXT | No | - | - | Unique event identifier |
| attempt_count | INTEGER | No | 1 | - | Number of delivery attempts |
| status | TEXT | No | 'pending' | CHECK IN ('pending', 'success', 'failed') | |
| last_attempt_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| response_code | INTEGER | Yes | NULL | - | HTTP response code |
| response_body_snippet | TEXT | Yes | NULL | - | Truncated response body for debugging |

**Indexes:**

- `idx_webhook_deliveries_subscription_id` on (`subscription_id`) - FK lookup, listing deliveries per subscription
- `idx_webhook_deliveries_event_id` on (`event_id`) - Deduplication and lookup by event
- `idx_webhook_deliveries_status` on (`status`) - Finding pending/failed deliveries for retry
- `idx_webhook_deliveries_last_attempt` on (`last_attempt_at`) - Time-based retry scheduling

---

## Laravel Framework Tables

The following tables are managed by Laravel's built-in migrations and should not be modified.

### sessions

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | TEXT | No | - | PRIMARY KEY | Session ID |
| user_id | INTEGER | Yes | NULL | - | Associated user |
| ip_address | TEXT | Yes | NULL | - | Client IP |
| user_agent | TEXT | Yes | NULL | - | Client user agent |
| payload | TEXT | No | - | - | Serialized session data |
| last_activity | INTEGER | No | - | - | Unix timestamp |

### cache

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| key | TEXT | No | - | PRIMARY KEY | Cache key |
| value | TEXT | No | - | - | Serialized cache value |
| expiration | INTEGER | No | - | - | Unix timestamp |

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| email | TEXT | No | - | PRIMARY KEY | Admin user email |
| token | TEXT | No | - | - | Hashed reset token |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

---

### customer_password_reset_tokens

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| email | TEXT | No | - | - | Customer email |
| token | TEXT | No | - | - | Hashed reset token |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

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

---

## Entity Relationship Diagrams

### Epic 1 - Foundation and Tenancy

```mermaid
erDiagram
    organizations ||--o{ stores : "owns"
    stores ||--o{ store_domains : "has"
    stores ||--o{ store_users : "links"


text:
Script completed
Wall time 0.1 seconds
Output:

text:
### users

Admin/staff users who manage stores (not storefront customers).

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| email | TEXT | No | - | UNIQUE per table | Login email |
| password_hash | TEXT | No | - | - | bcrypt/argon2 hash; maps to Laravel's `password` field |
| name | TEXT | No | - | - | Display name |
| status | TEXT | No | 'active' | CHECK IN ('active', 'disabled') | |
| email_verified_at | TEXT | Yes | NULL | - | ISO-8601 timestamp; required for `verified` middleware |
| last_login_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| two_factor_secret | TEXT | Yes | NULL | - | TOTP secret |
| two_factor_recovery_codes | TEXT | Yes | NULL | - | JSON array of recovery codes |
| two_factor_confirmed_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| remember_token | TEXT | Yes | NULL | - | Laravel remember-me token |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_users_email` UNIQUE on (`email`) - Enforces unique email, supports login lookup
- `idx_users_status` on (`status`) - Filtering users by active/disabled

**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.

---

### store_users

Pivot table linking users to stores with a role. Uses composite primary key.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| store_id | INTEGER | No | - | PK (composite), FK -> stores(id) ON DELETE CASCADE | |
| user_id | INTEGER | No | - | PK (composite), FK -> users(id) ON DELETE CASCADE | |
| role | TEXT | No | 'staff' | CHECK IN ('owner', 'admin', 'staff', 'support') | |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
### product_media

Images and videos attached to a product.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| product_id | INTEGER | No | - | FK -> products(id) ON DELETE CASCADE | Parent product |
| type | TEXT | No | 'image' | CHECK IN ('image', 'video') | Media type |
| storage_key | TEXT | No | - | - | File storage path/key |
| alt_text | TEXT | Yes | NULL | - | Accessibility alt text |
| width | INTEGER | Yes | NULL | - | Pixel width |
| height | INTEGER | Yes | NULL | - | Pixel height |
| mime_type | TEXT | Yes | NULL | - | e.g. "image/jpeg" |
| byte_size | INTEGER | Yes | NULL | - | File size in bytes |
| position | INTEGER | No | 0 | - | Display ordering |
| status | TEXT | No | 'processing' | CHECK IN ('processing', 'ready', 'failed') | Processing status |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_product_media_product_id` on (`product_id`) - FK lookup, listing media per product
- `idx_product_media_product_position` on (`product_id`, `position`) - Ordered media listing
- `idx_product_media_status` on (`status`) - Finding media by processing status

---

## Epic 3 - Themes, Storefront, and Localization

### themes

Storefront theme definitions per store.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
### theme_files

Individual files belonging to a theme (templates, assets, snippets).

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| theme_id | INTEGER | No | - | FK -> themes(id) ON DELETE CASCADE | Parent theme |
| path | TEXT | No | - | UNIQUE per (theme_id, path) | File path within theme |
| storage_key | TEXT | No | - | - | File storage path/key |
| sha256 | TEXT | No | - | - | Content hash for cache busting |
| byte_size | INTEGER | No | 0 | - | File size in bytes |

**Indexes:**

- `idx_theme_files_theme_path` UNIQUE on (`theme_id`, `path`) - Enforces unique file paths per theme
- `idx_theme_files_theme_id` on (`theme_id`) - FK lookup, listing files per theme

---

### theme_settings

Single-row JSON configuration per theme.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| theme_id | INTEGER | No | - | PRIMARY KEY, FK -> themes(id) ON DELETE CASCADE | One-to-one with themes |
| settings_json | TEXT | No | '{}' | - | JSON settings object |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

---

### pages

Static CMS pages (About Us, Contact, etc.).

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| title | TEXT | No | - | - | Page title |
| handle | TEXT | No | - | UNIQUE per (store_id, handle) | URL-friendly slug |
| body_html | TEXT | Yes | NULL | - | Rich-text content |
| status | TEXT | No | 'draft' | CHECK IN ('draft', 'published', 'archived') | |
| published_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_pages_store_handle` UNIQUE on (`store_id`, `handle`) - Unique handle per store for URL routing
- `idx_pages_store_id` on (`store_id`) - FK lookup, listing pages per store
- `idx_pages_store_status` on (`store_id`, `status`) - Filtering pages by status

---

### navigation_menus

Named menus (e.g. main-menu, footer-menu).

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
### navigation_items

Individual links within a navigation menu (supports nesting via position).

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| menu_id | INTEGER | No | - | FK -> navigation_menus(id) ON DELETE CASCADE | Parent menu |
| type | TEXT | No | 'link' | CHECK IN ('link', 'page', 'collection', 'product') | Item type |
| label | TEXT | No | - | - | Display label |
| url | TEXT | Yes | NULL | - | Direct URL (used when type = 'link') |
| resource_id | INTEGER | Yes | NULL | - | FK to pages/collections/products (polymorphic) |
| position | INTEGER | No | 0 | - | Display ordering |

**Indexes:**

- `idx_navigation_items_menu_id` on (`menu_id`) - FK lookup, listing items per menu
- `idx_navigation_items_menu_position` on (`menu_id`, `position`) - Ordered item listing

**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.

---

## Epic 4 - Search

### search_settings

Per-store search configuration (synonyms, stop words).

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| store_id | INTEGER | No | - | PRIMARY KEY, FK -> stores(id) ON DELETE CASCADE | One-to-one with stores |
| synonyms_json | TEXT | No | '[]' | - | JSON array of synonym groups |
| stop_words_json | TEXT | No | '[]' | - | JSON array of stop words |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

---

### search_queries

Search query log for analytics and autocomplete.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| query | TEXT | No | - | - | Search query text |
| filters_json | TEXT | Yes | NULL | - | JSON object of applied filters |
| results_count | INTEGER | No | 0 | - | Number of results returned |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_search_queries_store_id` on (`store_id`) - FK lookup
### order_lines

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

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

**Indexes:**

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

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

---

### payments

Payment attempts and captures for an order.

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

**Indexes:**

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

Raw event stream for storefront analytics.

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

**Indexes:**

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

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

---

### analytics_daily

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

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

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

**Indexes:**

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

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

Delivery log for webhook attempts.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| subscription_id | INTEGER | No | - | FK -> webhook_subscriptions(id) ON DELETE CASCADE | Parent subscription |
| event_id | TEXT | No | - | - | Unique event identifier |
| attempt_count | INTEGER | No | 1 | - | Number of delivery attempts |
| status | TEXT | No | 'pending' | CHECK IN ('pending', 'success', 'failed') | |
| last_attempt_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| response_code | INTEGER | Yes | NULL | - | HTTP response code |
| response_body_snippet | TEXT | Yes | NULL | - | Truncated response body for debugging |

**Indexes:**

- `idx_webhook_deliveries_subscription_id` on (`subscription_id`) - FK lookup, listing deliveries per subscription
- `idx_webhook_deliveries_event_id` on (`event_id`) - Deduplication and lookup by event
- `idx_webhook_deliveries_status` on (`status`) - Finding pending/failed deliveries for retry
- `idx_webhook_deliveries_last_attempt` on (`last_attempt_at`) - Time-based retry scheduling

---

## Laravel Framework Tables

The following tables are managed by Laravel's built-in migrations and should not be modified.

### sessions

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | TEXT | No | - | PRIMARY KEY | Session ID |
| user_id | INTEGER | Yes | NULL | - | Associated user |
| ip_address | TEXT | Yes | NULL | - | Client IP |
| user_agent | TEXT | Yes | NULL | - | Client user agent |
| payload | TEXT | No | - | - | Serialized session data |
| last_activity | INTEGER | No | - | - | Unix timestamp |

### cache

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| key | TEXT | No | - | PRIMARY KEY | Cache key |
| value | TEXT | No | - | - | Serialized cache value |
| expiration | INTEGER | No | - | - | Unix timestamp |

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| email | TEXT | No | - | PRIMARY KEY | Admin user email |
| token | TEXT | No | - | - | Hashed reset token |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

---

### customer_password_reset_tokens

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| email | TEXT | No | - | - | Customer email |
| token | TEXT | No | - | - | Hashed reset token |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

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

---

## Entity Relationship Diagrams

### Epic 1 - Foundation and Tenancy

```mermaid
erDiagram
    organizations ||--o{ stores : "owns"
    stores ||--o{ store_domains : "has"
    stores ||--o{ store_users : "links"


status: completed

input:
const r = await tools.exec_command({cmd: "sed -n '1,220p' app/Models/AnalyticsEvent.php; sed -n '1,120p' app/Models/NavigationItem.php; sed -n '1,180p' app/Models/ThemeFile.php; sed -n '1,160p' app/Models/Payment.php; sed -n '1,240p' database/migrations/2026_08_20_000000_create_shop_schema.php | tail -90", workdir: "/Users/wesner/Herd/shop", max_output_tokens: 12000, yield_time_ms: 10000});
text(r.output);


command: sed -n '1,220p' app/Models/AnalyticsEvent.php; sed -n '1,120p' app/Models/NavigationItem.php; sed -n '1,180p' app/Models/ThemeFile.php; sed -n '1,160p' app/Models/Payment.php; sed -n '1,240p' database/migrations/2026_08_20_000000_create_shop_schema.php | tail -90

Payment.php

cmd: sed -n '1,160p' app/Models/Payment.php

stdout:
<?php

namespace App\Models;

use App\Models\Concerns\BelongsToStore;
use Illuminate\Database\Eloquent\Model;

class AnalyticsEvent extends Model
{
    use BelongsToStore;

    protected $fillable = ['store_id', 'type', 'session_id', 'customer_id', 'client_event_id', 'payload'];

    protected function casts(): array
    {
        return ['payload' => 'array'];
    }
}
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class NavigationItem extends Model
{
    protected $fillable = ['navigation_menu_id', 'label', 'type', 'url', 'resource_id', 'position', 'parent_id'];

    public function menu(): BelongsTo
    {
        return $this->belongsTo(NavigationMenu::class, 'navigation_menu_id');
    }

    public function parent(): BelongsTo
    {
        return $this->belongsTo(self::class, 'parent_id');
    }
}
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class ThemeFile extends Model
{
    protected $fillable = ['theme_id', 'path', 'content'];

    public function theme(): BelongsTo
    {
        return $this->belongsTo(Theme::class);
    }
}
<?php

namespace App\Models;

use App\Enums\PaymentMethod;
use App\Enums\PaymentStatus;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class Payment extends Model
{
    protected $fillable = ['order_id', 'provider', 'provider_payment_id', 'method', 'status', 'amount', 'raw_json_encrypted'];

    protected $hidden = ['raw_json_encrypted'];

    protected function casts(): array
    {
        return ['method' => PaymentMethod::class, 'status' => PaymentStatus::class, 'raw_json_encrypted' => 'encrypted'];
    }

    public function order(): BelongsTo
    {
        return $this->belongsTo(Order::class);
    }
}
            $table->timestamps();
            $table->unique(['store_id', 'handle']);
            $table->index(['store_id', 'status']);
        });

        Schema::create('collection_products', function (Blueprint $table): void {
            $table->foreignId('collection_id')->constrained()->cascadeOnDelete();
            $table->foreignId('product_id')->constrained()->cascadeOnDelete();
            $table->unsignedInteger('position')->default(0);
            $table->primary(['collection_id', 'product_id']);
            $table->index('product_id');
            $table->index(['collection_id', 'position']);
        });

        Schema::create('product_media', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('product_id')->constrained()->cascadeOnDelete();
            $table->string('path');
            $table->string('url')->nullable();
            $table->string('alt_text')->nullable();
            $table->string('status')->default('ready');
            $table->unsignedInteger('position')->default(0);
            $table->json('metadata')->nullable();
            $table->timestamps();
            $table->index(['product_id', 'position']);
            $table->index('status');
        });

        Schema::create('themes', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->string('name');
            $table->string('status')->default('draft')->index();
            $table->string('version')->default('1.0.0');
            $table->json('settings')->nullable();
            $table->timestamps();
            $table->index(['store_id', 'status']);
        });

        Schema::create('theme_files', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('theme_id')->constrained()->cascadeOnDelete();
            $table->string('path');
            $table->longText('content')->nullable();
            $table->timestamps();
            $table->unique(['theme_id', 'path']);
        });

        Schema::create('theme_settings', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('theme_id')->constrained()->cascadeOnDelete();
            $table->string('key');
            $table->json('value')->nullable();
            $table->timestamps();
            $table->unique(['theme_id', 'key']);
        });

        Schema::create('pages', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->string('title');
            $table->string('handle');
            $table->longText('content')->nullable();
            $table->string('status')->default('draft')->index();
            $table->timestamp('published_at')->nullable();
            $table->timestamps();
            $table->unique(['store_id', 'handle']);
        });

        Schema::create('navigation_menus', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->string('name');
            $table->string('handle');
            $table->timestamps();
            $table->unique(['store_id', 'handle']);
        });

        Schema::create('navigation_items', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('navigation_menu_id')->constrained('navigation_menus')->cascadeOnDelete();
            $table->string('label');
            $table->string('type')->default('link');
            $table->string('url')->nullable();
            $table->unsignedBigInteger('resource_id')->nullable();
            $table->unsignedInteger('position')->default(0);
            $table->foreignId('parent_id')->nullable()->constrained('navigation_items')->nullOnDelete();
            $table->timestamps();
            $table->index(['navigation_menu_id', 'position']);
        });


text:
Script completed
Wall time 0.1 seconds
Output:

text:
<?php

namespace App\Models;

use App\Models\Concerns\BelongsToStore;
use Illuminate\Database\Eloquent\Model;

class AnalyticsEvent extends Model
{
    use BelongsToStore;

    protected $fillable = ['store_id', 'type', 'session_id', 'customer_id', 'client_event_id', 'payload'];

    protected function casts(): array
    {
        return ['payload' => 'array'];
    }
}
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class NavigationItem extends Model
{
    protected $fillable = ['navigation_menu_id', 'label', 'type', 'url', 'resource_id', 'position', 'parent_id'];

    public function menu(): BelongsTo
    {
        return $this->belongsTo(NavigationMenu::class, 'navigation_menu_id');
    }

    public function parent(): BelongsTo
    {
        return $this->belongsTo(self::class, 'parent_id');
    }
}
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class ThemeFile extends Model
{
    protected $fillable = ['theme_id', 'path', 'content'];

    public function theme(): BelongsTo
    {
        return $this->belongsTo(Theme::class);
    }
}
<?php

namespace App\Models;

use App\Enums\PaymentMethod;
use App\Enums\PaymentStatus;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class Payment extends Model
{
    protected $fillable = ['order_id', 'provider', 'provider_payment_id', 'method', 'status', 'amount', 'raw_json_encrypted'];

    protected $hidden = ['raw_json_encrypted'];

    protected function casts(): array
    {
        return ['method' => PaymentMethod::class, 'status' => PaymentStatus::class, 'raw_json_encrypted' => 'encrypted'];
    }

    public function order(): BelongsTo
    {
        return $this->belongsTo(Order::class);
    }
}
            $table->timestamps();
            $table->unique(['store_id', 'handle']);
            $table->index(['store_id', 'status']);
        });

        Schema::create('collection_products', function (Blueprint $table): void {
            $table->foreignId('collection_id')->constrained()->cascadeOnDelete();
            $table->foreignId('product_id')->constrained()->cascadeOnDelete();
            $table->unsignedInteger('position')->default(0);
            $table->primary(['collection_id', 'product_id']);
            $table->index('product_id');
            $table->index(['collection_id', 'position']);
        });

        Schema::create('product_media', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('product_id')->constrained()->cascadeOnDelete();
            $table->string('path');
            $table->string('url')->nullable();
            $table->string('alt_text')->nullable();
            $table->string('status')->default('ready');
            $table->unsignedInteger('position')->default(0);
            $table->json('metadata')->nullable();
            $table->timestamps();
            $table->index(['product_id', 'position']);
            $table->index('status');
        });

        Schema::create('themes', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->string('name');
            $table->string('status')->default('draft')->index();
            $table->string('version')->default('1.0.0');
            $table->json('settings')->nullable();
            $table->timestamps();
            $table->index(['store_id', 'status']);
        });

        Schema::create('theme_files', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('theme_id')->constrained()->cascadeOnDelete();
            $table->string('path');
            $table->longText('content')->nullable();
            $table->timestamps();
            $table->unique(['theme_id', 'path']);
        });

        Schema::create('theme_settings', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('theme_id')->constrained()->cascadeOnDelete();
            $table->string('key');
            $table->json('value')->nullable();
            $table->timestamps();
            $table->unique(['theme_id', 'key']);
        });

        Schema::create('pages', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->string('title');
            $table->string('handle');
            $table->longText('content')->nullable();
            $table->string('status')->default('draft')->index();
            $table->timestamp('published_at')->nullable();
            $table->timestamps();
            $table->unique(['store_id', 'handle']);
        });

        Schema::create('navigation_menus', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->string('name');
            $table->string('handle');
            $table->timestamps();
            $table->unique(['store_id', 'handle']);
        });

        Schema::create('navigation_items', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('navigation_menu_id')->constrained('navigation_menus')->cascadeOnDelete();
            $table->string('label');
            $table->string('type')->default('link');
            $table->string('url')->nullable();
            $table->unsignedBigInteger('resource_id')->nullable();
            $table->unsignedInteger('position')->default(0);
            $table->foreignId('parent_id')->nullable()->constrained('navigation_items')->nullOnDelete();
            $table->timestamps();
            $table->index(['navigation_menu_id', 'position']);
        });


exec

status: completed

input:
const r = await tools.exec_command({cmd: "rg -n -A45 -B5 \"Schema::create\\('analytics_events|Schema::create\\('payments|Schema::create\\('order_lines|Schema::create\\('webhook_deliveries\" database/migrations/2026_08_20_000000_create_shop_schema.php", workdir: "/Users/wesner/Herd/shop", max_output_tokens: 12000, yield_time_ms: 10000});
text(r.output);


type: search

query: Schema::create\('analytics_events|Schema::create\('payments|Schema::create\('order_lines|Schema::create\('webhook_deliveries

stdout:
409-            $table->index(['store_id', 'status']);
410-            $table->index(['store_id', 'financial_status']);
411-            $table->index(['store_id', 'fulfillment_status']);
412-        });
413-
414:        Schema::create('order_lines', function (Blueprint $table): void {
415-            $table->id();
416-            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
417-            $table->foreignId('product_id')->nullable()->constrained()->nullOnDelete();
418-            $table->foreignId('variant_id')->nullable()->constrained('product_variants')->nullOnDelete();
419-            $table->string('product_title');
420-            $table->string('variant_title')->nullable();
421-            $table->string('sku')->nullable();
422-            $table->unsignedInteger('quantity');
423-            $table->unsignedInteger('unit_price_amount');
424-            $table->unsignedInteger('line_subtotal_amount');
425-            $table->unsignedInteger('line_discount_amount')->default(0);
426-            $table->unsignedInteger('line_total_amount');
427-            $table->json('tax_lines_json')->nullable();
428-            $table->json('discount_allocations_json')->nullable();
429-            $table->timestamps();
430-            $table->index('order_id');
431-        });
432-
433:        Schema::create('payments', function (Blueprint $table): void {
434-            $table->id();
435-            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
436-            $table->string('provider')->default('mock');
437-            $table->string('provider_payment_id')->nullable();
438-            $table->string('method');
439-            $table->string('status')->default('pending')->index();
440-            $table->unsignedInteger('amount');
441-            $table->text('raw_json_encrypted')->nullable();
442-            $table->timestamps();
443-            $table->index(['provider', 'provider_payment_id']);
444-            $table->index('order_id');
445-        });
446-
447-        Schema::create('refunds', function (Blueprint $table): void {
448-            $table->id();
449-            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
450-            $table->foreignId('payment_id')->nullable()->constrained()->nullOnDelete();
451-            $table->unsignedInteger('amount');
452-            $table->string('status')->default('pending');
453-            $table->text('reason')->nullable();
454-            $table->boolean('restock')->default(false);
455-            $table->timestamps();
456-            $table->index(['order_id', 'status']);
457-        });
458-
459-        Schema::create('fulfillments', function (Blueprint $table): void {
460-            $table->id();
461-            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
462-            $table->string('status')->default('pending')->index();
463-            $table->string('tracking_company')->nullable();
464-            $table->string('tracking_number')->nullable();
465-            $table->text('tracking_url')->nullable();
466-            $table->timestamp('shipped_at')->nullable();
467-            $table->timestamp('delivered_at')->nullable();
468-            $table->timestamp('fulfilled_at')->nullable();
469-            $table->timestamps();
470-            $table->index(['tracking_company', 'tracking_number']);
471-        });
472-
473-        Schema::create('fulfillment_lines', function (Blueprint $table): void {
474-            $table->id();
475-            $table->foreignId('fulfillment_id')->constrained()->cascadeOnDelete();
476-            $table->foreignId('order_line_id')->constrained()->cascadeOnDelete();
477-            $table->unsignedInteger('quantity');
478-            $table->timestamps();
479-            $table->unique(['fulfillment_id', 'order_line_id']);
480-        });
481-
482:        Schema::create('analytics_events', function (Blueprint $table): void {
483-            $table->id();
484-            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
485-            $table->string('type');
486-            $table->string('session_id')->nullable()->index();
487-            $table->foreignId('customer_id')->nullable()->constrained()->nullOnDelete();
488-            $table->string('client_event_id')->nullable();
489-            $table->json('payload')->nullable();
490-            $table->timestamps();
491-            $table->unique(['store_id', 'client_event_id']);
492-            $table->index(['store_id', 'type']);
493-            $table->index(['store_id', 'created_at']);
494-        });
495-
496-        Schema::create('analytics_daily', function (Blueprint $table): void {
497-            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
498-            $table->date('date');
499-            $table->unsignedInteger('orders_count')->default(0);
500-            $table->unsignedInteger('revenue_amount')->default(0);
501-            $table->unsignedInteger('aov_amount')->default(0);
502-            $table->unsignedInteger('visits_count')->default(0);
503-            $table->unsignedInteger('add_to_cart_count')->default(0);
504-            $table->unsignedInteger('checkout_started_count')->default(0);
505-            $table->primary(['store_id', 'date']);
506-        });
507-
508-        Schema::create('apps', function (Blueprint $table): void {
509-            $table->id();
510-            $table->string('name');
511-            $table->string('slug')->unique();
512-            $table->text('description')->nullable();
513-            $table->string('status')->default('active');
514-            $table->json('scopes')->nullable();
515-            $table->timestamps();
516-        });
517-
518-        Schema::create('app_installations', function (Blueprint $table): void {
519-            $table->id();
520-            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
521-            $table->foreignId('app_id')->constrained()->cascadeOnDelete();
522-            $table->string('status')->default('active');
523-            $table->json('config')->nullable();
524-            $table->timestamps();
525-            $table->unique(['store_id', 'app_id']);
526-        });
527-
--
556-            $table->unsignedInteger('consecutive_failures')->default(0);
557-            $table->timestamps();
558-            $table->index(['store_id', 'event']);
559-        });
560-
561:        Schema::create('webhook_deliveries', function (Blueprint $table): void {
562-            $table->id();
563-            $table->foreignId('webhook_subscription_id')->constrained()->cascadeOnDelete();
564-            $table->string('event');
565-            $table->json('payload');
566-            $table->unsignedInteger('attempts')->default(0);
567-            $table->unsignedSmallInteger('response_status')->nullable();
568-            $table->text('response_body')->nullable();
569-            $table->timestamp('delivered_at')->nullable();
570-            $table->timestamp('next_attempt_at')->nullable();
571-            $table->timestamps();
572-        });
573-    }
574-
575-    public function down(): void
576-    {
577-        foreach ([
578-            'webhook_deliveries', 'webhook_subscriptions', 'oauth_tokens', 'oauth_clients',
579-            'app_installations', 'apps', 'analytics_daily', 'analytics_events',
580-            'fulfillment_lines', 'fulfillments', 'refunds', 'payments', 'order_lines', 'orders',
581-            'checkouts', 'cart_lines', 'carts', 'discounts', 'tax_settings', 'shipping_rates',
582-            'shipping_zones', 'search_queries', 'search_settings', 'navigation_items',
583-            'navigation_menus', 'pages', 'theme_settings', 'theme_files', 'themes',
584-            'product_media', 'collection_products', 'collections', 'inventory_items',
585-            'variant_option_values', 'product_variants', 'product_option_values', 'product_options',
586-            'products', 'customer_addresses', 'customers', 'store_settings', 'store_users',
587-            'store_domains', 'stores', 'organizations',
588-        ] as $table) {
589-            Schema::dropIfExists($table);
590-        }
591-
592-        Schema::table('users', function (Blueprint $table): void {
593-            $table->dropColumn(['status', 'last_login_at']);
594-        });
595-    }
596-};


text:
Script completed
Wall time 0.1 seconds
Output:

text:
409-            $table->index(['store_id', 'status']);
410-            $table->index(['store_id', 'financial_status']);
411-            $table->index(['store_id', 'fulfillment_status']);
412-        });
413-
414:        Schema::create('order_lines', function (Blueprint $table): void {
415-            $table->id();
416-            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
417-            $table->foreignId('product_id')->nullable()->constrained()->nullOnDelete();
418-            $table->foreignId('variant_id')->nullable()->constrained('product_variants')->nullOnDelete();
419-            $table->string('product_title');
420-            $table->string('variant_title')->nullable();
421-            $table->string('sku')->nullable();
422-            $table->unsignedInteger('quantity');
423-            $table->unsignedInteger('unit_price_amount');
424-            $table->unsignedInteger('line_subtotal_amount');
425-            $table->unsignedInteger('line_discount_amount')->default(0);
426-            $table->unsignedInteger('line_total_amount');
427-            $table->json('tax_lines_json')->nullable();
428-            $table->json('discount_allocations_json')->nullable();
429-            $table->timestamps();
430-            $table->index('order_id');
431-        });
432-
433:        Schema::create('payments', function (Blueprint $table): void {
434-            $table->id();
435-            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
436-            $table->string('provider')->default('mock');
437-            $table->string('provider_payment_id')->nullable();
438-            $table->string('method');
439-            $table->string('status')->default('pending')->index();
440-            $table->unsignedInteger('amount');
441-            $table->text('raw_json_encrypted')->nullable();
442-            $table->timestamps();
443-            $table->index(['provider', 'provider_payment_id']);
444-            $table->index('order_id');
445-        });
446-
447-        Schema::create('refunds', function (Blueprint $table): void {
448-            $table->id();
449-            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
450-            $table->foreignId('payment_id')->nullable()->constrained()->nullOnDelete();
451-            $table->unsignedInteger('amount');
452-            $table->string('status')->default('pending');
453-            $table->text('reason')->nullable();
454-            $table->boolean('restock')->default(false);
455-            $table->timestamps();
456-            $table->index(['order_id', 'status']);
457-        });
458-
459-        Schema::create('fulfillments', function (Blueprint $table): void {
460-            $table->id();
461-            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
462-            $table->string('status')->default('pending')->index();
463-            $table->string('tracking_company')->nullable();
464-            $table->string('tracking_number')->nullable();
465-            $table->text('tracking_url')->nullable();
466-            $table->timestamp('shipped_at')->nullable();
467-            $table->timestamp('delivered_at')->nullable();
468-            $table->timestamp('fulfilled_at')->nullable();
469-            $table->timestamps();
470-            $table->index(['tracking_company', 'tracking_number']);
471-        });
472-
473-        Schema::create('fulfillment_lines', function (Blueprint $table): void {
474-            $table->id();
475-            $table->foreignId('fulfillment_id')->constrained()->cascadeOnDelete();
476-            $table->foreignId('order_line_id')->constrained()->cascadeOnDelete();
477-            $table->unsignedInteger('quantity');
478-            $table->timestamps();
479-            $table->unique(['fulfillment_id', 'order_line_id']);
480-        });
481-
482:        Schema::create('analytics_events', function (Blueprint $table): void {
483-            $table->id();
484-            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
485-            $table->string('type');
486-            $table->string('session_id')->nullable()->index();
487-            $table->foreignId('customer_id')->nullable()->constrained()->nullOnDelete();
488-            $table->string('client_event_id')->nullable();
489-            $table->json('payload')->nullable();
490-            $table->timestamps();
491-            $table->unique(['store_id', 'client_event_id']);
492-            $table->index(['store_id', 'type']);
493-            $table->index(['store_id', 'created_at']);
494-        });
495-
496-        Schema::create('analytics_daily', function (Blueprint $table): void {
497-            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
498-            $table->date('date');
499-            $table->unsignedInteger('orders_count')->default(0);
500-            $table->unsignedInteger('revenue_amount')->default(0);
501-            $table->unsignedInteger('aov_amount')->default(0);
502-            $table->unsignedInteger('visits_count')->default(0);
503-            $table->unsignedInteger('add_to_cart_count')->default(0);
504-            $table->unsignedInteger('checkout_started_count')->default(0);
505-            $table->primary(['store_id', 'date']);
506-        });
507-
508-        Schema::create('apps', function (Blueprint $table): void {
509-            $table->id();
510-            $table->string('name');
511-            $table->string('slug')->unique();
512-            $table->text('description')->nullable();
513-            $table->string('status')->default('active');
514-            $table->json('scopes')->nullable();
515-            $table->timestamps();
516-        });
517-
518-        Schema::create('app_installations', function (Blueprint $table): void {
519-            $table->id();
520-            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
521-            $table->foreignId('app_id')->constrained()->cascadeOnDelete();
522-            $table->string('status')->default('active');
523-            $table->json('config')->nullable();
524-            $table->timestamps();
525-            $table->unique(['store_id', 'app_id']);
526-        });
527-
--
556-            $table->unsignedInteger('consecutive_failures')->default(0);
557-            $table->timestamps();
558-            $table->index(['store_id', 'event']);
559-        });
560-
561:        Schema::create('webhook_deliveries', function (Blueprint $table): void {
562-            $table->id();
563-            $table->foreignId('webhook_subscription_id')->constrained()->cascadeOnDelete();
564-            $table->string('event');
565-            $table->json('payload');
566-            $table->unsignedInteger('attempts')->default(0);
567-            $table->unsignedSmallInteger('response_status')->nullable();
568-            $table->text('response_body')->nullable();
569-            $table->timestamp('delivered_at')->nullable();
570-            $table->timestamp('next_attempt_at')->nullable();
571-            $table->timestamps();
572-        });
573-    }
574-
575-    public function down(): void
576-    {
577-        foreach ([
578-            'webhook_deliveries', 'webhook_subscriptions', 'oauth_tokens', 'oauth_clients',
579-            'app_installations', 'apps', 'analytics_daily', 'analytics_events',
580-            'fulfillment_lines', 'fulfillments', 'refunds', 'payments', 'order_lines', 'orders',
581-            'checkouts', 'cart_lines', 'carts', 'discounts', 'tax_settings', 'shipping_rates',
582-            'shipping_zones', 'search_queries', 'search_settings', 'navigation_items',
583-            'navigation_menus', 'pages', 'theme_settings', 'theme_files', 'themes',
584-            'product_media', 'collection_products', 'collections', 'inventory_items',
585-            'variant_option_values', 'product_variants', 'product_option_values', 'product_options',
586-            'products', 'customer_addresses', 'customers', 'store_settings', 'store_users',
587-            'store_domains', 'stores', 'organizations',
588-        ] as $table) {
589-            Schema::dropIfExists($table);
590-        }
591-
592-        Schema::table('users', function (Blueprint $table): void {
593-            $table->dropColumn(['status', 'last_login_at']);
594-        });
595-    }
596-};


status: completed

input:
const r = await tools.exec_command({cmd: "sed -n '1,180p' app/Models/WebhookDelivery.php; sed -n '1,180p' app/Models/WebhookSubscription.php; sed -n '1,140p' app/Services/AnalyticsService.php; rg -n \"WebhookDelivery::|WebhookDelivery\" app/Services app/Jobs", workdir: "/Users/wesner/Herd/shop", max_output_tokens: 10000, yield_time_ms: 10000});
text(r.output);


command: sed -n '1,180p' app/Models/WebhookDelivery.php; sed -n '1,180p' app/Models/WebhookSubscription.php; sed -n '1,140p' app/Services/AnalyticsService.php; rg -n "WebhookDelivery::|WebhookDelivery" app/Services app/Jobs

AnalyticsService.php

type: search

cmd: rg -n 'WebhookDelivery::|WebhookDelivery' app/Services app/Jobs

query: WebhookDelivery::|WebhookDelivery

path: Services

stdout:
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class WebhookDelivery extends Model
{
    protected $fillable = ['webhook_subscription_id', 'event', 'payload', 'status', 'attempts', 'response_status', 'response_body', 'delivered_at', 'next_attempt_at'];

    protected function casts(): array
    {
        return ['payload' => 'array', 'delivered_at' => 'datetime', 'next_attempt_at' => 'datetime'];
    }

    public function subscription(): BelongsTo
    {
        return $this->belongsTo(WebhookSubscription::class, 'webhook_subscription_id');
    }
}
<?php

namespace App\Models;

use App\Models\Concerns\BelongsToStore;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class WebhookSubscription extends Model
{
    use BelongsToStore;

    protected $fillable = ['store_id', 'event', 'target_url', 'secret_encrypted', 'status', 'consecutive_failures'];

    protected $hidden = ['secret_encrypted'];

    protected function casts(): array
    {
        return ['secret_encrypted' => 'encrypted'];
    }

    public function deliveries(): HasMany
    {
        return $this->hasMany(WebhookDelivery::class);
    }
}
<?php

namespace App\Services;

use App\Models\AnalyticsEvent;
use App\Models\Store;
use Illuminate\Support\Collection;

class AnalyticsService
{
    /** @var array<int, string> */
    private array $eventTypes = ['page_view', 'product_view', 'add_to_cart', 'remove_from_cart', 'checkout_started', 'checkout_completed', 'search'];

    public function track(Store $store, string $type, array $properties = [], ?string $sessionId = null, ?int $customerId = null, ?string $clientEventId = null): AnalyticsEvent
    {
        if (! in_array($type, $this->eventTypes, true)) {
            throw new \InvalidArgumentException('Unsupported analytics event type.');
        }

        if ($clientEventId !== null) {
            $existing = AnalyticsEvent::withoutGlobalScopes()->where('store_id', $store->getKey())->where('client_event_id', $clientEventId)->first();

            if ($existing !== null) {
                return $existing;
            }
        }

        return AnalyticsEvent::withoutGlobalScopes()->create(['store_id' => $store->getKey(), 'type' => $type, 'session_id' => $sessionId, 'customer_id' => $customerId, 'client_event_id' => $clientEventId, 'payload' => $properties]);
    }

    public function getDailyMetrics(Store $store, string $startDate, string $endDate): Collection
    {
        return \App\Models\AnalyticsDaily::withoutGlobalScopes()->where('store_id', $store->getKey())->whereBetween('date', [$startDate, $endDate])->orderBy('date')->get();
    }
}
app/Jobs/DeliverWebhook.php:5:use App\Models\WebhookDelivery;
app/Jobs/DeliverWebhook.php:20:    public function __construct(public WebhookDelivery $delivery) {}


text:
Script completed
Wall time 0.1 seconds
Output:

text:
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class WebhookDelivery extends Model
{
    protected $fillable = ['webhook_subscription_id', 'event', 'payload', 'status', 'attempts', 'response_status', 'response_body', 'delivered_at', 'next_attempt_at'];

    protected function casts(): array
    {
        return ['payload' => 'array', 'delivered_at' => 'datetime', 'next_attempt_at' => 'datetime'];
    }

    public function subscription(): BelongsTo
    {
        return $this->belongsTo(WebhookSubscription::class, 'webhook_subscription_id');
    }
}
<?php

namespace App\Models;

use App\Models\Concerns\BelongsToStore;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class WebhookSubscription extends Model
{
    use BelongsToStore;

    protected $fillable = ['store_id', 'event', 'target_url', 'secret_encrypted', 'status', 'consecutive_failures'];

    protected $hidden = ['secret_encrypted'];

    protected function casts(): array
    {
        return ['secret_encrypted' => 'encrypted'];
    }

    public function deliveries(): HasMany
    {
        return $this->hasMany(WebhookDelivery::class);
    }
}
<?php

namespace App\Services;

use App\Models\AnalyticsEvent;
use App\Models\Store;
use Illuminate\Support\Collection;

class AnalyticsService
{
    /** @var array<int, string> */
    private array $eventTypes = ['page_view', 'product_view', 'add_to_cart', 'remove_from_cart', 'checkout_started', 'checkout_completed', 'search'];

    public function track(Store $store, string $type, array $properties = [], ?string $sessionId = null, ?int $customerId = null, ?string $clientEventId = null): AnalyticsEvent
    {
        if (! in_array($type, $this->eventTypes, true)) {
            throw new \InvalidArgumentException('Unsupported analytics event type.');
        }

        if ($clientEventId !== null) {
            $existing = AnalyticsEvent::withoutGlobalScopes()->where('store_id', $store->getKey())->where('client_event_id', $clientEventId)->first();

            if ($existing !== null) {
                return $existing;
            }
        }

        return AnalyticsEvent::withoutGlobalScopes()->create(['store_id' => $store->getKey(), 'type' => $type, 'session_id' => $sessionId, 'customer_id' => $customerId, 'client_event_id' => $clientEventId, 'payload' => $properties]);
    }

    public function getDailyMetrics(Store $store, string $startDate, string $endDate): Collection
    {
        return \App\Models\AnalyticsDaily::withoutGlobalScopes()->where('store_id', $store->getKey())->whereBetween('date', [$startDate, $endDate])->orderBy('date')->get();
    }
}
app/Jobs/DeliverWebhook.php:5:use App\Models\WebhookDelivery;
app/Jobs/DeliverWebhook.php:20:    public function __construct(public WebhookDelivery $delivery) {}


type: AgentMessage

type: Text

text: One last schema-contract pass is worthwhile because the audit identified aliases that are safe to add without changing the existing Laravel-facing fields. I’m adding compatibility columns and synchronizing model snapshots for pages, media, order lines, payments, analytics, search settings, navigation, and webhook records; this keeps current behavior intact while matching the spec’s names.

phase: commentary


type: message

role: assistant


exec

status: completed

input:
const r = await tools.exec_command({cmd: "php artisan make:migration add_remaining_schema_contract_columns --no-interaction", workdir: "/Users/wesner/Herd/shop", max_output_tokens: 4000, yield_time_ms: 10000});
text(r.output);


command: php artisan make:migration add_remaining_schema_contract_columns --no-interaction

type: unknown

stdout: INFO  Migration [database/migrations/2026_08_20_223045_add_remaining_schema_contract_columns.php] created successfully.


text:
Script completed
Wall time 0.2 seconds
Output:

text: INFO  Migration [database/migrations/2026_08_20_223045_add_remaining_schema_contract_columns.php] created successfully.


status: completed

input:
const patch = "*** Begin Patch\n*** Update File: /Users/wesner/Herd/shop/database/migrations/2026_08_20_223045_add_remaining_schema_contract_columns.php\n@@\n return new class extends Migration\n {\n     public function up(): void\n     {\n-        //\n+        Schema::table('users', function (Blueprint $table): void {\n+            if (! Schema::hasColumn('users', 'password_hash')) {\n+                $table->text('password_hash')->nullable();\n+            }\n+        });\n+        Schema::table('customer_password_reset_tokens', function (Blueprint $table): void {\n+            if (! Schema::hasColumn('customer_password_reset_tokens', 'store_id')) {\n+                $table->foreignId('store_id')->nullable()->constrained('stores')->nullOnDelete();\n+            }\n+        });\n+        Schema::table('product_media', function (Blueprint $table): void {\n+            if (! Schema::hasColumn('product_media', 'width')) {\n+                $table->unsignedInteger('width')->nullable();\n+            }\n+            if (! Schema::hasColumn('product_media', 'height')) {\n+                $table->unsignedInteger('height')->nullable();\n+            }\n+        });\n+        Schema::table('theme_files', function (Blueprint $table): void {\n+            if (! Schema::hasColumn('theme_files', 'storage_key')) {\n+                $table->string('storage_key')->nullable();\n+            }\n+            if (! Schema::hasColumn('theme_files', 'sha256')) {\n+                $table->string('sha256')->nullable();\n+            }\n+            if (! Schema::hasColumn('theme_files', 'byte_size')) {\n+                $table->unsignedBigInteger('byte_size')->default(0);\n+            }\n+        });\n+        Schema::table('pages', function (Blueprint $table): void {\n+            if (! Schema::hasColumn('pages', 'body_html')) {\n+                $table->longText('body_html')->nullable();\n+            }\n+        });\n+        Schema::table('navigation_items', function (Blueprint $table): void {\n+            if (! Schema::hasColumn('navigation_items', 'menu_id')) {\n+                $table->unsignedBigInteger('menu_id')->nullable()->index();\n+            }\n+        });\n+        Schema::table('search_settings', function (Blueprint $table): void {\n+            if (! Schema::hasColumn('search_settings', 'synonyms_json')) {\n+                $table->json('synonyms_json')->nullable();\n+            }\n+            if (! Schema::hasColumn('search_settings', 'stop_words_json')) {\n+                $table->json('stop_words_json')->nullable();\n+            }\n+        });\n+        Schema::table('order_lines', function (Blueprint $table): void {\n+            if (! Schema::hasColumn('order_lines', 'total_amount')) {\n+                $table->unsignedInteger('total_amount')->nullable();\n+            }\n+        });\n+        Schema::table('payments', function (Blueprint $table): void {\n+            if (! Schema::hasColumn('payments', 'currency')) {\n+                $table->string('currency', 3)->default('USD');\n+            }\n+        });\n+        Schema::table('analytics_events', function (Blueprint $table): void {\n+            if (! Schema::hasColumn('analytics_events', 'properties_json')) {\n+                $table->json('properties_json')->nullable();\n+            }\n+            if (! Schema::hasColumn('analytics_events', 'occurred_at')) {\n+                $table->timestamp('occurred_at')->nullable();\n+            }\n+        });\n+        Schema::table('analytics_daily', function (Blueprint $table): void {\n+            if (! Schema::hasColumn('analytics_daily', 'checkout_completed_count')) {\n+                $table->unsignedInteger('checkout_completed_count')->default(0);\n+            }\n+        });\n+        Schema::table('webhook_subscriptions', function (Blueprint $table): void {\n+            if (! Schema::hasColumn('webhook_subscriptions', 'event_type')) {\n+                $table->string('event_type')->nullable()->index();\n+            }\n+            if (! Schema::hasColumn('webhook_subscriptions', 'app_installation_id')) {\n+                $table->unsignedBigInteger('app_installation_id')->nullable()->index();\n+            }\n+        });\n+        Schema::table('webhook_deliveries', function (Blueprint $table): void {\n+            if (! Schema::hasColumn('webhook_deliveries', 'subscription_id')) {\n+                $table->unsignedBigInteger('subscription_id')->nullable()->index();\n+            }\n+            if (! Schema::hasColumn('webhook_deliveries', 'event_id')) {\n+                $table->string('event_id')->nullable()->index();\n+            }\n+            if (! Schema::hasColumn('webhook_deliveries', 'attempt_count')) {\n+                $table->unsignedInteger('attempt_count')->default(0);\n+            }\n+            if (! Schema::hasColumn('webhook_deliveries', 'last_attempt_at')) {\n+                $table->timestamp('last_attempt_at')->nullable();\n+            }\n+            if (! Schema::hasColumn('webhook_deliveries', 'response_code')) {\n+                $table->unsignedSmallInteger('response_code')->nullable();\n+            }\n+            if (! Schema::hasColumn('webhook_deliveries', 'response_body_snippet')) {\n+                $table->text('response_body_snippet')->nullable();\n+            }\n+        });\n     }\n \n     public function down(): void\n     {\n-        //\n+        foreach (['password_hash' => 'users', 'store_id' => 'customer_password_reset_tokens', 'width' => 'product_media', 'height' => 'product_media', 'storage_key' => 'theme_files', 'sha256' => 'theme_files', 'byte_size' => 'theme_files', 'body_html' => 'pages', 'menu_id' => 'navigation_items', 'synonyms_json' => 'search_settings', 'stop_words_json' => 'search_settings', 'total_amount' => 'order_lines', 'currency' => 'payments', 'properties_json' => 'analytics_events', 'occurred_at' => 'analytics_events', 'checkout_completed_count' => 'analytics_daily', 'event_type' => 'webhook_subscriptions', 'app_installation_id' => 'webhook_subscriptions', 'subscription_id' => 'webhook_deliveries', 'event_id' => 'webhook_deliveries', 'attempt_count' => 'webhook_deliveries', 'last_attempt_at' => 'webhook_deliveries', 'response_code' => 'webhook_deliveries', 'response_body_snippet' => 'webhook_deliveries'] as $column => $tableName) {\n+            if (Schema::hasColumn($tableName, $column)) {\n+                Schema::table($tableName, fn (Blueprint $table): \\Illuminate\\Support\\Fluent => $table->dropColumn($column));\n+            }\n+        }\n     }\n };\n*** End Patch";
text(await tools.apply_patch(patch));


text:
Script failed
Wall time 0.0 seconds
Output:

text:
Script error:
apply_patch verification failed: Failed to find expected lines in /Users/wesner/Herd/shop/database/migrations/2026_08_20_223045_add_remaining_schema_contract_columns.php:
return new class extends Migration
{
    public function up(): void
    {
        //
    }

    public function down(): void
    {
        //
    }
};


status: completed

input:
const r = await tools.exec_command({cmd: "sed -n '1,120p' database/migrations/2026_08_20_223045_add_remaining_schema_contract_columns.php", workdir: "/Users/wesner/Herd/shop", max_output_tokens: 3000, yield_time_ms: 10000});
text(r.output);


command: sed -n '1,120p' database/migrations/2026_08_20_223045_add_remaining_schema_contract_columns.php

stdout:
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        //
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        //
    }
};


text:
Script completed
Wall time 0.1 seconds
Output:

text:
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        //
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        //
    }
};