Repository Public
1import { sql } from "@/lib/db";2import { ApiError } from "@/lib/http";34/**5 * Read side for native clients that keep their own copy of the catalogue (SP Music for macOS).6 *7 * `librarySync(since)` returns every artist, album and track whose revision is above `since`, plus the tombstones8 * of what was deleted. All four reads share one snapshot; revisions commit in increasing order (see migration 003),9 * so the highest revision in the answer is a safe cursor for the next call.10 */1112const PAGE = 4000;1314export async function librarySync(since: number) {15 return sql.begin("isolation level repeatable read read only", async (tx) => {16 const [artists, albums, tracks, deleted, [counts]] = await Promise.all([17 tx<{ rev: number }[]>`SELECT a.id, a.name, a.rev::float8 AS rev FROM artists a WHERE a.rev > ${since} ORDER BY a.rev LIMIT ${PAGE}`,18 tx<{ rev: number }[]>`19 SELECT al.id, al.title, al.year, al.artwork_id AS "artworkId", al.artist_id AS "artistId", g.name AS genre, al.created_at AS "createdAt", al.rev::float8 AS rev20 FROM albums al LEFT JOIN genres g ON g.id = al.genre_id WHERE al.rev > ${since} ORDER BY al.rev LIMIT ${PAGE}`,21 tx<{ rev: number }[]>`22 SELECT t.id, t.title, t.track_no AS "trackNo", t.disc_no AS "discNo", t.duration_ms AS "durationMs", t.year,23 t.codec, t.container, t.sample_rate AS "sampleRate", t.bit_depth AS "bitDepth", t.channels, t.bitrate,24 t.web_playable AS "webPlayable", t.compat_state AS "compatState", t.artist_id AS "artistId", t.album_id AS "albumId", g.name AS genre,25 (SELECT ly.state FROM lyrics ly WHERE ly.track_id = t.id) AS "lyricsState", t.created_at AS "createdAt",26 (SELECT json_build_object('assetId', m.id, 'sha256', m.sha256, 'sizeBytes', m.size_bytes, 'mime', m.mime) FROM media_assets m WHERE m.track_id = t.id AND m.kind = 'original') AS file,27 (SELECT json_build_object('assetId', m.id, 'sizeBytes', m.size_bytes, 'mime', m.mime) FROM media_assets m WHERE m.track_id = t.id AND m.kind = 'rendition') AS compat,28 t.rev::float8 AS rev29 FROM tracks t LEFT JOIN genres g ON g.id = t.genre_id WHERE t.rev > ${since} ORDER BY t.rev LIMIT ${PAGE}`,30 tx<{ rev: number }[]>`SELECT kind, id, rev::float8 AS rev FROM library_tombstones WHERE rev > ${since} ORDER BY rev LIMIT ${PAGE}`,31 tx<{ artists: number; albums: number; tracks: number }[]>`SELECT (SELECT count(*)::int FROM artists) AS artists, (SELECT count(*)::int FROM albums) AS albums, (SELECT count(*)::int FROM tracks) AS tracks`,32 ]);33 // A truncated list ends at its last revision: the cursor may not go past the lowest such end, or rows would be skipped.34 const lists = [artists, albums, tracks, deleted];35 const truncated = lists.filter((l) => l.length === PAGE);36 const more = truncated.length > 0;37 const top = (l: { rev: number }[]) => (l.length ? l[l.length - 1].rev : since);38 const rev = more ? Math.min(...truncated.map(top)) : Math.max(since, ...lists.map(top));39 const keep = <T extends { rev: number }>(l: T[]) => (more ? l.filter((r) => r.rev <= rev) : l);40 return { since, rev, more, artists: keep(artists), albums: keep(albums), tracks: keep(tracks), deleted: keep(deleted), counts };41 });42}4344/** Everything that belongs to one account, in one answer: small by nature, so always sent whole. */45export async function userSync(userId: string) {46 const [playlists, entries, favTracks, favAlbums, [pb]] = await Promise.all([47 sql<{ id: string }[]>`SELECT id, name, created_at AS "createdAt", updated_at AS "updatedAt" FROM playlists WHERE user_id = ${userId} ORDER BY updated_at DESC`,48 sql<{ playlistId: string }[]>`49 SELECT pt.playlist_id AS "playlistId", pt.id AS "entryId", pt.track_id AS "trackId", pt.position, pt.added_at AS "addedAt"50 FROM playlist_tracks pt JOIN playlists p ON p.id = pt.playlist_id WHERE p.user_id = ${userId} ORDER BY pt.playlist_id, pt.position, pt.added_at`,51 sql`SELECT track_id AS id, created_at AS "createdAt" FROM favorites WHERE user_id = ${userId} ORDER BY created_at DESC`,52 sql`SELECT album_id AS id, created_at AS "createdAt" FROM favorite_albums WHERE user_id = ${userId} ORDER BY created_at DESC`,53 sql<{ queue: string[]; queue_index: number; position_ms: number; shuffle: boolean; repeat: string; volume: number; updated_at: Date }[]>`54 SELECT queue, queue_index, position_ms, shuffle, repeat, volume, updated_at FROM playback_state WHERE user_id = ${userId}`,55 ]);56 const by = new Map<string, unknown[]>();57 for (const e of entries) { const { playlistId, ...rest } = e; (by.get(playlistId) ?? by.set(playlistId, []).get(playlistId)!).push(rest); }58 return {59 playlists: playlists.map((p) => ({ ...p, entries: by.get(p.id) ?? [] })),60 favoriteTracks: favTracks, favoriteAlbums: favAlbums,61 playback: pb ? { queue: pb.queue, index: pb.queue_index, positionMs: pb.position_ms, shuffle: pb.shuffle, repeat: pb.repeat, volume: pb.volume, updatedAt: pb.updated_at } : null,62 };63}6465// ── Listening history ───────────────────────────────────────────────────────────────────────────────6667export interface PlayEvent { id: string; trackId: string; playedAt: string; listenedMs: number; completed: boolean; source: "stream" | "local" }6869/** Idempotent: an event already stored (same client-generated id) or naming a deleted track is skipped, not refused. */70export async function recordPlays(userId: string, client: string, events: PlayEvent[]) {71 if (events.length === 0) return { stored: 0, skipped: 0 };72 const known = new Set((await sql<{ id: string }[]>`SELECT id FROM tracks WHERE id = ANY(${events.map((e) => e.trackId)}::uuid[])`).map((r) => r.id));73 const now = Date.now();74 let stored = 0;75 for (const e of events) {76 if (!known.has(e.trackId)) continue;77 const at = new Date(e.playedAt);78 if (Number.isNaN(at.getTime()) || at.getTime() > now + 86_400_000) continue; // a clock far in the future is not history79 const r = await sql`INSERT INTO play_events (id, user_id, track_id, played_at, listened_ms, completed, source, client)80 VALUES (${e.id}, ${userId}, ${e.trackId}, ${at}, ${e.listenedMs}, ${e.completed}, ${e.source}, ${client.slice(0, 120)}) ON CONFLICT (id) DO NOTHING RETURNING id`;81 stored += r.length;82 }83 return { stored, skipped: events.length - stored };84}8586export async function listPlays(userId: string, p: { limit: number; offset: number }) {87 const [items, [{ n }]] = await Promise.all([88 sql`SELECT id, track_id AS "trackId", played_at AS "playedAt", listened_ms AS "listenedMs", completed, source89 FROM play_events WHERE user_id = ${userId} ORDER BY played_at DESC, id LIMIT ${p.limit} OFFSET ${p.offset}`,90 sql<{ n: number }[]>`SELECT count(*)::int AS n FROM play_events WHERE user_id = ${userId}`,91 ]);92 return { items, total: n, ...p };93}9495/** Optimistic concurrency for playlist edits made offline: refuse when the list changed after the client's copy. */96export async function assertPlaylistBase(userId: string, id: string, base: string | undefined) {97 if (!base) return;98 const at = new Date(base);99 if (Number.isNaN(at.getTime())) throw new ApiError(400, "invalid_input", "baseUpdatedAt is not a date");100 const [p] = await sql<{ updated_at: Date }[]>`SELECT updated_at FROM playlists WHERE id = ${id} AND user_id = ${userId}`;101 if (!p) throw new ApiError(404, "not_found", "Playlist not found");102 // The client holds millisecond precision (JSON dates); compare at that precision.103 if (Math.floor(p.updated_at.getTime()) > at.getTime()) throw new ApiError(409, "conflict", "The playlist was changed elsewhere", { updatedAt: p.updated_at.toISOString() });104}105106export async function playlistStamp(userId: string, id: string) {107 const [p] = await sql<{ updated_at: Date }[]>`SELECT updated_at FROM playlists WHERE id = ${id} AND user_id = ${userId}`;108 return { ok: true, updatedAt: p?.updated_at ?? null };109}110