creativesar/taskflow

1-- Todo App Database Schema2-- Phase II + Phase III - Full-Stack Web Application with AI Chatbot3-- Execute this script in Neon PostgreSQL Console4 5-- ============================================6-- Phase II: Core Tables7-- ============================================8 9-- Users table (managed by Better Auth)10CREATE TABLE IF NOT EXISTS users (11    id TEXT PRIMARY KEY,12    email TEXT UNIQUE NOT NULL,13    name TEXT,14    hashed_password TEXT NOT NULL,15    created_at TIMESTAMP DEFAULT NOW()16);17 18-- Tasks table (our application)19CREATE TABLE IF NOT EXISTS tasks (20    id SERIAL PRIMARY KEY,21    user_id TEXT NOT NULL,22    title VARCHAR(200) NOT NULL,23    description TEXT,24    completed BOOLEAN DEFAULT FALSE,25    created_at TIMESTAMP DEFAULT NOW(),26    updated_at TIMESTAMP DEFAULT NOW(),27    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE28);29 30-- ============================================31-- Phase III: AI Chatbot Tables32-- ============================================33 34-- Conversations table35CREATE TABLE IF NOT EXISTS conversations (36    id SERIAL PRIMARY KEY,37    user_id TEXT NOT NULL,38    created_at TIMESTAMP DEFAULT NOW(),39    updated_at TIMESTAMP DEFAULT NOW(),40    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE41);42 43-- Messages table44CREATE TABLE IF NOT EXISTS messages (45    id SERIAL PRIMARY KEY,46    user_id TEXT NOT NULL,47    conversation_id INTEGER NOT NULL,48    role VARCHAR(20) NOT NULL CHECK (role IN ('user', 'assistant')),49    content TEXT NOT NULL,50    tool_calls TEXT,51    created_at TIMESTAMP DEFAULT NOW(),52    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,53    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE54);55 56-- ============================================57-- Indexes for Performance58-- ============================================59 60-- Phase II indexes61CREATE INDEX IF NOT EXISTS idx_tasks_user_id ON tasks(user_id);62CREATE INDEX IF NOT EXISTS idx_tasks_completed ON tasks(completed);63 64-- Phase III indexes65CREATE INDEX IF NOT EXISTS idx_conversations_user_id ON conversations(user_id);66CREATE INDEX IF NOT EXISTS idx_messages_conversation_id ON messages(conversation_id);67CREATE INDEX IF NOT EXISTS idx_messages_user_id ON messages(user_id);68CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at);69 70-- ============================================71-- Fix: Add missing column if needed72-- ============================================73 74-- If conversations table exists but missing updated_at, add it:75DO $$76BEGIN77    IF NOT EXISTS (78        SELECT 1 FROM information_schema.columns79        WHERE table_name = 'conversations' AND column_name = 'updated_at'80    ) THEN81        ALTER TABLE conversations ADD COLUMN updated_at TIMESTAMP DEFAULT NOW();82    END IF;83END $$;84 85-- ============================================86-- Verify Schema87-- ============================================88 89SELECT table_name FROM information_schema.tables90WHERE table_schema = 'public'91ORDER BY table_name;92