AzenKain
600246426b
All checks were successful
Build and Release / release (push) Successful in 1m30s
35 lines
1.4 KiB
SQL
35 lines
1.4 KiB
SQL
CREATE TABLE IF NOT EXISTS conversations (
|
|
id UUID PRIMARY KEY DEFAULT uuidv7(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
mod_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
status SMALLINT NOT NULL DEFAULT 1, -- 1: waiting, 2: active, 3: resolved
|
|
closed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_conversations_user_id ON conversations (user_id);
|
|
CREATE INDEX idx_conversations_mod_id ON conversations (mod_id);
|
|
CREATE INDEX idx_conversations_status ON conversations (status);
|
|
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id UUID PRIMARY KEY DEFAULT uuidv7(),
|
|
conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
|
|
sender_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
content TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_messages_conversation_id ON messages (conversation_id);
|
|
CREATE INDEX idx_messages_created_at ON messages (created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS chatbot_histories (
|
|
id UUID PRIMARY KEY DEFAULT uuidv7(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
question TEXT NOT NULL,
|
|
answer TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_chatbot_histories_user_id ON chatbot_histories (user_id, created_at DESC);
|