Schema guarantees
pg_promise_guard v0.2.10
Find the guarantees your schema claims to give and silently stopped giving.
A migration runs CREATE UNIQUE INDEX CONCURRENTLY, fails on duplicates that were already in the table, and leaves the index behind: marked unique, marked invalid. From that moment \d says UNIQUE, your ORM says unique, your application logic assumes unique, and nothing is enforced. Duplicates keep going in.
Install
CREATE EXTENSION pg_promise_guard; In plain words
It finds the guarantees your database claims to have and quietly stopped keeping.
- The situation
Your system promises there cannot be two customers with the same tax ID, and that every change lands in the audit log.
- Without this piece
A system update failed halfway and left those rules switched off. Everything looks the same as before. Duplicates go in and the audit log records nothing.
- With this piece
A check cheap enough to run every minute lists every broken guarantee, and says whether it is serious (bad data is entering right now) or transitional (in the middle of a planned change).
Why it matters if you decide
- A yes or no for monitoring: "promises kept" or "not"
- Tells serious from transitional, so nobody silences the alert
- It never touches your data: it only reads how the database is configured
The problem
The failure was loud exactly once; the broken state is silent forever
A loud, one-off event is precisely what needs a periodic sentinel rather than one more alert.
Not amcheck: amcheck verifies that a valid index is structurally sound. These indexes are structurally perfect and simply not in effect. Different question, different answer.
| What the schema says | What happens |
|---|---|
| UNIQUE index | invalid: duplicates go in |
| CHECK / FOREIGN KEY constraint | NOT VALID: the rows already there were never checked |
| Audit trigger | DISABLED for a bulk load, never re-enabled |
| Row level security | ENABLED but not FORCED: the table owner bypasses every policy |
How it works
One view, one boolean, two severities
- 01
promise_breaks
One row per broken guarantee: kind, object, relation, severity. Reads the system catalogs only: no user data, no locks, no shared library.
- 02
breach vs gap
breach: the schema states a guarantee that is NOT being enforced right now; wrong data can be entering as you read. gap: the guarantee holds from here on but does not cover what was already there (a NOT VALID constraint mid-migration is legitimate).
- 03
promises_kept()
Only goes false on a breach. A monitor that goes red for a deliberate intermediate state gets silenced, and takes the real breaches with it.
- 04
What it reports since 0.2.8
Beyond invalid indexes, NOT VALID constraints, disabled triggers and RLS not forced: NOT ENFORCED (PostgreSQL 18), disabled FK triggers, a policy on a table without RLS, disabled rules and event triggers. It searches pg_catalog first (0.2.7) so nobody can blind the scanner with a fake pg_trigger in public.
SQL
Usage
Safe to run on a busy production database and cheap enough to run every minute. Only watch() needs pg_living_assertions.
CREATE EXTENSION pg_promise_guard;
SELECT * FROM promise_breaks;
-- kind | object | relation | severity
-- ----------------------+-------------------------+-----------------+----------
-- invalid_unique_index | app.customers_tax_id_uk | app.customers | breach
-- disabled_trigger | app.audit_insert | app.invoices | breach
-- unenforced_rls | app.tenant_data | app.tenant_data | breach
-- not_valid_constraint | app.amount_positive | app.invoices | gap
SELECT promises_kept(); -- whole database
SELECT promises_kept('app'); -- one schema
SELECT * FROM check_promises('app'); What it guarantees
What it guarantees
- Runs under READ COMMITTED; a schema that does not exist is an error, not a clean bill of health.
- Objects of an extension owned by a superuser are skipped; an extension a tenant owns is not.
- Green on 18.6 and 19beta2 against the same expected output: nothing depends on 19.
What it does not do
What it does NOT do, declared here rather than hidden in the version number
- It reports state, not history: it cannot tell you when the index went invalid or who disabled the trigger.
- It does not check whether a valid index is corrupt: that is amcheck.
- It does not look at permissions, search_path shadowing or default privileges, all of which can also break what people call "guarantees".
- It cannot see session_replication_role = replica set by a superuser: it skips triggers and foreign keys without leaving anything in the catalog.
- It does not run below PostgreSQL 11: META.json declares 11 as the minimum, and installcheck passes on 11 through 19 (CI and the 2026-11-11 review).
For the enterprise
For the enterprise
The sentinel that turns "we have uniqueness / auditing / RLS" into something checked every minute and understood by monitoring as one boolean.
- promises_kept() as a health check in your existing monitoring
- Tells breach from gap: it does not train the team to ignore alerts
- With pg_living_assertions, watch() records the scan with its date
Spec sheet
- Version measured
- 0.2.10 (public image 0.3.6, 2026-11-13)
- PostgreSQL
- 11 through 19 green (installcheck in CI and in the 2026-11-11 review); META declares 11
- Depends on
- Nothing; pg_living_assertions only for watch()
- 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_living_assertions Guarantees with a date
- pg_plan_guard Query plans, watched
- pg_recall_guard Vector index recall
- 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.