Form Dispatch System Tables

Migration: 20260503230951_forms.sql. Six tables (form_types, form_definitions, form_definition_screens, form_definition_fields, form_submissions, entity_associations) plus three enums (employment_type, form_submission_status, entity_association_type) and five permissions (form:send, form:resend, form:fill_on_behalf, form:view_submission, entity_association:edit — see Permissions).

The form definition is fully data-driven: each form_definition is a tree of form_definition_screens and form_definition_fields, both supporting a condition JSONB rule (eq / neq / and / or / any) so screens and fields can be conditionally shown based on earlier answers. Rendering, validation, and WhatsApp Flow generation all read from this schema.

Form Types (form_types)

Purpose: Catalogue of high-level form categories (identity, bank details, tax, etc.). A form_definition belongs to exactly one form_type.

Column	Type	Description
id	UUID	Primary key (auto-generated)
type_key	TEXT	Stable machine-readable key for this form type (UNIQUE)
label	TEXT	Human-readable label shown in the UI
description	TEXT	Optional description shown in the UI
is_active	BOOLEAN	Whether this form type is currently available. Default TRUE
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp (update_form_types_updated_at trigger)

Seeded type_key values: identity, emergency_contact, proof_of_work, bank_details, tax_form, starter_form, address.

RLS: Enabled. SELECT for authenticated users only (Anyone authenticated can view form types). No INSERT/UPDATE/DELETE policies — definition data is managed via migrations, not the app.

Indexes: PK on id; UNIQUE on type_key.

Form Definitions (form_definitions)

Purpose: Registry of available form definitions. Each row maps to a code-side FormLogic implementation (matched by form_key). Form metadata (label, description, country, notification template) lives here; the screens/fields tree is in the next two tables.

Column	Type	Description
id	UUID	Primary key
form_key	TEXT	Stable machine-readable key (UNIQUE) matching the FormLogic registry key
form_type_id	UUID	FK to form_types(id) ON DELETE RESTRICT
notification_template_id	UUID	FK to notification_templates(id) ON DELETE SET NULL — drives email_available/whatsapp_available flags in FormPicker
label	TEXT	Human-readable label shown in FormPicker
description	TEXT	Optional description shown in FormPicker
country_code	TEXT	FK to countries(code) ON DELETE SET NULL. NULL = universal (no jurisdiction restriction)
version	INTEGER	Schema version — increment when breaking changes are made to screens/fields. Default 1
disabled_when_active	TEXT[]	Array of form_key values that become unselectable in FormPicker when this form is selected. Default '{}'
audiences	TEXT[]	Relationship audiences this form applies to ('crew', 'cast', 'supplier', 'customer', 'all'). Mirrors ProjectRelationshipType + ALL sentinel. Empty array = universal (legacy default); 'all' = explicit universal. CHECK-constrained. Default '{}'.
display_order	INTEGER	Ascending sort order in FormPicker. Default 99
is_active	BOOLEAN	Whether this definition is available to send. Default TRUE
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Seeded form_key values: form_starter_uk (UK Starter Form, GB), form_starter_uk_loan_out (UK Loan-Out Starter Form, GB — UK loan-out crew use a trimmed flow because bank/tax/company details live on the supplier relationship), form_starter_in_full (India Starter Form, IN). Each starter form is wired to the form_fill notification template.

disabled_when_active example: form_starter_uk lists ['form_starter_uk_loan_out'] and vice versa, so a producer can pick one or the other but not both within the same dispatch.

RLS: Enabled. SELECT for authenticated users only.

Indexes: idx_form_definitions_form_type_id, idx_form_definitions_notification_template_id, idx_form_definitions_country_code, idx_form_definitions_is_active, idx_form_definitions_display_order, idx_form_definitions_audiences (GIN — array overlap queries).

Form Definition Screens (form_definition_screens)

Purpose: Ordered list of wizard screens within a form definition. A condition JSONB rule can gate a screen so it only renders when prior field values match.

Column	Type	Description
id	UUID	Primary key
form_definition_id	UUID	FK to form_definitions(id) ON DELETE CASCADE
screen_key	TEXT	Stable key for this screen within the form (UNIQUE per form_definition_id)
title	TEXT	Screen heading shown to the user
description	TEXT	Optional subtitle/instructions shown below the heading
display_order	INTEGER	Ascending sort order for screen sequence. Default 0
condition	JSONB	Display condition tree. Supported ops: eq, neq, and, or, any. NULL = always shown
is_active	BOOLEAN	Default TRUE
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

condition shape examples:

{ "op": "eq", "field": "employment_type", "value": "loan_out" }
{ "op": "any", "conditions": [
  { "op": "eq", "field": "employment_type", "value": "employee" },
  { "op": "eq", "field": "employment_type", "value": "self_employed" }
]}
{ "op": "and", "conditions": [
  { "op": "neq", "field": "nationality", "value": "GB" },
  { "op": "neq", "field": "nationality", "value": "IE" }
]}

RLS: Enabled. SELECT for authenticated users only.

Indexes: idx_form_definition_screens_form_definition_id, UNIQUE (form_definition_id, screen_key).

Form Definition Fields (form_definition_fields)

Purpose: Ordered list of input fields within each screen. Stores field type, label, required flag, select/radio options, optional condition, and (for field_type = 'html') read-only HTML content.

Column	Type	Description
id	UUID	Primary key
form_definition_id	UUID	Denormalised FK to form_definitions(id) ON DELETE CASCADE — supports efficient bulk queries without joining through screens
form_definition_screen_id	UUID	FK to form_definition_screens(id) ON DELETE CASCADE
field_key	TEXT	Stable key (UNIQUE per form_definition_screen_id)
label	TEXT	Field label shown to the user
field_type	TEXT	One of: text, select, date, file, checkbox, radio, textarea, phone, email, address, country, signature, currency, html
is_required	BOOLEAN	Whether the field must be filled before advancing. Default TRUE
placeholder	TEXT	Placeholder text for text-like inputs
help_text	TEXT	Additional guidance shown below the field
html_content	TEXT	Sanitised HTML body rendered when field_type = 'html' (read-only blocks like declarations or instructions). DOMPurified client-side
options	JSONB	For select/radio: [{"value":"x","label":"y","description":"z"?}]
display_order	INTEGER	Ascending sort within the screen. Default 0
condition	JSONB	Same shape as form_definition_screens.condition. NULL = always shown
is_active	BOOLEAN	Default TRUE
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

RLS: Enabled. SELECT for authenticated users only.

Indexes: idx_form_definition_fields_definition_id, idx_form_definition_fields_screen_id, UNIQUE (form_definition_screen_id, field_key).

Form Submissions (form_submissions)

Purpose: Each row represents one instance of a form being sent to and (optionally) filled by a project participant. Tracks lifecycle, delivery channel, dispatch errors, and the rendered submission payload.

Column	Type	Description
id	UUID	Primary key
organisation_id	UUID	FK to organisations(id) ON DELETE CASCADE — denormalised for RLS efficiency
project_id	UUID	FK to projects(id) ON DELETE CASCADE
project_relationship_id	UUID	FK to project_relationships(id) ON DELETE CASCADE — the (person + role) recipient
form_definition_id	UUID	FK to form_definitions(id) ON DELETE RESTRICT
status	form_submission_status	requested → in_progress → completed; or superseded / cancelled. Default requested
data	JSONB	Raw JSON payload completed by the user. Nullable
completed_at	TIMESTAMPTZ	When the form was completed by the recipient
delivered_at	TIMESTAMPTZ	When the form was delivered (WhatsApp / email sent)
delivery_channel	channel_type	Channel used to deliver: whatsapp, email, in_app. Nullable
delivery_error	TEXT	Error message if delivery failed
requested_by_user_id	UUID	FK to users(id) ON DELETE SET NULL — user who triggered the dispatch
completed_by_user_id	UUID	FK to users(id) ON DELETE SET NULL — user who submitted/completed (may differ from recipient if filled on behalf)
superseded_by_id	UUID	FK to form_submissions(id) ON DELETE SET NULL — replacement submission when superseded by a resend
promotion_status	form_submission_promotion_status	Async promotion state enum: pending → running → applied. NULL = legacy/not-applicable (below)
promotion_claimed_at	TIMESTAMPTZ	When the current promoter claimed the promotion (pending→running); used to reclaim a stale running lease. Nullable
created_by_user_id	UUID	FK to users(id) (public.users) ON DELETE SET NULL. DEFAULT auth.uid()
updated_by_user_id	UUID	FK to users(id) (public.users) ON DELETE SET NULL. DEFAULT auth.uid()
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

RLS:

