speakle

Tables (48)

album

  • id — BIGSERIAL
  • name — TEXT — NOT NULL
  • release_date — DATE
  • spotify_album_id — TEXT
  • meta_json — JSONB

app_user

  • id — BIGSERIAL
  • email_enc — BYTEA — NOT NULL
  • email_hash — CHAR(64) — NOT NULL
  • password_hash — TEXT
  • display_name — TEXT
  • phone_enc — BYTEA
  • status — TEXT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

artist

  • id — BIGSERIAL
  • name — TEXT — NOT NULL
  • spotify_artist_id — TEXT
  • country — TEXT
  • meta_json — JSONB

attendance

  • id — BIGSERIAL
  • user_id — BIGINT — NOT NULL
  • date — DATE — NOT NULL
  • streak_after — INT
  • created_at — TIMESTAMPTZ — NOT NULL

auth_provider

  • id — BIGSERIAL
  • code — TEXT — NOT NULL
  • name — TEXT — NOT NULL

badge

  • id — BIGSERIAL
  • code — TEXT — NOT NULL
  • name — TEXT — NOT NULL
  • description — TEXT

cloze_attempt

  • id — BIGSERIAL
  • user_id — BIGINT — NOT NULL
  • song_id — BIGINT — NOT NULL
  • question_json — JSONB — NOT NULL
  • user_answer_json — JSONB
  • score — INT
  • created_at — TIMESTAMPTZ — NOT NULL

content_unit

  • id — BIGSERIAL
  • type — TEXT — NOT NULL
  • text — TEXT — NOT NULL
  • lang — TEXT — NOT NULL
  • phonetics — TEXT
  • pos — TEXT
  • difficulty — TEXT
  • explanation_ko — TEXT
  • examples_json — JSONB
  • source — TEXT — NOT NULL

dictation_attempt

  • id — BIGSERIAL
  • user_id — BIGINT — NOT NULL
  • song_id — BIGINT — NOT NULL
  • line_no — INT — NOT NULL
  • audio_segment_ms — JSONB
  • user_answer — TEXT
  • correct_text — TEXT — NOT NULL
  • score — INT
  • mistakes_json — JSONB
  • created_at — TIMESTAMPTZ — NOT NULL

generated_term

  • id — BIGSERIAL
  • query_id — BIGINT — NOT NULL
  • type — TEXT — NOT NULL
  • text — TEXT — NOT NULL
  • lang — TEXT — NOT NULL
  • pos — TEXT
  • weight — NUMERIC(6,3)

genre

  • id — BIGSERIAL
  • name — TEXT — NOT NULL

leaderboard_snapshot

  • id — BIGSERIAL
  • period — TEXT — NOT NULL
  • as_of_date — DATE — NOT NULL
  • board_json — JSONB — NOT NULL
  • generated_at — TIMESTAMPTZ — NOT NULL

learning_item

  • id — BIGSERIAL
  • user_id — BIGINT — NOT NULL
  • unit_id — BIGINT — NOT NULL
  • origin_song_id — BIGINT
  • status — TEXT — NOT NULL
  • srs_ef — NUMERIC(4,2)
  • srs_interval_days — INT
  • srs_due_on — DATE
  • last_result — SMALLINT
  • last_review_at — TIMESTAMPTZ

llm_prompt

  • id — BIGSERIAL
  • query_id — BIGINT — NOT NULL
  • model — TEXT — NOT NULL
  • prompt_text — TEXT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

llm_response

  • id — BIGSERIAL
  • prompt_id — BIGINT — NOT NULL
  • raw_text — TEXT — NOT NULL
  • latency_ms — INT
  • token_usage_json — JSONB
  • created_at — TIMESTAMPTZ — NOT NULL

lyric_timeline

  • id — BIGSERIAL
  • song_id — BIGINT — NOT NULL
  • line_no — INT — NOT NULL
  • start_ms — INT
  • end_ms — INT
  • text_raw — TEXT — NOT NULL
  • text_tokens_json — JSONB

