# nmf-status-check

A Claude skill for monitoring New Music Friday (NMF) playlist ingestion across the full pipeline — from Spotify through to Insights.

## What it does

Runs 5 checkpoints for a given NMF Friday date (plus an optional 6th) and reports a per-market status for each:

| Step | Source | Check |
|------|--------|-------|
| 1. Spotify updated | Spotify Web API | Tracks have `added_at` on or after target Friday − 1 day (UTC) |
| 2. Pipeline health | Snowflake task history + stream state | `CAPTURE_SPOTIFY_PRIORITY_PLAYLIST_EVENTS` running cleanly and stream state |
| 3. Pipeline ingested | `FACTS.PROD.FACT_NEW_MUSIC_FRIDAY_AVAILABILITY` | `DATA_RECEIVED = TRUE` per market for the target date |
| 4. NMF page available | `FACTS.PROD.FACT_CHARTS` | Non-zero track count per market for the target date |
| 5. Playlist page available | `FACTS.PROD.PLAYLISTS_PRIORITY_PLACEMENTS_BY_ISRC_PLAYLIST_PUBLIC` | Placements updated on or after target Friday − 1 day (UTC) |
| 6. Tracklist match *(optional)* | Spotify Web API + `FACTS.PROD.FACT_CHARTS` | Full ISRC-level diff between Spotify playlist and what Insights shows |

**Checkpoint 2** checks the health of the Snowflake task rather than querying the ChartMetric data share directly. The data share (`L_SPOTIFY_PLAYLIST_SONY`) returns stale cached snapshots to direct `SELECT` queries — the task reads from it via a stream that sees live data first. Task history + stream state gives a more reliable picture of whether the pipeline is idle (waiting for ChartMetric), actively processing, or has failed.

**Checkpoint 6** is only run when the `tracklist-check` argument is passed. It paginates through each NMF playlist on Spotify, extracts ISRCs, and diffs them against `FACT_CHARTS`. A small number of mismatches per market is expected due to regional ISRC variants. Most useful after all markets have fully ingested (checkpoint 3 = 57/57).

After displaying results, the skill asks whether you'd like a Slack-ready summary copied to your clipboard.

## Known caveats

**Timezone pre-population:** Two overlapping patterns can cause a market to appear in CP3 (`DATA_RECEIVED = TRUE`) before its Spotify playlist has actually refreshed:

- **Null-timezone markets (CHR, GULF, LEV, SUR):** No timezone is configured in `DIM_NEW_MUSIC_FRIDAY_CHART`; they default to `Australia/Sydney (UTC+10)`. The pipeline assigns `CHART_DATE = <target friday>` based purely on ChartMetric's scrape time.
- **APAC markets (ID, VN, SG, MY, PH, TH, HK, TW, JP, KR):** These markets are in UTC+7/+8. During the Thursday UTC window, the task can assign `CHART_DATE = <target friday>` when ChartMetric delivers a scrape just as the timezone tips over to Friday locally — even if the Spotify playlist still shows last week's content.

The skill flags any market where CP3 is TRUE but CP1 shows last week's `added_at`, and notes that the ingested content may be the prior week's tracklist.

**Early Spotify updates:** Occasionally a market's NMF playlist refreshes several hours earlier than expected for its timezone (e.g. a European market updating mid-afternoon UTC). CP1 now detects this and flags it — but also instructs verifying that the full playlist was replaced (not just a stale track at position 1) by checking that multiple tracks share the same `added_at`.

## Dependencies

### Snowflake MCP
The skill queries Snowflake directly via the `mcp__snowflake-labs__run_snowflake_query` MCP tool. This must be configured in your Claude Code MCP settings and connected to a Snowflake account with read access to:
- `FACTS.PROD.*`
- `INFORMATION_SCHEMA.TASK_HISTORY` (for pipeline health check)

### Spotify API credentials
The skill calls the Spotify Web API using the [client credentials flow](https://developer.spotify.com/documentation/web-api/tutorials/client-credentials-flow) — no user login required.

On first run, the skill will prompt you to either:
- Point to an existing `.env` file containing `SPOTIFY_CLIENT_ID` and `SPOTIFY_CLIENT_SECRET`, or
- Enter the credentials directly — they will be saved to `~/.claude/skills/nmf-status-check/.env` for future runs.

To obtain credentials, create an app at https://developer.spotify.com/dashboard. No special scopes are needed.

## Installation

```bash
cp -r aoshomoji/claude-skills/nmf-status-check ~/.claude/skills/
```

## Usage

```
/nmf-status-check
```

Optionally pass a specific Friday date:

```
/nmf-status-check 2026-06-20
```

Run with the full tracklist diff (checkpoint 6):

```
/nmf-status-check tracklist-check
```

**Date defaults:**
- **Thursday or Friday** — targets the upcoming Friday (early-timezone markets like AU/Asia will already have updated)
- **Saturday–Wednesday** — targets the most recent past Friday

If a date is provided, the skill validates it: it warns if the date is more than 7 days from today (likely a wrong-year typo) or is not a Friday.

## Pipeline timing

Runs automatically after checkpoint 4 whenever any markets have data in `FACT_CHARTS`. Shows the Spotify → ChartMetric lag per market: how long after Spotify's playlist refresh until ChartMetric first scraped the new content.

Only markets where both Spotify `added_at` (CP1) and `FACT_CHARTS` data (CP4) are available appear in the timing table. Markets with CP4 data but no CP1 update yet (timezone pre-population) are excluded.

**Note:** NMF page timing (`FACT_CHARTS.modified_at`) is not shown — the `modified_at` column is overwritten on every batch write, so `MIN(modified_at)` does not reliably reflect when data first appeared. Similarly, `last_added_on_date` in the playlist page table carries the Spotify `added_at` timestamp rather than a pipeline write time, so downstream lag is also not measurable.