DEV Community: Veristria

The complete anatomy of a Stripe Connect refund

Veristria — Mon, 28 Sep 2026 22:34:48 +0000

The complete anatomy of a Stripe Connect refund

A single refund can move five different API objects and debit either the platform balance or a connected account's balance, depending on the Connect charge type behind it. This piece walks one $100.00 refund through direct charges, destination charges, and separate charges and transfers, so platform engineers and finance ops can predict every hop before issuing it.

The objects inside one refund

A refund is one API call, but it is never one object. Issuing one writes a new Refund, updates the Charge, updates the wrapping PaymentIntent, and — depending on parameters and charge type — reaches into Transfer and ApplicationFee children as well. Fix the cast before tracing balances:

Charge — the record of the original payment, carrying amount, amount_refunded, and the boolean refunded once nothing is left to return.
Refund — the payback record: amount, status, and the balance_transaction describing the debit.
PaymentIntent — the lifecycle wrapper around the charge attempt; it tracks cumulative refunds and is the handle most integrations pass to the Refunds API.
Transfer — funds moved from the platform to a connected account. Exists only in the destination and separate patterns.
TransferReversal — a child of a Transfer that pulls some or all of those funds back to the platform.
ApplicationFee — the platform's fee on a direct or destination charge, held as its own object with amount and amount_refunded.
ApplicationFeeRefund — a child of the ApplicationFee, created when the fee is returned.

Where each object sits depends on the charge type. Direct charges are created on the connected account through the Stripe-Account header, so the Charge, Refund, and PaymentIntent live there. Destination charges are created on the platform with transfer_data[destination], and separate charges pair a platform Charge with decoupled Transfers, so those objects sit on the platform. The table below maps each object to its owning account side and states whether a default refund call touches it.

Object	Lives on	Touched by a default refund?
Charge	Connected account (direct); platform (destination, separate)	Yes, marked refunded
Refund	Same account as the Charge	Created by the call itself
PaymentIntent	Same account as the Charge	Updated with refund totals
Transfer	Platform (destination, separate only)	No
TransferReversal	Platform, child of the Transfer	No
ApplicationFee	Platform-side record tied to the connected account's charge	No
ApplicationFeeRefund	Platform, child of the ApplicationFee	No

Two absences in that table matter as much as the rows. A direct charge has no Transfer, so there is nothing to reverse. Separate charges and transfers create no ApplicationFee objects, because the platform collects its cut by transferring less. A default refund therefore moves exactly three objects — Charge, Refund, PaymentIntent — and everything else waits for explicit flags or follow-up calls (Refunds API).

The objects also reference each other, which is what makes programmatic auditing possible. A Refund points at its charge and usually its payment_intent. An ApplicationFee carries charge and account. A Transfer created by transfer_data traces back to the originating charge, and every TransferReversal carries a source_refund when a refund caused it. Follow those pointers and a single refund ID expands into the entire money trail.

Who funds the buyer

The buyer is always made whole against the original payment method. The open question is whose Stripe balance funds the payback and what happens when that balance runs short. The table below summarizes debit behavior per charge type; the paragraphs after it expand on the edge cases.

Charge type	Balance debited on refund	If that balance is short
Direct	Connected account's available balance	Refund enters status `pending` and processes automatically once funded
Destination	Platform balance	Pending or failed; with a reversal attached and a depleted connected account, the API returns an error instead of creating a pending refund
Separate	Platform balance	Pending or failed; transfers are never touched

Three facts drive the table. First, refunds draw only on the available balance — money still sitting in pending cannot fund them, which is why payout timing (charges land in pending and become available on a rolling schedule) shapes what a refund can do (refunds, payouts). Second, Stripe states plainly that "Stripe's processing fees from the original transaction aren't returned," which is why every ledger below retains the processing cost somewhere. Third, the debit target differs by construction: Stripe debits the connected account directly for direct charges, and debits the platform balance for destination and separate charges (connect charges compared).

The direct-charge shortfall path is patient. If the connected account's available balance cannot cover the refund, Stripe creates the refund anyway with status pending and processes it automatically once payouts or new payments fund the account.

The destination path is strict when a reversal rides along. A plain destination refund simply debits the platform. But if the request includes reverse_transfer=true and the connected account lacks the funds to give back, Stripe returns an error rather than creating a pending refund. That asymmetry is useful: you learn at refund time that recovery is impossible, instead of discovering a pending refund that will quietly strand the money with the seller.

Separate charges are the indifferent case: refunding the charge has no effect on any transfer, ever. Recovery is a separate, manual act covered below.

One configuration changes none of this debit logic. With on_behalf_of, settlement moves to the connected account's country and currency, their statement descriptor applies, and country-specific fees kick in — yet for destination and separate charges the platform balance is still debited for refunds and disputes (connect charges compared). Whose descriptor the buyer sees has no bearing on whose balance absorbs the refund.

Failure handling closes the loop. A failed refund returns the funds to your balance, within up to about 30 days, with failure_balance_transaction and failure_reason populated and a refund.failed event fired. Track that balance transaction back into your books so the returned funds are not counted twice. For destination charges, failed or canceled refunds deposit back into the platform balance, since the platform funded them in the first place.

Direct charges, walked through the ledger

Assumptions for every ledger in this piece:

US platform, USD throughout.
Stripe's standard US card pricing assumed: 2.9% + $0.30, per Stripe's published pricing.
Charge of $100.00 (10000 cents) carrying application_fee_amount of $10.00 (1000 cents).
Stripe's processing fee billed to the connected account, the standard direct-charge arrangement.

The processing fee follows from the assumption: 2.9% of $100.00 is $2.90, plus the $0.30 fixed component, giving $3.20 (320 cents).

Sale. The connected account receives the payment net of the processing fee and your fee:

gross charge $100.00
processing fee -$3.20
application fee to you -$10.00
seller net $86.80

Your platform holds $10.00 of fee income. Conservation check: $86.80 + $10.00 + $3.20 = $100.00.

Full refund, defaults. You call the Refunds API with no flags. Stripe debits the connected account's available balance by the full amount and the application fee stays with you. Stripe's documentation is explicit: "Application fees aren't automatically refunded when issuing a refund. Your platform must explicitly refund the application fee or the connected account—the account on which the charge was created—loses that amount" (direct charges).

seller before $86.80
refund debit -$100.00
seller after -$13.20
your fee kept +$10.00

The seller is now negative by exactly the two costs Stripe does not return: the $10.00 fee you kept and the $3.20 processing fee. Conservation: -$13.20 + $10.00 + $3.20 = $0, with the buyer whole.

Full refund with refund_application_fee=true. The flag pushes the fee back to the connected account:

seller -$13.20
fee refund +$10.00
seller final -$3.20
your position $10.00 - $10.00 = $0.00

The seller now absorbs exactly the non-returned processing fee and you break even. That is the honest floor for a direct-charge platform on a full refund: someone pays Stripe $3.20, and by default it is your seller.

Partial refund of $40.00 with the flag. Proportionality applies to the fee as well. The fee refund is 40% of $10.00:

fee share 0.40 x $10.00 = $4.00
seller -$40.00 + $4.00 = -$36.00
your position $10.00 - $4.00 = $6.00

The seller again eats the unreturned slice of the processing fee, scaled to the refund. Every direct-charge refund, full or partial, is a choice between these two shapes.

Destination charges, walked through the ledger

Keep the same assumptions and add transfer_data[destination] pointing at the seller, with application_fee_amount of 1000 cents (what a destination charge is). The transfer to the seller defaults to the charge amount minus the application fee: $100.00 - $10.00 = $90.00.

Sale. Two platform-side balance transactions appear immediately:

charge net of processing +$96.80 (10000c - 320c)
transfer to seller -$90.00
platform margin $6.80

The seller's connected balance gains $90.00 and Stripe retains $3.20. Conservation: $6.80 + $90.00 + $3.20 = $100.00.

Full refund, defaults. Both flags false. Per Stripe's destination-charge refund documentation, the destination account keeps the transferred funds and the platform covers the refund (destination charges):

margin $6.80
refund -$100.00
platform position -$93.20
seller +$90.00 (unchanged)

Conservation: -$93.20 + $90.00 + $3.20 = $0. This is the default leak: the buyer is made whole, the seller keeps everything, and the platform funds the difference. Partials scale the same mechanics down — with the flag set, Stripe reverses a proportional slice of the transfer rather than the whole thing, computed against the refunded fraction of the charge. Platforms that discover this pattern months late are the reason destination-charge refund accounting deserves its own runbook; FeeGuard's detectors look for exactly this signature — platform-funded refunds with transfers left standing (more on the leak).

Full refund with both flags true. Now reverse_transfer=true and refund_application_fee=true ride together:

margin (from sale) +$6.80
refund -$100.00
transfer reversal +$90.00
platform subtotal -$3.20
application fee refund -$10.00
platform final -$13.20
seller: $90.00 - $90.00 + $10.00 = +$10.00

Conservation: -$13.20 + $10.00 + $3.20 = $0, buyer at zero. Read the seller line twice. The reversal claws back the $90.00, then the fee refund hands $10.00 straight back. Application fee refunds compensate the connected account, never the buyer, so on destination charges the both-flags reflex over-compensates your seller by the full fee on every full refund. Set the flags deliberately, per refund policy, not by habit.

A useful policy test before standardizing: ask who should be $10.00 richer after a fully refunded $100.00 sale. If the answer is nobody, refund_application_fee belongs only in cases where you intend to compensate the seller explicitly, not in your default refund path.

Separate charges and transfers, walked through the ledger

New inputs: the $100.00 charge sits on your platform and you separately transfer $70.00 to the seller. Your margin is $96.80 - $70.00 = $26.80. Stripe's documentation is blunt about refunds: "refunding a charge has no impact on any associated transfers. It's up to your platform to reconcile any amount owed back to it by reducing subsequent transfer amounts or by reversing transfers" (separate charges and transfers).

Full refund, defaults. Nothing automatic reaches the transfer:

charge net +$96.80
transfer -$70.00
refund -$100.00
platform position -$73.20
seller +$70.00 (untouched)

Conservation: -$73.20 + $70.00 + $3.20 = $0. You are out the buyer's $100.00 minus the processing fee you already retained, and the seller is up $70.00 until you act. The corrective move is a transfer reversal, and it carries a hard precondition: the connected account's available balance must cover the reversal amount or the call fails. If it succeeds, +$70.00 returns and the platform rests at -$73.20 + $70.00 = -$3.20 — the permanent processing-fee residue. Reducing subsequent transfer amounts instead of reversing works too, and sidesteps that balance gate, at the price of running the reconciliation yourself. Note also that when an async payment method fails on this pattern, Stripe does not reverse anything either; every recovery here is manual.

The two flags that decide where the money goes

Everything above reduces to two booleans on the Refunds create call. The table lays out their behavior side by side.

Parameter	What it does	Proportional?	Who can set it	Destination default	Direct default
`refund_application_fee`	Returns the application fee, landing back with the connected account	Full refund returns the full fee; partial returns a proportional share	Only the application that created the charge	false	false
`reverse_transfer`	Creates a TransferReversal against the original Transfer	"The transfer will be reversed proportionally to the amount being refunded"	Only the application that created the charge	false	false

Two quotations anchor the restrictions, both from the parameter reference. On fees: "An application fee can be refunded only by the application that created the charge." On transfers: "The transfer will be reversed proportionally to the amount being refunded."

Defaults deserve emphasis: both flags are false everywhere, for every charge type. Silence is the expensive option. Three operational notes follow:

On destination charges, refunding the fee requires reversing the transfer in the same request; you may instead leave the flag false and refund the fee separately through the Application Fees Refund API afterwards (flag explainer).
On direct charges, reverse_transfer is inert — no transfer exists to act on (flag explainer).
On separate charges, neither flag finds an object to touch; recovery is always a standalone reversal against the transfer you chose to send.

