Skip to main content

​
Migrations

Tracera uses a simple file-based migration system. SQL files in the migrations/ directory are executed in order on server startup.

​
How It Works

  1. On startup, the server reads all .sql files from migrations/
  2. Files are sorted by filename (numeric prefix)
  3. Each migration is applied in a transaction
  4. Already-applied migrations are tracked and skipped

​
Current Migrations

FileDescription
001_auth.sqlCreates users and auth_providers tables with indexes and triggers
002_auth_provider_user_provider_unique.sqlAdds unique constraint on (user_id, provider) and deduplicates legacy rows

​
Naming Convention

Migrations follow a strict naming convention: 
{sequence_number}_{description}.sql
  • Sequence number — zero-padded three-digit number (e.g., 001, 002, 003)
  • Description — snake_case description of the change
  • Strict sequence continuity — no gaps allowed between sequence numbers
Examples: 
001_auth.sql
002_auth_provider_user_provider_unique.sql
003_items_and_prices.sql
004_portfolio.sql

​
Validation

The make migration-check target validates:
  • Filename convention is followed
  • Sequence numbers are continuous (no gaps)
  • No merge conflict markers exist in migration files
make migration-check

​
Writing Migrations

​
Do’s

  • Use IF NOT EXISTS / IF EXISTS for idempotent operations
  • Wrap destructive operations in transactions
  • Test migrations against a real database with make db-check
  • Include DOWN logic as comments for documentation (not auto-executed)

​
Don’ts

  • Never modify an existing migration file after it’s been applied in production
  • Never delete migration files
  • Never leave gaps in sequence numbers
  • Avoid long-running locks (e.g., adding indexes on large tables without CONCURRENTLY)

​
Testing Migrations

# Validate migration files
make migration-check

# Test against a real TimescaleDB instance (Docker-based)
make db-check
The db-check target spins up a temporary TimescaleDB container, applies all migrations, and validates the resulting schema.