Spotify "What Was I Listening To?" Integration (HiveJournal)

Goal

For a user who connects Spotify, we want to:

• Track what they’re listening to over time (from the moment they connect).
• When they create a journal entry, attach the last track they were playing at or before the entry timestamp.
• Show that track (title, artist, album art, link) on the entry detail screen.

We only rely on:

• GET /v1/me/player/recently-played
• GET /v1/tracks?ids=...

No playback control, no audio features, etc.

High-Level Flow

1. User connects Spotify

   • They click “Connect Spotify” in HiveJournal.
   • We redirect to Spotify OAuth (Authorization Code + PKCE).
   • On callback, we store access + refresh tokens and the Spotify user id.

2. Background sync of listening history

   • A scheduled job (cron / server action) runs periodically (e.g. every 5–15 minutes).
   • For each connected user, call GET /v1/me/player/recently-played with after=<last_seen_ms> to fetch new plays.
   • Store plays in a spotify_plays table with user_id, spotify_track_id, played_at.
   • For any new track IDs, upsert into a spotify_tracks table with track metadata (name, artists, album art, external URL, etc.).

3. On journal entry creation

   • When we insert a new journal_entries row, we:
     • Look up the most recent play where played_at <= journal_entries.created_at for that user.
     • If found, set journal_entries.spotify_track_id to that track’s ID.

4. On journal entry display

   • Join journal_entries with spotify_tracks on spotify_track_id, and render:
     • Track title
     • Artist name(s)
     • Album artwork
     • “Open in Spotify” link

Database Schema (Supabase-style)

We assume there is already a journal_entries table with at least:

• id
• user_id
• created_at
• content (or similar)

Add the following:

-- 1) Spotify account linkage per HiveJournal user
create table if not exists public.spotify_accounts (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users (id) on delete cascade,
  spotify_user_id text not null,
  access_token text not null,
  refresh_token text not null,
  expires_at timestamptz not null, -- when access_token expires
  scope text not null,
  connected_at timestamptz not null default now(),
  last_synced_at timestamptz null
);

create unique index if not exists spotify_accounts_user_id_key
  on public.spotify_accounts (user_id);

-- 2) Track metadata (cached from Spotify)
create table if not exists public.spotify_tracks (
  id text primary key, -- Spotify track ID
  raw jsonb not null,
  name text not null,
  artist_names text[] not null,
  album_name text not null,
  album_image_url text null,
  external_url text null,
  duration_ms integer,
  explicit boolean,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- 3) Play events
create table if not exists public.spotify_plays (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users (id) on delete cascade,
  spotify_track_id text not null references public.spotify_tracks (id),
  played_at timestamptz not null,
  source text not null default 'recently_played',
  created_at timestamptz not null default now()
);

create index if not exists spotify_plays_user_played_idx
  on public.spotify_plays (user_id, played_at desc);

-- 4) Link journal entries to tracks
alter table public.journal_entries
  add column if not exists spotify_track_id text null references public.spotify_tracks (id);