DEV Community: Veristria

Auditing a Supabase project in one afternoon

Veristria — Wed, 09 Sep 2026 16:38:52 +0000

Auditing a Supabase project in one afternoon

You don't need tooling to know where your Supabase security stands - you need four hours and the right questions in the right order. This is the complete manual audit: every catalog query, how to read each result, and the outside probes that turn findings into proof.

RowShield exists because this audit is worth automating and monitoring. But the manual version matters for different reasons: it teaches you what the automation watches, it works today with zero setup, and when a scan does flag something, understanding the query behind the finding means understanding the fix. This article is the audit we run by hand — scheduled as an afternoon, structured so each hour's output feeds the next.

Everything below reads or probes your own project using your own access. Every SQL statement runs against current Postgres; all were executed during this article's preparation. By the end you'll have a findings document with evidence attached — not a vibe about your security, a list with proof per line, sorted by what deserves fixing first.

The afternoon plan

Time	Work	Output
Hour 1	Catalog inventory: tables, policies, grants, functions, views, buckets	Protection inventory
Hour 2	Read the inventory: shapes, unions, gaps	Findings list (structural)
Hour 3	Outside probes: anon surface + two-account battery	Findings list (behavioral)
Hour 4	Storage, functions, keys; write up everything	Prioritized remediation worklist

Bring to the session: your project's SQL editor access, the public anon key, two test accounts if the app has authentication (create them through signup if not), and a document open for findings. Nothing else — no special tooling, no credentials beyond what you already hold.

The order matters more than the clock. Inventory before reading prevents anchoring on whatever the app's UI shows; structural reading before probing tells you which probes matter most; probes last because they confirm rather than explore. Teams that start with probing often stop at the first scary result and never learn what else the catalog was trying to tell them.

A word on scope before starting: this audit covers authorization posture — what's reachable, by whom, under what rules — rather than code vulnerabilities, dependency patching, or infrastructure hardening. Those belong to other checklists. The authorization layer earns its own audit because it changes on every deploy and fails in ways nothing else surfaces; treat this afternoon as the recurring core, with other security reviews layered around it at their own cadences.

Hour one: inventory the protection surface

Open the SQL editor and run five queries. First, tables and their RLS flags:

select c.relname,
       c.relrowsecurity as rls_enabled,
       c.relforcerowsecurity as force_rls
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'r'
order by c.relname;

Second, every policy in full:

select tablename, policyname, permissive, roles, cmd, qual, with_check
from pg_policies
where schemaname = 'public'
order by tablename, cmd, policyname;

Third, grants — which client-facing roles can do what:

select grantee, table_name, privilege_type
from information_schema.role_table_grants
where grantee in ('anon', 'authenticated') and table_schema = 'public'
order by grantee, table_name, privilege_type;

Fourth, privilege-elevating functions:

select p.proname, p.proconfig
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public' and p.prosecdef;

Fifth, views and their invocation semantics:

select c.relname, c.reloptions
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'v';

If the project uses Storage, add its buckets and object policies to the pile (storage.buckets, then pg_policies filtered to schemaname = 'storage'). Export all of it — copy to a document, literally. Hour two reads from this artifact, and hour four's write-up cites it as evidence.

To make the reading concrete, here is the first query's output from the fabricated example project used across RowShield articles, annotated the way you should annotate your own:

      relname      | rls_enabled | force_rls
-------------------+-------------+-----------
 documents         | t           | f
 notifications     | f           | f      <-- FINDING: open table
 workspace_members | t           | f
 workspaces        | t           | f

Every column earns its keep. rls_enabled = false means the API serves that table to anyone holding your public key — no policy discussion needed, exposure exists today. force_rls = false is normal (owner access for migrations) but worth knowing per table: it names which tables would still bypass policies if someone connects as the owner. Annotate every anomaly inline as you go; memory doesn't survive to hour two.

Hour two: read what you found

The inventory answers six questions, each mapped to a verdict:

Question	Where to look	Bad answer looks like
Which tables are open?	`rls_enabled` column	Any `false` on a non-public-by-design table
Which writes lack constraints?	`with_check` column	NULL under INSERT/UPDATE/ALL policies
Which tables carry tautologies?	`qual` column	Constant `true` expressions
Where do unions widen?	Same table+cmd grouping	Three-plus PERMISSIVE policies per command
What bypasses row security?	Function list	Definer functions without pinned search_path
What elevates indirectly?	Views' reloptions	Views missing `security_invoker = true`

Reading technique for the policy dump: group rows mentally by (tablename, cmd) and read each group as one OR expression, since permissive policies combine by union. A group whose members disagree about scope isn't contradictory — it's as wide as its widest member. Note any identity comparison that references something other than (select auth.uid()): request parameters and client-shaped columns don't count as verified identity.

A worked mini-read shows the method. Suppose some project's dump contains, for one table:

policyname            | permissive | cmd    | qual                          | with_check
----------------------+------------+--------+-------------------------------+-----------
invoices_select_owner | PERMISSIVE | SELECT | uid() = owner_id              |
invoices_select_any   | PERMISSIVE | SELECT | true                          |
invoices_insert_own   | PERMISSIVE | INSERT |                               | false

Three lines, three verdicts. The any tautology makes the owner policy redundant for reads — every authenticated user matches every row already, so the careful clause contributes nothing (and its presence may mislead reviewers into thinking reads are scoped). The insert policy's check of literal false means inserts always fail — likely a debugging leftover that broke a feature someone "fixed" elsewhere, worth investigating before deleting. Two findings from three rows, both with evidence attached, neither requiring any judgment beyond the reading rules above.

Mark each finding high/medium/low as you go — high for anything exposing data now (open tables, unconstrained writes), medium for structure that will misbehave under pressure (tautologies, unhygienic definer functions), low for hygiene (naming, comments, bare auth calls). Severity triage during reading beats severity debate later.

Also record the absence findings while reading: tables with zero policies despite being enabled, buckets with no policies at all, views nobody can explain. Absence findings age differently from defect findings — they're often intentional interim states that quietly became permanent — and they're precisely what a fresh reader catches that the schema's author no longer sees.

Hour three: probe from outside

Structural findings predict exposure; probes prove it. Two batteries, both framed entirely within your own project.

The anonymous sweep. With only your public key, GET every exposed table:

curl -s "https://YOUR-PROJECT.supabase.co/rest/v1/TABLE_NAME" \
  -H "apikey: $ANON_KEY"

Empty array: pass. Rows: a finding, already half-documented from hour two. Also try one insert attempt per sensitive table expecting rejection — anonymous writes are rarer than reads but strictly worse. If your model includes deliberate public reads (published listings and the like), verify their filters instead: draft rows must stay absent, internal columns must stay out of responses.

Interpreting responses is mechanical once you've seen each shape:

Response	Verdict
`[]`	Protected — or genuinely empty; note which and confirm later
Rows returned	Open window, live now; capture the response as evidence
404 / "relation not found" error	Table not API-exposed; fine for private tables, check why if public
401/403 on an attempted anonymous insert	Pass — write path rejects unauthenticated callers

Two practical notes. First, keep requests modest — a handful of rows per table proves exposure without bulk-downloading anything, which matters both ethically and for your own logs. Second, run probes against production, not staging: staging answers whether your pipeline produces protection, production answers whether protection currently exists, and they differ more often than teams expect.

The two-account battery. With test accounts Alice and Bob, run the five adversarial probes — anonymous read, forged write, cross-tenant read, ownership transfer, silent delete — exactly as specified in the tenant-isolation playbook. Each probe has a binary expected outcome; record actual outcomes beside expectations. Where hour two predicted defects, these probes supply behavioral proof; where probes fail unexpectedly, hour two's inventory explains why. The two halves of the audit corroborate each other, and disagreements between them are themselves findings — they mean something mediates access outside the obvious path.

Budget roughly twenty minutes per sensitive table for the full battery, less once practiced. Prioritize tables by what a breach of them means — user credentials and personal data outrank configuration tables — so if the afternoon compresses, the most important tables already ran their probes. The battery is also the audit's most transferable artifact: the same five requests run against any future project, unchanged.

Hour four: storage, functions, keys

Three remaining surfaces, each with its own quick check.

Buckets: list them with visibility flags. Public buckets get reviewed as published content — is everything inside genuinely meant to be URL-accessible? Private buckets get their own policy review, path-scoping patterns especially (the own-folder pattern and its neighbors). Check for the orphan case too: buckets with no policies at all, which behave as write-voids — safe from anonymous access but unusable by legitimate users, usually signaling an abandoned feature.

Functions: for each definer function from hour one, call it as a restricted user and compare results to what that user could derive through policies alone. Pin search_path wherever missing. Confirm execute privileges match intent.

Keys: grep built assets and environment files for service-grade material, then fetch your deployed site's JavaScript and search it too:

grep -r "sb_secret_" dist/ .next/static/ build/ 2>/dev/null
grep -ri "service_role" .env* --include="*" 2>/dev/null

Key material anywhere browser-reachable converts this afternoon into an incident-response morning — rotate first, investigate after (rotation guidance).

Then spend the last thirty minutes writing. Per finding: what's exposed, catalog or probe evidence, proposed fix as concrete SQL, affected features for QA awareness. Sort high-to-low. That document is the audit's deliverable — and next quarter's baseline. It is also the step most easily dropped once the scary finding is fixed, which is how a repeatable program degrades back into an ad-hoc scramble with nothing to compare against.

Where manual ends and monitoring begins

An afternoon audit is a snapshot; snapshots age. Migrations ship weekly, dashboards invite hotfixes, restores replace state silently — every channel that makes audits necessary also makes them stale. Re-running this whole sequence monthly is realistic; after every deploy is not, which is precisely the gap automation fills.

The mapping between this article's steps and continuous coverage is direct: hour one's inventory becomes CI assertions; hour three's probes become scheduled external scans; hour four's key checks become build-time scanning. RowShield automates exactly those translations — the free scan covers the outside-probe layer immediately, catalog monitoring extends it — while the judgment calls (is this public bucket intentional? should editors see drafts?) remain yours regardless of tooling.

