oikos/migrations/028_relationship_blast_direction.up.sql

-- 028_relationship_blast_direction.up.sql
-- Make blast_radius answer the question it is named after.
--
-- blast_radius walked source_id -> target_id for every relationship type. But
-- which end of an edge is the DEPENDENT differs per type:
--
--   machine --hosts--> container      if the machine dies, the container dies
--                                     -> dependent is the TARGET (forward)
--   service --depends-on--> service   if the target dies, the SOURCE breaks
--                                     -> dependent is the SOURCE (backward)
--   ingress --routes-to--> service    if the service dies, the route 502s
--                                     -> dependent is the SOURCE (backward)
--   document --documents--> entity    neither breaks the other
--                                     -> no runtime dependency at all
--
-- Walking everything forwards meant the answer was right for `hosts` and
-- `provides` and wrong for every backward edge, while `documents`, `involves`
-- and `targets` (2,800+ edges of pure bookkeeping) polluted the result with
-- tasks and executions that cannot "break".
--
-- Direction is therefore a property of the relationship type, declared in
-- seeds/ontology.yaml — the same shape as the `monitoring:` declaration on
-- entity types.
--
--   forward  : if the SOURCE fails, the TARGET is affected
--   backward : if the TARGET fails, the SOURCE is affected
--   none     : no runtime dependency (default — bookkeeping and documentation)
--
-- Defaulting to 'none' is deliberate: an undeclared edge contributes nothing
-- rather than silently producing a wrong answer, which is how the old
-- everything-is-forward behaviour went unnoticed.

ALTER TABLE relationship_types
  ADD COLUMN IF NOT EXISTS blast_direction TEXT NOT NULL DEFAULT 'none'
  CHECK (blast_direction IN ('forward', 'backward', 'none'));

COMMENT ON COLUMN relationship_types.blast_direction IS
  'Which end of this edge depends on the other. forward = target depends on source. backward = source depends on target. none = no runtime dependency. Drives blast_radius().';

-- Walk the dependency graph in the direction each edge type declares.
--
-- Returns everything that is affected when start_id fails, with the number of
-- hops. Cycles are guarded by the path array, as before.
CREATE OR REPLACE FUNCTION blast_radius(start_id UUID, max_depth INT DEFAULT 3,
                                        rel_types TEXT[] DEFAULT NULL)
RETURNS TABLE(entity_id UUID, depth INT) AS $$
    WITH RECURSIVE walk AS (
        SELECT start_id AS entity_id, 0 AS depth, ARRAY[start_id] AS path
        UNION ALL
        SELECT next_id, w.depth + 1, w.path || next_id
        FROM walk w
        JOIN LATERAL (
            -- forward: this entity is the source, so the target depends on it
            SELECT r.target_id AS next_id
            FROM relationships r
            JOIN relationship_types rt ON rt.name = r.type
            WHERE r.source_id = w.entity_id
              AND r.valid_to IS NULL
              AND rt.blast_direction = 'forward'
              AND (rel_types IS NULL OR r.type = ANY(rel_types))
            UNION ALL
            -- backward: this entity is the target, so the source depends on it
            SELECT r.source_id AS next_id
            FROM relationships r
            JOIN relationship_types rt ON rt.name = r.type
            WHERE r.target_id = w.entity_id
              AND r.valid_to IS NULL
              AND rt.blast_direction = 'backward'
              AND (rel_types IS NULL OR r.type = ANY(rel_types))
        ) nxt ON TRUE
        WHERE w.depth < LEAST(max_depth, 5)
          AND NOT nxt.next_id = ANY(w.path)
    )
    SELECT entity_id, MIN(depth) FROM walk GROUP BY entity_id;
$$ LANGUAGE sql STABLE;