app/api/admin/posts/[id]/route.ts mints the case number for a newly approved post by counting the approved rows and adding one:
const { count } = await supabase
.from("posts")
.select("*", { count: "exact", head: true })
.eq("status", "approved");
const seq = ((count ?? 0) + 1).toString().padStart(4, "0");
caseNumber = `APM-${seq}`;This is wrong in two independent ways.
1. It collides whenever a numbered case is deleted or unapproved. The count is not the high-water mark. The live registry currently holds APM-0001 through APM-0008 and APM-0037 through APM-0071, with APM-0009 through APM-0036 retired. There are 43 approved posts, so the next approval mints APM-0044, which already exists and is a completely different case. Case numbers are described as permanent identifiers and are used as the public URL (/case/[caseNumber]), so a collision either fails on a unique index or silently produces two cases with one number.
2. It races. Two approvals processed concurrently both read the same count and both mint the same number. There is no transaction around the read and the write.
Suggested fix: move the allocation into Postgres so it is atomic and monotonic, for example a dedicated sequence plus a SECURITY DEFINER function called via supabase.rpc(...), following the pattern already used for consume_rate_limit in lib/rate-limit/shared.ts. Deriving from MAX(case_number) inside a single statement would also work, but a sequence is cleaner. Add a unique index on posts.case_number if one is not already present, and add a regression test alongside app/api/admin/posts/route.test.ts.
If you would like to take this on, comment here to claim it. Contributors can hold two open claims at a time.
app/api/admin/posts/[id]/route.tsmints the case number for a newly approved post by counting the approved rows and adding one:This is wrong in two independent ways.
1. It collides whenever a numbered case is deleted or unapproved. The count is not the high-water mark. The live registry currently holds
APM-0001throughAPM-0008andAPM-0037throughAPM-0071, withAPM-0009throughAPM-0036retired. There are 43 approved posts, so the next approval mintsAPM-0044, which already exists and is a completely different case. Case numbers are described as permanent identifiers and are used as the public URL (/case/[caseNumber]), so a collision either fails on a unique index or silently produces two cases with one number.2. It races. Two approvals processed concurrently both read the same count and both mint the same number. There is no transaction around the read and the write.
Suggested fix: move the allocation into Postgres so it is atomic and monotonic, for example a dedicated sequence plus a
SECURITY DEFINERfunction called viasupabase.rpc(...), following the pattern already used forconsume_rate_limitinlib/rate-limit/shared.ts. Deriving fromMAX(case_number)inside a single statement would also work, but a sequence is cleaner. Add a unique index onposts.case_numberif one is not already present, and add a regression test alongsideapp/api/admin/posts/route.test.ts.If you would like to take this on, comment here to claim it. Contributors can hold two open claims at a time.