Why marketplace accounting becomes so hard

Financial reports are not just another dashboard. For most companies they are the only numbers that truly matter. Revenue, liabilities, balances, expenses - these decide whether the business is actually working. Everything else, from funnel analytics to engagement charts, exists to move those numbers in the right direction.

An error in a recommendation algorithm costs you some conversion. An error in an email campaign costs you some retention. An error in your financial numbers costs you the thing you cannot buy back: people stop believing the system. A user will forgive a broken layout. A user will not forgive a payout that is $12 short, because the next question they ask is not “is this a bug?” but “what else is wrong?”

Most platforms believe they have accounting. In practice they have a few tables and a lot of SQL that reconstructs reports from them. At the beginning this is not a mistake - it is the correct decision. The numbers match, the queries are short, and nothing about it feels risky.

Then the product needs flexibility: refunds, credits, discounts, fee waivers, disputes. The system does not break. It starts to resist. Every new rule takes longer than the last one, every report becomes a little more fragile, and every change starts to feel like something you would rather do on a Tuesday morning than a Friday afternoon.

This guide walks through exactly where that turn happens. We build the simple version, add two real business rules to it, and watch what each one costs. The examples come from years of building payment and payout systems for marketplaces. The schema in the first section is roughly where all of them started.

The model that works

The specifics of what a marketplace sells barely matter: services, physical goods, rentals, freelance work. The financial flow underneath is nearly identical: a buyer pays, the platform takes a cut, and the seller is paid later.

You need to track what the buyer paid, what the platform keeps, and what the seller is owed. So you write down the obvious tables: order, payment, payout. Focus on payment:

create table payment (
  id                    uuid primary key,
  order_id              uuid not null,
  buyer_id              uuid not null,
  seller_id             uuid not null,
  charge_amount         bigint not null,  -- what the buyer paid
  platform_fee_amount   bigint not null,  -- our commission
  processing_fee_amount bigint not null,  -- what the processor kept
  status                text   not null,  -- pending | completed | failed
  created_at            timestamptz not null default now()
);

Everything else is derived from those three amounts. At this stage you accept cards only, you know the processor’s rates, and you have decided to fold the processing fee into your own commission so the seller sees one number instead of two.

Take a $1,000 order. Your commission is 10%. Stripe charges 2.9% + $0.30.

Two queries answer the two questions anyone will ask:

-- What did we earn?
select sum(platform_fee_amount - processing_fee_amount) as revenue
from payment
where status = 'completed';

-- What do we owe sellers?
select sum(charge_amount - platform_fee_amount) as seller_payable
from payment
where status = 'completed';

This design has real advantages and it is worth naming them, because they are what you are about to trade away. The data model is small. Each query fits on a screen. Every number traces back to one row you can open and read. You can look at a figure and understand where it came from without asking anyone.

There is one assumption underneath all of it: every metric can be reconstructed from raw columns with SQL. For now that assumption holds perfectly, which is exactly why nobody has any reason to build something more complicated.

The first new rule: refunds

The next thing the product asks for is refunds. And refunds bring one fact that has nothing to do with your code: the payment processor returns the buyer’s money and keeps its fee.

Stripe hands back $1,000. It does not hand back the $29.30. That money is gone regardless of what your schema looks like. So the refund is not a reversal - it is a reversal plus a $29.30 loss that somebody has to absorb.

Who pays the processing fee?

There are only three candidates, and every marketplace picks at least two of them depending on the situation:

• The platform absorbs it. Cleanest experience, and it means a refunded order costs you money.
• The seller absorbs it. Common when the cancellation was the seller’s fault.
• The buyer absorbs it. They receive $970.70 back rather than $1,000.

This is not an edge case you can defer. It is a policy the business will want to set per transaction, sometimes per dispute, sometimes by hand in an admin panel. Which means it is data, not a constant, and it has to be stored:

alter table payment
  add column refund_status text,          -- null | refunded
  add column fee_paid_by   text;          -- platform | seller | buyer

Two columns. That still looks cheap.

What the reports look like now

Here is revenue with one refund rule in it:

select sum(
  case
    when refund_status = 'refunded' and fee_paid_by = 'platform'
      then -processing_fee_amount
    when refund_status = 'refunded'
      then 0
    else platform_fee_amount - processing_fee_amount
  end
) as revenue
from payment
where status in ('completed', 'refunded');

And what you owe sellers:

select sum(
  case
    when refund_status = 'refunded' and fee_paid_by = 'seller'
      then -processing_fee_amount
    when refund_status = 'refunded'
      then 0
    else charge_amount - platform_fee_amount
  end
) as seller_payable
from payment
where status in ('completed', 'refunded');

