-- Phase V migration: Add task organization fields + stats/events tables -- Run against Neon PostgreSQL once before deploying Phase V backend -- Usage: psql $DATABASE_URL -f scripts/migrate_phase5.sql -- 1. Add new columns to todo table (idempotent with IF NOT EXISTS) ALTER TABLE todo ADD COLUMN IF NOT EXISTS priority VARCHAR DEFAULT 'medium', ADD COLUMN IF NOT EXISTS tags TEXT[] DEFAULT '{}', ADD COLUMN IF NOT EXISTS due_date TIMESTAMP, ADD COLUMN IF NOT EXISTS reminder_at TIMESTAMP, ADD COLUMN IF NOT EXISTS recurrence VARCHAR DEFAULT 'none', ADD COLUMN IF NOT EXISTS parent_id INTEGER REFERENCES todo(id); -- 2. Create indexes for new todo columns CREATE INDEX IF NOT EXISTS idx_todo_user_priority ON todo(user_id, priority); CREATE INDEX IF NOT EXISTS idx_todo_user_due_date ON todo(user_id, due_date); CREATE INDEX IF NOT EXISTS idx_todo_tags ON todo USING GIN(tags); -- 3. Create daily_completion table CREATE TABLE IF NOT EXISTS daily_completion ( id SERIAL PRIMARY KEY, user_id VARCHAR NOT NULL REFERENCES "user"(id), date DATE NOT NULL, count INTEGER DEFAULT 0, UNIQUE(user_id, date) ); CREATE INDEX IF NOT EXISTS idx_daily_completion_user_date ON daily_completion(user_id, date); -- 4. Create user_stats table CREATE TABLE IF NOT EXISTS user_stats ( id SERIAL PRIMARY KEY, user_id VARCHAR NOT NULL UNIQUE REFERENCES "user"(id), total_completed INTEGER DEFAULT 0, current_streak INTEGER DEFAULT 0, longest_streak INTEGER DEFAULT 0, last_active_date DATE, updated_at TIMESTAMP DEFAULT NOW() ); -- 5. Create achievement table CREATE TABLE IF NOT EXISTS achievement ( id SERIAL PRIMARY KEY, user_id VARCHAR NOT NULL REFERENCES "user"(id), achievement_type VARCHAR NOT NULL, unlocked_at TIMESTAMP DEFAULT NOW(), UNIQUE(user_id, achievement_type) ); -- 6. Create task_event table CREATE TABLE IF NOT EXISTS task_event ( id SERIAL PRIMARY KEY, event_type VARCHAR NOT NULL, task_id INTEGER NOT NULL, user_id VARCHAR NOT NULL REFERENCES "user"(id), timestamp TIMESTAMP DEFAULT NOW(), payload JSONB DEFAULT '{}' ); CREATE INDEX IF NOT EXISTS idx_task_event_user_ts ON task_event(user_id, timestamp); CREATE INDEX IF NOT EXISTS idx_task_event_task ON task_event(task_id); SELECT 'Phase V migration complete' AS status;