Skip to content
Datanotes

Table history and time travel

With history on, every write to a table becomes a numbered version in a transaction log, including edits made by hand. The engine keeps the log in its store, outside the vault.

History is on in a database the engine creates: its metastore.yaml says history: true under config. A database whose metastore.yaml lacks the key, such as one from a version before 3.0.0, has it off. Turn it on for the whole database with configure_database, or per table with alter_table or create_table (history: true).

History costs disk space. Each version keeps the full text of every note it writes, so a note saved ten times is stored about ten times. Trim old versions with vacuum_table, and turn history off with history: false for a table that doesn’t need it.

Turn history on for a table

Section titled “Turn history on for a table”

A table’s own history wins over the database’s setting. With history: true, the engine records every write to the table, even in a database with history off.

alter_table

{ "table": "film", "history": true }

Answer:

{
  "ok": true,
  "op": "alter_table",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.film", "version": 1, "operation": "ALTER TABLE" }],
  "migration_id": "20261012T165236-alter_table-alter-table-main-public-film"
}

insert_row

{ "table": "film", "values": { "title": "Nosferatu" } }

insert_row

{ "table": "film", "values": { "title": "Metropolis" } }

update_row

{
  "table": "film",
  "path": "datanotes/main/public/film/Nosferatu.md",
  "set": { "status": "watched", "rating": 4, "watched_on": "2026-10-03" }
}

update_row

{
  "table": "film",
  "path": "datanotes/main/public/film/Metropolis.md",
  "set": { "status": "watched" }
}

delete_rows

{ "table": "film", "where": { "title": "Metropolis" } }

The calls after alter_table add two films, rate Nosferatu, mark Metropolis watched by mistake and then remove it.

List the versions of a table

Section titled “List the versions of a table”

describe_history lists the versions, newest first, each with its operation and metrics. Each version has the migration_id that undo_migration takes.

describe_history

{ "table": "film" }
Answer (110 lines) 
{
  "table": "main.public.film",
  "history": [
    {
      "version": 6,
      "timestamp": "2026-10-12T16:52:36.298Z",
      "operation": "DELETE",
      "operation_parameters": { "summary": "archive 1 rows of film" },
      "migration_id": "20261012T165236-delete_rows-archive-1-rows-of-film",
      "operation_metrics": {
        "num_added_files": 0,
        "num_removed_files": 1,
        "metadata_changed": false
      },
      "engine_info": "datanotes",
      "user_name": "docs-example",
      "actor": { "session": "history", "harness": "docs-example", "auth": "owner" }
    },
    {
      "version": 5,
      "timestamp": "2026-10-12T16:52:36.293Z",
      "operation": "UPDATE",
      "operation_parameters": { "summary": "update film" },
      "migration_id": "20261012T165236-update_row-update-film-2",
      "operation_metrics": {
        "num_added_files": 1,
        "num_removed_files": 1,
        "metadata_changed": false
      },
      "engine_info": "datanotes",
      "user_name": "docs-example",
      "actor": { "session": "history", "harness": "docs-example", "auth": "owner" }
    },
    {
      "version": 4,
      "timestamp": "2026-10-12T16:52:36.286Z",
      "operation": "UPDATE",
      "operation_parameters": { "summary": "update film" },
      "migration_id": "20261012T165236-update_row-update-film",
      "operation_metrics": {
        "num_added_files": 1,
        "num_removed_files": 1,
        "metadata_changed": false
      },
      "engine_info": "datanotes",
      "user_name": "docs-example",
      "actor": { "session": "history", "harness": "docs-example", "auth": "owner" }
    },
    {
      "version": 3,
      "timestamp": "2026-10-12T16:52:36.280Z",
      "operation": "WRITE",
      "operation_parameters": { "summary": "insert into film" },
      "migration_id": "20261012T165236-insert_row-insert-into-film-2",
      "operation_metrics": {
        "num_added_files": 1,
        "num_removed_files": 0,
        "metadata_changed": false
      },
      "engine_info": "datanotes",
      "user_name": "docs-example",
      "actor": { "session": "history", "harness": "docs-example", "auth": "owner" }
    },
    {
      "version": 2,
      "timestamp": "2026-10-12T16:52:36.274Z",
      "operation": "WRITE",
      "operation_parameters": { "summary": "insert into film" },
      "migration_id": "20261012T165236-insert_row-insert-into-film",
      "operation_metrics": {
        "num_added_files": 1,
        "num_removed_files": 0,
        "metadata_changed": false
      },
      "engine_info": "datanotes",
      "user_name": "docs-example",
      "actor": { "session": "history", "harness": "docs-example", "auth": "owner" }
    },
    {
      "version": 1,
      "timestamp": "2026-10-12T16:52:36.267Z",
      "operation": "ALTER TABLE",
      "operation_parameters": { "summary": "alter table main.public.film" },
      "migration_id": "20261012T165236-alter_table-alter-table-main-public-film",
      "operation_metrics": {
        "num_added_files": 0,
        "num_removed_files": 0,
        "metadata_changed": true
      },
      "engine_info": "datanotes",
      "user_name": "docs-example",
      "actor": { "session": "history", "harness": "docs-example", "auth": "owner" }
    },
    {
      "version": 0,
      "timestamp": "2026-10-12T16:52:36.259Z",
      "operation": "CREATE TABLE",
      "operation_parameters": { "summary": "create table main.public.film" },
      "migration_id": "20261012T165236-create_table-create-table-main-public-film",
      "operation_metrics": {
        "num_added_files": 0,
        "num_removed_files": 0,
        "metadata_changed": true
      },
      "engine_info": "datanotes",
      "user_name": "docs-example",
      "actor": { "session": "history", "harness": "docs-example", "auth": "owner" }
    }
  ]
}

