Writing a database migration

A migration is a numbered SQL file that changes Hardcount’s database schema. This page is for contributors. It shows how to add one in three steps, the two shapes a file can take, and the rules CI checks before any database is involved.

Hardcount applies migrations with goose. Migration 180 is the starting schema in one file, baseline/00180_baseline.sql. It runs only on an empty database. New files go in internal/platform/migrate/sql/ and start at 00181.

Hardcount was called Velox before v0.9.0. v0.9.0 installs fresh only: it refuses a database made by Velox, a 0.8.x release or a build of main (why).

Add a migration in three steps

Step 3 needs the local Postgres from CONTRIBUTING (docker compose up -d postgres).

1. Create the file. Fetch first, so the number counts every merged file:

   git fetch origin
   make migration name=add_invoice_purchase_order

   You see:

   Wrote internal/platform/migrate/sql/00181_add_invoice_purchase_order.sql
   The highest number before it was 00180, in the baseline.

   The number is one above the highest file in any worktree of your clone or on any branch you have fetched. For an index, add concurrent=1 and use the index’s name: make migration name=idx_invoices_purchase_order concurrent=1 (see An index build).

2. Write the Up and the Down, then run the checks. The Up makes the change, and the Down undoes it. Replace the TODO lines:

   -- +goose Up
   -- Invoices keep the purchase-order number the buyer gives.
   ALTER TABLE invoices ADD COLUMN purchase_order TEXT;
   
   -- +goose Down
   ALTER TABLE invoices DROP COLUMN purchase_order;

   go test -short ./internal/platform/migrate/

   You see:

   ok    github.com/gethardcount/hardcount/internal/platform/migrate  0.031s

   A file that breaks a rule fails with its name and the reason. Before you replace the TODO lines, for example:

   lint_test.go:30: sql/00181_add_invoice_purchase_order.sql: the Up section has no statement

3. Apply it to your local database, roll it back, and apply it again. This proves the Down undoes the Up.

   export DATABASE_URL="postgres://hardcount:hardcount@localhost:5432/hardcount?sslmode=disable"
   go run ./cmd/hardcount migrate status
   go run ./cmd/hardcount migrate
   go run ./cmd/hardcount migrate rollback
   go run ./cmd/hardcount migrate

   You see (on an empty database, so the baseline runs first):

   $ go run ./cmd/hardcount migrate status
   database:     empty
   this release: 181 (hardcount dev)
   state:        empty, 2 migrations to apply
     00180 baseline
     00181_add_invoice_purchase_order.sql
   running now:  no
   next: take a backup, then run "hardcount migrate". A server started with RUN_MIGRATIONS_ON_BOOT=true runs it as it starts.
   $ go run ./cmd/hardcount migrate
   2026/10/18 01:00:33 INFO migration applied migration="00180 baseline" version=180 duration_ms=493
   2026/10/18 01:00:33 INFO migration applied migration=00181_add_invoice_purchase_order.sql version=181 duration_ms=4
   2026/10/18 01:00:33 INFO migrations applied version=181 applied=2 duration_ms=507
   migrations applied successfully
   $ go run ./cmd/hardcount migrate rollback
   rolled back to version 180
   $ go run ./cmd/hardcount migrate
   2026/10/18 01:00:33 INFO migration applied migration=00181_add_invoice_purchase_order.sql version=181 duration_ms=2
   2026/10/18 01:00:33 INFO migrations applied version=181 applied=1 duration_ms=5
   migrations applied successfully

   hardcount migrate rollback undoes one migration at a time, and only when APP_ENV is local (the default when it is unset). It never goes below 180.

Then commit the file with the code that uses it. CI applies every migration to an empty database under three role setups, and checks that a second run has nothing to do.

The two shapes of a file

A normal file

goose runs the whole file in one transaction, together with the row that records it as applied. If any statement fails, Postgres undoes the whole file and nothing is recorded: fix the cause and run it again.

• No transaction control. No BEGIN, COMMIT, ROLLBACK, SAVEPOINT or END. A COMMIT would make part of the file permanent with no record of it.

• SET LOCAL, never SET. goose runs every file of one hardcount migrate on the same database connection. A plain SET, a RESET or set_config(..., false) would carry into the files after this one. SET LOCAL ends with the file’s transaction.

• Put a multi-line $$ body between two annotation lines, as below. goose ends a statement at every line that ends in ;, so without them it cuts a function body apart, and Postgres rejects the pieces.

  -- +goose StatementBegin
  CREATE FUNCTION touch_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
  BEGIN
    NEW.updated_at := now();
    RETURN NEW;
  END
  $$;
  -- +goose StatementEnd

• Keep +goose out of comments. goose reads any comment line that contains it as an annotation, and fails on one it does not know.

An index build

CREATE INDEX CONCURRENTLY builds an index without blocking writes to the table, but it cannot run inside a transaction. It is the only statement that goes in a file without one, and that file has exactly this shape:

-- +goose NO TRANSACTION
-- +goose Up
SET lock_timeout = 0;
DROP INDEX CONCURRENTLY IF EXISTS idx_invoices_purchase_order;
CREATE INDEX CONCURRENTLY idx_invoices_purchase_order ON invoices (purchase_order);
RESET lock_timeout;
-- +goose Down
SET lock_timeout = 0;
DROP INDEX CONCURRENTLY IF EXISTS idx_invoices_purchase_order;
RESET lock_timeout;

make migration name=idx_invoices_purchase_order concurrent=1 writes it, and you replace your_table (your_column). UNIQUE may follow CREATE. One index goes in each file, with a name no earlier migration used, and never with IF NOT EXISTS. Each statement stays on a line of its own: goose sends this file one statement at a time, and two statements sent together would run in a transaction.

