1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
-- Create contract_chat_conversations table for storing conversation threads per contract
CREATE TABLE IF NOT EXISTS contract_chat_conversations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
contract_id UUID NOT NULL REFERENCES contracts(id) ON DELETE CASCADE,
owner_id UUID NOT NULL,
name VARCHAR(255),
is_active BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_contract_chat_conversations_contract ON contract_chat_conversations(contract_id);
CREATE INDEX idx_contract_chat_conversations_owner ON contract_chat_conversations(owner_id);
CREATE INDEX idx_contract_chat_conversations_active ON contract_chat_conversations(contract_id, is_active);
CREATE TRIGGER update_contract_chat_conversations_updated_at
BEFORE UPDATE ON contract_chat_conversations
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
-- Create contract_chat_messages table for individual messages
CREATE TABLE IF NOT EXISTS contract_chat_messages (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
conversation_id UUID NOT NULL REFERENCES contract_chat_conversations(id) ON DELETE CASCADE,
role VARCHAR(16) NOT NULL CHECK (role IN ('user', 'assistant', 'error')),
content TEXT NOT NULL,
tool_calls JSONB,
pending_questions JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_contract_chat_messages_conversation ON contract_chat_messages(conversation_id);
CREATE INDEX idx_contract_chat_messages_created ON contract_chat_messages(created_at);
|
