Send answers data to a PSQL database you run, or a hosted database like RDS.
Authentication
To allow Formsort to write to PostgreSQL, create a new user with insert permissions to the schema that contains your data table. You can then add the credentials for the user directly into Formsort.
Do not reuse a user with more permissions than are necessary to INSERT rows - Formsort does not even need SELECT access, and should not have any administrative permissions. Note: do grantUSAGE right on the sequence underpinning the primary key. Example using formsort role:
1
GRANTINSERTONTABLE answer_set TO formsort;
2
GRANTUSAGEON SEQUENCE answer_set_id_seq TO formsort;
Copied!
Frequency
The frequency of inserting a row to PostgreSQL can be configured as:
On Finalize: only at the end of the flow.
On Savepoint: after each step marked as save point, and at the end of the flow.
Every step: at the end of each step (when the responder advances using the Next button), and at the end of the flow.
Debounced: when the responder abandons the flow after a period of inactivity, and at the end of the flow. Formsort recommends using this setting to reduce the load.
Security
Formsort backend system will exclusively connect to Postgres from the static IP 18.217.92.196.
Schema
Because the Formsort user should not have administrative permissions, you must create it yourself, using a SQL command.
Create a table named answer_set with the following command:
1
CREATETABLE answer_set (
2
id SERIALPRIMARYKEY,
3
submitted_at timestampwithtime zone NOTNULL,
4
responder_uuid uuid NOTNULL,
5
flow_label charactervarying,
6
variant_label charactervarying,
7
variant_revision_uuid uuid,
8
event_type charactervarying,
9
answers jsonb,
10
schema_version smallint
11
);
12
CREATEUNIQUEINDEX answer_set_set_id_idx ON answer_set(id);