Subscriptions Table Schema

The subscriptions table stores all subscription records from Dodo Payments, including billing details, status, and customer information. Source: ~/workspace/source/lib/drizzle/schema.ts:30-69

Table Definition

export const subscriptions = pgTable("subscriptions", {
  subscriptionId: text("subscription_id").primaryKey().notNull(),
  userId: text("user_id").references(() => users.supabaseUserId),
  recurringPreTaxAmount: real("recurring_pre_tax_amount").notNull(),
  taxInclusive: boolean("tax_inclusive").notNull(),
  currency: text("currency").notNull(),
  status: text("status").notNull(),
  createdAt: timestamp("created_at", {
    mode: "string",
    withTimezone: true,
  }).notNull(),
  productId: text("product_id").notNull(),
  quantity: integer("quantity").notNull(),
  trialPeriodDays: integer("trial_period_days"),
  subscriptionPeriodInterval: text("subscription_period_interval"),
  paymentPeriodInterval: text("payment_period_interval"),
  subscriptionPeriodCount: integer("subscription_period_count"),
  paymentFrequencyCount: integer("payment_frequency_count"),
  nextBillingDate: timestamp("next_billing_date", {
    mode: "string",
    withTimezone: true,
  }).notNull(),
  previousBillingDate: timestamp("previous_billing_date", {
    mode: "string",
    withTimezone: true,
  }).notNull(),
  customerId: text("customer_id").notNull(),
  customerName: text("customer_name"),
  customerEmail: text("customer_email").notNull(),
  metadata: jsonb("metadata"),
  discountId: text("discount_id"),
  cancelledAt: timestamp("cancelled_at", {
    mode: "string",
    withTimezone: true,
  }),
  cancelAtNextBillingDate: boolean("cancel_at_next_billing_date"),
  billing: jsonb("billing").notNull(),
  onDemand: boolean("on_demand"),
  addons: jsonb("addons"),
});

Core Fields

subscription_id

text

required

Primary KeyUnique identifier for the subscription from Dodo Payments.Used for upsert operations in webhook handlers.

user_id

text

Foreign Key → users.supabase_user_idLinks subscription to a user in your application.Nullable to support subscriptions created before user account exists.

status

text

required

Current status of the subscription.Possible values:

active - Subscription is active
cancelled - Subscription has been cancelled
expired - Subscription has expired
failed - Subscription failed (e.g., payment failure)
on_hold - Subscription is on hold

Updated by webhook events.

Pricing Fields

recurring_pre_tax_amount

real

required

Recurring subscription amount before tax.Stored as a floating-point number.

tax_inclusive

boolean

required

Whether the pricing includes tax.

true - Price includes tax
false - Tax added on top of price

currency

text

required

Three-letter ISO currency code (e.g., “USD”, “EUR”, “GBP”).

quantity

integer

required

Number of subscription units.Used for quantity-based pricing.

Product & Plan Fields

product_id

text

required

Dodo Payments product identifier.Links to your product catalog in Dodo Payments.

trial_period_days

integer

NullableLength of trial period in days.null if no trial period.

subscription_period_interval

text

NullableInterval unit for the subscription period (e.g., “month”, “year”).

subscription_period_count

integer

NullableNumber of subscription period intervals.Combined with subscription_period_interval to define total subscription length.

payment_period_interval

text

NullableInterval unit for billing frequency.Mapped from webhook field payment_frequency_interval (note the naming difference).Source: supabase/functions/dodo-webhook/index.ts:181

payment_frequency_count

integer

NullableNumber of intervals between payments.Example: payment_frequency_count: 3 with payment_period_interval: "month" means billing every 3 months.

Billing Fields

next_billing_date

timestamp

required

With timezoneISO 8601 timestamp of the next scheduled billing.Updated with each renewal.

previous_billing_date

timestamp

required

With timezoneISO 8601 timestamp of the last billing.

billing

jsonb

required

Billing information object.Contains billing address and contact details from Dodo Payments.

Customer Fields

customer_id

text

required

Dodo Payments customer identifier.Corresponds to dodo_customer_id in the users table.

customer_email

text

required

Customer email address.

customer_name

text

NullableCustomer full name.

Optional Fields

metadata

