Establish PocketBase transaction and file consistency guarantees

[nathanhruby]

Question

What guarantees and limitations do current PocketBase record transactions, SQLite concurrency, and file-field operations provide in a single-process deployment, especially under concurrent requests, failed commits, file-operation failures, and process crashes, and what do those facts imply for state-version writes and exclusive lock leases?

[nathanhruby]

Resolved by research artifact on branch , commit .\n\nGist: PocketBase/SQLite provides atomic database transactions and single-writer serialization in one process, but not an atomic transaction spanning SQLite metadata and local/S3 file storage. File-field upload happens before SQL, cleanup is best effort, post-commit deletion is separate, and crashes can leave orphans or stale files. For immutable encrypted state versions, use unique immutable object keys, metadata as the publication authority with an explicit committed status, verification and reconciliation. For exclusive leases, use short conditional transactions with owner tokens, expiry, and fencing generations; never hold the DB transaction across encryption or file/S3 I/O.\n\nNew decisions/prototypes: local vs S3 consistency target; missing-object policy; digest/length verification; linearizability target; lease timing/fencing; SQLite/driver version pinning; failure-injection tests for upload-before-row, commit-before-cleanup, partial upload, delayed object visibility, stale lease holders, concurrent expiry, and WAL recovery. Parent/map issue #405 was not edited.

[nathanhruby]

Correction: the previous comment was posted with shell-stripped inline code, but its substantive resolution text was intact.

Artifact: docs/research/pocketbase-consistency.md
Branch: research/pocketbase-consistency
Commit: fe41b7a

Resolution: PocketBase/SQLite provides atomic database transactions and single-writer serialization in one process, but not an atomic transaction spanning SQLite metadata and local/S3 file storage. File-field upload happens before SQL, cleanup is best effort, post-commit deletion is separate, and crashes can leave orphans or stale files. Immutable encrypted state versions need unique immutable object keys, metadata as the publication authority with an explicit committed status, verification, and reconciliation. Exclusive leases need short conditional transactions with owner tokens, expiry, and fencing generations; DB transactions must not cover encryption or file/S3 I/O.

New decisions/prototypes: local vs S3 consistency target; missing-object policy; digest/length verification; linearizability target; lease timing/fencing; SQLite/driver version pinning; and failure-injection tests for upload-before-row, commit-before-cleanup, partial upload, delayed object visibility, stale lease holders, concurrent expiry, and WAL recovery. Parent/map issue #405 was not edited.