red-future/model-gateway
4
0
Fork 0
Code Issues Pull Requests 1 Actions Packages Projects Releases Wiki Activity
Files
dev优化中
model-gateway/update.sql
T
WangLiZhao 6a48edb742 docs(readme): 更新项目文档为智能模型网关 
chore(database): 优化模型配置表结构和索引设计

- 重新设计 model_gateway_models 表的字段结构和约束
- 新增 billing_config 和 special_params 配置字段
- 优化索引策略，包括复合索引和条件索引的创建
- 统一表注释格式和字段说明文档
- 移除过时的配置字段和冗余索引
2026-07-07 09:47:30 +08:00

202 lines
15 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- ============================================
-- 模型网关 (model-gateway) 建表语句
-- ============================================
-- 1. 模型配置表
CREATE TABLE "public"."model_gateway_models" (
"id" int8 NOT NULL,
"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,
"model_type" int2 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 '{}',
"api_key" varchar(256) NOT NULL DEFAULT '',
"is_private" int2 NOT NULL DEFAULT 0,
"enabled" int2 NOT NULL DEFAULT 1,
"is_chat_model" int2 NOT NULL DEFAULT 0,
"form_json" jsonb NOT NULL DEFAULT '{}',
"request_mapping" jsonb NOT NULL DEFAULT '{}',
"response_mapping" jsonb NOT NULL DEFAULT '{}',
"extend_mapping" jsonb NOT NULL DEFAULT '{}',
"query_config" jsonb NOT NULL DEFAULT '{}',
"stream_config" jsonb NOT NULL DEFAULT '{}',
"max_concurrency" int4 NOT NULL DEFAULT 10,
"timeout_seconds" int4 NOT NULL DEFAULT 600,
"retry_times" int2 NOT NULL DEFAULT 3,
"call_mode" int2 NOT NULL DEFAULT 0,
"required_fields" jsonb NOT NULL DEFAULT '[]',
"billing_config" jsonb NOT NULL DEFAULT '{}',
"special_params" jsonb NOT NULL DEFAULT '{}',
CONSTRAINT "model_gateway_models_pkey" PRIMARY KEY ("id")
);
ALTER TABLE "public"."model_gateway_models" OWNER TO "postgres";
CREATE INDEX "idx_models_deleted_at" ON "public"."model_gateway_models" ("deleted_at");
CREATE INDEX "idx_models_enabled" ON "public"."model_gateway_models" ("enabled");
CREATE INDEX "idx_models_model_name" ON "public"."model_gateway_models" ("model_name");
CREATE INDEX "idx_models_model_type" ON "public"."model_gateway_models" ("model_type");
CREATE INDEX "idx_models_tenant_id" ON "public"."model_gateway_models" ("tenant_id");
CREATE INDEX "idx_models_tenant_creator" ON "public"."model_gateway_models" ("tenant_id", "creator");
CREATE INDEX "idx_models_tenant_creator_model_deleted" ON "public"."model_gateway_models" ("tenant_id", "creator", "model_name") WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX "uk_models_tenant_creator_model" ON "public"."model_gateway_models" ("tenant_id", "creator", "model_name");
COMMENT ON TABLE "public"."model_gateway_models" IS '模型配置表';
COMMENT ON COLUMN "public"."model_gateway_models"."model_name" IS '模型名称';
COMMENT ON COLUMN "public"."model_gateway_models"."model_type" IS '模型类型';
COMMENT ON COLUMN "public"."model_gateway_models"."operator_name" IS '运营商名称';
COMMENT ON COLUMN "public"."model_gateway_models"."base_url" IS '模型地址';
COMMENT ON COLUMN "public"."model_gateway_models"."http_method" IS '请求方式 GET/POST';
COMMENT ON COLUMN "public"."model_gateway_models"."head_msg" IS '请求头信息';
COMMENT ON COLUMN "public"."model_gateway_models"."api_key" IS '调用凭证/密钥';
COMMENT ON COLUMN "public"."model_gateway_models"."is_private" IS '是否私有化：0-私有 1-公共';
COMMENT ON COLUMN "public"."model_gateway_models"."enabled" IS '是否启用：0-停用 1-启用';
COMMENT ON COLUMN "public"."model_gateway_models"."is_chat_model" IS '是否为对话模型：0-否 1-是';
COMMENT ON COLUMN "public"."model_gateway_models"."form_json" IS '动态表单结构';
COMMENT ON COLUMN "public"."model_gateway_models"."request_mapping" IS '请求映射';
COMMENT ON COLUMN "public"."model_gateway_models"."response_mapping" IS '返回映射';
COMMENT ON COLUMN "public"."model_gateway_models"."extend_mapping" IS '附加映射';
COMMENT ON COLUMN "public"."model_gateway_models"."query_config" IS '查询/回调配置';
COMMENT ON COLUMN "public"."model_gateway_models"."stream_config" IS '流式输出配置';
COMMENT ON COLUMN "public"."model_gateway_models"."max_concurrency" IS '最大并发数';
COMMENT ON COLUMN "public"."model_gateway_models"."timeout_seconds" IS '调用模型超时(秒)';
COMMENT ON COLUMN "public"."model_gateway_models"."retry_times" IS '失败重试次数';
COMMENT ON COLUMN "public"."model_gateway_models"."call_mode" IS '调用模式：0-同步 1-异步 2-流式';
COMMENT ON COLUMN "public"."model_gateway_models"."required_fields" IS '必选字段列表';
COMMENT ON COLUMN "public"."model_gateway_models"."billing_config" IS '计费配置';
COMMENT ON COLUMN "public"."model_gateway_models"."special_params" IS '请求特殊参数';
-- 2. 模型网关任务表
CREATE TABLE "public"."model_gateway_task" (
"id" int8 NOT NULL,
"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,
"error_msg" text DEFAULT '',
"result_file" jsonb NOT NULL DEFAULT '{}',
"request_payload" jsonb NOT NULL DEFAULT '{}',
"duration_seconds" int8 NOT NULL DEFAULT 0,
"epicycle_id" varchar(64) NOT NULL DEFAULT '',
"billing_data" jsonb NOT NULL DEFAULT '[]',
"build_model_name" varchar(128) NOT NULL DEFAULT '',
CONSTRAINT "model_gateway_task_pkey" PRIMARY KEY ("id")
);
ALTER TABLE "public"."model_gateway_task" OWNER TO "postgres";
CREATE INDEX "idx_task_creator_created" ON "public"."model_gateway_task" ("creator", "created_at" DESC);
CREATE INDEX "idx_task_model_state" ON "public"."model_gateway_task" ("model_name", "state");
CREATE INDEX "idx_task_state" ON "public"."model_gateway_task" ("state");
CREATE INDEX "idx_task_state_created" ON "public"."model_gateway_task" ("state", "created_at");
CREATE INDEX "idx_task_task_id" ON "public"."model_gateway_task" ("task_id");
CREATE INDEX "idx_task_tenant_model" ON "public"."model_gateway_task" ("tenant_id", "model_name");
CREATE UNIQUE INDEX "uk_task_task_id" ON "public"."model_gateway_task" ("task_id");
COMMENT ON TABLE "public"."model_gateway_task" IS '模型网关任务表';
COMMENT ON COLUMN "public"."model_gateway_task"."model_name" IS '模型名称';
COMMENT ON COLUMN "public"."model_gateway_task"."task_id" IS '任务ID（对外返回）';
COMMENT ON COLUMN "public"."model_gateway_task"."biz_name" IS '业务名称';
COMMENT ON COLUMN "public"."model_gateway_task"."callback_url" IS '回调地址';
COMMENT ON COLUMN "public"."model_gateway_task"."state" IS '0排队中/1执行中/2成功/3失败/4已下载';
COMMENT ON COLUMN "public"."model_gateway_task"."retry_count" IS '已重试次数';
COMMENT ON COLUMN "public"."model_gateway_task"."error_msg" IS '错误信息';
COMMENT ON COLUMN "public"."model_gateway_task"."result_file" IS '结果文件：{oss_file, file_type, file_size}';
COMMENT ON COLUMN "public"."model_gateway_task"."request_payload" IS '请求参数（JSON）';
COMMENT ON COLUMN "public"."model_gateway_task"."duration_seconds" IS '耗时（秒）';
COMMENT ON COLUMN "public"."model_gateway_task"."epicycle_id" IS '轮次ID';
COMMENT ON COLUMN "public"."model_gateway_task"."billing_data" IS '计费数据';
COMMENT ON COLUMN "public"."model_gateway_task"."build_model_name" IS '构建模型名称';
-- 3. 操作日志表
CREATE TABLE "public"."model_gateway_logs_op" (
"id" int8 NOT NULL,
"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),
"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" int2 NOT NULL DEFAULT 1,
"error_msg" text DEFAULT '',
"cost_ms" int8 NOT NULL DEFAULT 0,
"request_payload" jsonb,
"response_payload" jsonb,
CONSTRAINT "model_gateway_logs_op_pkey" PRIMARY KEY ("id")
);
ALTER TABLE "public"."model_gateway_logs_op" OWNER TO "postgres";
CREATE INDEX "idx_op_log_biz_name" ON "public"."model_gateway_logs_op" ("biz_name");
CREATE INDEX "idx_op_log_deleted_at" ON "public"."model_gateway_logs_op" ("deleted_at");
CREATE INDEX "idx_op_log_model_name" ON "public"."model_gateway_logs_op" ("model_name");
CREATE INDEX "idx_op_log_op_type" ON "public"."model_gateway_logs_op" ("op_type");
CREATE INDEX "idx_op_log_task_id" ON "public"."model_gateway_logs_op" ("task_id");
CREATE INDEX "idx_op_log_tenant_time" ON "public"."model_gateway_logs_op" ("tenant_id", "created_at");
CREATE INDEX "idx_op_log_model_time" ON "public"."model_gateway_logs_op" ("model_name", "created_at");
COMMENT ON TABLE "public"."model_gateway_logs_op" IS '操作日志表';
COMMENT ON COLUMN "public"."model_gateway_logs_op"."ip" IS '客户端IP';
COMMENT ON COLUMN "public"."model_gateway_logs_op"."user_agent" IS 'User-Agent';
COMMENT ON COLUMN "public"."model_gateway_logs_op"."api_path" IS '接口路径';
COMMENT ON COLUMN "public"."model_gateway_logs_op"."http_method" IS 'HTTP方法';
COMMENT ON COLUMN "public"."model_gateway_logs_op"."biz_name" IS '业务名称';
COMMENT ON COLUMN "public"."model_gateway_logs_op"."model_name" IS '模型名称';
COMMENT ON COLUMN "public"."model_gateway_logs_op"."task_id" IS '任务ID';
COMMENT ON COLUMN "public"."model_gateway_logs_op"."op_type" IS '操作类型';
COMMENT ON COLUMN "public"."model_gateway_logs_op"."success" IS '是否成功：1成功/0失败';
COMMENT ON COLUMN "public"."model_gateway_logs_op"."error_msg" IS '错误信息';
COMMENT ON COLUMN "public"."model_gateway_logs_op"."cost_ms" IS '耗时（毫秒）';
COMMENT ON COLUMN "public"."model_gateway_logs_op"."request_payload" IS '请求 JSON';
COMMENT ON COLUMN "public"."model_gateway_logs_op"."response_payload" IS '响应 JSON';
-- 4. 按天统计表
CREATE TABLE "public"."model_gateway_logs_stat" (
"day" date NOT NULL,
"tenant_id" int8 NOT NULL DEFAULT 0,
"creator" varchar(64) NOT NULL DEFAULT '',
"model_name" varchar(128) NOT NULL DEFAULT '',
"request_count" int8 NOT NULL DEFAULT 0,
"created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updated_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE "public"."model_gateway_logs_stat" OWNER TO "postgres";
CREATE INDEX "idx_stat_creator" ON "public"."model_gateway_logs_stat" ("creator");
CREATE INDEX "idx_stat_day" ON "public"."model_gateway_logs_stat" ("day");
CREATE INDEX "idx_stat_model_name" ON "public"."model_gateway_logs_stat" ("model_name");
CREATE INDEX "idx_stat_tenant_day" ON "public"."model_gateway_logs_stat" ("tenant_id", "day");
COMMENT ON TABLE "public"."model_gateway_logs_stat" IS '按天统计表';
COMMENT ON COLUMN "public"."model_gateway_logs_stat"."day" IS '天（YYYY-MM-DD）';
COMMENT ON COLUMN "public"."model_gateway_logs_stat"."tenant_id" IS '租户ID';
COMMENT ON COLUMN "public"."model_gateway_logs_stat"."creator" IS '创建人';
COMMENT ON COLUMN "public"."model_gateway_logs_stat"."model_name" IS '模型名称';
COMMENT ON COLUMN "public"."model_gateway_logs_stat"."request_count" IS '请求次数';
Reference in New Issue View Git Blame Copy Permalink