jsonb

NullableCustom metadata object from Dodo Payments.Use for storing custom attributes and tags.

discount_id

text

NullableApplied discount identifier.Links to discount/coupon in Dodo Payments.

addons

jsonb

NullableSubscription addons object.Contains additional products or features added to the base subscription.

on_demand

boolean

NullableWhether this is an on-demand subscription.

Cancellation Fields

cancelled_at

timestamp

Nullable, With timezoneISO 8601 timestamp when subscription was cancelled.Set by subscription.cancelled webhook event.

cancel_at_next_billing_date

boolean

NullableWhether subscription will cancel at next billing date.

true - Subscription will end at next billing
false or null - Subscription continues normally

Timestamps

created_at

timestamp

required

With timezoneISO 8601 timestamp when subscription was created.

Relations

Defined at: lib/drizzle/schema.ts:119-124

export const subscriptionsRelations = relations(subscriptions, ({ one }) => ({
  user: one(users, {
    fields: [subscriptions.userId],
    references: [users.supabaseUserId],
  }),
}));

user (many-to-one)

Links subscription to the associated user account.

Foreign key: subscriptions.user_id → users.supabase_user_id

TypeScript Types

Defined at: lib/drizzle/schema.ts:129-130

export type SelectSubscription = typeof subscriptions.$inferSelect;
export type InsertSubscription = typeof subscriptions.$inferInsert;

SelectSubscription

Type for reading subscription records from the database.

type SelectSubscription = {
  subscriptionId: string;
  userId: string | null;
  recurringPreTaxAmount: number;
  taxInclusive: boolean;
  currency: string;
  status: string;
  createdAt: string;
  productId: string;
  quantity: number;
  trialPeriodDays: number | null;
  subscriptionPeriodInterval: string | null;
  paymentPeriodInterval: string | null;
  subscriptionPeriodCount: number | null;
  paymentFrequencyCount: number | null;
  nextBillingDate: string;
  previousBillingDate: string;
  customerId: string;
  customerName: string | null;
  customerEmail: string;
  metadata: unknown | null;
  discountId: string | null;
  cancelledAt: string | null;
  cancelAtNextBillingDate: boolean | null;
  billing: unknown;
  onDemand: boolean | null;
  addons: unknown | null;
}

InsertSubscription

Type for inserting new subscription records.

type InsertSubscription = {
  subscriptionId: string;
  userId?: string | null;
  recurringPreTaxAmount: number;
  taxInclusive: boolean;
  currency: string;
  status: string;
  createdAt: string;
  productId: string;
  quantity: number;
  trialPeriodDays?: number | null;
  subscriptionPeriodInterval?: string | null;
  paymentPeriodInterval?: string | null;
  subscriptionPeriodCount?: number | null;
  paymentFrequencyCount?: number | null;
  nextBillingDate: string;
  previousBillingDate: string;
  customerId: string;
  customerName?: string | null;
  customerEmail: string;
  metadata?: unknown | null;
  discountId?: string | null;
  cancelledAt?: string | null;
  cancelAtNextBillingDate?: boolean | null;
  billing: unknown;
  onDemand?: boolean | null;
  addons?: unknown | null;
}

Usage in Webhook Handlers

Upserting Subscription Data

Source: supabase/functions/dodo-webhook/index.ts:164-199

async function manageSubscription(event: any) {
  const data = {
    subscription_id: event.data.subscription_id,
    addons: event.data.addons,
    billing: event.data.billing,
    cancel_at_next_billing_date: event.data.cancel_at_next_billing_date,
    cancelled_at: event.data.cancelled_at,
    created_at: event.data.created_at,
    currency: event.data.currency,
    customer_email: event.data.customer.email,
    customer_name: event.data.customer.name,
    customer_id: event.data.customer.customer_id,
    discount_id: event.data.discount_id,
    metadata: event.data.metadata,
    next_billing_date: event.data.next_billing_date,
    on_demand: event.data.on_demand,
    payment_frequency_count: event.data.payment_frequency_count,
    payment_period_interval: event.data.payment_frequency_interval,
    previous_billing_date: event.data.previous_billing_date,
    product_id: event.data.product_id,
    quantity: event.data.quantity,
    recurring_pre_tax_amount: event.data.recurring_pre_tax_amount,
    status: event.data.status,
    subscription_period_count: event.data.subscription_period_count,
    subscription_period_interval: event.data.subscription_period_interval,
    tax_inclusive: event.data.tax_inclusive,
    trial_period_days: event.data.trial_period_days,
  };

  const { error } = await supabase.from("subscriptions").upsert(data, {
    onConflict: "subscription_id",
  });

  if (error) throw error;
  console.log(`Subscription ${data.subscription_id} upserted successfully.`);
}

