Add a field to an existing table

Example used to test this page: items.priority, an integer that defaults to 0, readable in the API and settable on create and update.

Steps

1. Write the migration. Copy the last file in backend/migrations/ and save it as the next number, NNN_<name>.ts. Migrations run in file-name order, so the number is what places yours last. For adding a column, follow the shape of backend/migrations/005_worker_claimed_at.ts:

   export async function up(knex: Knex): Promise<void> {
     await knex.schema.alterTable('user_deletion_queue', (t) => {
       t.string('status').notNullable().defaultTo('pending');
       t.timestamp('claimed_at', { useTz: true }).nullable();
   

   The safe column is either .nullable() or .notNullable() with a .defaultTo(...). A NOT NULL column with no default fails the migration on any table that already has rows. up() never drops or rewrites existing data. To rename or retype a column, add the new one, copy the values across, and drop the old one in a later release. down() reverses only what up() added.

2. Add the column to the row interface in backend/src/db/types.ts (ItemRow, UserRow, ...). snake_case, | null if the column is nullable.

3. Fix every spec fixture typed as that row. A spec that builds a row literal, such as baseRow in items.controller.spec.ts, no longer compiles until it carries the new field, and jest fails the suite. npx tsc --noEmit -p tsconfig.json in backend/ lists them.

4. Add the field to the zod schemas in the feature's *.schemas.ts (backend/src/items/items.schemas.ts for items): the response schema, the create and update request schemas if clients may set it, and the extractor at the bottom of the file. camelCase on the wire:

   export const extractItemDtoFromRow = (row: ItemRow): ItemDto => ({
     id: row.id,
     name: row.name,
     description: row.description,
   

   Skip the extractor and the field never reaches the API, with no error.

5. Pass it through the service and controller if clients write it. In items, CreateItemArgs / UpdateItemArgs in items.service.ts, the insert and the update object there, and the explicit argument list in ItemsController.create. A field missing here is accepted by the schema and then silently dropped.

6. The deletion-policy rule. Find the table's entry in backend/src/users/deletion-policy.ts. That file is the current list of which tables survive account deletion. An entry without a columns block is a table whose rows are deleted outright, and needs nothing. An entry with columns is a table whose rows survive: list the new column under erased or kept.

   • kept: the column holds nothing that identifies the person.
   • erased: it does. The eraser named in the entry's erases.by must overwrite it, in its existing update on that same table. For users and email_jobs that eraser is ghostDelete in backend/src/users/users.service.ts.
   • user_deletion_queue is the exception among erases entries: its eraser, processDeletionQueue in user-deletion.worker.ts, only deletes the row and has no update the spec can check. Do not store personal data in the queue row at all. last_error shows the pattern: the worker scrubs it before writing, and it is listed as kept.
   • An entry with keeps instead of erases has no eraser, so an erased column there fails the spec. For the billing_* tables, do not add personal columns at all: they are written only by the billing code (billing_subscriptions only by BillingSubscriptionStore), and deletion reaches them through deleteCustomerData; see Account deletion. If you must, list the column under kept and name the call that erases it in the entry's keeps reason, as the header of deletion-policy.spec.ts prescribes. For any other keeps table, change the entry to erases and add the update to an eraser.

   What fails if you skip it:

   • Not listed: deletion-policy.integration.spec.ts fails with "users.timezone is unlisted: add it to erased or kept".
   • Listed as erased but not written by the named eraser: deletion-policy.spec.ts fails ("claims a column erasure only where the eraser writes it").

7. Format. npm run format in backend/; format:check is part of verify.

8. Regenerate the contract and the frontend types, from backend/:

   npx ts-node -r tsconfig-paths/register scripts/dump-openapi.ts
   cp openapi.runtime.json ../openapi.json
   cd ../frontend && npm run generate-types
   

   Commit openapi.json and frontend/types/api.d.ts with the change. Skip this and verify fails at the openapi stage ("runtime spec matches committed openapi.json"), or at the frontend's type drift check.

9. Use it in the frontend if a page shows it. The generated types already carry the field (item.priority), so a typo is a build error.

10. Run ./verify.sh. It applies every migration to a fresh database, so a migration that fails on an empty table fails here.

What the test run found

• With items.priority added end to end: verify all green.
• Adding users.timezone (nullable) without a policy entry: verify failed in deletion-policy.integration.spec.ts with the message above. Listing it as erased without touching ghostDelete failed deletion-policy.spec.ts. Adding timezone: null to the ghostDelete update made verify green again.

Related

• What Ghost Deletion Erases
• Define the persistence layer - the same conventions for a new table