tasklert-api / scripts /migrate_phase5.sql
hashir-qt's picture
initial deploy
507973e
Raw History Blame Contribute Delete
2.35 kB
-- 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;