A complete example¶

A worked example of the sort of thing this extension is for: a financial audit trail that must be append-only and kept for seven years.

The requirement¶

Every approval is recorded.
Nobody can edit or remove a record, including administrators.
Records must be kept for seven years, then removed.
After seven years they should be cleared automatically.

The table¶

CREATE SCHEMA audit;

CREATE TABLE audit.approvals (
    id          bigserial PRIMARY KEY,
    approved_at timestamptz NOT NULL DEFAULT now(),
    approver    text        NOT NULL,
    invoice_id  bigint      NOT NULL,
    amount      numeric(12,2) NOT NULL,
    notes       text
)
USING vault
WITH (permissions = 'insert', retention = 2555);

'insert' and nothing else — no update, no delete, no truncate, no drop. retention = 2555 is seven years.

Note that delete is not granted. Rows still get removed after seven years, because retention permits that by itself. Granting delete as well would allow any row to be removed at any time, which would leave the retention period protecting nothing.

Add the indexes you need¶

Ordinary DDL, no special handling:

CREATE INDEX approvals_invoice_idx  ON audit.approvals (invoice_id);
CREATE INDEX approvals_approver_idx ON audit.approvals (approver, approved_at);

Use it¶

INSERT INTO audit.approvals (approver, invoice_id, amount, notes)
VALUES ('alice', 4501, 12500.00, 'Q3 capital works');

SELECT id, approved_at, approver, amount, _$purge_ts
  FROM audit.approvals;

 id |         approved_at         | approver |  amount  |         _$purge_ts
----+-----------------------------+----------+----------+----------------------------
  1 | 2026-08-17 09:14:22.10+10   | alice    | 12500.00 | 2033-08-16 09:14:22.10+10

Confirm it is actually protected¶

Worth doing once, so you have seen it with your own eyes:

UPDATE audit.approvals SET amount = 0 WHERE id = 1;

ERROR:  update is not permitted on vault table "approvals"

DELETE FROM audit.approvals WHERE id = 1;

ERROR:  row in vault table "approvals" is still within its retention period

TRUNCATE audit.approvals;

ERROR:  truncate is not permitted on vault table "approvals"

DROP TABLE audit.approvals;

ERROR:  drop is not permitted on vault table "approvals"

Try the same as postgres. The answers do not change.

Correcting a mistake¶

Since rows cannot be edited, a correction is a new row:

INSERT INTO audit.approvals (approver, invoice_id, amount, notes)
VALUES ('alice', 4501, -12500.00, 'Reversal of approval id 1 — wrong invoice');

That is normal practice for a ledger, and it is what makes the trail worth having: the original entry and its correction are both visible.

Clear out expired records¶

Once rows pass seven years:

CALL pgvault_tables.purge_vault('audit', 'approvals');

Ask your DBA to run this nightly — see Scheduling the Purge.

Check on it later¶

SELECT * FROM pgvault_tables.view_vault_tables()
 WHERE schema_name = 'audit';

 schema_name | table_name | permissions | retention_days
-------------+------------+-------------+----------------
 audit       | approvals  | insert      |           2555