From 18a0f9c874aaa09c0f71252dad853c23f9511ed6 Mon Sep 17 00:00:00 2001
From: Martin Kagamino Lehoux <martin@lehoux.net>
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).
---
 src/lib/server/db/schema.ts | 50 ++++++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 49 insertions(+), 1 deletion(-)

(limited to 'src/lib/server/db/schema.ts')

diff --git a/src/lib/server/db/schema.ts b/src/lib/server/db/schema.ts
index 31c2f65..23e10f5 100644
--- a/src/lib/server/db/schema.ts
+++ b/src/lib/server/db/schema.ts
@@ -1,4 +1,11 @@
-import { sqliteTable, text, integer, real } from 'drizzle-orm/sqlite-core';
+import {
+	sqliteTable,
+	text,
+	integer,
+	real,
+	primaryKey,
+	foreignKey,
+} from 'drizzle-orm/sqlite-core';
 
 export const categories = sqliteTable('categories', {
 	id: integer('id').primaryKey({ autoIncrement: true }),
@@ -26,3 +33,44 @@ export const references = sqliteTable('references', {
 	unit: text('unit'),
 	qualityScore: integer('quality_score'),
 });
+
+export const projects = sqliteTable('projects', {
+	id: integer('id').primaryKey({ autoIncrement: true }),
+	name: text('name').notNull().unique(),
+	archived: integer('archived', { mode: 'boolean' }).notNull().default(false),
+});
+
+// Append-only snapshot table: an edit inserts a new row with version+1, the
+// current routine is the row with the max version for its id.
+export const routines = sqliteTable(
+	'routines',
+	{
+		id: integer('id').notNull(),
+		version: integer('version').notNull(),
+		projectId: integer('project_id')
+			.notNull()
+			.references(() => projects.id, { onDelete: 'cascade' }),
+		name: text('name').notNull(),
+		frequencyDays: integer('frequency_days').notNull(),
+		archived: integer('archived', { mode: 'boolean' }).notNull().default(false),
+		createdAt: text('created_at').notNull(),
+	},
+	(t) => [primaryKey({ columns: [t.id, t.version] })],
+);
+
+export const routineCompletions = sqliteTable(
+	'routine_completions',
+	{
+		id: integer('id').primaryKey({ autoIncrement: true }),
+		routineId: integer('routine_id').notNull(),
+		routineVersion: integer('routine_version').notNull(),
+		doneAt: text('done_at').notNull(),
+		note: text('note'),
+	},
+	(t) => [
+		foreignKey({
+			columns: [t.routineId, t.routineVersion],
+			foreignColumns: [routines.id, routines.version],
+		}).onDelete('cascade'),
+	],
+);
-- 
cgit v1.3