Order and Subscription Management

This document explains how to query and manage orders and subscriptions, as well as how to use the customer portal to manage subscriptions.

Order Management

Order Data Structure

Order records are stored in the orders table, containing the following key fields:

{
  id: string;                      // Order ID (UUID)
  userId: string;                   // User ID
  provider: 'stripe' | 'creem';    // Payment provider
  providerOrderId: string;         // Provider order ID (unique)
  orderType: string;                // Order type
  status: string;                   // Order status
  planId: string;                   // Associated plan ID
  subscriptionId?: string;          // Associated subscription ID (if subscription order)
  amountTotal: string;              // Order total amount
  currency: string;                 // Currency code
  createdAt: Date;                 // Creation time
  updatedAt: Date;                 // Update time
  metadata: object;                 // Metadata (JSONB)
}

Order Types

The system supports the following order types:

one_time_purchase: One-time purchase
subscription_initial: Subscription initial payment (Stripe)
subscription_renewal: Subscription renewal (Stripe)
recurring: Subscription payment (Creem)
refund: Refund

Order Status

Possible order statuses:

succeeded: Payment succeeded
pending: Pending
failed: Payment failed
refunded: Fully refunded
partially_refunded: Partially refunded

User Order Query

Users can query their own orders through the getMyOrders function:

actions/orders/user.ts

export async function getMyOrders(params: {
  pageIndex?: number;
  pageSize?: number;
  filter?: string;
  provider?: string;
  orderType?: string;
  status?: string;
}): Promise<GetMyOrdersResult> {
  const session = await getSession();
  const user = session?.user;
  if (!user) return actionResponse.unauthorized();
 
  const { pageIndex = 0, pageSize = 10, filter, provider, orderType, status } = params;
 
  // Build query conditions
  const baseWhere = eq(ordersSchema.userId, user.id);
  const optionalConditions: SQL[] = [];
 
  if (provider) {
    optionalConditions.push(eq(ordersSchema.provider, provider));
  }
  if (orderType) {
    optionalConditions.push(eq(ordersSchema.orderType, orderType));
  }
  if (status) {
    optionalConditions.push(eq(ordersSchema.status, status));
  }
  if (filter) {
    optionalConditions.push(
      or(
        ilike(ordersSchema.providerOrderId, `%${filter}%`),
        sql`CAST(${ordersSchema.id} AS TEXT) ILIKE ${`%${filter}%`}`
      ) as SQL
    );
  }
 
  const whereClause = optionalConditions.length
    ? (and(baseWhere, ...optionalConditions) as SQL)
    : baseWhere;
 
  // Query orders
  const orders = await db
    .select()
    .from(ordersSchema)
    .where(whereClause)
    .orderBy(desc(ordersSchema.createdAt))
    .offset(pageIndex * pageSize)
    .limit(pageSize);
 
  // Query total count
  const totalCountResult = await db
    .select({ value: count() })
    .from(ordersSchema)
    .where(whereClause);
 
  return actionResponse.success({
    orders,
    totalCount: totalCountResult[0]?.value ?? 0,
  });
}

Admin Order Query

Admins can query all orders through the getOrders function:

actions/orders/admin.ts

