Contract system

1 files changed, 33 insertions, 0 deletions

diff --git a/makima/migrations/20250112100000_create_contract_chat_history.sql b/makima/migrations/20250112100000_create_contract_chat_history.sql
new file mode 100644
index 0000000..c4d71d8
--- /dev/null
+++ b/makima/migrations/20250112100000_create_contract_chat_history.sql
@@ -0,0 +1,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);