# Table history and time travel

> Turn history on, list a table’s versions, read a table as it was, compare two versions and find hand edits.

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`](https://datanotes.dev/guides/history/vacuum/), and turn history off with `history: false` for a table that doesn’t need it.

## 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`**

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

Answer:

```json
{
  "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`**

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

**`insert_row`**

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

**`update_row`**

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

**`update_row`**

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

**`delete_rows`**

```json
{ "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

`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`**

```json
{ "table": "film" }
```

Answer (110 lines)

```json
{
  "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

`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`**

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

Answer:

```json
{
  "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

`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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

## 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`**

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

Answer:

```json
{
  "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

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:

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

**`describe_history`**

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

Answer:

```json
{
  "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

`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`**

```json
{ "history": true }
```

Answer:

```json
{
  "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`**

```json
{
  "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`**

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

The new table has versions from its creation:

**`describe_history`**

```json
{ "table": "actor" }
```

Answer:

```json
[{ "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`**

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

Answer:

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

**`alter_table`**

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

Answer:

```json
{
  "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`

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:

```yaml
# 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
```