iperdomo/flow-db-schema.org

## flow-db-schema.org

      
    Raw
  

              flow-db-schema.org
            
          
    Using Flow database structure

Checking connection

\d+

  List of relations 
  Schema Name Type Owner Size Description
  public collection table lumen 8192 bytes 
  public collection_entity table lumen 8192 bytes 
  public dashboard table lumen 8192 bytes 
  public dashboard_visualisation table lumen 8192 bytes 
  public data_source table lumen 8192 bytes 
  public dataset table lumen 8192 bytes 
  public dataset_version table lumen 8192 bytes 
  public geography_columns view postgres 0 bytes 
  public geometry_columns view postgres 0 bytes 
  public invite table lumen 8192 bytes 
  public job_execution table lumen 8192 bytes 
  public ragtime_migrations table lumen 8192 bytes 
  public raster_columns view postgres 0 bytes 
  public raster_dataset table lumen 8192 bytes 
  public raster_overviews view postgres 0 bytes 
  public share table lumen 8192 bytes 
  public spatial_ref_sys table postgres 4616 kB 
  public visualisation table lumen 8192 bytes 

Replicating Flow DB structure

Sample JSON from Flow API
{
  "id": "562899168",
  "name": "My RQG Survey",
  "registrationFormId": "",
  "forms": [
    {
      "id": "562919156",
      "name": "Form 1",
      "questionGroups": [
        {
          "id": "572849152",
          "name": "General",
          "repeatable": true,
          "questions": [
            {
              "id": "562909151",
              "name": "Name",
              "type": "FREE_TEXT",
              "order": 1,
              "variableName": "name",
              "createdAt": "2020-05-04T05:24:00.933Z",
              "modifiedAt": "2020-05-04T05:24:17.613Z"
            },
            {
              "id": "562879159",
              "name": "Age",
              "type": "NUMBER",
              "order": 2,
              "variableName": "age",
              "createdAt": "2020-05-04T05:24:21.066Z",
              "modifiedAt": "2020-05-04T05:24:38.712Z"
            },
            {
              "id": "594369156",
              "name": "Literacy",
              "type": "OPTION",
              "order": 3,
              "variableName": "literacy",
              "createdAt": "2020-05-04T05:24:42.025Z",
              "modifiedAt": "2020-05-04T05:25:22.813Z"
            }
          ],
          "createdAt": "2020-05-04T05:23:37.232Z",
          "modifiedAt": "2020-05-04T05:23:59.219Z"
        }
      ],
      "createdAt": "2020-05-04T05:23:24.915Z",
      "modifiedAt": "2020-05-04T05:25:33.321Z",
      "formInstancesUrl": "https://api-auth0.akvotest.org/flow/orgs/uat1/form_instances?survey_id=562899168&form_id=562919156"
    }
  ],
  "createdAt": "2020-05-04T05:23:08.705Z",
  "modifiedAt": "2020-05-04T05:23:22.939Z",
  "dataPointsUrl": "https://api-auth0.akvotest.org/flow/orgs/uat1/data_points?survey_id=562899168"
}
jq -M '{id, name, registrationFormId, createdAt, modifiedAt}' survey.json
Survey

CREATE TABLE survey (
    id text PRIMARY KEY,
    name text NOT NULL,
    registration_form_id text,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now()
);

  CREATE TABLE

INSERT INTO survey(id, name, created_at, modified_at)
VALUES ('562899168', 'My RQG Survey', '2020-05-04T05:23:08.705Z', '2020-05-04T05:23:22.939Z')

  INSERT 0 1

SELECT * FROM survey ORDER BY id

  id name registration_form_id created_at modified_at
  562899168 My RQG Survey 2020-05-04 05:23:08.705+00 2020-05-04 05:23:22.939+00

Form

jq -M '.forms[] | keys' survey.json
jq -M '.forms[0] | {id, name, createdAt, modifiedAt}' survey.json
CREATE TABLE form (
    id text PRIMARY KEY,
    survey_id text NOT NULL REFERENCES survey(id),
    name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now()
);

  CREATE TABLE