Treat both flags as policy encoded per request, not global configuration. Teams that standardize them inside one refund service — with the charge type deciding the defaults — stop relitigating this arithmetic every time support issues a refund by hand.

Events and statuses to watch

A refund is asynchronous machinery wearing a synchronous-looking interface, so wire bookkeeping to events rather than HTTP responses. Statuses traverse a small machine: succeeded, pending, failed, canceled, requires_action. Pending means Stripe is waiting — usually on funds or a slow payment method. Failed and canceled mean the payback aborted and the money lands back in your balance. Requires_action means the customer must complete a step before the payment method accepts the refund.

One nuance saves support tickets: a refund issued shortly after the charge may settle as a card-network reversal rather than a standard refund. The signal is destination_details[card][type] reading reversal instead of refund. It is cheaper on the network side and produces no acquirer reference number (refunds).

The table below lists the events relevant to refund-time money movement and what to verify in each handler (event list). Webhook delivery is at-least-once, so handlers should tolerate duplicates.

Event	Fires when	What to check
`refund.created`	A refund request is accepted	Amount, currency, metadata tags for reconciliation
`refund.updated`	The refund's status changes	Transitions out of `pending`; age of stuck refunds
`refund.failed`	Processing fails; funds return to your balance	`failure_reason`, `failure_balance_transaction`, re-issue decision
`charge.refunded`	The charge is refunded, partially or fully	Cumulative `amount_refunded` against your order system (event explainer)
`application_fee.refunded`	A fee is refunded by flag or by the fee-refund API	Refunded amount equals the proportional expectation
`transfer.reversed`	A transfer is reversed in whole or part	Sum of reversals against the expected proportional amount

Do not expect transfer.reversed without cause — outside the flagged refund path and async-failure cleanup on destination charges, reversals happen only when you call for them (can Stripe reverse transfers automatically?). Stripe has deprecated the older charge.refund.updated event; migrate anything still listening to refund.updated. For timing-sensitive reconciliation, subscribe to balance.available as well: it marks the moment pending funds become usable, which is exactly when a stuck pending refund becomes fundable.

Frequently asked questions

Does Stripe reverse the transfer automatically?

Only in specific cases. On destination charges, passing reverse_transfer=true makes Stripe create the reversal as part of the refund — the entire transfer for a full refund, a proportional slice for a partial one. When an async payment method fails after a destination charge settles, Stripe reverses the transfer on its own. Separate charges and transfers never see an automatic reversal, and direct charges have no transfer to reverse.

Why did the connected account go negative after a refund?

Because the charge was direct. Refunds on direct charges debit the connected account's available balance, and the application fee stays with you unless you passed refund_application_fee=true. A negative balance pauses payouts until future payments offset it, and Stripe attempts a debit of the account's external bank account only when debit_negative_balances is enabled for supported regions (account balances).

Who receives an application fee refund?

The connected account, always. Refunding a fee pushes the fee funds back to the connected account — it never reroutes money to the buyer. On destination charges this is why refunding the fee without reversing the transfer distorts the ledger: the seller pockets the fee on top of the transfer you failed to claw back.

What if the platform balance cannot cover the refund?

Refunds draw on the available balance only. Card refunds that cannot be funded sit in pending and complete automatically once the balance recovers. Some payment methods cannot pend and fail instead; a failed refund returns the funds to your balance within up to about 30 days, leaving failure_reason and failure_balance_transaction behind so you can decide whether to re-issue.

Run the 90-day audit

Every figure in this piece is reproducible from your own Stripe data: charges, refunds, transfers, reversals, and fee objects joined by ID. FeeGuard runs exactly that join. Point a restricted, read-only API key at your platform and the free audit reads your last 90 days of Connect activity, reporting every unreclaimed application fee, unreversed transfer, and uncovered dispute loss with the amounts attached. You get the complete answer first; ongoing monitoring that catches each new occurrence as it happens 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.

GRANTs versus RLS: two permission systems, one database

Veristria — Mon, 28 Sep 2026 09:27:27 +0000

GRANTs versus RLS: two permission systems, one database

Supabase tables sit behind two permission systems at once: SQL grants and row-level security. They layer rather than replace each other, fail with different symptoms, and are routinely confused. This article maps the interaction with runnable tests for every claim.

Ask "who can read this table?" about a Supabase project and you've actually asked two questions wearing one coat. Postgres answers access through grants — table-level privileges like SELECT granted to roles — and through row security policies, which filter rows per command per role. Both systems must permit an action; either alone can block it; and they fail so differently that knowing which gate refused you is most of debugging.

Confusion between the layers is endemic because Supabase provisions sensible defaults: client roles arrive pre-granted on public-schema tables, so teams live entirely in policy-land and forget grants exist — until a revoke, a new role, or a fresh environment makes the forgotten layer bite. This article separates the two systems, shows their interaction with verified SQL, and gives you the diagnostic habit that tells you instantly which gate said no.

Everything below was executed against current Postgres during preparation; expected results are stated before each test, in the site's standard style. By the end you'll be able to read any access mystery — empty results, permission errors, tables that work in one environment and fail in another — as a question about one specific gate.

Two gates, checked in order

When a query arrives under a given role, Postgres consults two independent systems sequentially — and their verdicts compose multiplicatively: both must say yes for anything to happen. Neither can substitute for the other, which is why the order of checks below determines not just whether your query runs but what kind of failure you see when it doesn't:

1. Privilege check: does this role hold SELECT on this table?
 No -> ERROR: permission denied (42501 class)
 Yes -> continue
2. Row security: do policies admit each row?
 RLS disabled -> all rows pass
 RLS enabled -> rows failing USING are silently filtered

The critical asymmetry is in the failure shapes. A missing grant is loud — the query errors, naming the table and privilege. Missing or restrictive policies are quiet — empty results, no error, indistinguishable from absent data. Loud failures get noticed and fixed immediately; quiet ones can persist for months. That's why the interaction matters less than the symptom mapping: errors mean grants, empties mean policies, and every mystery in between is usually both layers interacting with assumptions nobody wrote down.

The interaction matrix

All four combinations, each verified during this article's preparation — this matrix is the article's centerpiece, and worth internalizing until you can reconstruct it from memory:

Grant	Policy situation	Result	Symptom
Yes	Permissive policy admits row	Row returned	Normal operation
Yes	Policies deny (or none exist)	Empty result	Silent; looks like missing data
No	Any policy state	Permission denied error	Loud; blocks feature visibly
No	RLS also disabled	Permission denied error	Grants were protecting you

Read row four twice, because it carries a surprise: a table with RLS disabled but grants revoked still refuses client reads. Teams discovering this sometimes conclude grants are redundant with RLS — the opposite lesson is correct. Default-deny grants on unexposed schemas are legitimate defense-in-depth; what makes Supabase's default posture work is that exposed-schema tables get client grants provisioned automatically, making RLS the single active filter in normal operation.

What each system is actually for

The two systems aren't redundant because they operate at different granularities for different purposes:

Dimension	Grants	Row security policies
Granularity	Whole table (or columns)	Individual rows
Expression	Privilege names (`SELECT`, `INSERT`)	Arbitrary boolean expressions
Failure shape	Error, every time	Silent filtering
Natural audience	Database administrators	Application developers modeling access
Change cadence	Rarely — structural	Often — follows product features

Grants answer "may this role touch this object at all" — a database-administration question, answered rarely, changed deliberately. Policies answer "which rows of this object may this caller see or change right now" — a product question, answered per query, evolved continuously. Supabase's design leans into exactly this division: provision broad grants once, then do all real access modeling through policies that developers can read and migrate like code.

The trouble starts when teams use one system to do the other's job. Modeling row access through grants is impossible — there's no WHERE clause. But modeling structural decisions through policies happens constantly: revoking table access via deny-all policies instead of explicit grants, or worse, leaving grants wide while assuming policies cover internal tables nobody wrote rules for yet. Each system left doing the other's work produces confusion that surfaces months later as either mysterious errors (grants) or silent exposure (policies).

The tests, runnable as printed

Create the fixture and prove each cell:

create table gate_demo (
 id int primary key,
 note text not null
);

insert into gate_demo values (1, 'visible'), (2, 'also visible');

alter table gate_demo enable row level security;

grant select on gate_demo to authenticated;

create policy "gate_demo_all"
 on gate_demo for select
 to authenticated
 using (true);

Cell one — both gates open: rows return.

begin;
set local role authenticated;
select count(*) as both_open from gate_demo;
rollback;

Expected as executed: 2.

Row three — grant removed, loud failure:

revoke select on gate_demo from authenticated;

begin;
set local role authenticated;
select count(*) from gate_demo;
rollback;

Expected: ERROR: permission denied for table gate_demo — an exception, not an empty set. Restore with grant select ... before continuing.

Row two — grant restored, policy tightened to deny:

drop policy "gate_demo_all" on gate_demo;
-- zero policies remain: default deny

begin;
set local role authenticated;
select count(*) as deny_all from gate_demo;
rollback;

Expected: 0, no error. Same table, same grant, opposite symptom class from the previous test — that contrast is the entire diagnostic skill.

The anonymous variant, since public surfaces deserve their own proof:

begin;
set local role anon;
select count(*) from gate_demo;
rollback;

Expected: 0 — anon holds no grant issues (default provisioning covers it) but no policy targets anon, so default deny filters everything. Swap any element — revoke the grant, or add an anon policy with using (true) — and watch which symptom appears. Running these five variations against your own tables takes minutes and produces a complete behavioral fingerprint of both gates.

One more interaction surprises people: writes need sequence rights too when tables use serial-style defaults. Supabase's default privileges cover sequences alongside tables, so PostgREST inserts just work — but hand-rolled roles in fresh environments can hit insert failures whose fix (grant usage on sequence) has nothing to do with either RLS or the obvious table grant. When an INSERT fails with permission denied mentioning a sequence name, this is why — and it is a grant problem, not a policy problem, which is exactly the distinction our direct-grants finding documents.

How Supabase composes the two by default

Understanding defaults explains why the platform feels policy-centric:

New public-schema tables receive client-role grants automatically via default privileges — so grants are effectively always-passing unless you changed them.
RLS is opt-in per table, so the only active gate on a fresh unprotected table is... neither: grants pass, RLS off, rows flow.
Once RLS enables, policies become the sole meaningful filter for client roles, and grants fade into background plumbing — which is also why a table can end up RLS disabled without anyone noticing until an outside check runs.

This composition is coherent — it makes protected tables work out of the box and keeps API behavior predictable — but it concentrates attention entirely on policies. The residual risks live at the edges: tables where someone revoked grants (features break loudly), environments where default privileges differ (staging behaving unlike production), and roles beyond the standard trio whose grants nobody reviewed since creation.

The mechanism behind those defaults is worth knowing by name, because it's also the tool for changing them: ALTER DEFAULT PRIVILEGES. Supabase uses it to pre-grant client roles on future tables; you can inspect or extend it:

select pg_get_userbyid(defaclrole) as granting_role,
 defaclnamespace::regnamespace as schema,
 defaclacl
from pg_default_acl;

Every row is a standing instruction — "whenever a table appears in this schema, grant these privileges to these roles." Teams adding custom roles (an analytics reader, an integration account) should add their own default-privilege rows rather than remembering per-table grants, which keeps new tables automatically consistent with intent instead of relying on migration authors copying grant statements forever.

Diagnosing which gate refused you

The symptom-to-cause table that saves debugging sessions:

Symptom	Refusing gate	First check
`permission denied for table X`	Grants	`has_table_privilege(role, table, 'SELECT')`
`permission denied for schema`	Schema usage grant	Grant USAGE on the schema
`permission denied for sequence`	Sequence grant behind serial column	Grant usage on the sequence
Query succeeds, returns fewer rows than expected	RLS policies filtering	Read policies for caller's role
Query succeeds, returns everything	RLS disabled or tautology	Catalog check: flags and quals

The first three produce errors pointing at their own names; only the last two require interpretation, and both resolve to reading the same catalog view you already know:

select c.relname,
 c.relrowsecurity as rls_enabled,
 has_table_privilege('authenticated', c.oid, 'SELECT') as auth_can_select
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;

Two boolean columns per table — grant and flag together — answer "is this table's silence a policy question or a privilege question" for your whole schema at once. Add the policy count subquery from earlier audits when the answer needs depth.

A quick story makes the diagnostic stick. A team reports "the dashboard shows no data but there are no errors." Error-free plus empty means policies filtering — or grants fine, since errors would appear otherwise. Their policy dump shows a correct-looking select policy... targeting service_role by mistake in a copy-paste. The caller is authenticated; no policy matches that role; default deny filters everything; zero errors. One catalog read, one wrong role list, fixed by editing one word. Before learning this mapping, the same team had spent two days on cache-busting and client refactors.

Practical rules for living with both

Leave Supabase's default grants alone for client-facing tables; express access control through policies, which is where row-level decisions belong and where review tooling looks.
Use revokes deliberately for defense-in-depth on internal tables — but document them, because revokes are invisible in application code and surprising in fresh environments.
Never treat grants as your access control for exposed tables. They're all-or-nothing per table; they bypass nothing about row scoping; and they're one accidental re-grant away from irrelevance. Policies carry the actual model. For the full inventory habit, pair it with listing every RLS policy after environment changes.
Test both gates after environment changes: one query per role per sensitive table catches grant drift that policy reviews never see. Grants fail loud, policies fail quiet - and a five-line test script covering both takes minutes to run against any environment.

A fifth rule ties the set together: when documenting your authorization model, document both layers explicitly — a one-line note per table stating "grants: default provisioning; policies: the four-command ownership set" prevents future contributors from guessing which system carries which decision. The documentation cost is minutes; the debugging it prevents is measured in evenings.

Grants and RLS aren't competitors; they're different altitudes of the same air-traffic system. Grants decide which planes may enter the airspace at all; policies decide where each may fly once inside. Confusing them produces either empty skies or collisions — while understanding them produces systems where both layers quietly do their jobs. For the vocabulary of each layer, the RLS glossary keeps terms straight.

Common questions

If RLS is enabled, do grants even matter?

Yes, as a precondition: without the grant, queries error before policies evaluate. In normal Supabase operation the default provisioning makes grants a non-issue — which is precisely why anomalies (revokes, custom roles, new environments) surface as confusing errors. Check grants whenever an error mentions permission, and check policies whenever results look wrong.

Why did my staging environment behave differently from production?

Default privileges are established per-database by the provisioning process. Environments built differently — manual restores, partial dumps, recreated roles — can end up with divergent grants, so identical policies yield different behavior. When staging and production disagree on the same schema, diff both the policies and the grants before touching code.

Do column-level grants interact with RLS usefully?

They stack too, and sometimes helpfully: GRANT SELECT (id, title) ON documents TO anon narrows visible columns at the privilege layer while policies filter rows — two dimensions from two systems in one request. The operational caveat is forgettability: column grants are invisible in application code and easy to contradict when schemas grow. Many teams prefer narrowed views for public column scoping instead, keeping all row-and-column logic in one reviewable place.

Should I revoke the default grants and manage everything explicitly?

That trades convenience for control at real cost: every table needs explicit grants forever, every new role needs its grants enumerated, and mistakes become loud errors rather than quiet drift. The safer default is to leave grants defaulted and put all access modeling into policies — reserving revokes for specific internal tables where belt-and-suspenders genuinely helps.

Where do function execute privileges fit?

Functions carry their own EXECUTE privilege, granted to PUBLIC by default — which means every function in your schema is callable by every role unless revoked. For definer functions that elevate privileges, that default deserves review: revoke PUBLIC execute and grant explicitly to the roles that should call it. It's the grant-side twin of policy hygiene, and one of the highest-yield items in a manual audit.

Unsure which gates your tables are actually running? Run the free scan — paste your app URL and see the outside-visible consequences of both layers, findings included.

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

From weekend prototype to production: hardening a Supabase app

Veristria — Fri, 25 Sep 2026 09:05:36 +0000

From weekend prototype to production: hardening a Supabase app

The gap between a working prototype and a defensible product is a checklist, not a rewrite. This article gives founders and developers that checklist in order - with every row-level-security step shown as complete SQL - so nothing gets skipped because nobody knew it existed.

Prototypes are honest about features and silent about posture. Your app creates accounts, saves data, renders dashboards — and none of that says anything about what happens when someone other than a happy user connects. Production readiness, for the database layer, means being able to answer three questions without hedging: who can read each table, who can write into whose rows, and how you know both answers are still true after the next deploy.

This article is that answer, sequenced. Each stage builds on the last; every SQL block runs as printed (each was executed during preparation); and the whole sequence fits inside a focused day for a small app. It assumes the prototype already works — auth flows included — because hardening a moving feature set wastes effort; freeze features for the day if you can. The companion piece on prototype-to-production specifics covers platform concerns beyond authorization; here we go deep on the layer that protects your users from each other.

Stage zero: define what you're protecting

Before running queries, spend ten minutes writing down the access model in plain sentences. Per table: who reads it? Who creates rows, and can they only create their own? Who updates or deletes, and under what limits? Which tables are legitimately public, and with what filters?

This sounds bureaucratic. It isn't — it's the spec your policies will implement, and having it written turns later stages from judgment calls into conformance checks. A three-table notes app needs three sentences. A marketplace needs a page. Either way, write it before touching the schema, because the most common hardening failure isn't technical at all: it's discovering mid-migration that nobody ever decided whether collaborators should see drafts.

The sentences also become your review artifacts. When a stakeholder asks "can users see each other's data?", the answer stops being a shrug and becomes a quote from a document — plus, eventually, a pointer at passing tests. Founders who write this page once reuse it in security questionnaires, enterprise sales calls, and their own 2 a.m. incident triage; the words cost minutes and keep paying.

Stage one: inventory and triage

Now measure reality against the model you just wrote. The catalog produces the gap analysis:

select c.relname,
 c.relrowsecurity as rls_enabled,
 count(p.policyname) as policy_count
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_policies p
 on p.schemaname = n.nspname and p.tablename = c.relname
where n.nspname = 'public' and c.relkind = 'r'
group by c.relname, c.relrowsecurity
order by c.relname;

Sort the output into three buckets by risk rather than alphabetically:

Bucket	Signature	Action this stage
Open windows	`rls_enabled = false`	Fix first — exposure is live
Locked rooms	enabled, `policy_count = 0`	Write policies in stage two
Claimed territory	enabled with policies	Verify against your model in stage five

Prototype projects typically land almost entirely in the second bucket, sometimes with a couple of open windows where an assistant-generated migration skipped protection. Nothing here requires blame; the catalog doesn't do blame, it does state.

Triage discipline for this stage: don't fix while triaging. The temptation is to enable RLS on the first open table you see — but enabling before its policies exist breaks whatever feature uses it, mid-inventory, and now you're debugging your own hardening. Complete the three-bucket sort first; then work buckets in order with the full picture in hand. Open windows do jump the queue, but they jump into stage two's process rather than skipping it.

Stage two: write the policy set, completely

For each user-owned table, this is the full pattern — four policies covering all four commands, both clauses wherever both apply. The blocks from here on run against the prototype's two working tables; if you are following along in a scratch database rather than your own project, create them first:

create table documents (
 id uuid primary key default gen_random_uuid(),
 workspace_id uuid not null,
 title text not null,
 owner_id uuid not null
);

create table workspace_members (
 workspace_id uuid not null,
 user_id uuid not null,
 primary key (workspace_id, user_id)
);

alter table documents enable row level security;

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

create policy "documents_insert"
 on documents for insert
 to authenticated
 with check ((select auth.uid()) = owner_id);

create policy "documents_update"
 on documents for update
 to authenticated
 using ((select auth.uid()) = owner_id)
 with check ((select auth.uid()) = owner_id);

create policy "documents_delete"
 on documents for delete
 to authenticated
 using ((select auth.uid()) = owner_id);

Details worth understanding rather than copying blindly:

Both halves of update matter. USING decides which existing rows may change; WITH CHECK decides whether the resulting row stays legal. With only USING, users reassign ownership — moving rows out of their scope into someone else's account — in one successful statement. The check clause closes exactly that door.

Insert has no USING. There's no prior row to test, so the entire contract lives in WITH CHECK. Requiring the resulting owner_id to match the caller's verified identity means users cannot plant rows attributed to others, even ones they'll never be able to read afterward.

A naming convention pays for itself immediately: <table>_<command> (or with an audience suffix when several policies legitimately coexist) makes every future catalog dump self-describing, and mismatched names become visible drift signals. Add a one-line comment on policy statement per policy capturing the intent sentence from stage zero — the next reviewer, including you in six months, reads comments before clauses.

Tables shared across a workspace extend the select branch with a membership subquery instead of replacing ownership. The composite reads like this:

-- Widen the existing policy in place. ALTER, not DROP-then-CREATE:
-- dropping would leave the table with no select policy for a moment.
alter policy "documents_select"
 on documents
 using (
 (select auth.uid()) = owner_id
 or exists (
 select 1 from workspace_members m
 where m.workspace_id = documents.workspace_id
 and m.user_id = (select auth.uid())
 )
 );

alter table workspace_members enable row level security;

create policy "workspace_members_select_own"
 on workspace_members for select
 to authenticated
 using (user_id = (select auth.uid()));

Ownership still admits rows directly; membership adds the sharing dimension on top. The membership table needs its own read policy — the second statement above — so users can see their own memberships without seeing everyone's. That dependency matters more than it looks, because subqueries evaluate under the caller's policies too: a members table with RLS enabled and no select policy silently empties every EXISTS branch built on it, with no error anywhere. Tables that are genuinely public get a deliberate anon-targeted policy with its filters, never an accident of omission; anonymous access done deliberately covers that design in full.

Stage three: performance hygiene while you're in there

Policies run on every query forever; make them cheap once:

create index documents_owner_id_idx on documents (owner_id);

Index every column a policy compares against — owners, tenants, workspace keys. Wrap identity calls as (select auth.uid()) so Postgres evaluates them once per statement instead of per row; the plan-level difference is documented in our performance guide. If any table carries restrictive gates, confirm they reference indexed columns too. Fifteen minutes here prevents the classic arc where adding RLS quietly degrades every list view until somebody blames the framework.

The habit generalizes: policy columns deserve the same indexing attention as foreign keys, because they are foreign keys in disguise — predicates matching user identifiers against stored columns on every single query. Where one index serves both a foreign key and its policy, that's one index doing two jobs; where they diverge, add what's missing and let EXPLAIN arbitrate.

Also decide now about FORCE ROW LEVEL SECURITY: enabling it subjects the table owner to policies as well, which matters when backend scripts share the owner role with humans doing dashboard surgery. For tables where even privileged mistakes must respect isolation, add the line — it costs nothing and removes a standing exception you'd otherwise have to remember.

Verification for this stage is one query per sensitive table, run as a test persona:

begin;
set local role authenticated;
select set_config('request.jwt.claims', '{"sub":"11111111-1111-1111-1111-111111111111"}', true);
explain (costs off)
 select * from documents where workspace_id = 'aaaaaaaa-0000-0000-0000-00000000000a';
rollback;

Reading the plan: you want the policy predicate to appear in an Index Cond (policy served by index), not merely a Filter after a sequential scan. If the JWT-derived value appears inline in a per-row Filter, the (select ...) wrapper is missing; if there's no index candidate at all, stage three isn't done. Two minutes of plan-reading per table now buys years of flat latency later.

Stage four: storage and server paths

Two surfaces outside table RLS routinely undo an otherwise hardened app:

Buckets. Every bucket gets an explicit visibility decision and, for private buckets, object-level policies scoped by path. The standard own-folder pattern keeps users inside a prefix derived from their identity:

create policy "avatars_read"
 on storage.objects for select
 to authenticated
 using (bucket_id = 'avatars');

create policy "avatars_upload_own_folder"
 on storage.objects for insert
 to authenticated
 with check (
 bucket_id = 'avatars'
 and (storage.foldername(name))[1] = (select auth.uid())::text
 );

Uploads into another user's folder now reject at the policy layer — verified behavior, and the same WITH CHECK thinking from table land applied to object paths. Public buckets are legitimate for genuinely public assets; audit them with the same suspicion as public tables, because public-bucket exposure is served without authentication by definition.

Service credentials. Inventory every place the service-role key exists: environment files, function configs, build artifacts. It belongs exclusively behind server boundaries, and its call sites should fit on one screen with justifications attached. If grep finds it anywhere client-reachable, that finding outranks everything else on this page — rotation guidance is in service-role leakage.

Run the inventory against built output, not just source: framework env prefixes, bundler plugins, and copied snippets have all shipped credentials that never appeared in a repository. The check is two greps and takes less time than explaining its absence afterward.

Stage five: prove it

Claims become claims-with-evidence through the two-account battery: anonymous reads return empty; forged inserts reject; cross-tenant reads return nothing; ownership transfers fail; deletes stay in scope. All five are copy-pasteable in the tenant-isolation playbook, with expected results stated per probe. Run them against staging first, then production — environments drift independently, and production is where the proof matters.

This is the stage most easily talked out of, because verification feels redundant when everything was written carefully an hour ago. That feeling is the point of failure: transcription errors, forgotten tables, and misread requirements survive careful work precisely because care does not check itself. Twenty minutes of battery beats hours of post-launch forensics on the one occasion it finds something.

Score the run as a table, because a table converts anxiety into worklist:

Probe	Pass looks like	Fail points at
Anonymous read of private table	`[]`	Missing RLS flag or anon policy
Forged-owner insert	HTTP 4xx	Insert policy without check
Cross-tenant read by ID	`[]`	Policy trusting client input
Ownership-transfer update	Rejected	Update missing WITH CHECK
Out-of-scope delete	Zero effect	Delete policy too wide

Every failure names its fix; every pass earns a line in your launch document. Convert every fix made during stages two through four into a denial test in your suite. Hardening that isn't regression-tested decays on the next busy sprint; tests make the posture survive its authors.

The launch-week trap: changes that bypass the checklist

Hardened apps regress through unchecklisted channels. Knowing them in advance is most of the defense:

Hotfix branches skip review under time pressure and ship schema changes directly — often with "temporary" policy relaxations nobody reverts.
Dashboard edits change policies from the SQL editor without touching git, so the repo's migrations no longer describe production.
Restores and environment copies replace current state with whatever existed at snapshot time, including pre-hardening protection.
"Quick" data fixes by teammates connecting as the owner role quietly establish a habit of bypass-grade access.

Each channel has the same signature: production state diverges from what stages two through five verified, with nothing in CI to notice. That's why the final stage isn't optional even though it comes after "prove it" — proof has a timestamp, and launch week is precisely when timestamps start expiring fastest.

Stage six: keep it true

Production readiness is a property maintained over time, not achieved once. Three habits carry it forward: the catalog snapshot joins CI so unprotected tables fail builds; policy changes require the same review as code changes; and monitoring watches between deploys for the changes that bypass pipelines entirely — restores, manual edits, hotfix branches.

Concretely, continuous coverage means knowing about four transition classes without anyone having to remember to look: RLS flags flipping on or off; policies appearing, disappearing, or widening (a new permissive policy is a union expansion); write policies losing their checks; and key material appearing where it doesn't belong. Human review catches these when it happens to be looking; automation catches them when they happen. RowShield exists to automate that class of watching, beginning with a free scan that establishes your baseline in minutes.

Ship when all six stages have answers written down. That document — brief, concrete, evidence-linked — is the real deliverable of hardening: not the absence of risk, but the presence of knowledge about which risks you've accepted and which you've closed.

Common questions

How long does this take for a small app?

A three-to-eight-table project typically completes all stages in a day: the inventory in minutes, policy writing in a couple of hours, tests and storage another hour or two. Larger schemas scale mostly in stage two, since the per-table pattern repeats.

Can I ship with some tables still locked-but-policyless?

Yes — enabled with zero policies is safe default-deny, appropriate for backend-only tables awaiting rules. Ship it deliberately and track it; the failure mode is months of interim becoming permanent, which is why the inventory belongs in CI rather than memory.

Do I need to redo this after every feature?

No — you need the inventory after every feature. New tables enter through the same pipeline (enable, write four policies, index, test), which takes minutes per table when it's habitual. The heavy work was making the pipeline exist; afterwards it's maintenance.

What's the difference between this and a security audit?

Scope and independence. This checklist covers your authorization layer self-serve; an audit adds external perspective, compliance framing, and breadth across infrastructure. They complement each other — teams that run this sequence arrive at audits with evidence organized and low-severity noise already cleared, which shortens both.

We use an ORM that generates policies too. Covered?

Partially. Generated policies deserve identical review — same tautology and missing-check risks, per the vibe-coded posture piece — but generation plus verification is strictly better than generation alone. The checklist above is the verification side.

Establish your baseline in minutes: run the free scan — paste your app URL and see exactly which stage your project is really at, findings included.

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

A taxonomy of platform fee leakage

Veristria — Thu, 24 Sep 2026 20:30:08 +0000

A taxonomy of platform fee leakage

This article answers one question for people who own the money on a Stripe Connect platform: exactly which configuration states silently forfeit funds you were entitled to keep or recover? The answer is a finite list of leak vectors, each with a deterministic cost signature and a concrete API check. Walk the checklist against your own account.

Leakage, defined mechanically

Leakage is money the platform was entitled to keep or entitled to recover that left your balance without an error, a log line, or an alert. Nothing failed. Every API call returned 200. The objects Stripe created are all consistent with what was requested — the problem is what was not requested.

Two things that look like leakage are not leakage, and keeping them separate matters when you size the problem:

Fraud losses. Radar scores, blocks, and reviews payments for fraud risk before and at payment time (Radar). Fraud is adversarial and probabilistic. Leakage is neither: it follows mechanically from flag defaults.
Processing fees you knowingly pay. Stripe's processing fees from the original transaction are not returned when you refund (Refunds). That is a published cost of doing business. It becomes leakage only when you lose additional money around it.

Everything in this article is in the second category's neighborhood: deterministic consequences of refund-time defaults on destination charges, direct charges, and separate charges and transfers. For marketplaces splitting one payment across several parties, the same vectors multiply per payee; we cover that variant in the marketplace refund-leak deep dive.

The master taxonomy

The table below lists every leak vector this site tracks, which charge patterns it affects, what the default does, what one occurrence costs, and how you detect it. Sections that follow expand each detection check into something runnable.

Vector	Charge patterns affected	Default behavior	Unit cost signature	Detection check
L1 Unreversed transfer on refund	Destination charges; separate charges and transfers	Destination account keeps transferred funds; separate-pattern refunds have no effect on transfers at all	Missing reversal up to the full transfer amount (full refund) or the uncovered share (partial)	Compare Σ reversals against `round((amount_refunded / charge.amount) × transfer.amount)`
L2 Unrefunded application fee	Direct charges; destination charges refunded without `refund_application_fee`	Application fees are kept by default on both patterns	`application_fee.amount_refunded` stays 0 while `charge.amount_refunded` grows	Join each ApplicationFee to its charge and compare refunded amounts
L3 Uncovered dispute loss	Destination charges; separate charges (any `on_behalf_of` setting)	Stripe debits the disputed amount plus the dispute fee from the platform balance; the seller keeps the transfer	Gap = `transfer.amount` − Σ reversals after the dispute closes as lost	List disputes with status `lost`, then audit the related transfer
L4 Non-proportional partial-refund reversal	Destination charges, partial refunds	Proportional reversal happens only if you request it; manual flat amounts under-recover	Expected proportional reversal − actual reversal amount	Same formula as L1, evaluated per partial refund
L5 Cross-border FX spread	Any pattern where charge currency differs from settlement currency	Refund converts at the live rate on refund day; the original conversion fee is not returned	Refund-day debit minus charge-day settlement value	Recompute expected refund debit from the charge-day rate and diff
L6 Out-of-band refund paths	All patterns	Dashboard or manual refunds bypass whatever flag policy lives in your code	L1/L2 signatures appearing only on refunds lacking your metadata conventions	Match every Refund object against your application's request logs
L7 Integration drift	Copied or legacy refund endpoints	A call site copied between charge types omits explicit flags and inherits wrong defaults	Leaks cluster at specific endpoints rather than randomly	Inventory every `refunds.create` call site; assert both flags are explicit

Three properties of this list matter. First, it is closed: these seven vectors exhaust the ways default refund behavior moves platform money to someone else, because refunds touch exactly three objects — the charge, any transfer, and any application fee. Second, each vector has a deterministic unit cost: no probabilities anywhere. Third, all seven are detectable from ordinary API reads with a restricted key; nothing here requires special access.

Walk the checklist yourself

Each check below runs against live data with the official stripe-node SDK and a restricted read-only key. The snippets use auto-pagination and stay read-only end to end. FeeGuard automates exactly these joins in its free audit, but the manual method is complete, so run it yourself first.

L1: find refunds whose transfer was never (fully) reversed

Group refunds by charge, sum what was refunded, compute the reversal the formula implies, and compare it to the reversals that actually exist:

import Stripe from 'stripe';

const stripe = new Stripe(process.env.STRIPE_SECRET_KEY);

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

function isString(value: unknown): value is string {
 return typeof value === 'string';
}

type Sale = { refunded: number; charge: Stripe.Charge };

const sales = new Map<string, Sale>();

await stripe.refunds
 .list({ created: { gte: cutoff }, limit: 100, expand: ['data.charge'] })
 .autoPagingEach((refund) => {
 const charge = refund.charge as Stripe.Charge;
 if (isString(charge.transfer)) {
 const prior = sales.get(charge.id);
 if (prior) {
 prior.refunded += refund.amount;
 } else {
 sales.set(charge.id, { refunded: refund.amount, charge });
 }
 }
 });

let totalMissing = 0;

for (const [chargeId, sale] of sales) {
 const transferId = sale.charge.transfer as string;
 const transfer = await stripe.transfers.retrieve(transferId);
 const reversals = await stripe.transferReversals.list(transferId, { limit: 100 });

 let reversed = 0;
 for (const reversal of reversals.data) reversed += reversal.amount;

 const expected = Math.round((sale.refunded / sale.charge.amount) * transfer.amount);
 const missing = expected - reversed;

 if (missing > 0) {
 totalMissing += missing;
 console.log(`${chargeId}: expected ${expected}, reversed ${reversed}, missing ${missing}`);
 }
}

console.log(`total under-reversed across window: ${totalMissing}`);

charge.transfer links a destination charge to its transfer; the separate-charges pattern has no such link, so its reconciliation starts from your own transfer records instead. Amounts are integer cents throughout. If any single transfer has more than 100 reversals, paginate transferReversals.list fully before summing. For the mechanics of creating reversals after the fact — partial amounts, insufficient-balance errors, and netting — see the unreversed-transfer solution page.

L2: find sales that were refunded while their application fee was kept

Application fees aren't automatically refunded when issuing a refund — your platform must explicitly refund the fee or the connected account loses that amount (direct charges). The check joins each fee to its charge:

import Stripe from 'stripe';

const stripe = new Stripe(process.env.STRIPE_SECRET_KEY);

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

let keptFees = 0;

await stripe.applicationFees
 .list({ created: { gte: cutoff }, limit: 100, expand: ['data.charge'] })
 .autoPagingEach((fee) => {
 const charge = fee.charge as Stripe.Charge;
 const refundedOnSale = charge.amount_refunded ?? 0;

 if (fee.amount_refunded === 0 && refundedOnSale > 0) {
 keptFees += fee.amount - fee.amount_refunded;
 console.log(`${fee.id}: fee ${fee.amount} kept, sale refunded ${refundedOnSale}`);
 }
 });

