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

4 files changed, 334 insertions, 1 deletions

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>;