Shipping a two-sided marketplace on Supabase

A two-sided marketplace looks like one product and is two, joined at a transaction. Get Yachties has crew on one side and yacht employers on the other, and almost every hard decision in the build came from the fact that those two groups must see different truths about the same row in the same table.

We built it on Supabase, which means Postgres row-level security is doing work that in a conventional application would sit in a service layer. That is the right choice and it changes how you have to think.

With a thin client talking straight to the database, there is no server-side code between a user and a table to enforce who sees what. The policies are the enforcement. Get one wrong and you have not introduced a bug — you have published a dataset.

Three rules came out of building it, and all three are the sort of thing you only adopt after being frightened once.

1. Deny by default, always. Enable RLS on every table at creation, before any data exists. A table that is readable while you are still developing will eventually ship that way, because nobody audits what was never restricted.
2. Policies are per-operation. Select, insert, update and delete each need their own. The frequent mistake is a good read policy alongside an update policy that permits more than intended, so a user cannot see a record they can nevertheless modify.
3. Never trust a client-supplied identifier for authorisation. The policy derives identity from the session, not from a column the client set. Anything else is an authorisation check the caller can answer for itself.

A crew profile is the clearest example. The crew member sees everything on it. An employer who has not unlocked it sees enough to decide whether to — role, experience, availability, vessel type — and none of the contact details. An employer who has unlocked it sees the rest. Same row, three shapes.

The instinct is to handle this in the client by fetching the row and hiding fields. That is not a permission model, it is a display convention, and the data is in the response whatever the interface renders. The version that holds is column-level control at the database, with the restricted view exposed as its own object that the client can query without ever having access to the underlying row.

This costs more up front and it is the difference between a marketplace and a leak. The unlock economics are the entire business model: if the contact details are obtainable without unlocking, there is no product.

The obvious design is a credits column on the employer, decremented on unlock. Do not do this. A single mutable number has no history, cannot be audited, cannot be reconciled against payments, and gives you no answer at all when someone says they were charged twice.

Credits are an append-only ledger of movements — purchased, spent, refunded, expired, granted — each with a reason and a reference to what caused it. The balance is derived by summing, not stored. Every accounting system in history has arrived at this design and the reasons have not changed.

Two consequences worth planning for. Spending must be atomic with the thing it buys, so an unlock and its debit succeed or fail together — this belongs in a database transaction, not in two sequential client calls that a dropped connection can separate. And an unlock must be idempotent: unlocking a profile you already unlocked returns the same access and does not charge again. That protects against a double-tap, a retry and an impatient refresh, all of which will happen on day one.

The technical problems are tractable. The structural one is that neither side of a marketplace has a reason to show up before the other one has. Crew will not build profiles for an empty employer base; employers will not pay to search an empty crew base.

Every marketplace resolves this by subsidising one side, and the choice determines the product. It has to be made deliberately and early, because it changes what you build — the side you subsidise needs a reason to be there that does not depend on the other side existing yet, and that reason is usually a feature, not a discount.

Three rounds, and the pattern across them is consistent: nothing failed in the happy path. Everything failed at a boundary — a state change, a retry, a second device, a role that owned a record and then stopped owning it.

The durable lesson was about how we tested rather than what we found. Testing RLS through the interface tells you what the interface requests. It tells you nothing about what the database would return to a caller who asks differently — which is what an attacker, or a curious user with developer tools, will do. Policies have to be tested by querying as each role directly, including the roles you do not expect to exist: signed out, signed in with no profile, a member of the wrong side, an account mid-deletion.

We now write those as tests before the policy, and they are the only tests in the project that block a deploy.

Model the ledger before the interface. We built unlock as a feature and retrofitted the ledger when the first reconciliation question arrived, which meant migrating live balances into movements and inventing history that did not exist. A day of design at the start would have removed a week later.

And write the permission matrix as a document before writing any policy — every role against every table against every operation, with the answer in each cell. It is tedious and it takes an afternoon. Doing it afterwards means reverse-engineering intent from SQL, and that is where a marketplace ends up with a policy nobody can explain but everyone is afraid to remove.

In a thin-client architecture the security model is not in front of the database. It is the database. That is either the best thing about the approach or the worst, and which one depends entirely on whether you wrote the matrix down.