Menu

订单和订阅管理

本文档说明如何查询和管理订单、订阅，以及如何使用客户门户管理订阅。

订单管理

订单数据结构

订单记录存储在 orders 表中，包含以下关键字段：

{
  id: string;                      // 订单 ID（UUID）
  userId: string;                   // 用户 ID
  provider: 'stripe' | 'creem';    // 支付提供商
  providerOrderId: string;         // 提供商订单 ID（唯一）
  orderType: string;                // 订单类型
  status: string;                   // 订单状态
  planId: string;                   // 关联的计划 ID
  subscriptionId?: string;          // 关联的订阅 ID（如果是订阅订单）
  amountTotal: string;              // 订单总金额
  currency: string;                 // 货币代码
  createdAt: Date;                 // 创建时间
  updatedAt: Date;                 // 更新时间
  metadata: object;                 // 元数据（JSONB）
}

订单类型

系统支持以下订单类型：

  • one_time_purchase: 一次性购买
  • subscription_initial: 订阅初始支付（Stripe）
  • subscription_renewal: 订阅续费（Stripe）
  • recurring: 订阅支付（Creem）
  • refund: 退款

订单状态

订单可能的状态：

  • succeeded: 支付成功
  • pending: 待处理
  • failed: 支付失败
  • refunded: 已全额退款
  • partially_refunded: 部分退款

用户查询订单

用户可以通过 getMyOrders 函数查询自己的订单：

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;
 
  // 构建查询条件
  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;
 
  // 查询订单
  const orders = await db
    .select()
    .from(ordersSchema)
    .where(whereClause)
    .orderBy(desc(ordersSchema.createdAt))
    .offset(pageIndex * pageSize)
    .limit(pageSize);
 
  // 查询总数
  const totalCountResult = await db
    .select({ value: count() })
    .from(ordersSchema)
    .where(whereClause);
 
  return actionResponse.success({
    orders,
    totalCount: totalCountResult[0]?.value ?? 0,
  });
}

管理员查询订单

管理员可以通过 getOrders 函数查询所有订单：

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;
 
  // 查询订单（包含用户信息）
  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);
 
  // 查询总数
  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,
  });
}

订单列表页面

用户订单页面

创建 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>
  );
}

管理员订单页面

创建 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>
  );
}

订阅管理

订阅数据结构

订阅记录存储在 subscriptions 表中：

{
  id: string;                      // 订阅 ID（UUID）
  userId: string;                  // 用户 ID
  planId: string;                  // 关联的计划 ID
  provider: 'stripe' | 'creem';   // 支付提供商
  subscriptionId: string;          // 提供商订阅 ID（唯一）
  customerId: string;              // 客户 ID
  status: string;                  // 订阅状态
  currentPeriodStart: Date;        // 当前周期开始时间
  currentPeriodEnd: Date;           // 当前周期结束时间
  cancelAtPeriodEnd: boolean;     // 是否在周期结束时取消
  canceledAt?: Date;               // 取消时间
  endedAt?: Date;                  // 结束时间
  trialStart?: Date;               // 试用开始时间
  trialEnd?: Date;                 // 试用结束时间
  metadata: object;                // 元数据（JSONB）
}

订阅状态

订阅可能的状态：

  • active: 活跃
  • trialing: 试用中
  • past_due: 逾期
  • canceled: 已取消
  • incomplete: 不完整
  • incomplete_expired: 不完整已过期
  • unpaid: 未支付
  • paused: 已暂停（Creem）

订阅同步

系统通过 Webhook 自动同步订阅状态。你也可以手动同步：

Stripe 订阅同步

actions/stripe/index.ts
export async function syncSubscriptionData(
  subscriptionId: string,
  customerId: string,
  initialMetadata?: Record<string, any>
): Promise<void> {
  // 1. 从 Stripe 获取订阅信息
  const subscription = await stripe.subscriptions.retrieve(subscriptionId, {
    expand: ['default_payment_method', 'customer']
  });
 
  // 2. 解析用户 ID 和计划 ID
  let userId = subscription.metadata?.userId;
  let planId = subscription.metadata?.planId;
 
  // 如果元数据中没有，尝试从数据库查询
  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. 构建订阅数据
  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. 插入或更新订阅记录
  await db
    .insert(subscriptionsSchema)
    .values(subscriptionData)
    .onConflictDoUpdate({
      target: subscriptionsSchema.subscriptionId,
      set: subscriptionData,
    });
}

Creem 订阅同步

actions/creem/index.ts
export async function syncCreemSubscriptionData(
  subscriptionId: string,
  initialMetadata?: Record<string, any>
): Promise<void> {
  // 1. 从 Creem 获取订阅信息
  const subscription = await retrieveCreemSubscription(subscriptionId);
 
  // 2. 解析用户 ID 和计划 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. 构建订阅数据
  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. 插入或更新订阅记录
  await db
    .insert(subscriptionsSchema)
    .values(subscriptionData)
    .onConflictDoUpdate({
      target: subscriptionsSchema.subscriptionId,
      set: subscriptionData,
    });
}

客户门户

客户门户允许用户管理他们的订阅，包括：

  • 更新支付方式
  • 取消订阅
  • 查看发票
  • 更新账单信息

Stripe 客户门户

创建 Stripe 客户门户会话：

actions/stripe/index.ts
export async function createStripePortalSession(): Promise<void> {
  const session = await getSession();
  const user = session?.user;
  if (!user) {
    redirect('/login');
  }
 
  // 1. 获取用户的 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. 构建返回 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. 创建门户会话
  const portalSession = await stripe.billingPortal.sessions.create({
    customer: profile.stripeCustomerId,
    return_url: returnUrl,
  });
 
  // 4. 重定向到门户
  redirect(portalSession.url);
}

Creem 客户门户

创建 Creem 客户门户链接：

actions/creem/portal.ts
export async function createCreemPortalSession(): Promise<void> {
  const session = await getSession();
  const user = session?.user;
  if (!user) {
    redirect('/login');
  }
 
  // 1. 获取用户的 Creem 订阅
  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. 创建门户链接
  const portalUrl = await createCreemCustomerPortalLink(subscription.customerId);
 
  if (!portalUrl) {
    throw new Error('Failed to create Creem portal link');
  }
 
  // 3. 重定向到门户
  redirect(portalUrl);
}

订阅管理页面

创建订阅管理页面，显示用户当前的订阅状态和门户入口：

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');
 
  // 获取用户权益
  const benefits = await getUserBenefits(user.id);
 
  // 获取用户的订阅提供商
  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>
  );
}

积分历史查询

用户可以查看自己的积分使用历史：

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,
  });
}

积分历史页面

创建 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>
  );
}

常见问题

Q: 如何查询特定用户的订单？

A: 管理员可以使用 getOrders 函数，通过 filter 参数搜索用户邮箱或订单 ID。

Q: 订阅状态是如何更新的？

A: 订阅状态通过 Webhook 自动更新。当支付提供商发送订阅更新事件时，系统会自动同步状态。

Q: 用户可以取消订阅吗？

A: 可以。用户可以通过客户门户取消订阅。取消后，订阅会在当前周期结束时生效。

Q: 如何查看订阅的详细信息？

A: 查询 subscriptions 表获取订阅的详细信息，包括状态、周期、取消时间等。