form_submissions

This table stores completed or draft instances of forms filled out by users. Each row represents one submission event for a specific template and project.

Domain: Workflow Engine / Forms

Scope: Tenant / Project

Status: Proposed

Last Updated: 11 Apr 2026

Mutable: Draft yes, submitted no

Primary Owner: Forms Service / Core API

RLS Applies: Yes

Client Facing: Yes

Purpose

The purpose of this table is to persist the user-submitted data for a form, including the template version used, the project context, and the submitted payload.

What this table does

Stores one completed or draft form instance
Preserves the template snapshot used at submission time
Supports immutable submitted records
Acts as the main source for form history, PDF generation, and notifications

Why this table is defined

DirtView needs submissions to remain valid even when templates evolve later. That means submissions must store their own payload and template version context, independent from future template edits.

Columns

Column	Type	Required	Description	Example
`id`	uuid	Yes	Primary key for the submission	`fsub_001`
`tenant_id`	uuid	Yes	Tenant owning the submission	`tenant_001`
`project_id`	uuid	Yes	Project context	`project_phoenix`
`template_id`	uuid	Yes	Source template	`ftpl_001`
`template_version`	int	Yes	Template version used	`1`
`status`	text	Yes	Draft/submitted/final/etc.	`submitted`
`data`	jsonb	Yes	Submitted field data payload	`{"crew":["e1","e2"],"hours":8}`
`schema_snapshot`	jsonb	Yes	Template schema snapshot at submission time	`{"fields":[...]}`
`submitted_by`	uuid	No	User who created/submitted the form	`user_123`
`submitted_at`	timestamptz	No	Timestamp of submission	`2026-04-11 13:00:00+00`
`distribution_group_id`	uuid	No	Resolved distribution group for notifications	`dgroup_015`
`pdf_file_id`	uuid	No	Generated branded PDF output	`file_501`
`metadata`	jsonb	Yes	Additional submission metadata	`{"device":"mobile"}`
`created_at`	timestamptz	Yes	Creation timestamp	`2026-04-11 12:40:00+00`
`updated_at`	timestamptz	Yes	Last update timestamp	`2026-04-11 13:00:00+00`

Relationships

template_id → form_templates.id
project_id → projects.id
distribution_group_id → distribution_groups.id
pdf_file_id → files.id

How it is used

Rendered in form history and submission detail views
Used to generate branded PDFs
Used to trigger notifications and reports
Queried for search/filter/reporting across projects and users

Access and security

Submitted forms should usually be immutable
Drafts may be editable until explicitly submitted
Access must respect project membership and field-level visibility rules where applicable

Example scenarios

Scenario 1: Mobile Daily Log submission

A foreman completes and submits a Daily Log on mobile, storing all field data in one submission row.

Scenario 2: Draft saved

A partially completed form remains in draft until later submission.

Scenario 3: PDF export

After submission, a branded PDF is generated and linked back to the submission.

Notes and assumptions

Submitted records should remain valid even if the template changes later
Store snapshots deliberately to preserve historical meaning
Consider indexing (project_id, template_id, submitted_at)