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 |
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, classReminder (canonical in shared).
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_atsoft-delete. All reads filterIS NULL.- Hard delete only via user cascade.
notification_prefscascades on user delete.