From 811458427593a4172a2cd535cc768db375350dca Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
Date: Sat, 6 Dec 2025 17:05:21 +0900
Subject: feat(dev): change architecture and directory structure

---
 src/server/db/schema.ts | 116 ++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 116 insertions(+)
 create mode 100644 src/server/db/schema.ts

(limited to 'src/server/db/schema.ts')

diff --git a/src/server/db/schema.ts b/src/server/db/schema.ts
new file mode 100644
index 0000000..4b9631f
--- /dev/null
+++ b/src/server/db/schema.ts
@@ -0,0 +1,116 @@
+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 refreshTokens = pgTable("refresh_tokens", {
+	id: uuid("id").primaryKey().defaultRandom(),
+	userId: uuid("user_id")
+		.notNull()
+		.references(() => users.id, { onDelete: "cascade" }),
+	tokenHash: varchar("token_hash", { length: 255 }).notNull(),
+	expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
+	createdAt: timestamp("created_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),
+});
-- 
cgit v1.2.3-70-g09d2