Two collectors click "Reserve" on the same editioned print within four seconds of each other. Both get a confirmation email. One of them is about to be very disappointed, and your artist is about to ask why the resale royalty on an invoice went to the wrong edition number.
That's the problem this guide solves. Not the discovery side, not the pretty front end — the boring, dangerous part underneath where two concurrent requests fight over one piece of inventory, where a price changes mid‑checkout, and where a royalty split gets computed against the wrong base number. If you've already read the broader viewing-room system design that links discovery to fulfillment, treat this as the engineering appendix: the DDL, the transactions, the locks, and the ledger math that keep that system honest under load.
Everything below is copy‑safe. The SQL targets PostgreSQL (the patterns port to MySQL 8+ with minor syntax changes). I'll flag where dialect matters.
The failure modes, stated plainly
Before any schema, be clear about what you're actually defending against. There are four things that go wrong in viewing‑room sales plumbing, and they compound:
-
Oversell — two orders claim the same unit of inventory. Classic race condition.
-
Price drift — the price shown during browsing differs from what gets charged at checkout, usually because an admin edited it in between, or an FX rate moved.
-
Zombie reservations — a hold is placed, the buyer wanders off, and the unit stays locked forever because nothing releases it.
-
Misallocated royalty — the artist/estate split gets computed on the wrong base (gross vs. net, pre‑ or post‑discount, wrong FX snapshot), or gets double‑posted on retry.
The pattern that causes almost all of these: galleries treat the viewing‑room database as a display of truth rather than the enforcer of truth. The front end shows "1 available," but nothing in the database physically prevents a second successful claim. Display and enforcement are different jobs. If your enforcement lives in application code — an if available > 0 check — concurrency will eventually beat you.
Core schema: five tables, with the constraints doing real work
The trick is pushing invariants into CHECK constraints and column types so a bad write cannot commit, regardless of what the application layer believes. Here's the base DDL.
Never miss a key exhibition detail again.
Artioly helps you organize, promote, and manage every exhibition effortlessly.
- Unified exhibition scheduling
- Artist and visitor notifications
- Inventory and sales tracking
No credit card required
-- Inventory: one row per sellable unit (a unique work, OR one unit of an edition) CREATE TABLE inventory ( inventoryid BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, workid BIGINT NOT NULL, editionno INT, -- NULL for unique works editiontotal INT, -- NULL for unique works status TEXT NOT NULL DEFAULT 'available', version BIGINT NOT NULL DEFAULT 0, -- optimistic concurrency token currency CHAR(3) NOT NULL, listpriceminor BIGINT NOT NULL, -- store money in minor units (cents) createdat TIMESTAMPTZ NOT NULL DEFAULT now(), updatedat TIMESTAMPTZ NOT NULL DEFAULT now(), CONSTRAINT chkstatus CHECK (status IN ('available','reserved','sold','withdrawn')), CONSTRAINT chkpricepositive CHECK (listpriceminor > 0), CONSTRAINT chkeditionbounds CHECK ( editionno IS NULL OR (editionno >= 1 AND editionno <= edition_total) ) );
-- One physical unit of an edition cannot exist twice: CREATE UNIQUE INDEX uqeditionunit ON inventory (workid, editionno) WHERE edition_no IS NOT NULL;
Storing money in minor units as integers (listpriceminor BIGINT) is non‑negotiable. The moment you use floating point for prices or royalties, you've signed up for rounding bugs that surface months later as a £0.02 discrepancy the artist's accountant will absolutely notice. Integers, always.
-- Reservations: time-bounded holds against an inventory unit CREATE TABLE reservations ( reservationid BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, inventoryid BIGINT NOT NULL REFERENCES inventory(inventoryid), collectorid BIGINT NOT NULL, status TEXT NOT NULL DEFAULT 'held', heldat TIMESTAMPTZ NOT NULL DEFAULT now(), expiresat TIMESTAMPTZ NOT NULL, quotedpriceminor BIGINT NOT NULL, -- price locked at hold time quotedcurrency CHAR(3) NOT NULL, fxsnapshotid BIGINT, -- see FX section createdat TIMESTAMPTZ NOT NULL DEFAULT now(), CONSTRAINT chkresstatus CHECK (status IN ('held','converted','expired','cancelled')), CONSTRAINT chkexpiryfuture CHECK (expiresat > heldat) );
-- Only ONE active hold per inventory unit, enforced by the database: CREATE UNIQUE INDEX uqoneactivehold ON reservations (inventoryid) WHERE status = 'held';
That partial unique index is the single most important line in this whole guide. WHERE status = 'held' means the database itself refuses to allow a second active hold on the same inventory_id. The second concurrent insert doesn't get a polite "sorry, taken" message from your app logic — it gets a hard unique‑violation at the storage layer, which is exactly what you want. Enforcement, not display.
-- Price history: append-only audit of every price a unit ever had CREATE TABLE pricehistory ( pricehistoryid BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, inventoryid BIGINT NOT NULL REFERENCES inventory(inventoryid), oldpriceminor BIGINT, newpriceminor BIGINT NOT NULL, currency CHAR(3) NOT NULL, changedby BIGINT NOT NULL, changed_at TIMESTAMPTZ NOT NULL DEFAULT now(), reason TEXT );
-- Orders: the committed sale CREATE TABLE orders ( orderid BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, reservationid BIGINT NOT NULL REFERENCES reservations(reservationid), inventoryid BIGINT NOT NULL REFERENCES inventory(inventoryid), collectorid BIGINT NOT NULL, grossminor BIGINT NOT NULL, discountminor BIGINT NOT NULL DEFAULT 0, netminor BIGINT NOT NULL, currency CHAR(3) NOT NULL, fxsnapshotid BIGINT NOT NULL, idempotencykey TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'confirmed', createdat TIMESTAMPTZ NOT NULL DEFAULT now(), CONSTRAINT chkorderstatus CHECK (status IN ('confirmed','settled','refunded','voided')), CONSTRAINT chkdiscountsane CHECK (discountminor >= 0 AND discountminor <= grossminor), CONSTRAINT chknetmath CHECK (netminor = grossminor - discountminor), CONSTRAINT uqidempotency UNIQUE (idempotency_key) );
Two constraints there earn their keep. chknetmath means a row where net doesn't equal gross minus discount cannot be stored — an entire class of "the numbers don't add up" bugs becomes impossible. And uq_idempotency is your retry guard: if a checkout request fires twice (flaky network, impatient collector double‑clicking), the second insert collides and you return the original order instead of creating a duplicate.
-- Royalty ledger: append-only, double-entry-friendly CREATE TABLE royaltyledger ( ledgerid BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, orderid BIGINT NOT NULL REFERENCES orders(orderid), beneficiaryid BIGINT NOT NULL, -- artist, estate, co-rep gallery beneficiarytype TEXT NOT NULL, basisminor BIGINT NOT NULL, -- the base the % was applied to ratebps INT NOT NULL, -- rate in basis points (e.g. 3000 = 30%) amountminor BIGINT NOT NULL, -- computed payable currency CHAR(3) NOT NULL, fxsnapshotid BIGINT NOT NULL, postedat TIMESTAMPTZ NOT NULL DEFAULT now(), CONSTRAINT chkratebounds CHECK (ratebps >= 0 AND ratebps <= 10000), CONSTRAINT chkamountnonneg CHECK (amountminor >= 0), CONSTRAINT uqledgerentry UNIQUE (orderid, beneficiary_id) );
Rates live in basis points as integers (ratebps), not as 0.30 decimals, for the same reason prices do. And uqledger_entry prevents double‑posting: you cannot accidentally credit the same artist twice for the same order.
The checkout transaction: pessimistic lock vs. optimistic CAS
There are two correct ways to claim inventory safely. Pick one deliberately — mixing them half‑heartedly is where subtle bugs hide.
Option A — SELECT FOR UPDATE (pessimistic)
BEGIN; -- Lock the specific unit. Other transactions block here until we commit. SELECT inventoryid, status, listpriceminor, version FROM inventory WHERE inventoryid = :inventoryid FOR UPDATE; -- Application checks the returned status = 'available'. If not, ROLLBACK. -- Create the hold (the partial unique index is a second line of defence): INSERT INTO reservations (inventoryid, collectorid, expiresat, quotedpriceminor, quotedcurrency, fxsnapshotid) VALUES (:inventoryid, :collectorid, now() + interval '15 minutes', :quotedprice, :currency, :fxsnapshotid); UPDATE inventory SET status = 'reserved', version = version + 1, updatedat = now() WHERE inventoryid = :inventory_id; COMMIT;
Add FOR UPDATE SKIP LOCKED only when you're pulling any available unit from a pool of an edition — it lets a worker grab the next unlocked unit instead of queuing behind a busy one. For a specific chosen unit, plain FOR UPDATE is correct.
Option B — Optimistic compare‑and‑swap (CAS)
-- Read earlier: status='available', version=7 UPDATE inventory SET status = 'reserved', version = version + 1, updatedat = now() WHERE inventoryid = :inventory_id AND version = 7 -- the CAS condition AND status = 'available'; -- Check rows affected. 0 rows = someone beat you. Retry or fail cleanly.
| Dimension | SELECT FOR UPDATE | Optimistic CAS |
|---|---|---|
| Correctness under load | Excellent | Excellent |
| Behaviour under contention | Blocks (queues) | Fails fast, needs retry logic |
| Think‑time lock held | Yes (keep txns short!) | No |
| Best for | Specific chosen unit | High read volume, rare writes |
| Risk if misused | Lock held too long → timeouts | Retry storms if you don't back off |
| Complexity | Lower | Slightly higher (retry loop) |
Practical rule: for a viewing‑room where a collector picks one specific print and you hold it for a few minutes, pessimistic SELECT FOR UPDATE inside a short transaction is the simpler, safer default. Reserve CAS for pool‑style editions with heavy browsing traffic. Whichever you choose, keep the transaction span tight — do your payment‑auth call outside the lock, not inside it.
This diagram shows the steps in the checkout transaction and where locks or CAS apply.
Keep the transaction span tight — do payment auth outside the lock window.
Whichever you choose, keep the transaction span tight — do your payment‑auth call outside the lock, not inside it.
The state machine: what's allowed, and what must stay true
Inventory and reservations each move through a small set of states. Write these transitions down explicitly, because the bugs hide in the ones nobody drew.
Inventory states and allowed transitions:
available → reserved (hold placed) available → withdrawn (pulled from sale) reserved → sold (order confirmed) reserved → available (hold expired or cancelled) sold → available (refund/void — only via explicit reversal) withdrawn → available (re-listed)
Everything not in that list is illegal. sold → reserved is illegal. withdrawn → sold is illegal. You can enforce this with a trigger that rejects forbidden transitions, which is far safer than trusting every code path to behave:
CREATE OR REPLACE FUNCTION enforceinventorytransition() RETURNS TRIGGER AS $$ BEGIN IF NOT ( (OLD.status = 'available' AND NEW.status IN ('reserved','withdrawn')) OR (OLD.status = 'reserved' AND NEW.status IN ('sold','available')) OR (OLD.status = 'sold' AND NEW.status = 'available') OR (OLD.status = 'withdrawn' AND NEW.status = 'available') OR (OLD.status = NEW.status) ) THEN RAISE EXCEPTION 'illegal inventory transition: % -> %', OLD.status, NEW.status; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trginventorytransition BEFORE UPDATE OF status ON inventory FOR EACH ROW EXECUTE FUNCTION enforceinventorytransition();
Invariants that must hold at all times:
-
A unit in
soldstatus must have exactly oneordersrow with status in (confirmed,settled). -
A unit in
reservedstatus must have exactly onereservationsrow with statusheld. -
No
royalty_ledgerrows may exist for an order that isvoidedorrefundedwithout a matching reversal entry. -
The sum of royalty
amountminorfor an order must never exceed the order'snetminor(unless resale royalty rules explicitly allow a gross basis — decide this once, in writing).
Enforce these invariants at the DB layer where possible; triggers and constraints are far more reliable than hoping every code path behaves.
Royalty math: order of operations is the whole game
This is where galleries lose money quietly. The royalty is correct or incorrect depending entirely on the sequence of rounding and basis decisions. Get the order wrong and you'll be off by pennies on every sale, which adds up to real money across a year and poisons artist trust.
Fix this sequence and don't deviate:
-
Start from the agreed basis. Decide per contract whether royalty applies to gross or net (post‑discount). Store the choice; don't infer it at runtime.
-
Apply FX first, if the royalty is paid in a different currency than the sale — using the snapshotted rate from the moment of sale (next section), never today's rate.
-
Compute the raw amount in minor units using integer math
amount = basisminor * ratebps / 10000. -
Round once, at the end, using a documented rule (round half‑up is the common choice). Rounding mid‑calculation is the classic bug.
-
Post to the ledger as a single append‑only row. Never update an existing ledger row — reverse and re‑post.
-- Royalty of 30% (ratebps = 3000) on a net basis, integer-safe: INSERT INTO royaltyledger (orderid, beneficiaryid, beneficiarytype, basisminor, ratebps, amountminor, currency, fxsnapshotid) SELECT o.orderid, :artistid, 'artist', o.netminor, -- the agreed basis 3000, (o.netminor * 3000 + 5000) / 10000, -- round half-up, integer math o.currency, o.fxsnapshotid FROM orders o WHERE o.orderid = :orderid;
The + 5000 before dividing by 10000 is integer round‑half‑up. Unglamorous and correct. A quick worked example: on a net sale of £4,250.00 (net_minor = 425000) at 30%, you get 425000 * 3000 = 1,275,000,000; + 5000 = 1,275,005,000; / 10000 = 127,500 (integer division drops the remainder). So £1,275.00 to the artist, £2,975.00 retained. The rounding term only changes the result when the fractional part lands on or above the half‑penny, which is exactly when you want it to.
When multiple beneficiaries split (artist + co‑representing gallery), compute each independently from the same basis, then assert the sum against the basis as a reconciliation check. Don't compute one as "the remainder" — that approach hides rounding drift.
FX snapshotting: freeze the rate at the moment of truth
A collector in New York buys a work priced in euros. The sale happens Tuesday. Settlement lands Friday. The artist royalty is paid in pounds. If any of those steps reaches for "the current rate," your numbers will never reconcile.
Snapshot every rate you'll need, once, at sale time, and reference that snapshot everywhere downstream.
CREATE TABLE fxsnapshot ( fxsnapshotid BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, basecurrency CHAR(3) NOT NULL, capturedat TIMESTAMPTZ NOT NULL DEFAULT now(), source TEXT NOT NULL -- which rate provider ); CREATE TABLE fxrate ( fxsnapshotid BIGINT NOT NULL REFERENCES fxsnapshot(fxsnapshotid), quotecurrency CHAR(3) NOT NULL, rateppm BIGINT NOT NULL, -- rate * 1,000,000 (integer) PRIMARY KEY (fxsnapshotid, quotecurrency) );
Store rates as parts‑per‑million integers (rateppm) to maintain precision without floats. Every reservations, orders, and royaltyledger row carries the fxsnapshotid, so a reconciliation three months later uses the exact rate that was live at sale. When the royalty currency differs from the sale currency, convert using the snapshotted rate before applying the percentage, as step 2 above requires.
The lock reaper: killing zombie reservations
Holds expire. Collectors abandon carts. If nothing cleans up, your edition shows "sold out" while units sit in permanent limbo. A scheduled reaper job releases expired holds and returns inventory to available.
-- Run every 60 seconds via cron / scheduled worker WITH expired AS ( SELECT reservationid, inventoryid FROM reservations WHERE status = 'held' AND expiresat < now() FOR UPDATE SKIP LOCKED -- don't fight live checkouts ) UPDATE reservations r SET status = 'expired' FROM expired e WHERE r.reservationid = e.reservationid; -- Then flip the freed inventory back, but only if still 'reserved': UPDATE inventory i SET status = 'available', version = version + 1, updatedat = now() FROM reservations r WHERE r.inventoryid = i.inventoryid AND r.status = 'expired' AND i.status = 'reserved';
FOR UPDATE SKIP LOCKED matters here: the reaper quietly skips any reservation that's mid‑conversion in another transaction instead of deadlocking against a live checkout. Run it frequently (every 30–60 seconds), keep each batch small, and log how many it reaps — a sudden spike in reaped holds usually means something upstream is creating holds it never converts.
Reconciliation queries and KPIs you should run daily
Enforcement at write‑time stops most problems. Reconciliation catches the ones that slipped through — a code path that bypassed the trigger, a manual admin edit, a partial failure.
Oversell check — must always return zero rows:
SELECT inventoryid, COUNT() FROM orders WHERE status IN ('confirmed','settled') GROUP BY inventoryid HAVING COUNT() > 1;
Orphaned holds — reserved inventory with no active reservation:
SELECT i.inventoryid FROM inventory i LEFT JOIN reservations r ON r.inventoryid = i.inventoryid AND r.status = 'held' WHERE i.status = 'reserved' AND r.reservationid IS NULL;
Royalty integrity — ledger total exceeding order net:
SELECT o.orderid, o.netminor, SUM(l.amountminor) AS royaltytotal FROM orders o JOIN royaltyledger l ON l.orderid = o.orderid GROUP BY o.orderid, o.netminor HAVING SUM(l.amountminor) > o.net_minor;
KPIs worth putting on a weekly dashboard:
-
Oversell incidents target zero. Anything above zero is a sev‑1.
-
Hold‑to‑conversion rate holds that became orders ÷ total holds. A sharp drop can mean checkout friction or a pricing bug.
-
Mean hold duration before conversion rising numbers hint your 15‑minute window is miscalibrated.
-
Reaped‑hold rate expired ÷ total. A climbing rate is an early warning.
-
Royalty reconciliation variance sum of ledger vs. expected, per artist. Should be exactly zero in minor units.
KPIs should be sourced from the same snapshotted data used in production to ensure they're comparable to the operational state.
Security hardening checklist
Security hardening checklist
-
Price changes write to
price_historyvia trigger — not optionally, always. -
Application role has no
DELETEonorders,royaltyledger, orpricehistory(append‑only tables stay append‑only). -
Royalty rates (
rate_bps) are only editable by a restricted admin role, and every change is audited. -
idempotency_keyis required on every order‑creating endpoint, generated client‑side per checkout attempt. -
FX snapshots are immutable — no
UPDATEgrant onfx_rate. -
Row‑level security (or equivalent app scoping) so one gallery's data is unreachable from another's session in a multi‑tenant setup.
-
The transition trigger is tested against direct SQL writes, not just application paths.
-
Reaper job runs under its own limited role and logs every action.
Treat append‑only tables as sacrosanct audit trails; only admin processes may create reversal entries via explicit compensating rows.
Scaling notes
Under modest load — a few hundred works, normal gallery traffic — none of this needs exotic infrastructure. A single well‑indexed PostgreSQL instance handles it comfortably. The things that actually bite at scale:
-
Hot rows on popular editions. A sold‑out‑in‑seconds drop concentrates all contention on a handful of rows.
SKIP LOCKEDpool‑draining beats both plainFOR UPDATEand CAS retry storms in that scenario. -
Long transactions. The number one self‑inflicted wound
holding a row lock while waiting on a payment gateway. Authorise payment before or after the lock window, never during.
-
Reaper lag. If reservations expire faster than the reaper runs, available counts lag reality. Tune the interval to your hold duration.
-
Read replicas for reconciliation. Run the daily KPI and integrity queries against a replica so analytics never contends with live checkouts.
Monitor hot rows and reaper performance closely; those are the metrics that most directly correlate with user‑visible failures during drops.
Test cases you must write
A few that catch the real bugs:
-
Concurrent hold fire two hold requests at the same
inventory_idin parallel; assert exactly one succeeds, one gets a clean unique violation. -
Hold‑then‑expire place a hold, advance the clock past
expires_at, run the reaper, assert inventory returns toavailable. -
Illegal transition attempt
UPDATE inventory SET status='sold'on awithdrawnunit; assert the trigger rejects it. -
Idempotent checkout submit the same
idempotency_keytwice; assert one order, not two. -
Royalty rounding run the royalty calc on a value with a half‑penny remainder; assert the documented rounding rule applied.
-
FX consistency create an order, change the live FX rate, recompute royalty from the snapshot; assert it used the old rate.
-
Double‑post guard attempt two ledger inserts for the same (order, beneficiary); assert the second fails.
Automate these against a local throwaway DB and include them in CI where possible; they protect the invariants the schema enforces.
A short real scenario
A two‑person gallery running an editioned print release of 25 units had their viewing‑room priced in USD, with the artist royalty paid in euros at 30% of net. Over one launch week they noticed two things: a collector had been emailed a confirmation for edition 12/25 that another buyer also held, and the artist's royalty statement came out roughly €40–€60 lighter than the artist's own math.
The oversell came from an availability check that lived in application code with no database‑level uniqueness on active holds — two requests passed the if available check microseconds apart. The royalty gap came from computing the percentage in USD and converting the result to euros at the rate on statement day rather than sale day, plus a mid‑calculation rounding step that shouldn't have been there.
Adding the partial unique index on active holds, moving availability enforcement into SELECT FOR UPDATE, snapshotting FX at sale time, and consolidating rounding to a single end‑of‑calculation step closed both issues. The oversell rate went to zero across the next three releases, and royalty statements started matching the artist's figures to the penny — which, more than any dashboard, is what kept the artist signing with them.
When this level of rigor makes sense — and when it doesn't
If you sell a handful of unique works a month with plenty of staff attention on each sale, hand‑managed holds are fine and all this machinery is overkill. These enforcement patterns start paying for themselves the moment you have concurrent buyers competing for the same units — timed drops, popular editions, art‑fair online rooms, anything where two people can click "buy" within the same minute. That's when display‑level availability stops being enough and database‑level enforcement becomes the difference between a clean launch and an apology email.
The underlying principle is simple: your database should make the wrong outcome impossible to store, not merely unlikely. Every CHECK constraint, every partial unique index, every transition trigger is there so that when application logic eventually has a bad day — and it will — the data stays correct anyway. Build the guardrails into the schema, keep the royalty math to one fixed sequence, snapshot your rates, and run the reconciliation queries even when you're sure everything is fine. Those are often the days they catch something.
If you sell a handful of unique works a month with plenty of staff attention on each sale, hand‑managed holds are fine and all this machinery is overkill. These enforcement patterns start paying for themselves the moment you have concurrent buyers competing for the same units — timed drops, popular editions, art‑fair online rooms, anything where two people can click "buy" within the same minute. That's when display‑level availability stops being enough and database‑level enforcement becomes the difference between a clean launch and an apology email.
The underlying principle is simple: your database should make the wrong outcome impossible to store, not merely unlikely. Every CHECK constraint, every partial unique index, every transition trigger is there so that when application logic eventually has a bad day — and it will — the data stays correct anyway. Build the guardrails into the schema, keep the royalty math to one fixed sequence, snapshot your rates, and run the reconciliation queries even when you're sure everything is fine. Those are often the days they catch something.
Ready to elevate your gallery operations?
Join 500+ galleries using Artioly to streamline operations, boost visitor engagement, and increase art sales.