SPB Git

Repository Public

assets.ts 11.6 KB 168 lines typescript

1/**2 * Uploaded files (images, SVG, PDF, text, CSV, JSON) and generated exports.3 * Accepted types are decided from the bytes; SVG is sanitised before it is stored; everything is size-limited,4 * counted against a quota and — when a clamd socket is configured — scanned before it is kept.5 */6import { createHash, randomUUID } from "node:crypto";7import net from "node:net";8import { j, sql } from "@/lib/db";9import { env } from "@/lib/env";10import { ApiError } from "@/lib/http";11import { audit } from "@/lib/audit";12import { sanitizeSvg, svgTreeToMarkup } from "@/renderer/sanitize-svg";13import { parseCsv, sniff } from "./sniff";14import { storage, storageKey } from "./storage";1516export type AssetKind = "image" | "svg" | "pdf" | "text" | "csv" | "json" | "export";17export interface AssetRow { id: string; kind: AssetKind; filename: string; mime: string; bytes: string; sha256: string; storage_key: string; width: number | null; height: number | null; alt: string; credit: string; license: string; scan_status: "clean" | "unscanned"; is_public: boolean; created_at: Date }1819const view = (a: AssetRow) => ({ id: a.id, kind: a.kind, filename: a.filename, mime: a.mime, bytes: Number(a.bytes), width: a.width, height: a.height, alt: a.alt, credit: a.credit, license: a.license, scanStatus: a.scan_status, isPublic: a.is_public, createdAt: a.created_at.toISOString() });20export type AssetView = ReturnType<typeof view>;2122/** clamd INSTREAM. Resolves "clean", or the signature name when the stream is infected. */23export function clamScan(data: Buffer, target: string): Promise<"clean" | { infected: string }> {24  return new Promise((resolve, reject) => {25    const sock = target.includes("/") ? net.createConnection(target) : net.createConnection({ host: target.split(":")[0], port: Number(target.split(":")[1] ?? 3310) });26    let reply = "";27    sock.setTimeout(30_000, () => { sock.destroy(); reject(new Error("clamd timeout")); });28    sock.on("error", reject);29    sock.on("data", (d) => { reply += d.toString(); });30    sock.on("close", () => {31      const r = reply.replace(/\0/g, "").trim();32      if (r.endsWith("OK")) resolve("clean");33      else if (r.endsWith("FOUND")) resolve({ infected: r.replace(/^stream:\s*/, "").replace(/\s*FOUND$/, "") });34      else reject(new Error("clamd: " + (r || "no reply")));35    });36    sock.on("connect", () => {37      sock.write("zINSTREAM\0");38      for (let i = 0; i < data.length; i += 65536) { const chunk = data.subarray(i, i + 65536); const len = Buffer.alloc(4); len.writeUInt32BE(chunk.length); sock.write(len); sock.write(chunk); }39      sock.write(Buffer.alloc(4)); // zero-length chunk ends the stream40    });41  });42}4344export async function scannerStatus(): Promise<{ configured: boolean; ok: boolean; detail: string }> {45  if (!env.clamd) return { configured: false, ok: false, detail: "CLAMD_SOCKET is not set: uploads are stored unscanned" };46  try { await clamScan(Buffer.from("probe"), env.clamd); return { configured: true, ok: true, detail: "clamd" }; }47  catch (e) { return { configured: true, ok: false, detail: (e as Error).message }; }48}4950export async function createAsset(input: { filename: string; data: Buffer; userId: string | null; alt?: string; credit?: string; license?: string; ip?: string | null }): Promise<AssetView> {51  let data = input.data;52  if (data.length === 0) throw new ApiError(422, "empty_file", "The file is empty");53  if (data.length > env.maxUploadBytes) throw new ApiError(413, "file_too_large", `Files are limited to ${Math.round(env.maxUploadBytes / 1048576)} MB`);54  const filename = input.filename.replace(/[\\/\0-\x1f]/g, "_").slice(-120) || "file";55  const type = sniff(data, filename);56  if (!type) throw new ApiError(415, "unsupported_type", "Accepted files: PNG, JPEG, WebP, GIF, SVG, PDF, CSV, JSON and plain text");5758  const [{ used }] = await sql<{ used: string }[]>`SELECT coalesce(sum(bytes), 0) AS used FROM assets WHERE deleted_at IS NULL AND kind <> 'export'`;59  if (Number(used) + data.length > env.storageQuotaBytes) throw new ApiError(413, "quota_exceeded", "The storage quota is reached");6061  let scan: "clean" | "unscanned" = "unscanned";62  if (env.clamd) {63    let verdict;64    try { verdict = await clamScan(data, env.clamd); } catch { throw new ApiError(503, "scanner_unavailable", "The antivirus scanner is not answering; the file was not stored"); }65    if (verdict !== "clean") {66      await audit({ userId: input.userId, actor: "admin", action: "asset.rejected.infected", meta: { filename, signature: verdict.infected }, ip: input.ip });67      throw new ApiError(422, "infected", "The file was rejected by the antivirus scanner");68    }69    scan = "clean";70  }7172  let width: number | null = null, height: number | null = null;73  let dataset: ReturnType<typeof parseCsv> | null = null;74  if (type.kind === "image") {75    ({ width, height } = type);76    if ((width ?? 0) > 12000 || (height ?? 0) > 12000) throw new ApiError(422, "image_too_large", "Images are limited to 12 000 px a side");77  } else if (type.kind === "svg") {78    // Stored sanitised: scripts, event handlers, external references and foreign content are gone for good.79    const clean = sanitizeSvg(data.toString("utf8"));80    if (!clean.ok) throw new ApiError(422, "bad_svg", `The SVG could not be accepted: ${clean.error}`);81    data = Buffer.from(svgTreeToMarkup(clean.tree), "utf8");82    const vb = clean.tree.attrs.viewBox?.split(/[\s,]+/).map(Number);83    if (vb?.length === 4 && vb.every(Number.isFinite)) { width = Math.round(vb[2]); height = Math.round(vb[3]); }84  } else if (type.kind === "csv") {85    try { dataset = parseCsv(data.toString("utf8")); } catch (e) { throw new ApiError(422, "bad_csv", (e as Error).message); }86  } else if (type.kind === "json") {87    // An array of flat objects is also usable as a dataset.88    const v = JSON.parse(data.toString("utf8")) as unknown;89    if (Array.isArray(v) && v.length > 0 && v.every((r) => r && typeof r === "object" && !Array.isArray(r))) {90      const keys = [...new Set(v.slice(0, 200).flatMap((r) => Object.keys(r as object)))].slice(0, 60);91      const rows = v.slice(0, 5000).map((r) => keys.map((k) => { const x = (r as Record<string, unknown>)[k]; return typeof x === "number" && Number.isFinite(x) ? x : x == null ? "" : String(x).slice(0, 400); }));92      dataset = { columns: keys.map((name, i) => ({ name: name.slice(0, 80), type: rows.every((r) => typeof r[i] === "number" || r[i] === "") && rows.some((r) => typeof r[i] === "number") ? "number" : "string" })), rows, truncated: v.length > 5000 };93    }94  }9596  const id = randomUUID();97  const key = storageKey(id);98  await storage().put(key, data);99  try {100    const row = await sql.begin(async (tx) => {101      const [a] = await tx<AssetRow[]>`102        INSERT INTO assets (id, kind, filename, mime, bytes, sha256, storage_key, width, height, alt, credit, license, scan_status, created_by)103        VALUES (${id}, ${type.kind}, ${filename}, ${type.mime}, ${data.length}, ${createHash("sha256").update(data).digest("hex")}, ${key}, ${width}, ${height}, ${(input.alt ?? "").slice(0, 400)}, ${(input.credit ?? "").slice(0, 300)}, ${(input.license ?? "").slice(0, 120)}, ${scan}, ${input.userId})104        RETURNING *`;105      if (dataset) await tx`INSERT INTO datasets (asset_id, name, columns, row_count, sample) VALUES (${id}, ${filename.replace(/\.[^.]+$/, "")}, ${j(dataset.columns)}, ${dataset.rows.length}, ${j(dataset.rows.slice(0, 20))})`;106      await audit({ userId: input.userId, actor: "admin", action: "asset.uploaded", entity: "asset", entityId: id, meta: { kind: type.kind, bytes: data.length, scan }, ip: input.ip }, tx);107      return a;108    });109    return view(row);110  } catch (e) { await storage().remove(key); throw e; }111}112113/** Stores a file produced by the worker (PDF, PNG, SVG export). */114export async function createExportAsset(input: { filename: string; mime: string; data: Buffer; isPublic: boolean }): Promise<string> {115  const id = randomUUID(); const key = storageKey(id);116  await storage().put(key, input.data);117  await sql`INSERT INTO assets (id, kind, filename, mime, bytes, sha256, storage_key, scan_status, is_public)118            VALUES (${id}, 'export', ${input.filename}, ${input.mime}, ${input.data.length}, ${createHash("sha256").update(input.data).digest("hex")}, ${key}, 'clean', ${input.isPublic})`;119  return id;120}121122export async function listAssets(q: { kind?: AssetKind; limit: number; offset: number }) {123  const rows = await sql<(AssetRow & { total: number; dataset: { columns: unknown; row_count: number } | null })[]>`124    SELECT a.*, count(*) OVER ()::int AS total,125      (SELECT json_build_object('columns', d.columns, 'row_count', d.row_count) FROM datasets d WHERE d.asset_id = a.id) AS dataset126    FROM assets a WHERE a.deleted_at IS NULL AND a.kind <> 'export' AND (${q.kind ?? null}::text IS NULL OR a.kind = ${q.kind ?? null})127    ORDER BY a.created_at DESC LIMIT ${q.limit} OFFSET ${q.offset}`;128  const [{ used }] = await sql<{ used: string }[]>`SELECT coalesce(sum(bytes), 0) AS used FROM assets WHERE deleted_at IS NULL`;129  return { total: rows[0]?.total ?? 0, usedBytes: Number(used), quotaBytes: env.storageQuotaBytes, items: rows.map((r) => ({ ...view(r), dataset: r.dataset })) };130}131132export async function getAssetRow(id: string): Promise<AssetRow | null> {133  if (!/^[0-9a-f-]{36}$/.test(id)) return null;134  const [a] = await sql<AssetRow[]>`SELECT * FROM assets WHERE id = ${id} AND deleted_at IS NULL`;135  return a ?? null;136}137138export async function readAsset(a: AssetRow): Promise<Buffer> { return storage().get(a.storage_key); }139140export async function updateAsset(id: string, patch: { alt?: string; credit?: string; license?: string }): Promise<AssetView> {141  const [a] = await sql<AssetRow[]>`UPDATE assets SET alt = coalesce(${patch.alt ?? null}, alt), credit = coalesce(${patch.credit ?? null}, credit), license = coalesce(${patch.license ?? null}, license) WHERE id = ${id} AND deleted_at IS NULL RETURNING *`;142  if (!a) throw new ApiError(404, "not_found", "Asset not found");143  return view(a);144}145146export async function deleteAsset(id: string, userId: string): Promise<void> {147  const a = await getAssetRow(id);148  if (!a) throw new ApiError(404, "not_found", "Asset not found");149  const [used] = await sql<{ title: string }[]>`SELECT title FROM sheets WHERE deleted_at IS NULL AND (doc::text LIKE ${"%" + id + "%"} OR og_asset_id = ${id}) LIMIT 1`;150  if (used) throw new ApiError(409, "asset_in_use", `This file is used by the sheet “${used.title}”`);151  const [pub] = await sql`SELECT 1 FROM sheet_publications p JOIN sheets s ON s.publication_id = p.id WHERE p.doc::text LIKE ${"%" + id + "%"} LIMIT 1`;152  if (pub) throw new ApiError(409, "asset_in_use", "This file is used by a published sheet");153  await sql`UPDATE assets SET deleted_at = now() WHERE id = ${id}`;154  await storage().remove(a.storage_key);155  await audit({ userId, actor: "admin", action: "asset.deleted", entity: "asset", entityId: id });156}157158export async function datasetRows(assetId: string) {159  const a = await getAssetRow(assetId);160  if (!a || (a.kind !== "csv" && a.kind !== "json")) throw new ApiError(404, "not_found", "Dataset not found");161  const [d] = await sql<{ name: string; columns: { name: string; type: "number" | "string" }[]; row_count: number }[]>`SELECT name, columns, row_count FROM datasets WHERE asset_id = ${assetId}`;162  if (!d) throw new ApiError(422, "not_tabular", "This file is not a table");163  const text = (await readAsset(a)).toString("utf8");164  if (a.kind === "csv") { const p = parseCsv(text); return { name: d.name, columns: p.columns, rows: p.rows }; }165  const v = JSON.parse(text) as Record<string, unknown>[];166  return { name: d.name, columns: d.columns, rows: v.slice(0, 5000).map((r) => d.columns.map((c) => { const x = r[c.name]; return typeof x === "number" ? x : x == null ? "" : String(x); })) };167}168
Files main
Menu

Theme