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

1 files changed, 192 insertions, 0 deletions

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 })),
+		),
+	};
+}