SPB Git

Repository Public

admin.ts 27.1 KB 373 lines typescript

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
Files main
Menu

Theme