console.log(`total kept fees: ${keptFees}`);

A nonzero result means sellers funded refunds out of their own balances while your fee stayed intact — the exact case the direct-charges documentation warns about. We treat this vector in detail in the application-fee leak deep dive.

L3: find lost disputes where the seller still holds the transfer

On destination and separate patterns alike, Stripe debits the disputed amount and the dispute fee from the platform balance; recovery from the seller is a manual transfer reversal (Disputes on Connect). Check whether that reversal ever happened:

import Stripe from 'stripe';

const stripe = new Stripe(process.env.STRIPE_SECRET_KEY);

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

function isString(value: unknown): value is string {
 return typeof value === 'string';
}

let uncovered = 0;

await stripe.disputes
 .list({ created: { gte: cutoff }, limit: 100, expand: ['data.charge'] })
 .autoPagingEach(async (dispute) => {
 switch (dispute.status) {
 case 'lost':
 break;
 default:
 return;
 }

 const charge = dispute.charge as Stripe.Charge;
 if (isString(charge.transfer) === false) return;

 const transferId = charge.transfer;
 const transfer = await stripe.transfers.retrieve(transferId);
 const reversals = await stripe.transferReversals.list(transferId, { limit: 100 });

 let reversed = 0;
 for (const reversal of reversals.data) reversed += reversal.amount;

 const retained = transfer.amount - reversed;
 if (retained > 0) {
 uncovered += retained;
 console.log(`${dispute.id}: seller retains ${retained} of ${transfer.amount}`);
 }
 });

L4 through L7 in prose

L4 uses the same rounding rule as L1 applied per partial refund: expected reversal = round((amount_refunded / charge.amount) × transfer.amount), missing = expected − Σ existing reversals. The failure mode is a code path that reverses a flat amount, or nothing, on partials.

L5 needs a baseline comparison rather than an object join; the method is short enough that we give it its own article — see the FX slippage deep dive and the companion piece on refund-day conversion below it.

L6 is organizational: list refunds over the window and match each one to a request your systems made. A refund with none of your metadata conventions and no matching log line came from the Dashboard or a support tool, and whatever flag policy you maintain did not apply to it.

L7 is a static check: grep your codebase for every refunds.create call site and confirm each passes refund_application_fee and reverse_transfer explicitly, chosen per charge type. Defaults differ by pattern, so a call site moved between patterns silently changes meaning.

The whole procedure also exists as a printable sequence in the refund-path audit checklist, for teams that prefer to work it offline.

One charge through the machine

Nothing abstract survives contact with a concrete ledger. Inputs and assumptions, stated plainly:

US platform, USD everywhere, standard US card pricing of 2.9% + $0.30 per Stripe's published pricing.
One destination charge of $100.00 = 10000¢, with application_fee_amount = $10.00 (1000¢) and transfer_data.destination set, so $90.00 moves to the connected account.
Processing fee: 2.9% × 10000¢ + 30¢ = 290¢ + 30¢ = 320¢ = $3.20, kept by Stripe.
Buyer later demands a full refund; support issues it with default flags. Then, on a second, identical order, the cardholder disputes the payment and loses.

Step arithmetic, each line on its own:

Charge settles net of processing fee: 10000¢ − 320¢ = 9680¢ → +$96.80
Transfer to seller: → −$90.00
Platform margin after sale: 96.80 − 90.00 = +$6.80
Full refund, defaults (no flags): refund debits the platform balance → −$100.00
Cumulative position: 6.80 − 100.00 = −$93.20

Conservation check — the books must balance to zero across parties: −93.20 (platform) + 90.00 (seller still holds) + 3.20 (Stripe kept) = 0. The buyer is whole, Stripe is whole, the seller is untouched, and the entire cost landed on the platform because nobody passed reverse_transfer=true.

Now the second order ends in a lost dispute. Stripe debits the disputed amount plus the dispute fee from the platform balance; call the fee F, since its exact value comes from Stripe's published schedule per country and card brand (disputes doc). The cumulative table across both orders, including the recovery step on order one:

Step	Movement	Running platform position
Order 1 settles, net of $3.20 fee	+$96.80	+$96.80
Order 1 transfer to seller	−$90.00	+$6.80
Order 1 full refund, default flags	−$100.00	−$93.20
Order 1 transfer reversed manually afterward	+$90.00	−$3.20
Order 2 margin after sale	+$6.80	+$3.60
Order 2 lost dispute: debited $100.00 + F	−$100.00 − F	−$96.40 − F

Conclusion in dollars and cents: the refund-side leak on order one alone cost $93.20 — about 13.7 times the $6.80 margin the sale was supposed to earn (93.20 ÷ 6.80 ≈ 13.7) — and even with full recovery the order nets −$3.20, exactly the processing fee Stripe does not return. The lost dispute on order two contributes another $100.00 + F of loss against its own $6.80 margin, because its $90.00 transfer sits with a seller who has no incentive to volunteer it. Two routine outcomes, one shape of charge, and each one erased many multiples of the profit it was meant to produce before anyone noticed. Had order one been refunded with both flags true, the extra refund_application_fee=true would have pushed another −$10.00 to the platform and +$10.00 to the connected account — fee refunds compensate the seller, never the buyer (destination charges) — which is why flags deserve a written policy rather than muscle memory.

Prioritizing what to chase

You will not chase everything at once, so rank by two mechanical facts.

Recovery probability falls with age. Connected-account balances drain on the payout schedule, and once funds are paid out to the seller's bank, a reversal succeeds only while their available balance covers it. Recent findings can often be recovered outright; old ones migrate to the practical lever, which is netting — reducing future transfers until the debt clears rather than demanding an instant bank-top-up. Batching many small findings into one scheduled pass beats chasing them individually; the operational recipe lives in bulk reversal of historical findings.

Size versus count is a judgment call, not a formula. A single large unreversed transfer justifies a human conversation with the seller; hundreds of small kept fees do not, and should go straight to automated netting. State the tradeoff in your policy before the first awkward email, and send the underlying Stripe evidence with any request so the discussion is about facts.

Two cautions belong in the same paragraph. First, put idempotency keys on every recovery POST so a retried script cannot double-reverse (idempotency). Second, cross-border destination charges created with on_behalf_of carry sequencing risk: Stripe advises waiting until a dispute is lost before recovering those transfers, because winning means retransferring.

Prevention states

Prevention is a set of configuration states, not a habit. The table maps each state to safe or unsafe and says why.

Configuration state	Safe?	Why
Every `refunds.create` call site passes `reverse_transfer` and `refund_application_fee` explicitly, per charge-type policy	Safe	No default is ever consulted; behavior is invariant to refactors
Any call site omits either flag	Unsafe	Defaults keep the transfer (destination/separate) and the fee (all patterns)
One wrapper service owns all refund creation; other paths removed	Safe	Policy lives in exactly one place; drift shows up as compile errors, not leaks
Staff can issue Dashboard refunds ad hoc	Unsafe	Out-of-band refunds bypass flag policy entirely (vector L6)
A `charge.refunded` webhook recomputes expected reversal and fee refund, opening a finding on mismatch	Safe	Post-verification catches leaks even when creation happened out-of-band
Reconciliation of separate-pattern transfers runs only at month-end	Weak	Funds may already be paid out; recovery degrades to netting
Cross-border `on_behalf_of` destination refunds wait for dispute loss before reversing	Safe	Matches Stripe's advised sequencing for cross-border recovery

The webhook row deserves emphasis because it is the only state that also covers humans clicking buttons in the Dashboard: verification happens after the fact, on the object, regardless of origin. Pair it with the wrapper service and the unsafe states stop being reachable in normal operation.

Frequently asked questions

Doesn't Stripe reverse transfers automatically when I refund?

No. On destination charges the default leaves the transferred funds with the connected account, and pulling them back requires reverse_transfer=true. On separate charges and transfers, refunding the charge has no effect on any associated transfers at all. The one automatic reversal Stripe performs is for async payment failures on destination charges — a different event from a refund.

Which single change prevents the most leakage?

Passing reverse_transfer=true on every destination-charge refund where your terms say the seller eats refunds. It converts a −$93.20 outcome into −$3.20 on the canonical $100 order, because the reversal returns the seller's portion while the residual equals the processing fee Stripe does not return. Add refund_application_fee per your fee policy, deliberately.

When I refund an application fee, who gets the money?

The connected account, always. Application-fee refunds push the fee funds back to the connected account that paid them; the buyer is made whole by the refund itself, never by the fee leg. On direct charges the effect is starkest: without an explicit fee refund, the seller absorbs the full refund plus loses your fee on top.

How far back is a finding worth chasing?

Mechanically there is no expiration on the arithmetic — the objects remain readable and the missing amounts stay computable. Practically, recovery options degrade with age: available balances drain through payouts, and old findings resolve through netting against future transfers or, eventually, write-offs. Anything inside the most recent payout cycle is cheap to fix; everything older should be batched and scheduled.

Check your own last 90 days

Every finding above comes from arithmetic that runs silently each time your platform refunds a buyer or absorbs a dispute. FeeGuard exists to surface that arithmetic: the free audit reads your last 90 days of Connect activity through a restricted, read-only API key and reports every unreversed transfer, unreclaimed application fee, and uncovered dispute loss with the amounts attached and the underlying Stripe objects included, so you can verify each line yourself before acting. You get the answer first; ongoing 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.

Migrations that silently weaken policies

Veristria — Thu, 24 Sep 2026 07:43:43 +0000

Migrations that silently weaken policies

Migrations rarely attack authorization directly — they just rearrange the objects policies depend on, and protection erodes as a side effect. This article shows the four recurring patterns in runnable SQL, what each leaves behind, and the before/after checklist that catches them.

Policies are database objects with dependencies. They reference columns by name, attach to tables by name, and exist only while their prerequisites exist. That makes them first-class casualties of ordinary refactoring: any migration that touches tables or columns can strengthen features while quietly weakening the rules that protect them. Nothing about this is exotic. Every pattern below was executed against current Postgres during this article's preparation, and every one left a working application behind.

What distinguishes migration-driven drift from other kinds is its direction of surprise: it almost always helps in the short term. The recreated table stops erroring. The CASCADE unblocks the deploy. The split finishes the feature. Each pattern buys immediate progress with deferred isolation debt, which is exactly the trade teams accept under pressure unless a check exists to price it.

Why migrations are where policies go to die

Code review has an asymmetry baked into it: reviewers evaluate diffs for behavior the change is about. A migration adding a notifications table gets reviewed for column types, indexes, and backfill logic — nobody re-verifies the authorization posture of the whole schema on every PR, because that verification is tedious and manual.

Meanwhile, policies are post-data objects: they are created after tables, they depend on columns, and they carry no presence in application code. When a migration's blast radius reaches them, no test fails unless someone wrote a test asserting policy existence — which most suites don't. The result is a systematic blind spot, summarized:

Migration act	What reviewers check	What actually changes
Recreate table	Columns, indexes	RLS flag gone; every policy gone
Drop column	Callers of the column	Policies referencing it die with CASCADE
Rename anything	Updated references in code	Policies follow automatically (the safe case)
Split/merge tables	New query paths	Old policies cover none of the new surface

The rest of this article walks each row with real SQL, then gives you the two-query checklist that closes the gap regardless of which pattern sneaks through.

A note on scope before the patterns: everything here concerns schema migrations, but the same dependency logic applies to data backfills that drop-and-refill tables, and to environment copies that ship schemas between staging and production by hand. Wherever table definitions travel without their post-data objects, the four patterns below are what waits. The names change — "sync script" instead of "migration" — and the outcome does not.

Pattern 1: recreate the table, lose everything

The most common form is accidental. A type must change, a constraint must be rebuilt, an ORM suggests drop-and-recreate because Postgres lacks some direct alteration. All three variants below were run and produce the same outcome:

create table projects (
 id uuid primary key,
 owner_id uuid not null,
 title text not null
);
alter table projects enable row level security;

create policy "projects_select"
 on projects for select to authenticated
 using ((select auth.uid()) = owner_id);

State today: RLS enabled, one ownership policy. Now the refactor:

-- Variant A: explicit drop-and-recreate under the same name
drop table projects;
create table projects (
 id uuid primary key,
 owner_id uuid not null,
 title text not null
);

-- Variant B: clone-based rebuild
create table projects_v2 (like projects including all);
-- ...copy data, swap names...

-- Variant C: create-table-as shortcut
create table projects_v3 as select * from projects;

As executed, all three descendants share one property catalogued immediately after the run:

select c.relname, c.relrowsecurity as rls_enabled
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;

 relname | rls_enabled
-------------+-------------
 projects | f
 projects_v2 | f
 projects_v3 | f

INCLUDING ALL copies columns, indexes, defaults, even storage parameters — and not row security. CREATE TABLE AS doesn't pretend to. And variant A's fresh table starts life exactly like any new table: flag off, zero policies. On a Supabase project the client roles' default grants mean each of these tables is immediately readable through the API the moment data lands. The feature being migrated keeps working — better than before, if the old setup had been half-broken. The full anatomy of that window is at new table shipped without RLS.

Worth internalizing: the danger scales with how routine the operation feels. A drop-and-recreate performed during an incident gets scrutiny; the same operation embedded in a Friday-afternoon ORM migration gets merged. Teams adopting schema-management tools should read their generated SQL once with this specific question in mind — "does anything here DROP or re-CREATE a table my policies live on?" — because tool output optimizes for schema equivalence, and policy objects are not part of what it considers equivalent.

Pattern 2: CASCADE amputates a policy legally

Supabase's own dependency tracking will warn you — loudly, even. The question is whether anyone hears it over deadline pressure. Set up a policy that depends on a column:

create table documents (
 id uuid primary key,
 owner_id uuid not null,
 title text not null,
 confidential boolean not null default false
);
alter table documents enable row level security;

create policy documents_confidential_gate
 on documents as restrictive for select to authenticated
 using (confidential = false);

Then try the obvious refactor:

alter table documents drop column confidential restrict;

Postgres refuses, naming the victim in advance:

ERROR: cannot drop column confidential of table documents because
 other objects depend on it
DETAIL: policy documents_confidential_gate on table documents
 depends on column confidential of table documents
HINT: Use DROP ... CASCADE to drop the dependent objects too.

That DETAIL line is the database telling you precisely which protection dies if you proceed. The CASCADE path proceeds anyway:

alter table documents drop column confidential cascade;
-- NOTICE: drop cascades to policy documents_confidential_gate
-- on table documents

Migration succeeds. Application works. The restrictive gate — possibly the one thing standing between permissive policies and your compliance posture — is gone, recorded only in a NOTICE that CI logs swallow. We watched this exact beat delete a timeline's only defense in the schema drift guide's month four; here is the pre-flight query that would have surfaced the dependency before writing any DDL:

select tablename, policyname, cmd
from pg_policies
where schemaname = 'public'
 and (qual ilike '%confidential%' or with_check ilike '%confidential%');

Every row returned is a policy scheduled for execution if this migration ships with CASCADE. Run it as part of drafting the migration, not after the NOTICE appears — the difference between "we rewrote the gate onto the new classification column in the same PR" and "we discovered the amputation next quarter" is entirely when this query runs.

Pattern 3: renames are safer than their reputation

Good news for once, verified both ways. Renaming a column updates policy expressions automatically:

select qual from pg_policies where tablename = 'projects';
-- (( SELECT auth.uid() AS uid) = owner_id)

alter table projects rename column owner_id to created_by;

select qual from pg_policies where tablename = 'projects';
-- (( SELECT auth.uid() AS uid) = created_by)

Renaming the table carries its policies along identically. Postgres tracks these dependencies properly, and rename is a metadata operation — no policy loss occurs. The residual risks are human, not mechanical:

The rename can break generated clients and raw SQL elsewhere, producing pressure to "just revert it" via recreate — landing you in Pattern 1.
Renames don't fix stale intent: a policy now reading created_by = auth.uid() still encodes whatever assumption it always had, under a fresher name.
Renaming a column out from under a tautology-adjacent policy changes nothing about its width; renames preserve meaning, including bad meaning.

Treat renames as the pattern that behaves — and let that trust make you more suspicious of the patterns that don't. There is a second-order risk worth naming: a rename that updates policies correctly can still break application queries, generated types, or embedded dashboards, and the fastest "fix" under pressure is recreating the old column name — sometimes as a drop-and-recreate of the whole table, landing you in Pattern 1 with nobody watching the authorization surface. When a rename ships, watch the follow-up commits for exactly that rebound.

Pattern 4: splits and merges leave orphans and unions

Feature growth often splits a table ("archive old rows") or merges two ("unify profiles"). Both operations interact badly with policy sets:

Splitting strands the original policies on the original table. The new sibling — same shape, same sensitivity — inherits nothing, for the same reasons as Pattern 1's clones. Teams remember to migrate queries; policies aren't queries, and nothing red flags their absence until someone probes the new table as an outsider.

Merging is sneakier because nothing looks missing. Suppose two tables each carry an ownership select policy, and a consolidation folds both datasets into one table while porting both policies verbatim. Permissive policies OR together — the union semantics from policy accumulation — so rows now match whichever legacy condition is looser. Each policy was individually reviewed and correct; the merge silently promoted the more permissive of the pair to govern the whole combined dataset.

Neither failure produces an error, a failed test, or a dashboard signal. Both are visible in ten seconds to anyone who lists policies per table after the migration and compares against before.

The dump-and-restore special case

Schema moves through time by migrations; it also moves by dumps. The two paths treat policies differently, which is where restore-shaped drift enters:

A full schema dump (pg_dump without --data-only) includes policy definitions alongside tables. Restore it faithfully and protection arrives with the schema — this is the safe default.
A data-only restore into freshly created tables carries no policies at all, because data-only means no post-data objects. If the target tables were hand-created during the operation, they start exactly like Pattern 1's recreates: flag off, zero policies, grants ready.
Partial workflows — one table dumped here, restored there, the original dropped in between — combine both hazards and are the most common way a single-table "cleanup" deletes its own protection.

None of these paths warn. A restore completes successfully whether or not the resulting schema matches what you'd call protected, because "matches intent" was never a property dumps carry. The practical rules: prefer full-schema restores; when surgery is unavoidable, run the snapshot pair from the checklist below immediately after; and treat any environment rebuilt outside the migration pipeline as unauthorized until verified — the assumption that production equals what CI approved is precisely the gap staging-versus-production drift lives in.

The two-query checklist that catches all four patterns

This is the entire discipline, sized to fit in a migration template. Before the migration runs, snapshot the protection state:

-- protection_snapshot.sql
select c.relname,
 c.relrowsecurity as rls_enabled,
 count(p.policyname) as policy_count
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_policies p
 on p.schemaname = n.nspname and p.tablename = c.relname
where n.nspname = 'public' and c.relkind = 'r'
group by c.relname, c.relrowsecurity
order by c.relname;

After the migration runs, run it again. Three comparisons tell you everything:

Any new table with rls_enabled = false or policy_count = 0 — Patterns 1 and 4a.
Any familiar table whose policy_count dropped without an explicit, intended policy deletion in the diff — Pattern 2's CASCADE signature.
Any table where policies changed but the diff didn't mention authorization at all — Pattern 4b's union effect, and anything else unexpected.

Teams using migration tools can wire the snapshot pair into CI as a generated artifact: fail the build on unexplained deltas, require a one-line justification comment for intentional ones. The wiring is deliberately boring — capture to a file, diff against the previous capture, non-zero exit on unexplained change:

psql "$DATABASE_URL" -f protection_snapshot.sql > after.txt
diff migrations/_protection_baseline.txt after.txt \
 || (echo "Protection inventory changed — justify or fix"; exit 1)
cp after.txt migrations/_protection_baseline.txt

Committed alongside the migrations it guards, the baseline file makes protection drift as reviewable as any code change: the PR diff now contains authorization consequences, in four columns, where a reviewer cannot miss them. The cost is seconds per migration; the alternative cost is measured in quarters, as teams who found the same regression twice tend to discover.

For state that changes outside migrations — restores, dashboard edits, hotfix branches — no CI hook can see it. That residual window is exactly what continuous monitoring covers, and why our remediation flow (the SQL reference) pairs proposed fixes with ongoing checks rather than one-time cleanups.

Common questions

Doesn't pg_dump capture policies? Aren't restores safe?

Schema dumps include policies, so a full dump-and-restore preserves them. The danger sits in partial workflows: data-only restores applied to freshly created tables, hand-built replacement tables during surgery, or snapshots predating a hardening migration. Restores reproduce whatever state existed — including states you were glad to leave behind.

How do I find which policies a planned DROP COLUMN would kill?

Query pg_policies for the column name inside qual and with_check, as shown in Pattern 2 — plus check pg_depend for non-policy dependents. If several policies appear, plan their replacements in the same migration rather than accepting the CASCADE notice as an answer.

Is there ever a legitimate reason to disable RLS in a migration?

Yes, narrowly: bulk-load sessions sometimes disable constraints and security temporarily for speed, re-enabling afterward within the same migration. Treat it like disabling triggers — acceptable when scoped, transactional, and restored; a red flag anywhere else. Your post-migration snapshot should show the flag back on, which is precisely what the checklist enforces.

We use an ORM that manages schema. Does this apply?

Doubly. ORM-generated migrations are exactly where recreate-style refactors originate, and the tool's diff view shows tables and columns, not policy objects. Run the snapshot pair around ORM-generated migrations especially — they're the highest-volume source of Pattern 1 in the wild.

Do views over a table change any of this?

Views depend on their underlying tables the way policies do, so renames propagate cleanly — but replacing or dropping tables beneath views follows the same CASCADE mechanics as policies, and pre-Postgres-15 view semantics bypass row security entirely unless the view is declared security_invoker. If your migration touches a table feeding views, extend the pre-flight query to include pg_depend, and review the views guide for the invoker setting.

Check your schema's current protection inventory in minutes: run the free scan — paste your app URL and review findings with proposed remediation SQL for anything drifting.

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

Reading Stripe's ledger like an accountant

Veristria — Wed, 23 Sep 2026 20:16:29 +0000

Reading Stripe's ledger like an accountant

Every cent that moves through a Stripe account leaves exactly one trace: a BalanceTransaction row. This guide teaches finance teams to treat those rows as the general ledger — the object anatomy, the type vocabulary translated into journal-entry terms, and reconciling refunds against what should have happened rather than merely confirming that something did.

Start from the BalanceTransaction object

If you run the close for a Connect platform, this object is yours before anyone else's — controllers and finance operations own its interpretation (the controller's view). The anatomy is small enough to memorize; the table gives each field its accounting read.

Field	Holds	Accounting read
`amount`	Signed integer, smallest currency unit	Gross movement in or out
`fee`	Positive integer	Costs attached to this line
`net`	Signed integer	Cash impact; Stripe computes it as `amount - fee`
`fee_details`	Itemized array	The split behind `fee`
`currency`	ISO code	Never aggregate across these
`type`	Enum	The chart-of-accounts hint
`source`	Originating object ID	The join key back to reality
`created` / `available_on`	Timestamps	Event date versus recognition date
`status`	`pending` or `available`	Which clock the money is on

All of it lives on the BalanceTransaction object, which also carries a reporting_category field grouping types for accounting use. The discipline that follows from the anatomy: book revenue from amount, book cash from net, and never book a payout lump as revenue — a payout is a movement of already-earned money, not income.

The arithmetic is self-evident but worth pinning. A $100.00 payment with a $3.20 processing fee posts as one transaction where amount = 10000¢ and fee = 320¢:

net = amount - fee = 10000 - 320 = 9680 → $96.80 reaches the balance

One object, three numbers, and each answers a different question: what was billed, what it cost, what settled.

Two clocks on the same money

Every transaction exists on two timestamps. created records when the event happened; available_on records when its net becomes usable, because card payments land in pending first and roll onto the available balance on a rolling schedule — typically about two business days, varying by country and risk profile. Payouts draw only from available funds (payouts), and both balances exist per account on Connect (account balances). The balance.available event fires at the moment of the transition if you want it pushed rather than polled.

An illustrative timeline, dates assumed for the example:

Day	Ledger event	Money state
Mon	Charge created, `available_on` = Wed	Pending
Wed	Becomes available	Usable
Fri	Payout created against available	Leaving
Next Mon	Bank credit appears	Reconciled

This clockwork explains most bank-recognition mismatches. The payout that hits the bank next Monday contains sales from the previous week; matching it against Monday's invoices fails by construction. Match payouts to bank lines by payout amount and arrival date, recognize revenue per your policy on the sales themselves, and treat available_on as the recognition boundary between the two ledgers. When bank reconciliation disagrees with Stripe, timing is the default hypothesis and error the exception worth proving.

The type vocabulary, translated

The type enum is the closest thing Stripe has to a chart of accounts, and the table below translates the commonly seen values into journal-entry terms. Grouped by role:

Group	Types	Journal-entry meaning
Revenue side	`charge`, `payment`	Gross sales in
Revenue side	`application_fee`	Platform commission income on Connect charges
Contra	`refund`, `payment_refund`	Sales returns
Contra	`application_fee_refund`	Commission given back to the seller
Contra	`adjustment`	Post-settlement corrections; investigate each one
Movement out	`transfer`, `payout`	Funds to connected accounts; funds to bank
Movement failed or returned	`payout_cancel`, `payout_failure`, `transfer_refund`	Movements that bounced; a reversal returning transferred funds
Movement in	`topup`	Manually added funds
Stripe-side fees	`stripe_fee`, `stripe_fx_fee`	Processing and conversion costs
Connect machinery	`reserve_transaction`, `reserved_funds`, `connect_collection_transfer`	Risk holds; aged-negative collection

Treat the table as a working subset. The authoritative enumeration, with a sentence on what each value represents, sits on Stripe's balance transaction types reference, and rare values appear only on some accounts — an unfamiliar type is a prompt to look it up, not to guess.

Two entries deserve special respect in month-end review. adjustment means money moved after the fact for reasons ranging from dispute outcomes to corrections, and each one should trace to a cause you can name. connect_collection_transfer means Stripe reached into platform reserves to zero out an aged negative connected-account balance — rare, material, and never a surprise if the negative balances were being watched.

One net, many GL lines: fee_details

A single net rarely maps to a single general-ledger line, and fee_details is where the decomposition happens. This is a seller-side direct charge as the connected account sees it — $100.00 sale, standard US pricing assumption of 2.9% + $0.30, $10.00 application fee:

{
 "object": "balance_transaction",
 "amount": 10000,
 "fee": 1320,
 "net": 8680,
 "currency": "usd",
 "type": "charge",
 "fee_details": [
 { "description": "Stripe processing fee", "amount": 320, "type": "stripe_fee" },
 { "description": "Application fee", "amount": 1000, "type": "application_fee" }
 ]
}

Check the arithmetic: fee = 320 + 1000 = 1320, and net = 10000 − 1320 = 8680. One ledger row, three GL destinations; the table shows how a controller might map it:

GL line	Amount
Revenue (gross)	$100.00
Payment processing expense	$3.20
Platform commission expense	$10.00
Net settled	$86.80

With the check that 100.00 − 3.20 − 10.00 = 86.80. The platform side of the same economy splits across two transactions instead: a destination-charge sale nets $96.80 after only the Stripe fee, and the transfer to the seller posts separately at −$90.00, leaving margin of 96.80 − 90.00 = $6.80. The commission itself is an ApplicationFee object with its own amount, amount_refunded, and balance_transaction fields (application fee object) — the fields every fee-reconciliation check eventually reads.

Reconstructing a refund from three ledger lines

Refund flags state intent; the ledger states execution. Reading the cluster of transactions around a refund tells you which one you actually got.

Take the canonical destination charge: $100.00 sale, $90.00 transfer, $10.00 application fee, platform margin $6.80. Now refund it in full with both reverse_transfer=true and refund_application_fee=true. Three ledger rows appear:

Line	Amount	Type	Source points at
1	−10000¢	`refund`	The refund object
2	+9000¢	`transfer_refund`	The original transfer
3	−1000¢	`application_fee_refund`	The fee refund

Sum the cluster: −10000 + 9000 − 1000 = −2000, so the refund-week cash effect is −$20.00. Stack it on the sale-time margin of $6.80 and the platform's lifetime position is 6.80 − 20.00 = −$13.20 while the seller ends up +$10.00 — the cluster reveals that flagging everything true over-compensates the seller on destination charges, because fee refunds compensate sellers, never buyers.

Now the default signature: same refund, no flags. The ledger shows a single bare row — −10000¢ typed refund — and nothing else. Lifetime position: 6.80 − 100.00 = −$93.20, seller untouched at +$90.00. One line versus three lines is the entire difference between a deliberate unwind and money left on the table.

That contrast is automatable without any judgment. For each refund entry, expect siblings proportional to the refund ratio: a reversal near round(refund_ratio × transfer_amount) and a fee refund near round(refund_ratio × fee_amount). Missing siblings are findings. Present siblings are policy, working as designed. The ledger answers not just "did we refund" but "did the refund do everything our policy promised."

Tying the ledger to the bank

Three bridges connect the Stripe ledger to external reality, and each has a failure mode worth instrumenting.

Payouts bridge to bank statements. Every payout groups the transactions inside it, and Stripe's reporting offers itemized balance-change and payout-reconciliation report types built for exactly this match (Stripe reports). Match by amount and arrival date; investigate residuals rather than forcing them.

Failed legs leave their own entries. A payout that bounces produces payout_failure activity and a payout.failed event; a refund that cannot reach the card reverses course and returns funds to your balance within up to about 30 days, exposed through failure_balance_transaction on the refund (refunds). The ledger type refund_failure marks the round trip. Neither failure closes anything until the return leg lands.

Disputes reinstate as well as debit. A won case returns the held funds, announced by the charge.dispute.funds_reinstated event, with the corresponding positive entries landing on the ledger (Connect disputes). Book them against the original reserve, not as new income.

Keeping all of this queryable year-round is a pipeline decision, not a spreadsheet one; syncing Stripe data into a warehouse makes every assertion in the next section a scheduled query (BigQuery sync), while export-driven teams can still systematize the CSV path (recovery from Excel exports).

Assertions worth automating

Three classes of assertion turn the ledger from a record into a control. Run all three per currency, per day.

The identity test proves completeness: opening available balance plus all non-payout activity minus payout totals must equal the closing available balance. In code:

function closesBalanced(
 txs: Stripe.BalanceTransaction[],
 openingAvailable: number,
 payoutTotal: number,
 closingAvailable: number,
): boolean {
 const activity = txs.reduce(
 (sum, t) => (t.type === "payout" ? sum : sum + t.amount),
 0,
 );
 return openingAvailable + activity - payoutTotal === closingAvailable;
}

A false result means a missing or duplicated transaction — reconcile before anything else that day.

The expectation test proves correctness, not just completeness: every refund cluster must satisfy the proportional formulas — reversal near ratio times transfer, fee refund near ratio times fee — and violations are findings regardless of how cleanly the identity test passes. An internally consistent ledger happily records a policy executed badly.

The anomaly test proves nothing is hiding: any adjustment, reserve_transaction, or connect_collection_transfer without a filed reason gets a ticket before the close finishes. These types are legitimate; unexplained, they are how surprises enter the books quietly.

Teams running this manually today can compare their process against the month-end close pattern or the spreadsheet-based workflow as further reading — the assertions above are the part worth keeping no matter which tool executes them.

Corrections you will meet: failures, reinstatements, reserves

A clean month still contains entries that exist purely to correct other entries. Reading them fast is most of the accountant's edge.

Failed refunds come back to you. When a refund cannot reach the customer's instrument, the funds return to the balance that funded it — within up to roughly 30 days of the attempt (refunds) — and the Refund object carries status: failed with a failure_balance_transaction pointing at the return leg. In the ledger this appears as a fresh credit where you expected nothing; matching it against its failed refund prevents double-counting both the original refund and the return as activity.

Won disputes reinstate funds. A dispute resolved in your favor triggers charge.dispute.funds_reinstated, and the associated balance transaction restores what the freeze or debit had taken, including the partially-refunded-payment nuances Stripe documents. The reconciliation habit: every dispute.created debit should eventually pair with either a funds_reinstated credit or a permanent-loss entry, never neither.

Reserve machinery lives on the platform side. reserve_transaction entries appear when Stripe holds platform balance against a negative connected account and again when it releases that hold; connect_collection_transfer appears when a 180-day-old connected negative is zeroed out of your reserves (account balances). Neither is an error; both are movements of YOUR money triggered by someone else's account, which makes them exactly the lines worth ticketing on sight.

And then there is adjustment — the catch-all. Genuine uses exist, but an unexplained adjustment without a filed reason is how surprises enter books quietly. Treat any adjustment you cannot narrate in one sentence as an open item until closed.

Balances per currency

Multi-currency platforms read more than one column. The retrieve-balance call returns pending and available figures for each currency the account holds, so a USD-settled platform accumulating EUR application fees from cross-border destination charges sees separate EUR lines waiting there (retrieve balance). Reconcile per currency before converting anything; mixing them at the totals level hides exactly the conversion deltas that FX-focused reviews go hunting for.

curl https://api.stripe.com/v1/balance \
 -u "sk_live_...:"

Each currency block answers independently: payouts draw from their own currency's available figure, refunds debit their own charge currency, and no automatic netting crosses the columns.

Frequently asked questions

Is `net` what lands in the bank?

Not by itself. Each transaction's net aggregates into the available balance, and the bank receives payouts — lumps of many nets. The bank line matches the payout amount, and the individual nets reconcile inside it. Treating any single transaction's net as a bank posting is the fastest way to break reconciliation.

Which date should I book a sale on?

Pick a policy and apply it uniformly. Booking on created aligns revenue with customer activity; available_on marks when the cash became usable and drives the bank-side picture. Problems come from mixing the two conventions mid-year, not from either choice itself.

Where do currency conversions show up?

Inside the same balance transactions: when money converts, the transaction carries an exchange_rate field explaining exactly how much landed (BalanceTransaction object). Refunds convert at the live rate on refund day regardless of any quote locked at purchase time, and the original conversion fee is not returned (FX quotes) — both facts show up as spread between paired entries.

Do connected accounts see the same vocabulary?

Yes — every account on Connect keeps its own ledger using the identical type enum, seen from its own side. The platform watches transfer rows leave; the seller watches them arrive. Reconciling the two perspectives against each other is precisely how transfer-reversal gaps get found.

Where do currency conversions show up?

Inside the balance transactions themselves: when a charge's presentment currency differs from settlement, the transaction records the amounts and fees that produced the settled figure, so the conversion cost is embedded in fee/net rather than appearing as its own type. Comparing linked transactions across currencies is how the spread becomes visible — and why reconciling per currency first, then converting totals, keeps arithmetic honest.

Check your own last 90 days

Reading a ledger well tells you what happened; pairing every refund against what should have happened tells you what is missing. FeeGuard exists because that pairing runs silently on every refund your platform issues. 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.

The tenant-isolation test: five queries that prove your boundaries

Veristria — Fri, 11 Sep 2026 18:09:57 +0000

The tenant-isolation test: five queries that prove your boundaries

Tenant isolation is provable in minutes, not asserted in meetings. This article gives developers five concrete probes — runnable against their own project with their own keys — where every expected result is stated in advance, so passing or failing is never a matter of interpretation.