Look at the last line of each query before you look at the case. The where clause changed. It used to be status = 'completed'.

If you have five reports and you update four of them, the fifth keeps returning a number, and it is the old one. Nothing anywhere tells you it is now wrong. The report does not fail. It just quietly stops being true.

What actually changed

The case tree is the visible cost. It is not the expensive one.

The expensive one is that platform_fee_amount now means two different things depending on another column. For a completed payment it is revenue. For a refunded payment where the seller covered the fee, it is zero and there is a separate negative amount somewhere else. The column name did not change. The data in existing rows did not change. Only the meaning changed, and meaning does not live in the schema - it lives in whichever query someone writes next.

That is the moment the system stops being explainable. Not when it slows down. Not when it breaks. When two people can read the same column name and be equally confident about two different answers.

The second rule does not add, it multiplies

Now the product asks for partial refunds. A $1,000 order where $300 is returned.

Watch what this does. It does not add a branch to the existing logic. It invalidates the shape of every branch already there.refund_status = 'refunded' is no longer a meaningful question. A payment can be 30% refunded, then another 20% refunded next week, under a different fee policy each time, because the two disputes had different causes.

So refunds become their own table:

create table payment_refund (
  id                    uuid primary key,
  payment_id            uuid not null references payment (id),
  refund_amount         bigint not null,
  fee_paid_by           text   not null,
  processing_fee_amount bigint not null,  -- the fee lost on this refund
  created_at            timestamptz not null default now()
);

And the revenue query becomes this:

select sum(
  -- Our commission is returned in proportion to the amount refunded.
  round(p.platform_fee_amount * (1 - coalesce(r.refunded_ratio, 0)))
    - p.processing_fee_amount
    - coalesce(r.fee_absorbed_by_platform, 0)
) as revenue
from payment p
left join lateral (
  select
    sum(refund_amount)::numeric / p.charge_amount as refunded_ratio,
    sum(processing_fee_amount) filter (where fee_paid_by = 'platform')
      as fee_absorbed_by_platform
  from payment_refund
  where payment_id = p.id
) r on true
where p.status <> 'failed';

Three things went wrong in that query, and only one of them is legibility.

• The where clause changed again. Third revision, and by now no one is certain which reports are on which version.
• round() introduced a new class of bug that did not exist before. Refund $333.33 of a $1,000 order and you return 33.333% of a $100 commission. Round it and the parts no longer sum to the whole. Refund the rest later and the platform keeps or loses a cent it never agreed to. One cent is nothing. One cent that nobody can explain, appearing in a monthly report, costs an afternoon every time it is noticed.
• refunded_ratio is computed from charge_amount. The day a discount code is introduced, charge_amount is no longer the number the proportion should be taken against, and this query will keep returning a plausible answer anyway.

That is the property worth naming. The first rule cost you a case branch. The second rule cost you a table, a join, a rounding decision, and a dependency on a column that a future feature will silently redefine. Each rule does not add complexity to the previous one. It multiplies against it.

The seller with a zero balance

There is one more consequence, and it is the one that shows the schema has genuinely run out of room.

The seller covers the $29.30 refund fee. The seller’s balance is $0, because they withdrew yesterday.

You now owe yourself $29.30 from a seller. That is a receivable, a real and ordinary financial concept, and there is nowhere in this schema to put it. payment is about payments. payment_refund is about refunds. Neither one is about “this seller owes us money independent of any single order.”

So you add a fifth table. And now both reporting queries have to join it, and so does the payout job, and so does the seller-facing balance in the UI, and each of those three places has to independently get the sign right.

Everything else behaves the same way

At this point the escalation is the whole finding, and working through each remaining case in detail teaches nothing new. Here is what is still coming, one line each:

• Chargebacks. Like a refund, plus a $15 dispute fee, plus a window where the outcome is unknown.
• Discounts and promo codes. Whose money funded it, the platform’s marketing budget or the seller’s margin? Both are common and they report completely differently.
• Platform credits. Money that is spendable but was never collected.
• Referral bonuses. A fourth participant in a transaction designed for three.
• Fee waivers.platform_fee_amount = 0 and a reason that lives in a comment.
• Multi-seller orders. One payment, several recipients. The row has one seller_id.
• Failed payouts. Money that left the balance, did not arrive, and has to come back.
• A second payment provider. Different fee structure, different refund behaviour, same columns.
• Taxes. Collected by you, owed to someone who is not the buyer, the seller, or the platform.

