@erikguntner, I've been inspired by your suggestions in #672 and spent some time after work creating a first-draft SQL data model that can store and retrieve user-specified forms.

Let me know if you think it is missing anything critical. I'm planning to just hard-code the initial forms, and we can think about audit trails once we have a basic forms API working.

Show how to define a SQL Schema that supports user defined forms

CREATE TABLE FieldTypes (
    field_type_id INTEGER PRIMARY KEY,
    type_code VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    settings JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO FieldTypes (type_code, description, settings) VALUES 
('text', 'Single line text input', '{"max_length": 255}'),
('textarea', 'Multi-line text input', '{"max_length": 1000}'),
('integer', 'Integer input', '{"min_value": 0, "max_value": 10000}'),
('boolean', 'Checkbox for true/false values', '{}'),
('date', 'Date picker', '{"format": "yyyy-mm-dd"}'),
('email', 'Email address input', '{"pattern": "^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$"}'),
('dropdown', 'Dropdown selection', '{"options": ["Option1", "Option2"]}');

CREATE TABLE Forms (
    form_id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Fields (
    field_id VARCHAR(255) PRIMARY KEY,
    form_id INT NOT NULL,
    field_type_id INT NOT NULL,
    ref VARCHAR(255) NOT NULL,
    validation_rules JSON,
    description TEXT,
    FOREIGN KEY (form_id) REFERENCES Forms(form_id),
    FOREIGN KEY (field_type_id) REFERENCES FieldTypes(field_type_id)
);

CREATE TABLE Responses (
    response_id INTEGER PRIMARY KEY,
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(id)
);

CREATE TABLE Answers (
    answer_id INTEGER PRIMARY KEY,
    response_id INT NOT NULL,
    field_id VARCHAR(255) NOT NULL,
    answer_text TEXT, -- Generic text field that could store any type of answer as JSON or plain text
    FOREIGN KEY (response_id) REFERENCES Responses(response_id),
    FOREIGN KEY (field_id) REFERENCES Fields(field_id)
);

Show SQL Commands to define a new form

INSERT INTO Forms (title, description)
VALUES ('New Employee Survey', 'A form to gather information from new employees.');
INSERT INTO Fields (field_id, form_id, field_type_id, ref, description, validation_rules)
VALUES ('name', 1, (SELECT field_type_id FROM FieldTypes WHERE type_code = 'text'), 'full_name', 'Enter your full name', '{"max_length": 255}');
INSERT INTO Fields (field_id, form_id, field_type_id, ref, description, validation_rules)
VALUES ('age', 1, (SELECT field_type_id FROM FieldTypes WHERE type_code = 'integer'), 'age', 'Enter your age', '{"min_value": 18, "max_value": 65}');
INSERT INTO Fields (field_id, form_id, field_type_id, ref, description, validation_rules)
VALUES ('gdpr', 1, (SELECT field_type_id FROM FieldTypes WHERE type_code = 'boolean'), 'gdpr_consent', 'Do you consent to GDPR?', '{}');
INSERT INTO Fields (field_id, form_id, field_type_id, ref, description, validation_rules)
VALUES ('dob', 1, (SELECT field_type_id FROM FieldTypes WHERE type_code = 'date'), 'date_of_birth', 'Enter your date of birth', '{"format": "yyyy-mm-dd"}');
INSERT INTO Fields (field_id, form_id, field_type_id, ref, description, validation_rules)
VALUES ('email', 1, (SELECT field_type_id FROM FieldTypes WHERE type_code = 'email'), 'email_address', 'Enter your email address', '{"pattern": "^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$"}');
INSERT INTO Fields (field_id, form_id, field_type_id, ref, description, validation_rules)
VALUES ('department', 1, (SELECT field_type_id FROM FieldTypes WHERE type_code = 'dropdown'), 'department_selection', 'Select your department', '{"options": ["HR", "Tech", "Marketing"]}');

Show SQL Commands to Insert Responses

INSERT INTO Responses (user_id)
VALUES (20);
INSERT INTO Answers (response_id, field_id, answer_text)
VALUES (1, 'name', 'John Doe');
INSERT INTO Answers (response_id, field_id, answer_text)
VALUES (1, 'age', '30');
INSERT INTO Answers (response_id, field_id, answer_text)
VALUES (1, 'gdpr', 'true');
INSERT INTO Answers (response_id, field_id, answer_text)
VALUES (1, 'dob', '1989-07-23');
INSERT INTO Answers (response_id, field_id, answer_text)
VALUES (1, 'email', 'john.doe@example.com');
INSERT INTO Answers (response_id, field_id, answer_text)
VALUES (1, 'department', 'Tech');

How do you get the available fields for a form?

SELECT 
    f.title AS Form_Title,
    f.description AS Form_Description,
    fld.field_id AS Field_ID,
    ft.type_code AS Field_Type,
    fld.description AS Field_Description,
    fld.validation_rules AS Validation_Rules
FROM Forms f
JOIN Fields fld ON f.form_id = fld.form_id
JOIN FieldTypes ft ON fld.field_type_id = ft.field_type_id
WHERE f.form_id = 1
ORDER BY fld.field_id;

This returns

How do you get the responses for a form?

SELECT
    r.response_id AS Response_ID,
    u.email AS user,  -- Assuming your user table has a username field
    f.title AS Form_Title,
    fld.ref AS Field_Reference,
    fld.description AS Question,
    ft.type_code AS Field_Type,
    a.answer_text AS Answer
FROM Answers a
JOIN Responses r ON a.response_id = r.response_id
JOIN user u ON r.user_id = u.id 
JOIN Fields fld ON a.field_id = fld.field_id
JOIN FieldTypes ft ON fld.field_type_id = ft.field_type_id
JOIN Forms f ON fld.form_id = f.form_id
WHERE f.form_id = 1
ORDER BY r.response_id, fld.field_id;

How would Frontend Requests a Form?

The backend would responds with this form structure. If any answers are available for the user then they would be included.

{
  "form_id": 1,
  "title": "New Employee Survey",
  "description": "A form to gather information from new employees.",
  "fields": [
    {
      "field_id": "name",
      "type": "text",
      "ref": "full_name",
      "description": "Enter your full name",
      "validation_rules": {
        "max_length": 255
      }
    },
    {
      "field_id": "age",
      "type": "integer",
      "ref": "age",
      "description": "Enter your age",
      "validation_rules": {
        "min_value": 18,
        "max_value": 65
      }
    },
    {
      "field_id": "gdpr",
      "type": "boolean",
      "ref": "gdpr_consent",
      "description": "Do you consent to GDPR?",
      "validation_rules": {}
    },
    {
      "field_id": "dob",
      "type": "date",
      "ref": "date_of_birth",
      "description": "Enter your date of birth",
      "validation_rules": {
        "format": "yyyy-mm-dd"
      }
    },
    {
      "field_id": "email",
      "type": "email",
      "ref": "email_address",
      "description": "Enter your email address",
      "validation_rules": {
        "pattern": "^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$"
      }
    },
    {
      "field_id": "department",
      "type": "dropdown",
      "ref": "department_selection",
      "description": "Select your department",
      "validation_rules": {
        "options": ["HR", "Tech", "Marketing"]
      }
    }
  ],
  "answers": [
    {
      "field_id": "dob",
      "type": "date",
      "answer": "1989-07-23"
    },
  ]
}

Can frontend only request answers?

Yes, we don't need to send the full form structure each time.

{
  "response_id": 1,
  "user_id": 1,  // Assuming you're tracking which user submits the form
  "answers": [
    {
      "field_id": "name",
      "type": "text",
      "answer": "John Doe"
    },
    {
      "field_id": "age",
      "type": "integer",
      "answer": "30"
    },
    {
      "field_id": "gdpr",
      "type": "boolean",
      "answer": true
    },
    {
      "field_id": "dob",
      "type": "date",
      "answer": "1990-07-23"
    },
    {
      "field_id": "email",
      "type": "email",
      "answer": "john.doe@example.com"
    },
    {
      "field_id": "department",
      "type": "dropdown",
      "answer": "Tech"
    }
  ]
}

@Joshua-Douglas this is looking good! Two suggestions for what I think should be added:

1. A field groups/sections table. We can think of this as a way to group fields into sections such as 'Employment Information', 'Contact Information', etc.
2. A field properties table. This is a table that defines optional properties that can be used to configure each field type. For example, the options property for dropdown fields you have in validation rules. They can be used to help validate the response, however, I view them more as something passed to the component to define the choices a user can pick from.

I wonder if we need two tables for Answers and Response? Maybe these could be combined into one table?

Adding the data model I used for the front end here for reference and to provide a bit more context for each table.

erDiagram
    INTAKE_PROFILE ||--|{ FIELD_GROUP : contains
    INTAKE_PROFILE {
        string id PK
        string name
    }
    TYPE {
        string id PK
        string type "long_text, short_text, multiple_choice, yes_no, email, phone_number"
    }
    FIELD_GROUP ||--|{ FIELD : has
    FIELD_GROUP {
        string id PK
        string profile_id FK
        int order
        string title
    }
    FIELD ||--|| PROPERTIES : has
    FIELD ||--|| VALIDATIONS : has
    FIELD ||--|| TYPE : has
    FIELD ||--|| ANSWER : has
    FIELD {
        string id PK
        string field_group_id FK
        string type_id FK
        int order
        string title
    }
    PROPERTIES ||--|{ CHOICES : "can contain"
    PROPERTIES {
        string id PK
        string field_id FK
        string description "all"
        boolean randomize "multiple_choice, dropdown"
        boolean alphabetical_order "multiple_choice, dropdown"
        boolean allow_multiple_selection "multiple_choice"
        boolean allow_other_choice "multiple_choice"
    }
    VALIDATIONS {
        string id PK
        string field_id FK
        boolean is_required "all"
        int max_characters "short_text, long_text"
    }
    CHOICES {
        string id PK
        string properties_id FK
        string label
     }
     
    ANSWER {
	    string id PK
	    string field_id FK
            int user_id FK
	    jsonb value "unsure of the best way to define this type since it needs to cover a many different data types (e.g)"
    }

• INTAKE_PROFILE: The root for each profile. This would define an id for Guest or Host profile structures.
• FIELD_GROUPS: This table groups together fields into the different sections of the profile. For example, Contact Information, Employment Information, etc.
• FIELDS: Defines each field along with the properties that are common amongst all fields.
• FIELD_TYPE: A list of all the different field types linked to an id
• PROPERTIES: A list of properties that are unique to certain fields. We can think of these as properties that can be passed to components to help configure them. These can all be nullable and need to be expanded on.
• CHOICES: The choices for either a dropdown or multiple choice question.
• VALIDATIONS: A list of different validation requirements for each field type. These can all be nullable and need to be expanded on.
• ANSWER: Store an answer linked to a specific field and user. Currently, this table uses jsonb as a catch-all for the responses, but I believe we could get more specific with properties like "text", "boolean", etc. based on the value of the response we are storing.

@Joshua-Douglas this is looking good! Two suggestions for what I think should be added:

1. A field groups/sections table. We can think of this as a way to group fields into sections such as 'Employment Information', 'Contact Information', etc.
2. A field properties table. This is a table that defines optional properties that can be used to configure each field type. For example, the options property for dropdown fields you have in validation rules. They can be used to help validate the response, however, I view them more as something passed to the component to define the choices a user can pick from.

I wonder if we need two tables for Answers and Response? Maybe these could be combined into one table?

Thank @erikguntner for the suggestions. I implemented them on my branch for this issue.

1. The field groups are stored in a 'groups' table. This approach doesn't support nesting, but this is the approach taken by the TypeForm API, so I think it'll be good enough for our purposes.
2. I see your point about the difference between field properties and validations after looking at the TypeForm API more carefully. I broke out the properties and validations into their own tables. To start the field properties table will contain the field description and a choices JSON array that can be used to store multiple choice data. The field validation table will contain whether the field is required and a max length validation for text fields.
3. Good catch. Combined responses and answers into a single table.

Sounds good @Joshua-Douglas. I'm looking forward to seeing what you come up with. Could you please create a ticket that covers the scope of what you are working on in your branch?

The field groups are stored in a 'groups' table. This approach doesn't support nesting, but this is the approach taken by the TypeForm API, so I think it'll be good enough for our purposes.

Does this mean all fields come back as a flat array with each field having a group id? Typeform primarily does one field per page which is why you don't see a lot of nesting, but it does support the creation of field groups. This is what a "group" of fields looks like returned from the Typeform API:

		{
			"id": "jwTAYzpSS0mr",
			"title": "...",
			"ref": "a1e7b448-e682-4894-ba83-8fb278ce87a0",
			"properties": {
				"fields": [
					{
						"id": "Zj6RVeq1uAC7",
						"title": "First name",
						"ref": "1d6a7f94-79a1-4bae-adfb-ed2cbb64dce0",
						"subfield_key": "first_name",
						"properties": {},
						"validations": {
							"required": false
						},
						"type": "short_text"
					},
					{
						"id": "uXlq9bunkAuo",
						"title": "Last name",
						"ref": "9cdc7ba0-aa04-4adf-ac34-e24c0ebe5d3d",
						"subfield_key": "last_name",
						"properties": {},
						"validations": {
							"required": false
						},
						"type": "short_text"
					},
					{
						"id": "ALW3YSyGjF1b",
						"title": "Phone number",
						"ref": "2f45b1c6-105c-499b-a28c-90c54c41c84f",
						"subfield_key": "phone_number",
						"properties": {
							"default_country_code": "US"
						},
						"validations": {
							"required": false
						},
						"type": "phone_number"
					},
					{
						"id": "xSYRVV7rfMiD",
						"title": "Email",
						"ref": "540f05b5-e34e-4112-953f-da5bf59b2b55",
						"subfield_key": "email",
						"properties": {},
						"validations": {
							"required": false
						},
						"type": "email"
					},
					{
						"id": "UQiSCgtnmw9C",
						"title": "Company",
						"ref": "7199c2a3-17cd-43ba-b54a-5a50c120a523",
						"subfield_key": "company",
						"properties": {},
						"validations": {
							"required": false
						},
						"type": "short_text"
					}
				]
			},
			"type": "contact_info"
		},

Our pages are primarily groups of fields so it would be great if we could figure out a way to return them in a nested structure, but if not, we can do some transformations to achieve it.

Hey @erikguntner,

Does this mean all fields come back as a flat array with each field having a group id?

No, the form response currently comes back with each field placed in the correct group, similar to Typeform. The only difference is that I'm using field_groups as a custom attribute instead of having a field with type group. This is easier to implement, but means that every field has to be within a group.

By "lack of nesting" I mean that you can't put a field group within a field group. This restriction is also in place on the TypeForm API. Here is a snippet from their Create Form docs, describing the restriction for the field array:

Contains the fields that belong in a question group. Only payment and group blocks are not allowed inside a question group. Available for the group, matrix type.

Here is the json returned by the get_form endpoint on my branch. Responses are submitted and returned using the unique "field_id".

 {
   "title":"Employee Onboarding",
   "description":"Collect necessary employee data.",
   "field_groups":[
      {
         "title":"Personal Details",
         "description":"Please enter your personal details.",
         "fields":[
            {
               "field_id":1,
               "ref":"position",
               "properties":{
                  "description":"Position in the company",
                  "field_type":"dropdown",
                  "choices":[
                     "Manager",
                     "Developer",
                     "Designer"
                  ]
               },
               "validations":{
                  "required":true,
                  "max_length":12
               }
            },
            {
               "field_id":2,
               "ref":"service_length",
               "properties":{
                  "description":"Years in the company",
                  "field_type":"number",
                  "choices":"None"
               },
               "validations":{
                  "required":false,
                  "max_length":"None"
               }
            }
         ]
      },
      {
         "title":"Second Group",
         "description":"A second field group.",
         "fields":[
            {
               "field_id":3,
               "ref":"start date",
               "properties":{
                  "description":"Start date",
                  "field_type":"date",
                  "choices": None
               },
               "validations":{
                  "required": true,
                  "max_length": None
               }
            }
         ]
      }
   ],
   "created_at":"2024-05-06T05:08:02"
}

The responses look like this:

{
  {
    "field_id": 1,
    "answer_text": "Manager"
  },
  {
    "field_id": 2,
    "answer_text": "12"
  },
  {
    "field_id": 3,
    "answer_text": "11-22-2005"
  }
}

        },

@Joshua-Douglas I see. Thanks for clarifying.

Related to completed work #668 but still outstanding -- need to assess whether this ticket should be split up, existing scope appears to cover too broad a scope for estimation of remaining points

@Joshua-Douglas any updates on this?