• SELECT — form:view_submission permission on the project AND the sensitivity gate row_is_visible_to_caller('form_submissions', …). The permission check is the primary gate (submissions carry raw KYC data — directors' addresses, bank details); the sensitivity gate is defence in depth, cascading from any sensitive_rules on the row or its parent project_relationships. Both predicates must pass. (Restored in 20260705211557 — an earlier refactor had dropped the permission predicate, leaving reads gated on project access alone.)
• INSERT — form:send permission on the project
• UPDATE — form:send OR form:resend permission on the project (USING + WITH CHECK)
• DELETE — no policy (deletes blocked at the table level; submissions are superseded, not deleted)

Indexes: every FK column is indexed (organisation_id, project_id, project_relationship_id, form_definition_id, requested_by_user_id, completed_by_user_id, superseded_by_id, created_by_user_id, updated_by_user_id) plus idx_form_submissions_status for filtering by lifecycle state.

Promotion tracking (promotion_status / promotion_claimed_at): the final-submit "promotion" step (folding form data into entities / project_relationships / attachments) is asynchronous and retryable. promotion_status records its lifecycle — pending (final transition done, promotion not yet run) → running (a promoter has claimed it, promotion_claimed_at set) → applied (promotion succeeded). A promoter claims via a conditional pending → running update, or reclaims a stale running lease; failures reset the claim to pending for retry, and only full success marks applied. NULL means legacy / not-applicable — submissions completed before this change were promoted synchronously and are left NULL, and a non-final submission has no promotion yet (there is no backfill).

Token-capability / attachment flow: form-fill uploads by an account-less recipient are authorised by the notification-action-token capability, and each upload is durably linked to its submission via attachments.form_submission_id (server-managed, guarded — see ATTACHMENTS.md). Token lockdown + hash-at-rest is in NOTIFICATIONS.md; the guard/race trigger functions are in VIEWS_AND_FUNCTIONS.md.

Entity Associations (entity_associations)

Purpose: Links between entities (e.g. a person is employee_of a company, or director_of a company). Used during form dispatch and payroll to look up loan-out companies and billing authorities. Bound to an organisation for tenancy.

Column	Type	Description
id	UUID	Primary key
organisation_id	UUID	FK to organisations(id) ON DELETE CASCADE
entity_id	UUID	FK to entities(id) ON DELETE CASCADE — the primary entity (person or company)
associated_entity_id	UUID	FK to entities(id) ON DELETE CASCADE — the entity being associated (e.g. the employer)
association_type	entity_association_type	billing_authority, employee_of, director_of, contractor_of, other
metadata	JSONB	Additional structured data (e.g. start/end dates). Nullable
is_active	BOOLEAN	Soft-delete / historical flag. Default TRUE
created_by_user_id	UUID	FK to users(id) (public.users) ON DELETE SET NULL. DEFAULT auth.uid()
updated_by_user_id	UUID	FK to users(id) (public.users) ON DELETE SET NULL. DEFAULT auth.uid()
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Constraint: entity_associations_no_self_link CHECK (entity_id <> associated_entity_id).

RLS:

• SELECT — any authenticated user whose auth.uid() has access to organisation_id (via v_user_accessible_organisations)
• INSERT — entity_association:edit permission on any project in the organisation
• UPDATE — entity_association:edit permission on any project in the organisation (USING + WITH CHECK)
• DELETE — no policy (associations are deactivated via is_active, not deleted)

Indexes: idx_entity_associations_organisation_id, idx_entity_associations_entity_id, idx_entity_associations_associated_entity_id, idx_entity_associations_type, idx_entity_associations_created_by, idx_entity_associations_updated_by.

Loan-Out Validity Trigger

validate_loan_out_relationship() is a BEFORE INSERT OR UPDATE OF loan_out_via_project_relationship_id trigger on project_relationships. When loan_out_via_project_relationship_id is non-NULL, it asserts that the referenced relationship:

1. Belongs to the same project (pr.project_id = NEW.project_id)
2. References a Business entity (e.type = 'Business')

The function is SECURITY DEFINER with search_path = ''. The trigger is named trg_validate_loan_out_relationship.

Notification Template (forms migration)

The migration seeds a new forms notification template category and a form_fill notification template (email + WhatsApp). The form_fill WhatsApp template name is form_fill_v1 and its body variables are ${name}, ${formLabel}, ${formPlural}, ${businessName}, ${projectName}, ${external_action_path}. Every form_definition row is wired to this template via notification_template_id.

Migration Cross-References

• entity_attachment_type and project_relationship_attachment_type enums got two new values (tax_document, bank_details) so starter-form file uploads (P45, RTW, etc.) attach correctly
• entities.bank_overseas_code was added (briefly) and then dropped along with the rest of the flat bank_* columns by 20260505231911_add_payment_details_to_entities_and_relationships.sql — its content lives at payment_details->'bank'->>'overseas_code'
• project_relationships.employment_type and project_relationships.loan_out_via_project_relationship_id are new columns on the existing table (see Project Relationships)
• notification_action_tokens.created_by_user_id got an FK to users(id) and an index (the column existed previously, just unindexed)
• countries.flag and countries.nationality were added and backfilled for ~190 ISO codes