Forms (Form Builder)

Overview

This domain has 4 tables: form_builder stores form definitions (questions, theme, thank-you page) as JSONB, form_submission stores the answers users submit, linked to the form through form_hash, form_builder_rule stores rules applied to form fields, and otp_config stores per-LINE-OA OTP verification settings (SMS via ThaiBulkSMS, and email)

Table `form_builder`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	`nextval(...)`	Primary key
`title`	`TEXT`	NO	-	Form title
`description`	`TEXT`	YES	-	Form description
`questions`	`JSONB`	NO	-	Full question definition of the form (array of fields)
`theme`	`JSONB`	YES	-	Theme / appearance settings for the form
`thank_you`	`JSONB`	YES	-	Thank-you page settings shown after submission
`status`	`FormBuilderStatus` (ENUM)	NO	`draft`	Form status: `active`, `inactive`, `draft`, `delete`
`start_date`	`TIMESTAMPTZ(3)`	YES	-	When the form starts accepting answers
`end_date`	`TIMESTAMPTZ(3)`	YES	-	When the form stops accepting answers
`responses_count`	`INTEGER`	NO	`0`	Number of responses received (counter)
`form_hash`	`VARCHAR(50)`	NO	-	Public form reference code (unique), used in the form URL
`line_oa_id`	`INTEGER`	YES	-	LINE OA the form belongs to (FK to `line_oa.id`)
`convert_to_member`	`BOOLEAN`	YES	-	Whether to convert the respondent to a member after submission
`require_line_login`	`BOOLEAN`	NO	`true`	Whether LINE login is required before filling in the form
`one_time_submission`	`BOOLEAN`	YES	-	Whether a user may submit only once
`field_attribute_mappings`	`JSONB`	YES	`'[]'`	Mapping from form fields to user attributes
`created_by`	`INTEGER`	YES	-	Creator (user id)
`created_at`	`TIMESTAMPTZ(3)`	NO	`now()`	Creation timestamp
`updated_by`	`INTEGER`	YES	-	Last updater (user id)
`updated_at`	`TIMESTAMPTZ(3)`	NO	`now()`	Last update timestamp

Table `form_submission`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	`nextval(...)`	Primary key
`form_hash`	`VARCHAR(50)`	NO	-	Form the answers were submitted to (FK to `form_builder.form_hash`)
`submitted_date`	`TIMESTAMPTZ(3)`	YES	-	When the form was submitted
`line_user_id`	`TEXT`	YES	-	LINE user id of the respondent (when logged in via LINE)
`ip_address`	`TEXT`	YES	-	Respondent IP address
`user_agent`	`TEXT`	YES	-	Respondent browser user agent
`answer`	`JSONB`	NO	-	All answers for the form
`metadata`	`JSONB`	YES	-	Additional submission metadata
`is_submitted`	`BOOLEAN`	NO	`false`	Whether the submission is complete (separates drafts from real answers)

Table `form_builder_rule`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	`nextval(...)`	Primary key
`type`	`VARCHAR(50)`	NO	-	Rule type
`name`	`VARCHAR(100)`	NO	-	Rule name
`properties`	`JSONB`	NO	`'{}'`	Rule parameters
`status`	`CommonStatus` (ENUM)	NO	`active`	Rule status
`created_date`	`TIMESTAMPTZ(3)`	NO	`now()`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp

Table `otp_config`

Column	Type	Nullable	Default	Description
`id`	`UUID`	NO	`gen_random_uuid()`	Primary key
`line_oa_id`	`BIGINT`	NO	-	LINE OA using this configuration (unique — one config per OA)
`organization_id`	`BIGINT`	NO	-	Organization that owns the configuration
`thaibulksms_key`	`TEXT`	YES	-	ThaiBulkSMS API key for sending OTP over SMS
`thaibulksms_secret_enc`	`TEXT`	YES	-	ThaiBulkSMS API secret (stored encrypted)
`sms_enabled`	`BOOLEAN`	NO	`false`	Whether OTP delivery over SMS is enabled
`email_enabled`	`BOOLEAN`	NO	`false`	Whether OTP delivery over email is enabled
`email_subject`	`TEXT`	YES	-	OTP email subject line
`email_body`	`TEXT`	YES	-	OTP email body (template)
`status`	`TEXT`	NO	`'active'`	Configuration status
`created_date`	`TIMESTAMPTZ(3)`	NO	`now()`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp

Notes

form_submission.form_hash is a FK to form_builder.form_hash (not to id) with ON DELETE CASCADE — deleting a form deletes all of its submissions
form_builder.form_hash has a unique constraint plus a separate index, because it is the key used when opening a form from its public link
form_builder.line_oa_id is a FK to line_oa.id and is nullable (forms not yet bound to an OA)
Updating form_submission.is_submitted from false to true fires the notify_form_submitted() function, which emits pg_notify on channel form_submitted and inserts into event_outbox (see pg_notify and Event Outbox)
otp_config.line_oa_id has a unique constraint — a LINE OA can have only one OTP configuration
form_builder_rule has no direct FK to form_builder — rules are referenced from inside the JSONB of form_builder.questions

Overview​

Table form_builder​

Table form_submission​

Table form_builder_rule​

Table otp_config​

Notes​