Same Row, Three Truths: A Three-Party Workflow in Postgres

TL;DR — In a three-party workflow the hard problem isn’t permissions, it’s that one row has three audiences who each need a different version of it. Hold the lifecycle as data in one module so no handler invents a transition. Compute what each party sees as a projection applied on read — not a second “client-visible status” column that can disagree with the first, and not a filter you repeat at every call site. And be honest about the seam: putting a status change and its audit entry in the same call is not the same as putting them in the same transaction.

Key takeaways

• Three parties sharing a record is a projection problem, not an access-control problem. RBAC answers “may you do this”; projection answers “what is this, to you”.
• Hidden internal states belong in one read-time function. A second column drifts; a per-endpoint filter leaks the first time you forget one.
• Normalise every actor type onto one object in middleware, so no handler ever re-parses a credential or branches on who’s calling.
• Assign sequence numbers at the moment they become public, not at creation, or your users will see gaps and ask what happened to the missing one.
• Snapshot the counterparty’s contact details at assignment. A live join means the venue coordinator for last month’s event silently changes when someone edits a profile.

What a three-party workflow is: one record, three audiences, no trust

A three-party workflow is one where a single record is worked by parties who are not on the same team and shouldn’t see the same thing.

The one I built: a client organization asks the platform operator to cater an event. The operator reviews the request and assigns a caterer. The caterer drafts a quote. The operator approves it and sends it on. The client accepts or asks for a revision. Eventually somebody marks the event delivered.

Framed as a form with a status column, that’s a weekend. What makes it real is three things. The same record has to look different to each party. The transitions have to be enforceable rather than advisory. And one of the three is a third-party business that must not see the other two’s internal state.

Before the details, the honest framing: this is built and shipped to a branch, not running in production. The functional requirements came from a product spec someone else wrote — what’s mine is the data model, the state machine, the access model and the API surface. Where a decision below was a commercial call rather than an engineering one, I’ve said so.

Build it as a phase of what you have, not a new namespace

The spec described this as its own system with its own URL namespace. I built it inside the existing B2B module instead, reusing its authentication, response envelope, validation middleware, logging, migration style and naming conventions.

That bought consistency for free. The client organization’s admins already authenticate one way for everything else they do; a second namespace would have meant a second way, and “which login do I use for catering” is a support ticket that never stops arriving.

The thing that made the reuse actual rather than aspirational was writing a concern-by-concern mapping table into the plan before any code — for each thing the feature needs, which existing piece provides it and where that piece lives. Without that list, half of it gets quietly reinvented by the third day.

Resolve the identity question before you write a line

Two different identities in the codebase were both called “super admin”, and working out which one the spec meant had to happen before any code got written.

The spec said “super admin” without saying whose. The codebase had two candidates, and they are not variations of one idea — they’re completely different identities that happen to share a word:

• A platform-operator staff record. The people who run the marketplace.
• A flag on a client-organization admin meaning “super admin within one organization”. The people who run one customer’s account.

Conflating them would have let a customer’s own admin act as the operator across every customer. That’s not a bug you find in code review; it’s a bug you find when somebody reports it.

So I resolved it to the operator table, wrote the resolution into the plan with the evidence I used, and named the two identities differently everywhere downstream. If two things in your domain are both called “admin”, the rename is the fix. Anything else leaves the next person to re-derive which one a given line means, and eventually one of them guesses.

One actor shape, resolved once

Three identity types reach these endpoints. Rather than let each handler work out who’s calling, one middleware resolves all three onto a single normalised actor:

// Illustrative. Every downstream handler sees this shape and only this shape.
req.actor = {
  type: 'CLIENT' | 'OPERATOR' | 'CATERER',
  id, displayName,
  clientOrgId,   // CLIENT only  — the scope its queries are filtered by
  catererId,     // CATERER only — the scope its queries are filtered by
  grants,        // CLIENT only
};

Two things about the resolution order are load-bearing, and both are the kind of detail that reads as fussy until it doesn’t:

A credential that fails verification is rejected outright. It never falls through to a weaker identity path. If a request arrives with a signed token and the signature doesn’t verify, that’s a 401 — not an invitation to try the next scheme and see if the caller qualifies as something less privileged. Falling through launders a bad credential into a valid weaker one.

The stronger identity is checked first. A request carrying two identity claims resolves as the operator, never silently as the caterer. A downgrade is a smaller visible surface, which means it looks like it worked — the caller gets a 200 and a short list, and nobody files a bug. Silent downgrades are worse than loud failures because nothing tells you they happened.

One caveat I’d rather state than let you infer: third-party identity in this design is carried by the caller rather than independently authenticated, inherited from a pattern already used elsewhere in the product. Real authentication for that party was out of scope, and I recorded it in the plan as an accepted risk with a named condition for revisiting and a costed mitigation, sized at roughly one column and one check. A risk written down with a trigger for reopening it is a different artefact from a risk nobody noticed, but it is not the same as a solved one, and I’m not going to present it as one.

