command: sed -n '130,245p' database/migrations/2026_08_20_000000_create_shop_schema.php; sed -n '300,470p' database/migrations/2026_08_20_000000_create_shop_schema.php; sed -n '510,575p' database/migrations/2026_08_20_000000_create_shop_schema.php; sed -n '1,150p' database/migrations/2026_08_20_220002_add_spec_compatibility_columns.php; sed -n '1,180p' database/migrations/2026_08_20_223045_add_remaining_schema_contract_columns.php

stdout:
        });

        Schema::create('inventory_items', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->foreignId('variant_id')->unique()->constrained('product_variants')->cascadeOnDelete();
            $table->integer('quantity_on_hand')->default(0);
            $table->integer('quantity_reserved')->default(0);
            $table->string('policy')->default('deny');
            $table->timestamps();
            $table->index('store_id');
        });

        Schema::create('collections', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->string('title');
            $table->string('handle');
            $table->text('description')->nullable();
            $table->string('status')->default('active')->index();
            $table->string('image_url')->nullable();
            $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']);
        });

        Schema::create('search_settings', function (Blueprint $table): void {
            $table->foreignId('store_id')->primary()->constrained()->cascadeOnDelete();
            $table->json('synonyms')->nullable();
            $table->json('stopwords')->nullable();
            $table->string('value_type')->default('percent');
            $table->unsignedInteger('value_amount')->default(0);
            $table->string('status')->default('active')->index();
            $table->unsignedInteger('usage_limit')->nullable();
            $table->unsignedInteger('usage_count')->default(0);
            $table->timestamp('starts_at')->nullable();
            $table->timestamp('ends_at')->nullable();
            $table->json('rules_json')->nullable();
            $table->timestamps();
            $table->unique(['store_id', 'code']);
            $table->index(['store_id', 'type']);
        });

        Schema::create('customers', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->string('first_name');
            $table->string('last_name');
            $table->string('email');
            $table->string('password_hash')->nullable();
            $table->string('status')->default('active')->index();
            $table->timestamp('email_verified_at')->nullable();
            $table->timestamp('last_login_at')->nullable();
            $table->rememberToken();
            $table->json('metadata')->nullable();
            $table->timestamps();
            $table->unique(['store_id', 'email']);
            $table->index('store_id');
        });

        Schema::create('customer_addresses', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('customer_id')->constrained()->cascadeOnDelete();
            $table->string('label')->nullable();
            $table->json('address_json');
            $table->boolean('is_default')->default(false);
            $table->timestamps();
            $table->index(['customer_id', 'is_default']);
        });

        Schema::create('carts', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->foreignId('customer_id')->nullable()->constrained()->nullOnDelete();
            $table->string('currency', 3)->default('EUR');
            $table->unsignedInteger('cart_version')->default(1);
            $table->string('status')->default('active')->index();
            $table->timestamps();
            $table->index('store_id');
            $table->index('customer_id');
        });

        Schema::create('cart_lines', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('cart_id')->constrained()->cascadeOnDelete();
            $table->foreignId('variant_id')->constrained('product_variants')->restrictOnDelete();
            $table->unsignedInteger('quantity');
            $table->unsignedInteger('unit_price_amount');
            $table->unsignedInteger('line_subtotal_amount');
            $table->unsignedInteger('line_discount_amount')->default(0);
            $table->unsignedInteger('line_total_amount');
            $table->timestamps();
            $table->unique(['cart_id', 'variant_id']);
            $table->index('cart_id');
        });

        Schema::create('checkouts', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->foreignId('cart_id')->constrained()->restrictOnDelete();
            $table->foreignId('customer_id')->nullable()->constrained()->nullOnDelete();
            $table->string('status')->default('started')->index();
            $table->string('email');
            $table->string('payment_method')->nullable();
            $table->json('shipping_address_json')->nullable();
            $table->json('billing_address_json')->nullable();
            $table->foreignId('shipping_rate_id')->nullable()->constrained('shipping_rates')->nullOnDelete();
            $table->string('discount_code')->nullable();
            $table->json('totals_json')->nullable();
            $table->json('tax_provider_snapshot_json')->nullable();
            $table->timestamp('expires_at')->nullable()->index();
            $table->timestamps();
            $table->index('cart_id');
            $table->index('customer_id');
        });

        Schema::create('orders', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->foreignId('customer_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('checkout_id')->nullable()->constrained()->nullOnDelete();
            $table->string('order_number');
            $table->string('currency', 3)->default('EUR');
            $table->string('status')->default('pending')->index();
            $table->string('financial_status')->default('pending')->index();
            $table->string('fulfillment_status')->default('unfulfilled')->index();
            $table->string('payment_method')->nullable();
            $table->string('email');
            $table->json('shipping_address_json')->nullable();
            $table->json('billing_address_json')->nullable();
            $table->unsignedInteger('subtotal_amount')->default(0);
            $table->unsignedInteger('discount_amount')->default(0);
            $table->unsignedInteger('shipping_amount')->default(0);
            $table->unsignedInteger('tax_amount')->default(0);
            $table->unsignedInteger('total_amount')->default(0);
            $table->timestamp('placed_at')->nullable()->index();
            $table->json('metadata')->nullable();
            $table->timestamps();
            $table->unique(['store_id', 'order_number']);
            $table->index(['store_id', 'status']);
            $table->index(['store_id', 'financial_status']);
            $table->index(['store_id', 'fulfillment_status']);
        });

        Schema::create('order_lines', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
            $table->foreignId('product_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('variant_id')->nullable()->constrained('product_variants')->nullOnDelete();
            $table->string('product_title');
            $table->string('variant_title')->nullable();
            $table->string('sku')->nullable();
            $table->unsignedInteger('quantity');
            $table->unsignedInteger('unit_price_amount');
            $table->unsignedInteger('line_subtotal_amount');
            $table->unsignedInteger('line_discount_amount')->default(0);
            $table->unsignedInteger('line_total_amount');
            $table->json('tax_lines_json')->nullable();
            $table->json('discount_allocations_json')->nullable();
            $table->timestamps();
            $table->index('order_id');
        });

        Schema::create('payments', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
            $table->string('provider')->default('mock');
            $table->string('provider_payment_id')->nullable();
            $table->string('method');
            $table->string('status')->default('pending')->index();
            $table->unsignedInteger('amount');
            $table->text('raw_json_encrypted')->nullable();
            $table->timestamps();
            $table->index(['provider', 'provider_payment_id']);
            $table->index('order_id');
        });

        Schema::create('refunds', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
            $table->foreignId('payment_id')->nullable()->constrained()->nullOnDelete();
            $table->unsignedInteger('amount');
            $table->string('status')->default('pending');
            $table->text('reason')->nullable();
            $table->boolean('restock')->default(false);
            $table->timestamps();
            $table->index(['order_id', 'status']);
        });

        Schema::create('fulfillments', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
            $table->string('status')->default('pending')->index();
            $table->string('tracking_company')->nullable();
            $table->string('tracking_number')->nullable();
            $table->text('tracking_url')->nullable();
            $table->timestamp('shipped_at')->nullable();
            $table->timestamp('delivered_at')->nullable();
            $table->timestamp('fulfilled_at')->nullable();
            $table->timestamps();
            $table->index(['tracking_company', 'tracking_number']);
            $table->string('name');
            $table->string('slug')->unique();
            $table->text('description')->nullable();
            $table->string('status')->default('active');
            $table->json('scopes')->nullable();
            $table->timestamps();
        });

        Schema::create('app_installations', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->foreignId('app_id')->constrained()->cascadeOnDelete();
            $table->string('status')->default('active');
            $table->json('config')->nullable();
            $table->timestamps();
            $table->unique(['store_id', 'app_id']);
        });

        Schema::create('oauth_clients', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->string('name');
            $table->string('client_id')->unique();
            $table->text('client_secret_encrypted');
            $table->text('redirect_uris');
            $table->timestamps();
        });

        Schema::create('oauth_tokens', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('oauth_client_id')->constrained()->cascadeOnDelete();
            $table->foreignId('user_id')->nullable()->constrained()->nullOnDelete();
            $table->string('token_hash')->unique();
            $table->json('scopes')->nullable();
            $table->timestamp('expires_at')->nullable();
            $table->timestamp('revoked_at')->nullable();
            $table->timestamps();
        });

        Schema::create('webhook_subscriptions', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->string('event');
            $table->text('target_url');
            $table->text('secret_encrypted');
            $table->string('status')->default('active');
            $table->unsignedInteger('consecutive_failures')->default(0);
            $table->timestamps();
            $table->index(['store_id', 'event']);
        });

        Schema::create('webhook_deliveries', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('webhook_subscription_id')->constrained()->cascadeOnDelete();
            $table->string('event');
            $table->json('payload');
            $table->unsignedInteger('attempts')->default(0);
            $table->unsignedSmallInteger('response_status')->nullable();
            $table->text('response_body')->nullable();
            $table->timestamp('delivered_at')->nullable();
            $table->timestamp('next_attempt_at')->nullable();
            $table->timestamps();
        });
    }

    public function down(): void
