Forms — Data Model

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

forms — template

Constraints:

• forms_org_type_version_uq — unique (organization_id, type_key, version).
• forms_kind_payload_chk — (kind='compliance' AND recurrence IS NULL) OR (kind='check_in' AND validity_period_days IS NULL).

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.

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).
• Cascade on org delete: org → forms + form_instances + form_signatures all cascade. This is dangerous for compliance retention — deleting an org wipes the legal evidence. See gap below.

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. If a user’s account is deleted, form_instances.assignee_user_id becomes dangling (no onDelete clause), and the PDF in R2 still contains their national ID and signature. Israeli “right to erasure” requests would need a bespoke wipe path. Tracked in README gaps.

Soft vs. hard delete

• Templates — soft via archived_at. Existing instances pinned to a now-archived template still resolve correctly (template row is never deleted).
• Instances — soft via archived_at. Status transition to archived is a valid terminal state.
• Signatures — never deleted by application code. Hard delete only happens if the org cascade fires (org row delete → CASCADE all the way down). This is the legal-retention risk mentioned above.
• No application-level retention enforcement. Bucket-level lifecycle (Cloudflare R2 object lock + 7-year retention policy) is in the FIT-158 backlog but not yet provisioned.

Versioning model

• Each (organization_id, type_key) is a chain of forms rows: v1, v2, v3, …. All rows persist; old versions never delete.
• 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: