Submissions

A submission is the record of a single act of data capture. Every submission references a specific version of a form template (not the template directly), so older records remain valid even when the schema changes.

Core data model

form_submissions:

Column	Purpose
form_template_id + version	The exact template version that was current at submission time.
geometry GEOMETRY(POINTZ, 4326)	Optional 2D or 3D location (z defaults to 0 for 2D submissions).
data JSONB	Form fields and values, validated against the version's schema_json.
submitted_by / submitted_at / submitted_from	User, timestamp, source (web / mobile / public).
client_submission_id	UUID generated on the device, guarantees idempotency for offline retries. Unique per org; cross-org collision returns 409.
device metadata	GPS accuracy, battery level, timestamp, preserved for the audit trail.
linked_feature_id	Set if auto-feature creation succeeded.
archived	Soft-delete flag.

form_submission_media:

Column	Purpose
submission_id	Back-reference.
field_name	Which form field this media came from (one submission can attach multiple photos grouped by field).
media_type	PHOTO / VIDEO / AUDIO / SCAN.
storage_ref	Object storage key.
thumbnail_ref	Optional thumbnail for fast UI rendering.

Submissions are stored with geometry, media, audit trail, and device metadata (battery, GPS accuracy, timestamp). Submissions can auto-create features in a linked layer (e.g. valve inspection form → new row in the defects feature class).

Why it matters

Submissions solve the field-to-office data flow. A field engineer logs a defect, takes a photo, submits offline, reconnects, and the data flows into the asset register automatically. The audit trail (who submitted, when, from where, with what device state) is preserved for compliance, and that matters as much as the data itself when a regulator asks "prove this".

The auto-feature-creation contract closes the loop: the submission doesn't sit in a queue waiting for someone to manually create a feature; it becomes a feature in the right layer, with a permanent back-link.

Daily users

• Field engineer / Inspector: captures submissions on mobile (online or offline), watches them appear as map pins after sync.
• Ops supervisor: submission inbox surfaces as pins on the map with a detail drawer.
• GIS analyst: browses submissions via list and map UI, sees the linked feature.

Schema validation

• Data is validated against the form version's schema_json using JSON Schema Draft 2020-12, the same dialect as @rjsf/core on the web, so validation is identical client- and server-side.
• Empty schema = anything goes (useful while a form is being designed).
• Validation errors return 400 with joined error messages.

Submissions browser (web)

• List view + on-map pins + detail drawer.
• Paginated, filterable by form template.
• Click a pin → drawer shows submission data, attached media, and linked feature (if created).

Geometry capture

• Optional location: [lon, lat] or [lon, lat, z] in SRID 4326.
• Storage column is GEOMETRY(POINTZ, 4326): 2D submissions are stored as 3D internally with z = 0.

Media attachment

• Two-step flow: create the submission, then upload media.
• Files stored in object storage under submissions/{submissionId}/{mediaId}.{ext}.
• 1-hour presigned download URLs, org-scoped.
• Thumbnails generated for fast UI rendering.

Idempotency on offline retries

• The mobile client generates client_submission_id (UUID) before sending.
• Same id resubmitted after a connection drop returns the existing row, no duplicate.
• Cross-org collision returns 409.

Linked-feature auto-creation

• If the form's template has targetFeatureClass set, the submission writes a new feature in that layer (best-effort, not atomic).
• Submission location becomes feature geometry.
• data is filtered through the version's feature_property_map so operational fields don't pollute the asset record.
• Mapped properties are validated against the feature class schema; validation failure logs at WARN and skips feature creation, but the submission still succeeds (capture is primary).
• On success, submission.linked_feature_id is set.

Audit trail

• Every submission is recorded in the immutable audit_log table covering all user actions.
• Device metadata (GPS accuracy, battery, timestamp) is preserved for compliance proof.

info

Mobile offline sync (including a Hive-based offline queue, Kafka-streamed sync, and last-write-wins conflict handling for concurrent feature edits) is in active development.

How it links to the others

• Submissions ← Forms. A submission is created by filling a published form. Always tied to a specific form version.
• Submissions → Map. Submission geometry becomes a pin; the linked feature appears in its layer once auto-creation succeeds.
• Submissions → Dashboards. Widgets bind to submissions: count by template, by status, aggregations of submission values.
• Submissions → Analysis. Locations and aggregated submission data are valid input geometries / values for spatial analysis (e.g. "heatmap all defect submissions by severity").
• Submissions ↔ Annotations. Annotation submissions are a specific (planned) form type; they write to a system-managed annotations table so survey-derived defects appear in the same flow as field-logged defects.