ragchat/sql/pg.sql

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE IF NOT EXISTS vector_store (
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    content text,
    metadata json,
    embedding vector(768)
);
CREATE INDEX IF NOT EXISTS spring_ai_vector_index ON vector_store USING hnsw (embedding vector_cosine_ops);

CREATE TABLE IF NOT EXISTS log_error (
    id bigserial PRIMARY KEY, -- 主键
    "exception" text NULL, -- 异常
    message text NULL, -- 异常消息
    error_stack_trace text NULL, -- 异常堆栈
    create_time timestamp(0) DEFAULT now() NULL -- 创建时间
);
COMMENT ON COLUMN log_error.id IS '主键';
COMMENT ON COLUMN log_error."exception" IS '异常';
COMMENT ON COLUMN log_error.message IS '异常消息';
COMMENT ON COLUMN log_error.error_stack_trace IS '异常堆栈';
COMMENT ON COLUMN log_error.create_time IS '创建时间';

CREATE TABLE IF NOT EXISTS chat_history (
    id bigserial PRIMARY KEY, -- 主键
    user_id text NULL, -- 用户id
    user_q text NULL, -- 用户提问
    chat_a text NULL, -- chat回答
    create_time timestamp(0) DEFAULT now() NULL, -- 创建时间
    update_time timestamp(0) DEFAULT now() NULL, -- 更改时间
    is_deleted int2 DEFAULT 0 NULL -- 是否删除
);
COMMENT ON COLUMN chat_history.id IS '主键';
COMMENT ON COLUMN chat_history.user_id IS '用户id';
COMMENT ON COLUMN chat_history."user_q" IS '用户提问';
COMMENT ON COLUMN chat_history."chat_a" IS 'chat回答';
COMMENT ON COLUMN chat_history.create_time IS '创建时间';
COMMENT ON COLUMN chat_history.update_time IS '更改时间';
COMMENT ON COLUMN chat_history.is_deleted IS '是否删除';

CREATE TABLE IF NOT EXISTS user_list (
    id bigserial primary key, -- 主键
    user_id text not null, -- 用户id
    user_name text, -- 用户名
    user_password text NULL, -- 密码
    create_time timestamp(0) DEFAULT now() NULL, -- 创建时间
    update_time timestamp(0) DEFAULT now() NULL, -- 更新时间
    is_deleted int2 default 0, -- 是否删除
    UNIQUE (user_name, is_deleted)
);
COMMENT ON TABLE user_list IS '用户列表';
COMMENT ON COLUMN user_list.id IS '主键';
COMMENT ON COLUMN user_list.user_id IS '用户id';
COMMENT ON COLUMN user_list.user_name IS '用户名';
COMMENT ON COLUMN user_list.user_password IS '密码';
COMMENT ON COLUMN user_list.create_time IS '创建时间';
COMMENT ON COLUMN user_list.update_time IS '更新时间';
COMMENT ON COLUMN user_list.is_deleted IS '是否删除';