export async function getOrders(params: {
  pageIndex?: number;
  pageSize?: number;
  filter?: string;
  provider?: string;
  orderType?: string;
  status?: string;
}): Promise<GetOrdersResult> {
  if (!(await isAdmin())) {
    return actionResponse.forbidden('Admin privileges required.');
  }
 
  const { pageIndex = 0, pageSize = 10, filter, provider, orderType, status } = params;
 
  const conditions = [];
  if (provider) {
    conditions.push(eq(ordersSchema.provider, provider));
  }
  if (orderType) {
    conditions.push(eq(ordersSchema.orderType, orderType));
  }
  if (status) {
    conditions.push(eq(ordersSchema.status, status));
  }
  if (filter) {
    conditions.push(
      or(
        ilike(userSchema.email, `%${filter}%`),
        ilike(ordersSchema.providerOrderId, `%${filter}%`),
        sql`CAST(${ordersSchema.id} AS TEXT) ILIKE ${`%${filter}%`}`
      )
    );
  }
 
  const whereClause = conditions.length > 0 ? and(...conditions) : undefined;
 
  // Query orders (including user information)
  const results = await db
    .select({
      order: ordersSchema,
      user: { email: userSchema.email, name: userSchema.name },
    })
    .from(ordersSchema)
    .leftJoin(userSchema, eq(ordersSchema.userId, userSchema.id))
    .where(whereClause)
    .orderBy(desc(ordersSchema.createdAt))
    .offset(pageIndex * pageSize)
    .limit(pageSize);
 
  // Query total count
  const totalCountResult = await db
    .select({ value: count() })
    .from(ordersSchema)
    .leftJoin(userSchema, eq(ordersSchema.userId, userSchema.id))
    .where(whereClause);
 
  const ordersData = results.map((r) => ({
    ...r.order,
    users: r.user,
  }));
 
  return actionResponse.success({
    orders: ordersData as unknown as OrderWithUser[],
    totalCount: totalCountResult[0].value,
  });
}

Order List Pages

User Order Page

Create app/[locale]/(protected)/dashboard/(user)/my-orders/page.tsx:

import { getMyOrders } from '@/actions/orders/user';
import { MyOrdersDataTable } from './DataTable';
 
export default async function MyOrdersPage({
  searchParams
}: {
  searchParams: { page?: string; filter?: string; provider?: string; status?: string }
}) {
  const pageIndex = parseInt(searchParams.page || '0');
  const result = await getMyOrders({
    pageIndex,
    pageSize: 10,
    filter: searchParams.filter,
    provider: searchParams.provider,
    status: searchParams.status,
  });
 
  if (!result.success) {
    return <div>Error: {result.error}</div>;
  }
 
  return (
    <div className="space-y-6">
      <div>
        <h1 className="text-2xl font-semibold">My Orders</h1>
      </div>
 
      <MyOrdersDataTable
        orders={result.data.orders}
        totalCount={result.data.totalCount}
      />
    </div>
  );
}

Admin Order Page

Create app/[locale]/(protected)/dashboard/(admin)/orders/page.tsx:

import { getOrders } from '@/actions/orders/admin';
import { OrdersDataTable } from './DataTable';
 
export default async function OrdersPage({
  searchParams
}: {
  searchParams: { page?: string; filter?: string; provider?: string; status?: string }
}) {
  const pageIndex = parseInt(searchParams.page || '0');
  const result = await getOrders({
    pageIndex,
    pageSize: 10,
    filter: searchParams.filter,
    provider: searchParams.provider,
    status: searchParams.status,
  });
 
  if (!result.success) {
    return <div>Error: {result.error}</div>;
  }
 
  return (
    <div className="space-y-6">
      <div>
        <h1 className="text-2xl font-semibold">Orders</h1>
      </div>
 
      <OrdersDataTable
        orders={result.data.orders}
        totalCount={result.data.totalCount}
      />
    </div>
  );
}

Subscription Management

Subscription Data Structure

Subscription records are stored in the subscriptions table:

{
  id: string;                      // Subscription ID (UUID)
  userId: string;                  // User ID
  planId: string;                  // Associated plan ID
  provider: 'stripe' | 'creem';   // Payment provider
  subscriptionId: string;          // Provider subscription ID (unique)
  customerId: string;              // Customer ID
  status: string;                  // Subscription status
  currentPeriodStart: Date;        // Current period start time
  currentPeriodEnd: Date;           // Current period end time
  cancelAtPeriodEnd: boolean;     // Whether to cancel at period end
  canceledAt?: Date;               // Cancellation time
  endedAt?: Date;                  // End time
  trialStart?: Date;               // Trial start time
  trialEnd?: Date;                 // Trial end time
  metadata: object;                // Metadata (JSONB)
}

Subscription Status

Possible subscription statuses:

active: Active
trialing: Trialing
past_due: Past due
canceled: Canceled
incomplete: Incomplete
incomplete_expired: Incomplete expired
unpaid: Unpaid
paused: Paused (Creem)

