spb/note-spboucher-ai

1/**2 * JSON backup (format note-spboucher-ai/1) → idempotent merge by id into the target workspace.3 * Rows are upserted (`insert … on conflict (id) do update`), so re-running a restore is safe; nothing is deleted.4 * Attachment *files* are not part of the JSON (they live in data/uploads, covered by scripts/backup.sh); rows whose file is missing are counted.5 */6import fs from "node:fs";7import { getTableColumns, sql as dsql } from "drizzle-orm";8import { db, schema } from "@/db";9import { absPath } from "@/lib/files/storage";10import { enqueue, QUEUES } from "@/lib/jobs";11import { ImportError, type ImportCtx } from "./pages";12import { JSON_FORMAT } from "@/lib/export/json";1314type Row = Record<string, unknown>;15/** db uses `casing: "snake_case"`: columns declared without an explicit name keep their camelCase key in metadata. */16const dbName = (c: any) => String(c.name).replace(/[A-Z]/g, (m) => "_" + m.toLowerCase());17/** Coerce an exported row to driver-ready values using the table's column metadata (dates ← ISO strings, generated columns dropped). */18function coerceRow(table: any, row: Row, wsId: string): Row {19  const cols = getTableColumns(table) as Record<string, any>; const out: Row = {};20  for (const [k, c] of Object.entries(cols)) {21    if (c.generated) continue; if (!(k in row)) continue;22    let v = row[k];23    if (c.dataType === "date") v = v == null ? null : new Date(String(v));24    if (k === "workspaceId") v = wsId;25    out[k] = v;26  }27  return out;28}29async function upsert(table: any, rows: Row[], wsId: string, conflict: any[] = [table.id]) {30  if (!rows.length) return 0; let n = 0;31  const cols = getTableColumns(table) as Record<string, any>;32  for (let i = 0; i < rows.length; i += 200) {33    const part = rows.slice(i, i + 200).map((r) => coerceRow(table, r, wsId));34    const set: Record<string, unknown> = {}; for (const k of Object.keys(cols)) if (!cols[k].generated && !conflict.includes(cols[k]) && part.some((r) => k in r)) set[k] = dsql.raw(`excluded."${dbName(cols[k])}"`);35    if (Object.keys(set).length) await db.insert(table).values(part as any).onConflictDoUpdate({ target: conflict, set }); else await db.insert(table).values(part as any).onConflictDoNothing();36    n += part.length;37  }38  return n;39}40export async function restoreJson(ctx: ImportCtx, data: any) {41  if (!data || typeof data !== "object" || typeof data.format !== "string" || !data.format.startsWith("note-spboucher-ai/")) throw new ImportError("Not a Note SPBoucher AI JSON backup (expected format note-spboucher-ai/1)");42  if (data.format !== JSON_FORMAT) ctx.stats.skipped.push(`format ${data.format} ≠ ${JSON_FORMAT} — best effort`);43  const ws = ctx.workspaceId; const counts: Record<string, number> = {};44  const step = async (name: string, fn: () => Promise<number>) => { counts[name] = await fn(); await ctx.progress?.({ message: `Restored ${name}: ${counts[name]}` }); };45  const arr = (k: string): Row[] => (Array.isArray(data[k]) ? data[k] : []);46  // pages first (parent/database ids are plain uuids, no FK), then everything that references them47  await step("pages", () => upsert(schema.pages, arr("pages"), ws));48  await step("pageVersions", () => upsert(schema.pageVersions, arr("pageVersions"), ws));49  await step("databases", () => upsert(schema.databases, arr("databases"), ws));50  await step("databaseProperties", () => upsert(schema.databaseProperties, arr("databaseProperties"), ws));51  await step("databaseRows", () => upsert(schema.databaseRows, arr("databaseRows"), ws));52  await step("databaseViews", () => upsert(schema.databaseViews, arr("databaseViews"), ws));53  await step("relations", () => upsert(schema.relationsTable, arr("relations"), ws));54  await step("attachments", () => upsert(schema.attachments, arr("attachments"), ws));55  await step("tags", () => upsert(schema.tags, arr("tags"), ws));56  await step("pageTags", () => upsert(schema.pageTags, arr("pageTags"), ws, [schema.pageTags.pageId, schema.pageTags.tagId]));57  await step("tasks", () => upsert(schema.tasks, arr("tasks"), ws));58  await step("links", () => upsert(schema.links, arr("links"), ws));59  await step("knowledgeEdges", () => upsert(schema.knowledgeEdges, arr("knowledgeEdges"), ws));60  await step("references", () => upsert(schema.researchReferences, arr("references"), ws));61  await step("experiments", () => upsert(schema.experiments, arr("experiments"), ws));62  let missingFiles = 0; for (const a of arr("attachments")) { try { if (!fs.existsSync(absPath(String(a.storagePath)))) missingFiles++; } catch { missingFiles++; } }63  if (missingFiles) ctx.stats.skipped.push(`${missingFiles} attachment file(s) missing on disk (restore data/uploads from the encrypted backup)`);64  ctx.stats.pages += counts.pages ?? 0; ctx.stats.databases += counts.databases ?? 0; ctx.stats.rows += counts.databaseRows ?? 0; ctx.stats.attachments += counts.attachments ?? 0; ctx.stats.references += counts.references ?? 0;65  // chunks/embeddings are rebuilt by the worker (content hashes that changed are re-indexed; unchanged pages are skipped)66  await enqueue(QUEUES.reindexAll, { workspaceId: ws });67  return { counts, missingFiles };68}69