The lifecycle is data, in one module

Ten statuses, an explicit map from each status to its legal successors, a terminal set, and the per-status display colour. One file. Nothing else in the codebase gets an opinion about what a legal transition is.

DRAFT ──► SUBMITTED ──► UNDER REVIEW ──► QUOTE SENT
  │           │              │             │  │  │
  │           │              │             │  │  └──► ACCEPTED ──► IN PROGRESS
  │           │              │             │  │           │             │
  │           │              │             │  │           └─────────────┴──► COMPLETED
  │           │              │             │  └──► REVISION REQUESTED ──┐
  │           │              │             │              │             │
  │           │              │             │              └─────────────┘ (back to QUOTE SENT)
  └─► CANCELLED   └─► REJECTED  ◄──────────┘

  terminal: COMPLETED · REJECTED · CANCELLED
  not drawn, to keep it readable: CANCELLED is reachable from DRAFT, SUBMITTED,
  UNDER REVIEW, QUOTE SENT and ACCEPTED. The table is the authority, not this picture.

// Illustrative. Pure data, so the whole machine tests offline with no database.
const NEXT_STATES = {
  DRAFT:              ['SUBMITTED', 'CANCELLED'],
  SUBMITTED:          ['UNDER_REVIEW', 'REJECTED', 'CANCELLED'],
  UNDER_REVIEW:       ['QUOTE_SENT', 'REJECTED', 'CANCELLED'],
  QUOTE_SENT:         ['REVISION_REQUESTED', 'ACCEPTED', 'REJECTED', 'CANCELLED'],
  REVISION_REQUESTED: ['QUOTE_SENT', 'REJECTED'],
  // IN_PROGRESS is an optional operator-internal step: an event can finish
  // directly from ACCEPTED, so both edges are legal.
  ACCEPTED:           ['IN_PROGRESS', 'COMPLETED', 'CANCELLED'],
  IN_PROGRESS:        ['COMPLETED'],
  COMPLETED: [], REJECTED: [], CANCELLED: [],
};

const isLegalMove = (from, to) => (NEXT_STATES[from] ?? []).includes(to);

Holding the display colour in this module rather than in each frontend looks like trivia and prevents a genuinely annoying problem: two portals rendering the same status in different colours and nobody able to say which one is right.

Being pure data is what makes the machine exhaustively testable. Ten statuses is a hundred from→to pairs, and asserting all hundred against the table costs one loop and runs in milliseconds without a database. This is the opposite end of the spectrum from a state machine whose transitions are guarded by a row lock on a contended resource — there, the correctness lives in the database; here it lives in a table you can hold in your head.

What the transition table does not enforce

This is where I’d push back on my own design. The table governs what move is legal. It says nothing about who may make it.

isLegalMove('QUOTE_SENT', 'ACCEPTED') is true regardless of whether the caller is the client who should be accepting, the caterer, or the operator. Actor rules live at the route layer, endpoint by endpoint. That works, and it’s how it’s built — but it means the state machine is one source of truth for half the question and there’s a second, more scattered source of truth for the other half. If I extended this, the transition entries would carry the actor types allowed to make each move, and both checks would happen in one place.

Worth saying plainly, because “enforced server-side by one state machine” is the sort of sentence that sounds like it covers more than it does.

Hidden states belong at the read boundary

One status is operator-internal. The client shouldn’t see that their event moved from “accepted” into an internal working state — from their side it’s still accepted until it’s done.

There are three ways to do that and two of them are traps.

The third one, and it’s about as much code as it sounds:

// The client sees the internal working state as the last public one.
const asSeenByClient = (status) =>
  status === 'IN_PROGRESS' ? 'ACCEPTED' : status;

The same shape handles quote visibility, which is the highest-stakes projection in the feature. A caterer’s unapproved draft reaching the client would undercut the operator’s entire role in the middle of their own marketplace:

function quotesVisibleTo(actor, quotes) {
  switch (actor.type) {
    case 'CLIENT':  return quotes.filter(q => q.approvedAt !== null);
    case 'CATERER': return quotes.filter(q => q.authorId === actor.catererId
                                           || q.approvedAt !== null);
    case 'OPERATOR': return quotes;
  }
}

One function, called wherever quotes are returned. Deliberately not a per-endpoint filter, for exactly the reason in the table above.

Where my own projection leaked

A read-time projection is only as complete as your inventory of read paths, and I missed one: the audit log.

Here’s the part I got wrong, and it’s a good illustration of why projections are harder than they look.

