Workflow: Migrate All CherryTree Notes to Joplin Server – Part 1

A .ctb notebook is a SQLite database, which makes it easy to read. Open it read-only so you can never damage the source:

import sqlite3
con = sqlite3.connect("file:notes.ctb?mode=ro", uri=True)

The useful tables are node (each page, with its rich text stored as XML), children (the tree), image, codebox and grid (tables). Three surprises:

• node.level is always 0. Rebuild depth from children.
• Images, code blocks and tables live outside the text, anchored by a character offset, and each one takes exactly one offset position.
• A table’s header row is stored last.

Other formats: .ctd is the same model as XML, and .ctx/.ctz are 7-Zip-encrypted versions of each.

The core merges text runs and embedded objects by offset:

objs = sorted(node.objects, key=lambda o: o.offset)
stream, pos, k = [], 0, 0
for text, attrs in node.segments:
    i = 0
    while i < len(text):
        while k < len(objs) and objs[k].offset <= pos:
            stream.append(objs[k]); k += 1; pos += 1   # an object uses one position
        n = len(text) - i
        if k < len(objs):
            n = min(n, objs[k].offset - pos)
        stream.append((text[i:i + n], attrs)); i += n; pos += n
stream.extend(objs[k:])

• Escape < and # unconditionally. A look-ahead rule failed because CherryTree had stored < and hash in two separate formatting runs.
• A run of lines starting with # is pasted config; a single isolated ## Title is usually pasted AI-chat Markdown, so restore that one as a heading.
• Escape _ and * only at word boundaries, so __init__ stays literal and snake_case stays readable.
• Indented plain lines get non-breaking spaces; four normal spaces would turn them into a code block. Cap list nesting at one level deeper than the item above for the same reason.
• Percent-encode ( and ) in link targets: one unbalanced parenthesis in a note title cuts the link short.
• Write YAML front matter (title, created, updated, tags). Joplin’s “Markdown + Front Matter” importer reads all four, so original titles with : or / survive even though file names can’t hold them.

services:
  joplin-db:
    image: postgres:16
    environment: { POSTGRES_USER: joplin, POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}, POSTGRES_DB: joplin }
    volumes: [joplin_db:/var/lib/postgresql/data]
    healthcheck: { test: ["CMD-SHELL", "pg_isready -U joplin -d joplin"], interval: 10s }
  joplin:
    image: joplin/server:3.7.2          # pin the version; upgrade on purpose
    container_name: joplin               # tsdproxy uses this as the tailnet hostname
    depends_on: { joplin-db: { condition: service_healthy } }
    ports: ["22300:22300"]
    environment:
      APP_PORT: 22300
      APP_BASE_URL: https://joplin.<your-tailnet>.ts.net
      DB_CLIENT: pg
      POSTGRES_HOST: joplin-db
      POSTGRES_DATABASE: joplin
      POSTGRES_USER: joplin
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    labels: { tsdproxy.enable: "true", tsdproxy.container_port: "22300" }
volumes: { joplin_db: {} }

• Generate the database password on the server (openssl rand -hex 24) into a mode-600 .env, so it never passes through a chat or your shell history.
• tsdproxy gives each labelled container its own tailnet name with HTTPS. Without an auth key, every new node logs a one-time login.tailscale.com/a/… approval link. Right after approval, HTTPS fails with unexpected eof for about a minute while the certificate is issued.
• For a local health check, send the expected host: curl -H "Host: joplin.<tailnet>.ts.net" -H "X-Forwarded-Proto: https" http://localhost:22300/api/ping. Otherwise you get “Invalid origin”.
• Unsent emails sit in the emails table, confirmation links included.

• joplin import <dir> imports into the currently selected notebook, not a new one named after the folder, so my later imports ended up nested inside the first. Create the notebook (mkbook, with -p <parent-id> for nesting), then pass its ID as the import target; names like “Section 1- Introduction” repeat.
• Make the import script refuse to run if a target notebook already exists, so a second import can’t duplicate everything. joplin ls / also lists nested notebooks; query the profile’s database.sqlite for top-level ones instead.
• A locked-down npm (install scripts blocked) won’t build the native modules: npm install -g joplin --ignore-scripts, then npm approve-scripts sqlite3 sharp && npm rebuild sqlite3 sharp. keytar isn’t needed for the CLI.
• A brand-new CLI profile creates a “Welcome!” notebook on first run, and it syncs to everyone. Delete it.

• CherryTree: a free desktop notes app that keeps each notebook as a tree of pages.
• Joplin: a free notes app for computers, phones and tablets that can sync between them.
• Markdown: a simple way to format plain text that most notes apps understand.
• Sync server: the computer holding the master copy of your notes.
• Self-hosting: running a service on your own machine instead of paying a company.
• Docker: runs server software in self-contained boxes.
• Tailscale: a private network between your own devices.
• Git: records every change to files, so you can see or undo history.
• Pilot: a small trial run before the real move.
• Restore drill: rebuilding from a backup on purpose, to prove the backup works.