<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('products', function (Blueprint $table): void {
            $table->longText('description_html')->nullable();
        });
        Schema::table('product_variants', function (Blueprint $table): void {
            $table->string('currency', 3)->nullable();
            $table->unsignedInteger('weight_g')->nullable();
            $table->string('status')->default('active')->index();
        });
        Schema::table('product_media', function (Blueprint $table): void {
            $table->string('type')->default('image');
            $table->string('storage_key')->nullable();
            $table->string('mime_type')->nullable();
            $table->unsignedBigInteger('byte_size')->nullable();
            $table->string('checksum')->nullable();
        });
        Schema::table('shipping_rates', function (Blueprint $table): void {
            $table->foreignId('zone_id')->nullable()->constrained('shipping_zones')->nullOnDelete();
        });
        Schema::table('tax_settings', function (Blueprint $table): void {
            $table->string('provider')->default('none');
            $table->boolean('prices_include_tax')->default(false);
            $table->json('config_json')->nullable();
        });
        Schema::table('checkouts', function (Blueprint $table): void {
            $table->foreignId('shipping_method_id')->nullable()->constrained('shipping_rates')->nullOnDelete();
        });
        Schema::table('order_lines', function (Blueprint $table): void {
            $table->string('title_snapshot')->nullable();
            $table->string('sku_snapshot')->nullable();
        });
        Schema::table('refunds', function (Blueprint $table): void {
            $table->string('provider_refund_id')->nullable();
        });
    }

    public function down(): void
    {
        Schema::table('refunds', function (Blueprint $table): void {
            $table->dropColumn('provider_refund_id');
        });
        Schema::table('order_lines', function (Blueprint $table): void {
            $table->dropColumn(['title_snapshot', 'sku_snapshot']);
        });
        Schema::table('checkouts', function (Blueprint $table): void {
            $table->dropForeign(['shipping_method_id']);
            $table->dropColumn('shipping_method_id');
        });
        Schema::table('tax_settings', function (Blueprint $table): void {
            $table->dropColumn(['provider', 'prices_include_tax', 'config_json']);
        });
        Schema::table('shipping_rates', function (Blueprint $table): void {
            $table->dropForeign(['zone_id']);
            $table->dropColumn('zone_id');
        });
        Schema::table('product_media', function (Blueprint $table): void {
            $table->dropColumn(['type', 'storage_key', 'mime_type', 'byte_size', 'checksum']);
        });
        Schema::table('product_variants', function (Blueprint $table): void {
            $table->dropColumn(['currency', 'weight_g', 'status']);
        });
        Schema::table('products', function (Blueprint $table): void {
            $table->dropColumn('description_html');
        });
    }
};
<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('users', function (Blueprint $table): void {
            if (! Schema::hasColumn('users', 'password_hash')) {
                $table->text('password_hash')->nullable();
            }
        });
        Schema::table('customer_password_reset_tokens', function (Blueprint $table): void {
            if (! Schema::hasColumn('customer_password_reset_tokens', 'store_id')) {
                $table->foreignId('store_id')->nullable()->constrained('stores')->nullOnDelete();
            }
        });
        Schema::table('product_media', function (Blueprint $table): void {
            if (! Schema::hasColumn('product_media', 'width')) {
                $table->unsignedInteger('width')->nullable();
            }
            if (! Schema::hasColumn('product_media', 'height')) {
                $table->unsignedInteger('height')->nullable();
            }
        });
        Schema::table('theme_files', function (Blueprint $table): void {
            if (! Schema::hasColumn('theme_files', 'storage_key')) {
                $table->string('storage_key')->nullable();
            }
            if (! Schema::hasColumn('theme_files', 'sha256')) {
                $table->string('sha256')->nullable();
            }
            if (! Schema::hasColumn('theme_files', 'byte_size')) {
                $table->unsignedBigInteger('byte_size')->default(0);
            }
        });
        Schema::table('pages', function (Blueprint $table): void {
            if (! Schema::hasColumn('pages', 'body_html')) {
                $table->longText('body_html')->nullable();
            }
        });
        Schema::table('navigation_items', function (Blueprint $table): void {
            if (! Schema::hasColumn('navigation_items', 'menu_id')) {
                $table->unsignedBigInteger('menu_id')->nullable()->index();
            }
        });
        Schema::table('search_settings', function (Blueprint $table): void {
            if (! Schema::hasColumn('search_settings', 'synonyms_json')) {
                $table->json('synonyms_json')->nullable();
            }
            if (! Schema::hasColumn('search_settings', 'stop_words_json')) {
                $table->json('stop_words_json')->nullable();
            }
        });
        Schema::table('order_lines', function (Blueprint $table): void {
            if (! Schema::hasColumn('order_lines', 'total_amount')) {
                $table->unsignedInteger('total_amount')->nullable();
            }
        });
        Schema::table('payments', function (Blueprint $table): void {
            if (! Schema::hasColumn('payments', 'currency')) {
                $table->string('currency', 3)->default('USD');
            }
        });
        Schema::table('analytics_events', function (Blueprint $table): void {
            if (! Schema::hasColumn('analytics_events', 'properties_json')) {
                $table->json('properties_json')->nullable();
            }
            if (! Schema::hasColumn('analytics_events', 'occurred_at')) {
                $table->timestamp('occurred_at')->nullable();
            }
        });
        Schema::table('analytics_daily', function (Blueprint $table): void {
            if (! Schema::hasColumn('analytics_daily', 'checkout_completed_count')) {
                $table->unsignedInteger('checkout_completed_count')->default(0);
            }
        });
        Schema::table('webhook_subscriptions', function (Blueprint $table): void {
            if (! Schema::hasColumn('webhook_subscriptions', 'event_type')) {
                $table->string('event_type')->nullable()->index();
            }
            if (! Schema::hasColumn('webhook_subscriptions', 'app_installation_id')) {
                $table->unsignedBigInteger('app_installation_id')->nullable()->index();
            }
        });
        Schema::table('webhook_deliveries', function (Blueprint $table): void {
            if (! Schema::hasColumn('webhook_deliveries', 'subscription_id')) {
                $table->unsignedBigInteger('subscription_id')->nullable()->index();
            }
            if (! Schema::hasColumn('webhook_deliveries', 'event_id')) {
                $table->string('event_id')->nullable()->index();
            }
            if (! Schema::hasColumn('webhook_deliveries', 'attempt_count')) {
                $table->unsignedInteger('attempt_count')->default(0);
            }
            if (! Schema::hasColumn('webhook_deliveries', 'last_attempt_at')) {
                $table->timestamp('last_attempt_at')->nullable();
            }
            if (! Schema::hasColumn('webhook_deliveries', 'response_code')) {
                $table->unsignedSmallInteger('response_code')->nullable();
            }
            if (! Schema::hasColumn('webhook_deliveries', 'response_body_snippet')) {
                $table->text('response_body_snippet')->nullable();
            }
        });
    }

    public function down(): void
    {
        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) {
            if (Schema::hasColumn($tableName, $column)) {
                Schema::table($tableName, function (Blueprint $table) use ($column): void {
                    $table->dropColumn($column);
                });
            }
        }
    }
};