lyric_translation

  • id — BIGSERIAL
  • song_id — BIGINT — NOT NULL
  • lang — TEXT — NOT NULL
  • translated_text — TEXT — NOT NULL
  • provider — TEXT — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

media_asset

  • id — BIGSERIAL
  • type — TEXT — NOT NULL
  • bucket — TEXT — NOT NULL
  • object_key_enc — BYTEA — NOT NULL
  • size_bytes — BIGINT
  • checksum_sha256 — CHAR(64)
  • s3_version_id — TEXT
  • kms_key_alias — TEXT
  • created_by — BIGINT
  • created_at — TIMESTAMPTZ — NOT NULL

password_history

  • id — BIGSERIAL
  • user_id — BIGINT — NOT NULL
  • password_hash — TEXT — NOT NULL
  • changed_at — TIMESTAMPTZ — NOT NULL

playlist

  • id — BIGSERIAL
  • provider — TEXT — NOT NULL
  • provider_id — TEXT
  • owner_user_id — BIGINT
  • name — TEXT — NOT NULL
  • is_public — BOOLEAN
  • meta_json — JSONB

playlist_track

  • playlist_id — BIGINT — Primary key — NOT NULL
  • song_id — BIGINT — Primary key — NOT NULL
  • position — INT

points_ledger

  • id — BIGSERIAL
  • user_id — BIGINT — NOT NULL
  • reason — TEXT — NOT NULL
  • delta — INT — NOT NULL
  • balance_after — INT
  • ref_type — TEXT
  • ref_id — BIGINT
  • created_at — TIMESTAMPTZ — NOT NULL

recommend_query

  • id — BIGSERIAL
  • user_id — BIGINT
  • situation — TEXT
  • place — TEXT
  • genre_hint — TEXT
  • mood_hint — TEXT
  • seed_artists_json — JSONB
  • seed_playlists_json — JSONB
  • created_at — TIMESTAMPTZ — NOT NULL

recommendation_item

  • set_id — BIGINT — Primary key — NOT NULL
  • rank — INT — Primary key — NOT NULL
  • song_id — BIGINT — NOT NULL
  • score — NUMERIC(9,6)
  • reason_json — JSONB

recommendation_set

  • id — BIGSERIAL
  • query_id — BIGINT — NOT NULL
  • strategy — TEXT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

review_event

  • id — BIGSERIAL
  • learning_item_id — BIGINT — NOT NULL
  • session_id — BIGINT
  • result — SMALLINT — NOT NULL
  • response_ms — INT
  • created_at — TIMESTAMPTZ — NOT NULL

reward

  • id — BIGSERIAL
  • name — TEXT — NOT NULL
  • cost_points — INT — NOT NULL
  • stock — INT
  • active — BOOLEAN — NOT NULL

reward_claim

  • id — BIGSERIAL
  • reward_id — BIGINT — NOT NULL
  • user_id — BIGINT — NOT NULL
  • status — TEXT — NOT NULL
  • requested_at — TIMESTAMPTZ — NOT NULL
  • processed_at — TIMESTAMPTZ
  • memo — TEXT

role

  • code — TEXT

song

  • id — BIGSERIAL
  • title — TEXT — NOT NULL
  • album_id — BIGINT
  • duration_ms — INT
  • release_date — DATE
  • language — TEXT
  • is_explicit — BOOLEAN
  • popularity — INT
  • spotify_track_id — TEXT
  • kaggle_row_id — TEXT
  • source — TEXT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

song_artist

  • song_id — BIGINT — Primary key — NOT NULL
  • artist_id — BIGINT — Primary key — NOT NULL

song_content_unit

  • song_id — BIGINT — Primary key — NOT NULL
  • unit_id — BIGINT — Primary key — NOT NULL
  • line_no — INT
  • context_text — TEXT

song_embedding

  • song_id — BIGINT
  • vector_backend — TEXT — NOT NULL
  • collection — TEXT — NOT NULL
  • vector_dim — INT
  • vector_id — TEXT — NOT NULL

