TL;DR. Our Supabase upsert wrote the row. The chained
.select().single()returned PGRST116. The wrapper read that as a failed write. The frontend retried. The retry was wrong. Two days to find why.
We've been running an LMS on Supabase for the past several months — auth, RLS on every table, FastAPI talking to Postgres. The bug below cost us two days last quarter, and the fix changed how we read PGRST116 in our wrapper.
The setting
We have a quiz_attempts table. Each attempt gets created on quiz start, updated as the student progresses. The update is an upsert because a retry of the same attempt_id should patch the existing row, not insert a duplicate.
const { data, error } = await supabase
.from('quiz_attempts')
.upsert({ id, student_id, quiz_id, attempt_count })
.select()
.single();
The .select().single() chain returns the upserted row so we can show the student their new state.
RLS policies, simplified:
create policy "students write their own attempts"
on quiz_attempts for all to authenticated
using (student_id = auth.uid())
with check (student_id = auth.uid());
create policy "students read their own attempts"
on quiz_attempts for select to authenticated
using (student_id = auth.uid() and is_visible = true);
The SELECT policy carries an extra is_visible check that the write policy doesn't. That asymmetry is the seam the bug walks through.
The bug
A trigger on quiz_attempts flips is_visible to false under a corner case (timing relative to a parallel write from an admin tool — the specifics aren't the point). The student's upsert committed: their fields were written, the row is theirs.
Then .select().single() runs. The SELECT policy applies. is_visible = false. PostgREST returns:
{
"code": "PGRST116",
"details": "Results contain 0 rows",
"message": "JSON object requested, multiple (or no) rows returned"
}
Our runtime wrapper auto-threw on any .error. To the upstream code, this looked identical to a failed upsert. The frontend retried with the same payload. The retry hit the same trigger. The user state diverged from the DB state. Etc.
Why this was hard
PGRST116 has two completely different meanings when it comes after a mutation:
- The upsert truly failed — constraint violation, missing required field, RLS denied the write.
- The upsert succeeded — RLS just hid the returned row from the caller.
The wrapper conflated them. The PostgrestError code is identical. The HTTP status is identical (406). The only difference lives in the database, which the client can't see.
Two days of logs because we kept treating "no row returned" as "no row written."
The fix
Three changes.
1. Don't auto-throw on PGRST116 from a .select().single() chained off a mutation. Treat it as ambiguous and route to a separate branch:
async function safeUpsertReturning(query) {
const { data, error } = await query;
if (!error) return { data, error: null };
if (error.code === 'PGRST116') {
// The mutation may have succeeded; we just can't read the row.
return { data: null, error: null, hidden: true };
}
throw error;
}
2. Service-role verification. When the wrapper returns hidden: true, the backend does a service-role read by id to confirm whether the row actually exists. Yes → success-without-readback (treat as written). No → real failure, propagate. The client never branches on this directly.
3. Observability tying client errors to DB state. Every PGRST116 from the wrapper emits a structured event with the query shape and request id. Server-side, we log the same id with the actual row state visible to service role. Correlating the two would have surfaced the mismatch on day one.
What I'd tell past-us
PGRST116 is not a write error. It's a visibility error.
Your wrapper, your error handler, your retry logic — each should know which one it's seeing. If your stack can't tell the difference, you're going to retry successful writes. The kind of bug that produces is the kind where the symptom and the cause are twelve layers apart.
What I want to hear back
- Do you separate "write failed" from "write succeeded but I can't read it" in your Supabase code? What does the wrapper look like?
- Has anyone built a Postgres-side audit trigger that captures "row was written but RLS hid it from the writer"? Curious about the shape.
- For service-role verification — anything safer than
select(*).eq('id', id)you've used?
The project that runs this stack is open source:
ArVaViT
/
equip
Free, open-source LMS for Bible schools, ministries, and nonprofit educational programs. React + FastAPI + Supabase.
Equip
A free, open-source learning management system built for Bible schools church ministries, and nonprofit educational programs
Live demo · Roadmap · Contributing · Changelog
Why this project?
Hundreds of small Bible schools, home churches, and missionary training programs around the world still manage courses on paper, WhatsApp, or spreadsheets. Commercial LMS platforms are expensive, overkill, or require technical expertise that volunteer-run organizations simply don't have.
Equip is designed to change that:
- Free forever — MIT-licensed, no paywalls, no "premium" tiers.
- Simple to deploy — one-click Vercel deploy with a free Supabase database. No Docker, no servers to manage.
- Built for small scale — optimized for 20-100 students, not enterprise pricing models.
- Contributor-friendly — clear docs, conventional commits, issue templates, and a welcoming community.
Features
| Area | What you get |
|---|---|
| Course authoring | Courses, modules, chapters, rich content blocks (TipTap editor with images, YouTube, callouts, audio) |
| Assessments | Multiple-choice, true/false, short-answer, and essay |