SPB Git

Repository Public

ingest.ts 22.0 KB 331 lines typescript

1import { createHash, randomUUID } from "node:crypto";2import { createReadStream, createWriteStream } from "node:fs";3import { mkdir, rm, stat } from "node:fs/promises";4import path from "node:path";5import { pipeline } from "node:stream/promises";6import { Transform } from "node:stream";7import yauzl from "yauzl";8import { sql, type Tx } from "@/lib/db";9import { env } from "@/lib/env";10import { audit } from "@/lib/audit";11import { clean, norm, safeFilename } from "@/lib/text";12import { keys, storage, THUMB_SIZES } from "./storage";13import { AUDIO_KINDS, extFor, extractCover, imageSize, makeThumb, MediaError, mimeFor, probe, sniff, transcodeAac, webPlayable, type Kind, type Probe } from "./media";1415/** Import pipeline, run by the worker: validation → metadata → storage. Everything here works on server-built paths. */1617export const uploadTmpPath = (uploadId: string) => path.join(env.tmpDir, "uploads", `${uploadId}.part`);18const scratch = async () => { const d = path.join(env.tmpDir, "work"); await mkdir(d, { recursive: true, mode: 0o700 }); return path.join(d, randomUUID()); };1920export async function fileSha256(file: string): Promise<string> {21  const h = createHash("sha256");22  await pipeline(createReadStream(file), h);23  return h.digest("hex");24}2526export async function libraryBytes(): Promise<number> {27  const [r] = await sql<{ n: string }[]>`SELECT coalesce(sum(size_bytes), 0)::text AS n FROM media_assets`;28  return Number(r.n);29}3031// ── Entities ────────────────────────────────────────────────────────────────────────────────────────3233export async function upsertArtist(tx: Tx, name: string): Promise<string> {34  const n = clean(name, 200) || "Artiste inconnu";35  const [r] = await tx<{ id: string }[]>`INSERT INTO artists (name, name_norm) VALUES (${n}, ${norm(n)}) ON CONFLICT (name_norm) DO UPDATE SET name_norm = EXCLUDED.name_norm RETURNING id`;36  return r.id;37}38export async function upsertGenre(tx: Tx, name: string): Promise<string | null> {39  const n = clean(name, 80);40  if (!n) return null;41  const [r] = await tx<{ id: string }[]>`INSERT INTO genres (name, name_norm) VALUES (${n}, ${norm(n)}) ON CONFLICT (name_norm) DO UPDATE SET name_norm = EXCLUDED.name_norm RETURNING id`;42  return r.id;43}44export async function upsertAlbum(tx: Tx, a: { title: string; artistId: string; year: number | null; genreId: string | null; artworkId: string | null }): Promise<string> {45  const t = clean(a.title, 300) || "Album inconnu";46  const [r] = await tx<{ id: string }[]>`47    INSERT INTO albums (title, title_norm, artist_id, year, genre_id, artwork_id) VALUES (${t}, ${norm(t)}, ${a.artistId}, ${a.year}, ${a.genreId}, ${a.artworkId})48    ON CONFLICT (artist_id, title_norm) DO UPDATE SET49      year = coalesce(albums.year, EXCLUDED.year), genre_id = coalesce(albums.genre_id, EXCLUDED.genre_id), artwork_id = coalesce(albums.artwork_id, EXCLUDED.artwork_id)50    RETURNING id`;51  return r.id;52}5354/** Removes albums, artists, genres and cover files nothing refers to any more. Returns nothing: best effort after a change. */55export async function pruneOrphans(): Promise<void> {56  await sql`DELETE FROM albums al WHERE NOT EXISTS (SELECT 1 FROM tracks t WHERE t.album_id = al.id)`;57  await sql`DELETE FROM artists a WHERE NOT EXISTS (SELECT 1 FROM tracks t WHERE t.artist_id = a.id) AND NOT EXISTS (SELECT 1 FROM albums al WHERE al.artist_id = a.id)`;58  await sql`DELETE FROM genres g WHERE NOT EXISTS (SELECT 1 FROM tracks t WHERE t.genre_id = g.id) AND NOT EXISTS (SELECT 1 FROM albums al WHERE al.genre_id = g.id)`;59  const orphans = await sql<{ id: string }[]>`SELECT id FROM artworks w WHERE NOT EXISTS (SELECT 1 FROM albums al WHERE al.artwork_id = w.id) AND w.created_at < now() - interval '10 minutes'`;60  for (const w of orphans) {61    const assets = await sql<{ storage_key: string }[]>`SELECT storage_key FROM media_assets WHERE artwork_id = ${w.id}`;62    const gone = await sql`DELETE FROM artworks w WHERE id = ${w.id} AND NOT EXISTS (SELECT 1 FROM albums al WHERE al.artwork_id = w.id) RETURNING id`;63    if (gone.length) for (const a of assets) await storage().delete(a.storage_key).catch(() => {});64  }65}6667// ── Cover art ───────────────────────────────────────────────────────────────────────────────────────6869const MAX_COVER_BYTES = 25 * 1024 * 1024;7071/** Validates an image file, stores it untouched plus normalised square thumbnails. Deduplicated by content hash. */72export async function storeArtwork(file: string): Promise<string> {73  const kind = await sniff(file);74  if (kind !== "jpeg" && kind !== "png" && kind !== "webp") throw new MediaError("bad_image", "Covers must be JPEG, PNG or WebP");75  const size = (await stat(file)).size;76  if (size > MAX_COVER_BYTES) throw new MediaError("image_too_large", "Covers are limited to 25 MB");77  const { width, height } = await imageSize(file);78  const sha = await fileSha256(file);79  const [existing] = await sql<{ id: string }[]>`SELECT id FROM artworks WHERE sha256 = ${sha}`;80  if (existing) { await rm(file, { force: true }); return existing.id; }81  const st = storage();82  const thumbs: { size: number; tmp: string; bytes: number }[] = [];83  try {84    for (const s of THUMB_SIZES) { const tmp = `${await scratch()}.jpg`; await makeThumb(file, tmp, s); thumbs.push({ size: s, tmp, bytes: (await stat(tmp)).size }); }85    const ext = kind === "jpeg" ? "jpg" : kind;86    const mime = kind === "jpeg" ? "image/jpeg" : `image/${kind}`;87    await st.putFile(keys.artOriginal(sha, ext), file);88    for (const t of thumbs) await st.putFile(keys.artThumb(sha, t.size), t.tmp);89    return await sql.begin(async (tx) => {90      const [w] = await tx<{ id: string }[]>`INSERT INTO artworks (sha256, mime, width, height) VALUES (${sha}, ${mime}, ${width}, ${height}) ON CONFLICT (sha256) DO UPDATE SET mime = EXCLUDED.mime RETURNING id`;91      await tx`INSERT INTO media_assets (kind, storage_key, sha256, size_bytes, mime, artwork_id) VALUES ('artwork', ${keys.artOriginal(sha, ext)}, ${sha}, ${size}, ${mime}, ${w.id}) ON CONFLICT (storage_key) DO NOTHING`;92      for (const t of thumbs) await tx`INSERT INTO media_assets (kind, storage_key, size_bytes, mime, artwork_id) VALUES ('thumbnail', ${keys.artThumb(sha, t.size)}, ${t.bytes}, 'image/jpeg', ${w.id}) ON CONFLICT (storage_key) DO NOTHING`;93      return w.id;94    }) as string;95  } finally { for (const t of thumbs) await rm(t.tmp, { force: true }); }96}9798async function coverFromAudio(file: string): Promise<string | null> {99  const tmp = `${await scratch()}.img`;100  try {101    if (!(await extractCover(file, tmp))) return null;102    return await storeArtwork(tmp);103  } catch { return null; }       // a broken embedded picture never fails the import of the audio104  finally { await rm(tmp, { force: true }); }105}106107// ── One upload ──────────────────────────────────────────────────────────────────────────────────────108109interface UploadRow { id: string; user_id: string | null; filename: string; size_bytes: string; replace: boolean; duplicate_of: string | null; parent_id: string | null }110const setStatus = (id: string, status: string) => sql`UPDATE uploads SET status = ${status}, updated_at = now() WHERE id = ${id}`;111112function tagsWithFallback(p: Probe, filename: string) {113  const base = filename.replace(/\.[A-Za-z0-9]{2,5}$/, "");114  const m = base.match(/^(?:(\d{1,2})[-.](\d{1,3})|(\d{1,3}))[\s._-]+(.+)$/);115  return {116    title: p.tags.title || clean(m ? m[4] : base) || "Sans titre",117    artist: p.tags.artist || p.tags.albumArtist || "Artiste inconnu",118    albumArtist: p.tags.albumArtist || p.tags.artist || "Artiste inconnu",119    album: p.tags.album || "Album inconnu",120    trackNo: p.tags.trackNo ?? (m ? Number(m[2] ?? m[3]) : null),121    discNo: p.tags.discNo ?? (m?.[1] && m[2] ? Number(m[1]) : 1),122    year: p.tags.year, genre: p.tags.genre,123  };124}125126function compatStateFor(playable: "yes" | "some" | "no", codec: string): string {127  if (env.renditions === "never") return playable === "yes" ? "none" : "disabled";128  if (playable === "some") return "queued";129  if (playable === "no") return codec.startsWith("dsd") && env.transcodeDsd ? "queued" : "disabled";130  return "none";131}132133/** Processes a fully received upload. Never throws: the outcome is written on the row. */134export async function processUpload(id: string): Promise<void> {135  const [u] = await sql<UploadRow[]>`SELECT id, user_id, filename, size_bytes, replace, duplicate_of, parent_id FROM uploads WHERE id = ${id}`;136  if (!u) return;137  const tmp = uploadTmpPath(id);138  const fail = async (code: string, detail?: string, keepFile = false) => {139    await sql`UPDATE uploads SET status = 'error', error_code = ${code}, error_detail = ${detail?.slice(0, 300) ?? null}, updated_at = now() WHERE id = ${id}`;140    if (!keepFile) await rm(tmp, { force: true });141  };142  try {143    const size = (await stat(tmp).catch(() => null))?.size;144    if (size == null) return void (await fail("file_missing", "The uploaded data is no longer on the server; send the file again"));145    if (size !== Number(u.size_bytes)) return void (await fail("size_mismatch"));146    if (size === 0) return void (await fail("empty_file"));147148    // 1. Validation from the real content.149    const kind = await sniff(tmp);150    await sql`UPDATE uploads SET kind = ${kind}, updated_at = now() WHERE id = ${id}`;151    if (kind === "zip") { if (u.parent_id) return void (await fail("nested_archive")); return void (await expandArchive(u, tmp)); }152    if (!kind || !AUDIO_KINDS.has(kind)) return void (await fail("unsupported_type", "Accepted: FLAC, MP3, AAC/M4A, ALAC, WAV, AIFF, Ogg, DSD (DSF/DFF), or a ZIP of those"));153    const quota = env.libraryQuotaBytes;154    if (quota && (await libraryBytes()) + size > quota) return void (await fail("quota_exceeded", "The library quota is reached", true));155156    const sha = await fileSha256(tmp);157    await sql`UPDATE uploads SET sha256 = ${sha} WHERE id = ${id}`;158    const [same] = await sql<{ track_id: string }[]>`SELECT track_id FROM media_assets WHERE kind = 'original' AND sha256 = ${sha}`;159    if (same && !u.replace) {160      await sql`UPDATE uploads SET status = 'duplicate', duplicate_of = ${same.track_id}, error_code = 'same_file', updated_at = now() WHERE id = ${id}`;161      return;162    }163164    // 2. Metadata.165    await setStatus(id, "extracting");166    let p: Probe;167    try { p = await probe(tmp); } catch (e) { return void (await fail(e instanceof MediaError ? e.code : "undecodable", e instanceof Error ? e.message : undefined)); }168    const tags = tagsWithFallback(p, u.filename);169    const playable = webPlayable(p);170171    // A different file for a track already in the library (same album artist, album, disc, number and title).172    const [twin] = same ? [] : await sql<{ id: string }[]>`173      SELECT t.id FROM tracks t JOIN albums al ON al.id = t.album_id JOIN artists aa ON aa.id = al.artist_id174      WHERE aa.name_norm = ${norm(tags.albumArtist)} AND al.title_norm = ${norm(tags.album)} AND t.title_norm = ${norm(tags.title)}175        AND t.disc_no = ${tags.discNo ?? 1} AND t.track_no IS NOT DISTINCT FROM ${tags.trackNo}`;176    if (twin && !u.replace) {177      await sql`UPDATE uploads SET status = 'duplicate', duplicate_of = ${twin.id}, error_code = 'same_track', updated_at = now() WHERE id = ${id}`;178      return;179    }180    const replacing = u.replace ? (same?.track_id ?? twin?.id ?? null) : null;181182    const artworkId = p.hasPicture ? await coverFromAudio(tmp) : null;183    const ext = extFor(kind as Kind);184    const key = keys.original(sha, ext);185    const st = storage();186    const mime = mimeFor(p.codec, p.container);187    const compat = compatStateFor(playable, p.codec);188189    // 3. Storage, then the rows. The file is in place before any row points at it.190    const oldKeys: string[] = [];191    if (!same) await st.putFile(key, tmp); else await rm(tmp, { force: true });192    const trackId = await sql.begin(async (tx) => {193      const artistId = await upsertArtist(tx, tags.artist);194      const albumArtistId = await upsertArtist(tx, tags.albumArtist);195      const genreId = await upsertGenre(tx, tags.genre);196      const albumId = await upsertAlbum(tx, { title: tags.album, artistId: albumArtistId, year: tags.year, genreId, artworkId });197      const cols = { title: tags.title, title_norm: norm(tags.title), artist_id: artistId, album_id: albumId, track_no: tags.trackNo, disc_no: tags.discNo ?? 1, year: tags.year, genre_id: genreId,198        duration_ms: p.durationMs, codec: p.codec, container: p.container, sample_rate: p.sampleRate, bit_depth: p.bitDepth, channels: p.channels, bitrate: p.bitrate, web_playable: playable, compat_state: compat, compat_error: null };199      let tid: string;200      if (replacing) {201        // Same track id: playlists, favourites and queues keep pointing at it.202        const old = await tx<{ storage_key: string; kind: string }[]>`SELECT storage_key, kind FROM media_assets WHERE track_id = ${replacing} AND kind IN ('original', 'rendition')`;203        for (const o of old) if (o.storage_key !== key) oldKeys.push(o.storage_key);204        await tx`DELETE FROM media_assets WHERE track_id = ${replacing} AND kind IN ('original', 'rendition')`;205        await tx`UPDATE tracks SET ${tx(cols)}, updated_at = now() WHERE id = ${replacing}`;206        tid = replacing;207      } else {208        const [t] = await tx<{ id: string }[]>`INSERT INTO tracks ${tx(cols)} RETURNING id`;209        tid = t.id;210      }211      await tx`INSERT INTO media_assets (kind, storage_key, sha256, size_bytes, mime, track_id) VALUES ('original', ${key}, ${sha}, ${size}, ${mime}, ${tid})`;212      await tx`UPDATE uploads SET status = 'ready', track_id = ${tid}, error_code = NULL, error_detail = NULL, updated_at = now() WHERE id = ${id}`;213      return tid;214    }) as string;215    for (const k of oldKeys) await st.delete(k).catch(() => {});216    // New audio → new lyrics (hand-corrected ones are kept unless the file itself was replaced).217    if (env.lyrics === "auto" && env.lyricsPython) await sql`INSERT INTO lyrics (track_id, state) VALUES (${trackId}, 'queued') ON CONFLICT (track_id) DO UPDATE SET state = 'queued', edited = false, updated_at = now() WHERE ${!!replacing}`;218    if (replacing) {219      await audit({ userId: u.user_id, actor: "admin", action: "track.replaced", entity: "track", entityId: trackId, meta: { sha256: sha, reason: same ? "same_file" : "same_track" } });220      await pruneOrphans();221    }222  } catch (e) {223    console.error("[ingest] upload failed", id, e instanceof Error ? `${e.name}: ${e.message}` : String(e));224    await fail(e instanceof MediaError ? e.code : "internal", e instanceof MediaError ? e.message : undefined, true).catch(() => {});225  }226}227228// ── Archives ────────────────────────────────────────────────────────────────────────────────────────229230/**231 * ZIP import. Entries are never written under their own names: each audio entry becomes a child upload stored at232 * a server-generated path, so "../" or absolute paths in the archive are harmless. Bounded against bombs: entry233 * count, per-entry size, total size and compression ratio are checked on the bytes actually produced.234 */235async function expandArchive(u: UploadRow, zipPath: string): Promise<void> {236  const id = u.id;237  const created: string[] = [];238  let total = 0;239  try {240    const zip = await new Promise<yauzl.ZipFile>((res, rej) => yauzl.open(zipPath, { lazyEntries: true, autoClose: true, validateEntrySizes: true, strictFileNames: false }, (e, z) => (e || !z ? rej(e) : res(z))));241    if (zip.entryCount > env.archiveMaxEntries) { zip.close(); throw new MediaError("archive_too_many_entries", `Archives are limited to ${env.archiveMaxEntries} entries`); }242    const zipSize = (await stat(zipPath)).size;243    await mkdir(path.dirname(uploadTmpPath(id)), { recursive: true, mode: 0o700 });244    await new Promise<void>((resolve, reject) => {245      zip.on("error", reject);246      zip.on("end", resolve);247      zip.on("entry", (entry: yauzl.Entry) => {248        const name = String(entry.fileName);249        const isDir = name.endsWith("/");250        const base = safeFilename(name);251        const skip = isDir || name.startsWith("__MACOSX/") || base.startsWith("._") || !/\.(flac|mp3|m4a|aac|wav|aiff?|ogg|opus|dsf|dff)$/i.test(base);252        if (skip) return zip.readEntry();253        if (entry.uncompressedSize > env.maxFileBytes) return reject(new MediaError("archive_entry_too_large", `An entry exceeds the per-file limit`));254        if ((entry.generalPurposeBitFlag & 0x1) !== 0) return reject(new MediaError("archive_encrypted", "Encrypted archives are not accepted"));255        if (created.length >= env.maxFilesPerBatch) return reject(new MediaError("archive_too_many_entries", `Archives are limited to ${env.maxFilesPerBatch} audio files`));256        zip.openReadStream(entry, (err, rs) => {257          if (err || !rs) return reject(err ?? new MediaError("archive_invalid"));258          const childId = randomUUID();259          const dest = uploadTmpPath(childId);260          let n = 0;261          const guard = new Transform({ transform(chunk: Buffer, _enc, cb) {262            n += chunk.length; total += chunk.length;263            if (n > env.maxFileBytes) return cb(new MediaError("archive_entry_too_large"));264            if (total > env.archiveMaxTotalBytes) return cb(new MediaError("archive_too_large", "The archive expands beyond the configured limit"));265            if (total > zipSize * env.archiveMaxRatio && total > 100 * 1024 * 1024) return cb(new MediaError("archive_bomb", "Suspicious compression ratio"));266            cb(null, chunk);267          } });268          pipeline(rs, guard, createWriteStream(dest, { mode: 0o600 }))269            .then(async () => {270              await sql`INSERT INTO uploads (id, user_id, parent_id, filename, size_bytes, received_bytes, status) VALUES (${childId}, ${u.user_id}, ${id}, ${base}, ${n}, ${n}, 'uploading')`;271              created.push(childId);272              zip.readEntry();273            })274            .catch(async (e) => { await rm(dest, { force: true }); reject(e); });275        });276      });277      zip.readEntry();278    });279    if (created.length === 0) throw new MediaError("archive_no_audio", "The archive holds no supported audio file");280    // Children are released to the queue only once the whole archive passed its checks.281    await sql`UPDATE uploads SET status = 'received', updated_at = now() WHERE id = ANY(${created}::uuid[])`;282    await sql`UPDATE uploads SET status = 'ready', error_detail = ${`${created.length}`}, updated_at = now() WHERE id = ${id}`;283  } catch (e) {284    for (const c of created) await rm(uploadTmpPath(c), { force: true });285    if (created.length) await sql`DELETE FROM uploads WHERE id = ANY(${created}::uuid[])`;286    const code = e instanceof MediaError ? e.code : "archive_invalid";287    await sql`UPDATE uploads SET status = 'error', error_code = ${code}, error_detail = ${e instanceof MediaError ? e.message.slice(0, 300) : null}, updated_at = now() WHERE id = ${id}`;288  } finally { await rm(zipPath, { force: true }); }289}290291// ── Compatibility renditions ────────────────────────────────────────────────────────────────────────292293export async function buildRendition(trackId: string): Promise<void> {294  const [t] = await sql<{ storage_key: string; sample_rate: number | null }[]>`295    SELECT m.storage_key, t.sample_rate FROM tracks t JOIN media_assets m ON m.track_id = t.id AND m.kind = 'original' WHERE t.id = ${trackId}`;296  if (!t) return;297  const st = storage();298  const tmp = `${await scratch()}.m4a`;299  try {300    await transcodeAac(await st.localPath(t.storage_key), tmp, t.sample_rate);301    const size = (await stat(tmp)).size;302    const key = keys.rendition(trackId);303    await st.putFile(key, tmp);304    await sql.begin(async (tx) => {305      await tx`DELETE FROM media_assets WHERE track_id = ${trackId} AND kind = 'rendition'`;306      await tx`INSERT INTO media_assets (kind, storage_key, size_bytes, mime, track_id) VALUES ('rendition', ${key}, ${size}, 'audio/mp4', ${trackId})`;307      await tx`UPDATE tracks SET compat_state = 'ready', compat_error = NULL, updated_at = now() WHERE id = ${trackId}`;308    });309  } catch (e) {310    await sql`UPDATE tracks SET compat_state = 'error', compat_error = ${(e instanceof MediaError ? e.code : "internal")}, updated_at = now() WHERE id = ${trackId}`;311  } finally { await rm(tmp, { force: true }); }312}313314/** Re-reads the technical data (and the cover, if the album has none) of a stored original. */315export async function reanalyseTrack(trackId: string): Promise<void> {316  const [t] = await sql<{ storage_key: string; album_id: string; has_art: boolean }[]>`317    SELECT m.storage_key, t.album_id, (al.artwork_id IS NOT NULL) AS has_art FROM tracks t JOIN media_assets m ON m.track_id = t.id AND m.kind = 'original' JOIN albums al ON al.id = t.album_id WHERE t.id = ${trackId}`;318  if (!t) return;319  const file = await storage().localPath(t.storage_key);320  const p = await probe(file);321  const playable = webPlayable(p);322  const [cur] = await sql<{ compat_state: string }[]>`SELECT compat_state FROM tracks WHERE id = ${trackId}`;323  const compat = cur.compat_state === "ready" ? "ready" : compatStateFor(playable, p.codec);324  await sql`UPDATE tracks SET duration_ms = ${p.durationMs}, codec = ${p.codec}, container = ${p.container}, sample_rate = ${p.sampleRate}, bit_depth = ${p.bitDepth},325    channels = ${p.channels}, bitrate = ${p.bitrate}, web_playable = ${playable}, compat_state = ${compat}, compat_error = NULL, updated_at = now() WHERE id = ${trackId}`;326  if (!t.has_art && p.hasPicture) {327    const art = await coverFromAudio(file);328    if (art) await sql`UPDATE albums SET artwork_id = ${art} WHERE id = ${t.album_id} AND artwork_id IS NULL`;329  }330}331
Files main
Menu

Theme