model-gateway/update.sql

-- model-asynch 核心表(pgsql)
-- 1) asynch_models：模型配置
-- 2) asynch_task：异步任务
-- 3) logs_model_op：操作日志(统计用)
-- 4) logs_model_stat：按天模型请求统计(限流/监控用)

-- =========================
-- 1) asynch_models
-- =========================
CREATE TABLE IF NOT EXISTS asynch_models (
    -- ========== 基础字段 ==========
    id                         BIGINT PRIMARY KEY,
    tenant_id                  BIGINT NOT NULL DEFAULT 0,
    creator                    VARCHAR(64) NOT NULL,
    created_at                 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater                    VARCHAR(64) NOT NULL,
    updated_at                 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at                 TIMESTAMP(6),

    -- ========== 模型标识 ==========
    model_name                 VARCHAR(128) NOT NULL,
    model_type                 SMALLINT NOT NULL DEFAULT 0,
    operator_name              VARCHAR(64) NOT NULL DEFAULT '',

    -- ========== 请求配置 ==========
    base_url                   VARCHAR(256) NOT NULL,
    http_method                VARCHAR(8) NOT NULL DEFAULT 'POST',
    head_msg                   JSONB NOT NULL DEFAULT '{}'::jsonb,
    api_key                    VARCHAR(256) NOT NULL DEFAULT '',

    -- ========== 状态开关 ==========
    is_private                 SMALLINT NOT NULL DEFAULT 0,
    enabled                    SMALLINT NOT NULL DEFAULT 1,
    is_chat_model              SMALLINT NOT NULL DEFAULT 0,
    is_async                   SMALLINT NOT NULL DEFAULT 0,
    is_stream                  SMALLINT NOT NULL DEFAULT 0,
    is_owner                   SMALLINT NOT NULL DEFAULT 99,

    -- ========== 配置相关 ==========
    form_json                  JSONB NOT NULL DEFAULT '{}'::jsonb,
    request_mapping            JSONB NOT NULL DEFAULT '{}'::jsonb,
    response_mapping           JSONB NOT NULL DEFAULT '{}'::jsonb,
    response_body              JSONB NOT NULL DEFAULT '{}'::jsonb,
    token_config               JSONB NOT NULL DEFAULT '{}'::jsonb,
    extend_mapping             JSONB NOT NULL DEFAULT '{}'::jsonb,
    query_config               JSONB NOT NULL DEFAULT '{}'::jsonb,
    stream_config              JSONB NOT NULL DEFAULT '{}'::jsonb,
    first_frame                VARCHAR(128) NOT NULL DEFAULT '',
    last_frame                 VARCHAR(128) NOT NULL DEFAULT '',

    -- ========== 限制与重试 ==========
    max_concurrency            INT NOT NULL DEFAULT 10,
    timeout_seconds            INT NOT NULL DEFAULT 600,
    retry_times                SMALLINT NOT NULL DEFAULT 3,
    auto_clean_seconds         INT NOT NULL DEFAULT 86400,

    -- ========== 其他 ==========
    response_token_field       VARCHAR(128) NOT NULL DEFAULT '',
    );

-- ========== 索引 ==========
CREATE UNIQUE INDEX IF NOT EXISTS uk_asynch_models_tenant_creator_chat ON asynch_models(tenant_id, creator) WHERE is_chat_model = 1 AND deleted_at IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS uk_asynch_models_tenant_model_name ON asynch_models(tenant_id, creator, model_name);
CREATE INDEX IF NOT EXISTS idx_asynch_models_tenant_id ON asynch_models(tenant_id);
CREATE INDEX IF NOT EXISTS idx_asynch_models_model_name ON asynch_models(model_name);
CREATE INDEX IF NOT EXISTS idx_asynch_models_model_type ON asynch_models(model_type);
CREATE INDEX IF NOT EXISTS idx_asynch_models_enabled ON asynch_models(enabled);
CREATE INDEX IF NOT EXISTS idx_asynch_models_deleted_at ON asynch_models(deleted_at);

-- ========== 注释 ==========
COMMENT ON TABLE asynch_models IS '模型配置表';

