create-migration

name	create-migration
description	Generate Alembic (Postgres) or ClickHouse migration following project conventions
disable-model-invocation	true

Create Migration

Generate a database migration for the dev-health-ops dual-database architecture.

Arguments

The user should specify:

1. Target database: postgres (Alembic) or clickhouse (raw SQL)
2. Description: What the migration does

Workflow

Step 1: Determine target database

Ask which database layer if not specified:

• Postgres (via Alembic): Semantic data — users, orgs, settings, credentials, RBAC, licensing, audit logs
• ClickHouse (raw SQL): Analytics data — commits, PRs, work items, metrics, materialized views

Step 2: Generate migration

For Postgres (Alembic)

1. Read the latest migration in src/dev_health_ops/alembic/versions/ to get the current revision ID
2. Run: cd dev-health-ops && python -m alembic revision -m "<description>"
3. Edit the generated file following this pattern:

"""<Description of what the migration does>.

Revision ID: <auto-generated>
Revises: <previous revision>
Create Date: <auto-generated>

Creates/Modifies tables for:
- <table_name>: <purpose>
"""

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import UUID


revision = "<auto>"
down_revision = "<previous>"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # Table creation / alteration here
    pass


def downgrade() -> None:
    # Reverse operations here
    pass

Conventions:

• Use UUID(as_uuid=True) for primary keys
• Include org_id column (Text, default "default") for multi-tenancy
• Include created_at and updated_at DateTime(timezone=True) columns
• Use sa.Text() instead of sa.String() for text columns

For ClickHouse (raw SQL)

1. List files in src/dev_health_ops/migrations/clickhouse/ to determine the next number
2. Create a new file: src/dev_health_ops/migrations/clickhouse/<NNN>_<description>.sql
3. Follow this pattern:

-- <NNN>_<description>.sql
-- <Explanation of what this migration does and why>
--
-- Tables affected:
--   - <table_name>: <what changes>

CREATE TABLE IF NOT EXISTS <table_name> (
    -- columns
) ENGINE = ReplacingMergeTree()  -- or AggregatingMergeTree() for rollups
ORDER BY (<key columns>);

Conventions:

• Use ReplacingMergeTree() for mutable entity tables (PRs, work items)
• Use AggregatingMergeTree() with PARTITION BY toYYYYMM(day) for daily rollup materialized views
• Use SimpleAggregateFunction(sum, UInt64) for counter columns in rollups
• Include comments explaining which data is safe for materialization (immutable) vs not (mutable)
• SQL files are numbered sequentially (000, 001, ..., 026, ...)
• Python migration scripts (like 010_rename_mergestat_synced_at.py) are used for data migrations requiring logic

Step 3: Verify

• For Postgres: Run cd dev-health-ops && python -m alembic heads to verify no branch conflicts
• For ClickHouse: Check the SQL is syntactically valid