# Data model

Buildable DDL: TypeORM migrations in [`services/api/src/db/migrations/`](../services/api/src/db/migrations/) (ADR 0023). Historical SQL: [`schema/historical/`](../schema/historical/).
Demo seeds: [`schema/seeds/demo.sql`](../schema/seeds/demo.sql).

Conceptual identity and party model from spec §15 / §46.9 and ADRs 0001, 0013, 0014, 0026.

## Party (organisations)

Generic `party` with a type enum (ADR 0001): `PLAYER`, `OPERATOR`, `PROVIDER`, `AFFILIATE`, `MERCHANT`, `BRAND`, `APPLICATION`, plus integration. External IDs stay typed (`pp_opr_...`, `pp_prv_...`, `pp_int_...`), never `pp_party_...`.

## Player identity

Tables (TypeORM entities / baseline migration):

- `players`: private UUIDv7 `id` + `public_player_id` (`pp_plr_...`); optional `reservation_expires_at` while status=`reserved` (A0 pre-mint); `analytics_subject` (`pp_anl_...`) minted at activation; `anonymised_at` / status=`anonymised` after Delete Account
- `authentication_identities`: `issuer` + subject cipher + lookup HMAC (`UNIQUE(issuer, subject_lookup_hmac)`); opaque `iam_subject` (OIDC sub / ZITADEL user id) for password sign-in reverse lookup — not PII, never on `/v1/me`
- `player_aliases`: `pp_plr_opr_...` / `pp_plr_prv_...` (revoked aliases stay reserved)
- `external_identities`: partner-issued IDs
- `integrations`: provider × operator
- `identity_links`: consent / `effective_from` / `unlinked_at` / `revoked_at`
- `link_intents`: short-lived assertion exchange
- `player_registrations`: bootstrap state machine
- `player_sessions`: BFF cookie sessions; optional `iam_user_id` for signed-in factor add; optional `oidc_id_token` for logout handle only
- `webauthn_credentials`: public-key mirror for discoverable portal passkeys (no private keys)
- `player_account_merges`: dual-proof merge (UI later)
- `party_jwks` / `assertion_jti`: operator assertion verify + replay protection

Loyalty, Activity, and serving tables (TypeORM):

- `activity_batches` / `activity_uploads` / `activity_events` / `points_ledger` / `campaign_versions`
- Daily serving: `analytics_account_day`, `analytics_account_provider_day`, `analytics_partner_player_day` (provider Party + that provider’s `pp_plr_prv_...` + day), `analytics_studio_account_day` (provider Party + day, distinct Accounts)

The live ledger may reference `players.id`. Partner studio reads do not. After Delete Account, retained Activity keeps `round_ref` and analytics subject with `player_id` / `player_alias` nulled; partner-player-day rows for those aliases are deleted.

## HTTP API

NestJS control plane (`services/api`): `/auth/*`, `/v1/me`, `DELETE /v1/me/connections/:audienceId` (Unlink), `DELETE /v1/me` (Delete Account), `/v1/player-reservations`, `/v1/link-intents*`, `POST /v1/activity-batches`, `POST /v1/activity-uploads`, `POST /v1/activity-uploads/:uploadId/complete`, `GET /v1/analytics/studio`, `GET /v1/analytics/studio/aliases/:alias`. See [`infra/README.md`](../infra/README.md). Normative examples remain in spec §37.