Change history

Turn on Track changes for a table: every row added, changed or deleted is recorded with who did it and the old and new values, and any change can be undone.

Turn on Track changes for a table and every row added, changed or deleted in it is recorded: when, by whom, the row before and the values after. You can look up the history of one row and undo any change. It works for every write, whether it comes from your app through the API, from Studio, from a shared view, from an Edge Function or from SQL.

#Turn it on

In Studio open Database › Change history, open Tables and switch on Track changes for the tables you want. Only changes made after that are recorded. The table needs a primary key, which is how its rows are told apart. Project owners and admins can switch tracking on and off; turning it off keeps what was already recorded until it expires.

#What is recorded

The By column shows who made the change: the user id of a signed-in user of your app, the e-mail address of a Studio user or of someone using a shared view, service_role or anon for API calls made with those keys, or otherwise the database role (for example your own connection with the database password). Emptying a table with truncate is not recorded.

#Find the history of a row

Pick the table and enter the row's primary key as JSON, for example {"id": 42}, or click a row key in the list. You can also read the history with SQL:

select id, op, old, new, changed_at, changed_by
from _stitch_history.changes
where tbl = 'public.orders'::regclass and pk = '{"id": 42}'
order by id desc;

#Undo a change

Each change in the list has an undo button. Undoing a change sets the columns it changed back to their values before it, puts a deleted row back, or removes an added row. The undo is itself recorded as a new change, so it can be undone too. Nothing happens if the row is gone (for an update) or a row with the same key exists again (for a delete). The same from SQL:

select _stitch_history.revert(1234);  -- 'reverted', 'gone' or 'exists'

Undoing a change from long ago only sets back the columns that change touched; later changes to other columns stay. Triggers, constraints and foreign keys apply as for any write.

#Things to know

• Recorded changes count towards the database size. A table with many writes (counters, sessions, logs) records a lot; track the tables where the history matters.
• Each write statement adds one insert into the history, so bulk changes stay fast, but a statement that changes a million rows records a million changes.
• Deleted rows also go to the recycle bin when it is on for the table; you can use either to put them back.
• Changes are deleted automatically after the number of days your plan keeps them.

#Limits