How OpenChronology was built

#134 · Keep saved history as rows instead of whole documents

2026-10-13T19:25:07Z CE

• timeline-app
• pull request

Stage 1 of moving saved history to rows, from the #129 discussion. **This PR is stacked on #133**: it targets that branch, so its diff shows only this change. Once #133 merges, GitHub retargets it to `main`. ## What changes A timeline's saved history is no longer a chain of patches with a full document snapshot every 1,000 saves. Each version of a moment, duration, relationship or the timeline's settings is a row (`oc_history_moments`, `_durations`, `_relationships`, `_settings`). A row is valid from the revision number that saved it (`since`) until the revision that changed or removed it (`until`, null while current). Revision *n* is the rows with `since ≤ n < until` (`server/history.mjs`). **Saves** - An in-place save ends the current versions of what it touched (unless they're unchanged) and inserts their new versions. - Saves that rebuild the index (whole documents, large patches, syncs, merges, forks) compare the whole timeline with its current versions a page of 20,000 entities at a time, so no statement grows with the timeline. They still write only the differences. - A revision is charged the bytes of the versions it wrote (`oc_revisions.stored_bytes`). **Reading** a revision is a query. No revision depends on replaying others, and none is a single `jsonb` value, which removes both the periodic full copies and the 256 MB ceiling for saves. **Upgrading** - `migrate` starts each existing timeline's history at its current revision, with one copy made in the database. That's 14 s for 200k moments and 45 s for 1M on this VM. - Earlier revisions keep their snapshots and patches and read back as before. - A timeline that starts at a save (a new timeline, or a fork) is copied without lookups, because the planner can't yet estimate its new rows. Statistics are refreshed after large copies; without that, the save right after a copy got a bad plan. **Integrity** - A trigger makes versions immutable except for ending once, alongside the existing revision immutability. - Retention and timeline deletion remove the versions that no remaining revision sees, and keep what forks and pull requests refer to (with their ancestry). **Not yet:** pull requests still store whole documents in `oc_proposals` and proposal revisions. They're now refused with HTTP 413 and a reason past 200 MB as JSON, instead of failing in PostgreSQL. That's stage 3. **Log importer:** its storage estimate follows the new charges, and reads `history_since` to include the one-time copy. In the PostgreSQL test it now comes within about 1% of what the server charged (31.46 MB estimated vs 31.06 MB charged). ## Measured (8-core VM, PostgreSQL 17, benchmark timelines) | Moments | Saving 10 moments | Saving 5,000 | History a 5,000 save keeps | Reading a revision | Migration copy | |---|--:|--:|--:|--:|--:| | 200,000 | 0.05 s | 7–9 s | 0.4 MB | 4.6 s | 14 s | | 1,000,000 | 0.1–0.2 s | 21–25 s | 0.4 MB | 21 s | 45 s | Before this change, every ~1,000th save also wrote a full copy: 10 s and 35 MB at 200k, 49 s and 173 MB at 1M. At 1M moments, history accounts for 0.64 s of a 5,000-moment save. About 16 s is one existing statement that walks the moment tree, which gets close to the 30 s statement timeout. That's not from this change, and it's documented in the Timeline size section. ## Tests - `test/incremental-postgres.mjs`: now reads back **every** saved revision of 120 random saves from rows, checks that every revision is kept as rows, and checks that saves are charged a fraction of the timeline. - New `test/history-postgres.mjs` covers: - an old snapshot revision staying readable when the next save starts row history with one charged copy; - later saves being charged one version; - whole-document saves writing only differences; - pruning removing what only pruned revisions saw; - immutability; - deletion keeping what a fork's base needs, and removing everything once nothing refers to it; - the migration path, including that its SQL-built settings match what saves write.…

Open #134 · Keep saved history as rows instead of whole documents in the timeline