From 18a0f9c874aaa09c0f71252dad853c23f9511ed6 Mon Sep 17 00:00:00 2001 From: Martin Kagamino Lehoux Date: Mon, 7 Sep 2026 11:32:41 +0200 Subject: Add projects with routines, urgency tracking and health scores Projects group recurring routines (chores included) with an append-only snapshot table (id, version) so edits never rewrite history; completions reference the exact snapshot. Pure metrics compute due dates, urgency buckets (ok/due/late/veryLate) and 0-100 health scores per routine, project, and globally. Home page lists projects; project dashboard sorts routines most-urgent first with one-tap completion and interval parser inputs (1d/2w/3m/1y). --- drizzle/0003_mixed_captain_midlands.sql | 27 +++++++++++++++++++++++++++ 1 file changed, 27 insertions(+) create mode 100644 drizzle/0003_mixed_captain_midlands.sql (limited to 'drizzle/0003_mixed_captain_midlands.sql') diff --git a/drizzle/0003_mixed_captain_midlands.sql b/drizzle/0003_mixed_captain_midlands.sql new file mode 100644 index 0000000..af499d3 --- /dev/null +++ b/drizzle/0003_mixed_captain_midlands.sql @@ -0,0 +1,27 @@ +CREATE TABLE `projects` ( + `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL, + `name` text NOT NULL, + `archived` integer DEFAULT false NOT NULL +); +--> statement-breakpoint +CREATE UNIQUE INDEX `projects_name_unique` ON `projects` (`name`);--> statement-breakpoint +CREATE TABLE `routine_completions` ( + `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL, + `routine_id` integer NOT NULL, + `routine_version` integer NOT NULL, + `done_at` text NOT NULL, + `note` text, + FOREIGN KEY (`routine_id`,`routine_version`) REFERENCES `routines`(`id`,`version`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `routines` ( + `id` integer NOT NULL, + `version` integer NOT NULL, + `project_id` integer NOT NULL, + `name` text NOT NULL, + `frequency_days` integer NOT NULL, + `archived` integer DEFAULT false NOT NULL, + `created_at` text NOT NULL, + PRIMARY KEY(`id`, `version`), + FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON UPDATE no action ON DELETE cascade +); -- cgit v1.3