It is easy to believe your tenants are isolated because no customer has complained. That belief costs nothing until it's wrong, and when it's wrong it's expensive in the worst currency: someone else's data. The alternative is cheap enough to be embarrassing not to do — five requests, sent by you, against your own project, each with an unambiguous correct outcome. Run them once and you know your boundaries hold today. Wire them into CI and you know they held on every deploy since.

Everything here uses only resources you own: your project URL, your public anon key, and two test accounts you created yourself. Nothing below requires or produces privileged access to anyone else's system — it is the same evidence-gathering the RowShield free scan automates from outside, done by hand.

Why black-box probes at all, when you could read the policies? Because reading verifies intent while probing verifies outcome, and the distance between those is where every incident in this site's catalog lives. Policies are code written by humans and migrations under pressure; requests are truth. A policy review that says "only owners" and a probe that returns Bob's rows are not in disagreement — one of them is wrong, and it has never been the probe. The five tests below are also deliberately cheap: no test framework, no fixtures beyond two accounts, nothing to maintain except attention. Cheap enough that "we should really check that" stops being a reason to skip it.

Setup: two accounts and one honest baseline

Create two throwaway users through your own signup flow — call them Alice and Bob — and put each in their own workspace, project, or tenancy unit. Seed each with data you can recognize. Then establish the baseline both personas must agree on:

curl -s "https://YOUR-PROJECT.supabase.co/rest/v1/documents?select=id" \
 -H "apikey: $ANON_KEY" \
 -H "Authorization: Bearer $ALICE_JWT"

Alice should see exactly her fixture rows' IDs. If this baseline already surprises you — wrong count, missing rows, extra rows — stop here and investigate before running any negative tests, because everything downstream assumes you understand the happy path.

One more preparatory habit matters: capture responses with headers (curl -i) during your first run. Status codes carry half the diagnosis, and several of the five tests distinguish correct rejection from accidental success only by code.

The five probes

Test 1: the anonymous read

What it proves: private tables are invisible to the identity everyone holds — no session, just the public key.

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

Expected: [] — an empty JSON array. (Or a 404-class error if the table isn't exposed at all, which is also a pass.)

If you see rows: the table has no effective select policy for anon — either RLS was never enabled, or policies simply don't mention the role. This is the single most common exposure our scans find, catalogued as anon-table-readable, and it applies to every column including ones your UI never displays. A variation worth running per table: repeat with select=id&limit=1 across your full table inventory rather than assuming the answer transfers between tables.

Test 2: the forged write

What it proves: a signed-in user cannot create rows attributed to someone else — the WITH CHECK clause working as designed.

curl -s -X POST "https://YOUR-PROJECT.supabase.co/rest/v1/documents" \
 -H "apikey: $ANON_KEY" \
 -H "Authorization: Bearer $ALICE_JWT" \
 -H "Content-Type: application/json" \
 -d '{"workspace_id": "'"${ALICE_WS}"'", "title": "forged", "owner_id": "'"${BOB_ID}"'"}'

Expected: an HTTP error — PostgREST surfaces Postgres's policy rejection (42501) as a 4xx, typically 403 — and no row appears afterward.

If the insert succeeds: your insert path accepts planted rows, and the asymmetry that makes this dangerous deserves emphasis: check whether Alice can read what she just wrote. If she can't (selects correctly filter to her own rows), the forged record sits invisible in Bob's scope — a planted object in another tenant's account that functional testing will never notice, because from the app's perspective nothing happened. This exact hole is the missing-WITH-CHECK signature.

Test 3: the cross-tenant read

What it proves: knowing another tenant's identifiers buys nothing. Identity-based policies must beat ID-based queries.

curl -s "https://YOUR-PROJECT.supabase.co/rest/v1/documents?id=eq.${BOB_DOC_ID}" \
 -H "apikey: $ANON_KEY" \
 -H "Authorization: Bearer $ALICE_JWT"

Expected: []. The row exists; Alice's request is well-formed; the policy must still refuse it.

If the row comes back: your policies trust something other than the caller's verified identity — often a client-supplied workspace_id parameter, or a policy comparing against the wrong column. Cross-tenant reads by identifier are the canonical multi-tenant leak, and unlike Test 2 they're silent in both directions: nothing errors, nothing is planted, data just crosses a boundary that your architecture diagram says exists. Run the same shape for every foreign key chain an outsider could guess: workspace IDs, slugs, sequential numbers.

Test 4: the ownership transfer

What it proves: users cannot hand rows out of their own scope — the update path's WITH CHECK holding the line on the resulting row.

curl -s -X PATCH \
 "https://YOUR-PROJECT.supabase.co/rest/v1/documents?id=eq.${ALICE_DOC_ID}" \
 -H "apikey: $ANON_KEY" \
 -H "Authorization: Bearer $ALICE_JWT" \
 -H "Content-Type: application/json" \
 -d '{"owner_id": "'"${BOB_ID}"'"}'

Expected: an HTTP error, and the row's owner unchanged when Alice reads it again.

If the update succeeds: Alice just moved her row into Bob's account — or worse shapes exist depending on which clause is missing. An update policy with only USING lets callers modify any row they can currently see into anything at all; the before-state is guarded while the after-state escapes. Our WITH CHECK deep-dive dissects why this half of update protection is the most commonly omitted clause in generated policy sets.

Test 5: the silent delete

What it proves: deletion respects the same boundaries reads do — and stays silent when it refuses.

curl -s -X DELETE \
 "https://YOUR-PROJECT.supabase.co/rest/v1/documents?id=eq.${BOB_DOC_ID}" \
 -H "apikey: $ANON_KEY" \
 -H "Authorization: Bearer $ALICE_JWT" \
 -H "Prefer: return=representation"

Expected: an empty response — zero rows matched, zero returned — followed by Bob confirming his document still exists.

If the row vanishes: delete policies are wider than your model claims. Note the trap that makes this test necessary rather than obvious: a refused delete doesn't error, it matches nothing, so application code that treats "no error" as "deleted" will report success all day long while the boundary holds — and report success equally loudly on the day it doesn't.

Reading failures like an engineer

Each failure maps to exactly one clause of one policy, which is what makes this suite diagnostic rather than merely alarming:

Test failing	Clause implicated	Typical fix
1 — anonymous read	`USING` on select policy (or RLS flag off)	Enable RLS; write the anon decision deliberately
2 — forged write	`WITH CHECK` on insert policy	Add the ownership condition to the write check
3 — cross-tenant read	`USING` comparing wrong column or trusting input	Compare stored columns against `(select auth.uid())`
4 — ownership transfer	`WITH CHECK` on update policy	Constrain the resulting row, not just the target
5 — silent delete	`USING` on delete policy	Scope deletable rows by verified identity

Two status codes carry most of the diagnosis. 403 on writes means Postgres's policy rejection surfaced correctly — the database refused, your policies just need correcting. 200 where you expected refusal means the statement executed; no amount of application-layer filtering downstream compensates for that, because the write already happened. And an empty-array pass on reads is only meaningful alongside its negative: if Test 3 returns [] but so does Alice's own baseline query, you haven't proven isolation, you've broken reads — which is why the baseline in setup comes first.

Variations worth running

The five core probes generalize. Four variations extend coverage without new concepts:

Column probing. Repeat Test 1 with explicit sensitive columns — select=owner_id,email,total_cents rather than defaults. A table can be "empty" under default column selection while leaking precisely the fields that matter when requested by name, if any view or generated mapping exposes them differently.

The pagination walk. On any anon-readable-but-supposedly-limited surface, walk pages: ?select=id&limit=1000&offset=0, then offset=1000, and so on. Row-level limits enforced in application code evaporate at the API layer unless policies or PostgREST configuration enforce them server-side.

The storage sibling. Buckets have their own policy system; the equivalent probe is requesting an object URL without credentials and checking the response. A public bucket fails it exactly like a naked table — same test shape, different subsystem (the storage rule).

The second-tenant matrix. Once two accounts work, three reveal more: add Carol as a member of Alice's workspace and re-run Tests 3–5 expecting success through membership but failure through direct ID. Scale personas to match your model — one per distinct access level (owner, editor, viewer, admin, former member) — and the five tests stay identical per persona pair; only the number of baselines you record changes. Isolation is not secrecy from everyone — proving collaborators can reach shared data while outsiders can't is half the model, and it's the half teams test least.

Making it a habit

A suite that runs once is an audit; a suite that runs always is a boundary. Three scheduling rules keep these probes alive:

After every migration that touches tables or policies, run all five against staging. This is where regressions get caught while their cause is still in review.
On a schedule against production — monthly is defensible, weekly is better — because restores and dashboard edits don't travel through CI.
With an owner. Unowned checks rot. The table above fits in a README; a name next to it fits in a standup.

Teams that outgrow hand-running wire the five requests into their existing test runner — each is a single HTTP call with an assertion, translatable to any stack in minutes. The investment converts isolation from an annual question into a continuous answer, which is the difference between hoping and knowing that the boundaries your product promises are the boundaries your database enforces.

Scoring the run

Five tests, binary outcomes, no judgment calls. Run them in order — each builds on the previous one's passing state, and the sequence takes less time than the coffee it pairs with:

#	Probe	Pass	Fail means
1	Anonymous read	`[]`	Table open to the world
2	Forged write	Rejected (4xx)	Rows can be planted into others' scope
3	Cross-tenant read	`[]`	Tenant boundary crossed by ID
4	Ownership transfer	Rejected	Rows can leave their tenant
5	Silent delete	Zero effect, row intact	Deletion crosses tenants

Any failure earns a specific conversation, not a general worry — each test maps to one policy clause, and the remediation is usually a single corrected statement. The in-database versions of these same checks (with exact SQLSTATE assertions and pgTAP wrappers) live in our testing guide, and the natural next step is wiring the whole set to run after every migration, so isolation regressions surface in pull requests instead of support tickets.

What these five don't cover

Honest scoping, again: the suite proves the API surface for two personas on the tables you thought to test. It doesn't cover Storage buckets (their own policy system, checked separately), Realtime channels, service-role paths, or tables nobody remembered to include. It also proves today: a migration next week can invalidate every green checkmark without touching your test file. That's the gap continuous monitoring exists to close — the five probes tell you where your boundaries stand right now; monitoring tells you whether they moved.

Common questions

Can't I just run these once in staging?

Staging answers them for staging. Environments diverge — different migration histories, manual fixes, restore accidents — and production is where exposure is real. Run first in staging during development, then run the identical set against production as its own event; divergence between the two results is itself a finding about environment drift.

Do I need real JWTs for the authenticated tests?

You need valid tokens for accounts you control, obtained through your own auth flow — which is deliberately part of the test, since token issuance is step one of the authorization chain. Expired or malformed tokens should fail with 401s; if they don't, that's a sixth finding worth having.

One of my tables is genuinely public-read. Should Test 1 fail there?

No — Test 1's expectation is per-table intent, not blanket secrecy. For genuinely public tables, the expectation shifts: anon may read published columns but must not read unpublished ones (check with a known-draft row) and must fail writes. Deliberate public access still deserves deliberate proofs; anonymous access done deliberately's cluster covers the design side.

We found a failure. Fix the policy or fix the feature?

Fix the policy, then fix whatever breaks. Every one of these failures means the database admitted something your product never promised anyone — features built on that admission were borrowing unowned access. The remediation SQL for each failure class is small and mechanical (the findings reference) includes worked examples, and the breakage it causes in your app is the sound of assumptions being paid off.

Should views be tested separately?

Yes — a view is another API surface with its own exposure semantics. Depending on Postgres version and the view's security_invoker setting, it can bypass underlying table policies entirely or inherit them; probe the view endpoint as anon and as Bob exactly as you would a table. The views guide covers which behavior your project's views have.

Run the free scan on your own project — paste your app URL, and see your anonymous surface as the outside world sees it, with proposed remediation for every finding.

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

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.