text:
Script completed
Wall time 0.1 seconds
Output:

text:
        });

        Schema::create('inventory_items', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->foreignId('variant_id')->unique()->constrained('product_variants')->cascadeOnDelete();
            $table->integer('quantity_on_hand')->default(0);
            $table->integer('quantity_reserved')->default(0);
            $table->string('policy')->default('deny');
            $table->timestamps();
            $table->index('store_id');
        });

        Schema::create('collections', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->string('title');
            $table->string('handle');
            $table->text('description')->nullable();
            $table->string('status')->default('active')->index();
            $table->string('image_url')->nullable();
            $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']);
        });

        Schema::create('search_settings', function (Blueprint $table): void {
            $table->foreignId('store_id')->primary()->constrained()->cascadeOnDelete();
            $table->json('synonyms')->nullable();
            $table->json('stopwords')->nullable();
            $table->string('value_type')->default('percent');
            $table->unsignedInteger('value_amount')->default(0);
            $table->string('status')->default('active')->index();
            $table->unsignedInteger('usage_limit')->nullable();
            $table->unsignedInteger('usage_count')->default(0);
            $table->timestamp('starts_at')->nullable();
            $table->timestamp('ends_at')->nullable();
            $table->json('rules_json')->nullable();
            $table->timestamps();
            $table->unique(['store_id', 'code']);
            $table->index(['store_id', 'type']);
        });

        Schema::create('customers', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->string('first_name');
            $table->string('last_name');
            $table->string('email');
            $table->string('password_hash')->nullable();
            $table->string('status')->default('active')->index();
            $table->timestamp('email_verified_at')->nullable();
            $table->timestamp('last_login_at')->nullable();
            $table->rememberToken();
            $table->json('metadata')->nullable();
            $table->timestamps();
            $table->unique(['store_id', 'email']);
            $table->index('store_id');
        });

        Schema::create('customer_addresses', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('customer_id')->constrained()->cascadeOnDelete();
            $table->string('label')->nullable();
            $table->json('address_json');
            $table->boolean('is_default')->default(false);
            $table->timestamps();
            $table->index(['customer_id', 'is_default']);
        });

        Schema::create('carts', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->foreignId('customer_id')->nullable()->constrained()->nullOnDelete();
            $table->string('currency', 3)->default('EUR');
            $table->unsignedInteger('cart_version')->default(1);
            $table->string('status')->default('active')->index();
            $table->timestamps();
            $table->index('store_id');
            $table->index('customer_id');
        });

        Schema::create('cart_lines', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('cart_id')->constrained()->cascadeOnDelete();
            $table->foreignId('variant_id')->constrained('product_variants')->restrictOnDelete();
            $table->unsignedInteger('quantity');
            $table->unsignedInteger('unit_price_amount');
            $table->unsignedInteger('line_subtotal_amount');
            $table->unsignedInteger('line_discount_amount')->default(0);
            $table->unsignedInteger('line_total_amount');
            $table->timestamps();
            $table->unique(['cart_id', 'variant_id']);
            $table->index('cart_id');
        });

        Schema::create('checkouts', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->foreignId('cart_id')->constrained()->restrictOnDelete();
            $table->foreignId('customer_id')->nullable()->constrained()->nullOnDelete();
            $table->string('status')->default('started')->index();
            $table->string('email');
            $table->string('payment_method')->nullable();
            $table->json('shipping_address_json')->nullable();
            $table->json('billing_address_json')->nullable();
            $table->foreignId('shipping_rate_id')->nullable()->constrained('shipping_rates')->nullOnDelete();
            $table->string('discount_code')->nullable();
            $table->json('totals_json')->nullable();
            $table->json('tax_provider_snapshot_json')->nullable();
            $table->timestamp('expires_at')->nullable()->index();
            $table->timestamps();
            $table->index('cart_id');
            $table->index('customer_id');
        });

        Schema::create('orders', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->foreignId('customer_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('checkout_id')->nullable()->constrained()->nullOnDelete();
            $table->string('order_number');
            $table->string('currency', 3)->default('EUR');
            $table->string('status')->default('pending')->index();
            $table->string('financial_status')->default('pending')->index();
            $table->string('fulfillment_status')->default('unfulfilled')->index();
            $table->string('payment_method')->nullable();
            $table->string('email');
            $table->json('shipping_address_json')->nullable();
            $table->json('billing_address_json')->nullable();
            $table->unsignedInteger('subtotal_amount')->default(0);
            $table->unsignedInteger('discount_amount')->default(0);
            $table->unsignedInteger('shipping_amount')->default(0);
            $table->unsignedInteger('tax_amount')->default(0);
            $table->unsignedInteger('total_amount')->default(0);
            $table->timestamp('placed_at')->nullable()->index();
            $table->json('metadata')->nullable();
            $table->timestamps();
            $table->unique(['store_id', 'order_number']);
            $table->index(['store_id', 'status']);
            $table->index(['store_id', 'financial_status']);
            $table->index(['store_id', 'fulfillment_status']);
        });

        Schema::create('order_lines', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
            $table->foreignId('product_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('variant_id')->nullable()->constrained('product_variants')->nullOnDelete();
            $table->string('product_title');
            $table->string('variant_title')->nullable();
            $table->string('sku')->nullable();
            $table->unsignedInteger('quantity');
            $table->unsignedInteger('unit_price_amount');
            $table->unsignedInteger('line_subtotal_amount');
            $table->unsignedInteger('line_discount_amount')->default(0);
            $table->unsignedInteger('line_total_amount');
            $table->json('tax_lines_json')->nullable();
            $table->json('discount_allocations_json')->nullable();
            $table->timestamps();
            $table->index('order_id');
        });

        Schema::create('payments', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
            $table->string('provider')->default('mock');
            $table->string('provider_payment_id')->nullable();
            $table->string('method');
            $table->string('status')->default('pending')->index();
            $table->unsignedInteger('amount');
            $table->text('raw_json_encrypted')->nullable();
            $table->timestamps();
            $table->index(['provider', 'provider_payment_id']);
            $table->index('order_id');
        });

        Schema::create('refunds', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
            $table->foreignId('payment_id')->nullable()->constrained()->nullOnDelete();
            $table->unsignedInteger('amount');
            $table->string('status')->default('pending');
            $table->text('reason')->nullable();
            $table->boolean('restock')->default(false);
            $table->timestamps();
            $table->index(['order_id', 'status']);
        });

        Schema::create('fulfillments', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('order_id')->constrained()->cascadeOnDelete();
            $table->string('status')->default('pending')->index();
            $table->string('tracking_company')->nullable();
            $table->string('tracking_number')->nullable();
            $table->text('tracking_url')->nullable();
            $table->timestamp('shipped_at')->nullable();
            $table->timestamp('delivered_at')->nullable();
            $table->timestamp('fulfilled_at')->nullable();
            $table->timestamps();
            $table->index(['tracking_company', 'tracking_number']);
            $table->string('name');
            $table->string('slug')->unique();
            $table->text('description')->nullable();
            $table->string('status')->default('active');
            $table->json('scopes')->nullable();
            $table->timestamps();
        });

        Schema::create('app_installations', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->foreignId('app_id')->constrained()->cascadeOnDelete();
            $table->string('status')->default('active');
            $table->json('config')->nullable();
            $table->timestamps();
            $table->unique(['store_id', 'app_id']);
        });

        Schema::create('oauth_clients', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->string('name');
            $table->string('client_id')->unique();
            $table->text('client_secret_encrypted');
            $table->text('redirect_uris');
            $table->timestamps();
        });

        Schema::create('oauth_tokens', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('oauth_client_id')->constrained()->cascadeOnDelete();
            $table->foreignId('user_id')->nullable()->constrained()->nullOnDelete();
            $table->string('token_hash')->unique();
            $table->json('scopes')->nullable();
            $table->timestamp('expires_at')->nullable();
            $table->timestamp('revoked_at')->nullable();
            $table->timestamps();
        });

        Schema::create('webhook_subscriptions', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('store_id')->constrained()->cascadeOnDelete();
            $table->string('event');
            $table->text('target_url');
            $table->text('secret_encrypted');
            $table->string('status')->default('active');
            $table->unsignedInteger('consecutive_failures')->default(0);
            $table->timestamps();
            $table->index(['store_id', 'event']);
        });

        Schema::create('webhook_deliveries', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('webhook_subscription_id')->constrained()->cascadeOnDelete();
            $table->string('event');
            $table->json('payload');
            $table->unsignedInteger('attempts')->default(0);
            $table->unsignedSmallInteger('response_status')->nullable();
            $table->text('response_body')->nullable();
            $table->timestamp('delivered_at')->nullable();
            $table->timestamp('next_attempt_at')->nullable();
            $table->timestamps();
        });
    }

    public function down(): void
