From e3a84df9fcae8f25c8ea1d527638c2bfaafb942e Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
Date: Wed, 31 Dec 2025 20:34:27 +0900
Subject: feat(db): add ORDER BY to repository SELECT queries
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Ensures deterministic ordering for all multi-row SELECT queries:
- deck/note/noteType findByUserId/findByDeckId: order by createdAt
- card findByNoteId: order by isReversed (normal card first)
- note field values: order by noteFieldTypeId
- sync pull queries: order by id

This guarantees consistent UI display and sync results regardless
of PostgreSQL's internal row ordering.

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

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

(limited to 'src/server/repositories/card.test.ts')

diff --git a/src/server/repositories/card.test.ts b/src/server/repositories/card.test.ts
index 4263dad..0a46a76 100644
--- a/src/server/repositories/card.test.ts
+++ b/src/server/repositories/card.test.ts
@@ -504,3 +504,140 @@ describe("Card deletion behavior", () => {
 		});
 	});
 });
+
+describe("findByDeckId ordering", () => {
+	it("returns cards ordered by createdAt", async () => {
+		const repo = createMockCardRepo();
+
+		const oldCard = createMockCard({
+			id: "card-old",
+			createdAt: new Date("2024-01-01"),
+		});
+		const newCard = createMockCard({
+			id: "card-new",
+			createdAt: new Date("2024-06-01"),
+		});
+
+		vi.mocked(repo.findByDeckId).mockResolvedValue([oldCard, newCard]);
+
+		const results = await repo.findByDeckId("deck-123");
+
+		expect(results).toHaveLength(2);
+		expect(results[0]?.id).toBe("card-old");
+		expect(results[1]?.id).toBe("card-new");
+		expect(results[0]?.createdAt.getTime()).toBeLessThan(
+			results[1]?.createdAt.getTime() ?? 0,
+		);
+	});
+
+	it("returns empty array when deck has no cards", async () => {
+		const repo = createMockCardRepo();
+
+		vi.mocked(repo.findByDeckId).mockResolvedValue([]);
+
+		const results = await repo.findByDeckId("deck-with-no-cards");
+		expect(results).toHaveLength(0);
+	});
+
+	it("maintains consistent ordering across multiple calls", async () => {
+		const repo = createMockCardRepo();
+
+		const card1 = createMockCard({
+			id: "card-1",
+			createdAt: new Date("2024-01-01"),
+		});
+		const card2 = createMockCard({
+			id: "card-2",
+			createdAt: new Date("2024-02-01"),
+		});
+		const card3 = createMockCard({
+			id: "card-3",
+			createdAt: new Date("2024-03-01"),
+		});
+
+		vi.mocked(repo.findByDeckId).mockResolvedValue([card1, card2, card3]);
+
+		const results1 = await repo.findByDeckId("deck-123");
+		const results2 = await repo.findByDeckId("deck-123");
+
+		expect(results1.map((c) => c.id)).toEqual(results2.map((c) => c.id));
+		expect(results1.map((c) => c.id)).toEqual(["card-1", "card-2", "card-3"]);
+	});
+});
+
+describe("findByNoteId ordering", () => {
+	it("returns cards ordered by isReversed (normal card first)", async () => {
+		const repo = createMockCardRepo();
+
+		const normalCard = createMockCard({
+			id: "card-normal",
+			noteId: "note-123",
+			isReversed: false,
+		});
+		const reversedCard = createMockCard({
+			id: "card-reversed",
+			noteId: "note-123",
+			isReversed: true,
+		});
+
+		// Mock returns cards in isReversed order (false first, true second)
+		vi.mocked(repo.findByNoteId).mockResolvedValue([normalCard, reversedCard]);
+
+		const results = await repo.findByNoteId("note-123");
+
+		expect(results).toHaveLength(2);
+		expect(results[0]?.id).toBe("card-normal");
+		expect(results[0]?.isReversed).toBe(false);
+		expect(results[1]?.id).toBe("card-reversed");
+		expect(results[1]?.isReversed).toBe(true);
+	});
+
+	it("returns single card for non-reversible note type", async () => {
+		const repo = createMockCardRepo();
+
+		const normalCard = createMockCard({
+			id: "card-only",
+			noteId: "note-123",
+			isReversed: false,
+		});
+
+		vi.mocked(repo.findByNoteId).mockResolvedValue([normalCard]);
+
+		const results = await repo.findByNoteId("note-123");
+
+		expect(results).toHaveLength(1);
+		expect(results[0]?.isReversed).toBe(false);
+	});
+
+	it("returns empty array when note has no cards", async () => {
+		const repo = createMockCardRepo();
+
+		vi.mocked(repo.findByNoteId).mockResolvedValue([]);
+
+		const results = await repo.findByNoteId("note-without-cards");
+		expect(results).toHaveLength(0);
+	});
+
+	it("maintains consistent ordering across multiple calls", async () => {
+		const repo = createMockCardRepo();
+
+		const normalCard = createMockCard({
+			id: "card-normal",
+			noteId: "note-123",
+			isReversed: false,
+		});
+		const reversedCard = createMockCard({
+			id: "card-reversed",
+			noteId: "note-123",
+			isReversed: true,
+		});
+
+		vi.mocked(repo.findByNoteId).mockResolvedValue([normalCard, reversedCard]);
+
+		const results1 = await repo.findByNoteId("note-123");
+		const results2 = await repo.findByNoteId("note-123");
+
+		expect(results1.map((c) => c.id)).toEqual(results2.map((c) => c.id));
+		expect(results1.map((c) => c.isReversed)).toEqual([false, true]);
+	});
+});
-- 
cgit v1.2.3-70-g09d2