song_features

  • song_id — BIGINT
  • danceability — NUMERIC(6,5)
  • energy — NUMERIC(6,5)
  • valence — NUMERIC(6,5)
  • tempo — NUMERIC(7,3)
  • key — SMALLINT
  • mode — SMALLINT
  • loudness — NUMERIC(6,2)
  • acousticness — NUMERIC(6,5)
  • instrumentalness — NUMERIC(6,5)
  • liveness — NUMERIC(6,5)
  • speechiness — NUMERIC(6,5)

song_genre

  • song_id — BIGINT — Primary key — NOT NULL
  • genre_id — BIGINT — Primary key — NOT NULL

song_lyrics

  • song_id — BIGINT
  • lyrics_text — TEXT — NOT NULL
  • source — TEXT — NOT NULL
  • lang — TEXT — NOT NULL

song_source_map

  • id — BIGSERIAL
  • song_id — BIGINT — NOT NULL
  • kaggle_row_id — TEXT
  • spotify_track_id — TEXT
  • note — TEXT

speaking_attempt

  • id — BIGSERIAL
  • user_id — BIGINT — NOT NULL
  • song_id — BIGINT
  • prompt_text — TEXT — NOT NULL
  • audio_asset_id — BIGINT
  • score_overall — NUMERIC(5,2)
  • score_pronunciation — NUMERIC(5,2)
  • score_stress — NUMERIC(5,2)
  • score_rhythm — NUMERIC(5,2)
  • provider_payload_json — JSONB
  • created_at — TIMESTAMPTZ — NOT NULL

study_session

  • id — BIGSERIAL
  • user_id — BIGINT — NOT NULL
  • song_id — BIGINT
  • started_at — TIMESTAMPTZ — NOT NULL
  • ended_at — TIMESTAMPTZ
  • device — TEXT
  • summary_json — JSONB

term_embedding

  • id — BIGSERIAL
  • term_id — BIGINT — NOT NULL
  • vector_backend — TEXT — NOT NULL
  • collection — TEXT — NOT NULL
  • vector_dim — INT
  • vector_id — TEXT — NOT NULL

user_auth

  • id — BIGSERIAL
  • user_id — BIGINT — NOT NULL
  • provider_id — BIGINT — NOT NULL
  • provider_uid — TEXT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

user_badge

  • user_id — BIGINT — Primary key — NOT NULL
  • badge_id — BIGINT — Primary key — NOT NULL
  • granted_at — TIMESTAMPTZ — NOT NULL

user_consent

  • id — BIGSERIAL
  • user_id — BIGINT — NOT NULL
  • type — TEXT — NOT NULL
  • granted — BOOLEAN — NOT NULL
  • granted_at — TIMESTAMPTZ — NOT NULL

user_profile

  • user_id — BIGINT
  • avatar_asset_id — BIGINT
  • bio — TEXT
  • home_lang — TEXT
  • pref_study_minutes_per_day — INT
  • pref_quiz_level — TEXT
  • pref_privacy_masking — BOOLEAN
  • timezone — TEXT

user_role

  • user_id — BIGINT — Primary key — NOT NULL
  • role_code — TEXT — Primary key — NOT NULL

user_saved_track

  • user_id — BIGINT — Primary key — NOT NULL
  • song_id — BIGINT — Primary key — NOT NULL
  • saved_at — TIMESTAMPTZ — NOT NULL

user_session

  • id — BIGSERIAL
  • user_id — BIGINT — NOT NULL
  • device_id — TEXT
  • ip — INET
  • ua — TEXT
  • refresh_token_hash — CHAR(64)
  • expires_at — TIMESTAMPTZ
  • revoked_at — TIMESTAMPTZ

user_spotify_link

  • id — BIGSERIAL
  • user_id — BIGINT — NOT NULL
  • spotify_user_id — TEXT — NOT NULL
  • scope — TEXT
  • access_token_enc — BYTEA — NOT NULL
  • refresh_token_enc — BYTEA — NOT NULL
  • expires_at — TIMESTAMPTZ — NOT NULL