\d form

  Table “public.form” 
  Column Type Collation Nullable Default
  id text not null 
  survey_id text not null 
  name text not null 
  created_at timestamp with time zone not null now()
  modified_at timestamp with time zone not null now()
  Indexes: 
  “form_pkey” PRIMARY KEY, btree (id) 
  Foreign-key constraints: 
  “form_survey_id_fkey” FOREIGN KEY (survey_id) REFERENCES survey(id) 

INSERT INTO form(id, survey_id, name, created_at, modified_at)
VALUES('562919156', '562899168', 'Form 1', '2020-05-04T05:23:24.915Z', '2020-05-04T05:25:33.321Z');

  INSERT 0 1

SELECT * FROM form ORDER BY id

  id survey_id name created_at modified_at
  562919156 562899168 Form 1 2020-05-04 05:23:24.915+00 2020-05-04 05:25:33.321+00

Question group

jq -M '.forms[0].questionGroups[] | keys' survey.json
CREATE TABLE question_group (
    id text PRIMARY KEY,
    form_id text NOT NULL REFERENCES form(id),
    name text NOT NULL,
    repeatable boolean NOT NULL DEFAULT FALSE,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now()
);

  CREATE TABLE

\d question_group

  Table “public.question_group” 
  Column Type Collation Nullable Default
  id text not null 
  form_id text not null 
  name text not null 
  repeatable boolean not null false
  created_at timestamp with time zone not null now()
  modified_at timestamp with time zone not null now()
  Indexes: 
  “question_group_pkey” PRIMARY KEY, btree (id) 
  Foreign-key constraints: 
  “question_group_form_id_fkey” FOREIGN KEY (form_id) REFERENCES form(id) 

jq -M '.forms[0].questionGroups[0] | {id, name, repeatable, createdAt, modifiedAt}' survey.json
INSERT INTO question_group(id, form_id, name, repeatable, created_at, modified_at)
VALUES('572849152', '562919156', 'General', true, '2020-05-04T05:23:37.232Z', '2020-05-04T05:23:59.219Z')

  INSERT 0 1

SELECT * FROM question_group ORDER BY id

  id form_id name repeatable created_at modified_at
  572849152 562919156 General t 2020-05-04 05:23:37.232+00 2020-05-04 05:23:59.219+00

Questions

jq -M '.forms[0].questionGroups[0].questions[0] | keys' survey.json
jq -M '.forms[0].questionGroups[0].questions[] | .type' survey.json
-- There are many more
CREATE TYPE question_type AS ENUM('FREE_TEXT', 'NUMBER', 'OPTION');

CREATE TABLE question (
    id text PRIMARY KEY,
    question_group_id text NOT NULL REFERENCES question_group(id),
    name text NOT NULL,
    "order" smallint NOT NULL DEFAULT 0,
    type question_type NOT NULL,
    variable_name text,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now()
);

  CREATE TYPE
  CREATE TABLE

\d question

  Table “public.question” 
  Column Type Collation Nullable Default
  id text not null 
  question_group_id text not null 
  name text not null 
  order smallint not null 0
  type question_type not null 
  variable_name text 
  created_at timestamp with time zone not null now()
  modified_at timestamp with time zone not null now()
  Indexes: 
  “question_pkey” PRIMARY KEY, btree (id) 
  Foreign-key constraints: 
  “question_question_group_id_fkey” FOREIGN KEY (question_group_id) REFERENCES question_group(id) 

jq -M '.forms[0].questionGroups[0].questions[]' survey.json


INSERT INTO question (id, question_group_id, name, "order", type, variable_name, created_at, modified_at)
VALUES ('562909151', '572849152', 'Name', 1, 'FREE_TEXT', 'name', '2020-05-04T05:24:00.933Z', '2020-05-04T05:24:17.613Z'),
       ('562879159', '572849152', 'Age', 2, 'NUMBER', 'age', '2020-05-04T05:24:21.066Z', '2020-05-04T05:24:38.712Z'),
       ('594369156', '572849152', 'Literacy', 3, 'OPTION', 'literacy', '2020-05-04T05:24:42.025Z', '2020-05-04T05:25:22.813Z');

  INSERT 0 3

