Sponsor Billing and Partner Settlement - Simple Visual Guide

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.

Four Rules to Remember

  1. A successful report creates one sponsor charge.
  2. A callback request creates one additional sponsor charge.
  3. A partner receives a percentage of the report charge only.
  4. Callback charges belong entirely to Zetld and never enter partner settlement.

1. Business Flow

[Diagram]

The sponsor invoice and partner settlement are related, but they are not the same record:

2. What Creates a Financial Event?

[Diagram]

The event ledger is the single financial source of truth. The application must not calculate historical bills from current rate settings.

Event Differences

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

3. Monthly Processing

[Diagram]

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.

4. Example With Real Numbers

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
[Diagram]

Calculation:

Sponsor invoice = $150 reports + $60 callbacks = $210
Partner payments = $30 to P1 + $10 to P2 = $40
Zetld net = $210 - $40 = $170

5. Database Table Map

[Diagram]

Table Responsibilities

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.

6. Why This Is Not Overengineered

The design intentionally does not contain:

Only four financial tables are introduced:

  1. sponsor_billing_periods for collecting money.
  2. sponsor_billable_events as the financial ledger.
  3. partner_settlements for paying partners.
  4. partner_settlement_items to prevent missing or duplicate partner payments.

7. Essential Safety Rules