Run this audit manually once and you'll understand every automated finding forever after. That's not a consolation prize for lacking tooling — it's the reason the tooling's findings deserve trust. And when the next audit comes around, compare against the last one's document: deltas between snapshots are the drift narrative in miniature, each line either a deliberate change you can name or a finding that arrived uninvited.

Common questions

Is one afternoon really enough?

For small-to-medium projects — say, up to forty tables — yes, comfortably: most time goes to reading and writing up, not querying. Larger schemas split naturally across days by schema area, with the same per-area flow. What doesn't fit in an afternoon is fixing; this produces the prioritized list, and fixes schedule from there.

Do I need production access, or is staging enough?

Both, ideally — but production is where truth lives. Staging validates upcoming changes; production holds the accumulated drift of every manual edit and restore since. If forced to choose one, audit production first; staging diverges from it in ways that matter less than the reverse.

What if I find something alarming mid-afternoon?

Handle by class. Exposure-that-is-happening-now (open tables, leaked keys): pause the audit, contain, resume — the containment patterns are short and this document's earlier sections link them. Everything else waits for the write-up; alarming-but-contained findings lose nothing from a day's delay in fixing.

How do I audit a project I can't run locally?

Everything in hours one and two needs only SQL editor access to the live project — read-only catalog queries, safe on production. Hour three's probes hit public endpoints by design. Only fixes require the usual deploy discipline; an audit itself never modifies state, which is worth stating explicitly when requesting access to someone else's project.

Should the audit include checking my auth configuration too?

Yes as a fifth quarter-hour: confirm email confirmation requirements, redirect allowlists, and whether unused OAuth providers are disabled. This article scopes to the database and surfaces because that's RowShield's home turf, but an afternoon audit that touches keys might as well confirm the identity layer's basic posture — most of it is reading settings screens.

Prefer the automated starting point? Run the free scan — paste your app URL for the outside-probe layer of this audit instantly, then bring the results to your manual session.

RowShield is an independent product and is not affiliated with, endorsed by, or sponsored by Supabase, Inc.

Supabase RLS: A 200 Response Does Not Prove Data Exposure

Veristria — Tue, 08 Sep 2026 01:55:01 +0000

A public table endpoint can return HTTP 200 with an empty JSON array. That proves reachability, not data exposure.

The useful question is whether the policies return the right rows for each identity.

A small verification matrix

Test the same read-only query as:

an anonymous visitor
the row owner
an authenticated non-owner
a service role, kept strictly server-side

For every path, record the status, row count, and whether returned records belong to the expected tenant. Repeat the matrix after each migration.

What the result means

An empty anonymous result can be exactly correct. A non-owner receiving another tenant's row is evidence of a policy problem. Treat those as different findings.

Do not use destructive test queries against production. Start with read-only checks in a controlled environment and document what was tested.

You can run a free, read-only RLS check at https://rowshield.dev/audit

The check is evidence, not a guarantee: application logic, privileged server paths, and changes made after the test still need review.

RLS policies do not fail. They drift.

Veristria — Tue, 01 Sep 2026 18:56:17 +0000

RLS policies do not fail. They drift.

Row-level security has an unusual failure mode. A policy does not break in the way code breaks. It does not throw, it does not fail a test, it does not appear in an error log. It keeps running exactly as written, and at some point what it was written for stops being what it does.

The schema moved. The policy did not.

The shape of the drift

Here is a policy that is completely correct on the day it is written.

create policy "own_documents"
on documents for select
using (owner_id = auth.uid());

Every row in documents has an owner. A user reads their own. Nothing else. Correct.

Three months later the product grows a sharing feature. A document_collaborators table appears, and a new query joins it. The policy above is still there, still parses, still runs — and now the team writes a second policy to cover the new access path, because the first one does not.

Two months after that, someone adds an organisation_id column to documents and a dashboard that lists documents by organisation. The dashboard returns nothing, because own_documents filters by owner_id. So a third policy is added for organisation members.

Nothing here is negligent. Every step was a reasonable response to a real requirement. But documents now has three overlapping select policies, and Postgres combines multiple permissive policies with OR. The effective access rule is the union of all three — which is not what any of them says on its own, and is not something anybody has written down.

That is drift. Not a broken policy: a set of individually correct policies whose combination nobody has evaluated.

The four ways it happens

In practice, almost all of it comes from four events.

A new table ships with no policy at all. If RLS is not enabled on a table, the anon key reads it. This is the loudest version of the problem and still the most common, because enabling RLS is a separate step from creating the table, and a table created by a migration written at speed does not always get both.

A column widens what an existing policy exposes. The policy filters rows, not columns. Adding a column to a table that is already readable makes that column readable too — including the one holding an email address, an internal note or a partially masked identifier.

A new access path routes around the policy. A view, an RPC or a security-definer function that queries the table on the caller's behalf. Postgres views do not inherit the underlying table's RLS unless they are explicitly set up to; a security-definer function bypasses it by design. Both are legitimate tools, and both are ways for a row to arrive somewhere the policy never approved.

Policies accumulate. As above. Each addition is safe in isolation and the union is never reviewed.

Why reading the migrations does not catch it

The instinct is to review this statically: read the policy files, reason about the schema, check the logic. It does not work well, for the same reason reading code is a weak substitute for running it.

A policy's behaviour depends on the current schema, the current set of other policies on the same table, the current role, and the current contents of auth.uid(). Reasoning about the union of three policies against a schema that has changed twice since they were written is exactly the kind of thing humans get wrong — and it has to be redone every time any of those inputs changes.

The alternative is to ask the database. Not "what does this policy say", but "as this role, with this token, can I read this row". That question has a definite answer, it is cheap to ask, and it is correct by construction, because it is the same code path a real request takes.

What to check, in order

If you want to do this by hand today, this is the order that finds the most in the least time.

Every table with RLS disabled. Query pg_tables for rowsecurity = false in your public schema. Each one is readable by anyone holding the anon key, which is in your frontend bundle.
Every table with RLS enabled and no policies. This is the reverse failure: RLS on, no policy, everything denied. Usually it produces a bug report rather than a breach, but it tells you the table was set up in a hurry.
Tables with more than one permissive policy for the same operation. These are the union cases. Write down what the combination actually permits.
Views and security-definer functions that touch protected tables. Check each one for whether it is enforcing RLS or bypassing it.
Then test it. For each protected table, hold an anon token and a token for a user who should not have access, and try to read a row. The result is the only answer that counts.

The point

RLS is a good mechanism. It is enforced in the right place, it is hard to bypass by accident from application code, and it survives a careless client.

What it does not do is notice when the world around it changes. The policy you wrote in April is still doing precisely what you asked it to in April. Whether that is still the right thing is a question somebody has to keep asking — and the honest way to ask it is to run the query and look at what comes back.

Run a free audit

RowShield is an independent product with no affiliation to, or endorsement from, Supabase.

How to audit 90 days of platform refunds by hand

Veristria — Tue, 01 Sep 2026 13:50:02 +0000

How to audit 90 days of platform refunds by hand

How much money did the last 90 days of refunds fail to return — to your platform, or to the sellers on it? This guide walks through the complete manual answer: the API queries, the spreadsheet, the two formulas, and a disputes pass. Everything runs on a restricted read-only key. No product is required at any step.

Scope and inputs

The audit asks two questions of every refund issued in the trailing 90 days. Did the transfer get reversed when your refund policy says it should have been? And did the application fee come back when policy says it should? Two formulas answer both, and the audit's output is one number per gap class, in cents, ready to sum.

Anchor the window at run time. Ninety days is 7,776,000 seconds, so the lower bound in Unix seconds is now − 7,776,000. Treat the window as half-open — everything with created >= start and created < end — so reruns on consecutive days never double-count a boundary refund.

Two extraction routes exist. The Dashboard route exports payments filtered to refunded status and joins everything in a spreadsheet. The API route lists refunds directly. Prefer the API route: the joins this audit needs run on object IDs — transfer, application_fee, reversal records — and exports make those joins painful in ways the API makes trivial.

Access needs less privilege than the task sounds like it should. Stripe supports restricted API keys scoped per resource, with read or write access chosen per resource, so a read-only key over the relevant families audits your money while holding no money-movement rights at all (keys). Never paste a live secret key into a script; read it from an environment variable even for a one-off.

Five object families feed the audit; the table shows what each contributes and the minimum access it needs.

Object family	What it contributes	Minimum access
Refunds (`/v1/refunds`)	The refund rows themselves: amount, currency, dates	read
Charges (`/v1/charges`)	Parent amount, `transfer` ID, `application_fee` ID	read
Transfers and their reversals	What was pulled back from sellers, and when	read
Application fees and their refunds	Whether the commission came back	read
Disputes	Lost cases, which need their own pass below	read

If you want this same procedure formatted as a step-by-step companion, the 90-day lookback walkthrough follows the identical order: extract, transform, score, sum.

Extract: the queries

Start by listing refunds created inside the window. The created filter takes Unix seconds:

curl "https://api.stripe.com/v1/refunds?created[gte]=1771977600&created[lt]=1779753600&limit=100" \
  -u "$STRIPE_SECRET_KEY:"

The example bounds cover 2026-02-25 through 2026-05-26 UTC — exactly 90 days, half-open. Swap in your own dates. limit=100 is the page size; walk the remaining pages with the follow-up cursor automatically, as documented under auto-pagination. In TypeScript with the official SDK, the paginator does that walking for you, and expanding the parent charge inline saves one request per refund:

import Stripe from "stripe";

const key = process.env.STRIPE_SECRET_KEY ?? "";
if (key === "") throw new Error("STRIPE_SECRET_KEY is not set");
const stripe = new Stripe(key);

const windowStart = Math.floor(Date.now() / 1000) - 90 * 24 * 60 * 60;

type Row = {
  refund_id: string;
  charge_id: string;
  charge_amount: number;
  amount_refunded: number;
  currency: string;
  transfer_id: string | null;
  application_fee_id: string | null;
};

const rows: Row[] = [];