Read a table as it was

Section titled “Read a table as it was”

select_rows with version or timestamp reads the table as it was at that point, a technique called time travel. Metropolis, which you removed since, is still there.

select_rows

{ "table": "film", "columns": ["title", "status", "rating"], "version": 5 }

Answer:

{
  "table": "main.public.film",
  "total": 2,
  "rows": [
    {
      "path": "datanotes/main/public/film/Metropolis.md",
      "title": "Metropolis",
      "status": "watched",
      "rating": null
    },
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "title": "Nosferatu",
      "status": "watched",
      "rating": 4
    }
  ],
  "version": 5,
  "timestamp": "2026-10-12T16:52:36.293Z"
}

Copy a table as it was

Section titled “Copy a table as it was”

clone_table with version, or with timestamp as a date-time, copies the table as it was then into a new table. The copy gets the definition and the rows as independent notes, and the source stays untouched.

clone_table

{ "source": "film", "target": "film_before", "version": 5 }

Answer:

{
  "ok": true,
  "op": "clone_table",
  "files_created": 2,
  "planned_creates": [
    "datanotes/main/public/film_before/Metropolis.md",
    "datanotes/main/public/film_before/Nosferatu.md"
  ],
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.film_before", "version": 0, "operation": "CLONE" }],
  "migration_id": "20261012T165236-clone_table-clone-main-public-film-to-main"
}

select_rows

{ "table": "film_before", "columns": ["title", "status", "rating"] }

Answer:

{
  "table": "main.public.film_before",
  "total": 2,
  "rows": [
    {
      "path": "datanotes/main/public/film_before/Metropolis.md",
      "title": "Metropolis",
      "status": "watched",
      "rating": null
    },
    {
      "path": "datanotes/main/public/film_before/Nosferatu.md",
      "title": "Nosferatu",
      "status": "watched",
      "rating": 4
    }
  ]
}

This call names the same moment by its time, taken from the history read above:

clone_table

{
  "source": "film",
  "target": "film_at",
  "timestamp": "2026-10-12T16:52:36.293Z",
  "copy_rows": false
}