SELECT * FROM question ORDER BY id

  id question_group_id name order type variable_name created_at modified_at
  562879159 572849152 Age 2 NUMBER age 2020-05-04 05:24:21.066+00 2020-05-04 05:24:38.712+00
  562909151 572849152 Name 1 FREE_TEXT name 2020-05-04 05:24:00.933+00 2020-05-04 05:24:17.613+00
  594369156 572849152 Literacy 3 OPTION literacy 2020-05-04 05:24:42.025+00 2020-05-04 05:25:22.813+00

Collected data

Datapoints

{
  "dataPoints": [
    {
      "id": "572859149",
      "identifier": "k5x6-rus4-s1k3",
      "displayName": "Juan",
      "latitude": null,
      "longitude": null,
      "createdAt": "2020-05-04T05:31:23.668Z",
      "modifiedAt": "2020-05-04T05:31:28.470Z"
    }
  ],
  "nextPageUrl": "https://api-auth0.akvotest.org/flow/orgs/uat1/data_points?survey_id=562899168&cursor=CjESK2oPc35ha3ZvZmxvdy11YXQxchgLEg5TdXJ2ZXllZExvY2FsZRiNxpSRAgwYACAA"
}
jq -M '.dataPoints[] | keys' data-points.json
CREATE TABLE datapoint (
    id text PRIMARY KEY,
    display_name text NOT NULL, -- always true?
    identifier text NOT NULL UNIQUE,
    location geometry(Point,4326), -- Use a geometry column instead of separate lat/long values
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now()
);

  CREATE TABLE

\d datapoint

  Table “public.datapoint” 
  Column Type Collation Nullable Default
  id text not null 
  display_name text not null 
  identifier text not null 
  location geometry(Point,4326) 
  created_at timestamp with time zone not null now()
  modified_at timestamp with time zone not null now()
  Indexes: 
  “datapoint_pkey” PRIMARY KEY, btree (id) 
  “datapoint_identifier_key” UNIQUE CONSTRAINT, btree (identifier) 

jq -M '.dataPoints[]' data-points.json
INSERT INTO datapoint(id, display_name, identifier, created_at, modified_at)
VALUES ('572859149', 'Juan', 'k5x6-rus4-s1k3', '2020-05-04T05:31:23.668Z', '2020-05-04T05:31:28.470Z')

  INSERT 0 1

SELECT * FROM datapoint

  id display_name identifier location created_at modified_at
  572859149 Juan k5x6-rus4-s1k3 2020-05-04 05:31:23.668+00 2020-05-04 05:31:28.47+00

Form instances

{
  "formInstances": [
    {
      "deviceIdentifier": "s7",
      "dataPointId": "572859149",
      "submissionDate": "2020-05-04T05:31:19Z",
      "modifiedAt": "2020-05-04T05:31:28.582Z",
      "id": "599259147",
      "responses": {
        "572849152": [
          {
            "562879159": 10,
            "562909151": "Juan",
            "594369156": [
              {
                "text": "Yes",
                "code": "1"
              }
            ]
          },
          {
            "562879159": 50,
            "562909151": "Pedro",
            "594369156": [
              {
                "text": "No",
                "code": "0"
              }
            ]
          },
          {
            "562879159": 25,
            "562909151": "Pablo",
            "594369156": [
              {
                "text": "Yes",
                "code": "1"
              }
            ]
          },
          {
            "562879159": 33,
            "562909151": "Maria",
            "594369156": [
              {
                "text": "Yes",
                "code": "1"
              }
            ]
          }
        ]
      },
      "identifier": "k5x6-rus4-s1k3",
      "displayName": "Juan",
      "formId": "562919156",
      "surveyalTime": 50,
      "submitter": "iperdomo",
      "createdAt": "2020-05-04T05:31:23.996Z"
    }
  ],
  "nextPageUrl": "https://api-auth0.akvotest.org/flow/orgs/uat1/form_instances?survey_id=562899168&form_id=562919156&cursor=CjESK2oPc35ha3ZvZmxvdy11YXQxchgLEg5TdXJ2ZXlJbnN0YW5jZRiL8N-dAgwYACAA"
}
jq -M '.formInstances[] | keys' form-instances.json


