Database Migrations Guide

Active Development

The schema changes frequently. New migrations are added with each significant feature. Always run make migrate-up after pulling new changes.

Aether uses golang-migrate for database schema management. The current schema has 42 migrations covering all entities.

Overview

• Migrations live in goway/migrations/
• Each migration has .up.sql (apply) and .down.sql (rollback) files
• Migrations run automatically on server startup — no manual step needed
• Manual control available via make commands

Prerequisites

Install golang-migrate CLI:

# macOS
brew install golang-migrate

# Linux
curl -L https://github.com/golang-migrate/migrate/releases/download/v4.17.0/migrate.linux-amd64.tar.gz | tar xvz
sudo mv migrate /usr/local/bin/

# Go install
go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

Basic Commands

cd goway

# Apply all pending migrations
make migrate-up

# Rollback the last migration
make migrate-down

# Check current migration version
migrate -path migrations -database "$DATABASE_URL" version

Creating New Migrations

1. Generate Migration Files

cd goway
migrate create -ext sql -dir migrations -seq your_migration_name

This creates two files:

• migrations/000XXX_your_migration_name.up.sql
• migrations/000XXX_your_migration_name.down.sql

2. Write the Up Migration

-- migrations/000017_add_user_preferences.up.sql

CREATE TABLE user_preferences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    theme VARCHAR(20) DEFAULT 'light',
    notifications_enabled BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_user_preferences_user_id ON user_preferences(user_id);

3. Write the Down Migration

-- migrations/000017_add_user_preferences.down.sql

DROP INDEX IF EXISTS idx_user_preferences_user_id;
DROP TABLE IF EXISTS user_preferences;

Best Practices

1. Always write both up and down migrations
2. Make migrations idempotent when possible (IF NOT EXISTS, IF EXISTS)
3. One logical change per migration - don't combine unrelated changes
4. Test rollbacks before committing
5. Never modify existing migrations that have been applied in production

Migration Workflow

Development

# 1. Create migration
migrate create -ext sql -dir migrations -seq add_feature_x

# 2. Write SQL in both files

# 3. Apply and test
make migrate-up

# 4. Test rollback
make migrate-down

# 5. Re-apply
make migrate-up

Production

Migrations run automatically when the Go server starts:

// cmd/server/main.go
if err := postgres.RunMigrations(cfg.DatabaseURL, cfg.MigrationsPath); err != nil {
    logger.Fatal("Failed to run database migrations", zap.Error(err))
}

Troubleshooting

Dirty Database State

If a migration fails partway through, the database may be in a "dirty" state:

error: Dirty database version 17. Fix and force version.

Solution:

1. Check what was applied:

   psql $DATABASE_URL -c "SELECT * FROM schema_migrations;"

2. Manually fix any partial changes

3. Force the version:

   # If migration 17 failed and you've fixed it manually:
   migrate -path migrations -database "$DATABASE_URL" force 17
   
   # Or rollback to previous version:
   migrate -path migrations -database "$DATABASE_URL" force 16

Migration Not Found

error: file does not exist

Causes:

• Wrong MIGRATIONS_PATH in environment
• Missing migration file

Solution:

# Check migrations directory
ls -la goway/migrations/

# Verify MIGRATIONS_PATH
echo $MIGRATIONS_PATH

Connection Refused

error: dial tcp 127.0.0.1:5432: connect: connection refused

Solution:

# Ensure PostgreSQL is running
docker-compose up -d postgres

# Wait for healthy status
docker-compose ps

# Verify DATABASE_URL
echo $DATABASE_URL

Version Mismatch

When the code expects a different schema version than what's in the database:

# Check current version
migrate -path migrations -database "$DATABASE_URL" version

# Apply missing migrations
make migrate-up

Advanced Usage

Migrate to Specific Version

# Migrate up to version 10
migrate -path migrations -database "$DATABASE_URL" goto 10

# Migrate down to version 5
migrate -path migrations -database "$DATABASE_URL" down 5

Reset Database

# Drop all tables and re-migrate (DESTRUCTIVE)
migrate -path migrations -database "$DATABASE_URL" drop -f
make migrate-up

View Migration History

-- Connect to database
psql $DATABASE_URL

-- Check schema_migrations table
SELECT * FROM schema_migrations;

File Naming Convention

NNNNNN_description.up.sql
NNNNNN_description.down.sql

• NNNNNN: 6-digit sequential number (generated by migrate create -seq)
• description: Snake_case description of the change
• Must have both .up.sql and .down.sql

Examples:

000001_create_users.up.sql
000001_create_users.down.sql
000002_add_user_email_index.up.sql
000002_add_user_email_index.down.sql
000003_create_projects.up.sql
000003_create_projects.down.sql

Current Migrations

The project currently has 42 migrations (000001–000042):

Range	What Was Added
001–003	Webhook events, event definitions, subscriptions
004–009	Agent roles, agents, projects, source mappings, project assignments
010–011	Agent memories, watchdog logging
012–014	Tasks, trigger → task mappings, source event → canonical trigger mappings
015–017	Workflows, dynamic integrations, integration assignments
018–021	Agent email field, poll sources, poll source state
022–024	Advanced webhook mappings (with jq filters), tool definitions, tool executions
025–031	Agent auth secrets, gRPC sessions, capabilities, audit logs, task execution tracking
032–035	pgvector extension, knowledge sources, document chunks, ingestion job tracking
036–042	Agent budgets, full-text search indexes, memory metadata extensions, MCP server config

# View all migration files
ls goway/migrations/*.up.sql

Each migration should be:

• Self-contained
• Reversible (both .up.sql and .down.sql)
• Documented with comments for complex changes