Subscription Synchronization

The system automatically synchronizes subscription status through webhooks. You can also manually synchronize:

Stripe Subscription Synchronization

actions/stripe/index.ts

export async function syncSubscriptionData(
  subscriptionId: string,
  customerId: string,
  initialMetadata?: Record<string, any>
): Promise<void> {
  // 1. Get subscription information from Stripe
  const subscription = await stripe.subscriptions.retrieve(subscriptionId, {
    expand: ['default_payment_method', 'customer']
  });
 
  // 2. Parse user ID and plan ID
  let userId = subscription.metadata?.userId;
  let planId = subscription.metadata?.planId;
 
  // If not in metadata, try querying from database
  if (!userId) {
    const userData = await db
      .select({ id: userSchema.id })
      .from(userSchema)
      .where(eq(userSchema.stripeCustomerId, customerId))
      .limit(1);
    userId = userData[0]?.id;
  }
 
  if (!planId) {
    const priceId = subscription.items.data[0].price.id;
    const planData = await db
      .select({ id: pricingPlansSchema.id })
      .from(pricingPlansSchema)
      .where(eq(pricingPlansSchema.stripePriceId, priceId))
      .limit(1);
    planId = planData[0]?.id;
  }
 
  // 3. Build subscription data
  const subscriptionData = {
    userId,
    planId,
    provider: 'stripe',
    subscriptionId: subscription.id,
    customerId: typeof subscription.customer === 'string' 
      ? subscription.customer 
      : subscription.customer.id,
    priceId: subscription.items.data[0]?.price.id,
    status: subscription.status,
    currentPeriodStart: new Date(subscription.current_period_start * 1000),
    currentPeriodEnd: new Date(subscription.current_period_end * 1000),
    cancelAtPeriodEnd: subscription.cancel_at_period_end,
    canceledAt: subscription.canceled_at 
      ? new Date(subscription.canceled_at * 1000) 
      : null,
    endedAt: subscription.ended_at 
      ? new Date(subscription.ended_at * 1000) 
      : null,
    trialStart: subscription.trial_start 
      ? new Date(subscription.trial_start * 1000) 
      : null,
    trialEnd: subscription.trial_end 
      ? new Date(subscription.trial_end * 1000) 
      : null,
    metadata: {
      ...subscription.metadata,
      ...(initialMetadata && { checkoutSessionMetadata: initialMetadata })
    },
  };
 
  // 4. Insert or update subscription record
  await db
    .insert(subscriptionsSchema)
    .values(subscriptionData)
    .onConflictDoUpdate({
      target: subscriptionsSchema.subscriptionId,
      set: subscriptionData,
    });
}

Creem Subscription Synchronization

actions/creem/index.ts

export async function syncCreemSubscriptionData(
  subscriptionId: string,
  initialMetadata?: Record<string, any>
): Promise<void> {
  // 1. Get subscription information from Creem
  const subscription = await retrieveCreemSubscription(subscriptionId);
 
  // 2. Parse user ID and plan ID
  let userId = subscription.metadata?.userId;
  let planId = subscription.metadata?.planId;
  let productId = subscription.product.id;
 
  if (!userId) {
    const storeSubscription = await db
      .select({ userId: subscriptionsSchema.userId })
      .from(subscriptionsSchema)
      .where(eq(subscriptionsSchema.subscriptionId, subscriptionId))
      .limit(1);
    userId = storeSubscription[0]?.userId;
  }
 
  if (!planId) {
    const planRow = await db
      .select({ id: pricingPlansSchema.id })
      .from(pricingPlansSchema)
      .where(eq(pricingPlansSchema.creemProductId, productId))
      .limit(1);
    planId = planRow[0]?.id;
  }
 
  // 3. Build subscription data
  const subscriptionData = {
    userId,
    planId: planId ?? null,
    provider: 'creem',
    subscriptionId: subscription.id,
    customerId: subscription.customer.id,
    priceId: subscription.items?.[0]?.price_id ?? '',
    productId: productId,
    status: subscription.status,
    currentPeriodStart: toDate(subscription.current_period_start_date),
    currentPeriodEnd: toDate(subscription.current_period_end_date),
    cancelAtPeriodEnd: subscription.status === 'scheduled_cancel',
    canceledAt: toDate(subscription.canceled_at),
    endedAt: subscription.status === 'canceled' 
      ? toDate(subscription.current_period_end_date) 
      : null,
    trialStart: null,
    trialEnd: null,
    metadata: {
      ...metadata,
      creemSubscriptionId: subscription.id,
      creemCustomerId: subscription.customer.id,
      creemProductId: productId,
    },
  };
 
  // 4. Insert or update subscription record
  await db
    .insert(subscriptionsSchema)
    .values(subscriptionData)
    .onConflictDoUpdate({
      target: subscriptionsSchema.subscriptionId,
      set: subscriptionData,
    });
}