CREATE TABLE form_instance (
    id text PRIMARY KEY,
    form_id text NOT NULL REFERENCES form(id),
    datapoint_id text NOT NULL REFERENCES datapoint(id),
    device_identifier text NOT NULL,
    submitter text NOT NULL,
    submission_date timestamptz NOT NULL,
    surveyal_time numeric NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now()
);

  CREATE TABLE

jq -M '.formInstances[0] | del(.responses)' form-instances.json


INSERT INTO form_instance(id, form_id, datapoint_id, device_identifier, submission_date, submitter,
                          surveyal_time, created_at, modified_at)
VALUES('599259147', '562919156', '572859149', 's7', '2020-05-04T05:31:19Z', 'iperdomo', 50, '2020-05-04T05:31:23.996Z', '2020-05-04T05:31:28.582Z')

  INSERT 0 1

SELECT * FROM form_instance

  id form_id datapoint_id device_identifier submitter submission_date surveyal_time created_at modified_at
  599259147 562919156 572859149 s7 iperdomo 2020-05-04 05:31:19+00 50 2020-05-04 05:31:23.996+00 2020-05-04 05:31:28.582+00

Answers

jq -M '.formInstances[].responses' form-instances.json


CREATE TABLE answer (
    id text PRIMARY KEY DEFAULT gen_random_uuid(),
    form_instance_id text NOT NULL REFERENCES form_instance(id),
    question_id text NOT NULL REFERENCES question(id),
    iteration smallint NOT NULL DEFAULT 0,
    value_text text,
    value_number numeric,
    value_date timestamptz,
    value_object jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE(form_instance_id, question_id, iteration)
);

  CREATE TABLE

\d answer

  Table “public.answer” 
  Column Type Collation Nullable Default
  id text not null gen_random_uuid()
  form_instance_id text not null 
  question_id text not null 
  iteration smallint not null 0
  value_text text 
  value_number numeric 
  value_date timestamp with time zone 
  value_object jsonb 
  created_at timestamp with time zone not null now()
  modified_at timestamp with time zone not null now()
  Indexes: 
  “answer_pkey” PRIMARY KEY, btree (id) 
  “answer_form_instance_id_question_id_iteration_key” UNIQUE CONSTRAINT, btree (form_instance_id, question_id, iteration) 
  Foreign-key constraints: 
  “answer_form_instance_id_fkey” FOREIGN KEY (form_instance_id) REFERENCES form_instance(id) 
  “answer_question_id_fkey” FOREIGN KEY (question_id) REFERENCES question(id) 

jq -M '.formInstances[].responses["572849152"][]["562879159"]' form-instances.json
INSERT INTO answer(form_instance_id, question_id, iteration, value_number)
VALUES ('599259147', '562879159', 1, 10),
       ('599259147', '562879159', 2, 50),
       ('599259147', '562879159', 3, 25),
       ('599259147', '562879159', 4, 33)

  INSERT 0 4

SELECT * FROM answer

  id form_instance_id question_id iteration value_text value_number value_date value_object created_at modified_at
  b6e365fb-67da-4d58-8780-0a966f8840eb 599259147 562879159 1 10 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
  72d0d3fc-bd9e-4afd-b803-d4603ac08476 599259147 562879159 2 50 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
  b271b72c-95f5-4222-a572-844ec65e04f1 599259147 562879159 3 25 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
  424d4b3e-a93c-4391-8bde-c0bfaa8e00b0 599259147 562879159 4 33 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00

