1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
import {
sqliteTable,
text,
integer,
real,
primaryKey,
foreignKey,
} from 'drizzle-orm/sqlite-core';
export const categories = sqliteTable('categories', {
id: integer('id').primaryKey({ autoIncrement: true }),
name: text('name').notNull().unique(),
order: integer('order').notNull().default(0),
});
export const groceries = sqliteTable('groceries', {
id: integer('id').primaryKey({ autoIncrement: true }),
name: text('name').notNull().unique(),
checked: integer('checked', { mode: 'boolean' }).notNull().default(false),
createdAt: text('created_at').notNull(),
categoryId: integer('category_id').references(() => categories.id, {
onDelete: 'set null',
}),
});
export const references = sqliteTable('references', {
id: integer('id').primaryKey({ autoIncrement: true }),
groceryId: integer('grocery_id')
.notNull()
.references(() => groceries.id, { onDelete: 'cascade' }),
reference: text('reference').notNull(),
price: real('price'),
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: current routine = max version per 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(),
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] })],
);
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'),
],
);
|
