spb/note-spboucher-ai

1/** Full-fidelity JSON backup of a workspace (every content table). Restored by src/lib/import/restore.ts (merge by id). */2import { eq, inArray } from "drizzle-orm";3import { db, schema } from "@/db";4export const JSON_FORMAT = "note-spboucher-ai/1";5const ALL = <T>(q: Promise<T[]>) => q;6export async function workspaceJson(wsId: string, opts: { versions?: boolean } = {}) {7  const [ws] = await db.select().from(schema.workspaces).where(eq(schema.workspaces.id, wsId)); if (!ws) throw new Error("Workspace not found");8  const pages = await ALL(db.select().from(schema.pages).where(eq(schema.pages.workspaceId, wsId)));9  const pageIds = pages.map((p) => p.id);10  const inPages = <C extends { pageId: any }>(t: any, col: C["pageId"]) => (pageIds.length ? db.select().from(t).where(inArray(col, pageIds)) : Promise.resolve([] as any[]));11  const databases = await db.select().from(schema.databases).where(eq(schema.databases.workspaceId, wsId));12  const dbIds = databases.map((d) => d.id);13  const byDb = (t: any, col: any) => (dbIds.length ? db.select().from(t).where(inArray(col, dbIds)) : Promise.resolve([] as any[]));14  const databaseProperties = await byDb(schema.databaseProperties, schema.databaseProperties.databaseId);15  const databaseRows = await byDb(schema.databaseRows, schema.databaseRows.databaseId);16  const databaseViews = await byDb(schema.databaseViews, schema.databaseViews.databaseId);17  const propIds = (databaseProperties as any[]).map((p) => p.id);18  const relations = propIds.length ? await db.select().from(schema.relationsTable).where(inArray(schema.relationsTable.propertyId, propIds)) : [];19  const out = {20    format: JSON_FORMAT, exportedAt: new Date().toISOString(), app: "Note SPBoucher AI",21    workspace: { id: ws.id, name: ws.name, slug: ws.slug, settings: ws.settings, createdAt: ws.createdAt, updatedAt: ws.updatedAt },22    counts: {} as Record<string, number>,23    pages: pages.map(({ tsv: _t, ...p }: any) => p),24    pageVersions: opts.versions === false ? [] : await inPages(schema.pageVersions, schema.pageVersions.pageId),25    databases, databaseProperties, databaseRows, databaseViews, relations,26    attachments: await db.select().from(schema.attachments).where(eq(schema.attachments.workspaceId, wsId)),27    tags: await db.select().from(schema.tags).where(eq(schema.tags.workspaceId, wsId)),28    pageTags: await inPages(schema.pageTags, schema.pageTags.pageId),29    tasks: await db.select().from(schema.tasks).where(eq(schema.tasks.workspaceId, wsId)),30    links: await inPages(schema.links, schema.links.fromPageId),31    knowledgeEdges: await db.select().from(schema.knowledgeEdges).where(eq(schema.knowledgeEdges.workspaceId, wsId)),32    references: await db.select().from(schema.researchReferences).where(eq(schema.researchReferences.workspaceId, wsId)),33    experiments: await db.select().from(schema.experiments).where(eq(schema.experiments.workspaceId, wsId)),34  };35  for (const [k, v] of Object.entries(out)) if (Array.isArray(v)) out.counts[k] = v.length;36  return out;37}38export type WorkspaceJson = Awaited<ReturnType<typeof workspaceJson>>;39