jq -M '.formInstances[].responses["572849152"][]["562909151"]' form-instances.json
INSERT INTO answer(form_instance_id, question_id, iteration, value_text)
VALUES ('599259147', '562909151', 1, 'Juan'),
       ('599259147', '562909151', 2, 'Pedro'),
       ('599259147', '562909151', 3, 'Pablo'),
       ('599259147', '562909151', 4, 'Maria')

  INSERT 0 4

SELECT * FROM answer ORDER by form_instance_id, iteration, question_id;

  id form_instance_id question_id iteration value_text value_number value_date value_object created_at modified_at
  b6e365fb-67da-4d58-8780-0a966f8840eb 599259147 562879159 1 10 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
  a29511f4-5537-43a6-9257-dfe609d72187 599259147 562909151 1 Juan 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00
  72d0d3fc-bd9e-4afd-b803-d4603ac08476 599259147 562879159 2 50 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
  0d623612-af71-4878-90c9-88dc51bf1990 599259147 562909151 2 Pedro 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00
  b271b72c-95f5-4222-a572-844ec65e04f1 599259147 562879159 3 25 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
  a6e7e75b-239d-4e7e-bc38-9c491bd2f909 599259147 562909151 3 Pablo 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00
  424d4b3e-a93c-4391-8bde-c0bfaa8e00b0 599259147 562879159 4 33 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
  e90ad3c7-eb36-4dd8-bf14-73a63594b7b1 599259147 562909151 4 Maria 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00

jq -c -M '.formInstances[].responses["572849152"][]["594369156"]' form-instances.json
INSERT INTO answer(form_instance_id, question_id, iteration, value_object)
VALUES ('599259147', '594369156', 1, '[{"text":"Yes","code":"1"}]'::jsonb),
       ('599259147', '594369156', 2, '[{"text":"No","code":"0"}]'::jsonb),
       ('599259147', '594369156', 3, '[{"text":"Yes","code":"1"}]'::jsonb),
       ('599259147', '594369156', 4, '[{"text":"Yes","code":"1"}]'::jsonb)

  INSERT 0 4

SELECT * FROM answer ORDER by form_instance_id, iteration, question_id;

  id form_instance_id question_id iteration value_text value_number value_date value_object created_at modified_at
  b6e365fb-67da-4d58-8780-0a966f8840eb 599259147 562879159 1 10 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
  a29511f4-5537-43a6-9257-dfe609d72187 599259147 562909151 1 Juan 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00
  ec46a7c4-f358-401a-86bb-fd7d88df0cdd 599259147 594369156 1 [{“code”: “1”, “text”: “Yes”}] 2020-05-06 12:29:00.705249+00 2020-05-06 12:29:00.705249+00
  72d0d3fc-bd9e-4afd-b803-d4603ac08476 599259147 562879159 2 50 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
  0d623612-af71-4878-90c9-88dc51bf1990 599259147 562909151 2 Pedro 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00
  6f234327-2df7-4e29-b049-7356b6ba1ed8 599259147 594369156 2 [{“code”: “0”, “text”: “No”}] 2020-05-06 12:29:00.705249+00 2020-05-06 12:29:00.705249+00
  b271b72c-95f5-4222-a572-844ec65e04f1 599259147 562879159 3 25 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
  a6e7e75b-239d-4e7e-bc38-9c491bd2f909 599259147 562909151 3 Pablo 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00
  2fe0f858-5a3b-4149-aaae-79e76407b44c 599259147 594369156 3 [{“code”: “1”, “text”: “Yes”}] 2020-05-06 12:29:00.705249+00 2020-05-06 12:29:00.705249+00
  424d4b3e-a93c-4391-8bde-c0bfaa8e00b0 599259147 562879159 4 33 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
  e90ad3c7-eb36-4dd8-bf14-73a63594b7b1 599259147 562909151 4 Maria 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00
  55d750b9-b90e-4be2-9d8d-7ed14654def6 599259147 594369156 4 [{“code”: “1”, “text”: “Yes”}] 2020-05-06 12:29:00.705249+00 2020-05-06 12:29:00.705249+00

Presenting the data as a Dataset

Main dataset

