zsjz-ai/docs/db/zsjz-ai-schema-20261028.sql

-- PostgreSQL database schema dump (structure only, no data)
-- Database: zsjz-ai
-- Server: PostgreSQL 18.4 (Ubuntu 18.4-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
-- Generated: 2026-10-28 16:18:36
-- Note: generated from PostgreSQL catalogs because pg_dump is not installed on this host.

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET search_path = public, pg_catalog;

-- Extensions
CREATE EXTENSION IF NOT EXISTS "vector";

-- Tables
CREATE TABLE public."agent" (
    "row_id" bigint NOT NULL,
    "owner_id" bigint,
    "agent_id" character varying(8),
    "workspace_path" character varying(128),
    "name" character varying(50),
    "description" text,
    "sys_prompt" text,
    "model" character varying(32),
    "max_iters" integer,
    "tools_allow_json" text,
    "tools_deny_json" text,
    "skills_allow_json" text,
    "skills_deny_json" text,
    "run_as" character varying(30),
    "fork_of" character varying(30),
    "skill_repositories_json" text,
    "sandbox_mode" character varying(100),
    "sandbox_scope" character varying(100),
    "user_id" bigint,
    "is_builtin" smallint DEFAULT 0 NOT NULL,
    "tool_packs_json" text,
    "knowledge_base_id" bigint
);

CREATE TABLE public."agent_chat_session" (
    "id" bigint NOT NULL,
    "agent_id" bigint,
    "case_id" bigint,
    "title" character varying(100),
    "context" jsonb DEFAULT '{}'::jsonb,
    "message_count" integer DEFAULT 0,
    "total_tokens" bigint DEFAULT 0,
    "last_message_at" timestamp(6) without time zone,
    "pinned" boolean DEFAULT false,
    "attachments" jsonb,
    "create_at" timestamp(6) without time zone DEFAULT CURRENT_TIMESTAMP,
    "update_at" timestamp(6) without time zone DEFAULT CURRENT_TIMESTAMP,
    "model_id" bigint,
    "user_id" bigint
);

CREATE TABLE public."agent_insight_message" (
    "id" bigint NOT NULL,
    "session_id" bigint NOT NULL,
    "case_id" bigint NOT NULL,
    "seq" bigint NOT NULL,
    "role" character varying(16) NOT NULL,
    "content" text,
    "content_type" character varying(16) DEFAULT 'text'::character varying NOT NULL,
    "message_type" character varying(16) DEFAULT 'chat'::character varying NOT NULL,
    "metadata" jsonb,
    "tool_events" jsonb,
    "output_json" jsonb,
    "token_count" integer DEFAULT 0 NOT NULL,
    "reply_to_message_id" bigint,
    "starred" boolean DEFAULT false NOT NULL,
    "status" character varying(16) DEFAULT 'DONE'::character varying NOT NULL,
    "create_at" timestamp without time zone DEFAULT now() NOT NULL
);

CREATE TABLE public."agent_insight_session" (
    "id" bigint NOT NULL,
    "case_id" bigint NOT NULL,
    "biz_type" character varying(32) DEFAULT 'CALL_CONTINUOUS'::character varying NOT NULL,
    "scope" character varying(16) DEFAULT 'BATCH'::character varying NOT NULL,
    "agent_row_id" bigint,
    "model_id" bigint NOT NULL,
    "title" character varying(128),
    "context" jsonb NOT NULL,
    "context_hash" character varying(64) NOT NULL,
    "sys_prompt" text NOT NULL,
    "message_count" integer DEFAULT 0 NOT NULL,
    "last_seq" bigint DEFAULT 0 NOT NULL,
    "total_tokens" bigint DEFAULT 0 NOT NULL,
    "last_message_at" timestamp without time zone,
    "pinned" boolean DEFAULT false NOT NULL,
    "status" character varying(16) DEFAULT 'ACTIVE'::character varying NOT NULL,
    "create_at" timestamp without time zone DEFAULT now() NOT NULL,
    "update_at" timestamp without time zone DEFAULT now() NOT NULL
);

CREATE TABLE public."agent_message" (
    "id" bigint NOT NULL,
    "session_id" bigint,
    "role" character varying(20),
    "content" text,
    "content_type" character varying(20) DEFAULT 'text'::character varying,
    "token_count" integer DEFAULT 0,
    "metadata" jsonb DEFAULT '{}'::jsonb,
    "tool_events" jsonb DEFAULT '[]'::jsonb,
    "parent_id" bigint,
    "create_at" timestamp(6) without time zone DEFAULT CURRENT_TIMESTAMP,
    "message_type" character varying(32) DEFAULT 'text'::character varying,
    "reply_to_message_id" bigint,
    "starred" boolean DEFAULT false
);

CREATE TABLE public."agent_model" (
    "id" bigint NOT NULL,
    "model_id" character varying(128),
    "name" character varying(128),
    "type" character varying(20) DEFAULT 'llm'::character varying,
    "api_key" character varying(128),
    "base_url" character varying(256),
    "config" jsonb DEFAULT '{}'::jsonb,
    "headers_json" jsonb DEFAULT '{}'::jsonb,
    "extra_json" jsonb DEFAULT '{}'::jsonb,
    "status" character varying(10) DEFAULT 'active'::character varying,
    "create_by" bigint,
    "create_at" timestamp(6) without time zone DEFAULT CURRENT_TIMESTAMP,
    "update_at" timestamp(6) without time zone DEFAULT CURRENT_TIMESTAMP,
    "default_model" boolean DEFAULT false,
    "provider" character varying(64)
);

CREATE TABLE public."agent_model_provider" (
    "id" bigint NOT NULL,
    "name" character varying(64),
    "base_url" character varying(256),
    "config" jsonb DEFAULT '{}'::jsonb,
    "status" character varying(20) DEFAULT 'active'::character varying,
    "models_endpoint" character varying(512),
    "headers_json" jsonb DEFAULT '{}'::jsonb,
    "extra_json" jsonb DEFAULT '{}'::jsonb,
    "type" character varying(10)
);

CREATE TABLE public."agent_tool" (
    "id" bigint NOT NULL,
    "tool_name" character varying(128) NOT NULL,
    "tool_class" character varying(255),
    "tool_method" character varying(128),
    "group_code" character varying(32),
    "group_label" character varying(64),
    "group_description" text,
    "group_sort" integer,
    "default_active" boolean,
    "description" text,
    "parameters" jsonb,
    "read_only" boolean,
    "status" character varying(16),
    "create_at" timestamp without time zone,
    "update_at" timestamp without time zone
);

CREATE TABLE public."agent_tool_pack" (
    "id" bigint NOT NULL,
    "user_id" bigint NOT NULL,
    "name" character varying(100) NOT NULL,
    "description" character varying(500),
    "tool_names" text DEFAULT '[]'::text NOT NULL,
    "create_at" timestamp without time zone DEFAULT now(),
    "update_at" timestamp without time zone DEFAULT now(),
    "is_builtin" smallint DEFAULT 0 NOT NULL
);

CREATE TABLE public."ai_clean_batch" (
    "id" bigint NOT NULL,
    "case_id" bigint NOT NULL,
    "batch_id" bigint NOT NULL,
    "stage" character varying(20) NOT NULL,
    "file_count" integer DEFAULT 0,
    "sheet_total" integer DEFAULT 0,
    "matched_count" integer DEFAULT 0,
    "ai_judged_count" integer DEFAULT 0,
    "existing_hit_count" integer DEFAULT 0,
    "ai_load_count" integer DEFAULT 0,
    "need_review_count" integer DEFAULT 0,
    "failed_count" integer DEFAULT 0,
    "need_review_detail" text,
    "last_error" character varying(1000),
    "create_time" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    "update_time" timestamp without time zone,
    "loaded_rows" bigint DEFAULT 0,
    "rejected_rows" bigint DEFAULT 0,
    "unprocessed_rows" bigint DEFAULT 0,
    "self_built_count" integer DEFAULT 0
);

CREATE TABLE public."ai_clean_field" (
    "id" bigint NOT NULL,
    "ai_template_id" bigint NOT NULL,
    "column_name" character varying(5) NOT NULL,
    "field_name_cn" character varying(200),
    "field_name_en" character varying(5),
    "field_type" character varying(50),
    "required" integer DEFAULT 0,
    "field_len" integer DEFAULT 0,
    "field_sort" integer DEFAULT 0,
    "direction_conf" text,
    "col_index" integer,
    "matched" integer DEFAULT 1,
    "create_time" timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE public."ai_clean_job" (
    "id" bigint NOT NULL,
    "case_id" bigint NOT NULL,
    "file_id" bigint NOT NULL,
    "sheet_id" bigint NOT NULL,
    "block_no" integer DEFAULT 0,
    "ver" integer DEFAULT 1,
    "batch_id" bigint,
    "file_name" character varying(500),
    "sheet_name" character varying(200),
    "status" character varying(30) NOT NULL,
    "exit_code" character varying(10),
    "matched_by" character varying(20),
    "match_tier" character varying(20),
    "ref_template_id" integer,
    "ai_template_id" bigint,
    "header_md5" character varying(64),
    "header_row" integer,
    "confidence" numeric(6,4),
    "detail" text,
    "draft" text,
    "model_name" character varying(200),
    "token_in" integer,
    "token_out" integer,
    "llm_calls" integer DEFAULT 0,
    "cost_ms" bigint,
    "row_total" integer,
    "row_loaded" integer,
    "fail_reason" character varying(1000),
    "create_time" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    "update_time" timestamp without time zone,
    "row_rejected" integer,
    "row_warned" integer,
    "row_unprocessed" integer
);

CREATE TABLE public."ai_clean_rule" (
    "id" bigint NOT NULL,
    "ai_template_id" bigint,
    "col_index" integer NOT NULL,
    "field_name_en" character varying(200),
    "hints" text,
    "ref_template_id" integer,
    "ver" integer DEFAULT 1,
    "create_time" timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE public."ai_clean_synonym" (
    "id" bigint NOT NULL,
    "std_word" character varying(100) NOT NULL,
    "alias_word" character varying(100) NOT NULL,
    "remark" character varying(200),
    "status" smallint DEFAULT 1,
    "create_by" bigint,
    "create_time" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    "update_time" timestamp without time zone
);

CREATE TABLE public."ai_clean_template" (
    "id" bigint NOT NULL,
    "table_name_cn" character varying(200) NOT NULL,
    "table_name_en" character varying(200) NOT NULL,
    "headers" character varying(4000),
    "header_md5" character varying(64) NOT NULL,
    "header_line_no" integer,
    "func_regex" text,
    "block_no" integer,
    "struct_ops" text,
    "needs_struct" integer DEFAULT 0,
    "category" character varying(50),
    "category_l2" character varying(50),
    "main_ref_template_id" integer,
    "match_tier" character varying(20),
    "status" character varying(20) DEFAULT 'DRAFT'::character varying,
    "ver" integer DEFAULT 1,
    "confidence" numeric(6,4),
    "case_id_src" bigint,
    "create_by" bigint,
    "create_time" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    "update_time" timestamp without time zone
);

CREATE TABLE public."bank_card_issuer_info" (
    "bank_bin" character varying(8) NOT NULL,
    "bank_name" character varying(126),
    "card_type" character varying(10),
    "flag" integer
);

CREATE TABLE public."case_info" (
    "id" bigint NOT NULL,
    "name" character varying(30),
    "pwd" character varying(16),
    "type" character varying(20),
    "remark" character varying(200),
    "db" character varying(64),
    "db_path" character varying(200),
    "create_time" timestamp(0) without time zone,
    "init" integer DEFAULT 0,
    "owner_id" bigint
);

CREATE TABLE public."chat_attachment" (
    "id" bigint NOT NULL,
    "user_id" bigint NOT NULL,
    "case_id" bigint NOT NULL,
    "session_id" bigint NOT NULL,
    "file_name" character varying(500) NOT NULL,
    "file_path" character varying(1000) NOT NULL,
    "sidecar_path" character varying(1000),
    "mime" character varying(100),
    "kind" character varying(20) NOT NULL,
    "size_bytes" bigint DEFAULT 0,
    "char_count" integer DEFAULT 0,
    "row_count" integer DEFAULT 0,
    "truncated" integer DEFAULT 0,
    "inline_text" text,
    "index_blocks" integer DEFAULT 0,
    "index_status" character varying(20),
    "status" character varying(20) NOT NULL,
    "link_message_id" bigint,
    "fail_reason" character varying(1000),
    "create_time" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    "update_time" timestamp without time zone
);

CREATE TABLE public."file_ai_profile" (
    "id" bigint NOT NULL,
    "caseid" bigint,
    "fileid" bigint,
    "batchid" bigint,
    "filename" character varying,
    "filetype" character varying,
    "mimetype" character varying,
    "category" character varying,
    "summary" character varying,
    "keywords" character varying,
    "sampletext" character varying,
    "samplelength" integer,
    "parsestatus" character varying,
    "failreason" character varying,
    "modelname" character varying,
    "costmillis" bigint,
    "createdatetime" timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE public."gateway_node" (
    "id" character varying(64) NOT NULL,
    "service_id" character varying(64) NOT NULL,
    "name" character varying(128) NOT NULL,
    "base_url" character varying(255) NOT NULL,
    "enabled" smallint DEFAULT 1 NOT NULL,
    "remark" character varying(512),
    "create_time" timestamp without time zone DEFAULT now() NOT NULL,
    "update_time" timestamp without time zone DEFAULT now() NOT NULL
);

CREATE TABLE public."gateway_service" (
    "id" character varying(64) NOT NULL,
    "name" character varying(128) NOT NULL,
    "path_prefix" character varying(128) NOT NULL,
    "login_path" character varying(128),
    "health_path" character varying(255) NOT NULL,
    "default_node_id" character varying(64),
    "enabled" smallint DEFAULT 1 NOT NULL,
    "create_time" timestamp without time zone DEFAULT now() NOT NULL,
    "update_time" timestamp without time zone DEFAULT now() NOT NULL
);

CREATE TABLE public."gateway_user_route" (
    "id" bigint NOT NULL,
    "service_id" character varying(64) NOT NULL,
    "user_id" bigint,
    "username" character varying(64),
    "node_id" character varying(64) NOT NULL,
    "create_time" timestamp without time zone DEFAULT now() NOT NULL,
    "update_time" timestamp without time zone DEFAULT now() NOT NULL
);

CREATE TABLE public."kb_vec_1_bge_m3_d1024" (
    "doc_id" character varying(128) NOT NULL,
    "content" text,
    "payload" jsonb,
    "embedding" vector(1024)
);

CREATE TABLE public."kb_vec_1_d1024" (
    "doc_id" character varying(128) NOT NULL,
    "content" text,
    "payload" jsonb,
    "embedding" vector(1024)
);

CREATE TABLE public."knowledge_base" (
    "id" bigint NOT NULL,
    "user_id" bigint NOT NULL,
    "is_builtin" smallint DEFAULT 0 NOT NULL,
    "name" character varying(100) NOT NULL,
    "description" character varying(500),
    "create_time" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    "update_time" timestamp without time zone
);

CREATE TABLE public."knowledge_doc" (
    "id" bigint NOT NULL,
    "kb_id" bigint NOT NULL,
    "user_id" bigint NOT NULL,
    "file_name" character varying(500) NOT NULL,
    "file_path" character varying(1000) NOT NULL,
    "sidecar_path" character varying(1000),
    "mime" character varying(100),
    "size_bytes" bigint DEFAULT 0,
    "char_count" integer DEFAULT 0,
    "chunk_count" integer DEFAULT 0,
    "index_status" character varying(20) DEFAULT 'PENDING'::character varying,
    "fail_reason" character varying(1000),
    "download_count" integer DEFAULT 0,
    "create_time" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    "update_time" timestamp without time zone
);

CREATE TABLE public."knowledge_doc_download_log" (
    "id" bigint NOT NULL,
    "kb_id" bigint NOT NULL,
    "doc_id" bigint NOT NULL,
    "user_id" bigint NOT NULL,
    "file_name" character varying(500) NOT NULL,
    "download_time" timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE public."lunar" (
    "id" integer NOT NULL,
    "gl" character varying(20),
    "nl" character varying(20)
);

CREATE TABLE public."meta_raw_file" (
    "id" bigint NOT NULL,
    "file_name" character varying,
    "batch_id" bigint,
    "password" character varying(32),
    "charset" character varying(16),
    "delimiter" character varying(10),
    "file_type" character varying(8),
    "file_path" character varying,
    "file_size" bigint DEFAULT 0,
    "total_rows" integer DEFAULT 0,
    "success_rows" integer DEFAULT 0,
    "fail_rows" integer DEFAULT 0,
    "file_status" character varying(20),
    "md5" character varying(32),
    "created_at" timestamp(6) without time zone DEFAULT CURRENT_TIMESTAMP,
    "total_sheets" integer,
    "workspace_id" bigint
);

CREATE TABLE public."meta_raw_sheet" (
    "table_name" character varying(100),
    "workspace_id" bigint,
    "sheet_name" character varying(100),
    "total_rows" bigint DEFAULT 0,
    "column_count" integer DEFAULT 0,
    "headers_json" jsonb,
    "ai_clean_status" character varying(20) DEFAULT 'PENDING'::character varying,
    "last_processed_row_id" bigint DEFAULT 0,
    "created_at" timestamp(6) without time zone DEFAULT now(),
    "updated_at" timestamp(6) without time zone DEFAULT now(),
    "id" bigint NOT NULL,
    "file_id" bigint,
    "template_id" bigint,
    "lineNo" integer,
    "header" text,
    "success_rows" integer,
    "fail_rows" integer,
    "sheet_status" character varying(20),
    "table_rule_list" jsonb,
    "table_name_en" character varying(20),
    "funcregx" text,
    "main_id" integer
);

CREATE TABLE public."phone_isp" (
    "paragraph" character varying(8) NOT NULL,
    "province" character varying(32),
    "city" character varying(32),
    "isp" character varying(8)
);

CREATE TABLE public."rag_index_status" (
    "workspace_id" bigint NOT NULL,
    "embedding_model_id" bigint,
    "embedding_model_name" character varying(128),
    "dimensions" integer NOT NULL,
    "vector_table" character varying(64) NOT NULL,
    "schema_fingerprint" character varying(64) NOT NULL,
    "table_count" integer DEFAULT 0 NOT NULL,
    "status" character varying(20) DEFAULT 'idle'::character varying NOT NULL,
    "last_error" text,
    "indexed_at" timestamp(6) without time zone,
    "update_at" timestamp(6) without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE public."rag_tpl_bge_m3_d1024" (
    "doc_id" character varying(128) NOT NULL,
    "content" text,
    "payload" jsonb,
    "embedding" vector(1024)
);

CREATE TABLE public."rag_tpl_d1024" (
    "doc_id" character varying(128) NOT NULL,
    "content" text,
    "payload" jsonb,
    "embedding" vector(1024)
);

CREATE TABLE public."rag_ws78431654_d1024" (
    "doc_id" character varying(128) NOT NULL,
    "content" text,
    "payload" jsonb,
    "embedding" vector(1024)
);

CREATE TABLE public."sys_user" (
    "id" bigint NOT NULL,
    "username" character varying(64) NOT NULL,
    "pwd_hash" character varying(100) NOT NULL,
    "display_name" character varying(64),
    "is_admin" smallint DEFAULT 0 NOT NULL,
    "status" smallint DEFAULT 1 NOT NULL,
    "create_time" timestamp without time zone DEFAULT now() NOT NULL,
    "update_time" timestamp without time zone,
    "last_login_time" timestamp without time zone
);

CREATE TABLE public."system_info" (
    "machinecode" text NOT NULL,
    "client_no" text,
    "unit" text,
    "phone" text,
    "first_date_time" date,
    "active_date_time" date,
    "authdatetime" date,
    "overdatetime" date
);

CREATE TABLE public."table_field" (
    "id" bigint NOT NULL,
    "table_id" integer,
    "table_name_en" character varying(30),
    "field_name_cn" character varying(30),
    "field_name_en" character varying(30),
    "field_type" character varying(30),
    "required" integer DEFAULT 0,
    "field_len" integer DEFAULT 0,
    "matched" integer,
    "direction_conf" character varying(512),
    "field_sort" integer,
    "zf" integer DEFAULT 0
);

CREATE TABLE public."table_info" (
    "id" integer NOT NULL,
    "main_id" integer,
    "table_name_cn" character varying(30),
    "table_name_en" character varying(30),
    "func_regex" character varying(256),
    "line_no" integer,
    "headers" text,
    "classify" character varying(30),
    "has_inner" integer DEFAULT 0,
    "has_table" integer,
    "create_time" timestamp(0) without time zone
);

-- Constraints
ALTER TABLE ONLY public."agent" ADD CONSTRAINT "agent_pkey" PRIMARY KEY (row_id);
ALTER TABLE ONLY public."agent_chat_session" ADD CONSTRAINT "ai_chat_session_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."agent_insight_message" ADD CONSTRAINT "agent_insight_message_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."agent_insight_message" ADD CONSTRAINT "uk_aim_session_seq" UNIQUE (session_id, seq);
ALTER TABLE ONLY public."agent_insight_session" ADD CONSTRAINT "agent_insight_session_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."agent_message" ADD CONSTRAINT "ai_message_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."agent_model" ADD CONSTRAINT "ai_model_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."agent_model_provider" ADD CONSTRAINT "ai_model_provider_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."agent_tool" ADD CONSTRAINT "agent_tool_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."agent_tool_pack" ADD CONSTRAINT "agent_tool_pack_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."ai_clean_batch" ADD CONSTRAINT "ai_clean_batch_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."ai_clean_field" ADD CONSTRAINT "ai_clean_field_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."ai_clean_job" ADD CONSTRAINT "ai_clean_job_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."ai_clean_rule" ADD CONSTRAINT "ai_clean_rule_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."ai_clean_synonym" ADD CONSTRAINT "ai_clean_synonym_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."ai_clean_template" ADD CONSTRAINT "ai_clean_template_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."bank_card_issuer_info" ADD CONSTRAINT "bank_card_issuer_info_pkey" PRIMARY KEY (bank_bin);
ALTER TABLE ONLY public."case_info" ADD CONSTRAINT "case_info_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."chat_attachment" ADD CONSTRAINT "chat_attachment_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."file_ai_profile" ADD CONSTRAINT "file_ai_profile_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."gateway_node" ADD CONSTRAINT "gateway_node_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."gateway_service" ADD CONSTRAINT "gateway_service_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."gateway_user_route" ADD CONSTRAINT "gateway_user_route_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."kb_vec_1_bge_m3_d1024" ADD CONSTRAINT "kb_vec_1_bge_m3_d1024_pkey" PRIMARY KEY (doc_id);
ALTER TABLE ONLY public."kb_vec_1_d1024" ADD CONSTRAINT "kb_vec_1_d1024_pkey" PRIMARY KEY (doc_id);
ALTER TABLE ONLY public."knowledge_base" ADD CONSTRAINT "knowledge_base_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."knowledge_doc" ADD CONSTRAINT "knowledge_doc_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."knowledge_doc_download_log" ADD CONSTRAINT "knowledge_doc_download_log_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."lunar" ADD CONSTRAINT "lunar_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."phone_isp" ADD CONSTRAINT "phone_isp_pkey" PRIMARY KEY (paragraph);
ALTER TABLE ONLY public."rag_tpl_bge_m3_d1024" ADD CONSTRAINT "rag_tpl_bge_m3_d1024_pkey" PRIMARY KEY (doc_id);
ALTER TABLE ONLY public."rag_tpl_d1024" ADD CONSTRAINT "rag_tpl_d1024_pkey" PRIMARY KEY (doc_id);
ALTER TABLE ONLY public."rag_ws78431654_d1024" ADD CONSTRAINT "rag_ws78431654_d1024_pkey" PRIMARY KEY (doc_id);
ALTER TABLE ONLY public."sys_user" ADD CONSTRAINT "sys_user_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."system_info" ADD CONSTRAINT "system_info_pkey" PRIMARY KEY (machinecode);
ALTER TABLE ONLY public."table_field" ADD CONSTRAINT "table_field_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."table_info" ADD CONSTRAINT "table_info_pkey" PRIMARY KEY (id);
ALTER TABLE ONLY public."agent_insight_message" ADD CONSTRAINT "fk_aim_session" FOREIGN KEY (session_id) REFERENCES agent_insight_session(id) ON DELETE CASCADE;
ALTER TABLE ONLY public."gateway_node" ADD CONSTRAINT "fk_gateway_node_service" FOREIGN KEY (service_id) REFERENCES gateway_service(id) ON DELETE RESTRICT;
ALTER TABLE ONLY public."gateway_user_route" ADD CONSTRAINT "ck_gateway_user_route_owner" CHECK (((user_id IS NOT NULL) OR (username IS NOT NULL)));
ALTER TABLE ONLY public."gateway_user_route" ADD CONSTRAINT "fk_gateway_user_route_node" FOREIGN KEY (node_id) REFERENCES gateway_node(id) ON DELETE RESTRICT;
ALTER TABLE ONLY public."gateway_user_route" ADD CONSTRAINT "fk_gateway_user_route_service" FOREIGN KEY (service_id) REFERENCES gateway_service(id) ON DELETE RESTRICT;

-- Indexes (excluding constraint-backed indexes)
CREATE INDEX idx_agent_user_id ON public.agent USING btree (user_id);
CREATE INDEX idx_agent_chat_session_user_case ON public.agent_chat_session USING btree (user_id, case_id);
CREATE INDEX idx_aim_output ON public.agent_insight_message USING btree (case_id, create_at DESC) WHERE (output_json IS NOT NULL);
CREATE INDEX idx_ais_case_list ON public.agent_insight_session USING btree (case_id, biz_type, last_message_at DESC);
CREATE INDEX idx_ais_ctx_hash ON public.agent_insight_session USING btree (case_id, context_hash);
CREATE UNIQUE INDEX uk_agent_tool_name ON public.agent_tool USING btree (tool_name);
CREATE INDEX idx_agent_tool_pack_user ON public.agent_tool_pack USING btree (user_id);
CREATE UNIQUE INDEX uk_agent_tool_pack_user_name ON public.agent_tool_pack USING btree (user_id, name);
CREATE INDEX idx_ai_clean_batch_stage ON public.ai_clean_batch USING btree (stage);
CREATE UNIQUE INDEX uk_ai_clean_batch ON public.ai_clean_batch USING btree (case_id, batch_id);
CREATE INDEX idx_ai_clean_field_tpl ON public.ai_clean_field USING btree (ai_template_id);
CREATE INDEX idx_ai_clean_job_file ON public.ai_clean_job USING btree (file_id);
CREATE INDEX idx_ai_clean_job_status ON public.ai_clean_job USING btree (status);
CREATE UNIQUE INDEX uk_ai_clean_job ON public.ai_clean_job USING btree (case_id, file_id, sheet_id, block_no, ver);
CREATE INDEX idx_ai_clean_rule_tpl ON public.ai_clean_rule USING btree (ai_template_id);
CREATE UNIQUE INDEX uk_ai_clean_synonym ON public.ai_clean_synonym USING btree (std_word, alias_word);
CREATE INDEX idx_ai_clean_tpl_category ON public.ai_clean_template USING btree (category);
CREATE INDEX idx_ai_clean_tpl_status ON public.ai_clean_template USING btree (status);
CREATE UNIQUE INDEX uk_ai_clean_tpl_md5 ON public.ai_clean_template USING btree (header_md5);
CREATE INDEX idx_case_info_owner ON public.case_info USING btree (owner_id);
CREATE INDEX idx_chat_att_session ON public.chat_attachment USING btree (session_id);
CREATE INDEX idx_chat_att_status ON public.chat_attachment USING btree (status, link_message_id);
CREATE UNIQUE INDEX uk_gateway_node_service_base_url ON public.gateway_node USING btree (service_id, base_url);
CREATE UNIQUE INDEX uk_gateway_service_path_prefix ON public.gateway_service USING btree (path_prefix);
CREATE INDEX idx_gateway_user_route_node ON public.gateway_user_route USING btree (node_id);
CREATE UNIQUE INDEX uk_gateway_user_route_name ON public.gateway_user_route USING btree (service_id, lower((username)::text)) WHERE (username IS NOT NULL);
CREATE UNIQUE INDEX uk_gateway_user_route_user ON public.gateway_user_route USING btree (service_id, user_id) WHERE (user_id IS NOT NULL);
CREATE INDEX idx_kb_user ON public.knowledge_base USING btree (user_id);
CREATE INDEX idx_kd_kb_user ON public.knowledge_doc USING btree (kb_id, user_id);
CREATE INDEX idx_kdl_doc ON public.knowledge_doc_download_log USING btree (doc_id);
CREATE INDEX idx_kdl_user ON public.knowledge_doc_download_log USING btree (user_id);
CREATE UNIQUE INDEX uk_sys_user_username ON public.sys_user USING btree (lower((username)::text));

-- Sequence ownership

-- Comments
COMMENT ON TABLE public."agent" IS 'Agent 定义表';
COMMENT ON COLUMN public."agent"."row_id" IS '主键 ID（MyBatis-Plus 雪花算法生成）';
COMMENT ON COLUMN public."agent"."owner_id" IS '所属用户 ID（软外键关联 UserEntity）';
COMMENT ON COLUMN public."agent"."agent_id" IS 'Agent 唯一标识';
COMMENT ON COLUMN public."agent"."workspace_path" IS '用户提供的 Agent 工作区路径（可为空）';
COMMENT ON COLUMN public."agent"."name" IS 'Agent 名称';
COMMENT ON COLUMN public."agent"."description" IS 'Agent 描述信息';
COMMENT ON COLUMN public."agent"."sys_prompt" IS '系统提示词（System Prompt）';
COMMENT ON COLUMN public."agent"."model" IS '模型名称';
COMMENT ON COLUMN public."agent"."max_iters" IS '最大迭代次数';
COMMENT ON COLUMN public."agent"."tools_allow_json" IS '允许使用的工具列表（JSON 数组字符串）';
COMMENT ON COLUMN public."agent"."tools_deny_json" IS '禁止使用的工具列表（JSON 数组字符串）';
COMMENT ON COLUMN public."agent"."skills_allow_json" IS '允许使用的技能列表（JSON 数组字符串）';
COMMENT ON COLUMN public."agent"."skills_deny_json" IS '禁止使用的技能列表（JSON 数组字符串）';
COMMENT ON COLUMN public."agent"."run_as" IS '运行身份';
COMMENT ON COLUMN public."agent"."fork_of" IS '派生来源 Agent ID';
COMMENT ON COLUMN public."agent"."skill_repositories_json" IS '技能仓库配置列表（JSON 数组字符串）';
COMMENT ON COLUMN public."agent"."sandbox_mode" IS '沙箱执行模式（local / sandbox）';
COMMENT ON COLUMN public."agent"."sandbox_scope" IS '沙箱隔离范围（SESSION / USER / AGENT / GLOBAL）';
COMMENT ON COLUMN public."agent"."tool_packs_json" IS '方案勾选的工具包 id 列表（JSON 字符串数组）；与 tools_allow_json 并集构成业务工具白名单';
COMMENT ON COLUMN public."agent_chat_session"."user_id" IS '会话归属用户ID（Sa-Token loginId）';
COMMENT ON TABLE public."agent_insight_message" IS 'AI研判消息（独立存储；case_id 冗余自会话表，用于强制归属过滤）';
COMMENT ON COLUMN public."agent_insight_message"."case_id" IS '冗余自会话表：保证消息查询永远可带 case_id 过滤';
COMMENT ON COLUMN public."agent_insight_message"."seq" IS '会话内单调序号，分页排序列（勿用 create_at/id 排序）';
COMMENT ON COLUMN public."agent_insight_message"."message_type" IS 'chat | first_round(首轮自动分析) | followup';
COMMENT ON COLUMN public."agent_insight_message"."output_json" IS '```insight 围栏解析后的结构化产物，仅首轮 assistant 消息有值';
COMMENT ON COLUMN public."agent_insight_message"."status" IS 'DONE | INTERRUPTED（用户中止/异常时保留已生成正文）';
COMMENT ON TABLE public."agent_insight_session" IS 'AI研判会话（独立存储，不复用通用对话表）';
COMMENT ON COLUMN public."agent_insight_session"."case_id" IS '案件ID：案件级硬隔离边界，所有查询强制携带';
COMMENT ON COLUMN public."agent_insight_session"."biz_type" IS '研判场景：首期恒 CALL_CONTINUOUS，为将来同表扩场景预留';
COMMENT ON COLUMN public."agent_insight_session"."scope" IS '分析范围：BATCH=整批结果集，PAIR=单条关系对（预留）';
COMMENT ON COLUMN public."agent_insight_session"."context" IS '查询条件+结果快照，动态系统提示词的数据源';
COMMENT ON COLUMN public."agent_insight_session"."context_hash" IS 'context 的 sha256，用于同条件复用与去重提示';
COMMENT ON COLUMN public."agent_insight_session"."sys_prompt" IS '会话内实际使用的完整系统提示词，仅运维/追溯读，不通过接口下发';
COMMENT ON COLUMN public."agent_insight_session"."last_seq" IS '会话内消息序号水位，消息 seq 由它原子分配';
COMMENT ON COLUMN public."agent_insight_session"."status" IS 'ACTIVE | STREAMING（流式进行中，用于并发抢占）| ARCHIVED';
COMMENT ON TABLE public."agent_message" IS '消息表';
COMMENT ON COLUMN public."agent_message"."tool_events" IS '工具事件流（tool_call、tool_result、subagent_* 等），与 metadata 解耦存储';
COMMENT ON COLUMN public."agent_message"."message_type" IS '消息类型：text-文本, multimodal_image-多模态图片';
COMMENT ON COLUMN public."agent_message"."starred" IS '是否收藏';
COMMENT ON TABLE public."agent_model" IS '模型配置i表
';
COMMENT ON TABLE public."agent_tool_pack" IS '研判方案工具包：用户自定义工具组合，在「插件与技能 → 工具包」维护、新建方案时勾选';
COMMENT ON COLUMN public."agent_tool_pack"."user_id" IS '创建者（sa-token 登录用户 ID），用户隔离依据';
COMMENT ON COLUMN public."agent_tool_pack"."name" IS '工具包名（同用户内唯一）';
COMMENT ON COLUMN public."agent_tool_pack"."tool_names" IS '工具名清单（JSON 字符串数组），取值 agent_tool.tool_name（业务工具）';
COMMENT ON COLUMN public."agent_tool_pack"."is_builtin" IS '1=系统内置（全局可见、不可编辑/删除）；0=用户自建';
COMMENT ON COLUMN public."case_info"."id" IS '案件 ID（MyBatis-Plus 雪花算法生成）';
COMMENT ON COLUMN public."case_info"."owner_id" IS '案件归属用户 ID（sys_user.id），仅创建者可见';
COMMENT ON TABLE public."gateway_node" IS '网关后端节点（一台 ai-server）';
COMMENT ON COLUMN public."gateway_node"."base_url" IS 'http(s)://host:port，不含路径';
COMMENT ON TABLE public."gateway_service" IS '网关服务定义（路径前缀 -> 节点组）';
COMMENT ON COLUMN public."gateway_service"."login_path" IS '相对路径前缀的登录路径，如 /auth/login；空 = 不做登录粘性';
COMMENT ON COLUMN public."gateway_service"."default_node_id" IS '存量用户兜底节点 id；无绑定的已登录用户会被绑定到该节点';
COMMENT ON TABLE public."gateway_user_route" IS '网关用户粘性路由（用户 -> 节点）';
COMMENT ON COLUMN public."gateway_user_route"."id" IS '路由 ID（应用侧雪花算法生成）';
COMMENT ON COLUMN public."gateway_user_route"."node_id" IS '绑定的目标节点；节点不可用时网关返回 503，不自动切换';
COMMENT ON TABLE public."rag_index_status" IS 'RAG 表结构向量索引状态表';
COMMENT ON COLUMN public."rag_index_status"."workspace_id" IS '工作空间ID';
COMMENT ON COLUMN public."rag_index_status"."embedding_model_id" IS '构建索引使用的嵌入模型ID（model.id）';
COMMENT ON COLUMN public."rag_index_status"."embedding_model_name" IS '嵌入模型名称（冗余展示用）';
COMMENT ON COLUMN public."rag_index_status"."dimensions" IS '向量维度';
COMMENT ON COLUMN public."rag_index_status"."vector_table" IS 'pgvector 向量表名（rag_ws{workspaceId}_d{dims}）';
COMMENT ON COLUMN public."rag_index_status"."schema_fingerprint" IS 'DuckDB 表结构指纹（SHA-256），相同则跳过重建';
COMMENT ON COLUMN public."rag_index_status"."table_count" IS '已向量化的表数量';
COMMENT ON COLUMN public."rag_index_status"."status" IS '状态：idle-未索引, indexing-索引中, success-成功, failed-失败';
COMMENT ON COLUMN public."rag_index_status"."last_error" IS '最近一次失败原因';
COMMENT ON COLUMN public."rag_index_status"."indexed_at" IS '最近一次成功索引时间';
COMMENT ON COLUMN public."rag_index_status"."update_at" IS '更新时间';
COMMENT ON TABLE public."sys_user" IS '系统用户表（仅认证，不做权限体系）';
COMMENT ON COLUMN public."sys_user"."id" IS '用户 ID（MyBatis-Plus 雪花算法生成）';
COMMENT ON COLUMN public."sys_user"."pwd_hash" IS 'BCrypt 密码哈希';
COMMENT ON COLUMN public."sys_user"."is_admin" IS '1=管理员（可调用 /user/**） 0=普通用户';
COMMENT ON COLUMN public."sys_user"."status" IS '1=启用 0=停用';