Quiz JSON Persistence Migration

Motivation

A QuizExercise used to load its questions together with a fan-out of eager @OneToMany child tables: drop locations, drag items, answer options, short-answer spots / solutions / mappings, the submission selections, and the per-component statistics counters. Loading one quiz with many drag-and-drop questions produced a Cartesian-product join blow-up and, on the clustered deployment, a class of stale-collection / id-regeneration bugs (issues #12574, #12584).

This migration collapses each of those child tables into a single JSON column on the row that owns it. There are exactly three new columns:

New column	Owner entity/table	Replaces
quiz_question.content	every QuizQuestion	the question's authored "correct answer" child tables
submitted_answer.selection	every SubmittedAnswer	the submission's selected-answer child tables / join table
quiz_statistic.counters	each QuizQuestionStatistic	the per-component rows in quiz_statistic_counter

The child data is stored as plain POJOs serialized to JSON via Hibernate's native @JdbcTypeCode(SqlTypes.JSON) (the same mechanism already used by QuizQuestionProgress.progress, ExamRoom.seats, etc.). The public getters (getDropLocations(), getAnswerOptions(), getSelectedOptions(), getSubmittedTexts(), …) keep their exact signatures and resolve the JSON on access, so the REST/websocket wire format is unchanged — with the one deliberate exception documented below.

Breaking API change: short-answer submitted texts lose their id

ShortAnswerSubmittedTextDTO no longer carries an id, and the field is gone from the generated client model ShortAnswerSubmittedText.

Submitted texts used to be rows in short_answer_submitted_text and were serialized with that row id. They now live inside the submitted answer's JSON selection column as plain POJOs with no database row, so there is no stable identifier left to expose. Migrating a synthetic id into the JSON was considered and rejected: nothing reads it, and inventing an id that no longer identifies a row would be misleading.

What consumers must do: address a submitted text through its spot (spot.id), which is stable and unchanged. Nothing inside Artemis read the submitted-text id (verified across the Angular client, the Playwright suite, and the generated client), so no in-tree caller needed updating; external API consumers relying on it have to switch to the spot id.

The migration lives in src/main/resources/config/liquibase/changelog/20260707080319_changelog.xml.

Overview: which table collapses into which column

quiz_question.content  (one JSON object per question, discriminated by "type")
├── drag-and-drop   ← drop_location + drag_item + drag_and_drop_mapping (question-owned rows)
├── short-answer    ← short_answer_spot + short_answer_solution + short_answer_mapping
└── multiple-choice ← answer_option

submitted_answer.selection  (one JSON object per submitted answer, discriminated by "type")
├── drag-and-drop   ← drag_and_drop_mapping (submission-owned rows)
├── short-answer    ← short_answer_submitted_text
└── multiple-choice ← multiple_choice_submitted_answer_selected_options (join table)

quiz_statistic.counters  (one JSON array per question statistic)
├── drag-and-drop   ← quiz_statistic_counter rows, discriminator 'DD'
├── short-answer    ← quiz_statistic_counter rows, discriminator 'SA'
└── multiple-choice ← quiz_statistic_counter rows, discriminator 'AC'

Note that drag_and_drop_mapping was dual-purpose: it stored both a question's correct mappings (via question_id) and a submission's mappings (via submitted_answer_id). It is split — the question side goes into content.correctMappings (id-based, with its own id), the submission side into selection.mappings (identity-less value pairs).

JSON schemas

Every stored object matches a POJO in de.tum.cit.aet.artemis.quiz.domain. The content and selection values are polymorphic and carry a "type" discriminator, matching the @JsonTypeInfo / @JsonSubTypes on QuizQuestionContent and SubmittedAnswerSelection. The counters value is a plain array (its type is fixed by the owning statistic subtype, so it needs no discriminator).

content and counters use descriptive keys (drag-and-drop / short-answer / multiple-choice, dropLocations, posX, …) because those POJOs are also serialized to clients (the statistics websocket and exam conduction broadcast the raw entity via getDropLocations() / getDropLocationCounters() / … — so their keys are part of the wire contract). submitted_answer.selection uses abbreviated keys (dnd/sa/mc, maps, diId, …) to save space: it is the only high-volume column (one row per submitted answer, millions at scale, mostly below the TOAST compression threshold), and its POJOs are never serialized to a client (the wire uses the resolved objects from getMappings() / getSubmittedTexts() / getSelectedOptions()). The selection legend is below.

Component ids inside the JSON are question-scoped (unique only within one question) and are the verbatim old primary keys after migration; new components get max(existing ids)+1 minted server-side.

quiz_question.content

// drag-and-drop
{ "type": "drag-and-drop",
  "dropLocations":   [ { "id": 1, "posX": 10.0, "posY": 10.0, "width": 20.0, "height": 20.0, "invalid": false } ],
  "dragItems":       [ { "id": 2, "pictureFilePath": null, "text": "A", "invalid": false } ],
  "correctMappings": [ { "id": 3, "dragItemId": 2, "dropLocationId": 1, "invalid": false } ] }

// short-answer
{ "type": "short-answer",
  "spots":           [ { "id": 1, "spotNr": 0, "width": 30, "invalid": false } ],
  "solutions":       [ { "id": 2, "text": "is", "invalid": false } ],
  "correctMappings": [ { "id": 3, "spotId": 1, "solutionId": 2, "invalid": false } ] }

// multiple-choice
{ "type": "multiple-choice",
  "answerOptions":   [ { "id": 1, "text": "A", "hint": "h", "explanation": "e", "isCorrect": true, "invalid": false } ] }

submitted_answer.selection

Abbreviated keys (see the legend below):

{ "type": "dnd", "maps":  [ { "diId": 2, "dlId": 1 } ] }
{ "type": "sa",  "texts": [ { "spotId": 1, "text": "is", "correct": true } ] }
{ "type": "mc",  "soIds": [ 1, 4 ] }

selection key legend (for reading the column directly)

JSON key	Meaning (Java field)
type = dnd / sa / mc	drag-and-drop / short-answer / multiple-choice submitted answer
maps	mappings (drag-and-drop)
diId	dragItemId
dlId	dropLocationId
texts	submittedTexts (short-answer)
spotId	spotId (unchanged — already short)
text	text (unchanged)
correct	isCorrect
soIds	selectedOptionIds (multiple-choice)

quiz_statistic.counters

// drag-and-drop statistic
[ { "dropLocationId": 1, "ratedCounter": 5, "unRatedCounter": 2 } ]
// short-answer statistic
[ { "spotId": 1, "ratedCounter": 5, "unRatedCounter": 2 } ]
// multiple-choice statistic
[ { "answerId": 1, "ratedCounter": 5, "unRatedCounter": 2 } ]

Field-level mapping reference

Every old column and where it lands. content / selection are on the question / submitted-answer row (keyed by the old question_id / submitted_answer_id, which is why those FK columns are not carried into the JSON — the containment replaces them). [] denotes an array element.

Drag-and-drop

Old column	New location
drop_location.id	content.dropLocations[].id
drop_location.pos_x / pos_y / width / height	content.dropLocations[].posX / posY / width / height
drop_location.invalid	content.dropLocations[].invalid
drag_item.id	content.dragItems[].id
drag_item.picture_file_path	content.dragItems[].pictureFilePath
drag_item.text	content.dragItems[].text
drag_item.invalid	content.dragItems[].invalid
drag_and_drop_mapping.{id, drag_item_id, drop_location_id, invalid} where question_id is set	content.correctMappings[].{id, dragItemId, dropLocationId, invalid}
drag_and_drop_mapping.{drag_item_id, drop_location_id} where submitted_answer_id is set	selection.maps[].{diId, dlId} (abbreviated; no id — submission mappings have no identity)
quiz_statistic_counter.{drop_location_id, rated_counter, un_rated_counter} (discriminator DD)	quiz_statistic.counters[].{dropLocationId, ratedCounter, unRatedCounter}

Short-answer

Old column	New location
short_answer_spot.{id, spot_nr, width, invalid}	content.spots[].{id, spotNr, width, invalid}
short_answer_solution.{id, text, invalid}	content.solutions[].{id, text, invalid}
short_answer_mapping.{id, spot_id, solution_id, invalid}	content.correctMappings[].{id, spotId, solutionId, invalid}
short_answer_submitted_text.{spot_id, text, is_correct}	selection.texts[].{spotId, text, correct} (abbreviated; row id dropped — no identity)
quiz_statistic_counter.{spot_id, rated_counter, un_rated_counter} (discriminator SA)	quiz_statistic.counters[].{spotId, ratedCounter, unRatedCounter}

Multiple-choice

Old column	New location
answer_option.{id, text, hint, explanation, is_correct, invalid}	content.answerOptions[].{id, text, hint, explanation, isCorrect, invalid}
multiple_choice_submitted_answer_selected_options.selected_options_id	selection.soIds[] (abbreviated)
quiz_statistic_counter.{answer_id, rated_counter, un_rated_counter} (discriminator AC)	quiz_statistic.counters[].{answerId, ratedCounter, unRatedCounter}

Not carried over (intentionally dropped)

Old column(s)	Why
*.question_id / submitted_answer_id / multiple_choice_submitted_answers_id FKs	Replaced by JSON containment in the owning row
drop_locations_order, drag_items_order, spots_order, solutions_order, answer_options_order	Array element order encodes it (preserved via ordered aggregates on both PostgreSQL and MySQL)
short_answer_mapping.correct_mappings_order	Correct mappings are an unordered set (matched by id)
short_answer_submitted_text.id	Submitted texts have no identity in the new model
quiz_statistic_counter.{drag_and_drop_question_statistic_id, short_answer_question_statistic_id, multiple_choice_question_statistic_id}	Statistic ownership is by containment in quiz_statistic.counters; these columns are dropped (only quiz_point_statistic_id remains, for PointCounter)

Migration steps

The changelog runs three phases. The content / selection columns are added once (steps 1a–1d), the counters column once (steps 5a–5b); the per-type backfills reuse them.

Steps	What
1a–1d	Add quiz_question.content and submitted_answer.selection (jsonb on PostgreSQL, json on MySQL/H2)
2, 3	Backfill drag-and-drop content and selection
4	Drop the drop-location counter FK + unique index on quiz_statistic_counter
5	Add quiz_statistic.counters
6	Backfill drag-and-drop counters
7–10	Short-answer: drop spot-counter FK/index, backfill content / selection / counters
11–14	Multiple-choice: drop answer-counter FK/index, backfill content / selection / counters
15–17	Cleanup (destructive): delete dead counter rows, drop unused quiz_statistic_counter columns, drop the legacy tables

Each dialect has its own changeset: PostgreSQL uses jsonb_build_object / jsonb_agg (with ORDER BY), MySQL uses JSON_OBJECT / JSON_ARRAYAGG. Backfills are filtered by the row discriminator ('DD' / 'SA' / 'MC') so each row gets exactly the right shape, and use COALESCE(…, '[]') so a question with no components still gets a valid (empty) array.

Cleanup: what is dropped and what is kept

❗

Danger:

Steps 15–17 are destructive and irreversible. They delete the source rows and drop the legacy tables, so the JSON backfills become the single source of truth. Run the migration against a copy of the production database and verify a sample of migrated quizzes before the real cutover — especially on MySQL, where the backfill SQL is not covered by the automated test suite (see Caveats).

Tables dropped (step 17) — now fully represented in content / selection:

drag_and_drop_mapping, drag_item, drop_location, short_answer_mapping, short_answer_spot, short_answer_solution, short_answer_submitted_text, answer_option, multiple_choice_submitted_answer_selected_options.

They are dropped in dependency order (referencing tables first) so no foreign key blocks the drop. The foreign keys these tables held into quiz_question / submitted_answer are removed together with each table; the quiz_statistic_counter foreign keys into drop_location / short_answer_spot / answer_option were already removed in steps 4 / 7 / 11.

quiz_statistic_counter columns dropped (step 16) — only ever used by the folded counter types:

drag_and_drop_question_statistic_id, short_answer_question_statistic_id, multiple_choice_question_statistic_id, drop_location_id, spot_id, answer_id, plus their foreign keys and indexes.

Rows deleted (step 15): quiz_statistic_counter rows with discriminator 'DD', 'SA', 'AC'. Their data now lives in quiz_statistic.counters, and — because DropLocationCounter / ShortAnswerSpotCounter / AnswerCounter are no longer JPA entities — those discriminators no longer map to anything, so the rows must be removed before their columns are dropped.

ℹ️

Information:

quiz_statistic and quiz_statistic_counter are intentionally kept. QuizPointStatistic and its PointCounter (discriminator 'PC', columns points + quiz_point_statistic_id) are still relational — point statistics were not folded into JSON. After cleanup, quiz_statistic_counter holds only 'PC' rows, and quiz_statistic holds the question statistics (with the new counters column) plus the point statistics.

Correctness guarantees

• Keys match the POJOs. Every backfilled JSON key equals the @JsonProperty / field name on the target POJO, and every POJO field is backfilled, so nothing is silently dropped on read (FAIL_ON_UNKNOWN_PROPERTIES is disabled globally, which makes key correctness critical).
• Ids are preserved. Old primary keys become the question-scoped ids inside the JSON, so existing submissions (which reference drag items / spots / options by id) and statistics counters keep resolving after migration.
• Discriminator-scoped. Each backfill only touches rows of its own question type, so the three subtypes that share quiz_question / submitted_answer / quiz_statistic never cross-contaminate.
• Validated on PostgreSQL. The full changelog (backfills + cleanup) runs on every server-test context load against Testcontainers PostgreSQL, and the quiz + exam + statistics suites pass against the resulting schema.

Caveats

• MySQL is not covered by the automated test suite (tests use PostgreSQL via Testcontainers). Two MySQL-specific behaviors to validate on a staging copy before cutover:
  • Booleans: x = TRUE produces the JSON numbers 1 / 0 (not true / false). Jackson coerces them back to Boolean on read, so this is functionally correct, but the on-disk representation differs from PostgreSQL.
  • Ordering: MySQL JSON_ARRAYAGG has undefined order as a grouped aggregate. The migration therefore uses ordered window aggregates for every authored list and has been exercised directly on MySQL 9.7 with deliberately scrambled source ids; still verify representative migrated quizzes on staging because the destructive MySQL changesets are not part of regular CI.
• H2 is not backfilled. The columns are added on H2, but there are no H2 backfill changesets — H2 is only used for ephemeral, freshly-created schemas that never contain pre-migration relational data.
• Irreversible. Once step 17 drops the tables there is no rollback path for the child data; the JSON columns are authoritative.

Differences from the earlier feature/quiz-json-migration branch

This migration is adapted from the abandoned feature/quiz-json-migration branch (20240814091201_changelog.xml), with the following intentional differences:

Aspect	Old branch	This migration
Statistics location	folded all statistics, incl. point stats, into quiz_question.statistics + quiz_exercise.statistics (2 extra columns)	keeps point statistics relational; per-question counters go into quiz_statistic.counters (1 column)
Tables fully removable	could drop quiz_statistic and quiz_statistic_counter entirely	keeps both (needed by QuizPointStatistic / PointCounter); drops only the folded rows/columns
JSON keys	terse abbreviations (locs, items, dlid, diid, txt, pr/pu…)	descriptive names matching the POJO fields (dropLocations, dragItemId, ratedCounter…)
Correct-mapping ids	not stored	stored (id), so re-evaluation can diff mappings by id
Trailing-comma bug	present in the correct-mapping JSON_OBJECT / json_build_object (invalid SQL on both dialects)	fixed
Table drops	present but commented out (never executed)	executed in steps 15–17