CREATE OR REPLACE VIEW form_metadata_562919156 AS
SELECT dp.identifier, dp.display_name, fi.device_identifier, fi.id as instance, fi.submitter,
       fi.surveyal_time, 1 as form_version, fi.submission_date -- we don't have form version yet
 FROM datapoint dp,
      form_instance fi
WHERE dp.id = fi.datapoint_id;

  CREATE VIEW

SELECT * FROM form_metadata_562919156

  identifier display_name device_identifier instance submitter surveyal_time form_version submission_date
  k5x6-rus4-s1k3 Juan s7 599259147 iperdomo 50 1 2020-05-04 05:31:19+00

Repeated group

CREATE OR REPLACE VIEW question_group_572849152 AS
SELECT q1.value_text as "562909151_name", q2.value_number as "562879159_age",
       concat(q3.value_object->0->>'code', ':', q3.value_object->0->>'text')  as "594369156_literacy",
       q1.form_instance_id
  FROM answer q1, answer q2, answer q3
 WHERE q1.question_id = '562909151'
   AND q2.question_id = '562879159'
   AND q3.question_id = '594369156'
   AND q1.form_instance_id = q2.form_instance_id
   AND q2.form_instance_id = q3.form_instance_id
   AND q1.iteration = q2.iteration
   AND q2.iteration = q3.iteration
ORDER BY q1.iteration

  CREATE VIEW

SELECT * FROM question_group_572849152

  562909151_name 562879159_age 594369156_literacy form_instance_id
  Juan 10 1:Yes 599259147
  Pedro 50 0:No 599259147
  Pablo 25 1:Yes 599259147
  Maria 33 1:Yes 599259147

SELECT *
  FROM form_metadata_562919156 f,
       question_group_572849152 qg
 WHERE f.instance = qg.form_instance_id

  identifier display_name device_identifier instance submitter surveyal_time form_version submission_date 562909151_name 562879159_age 594369156_literacy form_instance_id
  k5x6-rus4-s1k3 Juan s7 599259147 iperdomo 50 1 2020-05-04 05:31:19+00 Juan 10 1:Yes 599259147
  k5x6-rus4-s1k3 Juan s7 599259147 iperdomo 50 1 2020-05-04 05:31:19+00 Pedro 50 0:No 599259147
  k5x6-rus4-s1k3 Juan s7 599259147 iperdomo 50 1 2020-05-04 05:31:19+00 Pablo 25 1:Yes 599259147
  k5x6-rus4-s1k3 Juan s7 599259147 iperdomo 50 1 2020-05-04 05:31:19+00 Maria 33 1:Yes 599259147

Flow DB structure

We have entities that defines schema and tables that hold data.
  Similar to OO concepts, classes and instances.
Entities that define schema


  Survey
  Form
  Question Group
  Question

Entities that hold data


  Datapoint
  Form Instance
  Answer

We can clearly see that we have less entities that hold data than
  those defining schema. The reason for this is that some entities that
  defined only presentation/UI concepts (Question Group) were reused
  to define schema.

  Structure Data
  Survey Datapoint
  Form Form Instance
  Question Group 
  Question Answer

In the current DB structure, there is a missing Question Group
  Instance, so an Answer should point to that parent and not to Form
  Instance.  More over, a Datapoint does not have a link (foreign key)
  to a Survey.

Implementation notes/thoughts


  There are several ways to optimize how we store answers. Having
    those all rows in a single table has some problems:
    
      A single table means joining the same table several times (as many
        as questions). We can always optimize by using subqueries and
        limiting the results per question_id but is always expensive.
        
          As example akvoflow-23 would have 31M rows in the answer
            table.
        
      
      To avoid NULL values and solve the problem of millions of rows
        in a single table we can shard. Create a table per question id
        and type (e.g. answer_123_text, answer_123_date, etc)
    
  
  Those views with the crosstab/pivot (e.g. form_123_metadata,
    question_group_456) are actual queries/joins to the database. We
    could pre-compute the work using Materialized Views. Using
    materialized views has the drawback of duplicating data (at least
    double of data storage requirement) and they need to be refreshed.
    https://www.postgresql.org/docs/11/rules-materializedviews.html
