From 5bb62819796bcd3b5e945662c23299eb8db71e34 Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
Date: Wed, 31 Dec 2025 00:33:44 +0900
Subject: feat(db): add Note feature database schema
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Add database tables and Zod validation schemas for Anki-compatible
Note concept as outlined in the roadmap Phase 1:

- note_types: defines note structure (templates, reversibility)
- note_field_types: defines fields within a note type
- notes: container for field values belonging to a deck
- note_field_values: actual field content for notes
- cards: add nullable note_id and is_reversed columns

Includes migration file and comprehensive test coverage for all
new Zod validation schemas.

🤖 Generated with [Claude Code](https://claude.com/claude-code)

Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
---
 src/server/db/schema.ts | 83 +++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 83 insertions(+)

(limited to 'src/server/db')

diff --git a/src/server/db/schema.ts b/src/server/db/schema.ts
index 4b9631f..bd3d396 100644
--- a/src/server/db/schema.ts
+++ b/src/server/db/schema.ts
@@ -1,4 +1,5 @@
 import {
+	boolean,
 	integer,
 	pgTable,
 	real,
@@ -25,6 +26,11 @@ export const Rating = {
 	Easy: 4,
 } as const;
 
+// Field types for note fields
+export const FieldType = {
+	Text: "text",
+} as const;
+
 export const users = pgTable("users", {
 	id: uuid("id").primaryKey().defaultRandom(),
 	username: varchar("username", { length: 255 }).notNull().unique(),
@@ -49,6 +55,45 @@ export const refreshTokens = pgTable("refresh_tokens", {
 		.defaultNow(),
 });
 
+export const noteTypes = pgTable("note_types", {
+	id: uuid("id").primaryKey().defaultRandom(),
+	userId: uuid("user_id")
+		.notNull()
+		.references(() => users.id),
+	name: varchar("name", { length: 255 }).notNull(),
+	frontTemplate: text("front_template").notNull(),
+	backTemplate: text("back_template").notNull(),
+	isReversible: boolean("is_reversible").notNull().default(false),
+	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 noteFieldTypes = pgTable("note_field_types", {
+	id: uuid("id").primaryKey().defaultRandom(),
+	noteTypeId: uuid("note_type_id")
+		.notNull()
+		.references(() => noteTypes.id),
+	name: varchar("name", { length: 255 }).notNull(),
+	order: integer("order").notNull(),
+	fieldType: varchar("field_type", { length: 50 })
+		.notNull()
+		.default(FieldType.Text),
+	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 decks = pgTable("decks", {
 	id: uuid("id").primaryKey().defaultRandom(),
 	userId: uuid("user_id")
@@ -67,11 +112,49 @@ export const decks = pgTable("decks", {
 	syncVersion: integer("sync_version").notNull().default(0),
 });
 
+export const notes = pgTable("notes", {
+	id: uuid("id").primaryKey().defaultRandom(),
+	deckId: uuid("deck_id")
+		.notNull()
+		.references(() => decks.id),
+	noteTypeId: uuid("note_type_id")
+		.notNull()
+		.references(() => noteTypes.id),
+	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 noteFieldValues = pgTable("note_field_values", {
+	id: uuid("id").primaryKey().defaultRandom(),
+	noteId: uuid("note_id")
+		.notNull()
+		.references(() => notes.id),
+	noteFieldTypeId: uuid("note_field_type_id")
+		.notNull()
+		.references(() => noteFieldTypes.id),
+	value: text("value").notNull(),
+	createdAt: timestamp("created_at", { withTimezone: true })
+		.notNull()
+		.defaultNow(),
+	updatedAt: timestamp("updated_at", { withTimezone: true })
+		.notNull()
+		.defaultNow(),
+	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),
+	noteId: uuid("note_id").references(() => notes.id),
+	isReversed: boolean("is_reversed"),
 	front: text("front").notNull(),
 	back: text("back").notNull(),
 
-- 
cgit v1.2.3-70-g09d2