oficina / sheets Public

Download this file

1	# Durable tab version watermark
2
3	Status: design approved for implementation at 9f6abe2 by Fable 5.1 (claude-fable-5-1), after the four amendments requested at 91e9991. Implementation starts at 6ccb609. Amadan discussion 19/task 3. Branch review, exact-head CI, reconciliation with deployed chrome and canary verification remain required before production deployment.
4
5	## Reproduction
6
7	A 500-cell paste returns version 503. Release/reopen the tab handle, clear those 500 cells, and the clear returns 1004. Release/reopen again and write one new cell: version 4. The browser correctly refuses version 4 as older than 1004, so the newly committed edit is absent from the display. The same defect affects a single-cell edit after clearing the last surviving cell.
8
9	buildHandle derives maxVersion only from current cells' InputVersion and CacheVersion. Deleting the newest cell rows destroys that ordering evidence. The in-memory handle counter survives only while some caller retains the handle; SSE subscribers deliberately do not retain it. An event count or timestamp is not a reliable substitute: neither is the previously issued version.
10
11	## Invariant and schema
12
13	For each tab, every committed cell input or persisted calculation stamp is no greater than a durable tab watermark. Opening a handle starts above both that watermark and all surviving input/cache versions. Clearing cells, recovering stale caches, and releasing every handle must never make a future edit's version go backwards.
14
15	Add Tab.ContentVersion as a non-null integer with default zero through an additive generated migration. It is metadata of the same class as the existing cell InputVersion/CacheVersion, not authored content. Existing tabs start at zero and retain all content, names, history, formats, rows and columns. At open, the maximum of watermark and existing cell versions preserves older populated databases without a destructive migration/backfill. Already-empty pre-upgrade databases have no reconstructable historic version; deployment must reconnect/refetch browsers, and all future edits gain the durable invariant.
16
17	SQLite integers are signed 64-bit. Refuse an exhausted/corrupt watermark before allocating a wrapped stamp; do not silently return to zero. No user-facing strings are added; existing fixed storage failure responses remain.
18
19	## Mutation paths
20
21	| Path | Required handling |
22	| --- | --- |
23	| SetReport -> setContent | Advance watermark to at least v in the same first transaction as sealed input and AppendInput |
24	| SetReport -> setBlank | Advance watermark to at least v in the same transaction as deletion, dirty dependent markers and AppendInput |
25	| persistTouched | Advance watermark to at least v in the same transaction as sealed cache updates and AppendRecalc |
26	| recoverStale | Uses persistTouched on the transaction-scoped handle; recovery version is also preserved even if its cells are later cleared |
27	| SetBatch | Every nested update uses staged.db=tx; outer rollback undoes watermark changes too; successful live swap retains staged.nextVersion |
28	| Table edit, XLSX import, restore, undo | Already call Set or SetReport; inherit the same durable handling without parallel logic |
29	| SetFormat, widths/heights, saved views | Existing behavior does not allocate content versions; retain it. This fix does not pretend to solve concurrent ordering of distinct equal-version formatting responses |
30	| EnsureRow/EnsureCol | Allocate stable axes only, not a content change; no new version |
31	| Row/column insert/delete/move | Not implemented in this repository; future structural operations must allocate and persist a tab stamp in the same mutation transaction. Do not delete a watermark with cell/axis rows |
32	| Tab add/rename/reorder | Future metadata events have separate ordering semantics; do not masquerade as cell history |
33
34	Implement one small transaction-scoped helper for monotonic advancement using an allowlisted update of content_version on Tab by ID. It never lowers a stored value. Both input write paths and persistTouched call it. No outer db calls occur while a transaction holds the one writer connection. Use SET content_version = MAX(content_version, ?) WHERE id = ? so RowsAffected=0 means the tab is missing, not that a lower stamp was safely ignored. A missing tab row must fail rather than silently discarding the watermark.
35
36	Read the tab watermark while building a handle, before allocating nextVersion; a missing tab row fails Open. Preserve the existing deterministic recovery-stamp reservation, even when there are no stale cells, so a snapshot version handed out before release is no greater than the first edit stamp after reopen. Check exhaustion at Open and SetReport allocation (including staged SetReport calls inside SetBatch). Move the in-memory nextVersion increment above the blank crash seam after transaction one commits. A failed input/recalculation transaction must not commit its watermark; the existing dirty-marker behavior after a committed input transaction remains valid. A failed atomic batch must leave both the live handle and persisted watermark unchanged.
37
38	## Regression coverage
39
40	1. Real release/reopen sequence for paste500, clear500, new edit; require strictly increasing report versions and the correct final value. Existing candidate fails 503 ->1004 ->4.
41	2. Single-cell last-cell clear followed by new edit after reopen; require the new stamp to exceed the clear stamp.
42	3. Blank crash seam after the first transaction: release/reopen and require the next edit to exceed the committed blank's allocated stamp.
43	4. Overlapping formula/dependent recomputation, removal of the newest cached cells, and reopen preserve ordering and correct values.
44	5. Existing failure injection into a late SetBatch event must leave Tab.ContentVersion unchanged alongside inputs, history and axes. A subsequent successful edit must remain usable.
45	6. Migrate a database built with BootSchema through the pinned pre-watermark migration IDs (Sheets 0001 through 0009 plus framework schema); seed encrypted names/content and history, then apply the new migration. Verify byte-for-byte ciphertext retention, default watermark, schema agreement and monotonic first post-upgrade edits.
46	7. A high watermark in an otherwise empty tab survives Open; the next write uses a higher stamp. A lower attempted advance cannot reduce it. Exhaustion refuses without a write.
47	8. Snapshot Version() before release/restart must be no greater than the first edit stamp after reopening. Test both populated and cleared tabs.
48	9. A capped blank with more than MaxDirtyPerPass dependents must advance the watermark in transaction one alone. Mutation-test by removing only that advancement; the regression must fail.
49	10. A retained handle starts a batch with nextVersion above the persisted watermark, commits a batch, then rolls back a late-failing batch. After release/reopen the next edit must exceed the successful report, and the failed batch must leave its watermark unchanged.
50	11. Run existing crash/recovery, plain SQLite/WAL leakage and race tests. The watermark contains no plaintext values and adds no logs.
51
52	## Review and deploy
53
54	Keep this prerequisite independently attributable in discussion 19. Fable reviews the design before implementation and the final code before merge. Integrate with atomic batch only after passing full make test, rastrillo migration check, exact-head CI and deployed canary browser regression (paste, clear, new paste, reload, second observer). A migration-only deploy must not erase or replace any user data. Root coordinates production promotion and /api/version proof.
55
56	The current migration head is 0009_alter_formats_ink_intent. Confirm current main has not acquired 0010 before final integration; assign a fresh additive migration ID if it has. The old-database fixture must select known IDs, not filter SQL text, so future migrations mentioning the column cannot be silently omitted.
57