<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('products', function (Blueprint $table): void {
            $table->longText('description_html')->nullable();
        });
        Schema::table('product_variants', function (Blueprint $table): void {
            $table->string('currency', 3)->nullable();
            $table->unsignedInteger('weight_g')->nullable();
            $table->string('status')->default('active')->index();
        });
        Schema::table('product_media', function (Blueprint $table): void {
            $table->string('type')->default('image');
            $table->string('storage_key')->nullable();
            $table->string('mime_type')->nullable();
            $table->unsignedBigInteger('byte_size')->nullable();
            $table->string('checksum')->nullable();
        });
        Schema::table('shipping_rates', function (Blueprint $table): void {
            $table->foreignId('zone_id')->nullable()->constrained('shipping_zones')->nullOnDelete();
        });
        Schema::table('tax_settings', function (Blueprint $table): void {
            $table->string('provider')->default('none');
            $table->boolean('prices_include_tax')->default(false);
            $table->json('config_json')->nullable();
        });
        Schema::table('checkouts', function (Blueprint $table): void {
            $table->foreignId('shipping_method_id')->nullable()->constrained('shipping_rates')->nullOnDelete();
        });
        Schema::table('order_lines', function (Blueprint $table): void {
            $table->string('title_snapshot')->nullable();
            $table->string('sku_snapshot')->nullable();
        });
        Schema::table('refunds', function (Blueprint $table): void {
            $table->string('provider_refund_id')->nullable();
        });
    }

    public function down(): void
    {
        Schema::table('refunds', function (Blueprint $table): void {
            $table->dropColumn('provider_refund_id');
        });
        Schema::table('order_lines', function (Blueprint $table): void {
            $table->dropColumn(['title_snapshot', 'sku_snapshot']);
        });
        Schema::table('checkouts', function (Blueprint $table): void {
            $table->dropForeign(['shipping_method_id']);
            $table->dropColumn('shipping_method_id');
        });
        Schema::table('tax_settings', function (Blueprint $table): void {
            $table->dropColumn(['provider', 'prices_include_tax', 'config_json']);
        });
        Schema::table('shipping_rates', function (Blueprint $table): void {
            $table->dropForeign(['zone_id']);
            $table->dropColumn('zone_id');
        });
        Schema::table('product_media', function (Blueprint $table): void {
            $table->dropColumn(['type', 'storage_key', 'mime_type', 'byte_size', 'checksum']);
        });
        Schema::table('product_variants', function (Blueprint $table): void {
            $table->dropColumn(['currency', 'weight_g', 'status']);
        });
        Schema::table('products', function (Blueprint $table): void {
            $table->dropColumn('description_html');
        });
    }
};
<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('users', function (Blueprint $table): void {
            if (! Schema::hasColumn('users', 'password_hash')) {
                $table->text('password_hash')->nullable();
            }
        });
        Schema::table('customer_password_reset_tokens', function (Blueprint $table): void {
            if (! Schema::hasColumn('customer_password_reset_tokens', 'store_id')) {
                $table->foreignId('store_id')->nullable()->constrained('stores')->nullOnDelete();
            }
        });
        Schema::table('product_media', function (Blueprint $table): void {
            if (! Schema::hasColumn('product_media', 'width')) {
                $table->unsignedInteger('width')->nullable();
            }
            if (! Schema::hasColumn('product_media', 'height')) {
                $table->unsignedInteger('height')->nullable();
            }
        });
        Schema::table('theme_files', function (Blueprint $table): void {
            if (! Schema::hasColumn('theme_files', 'storage_key')) {
                $table->string('storage_key')->nullable();
            }
            if (! Schema::hasColumn('theme_files', 'sha256')) {
                $table->string('sha256')->nullable();
            }
            if (! Schema::hasColumn('theme_files', 'byte_size')) {
                $table->unsignedBigInteger('byte_size')->default(0);
            }
        });
        Schema::table('pages', function (Blueprint $table): void {
            if (! Schema::hasColumn('pages', 'body_html')) {
                $table->longText('body_html')->nullable();
            }
        });
        Schema::table('navigation_items', function (Blueprint $table): void {
            if (! Schema::hasColumn('navigation_items', 'menu_id')) {
                $table->unsignedBigInteger('menu_id')->nullable()->index();
            }
        });
        Schema::table('search_settings', function (Blueprint $table): void {
            if (! Schema::hasColumn('search_settings', 'synonyms_json')) {
                $table->json('synonyms_json')->nullable();
            }
            if (! Schema::hasColumn('search_settings', 'stop_words_json')) {
                $table->json('stop_words_json')->nullable();
            }
        });
        Schema::table('order_lines', function (Blueprint $table): void {
            if (! Schema::hasColumn('order_lines', 'total_amount')) {
                $table->unsignedInteger('total_amount')->nullable();
            }
        });
        Schema::table('payments', function (Blueprint $table): void {
            if (! Schema::hasColumn('payments', 'currency')) {
                $table->string('currency', 3)->default('USD');
            }
        });
        Schema::table('analytics_events', function (Blueprint $table): void {
            if (! Schema::hasColumn('analytics_events', 'properties_json')) {
                $table->json('properties_json')->nullable();
            }
            if (! Schema::hasColumn('analytics_events', 'occurred_at')) {
                $table->timestamp('occurred_at')->nullable();
            }
        });
        Schema::table('analytics_daily', function (Blueprint $table): void {
            if (! Schema::hasColumn('analytics_daily', 'checkout_completed_count')) {
                $table->unsignedInteger('checkout_completed_count')->default(0);
            }
        });
        Schema::table('webhook_subscriptions', function (Blueprint $table): void {
            if (! Schema::hasColumn('webhook_subscriptions', 'event_type')) {
                $table->string('event_type')->nullable()->index();
            }
            if (! Schema::hasColumn('webhook_subscriptions', 'app_installation_id')) {
                $table->unsignedBigInteger('app_installation_id')->nullable()->index();
            }
        });
        Schema::table('webhook_deliveries', function (Blueprint $table): void {
            if (! Schema::hasColumn('webhook_deliveries', 'subscription_id')) {
                $table->unsignedBigInteger('subscription_id')->nullable()->index();
            }
            if (! Schema::hasColumn('webhook_deliveries', 'event_id')) {
                $table->string('event_id')->nullable()->index();
            }
            if (! Schema::hasColumn('webhook_deliveries', 'attempt_count')) {
                $table->unsignedInteger('attempt_count')->default(0);
            }
            if (! Schema::hasColumn('webhook_deliveries', 'last_attempt_at')) {
                $table->timestamp('last_attempt_at')->nullable();
            }
            if (! Schema::hasColumn('webhook_deliveries', 'response_code')) {
                $table->unsignedSmallInteger('response_code')->nullable();
            }
            if (! Schema::hasColumn('webhook_deliveries', 'response_body_snippet')) {
                $table->text('response_body_snippet')->nullable();
            }
        });
    }

    public function down(): void
    {
        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) {
            if (Schema::hasColumn($tableName, $column)) {
                Schema::table($tableName, function (Blueprint $table) use ($column): void {
                    $table->dropColumn($column);
                });
            }
        }
    }
};


exec

status: completed

input:
const r = await tools.exec_command({cmd:"sed -n '620,920p' specs/01-DATABASE-SCHEMA.md; rg -n \"class (Collection|NavigationMenu|NavigationItem|SearchQuery|Theme|Customer|Refund)\" app/Models -n && for f in app/Models/{Collection,NavigationMenu,NavigationItem,SearchQuery,Theme,Customer,Refund}.php; do echo ===$f; sed -n '1,140p' \"$f\"; done","workdir":"/Users/wesner/Herd/shop","yield_time_ms":1000,"max_output_tokens":22000});
text(r.output);


type: CommandExecution

command: sed -n '620,920p' specs/01-DATABASE-SCHEMA.md; rg -n "class (Collection|NavigationMenu|NavigationItem|SearchQuery|Theme|Customer|Refund)" app/Models -n && for f in app/Models/{Collection,NavigationMenu,NavigationItem,SearchQuery,Theme,Customer,Refund}.php; do echo ===$f; sed -n '1,140p' "$f"; done

type: unknown

status: failed

stdout:
| name | TEXT | No | - | - | Zone display name |
| countries_json | TEXT | No | '[]' | - | JSON array of ISO 3166-1 alpha-2 codes, e.g. ["US","CA"] |
| regions_json | TEXT | No | '[]' | - | JSON array of region/state codes, e.g. ["US-NY","US-CA"] |

**Indexes:**

- `idx_shipping_zones_store_id` on (`store_id`) - FK lookup, listing zones per store

**Notes:** `countries_json` is an array of ISO 3166-1 alpha-2 codes. `regions_json` is an array of region/state codes.

---

### shipping_rates

Shipping rate options within a zone.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| zone_id | INTEGER | No | - | FK -> shipping_zones(id) ON DELETE CASCADE | Parent zone |
| name | TEXT | No | - | - | Rate display name |
| type | TEXT | No | 'flat' | CHECK IN ('flat', 'weight', 'price', 'carrier') | Calculation method |
| config_json | TEXT | No | '{}' | - | Type-dependent configuration (see notes) |
| is_active | INTEGER | No | 1 | - | Boolean: active flag |

**Indexes:**

- `idx_shipping_rates_zone_id` on (`zone_id`) - FK lookup, listing rates per zone
- `idx_shipping_rates_zone_active` on (`zone_id`, `is_active`) - Finding active rates per zone

**Notes:** `config_json` structure depends on `type`:
- Flat: `{"amount": 799}`
- Weight: `{"ranges": [{"min_g": 0, "max_g": 500, "amount": 499}]}`
- Price: `{"ranges": [{"min_amount": 0, "max_amount": 5000, "amount": 799}]}`
- Carrier: `{"carrier": "ups", "service": "ground"}`

---

### tax_settings

Per-store tax configuration.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| store_id | INTEGER | No | - | PRIMARY KEY, FK -> stores(id) ON DELETE CASCADE | One-to-one with stores |
| mode | TEXT | No | 'manual' | CHECK IN ('manual', 'provider') | Tax calculation mode |
| provider | TEXT | No | 'none' | CHECK IN ('stripe_tax', 'none') | External tax provider |
| prices_include_tax | INTEGER | No | 0 | - | Boolean: tax-inclusive pricing |
| config_json | TEXT | No | '{}' | - | Provider-specific config |

---

### discounts

Discount codes and automatic discounts.

| 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 | 'code' | CHECK IN ('code', 'automatic') | Activation method |
| code | TEXT | Yes | NULL | UNIQUE per (store_id, code) | Discount code (null for automatic) |
| value_type | TEXT | No | - | CHECK IN ('fixed', 'percent', 'free_shipping') | Discount calculation type |
| value_amount | INTEGER | No | 0 | - | See notes for interpretation |
| starts_at | TEXT | No | - | - | ISO-8601 start timestamp |
| ends_at | TEXT | Yes | NULL | - | ISO-8601 end timestamp; null = no expiry |
| usage_limit | INTEGER | Yes | NULL | - | Max redemptions; null = unlimited |
| usage_count | INTEGER | No | 0 | - | Current redemption count |
| rules_json | TEXT | No | '{}' | - | Eligibility rules (min purchase, products, etc.) |
| status | TEXT | No | 'active' | CHECK IN ('draft', 'active', 'expired', 'disabled') | |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_discounts_store_code` UNIQUE on (`store_id`, `code`) - Unique discount code per store
- `idx_discounts_store_id` on (`store_id`) - FK lookup
- `idx_discounts_store_status` on (`store_id`, `status`) - Filtering discounts by status
- `idx_discounts_store_type` on (`store_id`, `type`) - Filtering by code vs automatic

**Notes:** `value_amount` interpretation depends on `value_type`. For `percent`, it is a whole number (e.g. 15 = 15%). For `fixed`, it is in minor units (cents). For `free_shipping`, it is ignored. `rules_json` may contain minimum purchase amounts, applicable product/collection IDs, customer eligibility, etc.

---

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

### customers

Storefront customer accounts, scoped to a store.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| email | TEXT | No | - | UNIQUE per (store_id, email) | Customer email |
| password_hash | TEXT | Yes | NULL | - | Nullable for guest checkout customers |
| name | TEXT | Yes | NULL | - | Display name |
| marketing_opt_in | INTEGER | No | 0 | - | Boolean: marketing consent |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_customers_store_email` UNIQUE on (`store_id`, `email`) - Unique email per store
- `idx_customers_store_id` on (`store_id`) - FK lookup, listing customers per store

**Notes:** `password_hash` is nullable to allow guest checkout customers who may later claim their account. Email is unique per store, not globally.

---

### customer_addresses

Saved addresses for a customer.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| customer_id | INTEGER | No | - | FK -> customers(id) ON DELETE CASCADE | Owning customer |
| label | TEXT | Yes | NULL | - | User label, e.g. "Home", "Work" |
| address_json | TEXT | No | '{}' | - | JSON address object (see notes) |
| is_default | INTEGER | No | 0 | - | Boolean: default address flag |

**Indexes:**

- `idx_customer_addresses_customer_id` on (`customer_id`) - FK lookup, listing addresses per customer
- `idx_customer_addresses_default` on (`customer_id`, `is_default`) - Quick lookup of default address

**Notes:** `address_json` structure: `{"first_name": "", "last_name": "", "company": "", "address1": "", "address2": "", "city": "", "province": "", "province_code": "", "country": "", "country_code": "", "zip": "", "phone": ""}`.

---

### orders

Placed orders with financial and fulfillment tracking.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| customer_id | INTEGER | Yes | NULL | FK -> customers(id) ON DELETE SET NULL | Nullable for guest orders |
| order_number | TEXT | No | - | UNIQUE per (store_id, order_number) | Human-readable order number |
| payment_method | TEXT | No | - | CHECK IN ('credit_card', 'paypal', 'bank_transfer') | Payment method used for this order |
| status | TEXT | No | 'pending' | CHECK IN ('pending', 'paid', 'fulfilled', 'cancelled', 'refunded') | Overall order status |
| financial_status | TEXT | No | 'pending' | CHECK IN ('pending', 'authorized', 'paid', 'partially_refunded', 'refunded', 'voided') | Payment status |
| fulfillment_status | TEXT | No | 'unfulfilled' | CHECK IN ('unfulfilled', 'partial', 'fulfilled') | Shipping status |
| currency | TEXT | No | 'USD' | - | ISO 4217 currency code |
| subtotal_amount | INTEGER | No | 0 | - | Sum of line items in minor units |
| discount_amount | INTEGER | No | 0 | - | Total discount in minor units |
| shipping_amount | INTEGER | No | 0 | - | Shipping cost in minor units |
| tax_amount | INTEGER | No | 0 | - | Total tax in minor units |
| total_amount | INTEGER | No | 0 | - | Grand total in minor units |
| email | TEXT | Yes | NULL | - | Contact email at time of order |
| billing_address_json | TEXT | Yes | NULL | - | JSON address snapshot |
| shipping_address_json | TEXT | Yes | NULL | - | JSON address snapshot |
| placed_at | TEXT | Yes | NULL | - | ISO-8601 timestamp of placement |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_orders_store_order_number` UNIQUE on (`store_id`, `order_number`) - Unique order number per store
- `idx_orders_store_id` on (`store_id`) - FK lookup
- `idx_orders_customer_id` on (`customer_id`) - Listing orders per customer
- `idx_orders_store_status` on (`store_id`, `status`) - Filtering by overall status
- `idx_orders_store_financial` on (`store_id`, `financial_status`) - Filtering by payment status
- `idx_orders_store_fulfillment` on (`store_id`, `fulfillment_status`) - Filtering by shipping status
- `idx_orders_placed_at` on (`store_id`, `placed_at`) - Date-range order queries

---

### 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

**Notes:** `raw_json_encrypted` stores the mock payment provider response, encrypted at the application layer. `amount` is in minor units (cents). The `provider` is always `mock` since the platform uses a mock PSP (no real payment gateway integration). The `method` column records which payment method the customer selected (credit_card, paypal, or bank_transfer).

---

### refunds

Refund records linked to an order and payment.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |
| payment_id | INTEGER | No | - | FK -> payments(id) ON DELETE CASCADE | Source payment |
| amount | INTEGER | No | 0 | - | Refund amount in minor units |
| reason | TEXT | Yes | NULL | - | Refund reason |
| status | TEXT | No | 'pending' | CHECK IN ('pending', 'processed', 'failed') | |
| provider_refund_id | TEXT | Yes | NULL | - | External refund ID |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_refunds_order_id` on (`order_id`) - FK lookup, listing refunds per order
- `idx_refunds_payment_id` on (`payment_id`) - Finding refunds per payment
- `idx_refunds_status` on (`status`) - Filtering refunds by status

---

### fulfillments

Shipment tracking for order fulfillment.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |
| status | TEXT | No | 'pending' | CHECK IN ('pending', 'shipped', 'delivered') | |
| tracking_company | TEXT | Yes | NULL | - | Carrier name |
| tracking_number | TEXT | Yes | NULL | - | Tracking number |
| tracking_url | TEXT | Yes | NULL | - | Tracking page URL |
| shipped_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_fulfillments_order_id` on (`order_id`) - FK lookup, listing fulfillments per order
- `idx_fulfillments_status` on (`status`) - Filtering fulfillments by status
- `idx_fulfillments_tracking` on (`tracking_company`, `tracking_number`) - Tracking number lookup

---

### fulfillment_lines

Links specific order lines and quantities to a fulfillment.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| fulfillment_id | INTEGER | No | - | FK -> fulfillments(id) ON DELETE CASCADE | Parent fulfillment |
| order_line_id | INTEGER | No | - | FK -> order_lines(id) ON DELETE CASCADE | Source order line |
| quantity | INTEGER | No | 1 | - | Units fulfilled from this line |

**Indexes:**

- `idx_fulfillment_lines_fulfillment_id` on (`fulfillment_id`) - FK lookup, listing lines per fulfillment
- `idx_fulfillment_lines_fulfillment_order_line` UNIQUE on (`fulfillment_id`, `order_line_id`) - Prevents duplicate line entries per fulfillment

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

---

## Epic 7 - Analytics

### analytics_events

Raw event stream for storefront analytics.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
app/Models/CustomerAddress.php:8:class CustomerAddress extends Model
app/Models/SearchQuery.php:8:class SearchQuery extends Model
app/Models/ThemeSetting.php:8:class ThemeSetting extends Model
app/Models/Collection.php:10:class Collection extends Model
app/Models/Customer.php:13:class Customer extends Model implements Authenticatable
app/Models/Theme.php:10:class Theme extends Model
app/Models/NavigationMenu.php:9:class NavigationMenu extends Model
app/Models/Refund.php:8:class Refund extends Model
app/Models/NavigationItem.php:8:class NavigationItem extends Model
app/Models/ThemeFile.php:8:class ThemeFile extends Model
zsh:1: ==app/Models/Collection.php not found


text:
Script completed
Wall time 0.1 seconds
Output:

text:
| name | TEXT | No | - | - | Zone display name |
| countries_json | TEXT | No | '[]' | - | JSON array of ISO 3166-1 alpha-2 codes, e.g. ["US","CA"] |
| regions_json | TEXT | No | '[]' | - | JSON array of region/state codes, e.g. ["US-NY","US-CA"] |

**Indexes:**

- `idx_shipping_zones_store_id` on (`store_id`) - FK lookup, listing zones per store

**Notes:** `countries_json` is an array of ISO 3166-1 alpha-2 codes. `regions_json` is an array of region/state codes.

---

### shipping_rates

Shipping rate options within a zone.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| zone_id | INTEGER | No | - | FK -> shipping_zones(id) ON DELETE CASCADE | Parent zone |
| name | TEXT | No | - | - | Rate display name |
| type | TEXT | No | 'flat' | CHECK IN ('flat', 'weight', 'price', 'carrier') | Calculation method |
| config_json | TEXT | No | '{}' | - | Type-dependent configuration (see notes) |
| is_active | INTEGER | No | 1 | - | Boolean: active flag |

**Indexes:**

- `idx_shipping_rates_zone_id` on (`zone_id`) - FK lookup, listing rates per zone
- `idx_shipping_rates_zone_active` on (`zone_id`, `is_active`) - Finding active rates per zone

**Notes:** `config_json` structure depends on `type`:
- Flat: `{"amount": 799}`
- Weight: `{"ranges": [{"min_g": 0, "max_g": 500, "amount": 499}]}`
- Price: `{"ranges": [{"min_amount": 0, "max_amount": 5000, "amount": 799}]}`
- Carrier: `{"carrier": "ups", "service": "ground"}`

---

### tax_settings

Per-store tax configuration.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| store_id | INTEGER | No | - | PRIMARY KEY, FK -> stores(id) ON DELETE CASCADE | One-to-one with stores |
| mode | TEXT | No | 'manual' | CHECK IN ('manual', 'provider') | Tax calculation mode |
| provider | TEXT | No | 'none' | CHECK IN ('stripe_tax', 'none') | External tax provider |
| prices_include_tax | INTEGER | No | 0 | - | Boolean: tax-inclusive pricing |
| config_json | TEXT | No | '{}' | - | Provider-specific config |

---

### discounts

Discount codes and automatic discounts.

| 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 | 'code' | CHECK IN ('code', 'automatic') | Activation method |
| code | TEXT | Yes | NULL | UNIQUE per (store_id, code) | Discount code (null for automatic) |
| value_type | TEXT | No | - | CHECK IN ('fixed', 'percent', 'free_shipping') | Discount calculation type |
| value_amount | INTEGER | No | 0 | - | See notes for interpretation |
| starts_at | TEXT | No | - | - | ISO-8601 start timestamp |
| ends_at | TEXT | Yes | NULL | - | ISO-8601 end timestamp; null = no expiry |
| usage_limit | INTEGER | Yes | NULL | - | Max redemptions; null = unlimited |
| usage_count | INTEGER | No | 0 | - | Current redemption count |
| rules_json | TEXT | No | '{}' | - | Eligibility rules (min purchase, products, etc.) |
| status | TEXT | No | 'active' | CHECK IN ('draft', 'active', 'expired', 'disabled') | |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_discounts_store_code` UNIQUE on (`store_id`, `code`) - Unique discount code per store
- `idx_discounts_store_id` on (`store_id`) - FK lookup
- `idx_discounts_store_status` on (`store_id`, `status`) - Filtering discounts by status
- `idx_discounts_store_type` on (`store_id`, `type`) - Filtering by code vs automatic

