197 lines
8.4 KiB
MySQL
197 lines
8.4 KiB
MySQL
|
-- Supply domain schema (PostgreSQL 15)
|
||
|
|
-- Single executable DDL source for supply_* tables.
|
||
|
|
-- Updated: 2026-03-25
|
||
|
|
|
||
|
|
BEGIN;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS supply_accounts (
|
||
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
|
|
user_id BIGINT NOT NULL,
|
||
|
|
platform VARCHAR(50) NOT NULL,
|
||
|
|
account_type VARCHAR(20) NOT NULL CHECK (account_type IN ('api_key', 'oauth')),
|
||
|
|
account_name VARCHAR(100),
|
||
|
|
encrypted_credentials TEXT NOT NULL,
|
||
|
|
key_id VARCHAR(100),
|
||
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending'
|
||
|
|
CHECK (status IN ('pending', 'active', 'suspended', 'disabled')),
|
||
|
|
risk_level VARCHAR(20) NOT NULL DEFAULT 'normal'
|
||
|
|
CHECK (risk_level IN ('low', 'normal', 'high')),
|
||
|
|
total_quota NUMERIC(20, 6),
|
||
|
|
available_quota NUMERIC(20, 6),
|
||
|
|
frozen_quota NUMERIC(20, 6) DEFAULT 0,
|
||
|
|
is_verified BOOLEAN DEFAULT FALSE,
|
||
|
|
verified_at TIMESTAMPTZ,
|
||
|
|
last_check_at TIMESTAMPTZ,
|
||
|
|
tos_compliant BOOLEAN DEFAULT TRUE,
|
||
|
|
tos_check_result TEXT,
|
||
|
|
total_requests BIGINT DEFAULT 0,
|
||
|
|
total_tokens BIGINT DEFAULT 0,
|
||
|
|
total_cost NUMERIC(20, 6) DEFAULT 0,
|
||
|
|
success_rate NUMERIC(5, 2) DEFAULT 0,
|
||
|
|
risk_score INT DEFAULT 0,
|
||
|
|
risk_reason TEXT,
|
||
|
|
is_frozen BOOLEAN DEFAULT FALSE,
|
||
|
|
frozen_reason TEXT,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
created_by BIGINT,
|
||
|
|
updated_by BIGINT
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_accounts_user_id ON supply_accounts (user_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_accounts_platform ON supply_accounts (platform);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_accounts_status ON supply_accounts (status);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_accounts_risk_level ON supply_accounts (risk_level);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS supply_packages (
|
||
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
|
|
supply_account_id BIGINT NOT NULL REFERENCES supply_accounts(id),
|
||
|
|
user_id BIGINT NOT NULL,
|
||
|
|
platform VARCHAR(50) NOT NULL,
|
||
|
|
model VARCHAR(100) NOT NULL,
|
||
|
|
total_quota NUMERIC(20, 6) NOT NULL,
|
||
|
|
available_quota NUMERIC(20, 6) NOT NULL,
|
||
|
|
sold_quota NUMERIC(20, 6) DEFAULT 0,
|
||
|
|
reserved_quota NUMERIC(20, 6) DEFAULT 0,
|
||
|
|
price_per_1m_input NUMERIC(20, 6),
|
||
|
|
price_per_1m_output NUMERIC(20, 6),
|
||
|
|
min_purchase NUMERIC(20, 6),
|
||
|
|
start_at TIMESTAMPTZ,
|
||
|
|
end_at TIMESTAMPTZ,
|
||
|
|
valid_days INT,
|
||
|
|
status VARCHAR(20) NOT NULL DEFAULT 'draft'
|
||
|
|
CHECK (status IN ('draft', 'active', 'paused', 'sold_out', 'expired')),
|
||
|
|
max_concurrent INT DEFAULT 10,
|
||
|
|
rate_limit_rpm INT DEFAULT 60,
|
||
|
|
total_orders INT DEFAULT 0,
|
||
|
|
total_revenue NUMERIC(20, 6) DEFAULT 0,
|
||
|
|
rating NUMERIC(3, 2) DEFAULT 0,
|
||
|
|
rating_count INT DEFAULT 0,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_packages_supply_account_id ON supply_packages (supply_account_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_packages_user_id ON supply_packages (user_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_packages_platform_model ON supply_packages (platform, model);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_packages_status ON supply_packages (status);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS supply_orders (
|
||
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
|
|
order_no VARCHAR(64) NOT NULL UNIQUE,
|
||
|
|
buyer_user_id BIGINT NOT NULL,
|
||
|
|
buyer_team_id BIGINT,
|
||
|
|
supply_account_id BIGINT NOT NULL REFERENCES supply_accounts(id),
|
||
|
|
supplier_user_id BIGINT NOT NULL,
|
||
|
|
supply_package_id BIGINT NOT NULL REFERENCES supply_packages(id),
|
||
|
|
platform VARCHAR(50) NOT NULL,
|
||
|
|
model VARCHAR(100) NOT NULL,
|
||
|
|
quota_amount NUMERIC(20, 6) NOT NULL,
|
||
|
|
quota_tokens BIGINT,
|
||
|
|
unit_price NUMERIC(20, 6) NOT NULL,
|
||
|
|
total_amount NUMERIC(20, 6) NOT NULL,
|
||
|
|
platform_fee NUMERIC(20, 6) NOT NULL,
|
||
|
|
supplier_earnings NUMERIC(20, 6) NOT NULL,
|
||
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending'
|
||
|
|
CHECK (status IN ('pending', 'paid', 'using', 'expired', 'refunded')),
|
||
|
|
used_quota NUMERIC(20, 6) DEFAULT 0,
|
||
|
|
remaining_quota NUMERIC(20, 6),
|
||
|
|
expired_at TIMESTAMPTZ,
|
||
|
|
payment_method VARCHAR(20),
|
||
|
|
paid_at TIMESTAMPTZ,
|
||
|
|
payment_transaction_id VARCHAR(100),
|
||
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_orders_buyer_user_id ON supply_orders (buyer_user_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_orders_supplier_user_id ON supply_orders (supplier_user_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_orders_supply_package_id ON supply_orders (supply_package_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_orders_status ON supply_orders (status);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS supply_usage_records (
|
||
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
|
|
order_id BIGINT NOT NULL REFERENCES supply_orders(id),
|
||
|
|
buyer_user_id BIGINT NOT NULL,
|
||
|
|
supply_account_id BIGINT NOT NULL REFERENCES supply_accounts(id),
|
||
|
|
supplier_user_id BIGINT NOT NULL,
|
||
|
|
request_id VARCHAR(64) NOT NULL,
|
||
|
|
upstream_request_id VARCHAR(128),
|
||
|
|
api_key_id BIGINT,
|
||
|
|
platform VARCHAR(50) NOT NULL,
|
||
|
|
model VARCHAR(100) NOT NULL,
|
||
|
|
endpoint VARCHAR(100) NOT NULL,
|
||
|
|
request_tokens BIGINT,
|
||
|
|
response_tokens BIGINT,
|
||
|
|
total_tokens BIGINT GENERATED ALWAYS AS (COALESCE(request_tokens, 0) + COALESCE(response_tokens, 0)) STORED,
|
||
|
|
input_cost NUMERIC(20, 6),
|
||
|
|
output_cost NUMERIC(20, 6),
|
||
|
|
total_cost NUMERIC(20, 6) NOT NULL,
|
||
|
|
unit_price NUMERIC(20, 6) NOT NULL,
|
||
|
|
response_status INT,
|
||
|
|
latency_ms INT,
|
||
|
|
error_message TEXT,
|
||
|
|
success BOOLEAN DEFAULT TRUE,
|
||
|
|
started_at TIMESTAMPTZ NOT NULL,
|
||
|
|
completed_at TIMESTAMPTZ,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_usage_records_request_id ON supply_usage_records (request_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_usage_records_order_id ON supply_usage_records (order_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_usage_records_supply_account_id ON supply_usage_records (supply_account_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_usage_records_platform_model ON supply_usage_records (platform, model);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_usage_records_started_at ON supply_usage_records (started_at);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS supply_earnings (
|
||
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
|
|
user_id BIGINT NOT NULL,
|
||
|
|
supply_account_id BIGINT REFERENCES supply_accounts(id),
|
||
|
|
order_id BIGINT REFERENCES supply_orders(id),
|
||
|
|
usage_record_id BIGINT REFERENCES supply_usage_records(id),
|
||
|
|
earnings_type VARCHAR(20) NOT NULL CHECK (earnings_type IN ('usage', 'bonus', 'refund')),
|
||
|
|
amount NUMERIC(20, 6) NOT NULL,
|
||
|
|
currency VARCHAR(10) DEFAULT 'CNY',
|
||
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending'
|
||
|
|
CHECK (status IN ('pending', 'available', 'withdrawn', 'frozen')),
|
||
|
|
available_amount NUMERIC(20, 6) DEFAULT 0,
|
||
|
|
frozen_amount NUMERIC(20, 6) DEFAULT 0,
|
||
|
|
withdrawn_amount NUMERIC(20, 6) DEFAULT 0,
|
||
|
|
description TEXT,
|
||
|
|
earned_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
available_at TIMESTAMPTZ,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_earnings_user_id ON supply_earnings (user_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_earnings_status ON supply_earnings (status);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_earnings_earned_at ON supply_earnings (earned_at);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS supply_settlements (
|
||
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
||
|
|
settlement_no VARCHAR(64) NOT NULL UNIQUE,
|
||
|
|
user_id BIGINT NOT NULL,
|
||
|
|
total_amount NUMERIC(20, 6) NOT NULL,
|
||
|
|
fee_amount NUMERIC(20, 6) DEFAULT 0,
|
||
|
|
net_amount NUMERIC(20, 6) NOT NULL,
|
||
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending'
|
||
|
|
CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
|
||
|
|
payment_method VARCHAR(20),
|
||
|
|
payment_account VARCHAR(100),
|
||
|
|
payment_transaction_id VARCHAR(100),
|
||
|
|
paid_at TIMESTAMPTZ,
|
||
|
|
period_start DATE NOT NULL,
|
||
|
|
period_end DATE NOT NULL,
|
||
|
|
total_orders INT DEFAULT 0,
|
||
|
|
total_usage_records INT DEFAULT 0,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_settlements_user_id ON supply_settlements (user_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_settlements_status ON supply_settlements (status);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supply_settlements_period ON supply_settlements (period_start, period_end);
|
||
|
|
|
||
|
|
COMMIT;
|