From 3f9165e4fcbd83b7f98875a4a3de4036b67dde37 Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
Date: Wed, 31 Dec 2025 15:59:38 +0900
Subject: feat(crdt): add server-side CRDT document storage schema
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

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>
---
 src/server/db/schema-crdt.test.ts | 126 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 126 insertions(+)
 create mode 100644 src/server/db/schema-crdt.test.ts

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

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");
+		});
+	});
+});
-- 
cgit v1.2.3-70-g09d2