# 34 · Billboard Library Sync & The One 100 Charts **Rank 17 of 34** · Tier B · `app/workers/billboard_users` (`src/lww.ts`, `src/LibraryService.ts`, `src/PlaylistService.ts`, `src/LibrarySyncService.ts`, `src/ChartService.ts`, `migrations/0039`, `migrations/0040`), `app/BillboardGateway/ingest` **Stack:** TypeScript on Cloudflare Workers, D1 (SQLite), Postgres activity outbox, Kafka via the activity relay, Go ingest daemon > **Where this sits.** Two problems that look like product features and are > really distributed-systems questions. The first is conflict resolution: a > phone and a browser write the same library rows, one of them offline. The > second is aggregation over an event log: a weekly chart that has to be > deterministic, rebuildable, and hard to game. Both shipped to production on > 2026-09-26. --- ## Part 1: the library syncs last-write-wins Before migration 0039 the server kept whatever *arrived* last and stamped it with its own clock. So an edit queued offline on a phone at 10:00 and replayed at 12:00 overwrote a web edit made at 11:00. The owner asked for last-write-wins. The precise version of that request is "the edit **made** last wins", which is a different system. ### The rule Every write now carries the time the listener made the change, and is applied only when it is at least as new as what the row holds. | Case | Behaviour | |---|---| | Time absent or unreadable | Server now | | Time in the future | **Clamped to server now** | | Tie | Applies (`>=`): the later arrival wins a tie | | Removal | Leaves a tombstone whose time never moves backwards | | Stale write | `200 { success: true, stale: true, data: }` | | Version storage | `lww_at` / `lww_fields`, never `created_at` / `updated_at` | The rule is shared word for word with `boxoffice-api` migration 0021, written by another session the same day and agreed by message. Two products, one conflict rule. That was a deliberate choice: a listener who uses both should never meet two different notions of "newer". ### Why the clock is clamped A device with a wrong clock is the classic failure of wall-clock LWW. Set a phone to 2030 and, unclamped, every edit it makes wins every conflict forever. Clamping to server now bounds the damage to "this device's edits count as arriving now". It does not make wall clocks safe. It makes them survivable, and that is the honest claim. The parser in `lww.ts` is fussier than it looks, and each branch is a bug that would otherwise ship: - **Seconds vs milliseconds.** A number below `1e11` is read as seconds. Nothing in this product predates 2001 in milliseconds. - **SQLite's `CURRENT_TIMESTAMP`** is zoneless and UTC. `Date.parse` reads a zoneless string as *local* time, so it is rewritten to `...Z` first. - **ISO without a zone** is ambiguous across devices and is treated as unreadable, so it becomes server now rather than a guess. ### Why the time goes in the body, not a header The web client calls cross-origin. A custom header triggers a CORS preflight and an allow-list change on every route. So the time rides in the body as `updatedAt`, or as `?updatedAt=` on a bodyless `DELETE`. Small decision, and it is the kind that decides whether a rule actually gets adopted by every caller. ### Why a stale write is a 200 A stale write is not an error from the client's point of view. Its change lost. Answering 4xx would leave it sitting in a retry queue, retrying a write that can never win. Answering 200 with `stale: true` and the current value lets the queue drop it and the next pull converge. The status code is part of the sync protocol, not decoration. ### Tombstones, and why deletes of playlists are final Removing a liked track records a row in `billboard_library_tombstones`. An add made *before* the removal that arrives *after* it then stays out. Without the tombstone, the late add has nothing to compare against and the track comes back. That resurrection bug is the most common way naive LWW fails. The add itself is one statement: ```sql INSERT INTO billboard_liked_tracks (...) SELECT ?, ?, ?, ? WHERE NOT EXISTS ( SELECT 1 FROM billboard_library_tombstones WHERE user_id = ? AND kind = ? AND target_id = ? AND deleted_at > ?) ON CONFLICT(user_id, track_id) DO UPDATE SET lww_at = MAX(COALESCE(lww_at, created_at), excluded.lww_at) ``` One statement so that a removal racing it cannot slip between a tombstone check and the insert. The removal side is a batch of the conditional `DELETE` and the tombstone upsert, and the tombstone's `deleted_at` only ever moves up via `MAX`. Playlists and playlist items go one step further. **Their deletes are final.** A newer edit to a deleted playlist gets `stale`, never a resurrection. Timestamps decide only whether the delete applies. The reasoning: a playlist is a container that other rows point at, and quietly recreating it from a late rename would bring back an empty shell the listener already threw away. ### Per-field versions for playlists `lww_fields` holds `{"name": ms, "description": ms, "is_public": ms, "artwork": ms}`. A rename on the phone does not undo a description edited on the web, because each field is compared against its own version. A change to a playlist's items bumps the playlist's `lww_at`, so delete-vs-edit is decided on the whole container. Rows written before 0039 have no field versions and read as their `created_at`, so the migration is purely additive: nullable columns and one new table, nothing altered or backfilled. ### Tested against real SQLite `tests/lww.test.mjs` drives the same statements through `node:sqlite` behind the structural `DB` interface the Worker uses for D1. **74 checks, 0 failures**, including the cases that matter: a late add after a removal stays out, a batch keeps the newest change rather than the last one in the list, and a progress update from before a completion does not reopen "continue listening". Writing it also found an unrelated defect: the `PUT` move route ignored `uid`, so moving one copy of a track that appeared twice in a playlist moved every copy. --- ## Part 2: The One 100, a chart over the play log The owner's spec asked for Kafka, a Go aggregator, Cassandra, Redis and Postgres. The box had about 5 GB free and had just been OOM-killed, and the real volume was 144 plays from 4 listeners. The owner chose to extend what existed on Cloudflare instead, and that was the right call. The interesting observation is *why* it works: `billboard_play_events` was already the raw, idempotent, server-qualified event log the spec wanted Cassandra for. A chart is a derived table over that log plus a weekly freeze. ``` play (>= 30 s, qualified at ingest) │ ▼ billboard_chart_streams music only; stamped once with week, day, country, genre, session; counted, capped, weighted │ ├──► live chart computed from the week's streams on request │ ▼ Friday 06:00 UTC freeze ──► billboard_chart_entries LW / PEAK / WKS / NEW / RE │ ▼ Postgres outbox ──► Kafka billboard.activity.v1 chart.published ``` ### Counting rules - **Week** runs Friday to Thursday UTC and is named by its Friday. The spec's example week ids were Saturdays. Any date maps to its week, so the code computes it rather than trusting a table. - **Session** is a 30 minute gap. A stream counts at most once per listener, track and session, and at most 10 times per listener, track and UTC day. One consequence the spec did not anticipate: its "loop 30 times counts 10" only holds across sessions. Back-to-back loops in one session count once. - **Score** is the sum over listeners of `sqrt(min(streams, 10)) x weight`. The square root makes a listener's tenth play worth far less than their first, which is the main defence against one fan carrying a track. - **Ties** break on unique listeners, then minutes, then track id. That last key exists so that a rebuild from the same streams yields the *same* chart. - **Weight 0** for an artist's plays of their own track, and for fraud flags. ### Fraud, and a rule that was too blunt A daily pass zero-weights a listener for the week for more than 16 hours of play in a day, or 3 or more plays running at once (a sweep over start and end edges). The loop rule was refined after it met a real person: someone who left repeat-one on for an afternoon, **86 full plays of one song on 2026-10-07**, was taking every other song they played that week off the chart with it. A loop of more than 50 now zero-weights *that track's* streams for the week, not the listener. ### The concentration rule that would have held everything The spec holds a track for review when more than 60% of its streams come from 5 or fewer listeners. At launch scale, every track on the chart comes from 5 or fewer listeners. Enforced, the rule would have held the entire chart. So it runs report-only, writing hold rows for review, and enforces only when the environment sets `CHART_HOLDS=enforce`. A hold row is never overwritten once reviewed. This is the useful lesson in the project: a fraud threshold is a statement about the population. Copied from a spec written for millions of listeners and applied to four, it is not conservative. It is just wrong. ### Exactly-once jobs on an at-least-once cron No new cron trigger. The existing 15 minute tick catches up on streams, runs yesterday's fraud pass, freezes the ended week, and announces it. Each step is recorded in `billboard_chart_jobs` and runs once. Every day of a week gets its fraud pass before the week freezes. The announcement is its own job. Postgres being away must neither block the freeze nor lose the event. So `chart.published` is written to the Postgres activity outbox with a deterministic id, `evt_chart_published_`, and `ON CONFLICT DO NOTHING`. A frozen week without its outbox row is retried every tick until the row lands, and a re-freeze announces nothing new. That is the same outbox the [Activity Graph](01-billboard-activity-graph.md) relays to Kafka, so the chart became a producer on the existing log rather than a new pipeline. `tests/charts.test.mjs`: **58 checks, 0 failures.** The first freeze was week 2026-09-18: seven GLOBAL entries. --- ## Feeding it: the Drive ingest daemon Charts and recommendations need a catalogue. `app/BillboardGateway/ingest` is a Go daemon, adapted from the BoxOffice ingest agent, that imports from Google Drive across four ports (music, audiobooks, podcasts, music videos) into the Worker's agent routes behind a shared-secret auth tier. The first pass covered about 3,400 audio files and 32 GB of music. A chapter never replaces another file's slot on a tag collision, and the Billboard copy pins the service-account token endpoint rather than trusting the key file's. Its Worker-side tests run **181 checks, 0 failures**; the daemon carries its own suite. --- ## Honest gaps - **No authenticated write was proven against production** for the sync. The deploy was smoke-tested with unauthenticated 401s and public reads only, because there was no test token. The 74 checks run against real SQLite, not against prod D1. - **No load test** on either part. Live chart computation on request is fine at this volume and has not been measured at any other. - Chart gaps: no heartbeats or server-side close of lost plays (a client checkpoint instead), no anonymous listeners, no "New #1" feed posts. The web date picker in the current week returns `not_frozen` with no live fallback. - Concentration holds are report-only until volume makes the rule meaningful.