Viewing History
This document describes how to read what pgVolvra recorded. Reading
history requires only membership in volvra_viewer and the privilege
to read the underlying table.
The history of one row
volvra.history returns every version of a row over time, in order:
SELECT change_id, ts, actor, db_user, op, old_row, new_row
FROM volvra.history('orders', '{"id":1}');
The second argument is the primary key as jsonb. pgVolvra matches by
containment, so a composite key can be given in full or in part.
The following table describes each column of the result:
| Column | Contents |
|---|---|
| change_id | Identifier of the captured change. |
| ts | When pgVolvra captured the change. |
| actor | What the application declared it was doing. Spoofable by design. |
| db_user | The authenticated principal. This is the audit column. |
| op | I for insert, U for update, D for delete, T for an uncaptured truncate. |
| old_row | Values before the change, for an update or delete. |
| new_row | Values after the change, for an insert or update. |
| txid | Transaction that made the change. |
An UPDATE stores only the columns whose values changed, so old_row
and new_row show the difference rather than the whole row. Set
capture_updates to full if you need complete images.
Recent transactions
volvra.transactions groups the history by transaction, newest first,
which is how you find a mistaken migration:
SELECT txid, started, ended, db_users, tables,
inserts, updates, deletes, changes
FROM volvra.transactions();
Limit the result to a time range and a row count:
SELECT * FROM volvra.transactions(now() - interval '1 day', now(), 50);
Reading the history table directly
The volvra.change_log table is readable, and row-level security
restricts each reader to the history of tables that reader may
already SELECT:
SELECT table_name, op, pk, ts, db_user
FROM volvra.change_log
WHERE table_name = 'public.orders'
ORDER BY id DESC
LIMIT 20;
Reading history is never a way around a table's own grants. A reader who cannot SELECT a table cannot read that table's history either.
Two identities for every change
pgVolvra records both what the application claimed and who the database authenticated. The following table compares the two columns:
| Column | Source | Trust |
|---|---|---|
| actor | The volvra.actor setting, or the authenticated principal when the application sets nothing. | Spoofable by design, because only the application knows which of its services acted. |
| db_user | The SET ROLE target if one is active, otherwise session_user. | Authenticated by the database. Use this column for audit. |
Set the actor for a transaction as follows:
SET LOCAL volvra.actor = 'svc:checkout';
Who changed a row, and when
The following statement answers the common audit question for a single row:
SELECT ts, db_user, actor, op,
old_row -> 'total' AS was,
new_row -> 'total' AS became
FROM volvra.history('orders', '{"id":1}')
ORDER BY change_id;
Redacted history
An erasure request blanks the row images and leaves the change record
in place, so the fact that a change happened survives. Such rows carry
redacted_at and redacted_by:
SELECT id, ts, op, redacted_at, redacted_by
FROM volvra.change_log
WHERE redacted_at IS NOT NULL;
See the Erasing Data document.
Next Steps
- The Undoing Changes document describes how to revert what you find.
- The Erasing Data document explains redaction and erasure.
- The Security document describes who may read history.