Tracking Changes in AWS Resource Tagging Over Time

kind: source
spec:
  name: 'aws'
  path: 'cloudquery/aws'
  version: 'v34.9.0'
  tables: ['aws_lambda_functions']
  deterministic_cq_id: true
  destinations: ['postgresql']
  spec:
---
kind: destination
spec:
  name: 'postgresql'
  path: 'cloudquery/postgresql'
  version: 'v8.16.0'
  write_mode: 'append'
  spec:
    connection_string: ''

WITH prev_tag AS (
    SELECT
        arn,
        _cq_sync_time,
        tags,
        LAG(tags) OVER (PARTITION BY arn ORDER BY _cq_sync_time) AS previous_tags
    FROM
        aws_lambda_functions
    ORDER BY
        arn, _cq_sync_time
)
SELECT
    _cq_sync_time,
    arn,
    tags,
    previous_tags,
    jsonb_build_object(
        'removed',
        (
            SELECT jsonb_object_agg(key, value)
            FROM (
                SELECT j1.key, j1.value
                FROM jsonb_each(previous_tags) j1
                LEFT JOIN jsonb_each(tags) j2 ON j1.key = j2.key
                WHERE j2.key IS NULL
            ) AS removed
        ),
        'added',
        (
            SELECT jsonb_object_agg(key, value)
            FROM (
                SELECT j1.key, j1.value
                FROM jsonb_each(tags) j1
                LEFT JOIN jsonb_each(previous_tags) j2 ON j1.key = j2.key
                WHERE j2.key IS NULL
            ) AS added
        ),
        'changed',
        (
            SELECT jsonb_object_agg(key, value)
            FROM (
                SELECT j1.key, j1.value
                FROM jsonb_each(tags) j1
                JOIN jsonb_each(previous_tags) j2 ON j1.key = j2.key
                WHERE j1.value <> j2.value
            ) AS changed
        )
    ) AS tag_diff
FROM
    prev_tag
WHERE previous_tags IS NOT NULL
ORDER BY arn, _cq_sync_time;

{
  "added": null,
  "changed": null,
  "removed": null
}

{
  "added": {
    "Owner": "Bob"
  },
  "changed": null,
  "removed": null
}

{
  "added": null,
  "changed": {
    "Owner": "Alice"
  },
  "removed": null
}

{
  "removed": {
    "Owner": "Alice"
  },
  "added": null,
  "changed": null
}

CREATE OR REPLACE FUNCTION jsonb_diff(tags JSONB, previous_tags JSONB)
RETURNS JSONB LANGUAGE plpgsql AS $$
BEGIN
    RETURN jsonb_build_object(
        'removed',
        (
            SELECT jsonb_object_agg(key, value)
            FROM (
                SELECT j1.key, j1.value
                FROM jsonb_each(previous_tags) j1
                LEFT JOIN jsonb_each(tags) j2 ON j1.key = j2.key
                WHERE j2.key IS NULL
            ) AS removed
        ),
        'added',
        (
            SELECT jsonb_object_agg(key, value)
            FROM (
                SELECT j1.key, j1.value
                FROM jsonb_each(tags) j1
                LEFT JOIN jsonb_each(previous_tags) j2 ON j1.key = j2.key
                WHERE j2.key IS NULL
            ) AS added
        ),
        'changed',
        (
            SELECT jsonb_object_agg(key, value)
            FROM (
                SELECT j1.key, j1.value
                FROM jsonb_each(tags) j1
                JOIN jsonb_each(previous_tags) j2 ON j1.key = j2.key
                WHERE j1.value <> j2.value
            ) AS changed
        )
    );
END;
$$;

WITH prev_tag AS (
    SELECT
        arn,
        _cq_sync_time,
        tags,
        LAG(tags) OVER (PARTITION BY arn ORDER BY _cq_sync_time) AS previous_tags
    FROM
        aws_lambda_functions
    ORDER BY
        arn, _cq_sync_time
)
SELECT
    _cq_sync_time,
    arn,
    tags,
    previous_tags,
    jsonb_diff(tags, previous_tags) AS tag_diff
FROM
    prev_tag
WHERE previous_tags IS NOT NULL
ORDER BY arn, _cq_sync_time;