Skip to content

AI-native knowledge base & Evernote/Obsidian alternative with instant sync & native MCP. Free on Cloudflare or self-hosted via Docker.

11.9 KB
Raw Blame
import type { MemoDetail } from "@edgeever/shared";
import type { Hono } from "hono";
import type { AppEnv } from "./api-context";
import { inboxNotebookIdentitySql, mapNotebook, type NotebookRow } from "./notebook-service";
import { getWorkspaceId, requireScopes } from "./request-auth";

type MobileSyncChangeRow = {
  id: number | null;
  entity_type: "notebook" | "memo" | null;
  entity_id: string | null;
  operation: "upsert" | "delete" | null;
  server_cursor: number;
  sync_identity: string;
};

export type SyncMemoDetailRow = {
  id: string;
  notebook_id: string;
  title: string | null;
  excerpt: string;
  tags_json: string;
  is_pinned: number;
  is_archived: number;
  is_deleted: number;
  created_at: string;
  updated_at: string;
  deleted_at: string | null;
  revision: number;
  content_json: string;
  content_markdown: string;
  content_text: string;
  source_memo_ids: string;
  merge_source_count: number;
  merged_into_memo_id: string | null;
  content_hash: string;
};

type SyncRouteDependencies = {
  clampNumber: (value: number, min: number, max: number) => number;
  mapMemoDetail: (row: SyncMemoDetailRow) => MemoDetail;
};

// Cloudflare D1 accepts at most 100 bound parameters per query. Each detail
// query also binds the workspace id, so keep entity batches comfortably below
// that ceiling. A large import can place 200 distinct entities in one change
// page; querying all of them in one IN clause would make sync return HTTP 500.
const SYNC_DETAIL_ID_BATCH_SIZE = 90;

// A deleted memo can outlive its original notebook: users may move every active
// memo to trash and then delete the now-empty notebook. Local mirrors only cache
// active notebooks, so expose the same destination used by restoreMemosRecord
// instead of returning a dangling notebook id.
const syncMemoNotebookIdSql = `CASE
  WHEN EXISTS (
    SELECT 1 FROM notebooks source_notebook
    WHERE source_notebook.id = m.notebook_id
      AND source_notebook.workspace_id = m.workspace_id
      AND source_notebook.is_deleted = 0
  ) THEN m.notebook_id
  ELSE COALESCE((
    SELECT inbox.id FROM notebooks inbox
    WHERE inbox.workspace_id = m.workspace_id
      AND inbox.is_deleted = 0
      AND ${inboxNotebookIdentitySql("inbox")}
    ORDER BY CASE
      WHEN inbox.id = inbox.workspace_id || '_inbox' THEN 0
      WHEN inbox.id = 'nb_inbox' THEN 1
      ELSE 2
    END
    LIMIT 1
  ), m.notebook_id)
END`;

// Keep active notebook snapshots self-contained even if historical data has an
// active child whose parent was deleted. The server remains authoritative; the
// mirror simply presents that orphan as a root notebook.
const syncNotebookParentIdSql = `CASE
  WHEN n.parent_id IS NULL OR EXISTS (
    SELECT 1 FROM notebooks parent_notebook
    WHERE parent_notebook.id = n.parent_id
      AND parent_notebook.workspace_id = n.workspace_id
      AND parent_notebook.is_deleted = 0
  ) THEN n.parent_id
  ELSE NULL
END`;

const splitSyncDetailIds = (ids: string[]) => {
  const batches: string[][] = [];
  for (let index = 0; index < ids.length; index += SYNC_DETAIL_ID_BATCH_SIZE) {
    batches.push(ids.slice(index, index + SYNC_DETAIL_ID_BATCH_SIZE));
  }
  return batches;
};

