Storage, SQLite WAL & Sync Engine — FleshNote Documentation

SQLite Database Schema (WAL Mode) #

Every FleshNote project is a self-contained directory with a single SQLite database at {project_path}/fleshnote.db. The database is initialized with PRAGMA journal_mode = WAL; and PRAGMA foreign_keys = ON;.

Table Name	Primary Key	Core Purpose & Stored Attributes
`project_config`	key (TEXT)	Key/value project metadata, genre presets, feature toggles, and JSON arrays.
`chapters`	id (UUID TEXT)	Chapter title, order, active POV character, target word count, status, world timestamp.
`characters`	id (UUID TEXT)	Full name, aliases (JSON), role, archetype, biological species, birth date, goal, lie.
`locations`	id (UUID TEXT)	Location name, `parent_location_id` (FK), weather state override, description.
`lore_entities`	id (UUID TEXT)	Artifacts, spells, institutions, items with custom category tagging.
`knowledge_states`	id (UUID TEXT)	Epistemic facts: `character_id` knows fact about `source_entity_id`, learned in chapter X.
`twists`	id (UUID TEXT)	Narrative revelations, target payoff chapter, and `danger_phrases` (JSON array).
`secrets`	id (UUID TEXT)	Concealed character information with danger phrase leak guards.
`calendar_config`	id (UUID TEXT)	Custom calendar months, day counts, season offsets, and epoch definitions (JSON).
`change_log`	id (UUID TEXT)	Sync engine mutation log tracking table, record ID, column name, new value, and HLC clock.
`prose_snapshots`	id (UUID TEXT)	Immutable full-text prose checkpoints taken at Pentimento session boundaries.

Database Migration Engine (v1 to v2 UUID) #

Version 1.3.0 modernizes all primary and foreign keys from autoincrementing integers (e.g. 1, 2, 3) to UUID strings (migration_engine.py) to prevent key collision during multi-device merges.

Migration Engine Workflow

1. Create backup: {project_path}/fleshnote.db.v1_backup.sqlite
2. Generate ID Mapping Table: mapping[entity_type][old_int_id] = new_uuid_str
3. Migrate SQLite Tables: Update all PKs and FKs inside an atomic transaction
4. Rewrite Chapter Prose on Disk:
   Scan {project}/md/*.md and transform markers:
   {{char:5|Sophia}} ──▶ {{char:c7b9a45e-86fe-49b8-a73c-3965b210d3f2|Sophia}}
5. Validate Table Integrity & Commit Version 2 Schema

Local Multi-Device Sync Engine (HLC & LWW) #

FleshNote enables serverless, peer-to-peer synchronization across laptops and the companion app (backend/remote_sync_session.py):

1. Hybrid Logical Clock (HLC)

Physical system clocks can drift. FleshNote pairs physical UTC timestamps with a monotonic logical counter:

HLC Clock Format

2026-08-29T21:45:00.120Z_0003_laptopA
│                        │    └─ Client Node Identifier
│                        └────── Monotonic Counter (increments on simultaneous ticks)
└─────────────────────────────── Physical UTC ISO-8601 Timestamp

2. Field-Level Last-Writer-Wins (LWW) for Entities

Unlike naive database copy tools that overwrite entire rows, FleshNote compares mutations column-by-column using the change_log. If author A modifies Sophia's bio on their desktop while author B modifies Sophia's archetype on their mobile device, both edits merge seamlessly without data loss.

3. 3-Way Hash Ancestry Merge for Chapter Prose

Chapter prose changes are compared using common ancestor base hashes:

If only one device modified the chapter since the base snapshot, changes fast-forward automatically.
If both devices modified the same chapter concurrently, the sync engine presents the author with a side-by-side Visual Diff Inspector (SyncDiffView.jsx) allowing per-paragraph cherry-picking before database commit.

Companion LAN & QR Pairing Handshake #

To synchronize with mobile devices over local WiFi:

The desktop IDE spawns an authenticated local HTTP endpoint on the local subnet.
The Desktop renders a secure QR code containing the LAN IP and one-time ephemeral token.
The mobile companion app scans the QR code, performs a cryptographic handshake, and initiates the sync payload exchange.