Memberships — Data Model

Two tables: memberships (the link) and invitations (the pre-acceptance shell).

`memberships`

Schema: libs/db/src/lib/schema/memberships.ts.

Column	Type	Notes
`id`	`uuid` PK
`user_id`	`uuid` NOT NULL	FK → `users.id`.
`organization_id`	`uuid` NOT NULL	FK → `organizations.id`. Direct org boundary.
`role`	`membership_role` NOT NULL default `member`	`owner
`status`	`membership_status` NOT NULL default `active`	`active
`payment_status`	`membership_payment_status` NOT NULL default `none`	`none
`joined_at`	`timestamptz` default `now()`	When the membership became active.
`end_date`	`timestamptz`	Used by analytics (members-growth queries).
`created_at` / `updated_at`	`timestamptz`
`deleted_at`	`timestamptz`	Soft-delete set by `usersService.deleteSelf`.
`source_lead_id`	`uuid`	FK → `leads.id`. Populated when membership was created via `convertLead`.

Constraints / indexes

UNIQUE: (user_id, organization_id) — one membership per (user, org).
memberships_org_status_idx — (organization_id, status) — listMembers filter.
memberships_user_id_idx — (user_id) — getUserMemberships.
memberships_org_joined_at_idx — analytics bucket by month, scoped by org.
memberships_org_end_date_idx — analytics window over end_date.

Relations

user — one.
organization — one.
bookings — many (bookings.membership_id).
coachedSessions — many (class_sessions.coach_membership_id).
subscriptions, paymentMethods, paymentTransactions, feedItems, reactions, comments, programEnrollments, courseEntitlements — all many.
sourceLead — optional one (leads.id via source_lead_id).

`invitations`

Schema: libs/db/src/lib/schema/invitations.ts.

Column	Type	Notes
`id`	`uuid` PK
`organization_id`	`uuid` NOT NULL	FK → `organizations.id`.
`email`	`varchar(255)` NOT NULL	Lowercase-compared at accept time.
`role`	`membership_role` NOT NULL default `member`	Intended role on acceptance.
`status`	`invitation_status` NOT NULL default `pending`	`pending
`clerk_invitation_id`	`varchar(255)`	The id from `clerk.invitations.createInvitation`. Best-effort revoke uses this.
`invited_by`	`uuid`	`users.id` of the inviter. Plain FK (not enforced — TODO: verify whether a FK is declared).
`expires_at`	`timestamptz` NOT NULL	`now() + 7 days`.
`accepted_at`	`timestamptz`	Set on `acceptPendingInvitations`.
`created_at` / `updated_at`	`timestamptz`
`deleted_at`	`timestamptz`	Reserved; not written.

Constraints / indexes

invitations_org_status_idx — (organization_id, status) — list pending in org.
invitations_email_status_idx — (email, status) — accept-pending search by email.

Lifecycle

`memberships`


                        ┌─────────────────┐
                        │   (insert)      │
                        └────────┬────────┘
                                 │
              ┌──────────────────┼──────────────────┐
              │                  │                  │
       owner self-create   createInvitation    convertLead /
       (org create)         (shell row)        sign-up auto-accept
              │                  │                  │
        ┌─────▼───┐        ┌─────▼────────┐   ┌─────▼─────┐
        │ active  │        │ pending_invit│   │  active   │
        └─┬─┬─────┘        └─────┬─┬──────┘   └─┬─────────┘
          │ │                    │ │            │
          │ │ updateMembership   │ │            │
          │ │ (suspended/cancel) │ │            │
          │ ▼                    │ │            │
          │ suspended/cancelled  │ revokeInvit. │
          │                      │ ▼            │
          │ deleteSelf cascade   │ cancelled    │
          ▼                      ▼              │
        cancelled + deleted_at   ◄──────────────┘
                                 acceptPendingInvitations
                                 (pending_invitation → active)

`invitations`


pending → accepted   (acceptPendingInvitations)
pending → revoked    (revokeInvitation)
pending → expired    (expires_at < now() at accept time)

Status transitions are one-way; no resurrection of a non-pending invitation. Resend creates a fresh Clerk token and updates the same row’s clerk_invitation_id + expires_at.

Multi-org isolation pattern

memberships.organization_id is the direct org boundary. Every read/write filters on it. requireMembership(orgId, clerkId):


SELECT * FROM memberships
WHERE organization_id = $orgId
  AND user_id = (SELECT id FROM users WHERE clerk_id = $clerkId)
  AND status = 'active'
LIMIT 1

Returns the row or throws ForbiddenException('Not a member of this organization'). Result is cached for 30s per (orgId, userId) tuple per process.

Soft-delete vs hard-delete

memberships: soft (deleted_at) on user self-delete; also status='cancelled' set in the same transaction. No hard delete path exposed.
invitations: status-based (revoked, expired). The deleted_at column exists but is unused.

Computed/derived fields on member responses

hasClerkAccount — user.clerk_id != null.
profileComplete — UsersService.isProfileComplete(user).
nationalIdMasked — UsersService.maskNationalId(user.national_id_encrypted).
Stats — computed on-demand in computeMemberStats.