a6065d7ff1
All checks were successful
Build and Push Docker Image / build-and-push (push) Successful in 39s
59 lines
2.9 KiB
SQL
59 lines
2.9 KiB
SQL
-- Migration: Add tasks and reward_configs tables
|
|
-- This script creates tables for task management system
|
|
|
|
-- ============================================
|
|
-- Table: tasks
|
|
-- ============================================
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
completed INTEGER DEFAULT 0,
|
|
last_completed_at TIMESTAMP WITH TIME ZONE,
|
|
parent_task_id INTEGER REFERENCES tasks(id) ON DELETE CASCADE,
|
|
reward_message TEXT,
|
|
progression_base NUMERIC(10,4),
|
|
deleted BOOLEAN DEFAULT FALSE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_user_id ON tasks(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_parent_task_id ON tasks(parent_task_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_deleted ON tasks(deleted);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_last_completed_at ON tasks(last_completed_at);
|
|
|
|
-- ============================================
|
|
-- Table: reward_configs
|
|
-- ============================================
|
|
CREATE TABLE IF NOT EXISTS reward_configs (
|
|
id SERIAL PRIMARY KEY,
|
|
position INTEGER NOT NULL,
|
|
task_id INTEGER REFERENCES tasks(id) ON DELETE CASCADE,
|
|
project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
|
|
value NUMERIC(10,4) NOT NULL,
|
|
use_progression BOOLEAN DEFAULT FALSE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_reward_configs_task_id ON reward_configs(task_id);
|
|
CREATE INDEX IF NOT EXISTS idx_reward_configs_project_id ON reward_configs(project_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_reward_configs_task_position ON reward_configs(task_id, position);
|
|
|
|
-- ============================================
|
|
-- Comments for documentation
|
|
-- ============================================
|
|
COMMENT ON TABLE tasks IS 'Tasks table for task management system';
|
|
COMMENT ON COLUMN tasks.name IS 'Task name (required for main tasks, optional for subtasks)';
|
|
COMMENT ON COLUMN tasks.completed IS 'Number of times task was completed';
|
|
COMMENT ON COLUMN tasks.last_completed_at IS 'Date and time of last task completion';
|
|
COMMENT ON COLUMN tasks.parent_task_id IS 'Parent task ID for subtasks (NULL for main tasks)';
|
|
COMMENT ON COLUMN tasks.reward_message IS 'Reward message template with placeholders ${0}, ${1}, etc.';
|
|
COMMENT ON COLUMN tasks.progression_base IS 'Base value for progression calculation (NULL means no progression)';
|
|
COMMENT ON COLUMN tasks.deleted IS 'Soft delete flag';
|
|
|
|
COMMENT ON TABLE reward_configs IS 'Reward configurations for tasks';
|
|
COMMENT ON COLUMN reward_configs.position IS 'Position in reward_message template (0, 1, 2, etc.)';
|
|
COMMENT ON COLUMN reward_configs.task_id IS 'Task this reward belongs to';
|
|
COMMENT ON COLUMN reward_configs.project_id IS 'Project to add reward to';
|
|
COMMENT ON COLUMN reward_configs.value IS 'Default score value (can be negative)';
|
|
COMMENT ON COLUMN reward_configs.use_progression IS 'Whether to use progression multiplier for this reward';
|
|
|