Platform
Data model
The tables the tracker owns. Other apps own others in the same database; see Architecture for the ownership rule.
The core five
accounts — one tenant. A client platform, or one of our own campaigns.
Everything else hangs off this, and every credential resolves to exactly one.
campaigns — one destination URL. click_param is the parameter the click
id is written into. external_id is the client's own id for the campaign,
unique per account where it is set, which is what makes campaign creation
idempotent from the partner API.
creators — one person on one platform. external_id is the client's own
id for them: their product already knows who the user is, and this is how that
identity survives into our records without us ever holding their user table.
links — the unit handed to a creator: one short code per creator per
campaign, optionally per post. post_url is what makes per-post ranking
possible, so it is the field worth insisting on.
clicks — one row per redirect served. This table is the product.
accounts ─┬─ campaigns ─┐
├─ creators ──┼─ links ── clicks
└─ api_keys ┘Identity, and the two unique indexes that matter
creators (account_id, platform, handle) is unique: one handle on one platform
belongs to one creator inside an account.
creators (account_id, external_id) is unique where external_id is set. This
is the one that makes enrolment's idempotency real rather than claimed —
without it, two concurrent enrolments of the same person produce two creator
rows and split their clicks between them.
The consequence worth knowing before you integrate: one external_id is one
creator is one link. A person with a TikTok and an Instagram that should be
counted separately must be enrolled twice, under a per-social-account id. Their
user id will not do it.
The same pair on campaigns (account_id, external_id) is what makes
POST /v1/campaigns safe to retry.
Short codes
Link ids are random, not sequential, so a competitor cannot walk the space and read a client's roster. Random means collisions are possible rather than impossible, so inserts retry against the primary key instead of assuming uniqueness.
The alphabet is Crockford-ish base32 with the vowels removed, so a generated code cannot spell a word.
Credentials and the audit trail
api_keys — one per client integration, scoped to an account. Only the
hash is stored; prefix exists so a key can be recognised in a list without
being recoverable from one. scopes is read and/or write.
admin_keys — operator credentials. Global rather than per-tenant, scoped
read/write/money. The key's id is the actor an audit entry names.
audit_log — append-only, who did what. Every privileged mutation writes
one, with the key behind it as the actor.
api_key_usage — one row per key per minute, for
rate limiting.
Tables that are not features
The database also carries conversions, attributions, ledger_entries,
identity_bindings, webhook_endpoints, payout_runs, fraud_findings and
more, with rows in several of them. They are the schema of an earlier version of
this product whose code was removed.
Nothing in the API serves them. Treat a table name as evidence of history,
not of a feature, and check apps/track/src/app.ts before believing otherwise.