Building a Credit Ledger with Postgres for Treasure It
How Treasure It's Postgres credit ledger keeps balances and history in sync, reserves credits for pickups, and handles retries without charging twice.
tl;dr
Treasure It was initially built for free item exchange. Something closer to swap meet, Trash nothing or Buy Nothing. We decided to pivot the product towards allowing people to buy and sell. We needed a way to charge for a match without handling the payment for the item itself to stay competitive with Craigslist and Facebook Marketplace.
The solution was a credit ledger in Postgres.
Credits became the in-app currency of Treasure It for both free and buying and selling.
The goal of credits is to incentivize good behavior (Replying promptly, Limiting reschedules, Mitigate ghosting, etc), while being the primary revenue driver.
Intro
Treasure It takes a unique approach to commerce because our users have the luxury of meeting each other face-to-face for hyperlocal peer-to-peer exchange, rather than shipping something across the country.
This means paying in cash or Venmo, which is the preferred way to pay for most users while making us competitive with Facebook Marketplace and Craigslist.
But the platform still needs to make money, and people need a reason to show up. If Bob spends an afternoon waiting for Alice to collect a chair and she never arrives, the item price doesn't account for his wasted time. That was the product thinking behind our credit system.
Buyers and sellers pay each other for items in person, and you use platform credits to pay for the match itself. Credits become the main policy and enforcement mechanism for taking action on the platform for both free items and items that are for sale.
If you only exchange free items and you're never inconsiderate to the other person, you'll never have to pay for credits. Users can earn credits by inviting friends.
The application has policies that decides whether Alice owes a fee or Bob should get a refund based off a number of different variables. We use the Decider pattern for those rules, which I cover in Turning Marketplace Policies into a Decider (Comming Soon!). This Post covers the underlying ledger.
Commerce Requirements Get Tricky Real Fast
A credit balance sounds like a single number on a user profile. Add to it when someone buys credits, subtract from it when they make a match.
There's a bit more to think about when two people are trying to coordinate a pickup:
- When you accept a match, your credits need to be held so you can't spend them on three other items at the same time.
- If the other person backs out under a rule that refunds you, you should get those credits back.
- Only when the pickup happens are those credits gone for good.
The list goes on...
Making something that actually covers all the edge cases of commerce gets complex quickly.
A single credits column can't tell you which credits are locked up in an active match, why a balance dropped, or whether a Stripe webhook just fired twice.
Walking through a match
The credit is valued at $1. For items that are bought and sold, 5% of profit is required on each side of the transaction. If a coffee table is bought for $100, $5 of credit needs to be reserved on both sides of the transaction for both the buyer and seller as a bond.
| Operation | Alice: available | Alice: reserved | Bob: available | Bob: reserved |
|---|---|---|---|---|
| Alice and Bob each buy five credits | 5 | 0 | 5 | 0 |
| Each reserves five credits for the $100 table | 0 | 5 | 0 | 5 |
| The match is cancelled and both holds released | 5 | 0 | 5 | 0 |
| Each reserves five credits for a new $100 match | 0 | 5 | 0 | 5 |
| Complete that pickup and spend both holds | 0 | 0 | 0 | 0 |
Reserving a credit is a hold. It stops Alice from double-spending that credit on another offer while keeping it around in case the match falls through and she's owed a refund. Completing the pickup spends the reserved credit.
Keeping the balance and the paper trail together
Everything runs through two tables:
match_credit_accountsholds each profile's currentavailableandreservedbalances.match_credit_ledgeris the paper trail. An append only table. Every time an account changes, we insert a row with the deltas, the balances after the move, the reason, the related purchase or offer thread, and an idempotency key.
Having a dedicated row in match_credit_accounts gives the app a fast way to read your current balance. More importantly, it gives Postgres a single row to lock when credits move.
The data model
Here is a diagram of how the account and ledger tables fit in with purchases, credit packs, profiles, and offer threads (trimmed down to the columns relevant to this post):
Note: Both match_credit_accounts and match_credit_ledger point to profiles rather than having a direct foreign key between the account and ledger tables. Keeping the account balances in sync with the ledger history is handled inside our Postgres functions. Balances and deltas in the account and ledger tables are stored in milliunits (1,000 per credit), while credit_count on packs and purchases is whole credits and price_cents / amount_cents are in cents. We save the purchased credit count and price on match_credit_purchases at checkout so updating a pack in the catalog later won't rewrite old purchase history.
Reading the ledger rows
Here is Alice's match from earlier, this time as rows in match_credit_ledger. We store credits as integer milliunits, with 1,000 milliunits per credit, so we never have to deal with floating-point math.
A signed 32-bit Postgres integer can hold up to 2,147,483,647 milliunits. Divide by 1,000 milliunits per credit and you get 2,147,483.647 credits. A hold equal to 5% of the sale price would reach that limit on a roughly $43 million item. That's plenty of room for one match on Treasure It.
| Reason | Available delta | Reserved delta | Available after | Reserved after |
|---|---|---|---|---|
purchase_grant | +3000 | 0 | 3000 | 0 |
reserve | -1000 | +1000 | 2000 | 1000 |
release | +1000 | -1000 | 3000 | 0 |
reserve | -1000 | +1000 | 2000 | 1000 |
consume | 0 | -1000 | 2000 | 0 |
Notice how reserve and release move the exact same amount between available and reserved. purchase_grant adds to available, and consume drains reserved once the pickup is done.
As long as every balance change goes through the ledger functions, the account row always equals the sum of its ledger rows.
account.available = sum(ledger.delta_available);
account.reserved = sum(ledger.delta_reserved);
We keep those in sync by updating the account row and inserting the ledger row inside the same transaction. App code never edits old ledger rows in place. A release or refund appends a new movement, so we can still see the original charge and what happened afterward.
A penalty can debit available credits directly, even if that pushes the balance below zero. This was an intentional design and product decision. If the user has 2 credits but they get chosen for 10 items, they should be able to pick up all of those items. If the person who is giving these items away is limited to what they can give away based off of the user's credit balance, they're going to go somewhere else. Being able to go negative gives users flexibility To get as many freedoms as they want without needing to purchase more credits. They just need to reply promptly and show up.
Reserving credits has a stricter rule, though. Alice needs enough available credits to cover a new hold.
Locking the account row when reserving credits
Let's look at reserve_match_credit, the Postgres function that puts a hold on your credits.
The function takes a profile UUID, an offer-thread UUID, an idempotency key, metadata, and an integer amount in milliunits. It returns the profile ID, current available and reserved balances, and the ledger row ID.
Where the transaction starts
Our server calls these functions through Supabase RPC. PostgREST runs each request inside a database transaction. The function's writes commit together when that request succeeds. An unhandled database error rolls them back.
The BEGIN and END inside a PL/pgSQL function delimit its body. The surrounding request owns the transaction. Calling a second SQL function from inside the first keeps its work in that same transaction. Making two separate RPC requests would create two separate transaction boundaries.
That matters when accepting a match reserves credits for both Alice and Bob. The enclosing match function calls the ledger helpers inside one database transaction. If Bob's reservation fails, Alice's reservation from that attempt rolls back too.
Why a Postgres function instead of an ORM transaction?
An ORM transaction could keep the balance update and ledger insert atomic too. If all the statements run on the same database transaction, Postgres provides the same commit and rollback guarantees. We'd still need the row locks, balance checks, and idempotency rules shown here. Moving the code into SQL doesn't remove those requirements.
For this implementation, the useful part is having one database operation behind an RPC. The request handlers, Stripe webhook, and background workers use Supabase clients. An application-level try block around several client requests wouldn't turn them into one database transaction. A Postgres function lets each caller invoke the complete operation in one request, and lets larger match functions compose the ledger helpers inside their own transaction.
An ORM-based version could instead use a direct Postgres connection and a shared transaction-aware service. That would be a reasonable choice if those callers already shared that data-access layer. Here, the function gives them a common mutation boundary, with database permissions controlling who can invoke it.
There is a cost. Changes to these functions go through SQL migrations, and TypeScript checks alone can't verify their behavior. Database integration tests need to cover concurrent requests, rollback, and retries. I keep the product rules in the deciders where I can test them as ordinary values, and use Postgres for the locks, persisted-state checks, and writes that have to succeed together.
Lock, check, and write
When you call it, it makes sure your account row exists, locks that row, checks whether we've already processed this exact request, checks that you can afford the hold, updates your balances, and writes the ledger entry.
First things first, we lock the account row:
SELECT *
INTO v_account
FROM public.match_credit_accounts
WHERE match_credit_accounts.profile_id = p_profile_id
FOR UPDATE;
Why lock the row? Say Alice has one credit left and tries to accept two different matches at the exact same time. Without FOR UPDATE, both requests could read available_credits = 1000 in the app and let both holds through. With the row lock, the second transaction has to wait until the first one finishes and commits. By the time the second transaction reads the row, available_credits is 0.
After checking for a retry, which we'll get to next, the function checks if there are enough credits and updates the account:
IF v_account.available_credits < v_credit_milliunits THEN
RAISE EXCEPTION 'MATCH_CREDIT_INSUFFICIENT_CREDITS'
USING ERRCODE = 'P0001';
END IF;
UPDATE public.match_credit_accounts AS account
SET
available_credits = account.available_credits - v_credit_milliunits,
reserved_credits = account.reserved_credits + v_credit_milliunits,
updated_at = now()
WHERE account.profile_id = p_profile_id
RETURNING account.* INTO v_account;
Next comes the history row, still inside reserve_match_credit:
INSERT INTO public.match_credit_ledger (
profile_id,
offer_thread_id,
reason,
delta_available_credits,
delta_reserved_credits,
balance_available_after,
balance_reserved_after,
idempotency_key,
metadata
)
VALUES (
p_profile_id,
p_offer_thread_id,
'reserve',
-v_credit_milliunits,
v_credit_milliunits,
v_account.available_credits,
v_account.reserved_credits,
p_idempotency_key,
coalesce(p_metadata, '{}'::jsonb) || jsonb_build_object(
'creditUnit', 'milli',
'creditMilliunits', v_credit_milliunits
)
)
RETURNING * INTO v_ledger;
The RETURNING from the account update supplies the after-balances for this row. If this insert fails, Postgres rolls back the balance update too. Alice can't end up with a successful hold and no record of which match owns it.
When activating a match, the SQL function locks both match_credit_accounts rows sorted by profile_id. Consistent ordering avoids Alice-first and Bob-first requests waiting on each other's account locks. Other rows still need consistent lock ordering too. The function also re-checks the offer state, accepted fee, and items inside the transaction.
Making retries safe with idempotency keys
Networks drop and background jobs retry. An idempotency key lets the app safely repeat a call without charging someone twice.
Just checking "does this idempotency key already exist?" isn't enough, though. When reserve_match_credit finds an existing key, it also checks that the profile, offer thread, reason, and credit deltas match the original row. If caller code accidentally reuses an idempotency key for a different match or a different amount, Postgres throws a conflict error instead of silently pretending the new call worked.
This branch runs after locking the account and before moving any credits:
SELECT *
INTO v_ledger
FROM public.match_credit_ledger
WHERE idempotency_key = p_idempotency_key;
IF FOUND THEN
IF v_ledger.profile_id <> p_profile_id
OR v_ledger.offer_thread_id IS DISTINCT FROM p_offer_thread_id
OR v_ledger.reason <> 'reserve'
OR v_ledger.delta_available_credits <> -v_credit_milliunits
OR v_ledger.delta_reserved_credits <> v_credit_milliunits THEN
RAISE unique_violation USING MESSAGE = 'MATCH_CREDIT_IDEMPOTENCY_CONFLICT';
END IF;
RETURN QUERY SELECT
p_profile_id,
v_account.available_credits,
v_account.reserved_credits,
v_ledger.id;
RETURN;
END IF;
There's a second check for an existing hold on the same profile and offer thread. The function sums that pair's reserved deltas. If a hold already exists for the requested amount, it returns the earlier reservation; if the amount differs, it raises MATCH_CREDIT_STALE_RESERVATION. A fresh request key alone isn't permission to reserve the same match twice.
When a retry is legitimate, the function returns the original ledger row ID alongside the account's current balances. Meanwhile, the ledger row itself keeps the available_after and reserved_after snapshot from when that move originally happened.
Webhook retries have a similar trap. It's tempting to use the Stripe webhook event ID as your idempotency key when someone buys credits, but Stripe can send more than one event type for the same payment. Instead, we log the webhook event ID separately and tie the credit grant key directly to the PaymentIntent:
stripe:payment_intent:<id>
If the database commits the credit grant and the server crashes a millisecond later before returning 200 to Stripe, Stripe is going to send that webhook again. Keying the grant off the PaymentIntent means the retry sees the existing grant and exits cleanly instead of handing out double credits.
Connecting Stripe payments to credit grants
Stripe handles payment for the credit packs. Alice still pays Bob directly for the chair. That distinction gives us a smaller payment flow to reason about. A successful credit-pack purchase needs to add the purchased credits exactly once, even if Stripe sends several events or our server loses a response.
Save the purchase before opening Checkout
The platform billing service loads the active pack and Alice's Stripe customer, then creates a pending match_credit_purchases row. It saves the pack's credit count, price, currency, and owner at that moment. Later changes to the pack catalog won't change what this purchase buys.
The purchase ID travels into Stripe Checkout as client_reference_id, with purchase metadata attached to both the session and PaymentIntent. The service uses match-credit-checkout:<profile-id>:<client-idempotency-key> to deduplicate local purchase creation and the Stripe request. If the purchase already has a checkout session, it can retrieve that session instead of creating another one.
The browser returning from Checkout isn't proof of payment. Credits are granted through the webhook path.
Verify the event and what was purchased
The platform billing webhook route reads the raw request body and verifies stripe-signature with STRIPE_PLATFORM_WEBHOOK_SECRET before processing the event.
The handler accepts paid Checkout completions, asynchronous Checkout payment successes, and successful PaymentIntents. A Checkout event must describe a paid session in payment mode; a PaymentIntent must be succeeded. The webhook service compares the payment amount and currency, profile, pack, and credit metadata with the stored purchase. It also checks any Stripe session or PaymentIntent identifiers already attached to that purchase.
Only after those checks does it request the grant:
const grantResult = await this.matchCreditService.grantPurchase({
purchaseId: purchase.id,
idempotencyKey: `stripe:payment_intent:${params.paymentIntentId}`,
metadata: this.createGrantMetadata(params),
});
The webhook event ID and this key solve different problems. stripe_webhook_events tracks processing of a particular delivery. The PaymentIntent key identifies the payment across different event types. A Checkout completion and a PaymentIntent success can both describe the same purchase without earning Alice two grants.
Grant credits in one database transaction
The grant_match_credit_purchase function locks the purchase row, checks the request key, and also looks for a prior purchase_grant for that purchase. A different key can't grant the same purchase again. A fresh grant rejects a failed, cancelled, or refunded purchase.
It calculates the amount from the saved credit_count * 1000, creates the account if needed, and locks it. Then it updates the account and purchase:
UPDATE public.match_credit_accounts AS account
SET
available_credits = account.available_credits + v_credit_milliunits,
lifetime_purchased_credits = account.lifetime_purchased_credits + v_credit_milliunits,
updated_at = now()
WHERE account.profile_id = v_purchase.profile_id
RETURNING account.* INTO v_account;
UPDATE public.match_credit_purchases
SET
status = 'succeeded',
purchased_at = coalesce(purchased_at, now()),
granted_at = coalesce(granted_at, now()),
updated_at = now()
WHERE id = v_purchase.id;
The function also inserts the purchase_grant ledger row with the purchase ID, positive available delta, after-balances, and idempotency key. All three changes commit together. SQL trusts the privileged webhook's payment verification; it doesn't call Stripe itself.
If the grant commits and later webhook bookkeeping fails, a retry can reach the same grant safely. Receipt email and optional purchase analytics happen afterward, with their own error handling. Granting credits successfully doesn't prove that an email was delivered.
Buying credits also doesn't automatically complete a waiting match in this webhook path. The funding action separately checks the available balance and deadline before reserving the required credits. Alice can own the credits she bought even if a particular match's funding window has expired.
Keeping cents and credit milliunits straight
Item prices and credits use different units. Item prices are stored in integer cents, while the ledger stores credits in integer milliunits, even on a couple of older columns whose names still end in _credits.
Here is the function that calculates the base credit fee from an accepted offer price:
export const resolveBaseMatchFeeCreditMilliunits = (
acceptedAmountCents: number
): number => Math.ceil(acceptedAmountCents / 2);
For a $20 item, 2000 cents comes out to 1000 milliunits, or 1 credit per person. For $20.01, 2001 cents rounds up to 1001 milliunits.
When an offer is accepted, we snapshot that result into base_match_fee_credit_milliunits on the offer thread. When the pickup finishes days later, settlement reads that snapshot rather than recalculating the fee. That way, if we ever change our fee formula, we won't pull the rug out from under matches that are already in progress.
Starting the four-hour response window
Free pickups have a different problem from paid matches. Bob picks Alice to receive a free chair and proposes pickup times. If she never responds, Bob is left waiting instead of offering it to someone else. We give Alice four hours to respond, but we pause that clock overnight.
The timer starts in the selection transaction. For each eligible free item, the database inserts a row into free_recipient_response_obligations with the recipient, item, proposed times, opened_at, due_at, and penalty amount. The selection function calculates the deadline with:
private.calculate_free_recipient_response_due_at(v_now, 'America/New_York', 240)
Those 240 minutes count only between 8:00 a.m. and 9:45 p.m. each day in New York. If Bob selects Alice at 8:45 p.m., she gets one hour that evening and the remaining three the next morning. Her deadline is 11:00 a.m. The SQL clock function converts each day's opening and closing times using the named timezone and carries unused minutes into the next day.
The row stores required_working_minutes = 240 and penalty_milliunits = 2000. That's a two-credit penalty per item, so a handoff containing several items can have several obligations. Persisting the deadline means a server restart doesn't reset Alice's clock.
Cron finds deadlines that have passed
We run these jobs through Trigger.dev. The free-response settlement task is registered like this:
export const FREE_RESPONSE_SETTLEMENT_CRON = "*/5 * * * *";
export const freeResponseSettlementTask = schedules.task({
id: "free-response-settlement",
cron: FREE_RESPONSE_SETTLEMENT_CRON,
run: async () => runFreeResponseSettlement({}),
});
The task creates a server context with a service-role Supabase client. Its worker loads due obligations, asks the Free decider for the outcome, and sends the expected facts and credit event to settle_free_response_obligation.
The deadline and the cron tick do different jobs. Alice's window ends at the stored due_at; a later worker run applies the settlement. We don't start an in-memory four-hour timer or depend on a job waking up at the exact expiry instant.
Before charging, the settlement RPC locks the relevant gift, item, pickup-item rows, and obligation, then rechecks whether the item is still free, chosen for Alice, and has no active pickup. An obligation that's no longer chargeable can be cancelled without a debit. For a penalty, it also locks Alice's credit account and checks the expected balance.
The balance update, free_response_penalty ledger insert, and obligation's transition to penalized happen in the same transaction. The movement uses free-response:obligation:<id>:penalty as its key. If the candidate facts changed, the RPC can return a stale result instead of applying the old decision.
A separate reminder job runs every five minutes too. Its claim function makes reminders eligible halfway through the working-time window, groups them by handoff and recipient, and uses delivery claims with leases to coordinate workers.
Paid funding has its own clock
The four-hour response window belongs to free items. If Alice accepts Bob's paid offer while Bob lacks credits, the paid funding decider gives him five elapsed hours from acceptance, with a reminder at 150 minutes:
export const OFFER_FUNDING_WINDOW_HOURS = 5;
export const OFFER_FUNDING_REMINDER_MINUTES = 150;
The paid funding worker runs on another five-minute Trigger.dev schedule. It finds awaiting_credits threads whose accepted_at is at least five hours old and submits ExpireAwaitingCredits through the funding service. The decider rejects funding completion at or after the deadline even if the expiry worker hasn't run yet. Expiry releases Alice's hold and charges Bob the match fee from available credits.
These schedules are separate from the completion-repair job below. Each looks for a specific persisted condition and applies the corresponding policy.
Cleaning up unfinished settlements
The credit watcher looks for a specific mismatch. The pickup is marked taken or completed, but its offer thread still has reserved credits in the ledger. The Trigger.dev task is configured for every 15 minutes unless COMMERCE_CRON_MANUAL_ONLY disables the schedule. It defaults to 100 candidates per run.
Find work from persisted facts
The worker calls find_incomplete_off_platform_match_credit_pickups. At the center of that function is this query:
SELECT ledger.offer_thread_id
FROM public.match_credit_ledger AS ledger
WHERE ledger.offer_thread_id IS NOT NULL
GROUP BY ledger.offer_thread_id
HAVING coalesce(sum(ledger.delta_reserved_credits), 0) > 0
A reserve adds to that sum; a release or consume subtracts from it. A positive result means the thread still holds credits. The full finder function joins those threads through their items to completed pickups. It requires an accepted event and an accepted or completed thread, orders candidates by pickup update time, and clamps the batch size between 1 and 500.
The finder is a read. Its results can become stale before processing, so the write function must recheck them. The batch is also not one large transaction. The service processes each candidate with a separate RPC and catches failures per pickup, recording them and continuing with the rest.
Retry the completion transaction
The retry service calls complete_accepted_match_pickup, the same RPC used for completion. That function locks and validates the pickup and related match rows, applies completion changes, and settles the reservations within its transaction.
For Alice's side, the settlement branch reads:
SELECT coalesce(sum(delta_reserved_credits), 0)::integer
INTO v_buyer_reserved_delta
FROM public.match_credit_ledger
WHERE profile_id = v_thread.buyer_profile_id
AND offer_thread_id = v_thread.id;
IF v_buyer_reserved_delta > 0 THEN
SELECT ledger_id
INTO buyer_consume_ledger_id
FROM public.consume_match_credit_reservation(
v_thread.buyer_profile_id,
v_thread.id,
'off-platform-match:' || v_thread.id::text || ':buyer:pickup-complete-consume',
v_metadata || jsonb_build_object('role', 'buyer', 'phase', 'pickup_complete_consume')
);
This is an excerpt from the completion function. Its other branch looks for an existing consume row when no hold remains and raises a stale-reservation error if it can't find one. It repeats the same process for Bob.
Inside consume_match_credit_reservation, Postgres locks the account, checks the request key, and calculates the outstanding reservation for this profile and thread. With no explicit amount supplied, it consumes that outstanding hold. It verifies both the thread's hold and the account's reserved balance before decrementing reserved credits and inserting a consume row. A different match's hold cannot stand in for a missing reservation here.
That gives the watcher a useful retry boundary. If settlement fails inside the completion RPC, its database changes roll back together. If the commit succeeds but the response is lost, the next scan normally finds no outstanding hold. A candidate already loaded by another run still has to pass the completion function's checks. The watcher repairs unfinished credit settlement; notification delivery has a separate lifecycle.
Wrapping it up
Keeping the balance update and its ledger entry in the same transaction has been the most important part of this design. A hold can't succeed without a record of which match it belongs to, and a payment retry can't quietly grant another set of credits.
Alice still pays Bob in person for the coffee table. Our part is keeping track of the credits they set aside for the pickup. If Alice asks where those credits went, I can point to the match and show her when we reserved them and whether we released or spent them.