Managed Members — Data Model

Ticket: TKN-382  Status: Specification, revision 2. Not implemented. Revision 2 supersedes the participants-entity design. See §2 for why it was wrong and what replaced it.

Normative for schema. Every claim about current state carries a path:line citation; anything unverified is marked [INFERENCE].

1. The actual problem

Not “we have no person entity”. We have one. The problem is narrower than revision 1 claimed:

2. Decision: no new person entity

Revision 1 proposed a participants table plus a participant_identity view to coalesce it against users. That was wrong, and the review objection was correct.

Why it was wrong

users is already a person table, not an account table. Three pieces of evidence, all in the running codebase:

1. users.clerkId is already nullable (users.ts:21). A row that cannot log in is already representable and already exists.
2. Login-less “shell users” are pervasive and load-bearing. memberships.service.ts:1587-1601 inserts a users row with no clerkId, carrying firstName, lastName, phone, birthDate, gender, emergencyContactName/Phone/Relationship — and attaches a pending_invitation membership to it (:1605). The same pattern appears at :2518. These rows carry form instances (forms.service.ts:2048-2052), legal consents, and their own scrub/tombstone lifecycle (payment-monitoring.service.ts:910-924). Insights has a dedicated join to exclude them from roster counts (insights.service.ts:1704-1708).
3. users holds person attributes, not auth attributes: firstName, lastName, birthDate, gender, phone, emergencyContact*, nationalIdEncrypted (users.ts:23-38). Authentication is one nullable column.

So the only thing making users an “account” table is the email NOT NULL UNIQUE constraint. A second person table would have duplicated an existing concept, and the coalescing view in revision 1 was a smell: it existed purely to paper over having two person tables. Removing the second table removes the need for the view.

What replaced it

A managed member is a users row that a responsible adult may act for. That is the whole definition, and it is the one an implementer is most likely to get wrong.

Being managed, having an email, and having a login are three orthogonal axes:

kind = 'managed' does not mean “has no email” and does not mean “cannot log in”. All four of these are valid and all four occur:

1. Child, no email, no login — the adult books from their own account. A fully valid bookable entity.
2. Child with an email, no login — the adult books; the child also receives the confirmation.
3. Teen with email and a login — books for themselves, and the adult still books and pays. Guardianship does not end because a login begins.
4. Adult with a login and no guardian — today’s normal case.

Case 3 is why claiming a login and ceasing to be managed are two separate acts (§3.9). Conflating them is what forced the earlier draft into treating majority as a single violent flip; separating them yields the graduation overlap band for free.

The cost of this decision, stated honestly: users no longer means “someone who can sign in”, users.email stops being a guaranteed unique business key, and every code path that sends an email or matches on one must handle null. That is a real cost. It is smaller than the cost it avoids, it is compiler-enforceable for reads (§6), and the uniqueness half is replaced deliberately rather than lost (§3.1).

What this collapses

Revision 1’s largest work package disappears entirely.

Cross-organization identity — the reason revision 1 rejected “membership-as-participant” — is preserved for free: one child is one users row with one membership per branch, exactly as an adult is today.

Naming

“Participant” is dropped. With no new entity there is nothing to name, and the review objection stands on its own merits.

אחראי is adopted over both אפוטרופוס and הורה/מבוגר מאשר. It is warmer than אפוטרופוס, accurate for a grandparent or legal guardian in a way הורה is not, and it matches how the gym law actually frames the role — הורה there is defined to include anyone with lawful custody, which is exactly “the responsible one”. אפוטרופוס is retained in legal documents and the staff record only, where the statutory term is the right one.

3. Schema changes

3.1 users — one constraint relaxed, uniqueness re-established

~ email           varchar(255) NULL                 (was NOT NULL)
- unique(email)
+ uniqueIndex(email)          WHERE email IS NOT NULL
+ nationalIdHash  varchar(64) NULL                  -- deterministic HMAC-SHA256
+ uniqueIndex(nationalIdHash) WHERE national_id_hash IS NOT NULL
+ kind            userKind NOT NULL DEFAULT 'account'   -- 'account' | 'managed'