**Notes:** `value_amount` interpretation depends on `value_type`. For `percent`, it is a whole number (e.g. 15 = 15%). For `fixed`, it is in minor units (cents). For `free_shipping`, it is ignored. `rules_json` may contain minimum purchase amounts, applicable product/collection IDs, customer eligibility, etc.

---

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

### customers

Storefront customer accounts, scoped to a store.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| email | TEXT | No | - | UNIQUE per (store_id, email) | Customer email |
| password_hash | TEXT | Yes | NULL | - | Nullable for guest checkout customers |
| name | TEXT | Yes | NULL | - | Display name |
| marketing_opt_in | INTEGER | No | 0 | - | Boolean: marketing consent |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_customers_store_email` UNIQUE on (`store_id`, `email`) - Unique email per store
- `idx_customers_store_id` on (`store_id`) - FK lookup, listing customers per store

**Notes:** `password_hash` is nullable to allow guest checkout customers who may later claim their account. Email is unique per store, not globally.

---

### customer_addresses

Saved addresses for a customer.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| customer_id | INTEGER | No | - | FK -> customers(id) ON DELETE CASCADE | Owning customer |
| label | TEXT | Yes | NULL | - | User label, e.g. "Home", "Work" |
| address_json | TEXT | No | '{}' | - | JSON address object (see notes) |
| is_default | INTEGER | No | 0 | - | Boolean: default address flag |

**Indexes:**

- `idx_customer_addresses_customer_id` on (`customer_id`) - FK lookup, listing addresses per customer
- `idx_customer_addresses_default` on (`customer_id`, `is_default`) - Quick lookup of default address

**Notes:** `address_json` structure: `{"first_name": "", "last_name": "", "company": "", "address1": "", "address2": "", "city": "", "province": "", "province_code": "", "country": "", "country_code": "", "zip": "", "phone": ""}`.

---

### orders

Placed orders with financial and fulfillment tracking.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| customer_id | INTEGER | Yes | NULL | FK -> customers(id) ON DELETE SET NULL | Nullable for guest orders |
| order_number | TEXT | No | - | UNIQUE per (store_id, order_number) | Human-readable order number |
| payment_method | TEXT | No | - | CHECK IN ('credit_card', 'paypal', 'bank_transfer') | Payment method used for this order |
| status | TEXT | No | 'pending' | CHECK IN ('pending', 'paid', 'fulfilled', 'cancelled', 'refunded') | Overall order status |
| financial_status | TEXT | No | 'pending' | CHECK IN ('pending', 'authorized', 'paid', 'partially_refunded', 'refunded', 'voided') | Payment status |
| fulfillment_status | TEXT | No | 'unfulfilled' | CHECK IN ('unfulfilled', 'partial', 'fulfilled') | Shipping status |
| currency | TEXT | No | 'USD' | - | ISO 4217 currency code |
| subtotal_amount | INTEGER | No | 0 | - | Sum of line items in minor units |
| discount_amount | INTEGER | No | 0 | - | Total discount in minor units |
| shipping_amount | INTEGER | No | 0 | - | Shipping cost in minor units |
| tax_amount | INTEGER | No | 0 | - | Total tax in minor units |
| total_amount | INTEGER | No | 0 | - | Grand total in minor units |
| email | TEXT | Yes | NULL | - | Contact email at time of order |
| billing_address_json | TEXT | Yes | NULL | - | JSON address snapshot |
| shipping_address_json | TEXT | Yes | NULL | - | JSON address snapshot |
| placed_at | TEXT | Yes | NULL | - | ISO-8601 timestamp of placement |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| updated_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_orders_store_order_number` UNIQUE on (`store_id`, `order_number`) - Unique order number per store
- `idx_orders_store_id` on (`store_id`) - FK lookup
- `idx_orders_customer_id` on (`customer_id`) - Listing orders per customer
- `idx_orders_store_status` on (`store_id`, `status`) - Filtering by overall status
- `idx_orders_store_financial` on (`store_id`, `financial_status`) - Filtering by payment status
- `idx_orders_store_fulfillment` on (`store_id`, `fulfillment_status`) - Filtering by shipping status
- `idx_orders_placed_at` on (`store_id`, `placed_at`) - Date-range order queries

---

### 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

**Notes:** `raw_json_encrypted` stores the mock payment provider response, encrypted at the application layer. `amount` is in minor units (cents). The `provider` is always `mock` since the platform uses a mock PSP (no real payment gateway integration). The `method` column records which payment method the customer selected (credit_card, paypal, or bank_transfer).

---

### refunds

Refund records linked to an order and payment.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |
| payment_id | INTEGER | No | - | FK -> payments(id) ON DELETE CASCADE | Source payment |
| amount | INTEGER | No | 0 | - | Refund amount in minor units |
| reason | TEXT | Yes | NULL | - | Refund reason |
| status | TEXT | No | 'pending' | CHECK IN ('pending', 'processed', 'failed') | |
| provider_refund_id | TEXT | Yes | NULL | - | External refund ID |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_refunds_order_id` on (`order_id`) - FK lookup, listing refunds per order
- `idx_refunds_payment_id` on (`payment_id`) - Finding refunds per payment
- `idx_refunds_status` on (`status`) - Filtering refunds by status

---

### fulfillments

Shipment tracking for order fulfillment.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| order_id | INTEGER | No | - | FK -> orders(id) ON DELETE CASCADE | Parent order |
| status | TEXT | No | 'pending' | CHECK IN ('pending', 'shipped', 'delivered') | |
| tracking_company | TEXT | Yes | NULL | - | Carrier name |
| tracking_number | TEXT | Yes | NULL | - | Tracking number |
| tracking_url | TEXT | Yes | NULL | - | Tracking page URL |
| shipped_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_fulfillments_order_id` on (`order_id`) - FK lookup, listing fulfillments per order
- `idx_fulfillments_status` on (`status`) - Filtering fulfillments by status
- `idx_fulfillments_tracking` on (`tracking_company`, `tracking_number`) - Tracking number lookup

---

### fulfillment_lines

Links specific order lines and quantities to a fulfillment.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| fulfillment_id | INTEGER | No | - | FK -> fulfillments(id) ON DELETE CASCADE | Parent fulfillment |
| order_line_id | INTEGER | No | - | FK -> order_lines(id) ON DELETE CASCADE | Source order line |
| quantity | INTEGER | No | 1 | - | Units fulfilled from this line |

**Indexes:**

- `idx_fulfillment_lines_fulfillment_id` on (`fulfillment_id`) - FK lookup, listing lines per fulfillment
- `idx_fulfillment_lines_fulfillment_order_line` UNIQUE on (`fulfillment_id`, `order_line_id`) - Prevents duplicate line entries per fulfillment

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

---

## Epic 7 - Analytics

### analytics_events

