feat(server): add purge mechanism for soft-deleted records

Automatically removes soft-deleted records older than 90 days.
Runs on server startup and every 24 hours thereafter.
Respects foreign key dependencies when deleting.

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

5 files changed, 392 insertions, 0 deletions

diff --git a/src/server/index.ts b/src/server/index.ts
index 52eab67..4a6978d 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -11,6 +11,7 @@ import {
 	study,
 	sync,
 } from "./routes/index.js";
+import { startPurgeScheduler } from "./services/purge-scheduler.js";
 
 const app = new Hono();
 
@@ -44,4 +45,6 @@ serve({
 	port,
 });
 
+startPurgeScheduler();
+
 export { app };
diff --git a/src/server/repositories/index.ts b/src/server/repositories/index.ts
index 3256a49..bf41150 100644
--- a/src/server/repositories/index.ts
+++ b/src/server/repositories/index.ts
@@ -5,6 +5,8 @@ export {
 	noteFieldTypeRepository,
 	noteTypeRepository,
 } from "./noteType.js";
+export type { PurgeOptions, PurgeResult } from "./purge.js";
+export { purgeRepository } from "./purge.js";
 export { refreshTokenRepository } from "./refresh-token.js";
 export { reviewLogRepository } from "./review-log.js";
 export { syncRepository } from "./sync.js";
