ER Diagram

Purpose

Visual/textual map of how every entity connects — companion to Table Specifications.

Entity Relationship Summary

(Full field-level detail lives in Table Specifications; this is the shape.)

Revised 2026-08-23 — see Update below for why: no separate Campaign entity, no Paddle fields, BillingCycle sits between Sale and Payout.

User
 ├── 1:1 → MerchantProfile (optional)
 └── 1:1 → CreatorProfile (optional)

MerchantProfile
 └── 1:N → Offer

Offer
 └── 1:N → Application

Application
 └── 1:1 → AffiliateLink (created on approval)

AffiliateLink
 └── 1:N → AttributionEvent

AttributionEvent (type=purchase)
 └── 1:1 → Sale

Sale
 ├── 1:1 → Commission
 ├── 1:1 → PlatformFee
 └── N:1 → BillingCycle (aggregated per Merchant, per billing period)

Commission
 └── N:1 → Payout (aggregated toward the payout threshold, per Creator)

MerchantProfile
 └── 1:N → BillingCycle

User
 └── 1:N → Notification

Notes on Cardinality Decisions

• One Application per approved link: a creator can only have one active AffiliateLink per Offer — prevents a creator generating multiple links for the same offer to obscure attribution.
• Commission and PlatformFee are separate rows, not just fields on Sale: keeping them as distinct entities makes the financial ledger auditable per 01. Business Logic → Commission Engine's split math, and gives a clean place to record refund-credit adjustments against a specific Commission row.
• Payout aggregates many Commissions: since payout is threshold-based rather than per-sale, a single Payout can (and usually will) represent multiple Sales/Commissions bundled together — needs a join table (see Table Specifications).
• BillingCycle aggregates many Sales, per Merchant: the merchant-owed side of the ledger (commissions + platform fee accumulated since the last cycle), separate from the Payout aggregation on the creator side — added 2026-08-07 for external-site tracking, unaffected by the Campaign removal.

Open Questions

• Whether PlatformFee needs its own table or can just be a field on Sale (leaning toward its own table for auditability, but it's a reasonable simplification either way for MVP)

Diagram

erDiagram
    USERS ||--o| MERCHANT_PROFILES : has
    USERS ||--o| CREATOR_PROFILES : has
    MERCHANT_PROFILES ||--o{ OFFERS : lists
    OFFERS ||--o{ APPLICATIONS : receives
    APPLICATIONS ||--|| AFFILIATE_LINKS : creates
    AFFILIATE_LINKS ||--o{ ATTRIBUTION_EVENTS : tracks
    ATTRIBUTION_EVENTS ||--|| SALES : results_in
    SALES ||--|| COMMISSIONS : generates
    SALES ||--|| PLATFORM_FEES : generates
    SALES }o--|| BILLING_CYCLES : included_in
    MERCHANT_PROFILES ||--o{ BILLING_CYCLES : owes
    COMMISSIONS }o--|| PAYOUTS : aggregated_into

Update (2026-08-23): MAJOR REVISION — Campaign Removed, Paddle Fields Gone

Founder decisions, full reasoning in 02. Architecture Decision Log; schema-level detail in 03. Table Specifications (updated same date).

• Campaign entity removed — "offer is offer, it is not turning into any campaign at all." OFFERS ||--o{ CAMPAIGNS : has and CAMPAIGNS ||--o{ APPLICATIONS are gone; OFFERS ||--o{ APPLICATIONS connects directly, one fewer hop than the original diagram.
• BillingCycle added to this diagram — it existed in Table Specifications since 2026-08-07 but was never added to this ER diagram/summary until now; corrected above.
• Paddle-derived fields removed, Swich fields added — no paddle_transaction_id, paddle_payout_id, paddle_seller_id, paddle_customer_id anywhere in this model. Merchant billing and creator payout both settle via Swich (confirmed 2026-08-23), referenced by swich_invoice_id/swich_payment_reference (BillingCycle) and swich_payout_id (Payout) instead of Paddle's IDs — see 03. Table Specifications for the full field list (working draft, pending real Swich integration).
• Market/currency: Pakistan-only, PKR only — no schema impact on this diagram's shape, only on offers.currency's allowed values (see Constraints, updated same date).