Notes that matter:

• The partial unique index on email preserves the existing invariant for everyone who has one, including the tombstoning path that rewrites an address to deleted+<id>@deleted.taikan.fit on scrub. Tombstones stay unique because they stay non-null.
• kind is an explicit enum rather than an inferred email IS NULL test. A shell user awaiting invitation acceptance also lacks a Clerk identity but is an account-to-be; conflating the two would push invited adults through the guardian flow. The distinction must be stated, not derived — and since email and login are orthogonal to kind (§2), there is nothing to derive it from anyway.
• birthDate becomes required for kind = 'managed' — CHECK-enforced. It is the input TKN-383’s age eligibility needs.

Uniqueness cannot depend on email

users.email NOT NULL UNIQUE is today’s dedup mechanism, and findOrCreateFromClerk matches on it (apps/api/src/users/users.service.ts:100-102). Relaxing the constraint removes a business key, not just a validation. Losing it silently would produce duplicate children and mislinked logins, so it is replaced in three deliberate layers:

1. nationalIdHash — the real key. Every Israeli resident receives an immutable מספר זהות at birth registration; the physical document only issues from age 16, and both Arbox and Boostapp already collect it (research.md §1). So a child with no email and no phone is still uniquely identifiable. nationalIdEncrypted already exists but is encrypted and therefore cannot carry a unique index — hence a separate deterministic HMAC-SHA256 column with a partial unique index. The hash is keyed separately from the encryption key and is never reversible to the ID.

2. Soft dedup where there is no ת.ז. Same firstName + lastName + birthDate under the same responsible adult → warn staff, do not block. This is the Arbox duplicate-profile failure mode surfaced as a warning rather than a silent duplicate. Blocking would be wrong: a family can legitimately reach that state after a re-registration, and a hard constraint here would wedge the front desk.

3. A token, never an email, links a login. See §3.9. Email may be absent, or may already belong to a different users row.

The '' hazard. Any normalisation that maps NULL to the empty string — email?.trim().toLowerCase() ?? '' — makes every managed member collide with every other managed member. Two unrelated children become one person. This is a data-integrity bug, not a delivery bug, and it is the single most dangerous consequence of this change. Audited call-site by call-site in §6.

3.2 guardianships — the one genuinely new table

libs/db/src/lib/schema/guardianships.ts

A person↔person capability grant. Global by default (organizationId nullable), per the review decision on Q-06.

Constraints:

• uniqueIndex('guardianships_global_uq').on(subjectUserId, guardianUserId).where(sql\organization_id IS NULL AND status <> ‘revoked’`)`
• uniqueIndex('guardianships_scoped_uq').on(subjectUserId, guardianUserId, organizationId).where(sql\organization_id IS NOT NULL AND status <> ‘revoked’`)`
• uniqueIndex('guardianships_one_primary_uq').on(subjectUserId).where(sql\is_primary AND status = ‘active’`)`
• Index (guardianUserId, status) drives “my household”; (subjectUserId, status) drives “who may act for this member”.
• subjectUserId <> guardianUserId, enforced in the service.

attestationText is stored verbatim, not as a template-version pointer. iClassPro voids every policy acceptance when a guardian’s name is edited — renaming a person destroys their signed consents. Storing the accepted string on the grant makes that class of bug impossible.

Suppression is not revocation. חוק זכויות החולה ס’16א is direct precedent: where a parent is charged with or convicted of an offence against their child, information shall not be given to that parent. Revocation is visible and self-explanatory, and in a safeguarding context that disclosure is itself the harm. So suppression is enforced in GuardianAccessService only, is invisible to the suppressed adult (their UI shows an ordinary grant and absent data, never “hidden from you”), is visible to staff with reason and evidence, and is never inferred from custody — a non-custodial parent retains access by default.

3.3 Why guardianship is not a membership

The review raised this directly: “guardianship is same level as membership. it’s actually a scoped/restricted membership.”

Half right, and the half that is right is worth acting on. A membership relates a person to an organization. A guardianship relates a person to another person. Different arity, so they cannot be one table: a guardian of three children in one org would need three rows, which unique(userId, organizationId) forbids.

But the intuition that they are the same tier of concept is correct, and it has a concrete consequence: authority must resolve through one service regardless of where it comes from. Role-derived authority (membership) and relationship-derived authority (guardianship) both terminate in a single question:

can(actorUserId, subjectUserId, capability, orgId): Promise<Decision>

That is the unification to build. Today authority is checked three inconsistent ways — PermissionsService.enforce, isStaffRole(), and ~45 raw inline role comparisons. Guardianship adds a fourth axis, and adding it as a fourth idiom is how this becomes unreviewable. One resolver, one decorator (WP-04).

3.4 memberships — one additive column

+ participation participationKind NOT NULL DEFAULT 'trains'   -- 'trains' | 'guardian_only'

That is the entire change. userId stays NOT NULL, unique(userId, organizationId) stays.

participation exists because an adult who only manages a child still needs a membership row to be billed, contacted and audited in the org — without inflating the member counts that drive analytics and platform billing. Every roster count, MRR figure, capacity report and platform-billing seat count must filter participation = 'trains'. Missing one is the most likely silent bug in this ticket. There is precedent for the hazard: SwimTopia instructs customers to create a teen’s volunteer login as a parent-type record specifically “so the teen doesn’t inflate the billable athlete roster count”.

3.5 forms — signer separate from subject

form_instances:
+ signerRequirement    signerRequirement NOT NULL DEFAULT 'self'  -- 'self' | 'guardian' | 'either'
+ healthArtifactKind   healthArtifactKind NULL
+ validFrom            timestamptz NULL
+ expiresAt            timestamptz NULL
+ consentScope         consentScope NULL
+ consentTiming        consentTiming NOT NULL DEFAULT 'prior'

form_signatures:
+ signedByUserId       uuid NOT NULL FK → users.id
+ signedInCapacity     signingCapacity NOT NULL   -- 'self' | 'guardian' | 'staff_witnessed'
+ signerRelationship   guardianRelationship NULL
+ signerFullName       text NOT NULL              -- typed by the signer at signing time
+ attestationText      text NOT NULL

assigneeUserId is unchanged — NOT NULL, pointing at the member the form is about, which for a child is the child’s own row.

This deliberately reopens a door the team deliberately closed. forms.service.ts:3478-3482 rejects any signer who is not the assignee, and the comment at forms.controller.ts:562-566 records why: a bare token let anyone sign as the member, “including a minor consenting on their own behalf.” The replacement is not a relaxation — it replaces “the token is valid” with “authority is proven”:

• A managed member’s form carries signerRequirement = 'guardian'. There is no login to self-sign with, and self-signing by a minor is never permitted at any age.
• Accepted iff (self and caller is the assignee) or (guardian and an active, unsuppressed guardianship with canSignForms exists at submit time, not at render time).
• Backfill every historical signature as signedInCapacity = 'self' — truthfully, because that is what it was.

Health artefact expiry is statutory, not a product setting. A health declaration refreshes every 24 months, or 12 where a medical certificate was triggered; a competing athlete needs a seasonal questionnaire and an annual physical. member_profiles.medicalCert is a bare boolean (member-profiles.ts:43) and cannot carry an expiry date. The forms gate in bookings.service.book() (bookings.service.ts:103-108) must consider expiresAt, not just status = 'signed'.

3.6 payments — separate the payer

subscriptions:
+ payerUserId          uuid NULL FK → users.id   -- NULL = the member pays for themselves
+ invoiceName          text NULL                 -- tax-invoice name when it differs from the payer's

member_payment_methods:
+ ownerUserId          uuid NULL FK → users.id
+ fallbackLiableUserId uuid NULL FK → users.id   -- third-party payer default

membershipId stays the entitlement holder; payerUserId is the liable party. One stored card funds every child’s subscription without duplicating the token per membership.

invoiceName is Q-15, confirmed. Arbox already ships it and an accountant will ask.

fallbackLiableUserId comes from SafeSplash’s third-party payer agreement, where liability reverts to the original enrolling customer if the third party defaults. Without it, a grandparent’s failed card leaves the operator with a debt and nobody to pursue.

3.7 bookings, audit_events

bookings:
+ bookedByUserId     uuid NULL FK → users.id
+ cancelledByUserId  uuid NULL FK → users.id

audit_events:
+ subjectUserId      uuid NULL FK → users.id
+ actorCapacity      actorCapacity NOT NULL DEFAULT 'self'   -- 'self' | 'guardian' | 'staff' | 'system'
~ auditCategory enum: + 'guardianship'

Booking subject remains membershipId, so entitlement, capacity and duplicate-prevention stay per member and two siblings are two seats. The actor columns answer “who did this”, which is the question that settles every co-parent dispute. Left NULL for history — we genuinely do not know, and backfilling = membership.userId would fabricate an audit record.

audit_events gains an index on (organizationId, subjectUserId, createdAt). Without it the log cannot answer “show me everything that happened to this child”, which is the first thing a complaining parent and a regulator both ask for. Guardian access is also a statutory right of the child, exercised by the adult (חוק הגנת הפרטיות ס’13(א)) — so the log must record “guardian X exercised child Y’s access right”, not “guardian X viewed their own data”.

3.8 Not changed

Stated explicitly so nobody re-adds them: memberships.userId, memberships unique constraint, member_profiles, form_instances.assigneeUserId, legal_consents.userId (capacity column only), users.nameEmbedding and member search, attachments, conversations, notification_prefs.

Messaging is out of phase 0 entirely (review decision on Q-05), so conversations.subjectUserId moves to a later phase. Notification routing is still required — that is fan-out, not messaging.

3.9 Granting a login later — the graduation path

A managed member without a Clerk identity cannot authenticate, and for the common case — a young child — that is the intended security property rather than an oversight: there is no credential to steal, no session to hijack and no password-reset flow to phish. Fizikal’s documented account-takeover across “dozens of large Israeli gyms” was exactly credential-based compromise of member accounts, and a member who cannot log in cannot be compromised that way. But note the axes in §2: managed does not imply credential-less. A managed member may hold a login, and keep their guardianship.

It is also not a dead end, and this is where revision 2 is better than revision 1 rather than merely cheaper.

Granting a login is an UPDATE to the same row:

UPDATE users SET clerk_id = $1, email = $2, kind = 'account' WHERE id = $3;

Same users.id. Every booking, attendance record, signed waiver, body metric, subscription and audit row already points at it and keeps pointing at it. Nothing migrates.

The mechanism already exists and is production-proven. UsersService.findOrCreateFromClerk (apps/api/src/users/users.service.ts:99-128) does precisely this today for imported members: it finds a users row with clerkId IS NULL, attaches the Clerk identity in place, preserves the row id, and backfills name from Clerk only where the import left it null. We are reusing a path that already runs on every imported member’s first sign-in.

Compare revision 1: a participants row plus a newly created users row means two identity rows per human, permanently, with historical activity hanging off one and the login off the other. Revision 2 has one row that gains a credential.

This is also the industry shape, not an invention — pattern P2 in research.md. Google mints a child account with a synthetic non-deliverable identifier and documents that it can later be “upgraded” to a real Gmail and password without changing the account. Nintendo requires a dependent to register an email before supervised status can be removed: a contact channel as the precondition of independence.

Three things the simple story glosses over

1. Email cannot be the match key, because it may be absent. findOrCreateFromClerk links by eq(users.email, email) AND clerkId IS NULL (users.service.ts:100-102). A managed member may well have an email — but may equally have none, and the link path cannot depend on one. Claiming therefore needs a token that names the row: the adult issues an invitation, the token carries the users.id, and the Clerk identity attaches to that row specifically. Email matching must not be the mechanism even when an email happens to exist.

2. The email may already belong to someone else. If the member’s address already exists on a different users row — they hold their own account at another gym — findOrCreateFromClerk would find that row first, attach the Clerk identity to it, and leave the managed row orphaned with all the member’s history stranded on it. The token must take precedence over email matching, and a genuine collision is a merge, not an update. Recorded as R-20.

3. Getting a login does not end guardianship. The two are separate acts on separate columns: attaching clerkId grants authentication; revoking the guardianships rows ends authority. A member can hold both at once, which is exactly case 3 in §2 and what makes the graduation overlap possible — the young member books for themselves while the adult retains payer and emergency-contact rights. Whoever builds the claim flow must not bundle the two, and kind should flip to 'account' only when the last guardianship is revoked, not when a login is attached.

None of these is an architectural problem; all three are requirements on whichever package builds the claim flow. That flow is out of phase 0 by review decision, so they are specified here rather than built now — but users.email’s partial unique index lands in phase 0, so the collision in point 2 is representable from day one and must not be discovered later.

4. New enums

libs/db/src/lib/schema/enums.ts, following the existing style.

emergencyContactRelationship on users/invitations stays free-text varchar(100) (users.ts:35-37). It is a different concept from guardianRelationship and the two must not be conflated.

5. Migration sequence

Planned as six migrations, down from eleven; shipped as one, libs/db/drizzle/0168_managed_members.sql, squashed before merge. Drizzle generate → review SQL → migrate. Never db:push. No step leaves the running application broken. The file runs in the migrator’s single transaction: the only ALTER TYPE … ADD VALUE statements (audit_category + guardianship, guardianship_source + staff_enrolled) add values no later statement in the file uses.

In order, as the file runs:

The adult-member invariant

TKN-382 requires “safe migration of existing adult memberships.” Revision 2 makes this nearly trivial, because no existing row is re-keyed and no existing column changes meaning. The relaxation is users.email becoming nullable; the only existing rows it touches are empty-string emails, which become NULL.

How it was proven in phase 1. The dedicated adult-migration-parity.int.spec.ts planned here was not written. What stands in its place: the strict-null type-check gate (scripts/strict-null-gate.sh, no new ! or ?? ''), every existing API and web suite passing on the migrated schema (unit, integration, e2e), drizzle-kit generate reporting no drift, and a fresh database migrated through 0168 producing a schema identical to the test database’s.

6. The one real risk, and its safety net

Every code path that reads users.email must now handle null. Email sends, Clerk sync, exports, the importer’s matching logic, lead conversion, receipts.

The mitigation is the same mechanism revision 1 relied on, and it is cheaper here because one column changes instead of five tables: making email nullable in the Drizzle schema regenerates the row type as email: string | null, so npx nx build api enumerates every unsafe read as a compile error. The sweep is mechanical. WP-02 is gated on a clean type-check plus the parity spec, not on reviewer diligence.

Two rules for working that list:

• Never silence a null with ! or ?? ''. An empty-string email is a silent delivery failure or, worse, a cross-member leak if it is used as a matching key.
• A managed member with no email is a legitimate terminal state for a send, not an error. Skip and record; do not throw. Notification fan-out to guardians is the correct path (WP-07).

7. Shared types

libs/shared/src/lib/schemas/guardianship.schema.ts, barrel-exported from libs/shared/src/index.ts, following conversation.schema.ts: as const value tuples with derived types, entity schemas whose nullability mirrors the DB exactly, .extend() for detail-vs-list variants, *Input/*Response naming, .refine() for cross-field invariants.

GuardianCapabilities is the single TypeScript source for the thirteen capability booleans — the API resolver, the web UI and the tests all import it. It must not be re-declared anywhere.

8. What the swim-school case still needs and this does not provide

Recorded so the plan does not look more complete than it is.