Forms — Data Model

Three tables. All defined in libs/db/src/lib/schema/forms.ts.

forms — template

Constraints:

• forms_org_type_version_uq — partial unique (organization_id, type_key, version) WHERE plan_id IS NULL.
• forms_org_type_plan_version_uq — partial unique (organization_id, type_key, plan_id, version) WHERE plan_id IS NOT NULL — each plan owns its own versioned family of the same typeKey.
• forms_kind_payload_chk — (kind='compliance' AND recurrence IS NULL) OR (kind='check_in' AND validity_period_days IS NULL).
• forms_plan_link_chk — plan link is compliance-only; plan_regulations must carry plan_id; required_for_booking only on org-wide compliance rows.

Indexes:

• forms_org_kind_type_idx on (organization_id, kind, type_key) — auto-issue lookup hot path.
• forms_active_check_in_idx partial on (organization_id, kind) WHERE archived_at IS NULL AND published_at IS NOT NULL — recurrence scheduler.
• forms_plan_gate_idx partial on (organization_id, plan_id) WHERE plan_id IS NOT NULL — purchase-gate lookup.
• forms_booking_gate_idx partial on (organization_id) WHERE required_for_booking AND archived_at IS NULL AND published_at IS NOT NULL — booking-gate lookup.

form_instances — issuance to a user

Constraints:

• form_instances_kind_status_chk — compliance ∈ {draft,pending,signed,archived}; check-in ∈ {scheduled,sent,answered,reviewed}.
• form_instances_token_kind_chk — signing_token IS NULL OR kind='compliance'.

Indexes:

• form_instances_assignee_status_idx on (assignee_user_id, status) — member “my open forms” + coach “pending for this member”.
• form_instances_org_kind_status_idx on (organization_id, kind, status) — coach review queue.
• form_instances_assignee_kind_answered_idx partial on (assignee_user_id, kind, answered_at) WHERE answered_at IS NOT NULL — trend charts.
• form_instances_scheduled_for_idx partial on (scheduled_for) WHERE status='scheduled' — recurrence batch.
• form_instances_signing_token_uq partial unique on (signing_token) WHERE NOT NULL — public token lookup.

form_signatures — append-only legal artefact

1:1 with a signed instance row. No UPDATE path exists in the codebase. Carries the immutable evidence.

Indexes:

• form_signatures_org_signed_idx on (organization_id, signed_at) — compliance reporting.

Relationships

organizations 1 ─── * forms
                  └── * form_instances
                  └── * form_signatures

forms 1 ─── * form_instances

form_instances 1 ─── 0..1 form_signatures   (UNIQUE form_instance_id)

users 1 ─── * forms (createdById)
        └── * form_instances (assigneeUserId, assignedByUserId)

Multi-org isolation

• All three tables carry organization_id. Service layer queries always include WHERE organization_id = $1.
• getTemplateScoped(orgId, formId) (forms.service.ts:1018) is the canonical “load this template if it belongs to my org” helper. 404 on cross-org access.
• Public token routes bypass the org gate but the token is globally unique (form_instances_signing_token_uq), so cross-org leakage is impossible without leaking the token itself.
• Cross-org typeKey collisions are permitted by design (every gym’s “health_declaration” v1 is its own row).
• Org delete: forms and form_instances cascade; form_signatures restricts. See Org deletion & retention.

PII handling

The signed PDF contains the most sensitive PII in the system. By design, fields can include national ID (תעודת זהות), date of birth, phone, emergency contact details (see forms-presets.ts healthDeclaration).

Open issues:

• No field-level encryption. A DB compromise reveals every member’s national ID. Acceptable today because the threat model treats Postgres as trusted, but should be revisited.
• Signature image bucket lacks retention. The PNG is technically reproducible by reading the PDF, so deletion is safe — but no janitor exists yet.
• No redaction on user deletion. Deleting a user who has form instances is blocked (assignee_user_id / assigned_by_user_id are ON DELETE RESTRICT) — the row can’t dangle, but nothing redacts either: the PDF in R2 still contains their national ID and signature. Israeli “right to erasure” requests need a bespoke wipe path that unpicks instances, signatures and R2 objects before the user row can go. Tracked in README gaps.

Soft vs. hard delete

• Templates — soft via archived_at, applied to the whole version family at once (archiveTemplate). Existing instances pinned to a now-archived template still resolve correctly (the row survives archiving). A hard delete exists for the never-used case only: deleteTemplate erases the family’s rows and open instances, and 409s (form_has_submissions) the moment any completed submission or signature exists.
• Instances — soft via archived_at. Status transition to archived is a valid terminal state. Hard-deleted only as part of a family deleteTemplate, which the submission guard restricts to never-completed instances.
• Signatures — never deleted, by application code or by cascade. The org FK is RESTRICT, so there is no DB path that removes a signature row as a side effect of deleting something else. Removing one requires a deliberate, explicit DELETE FROM form_signatures.
• No application-level retention enforcement. The DB now guarantees the row survives; the PDF bytes in R2 have no equivalent guard. Bucket-level lifecycle (Cloudflare R2 object lock + 7-year retention policy) is in the FIT-158 backlog but not yet provisioned.

Org deletion & retention

form_signatures.organization_id is ON DELETE RESTRICT (FIT-280, migration 0098_keen_ozymandias). Signed forms are legal evidence — health declarations, parental consents, cancellation notices — and the Israeli statute-of-limitations posture puts the retention horizon at roughly 7 years, well past the end of the customer relationship. Losing them because someone deleted an org row is not an acceptable failure mode.

What this means concretely:

forms and form_instances deliberately keep their cascades. An instance with no signature is workflow state, not evidence, and with signatures restricting first, those cascades are only reachable for orgs that have nothing to retain.

Offboarding an org with signatures therefore requires exporting / archiving the evidence first — pull the signature rows and their R2 objects into whatever the retention store ends up being, delete the signature rows explicitly, and only then delete the org. Soft-delete (deactivating the org and leaving the rows in place) satisfies retention with no extra work and is the expected default. The operational runbook for the hard-delete path — where the export lands, who authorizes it, how the R2 objects are held for the remainder of the 7 years — is deferred, not written. Until it exists, treat a blocked org delete as correct behavior rather than a bug to route around.

Versioning model

• Each template family — (organization_id, type_key) for org-wide rows, (organization_id, type_key, plan_id) for plan-linked ones — is a chain of forms rows: v1, v2, v3, …. All rows persist; old versions never delete. Coverage, expiry re-issue and the purchase/booking gates all match instances by family, never by bare type_key (two plans’ plan_regulations must not cross-cover).
• An instance pins form_id AND form_version. The pin is redundant (the row already encodes its version), but the denormalization lets the audit page render “Signed v1 — current is v3” without joining back through (org, type_key).
• Version bump via bumpVersion inserts a row with publishedAt=now directly; there is no v(n+1) draft phase. If a coach wants to iterate before going live, they must currently archive v(n) and create from scratch (gap).
• auto_issue_on_join lives on the template row, not the chain head — when a coach bumps the version, the new row carries forward the flag value from the previous (see forms.service.ts:256-257).

Reading paths

Hot queries: