-- 需要先安装pgvector这个扩展（https://github.com/pgvector/pgvector） -- CREATE EXTENSION vector; SET client_encoding = 'UTF8'; CREATE SCHEMA public; CREATE TABLE public.adi_ai_image ( id bigserial primary key, user_id bigint DEFAULT '0'::bigint NOT NULL, uuid character varying(32) DEFAULT ''::character varying NOT NULL, prompt character varying(1024) DEFAULT ''::character varying NOT NULL, generate_size character varying(20) DEFAULT ''::character varying NOT NULL, generate_number integer DEFAULT 1 NOT NULL, original_image character varying(1000) DEFAULT ''::character varying NOT NULL, mask_image character varying(1000) DEFAULT ''::character varying NOT NULL, resp_images_path character varying(2048) DEFAULT ''::character varying NOT NULL, generated_images character varying(2048) DEFAULT ''::character varying NOT NULL, interacting_method smallint DEFAULT '1'::smallint NOT NULL, process_status smallint DEFAULT '1'::smallint NOT NULL, create_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, update_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, is_deleted boolean DEFAULT false NOT NULL, CONSTRAINT adi_ai_image_generate_number_check CHECK (((generate_number >= 1) AND (generate_number <= 10))), CONSTRAINT adi_ai_image_interacting_method_check CHECK ((interacting_method = ANY (ARRAY [1, 2, 3]))), CONSTRAINT adi_ai_image_process_status_check CHECK ((process_status = ANY (ARRAY [1, 2, 3]))), CONSTRAINT adi_ai_image_user_id_check CHECK ((user_id >= 0)) ); ALTER TABLE ONLY public.adi_ai_image ADD CONSTRAINT udx_uuid UNIQUE (uuid); COMMENT ON TABLE public.adi_ai_image IS 'Images generated by ai'; COMMENT ON COLUMN public.adi_ai_image.user_id IS 'The user who generated the image'; COMMENT ON COLUMN public.adi_ai_image.uuid IS 'The uuid of the request of generated images'; COMMENT ON COLUMN public.adi_ai_image.prompt IS 'The prompt for generating images'; COMMENT ON COLUMN public.adi_ai_image.generate_size IS 'The size of the generated images. Must be one of "256x256", "512x512", or "1024x1024"'; COMMENT ON COLUMN public.adi_ai_image.generate_number IS 'The number of images to generate. Must be between 1 and 10. Defaults to 1.'; COMMENT ON COLUMN public.adi_ai_image.original_image IS 'The path of the original image (local path or http path), interacting_method must be 2/3'; COMMENT ON COLUMN public.adi_ai_image.mask_image IS 'The path of the mask image (local path or http path), interacting_method must be 2'; COMMENT ON COLUMN public.adi_ai_image.resp_images_path IS 'The url of the generated images which from openai response, separated by commas'; COMMENT ON COLUMN public.adi_ai_image.generated_images IS 'The path of the generated images, separated by commas'; COMMENT ON COLUMN public.adi_ai_image.interacting_method IS '1: Creating images from scratch based on a text prompt; 2: Creating edits of an existing image based on a new text prompt; 3: Creating variations of an existing image'; COMMENT ON COLUMN public.adi_ai_image.process_status IS 'Generate image status, 1: doing, 2: fail, 3: success'; COMMENT ON COLUMN public.adi_ai_image.create_time IS 'Timestamp of record creation'; COMMENT ON COLUMN public.adi_ai_image.update_time IS 'Timestamp of record last update, automatically updated on each update'; COMMENT ON COLUMN public.adi_ai_image.is_deleted IS 'Flag indicating whether the record is deleted (0: not deleted, 1: deleted)'; CREATE TABLE public.adi_ai_model ( id bigserial primary key, name character varying(45) DEFAULT ''::character varying NOT NULL, remark character varying(1000), model_status smallint DEFAULT '1'::smallint NOT NULL, create_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, update_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, is_deleted boolean DEFAULT false NOT NULL, CONSTRAINT adi_ai_model_model_status_check CHECK ((model_status = ANY (ARRAY [1, 2]))) ); COMMENT ON TABLE public.adi_ai_model IS 'ai模型'; COMMENT ON COLUMN public.adi_ai_model.name IS 'The name of the AI model'; COMMENT ON COLUMN public.adi_ai_model.remark IS 'Additional remarks about the AI model'; COMMENT ON COLUMN public.adi_ai_model.model_status IS '1: Normal usage, 2: Not available'; COMMENT ON COLUMN public.adi_ai_model.create_time IS 'Timestamp of record creation'; COMMENT ON COLUMN public.adi_ai_model.update_time IS 'Timestamp of record last update, automatically updated on each update'; CREATE TABLE public.adi_conversation ( id bigserial primary key, user_id bigint DEFAULT '0'::bigint NOT NULL, uuid character varying(32) DEFAULT ''::character varying NOT NULL, title character varying(45) DEFAULT ''::character varying NOT NULL, openai_conversation_id character varying(32) DEFAULT ''::character varying NOT NULL, tokens integer DEFAULT 0 NOT NULL, ai_system_message character varying(1000) DEFAULT ''::character varying NOT NULL, ai_model character varying(45) DEFAULT ''::character varying NOT NULL, understand_context_enable boolean DEFAULT false NOT NULL, create_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, update_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, is_deleted boolean DEFAULT false NOT NULL ); COMMENT ON TABLE public.adi_conversation IS '对话表'; COMMENT ON COLUMN public.adi_conversation.user_id IS '用户id'; COMMENT ON COLUMN public.adi_conversation.ai_model IS '模型名称'; COMMENT ON COLUMN public.adi_conversation.title IS '对话标题'; CREATE TABLE public.adi_conversation_message ( id bigserial primary key, parent_message_id bigint DEFAULT '0'::bigint NOT NULL, conversation_id bigint DEFAULT '0'::bigint NOT NULL, conversation_uuid character varying(32) DEFAULT ''::character varying NOT NULL, remark text NOT NULL, uuid character varying(32) DEFAULT ''::character varying NOT NULL, message_role integer DEFAULT 1 NOT NULL, tokens integer DEFAULT 0 NOT NULL, openai_message_id character varying(32) DEFAULT ''::character varying NOT NULL, user_id bigint DEFAULT '0'::bigint NOT NULL, secret_key_type integer DEFAULT 1 NOT NULL, understand_context_msg_pair_num integer DEFAULT 0 NOT NULL, create_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, update_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, is_deleted boolean DEFAULT false NOT NULL ); COMMENT ON TABLE public.adi_conversation_message IS '对话消息表'; COMMENT ON COLUMN public.adi_conversation_message.parent_message_id IS '父级消息id'; COMMENT ON COLUMN public.adi_conversation_message.conversation_id IS '对话id'; COMMENT ON COLUMN public.adi_conversation_message.conversation_uuid IS 'conversation''s uuid'; COMMENT ON COLUMN public.adi_conversation_message.remark IS 'ai回复的消息'; COMMENT ON COLUMN public.adi_conversation_message.uuid IS '唯一标识消息的UUID'; COMMENT ON COLUMN public.adi_conversation_message.message_role IS '产生该消息的角色：1: 用户, 2: 系统, 3: 助手'; COMMENT ON COLUMN public.adi_conversation_message.tokens IS '消耗的token数量'; COMMENT ON COLUMN public.adi_conversation_message.openai_message_id IS 'OpenAI生成的消息ID'; COMMENT ON COLUMN public.adi_conversation_message.user_id IS '用户ID'; COMMENT ON COLUMN public.adi_conversation_message.secret_key_type IS '加密密钥类型'; COMMENT ON COLUMN public.adi_conversation_message.understand_context_msg_pair_num IS '上下文消息对数量'; CREATE TABLE public.adi_file ( id bigserial primary key, name character varying(36) DEFAULT ''::character varying NOT NULL, uuid character varying(32) DEFAULT ''::character varying NOT NULL, ext character varying(36) DEFAULT ''::character varying NOT NULL, user_id bigint DEFAULT '0'::bigint NOT NULL, path character varying(250) DEFAULT ''::character varying NOT NULL, ref_count integer DEFAULT 0 NOT NULL, create_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, update_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, is_deleted boolean DEFAULT false NOT NULL, md5 character varying(128) DEFAULT ''::character varying NOT NULL ); COMMENT ON TABLE public.adi_file IS '文件'; COMMENT ON COLUMN public.adi_file.name IS 'File name'; COMMENT ON COLUMN public.adi_file.uuid IS 'UUID of the file'; COMMENT ON COLUMN public.adi_file.ext IS 'File extension'; COMMENT ON COLUMN public.adi_file.user_id IS '0: System; Other: User'; COMMENT ON COLUMN public.adi_file.path IS 'File path'; COMMENT ON COLUMN public.adi_file.ref_count IS 'The number of references to this file'; COMMENT ON COLUMN public.adi_file.create_time IS 'Timestamp of record creation'; COMMENT ON COLUMN public.adi_file.update_time IS 'Timestamp of record last update, automatically updated on each update'; COMMENT ON COLUMN public.adi_file.is_deleted IS '0: Normal; 1: Deleted'; COMMENT ON COLUMN public.adi_file.md5 IS 'MD5 hash of the file'; CREATE TABLE public.adi_prompt ( id bigserial primary key, user_id bigint DEFAULT '0'::bigint NOT NULL, act character varying(120) DEFAULT ''::character varying NOT NULL, prompt text NOT NULL, create_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, update_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, is_deleted boolean DEFAULT false NOT NULL ); COMMENT ON TABLE public.adi_prompt IS '提示词'; COMMENT ON COLUMN public.adi_prompt.user_id IS '所属用户(0: system)'; COMMENT ON COLUMN public.adi_prompt.act IS '提示词标题'; COMMENT ON COLUMN public.adi_prompt.prompt IS '提示词内容'; COMMENT ON COLUMN public.adi_prompt.create_time IS 'Timestamp of record creation'; COMMENT ON COLUMN public.adi_prompt.update_time IS 'Timestamp of record last update, automatically updated on each update'; COMMENT ON COLUMN public.adi_prompt.is_deleted IS '0:未删除；1：已删除'; CREATE TABLE public.adi_sys_config ( id bigserial primary key, name character varying(100) DEFAULT ''::character varying NOT NULL, value character varying(1000) DEFAULT ''::character varying NOT NULL, create_time timestamp DEFAULT localtimestamp NOT NULL, update_time timestamp DEFAULT localtimestamp NOT NULL, is_deleted boolean DEFAULT false NOT NULL ); COMMENT ON TABLE public.adi_sys_config IS '系统配置表'; COMMENT ON COLUMN public.adi_sys_config.name IS '配置项名称'; COMMENT ON COLUMN public.adi_sys_config.value IS '配置项值'; COMMENT ON COLUMN public.adi_sys_config.create_time IS 'Timestamp of record creation'; COMMENT ON COLUMN public.adi_sys_config.update_time IS 'Timestamp of record last update, automatically updated on each update'; COMMENT ON COLUMN public.adi_sys_config.is_deleted IS '0：未删除；1：已删除'; CREATE TABLE public.adi_user ( id bigserial primary key, name character varying(45) DEFAULT ''::character varying NOT NULL, password character varying(120) DEFAULT ''::character varying NOT NULL, uuid character varying(32) DEFAULT ''::character varying NOT NULL, email character varying(120) DEFAULT ''::character varying NOT NULL, active_time timestamp, user_status smallint DEFAULT '1'::smallint NOT NULL, is_admin boolean DEFAULT false NOT NULL, quota_by_token_daily integer DEFAULT 0 NOT NULL, quota_by_token_monthly integer DEFAULT 0 NOT NULL, quota_by_request_daily integer DEFAULT 0 NOT NULL, quota_by_request_monthly integer DEFAULT 0 NOT NULL, secret_key character varying(120) DEFAULT ''::character varying NOT NULL, understand_context_enable smallint DEFAULT '0'::smallint NOT NULL, understand_context_msg_pair_num integer DEFAULT 0 NOT NULL, quota_by_image_daily integer DEFAULT 0 NOT NULL, quota_by_image_monthly integer DEFAULT 0 NOT NULL, create_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, update_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, is_deleted boolean DEFAULT false NOT NULL ); COMMENT ON TABLE public.adi_user IS '用户表'; COMMENT ON COLUMN public.adi_user.name IS '用户名'; COMMENT ON COLUMN public.adi_user.password IS '密码'; COMMENT ON COLUMN public.adi_user.uuid IS 'UUID of the user'; COMMENT ON COLUMN public.adi_user.email IS '用户邮箱'; COMMENT ON COLUMN public.adi_user.active_time IS '激活时间'; COMMENT ON COLUMN public.adi_user.create_time IS 'Timestamp of record creation'; COMMENT ON COLUMN public.adi_user.update_time IS 'Timestamp of record last update, automatically updated on each update'; COMMENT ON COLUMN public.adi_user.user_status IS '用户状态，1：待验证；2：正常；3：冻结'; COMMENT ON COLUMN public.adi_user.is_admin IS '是否管理员，0：否；1：是'; COMMENT ON COLUMN public.adi_user.is_deleted IS '0：未删除；1：已删除'; COMMENT ON COLUMN public.adi_user.quota_by_token_daily IS '每日token配额'; COMMENT ON COLUMN public.adi_user.quota_by_token_monthly IS '每月token配额'; COMMENT ON COLUMN public.adi_user.quota_by_request_daily IS '每日请求配额'; COMMENT ON COLUMN public.adi_user.quota_by_request_monthly IS '每月请求配额'; COMMENT ON COLUMN public.adi_user.secret_key IS '用户密钥'; COMMENT ON COLUMN public.adi_user.understand_context_enable IS '上下文理解开关'; COMMENT ON COLUMN public.adi_user.understand_context_msg_pair_num IS '上下文消息对数量'; COMMENT ON COLUMN public.adi_user.quota_by_image_daily IS '每日图片配额'; COMMENT ON COLUMN public.adi_user.quota_by_image_monthly IS '每月图片配额'; CREATE TABLE public.adi_user_day_cost ( id bigserial primary key, user_id bigint DEFAULT '0'::bigint NOT NULL, day integer DEFAULT 0 NOT NULL, requests integer DEFAULT 0 NOT NULL, tokens integer DEFAULT 0 NOT NULL, create_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, update_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, secret_key_type integer DEFAULT 0 NOT NULL, images_number integer DEFAULT 0 NOT NULL, is_deleted boolean DEFAULT false NOT NULL ); COMMENT ON TABLE public.adi_user_day_cost IS '用户每天消耗总量表'; COMMENT ON COLUMN public.adi_user_day_cost.user_id IS '用户ID'; COMMENT ON COLUMN public.adi_user_day_cost.day IS '日期，用7位整数表示，如20230901'; COMMENT ON COLUMN public.adi_user_day_cost.requests IS '请求数量'; COMMENT ON COLUMN public.adi_user_day_cost.tokens IS '消耗的token数量'; COMMENT ON COLUMN public.adi_user_day_cost.create_time IS 'Timestamp of record creation'; COMMENT ON COLUMN public.adi_user_day_cost.update_time IS 'Timestamp of record last update, automatically updated on each update'; COMMENT ON COLUMN public.adi_user_day_cost.secret_key_type IS '加密密钥类型'; COMMENT ON COLUMN public.adi_user_day_cost.images_number IS '图片数量'; -- update_time trigger CREATE OR REPLACE FUNCTION update_modified_column() RETURNS TRIGGER AS $$ BEGIN NEW.update_time = CURRENT_TIMESTAMP; RETURN NEW; END; $$ language 'plpgsql'; CREATE TRIGGER trigger_ai_image_update_time BEFORE UPDATE ON adi_ai_image FOR EACH ROW EXECUTE PROCEDURE update_modified_column(); CREATE TRIGGER trigger_ai_model_update_time BEFORE UPDATE ON adi_ai_model FOR EACH ROW EXECUTE PROCEDURE update_modified_column(); CREATE TRIGGER trigger_conv_update_time BEFORE UPDATE ON adi_conversation FOR EACH ROW EXECUTE PROCEDURE update_modified_column(); CREATE TRIGGER trigger_conv_message_update_time BEFORE UPDATE ON adi_conversation_message FOR EACH ROW EXECUTE PROCEDURE update_modified_column(); CREATE TRIGGER trigger_file_update_time BEFORE UPDATE ON adi_file FOR EACH ROW EXECUTE PROCEDURE update_modified_column(); CREATE TRIGGER trigger_prompt_update_time BEFORE UPDATE ON adi_prompt FOR EACH ROW EXECUTE PROCEDURE update_modified_column(); CREATE TRIGGER trigger_sys_config_update_time BEFORE UPDATE ON adi_sys_config FOR EACH ROW EXECUTE PROCEDURE update_modified_column(); CREATE TRIGGER trigger_user_update_time BEFORE UPDATE ON adi_user FOR EACH ROW EXECUTE PROCEDURE update_modified_column(); CREATE TRIGGER trigger_user_day_cost_update_time BEFORE UPDATE ON adi_user_day_cost FOR EACH ROW EXECUTE PROCEDURE update_modified_column(); INSERT INTO adi_sys_config (name, value) VALUES ('openai_setting', '{"secret_key":""}'); INSERT INTO adi_sys_config (name, value) VALUES ('dashscope_setting', '{"api_key":""}'); INSERT INTO adi_sys_config (name, value) VALUES ('qianfan_setting', '{"api_key":"","secret_key":""}'); INSERT INTO adi_sys_config (name, value) VALUES ('request_text_rate_limit', '{"times":24,"minutes":3}'); INSERT INTO adi_sys_config (name, value) VALUES ('request_image_rate_limit', '{"times":6,"minutes":3}'); INSERT INTO adi_sys_config (name, value) VALUES ('conversation_max_num', '50'); INSERT INTO adi_sys_config (name, value) VALUES ('quota_by_token_daily', '10000'); INSERT INTO adi_sys_config (name, value) VALUES ('quota_by_token_monthly', '200000'); INSERT INTO adi_sys_config (name, value) VALUES ('quota_by_request_daily', '150'); INSERT INTO adi_sys_config (name, value) VALUES ('quota_by_request_monthly', '3000'); INSERT INTO adi_sys_config (name, value) VALUES ('quota_by_image_daily', '30'); INSERT INTO adi_sys_config (name, value) VALUES ('quota_by_image_monthly', '300'); INSERT INTO adi_sys_config (name, value) VALUES ('quota_by_qa_ask_daily', '50'); INSERT INTO adi_sys_config (name, value) VALUES ('quota_by_qa_item_monthly', '100'); create table adi_knowledge_base ( id bigserial primary key, uuid varchar(32) default ''::character varying not null, title varchar(250) default ''::character varying not null, remark text default ''::character varying not null, is_public boolean default false not null, owner_id bigint default 0 not null, owner_name varchar(45) default ''::character varying not null, create_time timestamp default CURRENT_TIMESTAMP not null, update_time timestamp default CURRENT_TIMESTAMP not null, is_deleted boolean default false not null ); comment on table adi_knowledge_base is '知识库'; comment on column adi_knowledge_base.title is '知识库名称'; comment on column adi_knowledge_base.remark is '知识库描述'; comment on column adi_knowledge_base.is_public is '是否公开'; comment on column adi_knowledge_base.owner_id is '所属人id'; comment on column adi_knowledge_base.owner_name is '所属人名称'; comment on column adi_knowledge_base.create_time is '创建时间'; comment on column adi_knowledge_base.update_time is '更新时间'; comment on column adi_knowledge_base.is_deleted is '0：未删除；1：已删除'; create trigger trigger_kb_update_time before update on adi_knowledge_base for each row execute procedure update_modified_column(); create table adi_knowledge_base_item ( id bigserial primary key, uuid varchar(32) default ''::character varying not null, kb_id bigint DEFAULT '0'::bigint NOT NULL, kb_uuid varchar(32) default ''::character varying not null, source_file_id bigint DEFAULT '0'::bigint NOT NULL, title varchar(250) default ''::character varying not null, brief varchar(250) default ''::character varying not null, remark text default ''::character varying not null, is_embedded boolean default false not null, create_time timestamp default CURRENT_TIMESTAMP not null, update_time timestamp default CURRENT_TIMESTAMP not null, is_deleted boolean default false not null ); comment on table adi_knowledge_base_item is '知识库-条目'; comment on column adi_knowledge_base_item.kb_id is '所属知识库id'; comment on column adi_knowledge_base_item.source_file_id is '来源文件id'; comment on column adi_knowledge_base_item.title is '条目标题'; comment on column adi_knowledge_base_item.brief is '条目内容摘要'; comment on column adi_knowledge_base_item.remark is '条目内容'; comment on column adi_knowledge_base_item.is_embedded is '是否已向量化,0:否,1:是'; comment on column adi_knowledge_base_item.create_time is '创建时间'; comment on column adi_knowledge_base_item.update_time is '更新时间'; comment on column adi_knowledge_base_item.is_deleted is '0：未删除；1：已删除'; create trigger trigger_kb_item_update_time before update on adi_knowledge_base_item for each row execute procedure update_modified_column(); create table adi_knowledge_base_qa_record ( id bigserial primary key, uuid varchar(32) default ''::character varying not null, kb_id bigint DEFAULT '0'::bigint NOT NULL, kb_uuid varchar(32) default ''::character varying not null, question varchar(1000) default ''::character varying not null, prompt text default ''::character varying not null, prompt_tokens integer DEFAULT 0 NOT NULL, answer text default ''::character varying not null, answer_tokens integer DEFAULT 0 NOT NULL, source_file_ids varchar(500) default ''::character varying not null, user_id bigint default '0' NOT NULL, create_time timestamp default CURRENT_TIMESTAMP not null, update_time timestamp default CURRENT_TIMESTAMP not null, is_deleted boolean default false not null ); comment on table adi_knowledge_base_qa_record is '知识库-提问记录'; comment on column adi_knowledge_base_qa_record.kb_id is '所属知识库id'; comment on column adi_knowledge_base_qa_record.kb_uuid is '所属知识库uuid'; comment on column adi_knowledge_base_qa_record.question is '用户的原始问题'; comment on column adi_knowledge_base_qa_record.prompt is '提供给LLM的提示词'; comment on column adi_knowledge_base_qa_record.prompt_tokens is '提示词消耗的token'; comment on column adi_knowledge_base_qa_record.answer is '答案'; comment on column adi_knowledge_base_qa_record.answer_tokens is '答案消耗的token'; comment on column adi_knowledge_base_qa_record.source_file_ids is '来源文档id,以逗号隔开'; comment on column adi_knowledge_base_qa_record.user_id is '提问用户id'; comment on column adi_knowledge_base_qa_record.create_time is '创建时间'; comment on column adi_knowledge_base_qa_record.update_time is '更新时间'; comment on column adi_knowledge_base_qa_record.is_deleted is '0：未删除；1：已删除'; create trigger trigger_kb_qa_record_update_time before update on adi_knowledge_base_qa_record for each row execute procedure update_modified_column();