Payment & Subscription Queries Deep Dive

Comprehensive reference for all payment provider management, payment account operations, subscription lifecycle, auto-renewal, and billing query functions.

Overview

The payment query layer is organized into two complementary modules:

• payment.queries.ts -- Payment provider CRUD, payment account management, and account setup orchestration
• subscription.queries.ts -- Subscription lifecycle (create, update, cancel, expire), plan management, history tracking, auto-renewal, and billing statistics

Source Files

lib/db/queries/payment.queries.ts
lib/db/queries/subscription.queries.ts

---

Function Reference: payment.queries.ts

Payment Provider Queries

getPaymentProvider

Gets a payment provider by ID.

async function getPaymentProvider(id: string): Promise<OldPaymentProvider | null>

SQL Pattern:

SELECT * FROM payment_providers WHERE id = ? LIMIT 1;

---

getPaymentProviderByName

Gets a payment provider by name (e.g., 'stripe', 'lemonsqueezy').

async function getPaymentProviderByName(name: string): Promise<OldPaymentProvider | null>

---

getActivePaymentProviders

Gets all active payment providers ordered by name.

async function getActivePaymentProviders(): Promise<OldPaymentProvider[]>

SQL Pattern:

SELECT * FROM payment_providers WHERE is_active = true ORDER BY name;

---

createPaymentProvider

Creates a new payment provider.

async function createPaymentProvider(data: NewPaymentProvider): Promise<OldPaymentProvider>

---

updatePaymentProvider

Updates a payment provider's fields.

async function updatePaymentProvider(
  id: string,
  data: Partial<NewPaymentProvider>
): Promise<OldPaymentProvider | null>

---

deactivatePaymentProvider

Deactivates a payment provider by setting isActive to false.

async function deactivatePaymentProvider(id: string): Promise<OldPaymentProvider | null>

---

Payment Account Queries

getPaymentAccountByUserId

Gets a payment account by user ID and provider ID. Validates both the provider and user are active.

async function getPaymentAccountByUserId(
  userId: string,
  providerId: string
): Promise<PaymentAccount | null>

SQL Pattern:

SELECT payment_accounts.* FROM payment_accounts
INNER JOIN payment_providers ON payment_accounts.provider_id = payment_providers.id
INNER JOIN users ON payment_accounts.user_id = users.id
WHERE payment_accounts.user_id = ?
  AND payment_accounts.provider_id = ?
  AND payment_providers.is_active = true
LIMIT 1;

Performance Notes: Uses INNER JOIN to ensure both the provider is active and the user exists.

---

getPaymentAccountByCustomerId

Gets a payment account by the external customer ID from the payment provider.

async function getPaymentAccountByCustomerId(
  customerId: string,
  providerId: string
): Promise<PaymentAccount | null>

---

createPaymentAccount

Creates a new payment account. Automatically sets lastUsed to current timestamp.

async function createPaymentAccount(data: NewPaymentAccount): Promise<PaymentAccount>

---

updatePaymentAccountLastUsed

Updates the lastUsed timestamp on a payment account.

async function updatePaymentAccountLastUsed(accountId: string): Promise<void>

---

getUserPaymentAccountByProvider

Gets a user's payment account by provider name (convenience function).

async function getUserPaymentAccountByProvider(
  userId: string,
  providerName: string
): Promise<PaymentAccount | null>

Internally calls getPaymentProviderByName then getPaymentAccountByUserId.

---

Payment Account Orchestration

ensurePaymentAccount

Ensures a payment account exists for a user and provider. Creates the provider and account if they do not exist, or updates lastUsed if they do.

async function ensurePaymentAccount(
  providerName: string,
  userId: string,
  customerId: string,
  accountId?: string
): Promise<PaymentAccount>

Parameters:

Parameter	Type	Required	Description
providerName	string	Yes	Provider name (e.g., 'stripe')
userId	string	Yes	User ID
customerId	string	Yes	Customer ID at the provider
accountId	string	No	Account ID at the provider

Behavior:

1. Checks if provider exists; creates if not
2. Checks if payment account exists for user+provider; updates lastUsed if found
3. Creates new payment account if not found

---

getOrCreatePaymentAccount

Alias for ensurePaymentAccount.

---

setupUserPaymentAccount

Enhanced version of ensurePaymentAccount with customer ID update logic. If the customerId has changed on an existing account, it updates the record.

