sschuhmann/rehearshalhub
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
rehearshalhub/api/alembic/versions/0001_initial.py
Steffen Schuhmann f7be1b994d Initial commit: RehearsalHub POC 
Full-stack self-hosted band rehearsal platform:

Backend (FastAPI + SQLAlchemy 2.0 async):
- Auth with JWT (register, login, /me, settings)
- Band management with Nextcloud folder integration
- Song management with audio version tracking
- Nextcloud scan to auto-import audio files
- Band membership with link-based invite system
- Song comments
- Audio analysis worker (BPM, key, loudness, waveform)
- Nextcloud activity watcher for auto-import
- WebSocket support for real-time annotation updates
- Alembic migrations (0001–0003)
- Repository pattern, Ruff + mypy configured

Frontend (React 18 + Vite + TypeScript strict):
- Login/register page with post-login redirect
- Home page with band list and creation form
- Band page with member panel, invite link, song list, NC scan
- Song page with waveform player, annotations, comment thread
- Settings page for per-user Nextcloud credentials
- Invite acceptance page (/invite/:token)
- ESLint v9 flat config + TypeScript strict mode

Infrastructure:
- Docker Compose: PostgreSQL, Redis, API, worker, watcher, nginx
- nginx reverse proxy for static files + /api/ proxy
- make check runs all linters before docker compose build

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
2026-03-28 21:53:03 +01:00

196 lines
10 KiB
Python
Raw Permalink Blame History

