Our interview practice product grew a second format last night: the original one-way video flow for early careers, and a new live-style track for experienced hires and returners. Different question flow, different timings, different marking, same product.

The database change for all of it is migration 0053, which is 312 bytes:

ALTER TABLE "interviews" ADD COLUMN "track" text DEFAULT 'early_careers' NOT NULL;--> statement-breakpoint ALTER TABLE "interviews" ADD COLUMN "config" jsonb;--> statement-breakpoint ALTER TABLE "interviews" ADD CONSTRAINT "interviews_track_valid" CHECK ("interviews"."track" IN ('early_careers','experienced'));

The backfill that is not there

DEFAULT ... NOT NULL on a populated table used to mean a full rewrite, so the folklore is still to add the column nullable, backfill in batches, then set NOT NULL. Since Postgres 11 a non-volatile default is stored in the catalogue and existing rows read it without being touched, so every interview recorded before last night is now correctly an early_careers session, at the cost of nothing.

That is the real argument for the default, by the way. It is not about saving an UPDATE. It is that every historical row genuinely was the old format, so the default is a true statement about the past rather than a placeholder.

Why text and a CHECK rather than an enum

53 migrations into this schema there are zero CREATE TYPE statements and, after this one, 37 CHECK constraints. The interviews table alone has three: status, sector and now track.

The reason is the shape of the next change. Adding a third track to a CHECK is one migration that drops and recreates a constraint inside a normal transaction. Adding a value to a Postgres enum is ALTER TYPE ... ADD VALUE, which has its own rules about transactions and cannot be removed again at all. Our value lists change more often than they are read, and a constraint that is one line of SQL is cheaper to change than a type the whole database shares.

The cost is that text tells the application nothing, so the application has to say it, which brings us to the read.

Two columns that have to agree

config is the experienced track's settings: level, interview stage, and an optional returner situation. It is null for early careers. Both the read and the write gate on the track rather than on the column being present:

// write config: options.track === 'experienced' ? options.config : null, // read track: row.track === 'experienced' ? 'experienced' : 'early_careers', config: row.track === 'experienced' ? parseExperiencedConfig(row.config) : null,

Reading config only when track says to is the part worth copying. A row that somehow ends up with a stale config and an early-careers track cannot leak those settings into the wrong renderer, because nothing reads the jsonb unless the discriminator asks for it. The track read is also written as a two-way narrowing rather than a cast, which makes the typed value 'early_careers' | 'experienced' by construction even though the column is text and the CHECK is somewhere the type system cannot see.

The parser is deliberately lenient:

export function parseExperiencedConfig(raw: unknown): ExperiencedConfig | null { if (typeof raw !== 'object' || raw === null) return null; const r = raw as Record<string, unknown>; const level = ALL_LEVELS.find((l) => l === r.level); const stage = ALL_STAGES.find((s) => s === r.stage); if (!level || !stage) return null; const returnerSituation = ALL_RETURNER_SITUATIONS.find((s) => s === r.returnerSituation) ?? null; return { level, stage, returnerSituation }; }

Three find calls over the allow-list arrays, which validate and narrow in one step. No schema library, because this module is imported by client components for its labels and we are not shipping a validator to the browser to read a jsonb column the browser never sees.

The column that does not exist

A returner situation is one of a fixed set of options, something like a break for caring, health, redundancy, a long job search, leaving the armed forces, or a career change. There is no free-text column for it anywhere, and no part of the flow asks for the story behind it. That is a schema decision doing the work that a policy sentence normally has to promise: the broad reason is a value from a list, and the detail cannot be stored because there is nowhere to put it.

Three things still not enforced

  • question_ids and question_texts must be the same length. The doc comments say so, the one caller guarantees it, and the database has three CHECK constraints on this table and none of them is that.
  • config must be present when track = 'experienced'. Nothing says so in SQL; the lenient parser returns null and the session degrades.
  • The repository function gained its new argument in the wrong place: 
export async function createInterview( userId: string, questionIds: string[], questionTexts: string[], sector: Sector | null = null, options: { track: InterviewTrack; config: ExperiencedConfig | null } = { ... }, id: string = crypto.randomUUID() )

A new optional parameter was inserted in front of an existing optional one. There is exactly one caller and it passes five arguments, so nothing broke, and a caller that had been passing a pre-generated id positionally would now be passing it as options. TypeScript catches that particular case, which is the only reason this is a note rather than an incident.

The file nobody reads

Drizzle writes a full schema snapshot per migration, so the 312 bytes of SQL arrived with a 116,107-byte JSON file. The real difference between it and the previous snapshot is 21 lines: two column definitions, one constraint, and two changed ids. The meta directory is now 2.3 MB across 53 snapshots, which is the price of being able to generate the next migration from a diff instead of from a live database. It shows up in a code review as a 4,237-line addition and should be read as one.

See it

  • The user-visible half of that jsonb column: cogniprep.app/interview has a FAQ entry on practising a career break, which ends "Only the broad reason is used, never any detail". That is the enum, in product copy.
  • The data categories are listed at cogniprep.app/privacy under Experienced interview settings: the level and stage you chose, and the broad reason, with the note that we never ask for the details behind it. The policy went to 1.9.0 in the same commit.