Backend: Registration Form

[sabinem]

Custom registration forms — data model

• Handle: registration-form-schema
• Area: components/backend/db/schema/ (new tables), Hackathon and User
  edges
• Kind: feature spec, not a gap report — nothing is degraded today because
  nothing exists.

First of three tickets. The API surface and the handlers follow, and both depend
on the decisions here.

What the feature is

An organizer defines the questions their event asks people at registration —
affiliation, dietary needs, t-shirt size, a code-of-conduct consent — and
registrants answer them after joining. The questions differ per event, so
nothing about them can be hard-coded.

Two roles, two surfaces:

• Organizer: builds the form; later reads everyone's answers and exports
  them.
• Registrant: answers after joining, and can come back and correct what they
  wrote.

What the schema must support

1. Questions defined per hackathon — ordered, typed, required or optional,
   some with a fixed set of choices.
2. Consents — checkboxes with legal weight, distinct from ordinary
   questions.
3. Answers that stay editable by the person who gave them.
4. An organizer reading the whole cohort's answers in one query.
5. Questions changing after people have already answered. This is the
   requirement that drives everything below.
6. The same machinery serving submission forms later, without a redesign.

The decision that shapes it: freeze on first answer

Requirement 5 is the whole problem. If an organizer can freely edit a live form,
then answers can be orphaned by a renamed key, invalidated by a changed type, or
retroactively made non-compliant by a newly required consent — and nothing in
the data model can prevent it.

So: once one person has answered, the destructive edits are refused. Not the
whole form — only the edits that can damage what is already stored.

Edit	After the first answer	Why
Change a label or help text	allowed	Cosmetic. Answers are keyed by row identity, not by prose.
Append an optional question	allowed	Nothing already stored becomes wrong.
Make a required question optional	allowed	A relaxation. Every stored answer stays valid.
Add a choice to a select	allowed	Existing answers still point at live choices.
Rename a question's key	allowed	Only because answers reference the UUID. See "Why normalized" below.
Reorder questions	allowed	Display only.
Delete a question	refused	Destroys answers.
Change a question's type	refused	text → number invalidates what is stored.
Make an optional question required	refused	Retroactively makes every existing submission incomplete.
Remove a choice from a select	refused	Existing answers would point at a dead choice.
Add a required question	refused	Everyone already registered becomes non-compliant.
Add a consent, or make one required	refused	Worse than non-compliance: the records would claim agreement from people who were never shown the text.

The trigger is the first submitted answer, not the registration window and not
CAPABILITY_REGISTER. A form nobody has answered stays fully editable, which
is the entire setup phase — exactly when organizers iterate. Gating on the
capability instead would lock them out while the form is still empty, for no
benefit. The check is one existence query (see FormSubmission below).

Enforcement is the handler's job (ticket 3), and it must be enforced in the
backend — the standing invariant is that the backend is authoritative for
every access decision, and "may I still edit this" is one.
FAILED_PRECONDITION, since it is about state rather than a malformed request.
The schema's job is to make the check cheap and to backstop the refusals with
foreign keys.

Open question, deliberately unresolved: the escape hatch. Organizers will
occasionally need to break these rules for real reasons — a question that turns
out to be unusable, mid-event, with forty registrations in. A system with no
hatch gets worked around by editing the database directly, which is worse than
any hatch we would design. To be settled with the client; the candidates are a
global-admin override or an explicit "delete all answers to this question and
unlock it" action that names the destruction it performs. No schema change is
needed for either, so this ticket is not blocked on it.

Why normalized rather than JSON

The tempting shortcut is two tables holding JSON: a schema blob and an answer
blob keyed by the organizer's string keys. It is smaller and faster to build,
and with the freeze rule above it is not unsafe.

It is still the wrong default here, for two reasons:

1. Question identity. With JSON keyed by strings, a question's identity is
   its key, so renaming a key orphans every answer already given — silently,
   with no error anywhere. The freeze table above can only forbid renaming to
   avoid it. With a row per question, identity is the row's UUID, so an
   organizer can rename the key and the label freely and nothing detaches.
   That converts a forbidden edit into a permitted one, which is a better form
   builder.
2. Segmentation. "Show me everyone who needs a vegetarian meal", "group the
   beginners" — these are ordinary SQL against rows and JSON operators against
   blobs. Normalizing keeps that available; JSON forecloses it, and migrating
   out later is painful. If it turns out organizers only ever read and export,
   this ticket can be collapsed to the JSON model cheaply — but not the reverse.

The ordering column that normalizing requires is not new work: Page already
does exactly this, with MoveUp / MoveDown / SetOrder. Copy that pattern.

Tables

Six new tables. Field lists are the intent, not a prescription — names and ent
idioms are the implementer's call.

HackathonForm — the form header

One row per hackathon per form kind.

Field	Type	Notes
kind	enum(registration, submission)	Present from the start even though only registration is used. It costs nothing now and saves a migration when submission forms land.
intro	string, optional	Prose shown above the questions.
created_at / modified_at	time

Edges: hackathon (from Hackathon.forms, required), modifier (from
User.modified_forms, optional — matching HackathonState, so seeded rows
need no attribution).

Unique index on (hackathon, kind).

Alternative considered: hang questions off Hackathon directly with a kind
discriminator and drop this table. It saves a join, but form-level metadata
(intro), edit attribution, and any future draft/published state have nowhere
to live. Keep the header.

FormQuestion

Field	Type	Notes
key	string	Machine name, used as the export column heading. Renameable, because it is not the identity. Constrain to [A-Za-z0-9][A-Za-z0-9_-]{0,63}. Unique per form.
label	string	The question as the registrant reads it.
help_text	string, optional
type	enum	text, textarea, email, url, number, date, select_one, select_many. See the note below.
required	bool
order	int	Display position. Follow Page's pattern.
created_at / modified_at	time

Edges: form (required), options (to FormQuestionOption), answers (to
FormAnswer, OnDelete(Restrict)).

Index on (form, order).

On type being an enum. A closed set means the backend can actually
validate an answer — that a select_one answer is one of that question's own
options, that a number is a number. A free string cannot be validated
server-side, which leaves the browser as the only thing enforcing the form's own
rules, and a browser is not a security boundary. The cost is honest: adding a
field type becomes a migration plus a regen rather than a frontend-only change.
Worth it. File-upload types are deliberately absent — there is no storage
backend to accept an upload yet, and a type nothing can fulfill is worse than no
type.

FormQuestionOption

The choices for select_one / select_many.

Field	Type	Notes
label	string	What the registrant reads.
value	string	Machine name, used in exports.
order	int

Edges: question (required), chosen_by (from FormAnswer.selected_options).

Its own table rather than a JSON array on the question, for the same identity
reason as questions: an answer references the option's UUID, so relabelling a
choice does not break the answers that chose it, and "removing a choice that
people have selected" is refused by a foreign key rather than by handler logic
that has to remember to look. This is the one table I would accept as JSON
if the count feels too high — it is the weakest of the six.

FormSubmission — one registrant's act of answering

One row per user per form. Attribution and timestamps live here rather than on
each answer, because filling a form in is one act.

Field	Type	Notes
created_at	time, immutable	When they first submitted.
modified_at	time	Last revision.

Edges: form (required), user (required — whose answers these are),
submitted_by (required — who entered them; the registrant, or an organizer
digitizing a paper form at the check-in desk), answers (to FormAnswer).

Unique index on (form, user) — the answers are current state, not an
append-only log, so a second submission revises the first. This is also the row
the freeze check reads: does any FormSubmission exist for this form.

FormAnswer

One row per question per submission.

Field	Type	Notes
value	string, optional	The answer for scalar types. Empty for the select types.

Edges: submission (required), question (required), selected_options (M2M
to FormQuestionOption — one row for select_one, many for select_many).

Unique index on (submission, question).

Denormalize hackathon onto this row. "Every answer for this hackathon" is
the organizer's hot path, and without it that is a three-table join up through
question → form → hackathon. It cannot drift, because a question never moves
between hackathons.

FormConsent and ConsentRecord

Consents are not questions, and modeling them as "a field whose answer is a
bool" is what makes the properties below impossible. This split is the
recommendation in this ticket I would defend hardest, and it is independent of
every other choice here.

FormConsent — the definition:

Field	Type	Notes
key	string
text	string	The statement being agreed to. Prose, possibly long.
required	bool	A required consent blocks submission until ticked.
order	int

Edges: form (required), records (to ConsentRecord, OnDelete(Restrict)).

ConsentRecord — append-only:

Field	Type	Notes
granted	bool	Given, or withdrawn.
consent_text	string	A snapshot of the exact text agreed to. The whole point: an organizer rewriting the code of conduct next week must not silently rewrite what people already agreed to.
created_at	time, immutable

Edges: consent (required), user (required), recorded_by (required).

No unique index, and no updates. Withdrawing consent appends a
granted: false row rather than flipping the old one, so the record of it
having been given survives. "Current consent state" is the latest row per
(consent, user).

This is what gives the compliance-shaped property — what exactly did this
person agree to, and when — without versioning ordinary questions, which nobody
has asked for.

Edges to add on existing tables

• Hackathon: forms (O2M to HackathonForm — O2M rather than unique, since
  kind makes room for the submission form).
• User: modified_forms, form_submissions, entered_form_submissions,
  consent_records, recorded_consents. All OnDelete(Restrict), matching
  every other authored edge on User.

Acceptance

• A hackathon can hold an ordered set of typed questions, with choices for the
  select types, and a separate ordered set of consents.
• A registrant has at most one submission per form, holding at most one answer
  per question, and both are revisable.
• Schema.md regenerates and documents all six tables.
• Deleting a question that has answers is refused by the database, not only by a
  handler.
• Relabelling a question or an option does not touch any stored answer.
• Withdrawing a consent leaves the record that it was previously given, together
  with the text that was agreed to at the time.
• "Has anyone answered this form" is a single indexed existence query.

Not in this ticket

• The API surface and the permitted-edit enforcement — ticket 2 (proto) and
  ticket 3 (handlers). Ticket 3 is where the freeze matrix above is
  enforced, and the granular RPC shape it argues for (AddQuestion,
  EditQuestionText, ReorderQuestions, RelaxRequirement) rather than a
  whole-form replace, which would have to diff to discover what changed.
• The escape hatch, pending the client conversation. No schema impact.
• Submission forms. The kind column makes room; nothing else here assumes
  them.
• Draft vs published forms. Deliberately out — the freeze-on-first-answer rule
  covers the actual need without a state machine.
• File-upload answers. No storage backend to accept them.
• Server-side validation rules beyond type and options (regex, min/max). The
  enum type makes them possible later; none are specified now.