Create Clerk Pseudo FDW

clerk.sql

-- ADD YOUR PRIVATE KEY
select vault.create_secret('<PRIVATE KEY>', 'clerk_private_key');

-- after executing the below code, you can query your user data from Clerk with the following query:
-- SELECT * FROM clerk_users;

-- Requires HTTP extension to function
create extension if not exists http with schema extensions;

CREATE OR REPLACE FUNCTION clerk_get_data()
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    request_status INT;
    final_response JSONB := '{}'::JSONB;
    intermediary_response JSONB;
    clerk_key TEXT;
    current_count INT := 0;
    max INT;
BEGIN
    SELECT 
        decrypted_secret INTO clerk_key 
    FROM vault.decrypted_secrets 
    WHERE name = 'clerk_private_key';

    SELECT
        status,
        ((content::JSONB)->>'total_count')::INT
        INTO request_status, max
    FROM http((
        'GET'::http_method,
        'https://api.clerk.com/v1/users/count',
        ARRAY[
            http_header('Authorization', 'Bearer ' || clerk_key)
        ]::http_header[],
        'application/json',
        NULL
    )::http_request);

    IF request_status <> 200 THEN
        RAISE EXCEPTION 'CLERK REQUEST FAILED WITH STATUS %', request_status;
    END IF;
    
    WHILE current_count < max LOOP
            SELECT
                status,
                content::JSONB
                INTO request_status, intermediary_response
            FROM http((
                'GET'::http_method,
                'https://api.clerk.com/v1/users?offset=' || current_count || '&limit=500&order_by=-created_at',
                ARRAY[
                    http_header('Authorization', 'Bearer ' || clerk_key)
                ]::http_header[],
                'application/json',
                NULL
            )::http_request);

            IF request_status <> 200 THEN
                RAISE EXCEPTION 'CLERK REQUEST FAILED WITH STATUS %', request_status;
            END IF;
            final_response := final_response || intermediary_response;
            current_count := current_count + 500;
    END LOOP;

    RETURN final_response;
        
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Error: %', SQLERRM;
    END;
$$;

SELECT clerk_get_data();

CREATE OR REPLACE FUNCTION clerk_return_temp_table()
RETURNS TABLE (
    user_id TEXT,
    first_name TEXT,
    last_name TEXT,
    emails TEXT,
    gender TEXT,
    created_at BIGINT,
    updated_at BIGINT,
    last_sign_in_at BIGINT,
    phone_numbers BIGINT[],
    username TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    CREATE TEMPORARY TABLE clerk_users_temp
    ON COMMIT DROP
    AS
    SELECT 
        data->>'id' AS user_id,
        data->>'first_name' AS first_name,
        data->>'last_name' AS last_name,
        (jsonb_array_elements(data->'email_addresses')->>'email_address')::TEXT AS emails,
        data->>'gender' AS gender,
        (data->>'created_at')::BIGINT AS created_at,
        (data->>'updated_at')::BIGINT AS updated_at,
        (data->>'last_sign_in_at')::BIGINT AS last_sign_in_at,
        ARRAY(SELECT jsonb_array_elements_text(data->'phone_numbers')::BIGINT) AS phone_numbers,
        data->>'username' AS username
    FROM jsonb_array_elements(clerk_get_data()) AS data;

    RETURN QUERY SELECT * FROM clerk_users_temp;

EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Error: %', SQLERRM;
END;
$$;

-- You should consider making it a materialized view
CREATE OR REPLACE VIEW clerk_users AS
SELECT * FROM clerk_return_temp_table();

SELECT * FROM clerk_users;