for await (const refund of stripe.refunds.list({
  created: { gte: windowStart },
  expand: ["data.charge"],
  limit: 100,
})) {
  const charge = refund.charge as Stripe.Charge;
  rows.push({
    refund_id: refund.id,
    charge_id: charge.id,
    charge_amount: charge.amount,
    amount_refunded: refund.amount,
    currency: refund.currency,
    transfer_id:
      typeof charge.transfer === "string" ? charge.transfer : null,
    application_fee_id:
      typeof charge.application_fee === "string"
        ? charge.application_fee
        : null,
  });
}

console.log(rows.length + " refunds pulled");

Each refund reaches its parents through the expanded charge: the charge carries amount, the transfer ID on destination charges, and the application_fee ID wherever fees were collected. On a direct-charge fleet, none of those IDs sit on the platform side — the objects live on each connected account, so query account by account by passing the account in request options:

const perAccount = await stripe.refunds.list(
  { created: { gte: windowStart }, limit: 100 },
  { stripeAccount: "acct_PLACEHOLDER" },
);

Loop that over your connected accounts and merge the rows; the rest of the audit is identical from there. Key the merge on charge ID plus refund ID, since both survive the trip across account boundaries unchanged.

With rows in hand, enrich each one: fetch the transfer with its reversals expanded, fetch the application fee with its refunds expanded, and compute the two scores the next sections derive:

for (const row of rows) {
  if (row.transfer_id === null || row.application_fee_id === null) continue;

  const transfer = await stripe.transfers.retrieve(row.transfer_id, {
    expand: ["reversals"],
  });
  const reversalsSum = transfer.reversals.data.reduce(
    (sum, r) => sum + r.amount,
    0,
  );
  const expectedReversal = Math.round(
    (row.amount_refunded / row.charge_amount) * transfer.amount,
  );

  const fee = await stripe.applicationFees.retrieve(row.application_fee_id, {
    expand: ["refunds"],
  });
  const feeRefundedSum = fee.refunds.data.reduce(
    (sum, r) => sum + r.amount,
    0,
  );
  const expectedFeeRefund = Math.round(
    (row.amount_refunded / row.charge_amount) * fee.amount,
  );

  console.log(row.refund_id, expectedReversal - reversalsSum, expectedFeeRefund - feeRefundedSum);
}

The same logic translates directly to other languages; if your team maintains Python tooling, the Python reconciliation scripts page shows the equivalent requests end to end.

Transform: build the sheet

One row per refund. The input columns come straight off the objects; the table maps each column to its source.

Column	Pulled from
`refund_id`, `currency`, `refund_created`	Refund
`charge_id`, `charge_created`	Parent charge
`charge_amount_cents`	`charge.amount`
`amount_refunded_cents`	`refund.amount`
`transfer_amount_cents`	`transfer.amount`
`reversals_sum_cents`	Sum of the transfer's reversal amounts
`app_fee_amount_cents`	`application_fee.amount`
`fee_refunded_sum_cents`	Sum of the application fee's refund amounts

Four more columns are computed, not fetched; the table defines each rule, and the next section grounds the two that matter.

Computed column	Rule
`expected_reversal_cents`	`round(amount_refunded_cents / charge_amount_cents * transfer_amount_cents)`
`missing_transfer_cents`	`expected_reversal_cents - reversals_sum_cents`
`expected_fee_cents`	`round(amount_refunded_cents / charge_amount_cents * app_fee_amount_cents)`
`missing_fee_cents`	`expected_fee_cents - fee_refunded_sum_cents`, scored only where refund policy returns fees

Three housekeeping rules keep the sheet honest. Keep one sheet per currency and never sum cents across currencies. Store every amount as an integer in the smallest currency unit and convert to dollars only when printing. And format ID columns as text before pasting, because spreadsheets quietly mangle long alphanumeric tokens into something that no longer joins. Charges refunded in several installments simply occupy several rows; the formulas score each independently, and the cumulative check in the next section catches any drift between them.

The two formulas

Everything reduces to proportionality. Write the rules once, in cents:

expected_reversal = round((amount_refunded / charge_amount) * transfer_amount)
missing_transfer  = expected_reversal - reversals_sum
expected_fee      = round((amount_refunded / charge_amount) * app_fee_amount)
missing_fee       = expected_fee - fee_refunded_sum     (scored only if policy returns the fee)

The proportionality is not a modeling choice — it mirrors Stripe's own behavior. Full refunds of destination charges reverse the entire transfer when reverse_transfer=true, and partial refunds reverse a proportional amount (destination charges). The same parameter is documented the same way at the API level: the transfer is reversed proportionally to the amount being refunded (refunds create). Application fees behave symmetrically: refunded in full on a full refund, proportionally on a partial one, when refund_application_fee is set.

Separate charges and transfers break the symmetry, and the formulas expose it: refunding the charge has no impact on any associated transfers, so reversals_sum_cents stays zero no matter how diligent you were at refund time (separate charges and transfers). Those rows score against whatever reversal you performed manually — often none.

One caveat on rounding. When several partial refunds hit one charge, each row rounds independently, and independent rounding can drift a cent or two from the cumulative truth. Score per charge, cumulatively, before believing any single row: if the sum of amount_refunded equals the charge amount, the expected cumulative reversal is exactly the whole transfer; between boundaries, accept dust of a cent or less per charge and investigate anything larger.

Worked rows

Three synthetic rows exercise every branch of the sheet. Assumptions, labeled as assumptions:

US platform, USD, Stripe's standard US card pricing of 2.9% + $0.30 (published pricing).
Charge of $100.00 = 10000¢, application fee $10.00 = 1000¢, Stripe processing fee $3.20 = 320¢.
Destination-charge transfer $90.00 = 9000¢; separate-charge transfer $70.00 = 7000¢.
Row B is a 40% partial refund issued with both reverse_transfer=true and refund_application_fee=true.

All amounts in cents. The table shows inputs left of expected_reversal and scores right of it.

Row	Path	charge_amount	amount_refunded	transfer_amount	Σ reversals	Σ fee refunds	expected_reversal	missing_transfer	missing_fee
A	Destination, full refund, no flags	10000	10000	9000	0	0	9000	9000	1000
B	Destination, 40% refund, both flags	10000	4000	9000	3600	400	3600	0	0
C	Separate, full refund	10000	10000	7000	0	—	7000	7000	—
SUM								16000	1000

The arithmetic, line by line:

Row A: round(10000 / 10000 * 9000) = 9000, so missing = 9000 − 0 = 9000, which is $90.00 of transfer that stayed with the seller. The fee: round(1.0 * 1000) = 1000 against 0 refunded, so $10.00 kept — the default on destination charges unless the platform acts.
Row B: round(4000 / 10000 * 9000) = round(3600) = 3600, so missing = 3600 − 3600 = 0. The fee: round(4000 / 10000 * 1000) = 400 against 400 refunded, also 0. A correctly flagged partial refund scores clean.
Row C: round(10000 / 10000 * 7000) = 7000, so missing = 7000 − 0 = $70.00 — the full transfer, outstanding until someone reverses it manually, which itself succeeds only if the seller's available balance covers it (separate charges and transfers).

Totals: 9000 + 0 + 7000 = 16000¢ = $160.00 of unreversed transfer value, plus 1000¢ = $10.00 of retained fee, across three toy refunds. On real data these columns are sums, and the SUM row is the number that goes in front of decision makers. Keep the refund-path audit checklist beside the sheet so each row's path classification stays consistent.

The disputes pass

Refunds are half the exposure. Lost disputes debit the platform on destination and separate charges — disputed amount plus dispute fee — and recovering from the seller is a manual transfer reversal, exactly like a refund that was issued without its flags (Connect disputes). So the audit repeats itself over disputes closed as lost inside the window:

for await (const dispute of stripe.disputes.list({
  created: { gte: windowStart },
  expand: ["data.charge", "data.charge.transfer"],
})) {
  if (dispute.status === "lost") {
    const charge = dispute.charge as Stripe.Charge;
    console.log(dispute.id, dispute.amount, charge.id, charge.transfer ?? null);
  }
}

The charge.dispute.closed explainer covers the event side if you would rather catch these as they happen. For each lost dispute, ask the sheet the same question: was the transfer reversed around the loss?

One worked row, assumptions labeled: destination charge of $100.00, transfer $90.00, dispute fee $15.00 at standard US pricing (published pricing), no reversal ever made.

Dispute	disputed	fee	transfer_amount	Σ reversals	uncovered_seller_side
dp_1	10000	1500	9000	0	9000

Arithmetic: uncovered = 9000 − 0 = 9000, so $90.00 could still be reclaimed from the seller, while the $15.00 fee has no reversal to ride on and is gone regardless.

Fold the pass into the total: 16000 + 9000 = 25000¢ of transfer-class gaps, plus the 1000¢ fee-class gap — $260.00 identified across five synthetic rows, every cent of it traced to a specific object ID.

What you will find

Expect shape, not scatter. The findings below are qualitative patterns this audit reliably surfaces; your sheet supplies the magnitudes.

Zero-reversal clusters concentrate. Sort missing_transfer_cents by the code path or dashboard origin that issued each refund and specific branches dominate — the places where refund calls went out without reverse_transfer, or fee refunds were never chained. Defaults leak where they were coded in.
Age destroys recoverability. Recent gaps can often still be reclaimed. Old ones frequently cannot, in place: the seller's balance was paid out long ago, so the money exists only as a claim, not a balance. Those rows migrate from "reverse it" to "net it or negotiate it."
Rounding dust accumulates. Rows scoring ±1¢ from multi-partial charges are noise; let them cancel rather than chasing them.
Pending stragglers linger. Refunds sitting in pending on underfunded connected accounts look like leaks in the raw data but have simply not executed yet; park them on a separate tab.
Separate-charge orphans surface. Transfers nobody associated with the refunds they survived, because nothing in the object graph connects them — only your records do.

The distribution is the diagnostic. Uniform spread suggests nothing actionable; concentration points at a fixable call site.

Why one pass is not enough

