User

Purpose

Identifies a real person inside a single Supabase project. The platform runs three independent Supabase projects — admin (business panel), client (gym / tickets mobile + web), and superadmin — and users.users holds the union of admin + client identities only. Superadmin users never sync into this table.

Because UUIDs from different Supabase projects never collide, the same real person who uses both the business panel and the client app appears as two independent rows — one with scope = 'business', one with scope = 'client'. Within a single scope the identity stays unified: a business-Ivan that staffs three gyms still has one row.

Identity & key fields

• Primary key: id (uuid) — by schema comment, this is the Supabase user id from the issuing project (admin OR client); NOT auto-generated by Postgres.
• email (text, NOT NULL).
• phone (nullable text).
• globalName (mapped from column full_name, nullable text).
• avatarUrl (nullable text).
• scope (nullable text; CHECK scope IS NULL OR scope IN ('business', 'client')) — discriminator pointing at the issuing Supabase project. Nullable in v1 to allow gradual backfill; AuthSyncService.validateBusiness / .validateClient populate it on each new auth-sync (see ADR global-user-identity v3 amendment).
• No createdAt/updatedAt columns at this table level.

The display fields (globalName, avatarUrl) are platform-global. Per ADR global-user-identity, every public-facing render reads users.globalName / users.avatarUrl plus the related User public profile row (bio, specializations, links, slug, verifiedAt, coverPhotoUrl) — no per-company identity override exists anymore. The display-role label per company (e.g. “yoga instructor at Gym X”) lives on Company member roleLabel, decoupled from the permission role enum.

Invariants

• id and email NOT NULL (enforced in tktspace-backend/libs/shared/data-access-db/src/lib/schema/users.schema.ts).
• id is the Supabase auth user id (per schema comment) — NOT a gen_random_uuid() default (enforced in tktspace-backend/libs/shared/data-access-db/src/lib/schema/users.schema.ts).
• GIN trigram index users_global_name_trgm_idx on full_name (globalName) using gin_trgm_ops — enables contributor-name matching in pg_trgm activity search Tier B (enforced in tktspace-backend/libs/shared/data-access-db/src/lib/schema/users.schema.ts, migration 0043_pg_trgm_search_indexes.sql).

Business invariants:

• The id of this row equals the Supabase auth.users.id of the issuing project (admin OR client) — this table is a shadow of Supabase auth, not an independent identity store.
• Email and phone uniqueness is enforced by Supabase per project, not by this table — the application must not assume users.email is an authoritative dedup key. The same email can legally appear in two rows when one is scope = 'business' and the other scope = 'client'.
• Single identity within a Supabase scope. A business-scope user staffing three gyms has exactly one row (one business-Ivan, three Company member rows). A real person who uses both the business panel and the client app has two independent rows — one per Supabase project — and they are independent identities (independent profiles, independent slugs, independent verifications). Unifying them across surfaces is out of scope today (see ADR global-user-identity v3 amendment).
• Superadmin users are NOT in this table — the superadmin Supabase project does not invoke AuthSyncService and never writes here. Superadmin identity is JWT-bearer only (see superadmin-jwt.strategy.ts).

Lifecycle

No state column on this row.

• Create: on first Supabase signup or first reach of a TKT Space endpoint with a Supabase JWT.
• Mutation sources (auth-sync): AuthSyncService.validateBusiness(payload) is invoked by AdminJwtGuard (admin Supabase) and writes scope = 'business'; AuthSyncService.validateClient(payload) is invoked by ClientJwtGuard (client Supabase) and writes scope = 'client'. Each method only ever creates / refreshes the row for its own scope; cross-surface writes are impossible by construction (each guard sees a different Supabase JWT issuer). See tktspace-backend/libs/features/auth/src/lib/services/auth-sync.service.ts.
• Update: when the user edits their global profile (globalName, avatarUrl) — via PATCH /api/client/me or PATCH /api/business/me/public-profile (symmetric per surface — see API surfaces).
• Update — one-shot globalName backfill on customer linking. Inside AuthSyncService.linkUnlinkedCustomersByEmail (tktspace-backend/libs/features/auth/src/lib/services/auth-sync.service.ts), when an offline Company customer row’s userId becomes set, the service does a follow-up conditional update: UPDATE users SET full_name = <oldest matching companyCustomer.name> WHERE id = $supabaseId AND full_name IS NULL. The chosen source row is the OLDEST offline customer (ORDER BY company_customer.created_at ASC, id ASC LIMIT 1) — deterministic across multi-company linking. Idempotent: subsequent links are no-ops because globalName is already set (see ADR global-user-identity D6). This branch only runs from the client-scope auth-sync (linking is a client-surface concern).
• Delete: hard — performed via Supabase admin API. No soft-delete column today.

Per-company state — active / banned / inactive — lives on the joining rows in other contexts (Company member isActive, Company customer status), not here.

Future work

