# Restore a table or the database

> Bring a table back to an old version, or every table back to a time or to the moment before an operation.

`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

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

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

Answer:

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

**`restore_table`**

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

Answer:

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

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

Answer:

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

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

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

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

**`describe_history`**

```json
{ "limit": 3 }
```

Answer (62 lines)

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

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

Answer:

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

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

Answer:

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

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

Answer:

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

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

**`restore_database`**

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

Answer:

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

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

Answer:

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