steampipe plugin install stripe

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_invoice
limit
5;
select
*
from
stripe_invoice
limit
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_due
from
stripe_invoice
where
created > current_timestamp - interval '1 day'
order by
created;
select
id,
customer_name,
amount_due
from
stripe_invoice
where
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_due
from
stripe_invoice
where
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_remaining
from
stripe_invoice
where
status = 'outstanding'
order by
created;
select
id,
customer_name,
amount_due,
amount_paid,
amount_remaining
from
stripe_invoice
where
status = 'outstanding'
order by
created;

Schema for stripe_invoice

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
account_countrytextThe country of the business associated with this invoice, most often the business creating the invoice.
account_nametextThe public name of the business associated with this invoice, most often the business creating the invoice.
amount_duebigintFinal 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_paidbigintThe amount, in cents, that was paid.
amount_remainingbigintThe amount remaining, in cents, that is due.
application_fee_amountbigintThe 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_countbigintNumber 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.
attemptedbooleanWhether 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_advancebooleanControls whether Stripe will perform automatic collection of the invoice. When false, the invoice’s state will not automatically advance without an explicit action.
billing_reasontextIndicates 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.
chargejsonbID of the latest charge generated for this invoice, if any.
collection_methodtext=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.
createdtimestamp with time zone>, >=, =, <, <=Time at which the invoice was created.
currencytextThree-letter ISO currency code, in lowercase. Must be a supported currency.
custom_fieldsjsonbCustom fields displayed on the invoice.
customerjsonbThe ID of the customer who will be billed.
customer_addressjsonbThe 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_emailtextThe 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_nametextThe 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_phonetextThe 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_shippingjsonbThe 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_exempttextThe 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_idsjsonbThe 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_methodtextID 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_sourcetextID 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_ratesjsonbThe tax rates applied to this invoice, if any.
descriptiontextAn arbitrary string attached to the object. Often useful for displaying to users. Referenced as ‘memo’ in the Dashboard.
discountjsonbDescribes the current discount applied to this invoice, if there is one. Not populated if there are multiple discounts.
due_datetimestamp 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_balancebigintEnding 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.
footertextFooter displayed on the invoice.
hosted_invoice_urltextThe 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.
idtext=Unique identifier for the invoice.
invoice_pdftextThe link to download the PDF for the invoice. If the invoice has not been finalized yet, this will be null.
linesjsonbThe 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.
livemodebooleanHas the value true if the invoice exists in live mode or the value false if the invoice exists in test mode.
metadatajsonbSet 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_attempttimestamp with time zoneThe time at which payment will next be attempted. This value will be null for invoices where collection_method=send_invoice.
numbertextA 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.
paidbooleanWhether 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_intentjsonbThe 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_endtimestamp with time zoneEnd of the usage period during which invoice items were added to this invoice.
period_starttimestamp with time zoneStart of the usage period during which invoice items were added to this invoice.
post_payment_credit_notes_amountbigintTotal amount of all post-payment credit notes issued for this invoice.
pre_payment_credit_notes_amountbigintTotal amount of all pre-payment credit notes issued for this invoice.
receipt_numbertextThis is the transaction number that appears on email receipts sent for this invoice.
starting_balancebigintStarting customer balance before the invoice is finalized. If the invoice has not been finalized yet, this will be the current customer balance.
statement_descriptortextExtra information about an invoice for the customer’s credit card statement.
statustext=The status of the invoice, one of draft, open, paid, uncollectible, or void.
status_transitionsjsonbThe timestamps at which the invoice status was updated.
subscription_idtext=ID of the subscription that this invoice was prepared for, if any.
subscription_proration_datetimestamp with time zoneOnly set for upcoming invoices that preview prorations. The time used to calculate prorations.
subtotalbigintTotal of all subscriptions, invoice items, and prorations on the invoice before any invoice level discount or tax is applied. Item discounts are already incorporated
taxbigintThe amount of tax on this invoice. This is the sum of all the tax amounts on this invoice.
threshold_reasonjsonbIf billing_reason is set to subscription_threshold this returns more information on which threshold rules triggered the invoice.
totalbigintTotal after discounts and taxes.
total_tax_amountsjsonbThe aggregate amounts calculated per tax rate for all line items.
transfer_datajsonbThe 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_attimestamp with time zoneInvoices 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