From c915f91fbe8d97201d813c792f4e1fe8c4401e15 Mon Sep 17 00:00:00 2001 From: Martin Kagamino Lehoux Date: Tue, 6 Oct 2026 00:46:53 +0200 Subject: 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. --- src/lib/server/services/groceries.test.ts | 33 ++++++++++++++++++++++++++++++ src/lib/server/services/groceries.ts | 17 +++++++++++---- src/lib/server/services/references.test.ts | 19 +++++++++++++++++ src/lib/server/services/references.ts | 4 ++-- 4 files changed, 67 insertions(+), 6 deletions(-) (limited to 'src/lib/server/services') 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( -- cgit v1.3