1 files changed, 49 insertions, 1 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'),
+	],
+);