v1.0.0.0

StocketBase

← Changes All versions

182 lines · 8.1 KB

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE IF NOT EXISTS user_accounts (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(190) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
display_name VARCHAR(120) NULL,
avatar_path VARCHAR(255) NULL,
email_verified_at DATETIME NULL,
status ENUM('active','suspended','deleted') NOT NULL DEFAULT 'active',
last_login_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uq_user_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS user_sessions (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_account_id INT UNSIGNED NOT NULL,
session_token VARCHAR(128) NOT NULL,
ip_address VARCHAR(64) NULL,
user_agent VARCHAR(255) NULL,
expires_at DATETIME NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uq_user_session_token (session_token),
KEY idx_user_session_account (user_account_id),
CONSTRAINT fk_user_session_account FOREIGN KEY (user_account_id) REFERENCES user_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS user_consents (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_account_id INT UNSIGNED NOT NULL,
consent_type VARCHAR(60) NOT NULL,
is_granted TINYINT(1) NOT NULL DEFAULT 0,
granted_at DATETIME NULL,
revoked_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY idx_consent_account (user_account_id),
CONSTRAINT fk_consent_account FOREIGN KEY (user_account_id) REFERENCES user_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS subscriptions (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_account_id INT UNSIGNED NOT NULL,
product_id INT UNSIGNED NULL,
provider VARCHAR(60) NOT NULL,
provider_customer_id VARCHAR(190) NULL,
provider_subscription_id VARCHAR(190) NULL,
plan_code VARCHAR(60) NULL,
status ENUM('active','trialing','past_due','canceled','expired') NOT NULL DEFAULT 'active',
current_period_end DATETIME NULL,
canceled_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY idx_subscription_account (user_account_id),
KEY idx_subscription_provider_ref (provider, provider_subscription_id),
CONSTRAINT fk_subscription_account FOREIGN KEY (user_account_id) REFERENCES user_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_number VARCHAR(40) NOT NULL,
user_account_id INT UNSIGNED NULL,
guest_email VARCHAR(190) NULL,
status ENUM('pending','paid','processing','fulfilled','shipped','completed','canceled','refunded','failed') NOT NULL DEFAULT 'pending',
provider VARCHAR(60) NULL,
provider_checkout_id VARCHAR(190) NULL,
provider_payment_id VARCHAR(190) NULL,
provider_customer_id VARCHAR(190) NULL,
currency CHAR(3) NOT NULL DEFAULT 'USD',
subtotal_cents INT UNSIGNED NOT NULL DEFAULT 0,
discount_cents INT UNSIGNED NOT NULL DEFAULT 0,
shipping_cents INT UNSIGNED NOT NULL DEFAULT 0,
tax_cents INT UNSIGNED NOT NULL DEFAULT 0,
total_cents INT UNSIGNED NOT NULL DEFAULT 0,
discount_code VARCHAR(60) NULL,
shipping_name VARCHAR(190) NULL,
shipping_address_line1 VARCHAR(255) NULL,
shipping_address_line2 VARCHAR(255) NULL,
shipping_city VARCHAR(120) NULL,
shipping_region VARCHAR(120) NULL,
shipping_postal_code VARCHAR(30) NULL,
shipping_country CHAR(2) NULL,
billing_same_as_shipping TINYINT(1) NOT NULL DEFAULT 1,
billing_name VARCHAR(190) NULL,
billing_address_line1 VARCHAR(255) NULL,
billing_address_line2 VARCHAR(255) NULL,
billing_city VARCHAR(120) NULL,
billing_region VARCHAR(120) NULL,
billing_postal_code VARCHAR(30) NULL,
billing_country CHAR(2) NULL,
customer_note TEXT NULL,
tracking_number VARCHAR(190) NULL,
tracking_url VARCHAR(255) NULL,
fulfilled_at DATETIME NULL,
shipped_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uq_order_number (order_number),
KEY idx_order_account (user_account_id),
KEY idx_order_status (status),
KEY idx_order_provider_checkout (provider, provider_checkout_id),
CONSTRAINT fk_order_account FOREIGN KEY (user_account_id) REFERENCES user_accounts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS order_items (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_id INT UNSIGNED NOT NULL,
product_id INT UNSIGNED NOT NULL,
variant_id INT UNSIGNED NULL,
product_title_snapshot VARCHAR(255) NOT NULL,
variant_name_snapshot VARCHAR(120) NULL,
unit_price_cents INT UNSIGNED NOT NULL,
quantity INT UNSIGNED NOT NULL DEFAULT 1,
total_cents INT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY idx_order_item_order (order_id),
KEY idx_order_item_product (product_id),
CONSTRAINT fk_order_item_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS order_status_history (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_id INT UNSIGNED NOT NULL,
status VARCHAR(30) NOT NULL,
note VARCHAR(500) NULL,
changed_by_team_account_id INT UNSIGNED NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY idx_order_history_order (order_id),
CONSTRAINT fk_order_history_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS payment_webhook_events (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
provider VARCHAR(60) NOT NULL,
event_id VARCHAR(190) NOT NULL,
event_type VARCHAR(120) NOT NULL,
payload LONGTEXT NOT NULL,
processing_status ENUM('received','processed','failed','ignored') NOT NULL DEFAULT 'received',
processing_error VARCHAR(500) NULL,
received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
processed_at DATETIME NULL,
UNIQUE KEY uq_webhook_provider_event (provider, event_id),
KEY idx_webhook_status (processing_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS product_reviews (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product_id INT UNSIGNED NOT NULL,
user_account_id INT UNSIGNED NULL,
parent_id INT UNSIGNED NULL,
external_thread_id VARCHAR(190) NULL,
rating TINYINT UNSIGNED NULL,
body TEXT NULL,
status ENUM('visible','hidden','flagged') NOT NULL DEFAULT 'visible',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY idx_review_product (product_id),
KEY idx_review_parent (parent_id),
CONSTRAINT fk_review_account FOREIGN KEY (user_account_id) REFERENCES user_accounts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS download_grants (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
token CHAR(64) NOT NULL,
product_id INT UNSIGNED NOT NULL,
order_id INT UNSIGNED NULL,
user_account_id INT UNSIGNED NULL,
email VARCHAR(190) NULL,
download_count INT UNSIGNED NOT NULL DEFAULT 0,
max_downloads INT UNSIGNED NULL,
expires_at DATETIME NULL,
revoked_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uq_download_token (token),
KEY idx_download_order (order_id),
KEY idx_download_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SET FOREIGN_KEY_CHECKS = 1;