bd24545b7b
- Config: try ENV_FILE, .env, ../.env for loading; trim trailing slash from BaseURL - Log BASE_URL at server startup for verification - .env.example: document BASE_URL - Tasks, projects, tags, migrations and related API/handlers Made-with: Cursor
112 lines
4.0 KiB
SQL
112 lines
4.0 KiB
SQL
-- To-do list support: projects, tags, tasks, and related tables
|
|
|
|
-- Projects (Layer 2)
|
|
CREATE TABLE projects (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
owner_id UUID NOT NULL REFERENCES users(id),
|
|
name TEXT NOT NULL,
|
|
color TEXT NOT NULL DEFAULT '#3B82F6',
|
|
is_shared BOOLEAN NOT NULL DEFAULT false,
|
|
deadline TIMESTAMPTZ,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE INDEX idx_projects_owner_id ON projects (owner_id);
|
|
|
|
-- Project Members (Layer 2 shared projects)
|
|
CREATE TABLE project_members (
|
|
project_id UUID NOT NULL REFERENCES projects(id),
|
|
user_id UUID NOT NULL REFERENCES users(id),
|
|
role TEXT NOT NULL CHECK (role IN ('owner', 'editor', 'viewer')),
|
|
PRIMARY KEY (project_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX idx_project_members_project_id ON project_members (project_id);
|
|
|
|
-- Tags (Layer 2)
|
|
CREATE TABLE tags (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
owner_id UUID NOT NULL REFERENCES users(id),
|
|
name TEXT NOT NULL,
|
|
color TEXT NOT NULL DEFAULT '#6B7280',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_tags_owner_id ON tags (owner_id);
|
|
|
|
-- Tasks (Layer 1 core)
|
|
CREATE TABLE tasks (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
owner_id UUID NOT NULL REFERENCES users(id),
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
status TEXT NOT NULL DEFAULT 'todo' CHECK (status IN ('todo', 'in_progress', 'done', 'archived')),
|
|
priority TEXT NOT NULL DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'critical')),
|
|
due_date TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
deleted_at TIMESTAMPTZ,
|
|
project_id UUID REFERENCES projects(id),
|
|
parent_id UUID REFERENCES tasks(id),
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
recurrence_rule TEXT
|
|
);
|
|
|
|
CREATE INDEX idx_tasks_owner_id ON tasks (owner_id);
|
|
CREATE INDEX idx_tasks_status ON tasks (owner_id, status) WHERE deleted_at IS NULL;
|
|
CREATE INDEX idx_tasks_priority ON tasks (owner_id, priority) WHERE deleted_at IS NULL;
|
|
CREATE INDEX idx_tasks_due_date ON tasks (owner_id, due_date) WHERE deleted_at IS NULL;
|
|
CREATE INDEX idx_tasks_project_id ON tasks (project_id) WHERE deleted_at IS NULL;
|
|
CREATE INDEX idx_tasks_parent_id ON tasks (parent_id) WHERE deleted_at IS NULL;
|
|
|
|
-- Task Tags (many-to-many)
|
|
CREATE TABLE task_tags (
|
|
task_id UUID NOT NULL REFERENCES tasks(id),
|
|
tag_id UUID NOT NULL REFERENCES tags(id),
|
|
PRIMARY KEY (task_id, tag_id)
|
|
);
|
|
|
|
CREATE INDEX idx_task_tags_task_id ON task_tags (task_id);
|
|
CREATE INDEX idx_task_tags_tag_id ON task_tags (tag_id);
|
|
|
|
-- Task Dependencies (Layer 3)
|
|
-- task_id is blocked by blocks_task_id (blocker)
|
|
CREATE TABLE task_dependencies (
|
|
task_id UUID NOT NULL REFERENCES tasks(id),
|
|
blocks_task_id UUID NOT NULL REFERENCES tasks(id),
|
|
PRIMARY KEY (task_id, blocks_task_id),
|
|
CHECK (task_id != blocks_task_id)
|
|
);
|
|
|
|
CREATE INDEX idx_task_dependencies_task_id ON task_dependencies (task_id);
|
|
CREATE INDEX idx_task_dependencies_blocks ON task_dependencies (blocks_task_id);
|
|
|
|
-- Task Reminders (Layer 2)
|
|
CREATE TABLE task_reminders (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
task_id UUID NOT NULL REFERENCES tasks(id),
|
|
type TEXT NOT NULL CHECK (type IN ('push', 'email', 'webhook', 'telegram', 'nostr')),
|
|
config JSONB NOT NULL DEFAULT '{}',
|
|
scheduled_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_task_reminders_task_id ON task_reminders (task_id);
|
|
CREATE INDEX idx_task_reminders_scheduled ON task_reminders (scheduled_at);
|
|
|
|
-- Task Webhooks (Layer 3)
|
|
CREATE TABLE task_webhooks (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
owner_id UUID NOT NULL REFERENCES users(id),
|
|
url TEXT NOT NULL,
|
|
events JSONB NOT NULL DEFAULT '[]',
|
|
secret TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_task_webhooks_owner_id ON task_webhooks (owner_id);
|