Subscriptions

Manage the subscriptions of your SaaS customers

When a new subscription is created or updated, a record in the UserPlan table is created or updated. That is where the subscription of your users lives. Remember that only one subscription can exist for a user and that the user must exist in the User table when the subscription or order is created.

What if the user was not signed up when they purchased?

In that case, ask for the user to sign up, go to Lemon Squeezy > Settings > Webhooks, search for the order_created or subscription_created event from that user, and resend the event.

Lemon Squeezy Webhook events (Settings > Webhooks)

Database

The file prisma/schema.prisma already contains tables to manage the subscription plans of your SaaS. Those tables are SubscriptionPlan, PlanProperty, and UserPlan, let's see how they are used.

Table SubscriptionPlan

This table must contain all the subscription plans you create in Lemon Squeezy.

Its columns are:

Table SubscriptionPlan

  id         String         @id @default(cuid())
  name       String
  productId  String
  price      String
  createdAt  DateTime       @default(now())
  updatedAt  DateTime       @updatedAt

You can add new records by running npx prisma studio in the terminal, it will run the database client UI at http://localhost:5555/.

To generate random cuids, you can use the CUID Generator of Shipped.

If you have a free plan, you can create a record for it as well.

Table PlanProperty

When you have subscription plans in your SaaS, each plan will probably have some limits and properties. This table is intended to save those properties for each Subscription Plan.

Here's an example.

Plan	Screenshots	planId
Free	10/month	thlx5o9ds0m8s8g9p8e7utvo
Hobby	500/month	hjtmghruw3tw68x6daxqgwhu
Pro	1000/month	rgiy096elb1s4fsauug9qamf

For each plan you can create a record in the table PlanProperty, like this:

id	planId	propertyName	value
123	thlx5o9ds0m8s8g9p8e7utvo	MAX_SCREENSHOTS	10
234	hjtmghruw3tw68x6daxqgwhu	MAX_SCREENSHOTS	500
345	rgiy096elb1s4fsauug9qamf	MAX_SCREENSHOTS	1000

Table UserPlan

UserPlan is the table in which are stored the subscription plans purchased by your customers.

The records are automatically inserted and updated by the webhook /api/webhooks/lemonsqueezy but it might happen that you need to manually update it, for any reason.

This table is intended to contain only one record for each user (no multiple plans are allowed).

UserPlan table schema

model UserPlan {
  id                  String           @id @default(cuid())
  userId              String           @unique
  planId              String
  lemonOrderId        String?
  lemonProductId      String
  lemonVariantId      String?
  lemonPlanName       String?
  lemonPlanPrice      String?
  lemonSubscriptionId String?
  createdAt           DateTime         @default(now())
  updatedAt           DateTime         @updatedAt
  validUntil          DateTime?
  cancelUrl           String?
  updateUrl           String?
  status              String?
  user                User             @relation(fields: [userId], references: [id])
  plan                SubscriptionPlan @relation(fields: [planId], references: [id])
}