Skip to content
Datanotes

Restore a table or the database

restore_table brings a table back to an old version, and restore_database brings every table that keeps a transaction log back to a time, or to the moment before an operation. Each restore is one write: it’s journaled, and undo_migration reverts it.

The same history lets you time travel before you restore: select_rows with timestamp reads a table, a view, a join or a base as it was at that time.

Restore a table to an old version

Section titled “Restore a table to an old version”

restore_table brings the rows and the definition back to a version:

  • it removes the rows added since
  • it gives changed rows their old values
  • it brings removed rows back

The restore doesn’t rewrite history: it’s a new version, and you can undo it. Run it with dry_run first.

The dry run names the version by its time, with a timestamp from the history read above:

restore_table

{ "table": "film", "timestamp": "2026-10-12T16:52:36.293Z", "dry_run": true }

Answer:

{
  "ok": true,
  "op": "restore_table",
  "dry_run": true,
  "planned_creates": ["datanotes/main/public/film/Metropolis.md"]
}

restore_table

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

Answer:

{
  "ok": true,
  "op": "restore_table",
  "files_created": 1,
  "planned_creates": ["datanotes/main/public/film/Metropolis.md"],
  "commits": [{ "table": "main.public.film", "version": 7, "operation": "RESTORE" }],
  "migration_id": "20261012T165236-restore_table-restore-main-public-film-to-ve"
}

select_rows

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

Answer:

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

Time travel and restore the whole database

Section titled “Time travel and restore the whole database”

An agent can change several tables in one call: here one batch adds an actor and rates a film. describe_history without table lists the commits of every table, newest first, each with its table. In that list:

  • the commits of one operation share its migration_id
  • the commits of a batch carry the batch’s id as batch
  • tables names every table the change touched

batch

{
  "summary": "add Cary Grant, rate His Girl Friday",
  "operations": [
    {
      "op": "insert_row",
      "table": "actor",
      "values": { "name": "Cary Grant", "born": "1904-01-18" }
    },
    {
      "op": "update_row",
      "table": "film",
      "path": "datanotes/main/public/film/His Girl Friday.md",
      "set": { "rating": 5, "watched_on": "2026-10-08" }
    }
  ]
}

Answer:

{ "ok": true, "migration_id": "20261012T165244-batch-add-cary-grant-rate-his-girl-f" }

describe_history

{ "limit": 3 }
Answer (62 lines) 
{
  "tables": [
    "main.public.actor",
    "main.public.film",
    "main.public.film_at",
    "main.public.film_before"
  ],
  "history": [
    {
      "table": "main.public.film",
      "version": 11,
      "timestamp": "2026-10-12T16:52:44.962Z",
      "operation": "UPDATE",
      "operation_parameters": { "summary": "update film" },
      "migration_id": "20261012T165244-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" },
      "batch": "20261012T165244-batch-add-cary-grant-rate-his-girl-f",
      "tables": ["main.public.actor", "main.public.film"]
    },
    {
      "table": "main.public.actor",
      "version": 2,
      "timestamp": "2026-10-12T16:52:44.956Z",
      "operation": "WRITE",
      "operation_parameters": { "summary": "insert into actor" },
      "migration_id": "20261012T165244-insert_row-insert-into-actor-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" },
      "batch": "20261012T165244-batch-add-cary-grant-rate-his-girl-f",
      "tables": ["main.public.actor", "main.public.film"]
    },
    {
      "table": "main.public.film_at",
      "version": 1,
      "timestamp": "2026-10-12T16:52:44.946Z",
      "operation": "ALTER TABLE",
      "operation_parameters": { "summary": "alter table main.public.film_at" },
      "migration_id": "20261012T165244-alter_table-alter-table-main-public-film_a",
      "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" }
    }
  ]
}

restore_database brings every table that keeps a log back to a moment, as one operation. before_migration names the moment by an operation, here the batch, and means the state immediately before it. The dry run lists what would change in each table, and shows a table without history, like film_before, as skipped with the reason.

restore_database

{
  "before_migration": "20261012T165244-batch-add-cary-grant-rate-his-girl-f",
  "dry_run": true
}

Answer:

{
  "ok": true,
  "op": "restore_database",
  "dry_run": true,
  "planned_deletes": ["datanotes/main/public/actor/Cary Grant.md"],
  "planned_texts": ["datanotes/main/public/film/His Girl Friday.md"],
  "restored_to": "2026-10-12T16:52:44.955Z",
  "tables": [
    {
      "table": "main.public.actor",
      "action": "restore",
      "version": 1,
      "definition_changed": false,
      "creates": [],
      "texts": [],
      "deletes": ["datanotes/main/public/actor/Cary Grant.md"],
      "archived": []
    },
    {
      "table": "main.public.film",
      "action": "restore",
      "version": 10,
      "definition_changed": false,
      "creates": [],
      "texts": ["datanotes/main/public/film/His Girl Friday.md"],
      "deletes": [],
      "archived": []
    }
  ],
  "unchanged": ["main.public.film_at"],
  "skipped": [
    {
      "table": "main.public.film_before",
      "reason": "history is off for it: it keeps no transaction log to go back with (alter_table history=true, or configure_database history=true, keeps one from then on)"
    }
  ]
}

restore_database

{ "before_migration": "20261012T165244-batch-add-cary-grant-rate-his-girl-f" }

Answer:

{
  "ok": true,
  "migration_id": "20261012T165244-restore_database-restore-database-to-before-202",
  "restored_to": "2026-10-12T16:52:44.955Z",
  "tables": ["main.public.actor: restore", "main.public.film: restore"]
}

select_rows

{ "table": "actor", "columns": ["name"] }

Answer:

{
  "table": "main.public.actor",
  "total": 1,
  "rows": [
    {
      "path": "datanotes/main/public/actor/Rosalind Russell.md",
      "name": "Rosalind Russell"
    }
  ]
}

The restore has one migration_id for every table, so undo_migration puts them all back as they were.

A timestamp names the moment by its time, as it does for a table. restored_to is that time for the restore above: the last millisecond before the batch, so passing it as timestamp names the same moment. A restore to that time would drop a table created after it and archive its rows. It would also bring back a table dropped since.

undo_migration

{ "migration_id": "20261012T165244-restore_database-restore-database-to-before-202" }

restore_database

{ "timestamp": "2026-10-12T16:52:44.955Z", "dry_run": true }

Answer:

{
  "ok": true,
  "dry_run": true,
  "tables": [
    "main.public.actor: restore to version 1",
    "main.public.film: restore to version 10"
  ],
  "skipped": [
    {
      "table": "main.public.film_before",
      "reason": "history is off for it: it keeps no transaction log to go back with (alter_table history=true, or configure_database history=true, keeps one from then on)"
    }
  ]
}

Time travel reads several tables the same way. select_rows with timestamp on a join, a view or base:<name> reads each table as of that time, and versions says which version of each it read.

select_rows

{ "table": "actor", "columns": ["name"], "timestamp": "2026-10-12T16:52:44.955Z" }

Answer:

{
  "table": "main.public.actor",
  "total": 1,
  "rows": [
    {
      "path": "datanotes/main/public/actor/Rosalind Russell.md",
      "name": "Rosalind Russell"
    }
  ],
  "version": 1,
  "timestamp": "2026-10-12T16:52:44.914Z"
}