A snapshot ages instantly. Tomorrow's refunds repeat today's pattern, so a quarterly habit means quarters of accumulation between passes, and the oldest rows rot from "recoverable" to "write-off" while they sit. If the SUM row justifies acting at all, it eventually justifies running the identical queries continuously — the two formulas do not change week to week; only the data does. Remediation of what you find has its own procedures, from the bulk reversal of historical findings playbook onward, and continuous versions of this same arithmetic are precisely what ongoing monitoring automates, FeeGuard included.

Frequently asked questions

Does this audit need write access to my Stripe account?

No. Restricted keys scope access per resource with read or write chosen separately, so read-only over refunds, charges, transfers, application fees, and disputes covers the entire procedure (keys). The key never moves money, and it should still live in an environment variable rather than in the script.

Can I do all of this from Dashboard CSV exports?

Partially. The payments export gives you refunds and their charges, but reversals and application-fee refunds live in separate exports, and joining them means manual key matching in a spreadsheet. It works; it is slower and frailer. The Excel-export recovery workflow walks the export-based variant if you cannot use the API.

We settle in multiple currencies — what changes?

Only discipline, not method. Run one sheet per currency, convert nothing until the final report, and remember that in zero-decimal currencies like JPY the integer amounts are already the major unit, so "cents" columns become "yen" columns (supported currencies). Never sum a USD column onto a EUR column.

What about refunds still showing as `pending`?

Score them, but on their own tab. A pending refund has not moved money yet — on direct charges it waits for the connected account's balance to fund it, then processes automatically (connect charges). Recheck them before you publish totals, because some will resolve themselves and some will reveal a seller whose balance never recovers.

Run the 90-day audit

Nothing above requires anything but a spreadsheet and an afternoon, and rerunning it weekly is exactly as tedious as it sounds. FeeGuard exists because this arithmetic runs silently on every refund your platform issues, and the running total almost never appears anywhere. The free audit reads your last 90 days of Connect activity through a restricted, read-only API key and reports every unreversed transfer, unreturned application fee, and uncovered dispute loss with the amounts attached. You get the answer first; ongoing monitoring stays optional afterward.

Run the free 90-day audit.

FeeGuard is an independent product and is not affiliated with, endorsed by, or sponsored by Stripe, Inc.

Build your own fee-leakage detection with the Stripe API

Veristria — Mon, 31 Aug 2026 13:30:15 +0000

Build your own fee-leakage detection with the Stripe API

You can detect Connect fee leakage yourself: list refunds and disputes, join them to charges, transfers, reversals, and application fees, apply two expectation formulas, and alert on the difference. This piece gives platform engineers the architecture, the exact API calls, and working code for continuous detection you own end to end.

Architecture in one diagram

Five components, one data flow, no money movement anywhere in the pipeline:

lister jobs ──▶ normalizer ──▶ expectation engine ──▶ findings store ──▶ alerter
     │             │                │                    │               │
     └─────────────┴───── read-only restricted key throughout ─────────┘

Lister jobs pull new refunds and disputes over the API since the last cursor. The normalizer flattens them into rows carrying everything the math needs: parent charge, transfer amount, reversals already made, application fee and its refunded portion. The expectation engine applies two formulas — proportional reversal, proportional fee refund — and emits a finding whenever actual falls short of expected. The findings store persists diffs with identifiers and ages. The alerter turns stored findings into human-visible output.

Every component reads through the same restricted key. Stripe supports restricted API keys scoped per resource with read or write access (API keys), and an auditor process has no business holding write access at all — more on that next. If you eventually compare this stack against alternatives, the honest framing lives at homegrown scripts versus managed detection; here we build.

Scoping credentials safely

Create a restricted key in the Dashboard and grant read access to exactly six resources: Charges, Refunds, Transfers, Application Fees, Disputes, and Balance. Nothing else, and write access nowhere.

The reasoning is mechanical. This system's entire job is noticing that money went somewhere it should not have; a key that cannot move money cannot make any of its own failure modes into incidents. A leaked secret key drains balances; a leaked restricted read-only key leaks data — serious, but bounded, and revocable in one click. Auditors and security reviewers also get a cleaner story: the credential proves the pipeline is incapable of self-help, so every recovery decision necessarily routes through humans. Wire it from the environment, never from source:

import Stripe from "stripe";
const stripe = new Stripe(process.env.STRIPE_RESTRICTED_KEY);

Pulling the corpus

The corpus starts from refunds, because every leak scenario begins with one. The snippet below autopaginates refunds created in a window, expands each parent charge inline, then fetches the three quantities the math depends on: the destination transfer's amount, the sum of reversals already made against it, and the application fee with its refunded portion.

interface CorpusRow {
  refund: Stripe.Refund;
  charge: Stripe.Charge;
  transferAmount: number | null;
  reversedCents: number;
  feeAmount: number | null;
  feeRefundedCents: number | null;
}

async function pullRefundRows(sinceUnix: number): Promise<CorpusRow[]> {
  const rows: CorpusRow[] = [];
  const refunds = stripe.refunds.list({
    created: { gte: sinceUnix },
    limit: 100,
    expand: ["data.charge"],
  });
  for await (const refund of refunds) {
    rows.push(await normalize(refund));
  }
  return rows;
}

async function normalize(refund: Stripe.Refund): Promise<CorpusRow> {
  const charge = refund.charge as Stripe.Charge;
  const row: CorpusRow = {
    refund,
    charge,
    transferAmount: null,
    reversedCents: 0,
    feeAmount: null,
    feeRefundedCents: null,
  };

  const transferId =
    typeof charge.transfer === "string" ? charge.transfer : (charge.transfer?.id ?? null);
  if (typeof transferId === "string") {
    const [transfer, reversals] = await Promise.all([
      stripe.transfers.retrieve(transferId),
      stripe.transfers.listReversals(transferId, { limit: 100 }),
    ]);
    row.transferAmount = transfer.amount;
    row.reversedCents = reversals.data.reduce((sum, rev) => sum + rev.amount, 0);
  }

  const feeId =
    typeof charge.application_fee === "string"
      ? charge.application_fee
      : (charge.application_fee?.id ?? null);
  if (typeof feeId === "string") {
    const fee = await stripe.applicationFees.retrieve(feeId);
    row.feeAmount = fee.amount;
    row.feeRefundedCents = fee.amount_refunded;
  }
  return row;
}

Three details worth internalizing. First, the expand parameter saves a request per row; without it, refund.charge is just an ID. Second, reversals are summed rather than assumed singular, because a transfer may carry several partial reversals from earlier refunds. Third, amount_refunded on the ApplicationFee object already aggregates any separately issued fee refunds, so the fee side costs one retrieval, not a listing loop.

Direct-charge fleets. On direct charges, refunds live on the connected account, not your platform — the objects are created under the connected account's context (direct charges), so the platform-side list above never sees them. Iterate known account IDs and pass each through the header-scoped call:

async function pullForAccount(accountId: string, sinceUnix: number): Promise<CorpusRow[]> {
  const rows: CorpusRow[] = [];
  const refunds = stripe.refunds.list(
    { created: { gte: sinceUnix }, limit: 100 },
    { stripeAccount: accountId }
  );
  for await (const refund of refunds) {
    rows.push(await normalize(refund));
  }
  return rows;
}

Keep the account-ID inventory in your own database, refreshed however you onboard and offboard sellers. Disputes join the corpus the same way — stripe.disputes.list with a created window, expanded charges included — feeding the covered-loss check described later.

Expectation functions

Two formulas carry the whole method. Both take integer cents and return integer cents; both assume nothing beyond the objects you already hold.

function expectedReversal(
  refundedCents: number,
  chargeCents: number,
  transferCents: number
): number {
  if (chargeCents <= 0 || transferCents <= 0) return 0;
  return Math.round((refundedCents / chargeCents) * transferCents);
}

function expectedFeeRefund(
  refundedCents: number,
  chargeCents: number,
  feeCents: number
): number {
  if (chargeCents <= 0 || feeCents <= 0) return 0;
  return Math.round((refundedCents / chargeCents) * feeCents);
}

The proportional-reversal shape is not arbitrary: it mirrors what Stripe itself does when reverse_transfer=true rides on a partial refund — the transfer is reversed proportionally to the amount being refunded (Refunds API). The fee analog follows identically from full-with-full, partial-with-proportional semantics.

The critical discipline is what refundedCents means: the charge-level cumulative, not each refund's individual amount. Compute it from charge.amount_refunded, which the Charge object maintains for you. Aggregating this way defeats rounding drift. Watch the difference on a small case: charge 300¢, transfer 100¢, three sequential refunds of 100¢ each. Per-refund expectations round to 33¢ apiece, and 3 × 33 = 99¢ — a phantom 1¢ shortfall manufactured entirely by intermediate rounding. Charge-level: round((300 ÷ 300) × 100) = 100¢, compared once against the reversals actually on file. No phantom. The trade-off is philosophical and worth stating plainly: the cumulative model answers "is money missing right now?" rather than "which specific refund underpaid?" For leakage detection, the first question is the one with financial consequences.

Turning diffs into findings

Group corpus rows by charge, evaluate both expectations once per group, and subtract what actually happened:

interface Finding {
  charge_id: string;
  missing_transfer_cents: number;
  missing_fee_cents: number;
  currency: string;
  age_days: number;
}

function buildFindings(corpus: CorpusRow[], nowUnix: number): Finding[] {
  const byCharge = new Map<string, CorpusRow[]>();
  for (const row of corpus) {
    const bucket = byCharge.get(row.charge.id) ?? [];
    bucket.push(row);
    byCharge.set(row.charge.id, bucket);
  }

  const findings: Finding[] = [];
  for (const [chargeId, rows] of byCharge) {
    const first = rows[0];
    const refundedSoFar = first.charge.amount_refunded;
    let missingTransfer = 0;
    let missingFee = 0;

    if (typeof first.transferAmount === "number") {
      const expected =
        expectedReversal(refundedSoFar, first.charge.amount, first.transferAmount);
      missingTransfer = Math.max(expected - first.reversedCents, 0);
    }
    if (
      typeof first.feeAmount === "number" &&
      typeof first.feeRefundedCents === "number"
    ) {
      const expectedFee =
        expectedFeeRefund(refundedSoFar, first.charge.amount, first.feeAmount);
      missingFee = Math.max(expectedFee - first.feeRefundedCents, 0);
    }
    if (missingTransfer === 0 && missingFee === 0) continue;

    findings.push({
      charge_id: chargeId,
      missing_transfer_cents: missingTransfer,
      missing_fee_cents: missingFee,
      currency: first.refund.currency,
      age_days: Math.floor((nowUnix - first.charge.created) / 86400),
    });
  }
  return findings;
}

