Orb

Orb is a metering and pricing platform built to support usage-based billing models.

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

Available Versions#

Preparation#

Before you can query Orb, 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:

1
create extension if not exists wrappers with schema extensions;

Enable the Orb Wrapper#

Enable the Wasm foreign data wrapper:

1
create foreign data wrapper wasm_wrapper
2
  handler wasm_fdw_handler
3
  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.

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

Connecting to Orb#

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

1
create server orb_server
2
  foreign data wrapper wasm_wrapper
3
  options (
4
    fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_orb_fdw_v0.1.0/orb_fdw.wasm',
5
    fdw_package_name 'supabase:orb-fdw',
6
    fdw_package_version '0.1.0',
7
    fdw_package_checksum '89153a0a570257c231b78561cc909766731c828324585a5b6e2aa553902cb73a',
8
    api_url 'https://api.withorb.com/v1',  -- optional
9
    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:

1
create schema if not exists orb;

Options#

The full list of foreign table options are below:

• object - Object name in Orb, required.

Supported objects are listed below:

• rowid_column - Primary key column name, optional for data scan, required for data modify

Entities#

We can use SQL import foreign schema to import foreign table definitions from Orb.

For example, using below SQL can automatically create foreign tables in the orb schema.

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

Alert#

This is a list of all alerts within Orb.

Ref: Orb API docs

Operations#

Usage#

1
create foreign table orb.alerts (
2
  id text,
3
  type text,
4
  enabled boolean,
5
  customer_id text,
6
  external_customer_id text,
7
  subscription_id text,
8
  created_at timestamp,
9
  attrs jsonb
10
)
11
  server orb_server
12
  options (
13
    object 'alerts'
14
  );

Notes#

• The attrs column contains additional attributes in JSON format
• The query must specify one of customer_id, external_customer_id, or subscription_id

Coupon#

This is a list of all coupons for an account.

Ref: Orb API docs

Operations#

Usage#

1
create foreign table orb.coupons (
2
  id text,
3
  redemption_code text,
4
  times_redeemed bigint,
5
  duration_in_months bigint,
6
  archived_at timestamp,
7
  attrs jsonb
8
)
9
  server orb_server
10
  options (
11
    object 'coupons'
12
  );

Notes#

• The attrs column contains additional attributes in JSON format

Credit Note#

This is a list of all CreditNotes.

Ref: Orb API docs

Operations#

Usage#

1
create foreign table orb.credit_notes (
2
  id text,
3
  type text,
4
  total numeric(18,2),
5
  created_at timestamp,
6
  attrs jsonb
7
)
8
  server orb_server
9
  options (
10
    object 'credit_notes',
11
    rowid_column 'id'
12
  );

Notes#

• The attrs column contains additional attributes in JSON format

Customer#

This is a list of all customers for an account.

Ref: Orb API docs

Operations#

Usage#

1
create foreign table orb.customers (
2
  id text,
3
  name text,
4
  email text,
5
  created_at timestamp,
6
  auto_collection boolean,
7
  attrs jsonb
8
)
9
  server orb_server
10
  options (
11
    object 'customers',
12
    rowid_column 'id'
13
  );

Notes#

• The attrs column contains additional attributes in JSON format

Credit#

This is a list of unexpired, non-zero credit blocks for a customer.

Ref: Orb API docs

Operations#

Usage#

1
create foreign table orb.credits (
2
  id text,
3
  customer_id text,
4
  external_customer_id text,
5
  balance numeric(18,2),
6
  status text,
7
  effective_date timestamp,
8
  expiry_date timestamp,
9
  attrs jsonb
10
)
11
  server orb_server
12
  options (
13
    object 'credits'
14
  );

Notes#

• The attrs column contains additional attributes in JSON format
• The query must specify one of customer_id or external_customer_id

Credits ledger#

This is a list of actions that have taken place to modify a customer’s credit balance.

Ref: Orb API docs

Operations#

Usage#

1
create foreign table orb.credits_ledger (
2
  id text,
3
  customer_id text,
4
  external_customer_id text,
5
  amount numeric(18,2),
6
  created_at timestamp,
7
  attrs jsonb
8
)
9
  server orb_server
10
  options (
11
    object 'credits/ledger'
12
  );

Notes#

• The attrs column contains additional attributes in JSON format
• The query must specify one of customer_id or external_customer_id

Dimensional Price Group#

This is a list of dimensional price groups.

Ref: Orb API docs

Operations#

Usage#

1
create foreign table orb.dimensional_price_groups (
2
  id text,
3
  name text,
4
  attrs jsonb
5
)
6
  server orb_server
7
  options (
8
    object 'dimensional_price_groups',
9
    rowid_column 'id'
10
  );

Notes#

• The attrs column contains additional attributes in JSON format

Event Backfill#

This is a list of all event backfills.

Ref: Orb API docs

Operations#

Usage#

1
create foreign table orb.events_backfills (
2
  id text,
3
  status text,
4
  events_ingested bigint,
5
  created_at timestamp,
6
  attrs jsonb
7
)
8
  server orb_server
9
  options (
10
    object 'events/backfills',
11
    rowid_column 'id'
12
  );

Notes#

• The attrs column contains additional attributes in JSON format

Event Volume#

This returns the event volume for an account.

Ref: Orb API docs

Operations#

Usage#

1
create foreign table orb.events_volume (
2
  count bigint,
3
  timeframe_start timestamp,
4
  timeframe_end timestamp,
5
  attrs jsonb
6
)
7
  server orb_server
8
  options (
9
    object 'events/volume'
10
  );

Notes#

• The attrs column contains additional attributes in JSON format

Invoice#

This is a list of invoices for an account.

Ref: Orb API docs

Operations#

Usage#

1
create foreign table orb.invoices (
2
  id text,
3
  invoice_number text,
4
  customer_id text,
5
  external_customer_id text,
6
  subscription_id text,
7
  status text,
8
  amount_due numeric(18,2),
9
  currency text,
10
  due_date timestamp,
11
  issued_at timestamp,
12
  created_at timestamp,
13
  attrs jsonb
14
)
15
  server orb_server
16
  options (
17
    object 'invoices',
18
    rowid_column 'id'
19
  );

Notes#

• The attrs column contains additional attributes in JSON format

Item#

This is a list of all items.

Ref: Orb API docs

Operations#

Usage#

1
create foreign table orb.items (
2
  id text,
3
  name text,
4
  created_at timestamp,
5
  attrs jsonb
6
)
7
  server orb_server
8
  options (
9
    object 'items',
10
    rowid_column 'id'
11
  );

Notes#

• The attrs column contains additional attributes in JSON format

Metric#

This is a list of metric details.

Ref: Orb API docs

Operations#

Usage#

1
create foreign table orb.metrics (
2
  id text,
3
  name text,
4
  description text,
5
  status text,
6
  created_at timestamp,
7
  attrs jsonb
8
)
9
  server orb_server
10
  options (
11
    object 'metrics',
12
    rowid_column 'id'
13
  );

Notes#

• The attrs column contains additional attributes in JSON format

Plan#

This is a list of all plans for an account.

Ref: Orb API docs

Operations#

Usage#

1
create foreign table orb.plans (
2
  id text,
3
  name text,
4
  description text,
5
  status text,
6
  maximum_amount numeric(18,2),
7
  minimum_amount numeric(18,2),
8
  created_at timestamp,
9
  attrs jsonb
10
)
11
  server orb_server
12
  options (
13
    object 'plans',
14
    rowid_column 'id'
15
  );

Notes#

• The attrs column contains additional attributes in JSON format

Price#

This is a list of all add-on prices.

Ref: Orb API docs

Operations#

Usage#

1
create foreign table orb.prices (
2
  id text,
3
  name text,
4
  external_price_id text,
5
  price_type text,
6
  maximum_amount numeric(18,2),
7
  minimum_amount numeric(18,2),
8
  created_at timestamp,
9
  attrs jsonb
10
)
11
  server orb_server
12
  options (
13
    object 'prices',
14
    rowid_column 'id'
15
  );

Notes#

• The attrs column contains additional attributes in JSON format

Subscription#

This is a list of all subscriptions for an account.

Ref: Orb API docs

Operations#

Usage#

1
create foreign table orb.subscriptions (
2
  id text,
3
  customer_id text,
4
  external_customer_id text,
5
  billing_cycle_day bigint,
6
  status text,
7
  start_date timestamp,
8
  end_date timestamp,
9
  created_at timestamp,
10
  attrs jsonb
11
)
12
  server orb_server
13
  options (
14
    object 'subscriptions',
15
    rowid_column 'id'
16
  );

Notes#

• The attrs column contains additional attributes in JSON format

Query Pushdown Support#

where clause pushdown#

This FDW supports where id = 'xxx' clause pushdown for below objects:

• Coupon
• Credit Note
• Customer
• Dimensional Price Group
• Event Backfill
• Invoice
• Item
• Metric
• Plan
• Price
• Subscription

Some other supported where clauses pushdown are listed below:

Alert#

For example, where customer_id = 'WmUkxWmvLvvXHaNV'.

• customer_id, (operations: =)
• external_customer_id, (operations: =)
• subscription_id, (operations: =)

Customer#

For example, where created_at >= '2025-02-15T10:25:36'.

• created_at, (operations: <, <=, >, >=)

Event Volume#

For example, where timeframe_start = '2025-02-15'.

• timeframe_start, (operations: =)

Invoice#

For example, where status = 'paid'.

• customer_id, (operations: =)
• external_customer_id, (operations: =)
• subscription_id, (operations: =)
• status, (operations: =)
• due_date, (operations: =, <, >)
• created_at, (operations: <, <=, >, >=)

Subscription#

For example, where status = 'active'.

• customer_id, (operations: =)
• external_customer_id, (operations: =)
• status, (operations: =)
• created_at, (operations: <, <=, >, >=)

limit clause pushdown#

This FDW supports limit clause pushdown for all the objects. For example,

1
select * from orb.customers limit 200;

Supported Data Types#

The Orb API uses JSON formatted data, please refer to Orb 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
• Materialized views using these foreign tables may fail during logical backups

Examples#

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

Basic example#

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

1
create foreign table orb.customers (
2
  id text,
3
  name text,
4
  email text,
5
  created_at timestamp,
6
  auto_collection boolean,
7
  attrs jsonb
8
)
9
  server orb_server
10
  options (
11
    object 'customers',
12
    rowid_column 'id'
13
  );
14
15
-- query all customers
16
select * from orb.customers;
17
18
-- you can use `limit` clause to reduce query time if customer number is large
19
select * from orb.customers limit 200;

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#

1
create foreign table orb.invoices (
2
  id text,
3
  invoice_number text,
4
  customer_id text,
5
  external_customer_id text,
6
  subscription_id text,
7
  status text,
8
  amount_due numeric(18,2),
9
  currency text,
10
  due_date timestamp,
11
  issued_at timestamp,
12
  created_at timestamp,
13
  attrs jsonb
14
)
15
  server orb_server
16
  options (
17
    object 'invoices',
18
    rowid_column 'id'
19
  );
20
21
-- extract all line items from an invoice
22
select
23
  i.id,
24
  li->>'name' as line_item_name,
25
  li->>'quantity' as line_item_quantity,
26
  li->>'subtotal' as line_item_subtotal
27
from orb.invoices i
28
  cross join json_array_elements((attrs->'line_items')::json) li
29
where
30
  i.id = 'PsEhbLd88auyhZ8F';

Data Modify Example#

This example will modify data in a "foreign table" inside your Postgres database, note that rowid_column foreign table option is mandatory for data modify. Data modify is done through the attrs jsonb column, which will be posted as request body to Orb API endpoint. Please refer to Orb API reference docs for the JSON request details.

1
-- create a new customer
2
insert into orb.customers(attrs)
3
values (
4
  '{
5
    "name": "John Doe",
6
    "email": "test@test.com"
7
  }'::jsonb
8
);
9
10
-- update the existing customer
11
update orb.customers
12
set attrs = '{
13
  "name": "Jane Smith",
14
  "billing_address": {
15
    "city": "New York",
16
    "country": "US"
17
  }
18
}'::jsonb
19
where id = 'n6DaYELQYubChJWf';
20
21
-- delete a customer
22
delete from orb.customers
23
where id = 'n6DaYELQYubChJWf';