Spaces:
Sleeping
Sleeping
Download scripts/migrate_phase5.sql from hashir-qt/tasklert-api: direct link, hf CLI and curl.
- Browser
- Download file 2.35 kB
-
https://huggingface.co/spaces/hashir-qt/tasklert-api/resolve/main/scripts/migrate_phase5.sql
- Command line
-
hf download hf://spaces/hashir-qt/tasklert-api/scripts/migrate_phase5.sql
-
curl -L -o migrate_phase5.sql https://huggingface.co/spaces/hashir-qt/tasklert-api/resolve/main/scripts/migrate_phase5.sql
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; | |