id: postgres-foreign-key-orphan-audit
namespace: company.data
concurrency:
limit: 1
behavior: QUEUE
inputs:
- id: tables
type: JSON
defaults:
- schema: public
name: order_items
description: Explicit list of 1-25 ordinary child tables to audit. Partitioned
and inherited tables are rejected.
- id: fail_on_violations
type: BOOL
defaults: true
description: Keep the JSON and Markdown reports, then fail the run if orphaned
or invalid MATCH FULL rows exist.
tasks:
- id: plan
type: io.kestra.plugin.jdbc.postgresql.Query
url: '{{ secret(''POSTGRES_JDBC_URL'') }}'
username: '{{ secret(''POSTGRES_USERNAME'') }}'
password: '{{ secret(''POSTGRES_PASSWORD'') }}'
fetchType: FETCH_ONE
parameters:
requested: '{{ inputs.tables | toJson }}'
description: "Build catalog-quoted anti-join queries with the foreign key’s
actual equality operators; reject missing tables, incomplete visibility
and unsupported inheritance."
sql: |
WITH requested AS (
SELECT entry, entry->>'schema' AS schema_name, entry->>'name' AS table_name
FROM jsonb_array_elements(CAST(:requested AS jsonb)) entry
), selected AS (
SELECT r.*, c.oid, c.relkind,
format('%I.%I', COALESCE(r.schema_name, ''), COALESCE(r.table_name, '')) AS qualified_name
FROM requested r LEFT JOIN pg_namespace n ON n.nspname = r.schema_name
LEFT JOIN pg_class c ON c.relnamespace = n.oid AND c.relname = r.table_name
), foreign_keys AS (
SELECT c.*, s.qualified_name AS child_table,
format('%I.%I', pn.nspname, pc.relname) AS parent_table,
pc.relkind AS parent_kind
FROM selected s JOIN pg_constraint c ON c.conrelid = s.oid AND c.contype = 'f'
JOIN pg_class pc ON pc.oid = c.confrelid JOIN pg_namespace pn ON pn.oid = pc.relnamespace
), expressions AS (
SELECT f.oid, string_agg(format('c.%I IS NOT NULL', ca.attname), ' AND ' ORDER BY k.i) AS all_present,
string_agg(format('c.%I IS NOT NULL', ca.attname), ' OR ' ORDER BY k.i) AS any_present,
string_agg(format('c.%I IS NULL', ca.attname), ' OR ' ORDER BY k.i) AS any_missing,
string_agg(format('p.%I OPERATOR(%I.%s) c.%I', pa.attname, ns.nspname, op.oprname, ca.attname), ' AND ' ORDER BY k.i) AS equality
FROM foreign_keys f CROSS JOIN LATERAL generate_subscripts(f.conkey, 1) k(i)
JOIN pg_attribute ca ON ca.attrelid = f.conrelid AND ca.attnum = f.conkey[k.i]
JOIN pg_attribute pa ON pa.attrelid = f.confrelid AND pa.attnum = f.confkey[k.i]
JOIN pg_operator op ON op.oid = f.conpfeqop[k.i] JOIN pg_namespace ns ON ns.oid = op.oprnamespace
GROUP BY f.oid
), checks AS (
SELECT f.*, e.*,
CASE WHEN f.confmatchtype = 'f' THEN format('(%s) AND ((%s) OR NOT EXISTS (SELECT 1 FROM %s p WHERE %s))',
e.any_present, e.any_missing, f.parent_table, e.equality)
ELSE format('(%s) AND NOT EXISTS (SELECT 1 FROM %s p WHERE %s)', e.all_present, f.parent_table, e.equality) END AS violation,
CASE WHEN f.confmatchtype = 'f' THEN format('(%s) AND (%s)', e.any_present, e.any_missing) ELSE 'false' END AS partial_null
FROM foreign_keys f JOIN expressions e ON e.oid = f.oid
), problems AS (
SELECT 'Choose 1-25 child tables' AS reason WHERE (SELECT count(*) FROM requested) NOT BETWEEN 1 AND 25
UNION ALL SELECT 'Invalid schema/name entry' FROM requested
WHERE (jsonb_typeof(entry->'schema') = 'string' AND schema_name <> ''
AND jsonb_typeof(entry->'name') = 'string' AND table_name <> '') IS NOT TRUE
UNION ALL SELECT 'Duplicate child table: ' || qualified_name FROM selected GROUP BY qualified_name HAVING count(*) > 1
UNION ALL SELECT 'Not an ordinary table: ' || qualified_name FROM selected WHERE relkind IS DISTINCT FROM 'r'
UNION ALL SELECT 'Inheritance/partition member: ' || qualified_name FROM selected s
WHERE EXISTS (SELECT 1 FROM pg_inherits WHERE inhrelid = s.oid OR inhparent = s.oid)
UNION ALL SELECT 'Missing child SELECT or RLS filters rows: ' || qualified_name FROM selected
WHERE oid IS NOT NULL AND (NOT has_table_privilege(oid, 'SELECT') OR row_security_active(oid))
UNION ALL SELECT 'Missing parent SELECT or RLS filters rows: ' || parent_table FROM foreign_keys
WHERE NOT has_table_privilege(confrelid, 'SELECT') OR row_security_active(confrelid)
UNION ALL SELECT 'Unsupported parent relation: ' || parent_table FROM foreign_keys f
WHERE parent_kind <> 'r' OR EXISTS (SELECT 1 FROM pg_inherits WHERE inhrelid = f.confrelid OR inhparent = f.confrelid)
UNION ALL SELECT 'Unsupported match type: ' || conname FROM foreign_keys WHERE confmatchtype NOT IN ('s', 'f')
UNION ALL SELECT 'More than 100 foreign keys selected' WHERE (SELECT count(*) FROM foreign_keys) > 100
), statements AS (
SELECT child_table, conname, format(
'SELECT %L AS child_table, %L AS parent_table, %L AS constraint_name, %L AS match_type, %L::boolean AS catalog_validated, count(*) AS scanned_rows, count(*) FILTER (WHERE %s) AS violating_rows, count(*) FILTER (WHERE %s) AS partial_null_rows FROM %s c',
child_table, parent_table, conname, CASE confmatchtype WHEN 'f' THEN 'FULL' ELSE 'SIMPLE' END,
convalidated, violation, partial_null, child_table) AS statement
FROM checks
)
SELECT CAST(COALESCE((SELECT jsonb_agg(DISTINCT reason) FROM problems), CAST('[]' AS jsonb)) AS text) AS problems,
(SELECT count(*) FROM foreign_keys) AS expected_checks,
COALESCE((SELECT string_agg(statement, E'
UNION ALL
' ORDER BY child_table, conname) FROM statements),
'SELECT NULL::text AS child_table, NULL::text AS parent_table, NULL::text AS constraint_name, NULL::text AS match_type, NULL::boolean AS catalog_validated, 0::bigint AS scanned_rows, 0::bigint AS violating_rows, 0::bigint AS partial_null_rows WHERE false') AS audit_sql;
- id: require_complete_visibility
type: io.kestra.plugin.core.execution.Assert
conditions:
- '{{ fromJson(outputs.plan.row.problems) | length == 0 }}'
- id: audit
type: io.kestra.plugin.jdbc.postgresql.Queries
url: '{{ secret(''POSTGRES_JDBC_URL'') }}'
username: '{{ secret(''POSTGRES_USERNAME'') }}'
password: '{{ secret(''POSTGRES_PASSWORD'') }}'
transaction: true
fetchType: FETCH
description: One read-only statement scans every planned key under one MVCC
snapshot, with server-side lock and statement timeouts. RLS filtering
raises an error.
sql: |
SET TRANSACTION READ ONLY;
SET LOCAL row_security = off;
SET LOCAL lock_timeout = '5s';
SET LOCAL statement_timeout = '60s';
{{ outputs.plan.row.audit_sql }};
- id: summarize
type: io.kestra.plugin.core.output.OutputValues
values:
rows: '{{ outputs.audit.outputs | toJson | jq(''[.[] | .rows[]?]'') | first }}'
violations: '{{ outputs.audit.outputs | toJson | jq(''[.[] | .rows[]? |
.violating_rows] | add // 0'') | first }}'
checked: '{{ outputs.audit.outputs | toJson | jq(''[.[] | .rows[]?] | length'')
| first }}'
- id: write_reports
type: io.kestra.plugin.core.flow.Parallel
tasks:
- id: json_report
type: io.kestra.plugin.core.storage.Write
extension: .json
content: '{{ {''execution_id'': execution.id, ''tables'': inputs.tables,
''checked_constraints'': fromJson(outputs.summarize.values.checked),
''violating_rows'': fromJson(outputs.summarize.values.violations),
''constraints'': fromJson(outputs.summarize.values.rows)} | toJson }}'
- id: markdown_report
type: io.kestra.plugin.core.storage.Write
extension: .md
content: "# PostgreSQL foreign-key data audit
Execution: {{ execution.id }}
Checked constraints: {{ outputs.summarize.values.checked }}
Violating rows across constraints: {{
outputs.summarize.values.violations }}
Counts are per constraint: one row can violate several constraints.
JSON contains the exact relation and constraint names.
{% for row in fromJson(outputs.summarize.values.rows) %}
- {{ row.child_table }} → {{ row.parent_table }} / {{
row.constraint_name }}: {{ row.violating_rows }} violating rows out of
{{ row.scanned_rows }}; MATCH {{ row.match_type }}; partial-null
violations {{ row.partial_null_rows }}; catalog validated={{
row.catalog_validated }}.
{% endfor %}
Read-only audit: no rows, constraints or triggers were changed. Zero
checks means the selected tables have no foreign keys; it is not a
data validation result.\n"
- id: enforce_result
type: io.kestra.plugin.core.execution.Assert
description: Reports remain downloadable when findings fail this gate.
conditions:
- '{{ fromJson(outputs.summarize.values.checked) ==
outputs.plan.row.expected_checks }}'
- '{{ not inputs.fail_on_violations or
fromJson(outputs.summarize.values.violations) == 0 }}'
outputs:
- id: audit_json
type: FILE
required: false
value: '{{ outputs.json_report.uri ?? null }}'
- id: audit_markdown
type: FILE
required: false
value: '{{ outputs.markdown_report.uri ?? null }}'
triggers:
- id: nightly_referential_audit
type: io.kestra.plugin.core.trigger.Schedule
cron: 0 3 * * *
disabled: true
description: Enable after selecting tables and testing database permissions.