List of relations
Schema	Name	Type	Owner	Size	Description
public	collection	table	lumen	8192 bytes
public	collection_entity	table	lumen	8192 bytes
public	dashboard	table	lumen	8192 bytes
public	dashboard_visualisation	table	lumen	8192 bytes
public	data_source	table	lumen	8192 bytes
public	dataset	table	lumen	8192 bytes
public	dataset_version	table	lumen	8192 bytes
public	geography_columns	view	postgres	0 bytes
public	geometry_columns	view	postgres	0 bytes
public	invite	table	lumen	8192 bytes
public	job_execution	table	lumen	8192 bytes
public	ragtime_migrations	table	lumen	8192 bytes
public	raster_columns	view	postgres	0 bytes
public	raster_dataset	table	lumen	8192 bytes
public	raster_overviews	view	postgres	0 bytes
public	share	table	lumen	8192 bytes
public	spatial_ref_sys	table	postgres	4616 kB
public	visualisation	table	lumen	8192 bytes
Table “public.form”
Column	Type	Collation	Nullable	Default
id	text		not null
survey_id	text		not null
name	text		not null
created_at	timestamp with time zone		not null	now()
modified_at	timestamp with time zone		not null	now()
Indexes:
“form_pkey” PRIMARY KEY, btree (id)
Foreign-key constraints:
“form_survey_id_fkey” FOREIGN KEY (survey_id) REFERENCES survey(id)
id	question_group_id	name	order	type	variable_name	created_at	modified_at
562879159	572849152	Age	2	NUMBER	age	2020-05-04 05:24:21.066+00	2020-05-04 05:24:38.712+00
562909151	572849152	Name	1	FREE_TEXT	name	2020-05-04 05:24:00.933+00	2020-05-04 05:24:17.613+00
594369156	572849152	Literacy	3	OPTION	literacy	2020-05-04 05:24:42.025+00	2020-05-04 05:25:22.813+00
id	form_instance_id	question_id	iteration	value_number	created_at	modified_at
b6e365fb-67da-4d58-8780-0a966f8840eb	599259147	562879159	1	10	2020-05-06 12:17:32.026693+00	2020-05-06 12:17:32.026693+00
72d0d3fc-bd9e-4afd-b803-d4603ac08476	599259147	562879159	2	50	2020-05-06 12:17:32.026693+00	2020-05-06 12:17:32.026693+00
b271b72c-95f5-4222-a572-844ec65e04f1	599259147	562879159	3	25	2020-05-06 12:17:32.026693+00	2020-05-06 12:17:32.026693+00
424d4b3e-a93c-4391-8bde-c0bfaa8e00b0	599259147	562879159	4	33	2020-05-06 12:17:32.026693+00	2020-05-06 12:17:32.026693+00
562909151_name	562879159_age	594369156_literacy	form_instance_id
Juan	10	1:Yes	599259147
Pedro	50	0:No	599259147
Pablo	25	1:Yes	599259147
Maria	33	1:Yes	599259147
identifier	display_name	device_identifier	instance	submitter	surveyal_time	form_version	submission_date	562909151_name	562879159_age	594369156_literacy	form_instance_id
k5x6-rus4-s1k3	Juan	s7	599259147	iperdomo	50	1	2020-05-04 05:31:19+00	Juan	10	1:Yes	599259147
k5x6-rus4-s1k3	Juan	s7	599259147	iperdomo	50	1	2020-05-04 05:31:19+00	Pedro	50	0:No	599259147
k5x6-rus4-s1k3	Juan	s7	599259147	iperdomo	50	1	2020-05-04 05:31:19+00	Pablo	25	1:Yes	599259147
k5x6-rus4-s1k3	Juan	s7	599259147	iperdomo	50	1	2020-05-04 05:31:19+00	Maria	33	1:Yes	599259147
Structure	Data
Survey	Datapoint
Form	Form Instance
Question Group
Question	Answer