Clerk

1
create extension if not exists wrappers with schema extensions;

1
create foreign data wrapper wasm_wrapper
2
  handler wasm_fdw_handler
3
  validator wasm_fdw_validator;

1
-- Save your Clerk API key in Vault and retrieve the created `key_id`
2
select vault.create_secret(
3
  '<Clerk API key>', -- Clerk API key
4
  'clerk',
5
  'Clerk API key for Wrappers'
6
);

1
create schema if not exists clerk;

1
-- create all the foreign tables
2
import foreign schema clerk from server clerk_server into clerk;
3
4
-- or, create selected tables only
5
import foreign schema clerk
6
   limit to ("users", "organizations")
7
   from server clerk_server into clerk;
8
9
-- or, create all foreign tables except selected tables
10
import foreign schema clerk
11
   except ("users")
12
   from server clerk_server into clerk;

1
create foreign table clerk.allowlist_identifiers (
2
  id text,
3
  invitation_id text,
4
  identifier text,
5
  identifier_type text,
6
  instance_id text,
7
  created_at timestamp,
8
  updated_at timestamp,
9
  attrs jsonb
10
)
11
  server clerk_server
12
  options (
13
    object 'allowlist_identifiers',
14
    rowid_column 'id'
15
  );

1
create foreign table clerk.blocklist_identifiers (
2
  id text,
3
  identifier text,
4
  identifier_type text,
5
  instance_id text,
6
  created_at timestamp,
7
  updated_at timestamp,
8
  attrs jsonb
9
)
10
  server clerk_server
11
  options (
12
    object 'blocklist_identifiers',
13
    rowid_column 'id'
14
  );

1
create foreign table clerk.domains (
2
  id text,
3
  name text,
4
  is_satellite boolean,
5
  frontend_api_url text,
6
  accounts_portal_url text,
7
  attrs jsonb
8
)
9
  server clerk_server
10
  options (
11
    object 'domains',
12
    rowid_column 'id'
13
  );

1
create foreign table clerk.invitations (
2
  id text,
3
  email_address text,
4
  url text,
5
  revoked boolean,
6
  status text,
7
  expires_at timestamp,
8
  created_at timestamp,
9
  updated_at timestamp,
10
  attrs jsonb
11
)
12
  server clerk_server
13
  options (
14
    object 'invitations',
15
    rowid_column 'id'
16
  );

1
create foreign table clerk.jwt_templates (
2
  id text,
3
  name text,
4
  lifetime bigint,
5
  allowed_clock_skew bigint,
6
  custom_signing_key boolean,
7
  signing_algorithm text,
8
  created_at timestamp,
9
  updated_at timestamp,
10
  attrs jsonb
11
)
12
  server clerk_server
13
  options (
14
    object 'jwt_templates',
15
    rowid_column 'id'
16
  );

1
create foreign table clerk.oauth_applications (
2
  id text,
3
  name text,
4
  instance_id text,
5
  client_id text,
6
  public boolean,
7
  scopes text,
8
  created_at timestamp,
9
  updated_at timestamp,
10
  attrs jsonb
11
)
12
  server clerk_server
13
  options (
14
    object 'oauth_applications',
15
    rowid_column 'id'
16
  );

1
create foreign table clerk.organizations (
2
  id text,
3
  name text,
4
  slug text,
5
  created_at timestamp,
6
  updated_at timestamp,
7
  attrs jsonb
8
)
9
  server clerk_server
10
  options (
11
    object 'organizations',
12
    rowid_column 'id'
13
  );

1
create foreign table clerk.organization_invitations (
2
  id text,
3
  email_address text,
4
  role text,
5
  role_name text,
6
  organization_id text,
7
  status text,
8
  created_at timestamp,
9
  updated_at timestamp,
10
  attrs jsonb
11
)
12
  server clerk_server
13
  options (
14
    object 'organization_invitations'
15
  );

1
create foreign table clerk.organization_memberships (
2
  id text,
3
  role text,
4
  role_name text,
5
  created_at timestamp,
6
  updated_at timestamp,
7
  attrs jsonb
8
)
9
  server clerk_server
10
  options (
11
    object 'organization_memberships'
12
  );

1
create foreign table clerk.redirect_urls (
2
  id text,
3
  url text,
4
  created_at timestamp,
5
  updated_at timestamp,
6
  attrs jsonb
7
)
8
  server clerk_server
9
  options (
10
    object 'redirect_urls',
11
    rowid_column 'id'
12
  );

1
create foreign table clerk.saml_connections (
2
  id text,
3
  name text,
4
  domain text,
5
  active boolean,
6
  provider text,
7
  user_count bigint,
8
  created_at timestamp,
9
  updated_at timestamp,
10
  attrs jsonb
11
)
12
  server clerk_server
13
  options (
14
    object 'saml_connections',
15
    rowid_column 'id'
16
  );

1
create foreign table clerk.users (
2
  id text,
3
  external_id text,
4
  username text,
5
  first_name text,
6
  last_name text,
7
  created_at timestamp,
8
  updated_at timestamp,
9
  attrs jsonb
10
)
11
  server clerk_server
12
  options (
13
    object 'users',
14
    rowid_column 'id'
15
  );

1
create foreign table clerk.user_billing_subscriptions (
2
  user_id text,
3
  id text,
4
  status text,
5
  payer_id text,
6
  created_at timestamp,
7
  updated_at timestamp,
8
  attrs jsonb
9
)
10
  server clerk_server
11
  options (
12
    object 'users/billing/subscription'
13
  );

