Database

Clerk

Clerk is a complete suite of embeddable UIs, flexible APIs, and admin dashboards to authenticate and manage users.

The Clerk Wrapper is a WebAssembly(Wasm) foreign data wrapper which allows you to read data from Clerk for use within your Postgres database.

Available Versions

VersionWasm Package URLChecksum
0.1.0https://github.com/supabase/wrappers/releases/download/wasm_clerk_fdw_v0.1.0/clerk_fdw.wasm613be26b59fa4c074e0b93f0db617fcd7b468d4d02edece0b1f85fdb683ebdc4

Preparation

Before you can query Clerk, you need to enable the Wrappers extension and store your credentials in Postgres.

Enable Wrappers

Make sure the wrappers extension is installed on your database:


_10
create extension if not exists wrappers with schema extensions;

Enable the Clerk Wrapper

Enable the Wasm foreign data wrapper:


_10
create foreign data wrapper wasm_wrapper
_10
handler wasm_fdw_handler
_10
validator wasm_fdw_validator;

Store your credentials (optional)

By default, Postgres stores FDW credentials inside pg_catalog.pg_foreign_server in plain text. Anyone with access to this table will be able to view these credentials. Wrappers is designed to work with Vault, which provides an additional level of security for storing credentials. We recommend using Vault to store your credentials.


_10
-- Save your Clerk API key in Vault and retrieve the `key_id`
_10
insert into vault.secrets (name, secret)
_10
values (
_10
'clerk',
_10
'<Clerk API key>' -- Clerk API key
_10
)
_10
returning key_id;

Connecting to Clerk

We need to provide Postgres with the credentials to access Clerk and any additional options. We can do this using the create server command:


_10
create server clerk_server
_10
foreign data wrapper wasm_wrapper
_10
options (
_10
fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_clerk_fdw_v0.1.0/clerk_fdw.wasm',
_10
fdw_package_name 'supabase:clerk-fdw',
_10
fdw_package_version '0.1.0',
_10
fdw_package_checksum '613be26b59fa4c074e0b93f0db617fcd7b468d4d02edece0b1f85fdb683ebdc4',
_10
api_url 'https://api.clerk.com/v1', -- optional
_10
api_key_id '<key_ID>' -- The Key ID from above.
_10
);

Note the fdw_package_* options are required, which specify the Wasm package metadata. You can get the available package version list from above.

Create a schema

We recommend creating a schema to hold all the foreign tables:


_10
create schema if not exists clerk;

Options

The full list of foreign table options are below:

  • object - Object name in Clerk, required.

Supported objects are listed below:

Object name
allowlist_identifiers
blocklist_identifiers
domains
invitations
jwt_templates
oauth_applications
organizations
organization_invitations
organization_memberships
redirect_urls
saml_connections
users

Entities

Allow-list

This is a list of all identifiers allowed to sign up to an instance.

Ref: Clerk API docs

Operations

ObjectSelectInsertUpdateDeleteTruncate
allowlist_identifiers✅❌❌❌❌

Usage


_14
create foreign table clerk.allowlist_identifiers (
_14
id text,
_14
invitation_id text,
_14
identifier text,
_14
identifier_type text,
_14
instance_id text,
_14
created_at timestamp,
_14
updated_at timestamp,
_14
attrs jsonb
_14
)
_14
server clerk_server
_14
options (
_14
object 'allowlist_identifiers'
_14
);

Notes

  • The attrs column contains additional attributes in JSON format

Block-list

This is a list of all identifiers which are not allowed to access an instance.

Ref: Clerk API docs

Operations

ObjectSelectInsertUpdateDeleteTruncate
blocklist_identifiers✅❌❌❌❌

Usage


_13
create foreign table clerk.blocklist_identifiers (
_13
id text,
_13
identifier text,
_13
identifier_type text,
_13
instance_id text,
_13
created_at timestamp,
_13
updated_at timestamp,
_13
attrs jsonb
_13
)
_13
server clerk_server
_13
options (
_13
object 'blocklist_identifiers'
_13
);

Notes

  • The attrs column contains additional attributes in JSON format

Domains

This is a list of all domains for an instance.

Ref: Clerk API docs

Operations

ObjectSelectInsertUpdateDeleteTruncate
domains✅❌❌❌❌

Usage


_12
create foreign table clerk.domains (
_12
id text,
_12
name text,
_12
is_satellite boolean,
_12
frontend_api_url text,
_12
accounts_portal_url text,
_12
attrs jsonb
_12
)
_12
server clerk_server
_12
options (
_12
object 'domains'
_12
);

Notes

  • The attrs column contains additional attributes in JSON format

Invitations

This is a list of all non-revoked invitations for your application.

Ref: Clerk API docs

Operations

ObjectSelectInsertUpdateDeleteTruncate
invitations✅❌❌❌❌

Usage


_15
create foreign table clerk.invitations (
_15
id text,
_15
email_address text,
_15
url text,
_15
revoked boolean,
_15
status text,
_15
expires_at timestamp,
_15
created_at timestamp,
_15
updated_at timestamp,
_15
attrs jsonb
_15
)
_15
server clerk_server
_15
options (
_15
object 'invitations'
_15
);

Notes

  • The attrs column contains additional attributes in JSON format

JWT Templates

This is a list of all JWT templates.

Ref: Clerk API docs

Operations

ObjectSelectInsertUpdateDeleteTruncate
jwt_templates✅❌❌❌❌

Usage


_15
create foreign table clerk.jwt_templates (
_15
id text,
_15
name text,
_15
lifetime bigint,
_15
allowed_clock_skew bigint,
_15
custom_signing_key boolean,
_15
signing_algorithm text,
_15
created_at timestamp,
_15
updated_at timestamp,
_15
attrs jsonb
_15
)
_15
server clerk_server
_15
options (
_15
object 'jwt_templates'
_15
);

