"""Schema repair via raw SQL — idempotent ADD COLUMN IF NOT EXISTS. 0035 was stamped before it ran on staging. This migration does the same job using op.execute(text(...)) with PostgreSQL's native IF NOT EXISTS syntax, which is the proven pattern already used in this migration chain. Revision ID: 0036 Revises: 0035 """ from __future__ import annotations from alembic import op from sqlalchemy import text revision = "0036" down_revision = "0035" branch_labels = None depends_on = None def upgrade() -> None: # musehub_commits — columns from migrations 0019, 0020, 0021 op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS agent_id VARCHAR(255) DEFAULT ''")) op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS model_id VARCHAR(255) DEFAULT ''")) op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS commit_branch VARCHAR(255)")) op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS signature TEXT DEFAULT ''")) op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS signer_public_key TEXT DEFAULT ''")) op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS signer_key_id VARCHAR(255) DEFAULT ''")) op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS toolchain_id VARCHAR(255) DEFAULT ''")) op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS sem_ver_bump VARCHAR(10) DEFAULT 'none'")) op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS breaking_changes TEXT[]")) op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS reviewed_by TEXT[]")) op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS test_runs INTEGER DEFAULT 0")) op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS prompt_hash VARCHAR(255) DEFAULT ''")) op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS structured_delta JSONB")) # musehub_symbol_history_entries — columns from 0018, 0031 op.execute(text("ALTER TABLE musehub_symbol_history_entries ADD COLUMN IF NOT EXISTS op_payload JSONB")) op.execute(text("ALTER TABLE musehub_symbol_history_entries ADD COLUMN IF NOT EXISTS message TEXT")) op.execute(text("ALTER TABLE musehub_symbol_history_entries ADD COLUMN IF NOT EXISTS commit_branch VARCHAR(512)")) # musehub_symbol_vitals — table from 0031, coupling_count from 0032 op.execute(text(""" CREATE TABLE IF NOT EXISTS musehub_symbol_vitals ( repo_id VARCHAR(128) NOT NULL REFERENCES musehub_repos(repo_id) ON DELETE CASCADE, address VARCHAR(512) NOT NULL, first_introduced TIMESTAMPTZ, change_count INTEGER NOT NULL DEFAULT 0, version_count INTEGER NOT NULL DEFAULT 0, op_add INTEGER NOT NULL DEFAULT 0, op_modify INTEGER NOT NULL DEFAULT 0, op_delete INTEGER NOT NULL DEFAULT 0, op_move INTEGER NOT NULL DEFAULT 0, coupling_count INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (repo_id, address) ) """)) op.execute(text("CREATE INDEX IF NOT EXISTS ix_symbol_vitals_repo ON musehub_symbol_vitals (repo_id)")) op.execute(text("ALTER TABLE musehub_symbol_vitals ADD COLUMN IF NOT EXISTS coupling_count INTEGER NOT NULL DEFAULT 0")) # musehub_symbol_coupling — table from 0031 op.execute(text(""" CREATE TABLE IF NOT EXISTS musehub_symbol_coupling ( repo_id VARCHAR(128) NOT NULL REFERENCES musehub_repos(repo_id) ON DELETE CASCADE, address VARCHAR(512) NOT NULL, co_address VARCHAR(512) NOT NULL, shared_commits INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (repo_id, address, co_address) ) """)) op.execute(text("CREATE INDEX IF NOT EXISTS ix_symbol_coupling_repo_address ON musehub_symbol_coupling (repo_id, address)")) # musehub_coord_records — rename record_uuid → record_id from 0027 op.execute(text(""" DO $$ BEGIN IF EXISTS ( SELECT 1 FROM information_schema.columns WHERE table_name='musehub_coord_records' AND column_name='record_uuid' ) AND NOT EXISTS ( SELECT 1 FROM information_schema.columns WHERE table_name='musehub_coord_records' AND column_name='record_id' ) THEN ALTER TABLE musehub_coord_records RENAME COLUMN record_uuid TO record_id; ALTER TABLE musehub_coord_records ALTER COLUMN record_id TYPE VARCHAR(128); END IF; END $$ """)) # musehub_file_last_commits — table from 0034 op.execute(text(""" CREATE TABLE IF NOT EXISTS musehub_file_last_commits ( repo_id VARCHAR(128) NOT NULL REFERENCES musehub_repos(repo_id) ON DELETE CASCADE, branch VARCHAR(255) NOT NULL, path TEXT NOT NULL, commit_id VARCHAR(128) NOT NULL, commit_message TEXT NOT NULL DEFAULT '', commit_author VARCHAR(255) NOT NULL DEFAULT '', commit_timestamp TIMESTAMPTZ NOT NULL, agent_id VARCHAR(128), model_id VARCHAR(128), PRIMARY KEY (repo_id, branch, path) ) """)) op.execute(text("CREATE INDEX IF NOT EXISTS ix_file_last_commits_repo_branch ON musehub_file_last_commits (repo_id, branch)")) def downgrade() -> None: # Drop tables this migration conditionally created (IF EXISTS — idempotent with owning migrations). op.execute(text("DROP INDEX IF EXISTS ix_symbol_coupling_repo_address")) op.execute(text("DROP TABLE IF EXISTS musehub_symbol_coupling")) op.execute(text("DROP INDEX IF EXISTS ix_symbol_vitals_repo")) op.execute(text("DROP TABLE IF EXISTS musehub_symbol_vitals")) op.execute(text("DROP INDEX IF EXISTS ix_file_last_commits_repo_branch")) op.execute(text("DROP TABLE IF EXISTS musehub_file_last_commits")) # Undo column repairs — all IF EXISTS so idempotent with owning migrations. for col in ("structured_delta", "prompt_hash", "test_runs", "reviewed_by", "breaking_changes", "sem_ver_bump", "toolchain_id", "signer_key_id", "signer_public_key", "signature", "commit_branch", "model_id", "agent_id"): op.execute(text(f"ALTER TABLE musehub_commits DROP COLUMN IF EXISTS {col}")) for col in ("commit_branch", "message", "op_payload"): op.execute(text(f"ALTER TABLE musehub_symbol_history_entries DROP COLUMN IF EXISTS {col}"))