Data models & operations

Every entity in the SportsBridge MVP, the fields it carries, and the operations allowed against it. Derived from the technical specification, sections A3 through A9.

Callers Operator Property Admin System

Kind get post ws fn — fn is an internal service function, not a public endpoint

Restricted field Append-only

The specification lists field names; column types below are proposed for the PostgreSQL and Prisma stack named in A2 and lock at the end of Sprint 0. Money is integer cents throughout, never float.

No model matches “”

Try a field name such as reserve_price, an endpoint such as reveal, or a model such as Bid.

Identity & access

User / Account

An organization — Operator or Property — has many users.

Field	Type	Notes
id	uuid	Primary key
org_id	uuid	Owning organization
role	enum	operator · property · admin_reviewer · admin_compliance · superadmin. Admin is subdivided from the start — cheap now, painful to retrofit
email	citext	Unique login identity
status	enum	invited · active · suspended
kyb_status	enum	pending · submitted · verified · rejected. Held at organization level
created_at	timestamptz	Server time

Operations

post/auth/*PublicSign up, sign in, reset password, refresh session
get/orgOperatorPropertyRead own organization profile
put/orgOperatorPropertyUpdate own organization profile
post/org/kyb-docsOperatorPropertyUpload verification documents; retention policy applies
fnmanageAdminUsers()SuperadminCreate, suspend and assign roles to admin users

LicenceProfile

Drives every compliance answer. Verified by an admin before it counts.

Field	Type	Notes
id	uuid	Primary key
operator_id	uuid	Operator organizations only
jurisdiction	char(2)	Country the licence covers
licence_type	text	Sports betting, casino, lottery
proof_doc_ref	text	Stored document reference
verified_at	timestamptz	Null until an admin verifies. An unverified licence grants no eligibility
verified_by	uuid	Admin who verified it

Operations

post/org/licencesOperatorAdd a licence; starts unverified
fnverifyLicence()AdminSets verified_at, activating the licence for matching
fnevaluateEligibility(operator, listing)SystemChecks the listing against every verified licence, best outcome wins, returns eligible · restricted · not_eligible

Supply

Listing

Holds restricted fields

Identity fields reach only an operator who has revealed. Enforced at the serializer, never in the UI.

Field	Type	Notes
id	uuid	Primary key
property_id	uuid	Owning property organization
sport	enum	Football, basketball, tennis, venue, and so on
asset_type	enum	Shirt, naming rights, LED, broadcast, digital, hospitality
region	text	Coarse public label, deliberately vaguer than the jurisdiction
jurisdiction	char(2)	Drives compliance matching. Not the same thing as the displayed region
audience_data	jsonb	Attendance, broadcast reach, social, demographics. Checked for plausibility at review
term	text	Contract length offered
price_band_min	int	Integer cents
price_band_max	int	Integer cents
status	enum	See the state machine below. Only live listings appear in the marketplace
rejection_reason	text	Required whenever a reviewer rejects
mode	enum	direct · auction. Immutable once the listing goes live
auction_enabled	bool	Admin gate. The property requests auction mode, an admin grants it during review
anon_title	text	Public title. Review strips anything that could identify the property
identity_name	text	Restricted. Stripped at the serializer for any caller without a Reveal
identity_contact	text	Restricted. Same rule. A leaked identity is a lost commission

State machine

draft→ submitted→ in_review→ live| rejected→ paused→ archived

A live listing under an active exclusivity lock shows as locked to every other operator.

Operations

post/listingsPropertyAdminCreate a draft from the wizard. Operators can never create listings
post/listings/:id/submitPropertySubmit a draft into the review queue
get/my/listingsPropertyOwn listings in any status
get/listings?filtersOperatorBrowse live listings, anonymized, with eligibility computed for this caller
get/listings/:idOperatorDetail. Identity fields present only when the caller holds a Reveal
get/admin/review-queueReviewerListings awaiting review, with SLA age against the 48-hour target
post/admin/listings/:id/approveReviewerPublish to live
post/admin/listings/:id/rejectReviewerReject with a stored reason
post/admin/listings/:id/enable-auctionReviewerGrant auction mode, judged on watchlist interest and eligible operators in that market
fnserializeForCaller(listing, caller)SystemStrips identity fields unless the caller holds a Reveal. Guarded by automated tests from Sprint 2 and treated as a release-blocking bug class

WatchlistItem

The only way demand is expressed. Operators cannot post requests of their own.

Field	Type	Notes
id	uuid	Primary key
operator_id	uuid	Watcher
listing_id	uuid	Watched listing
created_at	timestamptz	Server time

Operations

post/watchlistOperatorWatch a listing
del/watchlist/:idOperatorStop watching
fnnotifyWatchers(listing)SystemSends price and status change notices to everyone watching
fncountForMarket(jurisdiction, asset)AdminFeeds the demand signal shown on the auction-gating toggle

Auction

One per auction-mode listing. Open ascending with masked bidders and a soft close.

Field	Type	Notes
id	uuid	Primary key
listing_id	uuid	Unique. Exactly one auction per listing
reserve_price	int	Integer cents. Bids below it are rejected at the API
reserve_visible	bool	The property chooses whether bidders see the reserve
opens_at	timestamptz	Scheduled open
closes_at	timestamptz	Mutable only by a soft-close extension, and every extension is logged
soft_close_window	interval	Proposed 10 minutes, to confirm with the PO before Sprint 6
extension_cap	int	Extensions are unlimited by default but capped by this value
min_increment	int	Percentage or fixed, enforced server-side to stop one-cent laddering
state	enum	See below
extension_count	int	Pushed live to all bidders each time it increases

State machine

scheduled→ open→ soft_close_extended *→ closed→ awarded| declined_all| no_bids

Only awarded creates a Deal. A no_bids or declined_all auction converts the listing back to direct mode automatically rather than being publicly marked as failed.

Operations

get/auctions/:idOperatorPropertyLive state: current high, bid count, time left
wsauction:{id}OperatorPropertyPushes current high bid, bid count, countdown and extensions
fnextendOnLateBid(auction, bid)SystemA bid inside the final window pushes closes_at out, logs it, and notifies every bidder
fncloseAuction(auction)SystemCloses on the server clock. The server is the only source of truth for time remaining
fnconvertToDirect(auction)SystemSilently returns a failed auction to direct mode, protecting the property's confidence

Bid

Append-only

A raise is a new row, never an update. Treat this table like a payment endpoint.

Field	Type	Notes
id	uuid	Primary key
auction_id	uuid	Placement is serialized per auction
operator_id	uuid	Restricted. Masked from other bidders for the whole auction. Disclosed to the property at close
amount	int	Integer cents. Amounts below the current high are never leaked to other bidders
placed_at	timestamptz	Server time, authoritative. A client timestamp is never trusted
state	enum	active · outbid · withdrawn · winning · lost
terms_accepted_at	timestamptz	Placing a bid accepts the auction terms and binds the operator
rule_version	text	Compliance version in force at bid time, stored on the row

Operations

post/auctions/:id/bidsOperatorPlace a binding bid. Requires a prior reveal, re-checks eligibility, enforces reserve and increment
get/auctions/:id/bidsPropertyRanked, with bidder identities disclosed at close
get/auctions/:id/bidsOperatorOwn bids plus the current high only. Never other bidders
fnplaceBidSerialized(auction, operator, amount)SystemRow-level lock or single-writer queue. Two bids in the same millisecond must produce a deterministic order and the current-high read must never be stale. The most test-worthy logic in the MVP
fnwithdrawBid(bid, reason)AdminAllowed before close only, with an approved reason, and recorded
fnexportBidLog(auction)AdminImmutable, exportable dispute evidence

AuctionAward

The property may award a bid that is not the highest. The rationale is mandatory.

Field	Type	Notes
id	uuid	Primary key
auction_id	uuid	Awarded auction
winning_bid_id	uuid	Sets the deal's opening agreed amount
runner_up_bid_id	uuid	Recorded so a re-award needs no new auction
awarded_by	uuid	The property user who awarded
awarded_at	timestamptz	Server time
rationale	text	Mandatory and stored, because the highest number need not win

Operations

post/auctions/:id/awardPropertyAward a compliant bid and open a deal room at that price
post/auctions/:id/declinePropertyDecline every bid, releasing all bidders
fnreawardToRunnerUp(award)PropertyIf the winner misses the signature window, award the runner-up without re-running the auction
fnautoDeclineOnSilence(auction)SystemAward window defaults to 5 business days. Silence auto-declines and releases every bidder
fnoverrideAward()AdminEmergency only, and always logged. Admins may never bid

Compliance

ComplianceRule

Append-only versions

A rules table, not code. The compliance analyst edits it through the admin console.

Field	Type	Notes
id	uuid	Primary key
jurisdiction	char(2)	First axis of the lookup
asset_type	enum	Second axis
sponsor_category	enum	Third axis. Betting and gaming at launch
status	enum	eligible · restricted · banned
conditions	text	Human-readable conditions, shown on the listing when the status is restricted
source	text	Regulatory reference backing the entry
version	text	For example NL-v14. Every edit creates a new version; rows are never updated in place
reviewed_at	timestamptz	Last analyst review

Fail-closed. A jurisdiction with no rules entry renders as "compliance review pending" and cannot be revealed. Absence of a rule is never treated as permission.

Operations

get/admin/compliance-rulesComplianceBrowse the rule set with versions and review dates
post/admin/compliance-rulesCompliancePublish a new version. There is no in-place edit
fnpreviewImpact(draftRule)ComplianceShows which live listings change status before the version is published
fnresolveStatus(jurisdiction, asset, category)SystemReturns the status and the version used. Fails closed when no entry exists

Reveal & credits

CreditLedger

Append-only

Balance is derived by summing deltas, never stored as a mutable field.

Field	Type	Notes
id	uuid	Primary key
operator_id	uuid	Account the credits belong to
delta	int	Signed. Positive on purchase, minus one on a reveal
reason	enum	pack_purchase · reveal · expiry · refund
pack_id	uuid	Starter, Growth or Scale
balance_after	int	Snapshot for auditability. The authoritative balance is still the sum of deltas
expires_at	timestamptz	Twelve months from purchase

Operations

get/creditsOperatorCurrent balance and full ledger history
post/packs/:id/purchaseOperatorBuy a pack. Properties and admins never hold credits
fndebitOneCredit(operator)SystemCalled only inside the atomic reveal transaction, never on its own
fnexpireCredits()SystemScheduled job writing expiry rows at the twelve-month mark

Reveal

Immutable

The legal artifact of the whole platform. Immutable and exportable.

Field	Type	Notes
id	uuid	Referenced by enforcement cases
listing_id	uuid	Listing whose identity was unlocked
operator_id	uuid	Who paid and saw the identity
timestamp	timestamptz	Server time. Never updated
non_circumvention_accepted_at	timestamptz	Acceptance is required before the credit is debited
rule_version	text	Proves what the platform showed, and when, if the rules later change

The atomic reveal — one transaction, no partial states

check eligibility→ check mode→ require acceptance→ debit 1 credit→ write Reveal→ notify property

On a direct listing it also checks that no competing lock exists, then writes an ExclusivityLock.

On an auction listing there is no lock and no competition check. Many operators reveal the same listing, each paying, and the reveal grants bidding rights instead.

Operations

post/listings/:id/revealOperatorThe whole sequence above in a single database transaction. Partial reveals must be impossible
get/my/revealsOperatorOwn reveal log
fnexportRevealLog(scope)OperatorAdminTimestamped export used as enforcement evidence

ExclusivityLock

Direct listings only. An auction never creates a lock.

Field	Type	Notes
id	uuid	Primary key
listing_id	uuid	Locked listing
operator_id	uuid	Holder of the exclusive window
starts_at	timestamptz	Set at the moment of reveal
expires_at	timestamptz	Twenty-one days after the reveal

Operations

fnacquireLock(listing, operator)SystemRedis lock plus a database row, inside the reveal transaction. A competing lock fails the whole reveal
fncheckNoCompetingLock(listing)SystemGate on every direct reveal attempt. The double-reveal race test is release-blocking
fnexpireLock()SystemAt twenty-one days the listing reopens and an unused deal expires

Deal execution

Deal

Two entry points, one spine. A direct reveal and an auction award both land here.

Field	Type	Notes
id	uuid	Primary key
listing_id	uuid	Listing being transacted
operator_id	uuid	Buying operator
source	enum	direct_reveal · auction_award
state	enum	See the state machine below
agreed_amount	int	Integer cents. Pre-set to the winning bid on an auction-sourced deal, so negotiation starts from a settled price
commission_rate	numeric	7% to €250k · 6% to €1M · 5% above
commission_amount	int	Integer cents. Covered by unit tests against all three bands

Entry points

reveal_made→deal_room_opendirect

auction_awarded→deal_room_openauction, at the winning price

Spine

offer_negotiation→ agreed→ contract_generated→ contract_signed→ escrow_funded→ activation_pending→ activation_confirmed→ completed

Terminal branches

expiredexclusivity lapsed unused cancelledmutual disputedadmin resolution

If an auction-sourced deal is cancelled before contract signature, the property may re-award the runner-up without re-running the auction.

Operations

post/dealsSystemOpened by a reveal, or by an auction award at the winning price
get/deals/:idOperatorPropertyOwn deals only
wsdeal:{id}OperatorPropertyMessages, offers and state changes in real time
get/admin/dealsAdminPlatform-wide board with state and age, plus alerts on stalled deals. Admins enter a room only when a deal is flagged
fncomputeCommission(amount)SystemInteger cents across the 7, 6 and 5 percent bands. No float arithmetic anywhere

Offer

Immutable rows

A counter is a new row. The latest accepted offer sets the deal's agreed amount.

Field	Type	Notes
id	uuid	Primary key
deal_id	uuid	Parent deal
sender_id	uuid	Operator or property user
amount	int	Integer cents
state	enum	open · accepted · superseded
created_at	timestamptz	The offer trail is evidence and is never rewritten

Operations

post/deals/:id/offersOperatorPropertySend an offer or a counter as a new immutable row
post/offers/:id/acceptOperatorPropertyAccept, which sets the deal's agreed amount and advances it to agreed

Message

Deal-room conversation. Part of the deal record.

Field	Type	Notes
id	uuid	Primary key
deal_id	uuid	Parent deal
sender_id	uuid	Author
body	text	Message text
attachments	jsonb	Document references, for example audience reports
created_at	timestamptz	Server time

Operations

wsdeal:{id}OperatorPropertySend and receive messages in the deal room
get/deals/:idOperatorPropertyReturns message history with the deal
fnviewFlaggedDeal(deal)AdminAdmins read a room only once a deal is flagged or disputed

Contract

Generated from standard templates only. No free drafting on the platform.

Field	Type	Notes
id	uuid	Primary key
deal_id	uuid	Parent deal
template_version	text	Which standard template produced this contract
operator_signed_at	timestamptz	Signature status per party
property_signed_at	timestamptz	Both are required to advance the deal
signed_doc_ref	text	Provider document reference

Operations

fngenerateFromTemplate(deal)SystemProduces the contract at the agreed amount
post/deals/:id/contract/signOperatorPropertySign through the e-signature provider
fnSignatureProviderSystemProvider behind our own interface. A mock ships first so external onboarding never blocks the critical path

EscrowTransaction

Funds are provider-held. They never touch SMH accounts.

Field	Type	Notes
id	uuid	Primary key
deal_id	uuid	Parent deal
provider_ref	text	Reference at the licensed escrow provider
state	enum	initiated · funded · released · refunded
gross_amount	int	Integer cents
commission_amount	int	Deducted at release, not before
net_payout_amount	int	What reaches the property

State machine

initiated→ funded→ released| refunded

Operations

post/deals/:id/escrow/fundOperatorFund escrow once both parties have signed
fnreleaseEscrow(deal)SystemFires on activation confirmation, deducts commission and pays the property
fnrefundEscrow(deal)SuperadminOverride requiring dual approval. No other role can move escrow
fnEscrowProviderSystemProvider abstraction. MockEscrowProvider ships first so the deal flow is never blocked

ActivationProof

The gate for escrow release. No confirmation, no payout.

Field	Type	Notes
id	uuid	Primary key
deal_id	uuid	Parent deal
files	jsonb	Photos, broadcast clips, links
submitted_at	timestamptz	Uploaded by the property after delivery
confirmed_by	uuid	The operator user who confirmed
confirmed_at	timestamptz	Timestamp that unlocks the payout

Operations

post/deals/:id/proofPropertyUpload proof that the sponsorship went live
post/deals/:id/confirm-activationOperatorConfirm the proof, which releases escrow minus commission

Oversight

BypassAlert

Raised by admins, manually during beta, referencing the reveal and bid logs.

Field	Type	Notes
id	uuid	Primary key
listing_id	uuid	Listing the suspected bypass concerns
operator_id	uuid	Suspected party
severity	enum	low · medium · high
evidence	jsonb	Reveal references, bid log export, press items
state	enum	open · investigating · resolved · escalated

Operations

get/admin/alertsAdminThe alert queue
post/admin/alerts/:id/resolveAdminResolve or escalate an alert
fnautoWatchLosingBidders(auction)SystemLosing bidders are a heightened risk — they know the identity and did not win — so their listings are watched automatically
fnexportEvidencePack(alert)AdminReveal log plus bid log, packaged for an enforcement case

Notification

Notifications are the engine of an auction. They ship with the bidding module, not after it.

Field	Type	Notes
id	uuid	Primary key
user_id	uuid	Recipient
type	enum	outbid · closing_soon at 24h, 1h and 10min · extension · result · reveal_made · offer_received · review_decision · award_due
payload	jsonb	Deep-link target and display values
read_at	timestamptz	Null while unread

Operations

wsuser:{id}OperatorPropertyAdminLive notification channel
get/notificationsOperatorPropertyAdminHistory and unread state
fnfanOut(event)SystemRedis-backed fan-out, with transactional email for anything time-critical

AuditLog

Append-only

Covers every state transition in the system, from the first sprint.

Field	Type	Notes
id	uuid	Primary key
actor_id	uuid	User or system process that caused the transition
entity_type	text	Which model changed
entity_id	uuid	Which row
from_state	text	Previous state
to_state	text	New state
created_at	timestamptz	Server time. Rows are never updated or deleted

Operations

fnappend(event)SystemCalled on every state transition, including reveals, offers, signatures and escrow events
fnexportAuditLog(scope)AdminImmutable and exportable, because this is legal evidence for non-circumvention enforcement

Invariants

Rules that hold across the models above. Breaking one of these is a release-blocking bug, not a ticket.

Identity is unreachable without a reveal

Enforced at the serializer, not the UI. A leaked identity is a lost commission. Automated tests guard this from Sprint 2 onward.

Bidder identities never reach other bidders

Masked for the whole auction, disclosed to the property at close. Bid amounts below the current high are never leaked.

Append-only where it matters

Bid, Reveal, CreditLedger, ComplianceRule versions, Offer and AuditLog are written once. A raise, a counter or a rule change is always a new row.

Money is integer cents

No float arithmetic anywhere. Commission is unit-tested against the 7, 6 and 5 percent bands, and escrow funds stay with the provider.

The server owns time

Bid timestamps and the auction clock are server-side. Clients render a countdown from a server timestamp and must survive clock skew and reconnection without ever showing an open auction as closed.

Bid placement is serialized per auction

Two bids arriving in the same millisecond produce a deterministic order, and the current-high read is never stale. The concurrency suite is part of Sprint 6, not a follow-up.

Reveals are atomic

Eligibility, mode, acceptance, debit, write and notify happen in one transaction. A partial reveal must be impossible.

Compliance fails closed

A market with no rules entry cannot be revealed. Every eligibility answer stores the rule version that produced it.