Push Notifications — Data Model

`device_tokens`

Schema: libs/db/src/lib/schema/device-tokens.ts.

Column	Type	Notes
`id`	`uuid` PK
`user_id`	`uuid` NOT NULL	FK CASCADE
`expo_push_token`	`text` NOT NULL UNIQUE	Globally unique across all users
`platform`	`device_platform` NOT NULL	`ios \| android`
`app_id`	`varchar(180)` NOT NULL DEFAULT `fit.taikan.app`	Container or dedicated binary the token came from
`organization_id`	`uuid`	Set only for dedicated-app tokens
`locale`	`varchar(5)`	Device’s active language; never cleared by a client that omits it
`expo_project_id`	`uuid`	EAS project the token was minted under. `NULL` = unknown provenance (registered before the app started sending it). Delivery groups by this column because Expo rejects an entire request whose messages span two projects (`PUSH_TOO_MANY_EXPERIENCE_IDS`), and the container app was relinked from the FitKit project to the Taikan one on 2026-08-22, so both exist in the wild. Only written when the value matches a project the registering binary is known to use; never overwritten with `NULL`
`last_seen_at`	`timestamptz` NOT NULL DEFAULT now	Refreshed on every re-register
`created_at` / `updated_at` / `deleted_at`	`timestamptz`	Soft delete

Index: device_tokens_user_idx on user_id.

`notification_prefs`

Schema: libs/db/src/lib/schema/notification-prefs.ts.

Column	Type	Notes
`id`	`uuid` PK
`user_id`	`uuid` NOT NULL UNIQUE	One row per user
`prefs`	`jsonb` NOT NULL DEFAULT `{}`	`{ category: { channel: boolean } }`. Missing keys = opt-in

Channels: push, email. Categories: workoutAssigned, newMessage, newComment, announcement, bookingUpdate, classReminder, checkInReminder, automation, billing, trials, scoreReactions (canonical in shared). billing, trials and scoreReactions were added with no migration: a missing key is opt-in, so every member starts with billing push and email on, and every coach with the trials brief on.

bookingUpdate covers booking confirmations, waitlist changes, cancellations and class-cancelled notices; classReminder is only the T-30min reminder. They were one category until 2026-08-15, which left members no way to stop confirmation mail without also losing the reminder.

Multi-org isolation

Both tables are user-scoped, not org-scoped. A user with memberships in multiple orgs has one set of devices + one set of prefs across all orgs.
Per-org notification routing is a possible future need; not implemented today.

PII handling

Device tokens are credentials — anyone with the token can push to that device. Treat as secret.
Plaintext in DB. No encryption at column level.
Soft-delete preserves history; useful for ops debugging “did this device ever exist”.

Soft / hard delete

device_tokens.deleted_at soft-delete. All reads filter IS NULL.
Hard delete only via user cascade.
notification_prefs cascades on user delete.