async function setupUserPaymentAccount(
  providerName: string,
  userId: string,
  customerId: string,
  accountId?: string
): Promise<PaymentAccount>

Additional behavior vs ensurePaymentAccount:

• Detects changed customerId and updates the existing record
• Provides detailed error logging with stack traces

---

createOrGetPaymentAccount

Alias for setupUserPaymentAccount.

---

Function Reference: subscription.queries.ts

Subscription CRUD

getUserActiveSubscription

Gets the active subscription for a user.

async function getUserActiveSubscription(userId: string): Promise<Subscription | null>

SQL Pattern:

SELECT * FROM subscriptions
WHERE user_id = ? AND status = 'active'
LIMIT 1;

---

getUserSubscriptions

Gets all subscriptions for a user, ordered by creation date descending.

async function getUserSubscriptions(userId: string): Promise<Subscription[]>

---

getSubscriptionByProviderSubscriptionId

Looks up a subscription by the external provider's subscription ID.

async function getSubscriptionByProviderSubscriptionId(
  paymentProvider: string,
  subscriptionId: string
): Promise<Subscription | null>

---

getSubscriptionByUserIdAndSubscriptionId

async function getSubscriptionByUserIdAndSubscriptionId(
  userId: string,
  subscriptionId: string
): Promise<Subscription | null>

---

createSubscription

async function createSubscription(data: NewSubscription): Promise<Subscription>

Automatically sets createdAt and updatedAt to current timestamp.

---

updateSubscription

async function updateSubscription(
  subscriptionId: string,
  data: Partial<NewSubscription>
): Promise<Subscription | null>

---

updateSubscriptionBySubscriptionId

Updates subscription matching by the provider's subscriptionId field (not the internal ID).

async function updateSubscriptionBySubscriptionId(
  updateData: Partial<NewSubscription>
): Promise<Subscription | null>

---

updateSubscriptionStatus

Updates subscription status with automatic cancelledAt timestamp when status is CANCELLED.

async function updateSubscriptionStatus(
  subscriptionId: string,
  status: string,
  reason?: string
): Promise<Subscription | null>

---

cancelSubscription

Cancels a subscription either immediately or at period end.

async function cancelSubscription(
  subscriptionId: string,
  reason?: string,
  cancelAtPeriodEnd: boolean = false
): Promise<Subscription | null>

Behavior:

• If cancelAtPeriodEnd is true: keeps status as ACTIVE but sets cancelAtPeriodEnd flag
• If cancelAtPeriodEnd is false: sets status to CANCELLED immediately

---

getSubscriptionWithUser

Gets a subscription with joined user details.

async function getSubscriptionWithUser(
  subscriptionId: string
): Promise<SubscriptionWithUser | null>

---

Plan Management

getUserPlan

Gets the user's effective plan, checking for expiration.

async function getUserPlan(userId: string): Promise<string>

Returns: Plan ID string (defaults to PaymentPlan.FREE if no active subscription or expired)

Uses getEffectivePlan() utility to handle expiration logic.

---

getUserPlanWithExpiration

Gets full plan details including expiration information.

async function getUserPlanWithExpiration(userId: string): Promise<{
  planId: string;
  effectivePlan: string;
  isExpired: boolean;
  expiresAt: Date | null;
  status: string | null;
  subscriptionId: string | null;
}>

---

hasActiveSubscription

Boolean check for active subscription existence.

async function hasActiveSubscription(userId: string): Promise<boolean>

---

Expiration Management

getSubscriptionsExpiringSoon

Gets active subscriptions expiring within N days.

async function getSubscriptionsExpiringSoon(days: number = 7): Promise<Subscription[]>

SQL Pattern:

SELECT * FROM subscriptions
WHERE status = 'active' AND end_date <= ?
ORDER BY end_date ASC;

---

getExpiredActiveSubscriptions

Gets subscriptions that have passed their endDate but are still marked as active.

async function getExpiredActiveSubscriptions(): Promise<Subscription[]>

---

updateExpiredSubscriptionsStatus

Batch updates all expired-but-active subscriptions to EXPIRED status.

async function updateExpiredSubscriptionsStatus(): Promise<Subscription[]>

---

Auto-Renewal Queries

getSubscriptionsDueForRenewalReminder

Gets subscriptions that need renewal reminders (active, auto-renewal enabled, expiring within N days, reminder not yet sent).

async function getSubscriptionsDueForRenewalReminder(
  days: number = 7
): Promise<Subscription[]>

