Postgres said no

Fleeting is a time-capsule iOS app I built and shipped alone. It has no backend server: the SwiftUI client talks to Postgres on Supabase directly, and Row-Level Security decides what each user can read and write. Purchases go through RevenueCat.

At first, Pro features were enforced in exactly one place, the SwiftUI view that showed the paywall. The database had no idea who had paid. An audit caught it, and on 13 August I moved enforcement into Postgres. That same day, the code I wrote to protect paying users revoked a valid subscription.

Teaching the database who paid

RevenueCat is the only party that actually knows who is subscribed, so it tells the database through a webhook. The webhook writes to a subscribers table, and every Pro feature checks one function:

create or replace function public.is_subscriber()
returns boolean
language sql stable security definer
set search_path to 'public'
as $$
  select exists (
    select 1 from public.subscribers s
    where s.user_id = (select auth.uid())
      and s.is_active
      and (s.expires_at is null or s.expires_at > now())
  );
$$;

subscribers has no insert, update or delete policy at all. The only thing that can write to it is the webhook, running as service_role, which bypasses RLS. The fishing feature returns {"status":"locked"} unless is_subscriber() is true, and the storage policy refuses attachment uploads from non-subscribers.

The webhook needed more care than the policy did. RevenueCat retries and doesn't guarantee order, so an old event can land after a new one and bring back a stale state. Each row stores the event's timestamp, and anything not strictly newer gets dropped. CANCELLATION doesn't revoke anything either. In RevenueCat it means auto-renew was switched off, and the user has paid through the end of the period, so expires_at decides. The shared secret gets checked before the body is even parsed.

The failure mode flipped

Before, the worst case was a free user getting Pro. Now it was a paying user getting nothing. RevenueCat charges them, the webhook event gets lost or arrives while the function is down, the row never appears, and Postgres says no to someone who just paid. The webhook had become a single point of failure for the one thing you can't get wrong.

So I gave the client a way to repair itself. If the RevenueCat SDK on the phone says subscribed and the server says locked, the app calls an Edge Function called entitlement-repair. It reads the user ID from the verified login token, never from the request body (a body-supplied ID would let anyone grant Pro to any account). It asks RevenueCat's REST API directly, with a secret key the app never sees, and writes the row.

The repair that harmed

The first version wrote whatever RevenueCat answered, including "not subscribed". Its lookup was roughly this:

fetch(`https://api.revenuecat.com/v1/subscribers/${userId}`, ...)

RevenueCat app user IDs are case-sensitive. The app registers users with Purchases.logIn(uid.uuidString), and Swift's uuidString is uppercase. Supabase's auth.getUser() returns the same UUID in lowercase. So the repair function asked about a customer that didn't exist. RevenueCat's lookup endpoint quietly creates a customer if there isn't one, so the answer wasn't an error. It was a brand-new, empty customer with no entitlements.

The function took that at face value and wrote is_active = false over a valid subscription. The path I'd built to heal a dropped event had just revoked a real one.

The worst part was that the same case mismatch had already bitten me in July. Photo and voice attachments were vanishing with no error. The insert policy on capsules compared uuid = uuid, which ignores case, so it passed. The storage policy compared the folder name as text, which doesn't ignore case, so every upload into an uppercase folder was denied. The client swallowed the error and saved the capsule without its media.

Grant only

The lookup now tries the uppercase ID first, since that's what the app registers, and falls back to lowercase for accounts created any other way. But the case fix wasn't the real fix. This was:

// GRANT-ONLY. This function must never revoke.
if (!active) {
  return new Response(
    JSON.stringify({ status: "ok", is_active: false, changed: false }),
    { headers: { "Content-Type": "application/json" } },
  );
}

The repair function only runs because the server's view is already known to be incomplete, so a negative answer from it means very little. A wrong key, the wrong case or a RevenueCat blip would all look like "not subscribed". Revocation now belongs to the webhook alone, which receives the EXPIRATION and REFUND events and is the authority for them.

One more detail: the repair deliberately doesn't set event_ms. That column exists to order webhook events against each other, and a made-up timestamp from the repair path could make the next genuine webhook event look stale, so it would be dropped.

What stuck with me

Both bugs came from treating a confident answer as an authoritative one. RevenueCat didn't fail. It answered a slightly different question, about a customer my request had just created. Postgres didn't fail either. It compared two strings exactly as asked. A repair path only runs when something has already gone wrong, so the only safe thing it can do is move toward the outcome that's cheap to be wrong about. Here, that's granting.

The question I'm left with: what other APIs quietly create the thing you asked about when you read it? If one has bitten you, tell me.

References

home