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

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