forms

name	forms
description	Form definitions, fields, submissions, and file uploads with RLS

Forms

Configurable forms with ordered fields, submission tracking, and file uploads -- all scoped by organization with row-level security. Depends on the identity skill for organizations and users.

Tables

form_definitions

Column	Type	Description
id	uuid	Primary key
org_id	uuid	FK to organizations
creator_id	uuid	FK to users. The user who created the form. NULL if creator was deleted
name	text	Human-readable form name
slug	text	URL-safe identifier, unique per org
description	text	Optional description shown to respondents
status	form_status	Lifecycle state: draft, active, or archived
submit_message	text	Confirmation message shown after submission
redirect_url	text	Optional URL to redirect to after submission
created_at	timestamptz	Row creation timestamp
updated_at	timestamptz	Last update timestamp (auto-set by trigger)
metadata	jsonb	Arbitrary JSON for form-level settings

Unique constraint on (org_id, slug).

form_fields

Column	Type	Description
id	uuid	Primary key
org_id	uuid	FK to organizations
form_id	uuid	FK to form_definitions
label	text	Display label shown to the respondent
field_key	text	Machine-readable key stored in submission data JSON
field_type	form_field_type	Input type: text, email, number, select, multiselect, checkbox, textarea, date, or file
position	integer	Display order within the form. Lower values appear first
is_required	boolean	Whether the field must be filled before submission
options	jsonb	Option objects for select/multiselect fields
validation	jsonb	Validation rules, e.g. {"min_length": 3, "max_length": 500}
created_at	timestamptz	Row creation timestamp
updated_at	timestamptz	Last update timestamp (auto-set by trigger)
metadata	jsonb	Arbitrary JSON

Unique constraint on (form_id, field_key).

form_submissions

Column	Type	Description
id	uuid	Primary key
org_id	uuid	FK to organizations
form_id	uuid	FK to form_definitions
contact_id	uuid	Optional reference to a CRM contact (not enforced as FK)
data	jsonb	JSON object mapping field_key to submitted value
submitted_at	timestamptz	When the respondent submitted the form
ip_address	inet	IP address of the respondent
user_agent	text	Browser user agent string
created_at	timestamptz	Row creation timestamp
updated_at	timestamptz	Last update timestamp (auto-set by trigger)
metadata	jsonb	Arbitrary JSON

form_uploads

Column	Type	Description
id	uuid	Primary key
org_id	uuid	FK to organizations
submission_id	uuid	FK to form_submissions
field_id	uuid	FK to form_fields. NULL if originating field was deleted
file_name	text	Original file name
file_size	bigint	File size in bytes
mime_type	text	MIME type of the uploaded file
storage_path	text	Path within Supabase Storage
created_at	timestamptz	Row creation timestamp
updated_at	timestamptz	Last update timestamp (auto-set by trigger)
metadata	jsonb	Arbitrary JSON

Enums

form_status

Value	Description
draft	Not yet accepting submissions
active	Accepting submissions
archived	No longer accepting submissions, kept for reference

form_field_type

Value	Description
text	Single-line text input
email	Email address input
number	Numeric input
select	Single-choice dropdown
multiselect	Multi-choice dropdown
checkbox	Boolean checkbox
textarea	Multi-line text input
date	Date picker
file	File upload

Row-Level Security

All tables have RLS enabled and are scoped to the current user's organization via get_user_org_id().

• form_definitions: SELECT/INSERT/UPDATE for own org. DELETE requires admin.
• form_fields: SELECT/INSERT/UPDATE for own org. DELETE requires admin.
• form_submissions: SELECT/INSERT/UPDATE for own org. DELETE requires admin.
• form_uploads: SELECT/INSERT/UPDATE for own org. DELETE requires admin.

Dependencies

• identity -- organizations, users

Example Queries

List all active forms in the current organization:

SELECT fd.id, fd.name, fd.slug, fd.status,
       count(fs.id) AS submission_count
FROM form_definitions fd
LEFT JOIN form_submissions fs ON fs.form_id = fd.id
WHERE fd.org_id = get_user_org_id()
  AND fd.status = 'active'
GROUP BY fd.id
ORDER BY fd.name;

Get a form with its fields in display order:

SELECT fd.name AS form_name,
       ff.label, ff.field_key, ff.field_type, ff.is_required, ff.position
FROM form_definitions fd
JOIN form_fields ff ON ff.form_id = fd.id
WHERE fd.slug = 'contact-us'
  AND fd.org_id = get_user_org_id()
ORDER BY ff.position;

Retrieve submissions for a form with submitted values:

SELECT fs.id, fs.submitted_at, fs.data, fs.ip_address
FROM form_submissions fs
WHERE fs.form_id = '<form_id>'
  AND fs.org_id = get_user_org_id()
ORDER BY fs.submitted_at DESC
LIMIT 50;

Count submissions per form grouped by day:

SELECT fd.name,
       date_trunc('day', fs.submitted_at) AS day,
       count(*) AS submissions
FROM form_submissions fs
JOIN form_definitions fd ON fd.id = fs.form_id
WHERE fs.org_id = get_user_org_id()
GROUP BY fd.name, day
ORDER BY day DESC;

List all uploads for a given submission:

SELECT fu.file_name, fu.file_size, fu.mime_type, fu.storage_path,
       ff.label AS field_label
FROM form_uploads fu
LEFT JOIN form_fields ff ON ff.id = fu.field_id
WHERE fu.submission_id = '<submission_id>'
  AND fu.org_id = get_user_org_id()
ORDER BY fu.created_at;

Calculate NPS score from survey submissions:

SELECT
  count(*) FILTER (WHERE (data->>'score')::int >= 9)  AS promoters,
  count(*) FILTER (WHERE (data->>'score')::int BETWEEN 7 AND 8) AS passives,
  count(*) FILTER (WHERE (data->>'score')::int <= 6)  AS detractors,
  round(
    100.0 * (
      count(*) FILTER (WHERE (data->>'score')::int >= 9)
      - count(*) FILTER (WHERE (data->>'score')::int <= 6)
    )  ((), ),
    
  )  nps
 form_submissions
 form_id  
   org_id  get_user_org_id();