# playlist-status-check

A Claude skill that compares Spotify priority playlists against what Insights (via ChartMetric) shows — surfacing ISRC mismatches, position drift, missing/extra tracks, and playlists not tracked in Insights or missing from Spotify.

![Priority Playlist Status Report](report_screenshot.png)

---

## What it does

The skill operates in two modes:

**Single playlist** — `/playlist-status-check <playlist_id>`
Runs a detailed diff for one playlist: track counts, matched/missing/extra ISRCs, position mismatches, and suggested areas to investigate.

**Bulk (all priority playlists)** — `/playlist-status-check`
Compares all ~663 priority playlists in one run, generates a self-contained HTML report at `~/Downloads/priority_playlist_status_report.html`.

The bulk mode is driven by two persisted scripts in the skill folder:

| Script | Purpose |
|--------|---------|
| `bulk_check.py` | Fetches all playlists from Spotify + Snowflake, compares them, writes `/tmp/bulk_results.json` |
| `gen_report.py` | Reads `/tmp/bulk_results.json`, writes the HTML report |

Run `bulk_check.py` first, then `gen_report.py`. Expected runtime: ~10 minutes for 663 playlists.

---

## Status classifications

| Status | Condition |
|--------|-----------|
| ✅ Up to date | ISRCs and positions match |
| ⚠️ Small difference | 1–5 ISRC gap (after excluding ISRC variants) |
| ⚠️ Position drift | ISRCs match but positions differ |
| ❌ Significant mismatch | >5 ISRC gap |
| ❌ Not in Insights | No tracklist data found in Snowflake for this playlist |
| ❌ Not on Spotify | Playlist returned 404 (deleted or unpublished) |
| ❓ Spotify error | Non-404 HTTP error from Spotify API (error code shown) |

---

## Prerequisites

### Python

You need a Python 3.11+ interpreter with the following packages installed:

```
snowflake-connector-python
requests
cryptography
```

Install them into your preferred environment:

```bash
pip install snowflake-connector-python requests cryptography
```

> The pyenv interpreter at `~/.pyenv/versions/3.11.2/bin/python3` is used internally by the skill. Confirm with `pyenv which python3`.

### Snowflake credentials

The scripts read all connection details from `~/.snowflake/config.toml`. An example config:

```toml
[connections.default]
account = "SME-DELPHI"
user = "YOUR.EMAIL@company.com"
role = "DEV_ENGINEERING"
warehouse = "DEV_PERFORMANCE_WAREHOUSE"
database = "FACTS"
schema = "PROD"
private_key_file = "~/.ssh/snowflake/rsa_key.p8"
private_key_passphrase = "your_passphrase"
```

The script uses private key authentication. The `.p8` file path and passphrase are read from the config at runtime — no credentials are hardcoded.

### Spotify credentials

Create a `.env` file at `~/.claude/skills/playlist-status-check/.env`:

```
SPOTIFY_CLIENT_ID=your_client_id
SPOTIFY_CLIENT_SECRET=your_client_secret
```

