Data versioning is like keeping a diary for your data. It lets you make changes while keeping a record of every edit. This way, you can track what’s been changed and even go back to an earlier version if you need to.

Supabase uses PostgreSQL as its database. One cool feature in PostgreSQL for data versioning is temporal tables, also called system-versioned tables. These tables keep a log of all changes—whether you’re adding, updating, or deleting data. It’s super handy for auditing changes over time.

To get started with data versioning in Supabase, you’ll need to create a temporal table.

CREATE TABLE IF NOT EXISTS your_table
(
 id SERIAL PRIMARY KEY,
 data JSONB,
 sys_period tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
);

Here, your_table is the name of the table you want to version. The sys_period column is a tstzrange type, which represents a time period with a start and end time.

You’ll need to install the PostgreSQL temporal tables extension.

CREATE EXTENSION IF NOT EXISTS temporal_tables;

Next, set up a trigger function using the versioning function from the temporal_tables extension. This function will automatically create row versions for you.

CREATE TRIGGER versioning_trigger
 BEFORE INSERT OR UPDATE OR DELETE ON your_table
 FOR EACH ROW EXECUTE FUNCTION versioning('sys_period', your_table_history, true);

In this command, your_table_history is a history table that mirrors the structure of your_table.

The history table is where all the changes get stored. If you have a table named your_table, you’ll need a history table with the same columns.

CREATE TABLE IF NOT EXISTS your_table_history (LIKE your_table INCLUDING ALL);

Once you’ve done all this, the system will keep a version of every change in the history table. You can query this table to see the historical data.

Keep in mind, while data versioning with temporal tables is powerful, it’s not without its downsides. Using temporal tables can make your database grow quite a bit, and it might slow down queries on larger tables. Always weigh your specific needs to see if temporal versioning is the right fit.