prompts-core/update.sql

-- prompts_compose_task 提示词任务记录表
CREATE TABLE IF NOT EXISTS prompts_compose_task (
    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),

    task_id         VARCHAR(64)  NOT NULL,
    model_name      VARCHAR(128) NOT NULL DEFAULT '',
    skill_name      VARCHAR(128) NOT NULL DEFAULT '',
    build_type      INT          NOT NULL DEFAULT 0,
    callback_url    VARCHAR(512) NOT NULL DEFAULT '',
    gateway_state   INT          NOT NULL DEFAULT 0,
    request_payload JSONB        NOT NULL DEFAULT '{}'::jsonb,
    result_text     TEXT         NOT NULL DEFAULT '',
    messages        JSONB        NOT NULL DEFAULT '{}'::jsonb,
    status          VARCHAR(32)  NOT NULL DEFAULT 'pending',
    error_message   TEXT         NOT NULL DEFAULT '',
    oss_file        VARCHAR(1024) NOT NULL DEFAULT '',
    file_type       VARCHAR(64)  NOT NULL DEFAULT ''
    );

-- 索引
CREATE UNIQUE INDEX IF NOT EXISTS uk_prompts_compose_task_task_id ON prompts_compose_task(task_id);
CREATE INDEX IF NOT EXISTS idx_prompts_compose_task_status ON prompts_compose_task(status);
CREATE INDEX IF NOT EXISTS idx_prompts_compose_task_deleted_at ON prompts_compose_task(deleted_at);

-- 注释
COMMENT ON TABLE  prompts_compose_task IS '提示词任务记录表';
COMMENT ON COLUMN prompts_compose_task.id              IS '主键ID';
COMMENT ON COLUMN prompts_compose_task.tenant_id       IS '租户ID';
COMMENT ON COLUMN prompts_compose_task.creator         IS '创建人';
COMMENT ON COLUMN prompts_compose_task.created_at      IS '创建时间';
COMMENT ON COLUMN prompts_compose_task.updater         IS '更新人';
COMMENT ON COLUMN prompts_compose_task.updated_at      IS '更新时间';
COMMENT ON COLUMN prompts_compose_task.deleted_at      IS '删除时间（软删）';
COMMENT ON COLUMN prompts_compose_task.task_id         IS 'model-gateway 任务ID';
COMMENT ON COLUMN prompts_compose_task.model_name      IS '业务模型名称';
COMMENT ON COLUMN prompts_compose_task.skill_name      IS '技能名称';
COMMENT ON COLUMN prompts_compose_task.build_type      IS '构建类型：0默认/1提示词构建/2节点构建';
COMMENT ON COLUMN prompts_compose_task.callback_url    IS '回调地址';
COMMENT ON COLUMN prompts_compose_task.gateway_state   IS 'model-gateway 状态：0排队/1执行/2成功/3失败/4已下载';
COMMENT ON COLUMN prompts_compose_task.request_payload IS '发给 model-gateway 的请求内容';
COMMENT ON COLUMN prompts_compose_task.result_text     IS '回调返回的文本结果';
COMMENT ON COLUMN prompts_compose_task.messages        IS '最终解析后的 messages';
COMMENT ON COLUMN prompts_compose_task.status          IS '业务状态：pending/success/failed';
COMMENT ON COLUMN prompts_compose_task.error_message   IS '业务错误信息';
COMMENT ON COLUMN prompts_compose_task.oss_file        IS '网关返回的结果文件地址';
COMMENT ON COLUMN prompts_compose_task.file_type       IS '结果文件类型';



