Maas-Model-platform/backend/migrations/046_create_api_call_log_table.sql

-- 创建API调用日志表
-- 需求: 10.3 - 记录API调用的详细信息用于计费和审计

CREATE TABLE aigcspace.api_call_log (
    id BIGSERIAL PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL REFERENCES aigcspace.users(id) ON DELETE CASCADE,
    api_key_id INTEGER REFERENCES aigcspace.platform_api_key(id) ON DELETE SET NULL,
    model_id INTEGER REFERENCES aigcspace.models(id) ON DELETE SET NULL,
    model_name VARCHAR(255) NOT NULL,
    is_local BOOLEAN NOT NULL DEFAULT FALSE,
    input_tokens INTEGER NOT NULL DEFAULT 0,
    output_tokens INTEGER NOT NULL DEFAULT 0,
    bill DECIMAL(12,4) NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'success',
    error_message TEXT,
    request_ip VARCHAR(50),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX idx_api_call_log_user_id ON aigcspace.api_call_log(user_id);
CREATE INDEX idx_api_call_log_api_key_id ON aigcspace.api_call_log(api_key_id);
CREATE INDEX idx_api_call_log_model_id ON aigcspace.api_call_log(model_id);
CREATE INDEX idx_api_call_log_created_at ON aigcspace.api_call_log(created_at);
CREATE INDEX idx_api_call_log_is_local ON aigcspace.api_call_log(is_local);

COMMENT ON TABLE aigcspace.api_call_log IS 'API调用日志表';
COMMENT ON COLUMN aigcspace.api_call_log.id IS '主键ID';
COMMENT ON COLUMN aigcspace.api_call_log.user_id IS '用户ID';
COMMENT ON COLUMN aigcspace.api_call_log.api_key_id IS '使用的API Key ID';
COMMENT ON COLUMN aigcspace.api_call_log.model_id IS '调用的模型ID';
COMMENT ON COLUMN aigcspace.api_call_log.model_name IS '模型名称';
COMMENT ON COLUMN aigcspace.api_call_log.is_local IS '是否为本地模型';
COMMENT ON COLUMN aigcspace.api_call_log.input_tokens IS '输入Token数量';
COMMENT ON COLUMN aigcspace.api_call_log.output_tokens IS '输出Token数量';
COMMENT ON COLUMN aigcspace.api_call_log.bill IS '费用金额';
COMMENT ON COLUMN aigcspace.api_call_log.status IS '调用状态：success/failed';
COMMENT ON COLUMN aigcspace.api_call_log.error_message IS '错误信息';
COMMENT ON COLUMN aigcspace.api_call_log.request_ip IS '请求IP地址';
COMMENT ON COLUMN aigcspace.api_call_log.created_at IS '创建时间';