Each one adds conditions to logic that is already conditional. This is why the complexity curve bends: you are not adding features to a system, you are adding combinations.

The question that has no query: what do we owe?

Everything above is the same failure wearing different clothes: a rule arrives, the queries get worse. This next one is a different kind of problem, and it is usually the one that finally forces the rewrite, because it is not a query getting uglier. It is a question the schema was never built to answer.

Engineers model orders. An order is a product concept: it has a natural owner, a natural table, a lifecycle everyone agrees on. The business does not run on orders. It runs on obligations. “How much do we owe our users right now?” is the question a CFO will ask in the first week and keep asking forever.

It sounds like one line of SQL. It is not, because money does not become an obligation all at once. Follow a single $1,000 order:

1. Payment processing. The buyer paid. The card capture is pending, or it is a bank transfer that settles in two days. The money is not in your account, and an obligation is already forming.
2. Settled, not confirmed. The funds are yours to control. The service has not been delivered or the buyer has not confirmed it. You owe this money to somebody, the seller if it completes or the buyer if it is cancelled, and which one is not yet decided.
3. Confirmed. Now it is determinate: $900 owed to seller X, withdrawable whenever they ask.
4. Payout in flight. It has left your balance and has not landed. Payouts fail and come back.
5. Paid. The obligation is settled and gone.

Each of those transitions is triggered by something different: a processor webhook, a buyer clicking a button, an auto-release cron after fourteen days, a bank file the next morning. None of them is “somebody updated the order.”

Now look at what the schema records for all five:

Four financially distinct states collapse into the word completed. That column was never lying - it describes the order, and the order really is complete. It simply has nothing to do with where the money is, and the money is what the CFO is asking about.

So the answer gets reconstructed from whatever flags happen to exist:

select sum(charge_amount - platform_fee_amount) as owed_to_sellers
from payment
where status = 'completed'
  and confirmed_at  is not null
  and refund_status is null
  and payout_id     is null;

Four conditions, each one a business rule somebody had to remember, and nothing anywhere enforces that the four flags stay consistent with each other. Worse, the query is wrong in a way that is hard to see: it returns zero for stages 1, 2 and 4. Those stages hold real money with real obligations attached, and the number that gets pasted into a board deck silently excludes all of them.

And the real question was never one number anyway. “What are our liabilities?” means the breakdown: how much is still settling, how much is held pending confirmation, how much is withdrawable today, how much is in flight to a bank. Each of those is a different amount with a different risk attached, and answering it from this schema means writing four more queries that each re-derive a business rule from a different combination of flags.

Then a webhook arrives late for a payment from last month, and last month’s answer changes.

This matters more than it sounds. A platform holding $2M of which $1.8M is owed to its users does not have $2M - it has $200k and a $1.8M obligation, and spending the difference is one of the classic ways a marketplace dies. The uncomfortable part is that the schema cannot tell you which of those two numbers you are looking at. Both are true statements about the same balance, and there is one column for it.

What this feels like from the inside

The system still works. Nothing is on fire. What changes is quieter, and it sounds like five questions:

“Is this number actually right?” Nobody can say yes without checking. Payments go through, payouts are sent, reports render. But if you replayed everything from scratch, would you get the same answer? Nobody knows, so nobody asks.

“Where does this number come from?” Finance asks why revenue is what it is, and the honest reply is “let me check” followed by three joins and an assumption. A number you cannot explain is a number you cannot defend, and eventually one you cannot use.

“Does net_amount include the fee?”Naming decayed. seller_transaction.net_amount: net of what, before or after which fee, from whose perspective? It was obvious when it was written. Two policy changes later it is a question people ask in Slack.

“Will this break the reports?” Adding a fee, changing a commission, introducing credits: each one now carries risk that nobody can bound. So changes slow down, or get avoided, or ship with a quiet hope. The financial system becomes the thing the product has to route around.

“Why doesn’t this match Stripe?” You compare your database, the processor, the payouts, and the reports. There are small differences. Finding them means debugging historical data by hand, and it takes hours or days, every time.

Underneath all five is one sentence: the system works, but you do not trust it, and every change makes that worse.

The actual root cause

It is tempting to conclude that the problem was SQL, or the schema, or that someone should have designed it better on day one. None of that is true. The first version was correct for the product it was written for. What follows is structural, and it would have arrived no matter who wrote it.

You are storing conclusions, not events

platform_fee_amount is not a thing that happened. It is an answer: the answer to “what did we keep?”, computed at write time, under the business rules that existed the day that row was inserted.

