Using a CRDT for collaboration

1

Q for community-at-large: Is it possible to build Automerge CRDT as a syncing/collaboration plugin for tropy or does it need to change the way tropy stores data?
[ Automerge is a Conflict-Free Replicated Data Type (CRDT), which allows concurrent changes on different devices to be merged automatically without requiring any central server. The conflict resolution approach is described in the documentation]

1 Like
2

That’s something we’d definitely be interested in. Most of the data lives in a SQLite database. I know there are one or two projects which aim to transparently add CRDT syncing to SQLite so ideally that’s how I would go about it. The question is whether or not the the project schema is compatible with this.

3

we are using Tropy with a research team, database file shared in a Google drive folder. We have a lot of access conflict issues/errors, and would LOVE a solution! So this is a supportive comment! :slight_smile:

4

I gave it a try with opus 4.6, not sure how useful this is. this is looking at

  Summary: Change Effort Matrix
  ┌─────────────────────────┬──────────────────────────┬──────────────────┬────────────────────────────┐
  │         Change          │          Scope           │  Files Affected  │         Difficulty         │
  ├─────────────────────────┼──────────────────────────┼──────────────────┼────────────────────────────┤
  │ Remove WITHOUT ROWID    │ Migration                │ 1 migration file │ Low                        │
  ├─────────────────────────┼──────────────────────────┼──────────────────┼────────────────────────────┤
  │ Extension loading       │ db.js                    │ 1 file, ~5 lines │ Low-Medium (platform risk) │
  ├─────────────────────────┼──────────────────────────┼──────────────────┼────────────────────────────┤
  │ ID collision prevention │ Schema + entire codebase │ 30+ files        │ Very High                  │
  ├─────────────────────────┼──────────────────────────┼──────────────────┼────────────────────────────┤
  │ crsql_finalize()        │ db.js                    │ 1 file, ~2 lines │ Trivial                    │
  ├─────────────────────────┼──────────────────────────┼──────────────────┼────────────────────────────┤
  │ CRR initialization      │ Migration or project.js  │ 1-2 files        │ Low                        │
  ├─────────────────────────┼──────────────────────────┼──────────────────┼────────────────────────────┤
  │ FTS trigger compat      │ Testing only             │ 0 (hopefully)    │ Low                        │
  ├─────────────────────────┼──────────────────────────┼──────────────────┼────────────────────────────┤
  │ Sync transport          │ New code                 │ New file(s)      │ Medium                     │
  └─────────────────────────┴──────────────────────────┴──────────────────┴────────────────────────────┘
  Bottom Line

  The minimal changes to Tropy source are:
  1. One migration to drop WITHOUT ROWID (~30 lines of SQL)
  2. ~10 lines in db.js to load/finalize the extension on each connection
  3. One migration to call crsql_as_crr() on each table

  But the real blocker is the primary key collision problem. Without switching from auto-increment integers to
  globally-unique IDs, two peers creating items independently will produce corrupt cross-references. That change
  touches the subjects table (the base for items/photos/selections), all foreign keys, every model query, and likely
  the Redux state shape