Convert network configuration transactions to CTEs in diesel

We have a lot of "has many" relationships in our networking-related Nexus db schema. For creating these records
in a single, idempotent operation, I would like to be able to perform a query like this, or an equivalent query that accomplishes the same:

WITH

-- find the id of the desired parent record if it exists
-- if it doesn't exist, generate a new id to be used in a subsequent creation query
NewOrExisting AS (
    SELECT id
    FROM omicron.public.address_lot
    WHERE (name, kind) = ($1, $2)
    UNION ALL
    SELECT gen_random_uuid()
    WHERE NOT EXISTS (
        SELECT 1
        FROM omicron.public.address_lot
        WHERE (name, kind) = ($1, $2)
    )
    LIMIT 1
),

-- if a parent record wasn't found, insert a new one
InsertedLot AS (
    INSERT INTO omicron.public.address_lot (id, name, kind)
    SELECT id, $1, $2
    FROM NewOrExisting
    WHERE NOT EXISTS (
        SELECT 1
        FROM omicron.public.address_lot
        WHERE (name, kind) = ($1, $2)
    )
    RETURNING id
),

-- accept arrays of parameters and unnest them, allowing us to create multiple child records
-- in a single query
PreparedBlocks AS (
    SELECT id, first_address, last_address
    FROM InsertedLot, UNNEST($3::inet[], $4::inet[]) AS params(first_address, last_address)
),

-- if any of the child records already exist, filter them from the PreparedBlocks
FilteredBlocks AS (
    SELECT *
    FROM PreparedBlocks pb
    WHERE NOT EXISTS (
        SELECT 1
        FROM omicron.public.address_lot_block alb
        WHERE alb.address_lot_id = pb.id
          AND alb.first_address = pb.first_address
          AND alb.last_address = pb.last_address
    )
)

-- insert the child records
INSERT INTO omicron.public.address_lot_block (address_lot_id, first_address, last_address)
SELECT id, first_address, last_address
FROM FilteredBlocks;

Start by locating the networking-related Nexus database transaction code and its Diesel query paths; no specific files or tests are named in the issue. Compare those transactions with the provided SQL CTE example, and consider the work complete when the relevant parent and child record creation operations are idempotent and use an equivalent CTE-based approach.