Skip to content

Migrate JSONL data to SQLite

Use this guide when your ZettelForge data directory contains legacy JSONL files and you need to move your notes, knowledge graph, and entity index into the current SQLite store. This path is verified against ZettelForge v2.8.0.

Fresh installations use SQLite and do not need this migration.

Migration tooling is in progress

The v2.8.0 PyPI wheel does not include the migration module named in ZettelForge's startup warning. Run the repository-root scripts/migrate_jsonl_to_sqlite.py file from the v2.8.0 source tree.

Do not use --dry-run as a preview. In v2.8.0, that flag still creates and populates zettelforge.db; it only adds a [DRY RUN] label to the output.

Prerequisites

  • A ZettelForge v2.8.0 source checkout with scripts/migrate_jsonl_to_sqlite.py at its root
  • python3 in an environment that has the ZettelForge dependencies installed
  • Read and write access to the data directory: ~/.amem by default, or the directory selected by AMEM_DATA_DIR or storage.data_dir
  • Enough free space in that directory for both a copy of the JSONL files and the new SQLite database
  • All ZettelForge processes that write to this data directory stopped during the migration

Run every command below from the root of the v2.8.0 source checkout.

Run the migration

Set the data directory, then run the repository script:

DATA_DIR="$HOME/.amem"
PYTHONPATH=src python3 scripts/migrate_jsonl_to_sqlite.py --data-dir "$DATA_DIR"

A successful run ends with Migration complete! and reports separate counts for notes, knowledge-graph nodes, knowledge-graph edges, and entity mappings. It writes zettelforge.db with owner-only 0600 permissions and leaves the original JSONL files in place.

The script also copies any files it finds from this list into $DATA_DIR/backup_pre_sqlite/:

  • notes.jsonl
  • kg_nodes.jsonl
  • kg_edges.jsonl
  • entity_index.json

If one of these files is absent, the script warns and continues with the files that exist.

Verify the migrated data

First, confirm that ZettelForge can read the SQLite store. Replace actor and apt28 with an entity type and value from your data:

AMEM_DATA_DIR="$DATA_DIR" ZETTELFORGE_BACKEND=sqlite PYTHONPATH=src python3 - <<'PY'
from zettelforge import MemoryManager

mm = MemoryManager()
stats = mm.get_stats()
print(stats["total_notes"])
print([note.id for note in mm.recall_entity("actor", "apt28")])
try:
    mm.store.close()
except Exception:
    pass
PY

Then compare direct database counts with the migration summary:

sqlite3 "$DATA_DIR/zettelforge.db" "SELECT COUNT(*) FROM notes;"
sqlite3 "$DATA_DIR/zettelforge.db" "SELECT COUNT(*) FROM kg_edges;"

Roll back and retry

If verification fails, preserve the failed database and rerun the migration from the original JSONL files:

ls "$DATA_DIR/backup_pre_sqlite/"
FAILED_DB="$DATA_DIR/zettelforge.db.failed-$(date -u +%Y%m%dT%H%M%SZ)"
mv "$DATA_DIR/zettelforge.db" "$FAILED_DB"
PYTHONPATH=src python3 scripts/migrate_jsonl_to_sqlite.py --data-dir "$DATA_DIR"

The timestamped filename keeps the failed database available for inspection.

Move the backup after verification

Keep the backup outside the live data directory after you verify the migrated store:

BACKUP_PARENT="$(dirname "$DATA_DIR")"
BACKUP_DIR="$BACKUP_PARENT/zettelforge-jsonl-backup-$(date -u +%Y%m%dT%H%M%SZ)"
mv "$DATA_DIR/backup_pre_sqlite" "$BACKUP_DIR"

Retain both the JSONL source files and this backup until you complete a full release cycle without a migration regression.

Why a retry is stable

The migration is idempotent for the data shapes it handles. Notes use INSERT OR REPLACE, entity mappings use INSERT OR IGNORE, and knowledge-graph nodes and edges use backend upserts. Re-running the verified fixture kept all four table counts unchanged.