The chat room in this feature is deliberately unfiltered. All three parties see every message and each other. That’s a defensible design — the caterer is physically showing up at the client’s venue, so contact exchange is inevitable, and pretending otherwise means maintaining a fiction that reality defeats on the day of the event.

But the append-only timeline is served through that same unfiltered path, and every transition writes a timeline entry containing the raw from-status and to-status. So the status I was carefully hiding on the request read path was sitting in plain text in the audit trail, visible to the client.

The projection was correct. It was just applied in one of the two places the status surfaces. That’s the failure mode of read-time projection in general: it’s only as good as your inventory of read paths, and “the audit log is also a read path” is easy to miss because you think of it as write-side machinery.

The fix is to apply the same projection in the timeline serialiser. The broader lesson is that when you decide a field is audience-sensitive, you have to go and enumerate every surface it can reach — including the ones whose job you think of as recording rather than showing.

Assignment is the access grant

The caterer sees a request the moment it’s assigned to them, whatever its status. No additional status gate.

The operator already controls exposure by choosing when to assign. A second gate on top is a rule that can disagree with the first, and every disagreement is either a caterer who can’t see work they were given, or a caterer who can see work they weren’t. One grant, one answer.

Three consequences fall out of that, and I’d rather work them out on paper than discover them:

1. Unassigning revokes access, but the caterer’s quotes and comments remain. Authorship is history. Deleting it would falsify the timeline, and the timeline is the thing three mutually-suspicious parties will consult when they disagree. The same reasoning that makes a stock ledger append-only applies here: a record you can edit is a record nobody can rely on.
2. Reassigning to a different caterer is allowed. The first caterer’s drafts and pending quotes are auto-rejected. Anything already approved and sent to the client stands — the client saw a real document, and retracting it after the fact is worse than leaving it in the record with its history intact.
3. Contact details are snapshotted at assignment, not joined live.

That third one deserves its own paragraph, because a live join is the default and the default is wrong here. The coordinator for a specific event must not silently change because the caterer edited their business profile three weeks later. If you join live, then looking at last month’s event today shows you details that were never true for it, and nobody can tell that anything changed. Snapshot at the moment of assignment, and default each field individually from the caterer’s records if the assigning admin leaves it blank.

There’s a related check that’s easy to get backwards. The caterer record carries both an “is deleted” flag and an “is currently accepting orders” flag. Only deletion gates access. The accepting-orders flag is a storefront state — a large share of records have it off at any given time, and it has nothing to do with whether a caterer is a real business you assigned an event to. Gating on it would have locked out every paused caterer on day one. I checked what the data actually looked like before writing the condition, and left the reasoning in a comment at the check so nobody “fixes” it later.

Three small modelling decisions that save arguments

Version numbers get assigned when they become public. A caterer drafts a quote; the operator may reject it. If the version were assigned at creation, a rejected draft burns a number and the client sees v1 then v3 and immediately asks what v2 said. So the version column is nullable and gets its number at approval. The uniqueness constraint on (request, version) keeps holding, because Postgres treats NULLs as distinct in a unique index — though note that PG15 added UNIQUE NULLS NOT DISTINCT, documented under CREATE INDEX, so this design quietly depends on nobody redefining the constraint that way.

Being honest about that one too: the version is computed as max-plus-one, which is the same read-then-write race I avoided elsewhere. The unique constraint turns a genuine collision into a failed second approval rather than a duplicate version, which is acceptable here because two operators approving quotes on the same request in the same instant isn’t a real scenario. But it’s the constraint saving me, not the code being right.

Human-quotable request codes come from an atomic counter. Format is a prefix, the organization’s short code, the year and a zero-padded sequence — people read these out on the phone. The sequence comes from an atomic database function keyed on organization and year, backed by a Postgres sequence rather than a count of existing rows, because counting is a race two same-second submissions both lose. Codes are generated at submit rather than at draft creation, so abandoned drafts consume nothing.

“Not found” rather than “forbidden” for another party’s records. A caterer asking for a request that isn’t theirs gets a 404, not a 403. The general rule, and the enumeration bug that taught it to me, are in the piece on tenant-scoped authorization. What’s specific here is that the three parties are commercial competitors, so the mere existence of a request is a signal about who is talking to whom. This is the read side of what OWASP calls broken object level authorization, API1 in its 2023 API Security Top 10.

What actually broke

“A status cannot change without leaving an audit trail” is a sentence I wrote and it isn’t true.

The design puts the status update and the timeline entry in the same function, which is genuinely good — it means no code path can change a status while forgetting to record it. I then described that as making the audit trail unskippable. Look at what the audit write actually does:

// Best-effort by design: a failed audit write must not roll back a real
// state change. The cost is that a silent failure leaves only a log line.
async function recordEvent(recordId, entry) {
  try {
    await timeline.insert({ recordId, ...entry });
  } catch (err) {
    logger.error('audit append failed', { recordId, cause: err.message });
  }
}

