Platform Domain Model
PostgreSQL schema after migrations 0001–0093. Last updated: April 2026.
The platform is built around a Campaign → Offer → Negotiation → Deal chain. Operators fund campaigns on-chain via HTLC escrow; streamers and operators exchange offers, negotiate five deal terms bilaterally, and execute delivery through HTLC-backed deals. D2 diagrams below show key columns and FK relationships — scroll right or zoom in on wide tables.
ER Diagram — Campaign Flow
Core business entities: who creates campaigns, who participates, and how deals are agreed.
ER Diagram — Streamer Profile
Content creator account and all linked sub-entities (channels, wallets, KYC docs, portfolio, trust score).
ER Diagram — Delivery Chain
How a funded deal results in delivery proofs, operator confirmation, and on-chain payouts.
Status Machines
Campaign
Offer
Negotiation
Deal
Streamer
Session Submission
Table Reference
operators
| Column | Type | Constraints |
|---|---|---|
| id | SERIAL | PK |
| slug | TEXT | UNIQUE NOT NULL |
| name | TEXT | nullable |
| contact_email | TEXT | NOT NULL |
| status | TEXT | prepared · published · hidden |
| created_at / updated_at | TIMESTAMPTZ |
operator_wallets
| Column | Type | Notes |
|---|---|---|
| id | SERIAL | PK |
| operator_id | INT | FK → operators |
| wallet_address | TEXT | UNIQUE |
| consent_version | TEXT | T&C version at time of connection |
| connected_at | TIMESTAMPTZ |
invitations
| Column | Type | Notes |
|---|---|---|
| id | SERIAL | PK |
| code | TEXT | UNIQUE hex token |
| email / name | TEXT | Intended recipient |
| status | TEXT | pending · used · expired |
| operator_id | INT | FK → operators (set on redeem) |
| created_by | TEXT | Admin identifier |
| created_at / used_at | TIMESTAMPTZ |
campaigns
| Column | Type | Notes |
|---|---|---|
| id | SERIAL | PK |
| public_id | TEXT | UNIQUE · C-XXXXXX (IS-10) |
| operator_id | INT | FK → operators |
| name | TEXT | |
| category | TEXT | sportsbook · casino |
| status | TEXT | see state machine |
| budget_cap_usdc | NUMERIC(18,6) | Total escrow ceiling |
| escrow_id / network_id / funding_address | TEXT | On-chain identifiers |
| settings | JSONB | Versioned rich config (§ Campaign Settings) |
| start_date / end_date | TEXT | YYYY-MM-DD |
| minimum_terms | JSONB | Negotiation floor values per term |
| created_at / updated_at | TIMESTAMPTZ |
Campaign Settings JSONB (version "0.1")
| Key | Type | Notes |
|---|---|---|
| version | string | "0.1" |
| geos | string[] | Target audience GEO codes |
| spots_total | number | Max simultaneous streamers |
| payout_model | string | fixed · fixed+rs |
| payout_brackets | object[] | {condition, amount} per viewer tier |
| brief_summary | string | Operator brief shown to streamers |
| deliverables / brand_dos / brand_donts | string[] | Content guidelines |
| application_deadline | string | ISO date |
streamers
| Column | Type | Notes |
|---|---|---|
| id | SERIAL | PK |
| username / email | TEXT | UNIQUE NOT NULL |
| country | TEXT | ISO-3166 |
| status | TEXT | pending · active · suspended · archived |
| kyc_status | TEXT | not_started · pending · approved · rejected |
| deal_cpa_rate / deal_revshare_rate | TEXT | Withheld by API until allocation context |
| profile_published / deals_published | BOOLEAN | Visibility flags |
| created_at / updated_at | TIMESTAMPTZ |
Streamer Visibility Rules
| Rule | Condition | Behaviour |
|---|---|---|
| A — Streamer isolation | Any streamer session | /api/streamers and /:username return 403 |
| B — Operator tier | No funded/in-progress campaign | Anonymised list only; /:username returns 403 |
| B — Operator tier | At least one funded/in-progress campaign | Full profile fields returned |
| C — Pricing | Always | deal_cpa_rate / deal_revshare_rate replaced with computed price_bracket |
streamer_channels
| Column | Type | Notes |
|---|---|---|
| id | SERIAL | PK |
| streamer_id | INT | FK → streamers (nullable — unregistered streamers tracked by kick_user_id) |
| platform | TEXT | kick · twitch · youtube · facebook · twitter |
| channel_url / channel_name | TEXT | channel_name stored lowercase |
| verified | BOOLEAN | |
| kick_user_id / kick_follower_count / kick_is_live | various | Kick profile snapshot |
| twitter_user_id / twitter_follower_count | various | X profile snapshot |
UNIQUE on (streamer_id, platform, channel_url).
streamer_addresses
EVM wallet addresses for HTLC payouts. UNIQUE on (streamer_id, wallet_address).
streamer_documents
KYC uploads. storage_key is UNIQUE (S3 object key). streamer_id CASCADE on delete.
streamer_trust_scores
Latest trust score per (platform, platform_user_id) — UNIQUE. Full history in streamer_trust_score_history.
| Score component | Column |
|---|---|
| Overall | score NUMERIC(5,2) |
| Channel age | score_channel_age |
| Followers | score_followers |
| Stream frequency | score_stream_frequency |
| Avg viewers | score_avg_viewers |
| Chatter ratio | score_chatter_ratio |
Weights and benchmark pool are configurable via app_settings.trust_score_config.
offers
| Column | Type | Notes |
|---|---|---|
| id | BIGINT IDENTITY | PK |
| public_id | TEXT | UNIQUE · OFF-XXXXXX (IS-10) |
| campaign_id / operator_id / streamer_id | INT | FKs |
| initiated_by | TEXT | operator · streamer |
| starting_terms | JSONB | Pre-seeded term values, nullable |
| status | TEXT | see state machine |
| expires_at | TIMESTAMPTZ | Operator-initiated only; null for streamer-initiated |
| created_at / updated_at | TIMESTAMPTZ |
Partial UNIQUE index on (campaign_id, streamer_id) WHERE status IN ('pending', 'accepted').
negotiations
| Column | Type | Notes |
|---|---|---|
| id | BIGINT IDENTITY | PK |
| public_id | TEXT | UNIQUE · NEG-XXXXXX |
| offer_id | BIGINT | FK UNIQUE → offers (1:1) |
| terms | JSONB | {termKey: {value, proposed_by, op_ok, str_ok}} |
| turn | TEXT | operator · streamer · NULL when agreed |
| status | TEXT | in_progress · agreed · cancelled |
| agreed_at | TIMESTAMPTZ |
Five negotiated terms
| Term key | What it governs |
|---|---|
timeframe_days | Campaign window length in days |
deliveries | Number of qualifying streams to deliver |
min_viewers | Minimum average concurrent viewers per stream |
min_duration | Minimum stream length in hours |
payment_per_stream | USDC paid per confirmed stream |
A term is agreed when op_ok && str_ok. Proposing a new value resets the other party's flag.
deals
| Column | Type | Notes |
|---|---|---|
| id | BIGINT IDENTITY | PK |
| public_id | TEXT | UNIQUE · D-XXXXXX |
| negotiation_id | BIGINT | FK UNIQUE → negotiations (1:1) |
| agreed_terms | JSONB | Immutable flat snapshot at funding time |
| allocation_id / escrow_id / network_id / funding_address | TEXT | On-chain identifiers |
| allocation_amount | NUMERIC(18,6) | Total USDC locked |
| wallet_address | TEXT | Streamer wallet snapshotted at funding time |
| status | TEXT | active · completed · refunded · cancelled |
kick_stream_sessions
One row per distinct Kick broadcast, keyed by (kick_user_id, stream_started_at). Not linked to streamers by FK — join via streamer_channels.kick_user_id.
kick_streamer_probe
Per-minute viewer/follower/subscription snapshots for active broadcasts. Source data for trust score computation and delivery verification.
allocation_preimages
HTLC preimage set per offer. offer_id is UNIQUE (1:1). slots JSONB holds [{slot, amount, preimage}] — raw preimages for operator to reveal per confirmed slot.
session_submissions
| Column | Type | Notes |
|---|---|---|
| id | SERIAL | PK |
| public_id | TEXT | UNIQUE · SS-XXXXXX |
| offer_id | BIGINT | FK → offers |
| slot_index | INT | 0-based HTLC slot index |
| attempt | INT | 1-based; multiple attempts allowed per slot |
| status | TEXT | pending_review · confirmed · rejected |
| stream_url | TEXT | VOD/stream link |
| submitted_at / reviewed_at | TIMESTAMPTZ |
payout_records
Permanent confirmed-payout ledger. UNIQUE on (offer_id, slot_index).
| Column | Type | Notes |
|---|---|---|
| id | SERIAL | PK |
| offer_id | BIGINT | FK → offers |
| submission_id | INT | FK → session_submissions |
| slot_index | INT | |
| amount_usdc | NUMERIC(18,6) | Exact payout amount |
| allocation_id | TEXT | On-chain allocation identifier |
| tx_hash | TEXT | withdraw() on-chain tx; NULL until confirmed |
| wallet_address | TEXT | Snapshot at confirmation time |
| confirmed_at / tx_confirmed_at | TIMESTAMPTZ |
End-to-End Flow
Known Data Quality Notes
| # | Table | Issue |
|---|---|---|
| F-2 | session_submissions | No FK to kick_stream_sessions — platform session not joined to submission at review time |
| F-3 | deals | Auto-complete to completed on last slot confirmed not yet implemented |
| F-5 | session_submissions | slot_index not validated against allocation_preimages.slots array length |