60 lines
2.8 KiB
MySQL
60 lines
2.8 KiB
MySQL
|
-- Phase 1 PostgreSQL migration
|
|||
|
|
-- 三张核心表：models、model_prices、report_runs
|
|||
|
|
-- 对应 fetch_openrouter.go 采集器输出字段和日报生成器需求
|
|||
|
|
|
|||
|
|
-- models：模型基础信息表
|
|||
|
|
CREATE TABLE IF NOT EXISTS models (
|
|||
|
|
id BIGSERIAL PRIMARY KEY,
|
|||
|
|
source TEXT NOT NULL DEFAULT 'openrouter',
|
|||
|
|
external_id TEXT NOT NULL UNIQUE,
|
|||
|
|
name TEXT,
|
|||
|
|
description TEXT,
|
|||
|
|
context_length INTEGER,
|
|||
|
|
capabilities JSONB, -- JSONB 数组存储
|
|||
|
|
created_at_source BIGINT, -- OpenRouter created 字段（Unix 时间戳）
|
|||
|
|
is_free BOOLEAN NOT NULL DEFAULT FALSE,
|
|||
|
|
status TEXT NOT NULL DEFAULT 'active', -- active / deprecated / discontinued
|
|||
|
|
raw_payload JSONB, -- 完整原始 JSON
|
|||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_models_external_id ON models(external_id);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_models_source ON models(source);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_models_status ON models(status);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_models_is_free ON models(is_free);
|
|||
|
|
|
|||
|
|
-- model_prices：模型定价表
|
|||
|
|
CREATE TABLE IF NOT EXISTS model_prices (
|
|||
|
|
id BIGSERIAL PRIMARY KEY,
|
|||
|
|
model_id BIGINT NOT NULL REFERENCES models(id) ON DELETE CASCADE,
|
|||
|
|
source TEXT NOT NULL DEFAULT 'openrouter',
|
|||
|
|
currency TEXT NOT NULL DEFAULT 'USD',
|
|||
|
|
input_price_per_mtok REAL,
|
|||
|
|
output_price_per_mtok REAL,
|
|||
|
|
effective_date DATE,
|
|||
|
|
source_url TEXT,
|
|||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
UNIQUE(model_id, source, currency, effective_date)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_prices_model_id ON model_prices(model_id);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_prices_source ON model_prices(source);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_prices_currency ON model_prices(currency);
|
|||
|
|
|
|||
|
|
-- report_runs：日报运行记录表
|
|||
|
|
CREATE TABLE IF NOT EXISTS report_runs (
|
|||
|
|
id BIGSERIAL PRIMARY KEY,
|
|||
|
|
source TEXT NOT NULL DEFAULT 'openrouter',
|
|||
|
|
report_date DATE NOT NULL,
|
|||
|
|
status TEXT NOT NULL DEFAULT 'pending', -- pending / generated / failed
|
|||
|
|
summary_md TEXT,
|
|||
|
|
output_path TEXT,
|
|||
|
|
error_message TEXT,
|
|||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_reports_source ON report_runs(source);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_reports_report_date ON report_runs(report_date);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_reports_status ON report_runs(status);
|