Feature: Forms

[qwerzl]

So uh, I'm not familiar with designing a schema to store form responses with relational database. But here's what I've collected by far:

Zod ⇔ JSON Schema:

• json-schema-to-zod
• zod-to-json-schema

P.S. The library's README suggests using eval(). Use new Function() instead.

Form Rendering

• Auto Form. Convert the stored JSON schema back to zod.

Database design

e.g.:

model Form {
  id                String   @id @default(dbgenerated("gen_random_uuid()"))
  creationTimestamp DateTime @default(now())
  user              User     @relation(fields: [userId], references: [id])
  userId            String
  club              Club     @relation(fields: [clubId], references: [id])
  clubId            Int
  formSchema        String // A JSON Schema converted from Zod

  FormResponse FormResponse[]
}

model FormResponse {
  id                String   @id @default(dbgenerated("gen_random_uuid()"))
  creationTimestamp DateTime @default(now())
  user              User     @relation(fields: [userId], references: [id])
  userId            String
  form              Form     @relation(fields: [formId], references: [id])
  formId            String
  response          Json // A JSON object that conforms to the schema as designed in the form
}

It's best to validate the response data being fed into the database every INSERT or UPDATE.

Resources:

• pg_jsonschema
• Enable postgres extensions in Prisma
• We need a trigger at postgresql every UPDATE or INSERT. Check constraints won't work because they don't support subqueries. eg:

  CREATE OR REPLACE FUNCTION validate_formresponse_schema() 
  RETURNS TRIGGER AS $$
  BEGIN
      -- Validate that the meta field matches the schema
      PERFORM jsonb_matches_schema(
          NEW.form.formSchema,
          NEW.response
      );
  
      -- If validation passes, proceed with the insert/update
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

  CREATE TRIGGER validate_formresponse_schema_trigger
    BEFORE INSERT OR UPDATE ON FormResponse
    FOR EACH ROW
    EXECUTE FUNCTION alidate_formresponse_schema();

[qwerzl]

@Computerization/enspire-development-managers

[q1zhen]

LGTM. I would definitely recommend using JSON (or other equivalent standardized data structures that can store all data in a form into one single field). By this we would only need one uniform form (& data) read / write server-side API.

[q1zhen]

At least for me, I prefer the solution of using as few controllers (or APIs or whatever they are called) as possible and passing them parameters in later uses.