Blog · July 26, 2026

reset_sandbox deletes from nineteen tables by name. Table twenty shipped in between bumps, and nothing forced anyone to add it.

reset_sandbox is the RPC every sandbox tenant test run leans on: wipe everything a tenant owns, keep the actors and API keys so the same personas can be reused, and hand back a clean slate. It isn't RLS — it's security invoker with its own guard, callable only by service_role, and it works by naming every tenant-scoped table's DELETE ... where tenant_id = p_tenant explicitly, in FK-safe order, children before parents. Its own header comment says what that buys you and what it costs: "every tenant-scoped table joins the DELETE list" is the contract, and each time a new one shipped — 0016, 0023, 0027 — someone bumped the function to add it.

The bump that didn't happen

public.invites (team invites) landed in migration 0039 — tenant-scoped, (tenant_id, id) primary key, foreign keys into actors(tenant_id, id), textbook shape for the DELETE list. reset_sandbox's last bump before that was 0027. Nothing failed. No test caught it. No lint rule connects "new tenant-scoped table" to "existing hardcoded table list in a different function." The contract just quietly stopped being true for one migration, and stayed that way for sixty-one more.

The consequence was never a tenant-isolation bug — is_sandbox and tenant_id scoping on every other statement were untouched and correct the whole time. It was narrower and easier to miss: a sandbox tenant's invite rows — pending, accepted, revoked — survived every reset. Run the same invite-flow test against the same sandbox tenant twice and the second run could trip invites_pending_email_uq, a partial unique index guarding one pending invite per email, on a row the "clean" reset was supposed to have removed. Not a security hole. A test flake with a one-line root cause, sitting quiet until someone actually looked.

Why this fire is the one that looked

reset_sandbox had never been called from supabase/tests/rls_isolation.sql — zero pgTAP coverage, one of the RPCs the standing authenticated-RPC coverage audit had already flagged and nobody had picked up yet. Writing that coverage meant reading the function closely enough to assert its actual contract: which tables get deleted, which four get kept (actors, groups, memberships, api_keys — the personas and keys are reused, not recreated), that it's cross-tenant safe, that a second reset is idempotent. Asserting "every tenant-scoped table is in the DELETE list" as a test meant re-deriving that list independently — grep every create table public.* since 0027, not trust the function body's own claim about itself. public.invites was the one table on that independent list that wasn't on the function's list. (public.inbound_emails, added later in 0064, correctly isn't on either — it's Ledgenter's own ops inbox, no tenant_id column, service-role-only by design, out of scope on purpose.)

Migration 0100 adds the one missing line and nothing else — same FK-safe position as feature_requests, deleted before actors since actors are kept:

delete from public.feature_requests  where tenant_id = p_tenant;
delete from public.invites           where tenant_id = p_tenant;  -- ADDED 0100
delete from public.activity          where tenant_id = p_tenant;

A second finding, in the test suite itself

Closing the gap needed one more pass than expected. An early version of the new coverage asserted the ACL boundary the same way tenant_delete's existing test does — throws_ok wrapping a call from a role with no rights to run the function. tenant_delete's version works because authenticated still holds EXECUTE there; the function's internal guard is what throws. reset_sandbox is stricter — EXECUTE is revoked from authenticated entirely, service_role only — so the equivalent assertion was probing a full grant revocation, not an internal check, and no other RPC in the 3,300-line suite tests an ACL boundary that way. CI's Postgres backend crashed on that exact statement, deterministically, on two independent runs.

The revoke/grant pair is already a declarative fact the migration enforces — Postgres won't let an ungranted role reach the function body at all, whether or not a test says so. That assertion wasn't adding real coverage; it was exercising a corner of throws_ok against a fully-revoked function that apparently nothing else in the suite had ever hit. Dropped it, kept the rest — the is_sandbox guard, the full DELETE-list contract including the fix, cross-tenant isolation, idempotency — and CI went green.

Two findings from one coverage pass: a maintenance contract that had silently lapsed for one migration, and a test-writing pattern that was safe everywhere else in the suite but not here. Neither shows up by reading the function and nodding along — both only showed up by trying to pin the contract down as an assertion and watching where it didn't hold.

Start at ledgenter.com.