syrup_rail_postgres/
billing_portal.rs

1use chrono::{DateTime, Utc};
2use sqlx::{PgPool, Postgres, Row, postgres::PgArguments, query::Query};
3use syrup_rail::{
4    BillingPeriod, Entitlement, EntitlementQuery, Money, PaymentAttemptId, PaymentAttemptKind,
5    PaymentAttemptStatus, SubscriptionBillingPortalQuery, SubscriptionBillingPortalSnapshot,
6    SubscriptionPaymentHistoryCursor, SubscriptionPaymentHistoryItem,
7    SubscriptionPaymentHistoryPage, SubscriptionPaymentHistoryPageLimit,
8    SubscriptionPaymentMethodDisplay,
9};
10use thiserror::Error;
11
12use crate::entitlement::{EntitlementQueryError, entitlement_on_connection};
13
14const INVALID_BILLING_PORTAL_STATE: &str = "canonical subscription billing portal state is invalid";
15const SUBSCRIPTION_PAYMENT_HISTORY_FIRST_PAGE_SQL: &str = concat!(
16    include_str!("billing_portal/subscription_payment_history_page_head.sql"),
17    include_str!("billing_portal/subscription_payment_history_page_body.sql"),
18    "LIMIT $4\n"
19);
20const SUBSCRIPTION_PAYMENT_HISTORY_CONTINUATION_SQL: &str = concat!(
21    include_str!("billing_portal/subscription_payment_history_page_head.sql"),
22    "    AND (created_at, id) < ($4::timestamptz, $5::uuid)\n",
23    include_str!("billing_portal/subscription_payment_history_page_body.sql"),
24    "LIMIT $6\n"
25);
26
27#[derive(Clone, Copy, Debug, Eq, PartialEq)]
28enum SubscriptionPaymentHistoryPageQuery {
29    First,
30    Continuation(SubscriptionPaymentHistoryCursor),
31}
32
33impl SubscriptionPaymentHistoryPageQuery {
34    fn from_cursor(cursor: Option<&SubscriptionPaymentHistoryCursor>) -> Self {
35        match cursor {
36            Some(cursor) => Self::Continuation(*cursor),
37            None => Self::First,
38        }
39    }
40
41    const fn sql(self) -> &'static str {
42        match self {
43            Self::First => SUBSCRIPTION_PAYMENT_HISTORY_FIRST_PAGE_SQL,
44            Self::Continuation(_) => SUBSCRIPTION_PAYMENT_HISTORY_CONTINUATION_SQL,
45        }
46    }
47
48    fn bind<'args>(
49        self,
50        identity: &'args SubscriptionBillingPortalQuery,
51        limit: SubscriptionPaymentHistoryPageLimit,
52    ) -> Query<'args, Postgres, PgArguments> {
53        let query = sqlx::query(self.sql())
54            .bind(identity.billing_scope_id().into_uuid())
55            .bind(identity.subscriber_id().into_uuid())
56            .bind(identity.plan_key().as_str());
57        match self {
58            Self::First => query.bind(limit.get() + 1),
59            Self::Continuation(cursor) => query
60                .bind(cursor.created_at())
61                .bind(cursor.payment_attempt_id().into_uuid())
62                .bind(limit.get() + 1),
63        }
64    }
65}
66
67/// Error returned while loading a customer-facing billing portal projection.
68#[derive(Debug, Error)]
69pub enum SubscriptionBillingPortalQueryError {
70    #[error("subscription billing portal query failed")]
71    Sql(#[from] sqlx::Error),
72    #[error("{0}")]
73    InvalidState(&'static str),
74}
75
76/// Loads a provider-neutral customer billing portal from one PostgreSQL snapshot.
77///
78/// The returned entitlement is the canonical [`syrup_rail::Entitlement`]
79/// projection. Its stored method display contains only safe masked-card fields
80/// for the selected subscription; missing and grant-only entitlements have no
81/// payment-method display, as do disabled or fully scrubbed methods. Hosts
82/// must authenticate and authorize `query` before calling this read.
83pub async fn subscription_billing_portal(
84    pool: &PgPool,
85    query: &SubscriptionBillingPortalQuery,
86) -> Result<SubscriptionBillingPortalSnapshot, SubscriptionBillingPortalQueryError> {
87    let mut transaction = pool.begin().await?;
88    sqlx::query("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY")
89        .execute(&mut *transaction)
90        .await?;
91
92    let entitlement_query = EntitlementQuery::new(
93        query.billing_scope_id(),
94        query.subscriber_id(),
95        query.plan_key().clone(),
96    )
97    .across_gateway_account_modes();
98    let entitlement = entitlement_on_connection(&mut transaction, &entitlement_query)
99        .await
100        .map_err(map_entitlement_error)?;
101    let payment_method_display = match payment_method_id(&entitlement) {
102        Some(payment_method_id) => {
103            payment_method_display(&mut transaction, query, payment_method_id).await?
104        }
105        None => None,
106    };
107
108    transaction.commit().await?;
109    Ok(SubscriptionBillingPortalSnapshot::new(
110        entitlement,
111        payment_method_display,
112    ))
113}
114
115/// Returns one strict descending page of exact-plan subscription payment history.
116///
117/// The page deliberately selects only provider-neutral attempt lifecycle,
118/// money, billing-period, and timestamp facts. It never reads provider
119/// payment-method references, gateway transaction identifiers, contacts,
120/// response text, or raw diagnostics.
121pub async fn subscription_payment_history_page(
122    pool: &PgPool,
123    query: &SubscriptionBillingPortalQuery,
124    cursor: Option<&SubscriptionPaymentHistoryCursor>,
125    limit: SubscriptionPaymentHistoryPageLimit,
126) -> Result<SubscriptionPaymentHistoryPage, SubscriptionBillingPortalQueryError> {
127    let rows = SubscriptionPaymentHistoryPageQuery::from_cursor(cursor)
128        .bind(query, limit)
129        .fetch_all(pool)
130        .await?;
131
132    let mut items = rows
133        .iter()
134        .map(subscription_payment_history_item_from_row)
135        .collect::<Result<Vec<_>, _>>()?;
136    let has_more = items.len() > limit.get() as usize;
137    if has_more {
138        items.pop();
139    }
140    let next_cursor = has_more.then(|| {
141        let item = items
142            .last()
143            .expect("a page with an extra row always retains one item");
144        SubscriptionPaymentHistoryCursor::new(item.created_at(), item.payment_attempt_id())
145    });
146
147    Ok(SubscriptionPaymentHistoryPage::new(items, next_cursor))
148}
149
150fn map_entitlement_error(error: EntitlementQueryError) -> SubscriptionBillingPortalQueryError {
151    match error {
152        EntitlementQueryError::Sql(error) => SubscriptionBillingPortalQueryError::Sql(error),
153        EntitlementQueryError::InvalidState(_) => {
154            SubscriptionBillingPortalQueryError::InvalidState(INVALID_BILLING_PORTAL_STATE)
155        }
156    }
157}
158
159fn payment_method_id(entitlement: &Entitlement) -> Option<syrup_rail::PaymentMethodId> {
160    match entitlement {
161        Entitlement::PaidActive { subscription, .. }
162        | Entitlement::PaidThroughCancellation { subscription, .. }
163        | Entitlement::PastDue { subscription, .. } => Some(subscription.payment_method_id()),
164        Entitlement::Missing { .. } | Entitlement::Granted { .. } => None,
165    }
166}
167
168async fn payment_method_display(
169    connection: &mut sqlx::PgConnection,
170    query: &SubscriptionBillingPortalQuery,
171    payment_method_id: syrup_rail::PaymentMethodId,
172) -> Result<Option<SubscriptionPaymentMethodDisplay>, SubscriptionBillingPortalQueryError> {
173    let row = sqlx::query(
174        r#"
175        SELECT card_brand, card_last4, card_exp_month, card_exp_year
176        FROM billing_payment_methods
177        WHERE id = $1
178            AND billing_scope_id = $2
179            AND subscriber_id = $3
180            AND status = 'active'
181        "#,
182    )
183    .bind(payment_method_id.as_uuid())
184    .bind(query.billing_scope_id().as_uuid())
185    .bind(query.subscriber_id().as_uuid())
186    .fetch_optional(connection)
187    .await?;
188    let Some(row) = row else {
189        return Ok(None);
190    };
191
192    let card_brand: Option<String> = row.try_get("card_brand")?;
193    let card_last_four: Option<String> = row.try_get("card_last4")?;
194    let card_expiration_month = row
195        .try_get::<Option<i16>, _>("card_exp_month")?
196        .map(|value| u8::try_from(value).map_err(|_| invalid_state()))
197        .transpose()?;
198    let card_expiration_year = row
199        .try_get::<Option<i16>, _>("card_exp_year")?
200        .map(|value| u16::try_from(value).map_err(|_| invalid_state()))
201        .transpose()?;
202    SubscriptionPaymentMethodDisplay::from_provider_parts(
203        card_brand.as_deref(),
204        card_last_four,
205        card_expiration_month,
206        card_expiration_year,
207    )
208    .map_err(|_| invalid_state())
209}
210
211fn subscription_payment_history_item_from_row(
212    row: &sqlx::postgres::PgRow,
213) -> Result<SubscriptionPaymentHistoryItem, SubscriptionBillingPortalQueryError> {
214    let kind = row
215        .try_get::<String, _>("attempt_kind")?
216        .parse::<PaymentAttemptKind>()
217        .map_err(|_| invalid_state())?;
218    let status = row
219        .try_get::<String, _>("status")?
220        .parse::<PaymentAttemptStatus>()
221        .map_err(|_| invalid_state())?;
222    let currency = syrup_rail::CurrencyCode::new(&row.try_get::<String, _>("currency")?)
223        .map_err(|_| invalid_state())?;
224    let amount = Money::new(row.try_get("amount_cents")?, currency).map_err(|_| invalid_state())?;
225    let billing_period = billing_period_from_row(row)?;
226
227    SubscriptionPaymentHistoryItem::new(
228        PaymentAttemptId::new(row.try_get("id")?),
229        kind,
230        status,
231        amount,
232        billing_period,
233        row.try_get::<Option<DateTime<Utc>>, _>("submitted_at")?,
234        row.try_get::<Option<DateTime<Utc>>, _>("resolved_at")?,
235        row.try_get("created_at")?,
236    )
237    .map_err(|_| invalid_state())
238}
239
240fn billing_period_from_row(
241    row: &sqlx::postgres::PgRow,
242) -> Result<Option<BillingPeriod>, SubscriptionBillingPortalQueryError> {
243    let start_at = row.try_get::<Option<DateTime<Utc>>, _>("billing_period_start_at")?;
244    let end_at = row.try_get::<Option<DateTime<Utc>>, _>("billing_period_end_at")?;
245    match (start_at, end_at) {
246        (None, None) => Ok(None),
247        (Some(start_at), Some(end_at)) => BillingPeriod::new(start_at, end_at)
248            .map(Some)
249            .map_err(|_| invalid_state()),
250        _ => Err(invalid_state()),
251    }
252}
253
254fn invalid_state() -> SubscriptionBillingPortalQueryError {
255    SubscriptionBillingPortalQueryError::InvalidState(INVALID_BILLING_PORTAL_STATE)
256}
257
258#[cfg(test)]
259mod tests;
syrup_rail_postgres/billing_portal.rs

syrup_rail_postgres/
billing_portal.rs