Raw event stream for storefront analytics.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
app/Models/CustomerAddress.php:8:class CustomerAddress extends Model
app/Models/SearchQuery.php:8:class SearchQuery extends Model
app/Models/ThemeSetting.php:8:class ThemeSetting extends Model
app/Models/Collection.php:10:class Collection extends Model
app/Models/Customer.php:13:class Customer extends Model implements Authenticatable
app/Models/Theme.php:10:class Theme extends Model
app/Models/NavigationMenu.php:9:class NavigationMenu extends Model
app/Models/Refund.php:8:class Refund extends Model
app/Models/NavigationItem.php:8:class NavigationItem extends Model
app/Models/ThemeFile.php:8:class ThemeFile extends Model
zsh:1: ==app/Models/Collection.php not found


status: completed

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


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

status: completed

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


text:
Script completed
Wall time 0.2 seconds
Output:

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


status: completed

input:
const r = await tools.exec_command({cmd:"rg -n \"oauth_clients|oauth_tokens|apps|webhook_subscriptions|app_installations\" specs/01-DATABASE-SCHEMA.md | head -30; sed -n '940,1130p' specs/01-DATABASE-SCHEMA.md","workdir":"/Users/wesner/Herd/shop","yield_time_ms":1000,"max_output_tokens":16000});
text(r.output);


command: rg -n "oauth_clients|oauth_tokens|apps|webhook_subscriptions|app_installations" specs/01-DATABASE-SCHEMA.md | head -30; sed -n '940,1130p' specs/01-DATABASE-SCHEMA.md

type: search

cmd: rg -n 'oauth_clients|oauth_tokens|apps|webhook_subscriptions|app_installations' specs/01-DATABASE-SCHEMA.md

query: oauth_clients|oauth_tokens|apps|webhook_subscriptions|app_installations

type: read

stdout:
971:### apps
984:- `idx_apps_status` on (`status`) - Filtering apps by status
988:### app_installations
996:| app_id | INTEGER | No | - | FK -> apps(id) ON DELETE CASCADE | |
1003:- `idx_app_installations_store_app` UNIQUE on (`store_id`, `app_id`) - One installation per app per store
1004:- `idx_app_installations_store_id` on (`store_id`) - Listing installations per store
1005:- `idx_app_installations_app_id` on (`app_id`) - Finding all stores with an app installed
1009:### oauth_clients
1011:OAuth2 client credentials for apps.
1016:| app_id | INTEGER | No | - | FK -> apps(id) ON DELETE CASCADE | Parent app |
1023:- `idx_oauth_clients_client_id` UNIQUE on (`client_id`) - OAuth2 client lookup
1024:- `idx_oauth_clients_app_id` on (`app_id`) - FK lookup, listing clients per app
1028:### oauth_tokens
1035:| installation_id | INTEGER | No | - | FK -> app_installations(id) ON DELETE CASCADE | Parent installation |
1042:- `idx_oauth_tokens_installation_id` on (`installation_id`) - FK lookup, listing tokens per installation
1043:- `idx_oauth_tokens_access_hash` UNIQUE on (`access_token_hash`) - Token validation lookup
1044:- `idx_oauth_tokens_expires_at` on (`expires_at`) - Cleanup of expired tokens
1048:### webhook_subscriptions
1056:| app_installation_id | INTEGER | Yes | NULL | FK -> app_installations(id) ON DELETE CASCADE | Nullable for store-level hooks |
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
1077:| subscription_id | INTEGER | No | - | FK -> webhook_subscriptions(id) ON DELETE CASCADE | Parent subscription |
1435:    apps ||--o{ app_installations : "installed via"
1436:    stores ||--o{ app_installations : "has"
1437:    apps ||--o{ oauth_clients : "authenticates via"
1438:    app_installations ||--o{ oauth_tokens : "issues"
1439:    stores ||--o{ webhook_subscriptions : "registers"
1440:    app_installations ||--o{ webhook_subscriptions : "owns"
1441:    webhook_subscriptions ||--o{ webhook_deliveries : "delivers"

---

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

---

## Epic 8 - Apps and Webhooks

### apps

Third-party or first-party application registry.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| name | TEXT | No | - | - | App display name |
| status | TEXT | No | 'active' | CHECK IN ('active', 'disabled') | |
| created_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_apps_status` on (`status`) - Filtering apps by status

---

### app_installations

Installation of an app on a specific store.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | |
| app_id | INTEGER | No | - | FK -> apps(id) ON DELETE CASCADE | |
| scopes_json | TEXT | No | '[]' | - | JSON array of granted permission scopes |
| status | TEXT | No | 'active' | CHECK IN ('active', 'suspended', 'uninstalled') | |
| installed_at | TEXT | Yes | NULL | - | ISO-8601 timestamp |

**Indexes:**

- `idx_app_installations_store_app` UNIQUE on (`store_id`, `app_id`) - One installation per app per store
- `idx_app_installations_store_id` on (`store_id`) - Listing installations per store
- `idx_app_installations_app_id` on (`app_id`) - Finding all stores with an app installed

---

### oauth_clients

OAuth2 client credentials for apps.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| app_id | INTEGER | No | - | FK -> apps(id) ON DELETE CASCADE | Parent app |
| client_id | TEXT | No | - | UNIQUE per table | OAuth2 client identifier |
| client_secret_encrypted | TEXT | No | - | - | Encrypted client secret |
| redirect_uris_json | TEXT | No | '[]' | - | JSON array of allowed redirect URIs |

**Indexes:**

- `idx_oauth_clients_client_id` UNIQUE on (`client_id`) - OAuth2 client lookup
- `idx_oauth_clients_app_id` on (`app_id`) - FK lookup, listing clients per app

---

### oauth_tokens

Issued access/refresh tokens for app installations.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| installation_id | INTEGER | No | - | FK -> app_installations(id) ON DELETE CASCADE | Parent installation |
| access_token_hash | TEXT | No | - | UNIQUE per table | Hashed access token |
| refresh_token_hash | TEXT | Yes | NULL | - | Hashed refresh token |
| expires_at | TEXT | No | - | - | ISO-8601 expiration timestamp |

**Indexes:**

- `idx_oauth_tokens_installation_id` on (`installation_id`) - FK lookup, listing tokens per installation
- `idx_oauth_tokens_access_hash` UNIQUE on (`access_token_hash`) - Token validation lookup
- `idx_oauth_tokens_expires_at` on (`expires_at`) - Cleanup of expired tokens

---

### webhook_subscriptions

Registered webhook endpoints for event delivery.

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| store_id | INTEGER | No | - | FK -> stores(id) ON DELETE CASCADE | Owning store |
| app_installation_id | INTEGER | Yes | NULL | FK -> app_installations(id) ON DELETE CASCADE | Nullable for store-level hooks |
| event_type | TEXT | No | - | - | Event to subscribe to |
| target_url | TEXT | No | - | - | Delivery endpoint URL |
| signing_secret_encrypted | TEXT | No | - | - | Encrypted HMAC signing secret |
| status | TEXT | No | 'active' | CHECK IN ('active', 'paused', 'disabled') | |

**Indexes:**

- `idx_webhook_subscriptions_store_id` on (`store_id`) - FK lookup
- `idx_webhook_subscriptions_store_event` on (`store_id`, `event_type`) - Finding subscriptions for a given event
- `idx_webhook_subscriptions_installation` on (`app_installation_id`) - Listing subscriptions per app installation

---

### 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 |

### cache_locks

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| key | TEXT | No | - | PRIMARY KEY | Lock key |
| owner | TEXT | No | - | - | Lock owner identifier |
| expiration | INTEGER | No | - | - | Unix timestamp |

### jobs

| Column | Type | Nullable | Default | Constraints | Notes |
|---|---|---|---|---|---|
| id | INTEGER | No | autoincrement | PRIMARY KEY | |
| queue | TEXT | No | - | - | Queue name |