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.

Text
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.