Customer Portal

The customer portal allows users to manage their subscriptions, including:

Update payment methods
Cancel subscriptions
View invoices
Update billing information

Stripe Customer Portal

Create a Stripe customer portal session:

actions/stripe/index.ts

export async function createStripePortalSession(): Promise<void> {
  const session = await getSession();
  const user = session?.user;
  if (!user) {
    redirect('/login');
  }
 
  // 1. Get user's Stripe Customer ID
  const profile = await db
    .select({ stripeCustomerId: userSchema.stripeCustomerId })
    .from(userSchema)
    .where(eq(userSchema.id, user.id))
    .limit(1);
 
  if (!profile?.stripeCustomerId) {
    throw new Error('Stripe customer ID not found');
  }
 
  // 2. Build return URL
  const headersList = await headers();
  const domain = headersList.get('x-forwarded-host') || headersList.get('host');
  const protocol = headersList.get('x-forwarded-proto') || 'https';
  const returnUrl = `${protocol}://${domain}${process.env.STRIPE_CUSTOMER_PORTAL_URL}`;
 
  // 3. Create portal session
  const portalSession = await stripe.billingPortal.sessions.create({
    customer: profile.stripeCustomerId,
    return_url: returnUrl,
  });
 
  // 4. Redirect to portal
  redirect(portalSession.url);
}

Creem Customer Portal

Create a Creem customer portal link:

actions/creem/portal.ts

export async function createCreemPortalSession(): Promise<void> {
  const session = await getSession();
  const user = session?.user;
  if (!user) {
    redirect('/login');
  }
 
  // 1. Get user's Creem subscription
  const subscriptionResults = await db
    .select({
      customerId: subscriptionsSchema.customerId,
    })
    .from(subscriptionsSchema)
    .where(
      and(
        eq(subscriptionsSchema.userId, user.id),
        eq(subscriptionsSchema.provider, 'creem')
      )
    )
    .orderBy(desc(subscriptionsSchema.createdAt))
    .limit(1);
 
  const subscription = subscriptionResults[0];
  if (!subscription) {
    throw new Error('Creem subscription not found');
  }
 
  // 2. Create portal link
  const portalUrl = await createCreemCustomerPortalLink(subscription.customerId);
 
  if (!portalUrl) {
    throw new Error('Failed to create Creem portal link');
  }
 
  // 3. Redirect to portal
  redirect(portalUrl);
}

Subscription Management Page

Create a subscription management page that displays the user's current subscription status and portal entry:

app/[locale]/(protected)/dashboard/(user)/subscription/page.tsx

import { createCreemPortalSession } from '@/actions/creem/portal';
import { createStripePortalSession } from '@/actions/stripe';
import { getUserBenefits } from '@/actions/usage/benefits';
import CurrentUserBenefitsDisplay from '@/components/layout/CurrentUserBenefitsDisplay';
import { Button } from '@/components/ui/button';
import { getSession } from '@/lib/auth/server';
import { db } from '@/lib/db';
import { subscriptions as subscriptionsSchema } from '@/lib/db/schema';
import { desc, eq } from 'drizzle-orm';
import { redirect } from 'next/navigation';
import { PortalButton } from './PortalButton';
 