export const registerSyncRoutes = (
  app: Hono<AppEnv>,
  dependencies: SyncRouteDependencies,
) => {
  app.get("/api/v1/sync/bootstrap", async (context) => {
    const denied = requireScopes(context, "read:notebooks", "read:memos");
    if (denied) return denied;

    const workspaceId = getWorkspaceId(context);
    const limit = dependencies.clampNumber(Number(context.req.query("limit") ?? 100), 1, 200);
    const afterId = context.req.query("afterId")?.trim() ?? "";
    // Initial metadata is needed once; later pages can use the first snapshot.
    // Keep the historical response for clients that do not opt out.
    const includeMetadata = !afterId || context.req.query("includeMetadata") !== "0";
    const [notebookRows, memoRows, totalRow, cursorRow] = await Promise.all([
      includeMetadata ? context.env.storage.db.prepare(
        `SELECT n.id, ${syncNotebookParentIdSql} AS parent_id, n.name, n.slug, n.icon, n.color, n.text_color, n.color_target, n.is_bold, n.icon_inverted, n.row_inverted, n.sort_order,
                n.created_at, n.updated_at, COUNT(m.id) AS memo_count, MAX(m.updated_at) AS last_memo_updated_at
         FROM notebooks n
         LEFT JOIN memos m ON m.notebook_id = n.id AND m.workspace_id = n.workspace_id AND m.is_deleted = 0
         WHERE n.workspace_id = ? AND n.is_deleted = 0
         GROUP BY n.id, n.parent_id, n.name, n.slug, n.icon, n.color, n.text_color, n.color_target, n.is_bold, n.icon_inverted, n.row_inverted, n.sort_order, n.created_at, n.updated_at
         ORDER BY n.sort_order ASC, n.name ASC`,
      ).bind(workspaceId).all<NotebookRow>() : Promise.resolve({ results: [] as NotebookRow[] }),
      context.env.storage.db.prepare(
        `SELECT m.id, ${syncMemoNotebookIdSql} AS notebook_id, m.title, m.excerpt, m.tags_json, m.is_pinned,
                m.is_archived, m.is_deleted, m.created_at, m.updated_at, m.deleted_at, mc.revision,
                mc.content_json, mc.content_markdown, mc.content_text, mc.content_hash,
                m.source_memo_ids, m.merge_source_count, m.merged_into_memo_id
         FROM memos m
         INNER JOIN memo_contents mc ON mc.memo_id = m.id
         WHERE m.workspace_id = ? AND m.id > ?
         ORDER BY m.id ASC
         LIMIT ?`,
      ).bind(workspaceId, afterId, limit + 1).all<SyncMemoDetailRow>(),
      includeMetadata ? context.env.storage.db.prepare(
        `SELECT COUNT(*) AS count FROM memos WHERE workspace_id = ?`,
      ).bind(workspaceId).first<{ count: number }>() : Promise.resolve(null),
      context.env.storage.db.prepare(
        `SELECT w.created_at AS sync_identity,
                COALESCE((
                  SELECT MAX(c.id)
                  FROM mobile_sync_changes c
                  WHERE c.workspace_id = w.id
                ), 0) AS cursor
         FROM workspaces w
         WHERE w.id = ?`,
      ).bind(workspaceId).first<{ cursor: number; sync_identity: string }>(),
    ]);
    const page = memoRows.results.slice(0, limit);
    return context.json({
      notebooks: notebookRows.results.map(mapNotebook),
      memos: page.map(dependencies.mapMemoDetail),
      snapshotCursor: cursorRow?.cursor ?? 0,
      syncIdentity: cursorRow?.sync_identity,
      ...(includeMetadata ? { totalCount: totalRow?.count ?? page.length } : {}),
      nextAfterId: memoRows.results.length > limit ? page.at(-1)?.id ?? null : null,
    });
  });

  app.get("/api/v1/sync/changes", async (context) => {
    const denied = requireScopes(context, "read:notebooks", "read:memos");
    if (denied) return denied;

    const workspaceId = getWorkspaceId(context);
    const cursor = dependencies.clampNumber(
      Number(context.req.query("cursor") ?? 0),
      0,
      Number.MAX_SAFE_INTEGER,
    );
    const limit = dependencies.clampNumber(Number(context.req.query("limit") ?? 100), 1, 200);
    const rows = await context.env.storage.db.prepare(
      `WITH workspace_state AS (
         SELECT w.created_at AS sync_identity,
                COALESCE((
                  SELECT MAX(c.id)
                  FROM mobile_sync_changes c
                  WHERE c.workspace_id = w.id
                ), 0) AS server_cursor
         FROM workspaces w
         WHERE w.id = ?
       ), page_changes AS (
         SELECT id, entity_type, entity_id, operation
         FROM mobile_sync_changes
         WHERE workspace_id = ? AND id > ?
         ORDER BY id ASC
         LIMIT ?
       )
       SELECT c.id, c.entity_type, c.entity_id, c.operation,
              s.server_cursor, s.sync_identity
       FROM workspace_state s
       LEFT JOIN page_changes c ON 1 = 1
       ORDER BY c.id ASC`,
    ).bind(workspaceId, workspaceId, cursor, limit + 1).all<MobileSyncChangeRow>();
    const cursorRow = rows.results[0];
    const rawChanges = rows.results.filter((row): row is MobileSyncChangeRow & {
      id: number;
      entity_type: "notebook" | "memo";
      entity_id: string;
      operation: "upsert" | "delete";
    } => row.id !== null && row.entity_type !== null && row.entity_id !== null && row.operation !== null);
    const page = rawChanges.slice(0, limit);
    const latestPageChanges = new Map<string, (typeof page)[number]>();
    for (const change of page) {
      latestPageChanges.set(`${change.entity_type}:${change.entity_id}`, change);
    }
    const compactedPage = Array.from(latestPageChanges.values()).sort((left, right) => left.id - right.id);
    const memoIds = Array.from(new Set(compactedPage
      .filter((change) => change.entity_type === "memo" && change.operation === "upsert")
      .map((change) => change.entity_id)));
    const notebookIds = Array.from(new Set(compactedPage
      .filter((change) => change.entity_type === "notebook" && change.operation === "upsert")
      .map((change) => change.entity_id)));
    const [memoRowBatches, notebookRowBatches] = await Promise.all([
      Promise.all(splitSyncDetailIds(memoIds).map((batch) => {
        const placeholders = batch.map(() => "?").join(", ");
        return context.env.storage.db.prepare(
          `SELECT m.id, ${syncMemoNotebookIdSql} AS notebook_id, m.title, m.excerpt, m.tags_json, m.is_pinned,
                  m.is_archived, m.is_deleted, m.created_at, m.updated_at, m.deleted_at, mc.revision,
                  mc.content_json, mc.content_markdown, mc.content_text, mc.content_hash,
                  m.source_memo_ids, m.merge_source_count, m.merged_into_memo_id
           FROM memos m
           INNER JOIN memo_contents mc ON mc.memo_id = m.id
           WHERE m.workspace_id = ? AND m.id IN (${placeholders})`,
        ).bind(workspaceId, ...batch).all<SyncMemoDetailRow>();
      })),
      Promise.all(splitSyncDetailIds(notebookIds).map((batch) => {
        const placeholders = batch.map(() => "?").join(", ");
        return context.env.storage.db.prepare(
          `SELECT n.id, ${syncNotebookParentIdSql} AS parent_id, n.name, n.slug, n.icon, n.color, n.text_color, n.color_target, n.is_bold, n.icon_inverted, n.row_inverted, n.sort_order,
                  n.created_at, n.updated_at, COUNT(m.id) AS memo_count, MAX(m.updated_at) AS last_memo_updated_at
           FROM notebooks n
           LEFT JOIN memos m ON m.notebook_id = n.id AND m.workspace_id = n.workspace_id AND m.is_deleted = 0
           WHERE n.workspace_id = ? AND n.is_deleted = 0 AND n.id IN (${placeholders})
         GROUP BY n.id, n.parent_id, n.name, n.slug, n.icon, n.color, n.text_color, n.color_target, n.is_bold, n.icon_inverted, n.row_inverted, n.sort_order, n.created_at, n.updated_at`,
        ).bind(workspaceId, ...batch).all<NotebookRow>();
      })),
    ]);
    const memosById = new Map(memoRowBatches
      .flatMap((batch) => batch.results)
      .map((row) => [row.id, dependencies.mapMemoDetail(row)]));
    const notebooksById = new Map(notebookRowBatches
      .flatMap((batch) => batch.results)
      .map((row) => [row.id, mapNotebook(row)]));
    const changes = compactedPage.map((change) => {
      if (change.entity_type === "memo") {
        const memo = change.operation === "upsert" ? memosById.get(change.entity_id) ?? null : null;
        return {
          cursor: change.id,
          entityType: change.entity_type,
          entityId: change.entity_id,
          operation: memo ? "upsert" as const : "delete" as const,
          notebook: null,
          memo,
        };
      }
      const notebook = change.operation === "upsert"
        ? notebooksById.get(change.entity_id) ?? null
        : null;
      return {
        cursor: change.id,
        entityType: change.entity_type,
        entityId: change.entity_id,
        operation: notebook ? "upsert" as const : "delete" as const,
        notebook,
        memo: null,
      };
    });

    return context.json({
      changes,
      cursor: page.at(-1)?.id ?? cursor,
      hasMore: rows.results.length > limit,
      serverCursor: cursorRow?.server_cursor ?? 0,
      syncIdentity: cursorRow?.sync_identity,
    });
  });
};