Skip to content

Database Migrations

Database schema changes are managed with dbmate and written in raw PostgreSQL SQL.

Tooling & Commands

Migrations use dbmate and require both -- migrate:up and -- migrate:down sections:

# Apply pending migrations
dbmate up

# Inspect applied vs pending migrations
dbmate status

# Roll back the most recent migration
dbmate rollback

Environment variables are loaded from .env (copy from .env.example). Setting DATABASE_URL controls the target database.

Migration Directories

  • Production Migrations (infrastructure/db/migrations/): Production-grade migrations applied across all environments (local, dev, staging, production).
  • Development Migrations (infrastructure/db/migrations-dev/): Experimental features and private agent infrastructure (agent_control, agent_access). Local and dev environments execute both directories (DBMATE_MIGRATIONS_DIR=/db/migrations,/db/migrations-dev).
  • Resolved Schema (infrastructure/db/schema.sql): Automatically dumped by dbmate after running dbmate up. Commit this file whenever migrations change; do not edit it by hand.

Gateway-Shared Migrations

Files matching infrastructure/db/migrations/<version>_shared_*.sql form the database contract shared with local edge IoT gateways:

  • Strict Isolation: Shared migrations must never reference cloud-only application roles (platform_api_role, internal_api_role), internal service schemas (invoicing, site_maintenance), or manufacturing/billing workflows.
  • Immutability: Once an edge gateway image pins a shared migration version, that file is immutable. Future schema updates must be added as new shared migration files.
  • Verification: Run python3 scripts/check-gateway-shared-migrations.py to validate independence.

Migration Checklist

  1. Explicit Object Definitions: Use complete CREATE OR REPLACE statements in both up and down directions instead of dynamic catalog alteration (EXECUTE/pg_get_*def).
  2. Private Storage by Default: Tables live in domain schemas and are not granted directly to clients.
  3. Contracts in API Schemas: Expose read models as security-invoker views in api, enterprise_api, or internal_api. Workflow mutations are exposed as RPC functions.
  4. Privilege Revocation: Every new or replaced function must execute REVOKE EXECUTE ON FUNCTION ... FROM PUBLIC; before granting to authorized roles.
  5. Foreign Key Indexes: Every foreign key column must have a leading index usable by bare WHERE fk_col = $1 scans.
  6. Balanced Rollbacks: Every migration must provide a realistic down section. If a rollback is destructive, document it clearly in comments.
  7. Static Contract Linter: Run python3 scripts/check-sql-contracts.py --all before committing.