fix: make grocery and reference ordering deterministic

Every list query lacked a tiebreak, so rows sharing a sort key (same-ms
createdAt inserts, tied or NULL quality scores) came back in unspecified
order and bestRef flipped between loads. Add explicit tiebreaks: items
createdAt desc, id desc (newest first); references qualityScore desc,
id asc (first inserted wins ties). Categories were already total
(order, name unique).

Pin the rules with service tests.

1 files changed, 33 insertions, 0 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',