Database Migration#

Migration Files#

Create Migration#

Create a SQL file in api/src/migrations/user/, named by date:

sql-- api/src/migrations/user/20260605.sql
CREATE TABLE IF NOT EXISTS todos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  title VARCHAR(255) NOT NULL,
  completed BOOLEAN DEFAULT false,
  created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,
  updated_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT
);

Run Migration#

bashnpm run db:migrate

How It Works#

The migration script (api/src/scripts/migrate.ts):

1. Creates a migrations table (tracks executed migrations)
2. Reads all .sql files sorted by filename
3. Skips already-executed ones, runs new ones
4. Core migrations (core/) run first, then business migrations (user/)

Conventions#

• Use CREATE TABLE IF NOT EXISTS for idempotency
• Use ALTER TABLE ... ADD COLUMN IF NOT EXISTS for new columns
• Timestamps: BIGINT (Unix epoch seconds)
• IDs: UUID
• Business tables must have a user_id column (data isolation)
• Indexes: CREATE INDEX IF NOT EXISTS
• Data seeding: ON CONFLICT DO NOTHING
• Amounts: INTEGER in cents