Problem RestatementProblem
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).
Entities and Relationships
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
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.
SchemaData model
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);Deep Dive — Answering "what did this campaign look like last Tuesday?"Deep dive
Advertisers change budgets, bids and targeting constantly, then ask why spend looked different last week. A schema that only stores the present cannot answer that.
Update the row in place
UPDATE campaigns SET daily_budget = 5000 WHERE id = 91.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
E["Budget changed 3 times this week"] --> ROW["One row - only the latest value"]
ROW --> Q["Why did Tuesday's spend spike?"]
Q --> NONE["No record of Tuesday's budget"]
ROW --> WHO["No record of who changed it, or when"]
ROW --> DISP["Advertiser disputes a charge - nothing to show them"]History is destroyed on every edit. Beyond the analytical problem, this is a commercial one: ad platforms bill from these settings, and a dispute you cannot reconstruct is a dispute you lose.
A change log of field diffs
Record (entity, entity_id, field, old_value, new_value, changed_by, changed_at) on every edit.
This is genuinely useful and it is what the user-facing "Activity" tab should show. As a reconstruction mechanism it is awkward: recovering the state at time T means replaying every diff for that entity from creation, in order, and getting the types right. It works for a human reading a list; it is painful as the basis for a report.
Versioned rows with validity ranges, plus the change log
Keep both, because they serve different readers:
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
UPD["Update campaign 91"] --> TX["One transaction"]
TX --> HIST["campaigns_history: version, valid_from, valid_to, changed_by, row_json"]
TX --> CUR["campaigns: current row, version + 1"]
TX --> LOG["change_log: field, old, new, who, when"]
ASOF["Campaign as of T"] --> SEL["SELECT ... WHERE valid_from <= T AND T < valid_to"]
SEL --> ONE["One indexed row - no replay"]
LOG --> TAB["Activity tab for humans"]- As-of queries become a single lookup. "The campaign as it was at T" is one row, which means reports can join spend against the settings that were actually in force — the thing the change log makes hard.
- Write the history row in the same transaction as the update, via the service layer or a trigger. Written afterwards, it is eventually missing for exactly the edit someone asks about.
- Keep the change log too. The history row answers "what was it"; the change log answers "what changed and who did it", which is the question a user is actually asking in the UI.
version it read, and the update is conditional on that version still being current. Two advertisers editing the same campaign means the second gets a conflict and re-reads, rather than silently overwriting a budget change they never saw.
Event sourcing is the alternative worth naming — every change as an event, current tables projected from them — with better replay and audit at meaningfully higher complexity. For a campaign model, versioned rows usually win.
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).
Wrap-UpWrap-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.