Skip to content
← Ecosystem

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.

  1. The situation

    Your team states business rules: "no order without a customer", "every invoice carries tax", "last night's backup restores".

  2. 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".

  3. 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
For the technical team

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

  1. 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).

  2. 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.

  3. 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.

  4. 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.

psql · pg_living_assertionssql
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)
Try it Talk