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).

7 files changed, 601 insertions, 2 deletions

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'),
+	],
+);
diff --git a/src/lib/server/services/projects.ts b/src/lib/server/services/projects.ts
new file mode 100644
index 0000000..b58c2f1
--- /dev/null
+++ b/src/lib/server/services/projects.ts
@@ -0,0 +1,38 @@
+import { db as defaultDb } from '$lib/server/db';
+import { projects } from '$lib/server/db/schema';
+import { asc, eq } from 'drizzle-orm';
+import type { AppDatabase } from './groceries';
+import type { Project } from './types';
+
+export async function loadAll(db = defaultDb): Promise<Project[]> {
+	return db
+		.select()
+		.from(projects)
+		.where(eq(projects.archived, false))
+		.orderBy(asc(projects.name));
+}
+
+export async function add(name: string, db = defaultDb): Promise<Project> {
+	const [inserted] = await db.insert(projects).values({ name }).returning();
+	return inserted;
+}
+
+export async function rename(
+	id: number,
+	name: string,
+	db = defaultDb,
+): Promise<void> {
+	await db.update(projects).set({ name }).where(eq(projects.id, id));
+}
+
+export async function archive(id: number, db = defaultDb): Promise<void> {
+	await db.update(projects).set({ archived: true }).where(eq(projects.id, id));
+}
+
+export async function getById(
+	id: number,
+	db = defaultDb,
+): Promise<Project | undefined> {
+	const [project] = await db.select().from(projects).where(eq(projects.id, id));
+	return project;
+}
diff --git a/src/lib/server/services/routines.test.ts b/src/lib/server/services/routines.test.ts
new file mode 100644
index 0000000..9e1f429
--- /dev/null
+++ b/src/lib/server/services/routines.test.ts
@@ -0,0 +1,93 @@
+import { describe, it, expect, beforeEach } from 'vitest';
+import { createTestDb } from '$lib/server/db/test-helper';
+import { add as addProject } from './projects';
+import {
+	add,
+	edit,
+	archive,
+	complete,
+	loadAllVersions,
+	loadProjectDashboard,
+	loadOverview,
+} from './routines';
+
+const DAY_MS = 24 * 60 * 60 * 1000;
+
+describe('routines service', () => {
+	let db: Awaited<ReturnType<typeof createTestDb>>['db'];
+	let projectId: number;
+
+	beforeEach(async () => {
+		const testDb = await createTestDb();
+		db = testDb.db;
+		const project = await addProject('Household', db);
+		projectId = project.id;
+	});
+
+	it('creates a routine with version 1', async () => {
+		const r = await add(projectId, 'clean kitchen', 7, db);
+		expect(r.id).toBe(1);
+		expect(r.version).toBe(1);
+		expect(r.frequencyDays).toBe(7);
+	});
+
+	it('edit inserts a new snapshot version and keeps history', async () => {
+		const r = await add(projectId, 'clean kitchen', 7, db);
+		const updated = await edit(r.id, 'clean kitchen deeply', 3, db);
+		expect(updated.version).toBe(2);
+		expect(updated.frequencyDays).toBe(3);
+
+		const versions = await loadAllVersions(r.id, db);
+		expect(versions).toHaveLength(2);
+		expect(versions[0].frequencyDays).toBe(7);
+		expect(versions[1].frequencyDays).toBe(3);
+	});
+
+	it('archive inserts an archived snapshot without removing history', async () => {
+		const r = await add(projectId, 'clean kitchen', 7, db);
+		await archive(r.id, db);
+		const versions = await loadAllVersions(r.id, db);
+		expect(versions).toHaveLength(2);
+		expect(versions[0].archived).toBe(false);
+		expect(versions[1].archived).toBe(true);
+
+		const dashboard = await loadProjectDashboard(projectId, Date.now(), db);
+		expect(dashboard.routines).toHaveLength(0);
+		expect(dashboard.healthScore).toBe(100);
+	});
+
+	it('complete records the snapshot version it was made against', async () => {
+		const r = await add(projectId, 'clean kitchen', 7, db);
+		await edit(r.id, 'clean kitchen', 5, db);
+		const doneAt = new Date().toISOString();
+		const completion = await complete(r.id, 'with vinegar', doneAt, db);
+		expect(completion.routineVersion).toBe(2);
+		expect(completion.note).toBe('with vinegar');
+	});
+
+	it('dashboard computes urgency and health from completions', async () => {
+		const r = await add(projectId, 'clean kitchen', 7, db);
+		const createdAt = Date.parse(r.createdAt);
+		await complete(r.id, null, new Date(createdAt).toISOString(), db);
+
+		// 15 days after completion with a 7-day routine => very late
+		const now = createdAt + 15 * DAY_MS;
+		const dashboard = await loadProjectDashboard(projectId, now, db);
+		expect(dashboard.routines).toHaveLength(1);
+		expect(dashboard.routines[0].status).toBe('veryLate');
+		expect(dashboard.healthScore).toBe(0);
+	});
+
+	it('overview aggregates per-project health and a global score', async () => {
+		await add(projectId, 'clean kitchen', 7, db);
+		const growth = await addProject('Personal growth', db);
+		await add(growth.id, 'read 30 minutes', 1, db);
+
+		const now = Date.now();
+		const overview = await loadOverview(now, db);
+		expect(overview.projects).toHaveLength(2);
+		expect(overview.globalScore).toBe(
+			Math.round(overview.projects.reduce((s, p) => s + p.healthScore, 0) / 2),
+		);
+	});
+});
diff --git a/src/lib/server/services/routines.ts b/src/lib/server/services/routines.ts
new file mode 100644
index 0000000..e9e0c45
--- /dev/null
+++ b/src/lib/server/services/routines.ts
@@ -0,0 +1,192 @@
+import { db as defaultDb } from '$lib/server/db';
+import { routines, routineCompletions, projects } from '$lib/server/db/schema';
+import { and, eq, inArray, sql } from 'drizzle-orm';
+import type { AppDatabase } from './groceries';
+import type { Routine, RoutineCompletion } from './types';
+import {
+	computeRoutineState,
+	computeHealthScore,
+	type RoutineState,
+} from '$lib/shared/routine-metrics';
+
+// ponytail: next routine id via MAX(id)+1, safe enough for a single-user app;
+// switch to a dedicated id table or transaction if concurrent writes appear.
+
+export async function nextRoutineId(db: AppDatabase): Promise<number> {
+	const [{ maxId }] = await db
+		.select({ maxId: sql<number>`COALESCE(MAX(${routines.id}), 0)` })
+		.from(routines);
+	return (maxId ?? 0) + 1;
+}
+
+export async function add(
+	projectId: number,
+	name: string,
+	frequencyDays: number,
+	db = defaultDb,
+): Promise<Routine> {
+	const id = await nextRoutineId(db);
+	const [inserted] = await db
+		.insert(routines)
+		.values({
+			id,
+			version: 1,
+			projectId,
+			name,
+			frequencyDays,
+			createdAt: new Date().toISOString(),
+		})
+		.returning();
+	return inserted;
+}
+
+/** Edit = insert a new snapshot row with version+1; history stays intact. */
+export async function edit(
+	id: number,
+	name: string,
+	frequencyDays: number,
+	db = defaultDb,
+): Promise<Routine> {
+	const [current] = await db
+		.select()
+		.from(routines)
+		.where(eq(routines.id, id))
+		.orderBy(sql`${routines.version} DESC`)
+		.limit(1);
+	if (!current) throw new Error('Routine not found');
+
+	const [inserted] = await db
+		.insert(routines)
+		.values({
+			id: current.id,
+			version: current.version + 1,
+			projectId: current.projectId,
+			name,
+			frequencyDays,
+			archived: current.archived,
+			createdAt: current.createdAt,
+		})
+		.returning();
+	return inserted;
+}
+
+export async function archive(id: number, db = defaultDb): Promise<void> {
+	const [current] = await db
+		.select()
+		.from(routines)
+		.where(eq(routines.id, id))
+		.orderBy(sql`${routines.version} DESC`)
+		.limit(1);
+	if (!current) throw new Error('Routine not found');
+
+	await db.insert(routines).values({
+		...current,
+		version: current.version + 1,
+		archived: true,
+	});
+}
+
+export async function loadAllVersions(
+	routineId: number,
+	db = defaultDb,
+): Promise<Routine[]> {
+	return db
+		.select()
+		.from(routines)
+		.where(eq(routines.id, routineId))
+		.orderBy(routines.version);
+}
+
+export async function complete(
+	routineId: number,
+	note: string | null,
+	doneAt: string,
+	db = defaultDb,
+): Promise<RoutineCompletion> {
+	const [current] = await db
+		.select()
+		.from(routines)
+		.where(eq(routines.id, routineId))
+		.orderBy(sql`${routines.version} DESC`)
+		.limit(1);
+	if (!current) throw new Error('Routine not found');
+
+	const [inserted] = await db
+		.insert(routineCompletions)
+		.values({
+			routineId: current.id,
+			routineVersion: current.version,
+			doneAt,
+			note,
+		})
+		.returning();
+	return inserted;
+}
+
+export interface ProjectDashboard {
+	routines: RoutineState[];
+	healthScore: number;
+}
+
+export async function loadProjectDashboard(
+	projectId: number,
+	now: number,
+	db = defaultDb,
+): Promise<ProjectDashboard> {
+	const versions = await db
+		.select()
+		.from(routines)
+		.where(eq(routines.projectId, projectId));
+
+	// Keep only the max-version row per routine id, then drop archived ones:
+	// an archived routine's max version is archived, so it disappears.
+	const current = new Map<number, Routine>();
+	for (const r of versions) {
+		const existing = current.get(r.id);
+		if (!existing || r.version > existing.version) current.set(r.id, r);
+	}
+	const routines_ = [...current.values()].filter((r) => !r.archived);
+	if (routines_.length === 0) return { routines: [], healthScore: 100 };
+
+	const completions = await db
+		.select()
+		.from(routineCompletions)
+		.where(
+			inArray(
+				routineCompletions.routineId,
+				routines_.map((r) => r.id),
+			),
+		);
+
+	const states = routines_.map((r) => computeRoutineState(r, completions, now));
+	return { routines: states, healthScore: computeHealthScore(states) };
+}
+
+export interface ProjectsOverview {
+	projects: { project: { id: number; name: string }; healthScore: number }[];
+	globalScore: number;
+}
+
+export async function loadOverview(
+	now: number,
+	db = defaultDb,
+): Promise<ProjectsOverview> {
+	const activeProjects = await db
+		.select({ id: projects.id, name: projects.name })
+		.from(projects)
+		.where(eq(projects.archived, false));
+
+	const entries = await Promise.all(
+		activeProjects.map(async (p) => ({
+			project: p,
+			healthScore: (await loadProjectDashboard(p.id, now, db)).healthScore,
+		})),
+	);
+
+	return {
+		projects: entries,
+		globalScore: computeHealthScore(
+			entries.map((e) => ({ health: e.healthScore })),
+		),
+	};
+}
diff --git a/src/lib/server/services/types.ts b/src/lib/server/services/types.ts
index e5e3918..aa4817a 100644
--- a/src/lib/server/services/types.ts
+++ b/src/lib/server/services/types.ts
@@ -1,6 +1,16 @@
 import type { InferSelectModel } from 'drizzle-orm';
