Alembic Migrations — Agent Rules

Parent rules: /workspace/backend/agents.md

Generating Migrations

• Generate via make migrate-new (or alembic revision --autogenerate -m "description").
• Never hand-edit DDL or write raw SQL in migration files unless autogenerate cannot handle the change.

Migration Requirements

• Every migration must include both upgrade() and downgrade() functions.
• Use descriptive revision messages: "add widget config_overrides column", not "update".
• Test both upgrade and downgrade paths.

Scope

• Migrations apply to PostgreSQL only — application metadata tables.
• Never write migrations for ClickHouse, Materialize, or Redis.

Adding New Models

When adding a new SQLAlchemy model:

1. Create the model in app/models/.
2. Import the model in alembic/env.py so autogenerate detects it.
3. Run make migrate-new to generate the migration.
4. Review the generated migration for correctness.
5. Apply with make migrate-up.

Multi-Tenancy Migration Rules

• All tenant-scoped tables (users, workflows, dashboards, api_keys) MUST have a tenant_id UUID NOT NULL column.
• Every tenant_id column MUST have an index (for query performance on filtered reads).
• Consider composite indexes on (tenant_id, id) or (tenant_id, created_at) for common query patterns.
• When adding tenant_id to existing tables, the migration must provide a default value for existing rows or use a data migration to backfill.
• Child tables that inherit tenant scope from a parent (widgets via dashboards, dashboard_filters via dashboards) do NOT get their own tenant_id column.
• PostgreSQL Row-Level Security (RLS) policies should be added as a defense-in-depth backstop: CREATE POLICY tenant_isolation ON <table> USING (tenant_id = current_setting('app.current_tenant')::uuid). These are safety nets — the application layer is the primary enforcement mechanism.