See what changed between two versions

Section titled “See what changed between two versions”

table_changes returns one entry per row change between two versions. It lists inserts, deletes, and updates, each update as a pre-image and a post-image of the row.

table_changes

{ "table": "film", "start_version": 5 }

Answer:

{
  "table": "main.public.film",
  "changes": [
    {
      "id": "01a13107-c934-747a-90f7-3becda86084f",
      "title": "Metropolis",
      "status": "to watch",
      "path": "datanotes/main/public/film/Metropolis.md",
      "_change_type": "update_preimage",
      "_commit_version": 5,
      "_commit_timestamp": "2026-10-12T16:52:36.293Z",
      "_operation": "UPDATE"
    },
    {
      "id": "01a13107-c934-747a-90f7-3becda86084f",
      "title": "Metropolis",
      "status": "watched",
      "path": "datanotes/main/public/film/Metropolis.md",
      "_change_type": "update_postimage",
      "_commit_version": 5,
      "_commit_timestamp": "2026-10-12T16:52:36.293Z",
      "_operation": "UPDATE"
    },
    {
      "id": "01a13107-c934-747a-90f7-3becda86084f",
      "title": "Metropolis",
      "status": "watched",
      "path": "datanotes/main/public/film/Metropolis.md",
      "_change_type": "delete",
      "_commit_version": 6,
      "_commit_timestamp": "2026-10-12T16:52:36.298Z",
      "_operation": "DELETE"
    }
  ]
}

Find hand edits in the history

Section titled “Find hand edits in the history”

When a note changes outside the engine, in an editor, through git or through a sync, the engine records an EXTERNAL_EDIT version a moment later.

You edit datanotes/main/public/film/Nosferatu.md by hand, outside the engine. The note now reads:

---
table: main.public.film
id: 01a13107-c92d-767d-9b60-f4e03575c27e
title: Nosferatu
status: watched
rating: 5
watched_on: 2026-10-03
---

describe_history

{ "table": "film", "limit": 2 }

Answer:

{
  "table": "main.public.film",
  "history": [
    {
      "version": 8,
      "timestamp": "2026-10-12T16:52:39.455Z",
      "operation": "EXTERNAL_EDIT",
      "operation_metrics": {
        "num_added_files": 1,
        "num_removed_files": 1,
        "metadata_changed": false,
        "violations": 0
      },
      "engine_info": "datanotes"
    },
    {
      "version": 7,
      "timestamp": "2026-10-12T16:52:36.354Z",
      "operation": "RESTORE",
      "operation_parameters": { "summary": "restore main.public.film to version 5" },
      "migration_id": "20261012T165236-restore_table-restore-main-public-film-to-ve",
      "operation_metrics": {
        "num_added_files": 1,
        "num_removed_files": 0,
        "metadata_changed": false
      },
      "engine_info": "datanotes",
      "user_name": "docs-example",
      "actor": { "session": "history", "harness": "docs-example", "auth": "owner" }
    }
  ]
}

Turn history on for every table

Section titled “Turn history on for every table”

configure_database with history: true turns history on for every table that doesn’t say otherwise. A database the engine creates has it on already. One whose metastore.yaml lacks the setting, such as one from a version before 3.0.0, has it off. A table’s own history, true or false with alter_table, wins, and "default" makes it follow the database again. The journal doesn’t record settings, so an agent changes it only when you ask.

configure_database

{ "history": true }

Answer:

{
  "default_catalog": "main",
  "default_schema": "public",
  "default_partition": "",
  "default_filename": "YYYY-MM-DD[T]HHmm",
  "archive_folder": "_archive",
  "timezone": "",
  "history": true,
  "autolink": true
}

create_table

{
  "name": "actor",
  "description": "Actors of the films I watch",
  "columns": [
    {
      "name": "name",
      "type": "string",
      "required": true,
      "description": "The actor's name, as credited"
    },
    { "name": "born", "type": "date", "description": "The actor's date of birth" }
  ],
  "filename": "{name}"
}