It swallows everything. Which is a defensible trade — I don’t want an email service or an audit insert failing a state change the caller has already been told succeeded. But it means a status change whose timeline entry failed commits anyway, leaving no trace except a log line.

The dispatcher I wrote about earlier got this right almost by accident of shape: its assignment-history insert sits inside the same BEGIN/COMMIT as the status write, so it rolls back with it. Here the audit write is a separate best-effort call, and that difference is the entire bug.

Same call is not the same transaction. That’s the whole correction, and it’s the sort of thing that’s obvious once written down and completely invisible while you’re writing the design doc, because “they happen together” feels true.

What makes it worse is that I already knew this one. On an earlier feature an audit write started failing and nothing alerted, and the trail simply went quiet. I carried the same trade forward here deliberately — and carried the same missing piece with it, because there is still no monitor on the audit write going silent. A deliberate trade with an unmonitored failure mode is only half a decision. The reconciliation sweep idea is what closes it: something that periodically asks whether every status change in the last hour has a matching timeline row, and shouts when the answer is no.

Pricing was not my call, and I nearly wrote it up as though it were. The model is pass-through: the caterer’s rate is the client’s rate, with the operator’s margin as a separate visible commission. That’s a commercial decision made by the business. What’s mine is the schema consequence — one rate column, no shadow field — and noticing that it’s what makes the transparent chat room safe. A hidden markup and a room where all three parties talk to each other cannot coexist. If pricing ever changes, the room has to be revisited in the same breath, and I wrote that dependency down next to both decisions.

FAQ

What is a three-party workflow? A workflow where one record is worked by three parties who aren’t on the same team — in a marketplace, typically a customer, the platform operator and a supplier. The defining constraint is that each party needs a different view of the same row, and none of them should see the others’ internal state.

Should I add a separate “customer-visible status” column? No. Two status columns can disagree, and every write path then has to update both correctly or the record starts lying. Compute the customer-facing value from the internal one in a single function applied on read, so there’s one place to get right and one place to test.

How is this different from RBAC? RBAC answers “may this actor perform this action”. Projection answers “what does this record look like to this actor”. You need both, and they’re separate concerns — a caterer may be fully authorised to read a request and still must not see the operator’s internal working state on it.

Where should a multi-party workflow’s state machine live? In one module, as data — a map from each status to its legal successors, plus a terminal set. Pure data tests exhaustively offline with no database. When a transition also allocates a scarce resource, the guard belongs in the database instead, behind a row lock.

Should the transition table also encode who may make each move? Ideally yes. If the table only says which moves are legal, actor rules end up scattered across route handlers, and you have one source of truth for half the question and many for the other half. Putting allowed actor types on each transition keeps both checks together.

When should a version number be assigned? When the version becomes visible to the audience that counts it, not when the draft is created. Otherwise rejected drafts burn numbers and users see gaps they’ll ask about. Keep the column nullable until then and let a unique constraint hold the invariant.

Should I snapshot or join the counterparty’s contact details? Snapshot, at the moment of assignment. A live join means historical records silently change when someone edits a profile, so last month’s event shows a coordinator who was never attached to it — and nothing in the record indicates it changed.

Should competitors on a marketplace get a 404 or a 403 for each other’s records? A 404. A 403 confirms the record exists, and when the parties are commercial competitors, existence itself is information — who is talking to whom, and how often. Return 404 for anything outside the caller’s grant and reveal nothing at all.

What I’d still improve

• Apply the client projection to the timeline serialiser, closing the leak above, and write a test that asserts the hidden status never appears in any serialised payload for a client actor.
• Move actor rules into the transition table, so one lookup answers both “is this move legal” and “may you make it”.
• Monitor the audit write going quiet. A count of state changes without a matching timeline row, per hour, with an alert. The trade is fine; the blind spot isn’t.
• Make version assignment atomic, using the same counter approach as request codes rather than max-plus-one leaning on the constraint.
• Revisit third-party authentication before this carries real client data at volume — the mitigation is specified and costed in the plan, it just isn’t built.

The one idea to take away

In a three-party workflow, decide what each party sees once, in one function, on the way out.

The instinct is to model audience into the schema — a column per view, or a filter per endpoint — and both of those spread a single decision across a growing number of places that have to agree forever. A read-time projection keeps it in one place. Just remember that the projection is only as complete as your list of read paths, and that your audit log is one of them. I found that out the way most people do.

---

I write about backend reliability, data modelling and multi-tenant architecture from production work on food-tech and healthcare platforms. More on what I build and how I work, the hospital meal system I own the backend for, and a companion piece on keeping tenants isolated without a policy engine. If you’re modelling a marketplace workflow and want a second opinion, get in touch.