Policy Device Tags Assignment

Requirements

device.tags → text[]
policyDeviceTagsAssignment.deviceTags → text[]
When a tag is removed from all devices, it should also be removed from policyDeviceTagsAssignment.deviceTags

No foreign keys are possible here, so PostgreSQL triggers are the correct and only safe solution.

Trigger logic (array → array cleanup)

What the trigger does

Detects which tags were removed from a device
For each removed tag:
- checks if it still exists on any device
- if not → removes it from all policies’ deviceTags[]

Trigger function

CREATE OR REPLACE FUNCTION cleanup_policy_device_tags_array()
RETURNS trigger AS $$
DECLARE
  removed_tags text[];
  tag text;
BEGIN
  -- Only run if tags actually changed
  IF NEW.tags IS DISTINCT FROM OLD.tags THEN
    -- Tags removed from this device
    removed_tags := ARRAY(
      SELECT unnest(OLD.tags)
      EXCEPT
      SELECT unnest(NEW.tags)
    );

    -- For each removed tag, check if it still exists on ANY device
    FOREACH tag IN ARRAY removed_tags LOOP
      IF NOT EXISTS (
        SELECT 1
        FROM device
        WHERE tag = ANY(tags)
      ) THEN
        -- Remove tag from all policy assignments
        UPDATE policy_device_tags_assignment
        SET device_tags = array_remove(device_tags, tag)
        WHERE tag = ANY(device_tags);
      END IF;
    END LOOP;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Attach trigger to `device`

CREATE TRIGGER trg_cleanup_policy_device_tags_array
AFTER UPDATE OF tags
ON device
FOR EACH ROW
EXECUTE FUNCTION cleanup_policy_device_tags_array();

Optional: clean up empty arrays

If you want to delete policy rows that end up with no tags:

DELETE FROM policy_device_tags_assignment
WHERE cardinality(device_tags) = 0;

Optional: prevent invalid inserts

Block assigning tags that don’t exist on any device:

CREATE OR REPLACE FUNCTION validate_policy_device_tags_array()
RETURNS trigger AS $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM unnest(NEW.device_tags) t(tag)
    WHERE NOT EXISTS (
      SELECT 1
      FROM device
      WHERE t.tag = ANY(tags)
    )
  ) THEN
    RAISE EXCEPTION 'One or more device tags do not exist on any device';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_policy_device_tags_array
BEFORE INSERT OR UPDATE
ON policy_device_tags_assignment
FOR EACH ROW
EXECUTE FUNCTION validate_policy_device_tags_array();

✅ Final guarantees

✅ Arrays stay in sync
✅ No orphaned policy tags
✅ No accidental deletes when tags still exist
✅ Works transparently with Drizzle
❌ No fake foreign keys