OPEN: a soft-disable mechanism (admin-side “deactivate user” without losing the row) is desired but not yet implemented. The current model only supports hard-delete via Supabase. Until then, “banning” a User platform-wide must be done either by deletion or by individually banning them on every company they participate in.

Relationships

• User profile (ENT-022) — user_profile.user_id → users.users.id, UNIQUE, on-delete cascade. 1:1. Private preferences (locale, notification flags) only.
• User public profile (ENT-042) — user_public_profile.user_id → users.users.id, UNIQUE, on-delete cascade. 1:1. Public-facing identity (bio, specializations, links, slug). May not exist — GET /me/public-profile synthesises all-null when absent.
• Company member (ENT-019) — company_member.user_id → users.users.id. 1:N (the same user may be a member of multiple companies).
• Company customer (ENT-017) — company_customer.user_id (nullable) → users.users.id. 1:N for online customers.
• User activity favorite (ENT-015) — user_activity_favorites.user_id → users.users.id, on-delete cascade. 1:N.
• Device token (ENT-023) — 1:N, on-delete cascade.
• Notification (ENT-026) — 1:N, on-delete cascade.

API surfaces

Surface	Exposed	Notes
client	yes — /me, /me/avatar, /me/notification-preferences, GET / PATCH /me/public-profile (UserMeDto, UpdateMeDto, UpdateNotificationPrefsDto, UpdateMyPublicProfileDto, UserPublicProfileDto). Client user edits the client-scope row only.	Swagger UI
business	yes — GET / PATCH /api/business/me/public-profile (symmetric with client; gated by AdminJwtGuard on the admin Supabase project). Business user edits the business-scope row only. Staff are otherwise accessed via Company member, not raw User rows.	Swagger UI
super-admin	no — the super-admin contract uses a separate Supabase project that never syncs into this table (see schema comment on sphereAuditLog.actorUserId)	Swagger UI

Known gotchas / open questions

• The PK is not auto-generated; it must equal the Supabase auth user id of the issuing project. Rows are created via auth flow, not directly.
• Super-admin auth uses a separate Supabase project (per sphere_audit_log.actor_user_id schema comment) — super-admin user ids do not match this table’s PK and must not be FK-linked. Superadmin never gets a users row.
• Cross-surface read is allowed; cross-surface edit is not. A business admin can read a customer’s client-scope public profile via JOIN (companyCustomer.userId → users → user_public_profile), and a client app can read a coach’s business-scope public profile the same way. Edits stay per-surface: each /me/public-profile endpoint operates only on its own surface’s user row (gated by the corresponding AdminJwtGuard / ClientJwtGuard). There is no endpoint that lets one surface mutate the other surface’s user.
• The notificationsEnabled / pushEnabled / emailEnabled preferences live on the related UserProfile, not on this row. The public-facing fields (bio, specializations, links, slug, verifiedAt, coverPhotoUrl) live on the related User public profile — every public render reads users.globalName + users.avatarUrl from THIS row AND the public-profile row in a single LEFT JOIN.
• Per ADR global-user-identity D9 (amends ADR contributors-must-be-members to v4), every name/avatar/bio render goes through member.user.* and member.user.publicProfile.*. The previous per-company identity columns on companyMember (publicName, bio, avatarUrl, specializations, links) have been dropped — historical pre-migration values are kept in the companies.company_member_legacy_identity backup table until release 2026.07.
• Cross-schema FKs from other contexts (company_member.user_id, company_customer.user_id, notifications.device_token.user_id, etc.) have no DB cascade when this row is deleted (deletion happens via Supabase admin API and triggers nothing in this database). The application is responsible for cascading cleanup — see Identity context Open questions.
• A single User may simultaneously be a customer AND a staff member of the same company (e.g., the gym owner who attends classes there) — both join rows coexist. This is per-scope: within the business scope, a business-Ivan can hold both Company member and Company customer rows for the same company.
• users.scope is nullable in v1 to allow gradual backfill. Auth-sync writes it on every login, so orphan rows heal over time. A follow-up ticket flips the column to NOT NULL once count(*) WHERE scope IS NULL = 0 is stable — see Improvement backlog.

Recommendations

Forward-looking improvements suggested while filling this doc — not currently in place.

• Add a soft-disable mechanism (e.g. isActive boolean or disabledAt timestamptz column) so the admin can deactivate a User platform-wide without invoking Supabase hard-delete. The current model forces either deletion or per-company banning, which is fragile.
• Add createdAt / updatedAt columns to this table. Today the User row has no timestamps of its own; only the related UserProfile tracks them.
• Define an explicit cascade contract for what happens on Supabase user deletion. Today no DB cascade fires across schemas, and the cleanup logic is scattered (or absent). A single owning service that fans out the cleanup would close the orphan-row risk.
• Consider a lastLoginAt column to support inactive-user reports and retention/GDPR workflows.
• Treat email/phone as application-mirror only — explicitly document that the source of truth for these fields is Supabase auth.users, not this table. A periodic resync job (or webhook on Supabase change) would prevent drift.