DailBand_ERD

Tables (29)

analysis_results

  • analysis_result_id — BIGINT — Primary key — NOT NULL
  • session_participant_id — BIGINT — Foreign key → session_participants.session_participant_id — NOT NULL
  • input_audio_file_id — BIGINT — Foreign key → files.file_id — NOT NULL
  • answer_midi_file_id — BIGINT — Foreign key → files.file_id — NOT NULL
  • feedback_file_id — BIGINT — Foreign key → files.file_id
  • transcribed_midi_file_id — BIGINT — Foreign key → files.file_id
  • status — VARCHAR(14) — NOT NULL
  • instrument — VARCHAR(20) — NOT NULL
  • mode — VARCHAR(24) — NOT NULL
  • pitch_score — INTEGER
  • timing_score — INTEGER
  • llm_status — VARCHAR(12)
  • one_line — VARCHAR(500)
  • contract_version — VARCHAR(40) — NOT NULL
  • rule_version — VARCHAR(80)
  • llm_model — VARCHAR(100)
  • prompt_version — VARCHAR(80)
  • llm_error_message — VARCHAR(500)
  • manifest_json — JSONB — NOT NULL
  • manifest_sha256 — CHAR(64) — NOT NULL
  • error_code — VARCHAR(80)
  • error_message — VARCHAR(500)
  • created_at — TIMESTAMPTZ — NOT NULL
  • dispatched_at — TIMESTAMPTZ
  • completed_at — TIMESTAMPTZ

archive_job_assets

  • asset_role — VARCHAR(20) — Primary key — NOT NULL
  • archive_job_id — BIGINT — Foreign key → archive_jobs.archive_job_id — NOT NULL
  • file_id — BIGINT — Foreign key → files.file_id — NOT NULL
  • owner_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • source_session_participant_id — BIGINT — NOT NULL
  • instrument — VARCHAR(20)

archive_jobs

  • archive_job_id — BIGINT — Primary key — NOT NULL
  • source_session_id — BIGINT — NOT NULL
  • owner_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • song_id — BIGINT — Foreign key → songs.song_id
  • track_id — BIGINT — Foreign key → tracks.track_id
  • session_mix_file_id — BIGINT — Foreign key → files.file_id
  • bpm — INTEGER
  • performance_type — VARCHAR(10) — NOT NULL
  • performance_song_title — VARCHAR(100)
  • performance_song_artist — VARCHAR(100)
  • title — VARCHAR(100) — NOT NULL
  • description — TEXT
  • status — VARCHAR(12) — NOT NULL
  • attempt_count — INTEGER — NOT NULL
  • lease_until — TIMESTAMPTZ
  • cleanup_hold — BOOLEAN — NOT NULL
  • error_code — VARCHAR(80)
  • error_message — VARCHAR(500)
  • recorded_at — TIMESTAMPTZ — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

files

  • file_id — BIGINT — Primary key — NOT NULL
  • storage_key — VARCHAR(512) — NOT NULL
  • original_name — VARCHAR(255)
  • content_type — VARCHAR(100)
  • size_bytes — BIGINT
  • duration_ms — INTEGER
  • sha256 — CHAR(64)
  • verified_at — TIMESTAMPTZ
  • status — VARCHAR(20) — NOT NULL
  • delete_requested_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL

ghost_prefetch_acks

  • participation_epoch — BIGINT — Primary key — NOT NULL
  • manifest_version — BIGINT — Primary key — NOT NULL
  • room_id — BIGINT — Foreign key → participants.room_id — NOT NULL
  • user_id — BIGINT — Foreign key → participants.user_id — NOT NULL
  • acked_at — TIMESTAMPTZ — NOT NULL

ghosts

  • ghost_id — BIGINT — Primary key — NOT NULL
  • file_id — BIGINT — Foreign key → files.file_id — NOT NULL
  • instrument — VARCHAR(20) — NOT NULL
  • character_type — VARCHAR(30)
  • status — VARCHAR(20) — NOT NULL
  • delete_requested_at — TIMESTAMPTZ
  • delete_requested_by — BIGINT — Foreign key → users.user_id
  • purged_at — TIMESTAMPTZ
  • purge_error — VARCHAR(500)
  • created_at — TIMESTAMPTZ — NOT NULL

