Trace exposure to the nearest taint

Download the complete SQL script, including dataset setup for a fresh database.

Mark 100 people as tainted, give every other person their minimum distance from any taint, and retain a pointer that explains the route. This pattern can represent downstream exposure to a flagged account or disrupted hub. Run the setup once, then run the following sections in order.

Select the tainted people

CREATE TABLE IF NOT EXISTS warren_examples.tainted (
    id bigint PRIMARY KEY REFERENCES warren_examples.people(id)
);
-- Replaces this example's seed selection on each run. Use your business
-- rule here instead, for example a join to a sanctions or disruption table.
TRUNCATE warren_examples.tainted;
INSERT INTO warren_examples.tainted (id)
SELECT id FROM warren_examples.people ORDER BY md5(id::text), id LIMIT 100;

The hash ordering chooses 100 reproducible sample seeds. Replace it with your actual business rule. These are ordinary people.id values; no internal graph numbering is involved.

Save the nearest distance

SELECT warren.vector_from_query('example_social', 'is_tainted',
    'SELECT id, true FROM warren_examples.tainted', dtype := 'bool');

SELECT warren.vector_from_query('example_social', 'taint_distance', $query$
    SELECT node_id, depth::float8
    FROM warren.bfs('example_social',
        ARRAY(SELECT id FROM warren_examples.tainted), NULL::integer)
$query$, fill := 'Infinity'::float8);

SELECT value AS distance, count(*) AS people
FROM warren.vector_values('example_social', 'taint_distance', NULL::bigint)
GROUP BY value ORDER BY value;

SELECT p.id, p.name, d.value AS distance_to_nearest_taint
FROM warren.vector_values('example_social', 'taint_distance', NULL::bigint) d
JOIN warren_examples.people p ON p.id = d.node_id
WHERE NOT EXISTS (SELECT 1 FROM warren_examples.tainted t WHERE t.id = p.id)
ORDER BY d.value DESC, p.id LIMIT 20;

One multi-source BFS visits each reachable person at their shortest distance from any seed. Tainted people receive zero; an untainted person’s distance is its nearest-taint distance along outward edges. Unreachable people retain Infinity in the saved vector. With no seeds, everyone would retain infinity.

For this dataset, the distance distribution is:

Distance	People
0	100
1	490
2	2,031
3	4,807
4	2,425
5	143
6	4

The graph holds two persisted vectors: is_tainted for seed membership and taint_distance for distances. vector_values returns node_id and value, which join directly to application tables.

Save pointers and reconstruct a path

BFS already returns the predecessor as via. Save that original id as toward_taint, alongside the distance. The table contains reached people only; a left join from people can also show the unreachable ones.

CREATE TABLE IF NOT EXISTS warren_examples.taint_routes (
    node_id bigint PRIMARY KEY,
    distance integer NOT NULL,
    toward_taint bigint
);
-- Replace the saved pointers atomically, even when pgAdmin autocommits.
DO $$
BEGIN
    DELETE FROM warren_examples.taint_routes;
    INSERT INTO warren_examples.taint_routes (node_id, distance, toward_taint)
    SELECT node_id, depth, via
    FROM warren.bfs('example_social',
        ARRAY(SELECT id FROM warren_examples.tainted), NULL::integer);
END $$;

SELECT p.name, r.distance, neighbour.name AS next_person_toward_taint
FROM warren_examples.taint_routes r
JOIN warren_examples.people p ON p.id = r.node_id
LEFT JOIN warren_examples.people neighbour ON neighbour.id = r.toward_taint
ORDER BY r.distance DESC, p.id LIMIT 10;

WITH RECURSIVE route AS (
    SELECT node_id, distance, toward_taint, ARRAY[node_id] AS path
    FROM warren_examples.taint_routes
    WHERE node_id = (SELECT id FROM warren_examples.people WHERE name = 'person-1316')
    UNION ALL
    SELECT next.node_id, next.distance, next.toward_taint, route.path || next.node_id
    FROM route
    JOIN warren_examples.taint_routes next ON next.node_id = route.toward_taint
    WHERE next.distance = route.distance - 1
)
SELECT node_id AS nearest_tainted_person, path AS path_to_taint
FROM route WHERE distance = 0;

Each pointer decreases the distance by one. Tainted seeds have distance zero and a NULL pointer. For person 1316, the path ends at tainted person 2081, six hops away. A route contains one chosen shortest path; equally near seeds or alternative shortest routes are not all enumerated.

Be explicit about direction and freshness

Propagation follows edges outward from a taint. The pointers walk back toward that taint, against those directed edges. If you instead want paths that follow edges from a person to a taint, use direction := 'in' in both BFS calls. For an undirected interpretation, use 'both' in both calls.

The vectors and route table are saved results, not live annotations. Rerun all analysis sections when the tainted selection changes. If nodes or edges change, refresh the graph first and then rerun the analysis. Both BFS calls must use the same graph version and seed set; in a concurrently changing application, use a REPEATABLE READ transaction to keep that snapshot consistent across all sections.

In logistics, distance from disruption describes potential exposure. Actual impact also depends on orders, stock, capacities, and alternatives, which you can join to these graph results in SQL.