SQL Pattern:

SELECT * FROM subscriptions
WHERE status = 'active'
  AND auto_renewal = true
  AND renewal_reminder_sent = false
  AND end_date >= NOW()
  AND end_date <= ?
ORDER BY end_date ASC;

---

getSubscriptionsToCancel

Gets subscriptions with auto-renewal disabled whose period has ended.

async function getSubscriptionsToCancel(): Promise<Subscription[]>

---

setAutoRenewal

Toggles auto-renewal. Also sets cancelAtPeriodEnd inversely.

async function setAutoRenewal(
  subscriptionId: string,
  enabled: boolean
): Promise<Subscription | null>

---

markRenewalReminderSent / resetRenewalReminderSent

async function markRenewalReminderSent(subscriptionId: string): Promise<Subscription | null>
async function resetRenewalReminderSent(subscriptionId: string): Promise<Subscription | null>

---

Failed Payment Management

incrementFailedPaymentCount

Atomically increments the failed payment counter.

async function incrementFailedPaymentCount(
  subscriptionId: string
): Promise<Subscription | null>

SQL Pattern:

UPDATE subscriptions
SET failed_payment_count = COALESCE(failed_payment_count, 0) + 1,
    last_renewal_attempt = NOW()
WHERE id = ?;

---

resetFailedPaymentCount

Resets counter after successful payment.

async function resetFailedPaymentCount(subscriptionId: string): Promise<Subscription | null>

---

getSubscriptionsWithFailedPayments

Gets subscriptions exceeding a failed payment threshold.

async function getSubscriptionsWithFailedPayments(
  threshold: number = 3
): Promise<Subscription[]>

---

resetRenewalStateAtomic

Atomically resets both renewalReminderSent and failedPaymentCount in a single UPDATE to ensure data consistency.

async function resetRenewalStateAtomic(
  subscriptionId: string
): Promise<Subscription | null>

---

Subscription History

createSubscriptionHistory

Creates a history entry for subscription changes.

async function createSubscriptionHistory(
  data: NewSubscriptionHistory
): Promise<SubscriptionHistoryType>

---

getSubscriptionHistory

Gets history entries for a subscription, ordered by date descending.

async function getSubscriptionHistory(
  subscriptionId: string
): Promise<SubscriptionHistoryType[]>

---

logSubscriptionChange

Convenience function for logging subscription state changes with structured data.

async function logSubscriptionChange(
  subscriptionId: string,
  action: string,
  previousStatus?: string,
  newStatus?: string,
  previousPlan?: string,
  newPlan?: string,
  reason?: string,
  metadata?: Record<string, unknown>
): Promise<SubscriptionHistoryType>

---

Statistics

getSubscriptionStats

Gets subscription statistics including totals and plan distribution.

async function getSubscriptionStats(): Promise<{
  total: number;
  active: number;
  cancelled: number;
  planDistribution: Array<{ planId: string; count: number }>;
}>

---

Performance Notes

1. INNER JOIN validation -- getPaymentAccountByUserId uses INNER JOINs to validate both provider activity and user existence in a single query.

2. Atomic updates -- incrementFailedPaymentCount uses COALESCE for null-safe increment. resetRenewalStateAtomic resets multiple fields in a single UPDATE.

3. Idempotent account setup -- ensurePaymentAccount and setupUserPaymentAccount handle race conditions gracefully, creating or updating as needed.

4. Expiration checking -- getUserPlan delegates to getEffectivePlan() utility which handles timezone-aware expiration logic without additional DB queries.

Usage Examples

Webhook handler for Stripe payment

import {
  ensurePaymentAccount,
  createSubscription,
  logSubscriptionChange,
} from '@/lib/db/queries';

// Ensure payment account exists
const account = await ensurePaymentAccount(
  'stripe', userId, stripeCustomerId
);

// Create subscription
const sub = await createSubscription({
  userId,
  planId: 'premium',
  status: 'active',
  paymentProvider: 'stripe',
  subscriptionId: stripeSubId,
  startDate: new Date(),
  endDate: endDate,
});

// Log the change
await logSubscriptionChange(sub.id, 'created', null, 'active', null, 'premium');

Checking user plan with expiration

import { getUserPlanWithExpiration } from '@/lib/db/queries';

const plan = await getUserPlanWithExpiration(userId);

if (plan.isExpired) {
  console.log(`Plan ${plan.planId} expired, effective plan: ${plan.effectivePlan}`);
}