# Data model

The tables the tracker owns. Other apps own others in the same database; see
[Architecture](https://acrune.com/dashboard/docs/engineering/architecture.md) 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](https://acrune.com/dashboard/docs/platform/accounts-and-keys.md).

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