Handled by webhook events:

subscription.active
subscription.plan_changed
subscription.renewed
subscription.on_hold
subscription.cancelled
subscription.expired
subscription.failed

Query Examples

Get User’s Active Subscription

import { db } from "@/lib/drizzle/db";
import { subscriptions } from "@/lib/drizzle/schema";
import { eq, and } from "drizzle-orm";

const [activeSubscription] = await db
  .select()
  .from(subscriptions)
  .where(
    and(
      eq(subscriptions.userId, userId),
      eq(subscriptions.status, "active")
    )
  )
  .limit(1);

Get Subscriptions Due for Renewal

import { db } from "@/lib/drizzle/db";
import { subscriptions } from "@/lib/drizzle/schema";
import { lte, eq } from "drizzle-orm";

const today = new Date().toISOString();

const dueSubscriptions = await db
  .select()
  .from(subscriptions)
  .where(
    and(
      eq(subscriptions.status, "active"),
      lte(subscriptions.nextBillingDate, today)
    )
  );

Calculate Monthly Recurring Revenue (MRR)

import { db } from "@/lib/drizzle/db";
import { subscriptions } from "@/lib/drizzle/schema";
import { eq, sum } from "drizzle-orm";

const [result] = await db
  .select({
    total: sum(subscriptions.recurringPreTaxAmount),
  })
  .from(subscriptions)
  .where(eq(subscriptions.status, "active"));

const mrr = result?.total || 0;

Get Subscriptions with Addons

import { db } from "@/lib/drizzle/db";
import { subscriptions } from "@/lib/drizzle/schema";
import { isNotNull } from "drizzle-orm";

const subscriptionsWithAddons = await db
  .select()
  .from(subscriptions)
  .where(isNotNull(subscriptions.addons));

Users Schema

View the users table schema

Payments Schema

View the payments table schema

Server Actions

Webhook Handlers

Database Schema

Table Definition

Core Fields

Pricing Fields

Product & Plan Fields

Billing Fields

Customer Fields

Optional Fields

Cancellation Fields

Timestamps

Relations

user (many-to-one)

TypeScript Types

SelectSubscription

InsertSubscription

Usage in Webhook Handlers

Upserting Subscription Data

Query Examples

Get User’s Active Subscription

Get Subscriptions Due for Renewal

Calculate Monthly Recurring Revenue (MRR)

Get Subscriptions with Addons

Users Schema

Payments Schema

Build docs developers (and LLMs) love

Server Actions

Webhook Handlers

Database Schema

​Table Definition

​Core Fields

​Pricing Fields

​Product & Plan Fields

​Billing Fields

​Customer Fields

​Optional Fields

​Cancellation Fields

​Timestamps

​Relations

​user (many-to-one)

​TypeScript Types

​SelectSubscription

​InsertSubscription

​Usage in Webhook Handlers

​Upserting Subscription Data

​Query Examples

​Get User’s Active Subscription

​Get Subscriptions Due for Renewal

​Calculate Monthly Recurring Revenue (MRR)

​Get Subscriptions with Addons

​Related Tables

Users Schema

Payments Schema

Build docs developers (and LLMs) love

Table Definition

Core Fields

Pricing Fields

Product & Plan Fields

Billing Fields

Customer Fields

Optional Fields

Cancellation Fields

Timestamps

Relations

user (many-to-one)

TypeScript Types

SelectSubscription

InsertSubscription

Usage in Webhook Handlers

Upserting Subscription Data

Query Examples

Get User’s Active Subscription

Get Subscriptions Due for Renewal

Calculate Monthly Recurring Revenue (MRR)

Get Subscriptions with Addons

Related Tables