Demand-Side Ad Platform: Campaigns, Targeting and Edit History

0) Problem Restatement

TikTok asked to design the data model for a demand-side ad platform (DSP): the system where advertisers create and edit campaigns. It must represent Advertiser → Campaign → Ad Group → Ad → Creative, how audience targeting is attached, budgets and bids, and keep an edit history so the platform can show "who changed what, when" and reconstruct a campaign as it was at any time (important for billing disputes and debugging delivery).

1) Entities and Relationships

classDiagram
    class Advertiser { +id +name +currency +status }
    class Campaign { +id +advertiserId +objective +budget +startAt +endAt +status +version }
    class AdGroup { +id +campaignId +bidType +bidAmount +pacing +status +version }
    class TargetingRule { +adGroupId +dimension +operator +values }
    class Ad { +id +adGroupId +creativeId +status }
    class Creative { +id +advertiserId +type +assetUrl +reviewStatus }
    Advertiser "1" --> "*" Campaign
    Campaign "1" --> "*" AdGroup
    AdGroup "1" --> "*" TargetingRule
    AdGroup "1" --> "*" Ad
    Ad "*" --> "1" Creative
    Advertiser "1" --> "*" Creative

• Campaign: objective (conversions, reach), total or daily budget, flight dates.
• Ad Group: where targeting, bidding and pacing live (standard in TikTok/Meta-style platforms).
• Ad: links an ad group to a creative (the video or image). Creatives are reusable across ads.

2) Schema

CREATE TABLE campaigns (campaign_id BIGINT PRIMARY KEY, advertiser_id BIGINT NOT NULL, name TEXT,
  objective TEXT, budget_type TEXT, budget_cents BIGINT, start_at TIMESTAMPTZ, end_at TIMESTAMPTZ,
  status TEXT, version INT NOT NULL DEFAULT 1, updated_at TIMESTAMPTZ, updated_by BIGINT);
CREATE TABLE ad_groups (ad_group_id BIGINT PRIMARY KEY, campaign_id BIGINT NOT NULL REFERENCES campaigns,
  bid_type TEXT, bid_cents BIGINT, pacing TEXT, status TEXT, version INT NOT NULL DEFAULT 1,
  updated_at TIMESTAMPTZ, updated_by BIGINT);
CREATE TABLE targeting_rules (ad_group_id BIGINT, dimension TEXT,   -- age, gender, geo, interest, audience_segment, device
  operator TEXT,                                                    -- include / exclude
  values TEXT[], PRIMARY KEY (ad_group_id, dimension, operator));
CREATE TABLE ads (ad_id BIGINT PRIMARY KEY, ad_group_id BIGINT REFERENCES ad_groups, creative_id BIGINT, status TEXT);
CREATE TABLE creatives (creative_id BIGINT PRIMARY KEY, advertiser_id BIGINT, type TEXT, asset_url TEXT,
  duration_sec INT, review_status TEXT);
CREATE INDEX ON ad_groups (campaign_id);
CREATE INDEX ON campaigns (advertiser_id, status);

3) Edit History

Two good options:

• History tables (simple, common): every update copies the old row into campaigns_history (campaign_id, version, valid_from, valid_to, changed_by, row_json). "Campaign as of time T" = the history row where valid_from ≤ T < valid_to. Triggers or the service layer write it in the same transaction.
• Event sourcing (powerful): store every change as an event (BudgetChanged{from, to}, TargetingUpdated{...}) and build current tables from them. Great audit and replay, but more complex.

Also keep a user-facing change log: (entity, entity_id, field, old_value, new_value, changed_by, changed_at) for the "Activity" tab.

4) Serving and Reporting Views

• Ad servers need fast, denormalized "active ad group + targeting + creative" records, built from change events (CDC) into an in-memory or key-value index.
• Reporting joins delivery facts with these entities as they were at delivery time (using history, i.e., SCD2 dimensions in the warehouse).

5) Wrap-Up

Model Advertiser → Campaign (budget, dates) → Ad Group (bids, pacing, targeting) → Ad → reusable Creative, with targeting stored as flexible include/exclude rule rows. Protect edits with version-based optimistic locking, record full history with versioned history tables (or event sourcing) plus a readable change log, and publish changes via CDC into denormalized serving indexes for ad servers and historical dimensions for reporting.