COMMENT ON COLUMN asynch_models.id IS '主键ID(非自增)';
COMMENT ON COLUMN asynch_models.tenant_id IS '租户ID';
COMMENT ON COLUMN asynch_models.creator IS '创建人';
COMMENT ON COLUMN asynch_models.created_at IS '创建时间';
COMMENT ON COLUMN asynch_models.updater IS '更新人';
COMMENT ON COLUMN asynch_models.updated_at IS '更新时间';
COMMENT ON COLUMN asynch_models.deleted_at IS '删除时间(软删)';

COMMENT ON COLUMN asynch_models.model_name IS '模型名称';
COMMENT ON COLUMN asynch_models.model_type IS '模型类型';
COMMENT ON COLUMN asynch_models.operator_name IS '运营商名称';
COMMENT ON COLUMN asynch_models.base_url IS '模型地址';
COMMENT ON COLUMN asynch_models.http_method IS '请求方式 GET/POST';
COMMENT ON COLUMN asynch_models.head_msg IS '请求头信息';
COMMENT ON COLUMN asynch_models.api_key IS '调用凭证/密钥';
COMMENT ON COLUMN asynch_models.is_private IS '是否私有化：0-私有 1-公共';
COMMENT ON COLUMN asynch_models.enabled IS '是否启用：0-停用 1-启用';
COMMENT ON COLUMN asynch_models.is_chat_model IS '是否为对话模型：0-否 1-是';
COMMENT ON COLUMN asynch_models.is_async IS '是否异步：0-同步 1-异步';
COMMENT ON COLUMN asynch_models.is_stream IS '是否流式：0-非流式 1-流式';
COMMENT ON COLUMN asynch_models.is_owner IS '1=当前用户创建 0=超级管理员';
COMMENT ON COLUMN asynch_models.form_json IS '动态表单结构';
COMMENT ON COLUMN asynch_models.request_mapping IS '请求映射';
COMMENT ON COLUMN asynch_models.response_mapping IS '返回映射';
COMMENT ON COLUMN asynch_models.response_body IS '返回主体';
COMMENT ON COLUMN asynch_models.token_config IS 'Token计算配置';
COMMENT ON COLUMN asynch_models.extend_mapping IS '附加映射';
COMMENT ON COLUMN asynch_models.query_config IS '查询/回调配置';
COMMENT ON COLUMN asynch_models.stream_config IS '流式输出配置';
COMMENT ON COLUMN asynch_models.first_frame IS '首帧图片参数';
COMMENT ON COLUMN asynch_models.last_frame IS '尾帧图片参数';
COMMENT ON COLUMN asynch_models.max_concurrency IS '最大并发数';
COMMENT ON COLUMN asynch_models.timeout_seconds IS '调用模型超时(秒)';
COMMENT ON COLUMN asynch_models.retry_times IS '失败重试次数';
COMMENT ON COLUMN asynch_models.auto_clean_seconds IS '任务完成后自动清理时间(秒)';
COMMENT ON COLUMN asynch_models.response_token_field IS '响应中消耗token的字段映射';


-- =========================
-- model_gateway_task
-- =========================
CREATE TABLE model_gateway_task (
    id              int8 PRIMARY KEY,
    tenant_id       int8 NOT NULL DEFAULT 0,
    creator         varchar(64) NOT NULL,
    created_at      timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater         varchar(64) NOT NULL,
    updated_at      timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at      timestamp(6),
    model_name      varchar(128) NOT NULL,
    task_id         varchar(64) NOT NULL,
    biz_name        varchar(128) NOT NULL DEFAULT '',
    callback_url    varchar(512) DEFAULT '',
    state           int2 NOT NULL DEFAULT 0,
    retry_count     int4 NOT NULL DEFAULT 0,
    phase           int2 NOT NULL DEFAULT 0,
    tmp_file        text DEFAULT '',
    error_msg       text DEFAULT '',
    result_file     jsonb NOT NULL DEFAULT '{}',
    request_payload jsonb NOT NULL DEFAULT '{}',
    text_result     jsonb NOT NULL DEFAULT '{}',
    expend_tokens   int8 NOT NULL DEFAULT 0,
    duration_seconds int8 NOT NULL DEFAULT 0,
    epicycle_id     varchar(64) NOT NULL DEFAULT ''
);

CREATE UNIQUE INDEX uk_model_gateway_task_tenant_creator_task_id ON model_gateway_task (tenant_id, creator, task_id);
CREATE INDEX idx_model_gateway_task_task_id ON model_gateway_task (task_id);
CREATE INDEX idx_model_gateway_task_state ON model_gateway_task (state);
CREATE INDEX idx_model_gateway_task_deleted_at ON model_gateway_task (deleted_at);

COMMENT ON TABLE model_gateway_task IS '模型网关任务表';
COMMENT ON COLUMN model_gateway_task.id IS '主键ID';
COMMENT ON COLUMN model_gateway_task.tenant_id IS '租户ID';
COMMENT ON COLUMN model_gateway_task.creator IS '创建人';
COMMENT ON COLUMN model_gateway_task.created_at IS '创建时间';
COMMENT ON COLUMN model_gateway_task.updater IS '更新人';
COMMENT ON COLUMN model_gateway_task.updated_at IS '更新时间';
COMMENT ON COLUMN model_gateway_task.deleted_at IS '删除时间（软删）';
COMMENT ON COLUMN model_gateway_task.model_name IS '模型名称';
COMMENT ON COLUMN model_gateway_task.task_id IS '任务ID（对外返回）';
COMMENT ON COLUMN model_gateway_task.biz_name IS '业务名称（调用方模块/系统）';
COMMENT ON COLUMN model_gateway_task.callback_url IS '回调地址';
COMMENT ON COLUMN model_gateway_task.state IS '0排队中/1执行中/2成功/3失败/4已下载';
COMMENT ON COLUMN model_gateway_task.retry_count IS '已重试次数';
COMMENT ON COLUMN model_gateway_task.phase IS '执行阶段：0模型阶段/1OSS阶段';
COMMENT ON COLUMN model_gateway_task.tmp_file IS '临时结果文件路径';
COMMENT ON COLUMN model_gateway_task.error_msg IS '错误信息';
COMMENT ON COLUMN model_gateway_task.result_file IS '结果文件：{oss_file, file_type, file_size}';
COMMENT ON COLUMN model_gateway_task.request_payload IS '请求参数（JSON）';
COMMENT ON COLUMN model_gateway_task.text_result IS '文本类结果';
COMMENT ON COLUMN model_gateway_task.expend_tokens IS '消耗token数';
COMMENT ON COLUMN model_gateway_task.duration_seconds IS '耗时（秒）';
COMMENT ON COLUMN model_gateway_task.epicycle_id IS '轮次ID';



-- =========================
-- 3) logs_model_op
-- =========================
CREATE TABLE IF NOT EXISTS logs_model_op (
    -- 基础字段
    id BIGINT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    creator VARCHAR(64) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater VARCHAR(64) NOT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP(6),
    -- 基础审计信息
    ip VARCHAR(64) DEFAULT '',
    user_agent VARCHAR(256) DEFAULT '',
    api_path VARCHAR(256) DEFAULT '',
    http_method VARCHAR(16) DEFAULT '',
    -- 业务信息
    biz_name VARCHAR(128) NOT NULL DEFAULT '', -- 调用方业务模块/系统
    model_name VARCHAR(128) NOT NULL DEFAULT '',
    task_id VARCHAR(64) NOT NULL DEFAULT '',
    -- 统计字段
    op_type VARCHAR(64) NOT NULL DEFAULT 'createTask', -- 操作类型(默认创建任务)
    success SMALLINT NOT NULL DEFAULT 1, -- 1成功/0失败
    error_msg TEXT DEFAULT '',
    cost_ms BIGINT NOT NULL DEFAULT 0, -- 耗时(毫秒)
    -- 请求/响应 JSON(用于后期统计分析)
    request_payload JSONB,
    response_payload JSONB
);

