How OpenChronology was built

#144 · Copy a fork's index in the database instead of rebuilding it

2026-10-14T03:26:03Z CE

• timeline-app
• pull request

Fixes #140. ## Problem Since #138, a fork or duplicate shares its source's saved history, but it still rebuilt its own index in Node: 1. read the source head as a whole document; 2. validate it; 3. build the moment tree in memory; 4. write every index table, batch by batch. At 200,000 moments that took about **110–120 s**, and it held the whole timeline in the server's memory (about 1.3 GB at a million moments, per #135). ## What changes **The index is copied in the database.** A copy is made from its source's current head, so its index is exactly the source's live rows. `copyIndex` (`server/store.mjs`) copies them with one `INSERT … SELECT` per table under the new timeline's ID: - tables: `oc_moments`, `oc_nodes`, `oc_duration_nodes`, `oc_edge_nodes`, `oc_relationships`, `oc_entity_search`; - the timeline row's index columns: root nodes, counts, settings, versions and catalogue text; - node IDs are per timeline, so they copy as they are; - column lists come from the catalog (all but `timeline_id` and generated columns), so future columns are copied automatically. Around the copy: - **Quotas:** the copy is charged its `storage_bytes` before anything is written, as a rebuild is. - **Settings:** the first revision's settings come from the head's saved history (`settingsAt`), so no document is read. - **Statistics:** they're refreshed afterwards for large copies (#143). - **Catalogue text:** if the source's text was still stale, a refresh follows. **A rebuild is still used for:** - comparisons, whose sources must be checked against the new owner; - indexes a startup repair still has to rebuild; - heads not kept as rows. **Search vectors are copied as they are.** `oc_entity_search.document` was a generated column, which PostgreSQL recomputes on every insert: 11 s of a 200k copy went to tokenizing notes again. - It's now a plain column kept by a trigger: computed on insert when not given, and whenever the title or body changes, with the same expression. - The upgrade drops the expression in place (`DROP EXPRESSION IF EXISTS`, PostgreSQL 13+), keeping existing vectors without rewriting them. - Other writers don't supply the vector, so the trigger computes it for them as before. ## Measured (200,000 benchmark moments, `node bench/fork-save.mjs`) | | Before | After | |---|--:|--:| | Forking | 110–120 s | 23–33 s (between runs) | | Whole document in the server's memory | yes | no | What's left is writing about 650,000 rows and their index entries (moments 4 s, tree nodes 3 s, search rows 6 s, duration and arc trees 2–4 s), which grows with the timeline. Getting a fork to a few seconds at this size would need copies to share index rows rather than copy them. That's a much larger change, not attempted here. ## Tests - `test/shared-history-postgres.mjs` now checks every copy in its chain, right after copying: - all six index tables equal their source's row for row (`EXCEPT` both ways); - so do the timeline's index columns. This also proves the copy path ran: the sources have trees reshaped by many incremental saves, which a rebuild wouldn't reproduce. The copies are then edited at random and every revision read back, as before. - `test/versioning-postgres.mjs` duplicates a timeline whose head was saved as a document, which takes the rebuild path; it reads back as the document. Locally, all of these pass: - unit tests (257) and typecheck; - every PostgreSQL suite, including the platform's. `test/sql-oracle.mjs` needs `psql` on the host, which this VM doesn't have; CI runs it.

Open #144 · Copy a fork's index in the database instead of rebuilding it in the timeline