insert_row

{ "table": "actor", "values": { "name": "Rosalind Russell", "born": "1907-06-04" } }

The new table has versions from its creation:

describe_history

{ "table": "actor" }

Answer:

[{ "version": 1, "operation": "WRITE" }, { "version": 0, "operation": "CREATE TABLE" }]

A table’s own setting wins: false keeps the copy film_before without history, and "default" makes film_at follow the database again:

alter_table

{ "table": "film_before", "history": false }

Answer:

{
  "ok": true,
  "op": "alter_table",
  "planned_texts": ["datanotes/metastore.yaml"],
  "migration_id": "20261012T165244-alter_table-alter-table-main-public-film_b"
}

alter_table

{ "table": "film_at", "history": "default" }

Answer:

{
  "ok": true,
  "op": "alter_table",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "main.public.film_at", "version": 1, "operation": "ALTER TABLE" }
  ],
  "migration_id": "20261012T165244-alter_table-alter-table-main-public-film_a"
}

See the history settings in metastore.yaml

Section titled “See the history settings in metastore.yaml”

History is a setting of the definition. alter_table writes history: true under the table’s options, false writes false, and "default" removes the key. For the whole database, the setting is under config. The copies made on this page are tables of their own.

datanotes/metastore.yaml now reads:

# Metastore (Datanotes): catalogs, schemas, tables, columns, views.
id: 01a13107-c91b-74e3-87fd-f98c5335518d
config:
  history: true


catalogs:
  main:
    schemas:
      public:
        tables:
          film:
            description: Classic films I watched or want to watch
            options:
              filename: "{title}"
              archive: _archive
              history: true
            columns:
              - name: id
                type: string
                unique: true
                identity: uuid
                required: true
                dense: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
              - name: title
                type: string
                required: true
                description: The film's title, as on its poster
                unique: true
              - name: status
                type: enum
                default: to watch
                values:
                  - to watch
                  - watched
                description: Where the film is on my list
                ordered: true
              - name: rating
                type: integer
                min: 1
                max: 5
                description: My rating, from 1 (poor) to 5 (a masterpiece)
              - name: watched_on
                type: date
                description: The day I last watched it
          film_before:
            description: Classic films I watched or want to watch
            options:
              filename: "{title}"
              archive: _archive
              history: false
            columns:
              - name: id
                type: string
                unique: true
                identity: uuid
                required: true
                dense: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
              - name: title
                type: string
                required: true
                description: The film's title, as on its poster
                unique: true
              - name: status
                type: enum
                default: to watch
                values:
                  - to watch
                  - watched
                description: Where the film is on my list
                ordered: true
              - name: rating
                type: integer
                min: 1
                max: 5
                description: My rating, from 1 (poor) to 5 (a masterpiece)
              - name: watched_on
                type: date
                description: The day I last watched it
          film_at:
            description: Classic films I watched or want to watch
            options:
              filename: "{title}"
              archive: _archive
            columns:
              - name: id
                type: string
                unique: true
                identity: uuid
                required: true
                dense: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
              - name: title
                type: string
                required: true
                description: The film's title, as on its poster
                unique: true
              - name: status
                type: enum
                default: to watch
                values:
                  - to watch
                  - watched
                description: Where the film is on my list
                ordered: true
              - name: rating
                type: integer
                min: 1
                max: 5
                description: My rating, from 1 (poor) to 5 (a masterpiece)
              - name: watched_on
                type: date
                description: The day I last watched it
          actor:
            description: Actors of the films I watch
            options:
              filename: "{name}"
              archive: _archive
            columns:
              - name: id
                type: string
                required: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
                unique: true
                identity: uuid
                dense: true
              - name: name
                type: string
                required: true
                description: The actor's name, as credited
              - name: born
                type: date
                description: The actor's date of birth