type: text

text:
Perfect! I have now read the complete database schema file. Let me provide you with a comprehensive summary of ALL table definitions.

## Database Schema Comprehensive Summary

### EPIC 1 - Foundation and Multi-Store Tenancy

**organizations**
- `id` (INTEGER, PK, autoincrement)
- `name` (TEXT, NOT NULL)
- `billing_email` (TEXT, NOT NULL)
- `created_at` (TEXT, nullable)
- `updated_at` (TEXT, nullable)
- Indexes: `idx_organizations_billing_email` on `billing_email`

**stores**
- `id` (INTEGER, PK, autoincrement)
- `organization_id` (INTEGER, NOT NULL, FK -> organizations(id) ON DELETE CASCADE)
- `name` (TEXT, NOT NULL)
- `handle` (TEXT, NOT NULL, UNIQUE)
- `status` (TEXT, NOT NULL, DEFAULT 'active', CHECK IN ('active', 'suspended'))
- `default_currency` (TEXT, NOT NULL, DEFAULT 'USD')
- `default_locale` (TEXT, NOT NULL, DEFAULT 'en')
- `timezone` (TEXT, NOT NULL, DEFAULT 'UTC')
- `created_at` (TEXT, nullable)
- `updated_at` (TEXT, nullable)
- Indexes: `idx_stores_handle` UNIQUE, `idx_stores_organization_id`, `idx_stores_status`

**store_domains**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `hostname` (TEXT, NOT NULL, UNIQUE)
- `type` (TEXT, NOT NULL, DEFAULT 'storefront', CHECK IN ('storefront', 'admin', 'api'))
- `is_primary` (INTEGER, NOT NULL, DEFAULT 0)
- `tls_mode` (TEXT, NOT NULL, DEFAULT 'managed', CHECK IN ('managed', 'bring_your_own'))
- `created_at` (TEXT, nullable)
- Indexes: `idx_store_domains_hostname` UNIQUE, `idx_store_domains_store_id`, `idx_store_domains_store_primary`

**users**
- `id` (INTEGER, PK, autoincrement)
- `email` (TEXT, NOT NULL, UNIQUE)
- `password_hash` (TEXT, NOT NULL)
- `name` (TEXT, NOT NULL)
- `status` (TEXT, NOT NULL, DEFAULT 'active', CHECK IN ('active', 'disabled'))
- `email_verified_at` (TEXT, nullable)
- `last_login_at` (TEXT, nullable)
- `two_factor_secret` (TEXT, nullable)
- `two_factor_recovery_codes` (TEXT, nullable)
- `two_factor_confirmed_at` (TEXT, nullable)
- `remember_token` (TEXT, nullable)
- `created_at` (TEXT, nullable)
- `updated_at` (TEXT, nullable)
- Indexes: `idx_users_email` UNIQUE, `idx_users_status`

**store_users** (Composite PK)
- `store_id` (INTEGER, PK, FK -> stores(id) ON DELETE CASCADE)
- `user_id` (INTEGER, PK, FK -> users(id) ON DELETE CASCADE)
- `role` (TEXT, NOT NULL, DEFAULT 'staff', CHECK IN ('owner', 'admin', 'staff', 'support'))
- `created_at` (TEXT, nullable)
- Indexes: `idx_store_users_user_id`, `idx_store_users_role`

**store_settings**
- `store_id` (INTEGER, PK, FK -> stores(id) ON DELETE CASCADE)
- `settings_json` (TEXT, NOT NULL, DEFAULT '{}')
- `updated_at` (TEXT, nullable)

---

### EPIC 2 - Catalog, Collections, Inventory, and Media

**products**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `title` (TEXT, NOT NULL)
- `handle` (TEXT, NOT NULL, UNIQUE per store)
- `status` (TEXT, NOT NULL, DEFAULT 'draft', CHECK IN ('draft', 'active', 'archived'))
- `description_html` (TEXT, nullable)
- `vendor` (TEXT, nullable)
- `product_type` (TEXT, nullable)
- `tags` (TEXT, NOT NULL, DEFAULT '[]')
- `published_at` (TEXT, nullable)
- `created_at` (TEXT, nullable)
- `updated_at` (TEXT, nullable)
- Indexes: `idx_products_store_handle` UNIQUE, `idx_products_store_id`, `idx_products_store_status`, `idx_products_published_at`, `idx_products_vendor`, `idx_products_product_type`

**product_options**
- `id` (INTEGER, PK, autoincrement)
- `product_id` (INTEGER, NOT NULL, FK -> products(id) ON DELETE CASCADE)
- `name` (TEXT, NOT NULL)
- `position` (INTEGER, NOT NULL, DEFAULT 0, UNIQUE per product)
- Indexes: `idx_product_options_product_id`, `idx_product_options_product_position` UNIQUE

**product_option_values**
- `id` (INTEGER, PK, autoincrement)
- `product_option_id` (INTEGER, NOT NULL, FK -> product_options(id) ON DELETE CASCADE)
- `value` (TEXT, NOT NULL)
- `position` (INTEGER, NOT NULL, DEFAULT 0, UNIQUE per option)
- Indexes: `idx_product_option_values_option_id`, `idx_product_option_values_option_position` UNIQUE

**product_variants**
- `id` (INTEGER, PK, autoincrement)
- `product_id` (INTEGER, NOT NULL, FK -> products(id) ON DELETE CASCADE)
- `sku` (TEXT, nullable)
- `barcode` (TEXT, nullable)
- `price_amount` (INTEGER, NOT NULL, DEFAULT 0)
- `compare_at_amount` (INTEGER, nullable)
- `currency` (TEXT, NOT NULL, DEFAULT 'USD')
- `weight_g` (INTEGER, nullable)
- `requires_shipping` (INTEGER, NOT NULL, DEFAULT 1)
- `is_default` (INTEGER, NOT NULL, DEFAULT 0)
- `position` (INTEGER, NOT NULL, DEFAULT 0)
- `status` (TEXT, NOT NULL, DEFAULT 'active', CHECK IN ('active', 'archived'))
- `created_at` (TEXT, nullable)
- `updated_at` (TEXT, nullable)
- Indexes: `idx_product_variants_product_id`, `idx_product_variants_sku`, `idx_product_variants_barcode`, `idx_product_variants_product_position`, `idx_product_variants_product_default`

**variant_option_values** (Composite PK)
- `variant_id` (INTEGER, PK, FK -> product_variants(id) ON DELETE CASCADE)
- `product_option_value_id` (INTEGER, PK, FK -> product_option_values(id) ON DELETE CASCADE)
- Indexes: `idx_variant_option_values_value_id`

**inventory_items**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `variant_id` (INTEGER, NOT NULL, UNIQUE, FK -> product_variants(id) ON DELETE CASCADE)
- `quantity_on_hand` (INTEGER, NOT NULL, DEFAULT 0)
- `quantity_reserved` (INTEGER, NOT NULL, DEFAULT 0)
- `policy` (TEXT, NOT NULL, DEFAULT 'deny', CHECK IN ('deny', 'continue'))
- Indexes: `idx_inventory_items_variant_id` UNIQUE, `idx_inventory_items_store_id`

**collections**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `title` (TEXT, NOT NULL)
- `handle` (TEXT, NOT NULL, UNIQUE per store)
- `description_html` (TEXT, nullable)
- `type` (TEXT, NOT NULL, DEFAULT 'manual', CHECK IN ('manual', 'automated'))
- `status` (TEXT, NOT NULL, DEFAULT 'active', CHECK IN ('draft', 'active', 'archived'))
- `created_at` (TEXT, nullable)
- `updated_at` (TEXT, nullable)
- Indexes: `idx_collections_store_handle` UNIQUE, `idx_collections_store_id`, `idx_collections_store_status`

**collection_products** (Composite PK)
- `collection_id` (INTEGER, PK, FK -> collections(id) ON DELETE CASCADE)
- `product_id` (INTEGER, PK, FK -> products(id) ON DELETE CASCADE)
- `position` (INTEGER, NOT NULL, DEFAULT 0)
- Indexes: `idx_collection_products_product_id`, `idx_collection_products_position`

**product_media**
- `id` (INTEGER, PK, autoincrement)
- `product_id` (INTEGER, NOT NULL, FK -> products(id) ON DELETE CASCADE)
- `type` (TEXT, NOT NULL, DEFAULT 'image', CHECK IN ('image', 'video'))
- `storage_key` (TEXT, NOT NULL)
- `alt_text` (TEXT, nullable)
- `width` (INTEGER, nullable)
- `height` (INTEGER, nullable)
- `mime_type` (TEXT, nullable)
- `byte_size` (INTEGER, nullable)
- `position` (INTEGER, NOT NULL, DEFAULT 0)
- `status` (TEXT, NOT NULL, DEFAULT 'processing', CHECK IN ('processing', 'ready', 'failed'))
- `created_at` (TEXT, nullable)
- Indexes: `idx_product_media_product_id`, `idx_product_media_product_position`, `idx_product_media_status`

---

### EPIC 3 - Themes, Storefront, and Localization

**themes**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `name` (TEXT, NOT NULL)
- `version` (TEXT, nullable)
- `status` (TEXT, NOT NULL, DEFAULT 'draft', CHECK IN ('draft', 'published'))
- `published_at` (TEXT, nullable)
- `created_at` (TEXT, nullable)
- `updated_at` (TEXT, nullable)
- Indexes: `idx_themes_store_id`, `idx_themes_store_status`

**theme_files**
- `id` (INTEGER, PK, autoincrement)
- `theme_id` (INTEGER, NOT NULL, FK -> themes(id) ON DELETE CASCADE)
- `path` (TEXT, NOT NULL, UNIQUE per theme)
- `storage_key` (TEXT, NOT NULL)
- `sha256` (TEXT, NOT NULL)
- `byte_size` (INTEGER, NOT NULL, DEFAULT 0)
- Indexes: `idx_theme_files_theme_path` UNIQUE, `idx_theme_files_theme_id`

**theme_settings**
- `theme_id` (INTEGER, PK, FK -> themes(id) ON DELETE CASCADE)
- `settings_json` (TEXT, NOT NULL, DEFAULT '{}')
- `updated_at` (TEXT, nullable)

**pages**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `title` (TEXT, NOT NULL)
- `handle` (TEXT, NOT NULL, UNIQUE per store)
- `body_html` (TEXT, nullable)
- `status` (TEXT, NOT NULL, DEFAULT 'draft', CHECK IN ('draft', 'published', 'archived'))
- `published_at` (TEXT, nullable)
- `created_at` (TEXT, nullable)
- `updated_at` (TEXT, nullable)
- Indexes: `idx_pages_store_handle` UNIQUE, `idx_pages_store_id`, `idx_pages_store_status`

**navigation_menus**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `handle` (TEXT, NOT NULL, UNIQUE per store)
- `title` (TEXT, NOT NULL)
- `created_at` (TEXT, nullable)
- `updated_at` (TEXT, nullable)
- Indexes: `idx_navigation_menus_store_handle` UNIQUE, `idx_navigation_menus_store_id`

**navigation_items**
- `id` (INTEGER, PK, autoincrement)
- `menu_id` (INTEGER, NOT NULL, FK -> navigation_menus(id) ON DELETE CASCADE)
- `type` (TEXT, NOT NULL, DEFAULT 'link', CHECK IN ('link', 'page', 'collection', 'product'))
- `label` (TEXT, NOT NULL)
- `url` (TEXT, nullable)
- `resource_id` (INTEGER, nullable)
- `position` (INTEGER, NOT NULL, DEFAULT 0)
- Indexes: `idx_navigation_items_menu_id`, `idx_navigation_items_menu_position`

---

### EPIC 4 - Search

