76c867fca4
- shared/ = portable Android-origin sources vendored from deferred/desktop-server (app/build.gradle.kts srcDir repointed; PlaybackController.kt excluded as Android-only) - backend/ = bundled-lite engine (SQLite + inline queue); .venv symlinked from the old checkout, PYTHONPATH pins THIS backend's code over any editable install - repoRoot() resolves this project dir (env SHONAR_REPO still wins); desktop-dev.sh watches shared/ + backend/ - Verified: :app:compileKotlin + :app:test green (23 tests); engine boots on :8010, self-migrates, /healthz ok
281 lines
16 KiB
Python
281 lines
16 KiB
Python
"""initial schema
|
|
|
|
Revision ID: 8d51af959ae4
|
|
Revises:
|
|
Create Date: 2026-09-08 13:07:10.455837
|
|
|
|
"""
|
|
from __future__ import annotations
|
|
|
|
from collections.abc import Sequence
|
|
|
|
import sqlalchemy as sa
|
|
from alembic import op
|
|
from sqlalchemy.dialects import postgresql
|
|
|
|
revision: str = '8d51af959ae4'
|
|
down_revision: str | None = None
|
|
branch_labels: str | Sequence[str] | None = None
|
|
depends_on: str | Sequence[str] | None = None
|
|
|
|
|
|
def _json_type() -> sa.types.TypeEngine:
|
|
"""JSONB on PostgreSQL, plain JSON on SQLite (desktop bundled engine)."""
|
|
if op.get_context().dialect.name == "postgresql":
|
|
return postgresql.JSONB(astext_type=sa.Text())
|
|
return sa.JSON()
|
|
|
|
|
|
def upgrade() -> None:
|
|
# ### commands auto generated by Alembic - please adjust! ###
|
|
op.create_table('users',
|
|
sa.Column('email', sa.String(length=320), nullable=False),
|
|
sa.Column('password_hash', sa.String(length=255), nullable=False),
|
|
sa.Column('display_name', sa.String(length=120), nullable=True),
|
|
sa.Column('is_active', sa.Boolean(), nullable=False),
|
|
sa.Column('location_storage_enabled', sa.Boolean(), nullable=False),
|
|
sa.Column('deleted_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('id', sa.Uuid(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_users_email'), 'users', ['email'], unique=True)
|
|
op.create_table('devices',
|
|
sa.Column('user_id', sa.Uuid(), nullable=False),
|
|
sa.Column('name', sa.String(length=120), nullable=False),
|
|
sa.Column('platform', sa.String(length=40), nullable=False),
|
|
sa.Column('last_seen_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('revoked_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('id', sa.Uuid(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_devices_user_id'), 'devices', ['user_id'], unique=False)
|
|
op.create_table('tags',
|
|
sa.Column('user_id', sa.Uuid(), nullable=False),
|
|
sa.Column('name', sa.String(length=80), nullable=False),
|
|
sa.Column('id', sa.Uuid(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('user_id', 'name')
|
|
)
|
|
op.create_index(op.f('ix_tags_user_id'), 'tags', ['user_id'], unique=False)
|
|
op.create_table('recordings',
|
|
sa.Column('user_id', sa.Uuid(), nullable=False),
|
|
sa.Column('device_id', sa.Uuid(), nullable=True),
|
|
sa.Column('client_recording_id', sa.String(length=64), nullable=True),
|
|
sa.Column('title', sa.String(length=300), nullable=False),
|
|
sa.Column('recorded_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('duration_seconds', sa.Float(), nullable=False),
|
|
sa.Column('notes', sa.Text(), nullable=True),
|
|
sa.Column('latitude', sa.Float(), nullable=True),
|
|
sa.Column('longitude', sa.Float(), nullable=True),
|
|
sa.Column('location_accuracy_m', sa.Float(), nullable=True),
|
|
sa.Column('processing_status', sa.Enum('pending_upload', 'uploaded', 'queued', 'processing', 'completed', 'failed', 'ai_disabled', name='processing_status'), nullable=False),
|
|
sa.Column('processing_error', sa.Text(), nullable=True),
|
|
sa.Column('deleted_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('id', sa.Uuid(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(['device_id'], ['devices.id'], ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_recordings_client_recording_id'), 'recordings', ['client_recording_id'], unique=False)
|
|
op.create_index(op.f('ix_recordings_processing_status'), 'recordings', ['processing_status'], unique=False)
|
|
op.create_index('ix_recordings_user_client_id', 'recordings', ['user_id', 'client_recording_id'], unique=True, postgresql_where='client_recording_id IS NOT NULL', sqlite_where=sa.text('client_recording_id IS NOT NULL'))
|
|
op.create_index(op.f('ix_recordings_user_id'), 'recordings', ['user_id'], unique=False)
|
|
op.create_index('ix_recordings_user_recorded', 'recordings', ['user_id', 'recorded_at'], unique=False)
|
|
op.create_table('refresh_tokens',
|
|
sa.Column('user_id', sa.Uuid(), nullable=False),
|
|
sa.Column('token_hash', sa.String(length=128), nullable=False),
|
|
sa.Column('family', sa.Uuid(), nullable=False),
|
|
sa.Column('device_id', sa.Uuid(), nullable=True),
|
|
sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('revoked_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('replaced_by', sa.Uuid(), nullable=True),
|
|
sa.Column('id', sa.Uuid(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(['device_id'], ['devices.id'], ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['replaced_by'], ['refresh_tokens.id'], ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('token_hash')
|
|
)
|
|
op.create_index(op.f('ix_refresh_tokens_family'), 'refresh_tokens', ['family'], unique=False)
|
|
op.create_index(op.f('ix_refresh_tokens_user_id'), 'refresh_tokens', ['user_id'], unique=False)
|
|
op.create_table('assets',
|
|
sa.Column('recording_id', sa.Uuid(), nullable=True),
|
|
sa.Column('user_id', sa.Uuid(), nullable=False),
|
|
sa.Column('kind', sa.Enum('original', 'normalized', 'export', name='asset_kind'), nullable=False),
|
|
sa.Column('storage_key', sa.String(length=500), nullable=False),
|
|
sa.Column('mime_type', sa.String(length=100), nullable=False),
|
|
sa.Column('size_bytes', sa.BigInteger(), nullable=False),
|
|
sa.Column('checksum_sha256', sa.String(length=64), nullable=False),
|
|
sa.Column('id', sa.Uuid(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(['recording_id'], ['recordings.id'], ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_assets_recording_id'), 'assets', ['recording_id'], unique=False)
|
|
op.create_index(op.f('ix_assets_user_id'), 'assets', ['user_id'], unique=False)
|
|
op.create_index('uq_assets_one_original', 'assets', ['recording_id'], unique=True, postgresql_where="kind = 'original'", sqlite_where=sa.text("kind = 'original'"))
|
|
op.create_table('processing_jobs',
|
|
sa.Column('recording_id', sa.Uuid(), nullable=False),
|
|
sa.Column('job_type', sa.Enum('normalize_audio', 'transcribe', 'summarize', name='job_type'), nullable=False),
|
|
sa.Column('status', sa.Enum('queued', 'running', 'succeeded', 'failed', 'skipped', name='job_status'), nullable=False),
|
|
sa.Column('attempt', sa.Integer(), nullable=False),
|
|
sa.Column('max_attempts', sa.Integer(), nullable=False),
|
|
sa.Column('error', sa.Text(), nullable=True),
|
|
sa.Column('started_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('finished_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('task_handle', sa.String(length=120), nullable=True),
|
|
sa.Column('id', sa.Uuid(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(['recording_id'], ['recordings.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_processing_jobs_recording_id'), 'processing_jobs', ['recording_id'], unique=False)
|
|
op.create_index('ix_processing_jobs_recording_type', 'processing_jobs', ['recording_id', 'job_type'], unique=False)
|
|
op.create_index(op.f('ix_processing_jobs_status'), 'processing_jobs', ['status'], unique=False)
|
|
op.create_table('recording_tags',
|
|
sa.Column('recording_id', sa.Uuid(), nullable=False),
|
|
sa.Column('tag_id', sa.Uuid(), nullable=False),
|
|
sa.ForeignKeyConstraint(['recording_id'], ['recordings.id'], ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['tag_id'], ['tags.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('recording_id', 'tag_id')
|
|
)
|
|
op.create_table('summaries',
|
|
sa.Column('recording_id', sa.Uuid(), nullable=False),
|
|
sa.Column('version', sa.Integer(), nullable=False),
|
|
sa.Column('superseded_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('provider', sa.String(length=80), nullable=False),
|
|
sa.Column('model', sa.String(length=120), nullable=True),
|
|
sa.Column('content', _json_type(), nullable=False),
|
|
sa.Column('edited_by_user', sa.Boolean(), nullable=False),
|
|
sa.Column('id', sa.Uuid(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(['recording_id'], ['recordings.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_summaries_recording_id'), 'summaries', ['recording_id'], unique=False)
|
|
op.create_table('transcripts',
|
|
sa.Column('recording_id', sa.Uuid(), nullable=False),
|
|
sa.Column('version', sa.Integer(), nullable=False),
|
|
sa.Column('superseded_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('language', sa.String(length=16), nullable=True),
|
|
sa.Column('provider', sa.String(length=80), nullable=False),
|
|
sa.Column('model', sa.String(length=120), nullable=True),
|
|
sa.Column('text', sa.Text(), nullable=False),
|
|
sa.Column('segments', _json_type(), nullable=True),
|
|
sa.Column('edited_by_user', sa.Boolean(), nullable=False),
|
|
sa.Column('id', sa.Uuid(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(['recording_id'], ['recordings.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_transcripts_recording_id'), 'transcripts', ['recording_id'], unique=False)
|
|
op.create_index('ix_transcripts_recording_version', 'transcripts', ['recording_id', 'version'], unique=False)
|
|
op.create_table('export_jobs',
|
|
sa.Column('user_id', sa.Uuid(), nullable=False),
|
|
sa.Column('recording_id', sa.Uuid(), nullable=False),
|
|
sa.Column('export_type', sa.String(length=40), nullable=False),
|
|
sa.Column('status', sa.Enum('queued', 'running', 'succeeded', 'failed', 'skipped', name='export_job_status'), nullable=False),
|
|
sa.Column('asset_id', sa.Uuid(), nullable=True),
|
|
sa.Column('error', sa.Text(), nullable=True),
|
|
sa.Column('id', sa.Uuid(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(['asset_id'], ['assets.id'], ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['recording_id'], ['recordings.id'], ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_export_jobs_recording_id'), 'export_jobs', ['recording_id'], unique=False)
|
|
op.create_index(op.f('ix_export_jobs_user_id'), 'export_jobs', ['user_id'], unique=False)
|
|
op.create_table('upload_sessions',
|
|
sa.Column('user_id', sa.Uuid(), nullable=False),
|
|
sa.Column('recording_id', sa.Uuid(), nullable=True),
|
|
sa.Column('client_recording_id', sa.String(length=64), nullable=True),
|
|
sa.Column('title', sa.String(length=300), nullable=True),
|
|
sa.Column('declared_mime_type', sa.String(length=100), nullable=False),
|
|
sa.Column('declared_size_bytes', sa.BigInteger(), nullable=False),
|
|
sa.Column('chunk_size_bytes', sa.BigInteger(), nullable=False),
|
|
sa.Column('status', sa.Enum('open', 'finalizing', 'completed', 'aborted', 'expired', name='upload_session_status'), nullable=False),
|
|
sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('completed_asset_id', sa.Uuid(), nullable=True),
|
|
sa.Column('id', sa.Uuid(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(['completed_asset_id'], ['assets.id'], ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['recording_id'], ['recordings.id'], ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_upload_sessions_user_id'), 'upload_sessions', ['user_id'], unique=False)
|
|
op.create_table('upload_chunks',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('session_id', sa.Uuid(), nullable=False),
|
|
sa.Column('chunk_index', sa.Integer(), nullable=False),
|
|
sa.Column('size_bytes', sa.BigInteger(), nullable=False),
|
|
sa.Column('checksum_sha256', sa.String(length=64), nullable=False),
|
|
sa.Column('storage_key', sa.String(length=500), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(['session_id'], ['upload_sessions.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('session_id', 'chunk_index')
|
|
)
|
|
op.create_index(op.f('ix_upload_chunks_session_id'), 'upload_chunks', ['session_id'], unique=False)
|
|
# ### end Alembic commands ###
|
|
|
|
|
|
def downgrade() -> None:
|
|
# ### commands auto generated by Alembic - please adjust! ###
|
|
op.drop_index(op.f('ix_upload_chunks_session_id'), table_name='upload_chunks')
|
|
op.drop_table('upload_chunks')
|
|
op.drop_index(op.f('ix_upload_sessions_user_id'), table_name='upload_sessions')
|
|
op.drop_table('upload_sessions')
|
|
op.drop_index(op.f('ix_export_jobs_user_id'), table_name='export_jobs')
|
|
op.drop_index(op.f('ix_export_jobs_recording_id'), table_name='export_jobs')
|
|
op.drop_table('export_jobs')
|
|
op.drop_index('ix_transcripts_recording_version', table_name='transcripts')
|
|
op.drop_index(op.f('ix_transcripts_recording_id'), table_name='transcripts')
|
|
op.drop_table('transcripts')
|
|
op.drop_index(op.f('ix_summaries_recording_id'), table_name='summaries')
|
|
op.drop_table('summaries')
|
|
op.drop_table('recording_tags')
|
|
op.drop_index(op.f('ix_processing_jobs_status'), table_name='processing_jobs')
|
|
op.drop_index('ix_processing_jobs_recording_type', table_name='processing_jobs')
|
|
op.drop_index(op.f('ix_processing_jobs_recording_id'), table_name='processing_jobs')
|
|
op.drop_table('processing_jobs')
|
|
op.drop_index('uq_assets_one_original', table_name='assets', postgresql_where="kind = 'original'")
|
|
op.drop_index(op.f('ix_assets_user_id'), table_name='assets')
|
|
op.drop_index(op.f('ix_assets_recording_id'), table_name='assets')
|
|
op.drop_table('assets')
|
|
op.drop_index(op.f('ix_refresh_tokens_user_id'), table_name='refresh_tokens')
|
|
op.drop_index(op.f('ix_refresh_tokens_family'), table_name='refresh_tokens')
|
|
op.drop_table('refresh_tokens')
|
|
op.drop_index('ix_recordings_user_recorded', table_name='recordings')
|
|
op.drop_index(op.f('ix_recordings_user_id'), table_name='recordings')
|
|
op.drop_index('ix_recordings_user_client_id', table_name='recordings', postgresql_where='client_recording_id IS NOT NULL')
|
|
op.drop_index(op.f('ix_recordings_processing_status'), table_name='recordings')
|
|
op.drop_index(op.f('ix_recordings_client_recording_id'), table_name='recordings')
|
|
op.drop_table('recordings')
|
|
op.drop_index(op.f('ix_tags_user_id'), table_name='tags')
|
|
op.drop_table('tags')
|
|
op.drop_index(op.f('ix_devices_user_id'), table_name='devices')
|
|
op.drop_table('devices')
|
|
op.drop_index(op.f('ix_users_email'), table_name='users')
|
|
op.drop_table('users')
|
|
# ### end Alembic commands ###
|