Crawl/backend/migrations/010_price_snapshot.sql

-- Migration 010: price_snapshot — canonical price data served by /prices API
SET search_path TO crawl;

-- 全局版本号，从 1 开始，每次快照有任何数据变化就 +1
CREATE TABLE IF NOT EXISTS price_snapshot_version (
    id         INT          PRIMARY KEY DEFAULT 1,
    version    BIGINT       NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    CONSTRAINT single_row CHECK (id = 1)
);

INSERT INTO price_snapshot_version (id, version) VALUES (1, 0)
ON CONFLICT (id) DO NOTHING;

-- 每个模型 URL 一行，不再存 version（version 统一在 price_snapshot_version 管理）
CREATE TABLE IF NOT EXISTS price_snapshot (
    url         TEXT         PRIMARY KEY,
    model_name  VARCHAR(200) NOT NULL,
    prices      JSONB        NOT NULL DEFAULT '{}',
    model_info  JSONB,
    rate_limits JSONB,
    tool_prices JSONB,
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE price_snapshot IS
    'One row per model URL. Global version is tracked in price_snapshot_version.';