Databases that survive

A schema outlives the application that first created it. Treat it with the same care as your API contract.

Migrations beat AutoMigrateCopy Anchor Link

Dropping a table because a struct changed is fine in week one and dangerous in week forty. Version your schema:

-- 001_create_users.up.sql
CREATE TABLE users (
    id       uuid PRIMARY KEY,
    email    text NOT NULL UNIQUE,
    status   text NOT NULL DEFAULT 'PENDING_VERIFICATION'
);

Apply migrations in dependency order, track which ran, and never edit an already-applied migration — write a new one.

Index what you queryCopy Anchor Link

Start with the primary key, then add indexes for the filters your API actually exposes. For a user directory you drill into by status and role, those earn indexes; columns you never filter on do not.

CREATE INDEX idx_users_status ON users (status);
CREATE INDEX idx_users_role   ON users (role);

The transaction instinctCopy Anchor Link

Any action that spans two writes — an audit log plus a user update, a member plus a cohort change — belongs in a transaction. Either both appear or neither does; the audit trail must never lie.

Concurrency for the 95% caseCopy Anchor Link

You will not need exotic isolation levels. You will need:

• NOT NULL + constraints so bad input fails loudly.
• Timestamps you trust (created_at, updated_at).
• A WHERE clause that matches exactly that row you intend to touch.