diff --git a/src/server/repositories/purge.test.ts b/src/server/repositories/purge.test.ts
new file mode 100644
index 0000000..efddd80
--- /dev/null
+++ b/src/server/repositories/purge.test.ts
@@ -0,0 +1,209 @@
+import { describe, expect, it, vi } from "vitest";
+import type { PurgeOptions, PurgeRepository, PurgeResult } from "./purge.js";
+
+function createMockPurgeResult(
+	overrides: Partial<PurgeResult> = {},
+): PurgeResult {
+	return {
+		reviewLogs: 0,
+		noteFieldValues: 0,
+		cards: 0,
+		notes: 0,
+		noteFieldTypes: 0,
+		noteTypes: 0,
+		decks: 0,
+		...overrides,
+	};
+}
+
+function createMockPurgeRepo(): PurgeRepository {
+	return {
+		purgeDeletedRecords: vi.fn(),
+	};
+}
+
+describe("PurgeRepository mock factory", () => {
+	describe("createMockPurgeResult", () => {
+		it("creates a valid PurgeResult with all zeros by default", () => {
+			const result = createMockPurgeResult();
+
+			expect(result.reviewLogs).toBe(0);
+			expect(result.noteFieldValues).toBe(0);
+			expect(result.cards).toBe(0);
+			expect(result.notes).toBe(0);
+			expect(result.noteFieldTypes).toBe(0);
+			expect(result.noteTypes).toBe(0);
+			expect(result.decks).toBe(0);
+		});
+
+		it("allows overriding properties", () => {
+			const result = createMockPurgeResult({
+				cards: 5,
+				notes: 3,
+				decks: 1,
+			});
+
+			expect(result.cards).toBe(5);
+			expect(result.notes).toBe(3);
+			expect(result.decks).toBe(1);
+			expect(result.reviewLogs).toBe(0);
+		});
+	});
+
+	describe("createMockPurgeRepo", () => {
+		it("creates a repository with purgeDeletedRecords method", () => {
+			const repo = createMockPurgeRepo();
+
+			expect(repo.purgeDeletedRecords).toBeDefined();
+		});
+
+		it("purgeDeletedRecords is mockable", async () => {
+			const repo = createMockPurgeRepo();
+			const mockResult = createMockPurgeResult({
+				cards: 10,
+				notes: 5,
+				reviewLogs: 25,
+			});
+
+			vi.mocked(repo.purgeDeletedRecords).mockResolvedValue(mockResult);
+
+			const options: PurgeOptions = { retentionDays: 90 };
+			const result = await repo.purgeDeletedRecords(options);
+
+			expect(result.cards).toBe(10);
+			expect(result.notes).toBe(5);
+			expect(result.reviewLogs).toBe(25);
+			expect(repo.purgeDeletedRecords).toHaveBeenCalledWith(options);
+		});
+
+		it("respects batchSize option", async () => {
+			const repo = createMockPurgeRepo();
+			const mockResult = createMockPurgeResult({ cards: 100 });
+
+			vi.mocked(repo.purgeDeletedRecords).mockResolvedValue(mockResult);
+
+			const options: PurgeOptions = { retentionDays: 30, batchSize: 500 };
+			await repo.purgeDeletedRecords(options);
+
+			expect(repo.purgeDeletedRecords).toHaveBeenCalledWith({
+				retentionDays: 30,
+				batchSize: 500,
+			});
+		});
+	});
+});
+
+describe("PurgeResult interface contracts", () => {
+	it("PurgeResult has all required fields", () => {
+		const result = createMockPurgeResult();
+
+		expect(result).toHaveProperty("reviewLogs");
+		expect(result).toHaveProperty("noteFieldValues");
+		expect(result).toHaveProperty("cards");
+		expect(result).toHaveProperty("notes");
+		expect(result).toHaveProperty("noteFieldTypes");
+		expect(result).toHaveProperty("noteTypes");
+		expect(result).toHaveProperty("decks");
+	});
+
+	it("all fields are numbers", () => {
+		const result = createMockPurgeResult({
+			reviewLogs: 10,
+			noteFieldValues: 20,
+			cards: 30,
+			notes: 40,
+			noteFieldTypes: 50,
+			noteTypes: 60,
+			decks: 70,
+		});
+
+		expect(typeof result.reviewLogs).toBe("number");
+		expect(typeof result.noteFieldValues).toBe("number");
+		expect(typeof result.cards).toBe("number");
+		expect(typeof result.notes).toBe("number");
+		expect(typeof result.noteFieldTypes).toBe("number");
+		expect(typeof result.noteTypes).toBe("number");
+		expect(typeof result.decks).toBe("number");
+	});
+});
+
+describe("PurgeOptions interface contracts", () => {
+	it("retentionDays is required", async () => {
+		const repo = createMockPurgeRepo();
+		vi.mocked(repo.purgeDeletedRecords).mockResolvedValue(
+			createMockPurgeResult(),
+		);
+
+		const options: PurgeOptions = { retentionDays: 90 };
+		await repo.purgeDeletedRecords(options);
+
+		expect(repo.purgeDeletedRecords).toHaveBeenCalledWith(
+			expect.objectContaining({ retentionDays: 90 }),
+		);
+	});
+
+	it("batchSize is optional", async () => {
+		const repo = createMockPurgeRepo();
+		vi.mocked(repo.purgeDeletedRecords).mockResolvedValue(
+			createMockPurgeResult(),
+		);
+
+		const optionsWithoutBatch: PurgeOptions = { retentionDays: 90 };
+		const optionsWithBatch: PurgeOptions = {
+			retentionDays: 90,
+			batchSize: 500,
+		};
+
+		await repo.purgeDeletedRecords(optionsWithoutBatch);
+		await repo.purgeDeletedRecords(optionsWithBatch);
+
+		expect(repo.purgeDeletedRecords).toHaveBeenCalledTimes(2);
+	});
+});
+
+describe("Purge deletion order", () => {
+	it("returns counts for all entity types", async () => {
+		const repo = createMockPurgeRepo();
+		const mockResult = createMockPurgeResult({
+			reviewLogs: 100,
+			noteFieldValues: 50,
+			cards: 25,
+			notes: 20,
+			noteFieldTypes: 10,
+			noteTypes: 5,
+			decks: 2,
+		});
+
+		vi.mocked(repo.purgeDeletedRecords).mockResolvedValue(mockResult);
+
+		const result = await repo.purgeDeletedRecords({ retentionDays: 90 });
+
+		expect(result.reviewLogs).toBe(100);
+		expect(result.noteFieldValues).toBe(50);
+		expect(result.cards).toBe(25);
+		expect(result.notes).toBe(20);
+		expect(result.noteFieldTypes).toBe(10);
+		expect(result.noteTypes).toBe(5);
+		expect(result.decks).toBe(2);
+	});
+
+	it("returns zero counts when no records to purge", async () => {
+		const repo = createMockPurgeRepo();
+		vi.mocked(repo.purgeDeletedRecords).mockResolvedValue(
+			createMockPurgeResult(),
+		);
+
+		const result = await repo.purgeDeletedRecords({ retentionDays: 90 });
+
+		const total =
+			result.reviewLogs +
+			result.noteFieldValues +
+			result.cards +
+			result.notes +
+			result.noteFieldTypes +
+			result.noteTypes +
+			result.decks;
+
+		expect(total).toBe(0);
+	});
+});
diff --git a/src/server/repositories/purge.ts b/src/server/repositories/purge.ts
new file mode 100644
index 0000000..15ea11c
--- /dev/null
+++ b/src/server/repositories/purge.ts
@@ -0,0 +1,140 @@
+import { and, inArray, isNotNull, lt } from "drizzle-orm";
+import { db } from "../db/index.js";
+import {
+	cards,
+	decks,
+	noteFieldTypes,
+	noteFieldValues,
+	notes,
+	noteTypes,
+	reviewLogs,
+} from "../db/schema.js";
+
+export interface PurgeResult {
+	reviewLogs: number;
+	noteFieldValues: number;
+	cards: number;
+	notes: number;
+	noteFieldTypes: number;
+	noteTypes: number;
+	decks: number;
+}
+
+export interface PurgeOptions {
+	retentionDays: number;
+	batchSize?: number;
+}
+
+export interface PurgeRepository {
+	purgeDeletedRecords(options: PurgeOptions): Promise<PurgeResult>;
+}
+
+const DEFAULT_BATCH_SIZE = 1000;
+
+export const purgeRepository: PurgeRepository = {
+	async purgeDeletedRecords(options: PurgeOptions): Promise<PurgeResult> {
+		const { retentionDays, batchSize = DEFAULT_BATCH_SIZE } = options;
+		const cutoffDate = new Date();
+		cutoffDate.setDate(cutoffDate.getDate() - retentionDays);
+
+		const result: PurgeResult = {
+			reviewLogs: 0,
+			noteFieldValues: 0,
+			cards: 0,
+			notes: 0,
+			noteFieldTypes: 0,
+			noteTypes: 0,
+			decks: 0,
+		};
+
+		await db.transaction(async (tx) => {
+			// 1. Delete review_logs for cards that will be purged
+			const cardsToPurge = await tx
+				.select({ id: cards.id })
+				.from(cards)
+				.where(and(isNotNull(cards.deletedAt), lt(cards.deletedAt, cutoffDate)))
+				.limit(batchSize);
+
+			if (cardsToPurge.length > 0) {
+				const cardIds = cardsToPurge.map((c) => c.id);
+				const deletedReviewLogs = await tx
+					.delete(reviewLogs)
+					.where(inArray(reviewLogs.cardId, cardIds))
+					.returning({ id: reviewLogs.id });
+				result.reviewLogs = deletedReviewLogs.length;
+			}
+
+			// 2. Delete note_field_values for notes that will be purged
+			const notesToPurge = await tx
+				.select({ id: notes.id })
+				.from(notes)
+				.where(and(isNotNull(notes.deletedAt), lt(notes.deletedAt, cutoffDate)))
+				.limit(batchSize);
+
+			if (notesToPurge.length > 0) {
+				const noteIds = notesToPurge.map((n) => n.id);
+				const deletedNoteFieldValues = await tx
+					.delete(noteFieldValues)
+					.where(inArray(noteFieldValues.noteId, noteIds))
+					.returning({ id: noteFieldValues.id });
+				result.noteFieldValues = deletedNoteFieldValues.length;
+			}
+
+			// 3. Delete cards
+			const deletedCards = await tx
+				.delete(cards)
+				.where(and(isNotNull(cards.deletedAt), lt(cards.deletedAt, cutoffDate)))
+				.returning({ id: cards.id });
+			result.cards = deletedCards.length;
+
+			// 4. Delete notes
+			const deletedNotes = await tx
+				.delete(notes)
+				.where(and(isNotNull(notes.deletedAt), lt(notes.deletedAt, cutoffDate)))
+				.returning({ id: notes.id });
+			result.notes = deletedNotes.length;
+
+			// 5. Delete note_field_types for note_types that will be purged
+			const noteTypesToPurge = await tx
+				.select({ id: noteTypes.id })
+				.from(noteTypes)
+				.where(
+					and(
+						isNotNull(noteTypes.deletedAt),
+						lt(noteTypes.deletedAt, cutoffDate),
+					),
+				)
+				.limit(batchSize);
+
+			if (noteTypesToPurge.length > 0) {
+				const noteTypeIds = noteTypesToPurge.map((nt) => nt.id);
+				const deletedNoteFieldTypes = await tx
+					.delete(noteFieldTypes)
+					.where(inArray(noteFieldTypes.noteTypeId, noteTypeIds))
+					.returning({ id: noteFieldTypes.id });
+				result.noteFieldTypes = deletedNoteFieldTypes.length;
+			}
+
+			// 6. Delete note_types
+			const deletedNoteTypes = await tx
+				.delete(noteTypes)
+				.where(
+					and(
+						isNotNull(noteTypes.deletedAt),
+						lt(noteTypes.deletedAt, cutoffDate),
+					),
+				)
+				.returning({ id: noteTypes.id });
+			result.noteTypes = deletedNoteTypes.length;
+
+			// 7. Delete decks
+			const deletedDecks = await tx
+				.delete(decks)
+				.where(and(isNotNull(decks.deletedAt), lt(decks.deletedAt, cutoffDate)))
+				.returning({ id: decks.id });
+			result.decks = deletedDecks.length;
+		});
+
+		return result;
+	},
+};
diff --git a/src/server/services/purge-scheduler.ts b/src/server/services/purge-scheduler.ts
new file mode 100644
index 0000000..6811593
--- /dev/null
+++ b/src/server/services/purge-scheduler.ts
@@ -0,0 +1,38 @@
+import { purgeRepository } from "../repositories/index.js";
+
+const DEFAULT_RETENTION_DAYS = 90;
+const PURGE_INTERVAL_MS = 24 * 60 * 60 * 1000; // 24 hours
+
+async function runPurge(): Promise<void> {
+	try {
+		const result = await purgeRepository.purgeDeletedRecords({
+			retentionDays: DEFAULT_RETENTION_DAYS,
+		});
+
+		const total =
+			result.reviewLogs +
+			result.noteFieldValues +
+			result.cards +
+			result.notes +
+			result.noteFieldTypes +
+			result.noteTypes +
+			result.decks;
+
+		console.log(`[Purge] Completed: ${total} records deleted`);
+		if (total > 0) {
+			console.log(
+				`[Purge] Details: reviewLogs=${result.reviewLogs}, noteFieldValues=${result.noteFieldValues}, cards=${result.cards}, notes=${result.notes}, noteFieldTypes=${result.noteFieldTypes}, noteTypes=${result.noteTypes}, decks=${result.decks}`,
+			);
+		}
+	} catch (error) {
+		console.error("[Purge] Failed:", error);
+	}
+}
+
+export function startPurgeScheduler(): void {
+	// Run immediately on startup
+	runPurge();
+
+	// Schedule to run every 24 hours
+	setInterval(runPurge, PURGE_INTERVAL_MS);
+}