Run it against a corpus containing the canonical scenarios — $100.00 charge, $10.00 application fee, $90.00 transfer, standard US card pricing — and the store fills with records shaped like this:

[
  {
    "charge_id": "ch_ExampleFullDefault",
    "missing_transfer_cents": 9000,
    "missing_fee_cents": 1000,
    "currency": "usd",
    "age_days": 41
  },
  {
    "charge_id": "ch_ExamplePartialFlag",
    "missing_transfer_cents": 0,
    "missing_fee_cents": 400,
    "currency": "usd",
    "age_days": 17
  },
  {
    "charge_id": "ch_ExampleFeeOnlyFlag",
    "missing_transfer_cents": 9000,
    "missing_fee_cents": 0,
    "currency": "usd",
    "age_days": 9
  }
]

Reading the rows: the first is a full default refund — transfer untouched ($90.00 short), fee kept ($10.00 short). The second is a $40.00 partial refund issued with reverse_transfer=true; the proportional reversal landed (expected round((4000 ÷ 10000) × 9000) = 3600¢, actual 3600¢) but the proportional fee refund of 400¢ did not. The third had refund_application_fee=true alone: fee squared away, $90.00 of seller-held funds left unclaimed. Every row carries the evidence trail implicitly — charge ID plus amounts — so any human can pull the underlying objects and verify the arithmetic by hand.

Scheduling, cursors, idempotency

Detection is a loop, and loops need memory. Persist the last successful scan boundary and resume from it:

import { readFileSync, writeFileSync } from "node:fs";

const CURSOR_PATH = "./refund-cursor.json";
const DAY_SECONDS = 86400;

function loadCursor(): number {
  try {
    return JSON.parse(readFileSync(CURSOR_PATH, "utf8")).created_gt as number;
  } catch {
    return Math.floor(Date.now() / 1000) - 90 * DAY_SECONDS;
  }
}

function saveCursor(unix: number): void {
  writeFileSync(CURSOR_PATH, JSON.stringify({ created_gt: unix }));
}

async function runScheduledScan(): Promise<void> {
  const now = Math.floor(Date.now() / 1000);
  const cursor = loadCursor();
  const rows = await pullRefundRows(cursor);
  const findings = buildFindings(rows, now);
  await storeFindings(findings);
  saveCursor(now);
}

A daily cron suits most platforms: refund-driven leaks do not decay faster than daily attention, and the 90-day lookback on first run — the catch branch seeds the cursor ninety days back — doubles as your initial historical sweep. Findings from that sweep will be old; route them to a batch review rather than paging anyone. Bulk-triaging aged findings is its own discipline (historical findings playbook).

One reservation: Idempotency-Key headers belong to the action phase, not this one. Detection writes nothing to Stripe, so it needs no idempotency keys. The moment any component graduates to taking actions — creating a reversal, refunding an application fee — every POST carries a deterministic key, because Stripe honors keys for 24 hours to make retries safe (idempotent requests):

await stripe.transfers.createReversal(
  transferId,
  { amount: finding.missing_transfer_cents },
  { idempotencyKey: `reverse-${finding.charge_id}-${policyVersion}` }
);

Never act automatically on first detection. A finding is a hypothesis with numbers attached; confirmation, context (policy version, goodwill overrides, netting already in flight), and approval come before any money moves. The design patterns for safe reversal execution are catalogued separately (idempotency for reversals).

The event-driven variant

Batch scans bound staleness at the cron interval. If you need tighter latency, subscribe to webhook events and run the identical expectation logic incrementally. The relevant set covers the money movements themselves: refund.created, refund.updated, charge.refunded, application_fee.created, application_fee.refunded, transfer.created, transfer.reversed, plus the dispute events (charge.dispute.created, charge.dispute.updated, charge.dispute.closed). Verify signatures, respond quickly, process asynchronously — delivery is at-least-once with retries up to roughly three days, so handlers must be idempotent (webhooks).

import express from "express";

const endpointSecret = process.env.STRIPE_WEBHOOK_SECRET as string;
const app = express();

app.post(
  "/webhooks/stripe",
  express.raw({ type: "application/json" }),
  (req, res) => {
    const signature = String(req.headers["stripe-signature"]);
    let event: Stripe.Event;
    try {
      event = stripe.webhooks.constructEvent(req.body, signature, endpointSecret);
    } catch {
      res.sendStatus(400);
      return;
    }
    if (
      event.type === "refund.updated" ||
      event.type === "charge.refunded" ||
      event.type === "application_fee.refunded" ||
      event.type === "transfer.reversed" ||
      event.type === "charge.dispute.closed"
    ) {
      void queue.enqueue(event);
    }
    res.sendStatus(200);
  }
);

app.listen(4242);

declare const queue: { enqueue: (event: Stripe.Event) => Promise<void> };

Put a queue between receiver and worker even at modest volume: the receiver's only jobs are signature verification and fast 2xx responses, while the worker fetches the affected refund, normalizes it, runs the same buildFindings evaluation for that single charge, and updates the findings store. Because both paths share the expectation engine, batch and event results agree by construction — disagreements become your best regression tests. The wiring patterns for Express receivers and queued workers are documented step-by-step in the Node refund-handling guide and the queue-based processing guide; the general trade space between these approaches is mapped at building on webhooks yourself.

Maintenance reality

The skeleton above works. Keeping it working is the honest part of this piece, because a homegrown detector inherits maintenance obligations that never fully retire:

Schema and API drift. Object fields, pagination behavior, and SDK typings move with API versions. Every Stripe upgrade warrants a re-read of the release notes against your normalizer, because your expectations silently encode field assumptions like amount_refunded staying cumulative.

Multi-currency conversions. Cross-currency refunds convert at the live rate on refund day regardless of any quote locked at payment time, and Stripe does not return the original transaction's FX fee (FX on refunds). Your expectation math stays in the charge currency, but platform-level loss reporting needs conversion handling, and FX slippage becomes its own detection problem beside the transfer math. Zero-decimal currencies such as JPY store amounts in the unit itself — the formulas survive unchanged, but every display threshold and alert constant needs currency-awareness (currencies).

Source_transaction timing. Transfers created with source_transaction attach to a charge's availability schedule rather than paying out immediately. The amounts remain valid inputs to the formulas, but "the transfer exists" and "the funds moved" stop being simultaneous, and reconciliation windows must tolerate the gap.

Partial-refund sequences. Mixed flag choices across multiple refunds on one charge are the common case, not the edge. The cumulative model handles state correctly, but per-event attribution — who under-reversed which time — requires replaying the sequence in order, which is extra machinery you will eventually want.

Pending and failed refunds. A refund debited against an insufficient balance sits in pending and may lack a balance transaction until funded; failed refunds return funds within up to roughly thirty days and carry failure_balance_transaction and failure_reason (refunds). Scan loops must skip-and-recheck rather than treat either state as final.

Direct-charge fleets. Account inventories churn, closed accounts linger, rate limits arrive per-account, and a fleet spanning hundreds of accounts turns yesterday's simple loop into a scheduling problem.

Disputes as their own lane. On destination and separate charges the disputed amount and the dispute fee debit the platform balance, and recovering from a seller is manual (disputes on Connect). Extending the engine to covered-loss checks means modeling dispute outcomes, win rates, and re-transfer decisions — genuinely worth doing, genuinely separate work.

None of this is disqualifying; all of it is permanent. That is the actual decision in front of you: the detection arithmetic fits in a file, but the upkeep — version drift, currency edges, state machines, fleet mechanics — is an ongoing product you would now operate. Some platforms should operate it; the exercise above is the honest way to find out whether yours is one of them, because building even the skeleton surfaces every question a maintained system must answer forever.

Frequently asked questions

Why Math.round specifically?

Because a convention beats a debate. Rounding half-up matches the convention used throughout Stripe's proportional behavior as commonly modeled, and — more importantly — holding one convention permanently matters more than which one you pick. Switching conventions mid-stream manufactures phantom findings out of thin air, so write the choice down next to the policy version.

Do zero-decimal currencies break the formulas?

No. In zero-decimal currencies the amount is already expressed in the unit itself, so a JPY charge and its refund use identical units and the ratios come out clean. What breaks naively is presentation and thresholds: an alert constant tuned as "$10" reads absurdly in yen unless your code converts display units per currency.

Why not compute expectations inside Sigma?

Sigma queries recorded data read-only, and expectations are not recorded anywhere in the schema — there is no column holding what should have been reversed. You can export raw components from Sigma and feed them into these same formulas, which is a legitimate hybrid, but the arithmetic always happens outside Stripe.

How should the scanner treat pending refunds?

Skip them, deliberately. Track their status and revisit on the next cycle; a pending refund typically lacks the balance transaction your normalizer joins on, and treating absence-of-evidence as a finding floods the store with noise during balance shortages. Only finalized refunds produce stable expectations.

When exactly do I add Idempotency-Key headers?

Only when the system starts taking actions. Detection POSTs nothing, so it needs no keys. The first createReversal or fee-refund call should carry a deterministic key built from the charge ID and policy version, keeping 24-hour retries safe — and that transition is precisely where automatic action should start requiring human approval first.

Run the free 90-day audit

If you would rather see the findings before committing to operating the machinery, FeeGuard runs this exact expectation arithmetic — proportional reversals, application-fee refunds, dispute losses, FX slippage — over your last 90 days of Connect activity through a restricted, read-only API key, and reports every occurrence with the underlying Stripe evidence attached. You get the answer first; monitoring is optional afterward.

Run the free 90-day audit.

FeeGuard is an independent product and is not affiliated with, endorsed by, or sponsored by Stripe, Inc.

Your NEXT_PUBLIC secret is already in the browser bundle