Notes

  • The attrs column contains additional attributes in JSON format

OAuth Applications

This is a list of OAuth applications for an instance.

Ref: Clerk API docs

Operations

ObjectSelectInsertUpdateDeleteTruncate
oauth_applications✅❌❌❌❌

Usage


_15
create foreign table clerk.oauth_applications (
_15
id text,
_15
name text,
_15
instance_id text,
_15
client_id text,
_15
public boolean,
_15
scopes text,
_15
created_at timestamp,
_15
updated_at timestamp,
_15
attrs jsonb
_15
)
_15
server clerk_server
_15
options (
_15
object 'oauth_applications'
_15
);

Notes

  • The attrs column contains additional attributes in JSON format

Organizations

This is a list of organizations for an instance.

Ref: Clerk API docs

Operations

ObjectSelectInsertUpdateDeleteTruncate
organizations✅❌❌❌❌

Usage


_12
create foreign table clerk.organizations (
_12
id text,
_12
name text,
_12
slug text,
_12
created_at timestamp,
_12
updated_at timestamp,
_12
attrs jsonb
_12
)
_12
server clerk_server
_12
options (
_12
object 'organizations'
_12
);

Notes

  • The attrs column contains additional attributes in JSON format

Organization Invitations

This is a list of organization invitations for an instance.

Ref: Clerk API docs

Operations

ObjectSelectInsertUpdateDeleteTruncate
organization_invitations✅❌❌❌❌

Usage


_15
create foreign table clerk.organization_invitations (
_15
id text,
_15
email_address text,
_15
role text,
_15
role_name text,
_15
organization_id text,
_15
status text,
_15
created_at timestamp,
_15
updated_at timestamp,
_15
attrs jsonb
_15
)
_15
server clerk_server
_15
options (
_15
object 'organization_invitations'
_15
);

Notes

  • The attrs column contains additional attributes in JSON format

Organization Memberships

This is a list of organization user memberships for an instance.

Ref: Clerk API docs

Operations

ObjectSelectInsertUpdateDeleteTruncate
organization_memberships✅❌❌❌❌

Usage


_12
create foreign table clerk.organization_memberships (
_12
id text,
_12
role text,
_12
role_name text,
_12
created_at timestamp,
_12
updated_at timestamp,
_12
attrs jsonb
_12
)
_12
server clerk_server
_12
options (
_12
object 'organization_memberships'
_12
);

Notes

  • The attrs column contains additional attributes in JSON format

Redirect URLs

This is a list of all whitelisted redirect urls for the instance.

Ref: Clerk API docs

Operations

ObjectSelectInsertUpdateDeleteTruncate
redirect_urls✅❌❌❌❌

Usage


_11
create foreign table clerk.redirect_urls (
_11
id text,
_11
url text,
_11
created_at timestamp,
_11
updated_at timestamp,
_11
attrs jsonb
_11
)
_11
server clerk_server
_11
options (
_11
object 'redirect_urls'
_11
);

Notes

  • The attrs column contains additional attributes in JSON format

SAML Connections

This is a list of SAML Connections for an instance.

Ref: Clerk API docs

Operations

ObjectSelectInsertUpdateDeleteTruncate
saml_connections✅❌❌❌❌

Usage


_15
create foreign table clerk.saml_connections (
_15
id text,
_15
name text,
_15
domain text,
_15
active boolean,
_15
provider text,
_15
user_count bigint,
_15
created_at timestamp,
_15
updated_at timestamp,
_15
attrs jsonb
_15
)
_15
server clerk_server
_15
options (
_15
object 'saml_connections'
_15
);

Notes

  • The attrs column contains additional attributes in JSON format

Users

This is a list of all users.

Ref: Clerk API docs

Operations

ObjectSelectInsertUpdateDeleteTruncate
users✅❌❌❌❌

Usage


_14
create foreign table clerk.users (
_14
id text,
_14
external_id text,
_14
username text,
_14
first_name text,
_14
last_name text,
_14
created_at timestamp,
_14
updated_at timestamp,
_14
attrs jsonb
_14
)
_14
server clerk_server
_14
options (
_14
object 'users'
_14
);

Notes

  • The attrs column contains additional attributes in JSON format

Query Pushdown Support

This FDW doesn't support query pushdown.

Supported Data Types

Postgres Data TypeClerk Data Type
booleanBoolean
bigintNumber
double precisionNumber
textString
timestampTime
jsonbJson

The Clerk API uses JSON formatted data, please refer to Clerk Backend API docs for more details.

Limitations

This section describes important limitations and considerations when using this FDW:

  • Large result sets may experience slower performance due to full data transfer requirement
  • Query pushdown is not supported
  • Materialized views using these foreign tables may fail during logical backups

Examples

Below are some examples on how to use Clerk foreign tables.

Basic example

This example will create a "foreign table" inside your Postgres database and query its data.


_17
create foreign table clerk.users (
_17
id text,
_17
external_id text,
_17
username text,
_17
first_name text,
_17
last_name text,
_17
created_at timestamp,
_17
updated_at timestamp,
_17
attrs jsonb
_17
)
_17
server clerk_server
_17
options (
_17
object 'users'
_17
);
_17
_17
-- query all users
_17
select * from clerk.users;

attrs is a special column which stores all the object attributes in JSON format, you can extract any attributes needed from it. See more examples below.

Query JSON attributes


_10
-- extract all email addresses from user
_10
select
_10
u.id,
_10
e->>'email_address' as email
_10
from clerk.users u
_10
cross join json_array_elements((attrs->'email_addresses')::json) e;