Legal — Data Model

Two tables. Defined in libs/db/src/lib/schema/legal.ts.

`legal_documents`

Column	Type	Notes
`id`	`uuid` PK
`type`	`legal_document_type` NOT NULL	`terms_of_use`, `privacy_policy`, `fitness_waiver`, `cookie_policy`, `acceptable_use`, `member_terms`, `external_payment_acknowledgement`
`version`	`varchar(20)` NOT NULL	Semver-ish string, e.g. `'2.0.0'`. Compared by `published_at` not by parsing this field
`locale`	`varchar(5)` NOT NULL	`he`, `en`, `ru`
`url`	`text` NOT NULL	Canonical URL on marketing site
`published_at`	`timestamptz` NOT NULL	Source of truth for “latest” ordering
`created_at`	`timestamptz` NOT NULL DEFAULT now

Unique: (type, version, locale).

`legal_consents`

Column	Type	Notes
`id`	`uuid` PK
`user_id`	`uuid` NOT NULL	FK `users(id)` (no ON DELETE — see PII)
`organization_id`	`uuid`	FK `organizations(id)` nullable — required acceptance scope for external-payment acknowledgement; audit context for other types
`document_id`	`uuid` NOT NULL	FK `legal_documents(id)` — immutable evidence of the exact version and locale accepted
`consent_context`	`consent_context` NOT NULL	`owner_onboarding`, `member_onboarding`, `reconsent`, `profile_update`
`ip_address`	`varchar(45)`	IPv6-capable
`user_agent`	`text`	Full UA string
`consented_at`	`timestamptz` NOT NULL DEFAULT now
`revoked_at`	`timestamptz`	NULL = active. No UI today

Index: (user_id, document_id).

Relationships


legal_documents 1 ─── * legal_consents
users 1 ─── * legal_consents
organizations 1 ─── * legal_consents   (external acknowledgement scope; otherwise optional audit context)

Multi-org isolation

Documents are a platform-wide versioned catalog. Privacy, health acknowledgement, platform Terms, and member terms acceptance is platform-level; organization_id records where it happened. external_payment_acknowledgement acceptance is instead scoped to an organization: active evidence and dedup must match (user_id, document_id, organization_id), and acceptance requires a target organization. Evidence from one gym never satisfies another gym’s external-payment acknowledgement. The existing (user_id, document_id) index is non-unique; append-only version evidence remains possible.

The required set is derived from the selected organization role, not stored on a document. Members require privacy + Fitness & Health Acknowledgement (fitness_waiver) + current member_terms; owners require only terms_of_use; admins and coaches require nothing until staff-specific terms are introduced. Member join flows additionally require external acknowledgement for a server-resolved plan payment-link URL or active external payment provider. Pending-only member onboarding may require it for an active external provider, but active-member backstop requirements never include it. Existing members accept only missing current types through /accept-terms; already-current evidence remains valid. Staff creation and invitation never synthesize member consent.

UsersService.getLegalRequirements(userId, organizationId?) returns { types, organizationId }. Selection matches the web client: valid explicit organization first, then membership role priority; stale/omitted selections fall back. Requirements use that one selected role rather than combining roles across organizations. GET /legal/consents/status?organizationId=… preserves the per-type response array and adds isRequired and the resolved organizationId. Missing required acceptance is exactly isRequired && requiresReconsent; clients must not reconstruct it from roles or flags.

The displayed title Fitness & Health Acknowledgement does not rename the existing fitness_waiver type or slug. Its terms preserve mandatory, non-waivable rights.

Localized publication

Every document used by owner or member onboarding must have a current row in each supported locale (he, en, ru). recordConsent fails closed when any requested type is absent in the submitted locale; it never returns after a partial legal write.

0125_needy_zuras.sql publishes member_terms and external_payment_acknowledgement version 1.0.0 in all three locales while transactionally expanding the enum. 0127_legal_document_locales.sql adds the missing English and Russian publications for the existing Terms of Use, Privacy Policy, and Fitness & Health Acknowledgement versions.

The latter migration also repairs the specific partial evidence created before the fail-closed check: a member_onboarding member-terms row proves the unified checkbox was accepted because the join API required both acceptedLegal and acceptedMemberTerms before recording it. The migration copies that row’s scope, IP, user agent, and timestamp into only the absent current privacy and fitness evidence. It does not infer consent from membership or create evidence for users without that explicit member-terms acceptance.

Hebrew canonical URLs have no /he prefix. Publication does not enable a rollout flag or synthesize acceptance outside the narrowly repairable partial write above.

Migration safety

0125_needy_zuras.sql replaces the legal-document enum inside one transaction because PostgreSQL cannot consume a newly added enum value until commit. Existing document IDs and consent foreign keys are preserved. The operation takes an ACCESS EXCLUSIVE lock and rewrites legal_documents; review and schedule that lock before applying.

0127_legal_document_locales.sql is insert-only and idempotent: document rows use the (type, version, locale) conflict key, and repaired consent rows use an active current-version NOT EXISTS guard.

PII handling

ip_address and user_agent are plaintext audit evidence.
No special encryption.
revoked_at exists for soft-revocation. No “right to be forgotten” cascade — the foreign key prevents deleting a referenced user until consent records are handled explicitly.

Soft vs hard delete

Documents: never deleted by app code.
Consents: soft-revocable via revoked_at. Active rows have revoked_at IS NULL — every read filters on this.