feat(client): add note-related tables to client IndexedDB

Add LocalNoteType, LocalNoteFieldType, LocalNote, and LocalNoteFieldValue
interfaces and tables to the client database for Anki-compatible note
system. Update LocalCard interface with noteId and isReversed fields.
Includes Dexie schema version 2 with upgrade handler for existing cards.

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

Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>

9 files changed, 412 insertions, 8 deletions

diff --git a/docs/dev/roadmap.md b/docs/dev/roadmap.md
index c3a4a19..f7d7728 100644
--- a/docs/dev/roadmap.md
+++ b/docs/dev/roadmap.md
@@ -201,12 +201,12 @@ Create these as default note types for each user:
 ### Phase 4: Client Database (Dexie)
 
 **Tasks:**
-- [ ] Add `LocalNoteType` interface and table
-- [ ] Add `LocalNoteTypeField` interface and table
-- [ ] Add `LocalNote` interface and table
-- [ ] Add `LocalNoteFieldValue` interface and table
-- [ ] Modify `LocalCard` interface: add `noteId`, `isReversed`
-- [ ] Update Dexie schema version and upgrade handler
+- [x] Add `LocalNoteType` interface and table
+- [x] Add `LocalNoteFieldType` interface and table
+- [x] Add `LocalNote` interface and table
+- [x] Add `LocalNoteFieldValue` interface and table
+- [x] Modify `LocalCard` interface: add `noteId`, `isReversed`
+- [x] Update Dexie schema version and upgrade handler
 - [ ] Create client repositories for new entities
 
 **Files to modify:**
diff --git a/src/client/db/index.test.ts b/src/client/db/index.test.ts
index c1a62f5..a30f94b 100644
--- a/src/client/db/index.test.ts
+++ b/src/client/db/index.test.ts
@@ -6,8 +6,13 @@ import { afterEach, beforeEach, describe, expect, it } from "vitest";
 import {
 	CardState,
 	db,
+	FieldType,
 	type LocalCard,
 	type LocalDeck,
+	type LocalNote,
+	type LocalNoteFieldType,
+	type LocalNoteFieldValue,
+	type LocalNoteType,
 	type LocalReviewLog,
 	Rating,
 } from "./index";
