maybeSingle() Throws PGRST116 on a Too-Loose Filter

Conclusion

If a table’s unique constraint is a composite key over 3 columns, and your query filters on only 2 of them before calling .maybeSingle(), PGRST116 fires the moment a row exists where the third column differs. PostgREST (the REST layer Supabase runs internally) errors out on .maybeSingle() or .single() as soon as 2 or more rows match — it has no way to decide which one to return. Data that genuinely existed was disappearing as a 404, purely because the filter was looser than the constraint it was meant to match.

Symptom

There’s an API that serves a PDF copy of the terms-of-service text a user agreed to at signature time. The copies live in a signature_evidence table, and the read side looked up a single row by entity (derived from the version) and entity_id (the contract ID) — 2 columns.

// src/app/api/evidence/[id]/terms/route.ts (before)
const { data: ev } = await admin
  .from("signature_evidence")
  .select("frozen_path, mime_type, signer_snapshot")
  .eq("entity", termsEvidenceEntity(version))
  .eq("entity_id", id)
  .maybeSingle();

The record was confirmed to exist, yet hitting this endpoint sometimes returned ev as empty and a 404 (“no terms evidence found for this contract at signing time”). The write path was fine — this was a read-side problem.

Root cause

signature_evidence’s unique constraint is a composite key across 3 columns: entity, entity_id, and digest_sha256 (the sha256 of the frozen document bytes).

-- supabase/migrations/0082_signature_evidence.sql
create table if not exists public.signature_evidence (
  id              uuid primary key default gen_random_uuid(),
  entity          text not null default 'contracts',
  entity_id       uuid not null,
  document_id     uuid references public.documents(id) on delete set null,
  digest_sha256   text not null,
  hash_alg        text not null default 'sha256',
  frozen_path     text not null,
  mime_type       text,
  size_bytes      bigint,
  signer_snapshot jsonb not null default '{}'::jsonb,
  created_at      timestamptz not null default now()
);
-- one row per (signed subject × content) — idempotent, re-hooks don't duplicate.
create unique index if not exists uq_signature_evidence_digest
  on public.signature_evidence (entity, entity_id, digest_sha256);

By design, the same entity/entity_id pair can legitimately have multiple rows as long as digest_sha256 differs. But the serving API’s query filtered on only entity and entity_id — it never looked at the third column the unique constraint actually depends on. As soon as a given contract and version accumulated more than one evidence row, .maybeSingle() hit 2+ matches, and PostgREST couldn’t collapse them to one, so it threw PGRST116 (multiple or zero rows matched). supabase-js surfaces this as an error value rather than throwing an exception, so code that only inspects data and treats its absence as “not found” folds a multi-row PGRST116 into the same 404 path as a genuine zero-row miss.

The mismatch between the constraint’s granularity (3 columns) and the query’s filter granularity (2 columns) let “multiple rows can exist” (true by design) collide with “there’s exactly one” (assumed by the read code).

The fix

Widening the filter to match all 3 constraint columns was one option, but what the serving API actually wants is “the latest evidence row for this version,” so the fix adds ordering and a row limit instead, guaranteeing a single result regardless of how many rows match.

// src/app/api/evidence/[id]/terms/route.ts (after)
const { data: ev } = await admin
  .from("signature_evidence")
  .select("frozen_path, mime_type, signer_snapshot")
  .eq("entity", termsEvidenceEntity(version))
  .eq("entity_id", id)
  // Unique constraint is (entity, entity_id, digest_sha256), so multiple rows
  // per version are allowed. maybeSingle() alone throws PGRST116 on a multi-row
  // match, turning real evidence into a permanent 404 — so pin to the latest row
  // (same convention as captureSignatureEvidence).
  .order("created_at", { ascending: false })
  .limit(1)
  .maybeSingle();

Inserting .order("created_at", { ascending: false }).limit(1) before .maybeSingle() guarantees exactly one row reaches PostgREST. Multiple rows can still exist by design, but the query now always resolves to “the newest one,” so 2+ matching rows no longer produce a 404.

Prevention

Whenever .maybeSingle() or .single() is used against a table whose unique constraint spans more than 2 columns, check whether the query’s .eq() chain covers every column in that constraint. If it doesn’t, rows differing only in the uncovered column can always slip through as a multi-row match — so either pin the result explicitly with .order() + .limit(1), or add the missing .eq() to match the constraint exactly. When adding a new table, it’s now standard practice to check that the number of columns in the unique constraint matches the number of columns any read query filters on.