3 files changed, 77 insertions, 0 deletions

diff --git a/src/lib/server/db/schema.ts b/src/lib/server/db/schema.ts
index bac1c46..c5daac5 100644
--- a/src/lib/server/db/schema.ts
+++ b/src/lib/server/db/schema.ts
@@ -54,6 +54,7 @@ export const routines = sqliteTable(
 		frequencyDays: integer('frequency_days').notNull(),
 		description: text('description').notNull().default(''),
 		archived: integer('archived', { mode: 'boolean' }).notNull().default(false),
+		archivedAt: text('archived_at'),
 		createdAt: text('created_at').notNull(),
 	},
 	(t) => [primaryKey({ columns: [t.id, t.version] })],
diff --git a/src/lib/server/services/routines.test.ts b/src/lib/server/services/routines.test.ts
index 05abcaf..e8c4f35 100644
--- a/src/lib/server/services/routines.test.ts
+++ b/src/lib/server/services/routines.test.ts
@@ -7,6 +7,7 @@ import {
 	archive,
 	complete,
 	loadAllVersions,
+	loadArchived,
 	loadProjectDashboard,
 	loadOverview,
 } from './routines';
@@ -88,6 +89,38 @@ describe('routines service', () => {
 		expect(dashboard.healthScore).toBe(100);
 	});
 
+	it('loadArchived lists archived routines; edits keep the archive date', async () => {
+		const a = await add(projectId, 'clean kitchen', 7, db);
+		await add(projectId, 'water plants', 3, db);
+		await archive(a.id, db);
+		const stamped = (await loadAllVersions(a.id, db))[1].archivedAt;
+		expect(stamped).toBeTruthy();
+
+		const edited = await edit(a.id, { name: 'clean kitchen deeply' }, db);
+		expect(edited.archived).toBe(true);
+		expect(edited.archivedAt).toBe(stamped);
+
+		const archived = await loadArchived(projectId, db);
+		expect(archived.map((r) => r.id)).toEqual([a.id]);
+		expect(archived[0].name).toBe('clean kitchen deeply');
+		expect(archived[0].frequencyDays).toBe(7);
+		expect(archived[0].archivedAt).toBe(stamped);
+	});
+
+	it('loadArchived sorts most recently archived first', async () => {
+		const a = await add(projectId, 'first', 1, db);
+		const b = await add(projectId, 'second', 1, db);
+		await archive(a.id, db);
+		await archive(b.id, db);
+
+		const archived = await loadArchived(projectId, db);
+		expect(archived).toHaveLength(2);
+		// Descending archive date; equal timestamps keep insertion order.
+		expect(new Date(archived[0].archivedAt).getTime()).toBeGreaterThanOrEqual(
+			new Date(archived[1].archivedAt).getTime(),
+		);
+	});
+
 	it('complete records the snapshot version it was made against', async () => {
 		const r = await add(projectId, 'clean kitchen', 7, db);
 		await edit(r.id, { frequencyDays: 5 }, db);
diff --git a/src/lib/server/services/routines.ts b/src/lib/server/services/routines.ts
index c3a5eff..b314d50 100644
--- a/src/lib/server/services/routines.ts
+++ b/src/lib/server/services/routines.ts
@@ -69,6 +69,7 @@ export async function edit(
 			frequencyDays: fields.frequencyDays ?? current.frequencyDays,
 			description: fields.description ?? current.description,
 			archived: current.archived,
+			archivedAt: current.archivedAt,
 			createdAt: current.createdAt,
 		})
 		.returning();
@@ -88,9 +89,51 @@ export async function archive(id: number, db = defaultDb): Promise<void> {
 		...current,
 		version: current.version + 1,
 		archived: true,
+		archivedAt: new Date().toISOString(),
 	});
 }
 
+export interface ArchivedRoutine {
+	id: number;
+	name: string;
+	frequencyDays: number;
+	createdAt: string;
+	archivedAt: string;
+}
+
+/** Archived routines of a project, most recently archived first. */
+export async function loadArchived(
+	projectId: number,
+	db = defaultDb,
+): Promise<ArchivedRoutine[]> {
+	const versions = await db
+		.select()
+		.from(routines)
+		.where(eq(routines.projectId, projectId));
+
+	// Keep only the max-version row per routine id (same rule as the
+	// dashboard): archived = its current snapshot is archived.
+	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);
+	}
+	return (
+		[...current.values()]
+			.filter((r) => r.archived)
+			// Legacy rows archived before archived_at existed fall back to their
+			// creation date.
+			.sort((a, b) => ((a.archivedAt ?? '') < (b.archivedAt ?? '') ? 1 : -1))
+			.map((r) => ({
+				id: r.id,
+				name: r.name,
+				frequencyDays: r.frequencyDays,
+				createdAt: r.createdAt,
+				archivedAt: r.archivedAt ?? r.createdAt,
+			}))
+	);
+}
+
 export async function loadAllVersions(
 	routineId: number,
 	db = defaultDb,