@@ -18,6 +23,10 @@ describe("KiokuDatabase", () => {
 		await db.decks.clear();
 		await db.cards.clear();
 		await db.reviewLogs.clear();
+		await db.noteTypes.clear();
+		await db.noteFieldTypes.clear();
+		await db.notes.clear();
+		await db.noteFieldValues.clear();
 	});
 
 	afterEach(async () => {
@@ -25,6 +34,10 @@ describe("KiokuDatabase", () => {
 		await db.decks.clear();
 		await db.cards.clear();
 		await db.reviewLogs.clear();
+		await db.noteTypes.clear();
+		await db.noteFieldTypes.clear();
+		await db.notes.clear();
+		await db.noteFieldValues.clear();
 	});
 
 	describe("database initialization", () => {
@@ -119,6 +132,8 @@ describe("KiokuDatabase", () => {
 		const testCard: LocalCard = {
 			id: "card-1",
 			deckId: "deck-1",
+			noteId: null,
+			isReversed: null,
 			front: "Question",
 			back: "Answer",
 			state: CardState.New,
@@ -289,6 +304,251 @@ describe("KiokuDatabase", () => {
 		});
 	});
 
+	describe("noteTypes table", () => {
+		const testNoteType: LocalNoteType = {
+			id: "note-type-1",
+			userId: "user-1",
+			name: "Basic",
+			frontTemplate: "{{Front}}",
+			backTemplate: "{{Back}}",
+			isReversible: false,
+			createdAt: new Date("2024-01-01"),
+			updatedAt: new Date("2024-01-01"),
+			deletedAt: null,
+			syncVersion: 0,
+			_synced: false,
+		};
+
+		it("should add and retrieve a note type", async () => {
+			await db.noteTypes.add(testNoteType);
+			const retrieved = await db.noteTypes.get("note-type-1");
+			expect(retrieved).toEqual(testNoteType);
+		});
+
+		it("should find note types by userId", async () => {
+			await db.noteTypes.add(testNoteType);
+			await db.noteTypes.add({
+				...testNoteType,
+				id: "note-type-2",
+				userId: "user-2",
+			});
+
+			const userNoteTypes = await db.noteTypes
+				.where("userId")
+				.equals("user-1")
+				.toArray();
+			expect(userNoteTypes).toHaveLength(1);
+			expect(userNoteTypes[0]?.id).toBe("note-type-1");
+		});
+
+		it("should find unsynced note types", async () => {
+			await db.noteTypes.add(testNoteType);
+			await db.noteTypes.add({
+				...testNoteType,
+				id: "note-type-2",
+				_synced: true,
+			});
+
+			const unsynced = await db.noteTypes.filter((nt) => !nt._synced).toArray();
+			expect(unsynced).toHaveLength(1);
+			expect(unsynced[0]?.id).toBe("note-type-1");
+		});
+
+		it("should update a note type", async () => {
+			await db.noteTypes.add(testNoteType);
+			await db.noteTypes.update("note-type-1", {
+				name: "Updated Name",
+				isReversible: true,
+			});
+
+			const updated = await db.noteTypes.get("note-type-1");
+			expect(updated?.name).toBe("Updated Name");
+			expect(updated?.isReversible).toBe(true);
+		});
+	});
+
+	describe("noteFieldTypes table", () => {
+		const testNoteFieldType: LocalNoteFieldType = {
+			id: "field-type-1",
+			noteTypeId: "note-type-1",
+			name: "Front",
+			order: 0,
+			fieldType: FieldType.Text,
+			createdAt: new Date("2024-01-01"),
+			updatedAt: new Date("2024-01-01"),
+			deletedAt: null,
+			syncVersion: 0,
+			_synced: false,
+		};
+
+		it("should add and retrieve a note field type", async () => {
+			await db.noteFieldTypes.add(testNoteFieldType);
+			const retrieved = await db.noteFieldTypes.get("field-type-1");
+			expect(retrieved).toEqual(testNoteFieldType);
+		});
+
+		it("should find note field types by noteTypeId", async () => {
+			await db.noteFieldTypes.add(testNoteFieldType);
+			await db.noteFieldTypes.add({
+				...testNoteFieldType,
+				id: "field-type-2",
+				noteTypeId: "note-type-2",
+			});
+
+			const fields = await db.noteFieldTypes
+				.where("noteTypeId")
+				.equals("note-type-1")
+				.toArray();
+			expect(fields).toHaveLength(1);
+			expect(fields[0]?.id).toBe("field-type-1");
+		});
+
+		it("should find unsynced note field types", async () => {
+			await db.noteFieldTypes.add(testNoteFieldType);
+			await db.noteFieldTypes.add({
+				...testNoteFieldType,
+				id: "field-type-2",
+				_synced: true,
+			});
+
+			const unsynced = await db.noteFieldTypes
+				.filter((nft) => !nft._synced)
+				.toArray();
+			expect(unsynced).toHaveLength(1);
+			expect(unsynced[0]?.id).toBe("field-type-1");
+		});
+	});
+
+	describe("notes table", () => {
+		const testNote: LocalNote = {
+			id: "note-1",
+			deckId: "deck-1",
+			noteTypeId: "note-type-1",
+			createdAt: new Date("2024-01-01"),
+			updatedAt: new Date("2024-01-01"),
+			deletedAt: null,
+			syncVersion: 0,
+			_synced: false,
+		};
+
+		it("should add and retrieve a note", async () => {
+			await db.notes.add(testNote);
+			const retrieved = await db.notes.get("note-1");
+			expect(retrieved).toEqual(testNote);
+		});
+
+		it("should find notes by deckId", async () => {
+			await db.notes.add(testNote);
+			await db.notes.add({
+				...testNote,
+				id: "note-2",
+				deckId: "deck-2",
+			});
+
+			const deckNotes = await db.notes
+				.where("deckId")
+				.equals("deck-1")
+				.toArray();
+			expect(deckNotes).toHaveLength(1);
+			expect(deckNotes[0]?.id).toBe("note-1");
+		});
+
+		it("should find notes by noteTypeId", async () => {
+			await db.notes.add(testNote);
+			await db.notes.add({
+				...testNote,
+				id: "note-2",
+				noteTypeId: "note-type-2",
+			});
+
+			const typeNotes = await db.notes
+				.where("noteTypeId")
+				.equals("note-type-1")
+				.toArray();
+			expect(typeNotes).toHaveLength(1);
+			expect(typeNotes[0]?.id).toBe("note-1");
+		});
+
+		it("should find unsynced notes", async () => {
+			await db.notes.add(testNote);
+			await db.notes.add({
+				...testNote,
+				id: "note-2",
+				_synced: true,
+			});
+
+			const unsynced = await db.notes.filter((n) => !n._synced).toArray();
+			expect(unsynced).toHaveLength(1);
+			expect(unsynced[0]?.id).toBe("note-1");
+		});
+	});
+
+	describe("noteFieldValues table", () => {
+		const testNoteFieldValue: LocalNoteFieldValue = {
+			id: "field-value-1",
+			noteId: "note-1",
+			noteFieldTypeId: "field-type-1",
+			value: "Test value",
+			createdAt: new Date("2024-01-01"),
+			updatedAt: new Date("2024-01-01"),
+			syncVersion: 0,
+			_synced: false,
+		};
+
+		it("should add and retrieve a note field value", async () => {
+			await db.noteFieldValues.add(testNoteFieldValue);
+			const retrieved = await db.noteFieldValues.get("field-value-1");
+			expect(retrieved).toEqual(testNoteFieldValue);
+		});
+
+		it("should find note field values by noteId", async () => {
+			await db.noteFieldValues.add(testNoteFieldValue);
+			await db.noteFieldValues.add({
+				...testNoteFieldValue,
+				id: "field-value-2",
+				noteId: "note-2",
+			});
+
+			const noteFieldValues = await db.noteFieldValues
+				.where("noteId")
+				.equals("note-1")
+				.toArray();
+			expect(noteFieldValues).toHaveLength(1);
+			expect(noteFieldValues[0]?.id).toBe("field-value-1");
+		});
+
+		it("should find note field values by noteFieldTypeId", async () => {
+			await db.noteFieldValues.add(testNoteFieldValue);
+			await db.noteFieldValues.add({
+				...testNoteFieldValue,
+				id: "field-value-2",
+				noteFieldTypeId: "field-type-2",
+			});
+
+			const typeFieldValues = await db.noteFieldValues
+				.where("noteFieldTypeId")
+				.equals("field-type-1")
+				.toArray();
+			expect(typeFieldValues).toHaveLength(1);
+			expect(typeFieldValues[0]?.id).toBe("field-value-1");
+		});
+
+		it("should find unsynced note field values", async () => {
+			await db.noteFieldValues.add(testNoteFieldValue);
+			await db.noteFieldValues.add({
+				...testNoteFieldValue,
+				id: "field-value-2",
+				_synced: true,
+			});
+
+			const unsynced = await db.noteFieldValues
+				.filter((nfv) => !nfv._synced)
+				.toArray();
+			expect(unsynced).toHaveLength(1);
+			expect(unsynced[0]?.id).toBe("field-value-1");
+		});
+	});
+
 	describe("constants", () => {
 		it("should export CardState enum", () => {
 			expect(CardState.New).toBe(0);
@@ -303,5 +563,9 @@ describe("KiokuDatabase", () => {
 			expect(Rating.Good).toBe(3);
 			expect(Rating.Easy).toBe(4);
 		});
+
+		it("should export FieldType enum", () => {
+			expect(FieldType.Text).toBe("text");
+		});
 	});
 });
diff --git a/src/client/db/index.ts b/src/client/db/index.ts
index 4c381d2..5318b17 100644
--- a/src/client/db/index.ts
+++ b/src/client/db/index.ts
@@ -25,6 +25,50 @@ export const Rating = {
 export type RatingType = (typeof Rating)[keyof typeof Rating];
 
 /**
+ * Field types for note fields
+ */
+export const FieldType = {
+	Text: "text",
+} as const;
+
+export type FieldTypeType = (typeof FieldType)[keyof typeof FieldType];
+
+/**
+ * Local note type stored in IndexedDB
+ * Defines the structure of notes (fields and card templates)
+ */
+export interface LocalNoteType {
+	id: string;
+	userId: string;
+	name: string;
+	frontTemplate: string;
+	backTemplate: string;
+	isReversible: boolean;
+	createdAt: Date;
+	updatedAt: Date;
+	deletedAt: Date | null;
+	syncVersion: number;
+	_synced: boolean;
+}
+
+/**
+ * Local note field type stored in IndexedDB
+ * Defines a field within a note type
+ */
+export interface LocalNoteFieldType {
+	id: string;
+	noteTypeId: string;
+	name: string;
+	order: number;
+	fieldType: FieldTypeType;
+	createdAt: Date;
+	updatedAt: Date;
+	deletedAt: Date | null;
+	syncVersion: number;
+	_synced: boolean;
+}
+
+/**
  * Local deck stored in IndexedDB
  * Includes _synced flag for offline sync tracking
  */
@@ -42,12 +86,44 @@ export interface LocalDeck {
 }
 
 /**
+ * Local note stored in IndexedDB
+ * Contains field values for a note type
+ */
+export interface LocalNote {
+	id: string;
+	deckId: string;
+	noteTypeId: string;
+	createdAt: Date;
+	updatedAt: Date;
+	deletedAt: Date | null;
+	syncVersion: number;
+	_synced: boolean;
+}
+
+/**
+ * Local note field value stored in IndexedDB
+ * Contains the value for a specific field in a note
+ */
+export interface LocalNoteFieldValue {
+	id: string;
+	noteId: string;
+	noteFieldTypeId: string;
+	value: string;
+	createdAt: Date;
+	updatedAt: Date;
+	syncVersion: number;
+	_synced: boolean;
+}
+
+/**
  * Local card stored in IndexedDB
  * Includes _synced flag for offline sync tracking
  */
 export interface LocalCard {
 	id: string;
 	deckId: string;
+	noteId: string | null;
+	isReversed: boolean | null;
 	front: string;
 	back: string;
 
@@ -91,13 +167,17 @@ export interface LocalReviewLog {
 /**
  * Kioku local database using Dexie (IndexedDB wrapper)
  *
- * This database stores decks, cards, and review logs locally for offline support.
+ * This database stores decks, cards, notes, and review logs locally for offline support.
  * Each entity has a _synced flag to track whether it has been synchronized with the server.
  */
 export class KiokuDatabase extends Dexie {
 	decks!: EntityTable<LocalDeck, "id">;
 	cards!: EntityTable<LocalCard, "id">;
 	reviewLogs!: EntityTable<LocalReviewLog, "id">;
+	noteTypes!: EntityTable<LocalNoteType, "id">;
+	noteFieldTypes!: EntityTable<LocalNoteFieldType, "id">;
+	notes!: EntityTable<LocalNote, "id">;
+	noteFieldValues!: EntityTable<LocalNoteFieldValue, "id">;
 
 	constructor() {
 		super("kioku");
@@ -120,6 +200,47 @@ export class KiokuDatabase extends Dexie {
 			// reviewedAt: for ordering reviews
 			reviewLogs: "id, cardId, userId, reviewedAt",
 		});
+
+		// Version 2: Add note-related tables for Anki-compatible note system
+		this.version(2)
+			.stores({
+				decks: "id, userId, updatedAt",
+				// Add noteId index for filtering cards by note
+				cards: "id, deckId, noteId, updatedAt, due, state",
+				reviewLogs: "id, cardId, userId, reviewedAt",
+
+				// Note types define the structure of notes (templates and fields)
+				// userId: for filtering by user
+				noteTypes: "id, userId, updatedAt",
+
+				// Note field types define the fields for a note type
+				// noteTypeId: for filtering fields by note type
+				noteFieldTypes: "id, noteTypeId, updatedAt",
+
+				// Notes contain field values for a note type
+				// deckId: for filtering notes by deck
+				// noteTypeId: for filtering notes by note type
+				notes: "id, deckId, noteTypeId, updatedAt",
+
+				// Note field values contain the actual field data
+				// noteId: for filtering values by note
+				// noteFieldTypeId: for filtering values by field type
+				noteFieldValues: "id, noteId, noteFieldTypeId, updatedAt",
+			})
+			.upgrade((tx) => {
+				// Migrate existing cards to have noteId and isReversed as null
+				return tx
+					.table("cards")
+					.toCollection()
+					.modify((card) => {
+						if (card.noteId === undefined) {
+							card.noteId = null;
+						}
+						if (card.isReversed === undefined) {
+							card.isReversed = null;
+						}
+					});
+			});
 	}
 }
 
diff --git a/src/client/db/repositories.ts b/src/client/db/repositories.ts
index 1a03f93..a2f0b41 100644
--- a/src/client/db/repositories.ts
+++ b/src/client/db/repositories.ts
@@ -171,6 +171,8 @@ export const localCardRepository = {
 		data: Omit<
 			LocalCard,
 			| "id"
+			| "noteId"
+			| "isReversed"
 			| "state"
 			| "due"
 			| "stability"
@@ -185,11 +187,14 @@ export const localCardRepository = {
 			| "deletedAt"
 			| "syncVersion"
 			| "_synced"
-		>,
+		> &
+			Partial<Pick<LocalCard, "noteId" | "isReversed">>,
 	): Promise<LocalCard> {
 		const now = new Date();
 		const card: LocalCard = {
 			id: uuidv4(),
+			noteId: null,
+			isReversed: null,
 			...data,
 			state: CardState.New,
 			due: now,
diff --git a/src/client/sync/conflict.ts b/src/client/sync/conflict.ts
index 4e0e3ef..e4f1cbf 100644
--- a/src/client/sync/conflict.ts
+++ b/src/client/sync/conflict.ts
@@ -65,6 +65,8 @@ function serverCardToLocal(card: ServerCard): LocalCard {
 	return {
 		id: card.id,
 		deckId: card.deckId,
+		noteId: card.noteId ?? null,
+		isReversed: card.isReversed ?? null,
 		front: card.front,
 		back: card.back,
 		state: card.state as LocalCard["state"],
diff --git a/src/client/sync/pull.test.ts b/src/client/sync/pull.test.ts
index 23c64ef..84c22bd 100644
--- a/src/client/sync/pull.test.ts
+++ b/src/client/sync/pull.test.ts
@@ -107,6 +107,8 @@ describe("pullResultToLocalData", () => {
 		expect(result.cards[0]).toEqual({
 			id: "card-1",
 			deckId: "deck-1",
+			noteId: null,
+			isReversed: null,
 			front: "Question",
 			back: "Answer",
 			state: CardState.Review,
diff --git a/src/client/sync/pull.ts b/src/client/sync/pull.ts
index 333782c..fa0899b 100644
--- a/src/client/sync/pull.ts
+++ b/src/client/sync/pull.ts
@@ -28,6 +28,8 @@ export interface ServerDeck {
 export interface ServerCard {
 	id: string;
 	deckId: string;
+	noteId?: string | null;
+	isReversed?: boolean | null;
 	front: string;
 	back: string;
 	state: number;
@@ -104,6 +106,8 @@ function serverCardToLocal(card: ServerCard): LocalCard {
 	return {
 		id: card.id,
 		deckId: card.deckId,
+		noteId: card.noteId ?? null,
+		isReversed: card.isReversed ?? null,
 		front: card.front,
 		back: card.back,
 		state: card.state as CardStateType,
diff --git a/src/client/sync/push.test.ts b/src/client/sync/push.test.ts
index 911a8d3..a3ff154 100644
--- a/src/client/sync/push.test.ts
+++ b/src/client/sync/push.test.ts
@@ -77,6 +77,8 @@ describe("pendingChangesToPushData", () => {
 			{
 				id: "card-1",
 				deckId: "deck-1",
+				noteId: null,
+				isReversed: null,
 				front: "Question",
 				back: "Answer",
 				state: CardState.Review,
@@ -128,6 +130,8 @@ describe("pendingChangesToPushData", () => {
 			{
 				id: "card-1",
 				deckId: "deck-1",
+				noteId: null,
+				isReversed: null,
 				front: "New Card",
 				back: "Answer",
 				state: CardState.New,
diff --git a/src/client/sync/queue.test.ts b/src/client/sync/queue.test.ts
index f6a3019..b62ece2 100644
--- a/src/client/sync/queue.test.ts
+++ b/src/client/sync/queue.test.ts
@@ -420,6 +420,8 @@ describe("SyncQueue", () => {
 			const serverCard = {
 				id: "server-card-1",
 				deckId: deck.id,
+				noteId: null,
+				isReversed: null,
 				front: "Server Question",
 				back: "Server Answer",
 				state: CardState.New,