media_jobs

  • job_id — BIGINT — Primary key — NOT NULL
  • job_type — VARCHAR(24) — NOT NULL
  • session_result_id — BIGINT — Foreign key → session_results.session_result_id
  • upload_id — BIGINT — Foreign key → uploads.upload_id
  • analysis_result_id — BIGINT — Foreign key → analysis_results.analysis_result_id
  • attempt — INTEGER — NOT NULL
  • status — VARCHAR(12) — NOT NULL
  • callback_token_key_id — VARCHAR(40)
  • callback_token_hash — CHAR(64)
  • callback_token_expires_at — TIMESTAMPTZ
  • terminal_callback_sha256 — CHAR(64)
  • callback_received_at — TIMESTAMPTZ
  • manifest_json — JSONB — NOT NULL
  • manifest_sha256 — CHAR(64) — NOT NULL
  • retryable — BOOLEAN — NOT NULL
  • error_code — VARCHAR(80)
  • error_message — VARCHAR(500)
  • created_at — TIMESTAMPTZ — NOT NULL
  • dispatched_at — TIMESTAMPTZ
  • completed_at — TIMESTAMPTZ

participants

  • room_id — BIGINT — Foreign key → rooms.room_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • role — VARCHAR(6) — NOT NULL
  • ready — BOOLEAN — NOT NULL
  • joined_at — TIMESTAMPTZ — NOT NULL
  • left_at — TIMESTAMPTZ
  • leave_type — VARCHAR(12)
  • last_seen_at — TIMESTAMPTZ
  • participation_epoch — BIGINT — NOT NULL
  • connection_generation — BIGINT — NOT NULL
  • realtime_connection_generation — BIGINT — NOT NULL
  • endpoint_generation — BIGINT — NOT NULL
  • active_endpoint_generation — BIGINT — NOT NULL
  • media_peer_id — UUID
  • latest_connection_id — UUID
  • active_connection_id — UUID

results

  • result_id — BIGINT — Primary key — NOT NULL
  • source_session_participant_id — BIGINT
  • instrument — VARCHAR(20)
  • offset_ms — INTEGER — NOT NULL
  • analysis_status — VARCHAR(14) — NOT NULL
  • pitch_score — INTEGER
  • timing_score — INTEGER
  • one_line — VARCHAR(500)
  • error_message — VARCHAR(500)
  • recorded_at — TIMESTAMPTZ — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

results_files

  • result_id — BIGINT — Foreign key → results.result_id — NOT NULL
  • file_id — BIGINT — Foreign key → files.file_id — NOT NULL
  • file_role — VARCHAR(20) — NOT NULL

room_ghost_config_items

  • room_id — BIGINT — Foreign key → room_ghost_configs.room_id — NOT NULL
  • ghost_id — BIGINT — Foreign key → ghosts.ghost_id — NOT NULL
  • instrument — VARCHAR(20) — NOT NULL
  • selection_mode — VARCHAR(20) — NOT NULL

room_ghost_configs

  • room_id — BIGINT — Foreign key → rooms.room_id — NOT NULL
  • upload_id — BIGINT — Foreign key → uploads.upload_id
  • manifest_version — BIGINT — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

rooms

  • room_id — BIGINT — Primary key — NOT NULL
  • title — VARCHAR(100) — NOT NULL
  • mode — VARCHAR(10) — NOT NULL
  • bpm — INTEGER
  • time_signature — VARCHAR(5) — NOT NULL
  • max_participants — INTEGER — NOT NULL
  • status — VARCHAR(10) — NOT NULL
  • phase — VARCHAR(10) — NOT NULL
  • event_version — BIGINT — NOT NULL
  • reference_song_id — BIGINT — Foreign key → songs.song_id
  • current_session_id — BIGINT — Foreign key → sessions.session_id
  • performance_song_title — VARCHAR(100)
  • performance_song_artist — VARCHAR(100)
  • created_at — TIMESTAMPTZ — NOT NULL
  • closed_at — TIMESTAMPTZ

rooms_sessions

  • room_id — BIGINT — Foreign key → rooms.room_id — NOT NULL
  • session_id — BIGINT — Foreign key → sessions.session_id — NOT NULL

session_participants

  • session_participant_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → sessions.session_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • participation_epoch — BIGINT — NOT NULL
  • instrument — VARCHAR(20)
  • role — VARCHAR(6) — NOT NULL
  • joined_at_snapshot — TIMESTAMPTZ — NOT NULL

session_results

  • session_result_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → sessions.session_id — NOT NULL
  • mix_file_id — BIGINT — Foreign key → files.file_id
  • status — VARCHAR(12) — NOT NULL
  • manifest_version — INTEGER
  • manifest_json — JSONB
  • manifest_sha256 — CHAR(64)
  • retryable — BOOLEAN — NOT NULL
  • error_code — VARCHAR(80)
  • error_message — VARCHAR(500)
  • created_at — TIMESTAMPTZ — NOT NULL
  • completed_at — TIMESTAMPTZ

