feat(tasks): add projects/labels schema, wire project_id through native tasks

project_id joins the existing labels JSON column as series-level
metadata: CreateNextIteration copies both onto every new recurring
occurrence, matching content/description/priority's existing behavior.

1 files changed, 20 insertions, 0 deletions

diff --git a/migrations/024_labels_and_projects.sql b/migrations/024_labels_and_projects.sql
new file mode 100644
index 0000000..9e5235c
--- /dev/null
+++ b/migrations/024_labels_and_projects.sql
@@ -0,0 +1,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);