4 files changed, 67 insertions, 6 deletions
diff --git a/src/lib/server/services/groceries.test.ts b/src/lib/server/services/groceries.test.ts
index 4d9aa2f..d12111f 100644
--- a/src/lib/server/services/groceries.test.ts
+++ b/src/lib/server/services/groceries.test.ts
@@ -44,6 +44,39 @@ describe('groceries service', () => {
 		expect(result.items).toHaveLength(2);
 	});
 
+	it('orders items with identical createdAt deterministically', async () => {
+		const now = Temporal.Now.instant().toString({
+			smallestUnit: 'millisecond',
+		});
+		await db.insert(groceries).values([
+			{ name: 'milk', checked: false, createdAt: now },
+			{ name: 'bread', checked: false, createdAt: now },
+		]);
+
+		const unchecked = await loadUnchecked(db);
+		expect(unchecked.items.map((i) => i.name)).toEqual(['bread', 'milk']);
+
+		const all = await loadAll(db);
+		expect(all.items.map((i) => i.name)).toEqual(['bread', 'milk']);
+	});
+
+	it('picks bestRef deterministically when quality scores tie', async () => {
+		const now = Temporal.Now.instant().toString({
+			smallestUnit: 'millisecond',
+		});
+		const [g] = await db
+			.insert(groceries)
+			.values({ name: 'coffee', checked: false, createdAt: now })
+			.returning();
+		await db.insert(references).values([
+			{ groceryId: g.id, reference: 'Brand A', qualityScore: 80 },
+			{ groceryId: g.id, reference: 'Brand B', qualityScore: 80 },
+		]);
+
+		const result = await loadAll(db);
+		expect(result.items[0].bestRef?.reference).toBe('Brand A');
+	});
+
 	it('toggle flips checked state', async () => {
 		const now = Temporal.Now.instant().toString({
 			smallestUnit: 'millisecond',
diff --git a/src/lib/server/services/groceries.ts b/src/lib/server/services/groceries.ts
index 9aa8102..fc66300 100644
--- a/src/lib/server/services/groceries.ts
+++ b/src/lib/server/services/groceries.ts
@@ -20,12 +20,15 @@ export async function loadUnchecked(
 			.select()
 			.from(groceries)
 			.where(eq(groceries.checked, false))
-			.orderBy(desc(groceries.createdAt)),
+			.orderBy(desc(groceries.createdAt), desc(groceries.id)),
 		db
 			.select()
 			.from(categories)
 			.orderBy(asc(categories.order), asc(categories.name)),
-		db.select().from(references).orderBy(desc(references.qualityScore)),
+		db
+			.select()
+			.from(references)
+			.orderBy(desc(references.qualityScore), asc(references.id)),
 	]);
 
 	return {
@@ -38,12 +41,18 @@ export async function loadAll(
 	db = defaultDb,
 ): Promise<{ items: EnrichedGrocery[]; categories: Category[] }> {
 	const [items, cats, refs] = await Promise.all([
-		db.select().from(groceries).orderBy(desc(groceries.createdAt)),
+		db
+			.select()
+			.from(groceries)
+			.orderBy(desc(groceries.createdAt), desc(groceries.id)),
 		db
 			.select()
 			.from(categories)
 			.orderBy(asc(categories.order), asc(categories.name)),
-		db.select().from(references).orderBy(desc(references.qualityScore)),
+		db
+			.select()
+			.from(references)
+			.orderBy(desc(references.qualityScore), asc(references.id)),
 	]);
 
 	return {
diff --git a/src/lib/server/services/references.test.ts b/src/lib/server/services/references.test.ts
index 49c5c94..bcefbd0 100644
--- a/src/lib/server/services/references.test.ts
+++ b/src/lib/server/services/references.test.ts
@@ -34,6 +34,25 @@ describe('references service', () => {
 		expect(refs[0].reference).toBe('Brand A');
 	});
 
+	it('loadForGrocery orders refs by score desc, then insertion', async () => {
+		const now = Temporal.Now.instant().toString({
+			smallestUnit: 'millisecond',
+		});
+		const [g] = await db
+			.insert(groceries)
+			.values({ name: 'coffee', checked: false, createdAt: now })
+			.returning();
+		await db.insert(references).values([
+			{ groceryId: g.id, reference: 'A' },
+			{ groceryId: g.id, reference: 'B', qualityScore: 50 },
+			{ groceryId: g.id, reference: 'C' },
+			{ groceryId: g.id, reference: 'D', qualityScore: 50 },
+		]);
+
+		const refs = await loadForGrocery(g.id, db);
+		expect(refs.map((r) => r.reference)).toEqual(['B', 'D', 'A', 'C']);
+	});
+
 	it('add creates a reference', async () => {
 		const now = Temporal.Now.instant().toString({
 			smallestUnit: 'millisecond',
diff --git a/src/lib/server/services/references.ts b/src/lib/server/services/references.ts
index 99ba487..c5b6630 100644
--- a/src/lib/server/services/references.ts
+++ b/src/lib/server/services/references.ts
@@ -1,6 +1,6 @@
 import { db as defaultDb } from '$lib/server/db';
 import { references } from '$lib/server/db/schema';
-import { eq, desc } from 'drizzle-orm';
+import { eq, desc, asc } from 'drizzle-orm';
 import type { AppDatabase } from './groceries';
 import type { Reference } from './types';
 
@@ -12,7 +12,7 @@ export async function loadForGrocery(
 		.select()
 		.from(references)
 		.where(eq(references.groceryId, groceryId))
-		.orderBy(desc(references.qualityScore));
+		.orderBy(desc(references.qualityScore), asc(references.id));
 }
 
 export async function add(