Table: stripe_invoice - Query Stripe Invoices using SQL
Stripe is a payment processing platform that allows businesses to accept payments online and in mobile apps. It provides the technical, fraud prevention, and banking infrastructure required to operate online payment systems. Invoices in Stripe represent a bill for goods or services, and they are often tied to a subscription, but can also be used for one-off billing.
Table Usage Guide
The stripe_invoice
table provides insights into Stripe invoices within the Stripe Payment processing platform. As a financial analyst or business owner, explore invoice-specific details through this table, including amounts, currency, customer details, and payment status. Utilize it to uncover information about invoices, such as those unpaid, partially paid, or fully paid, and the details of the associated customer.
Examples
List invoices
Explore the most recent financial transactions by listing the latest five invoices. This is useful for getting a quick overview of recent billing activity.
select *from stripe_invoicelimit 5;
select *from stripe_invoicelimit 5;
Invoices created in the last 24 hours
Discover the segments that have generated invoices in the last day. This can be used to gain insights into recent billing activity and customer engagement.
select id, customer_name, amount_duefrom stripe_invoicewhere created > current_timestamp - interval '1 day'order by created;
select id, customer_name, amount_duefrom stripe_invoicewhere created > datetime('now', '-1 day')order by created;
Invoices due in a given month
Analyze the settings to understand which invoices are due within a specific month. This can help businesses to manage their cash flow by anticipating incoming payments.
select id, customer_name, amount_duefrom stripe_invoicewhere due_date >= '2021-07-01' and due_date < '2021-08-01';
Error: The corresponding SQLite query is unavailable.
All open invoices
Explore which invoices are still open by identifying the outstanding amounts due and unpaid. This can help in tracking payments and managing financial records effectively.
select id, customer_name, amount_due, amount_paid, amount_remainingfrom stripe_invoicewhere status = 'outstanding'order by created;
select id, customer_name, amount_due, amount_paid, amount_remainingfrom stripe_invoicewhere status = 'outstanding'order by created;
Schema for stripe_invoice
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
account_country | text | The country of the business associated with this invoice, most often the business creating the invoice. | |
account_name | text | The public name of the business associated with this invoice, most often the business creating the invoice. | |
amount_due | bigint | Final amount due at this time for this invoice. If the invoice’s total is smaller than the minimum charge amount, for example, or if there is account credit that can be applied to the invoice, the amount_due may be 0. If there is a positive starting_balance for the invoice (the customer owes money), the amount_due will also take that into account. The charge that gets generated for the invoice will be for the amount specified in amount_due. | |
amount_paid | bigint | The amount, in cents, that was paid. | |
amount_remaining | bigint | The amount remaining, in cents, that is due. | |
application_fee_amount | bigint | The fee in cents that will be applied to the invoice and transferred to the application owner’s Stripe account when the invoice is paid. | |
attempt_count | bigint | Number of payment attempts made for this invoice, from the perspective of the payment retry schedule. Any payment attempt counts as the first attempt, and subsequently only automatic retries increment the attempt count. In other words, manual payment attempts after the first attempt do not affect the retry schedule. | |
attempted | boolean | Whether an attempt has been made to pay the invoice. An invoice is not attempted until 1 hour after the invoice.created webhook, for example, so you might not want to display that invoice as unpaid to your users. | |
auto_advance | boolean | Controls whether Stripe will perform automatic collection of the invoice. When false, the invoice’s state will not automatically advance without an explicit action. | |
billing_reason | text | Indicates the reason why the invoice was created. subscription_cycle indicates an invoice created by a subscription advancing into a new period. subscription_create indicates an invoice created due to creating a subscription. subscription_update indicates an invoice created due to updating a subscription. subscription is set for all old invoices to indicate either a change to a subscription or a period advancement. manual is set for all invoices unrelated to a subscription (for example: created via the invoice editor). The upcoming value is reserved for simulated invoices per the upcoming invoice endpoint. subscription_threshold indicates an invoice created due to a billing threshold being reached. | |
charge | jsonb | ID of the latest charge generated for this invoice, if any. | |
collection_method | text | = | Either charge_automatically, or send_invoice. When charging automatically, Stripe will attempt to pay this invoice using the default source attached to the customer. When sending an invoice, Stripe will email this invoice to the customer with payment instructions. |
created | timestamp with time zone | >, >=, =, <, <= | Time at which the invoice was created. |
currency | text | Three-letter ISO currency code, in lowercase. Must be a supported currency. | |
custom_fields | jsonb | Custom fields displayed on the invoice. | |
customer | jsonb | The ID of the customer who will be billed. | |
customer_address | jsonb | The customer’s address. Until the invoice is finalized, this field will equal customer.address. Once the invoice is finalized, this field will no longer be updated. | |
customer_email | text | The customer’s email. Until the invoice is finalized, this field will equal customer.email. Once the invoice is finalized, this field will no longer be updated. | |
customer_name | text | The customer’s name. Until the invoice is finalized, this field will equal customer.name. Once the invoice is finalized, this field will no longer be updated. | |
customer_phone | text | The customer’s phone number. Until the invoice is finalized, this field will equal customer.phone. Once the invoice is finalized, this field will no longer be updated. | |
customer_shipping | jsonb | The customer’s shipping information. Until the invoice is finalized, this field will equal customer.shipping. Once the invoice is finalized, this field will no longer be updated. | |
customer_tax_exempt | text | The customer’s tax exempt status. Until the invoice is finalized, this field will equal customer.tax_exempt. Once the invoice is finalized, this field will no longer be updated. | |
customer_tax_ids | jsonb | The customer’s tax IDs. Until the invoice is finalized, this field will contain the same tax IDs as customer.tax_ids. Once the invoice is finalized, this field will no longer be updated. | |
default_payment_method | text | ID of the default payment method for the invoice. It must belong to the customer associated with the invoice. If not set, defaults to the subscription’s default payment method, if any, or to the default payment method in the customer’s invoice settings. | |
default_source | text | ID of the default payment source for the invoice. It must belong to the customer associated with the invoice and be in a chargeable state. If not set, defaults to the subscription’s default source, if any, or to the customer’s default source. | |
default_tax_rates | jsonb | The tax rates applied to this invoice, if any. | |
description | text | An arbitrary string attached to the object. Often useful for displaying to users. Referenced as ‘memo’ in the Dashboard. | |
discount | jsonb | Describes the current discount applied to this invoice, if there is one. Not populated if there are multiple discounts. | |
due_date | timestamp with time zone | >, >=, =, <, <= | The date on which payment for this invoice is due. This value will be null for invoices where collection_method=charge_automatically. |
ending_balance | bigint | Ending customer balance after the invoice is finalized. Invoices are finalized approximately an hour after successful webhook delivery or when payment collection is attempted for the invoice. If the invoice has not been finalized yet, this will be null. | |
footer | text | Footer displayed on the invoice. | |
hosted_invoice_url | text | The URL for the hosted invoice page, which allows customers to view and pay an invoice. If the invoice has not been finalized yet, this will be null. | |
id | text | = | Unique identifier for the invoice. |
invoice_pdf | text | The link to download the PDF for the invoice. If the invoice has not been finalized yet, this will be null. | |
lines | jsonb | The individual line items that make up the invoice. lines is sorted as follows: invoice items in reverse chronological order, followed by the subscription, if any. | |
livemode | boolean | Has the value true if the invoice exists in live mode or the value false if the invoice exists in test mode. | |
metadata | jsonb | Set of key-value pairs that you can attach to an invoice. This can be useful for storing additional information about the invoice in a structured format. | |
next_payment_attempt | timestamp with time zone | The time at which payment will next be attempted. This value will be null for invoices where collection_method=send_invoice. | |
number | text | A unique, identifying string that appears on emails sent to the customer for this invoice. This starts with the customer’s unique invoice_prefix if it is specified. | |
paid | boolean | Whether payment was successfully collected for this invoice. An invoice can be paid (most commonly) with a charge or with credit from the customer’s account balance. | |
payment_intent | jsonb | The PaymentIntent associated with this invoice. The PaymentIntent is generated when the invoice is finalized, and can then be used to pay the invoice. Note that voiding an invoice will cancel the PaymentIntent. | |
period_end | timestamp with time zone | End of the usage period during which invoice items were added to this invoice. | |
period_start | timestamp with time zone | Start of the usage period during which invoice items were added to this invoice. | |
post_payment_credit_notes_amount | bigint | Total amount of all post-payment credit notes issued for this invoice. | |
pre_payment_credit_notes_amount | bigint | Total amount of all pre-payment credit notes issued for this invoice. | |
receipt_number | text | This is the transaction number that appears on email receipts sent for this invoice. | |
starting_balance | bigint | Starting customer balance before the invoice is finalized. If the invoice has not been finalized yet, this will be the current customer balance. | |
statement_descriptor | text | Extra information about an invoice for the customer’s credit card statement. | |
status | text | = | The status of the invoice, one of draft, open, paid, uncollectible, or void. |
status_transitions | jsonb | The timestamps at which the invoice status was updated. | |
subscription_id | text | = | ID of the subscription that this invoice was prepared for, if any. |
subscription_proration_date | timestamp with time zone | Only set for upcoming invoices that preview prorations. The time used to calculate prorations. | |
subtotal | bigint | Total of all subscriptions, invoice items, and prorations on the invoice before any invoice level discount or tax is applied. Item discounts are already incorporated | |
tax | bigint | The amount of tax on this invoice. This is the sum of all the tax amounts on this invoice. | |
threshold_reason | jsonb | If billing_reason is set to subscription_threshold this returns more information on which threshold rules triggered the invoice. | |
total | bigint | Total after discounts and taxes. | |
total_tax_amounts | jsonb | The aggregate amounts calculated per tax rate for all line items. | |
transfer_data | jsonb | The account (if any) the payment will be attributed to for tax reporting, and where funds from the payment will be transferred to for the invoice. | |
webhooks_delivered_at | timestamp with time zone | Invoices are automatically paid or sent 1 hour after webhooks are delivered, or until all webhook delivery attempts have been exhausted. This field tracks the time when webhooks for this invoice were successfully delivered. If the invoice had no webhooks to deliver, this will be set while the invoice is being created. |
Export
This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.
You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh
script:
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- stripe
You can pass the configuration to the command with the --config
argument:
steampipe_export_stripe --config '<your_config>' stripe_invoice