gabriel / musehub public
0001_consolidated_schema.py python
1249 lines 66.5 KB
Raw
cd448303 Initial extraction of MuseHub from maestro monorepo. Gabriel Cardona <gabriel@tellurstori.com> 7d ago
1 """Consolidated schema — all tables, single migration.
2
3 Revision ID: 0001
4 Revises:
5 Create Date: 2026-02-27 00:00:00.000000
6
7 THIS IS THE ONLY MIGRATION. All new tables are folded in here during
8 development. Do NOT create new migration files — add tables directly to
9 upgrade() and their drops (in reverse order) to the top of downgrade().
10
11 Single source-of-truth migration for Maestro. Creates:
12
13 Auth & usage
14 - maestro_users, maestro_usage_logs, maestro_access_tokens
15
16 Conversations
17 - maestro_conversations, maestro_conversation_messages, maestro_message_actions
18
19 Muse — DAW-level variation history
20 - muse_variations, muse_phrases, muse_note_changes
21
22 Muse — filesystem commit history
23 - muse_objects, muse_snapshots, muse_commits
24 (includes parent2_commit_id for merge commits; metadata JSON blob for
25 commit-level annotations e.g. tempo_bpm set via ``muse tempo --set``)
26 - muse_tags (music-semantic tags attached to commits)
27
28 Muse Hub — remote collaboration backend
29 - musehub_repos, musehub_branches, musehub_commits, musehub_issues
30 - musehub_issue_milestones (many-to-many join: issues ↔ milestones)
31 - musehub_pull_requests (PR workflow; merged_at records exact merge timestamp)
32 - musehub_pr_comments (inline review comments on musical diffs within PRs)
33 - musehub_objects (content-addressed binary artifact storage)
34 - musehub_stars (per-user repo starring for the explore/discover page)
35 - musehub_profiles (public user profile pages — bio, avatar, pinned repos)
36 - musehub_sessions (recording session metadata — participants, intent, commits)
37 - musehub_releases (published version releases with download packages)
38 - musehub_release_assets (downloadable file attachments per release with download counts)
39 - musehub_webhooks (registered event-driven webhook subscriptions)
40 - musehub_webhook_deliveries (delivery log per dispatch attempt; payload column stores JSON body for retry)
41 - musehub_render_jobs (async audio render pipeline)
42 - musehub_comments, musehub_reactions, musehub_follows, musehub_watches
43 - musehub_notifications, musehub_forks, musehub_view_events, musehub_download_events
44 - musehub_events (activity event stream)
45 - musehub_labels, musehub_issue_labels, musehub_pr_labels (label tagging)
46 - musehub_collaborators (repo access control beyond owner)
47 - musehub_stash, musehub_stash_entries (git-stash-style temporary shelving)
48 - musehub_pr_reviews (reviewer assignment and approval tracking per PR)
49 - musehub_repos.settings (nullable JSON column for feature-flag settings)
50
51 Fresh install:
52 docker compose exec maestro alembic upgrade head
53 """
54 from __future__ import annotations
55
56 import sqlalchemy as sa
57 from alembic import op
58 from sqlalchemy.dialects import postgresql
59
60 revision = "0001"
61 down_revision = None
62 branch_labels = None
63 depends_on = None
64
65
66 def upgrade() -> None:
67 # ── Users & auth ──────────────────────────────────────────────────────
68 op.create_table(
69 "maestro_users",
70 sa.Column("id", sa.String(36), nullable=False),
71 sa.Column("budget_cents", sa.Integer(), nullable=False, server_default="500"),
72 sa.Column("budget_limit_cents", sa.Integer(), nullable=False, server_default="500"),
73 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
74 sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
75 sa.PrimaryKeyConstraint("id"),
76 )
77
78 op.create_table(
79 "maestro_usage_logs",
80 sa.Column("id", sa.String(36), nullable=False),
81 sa.Column("user_id", sa.String(36), nullable=False),
82 sa.Column("prompt", sa.Text(), nullable=True),
83 sa.Column("model", sa.String(100), nullable=False),
84 sa.Column("prompt_tokens", sa.Integer(), nullable=False, server_default="0"),
85 sa.Column("completion_tokens", sa.Integer(), nullable=False, server_default="0"),
86 sa.Column("cost_cents", sa.Integer(), nullable=False, server_default="0"),
87 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
88 sa.ForeignKeyConstraint(["user_id"], ["maestro_users.id"], ondelete="CASCADE"),
89 sa.PrimaryKeyConstraint("id"),
90 )
91 op.create_index("ix_maestro_usage_logs_user_id", "maestro_usage_logs", ["user_id"])
92 op.create_index("ix_maestro_usage_logs_created_at", "maestro_usage_logs", ["created_at"])
93
94 op.create_table(
95 "maestro_access_tokens",
96 sa.Column("id", sa.String(36), nullable=False),
97 sa.Column("user_id", sa.String(36), nullable=False),
98 sa.Column("token_hash", sa.String(64), nullable=False),
99 sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
100 sa.Column("revoked", sa.Boolean(), nullable=False, server_default="false"),
101 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
102 sa.ForeignKeyConstraint(["user_id"], ["maestro_users.id"], ondelete="CASCADE"),
103 sa.PrimaryKeyConstraint("id"),
104 )
105 op.create_index("ix_maestro_access_tokens_user_id", "maestro_access_tokens", ["user_id"])
106 op.create_index("ix_maestro_access_tokens_token_hash", "maestro_access_tokens", ["token_hash"], unique=True)
107
108 # ── Conversations ─────────────────────────────────────────────────────
109 op.create_table(
110 "maestro_conversations",
111 sa.Column("id", sa.String(36), nullable=False),
112 sa.Column("user_id", sa.String(36), nullable=False),
113 sa.Column("project_id", sa.String(36), nullable=True),
114 sa.Column("title", sa.String(255), nullable=False, server_default="New Conversation"),
115 sa.Column("is_archived", sa.Boolean(), nullable=False, server_default="false"),
116 sa.Column("project_context", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
117 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
118 sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
119 sa.ForeignKeyConstraint(["user_id"], ["maestro_users.id"], ondelete="CASCADE"),
120 sa.PrimaryKeyConstraint("id"),
121 )
122 op.create_index("ix_maestro_conversations_user_id", "maestro_conversations", ["user_id"])
123 op.create_index("ix_maestro_conversations_project_id", "maestro_conversations", ["project_id"])
124 op.create_index("ix_maestro_conversations_is_archived", "maestro_conversations", ["is_archived"])
125 op.create_index("ix_maestro_conversations_updated_at", "maestro_conversations", ["updated_at"])
126
127 op.create_table(
128 "maestro_conversation_messages",
129 sa.Column("id", sa.String(36), nullable=False),
130 sa.Column("conversation_id", sa.String(36), nullable=False),
131 sa.Column("role", sa.String(20), nullable=False),
132 sa.Column("content", sa.Text(), nullable=False),
133 sa.Column("model_used", sa.String(100), nullable=True),
134 sa.Column("tokens_used", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
135 sa.Column("cost_cents", sa.Integer(), nullable=False, server_default="0"),
136 sa.Column("tool_calls", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
137 sa.Column("sse_events", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
138 sa.Column("extra_metadata", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
139 sa.Column("timestamp", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
140 sa.ForeignKeyConstraint(["conversation_id"], ["maestro_conversations.id"], ondelete="CASCADE"),
141 sa.PrimaryKeyConstraint("id"),
142 )
143 op.create_index("ix_maestro_conversation_messages_conversation_id", "maestro_conversation_messages", ["conversation_id"])
144 op.create_index("ix_maestro_conversation_messages_timestamp", "maestro_conversation_messages", ["timestamp"])
145
146 op.create_table(
147 "maestro_message_actions",
148 sa.Column("id", sa.String(36), nullable=False),
149 sa.Column("message_id", sa.String(36), nullable=False),
150 sa.Column("action_type", sa.String(50), nullable=False),
151 sa.Column("description", sa.Text(), nullable=False),
152 sa.Column("success", sa.Boolean(), nullable=False),
153 sa.Column("error_message", sa.Text(), nullable=True),
154 sa.Column("extra_metadata", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
155 sa.Column("timestamp", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
156 sa.ForeignKeyConstraint(["message_id"], ["maestro_conversation_messages.id"], ondelete="CASCADE"),
157 sa.PrimaryKeyConstraint("id"),
158 )
159 op.create_index("ix_maestro_message_actions_message_id", "maestro_message_actions", ["message_id"])
160
161 # ── Muse VCS — DAW-level variation history ────────────────────────────
162 op.create_table(
163 "muse_variations",
164 sa.Column("variation_id", sa.String(36), nullable=False),
165 sa.Column("project_id", sa.String(36), nullable=False),
166 sa.Column("base_state_id", sa.String(36), nullable=False),
167 sa.Column("conversation_id", sa.String(36), nullable=False, server_default=""),
168 sa.Column("intent", sa.Text(), nullable=False),
169 sa.Column("explanation", sa.Text(), nullable=True),
170 sa.Column("status", sa.String(20), nullable=False, server_default="created"),
171 sa.Column("affected_tracks", sa.JSON(), nullable=True),
172 sa.Column("affected_regions", sa.JSON(), nullable=True),
173 sa.Column("beat_range_start", sa.Float(), nullable=False, server_default="0"),
174 sa.Column("beat_range_end", sa.Float(), nullable=False, server_default="0"),
175 sa.Column("parent_variation_id", sa.String(36), sa.ForeignKey("muse_variations.variation_id", ondelete="SET NULL"), nullable=True),
176 sa.Column("parent2_variation_id", sa.String(36), sa.ForeignKey("muse_variations.variation_id", ondelete="SET NULL"), nullable=True),
177 sa.Column("commit_state_id", sa.String(36), nullable=True),
178 sa.Column("is_head", sa.Boolean(), nullable=False, server_default="false"),
179 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
180 sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
181 sa.PrimaryKeyConstraint("variation_id"),
182 )
183 op.create_index("ix_muse_variations_project_id", "muse_variations", ["project_id"])
184 op.create_index("ix_muse_variations_parent_variation_id", "muse_variations", ["parent_variation_id"])
185
186 op.create_table(
187 "muse_phrases",
188 sa.Column("phrase_id", sa.String(36), nullable=False),
189 sa.Column("variation_id", sa.String(36), nullable=False),
190 sa.Column("sequence", sa.Integer(), nullable=False),
191 sa.Column("track_id", sa.String(36), nullable=False),
192 sa.Column("region_id", sa.String(36), nullable=False),
193 sa.Column("start_beat", sa.Float(), nullable=False),
194 sa.Column("end_beat", sa.Float(), nullable=False),
195 sa.Column("label", sa.String(255), nullable=False),
196 sa.Column("tags", sa.JSON(), nullable=True),
197 sa.Column("explanation", sa.Text(), nullable=True),
198 sa.Column("cc_events", sa.JSON(), nullable=True),
199 sa.Column("pitch_bends", sa.JSON(), nullable=True),
200 sa.Column("aftertouch", sa.JSON(), nullable=True),
201 sa.Column("region_start_beat", sa.Float(), nullable=True),
202 sa.Column("region_duration_beats", sa.Float(), nullable=True),
203 sa.Column("region_name", sa.String(255), nullable=True),
204 sa.ForeignKeyConstraint(["variation_id"], ["muse_variations.variation_id"], ondelete="CASCADE"),
205 sa.PrimaryKeyConstraint("phrase_id"),
206 )
207 op.create_index("ix_muse_phrases_variation_id", "muse_phrases", ["variation_id"])
208
209 op.create_table(
210 "muse_note_changes",
211 sa.Column("id", sa.String(36), nullable=False),
212 sa.Column("phrase_id", sa.String(36), nullable=False),
213 sa.Column("change_type", sa.String(20), nullable=False),
214 sa.Column("before_json", sa.JSON(), nullable=True),
215 sa.Column("after_json", sa.JSON(), nullable=True),
216 sa.ForeignKeyConstraint(["phrase_id"], ["muse_phrases.phrase_id"], ondelete="CASCADE"),
217 sa.PrimaryKeyConstraint("id"),
218 )
219 op.create_index("ix_muse_note_changes_phrase_id", "muse_note_changes", ["phrase_id"])
220
221 # ── Muse CLI — filesystem commit history ──────────────────────────────
222 op.create_table(
223 "muse_objects",
224 sa.Column("object_id", sa.String(64), nullable=False),
225 sa.Column("size_bytes", sa.Integer(), nullable=False),
226 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
227 sa.PrimaryKeyConstraint("object_id"),
228 )
229
230 op.create_table(
231 "muse_snapshots",
232 sa.Column("snapshot_id", sa.String(64), nullable=False),
233 sa.Column("manifest", sa.JSON(), nullable=False),
234 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
235 sa.PrimaryKeyConstraint("snapshot_id"),
236 )
237
238 op.create_table(
239 "muse_commits",
240 sa.Column("commit_id", sa.String(64), nullable=False),
241 sa.Column("repo_id", sa.String(36), nullable=False),
242 sa.Column("branch", sa.String(255), nullable=False),
243 sa.Column("parent_commit_id", sa.String(64), nullable=True),
244 sa.Column("parent2_commit_id", sa.String(64), nullable=True),
245 sa.Column("snapshot_id", sa.String(64), nullable=False),
246 sa.Column("message", sa.Text(), nullable=False),
247 sa.Column("author", sa.String(255), nullable=False),
248 sa.Column("committed_at", sa.DateTime(timezone=True), nullable=False),
249 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
250 sa.Column("metadata", sa.JSON(), nullable=True),
251 sa.ForeignKeyConstraint(["snapshot_id"], ["muse_snapshots.snapshot_id"], ondelete="RESTRICT"),
252 sa.PrimaryKeyConstraint("commit_id"),
253 )
254 op.create_index("ix_muse_commits_repo_id", "muse_commits", ["repo_id"])
255 op.create_index("ix_muse_commits_parent_commit_id", "muse_commits", ["parent_commit_id"])
256 op.create_index("ix_muse_commits_parent2_commit_id", "muse_commits", ["parent2_commit_id"])
257
258 op.create_table(
259 "muse_tags",
260 sa.Column("tag_id", sa.String(36), nullable=False),
261 sa.Column("repo_id", sa.String(36), nullable=False),
262 sa.Column("commit_id", sa.String(64), nullable=False),
263 sa.Column("tag", sa.Text(), nullable=False),
264 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
265 sa.ForeignKeyConstraint(["commit_id"], ["muse_commits.commit_id"], ondelete="CASCADE"),
266 sa.PrimaryKeyConstraint("tag_id"),
267 )
268 op.create_index("ix_muse_tags_repo_id", "muse_tags", ["repo_id"])
269 op.create_index("ix_muse_tags_commit_id", "muse_tags", ["commit_id"])
270 op.create_index("ix_muse_tags_tag", "muse_tags", ["tag"])
271
272 # ── Muse Hub — remote collaboration backend ───────────────────────────
273 op.create_table(
274 "musehub_repos",
275 sa.Column("repo_id", sa.String(36), nullable=False),
276 sa.Column("name", sa.String(255), nullable=False),
277 # URL-visible owner username (e.g. "gabriel") — forms the /{owner}/{slug} path
278 sa.Column("owner", sa.String(64), nullable=False),
279 # URL-safe slug auto-generated from name (e.g. "neo-soul-experiment")
280 sa.Column("slug", sa.String(64), nullable=False),
281 sa.Column("visibility", sa.String(20), nullable=False, server_default="private"),
282 sa.Column("owner_user_id", sa.String(36), nullable=False),
283 sa.Column("description", sa.Text(), nullable=False, server_default=""),
284 sa.Column("tags", sa.JSON(), nullable=False, server_default="[]"),
285 sa.Column("key_signature", sa.String(50), nullable=True),
286 sa.Column("tempo_bpm", sa.Integer(), nullable=True),
287 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
288 # Soft-delete timestamp; non-null means the repo is logically deleted
289 sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
290 sa.PrimaryKeyConstraint("repo_id"),
291 sa.UniqueConstraint("owner", "slug", name="uq_musehub_repos_owner_slug"),
292 )
293 op.create_index("ix_musehub_repos_owner", "musehub_repos", ["owner"])
294 op.create_index("ix_musehub_repos_slug", "musehub_repos", ["slug"])
295 op.create_index("ix_musehub_repos_owner_user_id", "musehub_repos", ["owner_user_id"])
296
297 op.create_table(
298 "musehub_branches",
299 sa.Column("branch_id", sa.String(36), nullable=False),
300 sa.Column("repo_id", sa.String(36), nullable=False),
301 sa.Column("name", sa.String(255), nullable=False),
302 sa.Column("head_commit_id", sa.String(64), nullable=True),
303 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
304 sa.PrimaryKeyConstraint("branch_id"),
305 )
306 op.create_index("ix_musehub_branches_repo_id", "musehub_branches", ["repo_id"])
307
308 op.create_table(
309 "musehub_commits",
310 sa.Column("commit_id", sa.String(64), nullable=False),
311 sa.Column("repo_id", sa.String(36), nullable=False),
312 sa.Column("branch", sa.String(255), nullable=False),
313 sa.Column("parent_ids", sa.JSON(), nullable=False, server_default="[]"),
314 sa.Column("message", sa.Text(), nullable=False),
315 sa.Column("author", sa.String(255), nullable=False),
316 sa.Column("timestamp", sa.DateTime(timezone=True), nullable=False),
317 sa.Column("snapshot_id", sa.String(64), nullable=True),
318 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
319 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
320 sa.PrimaryKeyConstraint("commit_id"),
321 )
322 op.create_index("ix_musehub_commits_repo_id", "musehub_commits", ["repo_id"])
323 op.create_index("ix_musehub_commits_branch", "musehub_commits", ["branch"])
324 op.create_index("ix_musehub_commits_timestamp", "musehub_commits", ["timestamp"])
325
326 # ── Muse Hub — milestones ─────────────────────────────────────────────
327 op.create_table(
328 "musehub_milestones",
329 sa.Column("milestone_id", sa.String(36), nullable=False),
330 sa.Column("repo_id", sa.String(36), nullable=False),
331 sa.Column("number", sa.Integer(), nullable=False),
332 sa.Column("title", sa.String(255), nullable=False),
333 sa.Column("description", sa.Text(), nullable=False, server_default=""),
334 sa.Column("state", sa.String(20), nullable=False, server_default="open"),
335 sa.Column("author", sa.String(255), nullable=False, server_default=""),
336 sa.Column("due_on", sa.DateTime(timezone=True), nullable=True),
337 sa.Column(
338 "created_at",
339 sa.DateTime(timezone=True),
340 nullable=False,
341 server_default=sa.text("CURRENT_TIMESTAMP"),
342 ),
343 sa.Column(
344 "updated_at",
345 sa.DateTime(timezone=True),
346 nullable=False,
347 server_default=sa.text("CURRENT_TIMESTAMP"),
348 ),
349 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
350 sa.PrimaryKeyConstraint("milestone_id"),
351 )
352 op.create_index("ix_musehub_milestones_repo_id", "musehub_milestones", ["repo_id"])
353 op.create_index("ix_musehub_milestones_number", "musehub_milestones", ["number"])
354 op.create_index("ix_musehub_milestones_state", "musehub_milestones", ["state"])
355
356 # ── Muse Hub — issue tracking ─────────────────────────────────────────
357 op.create_table(
358 "musehub_issues",
359 sa.Column("issue_id", sa.String(36), nullable=False),
360 sa.Column("repo_id", sa.String(36), nullable=False),
361 sa.Column("number", sa.Integer(), nullable=False),
362 sa.Column("title", sa.String(500), nullable=False),
363 sa.Column("body", sa.Text(), nullable=False, server_default=""),
364 sa.Column("state", sa.String(20), nullable=False, server_default="open"),
365 sa.Column("labels", sa.JSON(), nullable=False, server_default="[]"),
366 sa.Column("author", sa.String(255), nullable=False, server_default=""),
367 sa.Column("assignee", sa.String(255), nullable=True),
368 sa.Column("milestone_id", sa.String(36), nullable=True),
369 sa.Column(
370 "created_at",
371 sa.DateTime(timezone=True),
372 nullable=False,
373 server_default=sa.text("CURRENT_TIMESTAMP"),
374 ),
375 sa.Column(
376 "updated_at",
377 sa.DateTime(timezone=True),
378 nullable=False,
379 server_default=sa.text("CURRENT_TIMESTAMP"),
380 ),
381 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
382 sa.ForeignKeyConstraint(
383 ["milestone_id"], ["musehub_milestones.milestone_id"], ondelete="SET NULL"
384 ),
385 sa.PrimaryKeyConstraint("issue_id"),
386 )
387 op.create_index("ix_musehub_issues_repo_id", "musehub_issues", ["repo_id"])
388 op.create_index("ix_musehub_issues_number", "musehub_issues", ["number"])
389 op.create_index("ix_musehub_issues_state", "musehub_issues", ["state"])
390 op.create_index("ix_musehub_issues_milestone_id", "musehub_issues", ["milestone_id"])
391
392 # ── Muse Hub — issue comments ─────────────────────────────────────────
393 op.create_table(
394 "musehub_issue_comments",
395 sa.Column("comment_id", sa.String(36), nullable=False),
396 sa.Column("issue_id", sa.String(36), nullable=False),
397 sa.Column("repo_id", sa.String(36), nullable=False),
398 sa.Column("author", sa.String(255), nullable=False, server_default=""),
399 sa.Column("body", sa.Text(), nullable=False),
400 sa.Column("parent_id", sa.String(36), nullable=True),
401 sa.Column("musical_refs", sa.JSON(), nullable=False, server_default="[]"),
402 sa.Column("is_deleted", sa.Boolean(), nullable=False, server_default="false"),
403 sa.Column(
404 "created_at",
405 sa.DateTime(timezone=True),
406 nullable=False,
407 server_default=sa.text("CURRENT_TIMESTAMP"),
408 ),
409 sa.Column(
410 "updated_at",
411 sa.DateTime(timezone=True),
412 nullable=False,
413 server_default=sa.text("CURRENT_TIMESTAMP"),
414 ),
415 sa.ForeignKeyConstraint(["issue_id"], ["musehub_issues.issue_id"], ondelete="CASCADE"),
416 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
417 sa.PrimaryKeyConstraint("comment_id"),
418 )
419 op.create_index("ix_musehub_issue_comments_issue_id", "musehub_issue_comments", ["issue_id"])
420 op.create_index("ix_musehub_issue_comments_repo_id", "musehub_issue_comments", ["repo_id"])
421 op.create_index("ix_musehub_issue_comments_parent_id", "musehub_issue_comments", ["parent_id"])
422
423 # ── Muse Hub — issue-milestone join table ─────────────────────────────
424 op.create_table(
425 "musehub_issue_milestones",
426 sa.Column("issue_id", sa.String(36), nullable=False),
427 sa.Column("milestone_id", sa.String(36), nullable=False),
428 sa.ForeignKeyConstraint(
429 ["issue_id"],
430 ["musehub_issues.issue_id"],
431 ondelete="CASCADE",
432 ),
433 sa.ForeignKeyConstraint(
434 ["milestone_id"],
435 ["musehub_milestones.milestone_id"],
436 ondelete="CASCADE",
437 ),
438 sa.PrimaryKeyConstraint("issue_id", "milestone_id"),
439 )
440 op.create_index(
441 "ix_musehub_issue_milestones_milestone_id",
442 "musehub_issue_milestones",
443 ["milestone_id"],
444 )
445 op.create_index("ix_musehub_issue_comments_created_at", "musehub_issue_comments", ["created_at"])
446
447 # ── Muse Hub — pull requests ──────────────────────────────────────────
448 op.create_table(
449 "musehub_pull_requests",
450 sa.Column("pr_id", sa.String(36), nullable=False),
451 sa.Column("repo_id", sa.String(36), nullable=False),
452 sa.Column("title", sa.String(500), nullable=False),
453 sa.Column("body", sa.Text(), nullable=False, server_default=""),
454 sa.Column("state", sa.String(20), nullable=False, server_default="open"),
455 sa.Column("from_branch", sa.String(255), nullable=False),
456 sa.Column("to_branch", sa.String(255), nullable=False),
457 sa.Column("merge_commit_id", sa.String(64), nullable=True),
458 sa.Column("author", sa.String(255), nullable=False, server_default=""),
459 sa.Column(
460 "created_at",
461 sa.DateTime(timezone=True),
462 nullable=False,
463 server_default=sa.text("CURRENT_TIMESTAMP"),
464 ),
465 # Set by merge_pr() at the exact moment of merge; NULL for open/unmerged PRs.
466 # Used by the timeline overlay to position PR markers at merge time, not open time.
467 sa.Column("merged_at", sa.DateTime(timezone=True), nullable=True),
468 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
469 sa.PrimaryKeyConstraint("pr_id"),
470 )
471 op.create_index("ix_musehub_pull_requests_repo_id", "musehub_pull_requests", ["repo_id"])
472 op.create_index("ix_musehub_pull_requests_state", "musehub_pull_requests", ["state"])
473
474 # ── Muse Hub — PR review comments ────────────────────────────────────
475 op.create_table(
476 "musehub_pr_comments",
477 sa.Column("comment_id", sa.String(36), nullable=False),
478 sa.Column("pr_id", sa.String(36), nullable=False),
479 sa.Column("repo_id", sa.String(36), nullable=False),
480 sa.Column("author", sa.String(255), nullable=False),
481 sa.Column("body", sa.Text(), nullable=False),
482 sa.Column("target_type", sa.String(20), nullable=False, server_default="general"),
483 sa.Column("target_track", sa.String(255), nullable=True),
484 sa.Column("target_beat_start", sa.Float(), nullable=True),
485 sa.Column("target_beat_end", sa.Float(), nullable=True),
486 sa.Column("target_note_pitch", sa.Integer(), nullable=True),
487 sa.Column("parent_comment_id", sa.String(36), nullable=True),
488 sa.Column(
489 "created_at",
490 sa.DateTime(timezone=True),
491 nullable=False,
492 server_default=sa.text("CURRENT_TIMESTAMP"),
493 ),
494 sa.ForeignKeyConstraint(["pr_id"], ["musehub_pull_requests.pr_id"], ondelete="CASCADE"),
495 sa.PrimaryKeyConstraint("comment_id"),
496 )
497 op.create_index("ix_musehub_pr_comments_pr_id", "musehub_pr_comments", ["pr_id"])
498 op.create_index("ix_musehub_pr_comments_repo_id", "musehub_pr_comments", ["repo_id"])
499 op.create_index("ix_musehub_pr_comments_parent_comment_id", "musehub_pr_comments", ["parent_comment_id"])
500 op.create_index("ix_musehub_pr_comments_created_at", "musehub_pr_comments", ["created_at"])
501
502 # ── Muse Hub — binary artifact storage ───────────────────────────────
503 op.create_table(
504 "musehub_objects",
505 sa.Column("object_id", sa.String(128), nullable=False),
506 sa.Column("repo_id", sa.String(36), nullable=False),
507 sa.Column("path", sa.String(1024), nullable=False),
508 sa.Column("size_bytes", sa.Integer(), nullable=False, server_default="0"),
509 sa.Column("disk_path", sa.String(2048), nullable=False),
510 sa.Column(
511 "created_at",
512 sa.DateTime(timezone=True),
513 nullable=False,
514 server_default=sa.text("CURRENT_TIMESTAMP"),
515 ),
516 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
517 sa.PrimaryKeyConstraint("object_id"),
518 )
519 op.create_index("ix_musehub_objects_repo_id", "musehub_objects", ["repo_id"])
520
521 # ── Muse Hub — repo starring (explore/discover page) ─────────────────
522 op.create_table(
523 "musehub_stars",
524 sa.Column("star_id", sa.String(36), nullable=False),
525 sa.Column("repo_id", sa.String(36), nullable=False),
526 sa.Column("user_id", sa.String(36), nullable=False),
527 sa.Column(
528 "created_at",
529 sa.DateTime(timezone=True),
530 nullable=False,
531 server_default=sa.text("CURRENT_TIMESTAMP"),
532 ),
533 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
534 sa.PrimaryKeyConstraint("star_id"),
535 sa.UniqueConstraint("repo_id", "user_id", name="uq_musehub_stars_repo_user"),
536 )
537 op.create_index("ix_musehub_stars_repo_id", "musehub_stars", ["repo_id"])
538 op.create_index("ix_musehub_stars_user_id", "musehub_stars", ["user_id"])
539
540 # ── Muse Hub — recording sessions ─────────────────────────────────────
541 op.create_table(
542 "musehub_sessions",
543 sa.Column("session_id", sa.String(36), nullable=False),
544 sa.Column("repo_id", sa.String(36), nullable=False),
545 sa.Column("schema_version", sa.String(10), nullable=False, server_default="1"),
546 sa.Column("started_at", sa.DateTime(timezone=True), nullable=False),
547 sa.Column("ended_at", sa.DateTime(timezone=True), nullable=True),
548 sa.Column("participants", sa.JSON(), nullable=False, server_default="[]"),
549 sa.Column("location", sa.String(500), nullable=False, server_default=""),
550 sa.Column("intent", sa.Text(), nullable=False, server_default=""),
551 sa.Column("commits", sa.JSON(), nullable=False, server_default="[]"),
552 sa.Column("notes", sa.Text(), nullable=False, server_default=""),
553 # True while the session is still active; False after muse session end / stop
554 sa.Column("is_active", sa.Boolean(), nullable=False, server_default="false"),
555 sa.Column(
556 "created_at",
557 sa.DateTime(timezone=True),
558 nullable=False,
559 server_default=sa.text("CURRENT_TIMESTAMP"),
560 ),
561 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
562 sa.PrimaryKeyConstraint("session_id"),
563 )
564 op.create_index("ix_musehub_sessions_repo_id", "musehub_sessions", ["repo_id"])
565 op.create_index("ix_musehub_sessions_started_at", "musehub_sessions", ["started_at"])
566 op.create_index("ix_musehub_sessions_is_active", "musehub_sessions", ["is_active"])
567
568 # ── Muse Hub — public user profiles ───────────────────────────────────
569 op.create_table(
570 "musehub_profiles",
571 # PK is the JWT sub claim — same value used in musehub_repos.owner_user_id
572 sa.Column("user_id", sa.String(36), nullable=False),
573 # URL-friendly handle, e.g. "gabriel" → /musehub/ui/users/gabriel
574 sa.Column("username", sa.String(64), nullable=False),
575 # Human-readable display name shown in profile header (e.g. "Johann Sebastian Bach")
576 sa.Column("display_name", sa.String(255), nullable=True),
577 sa.Column("bio", sa.Text(), nullable=True),
578 sa.Column("avatar_url", sa.String(2048), nullable=True),
579 # Physical or virtual location shown on profile card
580 sa.Column("location", sa.String(255), nullable=True),
581 # Personal website or project homepage URL
582 sa.Column("website_url", sa.String(2048), nullable=True),
583 # Twitter/X handle without the leading @
584 sa.Column("twitter_handle", sa.String(64), nullable=True),
585 # True for Public Domain and Creative Commons licensed archive artists
586 sa.Column("is_verified", sa.Boolean(), nullable=False, server_default="false"),
587 # CC attribution string, e.g. "Public Domain", "CC BY 4.0"; null = all rights reserved
588 sa.Column("cc_license", sa.String(50), nullable=True),
589 # JSON list of repo_ids (up to 6) pinned by the user on their profile page
590 sa.Column("pinned_repo_ids", sa.JSON(), nullable=False, server_default="[]"),
591 sa.Column(
592 "created_at",
593 sa.DateTime(timezone=True),
594 nullable=False,
595 server_default=sa.text("CURRENT_TIMESTAMP"),
596 ),
597 sa.Column(
598 "updated_at",
599 sa.DateTime(timezone=True),
600 nullable=False,
601 server_default=sa.text("CURRENT_TIMESTAMP"),
602 ),
603 sa.PrimaryKeyConstraint("user_id"),
604 sa.UniqueConstraint("username", name="uq_musehub_profiles_username"),
605 )
606 op.create_index("ix_musehub_profiles_username", "musehub_profiles", ["username"])
607 op.create_index("ix_musehub_profiles_is_verified", "musehub_profiles", ["is_verified"])
608
609 # ── Muse Hub — webhook subscriptions ─────────────────────────────────
610 op.create_table(
611 "musehub_webhooks",
612 sa.Column("webhook_id", sa.String(36), nullable=False),
613 sa.Column("repo_id", sa.String(36), nullable=False),
614 sa.Column("url", sa.String(2048), nullable=False),
615 sa.Column("events", sa.JSON(), nullable=False, server_default="[]"),
616 sa.Column("secret", sa.Text(), nullable=False, server_default=""),
617 sa.Column("active", sa.Boolean(), nullable=False, server_default="true"),
618 sa.Column(
619 "created_at",
620 sa.DateTime(timezone=True),
621 nullable=False,
622 server_default=sa.text("CURRENT_TIMESTAMP"),
623 ),
624 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
625 sa.PrimaryKeyConstraint("webhook_id"),
626 )
627 op.create_index("ix_musehub_webhooks_repo_id", "musehub_webhooks", ["repo_id"])
628
629 op.create_table(
630 "musehub_webhook_deliveries",
631 sa.Column("delivery_id", sa.String(36), nullable=False),
632 sa.Column("webhook_id", sa.String(36), nullable=False),
633 sa.Column("event_type", sa.String(64), nullable=False),
634 sa.Column("attempt", sa.Integer(), nullable=False, server_default="1"),
635 sa.Column("success", sa.Boolean(), nullable=False, server_default="false"),
636 sa.Column("response_status", sa.Integer(), nullable=False, server_default="0"),
637 sa.Column("response_body", sa.Text(), nullable=False, server_default=""),
638 # JSON-encoded payload stored so failed deliveries can be retried via redeliver endpoint
639 sa.Column("payload", sa.Text(), nullable=False, server_default=""),
640 sa.Column(
641 "delivered_at",
642 sa.DateTime(timezone=True),
643 nullable=False,
644 server_default=sa.text("CURRENT_TIMESTAMP"),
645 ),
646 sa.ForeignKeyConstraint(
647 ["webhook_id"], ["musehub_webhooks.webhook_id"], ondelete="CASCADE"
648 ),
649 sa.PrimaryKeyConstraint("delivery_id"),
650 )
651 op.create_index(
652 "ix_musehub_webhook_deliveries_webhook_id",
653 "musehub_webhook_deliveries",
654 ["webhook_id"],
655 )
656 op.create_index(
657 "ix_musehub_webhook_deliveries_event_type",
658 "musehub_webhook_deliveries",
659 ["event_type"],
660 )
661
662
663 # ── Muse Hub — releases ───────────────────────────────────────────────
664 op.create_table(
665 "musehub_releases",
666 sa.Column("release_id", sa.String(36), nullable=False),
667 sa.Column("repo_id", sa.String(36), nullable=False),
668 sa.Column("tag", sa.String(100), nullable=False),
669 sa.Column("title", sa.String(500), nullable=False),
670 sa.Column("body", sa.Text(), nullable=False, server_default=""),
671 sa.Column("commit_id", sa.String(64), nullable=True),
672 sa.Column("download_urls", sa.JSON(), nullable=False, server_default="{}"),
673 sa.Column("author", sa.String(255), nullable=False, server_default=""),
674 sa.Column("is_prerelease", sa.Boolean(), nullable=False, server_default="false"),
675 sa.Column("is_draft", sa.Boolean(), nullable=False, server_default="false"),
676 sa.Column("gpg_signature", sa.Text(), nullable=True),
677 sa.Column(
678 "created_at",
679 sa.DateTime(timezone=True),
680 nullable=False,
681 server_default=sa.text("CURRENT_TIMESTAMP"),
682 ),
683 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
684 sa.PrimaryKeyConstraint("release_id"),
685 sa.UniqueConstraint("repo_id", "tag", name="uq_musehub_releases_repo_tag"),
686 )
687 op.create_index("ix_musehub_releases_repo_id", "musehub_releases", ["repo_id"])
688 op.create_index("ix_musehub_releases_tag", "musehub_releases", ["tag"])
689
690 # ── Muse Hub — release assets ─────────────────────────────────────────
691 op.create_table(
692 "musehub_release_assets",
693 sa.Column("asset_id", sa.String(36), nullable=False),
694 sa.Column("release_id", sa.String(36), nullable=False),
695 sa.Column("repo_id", sa.String(36), nullable=False),
696 sa.Column("name", sa.String(500), nullable=False),
697 sa.Column("label", sa.String(255), nullable=False, server_default=""),
698 sa.Column("content_type", sa.String(128), nullable=False, server_default=""),
699 sa.Column("size", sa.Integer(), nullable=False, server_default="0"),
700 sa.Column("download_url", sa.String(2048), nullable=False),
701 sa.Column("download_count", sa.Integer(), nullable=False, server_default="0"),
702 sa.Column(
703 "created_at",
704 sa.DateTime(timezone=True),
705 nullable=False,
706 server_default=sa.text("CURRENT_TIMESTAMP"),
707 ),
708 sa.ForeignKeyConstraint(
709 ["release_id"], ["musehub_releases.release_id"], ondelete="CASCADE"
710 ),
711 sa.PrimaryKeyConstraint("asset_id"),
712 )
713 op.create_index(
714 "ix_musehub_release_assets_release_id", "musehub_release_assets", ["release_id"]
715 )
716 op.create_index(
717 "ix_musehub_release_assets_repo_id", "musehub_release_assets", ["repo_id"]
718 )
719
720 # ── Muse Hub — social layer (Phase 4) ────────────────────────────────
721
722 op.create_table(
723 "musehub_comments",
724 sa.Column("comment_id", sa.String(36), nullable=False),
725 sa.Column("repo_id", sa.String(36), nullable=False),
726 sa.Column("target_type", sa.String(20), nullable=False),
727 sa.Column("target_id", sa.String(255), nullable=False),
728 sa.Column("author", sa.String(255), nullable=False),
729 sa.Column("body", sa.Text(), nullable=False),
730 sa.Column("parent_id", sa.String(36), nullable=True),
731 sa.Column("is_deleted", sa.Boolean(), nullable=False, server_default="false"),
732 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
733 sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
734 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
735 sa.PrimaryKeyConstraint("comment_id"),
736 )
737 op.create_index("ix_musehub_comments_repo_id", "musehub_comments", ["repo_id"])
738 op.create_index("ix_musehub_comments_author", "musehub_comments", ["author"])
739 op.create_index("ix_musehub_comments_created_at", "musehub_comments", ["created_at"])
740 op.create_index("ix_musehub_comments_target_type", "musehub_comments", ["target_type"])
741 op.create_index("ix_musehub_comments_target_id", "musehub_comments", ["target_id"])
742
743 op.create_table(
744 "musehub_reactions",
745 sa.Column("reaction_id", sa.String(36), nullable=False),
746 sa.Column("repo_id", sa.String(36), nullable=False),
747 sa.Column("target_type", sa.String(20), nullable=False),
748 sa.Column("target_id", sa.String(255), nullable=False),
749 sa.Column("user_id", sa.String(255), nullable=False),
750 sa.Column("emoji", sa.String(10), nullable=False),
751 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
752 sa.PrimaryKeyConstraint("reaction_id"),
753 sa.UniqueConstraint("user_id", "target_type", "target_id", "emoji", name="uq_musehub_reactions"),
754 )
755 op.create_index("ix_musehub_reactions_repo_id", "musehub_reactions", ["repo_id"])
756 op.create_index("ix_musehub_reactions_target_type", "musehub_reactions", ["target_type"])
757 op.create_index("ix_musehub_reactions_target_id", "musehub_reactions", ["target_id"])
758 op.create_index("ix_musehub_reactions_user_id", "musehub_reactions", ["user_id"])
759
760 op.create_table(
761 "musehub_follows",
762 sa.Column("follow_id", sa.String(36), nullable=False),
763 sa.Column("follower_id", sa.String(255), nullable=False),
764 sa.Column("followee_id", sa.String(255), nullable=False),
765 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
766 sa.PrimaryKeyConstraint("follow_id"),
767 sa.UniqueConstraint("follower_id", "followee_id", name="uq_musehub_follows"),
768 )
769 op.create_index("ix_musehub_follows_follower_id", "musehub_follows", ["follower_id"])
770 op.create_index("ix_musehub_follows_followee_id", "musehub_follows", ["followee_id"])
771
772 op.create_table(
773 "musehub_watches",
774 sa.Column("watch_id", sa.String(36), nullable=False),
775 sa.Column("user_id", sa.String(255), nullable=False),
776 sa.Column("repo_id", sa.String(36), nullable=False),
777 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
778 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
779 sa.PrimaryKeyConstraint("watch_id"),
780 sa.UniqueConstraint("user_id", "repo_id", name="uq_musehub_watches"),
781 )
782 op.create_index("ix_musehub_watches_user_id", "musehub_watches", ["user_id"])
783 op.create_index("ix_musehub_watches_repo_id", "musehub_watches", ["repo_id"])
784
785 op.create_table(
786 "musehub_notifications",
787 sa.Column("notif_id", sa.String(36), nullable=False),
788 sa.Column("recipient_id", sa.String(255), nullable=False),
789 sa.Column("event_type", sa.String(40), nullable=False),
790 sa.Column("repo_id", sa.String(36), nullable=True),
791 sa.Column("actor", sa.String(255), nullable=False),
792 sa.Column("payload", sa.JSON(), nullable=False, server_default="{}"),
793 sa.Column("is_read", sa.Boolean(), nullable=False, server_default="false"),
794 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
795 sa.PrimaryKeyConstraint("notif_id"),
796 )
797 op.create_index("ix_musehub_notifications_recipient_id", "musehub_notifications", ["recipient_id"])
798 op.create_index("ix_musehub_notifications_is_read", "musehub_notifications", ["is_read"])
799 op.create_index("ix_musehub_notifications_created_at", "musehub_notifications", ["created_at"])
800
801 op.create_table(
802 "musehub_forks",
803 sa.Column("fork_id", sa.String(36), nullable=False),
804 sa.Column("source_repo_id", sa.String(36), nullable=False),
805 sa.Column("fork_repo_id", sa.String(36), nullable=False),
806 sa.Column("forked_by", sa.String(255), nullable=False),
807 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
808 sa.ForeignKeyConstraint(["source_repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
809 sa.ForeignKeyConstraint(["fork_repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
810 sa.PrimaryKeyConstraint("fork_id"),
811 sa.UniqueConstraint("source_repo_id", "fork_repo_id", name="uq_musehub_forks"),
812 )
813 op.create_index("ix_musehub_forks_source_repo_id", "musehub_forks", ["source_repo_id"])
814 op.create_index("ix_musehub_forks_fork_repo_id", "musehub_forks", ["fork_repo_id"])
815
816 op.create_table(
817 "musehub_view_events",
818 sa.Column("view_id", sa.String(36), nullable=False),
819 sa.Column("repo_id", sa.String(36), nullable=False),
820 sa.Column("viewer_fingerprint", sa.String(64), nullable=False),
821 sa.Column("event_date", sa.String(10), nullable=False),
822 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
823 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
824 sa.PrimaryKeyConstraint("view_id"),
825 sa.UniqueConstraint("repo_id", "viewer_fingerprint", "event_date", name="uq_musehub_view_events"),
826 )
827 op.create_index("ix_musehub_view_events_repo_id", "musehub_view_events", ["repo_id"])
828
829 op.create_table(
830 "musehub_download_events",
831 sa.Column("dl_id", sa.String(36), nullable=False),
832 sa.Column("repo_id", sa.String(36), nullable=False),
833 sa.Column("ref", sa.String(255), nullable=False),
834 sa.Column("downloader_id", sa.String(255), nullable=True),
835 sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP")),
836 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
837 sa.PrimaryKeyConstraint("dl_id"),
838 )
839 op.create_index("ix_musehub_download_events_repo_id", "musehub_download_events", ["repo_id"])
840 op.create_index("ix_musehub_download_events_created_at", "musehub_download_events", ["created_at"])
841
842 # ── MuseHub — render pipeline (Phase 5) ──────────────────────────────
843 op.create_table(
844 "musehub_render_jobs",
845 sa.Column("render_job_id", sa.String(36), nullable=False),
846 sa.Column("repo_id", sa.String(36), nullable=False),
847 sa.Column("commit_id", sa.String(64), nullable=False),
848 sa.Column("status", sa.String(20), nullable=False, server_default="pending"),
849 sa.Column("error_message", sa.Text(), nullable=True),
850 sa.Column("midi_count", sa.Integer(), nullable=False, server_default="0"),
851 sa.Column("mp3_object_ids", sa.JSON(), nullable=False, server_default="[]"),
852 sa.Column("image_object_ids", sa.JSON(), nullable=False, server_default="[]"),
853 sa.Column(
854 "created_at",
855 sa.DateTime(timezone=True),
856 nullable=False,
857 server_default=sa.text("CURRENT_TIMESTAMP"),
858 ),
859 sa.Column(
860 "updated_at",
861 sa.DateTime(timezone=True),
862 nullable=False,
863 server_default=sa.text("CURRENT_TIMESTAMP"),
864 ),
865 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
866 sa.PrimaryKeyConstraint("render_job_id"),
867 sa.UniqueConstraint("repo_id", "commit_id", name="uq_musehub_render_jobs_repo_commit"),
868 )
869 op.create_index("ix_musehub_render_jobs_repo_id", "musehub_render_jobs", ["repo_id"])
870 op.create_index("ix_musehub_render_jobs_commit_id", "musehub_render_jobs", ["commit_id"])
871 op.create_index("ix_musehub_render_jobs_status", "musehub_render_jobs", ["status"])
872
873 # ── MuseHub — activity event stream (Phase 6) ─────────────────────────
874 op.create_table(
875 "musehub_events",
876 sa.Column("event_id", sa.String(36), nullable=False),
877 sa.Column("repo_id", sa.String(36), nullable=False),
878 sa.Column("event_type", sa.String(40), nullable=False),
879 sa.Column("actor", sa.String(255), nullable=False),
880 sa.Column("description", sa.Text(), nullable=False, server_default=""),
881 sa.Column("event_metadata", sa.JSON(), nullable=False, server_default="{}"),
882 sa.Column(
883 "created_at",
884 sa.DateTime(timezone=True),
885 nullable=False,
886 server_default=sa.text("CURRENT_TIMESTAMP"),
887 ),
888 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
889 sa.PrimaryKeyConstraint("event_id"),
890 )
891 op.create_index("ix_musehub_events_repo_id", "musehub_events", ["repo_id"])
892 op.create_index("ix_musehub_events_event_type", "musehub_events", ["event_type"])
893 op.create_index("ix_musehub_events_created_at", "musehub_events", ["created_at"])
894
895 # Muse Hub — labels (folded from 0003_labels)
896 op.create_table(
897 "musehub_labels",
898 sa.Column("id", sa.String(36), nullable=False),
899 sa.Column("repo_id", sa.String(36), nullable=False),
900 sa.Column("name", sa.String(50), nullable=False),
901 sa.Column("color", sa.String(7), nullable=False),
902 sa.Column("description", sa.String(200), nullable=True),
903 sa.Column(
904 "created_at",
905 sa.DateTime(timezone=True),
906 nullable=False,
907 server_default=sa.text("CURRENT_TIMESTAMP"),
908 ),
909 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
910 sa.PrimaryKeyConstraint("id"),
911 sa.UniqueConstraint("repo_id", "name", name="uq_musehub_labels_repo_name"),
912 )
913 op.create_index("ix_musehub_labels_repo_id", "musehub_labels", ["repo_id"])
914
915 op.create_table(
916 "musehub_issue_labels",
917 sa.Column("issue_id", sa.String(36), nullable=False),
918 sa.Column("label_id", sa.String(36), nullable=False),
919 sa.ForeignKeyConstraint(["issue_id"], ["musehub_issues.issue_id"], ondelete="CASCADE"),
920 sa.ForeignKeyConstraint(["label_id"], ["musehub_labels.id"], ondelete="CASCADE"),
921 sa.PrimaryKeyConstraint("issue_id", "label_id"),
922 )
923 op.create_index("ix_musehub_issue_labels_label_id", "musehub_issue_labels", ["label_id"])
924
925 op.create_table(
926 "musehub_pr_labels",
927 sa.Column("pr_id", sa.String(36), nullable=False),
928 sa.Column("label_id", sa.String(36), nullable=False),
929 sa.ForeignKeyConstraint(["pr_id"], ["musehub_pull_requests.pr_id"], ondelete="CASCADE"),
930 sa.ForeignKeyConstraint(["label_id"], ["musehub_labels.id"], ondelete="CASCADE"),
931 sa.PrimaryKeyConstraint("pr_id", "label_id"),
932 )
933 op.create_index("ix_musehub_pr_labels_label_id", "musehub_pr_labels", ["label_id"])
934
935 # Muse Hub — collaborators (folded from 0004_collaborators)
936 op.create_table(
937 "musehub_collaborators",
938 sa.Column("id", sa.String(36), nullable=False),
939 sa.Column("repo_id", sa.String(36), nullable=False),
940 sa.Column("user_id", sa.String(36), nullable=False),
941 sa.Column("permission", sa.String(20), nullable=False, server_default="write"),
942 sa.Column("invited_by", sa.String(36), nullable=True),
943 sa.Column(
944 "invited_at",
945 sa.DateTime(timezone=True),
946 nullable=False,
947 server_default=sa.text("CURRENT_TIMESTAMP"),
948 ),
949 sa.Column("accepted_at", sa.DateTime(timezone=True), nullable=True),
950 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
951 sa.ForeignKeyConstraint(["user_id"], ["maestro_users.id"], ondelete="CASCADE"),
952 sa.ForeignKeyConstraint(["invited_by"], ["maestro_users.id"], ondelete="SET NULL"),
953 sa.PrimaryKeyConstraint("id"),
954 sa.UniqueConstraint("repo_id", "user_id", name="uq_musehub_collaborators_repo_user"),
955 )
956 op.create_index("ix_musehub_collaborators_repo_id", "musehub_collaborators", ["repo_id"])
957 op.create_index("ix_musehub_collaborators_user_id", "musehub_collaborators", ["user_id"])
958
959 # Muse Hub — stash (folded from 0005_stash)
960 op.create_table(
961 "musehub_stash",
962 sa.Column("id", sa.String(36), nullable=False),
963 sa.Column("repo_id", sa.String(36), nullable=False),
964 sa.Column("user_id", sa.String(36), nullable=False),
965 sa.Column("branch", sa.String(255), nullable=False),
966 sa.Column("message", sa.String(500), nullable=True),
967 sa.Column("is_applied", sa.Boolean(), nullable=False, server_default=sa.false()),
968 sa.Column(
969 "created_at",
970 sa.DateTime(timezone=True),
971 nullable=False,
972 server_default=sa.text("CURRENT_TIMESTAMP"),
973 ),
974 sa.Column("applied_at", sa.DateTime(timezone=True), nullable=True),
975 sa.ForeignKeyConstraint(["repo_id"], ["musehub_repos.repo_id"], ondelete="CASCADE"),
976 sa.ForeignKeyConstraint(["user_id"], ["maestro_users.id"], ondelete="CASCADE"),
977 sa.PrimaryKeyConstraint("id"),
978 )
979 op.create_index("ix_musehub_stash_repo_id", "musehub_stash", ["repo_id"])
980 op.create_index("ix_musehub_stash_user_id", "musehub_stash", ["user_id"])
981
982 op.create_table(
983 "musehub_stash_entries",
984 sa.Column("id", sa.String(36), nullable=False),
985 sa.Column("stash_id", sa.String(36), nullable=False),
986 sa.Column("path", sa.String(1024), nullable=False),
987 sa.Column("object_id", sa.String(128), nullable=False),
988 sa.Column("position", sa.Integer(), nullable=False),
989 sa.ForeignKeyConstraint(["stash_id"], ["musehub_stash.id"], ondelete="CASCADE"),
990 sa.PrimaryKeyConstraint("id"),
991 )
992 op.create_index("ix_musehub_stash_entries_stash_id", "musehub_stash_entries", ["stash_id"])
993
994 # Muse Hub — PR reviews (folded from 0006_pr_reviews)
995 op.create_table(
996 "musehub_pr_reviews",
997 sa.Column("id", sa.String(36), nullable=False),
998 sa.Column("pr_id", sa.String(36), nullable=False),
999 sa.Column("reviewer_username", sa.String(255), nullable=False),
1000 sa.Column("state", sa.String(30), nullable=False, server_default="pending"),
1001 sa.Column("body", sa.Text(), nullable=True),
1002 sa.Column("submitted_at", sa.DateTime(timezone=True), nullable=True),
1003 sa.Column(
1004 "created_at",
1005 sa.DateTime(timezone=True),
1006 nullable=False,
1007 server_default=sa.text("CURRENT_TIMESTAMP"),
1008 ),
1009 sa.ForeignKeyConstraint(
1010 ["pr_id"],
1011 ["musehub_pull_requests.pr_id"],
1012 ondelete="CASCADE",
1013 ),
1014 sa.PrimaryKeyConstraint("id"),
1015 )
1016 op.create_index("ix_musehub_pr_reviews_pr_id", "musehub_pr_reviews", ["pr_id"])
1017 op.create_index(
1018 "ix_musehub_pr_reviews_reviewer_username",
1019 "musehub_pr_reviews",
1020 ["reviewer_username"],
1021 )
1022 op.create_index("ix_musehub_pr_reviews_state", "musehub_pr_reviews", ["state"])
1023
1024 # Muse Hub — repo settings (folded from 0006_repo_settings)
1025 op.add_column(
1026 "musehub_repos",
1027 sa.Column("settings", sa.JSON(), nullable=True),
1028 )
1029
1030
1031 def downgrade() -> None:
1032 # Drop in reverse creation order, respecting foreign-key dependencies.
1033
1034 # Muse Hub — PR reviews (folded from 0006_pr_reviews)
1035 op.drop_index("ix_musehub_pr_reviews_state", table_name="musehub_pr_reviews")
1036 op.drop_index(
1037 "ix_musehub_pr_reviews_reviewer_username", table_name="musehub_pr_reviews"
1038 )
1039 op.drop_index("ix_musehub_pr_reviews_pr_id", table_name="musehub_pr_reviews")
1040 op.drop_table("musehub_pr_reviews")
1041
1042 # Muse Hub — repo settings (folded from 0006_repo_settings)
1043 op.drop_column("musehub_repos", "settings")
1044
1045 # Muse Hub — stash (folded from 0005_stash)
1046 op.drop_index("ix_musehub_stash_entries_stash_id", table_name="musehub_stash_entries")
1047 op.drop_table("musehub_stash_entries")
1048 op.drop_index("ix_musehub_stash_user_id", table_name="musehub_stash")
1049 op.drop_index("ix_musehub_stash_repo_id", table_name="musehub_stash")
1050 op.drop_table("musehub_stash")
1051
1052 # Muse Hub — collaborators (folded from 0004_collaborators)
1053 op.drop_index("ix_musehub_collaborators_user_id", table_name="musehub_collaborators")
1054 op.drop_index("ix_musehub_collaborators_repo_id", table_name="musehub_collaborators")
1055 op.drop_table("musehub_collaborators")
1056
1057 # Muse Hub — labels (folded from 0003_labels)
1058 op.drop_index("ix_musehub_pr_labels_label_id", table_name="musehub_pr_labels")
1059 op.drop_table("musehub_pr_labels")
1060 op.drop_index("ix_musehub_issue_labels_label_id", table_name="musehub_issue_labels")
1061 op.drop_table("musehub_issue_labels")
1062 op.drop_index("ix_musehub_labels_repo_id", table_name="musehub_labels")
1063 op.drop_table("musehub_labels")
1064
1065 # MuseHub — activity event stream (Phase 6)
1066 op.drop_index("ix_musehub_events_created_at", table_name="musehub_events")
1067 op.drop_index("ix_musehub_events_event_type", table_name="musehub_events")
1068 op.drop_index("ix_musehub_events_repo_id", table_name="musehub_events")
1069 op.drop_table("musehub_events")
1070
1071 # MuseHub — render pipeline (Phase 5)
1072 op.drop_index("ix_musehub_render_jobs_status", table_name="musehub_render_jobs")
1073 op.drop_index("ix_musehub_render_jobs_commit_id", table_name="musehub_render_jobs")
1074 op.drop_index("ix_musehub_render_jobs_repo_id", table_name="musehub_render_jobs")
1075 op.drop_table("musehub_render_jobs")
1076
1077 # Muse Hub — social layer (Phase 4)
1078 op.drop_index("ix_musehub_download_events_created_at", table_name="musehub_download_events")
1079 op.drop_index("ix_musehub_download_events_repo_id", table_name="musehub_download_events")
1080 op.drop_table("musehub_download_events")
1081 op.drop_index("ix_musehub_view_events_repo_id", table_name="musehub_view_events")
1082 op.drop_table("musehub_view_events")
1083 op.drop_index("ix_musehub_forks_fork_repo_id", table_name="musehub_forks")
1084 op.drop_index("ix_musehub_forks_source_repo_id", table_name="musehub_forks")
1085 op.drop_table("musehub_forks")
1086 op.drop_index("ix_musehub_notifications_created_at", table_name="musehub_notifications")
1087 op.drop_index("ix_musehub_notifications_is_read", table_name="musehub_notifications")
1088 op.drop_index("ix_musehub_notifications_recipient_id", table_name="musehub_notifications")
1089 op.drop_table("musehub_notifications")
1090 op.drop_index("ix_musehub_watches_repo_id", table_name="musehub_watches")
1091 op.drop_index("ix_musehub_watches_user_id", table_name="musehub_watches")
1092 op.drop_table("musehub_watches")
1093 op.drop_index("ix_musehub_follows_followee_id", table_name="musehub_follows")
1094 op.drop_index("ix_musehub_follows_follower_id", table_name="musehub_follows")
1095 op.drop_table("musehub_follows")
1096 op.drop_index("ix_musehub_reactions_user_id", table_name="musehub_reactions")
1097 op.drop_index("ix_musehub_reactions_target_id", table_name="musehub_reactions")
1098 op.drop_index("ix_musehub_reactions_target_type", table_name="musehub_reactions")
1099 op.drop_index("ix_musehub_reactions_repo_id", table_name="musehub_reactions")
1100 op.drop_table("musehub_reactions")
1101 op.drop_index("ix_musehub_comments_target_id", table_name="musehub_comments")
1102 op.drop_index("ix_musehub_comments_target_type", table_name="musehub_comments")
1103 op.drop_index("ix_musehub_comments_created_at", table_name="musehub_comments")
1104 op.drop_index("ix_musehub_comments_author", table_name="musehub_comments")
1105 op.drop_index("ix_musehub_comments_repo_id", table_name="musehub_comments")
1106 op.drop_table("musehub_comments")
1107
1108 # Muse Hub — profiles (no FK deps from other tables)
1109 op.drop_index("ix_musehub_profiles_is_verified", table_name="musehub_profiles")
1110 op.drop_index("ix_musehub_profiles_username", table_name="musehub_profiles")
1111 op.drop_table("musehub_profiles")
1112
1113 # Muse Hub — webhook deliveries (depends on webhooks)
1114 op.drop_index("ix_musehub_webhook_deliveries_event_type", table_name="musehub_webhook_deliveries")
1115 op.drop_index("ix_musehub_webhook_deliveries_webhook_id", table_name="musehub_webhook_deliveries")
1116 op.drop_table("musehub_webhook_deliveries")
1117
1118 # Muse Hub — webhooks (depends on repos)
1119 op.drop_index("ix_musehub_webhooks_repo_id", table_name="musehub_webhooks")
1120 op.drop_table("musehub_webhooks")
1121
1122 # Muse Hub — release assets (depends on musehub_releases)
1123 op.drop_index(
1124 "ix_musehub_release_assets_repo_id", table_name="musehub_release_assets"
1125 )
1126 op.drop_index(
1127 "ix_musehub_release_assets_release_id", table_name="musehub_release_assets"
1128 )
1129 op.drop_table("musehub_release_assets")
1130
1131 # Muse Hub — releases (depends on repos)
1132 op.drop_index("ix_musehub_releases_tag", table_name="musehub_releases")
1133 op.drop_index("ix_musehub_releases_repo_id", table_name="musehub_releases")
1134 op.drop_table("musehub_releases")
1135
1136 # Muse Hub — sessions (depends on repos)
1137 op.drop_index("ix_musehub_sessions_is_active", table_name="musehub_sessions")
1138 op.drop_index("ix_musehub_sessions_started_at", table_name="musehub_sessions")
1139 op.drop_index("ix_musehub_sessions_repo_id", table_name="musehub_sessions")
1140 op.drop_table("musehub_sessions")
1141
1142 # Muse Hub — stars (depends on repos)
1143 op.drop_index("ix_musehub_stars_user_id", table_name="musehub_stars")
1144 op.drop_index("ix_musehub_stars_repo_id", table_name="musehub_stars")
1145 op.drop_table("musehub_stars")
1146
1147 # Muse Hub — binary artifact storage (depends on repos)
1148 op.drop_index("ix_musehub_objects_repo_id", table_name="musehub_objects")
1149 op.drop_table("musehub_objects")
1150
1151 # Muse Hub — PR review comments (depends on pull_requests)
1152 op.drop_index("ix_musehub_pr_comments_created_at", table_name="musehub_pr_comments")
1153 op.drop_index("ix_musehub_pr_comments_parent_comment_id", table_name="musehub_pr_comments")
1154 op.drop_index("ix_musehub_pr_comments_repo_id", table_name="musehub_pr_comments")
1155 op.drop_index("ix_musehub_pr_comments_pr_id", table_name="musehub_pr_comments")
1156 op.drop_table("musehub_pr_comments")
1157
1158 # Muse Hub — pull requests (depends on repos; merged_at included in table creation)
1159 op.drop_index("ix_musehub_pull_requests_state", table_name="musehub_pull_requests")
1160 op.drop_index("ix_musehub_pull_requests_repo_id", table_name="musehub_pull_requests")
1161 op.drop_table("musehub_pull_requests")
1162
1163 # Muse Hub — issue comments (depends on issues and repos)
1164 op.drop_index("ix_musehub_issue_comments_created_at", table_name="musehub_issue_comments")
1165 op.drop_index("ix_musehub_issue_comments_parent_id", table_name="musehub_issue_comments")
1166 op.drop_index("ix_musehub_issue_comments_repo_id", table_name="musehub_issue_comments")
1167 op.drop_index("ix_musehub_issue_comments_issue_id", table_name="musehub_issue_comments")
1168 op.drop_table("musehub_issue_comments")
1169
1170 # Muse Hub — issue-milestone join table (depends on issues and milestones)
1171 op.drop_index(
1172 "ix_musehub_issue_milestones_milestone_id",
1173 table_name="musehub_issue_milestones",
1174 )
1175 op.drop_table("musehub_issue_milestones")
1176
1177 # Muse Hub — issues (depends on repos and milestones)
1178 op.drop_index("ix_musehub_issues_milestone_id", table_name="musehub_issues")
1179 op.drop_index("ix_musehub_issues_state", table_name="musehub_issues")
1180 op.drop_index("ix_musehub_issues_number", table_name="musehub_issues")
1181 op.drop_index("ix_musehub_issues_repo_id", table_name="musehub_issues")
1182 op.drop_table("musehub_issues")
1183
1184 # Muse Hub — milestones (depends on repos)
1185 op.drop_index("ix_musehub_milestones_state", table_name="musehub_milestones")
1186 op.drop_index("ix_musehub_milestones_number", table_name="musehub_milestones")
1187 op.drop_index("ix_musehub_milestones_repo_id", table_name="musehub_milestones")
1188 op.drop_table("musehub_milestones")
1189
1190 # Muse Hub — commits (depends on repos)
1191 op.drop_index("ix_musehub_commits_timestamp", table_name="musehub_commits")
1192 op.drop_index("ix_musehub_commits_branch", table_name="musehub_commits")
1193 op.drop_index("ix_musehub_commits_repo_id", table_name="musehub_commits")
1194 op.drop_table("musehub_commits")
1195
1196 # Muse Hub — branches (depends on repos)
1197 op.drop_index("ix_musehub_branches_repo_id", table_name="musehub_branches")
1198 op.drop_table("musehub_branches")
1199
1200 # Muse Hub — repos (root)
1201 op.drop_index("ix_musehub_repos_owner_user_id", table_name="musehub_repos")
1202 op.drop_index("ix_musehub_repos_slug", table_name="musehub_repos")
1203 op.drop_index("ix_musehub_repos_owner", table_name="musehub_repos")
1204 op.drop_table("musehub_repos")
1205
1206 # Muse CLI — tags (depends on commits)
1207 op.drop_index("ix_muse_tags_tag", table_name="muse_tags")
1208 op.drop_index("ix_muse_tags_commit_id", table_name="muse_tags")
1209 op.drop_index("ix_muse_tags_repo_id", table_name="muse_tags")
1210 op.drop_table("muse_tags")
1211
1212 # Muse CLI — commits (depends on snapshots)
1213 op.drop_index("ix_muse_commits_parent2_commit_id", table_name="muse_commits")
1214 op.drop_index("ix_muse_commits_parent_commit_id", table_name="muse_commits")
1215 op.drop_index("ix_muse_commits_repo_id", table_name="muse_commits")
1216 op.drop_table("muse_commits")
1217
1218 op.drop_table("muse_snapshots")
1219 op.drop_table("muse_objects")
1220
1221 # Muse VCS
1222 op.drop_index("ix_muse_note_changes_phrase_id", table_name="muse_note_changes")
1223 op.drop_table("muse_note_changes")
1224 op.drop_index("ix_muse_phrases_variation_id", table_name="muse_phrases")
1225 op.drop_table("muse_phrases")
1226 op.drop_index("ix_muse_variations_parent_variation_id", table_name="muse_variations")
1227 op.drop_index("ix_muse_variations_project_id", table_name="muse_variations")
1228 op.drop_table("muse_variations")
1229
1230 # Conversations
1231 op.drop_index("ix_maestro_message_actions_message_id", table_name="maestro_message_actions")
1232 op.drop_table("maestro_message_actions")
1233 op.drop_index("ix_maestro_conversation_messages_timestamp", table_name="maestro_conversation_messages")
1234 op.drop_index("ix_maestro_conversation_messages_conversation_id", table_name="maestro_conversation_messages")
1235 op.drop_table("maestro_conversation_messages")
1236 op.drop_index("ix_maestro_conversations_updated_at", table_name="maestro_conversations")
1237 op.drop_index("ix_maestro_conversations_is_archived", table_name="maestro_conversations")
1238 op.drop_index("ix_maestro_conversations_project_id", table_name="maestro_conversations")
1239 op.drop_index("ix_maestro_conversations_user_id", table_name="maestro_conversations")
1240 op.drop_table("maestro_conversations")
1241
1242 # Auth & usage
1243 op.drop_index("ix_maestro_access_tokens_token_hash", table_name="maestro_access_tokens")
1244 op.drop_index("ix_maestro_access_tokens_user_id", table_name="maestro_access_tokens")
1245 op.drop_table("maestro_access_tokens")
1246 op.drop_index("ix_maestro_usage_logs_created_at", table_name="maestro_usage_logs")
1247 op.drop_index("ix_maestro_usage_logs_user_id", table_name="maestro_usage_logs")
1248 op.drop_table("maestro_usage_logs")
1249 op.drop_table("maestro_users")