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 +++ drizzle/meta/0003_snapshot.json | 348 ++++++++++++++++++++++++++++++++ drizzle/meta/_journal.json | 7 + 3 files changed, 382 insertions(+) create mode 100644 drizzle/0003_mixed_captain_midlands.sql create mode 100644 drizzle/meta/0003_snapshot.json (limited to 'drizzle') 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 +); diff --git a/drizzle/meta/0003_snapshot.json b/drizzle/meta/0003_snapshot.json new file mode 100644 index 0000000..f55b2d3 --- /dev/null +++ b/drizzle/meta/0003_snapshot.json @@ -0,0 +1,348 @@ +{ + "version": "6", + "dialect": "sqlite", + "id": "abfcf814-414d-4ed9-9203-c9841a0aa5d7", + "prevId": "9fa05004-a34b-48ee-80c5-c97293f55154", + "tables": { + "categories": { + "name": "categories", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": true, + "notNull": true, + "autoincrement": true + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "order": { + "name": "order", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": 0 + } + }, + "indexes": { + "categories_name_unique": { + "name": "categories_name_unique", + "columns": ["name"], + "isUnique": true + } + }, + "foreignKeys": {}, + "compositePrimaryKeys": {}, + "uniqueConstraints": {}, + "checkConstraints": {} + }, + "groceries": { + "name": "groceries", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": true, + "notNull": true, + "autoincrement": true + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "checked": { + "name": "checked", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": false + }, + "created_at": { + "name": "created_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "category_id": { + "name": "category_id", + "type": "integer", + "primaryKey": false, + "notNull": false, + "autoincrement": false + } + }, + "indexes": { + "groceries_name_unique": { + "name": "groceries_name_unique", + "columns": ["name"], + "isUnique": true + } + }, + "foreignKeys": { + "groceries_category_id_categories_id_fk": { + "name": "groceries_category_id_categories_id_fk", + "tableFrom": "groceries", + "tableTo": "categories", + "columnsFrom": ["category_id"], + "columnsTo": ["id"], + "onDelete": "set null", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {}, + "checkConstraints": {} + }, + "projects": { + "name": "projects", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": true, + "notNull": true, + "autoincrement": true + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "archived": { + "name": "archived", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": false + } + }, + "indexes": { + "projects_name_unique": { + "name": "projects_name_unique", + "columns": ["name"], + "isUnique": true + } + }, + "foreignKeys": {}, + "compositePrimaryKeys": {}, + "uniqueConstraints": {}, + "checkConstraints": {} + }, + "references": { + "name": "references", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": true, + "notNull": true, + "autoincrement": true + }, + "grocery_id": { + "name": "grocery_id", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "reference": { + "name": "reference", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "price": { + "name": "price", + "type": "real", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "unit": { + "name": "unit", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "quality_score": { + "name": "quality_score", + "type": "integer", + "primaryKey": false, + "notNull": false, + "autoincrement": false + } + }, + "indexes": {}, + "foreignKeys": { + "references_grocery_id_groceries_id_fk": { + "name": "references_grocery_id_groceries_id_fk", + "tableFrom": "references", + "tableTo": "groceries", + "columnsFrom": ["grocery_id"], + "columnsTo": ["id"], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {}, + "checkConstraints": {} + }, + "routine_completions": { + "name": "routine_completions", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": true, + "notNull": true, + "autoincrement": true + }, + "routine_id": { + "name": "routine_id", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "routine_version": { + "name": "routine_version", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "done_at": { + "name": "done_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "note": { + "name": "note", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + } + }, + "indexes": {}, + "foreignKeys": { + "routine_completions_routine_id_routine_version_routines_id_version_fk": { + "name": "routine_completions_routine_id_routine_version_routines_id_version_fk", + "tableFrom": "routine_completions", + "tableTo": "routines", + "columnsFrom": ["routine_id", "routine_version"], + "columnsTo": ["id", "version"], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {}, + "checkConstraints": {} + }, + "routines": { + "name": "routines", + "columns": { + "id": { + "name": "id", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "version": { + "name": "version", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "project_id": { + "name": "project_id", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "frequency_days": { + "name": "frequency_days", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "archived": { + "name": "archived", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": false + }, + "created_at": { + "name": "created_at", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + } + }, + "indexes": {}, + "foreignKeys": { + "routines_project_id_projects_id_fk": { + "name": "routines_project_id_projects_id_fk", + "tableFrom": "routines", + "tableTo": "projects", + "columnsFrom": ["project_id"], + "columnsTo": ["id"], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": { + "routines_id_version_pk": { + "columns": ["id", "version"], + "name": "routines_id_version_pk" + } + }, + "uniqueConstraints": {}, + "checkConstraints": {} + } + }, + "views": {}, + "enums": {}, + "_meta": { + "schemas": {}, + "tables": {}, + "columns": {} + }, + "internal": { + "indexes": {} + } +} diff --git a/drizzle/meta/_journal.json b/drizzle/meta/_journal.json index 08d6c41..870d2f7 100644 --- a/drizzle/meta/_journal.json +++ b/drizzle/meta/_journal.json @@ -22,6 +22,13 @@ "when": 1785314243051, "tag": "0002_rainy_wind_dancer", "breakpoints": true + }, + { + "idx": 3, + "version": "6", + "when": 1788771513039, + "tag": "0003_mixed_captain_midlands", + "breakpoints": true } ] } -- cgit v1.3