Repository Public
1import { randomUUID } from "node:crypto";2import { createWriteStream } from "node:fs";3import { mkdir, open, readFile, rm, stat } from "node:fs/promises";4import path from "node:path";5import { Readable, Transform } from "node:stream";6import { pipeline } from "node:stream/promises";7import { sql, type Tx } from "@/lib/db";8import { env } from "@/lib/env";9import { ApiError } from "@/lib/http";10import { audit } from "@/lib/audit";11import { issueSetupToken, type UserContext } from "@/lib/auth";12import { clean, norm, safeFilename } from "@/lib/text";13import { storage } from "./storage";14import { libraryBytes, pruneOrphans, storeArtwork, uploadTmpPath, upsertAlbum, upsertArtist, upsertGenre } from "./ingest";15import { MediaError } from "./media";1617/** Write side of the library. Every function here is only reachable through the admin router (role checked there). */1819// ── Uploads ─────────────────────────────────────────────────────────────────────────────────────────2021const uploadCols = sql`u.id, u.filename, u.size_bytes::float8 AS "sizeBytes", u.received_bytes::float8 AS "receivedBytes", u.status, u.error_code AS "errorCode",22 u.error_detail AS "errorDetail", u.kind, u.track_id AS "trackId", u.duplicate_of AS "duplicateOf", u.parent_id AS "parentId", u.created_at AS "createdAt", u.updated_at AS "updatedAt",23 (SELECT t.title FROM tracks t WHERE t.id = coalesce(u.track_id, u.duplicate_of)) AS "trackTitle"`;2425export function uploadLimits() {26 return { maxFileBytes: env.maxFileBytes, chunkBytes: Math.min(8 * 1024 * 1024, env.maxChunkBytes), maxFiles: env.maxFilesPerBatch, quotaBytes: env.libraryQuotaBytes };27}2829export async function createUpload(user: UserContext, input: { filename: string; size: number }) {30 if (input.size <= 0) throw new ApiError(400, "empty_file", "The file is empty");31 if (input.size > env.maxFileBytes) throw new ApiError(413, "file_too_large", `Files are limited to ${Math.round(env.maxFileBytes / 1048576)} MB`);32 const [{ n }] = await sql<{ n: number }[]>`SELECT count(*)::int AS n FROM uploads WHERE status IN ('uploading', 'received', 'validating', 'extracting') AND parent_id IS NULL`;33 if (n >= env.maxFilesPerBatch) throw new ApiError(429, "too_many_uploads", `At most ${env.maxFilesPerBatch} files can be in progress at once`);34 const quota = env.libraryQuotaBytes;35 if (quota && (await libraryBytes()) + input.size > quota) throw new ApiError(413, "quota_exceeded", "The library quota is reached");36 const health = await storage().health();37 if (!health.ok) throw new ApiError(503, "storage_unavailable", "The library storage is not available");38 if (health.freeBytes !== null && health.freeBytes < input.size * 2 + 512 * 1024 * 1024) throw new ApiError(507, "disk_full", "Not enough free space on the library volume");39 const id = randomUUID();40 const tmp = uploadTmpPath(id);41 await mkdir(path.dirname(tmp), { recursive: true, mode: 0o700 });42 await (await open(tmp, "w", 0o600)).close();43 await sql`INSERT INTO uploads (id, user_id, filename, size_bytes) VALUES (${id}, ${user.userId}, ${safeFilename(input.filename)}, ${input.size})`;44 return { id, receivedBytes: 0, ...uploadLimits() };45}4647/**48 * Appends one chunk at `offset`. The body is streamed to disk (never buffered whole) and counted as it arrives.49 * A chunk sent at the wrong offset answers 409 with the offset the server expects, which is how a client resumes.50 */51export async function appendChunk(id: string, offset: number, req: Request) {52 const [u] = await sql<{ size_bytes: string; received_bytes: string; status: string }[]>`SELECT size_bytes, received_bytes, status FROM uploads WHERE id = ${id}`;53 if (!u) throw new ApiError(404, "not_found", "Upload not found");54 if (u.status !== "uploading") throw new ApiError(409, "not_uploading", "This upload no longer accepts data");55 const size = Number(u.size_bytes), received = Number(u.received_bytes);56 if (offset !== received) throw new ApiError(409, "offset_mismatch", "Unexpected offset", { receivedBytes: received });57 const declared = Number(req.headers.get("content-length") ?? NaN);58 const room = Math.min(env.maxChunkBytes, size - received);59 if (Number.isFinite(declared) && declared > room) throw new ApiError(413, "chunk_too_large", "Chunk larger than allowed");60 if (!req.body) throw new ApiError(400, "empty_chunk", "Empty chunk");61 const tmp = uploadTmpPath(id);62 if (!(await stat(tmp).catch(() => null))) throw new ApiError(409, "file_missing", "The upload must be restarted");63 let n = 0;64 const guard = new Transform({ transform(chunk: Buffer, _e, cb) { n += chunk.length; if (n > room) cb(new ApiError(413, "chunk_too_large", "Chunk larger than allowed")); else cb(null, chunk); } });65 try {66 await pipeline(Readable.fromWeb(req.body as never), guard, createWriteStream(tmp, { flags: "r+", start: received }));67 } catch (e) { if (e instanceof ApiError) throw e; throw new ApiError(400, "chunk_interrupted", "The chunk was interrupted", { receivedBytes: received }); }68 if (n === 0) throw new ApiError(400, "empty_chunk", "Empty chunk");69 const done = received + n === size;70 const upd = await sql`UPDATE uploads SET received_bytes = ${received + n}, status = ${done ? "received" : "uploading"}, updated_at = now()71 WHERE id = ${id} AND status = 'uploading' AND received_bytes = ${received} RETURNING id`;72 if (upd.length === 0) throw new ApiError(409, "offset_mismatch", "Concurrent write", { receivedBytes: received });73 return { receivedBytes: received + n, complete: done };74}7576export async function getUpload(id: string) {77 const [u] = await sql`SELECT ${uploadCols} FROM uploads u WHERE u.id = ${id}`;78 if (!u) throw new ApiError(404, "not_found", "Upload not found");79 return u;80}8182export async function listUploads(filter: "all" | "active" | "error" | "duplicate" | "ready", p: { limit: number; offset: number }) {83 const where = filter === "active" ? sql`u.status IN ('uploading', 'received', 'validating', 'extracting')` : filter === "error" ? sql`u.status = 'error'`84 : filter === "duplicate" ? sql`u.status = 'duplicate'` : filter === "ready" ? sql`u.status = 'ready'` : sql`true`;85 const [items, [{ n }], counts] = await Promise.all([86 sql`SELECT ${uploadCols} FROM uploads u WHERE ${where} ORDER BY u.created_at DESC, u.filename LIMIT ${p.limit} OFFSET ${p.offset}`,87 sql<{ n: number }[]>`SELECT count(*)::int AS n FROM uploads u WHERE ${where}`,88 sql<{ status: string; n: number }[]>`SELECT status, count(*)::int AS n FROM uploads GROUP BY status`,89 ]);90 return { items, total: n, ...p, counts: Object.fromEntries(counts.map((c) => [c.status, c.n])) };91}9293export async function cancelUpload(user: UserContext, id: string) {94 const r = await sql`UPDATE uploads SET status = 'cancelled', updated_at = now() WHERE id = ${id} AND status IN ('uploading', 'received', 'duplicate', 'error') RETURNING id`;95 if (r.length === 0) throw new ApiError(409, "not_cancellable", "This upload can no longer be cancelled");96 await rm(uploadTmpPath(id), { force: true });97 return { ok: true };98}99100/** Error → back in the queue when the data is still on the server; otherwise the client must send it again. */101export async function retryUpload(id: string) {102 const [u] = await sql<{ status: string; size_bytes: string }[]>`SELECT status, size_bytes FROM uploads WHERE id = ${id}`;103 if (!u) throw new ApiError(404, "not_found");104 if (u.status !== "error") throw new ApiError(409, "not_retryable", "Only failed imports can be retried");105 const st = await stat(uploadTmpPath(id)).catch(() => null);106 if (!st || st.size !== Number(u.size_bytes)) throw new ApiError(409, "file_missing", "The file must be uploaded again");107 await sql`UPDATE uploads SET status = 'received', error_code = NULL, error_detail = NULL, attempts = 0, updated_at = now() WHERE id = ${id}`;108 return { ok: true };109}110111/** Explicit confirmation that a detected duplicate replaces the track already in the library. */112export async function confirmReplace(user: UserContext, id: string) {113 const r = await sql<{ duplicate_of: string }[]>`UPDATE uploads SET replace = true, status = 'received', error_code = NULL, updated_at = now() WHERE id = ${id} AND status = 'duplicate' RETURNING duplicate_of`;114 if (r.length === 0) throw new ApiError(409, "not_duplicate", "This upload is not waiting for a decision");115 await audit({ userId: user.userId, actor: "admin", action: "upload.replace_confirmed", entity: "track", entityId: r[0].duplicate_of ?? "" });116 return { ok: true };117}118119export async function clearUploads() {120 const rows = await sql<{ id: string }[]>`DELETE FROM uploads WHERE status IN ('ready', 'cancelled', 'error', 'duplicate') RETURNING id`;121 for (const r of rows) await rm(uploadTmpPath(r.id), { force: true });122 return { removed: rows.length };123}124125// ── Library edits ───────────────────────────────────────────────────────────────────────────────────126127export interface TrackPatch { title?: string; artist?: string; album?: string; albumArtist?: string; trackNo?: number | null; discNo?: number; year?: number | null; genre?: string }128129async function patchTrack(tx: Tx, id: string, p: TrackPatch) {130 const [cur] = await tx<{ id: string; album_id: string; album_title: string; album_artist: string; album_year: number | null; album_genre: string | null; album_art: string | null }[]>`131 SELECT t.id, t.album_id, al.title AS album_title, aa.name AS album_artist, al.year AS album_year, al.genre_id AS album_genre, al.artwork_id AS album_art132 FROM tracks t JOIN albums al ON al.id = t.album_id JOIN artists aa ON aa.id = al.artist_id WHERE t.id = ${id} FOR UPDATE OF t`;133 if (!cur) throw new ApiError(404, "not_found", "Track not found");134 const set: Record<string, unknown> = {};135 if (p.title !== undefined) { const t = clean(p.title); if (!t) throw new ApiError(400, "invalid_input", "The title cannot be empty"); set.title = t; set.title_norm = norm(t); }136 if (p.artist !== undefined) set.artist_id = await upsertArtist(tx, p.artist);137 if (p.trackNo !== undefined) set.track_no = p.trackNo;138 if (p.discNo !== undefined) set.disc_no = p.discNo;139 if (p.year !== undefined) set.year = p.year;140 let genreId: string | null | undefined;141 if (p.genre !== undefined) { genreId = await upsertGenre(tx, p.genre); set.genre_id = genreId; }142 if (p.album !== undefined || p.albumArtist !== undefined) {143 // Moving to another album: found by (album artist, title) or created; the cover follows when the target has none.144 const artistId = await upsertArtist(tx, p.albumArtist ?? cur.album_artist);145 set.album_id = await upsertAlbum(tx, { title: p.album ?? cur.album_title, artistId, year: p.year ?? cur.album_year, genreId: genreId ?? cur.album_genre, artworkId: cur.album_art });146 }147 if (Object.keys(set).length) await tx`UPDATE tracks SET ${tx(set)}, updated_at = now() WHERE id = ${id}`;148}149150export async function updateTracks(user: UserContext, ids: string[], patch: TrackPatch) {151 if (ids.length > 1 && patch.title !== undefined) throw new ApiError(400, "invalid_input", "A title can only be set on one track at a time");152 await sql.begin(async (tx) => { for (const id of ids) await patchTrack(tx, id, patch); });153 await audit({ userId: user.userId, actor: "admin", action: "track.updated", entity: "track", entityId: ids.length === 1 ? ids[0] : "", meta: { count: ids.length, fields: Object.keys(patch) } });154 await pruneOrphans();155 return { updated: ids.length };156}157158export async function updateAlbum(user: UserContext, id: string, p: { title?: string; artist?: string; year?: number | null; genre?: string }) {159 const merged = await sql.begin(async (tx) => {160 const [al] = await tx<{ id: string; title: string; artist_id: string; year: number | null; genre_id: string | null; artwork_id: string | null }[]>`SELECT id, title, artist_id, year, genre_id, artwork_id FROM albums WHERE id = ${id} FOR UPDATE`;161 if (!al) throw new ApiError(404, "not_found", "Album not found");162 const title = p.title !== undefined ? clean(p.title) : al.title;163 if (!title) throw new ApiError(400, "invalid_input", "The title cannot be empty");164 const artistId = p.artist !== undefined ? await upsertArtist(tx, p.artist) : al.artist_id;165 const genreId = p.genre !== undefined ? await upsertGenre(tx, p.genre) : al.genre_id;166 const year = p.year !== undefined ? p.year : al.year;167 const [other] = await tx<{ id: string }[]>`SELECT id FROM albums WHERE artist_id = ${artistId} AND title_norm = ${norm(title)} AND id <> ${id}`;168 if (other) {169 // The new identity is an album that already exists: its tracks join it (merge).170 await tx`UPDATE tracks SET album_id = ${other.id}, updated_at = now() WHERE album_id = ${id}`;171 await tx`UPDATE albums SET artwork_id = coalesce(artwork_id, ${al.artwork_id}), year = coalesce(year, ${year}), genre_id = coalesce(genre_id, ${genreId}) WHERE id = ${other.id}`;172 await tx`DELETE FROM favorite_albums WHERE album_id = ${id}`;173 await tx`DELETE FROM albums WHERE id = ${id}`;174 return other.id;175 }176 await tx`UPDATE albums SET title = ${title}, title_norm = ${norm(title)}, artist_id = ${artistId}, year = ${year}, genre_id = ${genreId} WHERE id = ${id}`;177 return null;178 }) as string | null;179 await audit({ userId: user.userId, actor: "admin", action: merged ? "album.merged" : "album.updated", entity: "album", entityId: id, meta: { fields: Object.keys(p), into: merged ?? undefined } });180 await pruneOrphans();181 return { id: merged ?? id, merged: !!merged };182}183184/** Track order of an album: the given ids become tracks 1..n of their disc, in that order. */185export async function reorderAlbum(user: UserContext, albumId: string, trackIds: string[]) {186 await sql.begin(async (tx) => {187 const rows = await tx<{ id: string; disc_no: number }[]>`SELECT id, disc_no FROM tracks WHERE album_id = ${albumId} FOR UPDATE`;188 const disc = new Map(rows.map((r) => [r.id, r.disc_no]));189 if (trackIds.length !== rows.length || !trackIds.every((t) => disc.has(t)) || new Set(trackIds).size !== trackIds.length) throw new ApiError(400, "invalid_input", "The order must list every track of the album once");190 const next = new Map<number, number>();191 for (const t of trackIds) { const d = disc.get(t)!; const n = (next.get(d) ?? 0) + 1; next.set(d, n); await tx`UPDATE tracks SET track_no = ${n}, updated_at = now() WHERE id = ${t}`; }192 });193 await audit({ userId: user.userId, actor: "admin", action: "album.reordered", entity: "album", entityId: albumId });194 return { ok: true };195}196197const MAX_COVER_UPLOAD = 25 * 1024 * 1024;198export async function setAlbumArtwork(user: UserContext, albumId: string, req: Request) {199 const [al] = await sql<{ id: string }[]>`SELECT id FROM albums WHERE id = ${albumId}`;200 if (!al) throw new ApiError(404, "not_found", "Album not found");201 if (!req.body) throw new ApiError(400, "invalid_input", "Image required");202 const dir = path.join(env.tmpDir, "work");203 await mkdir(dir, { recursive: true, mode: 0o700 });204 const tmp = path.join(dir, `${randomUUID()}.img`);205 let n = 0;206 const guard = new Transform({ transform(chunk: Buffer, _e, cb) { n += chunk.length; if (n > MAX_COVER_UPLOAD) cb(new ApiError(413, "image_too_large", "Covers are limited to 25 MB")); else cb(null, chunk); } });207 try {208 try { await pipeline(Readable.fromWeb(req.body as never), guard, createWriteStream(tmp, { mode: 0o600 })); }209 catch (e) { if (e instanceof ApiError) throw e; throw new ApiError(400, "upload_interrupted", "The image upload was interrupted"); }210 let artworkId: string;211 try { artworkId = await storeArtwork(tmp); }212 catch (e) { if (e instanceof MediaError) throw new ApiError(400, e.code, e.message); throw e; }213 await sql`UPDATE albums SET artwork_id = ${artworkId} WHERE id = ${albumId}`;214 await audit({ userId: user.userId, actor: "admin", action: "album.artwork_set", entity: "album", entityId: albumId });215 await pruneOrphans();216 return { artworkId };217 } finally { await rm(tmp, { force: true }); }218}219220/**221 * Deletes tracks: rows first (one transaction), then exactly the files those rows named — the storage keys are222 * read from media_assets of the deleted tracks, so no other file can be touched.223 */224export async function deleteTracks(user: UserContext, ids: string[]) {225 const removed = await sql.begin(async (tx) => {226 const tracks = await tx<{ id: string; title: string; album: string; artist: string }[]>`227 SELECT t.id, t.title, al.title AS album, ar.name AS artist FROM tracks t JOIN albums al ON al.id = t.album_id JOIN artists ar ON ar.id = t.artist_id WHERE t.id = ANY(${ids}::uuid[]) FOR UPDATE OF t`;228 if (tracks.length === 0) throw new ApiError(404, "not_found", "Track not found");229 const tids = tracks.map((t) => t.id);230 const assets = await tx<{ storage_key: string; sha256: string | null; kind: string; track_id: string }[]>`SELECT storage_key, sha256, kind, track_id FROM media_assets WHERE track_id = ANY(${tids}::uuid[])`;231 await tx`DELETE FROM tracks WHERE id = ANY(${tids}::uuid[])`; // cascades: assets, favourites, playlist entries232 for (const t of tracks) {233 await audit({ userId: user.userId, actor: "admin", action: "track.deleted", entity: "track", entityId: t.id,234 meta: { title: t.title, album: t.album, artist: t.artist, sha256: assets.find((a) => a.track_id === t.id && a.kind === "original")?.sha256 ?? null } }, tx);235 }236 return { tracks, assets };237 }) as { tracks: { id: string }[]; assets: { storage_key: string }[] };238 const st = storage();239 let fileErrors = 0;240 for (const a of removed.assets) await st.delete(a.storage_key).catch(() => { fileErrors++; });241 await pruneOrphans();242 return { deleted: removed.tracks.length, fileErrors };243}244245export async function deleteAlbum(user: UserContext, albumId: string) {246 const rows = await sql<{ id: string }[]>`SELECT id FROM tracks WHERE album_id = ${albumId}`;247 const [al] = await sql<{ title: string }[]>`SELECT title FROM albums WHERE id = ${albumId}`;248 if (!al) throw new ApiError(404, "not_found", "Album not found");249 await audit({ userId: user.userId, actor: "admin", action: "album.deleted", entity: "album", entityId: albumId, meta: { title: al.title, tracks: rows.length } });250 if (rows.length === 0) { await pruneOrphans(); return { deleted: 0, fileErrors: 0 }; }251 return deleteTracks(user, rows.map((r) => r.id));252}253254/** Queues a fresh analysis (technical data, missing cover, rendition) — picked up by the worker. */255export async function requestReanalysis(user: UserContext, ids: string[]) {256 const r = await sql`UPDATE tracks SET analysis_requested = true, updated_at = now() WHERE id = ANY(${ids}::uuid[]) RETURNING id`;257 if (r.length === 0) throw new ApiError(404, "not_found");258 await audit({ userId: user.userId, actor: "admin", action: "track.reanalysis_requested", meta: { count: r.length } });259 return { queued: r.length };260}261262// ── Accounts ────────────────────────────────────────────────────────────────────────────────────────263264export const listUsers = () => sql`265 SELECT u.id, u.email, u.display_name AS "displayName", u.role, u.is_owner AS "isOwner", u.disabled_at AS "disabledAt", u.created_at AS "createdAt",266 (SELECT count(*)::int FROM authenticators a WHERE a.user_id = u.id) AS passkeys,267 (SELECT count(*)::int FROM sessions s WHERE s.user_id = u.id AND s.revoked_at IS NULL AND s.expires_at > now()) AS sessions,268 (SELECT max(s.last_seen_at) FROM sessions s WHERE s.user_id = u.id) AS "lastSeenAt",269 EXISTS (SELECT 1 FROM setup_tokens t WHERE t.user_id = u.id AND t.used_at IS NULL AND t.expires_at > now()) AS "invitePending"270 FROM users u ORDER BY u.is_owner DESC, u.created_at`;271272const INVITE_TTL_MIN = 72 * 60;273const inviteUrl = (token: string) => `${env.appUrl}/setup#${token}`;274275/** There is no public sign-up: an account exists only because an administrator created it. The link is shown once. */276export async function inviteUser(admin: UserContext, input: { email: string; displayName: string; role: "admin" | "listener" }) {277 let id: string;278 try {279 [{ id }] = await sql<{ id: string }[]>`INSERT INTO users (email, display_name, role) VALUES (${input.email}, ${clean(input.displayName, 80)}, ${input.role}) RETURNING id`;280 } catch (e) { if ((e as { code?: string }).code === "23505") throw new ApiError(409, "email_taken", "An account already uses this address"); throw e; }281 const token = await issueSetupToken(id, "invite", INVITE_TTL_MIN);282 await audit({ userId: admin.userId, actor: "admin", action: "user.invited", entity: "user", entityId: id, meta: { role: input.role } });283 return { id, inviteUrl: inviteUrl(token), expiresInHours: INVITE_TTL_MIN / 60 };284}285286async function targetUser(id: string) {287 const [u] = await sql<{ id: string; is_owner: boolean; role: string }[]>`SELECT id, is_owner, role FROM users WHERE id = ${id}`;288 if (!u) throw new ApiError(404, "not_found", "Account not found");289 return u;290}291292export async function reinviteUser(admin: UserContext, id: string) {293 const u = await targetUser(id);294 if (u.is_owner) throw new ApiError(403, "owner_protected", "The owner's access is recovered from the server's shell only");295 const token = await issueSetupToken(id, "invite", INVITE_TTL_MIN);296 await audit({ userId: admin.userId, actor: "admin", action: "user.reinvited", entity: "user", entityId: id });297 return { inviteUrl: inviteUrl(token), expiresInHours: INVITE_TTL_MIN / 60 };298}299300export async function updateUser(admin: UserContext, id: string, p: { role?: "admin" | "listener"; disabled?: boolean; displayName?: string }) {301 const u = await targetUser(id);302 if (u.is_owner && (p.role !== undefined || p.disabled !== undefined)) throw new ApiError(403, "owner_protected", "The owner account cannot be demoted or disabled");303 if (id === admin.userId && (p.role === "listener" || p.disabled)) throw new ApiError(403, "self_protected", "You cannot demote or disable your own account");304 await sql.begin(async (tx) => {305 if (p.role !== undefined) await tx`UPDATE users SET role = ${p.role} WHERE id = ${id}`;306 if (p.displayName !== undefined) await tx`UPDATE users SET display_name = ${clean(p.displayName, 80)} WHERE id = ${id}`;307 if (p.disabled !== undefined) {308 await tx`UPDATE users SET disabled_at = ${p.disabled ? sql`now()` : null} WHERE id = ${id}`;309 if (p.disabled) await tx`UPDATE sessions SET revoked_at = now() WHERE user_id = ${id} AND revoked_at IS NULL`;310 }311 });312 await audit({ userId: admin.userId, actor: "admin", action: "user.updated", entity: "user", entityId: id, meta: p });313 return { ok: true };314}315316export async function deleteUser(admin: UserContext, id: string) {317 const u = await targetUser(id);318 if (u.is_owner) throw new ApiError(403, "owner_protected", "The owner account cannot be deleted");319 if (id === admin.userId) throw new ApiError(403, "self_protected", "You cannot delete your own account");320 await sql`DELETE FROM users WHERE id = ${id}`;321 await audit({ userId: admin.userId, actor: "admin", action: "user.deleted", entity: "user", entityId: id });322 return { ok: true };323}324325export async function revokeUserSessions(admin: UserContext, id: string) {326 await targetUser(id);327 const r = await sql`UPDATE sessions SET revoked_at = now() WHERE user_id = ${id} AND revoked_at IS NULL AND id <> ${admin.sessionId} RETURNING id`;328 await audit({ userId: admin.userId, actor: "admin", action: "user.sessions_revoked", entity: "user", entityId: id, meta: { count: r.length } });329 return { revoked: r.length };330}331332// ── Health and audit ────────────────────────────────────────────────────────────────────────────────333334export async function health() {335 const st = storage();336 const [sth, [c], byKind, statuses, [hb], recentErrors] = await Promise.all([337 st.health(),338 sql<{ tracks: number; albums: number; artists: number; duration: number; unplayable: number; compat_pending: number; compat_error: number }[]>`339 SELECT (SELECT count(*)::int FROM tracks) AS tracks, (SELECT count(*)::int FROM albums) AS albums, (SELECT count(*)::int FROM artists) AS artists,340 (SELECT coalesce(sum(duration_ms), 0)::float8 FROM tracks) AS duration,341 (SELECT count(*)::int FROM tracks WHERE web_playable <> 'yes' AND compat_state <> 'ready') AS unplayable,342 (SELECT count(*)::int FROM tracks WHERE compat_state IN ('queued', 'working')) AS compat_pending,343 (SELECT count(*)::int FROM tracks WHERE compat_state = 'error') AS compat_error`,344 sql<{ kind: string; files: number; bytes: number }[]>`SELECT kind, count(*)::int AS files, coalesce(sum(size_bytes), 0)::float8 AS bytes FROM media_assets GROUP BY kind`,345 sql<{ status: string; n: number }[]>`SELECT status, count(*)::int AS n FROM uploads GROUP BY status`,346 sql<{ age: number | null }[]>`SELECT (SELECT extract(epoch FROM now() - beat_at)::int FROM worker_heartbeat WHERE name = 'worker') AS age`,347 sql`SELECT id, filename, error_code AS "errorCode", error_detail AS "errorDetail", updated_at AS "updatedAt" FROM uploads WHERE status = 'error' ORDER BY updated_at DESC LIMIT 10`,348 ]);349 let backup: { at: string; database: boolean; library: boolean; files?: number; bytes?: number } | null = null;350 if (env.backupDir) {351 try { backup = JSON.parse(await readFile(path.join(env.backupDir, "last-backup.json"), "utf8")); } catch { backup = null; }352 }353 const used = byKind.reduce((s, k) => s + k.bytes, 0);354 return {355 storage: { driver: st.driver, available: sth.ok, freeBytes: sth.freeBytes, totalBytes: sth.totalBytes, usedBytes: used, quotaBytes: env.libraryQuotaBytes, byKind },356 library: { tracks: c.tracks, albums: c.albums, artists: c.artists, durationMs: c.duration, notPlayableInBrowser: c.unplayable, renditionsPending: c.compat_pending, renditionErrors: c.compat_error },357 imports: { counts: Object.fromEntries(statuses.map((s) => [s.status, s.n])), recentErrors },358 worker: { ok: hb.age !== null && hb.age < 120, ageSec: hb.age },359 backup: { configured: !!env.backupDir, last: backup },360 lyrics: { mode: env.lyrics, configured: env.lyrics !== "off" && !!env.lyricsPython, model: env.lyricsModel, counts: Object.fromEntries((await sql<{ state: string; n: number }[]>`SELECT state, count(*)::int AS n FROM lyrics GROUP BY state`).map((r) => [r.state, r.n])) },361 config: { renditions: env.renditions, renditionBitrateKbps: env.renditionBitrateK, transcodeDsd: env.transcodeDsd, maxFileBytes: env.maxFileBytes, uploadRetentionDays: env.uploadRetentionDays, rpId: env.rpId, origin: env.appUrl },362 };363}364365export async function listAudit(p: { limit: number; offset: number }) {366 const [items, [{ n }]] = await Promise.all([367 sql`SELECT e.id::text, e.at, e.actor, e.action, e.entity, e.entity_id AS "entityId", e.meta, u.display_name AS "userName"368 FROM audit_events e LEFT JOIN users u ON u.id = e.user_id ORDER BY e.at DESC, e.id DESC LIMIT ${p.limit} OFFSET ${p.offset}`,369 sql<{ n: number }[]>`SELECT count(*)::int AS n FROM audit_events`,370 ]);371 return { items, total: n, ...p };372}373