AI-native knowledge base & Evernote/Obsidian alternative with instant sync & native MCP. Free on Cloudflare or self-hosted via Docker.
HTML ai-agentcloudflarecloudflare-d1cloudflare-r2cloudflare-workersdockerelectronevernoteevernote-alternativeknowledge-basemcpnote-takingobsidian-alternativeoffline-firstpersonal-knowledge-basepwaself-hostedsqlitetiptapweb-clipper
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,
});
});
};