When the rules change, the meaning of the column changes with them. The rows already in the table do not. So every query has to reconstruct which rule applied to which row, and the growing case tree is exactly that: rule archaeology, encoded in SQL, repeated in every report that needs it.

What actually happened is smaller and more stable: on this date, $1,000 moved from the buyer to us, $29.30 of it went to Stripe, $900 became owed to the seller, and $70.70 became ours. Those facts never change, no matter what next quarter’s refund policy says. Store those, and the conclusions become queries over facts rather than facts you have to re-derive.

A row is fixed-width, a money event is a list

The second structural problem is in the shape of the table, not its content.

A row has a fixed set of named participants: buyer_id, seller_id, one platform fee, one processing fee. That is a hard cap of four parties in one transaction, decided at schema-design time.

Real money events do not respect that cap. Add a referrer and you need a column. Add tax and you need a column. Add a second seller and you need a table. Every one of those is a migration plus a rewrite of every query that reads it.

Money movement is a variable-length list of participants, and a table row is a fixed-length tuple. That mismatch is not something you can name your columns out of. Events involving N parties need N rows.

There is a third symptom of the same thing, and it is the most direct one. Your platform holds $1,000 in Stripe and owes $900 of it to a seller. Both statements are true about the same money at the same instant. charge_amount - platform_fee_amount can express one of them. A single row has room for one truth, and correct accounting requires two.

This is also the whole reason the liabilities question has no query. An obligation is not a property of an order - it is a second fact about the same money, and it changes on its own schedule, driven by events the order does not know about. A schema that records orders can only ever infer it. Recording both facts, separately and explicitly, is not a reporting feature you add later. It is the thing that makes the question answerable at all.

What a financial system needs instead

You do not need an ERP, and you do not need to hire an accountant to write code. What you need is a set of properties. Here is the checklist, and every item is testable against a system you already have:

1. One place where money movement is recorded. Not payments here, refunds there, adjustments in a third table. One log, one shape, every movement in it.
2. Records are append-only. Nothing is ever edited or deleted. A correction is a new record that references the original. This is what makes history replayable and errors traceable rather than overwritten.
3. Every reported figure can name the rows that produced it. If you cannot drill from a number to the list of records behind it, you cannot defend that number to anyone who asks.
4. A new business rule adds records, not columns and not branches. This is the property that decides whether your complexity curve is linear or exponential. It is worth optimising for above almost everything else.
5. The system can state two facts about the same money at once. “We hold it” and “we owe it” are independent facts and both have to be recordable.
6. Balances are derived from the records. Cache them if you must, but then reconcile the cache against the records on a schedule and alert on any drift. A cached balance nobody checks is just the original problem wearing a hat.
7. Money is stored as integers in minor units. Never floats. 0.1 + 0.2 is not 0.3, and a rounding error in a financial system is permanent.

And the things to avoid, which are mostly the inverses:

• A balance column with no history behind it.
• Business rules living in report queries. If the rule is in the SQL, every report is a place the rule can be wrong.
• Column names that encode a policy (net_amount, final_amount, adjusted_total). They are accurate exactly once.
• The same figure computed in more than one place. It will diverge, and the only question is when you notice.
• Deleting or editing financial rows, ever, for any reason.

If you read that list and thought “this sounds like accounting” - yes. This is what double-entry bookkeeping is, and it is worth knowing that it is not a reporting format or a compliance requirement. It is a data structure, invented specifically to make an unbalanced write impossible and a wrong figure traceable, and it has been in production for about five hundred years.

Where this goes next

Simple tables work, right up until the product needs flexibility. Then each new rule multiplies against the last one, reports become fragile, naming becomes ambiguous, and the system stops being something anyone can hold in their head.

You can keep patching it. More tables, more conditions, more careful queries - it will keep working for a while, and plenty of businesses run on exactly that. But it does not get better with age, and that is the unusual part: most systems improve as you learn the domain. This one degrades as the domain gets richer.

The fix is double-entry bookkeeping, where “debits equal credits” is a runtime check rather than a convention, and an incorrect write is rejected at the boundary instead of absorbed into a report somebody reads three months later.

It is not a big framework. It is five account categories, one rule about which side makes each of them go up, and a constraint that runs on every single write. Learn those three things and the schema above stops being something you patch and starts being something you can reason about.

Originally published at ledfra.com.

I write these while building Ledfra, a double-entry ledger with an API and real financial statements on top. If you would rather poke at the model this article argues for than read about it, there is a live demo with a seeded marketplace in it - no signup.