export default async function SubscriptionPage() {
  const session = await getSession();
  const user = session?.user;
  if (!user) redirect('/login');
 
  // Get user benefits
  const benefits = await getUserBenefits(user.id);
 
  // Get user's subscription provider
  const subscriptionResults = await db
    .select({ provider: subscriptionsSchema.provider })
    .from(subscriptionsSchema)
    .where(eq(subscriptionsSchema.userId, user.id))
    .orderBy(desc(subscriptionsSchema.createdAt))
    .limit(1);
 
  const subscriptionProvider = subscriptionResults[0]?.provider || null;
 
  const isMember =
    benefits.subscriptionStatus === 'active' ||
    benefits.subscriptionStatus === 'trialing';
 
  return (
    <div className="space-y-6">
      <div>
        <h1 className="text-2xl font-semibold">Subscription</h1>
      </div>
 
      <div className="rounded-lg border p-6 space-y-4">
        {isMember ? (
          <>
            <CurrentUserBenefitsDisplay />
            {subscriptionProvider === 'stripe' && (
              <>
                <PortalButton
                  provider="stripe"
                  action={createStripePortalSession}
                />
                <p className="text-xs text-muted-foreground">
                  You will be redirected to Stripe to manage your subscription details.
                </p>
              </>
            )}
            {subscriptionProvider === 'creem' && (
              <>
                <PortalButton
                  provider="creem"
                  action={createCreemPortalSession}
                />
                <p className="text-xs text-muted-foreground">
                  You will be redirected to manage your subscription details.
                </p>
              </>
            )}
          </>
        ) : (
          <>
            <p>You are currently not subscribed to any plan.</p>
            <Button asChild>
              <Link href={process.env.NEXT_PUBLIC_PRICING_PATH!}>
                Upgrade Plan
              </Link>
            </Button>
          </>
        )}
      </div>
    </div>
  );
}

Credit History Query

Users can view their credit usage history:

actions/usage/logs.ts

export async function getCreditLogs({
  pageIndex = 0,
  pageSize = 20,
}: ListCreditLogsParams = {}): Promise<ListCreditLogsResult> {
  const session = await getSession();
  const user = session?.user;
  if (!user) return actionResponse.unauthorized();
 
  const logs = await db
    .select()
    .from(creditLogsSchema)
    .where(eq(creditLogsSchema.userId, user.id))
    .orderBy(desc(creditLogsSchema.createdAt))
    .offset(pageIndex * pageSize)
    .limit(pageSize);
 
  const totalCount = await db
    .select({ value: count() })
    .from(creditLogsSchema)
    .where(eq(creditLogsSchema.userId, user.id));
 
  return actionResponse.success({
    logs: logs || [],
    count: totalCount[0]?.value ?? 0,
  });
}

Credit History Page

Create app/[locale]/(protected)/dashboard/(user)/credit-history/page.tsx:

import { getCreditLogs } from '@/actions/usage/logs';
import { CreditHistoryDataTable } from './CreditHistoryDataTable';
 
export default async function CreditHistoryPage({
  searchParams
}: {
  searchParams: { page?: string }
}) {
  const pageIndex = parseInt(searchParams.page || '0');
  const result = await getCreditLogs({
    pageIndex,
    pageSize: 20,
  });
 
  if (!result.success) {
    return <div>Error: {result.error}</div>;
  }
 
  return (
    <div className="space-y-6">
      <div>
        <h1 className="text-2xl font-semibold">Credit History</h1>
      </div>
 
      <CreditHistoryDataTable
        logs={result.data.logs}
        totalCount={result.data.count}
      />
    </div>
  );
}

Frequently Asked Questions

Q: How to query orders for a specific user?

A: Admins can use the getOrders function and search by user email or order ID through the filter parameter.

Q: How is subscription status updated?

A: Subscription status is automatically updated through webhooks. When payment providers send subscription update events, the system automatically synchronizes the status.

Q: Can users cancel subscriptions?

A: Yes. Users can cancel subscriptions through the customer portal. After cancellation, the subscription will take effect at the end of the current period.

Q: How to view detailed subscription information?

A: Query the subscriptions table to get detailed subscription information, including status, period, cancellation time, etc.