# Playlist Pages Expert — Agent Memory

Last full refresh: 2026-03-16

## Topic Files

- `dbt-models.md` — dbt-analytics playlists package (tier split, model structure, macros)
- `data-model.md` — Snowflake tables/views, schemas, streams/demographics coverage gap, FAQ
- `api-endpoints.md` — ows-playlist, graphql-analytics, graphql-knowledge endpoints and recent changes
- `frontend-components.md` — frontend-insights playlist page components, PoT chart, Apollo queries
- `feature-flags.md` — All Split.io flags across all services
- `infrastructure.md` — Terraform roles, Snowflake tasks, service users
- `search.md` — Playlist Search: full implementation (graphql-knowledge only, ID detection, Cortex Search, result pipeline)

---

## Critical Architecture: dbt Tier Split (IN-16920)

The playlists dbt package is **fully split into 3 tiers**:

- `priority/` — PRIORITY_PLAYLISTS seed (high-priority, Snowflake-task managed)
- `hourly/` — HOURLY_PLAYLISTS (synced from Google Sheet via Fivetran → Snowflake task)
- `non_priority/` — Everything else in CLEAN_DIM_PLAYLIST_DBT

**All external consumers use `V_` unioned views** that UNION ALL three tiers. ows-playlist migrated all table references to V_ views in commit `9af87fb71`.

Key unioned views: `V_PLAYLIST_METADATA`, `V_PLAYLISTS_BY_PLAYLIST`, `V_PLAYLISTS_BY_PLAYLIST_CURRENT_TRACKLIST`, `V_PLAYLISTS_FOLLOWERS_BY_PLAYLIST_DATE_SPOTIFY`, `V_PLAYLISTS_PLACEMENT_EVENTS_BY_ISRC_PLAYLIST`, and all `V_PLAYLISTS_PLACEMENTS_*`.

Priority tracklist/placements are produced by Snowflake task graph, **not dbt** — they're declared as dbt sources in `unioned/sources.yml`.

---

## Snowflake Context

- Prod: `FACTS.PROD` | QA: `FACTS.QA`
- All dbt objects use `_DBT` suffix; `upgrade_view` macro drops suffix for production views
- `V_*` views are dbt view-materialized (no suffix needed)

---

## PoT Endpoint Summary

- PoT timeseries: `POST /playlist/analytics-bulk-timeseries` (ows-playlist)
- Demographics: `GET /playlist/<store_playlist_id>/demographics/` (ows-playlist)
- Followers: sourced from `V_PLAYLISTS_FOLLOWERS_BY_PLAYLIST_DATE_SPOTIFY` (Chartmetric, all tiers)
- graphql-analytics batches via DataLoaders into ows-playlist

---

## Key Feature Flags (for quick lookup)

| Flag | Service | Purpose |
|------|---------|---------|
| `insights_playlist_page_hourly_playlists` | ows-playlist + graphql-knowledge | Gates hourly playlist access |
| `insights_playlist_page_apple_music_playlists` | ows-playlist | Gates Apple Music playlists |
| `insights_playlist_page_hide_compilation_art` | ows-playlist + graphql-analytics | INFERRED_COMPILATION flag in tracklist |
| `insights_playlist_current_tracklist_preaggregated` | ows-playlist | Use pre-agg tracklist table |
| `insights_playlist_page_navigation` | frontend-insights | Playlist page nav (permanent) |
| `insights_store_front_fix` | frontend-insights | Storefront parameter fix |
| `insights_playlists_store_graphs` | frontend-insights | Store-level graphs |

---

## Snowflake Tasks (HOURLY_PLAYLISTS management)

- `GSHEETS_TO_HOURLY_PLAYLISTS` — syncs Spotify (store_id=286) from Google Sheet, 10 min prod / hourly QA
- `GSHEETS_TO_AM_HOURLY_PLAYLISTS` — syncs Apple Music (store_id=1), same schedule (IN-16924)
- Both owned by `{env}_INSIGHTS_PLAYLIST_PAGES_GSHEETS_SYNC_TASK_ROLE`
- DDL: `/Users/cbeesley/code/database/snowflake/FACTS/build/changelog/ddl/snowflake_tasks/insights_playlist_lists_sync/`

---

## OWNER Column

`CLASSIFY_PLAYLIST_OWNER` Snowflake UDF in dbt macro classifies playlist ownership: Apple Curators | Amazon Curators | Spotify | Spotify Radio | Sony | Universal | Warner | Brand | Tastemaker | Other Label | User

---

## Streams/Demographics/Followers Coverage Gap

- Streams & demographics: require Orchard-distributed content to have been streamed from the playlist (hourly playlists with no Orchard content = no data)
- Followers: Chartmetric-sourced, ~82% coverage; no Orchard-content dependency

---

## Playlist Placement Placeholders (IN-16510)

When the priority pipeline receives a track with no `GlobalSoundRecording` in Neo4j, ows-playlist returns it in `placeholder_placements[]` (separate from `placements[]`). graphql-analytics formats these as `{ __typename: 'PlaylistPlacementPlaceholder' }`. "Unknown Track" is injected in the formatter (`topPlaylistPlacements.ts`, line ~171), not in the frontend.

**Two scenarios:**
1. **Geo-restricted NMF regional track** (e.g. NMF AU & NZ before US/UK release window): no `track_name` in response → "Unknown Track". Bypasses NMF-tracks-ingestion (`track_name IS NOT NULL` filter). Resolves overnight after `swf-feed-ingestion chartmetric-track` creates the GSR.
2. **Non-catalog track, pre-ingestion** (globally released, not Orchard): Chartmetric has the track, NMF-tracks-ingestion picks it up (10-min Kafka poll), Neo4j Cypher sink creates full graph. Resolves in ~10 minutes.

**Always non-catalog** because Orchard tracks are DDEX-delivered into Neo4j before any playlist pickup.

**GraphQL**: `placementsV2: [PlaylistPlacementResult!]!` (union `PlaylistPlacement | PlaylistPlacementPlaceholder`) — use this. `placements` is `@deprecated` and silently omits placeholder rows. Frontend dispatches on `__typename` in `mapPlacement()` (`frontend-insights/src/pages/playlist/components/playlistTracklist/utils.ts`).

**Key files:**
- Formatter: `graphql/subgraphs/graphql-analytics/src/connectors/ows-playlist/formatters/topPlaylistPlacements.ts`
- Schema: `graphql/subgraphs/graphql-analytics/src/schema/Playlist.graphql`
- Kafka pipeline: `terraform-infra/prod/kafka-infra/jdbc_source/snowflake/create_new_gsrs_from_nmf/`
- Neo4j Cypher sink: `terraform-infra/prod/kafka-infra/neo4j_cdc_sink/new_gsr_nmf/queries/newmusicfriday_newtrack.cypher`

---

## Repo Locations

- `/Users/cbeesley/code/frontend-insights` — React/TypeScript FE
- `/Users/cbeesley/code/ows-playlist` — Python/Flask API
- `/Users/cbeesley/code/graphql-analytics` — GraphQL analytics service
- `/Users/cbeesley/code/graphql-knowledge` — GraphQL knowledge service (Snowflake direct query)
- `/Users/cbeesley/code/dbt-analytics` — dbt models (playlists package: `playlists/`)
- `/Users/cbeesley/code/database` — Snowflake DDL, Liquibase
- `/Users/cbeesley/code/terraform-infra` — IaC (roles, service users, warehouses)