1
create foreign table clerk.organization_billing_subscriptions (
2
  organization_id text,
3
  id text,
4
  status text,
5
  payer_id text,
6
  created_at timestamp,
7
  updated_at timestamp,
8
  attrs jsonb
9
)
10
  server clerk_server
11
  options (
12
    object 'organizations/billing/subscription'
13
  );

1
create foreign table clerk.billing_plans (
2
  id text,
3
  name text,
4
  description text,
5
  slug text,
6
  is_default boolean,
7
  is_recurring boolean,
8
  attrs jsonb
9
)
10
  server clerk_server
11
  options (
12
    object 'billing/plans'
13
  );

1
create foreign table clerk.billing_subscription_items (
2
  id text,
3
  status text,
4
  plan_id text,
5
  plan_period text,
6
  payer_id text,
7
  is_free_trial boolean,
8
  created_at timestamp,
9
  updated_at timestamp,
10
  attrs jsonb
11
)
12
  server clerk_server
13
  options (
14
    object 'billing/subscription_items'
15
  );

1
create foreign table clerk.billing_statements (
2
  id text,
3
  status text,
4
  timestamp timestamp,
5
  attrs jsonb
6
)
7
  server clerk_server
8
  options (
9
    object 'billing/statements'
10
  );

1
create foreign table clerk.billing_statement (
2
  statement_id text,
3
  id text,
4
  status text,
5
  timestamp timestamp,
6
  attrs jsonb
7
)
8
  server clerk_server
9
  options (
10
    object 'billing/statement'
11
  );

1
create foreign table clerk.billing_payment_attempts (
2
  statement_id text,
3
  id text,
4
  status text,
5
  created_at timestamp,
6
  updated_at timestamp,
7
  attrs jsonb
8
)
9
  server clerk_server
10
  options (
11
    object 'billing/payment_attempts'
12
  );

1
-- Fetches from GET /users/user_xxx (single API call)
2
select * from clerk.users where id = 'user_xxx';

1
-- Fetches from GET /users/{user_id}/billing/subscription
2
select * from clerk.user_billing_subscriptions where user_id = 'user_xxx';

1
create foreign table clerk.users (
2
  id text,
3
  external_id text,
4
  username text,
5
  first_name text,
6
  last_name text,
7
  created_at timestamp,
8
  updated_at timestamp,
9
  attrs jsonb
10
)
11
  server clerk_server
12
  options (
13
    object 'users',
14
    rowid_column 'id'
15
  );
16
17
-- query all users (fetches from GET /users)
18
select * from clerk.users;
19
20
-- retrieve a specific user by ID (fetches from GET /users/{user_id})
21
select * from clerk.users where id = 'user_xxx';

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

1
-- Create a new user
2
INSERT INTO clerk.users (attrs) VALUES ('{"email_address": ["user@example.com"], "password": "secure123"}');
3
4
-- Update a user (requires rowid_column 'id')
5
UPDATE clerk.users SET attrs = '{"first_name": "John", "last_name": "Doe"}' WHERE id = 'user_xxx';
6
7
-- Delete a user (requires rowid_column 'id')
8
DELETE FROM clerk.users WHERE id = 'user_xxx';
9
10
-- Create a new organization
11
INSERT INTO clerk.organizations (attrs) VALUES ('{"name": "My Organization", "slug": "my-org"}');
12
13
-- Update an organization
14
UPDATE clerk.organizations SET attrs = '{"name": "Updated Name"}' WHERE id = 'org_xxx';
15
16
-- Delete an organization
17
DELETE FROM clerk.organizations WHERE id = 'org_xxx';

1
-- Query all billing plans
2
SELECT * FROM clerk.billing_plans;
3
4
-- Query all billing statements
5
SELECT * FROM clerk.billing_statements;
6
7
-- Query all billing subscription items
8
SELECT * FROM clerk.billing_subscription_items;
9
10
-- Query a specific statement (requires WHERE clause)
11
SELECT * FROM clerk.billing_statement WHERE statement_id = 'stmt_xxx';
12
13
-- Query payment attempts for a statement (requires WHERE clause)
14
SELECT * FROM clerk.billing_payment_attempts WHERE statement_id = 'stmt_xxx';
15
16
-- Query subscription for a specific user (requires WHERE clause)
17
SELECT * FROM clerk.user_billing_subscriptions WHERE user_id = 'user_xxx';
18
19
-- Query subscription for a specific organization (requires WHERE clause)
20
SELECT * FROM clerk.organization_billing_subscriptions WHERE organization_id = 'org_xxx';
21
22
-- Retrieve a single user by ID (fetches from GET /users/{user_id})
23
SELECT * FROM clerk.users WHERE id = 'user_xxx';
24
25
-- Retrieve a single organization by ID (fetches from GET /organizations/{organization_id})
26
SELECT * FROM clerk.organizations WHERE id = 'org_xxx';
27
28
-- Retrieve a single JWT template by ID (fetches from GET /jwt_templates/{template_id})
29
SELECT * FROM clerk.jwt_templates WHERE id = 'tmpl_xxx';
30
31
-- Retrieve a single OAuth application by ID (fetches from GET /oauth_applications/{oauth_application_id})
32
SELECT * FROM clerk.oauth_applications WHERE id = 'oauth_xxx';
33
34
-- Retrieve a single SAML connection by ID (fetches from GET /saml_connections/{saml_connection_id})
35
SELECT * FROM clerk.saml_connections WHERE id = 'samlconn_xxx';
36
37
-- Retrieve a single redirect URL by ID (fetches from GET /redirect_urls/{id})
38
SELECT * FROM clerk.redirect_urls WHERE id = 'redir_xxx';