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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
|
import {
integer,
pgTable,
real,
smallint,
text,
timestamp,
uuid,
varchar,
} from "drizzle-orm/pg-core";
// Card states for FSRS algorithm
export const CardState = {
New: 0,
Learning: 1,
Review: 2,
Relearning: 3,
} as const;
// Rating values for reviews
export const Rating = {
Again: 1,
Hard: 2,
Good: 3,
Easy: 4,
} as const;
export const users = pgTable("users", {
id: uuid("id").primaryKey().defaultRandom(),
username: varchar("username", { length: 255 }).notNull().unique(),
passwordHash: varchar("password_hash", { length: 255 }).notNull(),
createdAt: timestamp("created_at", { withTimezone: true })
.notNull()
.defaultNow(),
updatedAt: timestamp("updated_at", { withTimezone: true })
.notNull()
.defaultNow(),
});
export const decks = pgTable("decks", {
id: uuid("id").primaryKey().defaultRandom(),
userId: uuid("user_id")
.notNull()
.references(() => users.id),
name: varchar("name", { length: 255 }).notNull(),
description: text("description"),
newCardsPerDay: integer("new_cards_per_day").notNull().default(20),
createdAt: timestamp("created_at", { withTimezone: true })
.notNull()
.defaultNow(),
updatedAt: timestamp("updated_at", { withTimezone: true })
.notNull()
.defaultNow(),
deletedAt: timestamp("deleted_at", { withTimezone: true }),
syncVersion: integer("sync_version").notNull().default(0),
});
export const cards = pgTable("cards", {
id: uuid("id").primaryKey().defaultRandom(),
deckId: uuid("deck_id")
.notNull()
.references(() => decks.id),
front: text("front").notNull(),
back: text("back").notNull(),
// FSRS fields
state: smallint("state").notNull().default(CardState.New),
due: timestamp("due", { withTimezone: true }).notNull().defaultNow(),
stability: real("stability").notNull().default(0),
difficulty: real("difficulty").notNull().default(0),
elapsedDays: integer("elapsed_days").notNull().default(0),
scheduledDays: integer("scheduled_days").notNull().default(0),
reps: integer("reps").notNull().default(0),
lapses: integer("lapses").notNull().default(0),
lastReview: timestamp("last_review", { withTimezone: true }),
createdAt: timestamp("created_at", { withTimezone: true })
.notNull()
.defaultNow(),
updatedAt: timestamp("updated_at", { withTimezone: true })
.notNull()
.defaultNow(),
deletedAt: timestamp("deleted_at", { withTimezone: true }),
syncVersion: integer("sync_version").notNull().default(0),
});
export const reviewLogs = pgTable("review_logs", {
id: uuid("id").primaryKey().defaultRandom(),
cardId: uuid("card_id")
.notNull()
.references(() => cards.id),
userId: uuid("user_id")
.notNull()
.references(() => users.id),
rating: smallint("rating").notNull(),
state: smallint("state").notNull(),
scheduledDays: integer("scheduled_days").notNull(),
elapsedDays: integer("elapsed_days").notNull(),
reviewedAt: timestamp("reviewed_at", { withTimezone: true })
.notNull()
.defaultNow(),
durationMs: integer("duration_ms"),
syncVersion: integer("sync_version").notNull().default(0),
});
|
