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 | admin | coach | member. |
| status | membership_status NOT NULL default active | active | invited | pending_invitation | suspended | cancelled. |
| payment_status | membership_payment_status NOT NULL default none | none | current | past_due | debt. Write-mostly mirror set by the payments module; never read for decisions. past_dueis legacy:refreshMembershipPaymentStatus (apps/api/src/payments/services/unpaid-term-close.ts) still emits it when every owing subscription carries the legacy subscriptions.status = 'past_due', which billing no longer writes — in practice only seeded or pre-debt-model rows. |
| joined_at | timestamptz default now() | When the membership became active. Cleared on a record cancelled before it ever held a member (revoked invitation shell, discarded join shell, released never-paid join registrant); restored with COALESCE(joined_at, now()) on re-activation. |
| end_date | timestamptz | When the record stopped counting as a member: stamped by every cancellation path, cleared by every re-activation (membership-lifecycle.ts); imports copy it from the source file. Analytics’ “Left” and month-end head-counts read it — never updated_at. Cancelled rows from before the paths stamped it are backfilled by scripts/backfill-membership-end-date.ts (end_date := updated_at; never-joined rows also get joined_at := NULL; imported rows stay NULL; manifest + --rollback). Run it per deploy.md › Post-deploy data backfills. See behavior.md › End date and “Left”. |
| 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. |
| source | membership_source nullable | Attribution: invite | join_link | lead_converted | import | manual. Imports stamp import; legacy and unstamped paths are NULL (unknown). |
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 overend_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.idviasource_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 | accepted | revoked | expired. |
| 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 1Returns 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; alsostatus='cancelled'andend_date(kept if already set) in the same transaction. No hard delete path exposed.invitations: status-based (revoked,expired). Thedeleted_atcolumn exists but is unused.
Computed/derived fields on member responses
hasClerkAccount—user.clerk_id != null; retained for clients that only need the boolean.accountStatus—activated | invited | not_invited, resolved from the Clerk link plus a case-insensitive pending invitation lookup in the same organization.profileComplete—UsersService.isProfileComplete(user).nationalIdMasked—UsersService.maskNationalId(user.national_id_encrypted).- Stats — computed on-demand in
computeMemberStats.