Archetype 16 — Auction / Competitive Window #
What this archetype is #
Users submit competing offers during a bounded window, and the system tracks the current best and closes correctly.
Examples: auctions, bidding, spot allocation.
We will use single-item auction as the running example.
Layer 1: Entities and Postgres table design #
AuctionState
BidState
BestBidState
WinnerState
create table auctions (
auction_id uuid primary key,
status text not null,
closes_at timestamptz not null,
current_best_bid numeric(18,2),
current_best_bidder bigint,
version bigint not null default 1
);
create table bids (
bid_id bigserial primary key,
auction_id uuid not null references auctions(auction_id),
bidder_id bigint not null,
amount numeric(18,2) not null,
created_at timestamptz not null default now()
);
create index bids_auction_amount_idx
on bids (auction_id, amount desc, created_at asc);
Layer 2: Write path mechanics #
Place bid #
begin;
insert into bids (auction_id, bidder_id, amount)
values ($1, $2, $3);
update auctions
set current_best_bid = $3,
current_best_bidder = $2,
version = version + 1
where auction_id = $1
and status = 'OPEN'
and closes_at > now()
and (current_best_bid is null or $3 > current_best_bid);
commit;
Close auction #
update auctions
set status = 'CLOSED',
version = version + 1
where auction_id = $1
and status = 'OPEN'
and closes_at <= now();
Layer 3: Fault tolerance #
- accepted late bid
- wrong winner
- duplicate close
- stale current-best read near close
Layer 4: Scale #
Default hotspots:
- burst bids near close
- hot auction row
- current-best contention
- append hotspot for hot auction
Common mitigations:
- narrow
auctionsrow update - append bids separately from current-best row
- close-window fencing and clock discipline