You can get credentials from the [Spotify Developer Dashboard](https://developer.spotify.com/dashboard). The client credentials flow is used (no user login required).

> This file must not be committed to version control. It is intentionally not included in this repository.

---

## Setup

```bash
# 1. Copy skill files to ~/.claude/skills/
cp -r aoshomoji/claude-skills/playlist-status-check ~/.claude/skills/

# 2. Install Python dependencies (into whichever interpreter you use)
pip install snowflake-connector-python requests cryptography

# 3. Create Spotify credentials file
cat > ~/.claude/skills/playlist-status-check/.env << 'EOF'
SPOTIFY_CLIENT_ID=your_client_id
SPOTIFY_CLIENT_SECRET=your_client_secret
EOF

# 4. Ensure ~/.snowflake/config.toml is configured (see above)
```

---

## Running the bulk check manually

```bash
PYTHON=~/.pyenv/versions/3.11.2/bin/python3  # or: $(pyenv which python3)

$PYTHON ~/.claude/skills/playlist-status-check/bulk_check.py
# ~10 minutes first run; near-instant if Spotify cache is warm

$PYTHON ~/.claude/skills/playlist-status-check/gen_report.py
# Writes ~/Downloads/priority_playlist_status_report.html

open ~/Downloads/priority_playlist_status_report.html
```

To force a full refetch from Spotify (ignoring the cache):

```bash
$PYTHON ~/.claude/skills/playlist-status-check/bulk_check.py --refresh
```

### Spotify result cache

Spotify API responses are cached for **15 minutes** in `~/.claude/skills/playlist-status-check/.spotify_cache.json`. The cache is per-playlist, so:

- Immediate reruns (e.g. to pick up fresher Snowflake data without re-hitting Spotify) complete in under a minute.
- If a run is interrupted partway through, already-fetched playlists are cached and won't be re-fetched on the next run within the window.
- The cache is written after each playlist fetch, so partial runs are preserved.
- Pass `--refresh` to ignore all cached entries and fetch fresh data from the Spotify API.

---

## How the comparison works

### Data sources

- **Spotify** — Spotify Web API (`/v1/playlists/{id}/tracks`). Fetches the full current tracklist.
- **Insights** — `FACTS.PROD.V_PLAYLISTS_BY_PLAYLIST_CURRENT_TRACKLIST` in Snowflake. This is the view that backs the Insights playlist page. Queried for all priority playlists in one batched `IN` clause.
- **Priority playlist list** — `FACTS.PROD.PRIORITY_PLAYLISTS` joined to `FACTS.PROD.PRIORITY_PLAYLIST_METADATA` for playlist type.
- **Track name enrichment** — `FACTS.PROD.GLOBAL_SOUND_RECORDING` (GSR), the authoritative source for track names keyed by ISRC.

### Track name priority

| List | Name source |
|------|-------------|
| Missing from Insights | Spotify API name (the track exists on Spotify but not yet in Insights) |
| Extra in Insights | GSR → fallback to ChartMetric name |
| Position mismatches | GSR → fallback to Spotify name → fallback to ChartMetric name |

ChartMetric track names are used as a last resort only — they often don't match the display names in Spotify or Insights.

### Considerations and assumptions

#### 1. ChartMetric position numbering

ChartMetric excludes two types of tracks from its feed and renumbers positions accordingly:

- **Unavailable slots** (`item.track is None`) — geo-blocked or removed tracks that Spotify still lists as a placeholder slot.
- **Null-ISRC tracks** — tracks Spotify returns but without an ISRC (local files, unlicensed content).

The script mirrors this by maintaining a separate `cm_position` counter that only increments for playable tracks with a valid ISRC. Using Spotify's raw position numbers would produce false position drift.

#### 2. ISRC normalisation

Spotify sometimes returns ISRCs with dashes (e.g. `GB-QLP-08-00502`) while Insights stores them without (`GBQLP0800502`). All ISRCs are normalised to uppercase alphanumeric only (`re.sub(r'[^A-Z0-9]', '', isrc.upper())`) before any comparison.

#### 3. Personalised playlists

For playlists of type `PERSONALIZED` (e.g. Discover Weekly, Daily Mixes), `current_position` is always `NULL` in `V_PLAYLISTS_BY_PLAYLIST_CURRENT_TRACKLIST`. ChartMetric does not track per-user positions for algorithmic playlists. The query does not filter on `current_position IS NOT NULL` — this would incorrectly classify all personalised playlists as "Not in Insights". Position comparisons are automatically skipped for any track whose Insights position is NULL.

#### 4. Duplicate ISRCs on Spotify

The same ISRC can appear at multiple positions in a Spotify playlist (e.g. a track added twice). Insights deduplicates and shows only one position per ISRC. The script stores all Spotify positions per ISRC as a list; a position is only flagged as a mismatch if the Insights position does not match *any* Spotify position for that ISRC.

#### 5. ISRC variants

The same recording is sometimes released under different ISRCs in different territories (regional re-releases). When a track appears as "missing from Insights" at position P, and there is also a track "extra in Insights" at the same position P, the script flags these as a **possible ISRC variant** — the same recording under a different ISRC — and excludes them from the ISRC diff count. They are shown separately in the report drilldown.

This detection is position-based (same ChartMetric position = same slot). It does not use name matching, so it only works for editorial playlists where `current_position` is populated. Variants in personalised playlists are not detected.

#### 6. No staleness check

There is no staleness / freshness threshold. ChartMetric only scrapes a playlist when its content genuinely changes, so timestamp differences without ISRC or position differences are not meaningful signals. The "Last updated (Insights)" column is shown only for playlists that have other issues.

#### 7. Spotify rate limits

The Spotify API allows ~100 requests per 30 seconds. The script pauses 10 seconds after every 80 playlist fetches. 429 responses are handled with exponential backoff using the `Retry-After` header.

---

## Report features

- **Summary cards** — counts per status category
- **Filter buttons** — show only one status category at a time
- **Sortable table** — click any column header
- **Clickable rows** — expand inline drilldown showing:
  - Missing from Insights (track name from Spotify)
  - Extra in Insights (track name from GSR)
  - Position mismatches (all Spotify positions vs Insights position, with diff)
  - Possible ISRC variants (same position, different ISRCs)
- **Type badge** — `PERSONALIZED`, `ALGORITHMIC`, `RADIO` types highlighted in amber with a tooltip explaining that tracklists vary per user
- **Insights Link** — direct link to the playlist in Insights (shown when the playlist exists in Insights)