**search_settings**
- `store_id` (INTEGER, PK, FK -> stores(id) ON DELETE CASCADE)
- `synonyms_json` (TEXT, NOT NULL, DEFAULT '[]')
- `stop_words_json` (TEXT, NOT NULL, DEFAULT '[]')
- `updated_at` (TEXT, nullable)

**search_queries**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `query` (TEXT, NOT NULL)
- `filters_json` (TEXT, nullable)
- `results_count` (INTEGER, NOT NULL, DEFAULT 0)
- `created_at` (TEXT, nullable)
- Indexes: `idx_search_queries_store_id`, `idx_search_queries_store_created`, `idx_search_queries_store_query`

---

### EPIC 5 - Cart, Checkout, Discounts, Shipping, and Taxes

**carts**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `customer_id` (INTEGER, nullable, FK -> customers(id) ON DELETE SET NULL)
- `currency` (TEXT, NOT NULL, DEFAULT 'USD')
- `cart_version` (INTEGER, NOT NULL, DEFAULT 1)
- `status` (TEXT, NOT NULL, DEFAULT 'active', CHECK IN ('active', 'converted', 'abandoned'))
- `created_at` (TEXT, nullable)
- `updated_at` (TEXT, nullable)
- Indexes: `idx_carts_store_id`, `idx_carts_customer_id`, `idx_carts_store_status`

**cart_lines**
- `id` (INTEGER, PK, autoincrement)
- `cart_id` (INTEGER, NOT NULL, FK -> carts(id) ON DELETE CASCADE)
- `variant_id` (INTEGER, NOT NULL, FK -> product_variants(id) ON DELETE CASCADE)
- `quantity` (INTEGER, NOT NULL, DEFAULT 1)
- `unit_price_amount` (INTEGER, NOT NULL, DEFAULT 0)
- `line_subtotal_amount` (INTEGER, NOT NULL, DEFAULT 0)
- `line_discount_amount` (INTEGER, NOT NULL, DEFAULT 0)
- `line_total_amount` (INTEGER, NOT NULL, DEFAULT 0)
- Indexes: `idx_cart_lines_cart_id`, `idx_cart_lines_cart_variant` UNIQUE

**checkouts**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `cart_id` (INTEGER, NOT NULL, FK -> carts(id) ON DELETE CASCADE)
- `customer_id` (INTEGER, nullable, FK -> customers(id) ON DELETE SET NULL)
- `status` (TEXT, NOT NULL, DEFAULT 'started', CHECK IN ('started', 'addressed', 'shipping_selected', 'payment_selected', 'completed', 'expired'))
- `payment_method` (TEXT, nullable, CHECK IN ('credit_card', 'paypal', 'bank_transfer'))
- `email` (TEXT, nullable)
- `shipping_address_json` (TEXT, nullable)
- `billing_address_json` (TEXT, nullable)
- `shipping_method_id` (INTEGER, nullable)
- `discount_code` (TEXT, nullable)
- `tax_provider_snapshot_json` (TEXT, nullable)
- `totals_json` (TEXT, nullable)
- `expires_at` (TEXT, nullable)
- `created_at` (TEXT, nullable)
- `updated_at` (TEXT, nullable)
- Indexes: `idx_checkouts_store_id`, `idx_checkouts_cart_id`, `idx_checkouts_customer_id`, `idx_checkouts_status`, `idx_checkouts_expires_at`

**shipping_zones**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `name` (TEXT, NOT NULL)
- `countries_json` (TEXT, NOT NULL, DEFAULT '[]')
- `regions_json` (TEXT, NOT NULL, DEFAULT '[]')
- Indexes: `idx_shipping_zones_store_id`

**shipping_rates**
- `id` (INTEGER, PK, autoincrement)
- `zone_id` (INTEGER, NOT NULL, FK -> shipping_zones(id) ON DELETE CASCADE)
- `name` (TEXT, NOT NULL)
- `type` (TEXT, NOT NULL, DEFAULT 'flat', CHECK IN ('flat', 'weight', 'price', 'carrier'))
- `config_json` (TEXT, NOT NULL, DEFAULT '{}')
- `is_active` (INTEGER, NOT NULL, DEFAULT 1)
- Indexes: `idx_shipping_rates_zone_id`, `idx_shipping_rates_zone_active`

**tax_settings**
- `store_id` (INTEGER, PK, FK -> stores(id) ON DELETE CASCADE)
- `mode` (TEXT, NOT NULL, DEFAULT 'manual', CHECK IN ('manual', 'provider'))
- `provider` (TEXT, NOT NULL, DEFAULT 'none', CHECK IN ('stripe_tax', 'none'))
- `prices_include_tax` (INTEGER, NOT NULL, DEFAULT 0)
- `config_json` (TEXT, NOT NULL, DEFAULT '{}')

**discounts**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `type` (TEXT, NOT NULL, DEFAULT 'code', CHECK IN ('code', 'automatic'))
- `code` (TEXT, nullable, UNIQUE per store)
- `value_type` (TEXT, NOT NULL, CHECK IN ('fixed', 'percent', 'free_shipping'))
- `value_amount` (INTEGER, NOT NULL, DEFAULT 0)
- `starts_at` (TEXT, NOT NULL)
- `ends_at` (TEXT, nullable)
- `usage_limit` (INTEGER, nullable)
- `usage_count` (INTEGER, NOT NULL, DEFAULT 0)
- `rules_json` (TEXT, NOT NULL, DEFAULT '{}')
- `status` (TEXT, NOT NULL, DEFAULT 'active', CHECK IN ('draft', 'active', 'expired', 'disabled'))
- `created_at` (TEXT, nullable)
- `updated_at` (TEXT, nullable)
- Indexes: `idx_discounts_store_code` UNIQUE, `idx_discounts_store_id`, `idx_discounts_store_status`, `idx_discounts_store_type`

---

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

**customers**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `email` (TEXT, NOT NULL, UNIQUE per store)
- `password_hash` (TEXT, nullable)
- `name` (TEXT, nullable)
- `marketing_opt_in` (INTEGER, NOT NULL, DEFAULT 0)
- `created_at` (TEXT, nullable)
- `updated_at` (TEXT, nullable)
- Indexes: `idx_customers_store_email` UNIQUE, `idx_customers_store_id`

**customer_addresses**
- `id` (INTEGER, PK, autoincrement)
- `customer_id` (INTEGER, NOT NULL, FK -> customers(id) ON DELETE CASCADE)
- `label` (TEXT, nullable)
- `address_json` (TEXT, NOT NULL, DEFAULT '{}')
- `is_default` (INTEGER, NOT NULL, DEFAULT 0)
- Indexes: `idx_customer_addresses_customer_id`, `idx_customer_addresses_default`

**orders**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `customer_id` (INTEGER, nullable, FK -> customers(id) ON DELETE SET NULL)
- `order_number` (TEXT, NOT NULL, UNIQUE per store)
- `payment_method` (TEXT, NOT NULL, CHECK IN ('credit_card', 'paypal', 'bank_transfer'))
- `status` (TEXT, NOT NULL, DEFAULT 'pending', CHECK IN ('pending', 'paid', 'fulfilled', 'cancelled', 'refunded'))
- `financial_status` (TEXT, NOT NULL, DEFAULT 'pending', CHECK IN ('pending', 'authorized', 'paid', 'partially_refunded', 'refunded', 'voided'))
- `fulfillment_status` (TEXT, NOT NULL, DEFAULT 'unfulfilled', CHECK IN ('unfulfilled', 'partial', 'fulfilled'))
- `currency` (TEXT, NOT NULL, DEFAULT 'USD')
- `subtotal_amount` (INTEGER, NOT NULL, DEFAULT 0)
- `discount_amount` (INTEGER, NOT NULL, DEFAULT 0)
- `shipping_amount` (INTEGER, NOT NULL, DEFAULT 0)
- `tax_amount` (INTEGER, NOT NULL, DEFAULT 0)
- `total_amount` (INTEGER, NOT NULL, DEFAULT 0)
- `email` (TEXT, nullable)
- `billing_address_json` (TEXT, nullable)
- `shipping_address_json` (TEXT, nullable)
- `placed_at` (TEXT, nullable)
- `created_at` (TEXT, nullable)
- `updated_at` (TEXT, nullable)
- Indexes: `idx_orders_store_order_number` UNIQUE, `idx_orders_store_id`, `idx_orders_customer_id`, `idx_orders_store_status`, `idx_orders_store_financial`, `idx_orders_store_fulfillment`, `idx_orders_placed_at`

**order_lines**
- `id` (INTEGER, PK, autoincrement)
- `order_id` (INTEGER, NOT NULL, FK -> orders(id) ON DELETE CASCADE)
- `product_id` (INTEGER, nullable, FK -> products(id) ON DELETE SET NULL)
- `variant_id` (INTEGER, nullable, FK -> product_variants(id) ON DELETE SET NULL)
- `title_snapshot` (TEXT, NOT NULL)
- `sku_snapshot` (TEXT, nullable)
- `quantity` (INTEGER, NOT NULL, DEFAULT 1)
- `unit_price_amount` (INTEGER, NOT NULL, DEFAULT 0)
- `total_amount` (INTEGER, NOT NULL, DEFAULT 0)
- `tax_lines_json` (TEXT, NOT NULL, DEFAULT '[]')
- `discount_allocations_json` (TEXT, NOT NULL, DEFAULT '[]')
- Indexes: `idx_order_lines_order_id`, `idx_order_lines_product_id`, `idx_order_lines_variant_id`

**payments**
- `id` (INTEGER, PK, autoincrement)
- `order_id` (INTEGER, NOT NULL, FK -> orders(id) ON DELETE CASCADE)
- `provider` (TEXT, NOT NULL, DEFAULT 'mock', CHECK IN ('mock'))
- `method` (TEXT, NOT NULL, CHECK IN ('credit_card', 'paypal', 'bank_transfer'))
- `provider_payment_id` (TEXT, nullable)
- `status` (TEXT, NOT NULL, DEFAULT 'pending', CHECK IN ('pending', 'captured', 'failed', 'refunded'))
- `amount` (INTEGER, NOT NULL, DEFAULT 0)
- `currency` (TEXT, NOT NULL, DEFAULT 'USD')
- `raw_json_encrypted` (TEXT, nullable)
- `created_at` (TEXT, nullable)
- Indexes: `idx_payments_order_id`, `idx_payments_provider_id`, `idx_payments_method`, `idx_payments_status`

**refunds**
- `id` (INTEGER, PK, autoincrement)
- `order_id` (INTEGER, NOT NULL, FK -> orders(id) ON DELETE CASCADE)
- `payment_id` (INTEGER, NOT NULL, FK -> payments(id) ON DELETE CASCADE)
- `amount` (INTEGER, NOT NULL, DEFAULT 0)
- `reason` (TEXT, nullable)
- `status` (TEXT, NOT NULL, DEFAULT 'pending', CHECK IN ('pending', 'processed', 'failed'))
- `provider_refund_id` (TEXT, nullable)
- `created_at` (TEXT, nullable)
- Indexes: `idx_refunds_order_id`, `idx_refunds_payment_id`, `idx_refunds_status`

**fulfillments**
- `id` (INTEGER, PK, autoincrement)
- `order_id` (INTEGER, NOT NULL, FK -> orders(id) ON DELETE CASCADE)
- `status` (TEXT, NOT NULL, DEFAULT 'pending', CHECK IN ('pending', 'shipped', 'delivered'))
- `tracking_company` (TEXT, nullable)
- `tracking_number` (TEXT, nullable)
- `tracking_url` (TEXT, nullable)
- `shipped_at` (TEXT, nullable)
- `created_at` (TEXT, nullable)
- Indexes: `idx_fulfillments_order_id`, `idx_fulfillments_status`, `idx_fulfillments_tracking`

**fulfillment_lines**
- `id` (INTEGER, PK, autoincrement)
- `fulfillment_id` (INTEGER, NOT NULL, FK -> fulfillments(id) ON DELETE CASCADE)
- `order_line_id` (INTEGER, NOT NULL, FK -> order_lines(id) ON DELETE CASCADE)
- `quantity` (INTEGER, NOT NULL, DEFAULT 1)
- Indexes: `idx_fulfillment_lines_fulfillment_id`, `idx_fulfillment_lines_fulfillment_order_line` UNIQUE

---

### EPIC 7 - Analytics

**analytics_events**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `type` (TEXT, NOT NULL)
- `session_id` (TEXT, nullable)
- `customer_id` (INTEGER, nullable, FK -> customers(id) ON DELETE SET NULL)
- `properties_json` (TEXT, NOT NULL, DEFAULT '{}')
- `client_event_id` (TEXT, nullable, UNIQUE per store)
- `occurred_at` (TEXT, nullable)
- `created_at` (TEXT, nullable)
- Indexes: `idx_analytics_events_store_id`, `idx_analytics_events_store_type`, `idx_analytics_events_store_created`, `idx_analytics_events_session`, `idx_analytics_events_customer`, `idx_analytics_events_client_event` UNIQUE

**analytics_daily** (Composite PK)
- `store_id` (INTEGER, PK, FK -> stores(id) ON DELETE CASCADE)
- `date` (TEXT, PK)
- `orders_count` (INTEGER, NOT NULL, DEFAULT 0)
- `revenue_amount` (INTEGER, NOT NULL, DEFAULT 0)
- `aov_amount` (INTEGER, NOT NULL, DEFAULT 0)
- `visits_count` (INTEGER, NOT NULL, DEFAULT 0)
- `add_to_cart_count` (INTEGER, NOT NULL, DEFAULT 0)
- `checkout_started_count` (INTEGER, NOT NULL, DEFAULT 0)
- `checkout_completed_count` (INTEGER, NOT NULL, DEFAULT 0)
- Indexes: `idx_analytics_daily_store_date`

---

### EPIC 8 - Apps and Webhooks

**apps**
- `id` (INTEGER, PK, autoincrement)
- `name` (TEXT, NOT NULL)
- `status` (TEXT, NOT NULL, DEFAULT 'active', CHECK IN ('active', 'disabled'))
- `created_at` (TEXT, nullable)
- Indexes: `idx_apps_status`

**app_installations**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `app_id` (INTEGER, NOT NULL, FK -> apps(id) ON DELETE CASCADE)
- `scopes_json` (TEXT, NOT NULL, DEFAULT '[]')
- `status` (TEXT, NOT NULL, DEFAULT 'active', CHECK IN ('active', 'suspended', 'uninstalled'))
- `installed_at` (TEXT, nullable)
- Indexes: `idx_app_installations_store_app` UNIQUE, `idx_app_installations_store_id`, `idx_app_installations_app_id`

**oauth_clients**
- `id` (INTEGER, PK, autoincrement)
- `app_id` (INTEGER, NOT NULL, FK -> apps(id) ON DELETE CASCADE)
- `client_id` (TEXT, NOT NULL, UNIQUE)
- `client_secret_encrypted` (TEXT, NOT NULL)
- `redirect_uris_json` (TEXT, NOT NULL, DEFAULT '[]')
- Indexes: `idx_oauth_clients_client_id` UNIQUE, `idx_oauth_clients_app_id`

**oauth_tokens**
- `id` (INTEGER, PK, autoincrement)
- `installation_id` (INTEGER, NOT NULL, FK -> app_installations(id) ON DELETE CASCADE)
- `access_token_hash` (TEXT, NOT NULL, UNIQUE)
- `refresh_token_hash` (TEXT, nullable)
- `expires_at` (TEXT, NOT NULL)
- Indexes: `idx_oauth_tokens_installation_id`, `idx_oauth_tokens_access_hash` UNIQUE, `idx_oauth_tokens_expires_at`

**webhook_subscriptions**
- `id` (INTEGER, PK, autoincrement)
- `store_id` (INTEGER, NOT NULL, FK -> stores(id) ON DELETE CASCADE)
- `app_installation_id` (INTEGER, nullable, FK -> app_installations(id) ON DELETE CASCADE)
- `event_type` (TEXT, NOT NULL)
- `target_url` (TEXT, NOT NULL)
- `signing_secret_encrypted` (TEXT, NOT NULL)
- `status` (TEXT, NOT NULL, DEFAULT 'active', CHECK IN ('active', 'paused', 'disabled'))
- Indexes: `idx_webhook_subscriptions_store_id`, `idx_webhook_subscriptions_store_event`, `idx_webhook_subscriptions_installation`

**webhook_deliveries**
- `id` (INTEGER, PK, autoincrement)
- `subscription_id` (INTEGER, NOT NULL, FK -> webhook_subscriptions(id) ON DELETE CASCADE)
- `event_id` (TEXT, NOT NULL)
- `attempt_count` (INTEGER, NOT NULL, DEFAULT 1)
- `status` (TEXT, NOT NULL, DEFAULT 'pending', CHECK IN ('pending', 'success', 'failed'))
- `last_attempt_at` (TEXT, nullable)
- `response_code` (INTEGER, nullable)
- `response_body_snippet` (TEXT, nullable)
- Indexes: `idx_webhook_deliveries_subscription_id`, `idx_webhook_deliveries_event_id`, `idx_webhook_deliveries_status`, `idx_webhook_deliveries_last_attempt`

---

### Laravel Framework Tables

**sessions**
- `id` (TEXT, PK)
- `user_id` (INTEGER, nullable)
- `ip_address` (TEXT, nullable)
- `user_agent` (TEXT, nullable)
- `payload` (TEXT, NOT NULL)
- `last_activity` (INTEGER, NOT NULL)

**cache**
- `key` (TEXT, PK)
- `value` (TEXT, NOT NULL)
- `expiration` (INTEGER, NOT NULL)

**cache_locks**
- `key` (TEXT, PK)
- `owner` (TEXT, NOT NULL)
- `expiration` (INTEGER, NOT NULL)

**jobs**
- `id` (INTEGER, PK, autoincrement)
- `queue` (TEXT, NOT NULL)
- `payload` (TEXT, NOT NULL)
- `attempts` (INTEGER, NOT NULL)
- `reserved_at` (INTEGER, nullable)
- `available_at` (INTEGER, NOT NULL)
- `created_at` (INTEGER, NOT NULL)

**job_batches**
- `id` (TEXT, PK)
- `name` (TEXT, NOT NULL)
- `total_jobs` (INTEGER, NOT NULL)
- `pending_jobs` (INTEGER, NOT NULL)
- `failed_jobs` (INTEGER, NOT NULL)
- `failed_job_ids` (TEXT, NOT NULL)
- `options` (TEXT, nullable)
- `cancelled_at` (INTEGER, nullable)
- `created_at` (INTEGER, NOT NULL)
- `finished_at` (INTEGER, nullable)

**failed_jobs**
- `id` (INTEGER, PK, autoincrement)
- `uuid` (TEXT, NOT NULL, UNIQUE)
- `connection` (TEXT, NOT NULL)
- `queue` (TEXT, NOT NULL)
- `payload` (TEXT, NOT NULL)
- `exception` (TEXT, NOT NULL)
- `failed_at` (TEXT, NOT NULL, DEFAULT CURRENT_TIMESTAMP)

**personal_access_tokens**
- `id` (INTEGER, PK, autoincrement)
- `tokenable_type` (TEXT, NOT NULL)
- `tokenable_id` (INTEGER, NOT NULL)
- `name` (TEXT, NOT NULL)
- `token` (TEXT, NOT NULL, UNIQUE)
- `abilities` (TEXT, nullable)
- `last_used_at` (TEXT, nullable)
- `expires_at` (TEXT, nullable)
- `created_at` (TEXT, nullable)
- `updated_at` (TEXT, nullable)

**password_reset_tokens**
- `email` (TEXT, PK)
- `token` (TEXT, NOT NULL)
- `created_at` (TEXT, nullable)

**customer_password_reset_tokens** (Composite PK)
- `store_id` (INTEGER, PK, FK -> stores(id) ON DELETE CASCADE)
- `email` (TEXT, PK)
- `token` (TEXT, NOT NULL)
- `created_at` (TEXT, nullable)

---

## Key Conventions Summary

- **Primary Keys**: Auto-incrementing integers on all tables except composites and Laravel tables
- **Tenant Isolation**: `store_id` foreign key on all tenant-scoped tables
- **Monetary Amounts**: Stored as integers in minor units (cents), columns suffixed `_amount`
- **Booleans**: INTEGER type (0 = false, 1 = true), NOT NULL, DEFAULT 0
- **JSON Data**: TEXT columns, cast at application layer
- **Enumerations**: CHECK constraints on TEXT columns
- **Timestamps**: TEXT (ISO-8601) using Laravel's `created_at`/`updated_at`
- **Soft Deletes**: Only where explicitly noted with `deleted_at` TEXT nullable
- **Encrypted Columns**: Suffixed `_encrypted`, encrypted at application layer

---

## SQLite PRAGMA Requirements

- `foreign_keys = ON` - Enforce referential integrity
- `journal_mode = WAL` - Write-Ahead Logging for concurrent reads
- `busy_timeout = 5000` - 5-second lock wait
- `synchronous = NORMAL` - Balanced durability with WAL
- `cache_size = -20000` - 20 MB page cache

This comprehensive schema provides the complete foundation for the multi-tenant e-commerce platform with full support for products, variants, orders, payments, and fulfillment.

text:
agentId: a223e7b (for resuming to continue this agent's work if needed)
<usage>total_tokens: 59083
tool_uses: 3
duration_ms: 55866</usage>

status: completed

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

speed: standard


userType: external

slug: iterative-seeking-pine

type: user

role: user

text:
Perfect! I've now read the entire API specification file. Let me provide you with a comprehensive summary of all API routes.

## Comprehensive API Routes Summary

### Section 1: Web Routes (Blade/Livewire Pages)

#### 1.1 Admin Auth Routes
No special middleware beyond `web`. Rate limit: `login` tier (5/min per IP).

| Method | Path | Component/Action |
|--------|------|------------------|
| GET | `/admin/login` | Livewire: Admin\Auth\Login |
| POST | `/admin/login` | Handled by Livewire action |
| POST | `/admin/logout` | Handled by Livewire action |
| GET | `/admin/forgot-password` | Livewire: Admin\Auth\ForgotPassword |
| POST | `/admin/forgot-password` | Handled by Livewire action |
| GET | `/admin/reset-password/{token}` | Livewire: Admin\Auth\ResetPassword |
| POST | `/admin/reset-password` | Handled by Livewire action |

#### 1.2 Admin Routes
**Prefix:** `/admin`
**Middleware:** `web`, `auth`, `verified`, `store.resolve`, `role.check`

Dashboard, Products (index/create/edit), Inventory, Collections, Orders, Customers, Discounts, Settings (general/shipping/taxes), Themes & Online Store, Pages, Navigation, Apps, Developers, Analytics, Search Settings. All render Livewire full-page components.

#### 1.3 Storefront Routes
**Middleware:** `web`, `store.resolve`

| Path | Component |
|------|-----------|
| `/` | Storefront\Home |
| `/collections` | Storefront\Collections\Index |
| `/collections/{handle}` | Storefront\Collections\Show |
| `/products/{handle}` | Storefront\Products\Show |
| `/cart` | Storefront\Cart\Show |
| `/search` | Storefront\Search\Index |
| `/pages/{handle}` | Storefront\Pages\Show |

**Customer Account Routes** (additional middleware: `auth:customer`):
- `/account`, `/account/orders`, `/account/orders/{orderNumber}`, `/account/addresses`

**Customer Auth Routes** (no auth required):
- `/account/login`, `/account/register`, `/forgot-password`, `/reset-password/{token}`

#### 1.4 Checkout Routes
**Middleware:** `web`, `store.resolve`

| Path | Component |
|------|-----------|
| `/checkout/{checkoutId}` | Storefront\Checkout\Show |
| `/checkout/{checkoutId}/confirmation` | Storefront\Checkout\Confirmation |

---

### Section 2: Storefront REST API

**Prefix:** `/api/storefront/v1`
**Middleware:** `store.resolve`, `throttle:api.storefront`
**Content-Type:** `application/json`

#### 2.1 Cart Endpoints

**POST /api/storefront/v1/carts** (201 Created)
- Request: `{ "currency": "EUR" }` (optional, defaults to store currency)
- Response: Cart object with ID, status, lines, totals
- Validation: Valid ISO 4217 currency code
- Rate limit: 120/min per IP

**GET /api/storefront/v1/carts/{cartId}** (200 OK)
- URL param: `cartId` (integer, required)
- Response: Full cart with line items, totals
- Rate limit: 120/min per IP

**POST /api/storefront/v1/carts/{cartId}/lines** (201 Created)
- Request: `{ "variant_id": 101, "quantity": 2 }`
- Validation: variant_id exists & active; quantity 1-9999, not exceeding inventory
- Response: Full cart object
- Error 409: Cart version conflict (concurrent modification)
- Rate limit: 120/min per IP

**PUT /api/storefront/v1/carts/{cartId}/lines/{lineId}** (200 OK)
- Request: `{ "quantity": 3, "cart_version": 3 }`
- Validation: quantity 1-9999; cart_version must match current
- Response: Full cart object
- Error 409: Version conflict
- Rate limit: 120/min per IP

**DELETE /api/storefront/v1/carts/{cartId}/lines/{lineId}** (200 OK)
- Request: `{ "cart_version": 3 }`
- Response: Full cart object
- Error 409: Version conflict
- Rate limit: 120/min per IP

#### 2.2 Checkout Endpoints

**POST /api/storefront/v1/checkouts** (201 Created)
- Request: `{ "cart_id": 42, "email": "customer@example.com" }`
- Validation: cart_id references active cart with lines; valid email
- Response: Checkout object (status: "started")
- Rate limit: 10/min per session

**GET /api/storefront/v1/checkouts/{checkoutId}** (200 OK)
- Response: Checkout object with current state
- Error 410: Checkout expired
- Rate limit: 10/min per session

**PUT /api/storefront/v1/checkouts/{checkoutId}/address** (200 OK)
- Request: Shipping & billing address objects with all address fields
- Validation: Required fields per address spec; valid country codes (ISO 3166-1 alpha-2)
- Response: Checkout (status transitions to "addressed"), includes available_shipping_methods
- Error 422: Missing fields, invalid country, address not in shipping zone
- Rate limit: 10/min per session

**PUT /api/storefront/v1/checkouts/{checkoutId}/shipping-method** (200 OK)
- Request: `{ "shipping_method_id": 1 }`
- Validation: shipping_method_id must be available for checkout's address
- Response: Checkout (status: "shipping_selected"), totals with shipping & tax
- Error 422: Invalid shipping method
- Rate limit: 10/min per session

**PUT /api/storefront/v1/checkouts/{checkoutId}/payment-method** (200 OK)
- Request: `{ "payment_method": "credit_card|paypal|bank_transfer" }`
- Response: Checkout (status: "payment_selected")
- Error 422: Invalid payment method or invalid state
- Rate limit: 10/min per session

**POST /api/storefront/v1/checkouts/{checkoutId}/apply-discount** (200 OK)
- Request: `{ "code": "WELCOME10" }`
- Validation: Valid, active discount code for store; max 50 chars
- Response: Checkout with applied_discounts and recalculated totals
- Error 400: Discount expired or usage limit exceeded
- Error 422: Invalid code (not found, doesn't apply)
- Rate limit: 10/min per session

**DELETE /api/storefront/v1/checkouts/{checkoutId}/discount** (200 OK)
- Response: Checkout with discount removed and totals recalculated
- Rate limit: 10/min per session

#### 2.3 Payment Endpoint

**POST /api/storefront/v1/checkouts/{checkoutId}/pay** (200 OK)

Request bodies by payment method:
- **Credit Card:** `{ "payment_method": "credit_card", "card_number": "4242...", "card_expiry": "12/28", "card_cvc": "123", "card_holder": "Jane Doe" }`
- **PayPal:** `{ "payment_method": "paypal" }`
- **Bank Transfer:** `{ "payment_method": "bank_transfer" }`

Validation:
- card_number: digits only
- card_expiry: format MM/YY
- card_cvc: 3-4 digits
- card_holder: max 255 chars

Response (Credit Card/PayPal - instant):
- Order with status "paid", financial_status "paid"

Response (Bank Transfer - deferred):
- Order with status "pending", financial_status "pending"
- Includes bank_transfer_instructions (IBAN, BIC, reference, amount)

Magic card errors:
- `4000000000000002`: card_declined
- `4000000000009995`: insufficient_funds

Error 409: Checkout not in payment_selected state
Error 422: Validation errors or payment declined
Rate limit: 10/min per session

#### 2.4 Order Endpoints

**GET /api/storefront/v1/orders/{orderNumber}** (200 OK)
- Authentication: Tokenized access via `token` query param (HMAC-signed)
- URL param: token (required)
- Response: Order details including lines, totals, addresses, fulfillments
- Error 401: Invalid or missing token
- Rate limit: 120/min per IP

#### 2.5 Search Endpoints

**GET /api/storefront/v1/search** (200 OK)
- Query params:
  - `q` (required): 1-200 chars
  - `filters` (optional): JSON with collection_id, price_min/max, in_stock, tags, vendor
  - `sort` (optional): relevance, price_asc, price_desc, newest, best_selling (default: relevance)
  - `page` (optional): default 1
  - `per_page` (optional): 1-50, default 24
- Response: Results with facets (vendors, tags, price_range), pagination
- Error 400: Query too long or malformed filters
- Rate limit: 30/min per IP

**GET /api/storefront/v1/search/suggest** (200 OK)
- Query params:
  - `q` (required): 1-100 chars
  - `limit` (optional): default 5, max 10
- Response: Suggestions with type (product/collection), title, handle, image_url, price
- Rate limit: 30/min per IP

#### 2.6 Analytics Endpoints

**POST /api/storefront/v1/analytics/events** (202 Accepted)
- Request: `{ "events": [{ "type": "page_view|add_to_cart|remove_from_cart|checkout_started|checkout_completed|search", "session_id": "...", "client_event_id": "...", "properties": {...}, "occurred_at": "ISO 8601" }] }`
- Validation:
  - 1-50 events per batch
  - client_event_id unique per store (deduplicates)
  - occurred_at within +/- 1 hour
  - properties: max 3 levels deep
- Response: `{ "accepted": 2, "rejected": 0 }`
- Rate limit: 60/min per IP

---

### Section 3: Admin REST API

**Prefix:** `/api/admin/v1`
**Middleware:** `auth:sanctum`, `store.resolve`, `throttle:api.admin`
**Content-Type:** `application/json`
**Rate limit:** 60/min per token

#### 3.1 Platform Management (Super-Admin Only, `manage-platform` scope)

**POST /api/admin/v1/platform/organizations** (201 Created)
- Request: `{ "name": "Acme Corp", "billing_email": "billing@acme.com" }`
- Validation: name max 255; valid email

**POST /api/admin/v1/platform/stores** (201 Created)
- Request: `{ "organization_id": 1, "name": "Acme Store", "handle": "acme", "default_currency": "EUR", "default_locale": "en", "timezone": "Europe/Berlin" }`
- Validation: handle unique, lowercase alphanumeric with hyphens, regex: `^[a-z0-9]([a-z0-9-]*[a-z0-9])?$`; valid ISO 4217, valid locale, valid IANA timezone
- Error 422: Duplicate handle, invalid timezone

**POST /api/admin/v1/stores/{storeId}/invites** (201 Created)
- Request: `{ "email": "staff@acme.com", "role": "owner|admin|staff|support" }`
- Response: Invite with expires_at (7 days)
- Error 409: User already a member
- Only owner/admin can invite

**GET /api/admin/v1/stores/{storeId}/me** (200 OK)
- Response: User membership with role and permissions array
- Error 403: Not a member of this store

#### 3.2 Products (`read-products`, `write-products` scopes)

**GET /api/admin/v1/stores/{storeId}/products** (200 OK)
- Query params: status (draft/active/archived), query, collection_id, page, per_page (max 100), sort
- Response: Array of products with featured_image, variants_count, total_inventory

**POST /api/admin/v1/stores/{storeId}/products** (201 Created)
- Request: Full product with title, handle, description_html, vendor, product_type, status, tags, options (max 3), variants (min 1, max 100), collections
- Variant validation: sku unique per store; price_amount min 0; compare_at_amount > price_amount if set; exactly one default; inventory.policy (deny/continue)
- Error 422: Duplicate SKU, duplicate handle, missing variants

**GET /api/admin/v1/stores/{storeId}/products/{productId}** (200 OK)
- Response: Full product with nested options (with values), variants (with inventory), media, collections

**PUT /api/admin/v1/stores/{storeId}/products/{productId}** (200 OK)
- Supports partial updates; same fields as POST

**DELETE /api/admin/v1/stores/{storeId}/products/{productId}** (200 OK)
- Soft delete (archive); returns product with status "archived"

**POST /api/admin/v1/stores/{storeId}/products/{productId}/media/presign-upload** (201 Created)
- Request: `{ "filename": "...", "content_type": "image/jpeg|png|webp|avif|video/mp4", "byte_size": 245000 }`
- Response: upload_url, method, headers, storage_key, media_id, expires_at
- Validation: valid extension; max 50MB images, 500MB video
- Error 422: Invalid content type, file too large

#### 3.3 Collections (`read-collections`, `write-collections` scopes)

**GET /api/admin/v1/stores/{storeId}/collections** (200 OK)
- Query params: status, query, page, per_page

**POST /api/admin/v1/stores/{storeId}/collections** (201 Created)
- Request: `{ "title": "...", "handle": "...", "description_html": "...", "type": "manual|automated", "status": "draft|active|archived", "product_ids": [...] }`
- Validation: title max 255; handle auto-generated & unique

**PUT /api/admin/v1/stores/{storeId}/collections/{collectionId}** (200 OK)
- Supports partial updates; use product_ids to replace, add_product_ids/remove_product_ids for incremental

**DELETE /api/admin/v1/stores/{storeId}/collections/{collectionId}** (200 OK)

#### 3.4 Orders (`read-orders`, `write-orders` scopes)

**GET /api/admin/v1/stores/{storeId}/orders** (200 OK)
- Query params: status, financial_status, fulfillment_status, customer_id, created_after, created_before, query, page, per_page, sort
- Response: Orders with customer, totals (subtotal, discount, shipping, tax, total)

**GET /api/admin/v1/stores/{storeId}/orders/{orderId}** (200 OK)
- Response: Full order with lines, payments, fulfillments, refunds, addresses

**POST /api/admin/v1/stores/{storeId}/orders/{orderId}/fulfillments** (201 Created)
- Request: `{ "tracking_company": "DHL", "tracking_number": "...", "tracking_url": "...", "line_items": [{ "order_line_id": 301, "quantity": 2 }], "notify_customer": true }`
- Validation: At least one line_item; quantity <= unfulfilled quantity
- Error 409: Order not fulfillable (cancelled, already fully fulfilled)
- Error 422: Exceeds unfulfilled quantity

**POST /api/admin/v1/stores/{storeId}/orders/{orderId}/refunds** (201 Created)
- Request: `{ "amount": 2500, "reason": "...", "line_items": [...], "notify_customer": true }`
- Validation: amount min 1, max total refundable; line_items.quantity <= line quantity minus already-refunded
- Error 409: Order cannot be refunded (not paid, fully refunded)
- Error 422: Amount exceeds refundable total
- Error 502: Payment provider refund failed

#### 3.5 Discounts (`read-discounts`, `write-discounts` scopes)

**GET /api/admin/v1/stores/{storeId}/discounts** (200 OK)
- Query params: type (code/automatic), status (active/expired/scheduled), page, per_page

**POST /api/admin/v1/stores/{storeId}/discounts** (201 Created)
- Request: `{ "type": "code|automatic", "code": "SUMMER20", "value_type": "fixed|percent|free_shipping", "value_amount": 20, "starts_at": "ISO", "ends_at": "ISO", "usage_limit": 500, "rules_json": {...} }`
- Validation: code unique per store, max 50; percent 1-100; fixed min 1 (minor units); ends_at > starts_at
- rules_json: minimum_purchase_amount, applicable_product_ids, applicable_collection_ids, customer_eligibility (all/specific_customers/specific_segments), once_per_customer

**PUT /api/admin/v1/stores/{storeId}/discounts/{discountId}** (200 OK)
- Supports partial updates; code cannot change after creation

**DELETE /api/admin/v1/stores/{storeId}/discounts/{discountId}** (200 OK)

#### 3.6 Shipping (`read-settings`, `write-settings` scopes)

**GET /api/admin/v1/stores/{storeId}/shipping/zones** (200 OK)
- Response: Zones with rates (flat, weight, price, carrier types)

**POST /api/admin/v1/stores/{storeId}/shipping/zones** (201 Created)
- Request: `{ "name": "...", "countries_json": ["DE", ...], "regions_json": [] }`
- Validation: Valid ISO 3166-1 alpha-2 codes; at least one country

**PUT /api/admin/v1/stores/{storeId}/shipping/zones/{zoneId}** (200 OK)

**POST /api/admin/v1/stores/{storeId}/shipping/zones/{zoneId}/rates** (201 Created)
- Request: `{ "name": "...", "type": "flat|weight|price|carrier", "config_json": {...}, "is_active": true }`

#### 3.7 Tax Settings (`read-settings`, `write-settings` scopes)

**GET /api/admin/v1/stores/{storeId}/tax/settings** (200 OK)
- Response: mode (manual/provider), provider, prices_include_tax, config_json

**PUT /api/admin/v1/stores/{storeId}/tax/settings** (200 OK)
- Request: `{ "mode": "manual|provider", "provider": "stripe_tax|none", "prices_include_tax": boolean, "config_json": {...} }`

#### 3.8 Themes (`write-themes` scope)

**POST /api/admin/v1/stores/{storeId}/themes** (201 Created)
- Request: multipart/form-data with file (ZIP), optional name
- Validation: ZIP max 50MB; valid theme structure
- Response: Theme with status "draft"

**POST /api/admin/v1/stores/{storeId}/themes/{themeId}/publish** (200 OK)
- Response: Theme with status "published", published_at

**PUT /api/admin/v1/stores/{storeId}/themes/{themeId}/settings** (200 OK)
- Request: `{ "settings_json": {...} }` (must conform to theme schema)

#### 3.9 Pages (`read-content`, `write-content` scopes)

**GET /api/admin/v1/stores/{storeId}/pages** (200 OK)
- Query params: status (draft/published), page, per_page

**POST /api/admin/v1/stores/{storeId}/pages** (201 Created)
- Request: `{ "title": "...", "handle": "...", "body_html": "...", "status": "draft|published" }`

**PUT /api/admin/v1/stores/{storeId}/pages/{pageId}** (200 OK)
- Supports partial updates

**DELETE /api/admin/v1/stores/{storeId}/pages/{pageId}** (200 OK)

#### 3.10 Search Admin (`read-settings`, `write-settings` scopes)

**POST /api/admin/v1/stores/{storeId}/search/reindex** (202 Accepted)
- Response: `{ "message": "...", "job_id": "...", "status": "queued" }`
- Error 409: Reindex already in progress

**GET /api/admin/v1/stores/{storeId}/search/status** (200 OK)
- Response: index_status, last_reindex_at, last_reindex_duration_seconds, documents_count, pending_updates

#### 3.11 Analytics and Exports (`read-analytics`, `read-orders` scopes)

**GET /api/admin/v1/stores/{storeId}/analytics/summary** (200 OK)
- Query params: from (YYYY-MM-DD), to (YYYY-MM-DD), granularity (day/week/month, default: day)
- Response: Orders count, revenue, AOV, visits, conversions, daily breakdown, top_products

**POST /api/admin/v1/stores/{storeId}/exports/orders** (202 Accepted)
- Request: `{ "format": "csv", "filters": {...} }`
- Response: export_id, status ("queued"), created_at

**GET /api/admin/v1/stores/{storeId}/exports/{exportId}** (200 OK)
- Response: status (queued/processing/completed/failed), row_count, download_url, expires_at

---

### Section 4: Webhook Receiver Endpoints

No inbound endpoints required (Mock PSP handles all payments in-process).

---

### Section 5: OAuth Endpoints (Deferred, return 501 Not Implemented)

**GET /oauth/authorize**
- Query params: client_id, redirect_uri, response_type ("code"), scope, state

**POST /oauth/token**
- Authorization code exchange & refresh token flows
- Response: token_type ("Bearer"), expires_in, access_token, refresh_token

**App API Endpoints** (deferred)
- `/api/apps/v1/stores/{storeId}/products` (read-products scope)
- `/api/apps/v1/stores/{storeId}/orders` (read-orders scope)
- `/api/apps/v1/stores/{storeId}/customers` (read-customers scope)

---

### Section 6: Middleware Stack

| Middleware | Purpose |
|-----------|---------|
| `web` | Default group (CSRF, sessions, errors) |
| `auth` | Require authenticated user |
| `verified` | Require verified email |
| `store.resolve` | Resolve store from session (admin) or hostname (storefront/API) |
| `role.check` | Verify user role for store |
| `auth:sanctum` | Sanctum API token authentication |
| `throttle:api.storefront` | 120/min per IP |
| `throttle:api.admin` | 60/min per token |

Custom middleware: `store.resolve` (hostname vs session-based resolution), `role.check` (hierarchy: owner > admin > staff > support)

---

### Section 7: Rate Limiting Configuration

| Tier | Limit | Window | Key |
|------|-------|--------|-----|
| `login` | 5 | 1 min | IP |
| `api.admin` | 60 | 1 min | Token ID |
| `api.storefront` | 120 | 1 min | IP |
| `checkout` | 10 | 1 min | Session ID |
| `search` | 30 | 1 min | IP |
| `analytics` | 60 | 1 min | IP |

Response header: `X-RateLimit-Limit`, `X-RateLimit-Remaining`, `Retry-After` (429 only)

---

### Section 8: Checkout State Machine

States: `started` -> `addressed` -> `shipping_selected` -> `payment_selected` -> `completed`
Any state can transition to `expired` (24-hour TTL)
`payment_selected` can revert to `shipping_selected` on payment decline

Discount application doesn't change state but recalculates totals.

---

### Section 9: API Versioning

- Storefront: `/api/storefront/v1/`
- Admin: `/api/admin/v1/`
- Apps: `/api/apps/v1/`
- Outbound webhooks include `api_version` field

---

### Section 10: Error Response Format

All errors follow consistent JSON format with `message` and optional `error_code`, `errors` (validation), `reference_id` (server errors), `current_version` (conflict).

This comprehensive specification provides all implementation details needed to build the entire API infrastructure.

text:
agentId: a49ee36 (for resuming to continue this agent's work if needed)
<usage>total_tokens: 75552
tool_uses: 8
duration_ms: 72824</usage>

status: completed

prompt: Read the file /Users/fabianwesner/Herd/shop/specs/02-API-ROUTES.md in chunks (500 lines at a time) and provide a comprehensive summary of ALL API routes including: method, URL, middleware, controller/handler, request/response formats, and validation rules. I need every detail for implementation.

service_tier: standard


text:
Perfect! I've now read the entire auth and security specification file. Here's a comprehensive summary of ALL auth and security specifications:

## Comprehensive Auth & Security Specification Summary

### 1. AUTHENTICATION MECHANISMS

#### 1.1 Admin Authentication (Web Guard)
- **Guard:** `web` (default Laravel guard)
- **Provider:** `users` (Eloquent driver)
- **Model:** `App\Models\User`
- **Routes:** `GET /admin/login`, `POST /admin/login`
- **Livewire Component:** `Admin\Auth\Login`
- **Key requirements:**
  - Validate email and password
  - Rate limit: 5 attempts per minute per IP (keyed by IP)
  - Regenerate session ID on successful login (prevent fixation)
  - Show only generic error "Invalid credentials" (never reveal field-specific failures)
  - "Remember me" checkbox with long-lived remember token cookie (default cookie name: `remember_web_{hash}`)
  - Session lifetime: 120 minutes (configurable via `SESSION_LIFETIME` env)

#### 1.2 Admin Password Reset
- **Password broker:** `users` broker
- **Token table:** `password_reset_tokens`
- **Token expiration:** 60 minutes
- **Throttle:** One reset email per 60 seconds per email
- **Routes:** `GET /admin/forgot-password`, `POST /admin/forgot-password`, `GET /admin/reset-password/{token}`, `POST /admin/reset-password`
- **Generic response:** "If that email exists, we sent a reset link" (never reveal if email exists)

#### 1.3 Customer Authentication (Custom Guard)
- **Guard name:** `customer` (custom guard)
- **Provider:** `customers` (custom Eloquent provider)
- **Model:** `App\Models\Customer`
- **Routes:** `GET /account/login`, `GET /account/register`
- **Critical requirement:** Always scoped to current store
- **Rate limit:** 5 attempts per minute per IP (same `login` limiter as admin)
- **Custom CustomerUserProvider** must inject `store_id` into credential checks
- **Same email** can exist in different stores (multi-tenant isolation)
- **Redirect:** After login, go to `/account` or intended URL

#### 1.4 Customer Registration
- **Form:** `RegisterCustomerRequest`
- **Fields:**
  - `name` - string, required, max 255
  - `email` - email, required, unique per store (composite unique on email + store_id)
  - `password` - string, required, min 8, confirmed
  - `password_confirmation` - must match password
  - `marketing_opt_in` - boolean, optional, defaults to false
- **Process:** Create customer record scoped to current store, auto-login, redirect to `/account`

#### 1.5 Customer Password Reset
- **Broker:** `customers` broker
- **Token table:** `customer_password_reset_tokens` (separate from admin)
- **Token records include:** `store_id` for store-scoped resets
- **Routes:** `GET /forgot-password`, `POST /forgot-password`, `GET /reset-password/{token}`, `POST /reset-password`
- **Token expiration:** 60 minutes
- **Throttle:** One reset email per 60 seconds
- **Generic response:** Do not reveal if email exists in store

#### 1.6 API Authentication (Sanctum)
- **Guard:** `sanctum`
- **Provider:** `users` (admin users only)
- **Token type:** Personal access tokens
- **Token prefix:** `shop_`
- **Hashing:** SHA-256
- **Default expiration:** 1 year
- **Storage:** `personal_access_tokens` table
- **Creation location:** Admin panel, Developers section
- **Delivery:** `Authorization: Bearer shop_xxxxxxxx` header
- **One-time display:** Plain-text token shown exactly once on creation, only hash persisted
- **Revocation:** Deletes token row from database

#### 1.7 Token Abilities (Scopes)
- `read-products` - List and view products
- `write-products` - Create, update, delete products
- `read-orders` - List and view orders
- `write-orders` - Update orders, create fulfillments
- `read-customers` - List and view customers
- `write-customers` - Update customers
- `read-collections` - List and view collections
- `write-collections` - Create, update, delete collections
- `read-discounts` - List and view discounts
- `write-discounts` - Create, update, delete discounts
- `read-analytics` - View analytics data
- `read-settings` - View store settings
- `write-settings` - Update store settings (replaces legacy scopes)
- `read-themes` - View themes and theme files
- `write-themes` - Create, update, delete, publish themes
- `read-content` - View pages and navigation menus
- `write-content` - Create, update, delete pages and navigation items
- `manage-platform` - Platform-level management (super-admin only)
- **Enforcement:** Each API endpoint checks token's abilities; returns 403 Forbidden if lacking required ability

#### 1.8 Guard Configuration (config/auth.php)
**Guards:**
| Guard | Driver | Provider | Purpose |
|-------|--------|----------|---------|
| `web` | session | `users` | Admin panel session auth |
| `customer` | session | `customers` | Storefront customer session auth |
| `sanctum` | sanctum | `users` | Admin REST API token auth |

**Providers:**
| Provider | Driver | Model | Purpose |
|----------|--------|-------|---------|
| `users` | eloquent | `App\Models\User` | Admin users |
| `customers` | eloquent | `App\Models\Customer` | Storefront customers (store-scoped via custom provider) |

**Password Brokers:**
| Broker | Provider | Token Table | Expiry | Throttle |
|--------|----------|-------------|--------|----------|
| `users` | `users` | `password_reset_tokens` | 60 min | 60 sec |
| `customers` | `customers` | `customer_password_reset_tokens` | 60 min | 60 sec |

---

### 2. AUTHORIZATION

#### 2.1 Role Enum (StoreUserRole)
File: `app/Enums/StoreUserRole.php` - Backed string enum with four values:

| Case | Stored Value | Description | Constraints |
|------|-------------|-------------|-------------|
| Owner | `'owner'` | Full access. Can transfer ownership, manage billing, delete store. | One per store |
| Admin | `'admin'` | Near-full access. Cannot transfer ownership or delete store. | |
| Staff | `'staff'` | Operational access. Can manage products, orders, collections, discounts, pages. Cannot access settings, themes, staff management, developers. | |
| Support | `'support'` | Read-only access to products, orders, and customers. For customer service agents. | |

**Key constraint:** Each store must have exactly one Owner. Ownership transfer is a dedicated action.

#### 2.2 Complete Permission Matrix

| Resource | Action | Owner | Admin | Staff | Support |
|----------|--------|:-----:|:-----:|:-----:|:-------:|
| Store Settings | View | Y | Y | N | N |
| Store Settings | Update | Y | Y | N | N |
| Store | Delete | Y | N | N | N |
| Products | List / View | Y | Y | Y | Y |
| Products | Create | Y | Y | Y | N |
| Products | Update | Y | Y | Y | N |
| Products | Delete / Archive | Y | Y | N | N |
| Products | Restore | Y | Y | N | N |
| Collections | List / View | Y | Y | Y | Y |
| Collections | Create | Y | Y | Y | N |
| Collections | Update | Y | Y | Y | N |
| Collections | Delete | Y | Y | N | N |
| Orders | List / View | Y | Y | Y | Y |
| Orders | Update | Y | Y | Y | N |
| Orders | Cancel | Y | Y | N | N |
| Fulfillments | Create | Y | Y | Y | N |
| Fulfillments | Update / Cancel | Y | Y | Y | N |
| Refunds | Create | Y | Y | N | N |
| Customers | List / View | Y | Y | Y | Y |
| Customers | Update | Y | Y | Y | N |
| Discounts | List / View | Y | Y | Y | Y |
| Discounts | Create | Y | Y | Y | N |
| Discounts | Update | Y | Y | Y | N |
| Discounts | Delete | Y | Y | N | N |
| Themes | List / View | Y | Y | N | N |
| Themes | Create / Upload | Y | Y | N | N |
| Themes | Customize | Y | Y | N | N |
| Themes | Publish | Y | Y | N | N |
| Themes | Delete | Y | Y | N | N |
| Pages | List / View | Y | Y | Y | N |
| Pages | Create | Y | Y | Y | N |
| Pages | Update | Y | Y | Y | N |
| Pages | Delete | Y | Y | N | N |
| Navigation | List / View | Y | Y | Y | N |
| Navigation | Manage | Y | Y | N | N |
| Apps | List / View | Y | Y | N | N |
| Apps | Install / Remove | Y | Y | N | N |
| Analytics | View | Y | Y | Y | N |
| Developers / API | Manage | Y | Y | N | N |
| Staff | List / View | Y | Y | N | N |
| Staff | Invite | Y | Y | N | N |
| Staff | Update Role | Y | Y | N | N |
| Staff | Remove | Y | Y | N | N |
| Shipping Zones | Manage | Y | Y | N | N |
| Tax Settings | Manage | Y | Y | N | N |
| Search Settings | Manage | Y | Y | N | N |

#### 2.3 Role Group Shorthand
Helper groups for authorization layer:

| Group | Roles | Used For |
|-------|-------|----------|
| OwnerOnly | Owner | Store deletion, ownership transfer |
| OwnerOrAdmin | Owner, Admin | Settings, themes, staff, developers, navigation, apps, shipping, taxes, search, delete/archive actions |
| OwnerAdminOrStaff | Owner, Admin, Staff | Create/update products, collections, discounts, pages, fulfillments, order updates |
| AnyRole | Owner, Admin, Staff, Support | Read-only listing/viewing of products, orders, customers, collections, discounts |

#### 2.4 ChecksStoreRole Trait
File: `app/Traits/ChecksStoreRole.php` - Reusable trait for policies and gates

**Methods:**
| Method | Parameters | Returns | Description |
|--------|-----------|---------|-------------|
| `getStoreRole` | User, store_id (int) | StoreUserRole or null | Lookup user's role for given store via `store_users` relationship |
| `hasRole` | User, store_id (int), roles (array of StoreUserRole) | bool | True if user's role is in provided list |
| `isOwnerOrAdmin` | User, store_id (int) | bool | Shorthand for Owner or Admin |
| `isOwnerAdminOrStaff` | User, store_id (int) | bool | Shorthand for Owner, Admin, or Staff |
| `isAnyRole` | User, store_id (int) | bool | True if user has any role in store |

**Store ID resolution:**
- When policy receives model instance: use model's `store_id` attribute
- When policy does NOT receive model (e.g., `viewAny`, `create`): resolve from container-bound `current_store`

#### 2.5 Laravel Policies
Auto-discovered by convention: `App\Policies\{Model}Policy`
Pattern: All policies use `ChecksStoreRole` trait

**ProductPolicy:**
| Method | Model? | Required Roles | Store ID Source |
|--------|:------:|----------------|-----------------|
| `viewAny(User)` | No | AnyRole | current_store |
| `view(User, Product)` | Yes | AnyRole | product.store_id |
| `create(User)` | No | OwnerAdminOrStaff | current_store |
| `update(User, Product)` | Yes | OwnerAdminOrStaff | product.store_id |
| `delete(User, Product)` | Yes | OwnerOrAdmin | product.store_id |
| `archive(User, Product)` | Yes | OwnerOrAdmin | product.store_id |
| `restore(User, Product)` | Yes | OwnerOrAdmin | product.store_id |

**OrderPolicy:**
| Method | Model? | Required Roles | Store ID Source |
|--------|:------:|----------------|-----------------|
| `viewAny(User)` | No | AnyRole | current_store |
| `view(User, Order)` | Yes | AnyRole | order.store_id |
| `update(User, Order)` | Yes | OwnerAdminOrStaff | order.store_id |
| `cancel(User, Order)` | Yes | OwnerOrAdmin | order.store_id |
| `createFulfillment(User, Order)` | Yes | OwnerAdminOrStaff | order.store_id |
| `createRefund(User, Order)` | Yes | OwnerOrAdmin | order.store_id |

**CollectionPolicy:**
| Method | Model? | Required Roles | Store ID Source |
|--------|:------:|----------------|-----------------|
| `viewAny(User)` | No | AnyRole | current_store |
| `view(User, Collection)` | Yes | AnyRole | collection.store_id |
| `create(User)` | No | OwnerAdminOrStaff | current_store |
| `update(User, Collection)` | Yes | OwnerAdminOrStaff | collection.store_id |
| `delete(User, Collection)` | Yes | OwnerOrAdmin | collection.store_id |

**DiscountPolicy:**
| Method | Model? | Required Roles | Store ID Source |
|--------|:------:|----------------|-----------------|
| `viewAny(User)` | No | AnyRole | current_store |
| `view(User, Discount)` | Yes | AnyRole | discount.store_id |
| `create(User)` | No | OwnerAdminOrStaff | current_store |
| `update(User, Discount)` | Yes | OwnerAdminOrStaff | discount.store_id |
| `delete(User, Discount)` | Yes | OwnerOrAdmin | discount.store_id |

**CustomerPolicy:**
| Method | Model? | Required Roles | Store ID Source |
|--------|:------:|----------------|-----------------|
| `viewAny(User)` | No | AnyRole | current_store |
| `view(User, Customer)` | Yes | AnyRole | customer.store_id |
| `update(User, Customer)` | Yes | OwnerAdminOrStaff | customer.store_id |

**ThemePolicy:**
| Method | Model? | Required Roles | Store ID Source |
|--------|:------:|----------------|-----------------|
| `viewAny(User)` | No | OwnerOrAdmin | current_store |
| `view(User, Theme)` | Yes | OwnerOrAdmin | theme.store_id |
| `create(User)` | No | OwnerOrAdmin | current_store |
| `update(User, Theme)` | Yes | OwnerOrAdmin | theme.store_id |
| `delete(User, Theme)` | Yes | OwnerOrAdmin | theme.store_id |
| `publish(User, Theme)` | Yes | OwnerOrAdmin | theme.store_id |

**PagePolicy:**
| Method | Model? | Required Roles | Store ID Source |
|--------|:------:|----------------|-----------------|
| `viewAny(User)` | No | OwnerAdminOrStaff | current_store |
| `view(User, Page)` | Yes | OwnerAdminOrStaff | page.store_id |
| `create(User)` | No | OwnerAdminOrStaff | current_store |
| `update(User, Page)` | Yes | OwnerAdminOrStaff | page.store_id |
| `delete(User, Page)` | Yes | OwnerOrAdmin | page.store_id |

**StorePolicy:**
| Method | Model? | Required Roles | Store ID Source |
|--------|:------:|----------------|-----------------|
| `viewSettings(User, Store)` | Yes | OwnerOrAdmin | store.id |
| `updateSettings(User, Store)` | Yes | OwnerOrAdmin | store.id |
| `delete(User, Store)` | Yes | OwnerOnly | store.id |

**FulfillmentPolicy:**
| Method | Model? | Required Roles | Store ID Source |
|--------|:------:|----------------|-----------------|
| `create(User, Order)` | Yes (Order) | OwnerAdminOrStaff | order.store_id |
| `update(User, Fulfillment)` | Yes | OwnerAdminOrStaff | fulfillment -> order.store_id |
| `cancel(User, Fulfillment)` | Yes | OwnerAdminOrStaff | fulfillment -> order.store_id |

**RefundPolicy:**
| Method | Model? | Required Roles | Store ID Source |
|--------|:------:|----------------|-----------------|
| `create(User, Order)` | Yes (Order) | OwnerOrAdmin | order.store_id |

**NavigationMenuPolicy:**
| Method | Model? | Required Roles | Store ID Source |
|--------|:------:|----------------|-----------------|
| `viewAny(User)` | No | OwnerAdminOrStaff | current_store |
| `manage(User)` | No | OwnerOrAdmin | current_store |

#### 2.6 Gates (Non-Model Operations)
Registered in `AppServiceProvider` or `AuthServiceProvider`. All gates resolve current store from container and check user's role via `store_users` relationship.

| Gate Name | Required Roles | Purpose |
|-----------|---------------|---------|
| `manage-store-settings` | Owner, Admin | Access store settings pages |
| `manage-staff` | Owner, Admin | Invite, update, remove staff members |
| `manage-developers` | Owner, Admin | Create/revoke API tokens, manage webhooks |
| `view-analytics` | Owner, Admin, Staff | View analytics dashboard and reports |
| `manage-shipping` | Owner, Admin | Manage shipping zones, rates, profiles |
| `manage-taxes` | Owner, Admin | Manage tax settings and rates |
| `manage-search-settings` | Owner, Admin | Configure search weights and synonyms |
| `manage-navigation` | Owner, Admin | Create/edit/delete navigation menus |
| `manage-apps` | Owner, Admin | Install and remove third-party apps |

**Gate resolution pattern:**
1. Resolve current store from container
2. Look up user's `store_users` record for that store
3. If no record exists, return false
4. If user's role is in gate's required roles list, return true
5. Otherwise return false

#### 2.7 Policy and Gate Usage Patterns

**In Livewire Components:**
- List views: call `authorize('viewAny', ModelClass)` in `mount()` lifecycle
- Action methods: call `authorize('action', $modelInstance)` at start before mutation
- Throws `AuthorizationException` (renders as 403) on failure

**In API Controllers:**
- Use `$this->authorize('action', $model)` at start of each method
- Or use `can` middleware on routes
- Form Requests also implement `authorize()` to check policy/gate before validation

**In Blade Templates:**
- Use `@can('gate-name')` / `@cannot` directives
- Purely cosmetic UI hiding - actual authorization happens server-side

---

### 3. MIDDLEWARE STACK

#### 3.1 Middleware Registration (bootstrap/app.php)

| Alias | Class | Purpose |
|-------|-------|---------|
| `store.resolve` | `App\Http\Middleware\ResolveStore` | Resolves current store from hostname or session |
| `role.check` | `App\Http\Middleware\CheckStoreRole` | Checks user has required role for current store |
| `auth:customer` | `App\Http\Middleware\CustomerAuthenticate` | Ensures customer is authenticated via customer guard |

#### 3.2 Route Middleware Groups

| Route Group | Middleware Stack | Description |
|-------------|-----------------|-------------|
| Admin web (auth pages) | `web` | Login, forgot-password, reset-password |
| Admin web (authenticated) | `web`, `auth`, `verified`, `store.resolve`, `role.check` | All admin panel pages |
| Admin API | `api`, `auth:sanctum`, `store.resolve`, `throttle:api.admin` | REST API endpoints |
| Storefront web (public) | `web`, `store.resolve` | Public store pages (catalog, product, cart) |
| Storefront web (customer) | `web`, `store.resolve`, `auth:customer` | Customer account pages |
| Storefront API | `api`, `store.resolve`, `throttle:api.storefront` | Cart and checkout API |
| Webhook routes | `api`, `throttle:webhooks` | Outbound app webhooks (no inbound payment webhooks - Mock PSP in-process) |

#### 3.3 Custom Middleware Specifications

**ResolveStore Middleware (`app/Http/Middleware/ResolveStore.php`):**

Purpose: Resolve current store from request hostname and bind to container.

**Storefront flow:**
1. Extract hostname from request
2. Match hostname in `store_domains` table
3. Cache hostname-to-store mapping using file cache (tag: `store-domains`)
4. If not found, abort 404
5. Check if store status is 'suspended', if yes abort 503
6. Bind Store to container as `'current_store'`
7. Share Store with all views as `currentStore`
8. Continue

**Admin flow:**
1. Read store_id from session key `'current_store_id'`
2. Verify user has `store_users` record for that store
3. If no record, abort 403
4. Same binding and sharing as storefront
5. Continue

**Shared requirements:**
- If store status is 'suspended': abort with HTTP 503 "This store is currently unavailable"
- For admin routes: abort 403 for mutations if suspended
- Bind resolved Store model to container with key `'current_store'`
- Share store with all Blade views as `currentStore`

**CheckStoreRole Middleware (`app/Http/Middleware/CheckStoreRole.php`):**

Purpose: Verify authenticated user has one of specified roles for current store.

**Route usage:** `role.check:owner,admin` or `role.check:owner,admin,staff` (comma-separated role values)

**Flow:**
1. Resolve `current_store` from container
2. Get authenticated user from request
3. Query user's `store_users` relationship filtered by store_id
4. If no record exists: abort 403 "You do not have access to this store"
5. Parse allowed roles from middleware parameters (convert to StoreUserRole enum)
6. If user's role not in allowed list: abort 403 "Insufficient permissions"
7. Store resolved `StoreUser` pivot record on request attributes (key: `store_user`)
8. Continue

**CustomerAuthenticate Middleware (`app/Http/Middleware/CustomerAuthenticate.php`):**

Purpose: Ensure request is authenticated via `customer` guard. Redirect unauthenticated customers to login.

**Flow:**
1. Check if `Auth::guard('customer')` reports user as authenticated
2. If authenticated: continue
3. If not authenticated:
   - Save current URL in session as intended URL
   - Redirect to storefront login route (`/account/login`)

---

### 4. SECURITY CONTROLS

#### 4.1 CSRF Protection

| Context | Mechanism |
|---------|-----------|
| Blade forms | Laravel's `@csrf` directive injects hidden token field (automatic) |
| Livewire actions | Livewire handles CSRF verification automatically on every request |
| API routes (Sanctum token) | Exempt from CSRF (token-based auth replaces CSRF need) |
| SPA clients | `GET /sanctum/csrf-cookie` sets XSRF-TOKEN cookie for session-based API calls |

**Requirements:**
- CSRF verification enforced globally on all `web` middleware group routes
- No routes within `web` group may be excluded from CSRF
- API routes (under `api` middleware group) inherently exempt since they use token auth

#### 4.2 Rate Limiting

All limiters defined in `bootstrap/app.php` or `AppServiceProvider::boot()`

| Limiter Name | Limit | Keyed By | Applied To |
|-------------|-------|----------|------------|
| `login` | 5 per minute | IP address | Admin login, customer login |
| `api.admin` | 60 per minute | User ID (if authenticated), otherwise IP | Admin REST API endpoints |
| `api.storefront` | 120 per minute | IP address | Public storefront API (cart, search) |
| `checkout` | 10 per minute | Session ID | Checkout-related operations |
| `search` | 30 per minute | IP address | Search queries |
| `analytics` | 60 per minute | IP address | Analytics event ingestion |
| `webhooks` | 100 per minute | IP address | Outbound app webhook endpoints |

**Rate limit response headers** (returned on all API responses):
- `X-RateLimit-Limit` - Maximum requests allowed in window
- `X-RateLimit-Remaining` - Requests remaining in current window
- `Retry-After` - Seconds until rate limit resets (only on 429 responses)

When exceeded: HTTP 429 Too Many Requests

#### 4.3 Encryption at Rest

**Algorithm:** AES-256-CBC with MAC verification, keyed by application's `APP_KEY`

**Fields requiring encrypted storage** (using Laravel's `encrypted` cast):

| Model | Column | Cast Type | Purpose |
|-------|--------|-----------|---------|
| OauthClient | `client_secret_encrypted` | `encrypted` | Third-party OAuth client secrets |
| WebhookSubscription | `signing_secret_encrypted` | `encrypted` | Per-subscription signing secrets for outbound webhooks |
| Payment | `raw_json` | `encrypted:array` | Full payment gateway response (may contain sensitive card/account data) |

**Requirements:**
- Columns declared with `encrypted` or `encrypted:array` cast in model's `casts()` method
- Data automatically encrypted before writing, decrypted when reading
- Encrypted columns store opaque ciphertext (TEXT in SQLite)
- Cannot be queried with WHERE clauses at database level

#### 4.4 Input Validation

All write endpoints must use dedicated Form Request classes with string syntax validation rules.

**Form Request Inventory:**

| Form Request | Used By | Key Validation Rules |
|--------------|---------|---------------------|
| `StoreProductRequest` | Create product | `title`: required, string, max:255. `description_html`: nullable, string. `status`: required, in:active,draft,archived. `vendor`: nullable, string. Price fields: required, integer, min:0. |
| `UpdateProductRequest` | Update product | Same as StoreProductRequest but all optional |
| `StoreCollectionRequest` | Create collection | `title`: required, string, max:255. `type`: required, in:manual,automated. `rules_json`: required_if:type,automated, valid JSON. |
| `UpdateCollectionRequest` | Update collection | Same as StoreCollectionRequest, all optional |
| `StoreDiscountRequest` | Create discount | `type`: required, in:code,automatic. `code`: required_if:type,code, string, max:50, unique per store (case-insensitive). `value_type`: required, in:fixed,percent,free_shipping. `value_amount`: required_unless:value_type,free_shipping, integer, min:0; when percent, max:100. `minimum_order_amount`: nullable, integer, min:0. `usage_limit`: nullable, integer, min:1. `starts_at`: nullable, date, ISO-8601. `ends_at`: nullable, date, after:starts_at. |
| `UpdateDiscountRequest` | Update discount | Same as StoreDiscountRequest, code unique ignoring self |
| `SetCheckoutAddressRequest` | Set shipping/billing address | `first_name`, `last_name`, `address1`, `city`, `country_code`: required. `province_code`: required when applicable. `postal_code`: required. `phone`: nullable. |
| `ApplyDiscountRequest` | Apply discount to checkout | `code`: required, string, must exist in discounts table for current store |
| `CreateFulfillmentRequest` | Create fulfillment | `line_item_ids`: required, array of existing line item IDs. `tracking_company`: nullable, string. `tracking_number`: nullable, string. `tracking_url`: nullable, valid URL. |
| `CreateRefundRequest` | Create refund | `line_items`: required, array with `id` and `quantity` per item. `reason`: required, string. `amount`: required, integer, min:0, max:original payment amount. |
| `UpdateStoreSettingsRequest` | Update store settings | `name`: required, string. `default_currency`: in allowed currencies. `default_locale`: in allowed locales. `timezone`: valid timezone string. |
| `InviteStaffRequest` | Invite staff member | `email`: required, valid email. `role`: required, in:admin,staff,support (not owner). |
| `RegisterCustomerRequest` | Customer registration | `name`: required. `email`: required, email, unique per store (composite). `password`: required, min:8, confirmed. |
| `LoginRequest` | Admin/customer login | `email`: required, email. `password`: required, string. |

**Authorization in Form Requests:**
Each Form Request's `authorize()` method must check relevant policy or gate. If authorization fails, request rejected with 403 before validation runs.

#### 4.5 XSS Prevention

| Context | Strategy |
|---------|----------|
| Standard Blade output | Double-curly-brace syntax auto-escapes (Laravel default) |
| Rich HTML output | Unescaped syntax used only for trusted HTML: product `description_html`, page `body_html` |
| HTML input sanitization | HTMLPurifier (or equivalent) on save, before persisting to database |
| Livewire properties | Auto-escaped by default when rendered in Blade |
| JSON API responses | Laravel escapes JSON output by default |

**HTMLPurifier Sanitization (app/Actions/SanitizeHtml.php):**

**Allowed HTML elements and attributes:**

| Element | Allowed Attributes |
|---------|-------------------|
| `p`, `br`, `strong`, `em`, `u` | none |
| `ol`, `ul`, `li` | none |
| `a` | `href` |
| `img` | `src`, `alt` |
| `h1` through `h6` | none |
| `blockquote` | none |
| `table`, `thead`, `tbody`, `tr`, `th`, `td` | none |
| `div`, `span` | none |

**Rules:**
- All other elements and attributes stripped
- Empty elements removed
- Applied before persisting product `description_html` and page `body_html`
- Runs during create and update operations

#### 4.6 SQL Injection Prevention

**Requirements:**
- All database queries must use Eloquent ORM with parameterized bindings (Laravel default)
- No raw query strings may contain unsanitized user input
- Any use of `whereRaw`, `selectRaw`, or `DB::raw` must use parameter bindings (`?` placeholder syntax)

**FTS5 Input Sanitization:**
Before using user input in FTS5 MATCH expression:
1. Strip all characters that are not Unicode letters, Unicode numbers, or whitespace
2. Remove FTS5 special operators: `*`, `AND`, `OR`, `NOT`, `NEAR`, quotes, parentheses, etc.
3. Trim leading and trailing whitespace
4. Pass sanitized string via parameterized binding (never concatenate into query string)

#### 4.7 Mass Assignment Protection

**Requirements:**
- Every Eloquent model must define a `$fillable` array listing only safe columns for mass assignment
- Pattern `$guarded = []` never permitted
- All controller and Livewire action code must use `$request->validated()` (not `$request->all()` or `$request->input()`) when passing data to model creation/update methods

#### 4.8 Password Security

| Setting | Value |
|---------|-------|
| Minimum length | 8 characters (admin and customer) |
| Hashing algorithm | Bcrypt (Laravel default) |
| Password in logs | Never logged - use Laravel's built-in log redaction |
| Password in API responses | Never returned - must be in model's `$hidden` array |

**Model requirements:**
- Both `User` and `Customer` models must include `password` and `remember_token` in `$hidden` array
- Password validation rules must enforce min 8 characters and use `confirmed` rule
- **Critical:** Both models use `password_hash` as database column name (not Laravel default `password`)
- Both models must override `getAuthPassword()`:
  ```php
  public function getAuthPassword(): string
  {
      return $this->password_hash;
  }
  ```
  This is required for `Auth::attempt()` and `Hash::check()` to work with custom column name

---

### 5. SESSION CONFIGURATION (config/session.php)

| Setting | Value | Rationale |
|---------|-------|-----------|
| `driver` | `'file'` | Simple, no external dependencies. Compatible with SQLite-based stack |
| `lifetime` | 120 (minutes) | Configurable via `SESSION_LIFETIME` env var |
| `expire_on_close` | `false` | Session persists until lifetime expires, not on browser close |
| `encrypt` | `true` | Session data encrypted at rest on disk |
| `cookie` | `'shop_session'` | Custom name to avoid generic fingerprinting. Configurable via `SESSION_COOKIE` env var |
| `path` | `'/'` | Cookie available across entire domain |
| `domain` | `null` | Laravel determines from request |
| `secure` | `true` | HTTPS-only cookies in production. Configurable via `SESSION_SECURE_COOKIE` env var (set false for local dev) |
| `http_only` | `true` | Cookie inaccessible to JavaScript (mitigates XSS-based cookie theft) |
| `same_site` | `'lax'` | Allows navigation-initiated requests while preventing CSRF from external origins |
| `partitioned` | `false` | Not needed for this use case |

---

### 6. CORS CONFIGURATION (config/cors.php)

| Setting | Value | Rationale |
|---------|-------|-----------|
| `paths` | `['api/*', 'sanctum/csrf-cookie']` | Only API and Sanctum CSRF cookie endpoints are CORS-enabled. Web routes not. |
| `allowed_methods` | `['*']` | All HTTP methods allowed for API |
| `allowed_origins` | `[FRONTEND_URL env var, default '*']` | Restrict to store domain in production. Wildcard for development. |
| `allowed_origins_patterns` | `[]` | No pattern-based origin matching needed |
| `allowed_headers` | `['*']` | All headers allowed (Authorization, Content-Type, etc.) |
| `exposed_headers` | `['X-RateLimit-Limit', 'X-RateLimit-Remaining', 'Retry-After']` | Expose rate limit headers so API clients can implement backoff |
| `max_age` | `0` | No preflight caching (conservative default) |
| `supports_credentials` | `true` | Required for Sanctum SPA auth (cookies) |

---

### 7. WEBHOOK SIGNATURE VERIFICATION

#### 7.1 Inbound Payment Webhooks
**Not applicable.** All payment processing happens in-process via Mock PSP. No inbound payment webhooks from external providers (no Stripe, PayPal, etc.). This section preserved for reference only.

#### 7.2 Platform Webhooks (Outbound)
Service: `App\Services\WebhookDispatcher`

When platform sends webhooks to external subscribers (third-party apps, integrations):

**Signature Process:**
1. Serialize payload as JSON
2. Generate Unix timestamp
3. Concatenate: `"{timestamp}.{json_body}"`
4. Compute HMAC-SHA256(signing_secret, concatenated_message)
5. POST to subscriber URL with three headers:
   - `X-Platform-Signature` - hex-encoded HMAC digest
   - `X-Platform-Timestamp` - Unix timestamp as string
   - `Content-Type` - `application/json`

**Requirements:**
- Signature computed using per-subscription `signing_secret` (decrypted from `signing_secret_encrypted` column)
- Message format: `"{timestamp}.{json_body}"` (timestamp, dot, JSON string)
- HMAC algorithm: SHA-256
- **Replay protection guidance for recipients:** Reject any request where `X-Platform-Timestamp` is more than 5 minutes in the past

---

### 8. AUDIT LOGGING

#### 8.1 Logging Channel
File: `config/logging.php`

Add dedicated `audit` channel:

| Setting | Value |
|---------|-------|
| Driver | `daily` (file-based, daily rotation) |
| Path | `storage/logs/audit.log` |
| Level | `info` |
| Retention | 90 days |

#### 8.2 Log Entry Format
All audit log entries are structured JSON objects:

| Field | Type | Required | Description |
|-------|------|:--------:|-------------|
| `timestamp` | string (ISO-8601) | Y | When the event occurred |
| `event` | string | Y | Event identifier (e.g., `product.updated`, `auth.login`) |
| `user_id` | integer or null | N | ID of the admin user who triggered event |
| `store_id` | integer or null | N | ID of the store context |
| `resource_type` | string or null | N | Type of resource affected (e.g., `product`, `order`) |
| `resource_id` | integer or null | N | ID of the affected resource |
| `ip` | string | Y | Client IP address |
| `user_agent` | string | Y | Client user agent string |
| `changes` | object or null | N | For update events: object mapping field names to `[old_value, new_value]` tuples |
| (additional keys) | varies | N | Event-specific extra data |

#### 8.3 Events to Log

**Authentication Events:**

| Event ID | Trigger | Extra Data |
|----------|---------|------------|
| `auth.login` | Successful admin login | user_id, ip, user_agent |
| `auth.logout` | Admin logout | user_id |
| `auth.failed_login` | Failed admin login attempt | email (never password), ip, user_agent |
| `customer.login` | Successful customer login | customer_id, store_id, ip |
| `customer.registered` | New customer registration | customer_id, store_id |
| `customer.failed_login` | Failed customer login attempt | email, store_id, ip |

**Admin Resource Events:**
For each resource type (product, collection, discount, page, theme, order, fulfillment, refund, navigation_menu, shipping_zone, tax_setting, staff), log `created`, `updated`, `deleted`, and `restored` (where applicable):

| Event Pattern | Trigger | Extra Data |
|---------------|---------|------------|
| `{resource}.created` | Resource created | resource_id, key attributes of new resource |
| `{resource}.updated` | Resource updated | resource_id, `changes` object with old/new values for each changed field |
| `{resource}.deleted` | Resource soft-deleted or archived | resource_id |
| `{resource}.restored` | Resource restored from soft-delete | resource_id |

**Security Events:**

| Event ID | Trigger | Extra Data |
|----------|---------|------------|
| `api_token.created` | New API token issued | token_name, abilities list, user_id |
| `api_token.revoked` | API token revoked | token_name, user_id |
| `webhook.signature_invalid` | Outbound webhook delivery failed signature verification | subscriber_url, ip |
| `store.settings_changed` | Store settings updated | changed fields with old/new values |

#### 8.4 Audit Logger Service
File: `app/Services/AuditLogger.php`

Service class that encapsulates audit log writing.

**Method parameters:**

| Parameter | Type | Required | Description |
|-----------|------|:--------:|-------------|
| `event` | string | Y | Event identifier |
| `userId` | int or null | N | Acting user ID |
| `storeId` | int or null | N | Store context ID |
| `resourceType` | string or null | N | Affected resource type |
| `resourceId` | int or null | N | Affected resource ID |
| `extra` | associative array | N | Additional key-value pairs to include in log entry |

**Behavior:**
1. Write to `audit` logging channel at `info` level
2. Automatically include `timestamp` (ISO-8601), `ip` (from request), and `user_agent` (from request) in every entry
3. Merge any `extra` data into log context

#### 8.5 Integration with Laravel Events

**Auth Event Listeners** (register in `AppServiceProvider` or `EventServiceProvider`):

| Laravel Event | Audit Event ID | Data Captured |
|--------------|----------------|---------------|
| `Illuminate\Auth\Events\Login` | `auth.login` | user_id from event |
| `Illuminate\Auth\Events\Failed` | `auth.failed_login` | email from credentials (never password) |
| `Illuminate\Auth\Events\Logout` | `auth.logout` | user_id from event |

**Model Observers:**
For each auditable resource model, create an observer (or use shared trait) that logs `created`, `updated`, and `deleted` events. The `updated` observer should capture changed fields (dirty attributes) with original and new values.

---

### 9. SECURITY CHECKLIST SUMMARY

| # | Control | Implementation Approach | Priority |
|---|---------|------------------------|----------|
| 1 | Session-based auth (admin) | Laravel `web` guard with bcrypt, session regeneration on login | Required |
| 2 | Session-based auth (customer) | Custom `customer` guard, store-scoped via custom user provider | Required |
| 3 | API token auth | Sanctum personal access tokens with per-token abilities | Required |
| 4 | Role-based access control | `StoreUserRole` enum, Laravel policies on all models, gates for non-model ops | Required |
| 5 | CSRF protection | `@csrf` on all session forms, Livewire auto-handles, API routes exempt | Required |
| 6 | Rate limiting | Seven named limiters (login, api.admin, api.storefront, checkout, search, analytics, webhooks) | Required |
| 7 | Input validation | Dedicated Form Request class for every write endpoint | Required |
| 8 | XSS prevention | Blade auto-escaping by default, HTMLPurifier for rich content fields | Required |
| 9 | SQL injection prevention | Eloquent ORM with parameterized queries, FTS5 input sanitization | Required |
| 10 | Mass assignment protection | `$fillable` on all models, always use `$request->validated()` | Required |
| 11 | Encryption at rest | Laravel `encrypted` cast for secrets and sensitive JSON columns | Required |
| 12 | Password security | Bcrypt, min 8 characters, `$hidden` on User and Customer models | Required |
| 13 | Session security | Encrypted sessions, HTTP-only cookies, secure flag, SameSite=Lax | Required |
| 14 | Webhook signature verification | HMAC-SHA256 for outbound platform webhooks (no inbound payment webhooks - Mock PSP in-process) | Required |
| 15 | Audit logging | Structured JSON to dedicated daily-rotation log channel, 90-day retention | Required |
| 16 | CORS | Restricted to `api/*` paths, credentials supported, rate limit headers exposed | Required |

This comprehensive summary covers all authentication guards, custom providers, password reset flows, API token lifecycle, role-based permission matrices, middleware stack details, security controls including CSRF, rate limiting, encryption, input validation, XSS prevention, SQL injection prevention, session configuration, CORS settings, webhook signature verification, and complete audit logging specifications.

text:
agentId: adb8203 (for resuming to continue this agent's work if needed)
<usage>total_tokens: 55362
tool_uses: 3
duration_ms: 97913</usage>

status: completed

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