-- prompts_compose_session 提示词历史会话表
CREATE TABLE IF NOT EXISTS prompts_compose_session (
    id               BIGINT       NOT NULL,
    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),

    session_id       VARCHAR(64)  NOT NULL,
    request_content  JSONB        NOT NULL DEFAULT '{}'::jsonb,
    response_content JSONB        NOT NULL DEFAULT '{}'::jsonb,
    remark           VARCHAR(500) NOT NULL DEFAULT ''
);
-- 索引
CREATE INDEX IF NOT EXISTS idx_prompts_compose_session_session_id ON prompts_compose_session(session_id);
CREATE INDEX IF NOT EXISTS idx_prompts_compose_session_deleted_at ON prompts_compose_session(deleted_at);
-- 注释
COMMENT ON TABLE  prompts_compose_session IS '提示词历史会话表';
COMMENT ON COLUMN prompts_compose_session.id               IS '主键ID';
COMMENT ON COLUMN prompts_compose_session.tenant_id        IS '租户ID';
COMMENT ON COLUMN prompts_compose_session.creator          IS '创建人';
COMMENT ON COLUMN prompts_compose_session.created_at       IS '创建时间';
COMMENT ON COLUMN prompts_compose_session.updater          IS '更新人';
COMMENT ON COLUMN prompts_compose_session.updated_at       IS '更新时间';
COMMENT ON COLUMN prompts_compose_session.deleted_at       IS '删除时间（软删）';
COMMENT ON COLUMN prompts_compose_session.session_id       IS '会话ID';
COMMENT ON COLUMN prompts_compose_session.request_content  IS '请求内容（JSON格式）';
COMMENT ON COLUMN prompts_compose_session.response_content IS '返回内容（JSON格式）';
COMMENT ON COLUMN prompts_compose_session.remark           IS '备注';



-- prompts_provider_protocol 模型协议映射配置表
CREATE TABLE IF NOT EXISTS prompts_provider_protocol (
    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),

    provider_name           VARCHAR(64)  NOT NULL DEFAULT '',
    target_field            VARCHAR(64)  NOT NULL DEFAULT '',
    merge_order             JSONB        NOT NULL DEFAULT '[]'::jsonb,
    role_mapping            JSONB        NOT NULL DEFAULT '{}'::jsonb,
    content_mapping         JSONB        NOT NULL DEFAULT '{}'::jsonb,
    capabilities            JSONB        NOT NULL DEFAULT '{}'::jsonb,
    request_template        JSONB        NOT NULL DEFAULT '{}'::jsonb,
    system_prompt_template  TEXT         NOT NULL DEFAULT '',
    user_prompt_template    TEXT         NOT NULL DEFAULT '',
    status                  INT          NOT NULL DEFAULT 1,
    remark                  VARCHAR(500) NOT NULL DEFAULT ''
);
-- 索引
CREATE INDEX IF NOT EXISTS idx_prompts_provider_protocol_provider_name ON prompts_provider_protocol(provider_name);
CREATE INDEX IF NOT EXISTS idx_prompts_provider_protocol_status ON prompts_provider_protocol(status);
CREATE INDEX IF NOT EXISTS idx_prompts_provider_protocol_deleted_at ON prompts_provider_protocol(deleted_at);
-- 注释
COMMENT ON TABLE  prompts_provider_protocol IS '模型协议映射配置表';
COMMENT ON COLUMN prompts_provider_protocol.id                    IS '主键ID';
COMMENT ON COLUMN prompts_provider_protocol.tenant_id             IS '租户ID';
COMMENT ON COLUMN prompts_provider_protocol.creator               IS '创建人';
COMMENT ON COLUMN prompts_provider_protocol.created_at            IS '创建时间';
COMMENT ON COLUMN prompts_provider_protocol.updater               IS '更新人';
COMMENT ON COLUMN prompts_provider_protocol.updated_at            IS '更新时间';
COMMENT ON COLUMN prompts_provider_protocol.deleted_at            IS '删除时间（软删）';
COMMENT ON COLUMN prompts_provider_protocol.provider_name         IS '运营商名称（openai/deepseek/qwen/anthropic/gemini等）';
COMMENT ON COLUMN prompts_provider_protocol.target_field          IS '目标字段（messages/contents/prompt）';
COMMENT ON COLUMN prompts_provider_protocol.merge_order           IS 'Prompt IR 拼接顺序（system/history/user）';
COMMENT ON COLUMN prompts_provider_protocol.role_mapping          IS '角色映射（system/user/assistant -> provider role）';
COMMENT ON COLUMN prompts_provider_protocol.content_mapping       IS '内容字段映射（content/parts.text等）';
COMMENT ON COLUMN prompts_provider_protocol.capabilities          IS '协议能力配置（system/history/tools/stream等支持情况）';
COMMENT ON COLUMN prompts_provider_protocol.request_template      IS '请求模板（JSON结构模板）';
COMMENT ON COLUMN prompts_provider_protocol.system_prompt_template IS '系统提示词模板';
COMMENT ON COLUMN prompts_provider_protocol.status                IS '状态：1启用/0禁用';
COMMENT ON COLUMN prompts_provider_protocol.remark                IS '备注';