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