The list shows two numbers, so the query digs them out of the JSON instead of loading a hundred attempts

We added a "Past attempts" list to the assessment centre dashboard. Each row is a title, a date, one number and a one-word caption. There is a /dashboard/exercises/report/<id> page behind it, so the list itself is pure navigation.

The table it reads is not small per row. One attempt holds the candidate's answers and, where the exercise has written items, the full AI review of them, both as jsonb. Those columns are the point of the table and they are also the reason you cannot write the obvious query. select * ... where user_id = $1 limit 100 to render a hundred one-line rows means moving every word anyone typed into that page's memory to find out whether to print 72 or Not scored.

Read the two values, not the two documents

Postgres will do the digging:

const rows = await db
  .select({
    id: exerciseAttemptsTable.id,
    exerciseId: exerciseAttemptsTable.exercise_id,
    completedAt: exerciseAttemptsTable.completed_at,
    createdAt: exerciseAttemptsTable.created_at,
    rawScore: exerciseAttemptsTable.raw_score,
    totalMcqItems: sql<number | null>`
      (${exerciseAttemptsTable.objective_metrics}->>'totalMcqItems')::int`,
    writtenScore: sql<string | null>`
      CASE WHEN ${exerciseAttemptsTable.ai_status} = 'complete'
      THEN ${exerciseAttemptsTable.ai_feedback}->>'overallScore' END`,
  })
  .from(exerciseAttemptsTable)
  .where(eq(exerciseAttemptsTable.user_id, userId))
  .orderBy(desc(exerciseAttemptsTable.completed_at))
  .limit(PAST_ATTEMPTS_LIMIT);

Four details in there earned their place:

->> and the cast. The text operator returns text, always. One of the two gets ::int in SQL because it is an integer that is compared against zero; the other comes back as a string and is converted in TypeScript, because it is a mean score that can carry decimals and I would rather do that rounding in one place than argue with numeric typing. The Drizzle sql<...> generic is a claim about the shape, not a check, so the mapper that follows treats both as untrusted: Number(r.totalMcqItems ?? 0) and r.writtenScore === null ? null : Number(r.writtenScore).

The CASE, rather than a WHERE. An attempt whose review failed is still an attempt and still belongs in the list. Filtering on ai_status = 'complete' would have hidden it. The CASE yields NULL for every state except complete, which is exactly the input the caption logic wants: a score when there is one, the word "Not scored" when there is not.

completed_at or created_at. The sort key is nullable, and the mapper falls back: completedAt: r.completedAt ?? r.createdAt. A row with no completion timestamp is an attempt that did not finish cleanly, and sorting it to one end of the list is better than dropping it.

The limit is a named constant with a comment, not a magic 100. Nobody has a hundred attempts yet. The limit exists so the page cannot become unbounded later without someone editing a line that says what it is for.

The return type is the other half of the discipline:

/** One row of the past attempts list: no answers, no feedback text. */
export interface ExerciseAttemptSummary { ... }

A type that cannot hold the heavy fields is a type you cannot accidentally widen in six months by adding one more select column.

The list is allowed to fail on its own

The dashboard's main job is to show the exercise library. The history is a nice extra, and it is fetched in the same Promise.all as the plan and the preferences, so a thrown error there would take out the page:

// The library still works without the history, so a failed read hides the
// list rather than the page.
getExerciseAttemptSummaries(user.id).catch(() => []),

An empty array renders no section at all, because the component returns null for zero attempts. Degrading to "the feature is absent" beats degrading to an error page when the feature is secondary.

Dates are formatted on the server, deliberately

Each row shows a date, and the row is a client component because the list collapses to three entries with a "Show all" toggle. The temptation is to pass a Date and format it in the component. We pass a string:

dateLabel: new Intl.DateTimeFormat(spelling, {
  day: 'numeric', month: 'short', year: 'numeric',
}).format(attempt.completedAt),

spelling there is the account's UK or US English preference, and because we store that preference as en-GB or en-US it is already a valid locale tag. One stored value decides both how the content is spelled and whether the row reads 15 Oct 2026 or Oct 15, 2026. Formatting it once on the server means the HTML and the first client render are the same string, which is one fewer hydration mismatch to debug, and it means the date does not depend on the timezone of whoever is looking.

One more rule in the mapper: an attempt at an exercise that is no longer in the library returns null and is filtered out, since its report has nothing to rebuild from. The list is derived from both the database and the content, and content is the one that changes.

See it

The exercises themselves are at cogniprep.app/assessment-centre-practice, where the page names the one e-tray scenario that is free to play in full, and the format is explained in the in-tray guide if you want to know what you are walking into first. The history list described here is in the repository and ships with the next release, so what you can see today is the attempt being saved rather than the list of saved attempts.

The general version

Before writing a list query, write down the fields the row renders. If any column in the table is a document rather than a value, the gap between that list and select * is the whole cost of the feature. Postgres is extremely good at reading one key out of a jsonb column, and the alternative is paying network and memory for text that nothing on the page will ever display.