Time-Travel SQL

dbtrail serves time-travel SQL: AS OF queries over the _flashback, _diff, and _snapshot virtual schemas, answered from the change index over the plain MySQL wire protocol. Any MySQL client (mysql, DBeaver, Metabase, an ORM, a BI tool) gets time-travel SQL with zero client integration:

SELECT * FROM _flashback.orders AS OF '2026-04-27 09:00:00' WHERE id = 42;
SELECT * FROM _diff.orders BETWEEN '2026-04-27 09:00' AND '2026-04-27 10:00' WHERE id = 42;
SELECT * FROM _snapshot.orders AT GTID '3E11FA47-71CA-11E1-9E33-C80AA9429562:42' WHERE id = 1;

When to use it

• Forensics over SQL. Investigators write SELECT … FROM _flashback.t AS OF '<ts>' WHERE id = ? instead of learning a new tool.
• Pre-flight recovery. A "what did this row look like 5 minutes ago?" check becomes a one-liner against your existing mysql shell.
• BI and ad-hoc tools. Metabase, DBeaver, JetBrains DataGrip (anything that speaks MySQL) can query historical state without a plugin.

Three ways to run it

1. The console's embedded port (recommended)

If you run the Compose stack, this is one flag away: the console daemon can open an embedded MySQL-protocol port that serves the virtual schemas for every monitored server at once: no extra processes, no per-server setup. You connect with the server's name as the MySQL username and the console token as the password, and the daemon routes the session to that server's index and baseline:

mysql -h <console-host> -P 3310 -u <server-name> -p

Setup (one env var / flag on the daemon): the embedded port.

2. A dedicated terminal

For a single server, bintrail shim is a standalone wire-protocol server you point a mysql client at directly (default 127.0.0.1:3308), the "time-travel terminal" pattern, good for investigations without touching application routing: the dedicated terminal.

3. Behind ProxySQL (transparent for applications)

With ProxySQL routing rules in front, virtual-schema queries route to dbtrail while every other query flows untouched to your production MySQL, so your application's existing connection gains AS OF with no code changes. The full setup (shim.yaml generation, the ProxySQL hostgroups and query rules, systemd units, and the MySQL 8.x auth-plugin compatibility notes) is the repository's step-by-step guide: Time-Travel SQL Setup.

PostgreSQL sources

The same AS OF grammar is available from psql, over the PostgreSQL wire protocol, for PostgreSQL-sourced servers:

SELECT * FROM _flashback.orders AS OF '5 minutes ago' WHERE id = 42;

See querying and recovering PostgreSQL sources.

SQL dialect

¹ id here stands for the table's primary-key column; any single-column PK name works (customer_id, uuid, …); see the callout below.

² Single equality (= <literal>) is the only predicate shape served end-to-end. IN (...) and BETWEEN <a> AND <b> are accepted by the safety check but the row resolver currently handles single equality only.

_flashback and _snapshot require an explicit AS OF clause: there is no implicit "current row state" form. For current rows, query the real table directly. Time literals accept relative forms too, like AS OF '5 minutes ago' or AS OF NOW() - INTERVAL 5 MINUTE.

_diff.<t> returns one row per change event with synthetic metadata columns (_event_id, _event_timestamp, _event_type, _gtid) followed by before_<col> / after_<col> pairs for every column in the table.

Two more shapes are recognized, both SELECT *-only and rewritten internally to _flashback: the hint-comment form SELECT /*+ DBTRAIL_AT='<ts>' */ * FROM t WHERE id = ?, and a bare trailing AS OF on the real table name (SELECT * FROM orders WHERE id = 42 AS OF '<ts>'; the AS OF clause must end the statement).

Omitting the WHERE entirely runs a full-table reconstruction at the AS OF instant, buffered and capped at 100,000 rows. Against _flashback this returns only rows with binlog activity in the retained window; _snapshot becomes baseline-aware when a baseline is configured and then returns the table's complete row state at that instant.

Deleted vs never existed

If the row existed at the requested timestamp, you get its full state back. If it was deleted at or before it, the resultset is empty: the row did not exist at that instant (Oracle AS OF semantics; there is no tombstone marker). To distinguish "deleted" from "never existed", query _diff.<t> BETWEEN <a> AND <b> WHERE id = <id>: a deleted row produces at least one event (including the DELETE with its before_ image, its timestamp, and its GTID); a row that never existed produces none.

Errors you might see

The server emits typed MySQL error codes so ORMs and monitoring can distinguish user-input errors from server faults:

The connection stays open after any of these: your next query on the same connection can succeed if the issue was transient. See Troubleshooting for the diagnosis of each, and the repository troubleshooting section for setup-related failures.

Limitations during beta

• DELETE events return an empty resultset, not a tombstone. See deleted vs never existed above.
• Composite primary keys are not yet supported. Tables whose PK spans more than one column reject up front. Workaround: use _diff.<t> and filter client-side.
• Dropped tables degraded for time-travel. Once a table is fully dropped from the source, PK metadata is no longer available and time-travel queries against it reject with "table has no primary key". _diff queries are unaffected.
• Single equality predicate today: WHERE id = <literal>. IN (...) and BETWEEN are recognized but not yet served.
• Full-table reconstruction is buffered, not streamed. Capped at 100,000 rows per query. Narrow the AS OF range or add a PK filter; point lookups are uncapped.
• No prepared statements. The parser only handles literal queries. Most ORMs that emit ? placeholders will be rejected.
• No mixed JOINs between a virtual schema and a real table. Federated planning is on the roadmap.
• No INSERT/UPDATE/DELETE against virtual tables: always read-only, matching dbtrail's guarantee that it never executes writes against your database.
• No multi-statement queries: one statement per round-trip.
• _diff has no implicit row cap. A hot row can return thousands of events in one response. Narrow the BETWEEN window if that's too much.
• Schema drift. If a column was added after the target timestamp, the reconstructed row will have NULL for that column.

If you hit a limitation that's blocking your use case, open an issue. Beta is the right time for us to hear about it.