34 · Billboard Library Sync & The One 100 Charts

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.

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:

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_<week>, 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 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.