feat(crdt): add server-side CRDT document storage schema

Add PostgreSQL schema for storing Automerge CRDT documents with
indexes for efficient querying by user, entity type, and sync version.

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

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

3 files changed, 227 insertions, 1 deletions

diff --git a/src/server/db/index.ts b/src/server/db/index.ts
index 22da621..0e69fad 100644
--- a/src/server/db/index.ts
+++ b/src/server/db/index.ts
@@ -1,5 +1,6 @@
 import { drizzle } from "drizzle-orm/node-postgres";
 import * as schema from "./schema.js";
+import * as schemaCrdt from "./schema-crdt.js";
 
 const databaseUrl = process.env.DATABASE_URL;
 
@@ -7,6 +8,9 @@ if (!databaseUrl) {
 	throw new Error("DATABASE_URL environment variable is not set");
 }
 
-export const db = drizzle(databaseUrl, { schema });
+export const db = drizzle(databaseUrl, {
+	schema: { ...schema, ...schemaCrdt },
+});
 
 export * from "./schema.js";
+export * from "./schema-crdt.js";
diff --git a/src/server/db/schema-crdt.test.ts b/src/server/db/schema-crdt.test.ts
new file mode 100644
index 0000000..4860cb9
--- /dev/null
+++ b/src/server/db/schema-crdt.test.ts
@@ -0,0 +1,126 @@
+import { getTableName } from "drizzle-orm";
+import { describe, expect, it } from "vitest";
+import {
+	CrdtEntityType,
+	type CrdtEntityTypeValue,
+	crdtDocuments,
+} from "./schema-crdt";
+
+describe("CRDT Schema", () => {
+	describe("CrdtEntityType", () => {
+		it("should have all required entity types", () => {
+			expect(CrdtEntityType.Deck).toBe("deck");
+			expect(CrdtEntityType.NoteType).toBe("noteType");
+			expect(CrdtEntityType.NoteFieldType).toBe("noteFieldType");
+			expect(CrdtEntityType.Note).toBe("note");
+			expect(CrdtEntityType.NoteFieldValue).toBe("noteFieldValue");
+			expect(CrdtEntityType.Card).toBe("card");
+			expect(CrdtEntityType.ReviewLog).toBe("reviewLog");
+		});
+
+		it("should be immutable (const assertion)", () => {
+			// TypeScript const assertion ensures immutability at compile time
+			// We verify the object structure matches expected values
+			const entityTypes = Object.values(CrdtEntityType);
+			expect(entityTypes).toHaveLength(7);
+			expect(entityTypes).toContain("deck");
+			expect(entityTypes).toContain("noteType");
+			expect(entityTypes).toContain("noteFieldType");
+			expect(entityTypes).toContain("note");
+			expect(entityTypes).toContain("noteFieldValue");
+			expect(entityTypes).toContain("card");
+			expect(entityTypes).toContain("reviewLog");
+		});
+	});
+
+	describe("CrdtEntityTypeValue type", () => {
+		it("should accept valid entity type values", () => {
+			// Type checking at compile time, runtime verification
+			const validTypes: CrdtEntityTypeValue[] = [
+				"deck",
+				"noteType",
+				"noteFieldType",
+				"note",
+				"noteFieldValue",
+				"card",
+				"reviewLog",
+			];
+			expect(validTypes).toHaveLength(7);
+		});
+	});
+
+	describe("crdtDocuments table", () => {
+		it("should have correct table name", () => {
+			expect(getTableName(crdtDocuments)).toBe("crdt_documents");
+		});
+
+		it("should have required columns", () => {
+			const columns = Object.keys(crdtDocuments);
+			expect(columns).toContain("id");
+			expect(columns).toContain("userId");
+			expect(columns).toContain("entityType");
+			expect(columns).toContain("entityId");
+			expect(columns).toContain("binary");
+			expect(columns).toContain("syncVersion");
+			expect(columns).toContain("createdAt");
+			expect(columns).toContain("updatedAt");
+		});
+
+		it("should have id as UUID primary key", () => {
+			const idColumn = crdtDocuments.id;
+			// Drizzle internally uses 'string' for UUID dataType
+			expect(idColumn.dataType).toBe("string");
+			expect(idColumn.primary).toBe(true);
+			// Verify column name maps to correct DB column
+			expect(idColumn.name).toBe("id");
+		});
+
+		it("should have userId as UUID with foreign key reference", () => {
+			const userIdColumn = crdtDocuments.userId;
+			expect(userIdColumn.dataType).toBe("string");
+			expect(userIdColumn.notNull).toBe(true);
+			expect(userIdColumn.name).toBe("user_id");
+		});
+
+		it("should have entityType as varchar", () => {
+			const entityTypeColumn = crdtDocuments.entityType;
+			expect(entityTypeColumn.dataType).toBe("string");
+			expect(entityTypeColumn.notNull).toBe(true);
+			expect(entityTypeColumn.name).toBe("entity_type");
+		});
+
+		it("should have entityId as UUID", () => {
+			const entityIdColumn = crdtDocuments.entityId;
+			expect(entityIdColumn.dataType).toBe("string");
+			expect(entityIdColumn.notNull).toBe(true);
+			expect(entityIdColumn.name).toBe("entity_id");
+		});
+
+		it("should have binary as varchar for base64 storage", () => {
+			const binaryColumn = crdtDocuments.binary;
+			expect(binaryColumn.dataType).toBe("string");
+			expect(binaryColumn.notNull).toBe(true);
+		});
+
+		it("should have syncVersion as integer with default 0", () => {
+			const syncVersionColumn = crdtDocuments.syncVersion;
+			expect(syncVersionColumn.dataType).toBe("number");
+			expect(syncVersionColumn.notNull).toBe(true);
+			expect(syncVersionColumn.default).toBe(0);
+		});
+
+		it("should have createdAt as timestamp with timezone", () => {
+			const createdAtColumn = crdtDocuments.createdAt;
+			expect(createdAtColumn.dataType).toBe("date");
+			expect(createdAtColumn.notNull).toBe(true);
+			expect(createdAtColumn.name).toBe("created_at");
+		});
+
+		it("should have updatedAt as timestamp with timezone", () => {
+			const updatedAtColumn = crdtDocuments.updatedAt;
+			expect(updatedAtColumn.dataType).toBe("date");
+			expect(updatedAtColumn.notNull).toBe(true);
+			expect(updatedAtColumn.name).toBe("updated_at");
+		});
+	});
+});
diff --git a/src/server/db/schema-crdt.ts b/src/server/db/schema-crdt.ts
new file mode 100644
index 0000000..2567609
--- /dev/null
+++ b/src/server/db/schema-crdt.ts
@@ -0,0 +1,96 @@
+/**
+ * CRDT Document Storage Schema
+ *
+ * This module defines the PostgreSQL schema for storing Automerge CRDT documents.
+ * Each CRDT document is stored as a binary blob with metadata for efficient querying.
+ *
+ * Design:
+ * - Documents are keyed by (user_id, entity_type, entity_id) for efficient lookup
+ * - Binary data stores the serialized Automerge document
+ * - sync_version enables incremental sync operations
+ */
+
+import {
+	index,
+	integer,
+	pgTable,
+	timestamp,
+	uuid,
+	varchar,
+} from "drizzle-orm/pg-core";
+import { users } from "./schema.js";
+
+/**
+ * Valid entity types for CRDT documents
+ * Must match CrdtEntityType in client/sync/crdt/types.ts
+ */
+export const CrdtEntityType = {
+	Deck: "deck",
+	NoteType: "noteType",
+	NoteFieldType: "noteFieldType",
+	Note: "note",
+	NoteFieldValue: "noteFieldValue",
+	Card: "card",
+	ReviewLog: "reviewLog",
+} as const;
+
+export type CrdtEntityTypeValue =
+	(typeof CrdtEntityType)[keyof typeof CrdtEntityType];
+
+/**
+ * CRDT documents table
+ *
+ * Stores serialized Automerge documents for each entity.
+ * The binary field contains the full Automerge document state.
+ */
+export const crdtDocuments = pgTable(
+	"crdt_documents",
+	{
+		id: uuid("id").primaryKey().defaultRandom(),
+		/** User who owns this document */
+		userId: uuid("user_id")
+			.notNull()
+			.references(() => users.id, { onDelete: "cascade" }),
+		/** Entity type (deck, card, note, etc.) */
+		entityType: varchar("entity_type", { length: 50 }).notNull(),
+		/** Entity ID (matches the entity's primary key) */
+		entityId: uuid("entity_id").notNull(),
+		/** Serialized Automerge document binary (stored as base64 text for simplicity) */
+		binary: varchar("binary", { length: 1048576 }).notNull(),
+		/** Sync version for incremental sync */
+		syncVersion: integer("sync_version").notNull().default(0),
+		/** When the document was created */
+		createdAt: timestamp("created_at", { withTimezone: true })
+			.notNull()
+			.defaultNow(),
+		/** When the document was last updated */
+		updatedAt: timestamp("updated_at", { withTimezone: true })
+			.notNull()
+			.defaultNow(),
+	},
+	(table) => [
+		// Unique constraint on (user_id, entity_type, entity_id)
+		index("crdt_documents_user_entity_idx").on(
+			table.userId,
+			table.entityType,
+			table.entityId,
+		),
+		// Index for querying by entity type
+		index("crdt_documents_entity_type_idx").on(table.entityType),
+		// Index for sync version queries
+		index("crdt_documents_sync_version_idx").on(
+			table.userId,
+			table.syncVersion,
+		),
+	],
+);
+
+/**
+ * Type for inserting a new CRDT document
+ */
+export type NewCrdtDocument = typeof crdtDocuments.$inferInsert;
+
+/**
+ * Type for a CRDT document from the database
+ */
+export type CrdtDocument = typeof crdtDocuments.$inferSelect;