writing-migrations

name	writing-migrations
description	Creates and modifies PostgreSQL database migrations for xmtpd using golang-migrate. Use when adding or altering tables, columns, indexes, functions, triggers, constraints, or partitions, or when the user mentions migrations or schema changes.

Writing Database Migrations

Creating a Migration

Run dev/gen/migration {name} to produce:

• pkg/db/migrations/NNNNN_name.up.sql
• pkg/db/migrations/NNNNN_name.down.sql

5-digit sequential numbering. Hyphen-separated lowercase names (e.g., add-latest-block, payer-nonces, add-dead-letter-box).

How Migrations Run

• Embedded via //go:embed *.sql in pkg/db/migrations/migrations.go
• Uses golang-migrate with PostgreSQL driver
• Each migration runs in a transaction
• Tracked in schema_migrations table

SQL Formatting

Same as queries: tab indentation, UPPERCASE keywords/functions/types, lowercase identifiers.

Additional conventions:

• Comment at top explains purpose when non-obvious
• SET statement_timeout = 0 for long-running DDL (00006, 00018)
• IF NOT EXISTS / IF EXISTS for idempotency

Up Migration Patterns

CREATE TABLE

Project data type conventions:

Type	Usage
BYTEA	Binary data (keys, hashes, envelopes, topics)