Why each line is there:

• The DROP before the build. goose records a file without a transaction only after the whole file has run. If Hardcount stops between the build and that record, the index exists but the file is not recorded. The next run drops the index and builds it again, so running the file again is always safe. This is also why the name must be new: the DROP would remove any older index of that name.
• No IF NOT EXISTS. A failed build can leave an INVALID index, one Postgres keeps but never uses. IF NOT EXISTS would find it, skip the build, and record the file as done.
• SET lock_timeout = 0, then RESET. A concurrent build waits for every transaction that started before it. The usual limit on lock waits (MIGRATION_LOCK_TIMEOUT, 5 seconds by default) would cancel it, so the file lifts the limit for the build and puts it back for the files after it.

A file that only drops an index swaps the two sections: its Up is SET, DROP, RESET, and its Down builds the index again.

Common changes

Every migration must work on a table that already holds millions of rows, while Hardcount keeps serving: installs upgrade with their data in place.

• Add a column. ADD COLUMN purchase_order TEXT, or NOT NULL DEFAULT with a constant or now(). Both are instant: Postgres stores the default without rewriting the table. Two other forms fail the checks:
  • NOT NULL with no DEFAULT. Postgres refuses it on a table that has rows, so the upgrade stops on every install with data. Give the column a constant default. Or add it nullable, fill it, and then make it NOT NULL as described below.
  • A value computed for each row. This means a default that calls a volatile function (one that can return a new value on each call, such as gen_random_uuid()), a SERIAL type, an identity column or a stored generated column. Postgres fills the old rows by rewriting the whole table, and every read and write waits until it ends. For a default, add the column with no default, then run ALTER COLUMN ... SET DEFAULT gen_random_uuid() in the same file. Only new rows use it. The check accepts a default that calls only now(), transaction_timestamp(), statement_timestamp(), CURRENT_TIMESTAMP, CURRENT_TIME, LOCALTIMESTAMP, LOCALTIME, encode(), CAST, COALESCE, NULLIF, GREATEST and LEAST. Any other function might be volatile, clock_timestamp() among them. If the one you call is IMMUTABLE or STABLE, add the opt-out line and say so.
• Add an index. Use an index build.
• Add a CHECK or a foreign key. Write ADD CONSTRAINT ... NOT VALID in one file, then VALIDATE CONSTRAINT in the next file. NOT VALID checks only new rows, so it is instant. VALIDATE checks the old rows without blocking writes, but only in a file of its own. In the same file, the ADD’s lock is still held while every row is checked.
• Add a UNIQUE constraint. Build the index with CREATE UNIQUE INDEX CONCURRENTLY, then write ADD CONSTRAINT ... UNIQUE USING INDEX ... in the next file.
• Make a column NOT NULL.
  1. One file adds CHECK (col IS NOT NULL) NOT VALID.
  2. The next file runs VALIDATE CONSTRAINT, then SET NOT NULL, then drops the CHECK. Postgres trusts the valid CHECK and skips the scan. This file needs the opt-out line, because the check cannot see the CHECK.
• Add a value to a Go enum stored in a column. Change the column’s CHECK constraint in the same PR. A value the CHECK does not list fails every insert that uses it. Drop the old constraint and add the new one NOT VALID, then VALIDATE it in the next file.
• Insert rows into a table with a livemode column. Run SET LOCAL app.livemode = 'on' (live rows) or 'off' (test rows) first. The table’s set_livemode trigger stamps each new row’s mode from that setting, and refuses the insert when it is unset.

Never edit a file once it is merged. A database that already ran it has recorded its number and never runs it again, so an edit reaches only new installs. Write a new file instead.

What the checks enforce

go test -short ./internal/platform/migrate/ runs these on every file in sql/. CI runs them in the unit job. The checks are in lint_test.go.

Rule	Why
The name is NNNNN_name.sql: five digits, an underscore, lowercase letters, digits and underscores, directly in sql/	goose skips many other names without a word, so the file would never run
Numbers go up by one from 00181, with no gap or duplicate	A file merged since your branch started shows up here as a duplicate when you rebase, so you renumber before merging
Exactly one -- +goose Up line, then one -- +goose Down line, each followed by at least one statement	The Down is what hardcount migrate rollback runs while you develop the file
Annotations are only Up, Down, StatementBegin, StatementEnd and NO TRANSACTION	ENVSUB would make the SQL depend on the environment of whoever runs it
Every statement ends with ;, and a multi-line $$ body sits between StatementBegin and StatementEnd	goose cuts statements at lines that end in ;
A normal file has no transaction control and uses only SET LOCAL	See A normal file
A file with NO TRANSACTION has exactly the index-build shape, one statement per line, with a new index name; a drop names an index that exists	See An index build
No statement that blocks writes on an existing table: a plain CREATE INDEX, an ADD CONSTRAINT without NOT VALID or USING INDEX, SET NOT NULL, a VALIDATE after another change to the same table in the file, or an ADD COLUMN whose value is computed for each row	On a large table, the lock stops every write until the statement ends. A table the same file creates is exempt
An ADD COLUMN ... NOT NULL on an existing table has a DEFAULT	Without one, Postgres refuses the column on a table with rows, and the upgrade stops. A table the same file creates is exempt. The opt-out line below does not cover this rule

When blocking is safe, for example on a table known to stay small, say so in the file. The reason must be at least 15 characters:

-- hardcount:blocking-ddl-ok: fx_rates has under 200 rows on every install

Related

ADR-125 records why Hardcount uses goose with one baseline. Operators whose upgrade stopped start at the runbook.