"""Initial schema
Revision ID: 0001
Revises:
Create Date: 2026-03-28
"""
from typing import Sequence, Union
import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql
revision: str = "0001"
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None
def upgrade() -> None:
op.create_table(
"members",
sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("email", sa.String(320), nullable=False),
sa.Column("display_name", sa.String(255), nullable=False),
sa.Column("avatar_url", sa.Text(), nullable=True),
sa.Column("nc_username", sa.String(255), nullable=True),
sa.Column("password_hash", sa.Text(), nullable=False),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("email"),
)
op.create_index("ix_members_email", "members", ["email"])
op.create_table(
"bands",
sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("name", sa.String(255), nullable=False),
sa.Column("slug", sa.String(255), nullable=False),
sa.Column("nc_folder_path", sa.Text(), nullable=True),
sa.Column("nc_user", sa.String(255), nullable=True),
sa.Column("genre_tags", postgresql.ARRAY(sa.Text()), nullable=False, server_default="{}"),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("slug"),
)
op.create_index("ix_bands_slug", "bands", ["slug"])
op.create_table(
"band_members",
sa.Column("band_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("member_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("role", sa.String(20), nullable=False),
sa.Column("joined_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
sa.Column("instrument", sa.String(100), nullable=True),
sa.ForeignKeyConstraint(["band_id"], ["bands.id"], ondelete="CASCADE"),
sa.ForeignKeyConstraint(["member_id"], ["members.id"], ondelete="CASCADE"),
sa.PrimaryKeyConstraint("band_id", "member_id"),
sa.UniqueConstraint("band_id", "member_id", name="uq_band_member"),
)
op.create_table(
"songs",
sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("band_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("title", sa.String(500), nullable=False),
sa.Column("nc_folder_path", sa.Text(), nullable=True),
sa.Column("status", sa.String(20), nullable=False, server_default="jam"),
sa.Column("global_key", sa.String(30), nullable=True),
sa.Column("global_bpm", sa.Numeric(6, 2), nullable=True),
sa.Column("notes", sa.Text(), nullable=True),
sa.Column("created_by", postgresql.UUID(as_uuid=True), nullable=True),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
sa.ForeignKeyConstraint(["band_id"], ["bands.id"], ondelete="CASCADE"),
sa.ForeignKeyConstraint(["created_by"], ["members.id"], ondelete="SET NULL"),
sa.PrimaryKeyConstraint("id"),
)
op.create_index("ix_songs_band_id", "songs", ["band_id"])
op.create_table(
"audio_versions",
sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("song_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("version_number", sa.Integer(), nullable=False),
sa.Column("label", sa.String(255), nullable=True),
sa.Column("nc_file_path", sa.Text(), nullable=False),
sa.Column("nc_file_etag", sa.String(255), nullable=True),
sa.Column("cdn_hls_base", sa.Text(), nullable=True),
sa.Column("waveform_url", sa.Text(), nullable=True),
sa.Column("duration_ms", sa.Integer(), nullable=True),
sa.Column("format", sa.String(10), nullable=True),
sa.Column("file_size_bytes", sa.BigInteger(), nullable=True),
sa.Column("analysis_status", sa.String(20), nullable=False, server_default="pending"),
sa.Column("uploaded_by", postgresql.UUID(as_uuid=True), nullable=True),
sa.Column("uploaded_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
sa.ForeignKeyConstraint(["song_id"], ["songs.id"], ondelete="CASCADE"),
sa.ForeignKeyConstraint(["uploaded_by"], ["members.id"], ondelete="SET NULL"),
sa.PrimaryKeyConstraint("id"),
)
op.create_index("ix_audio_versions_song_id", "audio_versions", ["song_id"])
op.create_table(
"annotations",
sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("version_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("author_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("type", sa.String(10), nullable=False),
sa.Column("timestamp_ms", sa.Integer(), nullable=False),
sa.Column("range_end_ms", sa.Integer(), nullable=True),
sa.Column("body", sa.Text(), nullable=True),
sa.Column("voice_note_url", sa.Text(), nullable=True),
sa.Column("label", sa.String(255), nullable=True),
sa.Column("tags", postgresql.ARRAY(sa.Text()), nullable=False, server_default="{}"),
sa.Column("parent_id", postgresql.UUID(as_uuid=True), nullable=True),
sa.Column("resolved", sa.Boolean(), nullable=False, server_default="false"),
sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
sa.ForeignKeyConstraint(["author_id"], ["members.id"], ondelete="CASCADE"),
sa.ForeignKeyConstraint(["parent_id"], ["annotations.id"], ondelete="SET NULL"),
sa.ForeignKeyConstraint(["version_id"], ["audio_versions.id"], ondelete="CASCADE"),
sa.PrimaryKeyConstraint("id"),
)
op.create_index("ix_annotations_version_id", "annotations", ["version_id"])
op.create_table(
"range_analyses",
sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("annotation_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("version_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("start_ms", sa.Integer(), nullable=False),
sa.Column("end_ms", sa.Integer(), nullable=False),
sa.Column("bpm", sa.Numeric(7, 2), nullable=True),
sa.Column("bpm_confidence", sa.Numeric(4, 3), nullable=True),
sa.Column("key", sa.String(30), nullable=True),
sa.Column("key_confidence", sa.Numeric(4, 3), nullable=True),
sa.Column("scale", sa.String(10), nullable=True),
sa.Column("avg_loudness_lufs", sa.Numeric(6, 2), nullable=True),
sa.Column("peak_loudness_dbfs", sa.Numeric(6, 2), nullable=True),
sa.Column("spectral_centroid", sa.Numeric(10, 2), nullable=True),
sa.Column("energy", sa.Numeric(5, 4), nullable=True),
sa.Column("danceability", sa.Numeric(5, 4), nullable=True),
sa.Column("chroma_vector", postgresql.ARRAY(sa.Numeric()), nullable=True),
sa.Column("mfcc_mean", postgresql.ARRAY(sa.Numeric()), nullable=True),
sa.Column("analysis_version", sa.String(20), nullable=True),
sa.Column("computed_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
sa.ForeignKeyConstraint(["annotation_id"], ["annotations.id"], ondelete="CASCADE"),
sa.ForeignKeyConstraint(["version_id"], ["audio_versions.id"], ondelete="CASCADE"),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("annotation_id"),
)
op.create_index("ix_range_analyses_version_id", "range_analyses", ["version_id"])
op.create_table(
"reactions",
sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("annotation_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("member_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("emoji", sa.String(10), nullable=False),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
sa.ForeignKeyConstraint(["annotation_id"], ["annotations.id"], ondelete="CASCADE"),
sa.ForeignKeyConstraint(["member_id"], ["members.id"], ondelete="CASCADE"),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("annotation_id", "member_id", "emoji", name="uq_reaction"),
)
op.create_table(
"jobs",
sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("type", sa.String(50), nullable=False),
sa.Column("payload", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
sa.Column("status", sa.String(20), nullable=False, server_default="queued"),
sa.Column("attempt", sa.Integer(), nullable=False, server_default="0"),
sa.Column("error", sa.Text(), nullable=True),
sa.Column("queued_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
sa.Column("started_at", sa.DateTime(timezone=True), nullable=True),
sa.Column("finished_at", sa.DateTime(timezone=True), nullable=True),
sa.PrimaryKeyConstraint("id"),
)
op.create_index("ix_jobs_type", "jobs", ["type"])
op.create_index("ix_jobs_status", "jobs", ["status"])
def downgrade() -> None:
op.drop_table("jobs")
op.drop_table("reactions")
op.drop_table("range_analyses")
op.drop_table("annotations")
op.drop_table("audio_versions")
op.drop_table("songs")
op.drop_table("band_members")
op.drop_table("bands")
op.drop_table("members")
Reference in New Issue View Git Blame Copy Permalink