2 files changed, 116 insertions, 0 deletions
diff --git a/pkgs/server/src/db/index.ts b/pkgs/server/src/db/index.ts
new file mode 100644
index 0000000..6730947
--- /dev/null
+++ b/pkgs/server/src/db/index.ts
@@ -0,0 +1,12 @@
+import { drizzle } from "drizzle-orm/node-postgres";
+import * as schema from "./schema";
+
+const databaseUrl = process.env.DATABASE_URL;
+
+if (!databaseUrl) {
+	throw new Error("DATABASE_URL environment variable is not set");
+}
+
+export const db = drizzle(databaseUrl, { schema });
+
+export * from "./schema";
diff --git a/pkgs/server/src/db/schema.ts b/pkgs/server/src/db/schema.ts
new file mode 100644
index 0000000..23f19d1
--- /dev/null
+++ b/pkgs/server/src/db/schema.ts
@@ -0,0 +1,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),
+});