-import type { categories, groceries, references } from '$lib/server/db/schema';
+import type {
+	categories,
+	groceries,
+	references,
+	projects,
+	routines,
+	routineCompletions,
+} from '$lib/server/db/schema';
 
 export type Category = InferSelectModel<typeof categories>;
 export type Grocery = InferSelectModel<typeof groceries>;
 export type Reference = InferSelectModel<typeof references>;
+export type Project = InferSelectModel<typeof projects>;
+export type Routine = InferSelectModel<typeof routines>;
+export type RoutineCompletion = InferSelectModel<typeof routineCompletions>;
diff --git a/src/lib/shared/routine-metrics.test.ts b/src/lib/shared/routine-metrics.test.ts
new file mode 100644
index 0000000..4a84eda
--- /dev/null
+++ b/src/lib/shared/routine-metrics.test.ts
@@ -0,0 +1,105 @@
+import { describe, it, expect } from 'vitest';
+import {
+	computeRoutineState,
+	computeHealthScore,
+	parseIntervalToDays,
+} from './routine-metrics';
+
+const DAY_MS = 24 * 60 * 60 * 1000;
+const CREATED = '2026-09-01T00:00:00.000Z';
+
+const routine = {
+	id: 1,
+	version: 1,
+	name: 'clean kitchen',
+	frequencyDays: 7,
+	createdAt: CREATED,
+};
+
+describe('computeRoutineState', () => {
+	it('is ok and healthy before the due date', () => {
+		const now = Date.parse(CREATED) + 3 * DAY_MS;
+		const state = computeRoutineState(routine, [], now);
+		expect(state.status).toBe('ok');
+		expect(state.overdueDays).toBe(0);
+		expect(state.health).toBe(100);
+		expect(state.dueAt).toBe(
+			new Date(Date.parse(CREATED) + 7 * DAY_MS).toISOString(),
+		);
+	});
+
+	it('uses the last completion as reference once done', () => {
+		const doneAt = new Date(Date.parse(CREATED) + 5 * DAY_MS).toISOString();
+		const now = Date.parse(doneAt) + 2 * DAY_MS;
+		const state = computeRoutineState(routine, [{ routineId: 1, doneAt }], now);
+		expect(state.lastDoneAt).toBe(doneAt);
+		expect(state.status).toBe('ok');
+	});
+
+	it('is due at exactly the frequency', () => {
+		const now = Date.parse(CREATED) + 7 * DAY_MS;
+		const state = computeRoutineState(routine, [], now);
+		expect(state.status).toBe('due');
+		expect(state.health).toBe(100);
+	});
+
+	it('is very urgent (veryLate) when two weeks old for a weekly routine', () => {
+		const now = Date.parse(CREATED) + 15 * DAY_MS;
+		const state = computeRoutineState(routine, [], now);
+		expect(state.status).toBe('veryLate');
+		expect(state.overdueDays).toBe(8);
+		expect(state.health).toBe(0);
+	});
+
+	it('is late when overdue by half a frequency', () => {
+		const now = Date.parse(CREATED) + 10.5 * DAY_MS;
+		const state = computeRoutineState(routine, [], now);
+		expect(state.status).toBe('late');
+	});
+
+	it('health of 75 means a quarter of the period overdue', () => {
+		const now = Date.parse(CREATED) + 7 * DAY_MS + 1.75 * DAY_MS;
+		const state = computeRoutineState(routine, [], now);
+		expect(state.health).toBe(75);
+	});
+});
+
+describe('parseIntervalToDays', () => {
+	it('converts units to days', () => {
+		expect(parseIntervalToDays('1d')).toBe(1);
+		expect(parseIntervalToDays('2w')).toBe(14);
+		expect(parseIntervalToDays('3m')).toBe(90);
+		expect(parseIntervalToDays('1y')).toBe(365);
+	});
+
+	it('accepts whitespace and uppercase', () => {
+		expect(parseIntervalToDays(' 2W ')).toBe(14);
+	});
+
+	it('rejects invalid input', () => {
+		expect(parseIntervalToDays('2')).toBeNull();
+		expect(parseIntervalToDays('w')).toBeNull();
+		expect(parseIntervalToDays('2x')).toBeNull();
+		expect(parseIntervalToDays('-1d')).toBeNull();
+		expect(parseIntervalToDays('')).toBeNull();
+	});
+});
+
+describe('computeHealthScore', () => {
+	it('is 100 with no routines', () => {
+		expect(computeHealthScore([])).toBe(100);
+	});
+
+	it('averages routine healths', () => {
+		const now = Date.parse(CREATED) + 8 * DAY_MS;
+		const a = computeRoutineState(routine, [], now); // overdue 1 day
+		const b = computeRoutineState(
+			routine,
+			[],
+			Date.parse(CREATED) + 3 * DAY_MS,
+		);
+		expect(computeHealthScore([a, b])).toBe(
+			Math.round((a.health + b.health) / 2),
+		);
+	});
+});
diff --git a/src/lib/shared/routine-metrics.ts b/src/lib/shared/routine-metrics.ts
new file mode 100644
index 0000000..2ac245e
--- /dev/null
+++ b/src/lib/shared/routine-metrics.ts
@@ -0,0 +1,113 @@
+// Pure routine metrics, shared by server services and UI. No db access.
+// All timestamps are ISO strings; all math is done in ms then converted to days.
+
+const DAY_MS = 24 * 60 * 60 * 1000;
+
+export type RoutineStatus = 'ok' | 'due' | 'late' | 'veryLate';
+
+export interface RoutineInput {
+	id: number;
+	version: number;
+	name: string;
+	frequencyDays: number;
+	createdAt: string;
+}
+
+export interface CompletionInput {
+	routineId: number;
+	doneAt: string;
+}
+
+export interface RoutineState {
+	id: number;
+	version: number;
+	name: string;
+	frequencyDays: number;
+	lastDoneAt: string | null;
+	/** When the routine should next be done. */
+	dueAt: string;
+	/** Days past dueAt (0 if not yet due). */
+	overdueDays: number;
+	/** Time past due, in units of the routine's own frequency (0 if not due). */
+	overdueRatio: number;
+	status: RoutineStatus;
+	/** 100 on schedule, 0 when overdue by a full frequency, floor at 0. */
+	health: number;
+}
+
+export function computeRoutineState(
+	routine: RoutineInput,
+	completions: CompletionInput[],
+	now: number,
+): RoutineState {
+	const mine = completions
+		.filter((c) => c.routineId === routine.id)
+		.map((c) => Date.parse(c.doneAt));
+	const lastDoneMs = mine.length > 0 ? Math.max(...mine) : null;
+	const createdMs = Date.parse(routine.createdAt);
+	const referenceMs =
+		lastDoneMs ?? (Number.isFinite(createdMs) ? createdMs : now);
+	const periodMs = routine.frequencyDays * DAY_MS;
+	const dueMs = referenceMs + periodMs;
+	const rawRatio = (now - dueMs) / periodMs;
+	const overdueRatio = Math.max(0, rawRatio);
+	// Buckets on overdue time in units of the routine's own frequency:
+	// not due / due now / late (half a period overdue) / very late (a full
+	// period overdue — a weekly routine two weeks old lands here).
+	const status: RoutineStatus =
+		rawRatio < 0
+			? 'ok'
+			: overdueRatio < 0.5
+				? 'due'
+				: overdueRatio < 1
+					? 'late'
+					: 'veryLate';
+
+	return {
+		id: routine.id,
+		version: routine.version,
+		name: routine.name,
+		frequencyDays: routine.frequencyDays,
+		lastDoneAt: lastDoneMs !== null ? new Date(lastDoneMs).toISOString() : null,
+		dueAt: new Date(dueMs).toISOString(),
+		overdueDays: Math.floor(overdueRatio * routine.frequencyDays),
+		overdueRatio,
+		status,
+		health: Math.max(0, Math.round(100 - overdueRatio * 100)),
+	};
+}
+
+/** Average health across routines; 100 when there is nothing to do. */
+export function computeHealthScore(states: { health: number }[]): number {
+	if (states.length === 0) return 100;
+	const total = states.reduce((sum, s) => sum + s.health, 0);
+	return Math.round(total / states.length);
+}
+
+export function formatFrequency(days: number): string {
+	if (days === 1) return 'chaque jour';
+	if (days === 7) return 'chaque semaine';
+	if (days === 30) return 'chaque mois';
+	if (days === 365) return 'chaque année';
+	return `tous les ${days} jours`;
+}
+
+const UNIT_DAYS: Record<string, number> = { d: 1, w: 7, m: 30, y: 365 };
+
+/** Parses "1d | 2w | 3m | 1y" into days; returns null when invalid. */
+export function parseIntervalToDays(input: string): number | null {
+	const match = input
+		.trim()
+		.toLowerCase()
+		.match(/^(\d+)([dwmy])$/);
+	if (!match) return null;
+	const days = Number(match[1]) * UNIT_DAYS[match[2]];
+	return days >= 1 ? days : null;
+}
+
+export const STATUS_LABELS: Record<RoutineStatus, string> = {
+	ok: 'À jour',
+	due: 'À faire',
+	late: 'En retard',
+	veryLate: 'Très en retard',
+};