If your API answers "clear" from an empty result set, your retention policy is product copy

I run a free lookup that answers one question: is this nonprofit in good standing? You type an EIN, it checks the IRS auto-revocation list and California's "may not operate or solicit" list, and tells you whether a fundraising platform has to block that org's donation page.

The table behind it, gs_orgs, only holds organizations that appear on one of those lists. Which means the API's happiest answer is produced by finding nothing. The comment at the top of route.ts said so plainly:

The gs_orgs table only holds orgs that appear on a revocation/delinquency list, so "no rows" reads as good standing on the lists we track.

That's a normal, sane design. It's also a landmine, and I stepped on it the week I tried to make the table smaller.

The pruning was correct on its own terms

The ingest pulls both registries in full and had grown to 1,206,313 rows on a Postgres instance shared across the whole estate. Most of it was dead weight: the IRS file carries revocations going back years, and an org revoked in 2013 cannot use the streamlined 15-month reinstatement path this product exists to catch. It's a full re-application, not a buyer.

So the ingest got a retention rule. The relevant part is seven lines:

keep_since = os.environ.get('GS_KEEP_REVOKED_SINCE', '2020-01-01')

def worth_keeping(r):
    if r.get('ca_reg_no') or r.get('ca_registry_status'):
        return True                      # CA-listed: AB 488 buyer at any age
    if r.get('reinstatement_date'):
        return False                     # back in good standing; "clear" is correct
    return (r.get('revocation_date') or '') >= keep_since \
        or (r.get('revocation_posting_date') or '') >= keep_since

331,377 rows survived out of 1,206,313. The table went from 261 MB to 81 MB, and the outreach outputs the product actually acts on were untouched — 716 IRS postings in the trailing 45 days, 8,219 California delinquent or suspended orgs, same as before.

Every number there says this was a good change. And it broke the API without touching a line of API code.

Deleting a row edits a sentence

Look at the two False branches and notice they are not the same kind of false.

The reinstatement_date branch drops a row because the org got its status back. Absence is accurate for that org — the answer "clear" is the answer. Fine.

The date-cutoff branch drops rows that are still revoked. Nothing about their status changed; only my willingness to store them did. But the API has no way to tell those two absences apart. It sees no row and prints the same thing.

So a 2013 revocation — still revoked, still a real problem, still something a fundraising platform is on the hook for — came back from the lookup as clean. Not as an error, not as "unknown". As a confident green panel.

The tempting fix is to unprune. That's wrong: the rows genuinely aren't worth storing, and keeping 874,936 of them to protect one sentence is an absurd trade.

The actual fix is to make the sentence smaller. The clear-state copy used to read:

No IRS auto-revocation and no California delinquency on record for that EIN.

It now reads:

No IRS auto-revocation posted since 2020, and no California delinquency on record for that EIN.

The second version is a claim the data can still support. The first one wasn't true even before the pruning — it just happened to be less wrong.

Keeping them tied together

The failure mode from here is drift: someone widens GS_KEEP_REVOKED_SINCE to 2015, or narrows it to 2022, and the copy stays frozen at "since 2020". Two files, one invariant, no compiler between them.

There's no clever mechanism for this — you can't type-check a sentence against an environment variable. What exists instead is a warning at both ends. The ingest carries a ⛔ block above keep_since saying the cutoff is load-bearing for /api/lookup and that widening it means widening the copy. route.ts carries the mirror image, naming the env var and the component that renders the claim. Whichever file you open first, it points at the other two.

That's a weaker guarantee than a test, and I'd take a test if one were possible. What makes the comments work is that they're specific enough to act on: not "careful here", but the variable name, the file name, and the exact consequence.

The general shape

Any table that stores only exceptions has this property. Blocklists, incident tables, audit-failure tables, sanctions screening, fraud flags — the whole design is "presence means bad, absence means fine," and the moment absence carries meaning, your retention window becomes part of your product's claim. Compaction, TTLs, partition drops, a cheeky DELETE FROM ... WHERE created_at < ... to reclaim disk: all of them silently rewrite what your API is asserting to a user.

Before you prune one of those tables, go find the string that renders when the query comes back empty. That string is a spec. Either the deletion has to respect it, or it has to change with it.

That's how we built the free EIN lookup in GoodStanding — try it on an org you care about, and read the caveat, because it means exactly what it says.

Comments

[Mads Hansen]

The strongest version of this fix may be to stop embedding the retention boundary only in copy.

Have the lookup return a typed result such as status: no_match_in_covered_window, coverage_start: 2020-01-01, source_as_of, and sources_checked. The UI can render “No IRS auto-revocation posted since {coverage_start}” from that response. Now changing the ingest cutoff changes the claim automatically, and API consumers cannot accidentally collapse “no matching row” into timeless “clear.”

I’d also keep unknown separate for stale or failed source refreshes. An empty exception table, a successfully checked window with no match, and an unavailable registry are three different epistemic states even if all three currently produce zero rows.

That turns retention from a comment-linked invariant into part of the response contract and makes the caveat testable end to end.

[Cenk KURTOĞLU]

"Presence means bad, absence means fine" is the right frame, and I would add a third source of absence that neither the post nor the typed-response fix above covers, because it is the one that produces no artifact at all.

A row-level security policy manufactures absence that is indistinguishable from a miss.

When RLS filters a row, Postgres does not raise. It returns zero rows, exactly like a query that genuinely matched nothing. So for an exceptions table you now have three states collapsing into one:

1. No such org — "clear" is accurate
2. Pruned by retention — the bug in the post
3. The row is there, and the policy removed it for this caller

Three is the one worth worrying about in this shape, because the trigger is a config change rather than a code change. gs_orgs goes through PostgREST or Supabase, someone enables RLS to tick a security box, and no policy is written for the role the API connects as. Postgres defaults to deny. Every lookup returns zero rows. Every lookup renders the green panel.

That failure is worse than the retention one in two ways. It is total rather than partial — every EIN comes back clean, not just pre-2020 ones. And it has no seam: with pruning you can at least go count what you deleted, whereas here the table is full, the query is correct, the connection works, and the answer is confidently wrong.

Mads's typed response helps with 1 and 2 because the API knows its own coverage window. It cannot help with 3, because the API does not know it was filtered — from the client's side a policy denial and an empty match are the same HTTP 200 with an empty array.

The check that separates them has to run against the catalog, not the result set:

select c.relname, c.relrowsecurity as rls_on, count(p.polname) as policies
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_policy p on p.polrelid = c.oid
where n.nspname = 'public' and c.relkind = 'r'
group by 1,2 order by 2,1;

rls_on = true with policies = 0 on an exceptions table is a lookup that answers "clear" to everything.

The cheap invariant, in the spirit of your load-bearing comments: have the ingest assert a known-bad EIN still resolves after every run, using the same role and connection string the API uses. One row, one assertion. It catches the retention drift you wrote about, and it is the only thing that catches a policy change, because it is the only check that exercises the actual read path rather than reasoning about it.

Same class of silence from the other direction, if useful — a policy that exists, tests green, and still hands back the wrong rows: github.com/cekuu35/supabase-rls-le...