Skip to main content

​
Payment Database Schema

The payment system uses a flexible database schema designed to support multiple billing models and payment providers while maintaining clean separation of concerns.

​
Schema Design Philosophy

​
Plans and Subscriptions

The schema focuses on:
  1. Plans for pricing metadata
  2. Subscriptions for ongoing access
  3. Payments for charge history
This separation allows you to:
  • Change pricing without affecting feature logic
  • Support multiple billing models simultaneously
  • Switch payment providers easily

​
Core Models

​
Plan Model

Located in apps/api/prisma/schema.prisma Stores subscription plans and their Stripe integration details:
  • Links to Stripe products and prices
  • Supports multiple billing intervals (monthly, yearly, one-time)
  • Currency and amount tracking
  • Active/inactive status for plan management
Key Fields:
  • stripePriceId - Links to Stripe Price objects
  • stripeProductId - Links to Stripe Product objects
  • interval - Billing frequency (monthly, yearly, one_time)
  • amount - Price in cents for display purposes

​
Subscription Model

Tracks active user subscriptions with full Stripe integration:
  • Links users to their active plans
  • Stores Stripe subscription and customer IDs
  • Tracks subscription status and billing periods
  • Handles cancellation scheduling
Key Relationships:
  • Belongs to a User
  • References a Plan
  • Synced with Stripe via webhooks

​
Payment Model

Stores individual Stripe payment intents:
  • Links users to Stripe payment intents and customers
  • Tracks success/failure status
  • Records paid timestamps

​
Indexing Strategy

The schema includes strategic indexes for performance:

​
Primary Lookups

  • User.email - Authentication and user lookup
  • Payment.stripePaymentIntentId - Payment lookup

​
Stripe Integration

  • Plan.stripePriceId - Webhook processing
  • Subscription.stripeSubscriptionId - Webhook processing
  • Subscription.userId - User subscription queries

​
Relationships Overview

User
├── subscriptions[] → Subscription
└── payments[] → Payment

Subscription
├── user → User
└── plan → Plan

Plan
└── subscriptions[] → Subscription

​
Example Scenarios

​
Payment History

  1. Query Payment for user + recent records
  2. Display status and timestamps
  3. Use Stripe IDs for reconciliation if needed

​
Subscription Changes

  1. Stripe webhook updates Subscription status
  2. System updates internal Subscription status
  3. User immediately sees new access levels

​
Migration Commands

After updating the schema, run these commands: 
cd apps/api
pnpm db:migrate
pnpm db:generate
This creates the migration files and updates the Prisma client with the new models.