Guarantees with a date
pg_living_assertions v0.5.10
A guarantee with no date of last check is a belief.
A registry of things you claim are true about your database, each with the SQL that proves it and the date it was last proven. The catalog stores the state (indisvalid, convalidated, tgenabled) and never the question that matters: is it still true, and since when have we not looked?
Install
CREATE EXTENSION pg_living_assertions; In plain words
A list of the things your company takes as true about its data, with the proof and the date somebody last checked.
- The situation
Your team states business rules: "no order without a customer", "every invoice carries tax", "last night's backup restores".
- Without this piece
Somebody checked them once, months ago. Today nobody knows whether they still hold, and "nobody has checked" looks far too much like "all good".
- With this piece
Every rule has its check and runs whenever you want. You see "holds", "broken" or "never checked", always with the date. An old, unchecked rule no longer passes for healthy.
Why it matters if you decide
- Evidence for audits and for customers: every guarantee with its proof and its date
- "Never checked" stops looking like "all good": the system tells seven states apart, not two
- It is the foundation two other pieces of the ecosystem build on
The problem
The mechanism works and the thing that records it lies
The index stays marked UNIQUE while duplicates go in. The RLS policy "protects" the table. The replica "is caught up". The backup "restores". Claims someone made one day and nobody checked again.
This is not an implementation of CREATE ASSERTION (SQL-92, not implemented in PostgreSQL): those are constraints on data, evaluated on every write. These are assertions about the state of the system, evaluated when you ask. Saying otherwise would be selling this as something it is not.
How it works
Declare, run, read the age next to the verdict
- 01
declare(name, description, sql)
The check returns ONE row with a boolean column holds and, optionally, a text detail. Re-run everything with run_all() or one with run(name).
- 02
Seven states, and the extra ones are the point
holds, broken, unknown (it ran and could not decide: NOT false), erroring (the check itself is failing: a defect; until fixed it watches nothing), unchecked (nobody ever ran it: NOT a clean bill of health), retired (turned off on purpose, with a reason and a date), unregistered.
- 03
The age travels with the verdict
status always reports checked_at and age; stale(interval) lists what has gone too long, labelling "never checked" apart from "checked long ago": only one of those is fixed by waiting.
- 04
The stored SQL runs sealed
It runs as the role that declared it and, since 0.5.10, cannot leave it: whatever it reaches, it reaches with its author's rights and not the caller's. Declaring an assertion is a privilege.
SQL
Use
state(), assert_holds() and stale() are open to any role given USAGE on the schema (a name, a verdict and its age). The status view is not: it carries each check's detail, which may be data, and is the owner's to grant.
CREATE EXTENSION pg_living_assertions;
SELECT living_assertions.declare(
'no_orphaned_invoices',
'every invoice points at a customer that exists',
$$select count(*) = 0 as holds,
count(*) || ' orphaned invoices' as detail
from invoices i
left join customers c on c.id = i.customer_id
where c.id is null$$);
SELECT name, state, age FROM living_assertions.status; What it guarantees
What it guarantees
- state() never returns NULL and never returns an empty result: both read as "fine".
- unknown is not collapsed into broken (that is how a monitor starts reporting what it cannot know), nor unchecked into holds (that is how a guarantee nobody looked at gets trusted).
- An assertion is not renegotiated in place: it is declared again. Those of a dropped consumer turn erroring rather than vanishing: loud beats silent.
- declare_unchanged approves what an expression evaluates to today and tells you when it changes. It takes the expression, not the value: a stored value would be compared against itself forever.
- The check log is append-only: since 0.5.9 the server dates both an assertion and a check, and TRUNCATE is refused on both tables.
- Pure SQL: no shared library, no dependencies.
What it does not do
What it does not do
- It does not make anything correct: it tells you whether something you claimed is still true. It cannot discover claims nobody wrote down.
- It does not replace constraints: a constraint stops bad data getting in; a living assertion tells you a guarantee stopped applying.
- Somebody has to write the check, same as a CHECK constraint.
- unknown is not a diagnosis: it says the check could not decide, not why.
- The seal does not undo what leaves the transaction: nextval() on a temporary sequence, dblink, a foreign data wrapper, an untrusted-language function that writes a file, COPY TO PROGRAM.
- declare_unchanged compares the TEXT of the value, and making that text canonical is your job: jsonb normalises key order; an array does not.
- It is not pg_isok (by Karl O. Pinc, which the author of this extension co-maintains). pg_isok works row by row, for a person to review what looks questionable; this one works claim by claim and keeps a verdict and when it was last reached.
For the enterprise
For the enterprise
This is audit evidence: every guarantee the business assumes about the database, with its proof and its date. And it is the foundation of pg_promise_guard and pg_grammar_guard, which record what they watch in it.
- A versionable registry of guarantees with verdict and age, readable by a monitor
- Survives pg_dump + restore (make check-dump); who may store SQL that somebody else will execute is tested (make check-privs)
- Version matrix measured with make installcheck in official containers
Spec sheet
- Version measured
- 0.5.10 (public image 0.3.6, 2026-11-13)
- PostgreSQL
- 11 to 19 green (measured 2026-09-16 and by CI on 0.5.0); 10 unsupported by design (EXECUTE FUNCTION)
- License
- Apache-2.0
- Enterprise edition
- Included (PostgreSQL 18)
The other pieces of the ecosystem
- pg_agent_gate AI agents on PostgreSQL
- pg_phasevec · preview 0.x Vector memory with provenance
- pg_plan_guard Query plans, watched
- pg_recall_guard Vector index recall
- pg_promise_guard Schema guarantees
- pg_grammar_guard Structured output that cannot invent names
- ygg-agent v0.2.0 · PyPI The agent loop on pg_agent_gate, with no MCP: the model writes SQL and the database decides.