Recovery

Recovery is dbtrail's row-level point-in-time restore. When a single row (or a handful of rows) is wrong — a deleted customer, a reverted UPDATE, an unintended INSERT — you don't need to restore a whole database. dbtrail indexes every row change, so you can pick any moment in the past and restore that specific row to its state at that moment.

How recovery works

dbtrail indexes the full before/after state of every row change. These before/after row images are captured by the open-source bintrail CLI as it parses MySQL binary log events. When you request recovery, dbtrail uses this data to generate the inverse SQL:

The generated SQL uses primary-key-only WHERE clauses when a schema snapshot is available, falling back to all-columns WHERE otherwise.

Foreign key awareness

Recovery is FK-aware by default. When you recover a deleted row, dbtrail automatically checks whether any parent rows referenced by foreign keys were also deleted — and includes them in the recovery SQL in the correct order.

Why this matters

A naive single-table recovery can fail silently. If you deleted a customer and their orders in the same transaction, re-inserting the orders first would violate the foreign key constraint on orders.customer_id. dbtrail prevents this by:

1. Resolving parent rows — walks up the FK chain to find deleted parents that must be restored first (enabled by default via resolve_fk)
2. Optionally including dependents — walks down the FK chain to include deleted child rows (opt-in via include_dependents)
3. Topologically sorting — uses Kahn's algorithm to order INSERT statements so parents are always restored before children
4. Handling circular and self-referencing FKs — when a cycle is detected (e.g. employees.manager_id → employees.id), the SQL is wrapped with SET FOREIGN_KEY_CHECKS=0 / SET FOREIGN_KEY_CHECKS=1

Resolution is recursive up to 5 levels deep. If your schema has deeper FK chains, the remaining rows are skipped with a warning.

Example

Given this schema:

customers (id, name)
orders (id, customer_id → customers.id, total)
order_items (id, order_id → orders.id, product, qty)

Recovering a deleted customer with include_dependents: true generates:

-- FK-aware recovery generated by bintrail SaaS
-- Recovery steps: 4

BEGIN;

-- Step 1: customers (pk={'id': 42}) — requested recovery target
INSERT INTO `mydb`.`customers` (`id`, `name`) VALUES (42, 'Acme Corp');

-- Step 2: orders (pk={'id': 100}) — dependent of mydb.customers via customer_id
INSERT INTO `mydb`.`orders` (`id`, `customer_id`, `total`) VALUES (100, 42, 250.00);

-- Step 3: orders (pk={'id': 101}) — dependent of mydb.customers via customer_id
INSERT INTO `mydb`.`orders` (`id`, `customer_id`, `total`) VALUES (101, 42, 75.00);

-- Step 4: order_items (pk={'id': 500}) — dependent of mydb.orders via order_id
INSERT INTO `mydb`.`order_items` (`id`, `order_id`, `product`, `qty`) VALUES (500, 100, 'Widget', 3);

COMMIT;

Notice that customers is inserted before orders, and orders before order_items — matching the FK dependency order.

Set resolve_fk: false to get single-table recovery without any FK resolution.

MySQL 8.x CASCADE limitation

Using recovery

Via Claude (recommended)

The fastest way to recover a row is to ask Claude in plain English:

"Generate recovery SQL for the deleted order 12345 in the orders table"

"Undo the UPDATE on user 42 from yesterday around 3 PM"

Claude calls the recover tool and shows you the generated SQL for review. You apply it (or not) — dbtrail never writes to your database itself.

Claude is the recommended interface for recovery because the natural-language flow maps directly to how incidents actually come up ("a customer said their profile got wiped yesterday afternoon"). See Connect Claude to wire it up.

Via the dashboard

The Dashboard → Query page surfaces a Recover button on any row change event. Same output as the API, with a diff view so you can see before/after values inline.

Via the API

If you prefer programmatic access or want to integrate recovery into your own runbooks, the recover endpoint returns the generated SQL, an affected row count, and a list of operations. See the Recover API reference for full request/response details.

Recovery parameters

Best practices

1. Always review the SQL before executing — recovery SQL can have unintended side effects if the data has changed since the original event
2. Use specific filters — narrow down by table, primary key, and time range to avoid generating more statements than needed
3. Test in a staging environment first when recovering large batches
4. Run inside a transaction — the generated SQL is already wrapped in BEGIN/COMMIT, so you can ROLLBACK if something looks wrong

Permissions

Recovery requires the recover:execute permission, available to owners, admins, and operators.