session_sources

  • session_source_id — BIGINT — Primary key — NOT NULL
  • session_participant_id — BIGINT — Foreign key → session_participants.session_participant_id — NOT NULL
  • file_id — BIGINT — Foreign key → files.file_id
  • object_key — VARCHAR(512)
  • status — VARCHAR(16) — NOT NULL
  • size_bytes — BIGINT
  • sha256 — CHAR(64)
  • upload_issued_at — TIMESTAMPTZ
  • completed_at — TIMESTAMPTZ

sessions

  • session_id — BIGINT — Primary key — NOT NULL
  • song_id — BIGINT — Foreign key → songs.song_id
  • bpm — INTEGER
  • metronome_on — BOOLEAN — NOT NULL
  • status — VARCHAR(12) — NOT NULL
  • count_in_starts_at — TIMESTAMPTZ
  • recording_starts_at — TIMESTAMPTZ
  • count_in_beats — INTEGER
  • max_duration_seconds — INTEGER
  • timing_version — INTEGER
  • end_reason — VARCHAR(20)
  • started_at — TIMESTAMPTZ — NOT NULL
  • ended_at — TIMESTAMPTZ

sessions_ghosts

  • ghost_id — BIGINT — Foreign key → ghosts.ghost_id — NOT NULL
  • session_id — BIGINT — Foreign key → sessions.session_id — NOT NULL

songs

  • song_id — BIGINT — Primary key — NOT NULL
  • title — VARCHAR(100) — NOT NULL
  • artist — VARCHAR(100) — NOT NULL
  • default_bpm — INTEGER
  • duration_sec — INTEGER
  • seed_key — VARCHAR(64)
  • created_at — TIMESTAMPTZ — NOT NULL

songs_files

  • file_id — BIGINT — Foreign key → files.file_id — NOT NULL
  • song_id — BIGINT — Foreign key → songs.song_id — NOT NULL
  • file_role — VARCHAR(20) — NOT NULL
  • instrument — VARCHAR(20)

tracks

  • track_id — BIGINT — Primary key — NOT NULL
  • song_id — BIGINT — Foreign key → songs.song_id
  • source_session_id — BIGINT — NOT NULL
  • bpm — INTEGER
  • performance_type — VARCHAR(10) — NOT NULL
  • performance_song_title — VARCHAR(100)
  • performance_song_artist — VARCHAR(100)
  • recorded_at — TIMESTAMPTZ — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

tracks_files

  • track_id — BIGINT — Foreign key → tracks.track_id — NOT NULL
  • file_id — BIGINT — Foreign key → files.file_id — NOT NULL
  • file_role — VARCHAR(20) — NOT NULL

tracks_results

  • track_id — BIGINT — Foreign key → tracks.track_id — NOT NULL
  • result_id — BIGINT — Foreign key → results.result_id — NOT NULL

upload_ghosts

  • ghost_id — BIGINT — Foreign key → ghosts.ghost_id — NOT NULL
  • upload_id — BIGINT — Foreign key → uploads.upload_id — NOT NULL

uploads

  • upload_id — BIGINT — Primary key — NOT NULL
  • room_id — BIGINT — Foreign key → rooms.room_id — NOT NULL
  • source_file_id — BIGINT — Foreign key → files.file_id
  • uploader_user_id — BIGINT — Foreign key → users.user_id
  • original_name — VARCHAR(255)
  • object_key — VARCHAR(512)
  • expected_content_type — VARCHAR(100)
  • expected_size_bytes — BIGINT
  • expected_sha256 — CHAR(64)
  • signature — VARCHAR(20)
  • bpm — INTEGER
  • offset_value — INTEGER
  • status — VARCHAR(20) — NOT NULL
  • error_message — VARCHAR(500)
  • delete_requested_at — TIMESTAMPTZ
  • delete_requested_by — BIGINT — Foreign key → users.user_id
  • purged_at — TIMESTAMPTZ
  • purge_error — VARCHAR(500)
  • created_at — TIMESTAMPTZ — NOT NULL
  • processed_at — TIMESTAMPTZ

users

  • user_id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • nickname — VARCHAR(30) — NOT NULL
  • instrument — VARCHAR(20)
  • character_type — VARCHAR(30)
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

users_results

  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • result_id — BIGINT — Foreign key → results.result_id — NOT NULL

users_tracks

  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • track_id — BIGINT — Foreign key → tracks.track_id — NOT NULL
  • title — VARCHAR(100) — NOT NULL
  • description — TEXT
  • deleted_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL