Subscriptions

Represents customer subscriptions, including the associated plan, billing cycle and collection settings, trial periods, and applied discounts.

Table-Specific Information

SELECT

The add-in uses the Stripe API to filter the results by the following columns and operators while the rest of the filter is executed client-side within the add-in.


Column	Supported Operators
Id	=
CustomerId	=
PlanId	=
Status	=
MetadataAggregate	=
AutomaticTaxEnabled	=
CollectionMethod	=
TestClock	=
AccountId	=
CreatedAt	=, !=, >, >=, <, <=
CanceledAt	=, !=, >, >=, <, <=

You can select:

A subscription by specifying its Id:

SELECT * FROM Subscriptions WHERE Id = 'mySubscriptionId'

Subscriptions created after a specific date:

SELECT * FROM Subscriptions WHERE CreatedAt > '2016-01-03'

CreatedAt can also be used twice to specify a range:

SELECT * FROM Subscriptions WHERE CreatedAt >= '2016-01-01' AND CreatedAt <= '2016-12-31'

Subscriptions that belong to a customer:

SELECT * FROM Subscriptions WHERE CustomerId = 'cus_12345678'

Subscriptions that belong to a plan:

SELECT * FROM Subscriptions WHERE PlanId = 'myPlanId'

Subscriptions with a specific status:

SELECT * FROM Subscriptions WHERE Status = 'active'

Subscriptions with metadata:

SELECT * FROM Subscriptions WHERE MetadataAggregate = '{\"fdgm\":\"gdfgkm\"}'

INSERT

CustomerId and ItemsAggregate columns are required to create a new subscription:

INSERT INTO Subscriptions (CustomerId, TrialEnd, ItemsAggregate, CancelAtPeriodEnd) VALUES ('cus_PDYvuww0WynoTg', '2023-12-22', '[{\"price\": \"43\",\"quantity\":\"12\"}]', true)

Alternatively, you can provide CustomerId and PlanId columns to create a new subscription:

INSERT INTO Subscriptions (CustomerId, PlanId, TrialEnd, CancelAtPeriodEnd) VALUES ('cus_LMchUD47S8Eumg', 'price_1ONAgvCZ8rn6wdu', '2024-12-22', true)

UPDATE

To update a subscription, specify the Id:

UPDATE Subscriptions SET ApplicationFeePercent = 0, MetadataAggregate = '[{\"order\":\"14453\"}]' WHERE Id = 'sub_A9WZGVTbvgBJ4t'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the subscription.
CustomerId	String	False	Customers.Id	The Id of the customer who owns the subscription.
PlanId	String	False	Plans.Id	The Id of the plan.
PlanActive	Boolean	True		Indicates whether the plan can be used for new purchases.
PlanAggregateUsage	String	True		Specifies a usage aggregation strategy for plans of usage_type=metered.
PlanAmount	Integer	True		The unit amount in cents to be charged, represented as a whole integer if possible.
PlanAmountDecimal	String	True		The unit amount in cents to be charged, represented as a decimal string with at most 12 decimal places.
PlanBillingScheme	String	True		Describes how to compute the price per period.
PlanCreated	Datetime	True		The time at which the plan was created, measured in seconds since the Unix epoch.
PlanCurrency	String	True		The three-letter ISO currency code, in lowercase. Must be a supported currency.
PlanInterval	String	True		The frequency at which a subscription is billed, such as day, week, month, or year.
PlanIntervalCount	Integer	True		The number of intervals, specified in the interval attribute, between subscription billings.
PlanLivemode	Boolean	True		Indicates whether the object exists in live mode. Has the value true if the object exists in live mode, or the value false if the object exists in test mode.
PlanMetadata	String	True		The set of key-value pairs that you can attach to the plan.
PlanMeter	String	True		The meter tracking the usage of a metered price.
PlanNickname	String	True		A brief description of the plan, hidden from customers.
PlanObject	String	True		The string representing the object's type. Objects of the same type share the same value.
PlanProduct	String	True		The product whose pricing this plan determines.
PlanTiersMode	String	True		Defines whether the tiering price is graduated or volume-based.
PlanTransformUsage	String	True		The transformation applied to the reported usage or set quantity before computing the amount billed.
PlanTrialPeriodDays	Integer	True		The default number of trial days applied when subscribing a customer to this plan using trial_from_plan=true.
PlanUsageType	String	True		Configures how the quantity per period should be determined.
ApplicationFeePercent	Decimal	False		A positive decimal representing the fee percentage of the subscription invoice amount that is transferred to the application of the Stripe account owner each billing period.
CancelAtPeriodEnd	Boolean	False		Indicates whether the subscription is scheduled to be canceled at the end of the current period. If the subscription has been canceled with the at_period_end flag set to true, this value is true. You can use this attribute to determine whether an active subscription is scheduled to be canceled at the end of the current period.
CanceledAt	Datetime	True		The date of cancellation, if the subscription has been canceled. If the subscription was canceled with cancel_at_period_end, this value reflects the date of the initial cancellation request, not the end of the subscription period when the subscription is automatically moved to a canceled state.
CreatedAt	Datetime	True		The time at which the subscription was created, measured in seconds since the Unix epoch.
EndedAt	Datetime	True		The date the subscription ended, if the subscription has ended because it was canceled or because the customer was switched to a new plan.
Quantity	Double	True		The quantity of the plan to which the customer should be subscribed. For example, if your plan is $10/user/month, and your customer has 5 users, you could pass 5 as the quantity to have the customer charged $50 (5 x $10) monthly.
StartDate	Datetime	True		The date the most recent update to this subscription started.
Status	String	True		The status of the subscription. The allowed values are active, past_due, unpaid, canceled, incomplete, incomplete_expired, trialing, all, ended, paused.
TaxPercent	Decimal	True		[DEPRECATED - Use default_tax_rates instead] The tax rate applied to each invoice created by this subscription, increasing the amount billed to the customer, if provided.
TrialEnd	Datetime	False		The end of the trial, if the subscription has a trial.
TrialStart	Datetime	True		The beginning of the trial, if the subscription has a trial.
MetadataAggregate	String	False		The set of key-value pairs that you can attach to the subscription.
BillingCycleAnchor	Datetime	False		Determines the date of the first full invoice, and, for plans with month or year intervals, the day of the month for subsequent invoices.
DefaultPaymentMethod	String	False		The Id of the default payment method for the subscription.
ItemsAggregate	String	False		The list of subscription items, each with an attached price.
LatestInvoice	String	True		The Id of the most recent invoice this subscription has generated.
PendingSetupIntent	String	True		The setup intent used to collect user authentication when creating a subscription without immediate payment or updating a subscription's payment method, allowing you to optimize for off-session payments.
PendingUpdate	String	True		The pending updates applied to the subscription once the latest invoice has been paid, if specified.
AutomaticTaxEnabled	Boolean	False		Indicates whether automatic tax is enabled for this subscription.
AutomaticTaxLiabilityAccount	String	False		The connected account being referenced when the type is account.
AutomaticTaxLiabilityType	String	False		The type of the account referenced.
CollectionMethod	String	False		The method used to collect payment for the subscription, either charge_automatically or send_invoice. When charging automatically, Stripe attempts to pay this subscription at the end of the cycle using the default source attached to the customer. The allowed values are charge_automatically, send_invoice.
DaysUntilDue	Integer	False		The number of days a customer has to pay invoices generated by this subscription. This value is null for subscriptions where collection_method=charge_automatically.
DefaultSource	String	False		The Id of the default payment source for the subscription.
DefaultTaxRates	String	False		The tax rates that apply to any subscription item that does not have tax_rates set. Created invoices have their default_tax_rates populated from the subscription.
LiveMode	Boolean	True		Indicates whether the object exists in live mode. Has the value true if the object exists in live mode, or the value false if the object exists in test mode.
NextPendingInvoiceItemInvoice	Datetime	True		The approximate timestamp on which any pending invoice items are billed, according to the schedule provided at pending_invoice_item_interval.
PauseCollectionBehavior	String	False		The payment collection behavior for this subscription while paused, if specified. One of keep_as_draft, mark_uncollectible, or void.
PauseCollectionResumesAt	Datetime	False		The time after which the subscription resumes collecting payments, if collection is paused.
PaymentSettings	String	False		The payment settings passed on to invoices created by the subscription.
PendingInvoiceItemInterval	String	False		The interval for how often to bill for any pending invoice items, which specifies the invoicing frequency, either day, week, month, or year.
PendingInvoiceItemIntervalCount	Integer	False		The number of intervals between invoices for any pending invoice items.
Schedule	String	True		The schedule attached to the subscription.
TestClock	String	True		The Id of the test clock this subscription belongs to.
TransferData	String	False		The account, if any, to which the subscription's payments are attributed for tax reporting and to which funds from each payment are transferred for each of the subscription's invoices.
CancelAt	Datetime	False		A date in the future at which the subscription is automatically canceled.
Description	String	False		The subscription's description.
CancellationDetailsComment	String	False		Additional comments about why the user canceled the subscription, if the subscription was canceled explicitly by the user.
CancellationDetailsFeedback	String	False		The customer-submitted reason for the cancellation, if the subscription was canceled explicitly by the user.
CancellationDetailsReason	String	False		The reason why this subscription was canceled. The allowed values are cancellation_requested, payment_disputed, payment_failed, retention_policy.
Currency	String	False		The three-letter ISO currency code, in lowercase. Must be a supported currency.
TrialSettingsEndBehaviorPaymentMethod	String	False		Indicates how the subscription changes when the trial ends if the user did not provide a payment method. The allowed values are cancel, pause, create_invoice.
OnBehalfOf	String	False		The account on behalf of which to charge for each of the subscription's invoices.
DiscountId	String	True		DEPRECATED. The Id of the discount.
DiscountCheckoutSession	String	True		DEPRECATED. The Checkout session that this coupon is applied to, if it is applied to a particular session in payment mode. This value is not present for subscription mode.
DiscountCustomer	String	True		DEPRECATED. The Id of the customer associated with this discount.
DiscountStart	Datetime	True		DEPRECATED. The date the discount began.
DiscountEnd	Datetime	True		DEPRECATED. The date the discount ends.
DiscountInvoice	String	True		DEPRECATED. The invoice that the discount's coupon was applied to, if it was applied directly to a particular invoice.
DiscountInvoiceItem	String	True		DEPRECATED. The Id of the invoice line item that the discount's coupon was applied to, if it was applied directly to an invoice line item.
DiscountPromotionCode	String	True		DEPRECATED. The promotion code applied to create this discount.
DiscountSubscription	String	True		DEPRECATED. The subscription that this coupon is applied to, if it is applied to a particular subscription.
DiscountCouponCreatedAt	Datetime	True		DEPRECATED. The time at which the coupon was created, measured in seconds since the Unix epoch.
DiscountCouponCurrency	String	True		DEPRECATED. The three-letter ISO code for the currency of the amount to take off, if amount_off has been set.
DiscountCouponName	String	True		DEPRECATED. The name of the coupon displayed to customers, for example, on invoices or receipts.
DiscountCouponDuration	String	True		DEPRECATED. Describes how long a customer who applies this coupon receives the discount. One of forever, once, or repeating.
DiscountCouponDurationInMonths	Integer	True		DEPRECATED. The number of months the coupon applies.
DiscountCouponAmountOff	Integer	True		DEPRECATED. The amount, in the specified currency, that is taken off the subtotal of any invoices for this customer.
DiscountCouponPercentOff	Decimal	True		DEPRECATED. The percent taken off the subtotal of any invoices for this customer for the duration of the coupon. For example, a coupon with a percent_off of 50 makes a $100 invoice $50 instead.
DiscountCouponValid	Boolean	True		DEPRECATED. Indicates whether this coupon can still be applied to a customer, taking the other properties into account.
DiscountCouponMaxRedemptions	Integer	True		DEPRECATED. The maximum number of times this coupon can be redeemed, in total, before it is no longer valid.
DiscountCouponRedeemBy	Datetime	True		DEPRECATED. The date after which the coupon can no longer be redeemed.
DiscountCouponTimesRedeemed	Integer	True		DEPRECATED. The number of times this coupon has been applied to a customer.
DiscountCouponObject	String	True		DEPRECATED. The string representing the object's type. Objects of the same type share the same value.
DiscountCouponLiveMode	Boolean	True		DEPRECATED. Indicates whether the object exists in live mode. Has the value true if the object exists in live mode, or the value false if the object exists in test mode.
DiscountCouponMetadataAggregate	String	True		DEPRECATED. The set of key-value pairs that you can attach to the coupon.
Application	String	True		The Id of the Connect application that created the subscription.
BillingCycleAnchorConfigDayofMonth	Integer	False		The day of the month of the billing cycle anchor.
BillingCycleAnchorConfigHour	Integer	False		The hour of the day of the billing cycle anchor.
BillingCycleAnchorConfigMinute	Integer	False		The minute of the hour of the billing cycle anchor.
BillingCycleAnchorConfigMonth	Integer	False		The month to start full cycle billing periods.
BillingCycleAnchorConfigSecond	Integer	False		The second of the minute of the billing cycle anchor.
InvoiceSettingsAccountTaxIds	String	False		The account tax Ids included in the invoice settings.
InvoiceSettingsIssuerType	String	False		The issuer type included in the invoice settings.
InvoiceSettingsIssuerAccount	String	False		The connected account being referenced when InvoiceSettingsIssuerType is account.
AutomaticTaxDisabledReason	String	True		The reason automatic tax was disabled, if Stripe disabled automatic tax. The allowed values are requires_location_inputs.
CustomerAccount	String	False		The Id of the account representing the customer who owns the subscription.
BillingModeType	String	False		Controls how prorations and invoices for subscriptions are calculated and orchestrated. One of flexible or classic. The allowed values are flexible, classic.
ManagedPaymentsExclusiveManagement	String	True		DEPRECATED. Indicates whether this subscription's managed payments are exclusive.
PresentmentDetailsPresentmentCurrency	String	True		The currency presented to the customer for this subscription.
DiscountsAggregate	String	False		The list of discounts applied to the subscription.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.


Name	Type	Description
IsProrate	Boolean	Indicates whether to prorate when switching plans during a billing cycle.
ProrationDate	Date	The time used to calculate prorations. If set, the proration is calculated as though the subscription was updated at the given time. It can also be used to implement custom proration logic, such as prorating by day instead of by second, by providing the time that you want to use for proration calculations.
PaymentBehavior	String	Determines how Stripe handles payments when a subscription change requires payment. Use allow_incomplete to transition the subscription to status=past_due if a payment is required but cannot be paid. This allows you to manage scenarios where additional user actions are needed to pay a subscription's invoice.
ProrationBehavior	String	Determines how to handle prorations when the billing cycle changes, such as when switching plans, resetting billing_cycle_anchor=now, or starting a trial, or when an item's quantity changes. The default value is create_prorations.
TrialPeriodDays	Integer	The number of trial period days before the customer is charged for the first time. If set, trial_period_days overrides the default trial period days of the plan to which the customer is being subscribed.
AccountId	String	The Id of the connected account to get subscriptions for.