Payments Table Schema

The payments table stores all payment transaction records from Dodo Payments, including payment details, card information, and complete webhook data. Source: ~/workspace/source/lib/drizzle/schema.ts:71-108

Table Definition

export const payments = pgTable("payments", {
  paymentId: text("payment_id").primaryKey(),
  status: text("status").notNull(),
  totalAmount: real("total_amount").notNull(),
  currency: text("currency").notNull(),
  paymentMethod: text("payment_method"),
  paymentMethodType: text("payment_method_type"),
  customerId: text("customer_id").notNull(),
  customerName: text("customer_name"),
  customerEmail: text("customer_email").notNull(),
  createdAt: timestamp("created_at", {
    mode: "string",
    withTimezone: true,
  }).notNull(),
  subscriptionId: text("subscription_id").notNull(),
  brandId: text("brand_id").notNull(),
  digitalProductDelivered: boolean("digital_product_delivered"),
  metadata: jsonb("metadata"),
  webhookData: jsonb("webhook_data").notNull(),
  billing: jsonb("billing").notNull(),
  businessId: text("business_id").notNull(),
  cardIssuingCountry: text("card_issuing_country"),
  cardLastFour: text("card_last_four"),
  cardNetwork: text("card_network"),
  cardType: text("card_type"),
  discountId: text("discount_id"),
  disputes: jsonb("disputes"),
  errorCode: text("error_code"),
  errorMessage: text("error_message"),
  paymentLink: text("payment_link"),
  productCart: jsonb("product_cart"),
  refunds: jsonb("refunds"),
  settlementAmount: real("settlement_amount"),
  settlementCurrency: text("settlement_currency"),
  settlementTax: real("settlement_tax"),
  tax: real("tax"),
  updatedAt: timestamp("updated_at", { mode: "string", withTimezone: true }),
});

Core Fields

payment_id

text

required

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

status

text

required

Current status of the payment.Possible values:

succeeded - Payment completed successfully
failed - Payment failed
processing - Payment is being processed
cancelled - Payment was cancelled

Updated by webhook events:

payment.succeeded
payment.failed
payment.processing
payment.cancelled

total_amount

real

required

Total payment amount including tax.Stored as a floating-point number.

currency

text

required

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

Payment Method Fields

payment_method

text

NullablePayment method identifier from Dodo Payments.

payment_method_type

text

NullableType of payment method (e.g., “card”, “bank_transfer”).

card_last_four

text

NullableLast four digits of the card number.Only populated for card payments.

card_network

text

NullableCard network (e.g., “Visa”, “Mastercard”, “American Express”).

card_type

text

NullableCard type (e.g., “credit”, “debit”, “prepaid”).

card_issuing_country

text

NullableISO country code of the card issuing bank.

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.

Subscription & Product Fields

subscription_id

text

required

Associated subscription identifier.Links payment to subscription in the subscriptions table.

product_cart

jsonb

NullableProduct cart details including items purchased.

digital_product_delivered

boolean

NullableWhether digital products have been delivered.

Business Fields

brand_id

text

required

Brand identifier from Dodo Payments.

business_id

text

required

Business identifier from Dodo Payments.

Financial Fields

tax

real

NullableTax amount in payment currency.

settlement_amount

real

NullableAmount you receive after fees in settlement currency.

settlement_currency

text

NullableCurrency code for settlement (may differ from payment currency).

settlement_tax

real

NullableTax amount in settlement currency.

Optional Fields

billing

jsonb

required

Billing information object.Contains billing address and contact details.

metadata

jsonb

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

discount_id

text

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

payment_link

text

NullableURL for the payment link if payment was created via link.

webhook_data

jsonb

required

Complete webhook event data.Stores the entire webhook payload for reference and debugging.Source: supabase/functions/dodo-webhook/index.ts:135

Error Fields

error_code

text

NullableError code for failed payments.Populated when status is “failed”.

error_message

text

NullableHuman-readable error message for failed payments.

Refunds & Disputes

refunds

jsonb

NullableRefund information object.Contains refund history and details if payment has been refunded.

disputes

jsonb

NullableDispute information object.Contains chargeback and dispute details if applicable.

Timestamps

created_at

timestamp

required

With timezoneISO 8601 timestamp when payment was created.

updated_at

timestamp

Nullable, With timezoneISO 8601 timestamp when payment was last updated.

TypeScript Types

Defined at: lib/drizzle/schema.ts:132-133

export type SelectPayment = typeof payments.$inferSelect;
export type InsertPayment = typeof payments.$inferInsert;

SelectPayment

Type for reading payment records from the database.

type SelectPayment = {
  paymentId: string;
  status: string;
  totalAmount: number;
  currency: string;
  paymentMethod: string | null;
  paymentMethodType: string | null;
  customerId: string;
  customerName: string | null;
  customerEmail: string;
  createdAt: string;
  subscriptionId: string;
  brandId: string;
  digitalProductDelivered: boolean | null;
  metadata: unknown | null;
  webhookData: unknown;
  billing: unknown;
  businessId: string;
  cardIssuingCountry: string | null;
  cardLastFour: string | null;
  cardNetwork: string | null;
  cardType: string | null;
  discountId: string | null;
  disputes: unknown | null;
  errorCode: string | null;
  errorMessage: string | null;
  paymentLink: string | null;
  productCart: unknown | null;
  refunds: unknown | null;
  settlementAmount: number | null;
  settlementCurrency: string | null;
  settlementTax: number | null;
  tax: number | null;
  updatedAt: string | null;
}

InsertPayment

Type for inserting new payment records.

