gabriel / musehub public
0036_schema_repair_sql.py python
122 lines 6.4 KB
Raw
sha256:94ef169c149a452bff7c604ded8b280b19bd477c2dabcb56972780b0b784c7aa Merge 'fix/assignee-sigil-inline' into 'dev' — proposal: As… Human 2 days ago
1 """Schema repair via raw SQL — idempotent ADD COLUMN IF NOT EXISTS.
2
3 0035 was stamped before it ran on staging. This migration does the same
4 job using op.execute(text(...)) with PostgreSQL's native IF NOT EXISTS
5 syntax, which is the proven pattern already used in this migration chain.
6
7 Revision ID: 0036
8 Revises: 0035
9 """
10 from __future__ import annotations
11
12 from alembic import op
13 from sqlalchemy import text
14
15 revision = "0036"
16 down_revision = "0035"
17 branch_labels = None
18 depends_on = None
19
20
21 def upgrade() -> None:
22 # musehub_commits — columns from migrations 0019, 0020, 0021
23 op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS agent_id VARCHAR(255) DEFAULT ''"))
24 op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS model_id VARCHAR(255) DEFAULT ''"))
25 op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS commit_branch VARCHAR(255)"))
26 op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS signature TEXT DEFAULT ''"))
27 op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS signer_public_key TEXT DEFAULT ''"))
28 op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS signer_key_id VARCHAR(255) DEFAULT ''"))
29 op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS toolchain_id VARCHAR(255) DEFAULT ''"))
30 op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS sem_ver_bump VARCHAR(10) DEFAULT 'none'"))
31 op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS breaking_changes TEXT[]"))
32 op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS reviewed_by TEXT[]"))
33 op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS test_runs INTEGER DEFAULT 0"))
34 op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS prompt_hash VARCHAR(255) DEFAULT ''"))
35 op.execute(text("ALTER TABLE musehub_commits ADD COLUMN IF NOT EXISTS structured_delta JSONB"))
36
37 # musehub_symbol_history_entries — columns from 0018, 0031
38 op.execute(text("ALTER TABLE musehub_symbol_history_entries ADD COLUMN IF NOT EXISTS op_payload JSONB"))
39 op.execute(text("ALTER TABLE musehub_symbol_history_entries ADD COLUMN IF NOT EXISTS message TEXT"))
40 op.execute(text("ALTER TABLE musehub_symbol_history_entries ADD COLUMN IF NOT EXISTS commit_branch VARCHAR(512)"))
41
42 # musehub_symbol_vitals — table from 0031, coupling_count from 0032
43 op.execute(text("""
44 CREATE TABLE IF NOT EXISTS musehub_symbol_vitals (
45 repo_id VARCHAR(128) NOT NULL REFERENCES musehub_repos(repo_id) ON DELETE CASCADE,
46 address VARCHAR(512) NOT NULL,
47 first_introduced TIMESTAMPTZ,
48 change_count INTEGER NOT NULL DEFAULT 0,
49 version_count INTEGER NOT NULL DEFAULT 0,
50 op_add INTEGER NOT NULL DEFAULT 0,
51 op_modify INTEGER NOT NULL DEFAULT 0,
52 op_delete INTEGER NOT NULL DEFAULT 0,
53 op_move INTEGER NOT NULL DEFAULT 0,
54 coupling_count INTEGER NOT NULL DEFAULT 0,
55 PRIMARY KEY (repo_id, address)
56 )
57 """))
58 op.execute(text("CREATE INDEX IF NOT EXISTS ix_symbol_vitals_repo ON musehub_symbol_vitals (repo_id)"))
59 op.execute(text("ALTER TABLE musehub_symbol_vitals ADD COLUMN IF NOT EXISTS coupling_count INTEGER NOT NULL DEFAULT 0"))
60
61 # musehub_symbol_coupling — table from 0031
62 op.execute(text("""
63 CREATE TABLE IF NOT EXISTS musehub_symbol_coupling (
64 repo_id VARCHAR(128) NOT NULL REFERENCES musehub_repos(repo_id) ON DELETE CASCADE,
65 address VARCHAR(512) NOT NULL,
66 co_address VARCHAR(512) NOT NULL,
67 shared_commits INTEGER NOT NULL DEFAULT 0,
68 PRIMARY KEY (repo_id, address, co_address)
69 )
70 """))
71 op.execute(text("CREATE INDEX IF NOT EXISTS ix_symbol_coupling_repo_address ON musehub_symbol_coupling (repo_id, address)"))
72
73 # musehub_coord_records — rename record_uuid → record_id from 0027
74 op.execute(text("""
75 DO $$
76 BEGIN
77 IF EXISTS (
78 SELECT 1 FROM information_schema.columns
79 WHERE table_name='musehub_coord_records' AND column_name='record_uuid'
80 ) AND NOT EXISTS (
81 SELECT 1 FROM information_schema.columns
82 WHERE table_name='musehub_coord_records' AND column_name='record_id'
83 ) THEN
84 ALTER TABLE musehub_coord_records RENAME COLUMN record_uuid TO record_id;
85 ALTER TABLE musehub_coord_records ALTER COLUMN record_id TYPE VARCHAR(128);
86 END IF;
87 END $$
88 """))
89
90 # musehub_file_last_commits — table from 0034
91 op.execute(text("""
92 CREATE TABLE IF NOT EXISTS musehub_file_last_commits (
93 repo_id VARCHAR(128) NOT NULL REFERENCES musehub_repos(repo_id) ON DELETE CASCADE,
94 branch VARCHAR(255) NOT NULL,
95 path TEXT NOT NULL,
96 commit_id VARCHAR(128) NOT NULL,
97 commit_message TEXT NOT NULL DEFAULT '',
98 commit_author VARCHAR(255) NOT NULL DEFAULT '',
99 commit_timestamp TIMESTAMPTZ NOT NULL,
100 agent_id VARCHAR(128),
101 model_id VARCHAR(128),
102 PRIMARY KEY (repo_id, branch, path)
103 )
104 """))
105 op.execute(text("CREATE INDEX IF NOT EXISTS ix_file_last_commits_repo_branch ON musehub_file_last_commits (repo_id, branch)"))
106
107
108 def downgrade() -> None:
109 # Drop tables this migration conditionally created (IF EXISTS — idempotent with owning migrations).
110 op.execute(text("DROP INDEX IF EXISTS ix_symbol_coupling_repo_address"))
111 op.execute(text("DROP TABLE IF EXISTS musehub_symbol_coupling"))
112 op.execute(text("DROP INDEX IF EXISTS ix_symbol_vitals_repo"))
113 op.execute(text("DROP TABLE IF EXISTS musehub_symbol_vitals"))
114 op.execute(text("DROP INDEX IF EXISTS ix_file_last_commits_repo_branch"))
115 op.execute(text("DROP TABLE IF EXISTS musehub_file_last_commits"))
116 # Undo column repairs — all IF EXISTS so idempotent with owning migrations.
117 for col in ("structured_delta", "prompt_hash", "test_runs", "reviewed_by",
118 "breaking_changes", "sem_ver_bump", "toolchain_id", "signer_key_id",
119 "signer_public_key", "signature", "commit_branch", "model_id", "agent_id"):
120 op.execute(text(f"ALTER TABLE musehub_commits DROP COLUMN IF EXISTS {col}"))
121 for col in ("commit_branch", "message", "op_payload"):
122 op.execute(text(f"ALTER TABLE musehub_symbol_history_entries DROP COLUMN IF EXISTS {col}"))
File History 3 commits
sha256:94ef169c149a452bff7c604ded8b280b19bd477c2dabcb56972780b0b784c7aa Merge 'fix/assignee-sigil-inline' into 'dev' — proposal: As… Human 2 days ago
sha256:6b1949fc2797ca4c1936a637a4cbfec828ef56cf52398a2e74ca3c4f494e728f fix: use wire_bytes not mpack_bytes_raw in compute_object_b… Sonnet 4.6 patch 10 days ago
sha256:4aed3d8601c8dd3ed37074de35f11f4a9699a0a4b99d43727048fd3f8e6fd13d chore: doc sweep, ignore wrangler build state, misc fixes Sonnet 4.6 minor ⚠ 13 days ago