This document explains the design in business language first and database language second.
For a complete walkthrough with sample rows and multiple partners and sponsors, read SPONSOR_PARTNER_BILLING_EXAMPLE_WALKTHROUGH.md after this guide.
The sponsor invoice and partner settlement are related, but they are not the same record:
The event ledger is the single financial source of truth. The application must not calculate historical bills from current rate settings.
| Value | Report event | Callback event |
|---|---|---|
| Sponsor is charged | Yes | Yes |
| Partner is identified | Yes for partner reports | May be retained for analytics |
| Partner share percentage | Snapshotted from partner | Always 0 |
| Partner share amount | Calculated once | Always 0 |
| Can enter partner settlement | Yes | No |
The rule deciding when a report becomes eligible for partner settlement can be chosen later, for example after the sponsor is invoiced or after the sponsor has paid. The schema supports either policy without redesign.
Assume sponsor S1 has these September events:
| Source | Usage | Sponsor charge | Partner share |
|---|---|---|---|
| Partner P1 | 10 reports at $10 | $100 | 30% = $30 |
| Partner P2 | 5 reports at $10 | $50 | 20% = $10 |
| Callback requests | 3 callbacks at $20 | $60 | $0 |
Calculation:
Sponsor invoice = $150 reports + $60 callbacks = $210
Partner payments = $30 to P1 + $10 to P2 = $40
Zetld net = $210 - $40 = $170
| Table | One-sentence responsibility |
|---|---|
partner_reports |
Records which partner submitted the contract. |
reviews |
Runs the summary-generation process. |
sponsor_reviews |
Connects the generated report to its sponsor. |
sponsor_callback_requests |
Stores each buyer callback request. |
sponsor_rate_cards |
Configures sponsor report and callback prices. |
sponsor_billing_periods |
Manages one sponsor invoice month and its status. |
sponsor_billable_events |
Stores the immutable money result of every report or callback. |
partner_settlements |
Manages one monthly payment to a partner. |
partner_settlement_items |
Identifies the report events included in that payment. |
The design intentionally does not contain:
Only four financial tables are introduced:
sponsor_billing_periods for collecting money.sponsor_billable_events as the financial ledger.partner_settlements for paying partners.partner_settlement_items to prevent missing or duplicate partner payments.sponsor_review_id.callback_request_id.