Users & Auth — Data Model

Primary table: users in libs/db/src/lib/schema/users.ts. Related tables: member_profiles, legal_consents, device_tokens.

`users`

Column	Type	Notes
`id`	`uuid` PK	`defaultRandom()`. Internal canonical identifier.
`clerk_id`	`varchar(255)` UNIQUE	Clerk subject (`payload.sub`). Nullable for imported users that haven’t signed up yet.
`email`	`varchar(255)` UNIQUE NOT NULL	Lowercased on read where it matters (`acceptPendingInvitations` does `lower()` compare). Globally unique.
`first_name`	`text`	FitKit-authoritative. Webhook never overwrites.
`last_name`	`text`	Same.
`image_url`	`text`	Synced from Clerk on `user.updated`.
`phone`	`varchar(50)`	Normalized via `normalizeIsraeliPhone` on PATCH. Fallback to raw input.
`birth_date`	`date`	Validated against `isValidDob` (past date, age 13-120).
`gender`	`gender` enum	`male` / `female` / `non_binary` / `prefer_not_to_say`.
`emergency_contact_name`	`text`	Required for `profileComplete`.
`emergency_contact_phone`	`varchar(50)`	Normalized like main phone.
`emergency_contact_relationship`	`varchar(100)`	Free-text but typically constrained to `relationshipValues` from `@fitkit/shared`.
`national_id_encrypted`	`text`	Envelope-encrypted Israeli ID. Plaintext never stored. Read returns mask.
`guided_tour_completed_at`	`timestamptz`	FIT-93 post-onboarding tour completion.
`created_at` / `updated_at`	`timestamptz`
`deleted_at`	`timestamptz`	Soft-delete via `deleteSelf`.

Constraints / indexes

UNIQUE: clerk_id, email.
No additional indexes declared.

Profile-complete required fields


const PROFILE_REQUIRED_FIELDS = [
  'firstName',
  'lastName',
  'phone',
  'birthDate',
  'gender',
  'emergencyContactName',
  'emergencyContactPhone',
] as const;

All 7 must be non-null for isProfileComplete to return true.

`member_profiles`

Per-(user, org) extended profile.

Column	Type	Notes
`id`	`uuid` PK
`user_id`	`uuid` NOT NULL	FK → `users.id`.
`organization_id`	`uuid` NOT NULL	FK → `organizations.id`.
`address`, `city`, `country`, `zip`, `state`, `personal_id`, `additional_phone`	various	All optional gym-collected fields.
`allow_sms`, `allow_mailing_list`, `medical_cert`	`bool`	Consents.
`rfid`	`varchar(255)`	Physical RFID tag id.
`is_child`	`bool`
`arbox_user_id`	`int`	Mapping for Arbox import.
`metadata`	`jsonb`	Catch-all.
`profile_embedding`	`vector(1024)`	Semantic (“the new yoga coach”, “members in Tel Aviv”). HNSW index.
`embedding_hash`	`varchar(64)`	Gates re-embedding on no-op updates.

UNIQUE: (user_id, organization_id). One row per (user, org).

`device_tokens`

(Schema in libs/db/src/lib/schema/device-tokens.ts — not read in this audit but referenced by deleteSelf.) Soft-deleted on user delete so push stops.

Lifecycle of a `users` row

Created by either:
- Clerk webhook user.created (canonical path), or
- Lazily by GET /users/me if webhook hasn’t landed, or
- By an import job (Arbox/CSV) with clerk_id=NULL, name/email/phone pre-populated, awaiting linking.
Linked — first sign-in for an imported row updates clerk_id, image_url, and backfills first_name/last_name only if currently null.
Updated — name/phone/DOB/etc. via PATCH /users/me. Name pushed back to Clerk; nothing else is.
Deleted — DELETE /users/me (or webhook user.deleted) soft-deletes the row plus cascade (memberships → cancelled, subs → cancelled, device tokens → deleted).

Clerk → FitKit sync surface

Direction	Field	Trigger
Clerk → DB	`email`	webhook `user.updated`
Clerk → DB	`image_url`	webhook `user.updated`
Clerk → DB	`clerk_id` + initial `first_name`/`last_name`	webhook `user.created` / lazy create
DB → Clerk	`first_name`, `last_name`	`PATCH /users/me` via `syncToClerk`
Clerk delete → DB	soft-delete cascade	webhook `user.deleted`
DB delete → Clerk	`clerk.users.deleteUser`	`DELETE /users/me`

Nothing else syncs. Phone, DOB, gender, addresses, emergency contact, national ID are all FitKit-only.

National ID encryption

Service: NationalIdEncryptionService (apps/api/src/users/national-id-encryption.service.ts). Envelope encryption (TODO: verify which library/key source — likely crypto.createCipheriv with key from env). Stored as base64 ciphertext in national_id_encrypted. The maskNationalId helper returns ***1234 (last 4 digits).

Multi-org isolation pattern

users itself is global — no organization_id. Org isolation happens at the memberships table. To go from a user to an org’s view, the API takes both clerkId (caller) and orgId (resource), resolves to user → membership → org. See MembershipsService.requireMembership and ClassSessionsService patterns.

Soft-delete vs hard-delete

Soft for users (deleted_at).
Soft cascade: memberships (deleted_at), device_tokens (deleted_at), subscriptions (cancelled_at + cancel_at_period_end=true).
Hard delete on the Clerk side (the Clerk identity is removed so the email/phone is freed for a fresh signup).