migrations/024_labels_and_projects.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- Lightweight projects (exactly one per task) and label colors for
-- doot-native tasks. Labels themselves already exist as a JSON column on
-- native_tasks; this only adds color metadata for them, plus the new
-- projects concept.
CREATE TABLE projects (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    color TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    archived BOOLEAN DEFAULT 0
);

CREATE TABLE labels (
    name TEXT PRIMARY KEY,
    color TEXT NOT NULL
);

ALTER TABLE native_tasks ADD COLUMN project_id TEXT DEFAULT '';

CREATE INDEX IF NOT EXISTS idx_native_tasks_project ON native_tasks(project_id);