Query icon
Assert icon
Queries icon
OutputValues icon
Parallel icon
Write icon
Schedule icon

Audit PostgreSQL foreign-key data without trusting constraint flags

Audit PostgreSQL orphaned rows and composite foreign keys in a read-only snapshot. Keep JSON and Markdown evidence even when the quality gate fails.

Categories
Data

Bulk imports or replication sessions can bypass foreign-key triggers while the catalog still says a constraint is valid. This flow checks the data itself, including composite keys and MATCH FULL null rules, without changing rows or constraints.

How it works

  1. Select 1-25 ordinary child tables. A native catalog query discovers up to 100 foreign keys and builds safely quoted anti-joins using each key's actual comparison operators.
  2. Reject missing tables, inherited/partitioned relations, missing SELECT privileges or row-security filtering on either side. An empty key set is reported explicitly.
  3. Run all checks in one read-only SELECT and MVCC snapshot. Server-side timeouts bound locks to five seconds and the scan to one minute; row-security changes cause failure rather than hidden rows.
  4. Write JSON and Markdown reports in parallel. Check the result count and optionally fail on violations; reports remain available when this gate fails.

What you get

  • Counts per constraint: scanned rows, violations and MATCH FULL partial-null failures, alongside the catalog validation flag.
  • Exact child/parent names in JSON, without copying sensitive key values.
  • MATCH SIMPLE exempts any null key; MATCH FULL exempts only all-null keys. Repeated bad rows are counted separately.

Who it's for

Data engineers checking a restore, bulk load or CDC target before downstream jobs resume.

Why orchestrate this with Kestra

Database discovery, visibility checks, transactional scans, parallel evidence and a quality gate share an execution. A nightly trigger reuses the same path as manual verification.

Prerequisites

PostgreSQL 14+ and its JDBC plugin. Use a role with SELECT on every child and referenced table and unrestricted row visibility. Start with small tables: this is a full scan per key, not a sample. Partitioning and inheritance are rejected on either side to avoid mismatched scan semantics. Concurrent DDL should be paused; separate catalog planning and scanning do not share a DDL snapshot.

Secrets

  • POSTGRES_JDBC_URL: JDBC URL of the database to audit.
  • POSTGRES_USERNAME: audit role.
  • POSTGRES_PASSWORD: role password.

Quick start

  1. Configure the secrets and import the flow.
  2. Replace public.order_items with real child tables and execute. Inspect both reports, including any zero-check result.
  3. Repair findings separately, rerun, then enable the 03:00 UTC schedule. Set fail_on_violations: false for report-only runs.

How to extend

Send the saved evidence to object storage or add an alert after report creation. Counts sum constraint violations, not distinct physical rows.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.