title: Proper Data model for stateful user forms using PostgreSQL
body: <p>I'm building where much of the UI evolves from the user filling out and completing form wizards centered around a specific topic. I'm currently using PostgreSQL with Hasura GraphQL engine on the backend. I chose Hasura becuase I'm using GraphQL to pull in other data sources as well, and because of how well it can handle eventing with Postgres. My requirements are as follows:</p>
  <ol>
  <li><p>Because this is an early-stage startup, the questions on these forms are likely to change frequently. The questions can take several different shapes, so I've been using JSONB columns to store the answers.I'm trying my best to make sure the data model doesn't change too much with it but this should be expected.</p>
  </li>
  <li><p>These wizards are stateful, so a user should be able to pick up where they left off before completing it (current slide, answers etc).</p>
  </li>
  <li><p>The idea is that once the user completes a form, it's marked as complete and <strong>some (not all)</strong> of that data is used in the UI in one way or another. Some of the questions are more meant to help the user/collect additional information.</p>
  </li>
  <li><p>because much of the UI builds from these forms, high writes are to be expected.</p>
  </li>
  </ol>
  <p>I've been banging my head for a while now trying to figure out the best model that allows the most flexbility while being scalable. So far what I have are these 3 ideas:</p>
  <p><strong>Option 1 (What I'm thinking of doing)</strong>:
  have a table that holds the state of a form, including answers (jsonb), userId and status. When the user completes a form there is logic to deconstruct the JSONB column and populate other tables that the UI can pull from. This allows flexibility in the question/answers schema, but my fear is  that if a user hasn't completed a form prior to a question changing, they'll be left with invalid state (also there would be duplicate data eating space).</p>
  <p><strong>Option 1.5</strong> is to use DynamoDB to store these states instead, allowing for faster read/writes with JSON but adding more complexity.</p>
  <p><strong>Option 2  (what I'm doing now)</strong>:
  have a table for saving form status and current slide, and another table that's an EAV table for storing the answers (labeled attributes here), like below:</p>
  <pre><code>CREATE TABLE public.company_attribute (
      attribute_id text NOT NULL,
      company_id uuid NOT NULL,
      &quot;attribute&quot; jsonb NOT NULL DEFAULT jsonb_build_object(),
      form_id text NULL,
      id serial NOT NULL,
      CONSTRAINT company_attribute_pkey PRIMARY KEY (attribute_id,company_id)
  );
  </code></pre>
  <p>This way answers are decoupled from state and I can just query the attribute_id for specific answers. Because there are alot of read/writes this seems like it could be bad down the line, but it works so far</p>
  <p><strong>Option 3</strong> : Same as option 2, except that instead of the EAV table I would just bite the bullet and make tables for different groupings of attributes. From what I understand this would be the most performant and type-strong but would be much less flexible and more book keeping.</p>
  <p>I'm launching this product soon and just want to make sure my data model doesn't hurt too much later on. The tradeoffs are essentially performance vs flexibility vs type safety. I should note that wherever there's JSONB columns I would be doing validation in code.</p>