Stripe
Stripe is an API driven online payment processing utility. supabase/wrappers exposes below endpoints.
Preparation#
Before you get started, make sure the wrappers extension is installed on your database:
_10create extension if not exists wrappers;
and then create the foreign data wrapper:
_10create foreign data wrapper stripe_wrapper_10 handler stripe_fdw_handler_10 validator stripe_fdw_validator;
Secure 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 Stripe API key in Vault and retrieve the `key_id`_10insert into vault.secrets (name, secret)_10values (_10 'stripe',_10 'YOUR_SECRET'_10)_10returning key_id;
Connecting to Stripe#
We need to provide Postgres with the credentials to connect to Stripe, and any additional options. We can do this using the create server command:
_10create server stripe_server_10 foreign data wrapper stripe_wrapper_10 options (_10 api_key_id '<key_ID>' -- The Key ID from above._10 );
Creating Foreign Tables#
The Stripe Wrapper supports data read and modify from Stripe API.
| Object | Select | Insert | Update | Delete | Truncate |
|---|---|---|---|---|---|
| Accounts | ✅ | ❌ | ❌ | ❌ | ❌ |
| Balance | ✅ | ❌ | ❌ | ❌ | ❌ |
| Balance Transactions | ✅ | ❌ | ❌ | ❌ | ❌ |
| Charges | ✅ | ❌ | ❌ | ❌ | ❌ |
| Checkout Sessions | ✅ | ❌ | ❌ | ❌ | ❌ |
| Customers | ✅ | ✅ | ✅ | ✅ | ❌ |
| Disputes | ✅ | ❌ | ❌ | ❌ | ❌ |
| Events | ✅ | ❌ | ❌ | ❌ | ❌ |
| Files | ✅ | ❌ | ❌ | ❌ | ❌ |
| File Links | ✅ | ❌ | ❌ | ❌ | ❌ |
| Invoices | ✅ | ❌ | ❌ | ❌ | ❌ |
| Mandates | ✅ | ❌ | ❌ | ❌ | ❌ |
| PaymentIntents | ✅ | ❌ | ❌ | ❌ | ❌ |
| Payouts | ✅ | ❌ | ❌ | ❌ | ❌ |
| Prices | ✅ | ❌ | ❌ | ❌ | ❌ |
| Products | ✅ | ✅ | ✅ | ✅ | ❌ |
| Refunds | ✅ | ❌ | ❌ | ❌ | ❌ |
| SetupAttempts | ✅ | ❌ | ❌ | ❌ | ❌ |
| SetupIntents | ✅ | ❌ | ❌ | ❌ | ❌ |
| Subscriptions | ✅ | ✅ | ✅ | ✅ | ❌ |
| Tokens | ✅ | ❌ | ❌ | ❌ | ❌ |
| Topups | ✅ | ❌ | ❌ | ❌ | ❌ |
| Transfers | ✅ | ❌ | ❌ | ❌ | ❌ |
The Stripe foreign tables mirror Stripe's API. We can create a schema to hold all the Stripe tables.
_10create schema stripe;
Then create the foreign table, for example:
_13create foreign table stripe.accounts (_13 id text,_13 business_type text,_13 country text,_13 email text,_13 type text,_13 created timestamp,_13 attrs jsonb_13)_13 server stripe_server_13 options (_13 object 'accounts'_13 );
attrs is a special column which stores all the object attributes in JSON format, you can extract any attributes needed or its associated sub objects from it. See more examples below.
Accounts#
read only
This is an object representing a Stripe account.
Ref: Stripe docs
_13create foreign table stripe.accounts (_13 id text,_13 business_type text,_13 country text,_13 email text,_13 type text,_13 created timestamp,_13 attrs jsonb_13)_13 server stripe_server_13 options (_13 object 'accounts'_13 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
Balance#
read only
Shows the balance currently on your Stripe account.
Ref: Stripe docs
_10create foreign table stripe.balance (_10 balance_type text,_10 amount bigint,_10 currency text,_10 attrs jsonb_10)_10 server stripe_server_10 options (_10 object 'balance'_10 );
Balance Transactions#
read only
Balance transactions represent funds moving through your Stripe account. They're created for every type of transaction that comes into or flows out of your Stripe account balance.
Ref: Stripe docs
_16create foreign table stripe.balance_transactions (_16 id text,_16 amount bigint,_16 currency text,_16 description text,_16 fee bigint,_16 net bigint,_16 status text,_16 type text,_16 created timestamp,_16 attrs jsonb_16)_16 server stripe_server_16 options (_16 object 'balance_transactions'_16 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- type
Charges#
read only
To charge a credit or a debit card, you create a Charge object. You can retrieve and refund individual charges as well as list all charges. Charges are identified by a unique, random ID.
Ref: Stripe docs
_16create foreign table stripe.charges (_16 id text,_16 amount bigint,_16 currency text,_16 customer text,_16 description text,_16 invoice text,_16 payment_intent text,_16 status text,_16 created timestamp,_16 attrs jsonb_16)_16 server stripe_server_16 options (_16 object 'charges'_16 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- customer
Checkout Sessions#
read only
A Checkout Session represents your customer's session as they pay for one-time purchases or subscriptions through Checkout or Payment Links. We recommend creating a new Session each time your customer attempts to pay.
Ref: Stripe docs
_12create foreign table stripe.checkout_sessions (_12 id text,_12 customer text,_12 payment_intent text,_12 subscription text,_12 attrs jsonb_12)_12 server stripe_server_12 options (_12 object 'checkout/sessions',_12 rowid_column 'id'_12 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- customer
- payment_intent
- subscription
Customers#
read and modify
Contains customers known to Stripe.
Ref: Stripe docs
_13create foreign table stripe.customers (_13 id text,_13 email text,_13 name text,_13 description text,_13 created timestamp,_13 attrs jsonb_13)_13 server stripe_server_13 options (_13 object 'customers',_13 rowid_column 'id'_13 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
Disputes#
read only
A dispute occurs when a customer questions your charge with their card issuer.
Ref: Stripe docs
_15create foreign table stripe.disputes (_15 id text,_15 amount bigint,_15 currency text,_15 charge text,_15 payment_intent text,_15 reason text,_15 status text,_15 created timestamp,_15 attrs jsonb_15)_15 server stripe_server_15 options (_15 object 'disputes'_15 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- charge
- payment_intent
Events#
read only
Events are our way of letting you know when something interesting happens in your account.
Ref: Stripe docs
_11create foreign table stripe.events (_11 id text,_11 type text,_11 api_version text,_11 created timestamp,_11 attrs jsonb_11)_11 server stripe_server_11 options (_11 object 'events'_11 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- type
Files#
read only
This is an object representing a file hosted on Stripe's servers.
Ref: Stripe docs
_16create foreign table stripe.files (_16 id text,_16 filename text,_16 purpose text,_16 title text,_16 size bigint,_16 type text,_16 url text,_16 created timestamp,_16 expires_at timestamp,_16 attrs jsonb_16)_16 server stripe_server_16 options (_16 object 'files'_16 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- purpose
File Links#
read only
To share the contents of a File object with non-Stripe users, you can create a FileLink.
Ref: Stripe docs
_13create foreign table stripe.file_links (_13 id text,_13 file text,_13 url text,_13 created timestamp,_13 expired bool,_13 expires_at timestamp,_13 attrs jsonb_13)_13 server stripe_server_13 options (_13 object 'file_links'_13 );
Invoices#
read only
Invoices are statements of amounts owed by a customer, and are either generated one-off, or generated periodically from a subscription.
Ref: Stripe docs
_15create foreign table stripe.invoices (_15 id text,_15 customer text,_15 subscription text,_15 status text,_15 total bigint,_15 currency text,_15 period_start timestamp,_15 period_end timestamp,_15 attrs jsonb_15)_15 server stripe_server_15 options (_15 object 'invoices'_15 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- customer
- status
- subscription
Mandates#
read only
A Mandate is a record of the permission a customer has given you to debit their payment method.
Ref: Stripe docs
_11create foreign table stripe.mandates (_11 id text,_11 payment_method text,_11 status text,_11 type text,_11 attrs jsonb_11)_11 server stripe_server_11 options (_11 object 'mandates'_11 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
Payment Intents#
read only
A payment intent guides you through the process of collecting a payment from your customer.
Ref: Stripe docs
_13create foreign table stripe.payment_intents (_13 id text,_13 customer text,_13 amount bigint,_13 currency text,_13 payment_method text,_13 created timestamp,_13 attrs jsonb_13)_13 server stripe_server_13 options (_13 object 'payment_intents'_13 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- customer
Payouts#
read only
A Payout object is created when you receive funds from Stripe, or when you initiate a payout to either a bank account or debit card of a connected Stripe account.
Ref: Stripe docs
_15create foreign table stripe.payouts (_15 id text,_15 amount bigint,_15 currency text,_15 arrival_date timestamp,_15 description text,_15 statement_descriptor text,_15 status text,_15 created timestamp,_15 attrs jsonb_15)_15 server stripe_server_15 options (_15 object 'payouts'_15 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- status
Prices#
read only
A Price object is needed for all of your products to facilitate multiple currencies and pricing options.
Ref: Stripe docs
_14create foreign table stripe.prices (_14 id text,_14 active bool,_14 currency text,_14 product text,_14 unit_amount bigint,_14 type text,_14 created timestamp,_14 attrs jsonb_14)_14 server stripe_server_14 options (_14 object 'prices'_14 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- active
Products#
read and modify
All products available in Stripe.
Ref: Stripe docs
_15create foreign table stripe.products (_15 id text,_15 name text,_15 active bool,_15 default_price text,_15 description text,_15 created timestamp,_15 updated timestamp,_15 attrs jsonb_15)_15 server stripe_server_15 options (_15 object 'products',_15 rowid_column 'id'_15 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- active
Refunds#
read only
Refund objects allow you to refund a charge that has previously been created but not yet refunded.
Ref: Stripe docs
_15create foreign table stripe.refunds (_15 id text,_15 amount bigint,_15 currency text,_15 charge text,_15 payment_intent text,_15 reason text,_15 status text,_15 created timestamp,_15 attrs jsonb_15)_15 server stripe_server_15 options (_15 object 'refunds'_15 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- charge
- payment_intent
SetupAttempts#
read only
A SetupAttempt describes one attempted confirmation of a SetupIntent, whether that confirmation was successful or unsuccessful.
Ref: Stripe docs
_16create foreign table stripe.setup_attempts (_16 id text,_16 application text,_16 customer text,_16 on_behalf_of text,_16 payment_method text,_16 setup_intent text,_16 status text,_16 usage text,_16 created timestamp,_16 attrs jsonb_16)_16 server stripe_server_16 options (_16 object 'setup_attempts'_16 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- setup_intent
SetupIntents#
read only
A SetupIntent guides you through the process of setting up and saving a customer's payment credentials for future payments.
Ref: Stripe docs
_15create foreign table stripe.setup_intents (_15 id text,_15 client_secret text,_15 customer text,_15 description text,_15 payment_method text,_15 status text,_15 usage text,_15 created timestamp,_15 attrs jsonb_15)_15 server stripe_server_15 options (_15 object 'setup_intents'_15 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- customer
- payment_method
Subscriptions#
read and modify
Customer recurring payment schedules.
Ref: Stripe docs
_13create foreign table stripe.subscriptions (_13 id text,_13 customer text,_13 currency text,_13 current_period_start timestamp,_13 current_period_end timestamp,_13 attrs jsonb_13)_13 server stripe_server_13 options (_13 object 'subscriptions',_13 rowid_column 'id'_13 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- customer
- price
- status
Tokens#
read only
Tokenization is the process Stripe uses to collect sensitive card or bank account details, or personally identifiable information (PII), directly from your customers in a secure manner.
Ref: Stripe docs
_12create foreign table stripe.tokens (_12 id text,_12 customer text,_12 currency text,_12 current_period_start timestamp,_12 current_period_end timestamp,_12 attrs jsonb_12)_12 server stripe_server_12 options (_12 object 'tokens'_12 );
Top-ups#
read only
To top up your Stripe balance, you create a top-up object.
Ref: Stripe docs
_13create foreign table stripe.topups (_13 id text,_13 amount bigint,_13 currency text,_13 description text,_13 status text,_13 created timestamp,_13 attrs jsonb_13)_13 server stripe_server_13 options (_13 object 'topups'_13 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- status
Transfers#
read only
A Transfer object is created when you move funds between Stripe accounts as part of Connect.
Ref: Stripe docs
_13create foreign table stripe.transfers (_13 id text,_13 amount bigint,_13 currency text,_13 description text,_13 destination text,_13 created timestamp,_13 attrs jsonb_13)_13 server stripe_server_13 options (_13 object 'transfers'_13 );
While any column is allowed in a where clause, it is most efficient to filter by:
- id
- destination
Limitations#
Joining local data against a Stripe FDW returns incomplete or empty results. We're tracking the issue on GitHub and working towards a fix.
Examples#
Some examples on how to use Stripe foreign tables.
Basic example#
_10-- always limit records to reduce API calls to Stripe_10select * from stripe.customers limit 10;_10select * from stripe.invoices limit 10;_10select * from stripe.subscriptions limit 10;
Query JSON attributes#
_11-- extract account name for an invoice_11select id, attrs->>'account_name' as account_name_11from stripe.invoices where id = 'in_xxx';_11_11-- extract invoice line items for an invoice_11select id, attrs#>'{lines,data}' as line_items_11from stripe.invoices where id = 'in_xxx';_11_11-- extract subscription items for a subscription_11select id, attrs#>'{items,data}' as items_11from stripe.subscriptions where id = 'sub_xxx';
Data modify#
_10insert into stripe.customers(email,name,description) values ('test@test.com', 'test name', null);_10update stripe.customers set description='hello fdw' where id ='cus_xxx';_10update stripe.customers set attrs='{"metadata[foo]": "bar"}' where id ='cus_xxx';_10delete from stripe.customers where id ='cus_xxx';