ProjectDB auto-migrates schema on every open — read-only commands mutate reference datasets in place

[pfmephisto]

Observed today while running agents against the NewOffice reference scan: the original project.rux (a benchmark reference that every workflow treats as read-only) was silently migrated v10→v11→v12 during the day, because ProjectDB migrates on open unconditionally (libs/reusex/src/core/ProjectDB.cpp:268) — including for purely-reading commands (rux info, rux analyze quality, trajectory renders). WAL checkpointing on connection close also rewrites the main file even for SELECT-only sessions.

Consequences:

• Reference/benchmark datasets change hash and mtime under readers, breaking reproducibility ("same input" is no longer the same file).
• A dataset opened once with a newer build is no longer readable by an older build (schema is a one-way door), without the user ever asking for a write.

Proposal (pick one or combine):

1. Read-only open mode (SQLITE_OPEN_READONLY + skip migration) used automatically by read-only commands (info, log, get, analyze, render, export, the Python bindings already claim read-only) — migration then happens only on commands that write.
2. Or: migrate lazily only when a write is attempted, never on open.
3. At minimum: log at warn when an open migrates the schema, so mutation of an input is visible (STANDARDS §5 — today it is silent).

Marker: the NewOffice reference at /home/mephisto/repos/NewOffice/project.rux now reports schema 12 with three migrations applied 2026-09-10 while under read-only workloads only. PRAGMA integrity_check is ok; migrations are additive, so no data loss this time.

[pfmephisto]

Plan validated — implementation in progress

Root cause confirmed: ProjectDB::Impl::runMigrations() was called unconditionally in read-write mode; SQLITE_OPEN_READONLY was not used for read-only commands; no checkWritable() guard existed on write methods.

Implementation (all changes on branch fix/projectdb-readonly-open-365):

libs/reusex/include/core/ProjectDB.hpp

• Added bool is_read_only() const noexcept; accessor

libs/reusex/src/core/ProjectDB.cpp

• Added checkWritable() to Impl — throws std::runtime_error("Cannot write to read-only database: <path>") when readOnly==true
• Added impl_->checkWritable(); as first statement of all 35 write-method call sites
• Added reusex::warn("Migrating project schema from v{} to v{}: {}", ...) in runMigrations() — fires when current >= 0 && current < LATEST_SCHEMA_VERSION (not for fresh DBs)
• Added bool ProjectDB::is_read_only() const noexcept implementation

CLI commands switched to readOnly=true

Commands that never write to the project database:

• analyze/quality.cpp ✓
• analyze/accuracy.cpp ✓
• export/ply.cpp ✓
• export/e57.cpp ✓
• export/colmap.cpp ✓

(Already correct from prior work: info, log, validate, get, render, export/{materialepas,csv,speckle,rhino,semantic_images}, view/*, Python bindings default read_only=True)

New test file: tests/unit/core/test_project_db_readonly.cpp

Five TEST_CASEs:

1. ProjectDbReadOnly_OldSchema_FileUnchanged — opens v9 fixture read-only, asserts file bytes identical before/after (no migration, no WAL checkpoint)
2. ProjectDbReadWrite_OldSchema_Migrates — same fixture read-write, asserts schema_version() > 9
3. ProjectDbReadOnly_IsReadOnly_ReturnsTrue — fresh DB opened read-only, checks accessor
4. ProjectDbReadWrite_IsReadOnly_ReturnsFalse — fresh DB opened read-write, checks accessor
5. ProjectDbReadOnly_WriteApi_Throws — save_point_cloud on read-only handle must throw std::runtime_error

WAL note: SQLite SQLITE_OPEN_READONLY does not run a WAL checkpoint on close; the read-only connection can read the WAL but won't modify it. The byte-comparison test (FileUnchanged) verifies this empirically for fixtures without WAL, and IsReadOnly_ReturnsTrue opens a WAL-mode DB read-only to smoke-test the WAL path.

Building now...

[pfmephisto]

Done — PR #378

All changes landed in one commit on branch fix/projectdb-readonly-open-365.

Test results: 931/931 pass

What was added / changed

File	Change
libs/reusex/include/core/ProjectDB.hpp	Added bool is_read_only() const noexcept;
libs/reusex/src/core/ProjectDB.cpp	Impl::checkWritable() + 35 call sites on write methods; runMigrations() warns before migrating; ProjectDB::is_read_only() impl
apps/rux/src/analyze/quality.cpp	readOnly=true
apps/rux/src/analyze/accuracy.cpp	readOnly=true
apps/rux/src/export/ply.cpp	readOnly=true
apps/rux/src/export/e57.cpp	readOnly=true
apps/rux/src/export/colmap.cpp	readOnly=true
tests/unit/core/test_project_db_readonly.cpp	New: 5 tests
tests/unit/core/test_project_db_gaussian_splat.cpp	Remove delete_gaussian_splat() call on a read-only handle (write calls now correctly throw)

Five new tests

1. ProjectDbReadOnly_OldSchema_FileUnchanged — file bytes identical before/after read-only open of v9 fixture (no WAL flush, no migration)
2. ProjectDbReadWrite_OldSchema_Migrates — same fixture read-write: schema_version() > 9
3. ProjectDbReadOnly_IsReadOnly_ReturnsTrue
4. ProjectDbReadWrite_IsReadOnly_ReturnsFalse
5. ProjectDbReadOnly_WriteApi_Throws — save_point_cloud on read-only handle throws std::runtime_error