Maintaining application state with the DBOS Transact open source durable execution librarybrary (Python and TypeScript) and DBOS Cloud not only makes applications resilient to failure, but also enables database time travel. DBOS automatically logs application state changes, including the history of interactions with your PostgreSQL database (or any PostgreSQL wire protocol-compatible DBMS), and you can query that information, as if you were querying your database at a specific point in time.
In this blog, we show how you can leverage time travel to more easily observe, debug, and audit your applications.
As a running example, we use the DBOS Widget Store demo app, simulating the backend of an online store. The demo app is written in TypeScript, with the open source DBOS Transact library (to enable automatic application durability and state change history), and deployed to DBOS Cloud, which we use to show the time travel features below.
1. Interactive Time Travel Queries
The first use-case for time travel is time travel queries: querying your application database as of a past point in time. For example, you can query what products you had in inventory as of 12 PM yesterday. This is useful for analysis and reporting, allowing you to see how your application data evolves over time.
To run interactive time travel queries on your application database, launch the DBOS time travel proxy and connect to it with a client like psql. By default, you view the database as it is now. For example, in the widget store demo app, you can check what products you have in inventory right now:
widget_store=> select product, inventory from products;
product | inventory
------------------------+-----------
Premium Quality Widget | 6
To query your database as it was in the past, use the special DBOS TS <timestamp> command. All subsequent queries will execute as of the specified timestamp. For example, let's check the inventory at 12 AM PDT (UTC-07:00) on 2024-05-21:
widget_store=> DBOS TS "2024-05-21T00:00:00-07:00";
widget_store=> select product, inventory from products;
product | inventory
------------------------+-----------
Premium Quality Widget | 8
Now, let’s go a day back and query the inventory at 12 AM PDT on 2024-05-20:
widget_store=> DBOS TS "2024-05-20T00:00:00-07:00";
widget_store=> select product, inventory from products;
product | inventory
------------------------+-----------
Premium Quality Widget | 102It looks like we sold a lot of widgets on May 20th! By changing the DBOS TS timestamp, you can easily generate a report showing how inventory changed over the past week or month.
2. Time Travel Debugging
The second use-case for time travel is time travel debugging: replaying any cloud trace locally on your laptop. Time travel debugging is especially helpful for locally reproducing issues that occur on your cloud deployment, such as investigating a tricky race condition that only occurs in production.
To use time travel debugging, open your application with VS Code and install the DBOS VS Code extension. The extension adds a "⏳ Time Travel Debug" CodeLens to every DBOS workflow, transaction, and communicator in your application. You can click the CodeLens button to select and replay a recent execution. For example, we can replay a workflow in the widget store app and single-step through its execution:
Under the hood, the debugger leverages time travel to execute your code against the database state as it was at the time of the original execution. Thus, the debugging experience is similar to debugging a regular TypeScript application in VS Code – you can set breakpoints, inspect variables, and single step through your code. For methods that don’t interact with the database, like communicators, the debugger returns their recorded outputs rather than re-executing them to avoid unexpected side effects like sending an email during a debugging session.
Unlike traditional trace-based debuggers that only support replay, the DBOS time travel debugger allows you to modify your code and execute it as if the new code ran in the past. For example, you can change your application code to add more database read queries or print statements to help debugging.
3. Auditing Database Interactions
The third use-case for time travel is auditing: using the data history collected by DBOS to check who changed what data when. DBOS stores this information in the “provenance database,” accessed under your application database name with a _dbos_prov suffix. The provenance database stores the history of each of your database tables: a record of every change made to that table. It also stores the DBOS system tables, which record each request execution. You'll run auditing queries by directly connecting to the provenance database (without a proxy).
Using the history and system tables, you can construct complex auditing queries analyzing how your application is used. For example, the following query shows you the IP address of every user who has purchased a widget:
SELECT ts.created_at as changed_at, SPLIT_PART(request::json->'headers'->>'x-forwarded-for', ',', 1) as src_ip, pd.product, pd.inventory
FROM dbos.transaction_outputs as ts JOIN products as pd on ts.txn_id = pd.begin_xid::text
JOIN dbos.workflow_status as ws on ws.workflow_uuid = ts.workflow_uuid
ORDER BY changed_at;
changed_at | src_ip | product | inventory
---------------+-------------+------------------------+-----------
1716840311459 | 123.45.67.8 | Premium Quality Widget | 7
1716840314539 | 321.42.24.5 | Premium Quality Widget | 6
This query works by joining together a history table with two system tables:
- products : This history table records every change ever made to the original products table. In this application, each row corresponds to a purchase. Each row contains a version number column (begin_xid) indicating which transaction made the change.
- dbos.transaction_outputs : This system table records the state of each transaction execution. It contains the transaction’s ID (txn_id) and the idempotency key of the workflow to which the transaction belongs (workflow_uuid).
- dbos.workflow_status : This system table records the state of each workflow execution. It includes the workflow execution’s idempotency key (workflow_uuid) and the HTTP request that originally triggered it (request).
Since each row of the products history table corresponds to a purchase, by joining these three tables together, we obtain information on the transaction and workflow which performed each purchase, including the IP address of the purchaser (stored in the HTTP request headers).
See it in Action...
Try Database Time Travel with DBOS for Free
Building database applications is challenging enough, and serverless computing platforms and distributed microservices architectures aren’t making it any easier. Database time-travel on DBOS gives you new tools to understand database behavior at any point in time to speed up development and troubleshooting.
To experience time travel on your own backend, you’ll need:
- TypeScript database application code, compiled with DBOS Transact
- An Amazon RDS PostgreSQL instance (there’s a free one included with every DBOS Cloud account, or bring your own)
- A free DBOS Cloud account to run time travel queries and debugging