type InsertPayment = {
  paymentId: string;
  status: string;
  totalAmount: number;
  currency: string;
  paymentMethod?: string | null;
  paymentMethodType?: string | null;
  customerId: string;
  customerName?: string | null;
  customerEmail: string;
  createdAt: string;
  subscriptionId: string;
  brandId: string;
  digitalProductDelivered?: boolean | null;
  metadata?: unknown | null;
  webhookData: unknown;
  billing: unknown;
  businessId: string;
  cardIssuingCountry?: string | null;
  cardLastFour?: string | null;
  cardNetwork?: string | null;
  cardType?: string | null;
  discountId?: string | null;
  disputes?: unknown | null;
  errorCode?: string | null;
  errorMessage?: string | null;
  paymentLink?: string | null;
  productCart?: unknown | null;
  refunds?: unknown | null;
  settlementAmount?: number | null;
  settlementCurrency?: string | null;
  settlementTax?: number | null;
  tax?: number | null;
  updatedAt?: string | null;
}

Usage in Webhook Handlers

Upserting Payment Data

Source: supabase/functions/dodo-webhook/index.ts:120-162

async function managePayment(event: any) {
  const data = {
    payment_id: event.data.payment_id,
    brand_id: event.data.brand_id,
    created_at: event.data.created_at,
    currency: event.data.currency,
    metadata: event.data.metadata,
    payment_method: event.data.payment_method,
    payment_method_type: event.data.payment_method_type,
    status: event.data.status,
    subscription_id: event.data.subscription_id,
    total_amount: event.data.total_amount,
    customer_email: event.data.customer.email,
    customer_name: event.data.customer.name,
    customer_id: event.data.customer.customer_id,
    webhook_data: event,
    billing: event.data.billing,
    business_id: event.data.business_id,
    card_issuing_country: event.data.card_issuing_country,
    card_last_four: event.data.card_last_four,
    card_network: event.data.card_network,
    card_type: event.data.card_type,
    discount_id: event.data.discount_id,
    disputes: event.data.disputes,
    error_code: event.data.error_code,
    error_message: event.data.error_message,
    payment_link: event.data.payment_link,
    product_cart: event.data.product_cart,
    refunds: event.data.refunds,
    settlement_amount: event.data.settlement_amount,
    settlement_currency: event.data.settlement_currency,
    settlement_tax: event.data.settlement_tax,
    tax: event.data.tax,
    updated_at: event.data.updated_at,
  };

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

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

Handled by webhook events:

payment.succeeded
payment.failed
payment.processing
payment.cancelled

Query Examples

Get Customer’s Payment History

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

const paymentHistory = await db
  .select()
  .from(payments)
  .where(eq(payments.customerId, dodoCustomerId))
  .orderBy(desc(payments.createdAt));

Get Successful Payments for Subscription

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

const successfulPayments = await db
  .select()
  .from(payments)
  .where(
    and(
      eq(payments.subscriptionId, subscriptionId),
      eq(payments.status, "succeeded")
    )
  );

Get Failed Payments

import { db } from "@/lib/drizzle/db";
import { payments } from "@/lib/drizzle/schema";
import { eq } from "drizzle-orm";

const failedPayments = await db
  .select({
    paymentId: payments.paymentId,
    customerEmail: payments.customerEmail,
    totalAmount: payments.totalAmount,
    currency: payments.currency,
    errorCode: payments.errorCode,
    errorMessage: payments.errorMessage,
    createdAt: payments.createdAt,
  })
  .from(payments)
  .where(eq(payments.status, "failed"));

Calculate Total Revenue

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

const [result] = await db
  .select({
    total: sum(payments.totalAmount),
  })
  .from(payments)
  .where(eq(payments.status, "succeeded"));

const totalRevenue = result?.total || 0;

Get Payments with Disputes

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

const disputedPayments = await db
  .select()
  .from(payments)
  .where(isNotNull(payments.disputes));

Get Refunded Payments

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

const refundedPayments = await db
  .select()
  .from(payments)
  .where(isNotNull(payments.refunds));

Users Schema

View the users table schema

Subscriptions Schema

View the subscriptions table schema

Server Actions

Webhook Handlers

Database Schema

Table Definition

Core Fields

Payment Method Fields

Customer Fields

Subscription & Product Fields

Business Fields

Financial Fields

Optional Fields

Error Fields

Refunds & Disputes

Timestamps

TypeScript Types

SelectPayment

InsertPayment

Usage in Webhook Handlers

Upserting Payment Data

Query Examples

Get Customer’s Payment History

Get Successful Payments for Subscription

Get Failed Payments

Calculate Total Revenue

Get Payments with Disputes

Get Refunded Payments

Users Schema

Subscriptions Schema

Build docs developers (and LLMs) love

Server Actions

Webhook Handlers

Database Schema

​Table Definition

​Core Fields

​Payment Method Fields

​Customer Fields

​Subscription & Product Fields

​Business Fields

​Financial Fields

​Optional Fields

​Error Fields

​Refunds & Disputes

​Timestamps

​TypeScript Types

​SelectPayment

​InsertPayment

​Usage in Webhook Handlers

​Upserting Payment Data

​Query Examples

​Get Customer’s Payment History

​Get Successful Payments for Subscription

​Get Failed Payments

​Calculate Total Revenue

​Get Payments with Disputes

​Get Refunded Payments

​Related Tables

Users Schema

Subscriptions Schema

Build docs developers (and LLMs) love

Table Definition

Core Fields

Payment Method Fields

Customer Fields

Subscription & Product Fields

Business Fields

Financial Fields

Optional Fields

Error Fields

Refunds & Disputes

Timestamps

TypeScript Types

SelectPayment

InsertPayment

Usage in Webhook Handlers

Upserting Payment Data

Query Examples

Get Customer’s Payment History

Get Successful Payments for Subscription

Get Failed Payments

Calculate Total Revenue

Get Payments with Disputes

Get Refunded Payments

Related Tables