Veristria — Fri, 28 Aug 2026 20:59:58 +0000

Problem

In a Next.js app it’s easy to slip a secret (e.g., STRIPE_SECRET_KEY, OpenAI API key, Supabase JWT) into a client‑side bundle. When a server component reads process.env.STRIPE_SECRET_KEY it stays on the server, but copying that line into a client component causes the build to fail to resolve the variable. The common “quick fix”—renaming the variable with the NEXT_PUBLIC_ prefix—makes the value part of the JavaScript that every visitor downloads, turning a server‑only secret into a public leak.

How it works (mechanism)

Next.js environment variable scoping
- Variables without the NEXT_PUBLIC_ prefix are stripped from the client bundle at build time. They are only available in server‑side code (pages/api/*, server components, getServerSideProps, etc.).
- Variables with the NEXT_PUBLIC_ prefix are injected into the client bundle and can be read from process.env in any browser‑executed code.
Accidental exposure
- A developer moves a line such as const stripeKey = process.env.STRIPE_SECRET_KEY; from a server component to a client component (or a shared utility imported by both).
- The build fails because STRIPE_SECRET_KEY is undefined on the client.
- To silence the error they rename the variable to NEXT_PUBLIC_STRIPE_SECRET_KEY.
- The renamed variable is now inlined into the bundle, exposing the secret to anyone who can view the page source or network traffic.
Why the leak is critical
- Secrets like Stripe secret keys, OpenAI API keys, Supabase JWTs, AWS access keys, and webhook signing secrets grant full access to the respective services. Once they appear in a public bundle they can be harvested by bots or malicious actors.

Detection (KeyDrift)

KeyDrift performs a read‑only scan of your client bundle—no credentials are required—to locate hard‑coded secrets and environment variables that have been inlined. The scan cross‑references each detected credential with the tool that introduced it (e.g., Next.js, Replit, Cursor).

Typical output for a Next.js leak looks like:

[Critical] STRIPE_SECRET_KEY found in client bundle (tool: Next.js)
Location: static/chunks/pages/_app.js:1234
Recommendation: Move usage to a server component or API route.

KeyDrift also flags variables that have been renamed with the NEXT_PUBLIC_ prefix and marks them as high or critical depending on the credential type (e.g., Stripe secret key → critical).

Fix

1. Keep the secret on the server

// app/api/stripe/checkout/route.ts (server‑only)
import Stripe from 'stripe';

const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!, {
  apiVersion: '2023-10-16',
});

export async function POST(req: Request) {
  // server‑side logic only
}

2. Call the server from the client

// app/components/CheckoutButton.tsx (client component)
'use client';
import { useState } from 'react';

export default function CheckoutButton() {
  const [loading, setLoading] = useState(false);

  const startCheckout = async () => {
    setLoading(true);
    const res = await fetch('/api/stripe/checkout', { method: 'POST' });
    // handle response...
    setLoading(false);
  };

  return <button onClick={startCheckout} disabled={loading}>Buy</button>;
}

3. Remove `NEXT_PUBLIC_` prefixes for real secrets

If you have already renamed a secret, revert the name in the source and run a clean build:

- const stripeKey = process.env.NEXT_PUBLIC_STRIPE_SECRET_KEY;
+ const stripeKey = process.env.STRIPE_SECRET_KEY; // server‑only

4. Verify with KeyDrift

Run a free KeyDrift scan (read‑only, no credentials) after the change:

npx keydrift scan --path ./out

The scan should no longer report the secret in the client bundle.

Caveats

Caveat	Details
Environment variable duplication	If you need a value both on server and client (e.g., a public API key), store it separately as `NEXT_PUBLIC_...` and keep the secret version (`..._SECRET`) only on the server.
Third‑party libraries	Some libraries (e.g., Stripe.js) expect a public key (`pk_test_...`). Ensure you are not accidentally passing a secret key to such libraries.
Build caching	After renaming variables, clear `.next` or run `next build --no-cache` to avoid stale bundles that still contain the leaked value.
Server‑side rendering (SSR) vs. static generation	In `getStaticProps` the code runs at build time on the server, so secrets are safe there. However, any data returned to the page becomes part of the HTML and can be inspected, so avoid embedding raw secrets in the returned props.
Dynamic imports	Importing a module that reads a secret inside a client component will cause the same leak. Keep such imports confined to server‑only modules.

KeyDrift provides a concrete, read‑only audit that surfaces these leaks before they reach production. By moving secret usage back to server‑only code and avoiding the NEXT_PUBLIC_ prefix for real credentials, you eliminate the most common source of client‑bundle secret exposure in Next.js projects.

For more detailed guidance see the KeyDrift Fix Guides on “exposed keys by tool and credential.”

Your Supabase Storage bucket is public - signed URLs will not save you

Veristria — Fri, 28 Aug 2026 20:59:51 +0000

Problem

You are generating private B2B images with Sharp in a Next.js API route and storing the derivatives in Supabase Storage. The bucket that holds those images is currently public, meaning anyone who can guess or discover the object URL can download the original or any derivative without authentication. This defeats the “private‑only” intent of your workflow and can expose sensitive business assets (e.g., brand assets, customer logos, confidential diagrams).

Mechanism

Supabase Storage buckets have a public flag. When public = true the bucket’s objects are served directly via a public URL (https://<project>.supabase.co/storage/v1/object/public/<bucket>/<path>). No auth check is performed, and the URL is stable as long as the object exists.

When you later generate presigned URLs (e.g., storage.from('avatars').createSignedUrl(...)) you rely on the bucket being private; the signed URL adds a time‑limited token that Supabase validates before serving the object. If the bucket is public, the signed URL is unnecessary and the underlying object can be fetched without it.

Detection

A quick read‑only audit (no credentials required) can confirm the bucket’s exposure:

-- RowShield‑style detection query
SELECT id, public
FROM storage.buckets
WHERE id = 'avatars';   -- replace with your bucket name

If the result shows public = true, the bucket is publicly readable.

You can also verify manually:

curl -I "https://<project>.supabase.co/storage/v1/object/public/avatars/example.jpg"
# Expect HTTP 200 even without auth → bucket is public

Fix

Make the bucket private

-- Turn off public access
UPDATE storage.buckets
SET public = false
WHERE id = 'avatars';   -- replace with your bucket name

Add a row‑level security (RLS) policy that limits reads to the owning user (or to a service account you control). Example for per‑user folders:

-- Allow authenticated users to read only objects in their own folder
CREATE POLICY avatars_read_own
ON storage.objects
FOR SELECT
TO authenticated
USING (
  bucket_id = 'avatars' AND
  (storage.foldername(name))[1] = (auth.uid())::text
);

Generate expiring signed URLs in your Next.js API route instead of returning raw URLs:

// pages/api/image.ts
import { createClient } from '@supabase/supabase-js';
import type { NextApiRequest, NextApiResponse } from 'next';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!   // server‑side only
);

export default async function handler(req: NextApiRequest, res: NextApiResponse) {
  const { bucket, path, expiresIn } = req.query as {
    bucket: string;
    path: string;
    expiresIn?: string;
  };

  // Validate request (e.g., check auth, ownership, etc.)
  // ...

  const { data, error } = await supabase.storage
    .from(bucket)
    .createSignedUrl(path, Number(expiresIn) || 60); // seconds

  if (error) {
    res.status(500).json({ error: error.message });
    return;
  }

  res.status(200).json({ url: data?.signedUrl });
}

Update your client to request the signed URL from the API route and use that URL for <img> tags or downloads.
Optional: Queue heavy Sharp processing

If derivative generation is CPU‑intensive, offload it to a background worker (e.g., Supabase Edge Functions, a separate Node worker, or a cloud queue). Store the resulting files in the now‑private bucket, then serve them via the signed‑URL endpoint above.

Caveats

Issue	Impact	Mitigation
Service‑role key exposure	If the key leaks, anyone can bypass RLS.	Store it only on the server (e.g., Vercel/Netlify serverless functions). Never bundle it in client code.
Policy granularity	The example policy assumes a flat `userId/filename` layout.	Adjust the `USING` clause to match your folder structure (e.g., `projectId/userId/...`).
Signed‑URL TTL	Too short → frequent re‑fetches; too long → larger attack window.	Choose a TTL that balances UX and security (commonly 60–300 seconds).
Bucket‑wide public flag	Changing `public` to `false` affects all objects, including any that truly need public access (e.g., marketing assets).	Split assets into separate buckets: one private for B2B images, one public for truly public content.
Cache invalidation	Browsers may cache a signed URL longer than its TTL if `Cache-Control` headers aren’t set.	Set `Cache-Control: private, max-age=0` on the response or on the object metadata.

Next steps

Run the read‑only audit (SELECT id, public FROM storage.buckets) to confirm the bucket’s current state.
Apply the UPDATE statement to make the bucket private.
Deploy the RLS policy that matches your ownership model.
Update your Next.js API route to issue signed URLs and replace any direct object URLs in the UI.
Test end‑to‑end: unauthenticated request → 403, authenticated request → signed URL → successful image load.

By tightening the bucket’s visibility and gating access through signed URLs generated in a server‑side Next.js route, you close the primary exposure vector while preserving the ability to serve private B2B images efficiently.

All audit queries are read‑only and require no credentials.

Stripe Connect refunds: the two flags that silently cost platforms

Veristria — Fri, 28 Aug 2026 20:59:39 +0000

Problem

When a Stripe Connect platform refunds a charge, the platform often assumes that the original transfer and the application fee are automatically undone. In practice, two boolean flags—refund_application_fee and reverse_transfer—control whether the fee and the transfer are reversed. Their defaults are non‑obvious (false for both), so a refund can leave the platform with a net loss that never appears in Stripe’s error logs. The loss shows up only as a discrepancy between the amount refunded to the customer and the amount that actually returns to the platform’s balance.

Mechanism

A Connect refund touches several objects:

Object	Role in a refund	Default behavior
`Charge`	Original payment	N/A
`Transfer`	Moves funds from platform to connected account	Not reversed unless `reverse_transfer: true`
`ApplicationFee`	Platform’s fee on the charge	Not refunded unless `refund_application_fee: true`
`Refund`	Credits the customer	Always creates a credit on the original charge

The two flags are independent:

`refund_application_fee`	`reverse_transfer`	Platform net outcome
false	false	Platform keeps the fee and the transfer stays settled → net loss = fee + any FX spread
false	true	Transfer is reversed but fee stays → net loss = fee
true	false	Fee is refunded but transfer stays → net loss = transferred amount (minus fee)
true	true	Both fee and transfer are undone → net zero (aside from timing/FX)

Because the defaults are false, a typical “refund” only credits the customer; the platform’s money remains locked in the connected account and the fee stays on the platform’s ledger. This silent leak is why many platforms see “missing” revenue after a batch of refunds.

Detection

FeeGuard can audit the last 90 days (or any custom window) without any credentials—just a read‑only API token or a CSV export. The audit consists of three steps:

Export refunds – Pull all Refund objects for the target date range.
Join to transfers and fees – For each refund, locate the associated Transfer (transfer field on the original Charge) and ApplicationFee (application_fee field).
Apply the truth table – Compute the expected platform balance change based on the two flags. Compare it to the actual balance delta reported by Stripe.

# Python example (requires only read‑only Stripe secret)
import stripe
from datetime import datetime, timedelta

stripe.api_key = "sk_test_readonly_..."

def fetch_refunds(days=90):
    start = int((datetime.utcnow() - timedelta(days=days)).timestamp())
    refunds = stripe.Refund.list(created={'gte': start}, limit=100)
    return refunds.auto_paging_iter()

def audit():
    total_gap = 0
    for r in fetch_refunds():
        charge = stripe.Charge.retrieve(r.charge)
        transfer = stripe.Transfer.retrieve(charge.transfer) if charge.transfer else None
        fee = stripe.ApplicationFee.retrieve(charge.application_fee) if charge.application_fee else None

        # Flags (defaults are false if not present)
        rev_fee = r.refund_application_fee or False
        rev_transfer = r.reverse_transfer or False

        # Expected platform delta
        fee_amount = fee.amount if fee else 0
        transfer_amount = transfer.amount if transfer else 0

        expected = 0
        if not rev_fee:
            expected -= fee_amount
        if not rev_transfer:
            expected -= transfer_amount

        # Actual delta is the amount Stripe reports as returned to the platform
        actual = r.amount  # amount refunded to customer (negative for platform)

        gap = expected - actual
        total_gap += gap

    print(f"Total unrecovered loss: {total_gap/100:.2f} USD")

Running the script (or using FeeGuard’s UI) yields the exact dollar amount that never made it back to the platform.

Fix

There are two complementary approaches:

Preventive configuration – When creating a refund via the API, explicitly set the flags to true if you want the fee and transfer reversed:

curl https://api.stripe.com/v1/refunds \
  -u sk_test_readonly_: \
  -d charge=ch_1ABC... \
  -d amount=5000 \
  -d refund_application_fee=true \
  -d reverse_transfer=true

Tip: Wrap this call in a helper function in your backend so every refund uses the same parameters.

Post‑refund recovery – If a batch of refunds has already been processed without the flags, use FeeGuard’s recovery flow:

Run a read‑only audit to quantify the loss.
For each affected refund, issue a separate “fee reversal” and “transfer reversal” using the ApplicationFeeRefund and TransferReversal endpoints. Both are independent operations and can be performed after the original refund.

# Reverse fee
stripe.ApplicationFeeRefund.create(
    application_fee=fee.id,
    amount=fee.amount,
)

# Reverse transfer
stripe.TransferReversal.create(
    transfer=transfer.id,
    amount=transfer.amount,
)

Because FeeGuard’s pricing is “pay‑only‑on‑recovery,” you only incur cost when the above steps actually return money to the platform.

Caveats

Issue	Detail	Mitigation
Timing	Transfer reversals can take up to a few business days to settle.	Track reversal status via `TransferReversal` objects.
FX exposure	If the original transfer involved currency conversion, the reversal may settle at a different rate, leaving a small residual.	Include FX variance in the audit’s “gap” calculation.
Partial refunds	When only part of a charge is refunded, you must decide whether to reverse the proportional fee/transfer or keep the remainder.	Use the same proportion (`refund.amount / charge.amount`) when creating `ApplicationFeeRefund` and `TransferReversal`.
Disconnected accounts	Some connected accounts may have custom payout schedules that block immediate reversals.	Verify the account’s `payout_schedule` before attempting a reversal; fallback to manual payout if needed.
Read‑only audit limits	The audit does not require any write credentials, but it cannot detect unreversed transfers that were never created (e.g., a missing `transfer` field).	Ensure your platform always creates a `Transfer` for every Connect charge; otherwise, the audit will flag the missing link.

Summary

Stripe Connect refunds can silently leak platform revenue because refund_application_fee and reverse_transfer default to false. By systematically joining refunds to their associated transfers and fees, applying the truth table, and either configuring refunds correctly up front or reversing the missed amounts afterward, a platform can recover the hidden loss. FeeGuard’s read‑only audit makes the detection step trivial, and its pay‑only‑on‑recovery model ensures you only pay when money is actually returned.

For a deeper dive, see the full blog post on the FeeGuard site.

What a lost dispute actually costs a Connect platform

Veristria — Fri, 21 Aug 2026 13:40:43 +0000

Refunds get the attention. Disputes are worse, and the reason is structural rather than a matter of degree.

On a refund you at least have a parameter. reverse_transfer: true exists, you can pass it, and if you forget there is a boolean to point at afterwards. On a dispute there is no such parameter, because there is no such option.

What Stripe does when you lose

Stripe debits the platform for the disputed amount plus the dispute fee. That is it. That is the whole automatic behaviour.

The connected account that received the original transfer is not touched. Not reduced, not notified, not flagged. From that seller's perspective nothing happened at all — the money arrived weeks ago and is still theirs.

So the platform's position after losing a dispute on a destination charge is:

- disputed amount   (debited from platform balance)
- dispute fee       (debited from platform balance, typically ~15 USD)
+ nothing            (the transfer is untouched)

The seller keeps the funds. You paid the customer back and paid a fee for the privilege.

Why this is not symmetrical with refunds

A refund is initiated by you, so Stripe can offer a parameter at the moment of initiation. A dispute is initiated by the cardholder's bank, days or weeks after the charge, through a process you are not in. There is no call of yours to attach a flag to.

The reversal, if you want one, has to be a separate action you take afterwards:

// on charge.dispute.closed with status 'lost'
await stripe.transfers.createReversal(transferId, {
  amount: disputedAmount,       // not necessarily the full transfer
  description: `dispute ${disputeId}`,
});

Nothing calls that for you. If your webhook handler for charge.dispute.closed only writes a row and updates a status, the clawback never happens.

Three things that make this accumulate quietly

The timing hides it. A dispute closes 30 to 90 days after the charge. By then the transfer is in a different payout, a different month, and usually a different reconciliation report. The debit and the un-reversed transfer are never adjacent in any view you look at.

The connected balance may not cover it. Even when you do reverse, the seller may have already paid out. The reversal leaves the account negative, which surfaces later as a failed payout — a third event, in a fourth place, with no obvious link back to a dispute.

Winning still costs. The dispute fee is generally not returned on a win in most regions. A platform with a good win rate still bleeds fees, and those fees are charged to the platform rather than to the seller whose transaction caused them.

The reconciliation query

The question is the same shape as the refund one, and it is worth running over full history rather than a recent window, because the whole failure mode is that it is old by the time it matters:

SELECT d.id                AS dispute_id,
       d.charge_id,
       d.amount            AS debited,
       d.fee               AS dispute_fee,
       COALESCE(r.amount, 0) AS reversed,
       d.amount - COALESCE(r.amount, 0) AS unrecovered
  FROM disputes d
  LEFT JOIN transfer_reversals r ON r.charge_id = d.charge_id
 WHERE d.status = 'lost'
   AND d.amount > COALESCE(r.amount, 0)
 ORDER BY unrecovered DESC;

The policy question underneath the technical one

Whether to claw back is genuinely a business decision, and reasonable platforms answer it differently. A marketplace with sellers who have no control over fraud may absorb disputes deliberately. A platform whose sellers do control fraud usually should not.

What is not a decision is doing it by accident. If you have never reversed a transfer on a lost dispute, you have chosen to absorb every one of them — you just did not know that was the choice you were making. Run the query and find out which platform you are.

The Stripe Connect refund that only costs the platform

Veristria — Fri, 21 Aug 2026 13:39:36 +0000

There is a class of bug in Stripe Connect that never throws, never logs, and never fails a webhook. The refund succeeds. The customer is made whole. The money comes out of your balance and stays with the seller.

It is worth understanding precisely, because the fix is one boolean and the detection is one join.

What a destination charge actually is

On a destination charge, one API call does two things: it charges the customer, and it creates a transfer moving funds to the connected account. Two objects, one call.

A refund, by default, only undoes the first one.

// the customer gets their money back. the transfer is untouched.
await stripe.refunds.create({ charge: 'ch_123' });

// the transfer is reversed too
await stripe.refunds.create({
  charge: 'ch_123',
  reverse_transfer: true,
});

reverse_transfer defaults to false. That default is defensible — sometimes the platform genuinely wants to absorb a refund rather than claw it back from a seller who already shipped. What makes it a footgun is that choosing wrongly produces no signal at all.

The second flag, which is not the same flag

refund_application_fee is independent. Reversing the transfer does not refund your platform fee, and refunding the fee does not reverse the transfer.

That means a refund can be wrong in either direction on its own:

transfer reversed, fee kept -> you kept a fee on revenue that no longer exists
fee refunded, transfer not reversed -> you paid the customer and gave up the fee

Three details that bite during reconciliation

Partial refunds do not partially reverse unless you ask. A partial refund with reverse_transfer: true reverses the corresponding fraction. A partial refund without it reverses nothing at all — not a smaller amount, nothing.

A negative connected balance does not block the refund. The reversal is attempted and the account can be left negative. You find out later, as a failed payout, in a different part of the system, with no obvious link back.

Disputes behave differently again. On a lost dispute, Stripe debits the platform in full. The connected account that received the transfer is untouched unless you reverse it yourself. There is no reverse_transfer on a dispute.

The query that finds them

The reconciliation question is not "did the refund succeed". It is: for every charge.refunded, is there a matching transfer.reversal of the expected amount?

Working from the events you already receive:

SELECT r.charge_id,
       r.amount                      AS refunded,
       COALESCE(rev.amount, 0)       AS reversed,
       r.amount - COALESCE(rev.amount, 0) AS unrecovered
  FROM refunds r
  LEFT JOIN transfer_reversals rev
         ON rev.transfer_id = r.transfer_id
 WHERE r.amount > COALESCE(rev.amount, 0)
 ORDER BY unrecovered DESC;

Every row is money that left and did not come back. Run it across your full history the first time — this accumulates silently, so the first run is usually the interesting one.

Why it survives code review

Because the code looks correct. refunds.create({ charge }) is the documented call, it does what its name says, and the omission is a parameter that is not there. Reviewers see present code, not absent parameters.

The durable fix is not to remember the flag. It is to make the two event streams reconcile automatically, so the answer to "is our ledger whole" is a number somebody looks at rather than an assumption everybody makes.

gitignore is not a security control

Veristria — Fri, 21 Aug 2026 13:34:31 +0000

A .env file in .gitignore tells you one thing: the file is not in your git history. It tells you nothing about whether the value inside it is currently readable by anyone who loads your site.

Those are different questions, and only the second one describes what an attacker actually does. Nobody clones your repo. They open the deployed bundle.

Where the value actually goes

A build reads .env and then makes a decision about each variable. The decision is usually made by a prefix.

Framework	Inlined into client output
Next.js	`NEXT_PUBLIC_*`
Vite	`VITE_*`
Create React App	`REACT_APP_*`
Nuxt	`NUXT_PUBLIC_*`
Astro	`PUBLIC_*`
SvelteKit	`PUBLIC_*`

Those prefixes are not warnings. They are instructions to publish. A variable so named is compiled into JavaScript that ships to every visitor, by design, and it works exactly as intended.

The mistake is almost never that someone misunderstood the prefix. It is that a key was moved into one during a debugging session at 2am, the bug got fixed, and it never moved back.

Three places the value survives after you rotate

This is the part people miss. Rotating the key is necessary. It is not sufficient, because the old value is still sitting in artefacts nobody thinks of as artefacts.

1. Source maps. A minified bundle may not show a readable key. The .map file beside it usually does — that is its entire purpose. Shipping source maps to production is common and mostly harmless, right up until the moment it is not.

# what an attacker runs, and takes about four seconds
curl -s https://yoursite.com/_next/static/chunks/main-*.js \n  | grep -oE '(sk|pk|rk)_(live|test)_[A-Za-z0-9]{16,}'

2. Previous deploys. Every platform keeps old deployments addressable. A Vercel preview URL from three weeks ago serves the bundle from three weeks ago, containing the key from three weeks ago. Rotation does not reach backwards.

3. Build logs. CI output, deploy logs and provider dashboards retain output for weeks. One console.log in a failing build is enough, and failing builds are exactly when people add logging.

The check that means something

Stop asking whether the file is ignored. Ask whether the value is recoverable from what you shipped.

# fetch what the browser actually receives, and look in it
curl -s https://yoursite.com | grep -oE '/_next/static/chunks/[^"]+.js' \n  | sort -u | head -20 \n  | xargs -I{} curl -s "https://yoursite.com{}" \n  | grep -oE '[A-Za-z0-9_-]{32,}' | sort -u

That is crude and it is still more informative than reading .gitignore, because it operates on the artefact rather than on the intent.

What to do about the ones that are already out

In order, because the order matters:

Rotate first, investigate second. The window matters more than the root cause, and the root cause is still there in an hour.
Check the provider's logs for use. Stripe, Supabase, OpenAI and most others will tell you whether the key was used and from where. "It leaked" and "it was used" call for very different responses.
Delete or expire old deploy previews. Otherwise step 1 protected the future and nothing else.
Then fix the prefix, move the call server-side, and add the check to CI.

The general principle is worth stating plainly, because it generalises past secrets: a control that operates on your source is not a control on what you ship. The build stands between them, and the build is where the interesting mistakes live.

Your RLS policies were correct when you wrote them

Veristria — Fri, 21 Aug 2026 13:34:01 +0000

Row-level security has a property that makes it unusually easy to get wrong over time: a policy that is correct is indistinguishable from a policy that is wrong, until someone reads it.

There is no error. No failed query. A too-permissive policy returns more rows, which looks exactly like a working application. That is the whole problem in one sentence.

The four ways a policy stops being correct

None of these involve anyone editing the policy.

1. A new column arrives. You wrote USING (auth.uid() = user_id) and it was right. Then a migration added organization_id, and rows are now reachable by a user who belongs to a different organisation but happens to match on user_id. The policy did not change. Its meaning did.

2. USING was written and WITH CHECK was not. These are separate halves and people routinely write one. USING filters what a statement can see; WITH CHECK constrains what it can write. A policy with only USING will happily let a user UPDATE a row into a state where they can no longer see it — writing data they cannot read back, into someone else's tenant.

-- readable only by the owner, writable into any shape
CREATE POLICY p ON documents
  FOR ALL USING (auth.uid() = owner_id);

-- both halves
CREATE POLICY p ON documents
  FOR ALL USING (auth.uid() = owner_id)
         WITH CHECK (auth.uid() = owner_id);

3. A table was created without RLS at all. ALTER TABLE ... ENABLE ROW LEVEL SECURITY is a separate statement from CREATE TABLE. A table created by a migration, a dashboard click, or a framework generator does not have it on by default. Enabling RLS with no policies denies everything, which is safe and visible. Forgetting to enable it exposes everything, which is unsafe and invisible.

4. The service role bypasses all of it. That is what it is for. The failure is not the key existing, it is the key being used from somewhere that handles end-user input — an edge function, a server action, a webhook handler that grew a query.

Reading a policy is not testing it

This is the part worth internalising. You can read a policy, agree it looks right, and be wrong — because what it does depends on the current schema, the current role, and the current auth.uid(), none of which are in the policy text.

The test that means something is: connect as the role, set the claims, and count rows you should not be able to see.

-- as an authenticated user who owns nothing in this tenant
SET request.jwt.claims = '{"sub":"11111111-1111-1111-1111-111111111111"}';
SET ROLE authenticated;

SELECT count(*) FROM documents;   -- expect 0, not "looks fine"

If that returns a non-zero number, the policy is wrong regardless of how it reads.

The check worth automating

Three queries, run on every migration, catch most of it:

-- tables with RLS off
SELECT relname FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE n.nspname = 'public' AND c.relkind = 'r' AND NOT c.relrowsecurity;

-- tables with RLS on and no policies (denies all - usually unintended)
SELECT c.relname 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.relrowsecurity AND p.oid IS NULL
 GROUP BY c.relname;

-- policies with USING and no WITH CHECK on a write command
SELECT polrelid::regclass, polname FROM pg_policy
 WHERE polwithcheck IS NULL AND polcmd IN ('a','w','*');

None of these need a tool. Run them in CI and fail the build.

The reason drift is worth monitoring rather than auditing once is in the first sentence: nothing tells you. The schema moves, the policy stays, and the gap between them opens quietly. The only way you find out is by looking, on a schedule, at something that does not change unless you check it.

DEV Community: Veristria

Auditing a Supabase project in one afternoon

Auditing a Supabase project in one afternoon

The afternoon plan

Hour one: inventory the protection surface

Hour two: read what you found

Hour three: probe from outside

Hour four: storage, functions, keys

Where manual ends and monitoring begins

Common questions

Is one afternoon really enough?

Do I need production access, or is staging enough?

What if I find something alarming mid-afternoon?

How do I audit a project I can't run locally?

Should the audit include checking my auth configuration too?

Supabase RLS: A 200 Response Does Not Prove Data Exposure

A small verification matrix

What the result means

RLS policies do not fail. They drift.

RLS policies do not fail. They drift.

The shape of the drift

The four ways it happens

Why reading the migrations does not catch it

What to check, in order

The point

How to audit 90 days of platform refunds by hand

How to audit 90 days of platform refunds by hand

How to audit 90 days of platform refunds by hand

Scope and inputs

Extract: the queries

Transform: build the sheet

The two formulas

Worked rows

The disputes pass

What you will find

Why one pass is not enough

Frequently asked questions

Does this audit need write access to my Stripe account?

Can I do all of this from Dashboard CSV exports?

We settle in multiple currencies — what changes?

What about refunds still showing as pending?

Run the 90-day audit

Build your own fee-leakage detection with the Stripe API

Build your own fee-leakage detection with the Stripe API

Build your own fee-leakage detection with the Stripe API

Architecture in one diagram

Scoping credentials safely

Pulling the corpus

Expectation functions

Turning diffs into findings

Scheduling, cursors, idempotency

The event-driven variant

Maintenance reality

Frequently asked questions

Why Math.round specifically?

Do zero-decimal currencies break the formulas?

Why not compute expectations inside Sigma?

How should the scanner treat pending refunds?

When exactly do I add Idempotency-Key headers?

Run the free 90-day audit

Your NEXT_PUBLIC secret is already in the browser bundle

Problem

How it works (mechanism)

Detection (KeyDrift)

Fix

1. Keep the secret on the server

2. Call the server from the client

3. Remove NEXT_PUBLIC_ prefixes for real secrets

4. Verify with KeyDrift

Caveats

Your Supabase Storage bucket is public - signed URLs will not save you

Stripe Connect refunds: the two flags that silently cost platforms

What a lost dispute actually costs a Connect platform

What Stripe does when you lose

Why this is not symmetrical with refunds

Three things that make this accumulate quietly

The reconciliation query

The policy question underneath the technical one

The Stripe Connect refund that only costs the platform

What a destination charge actually is

What about refunds still showing as `pending`?

3. Remove `NEXT_PUBLIC_` prefixes for real secrets