CREATE INDEX IF NOT EXISTS idx_logs_model_op_tenant_time ON logs_model_op(tenant_id, created_at);
CREATE INDEX IF NOT EXISTS idx_logs_model_op_model_name ON logs_model_op(model_name);
CREATE INDEX IF NOT EXISTS idx_logs_model_op_biz_name ON logs_model_op(biz_name);
CREATE INDEX IF NOT EXISTS idx_logs_model_op_task_id ON logs_model_op(task_id);
CREATE INDEX IF NOT EXISTS idx_logs_model_op_op_type ON logs_model_op(op_type);
CREATE INDEX IF NOT EXISTS idx_logs_model_op_deleted_at ON logs_model_op(deleted_at);

COMMENT ON TABLE logs_model_op IS '操作记录日志表(创建任务等，用于统计)';
COMMENT ON COLUMN logs_model_op.id IS '主键ID(非自增)';
COMMENT ON COLUMN logs_model_op.tenant_id IS '租户ID';
COMMENT ON COLUMN logs_model_op.creator IS '创建人';
COMMENT ON COLUMN logs_model_op.created_at IS '创建时间';
COMMENT ON COLUMN logs_model_op.updater IS '更新人';
COMMENT ON COLUMN logs_model_op.updated_at IS '更新时间';
COMMENT ON COLUMN logs_model_op.deleted_at IS '删除时间(软删)';
COMMENT ON COLUMN logs_model_op.ip IS '客户端IP';
COMMENT ON COLUMN logs_model_op.user_agent IS 'User-Agent';
COMMENT ON COLUMN logs_model_op.api_path IS '接口路径';
COMMENT ON COLUMN logs_model_op.http_method IS 'HTTP方法';
COMMENT ON COLUMN logs_model_op.biz_name IS '业务名称(调用方模块/系统)';
COMMENT ON COLUMN logs_model_op.model_name IS '模型名称';
COMMENT ON COLUMN logs_model_op.task_id IS '任务ID';
COMMENT ON COLUMN logs_model_op.op_type IS '操作类型(如 createTask/getTaskResult/getTaskBatch 等)';
COMMENT ON COLUMN logs_model_op.success IS '是否成功：1成功/0失败';
COMMENT ON COLUMN logs_model_op.error_msg IS '错误信息(失败时)';
COMMENT ON COLUMN logs_model_op.cost_ms IS '耗时(毫秒)';
COMMENT ON COLUMN logs_model_op.request_payload IS '请求 JSON';
COMMENT ON COLUMN logs_model_op.response_payload IS '响应 JSON';


-- =========================
-- 4) logs_model_stat
-- =========================
CREATE TABLE IF NOT EXISTS logs_model_stat (
    day DATE NOT NULL,                     -- 天(YYYY-MM-DD)
    tenant_id BIGINT NOT NULL DEFAULT 0,   -- 租户ID
    creator VARCHAR(64) NOT NULL DEFAULT '', -- 创建人
    model_name VARCHAR(128) NOT NULL DEFAULT '', -- 模型名称
    request_count BIGINT NOT NULL DEFAULT 0, -- 请求次数
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(day, tenant_id, creator, model_name)
);

-- 便于时间段/租户/人/模型过滤
CREATE INDEX IF NOT EXISTS idx_logs_model_stat_tenant_day ON logs_model_stat(tenant_id, day);
CREATE INDEX IF NOT EXISTS idx_logs_model_stat_day ON logs_model_stat(day);
CREATE INDEX IF NOT EXISTS idx_logs_model_stat_model_name ON logs_model_stat(model_name);
CREATE INDEX IF NOT EXISTS idx_logs_model_stat_creator ON logs_model_stat(creator);

COMMENT ON TABLE logs_model_stat IS '按天模型请求统计(用于限流/监控)';
COMMENT ON COLUMN logs_model_stat.day IS '天(YYYY-MM-DD)';
COMMENT ON COLUMN logs_model_stat.tenant_id IS '租户ID';
COMMENT ON COLUMN logs_model_stat.creator IS '创建人';
COMMENT ON COLUMN logs_model_stat.model_name IS '模型名称';
COMMENT ON COLUMN logs_model_stat.request_count IS '请求次数';
COMMENT ON COLUMN logs_model_stat.created_at IS '创建时间';
COMMENT ON COLUMN logs_model_stat.updated_at IS '更新时间';