Webhook 구현 부록

create table paddle_webhook_events (
  id bigserial primary key,
  notification_id text not null unique,
  event_id text not null unique,
  event_type text not null,
  occurred_at timestamptz not null,
  payload jsonb not null,
  signature_header text not null,
  processing_status text not null default 'pending',
  retry_count integer not null default 0,
  last_error text,
  received_at timestamptz not null default now(),
  processed_at timestamptz
);

create index paddle_webhook_events_status_idx
  on paddle_webhook_events (processing_status, received_at);

create index paddle_webhook_events_event_id_idx
  on paddle_webhook_events (event_id);

create table billing_subscriptions (
  id bigserial primary key,
  workspace_id text not null,
  paddle_customer_id text,
  paddle_subscription_id text unique,
  paddle_status text not null,
  current_price_id text,
  next_billed_at timestamptz,
  entitlement_status text not null,
  last_event_occurred_at timestamptz,
  updated_at timestamptz not null default now()
);

export async function POST(request: Request) {
  const rawBody = await request.text()
  const signature = request.headers.get('Paddle-Signature')
  const endpointSecret = process.env.PADDLE_WEBHOOK_SECRET

  if (!signature || !endpointSecret) return new Response('Invalid webhook configuration', { status: 400 })

  // Paddle SDK verifier 또는 아래 검증 요건을 충족한 함수만 사용
  verifyPaddleSignature({ rawBody, signature, endpointSecret, toleranceSeconds: 5 })

  const event = JSON.parse(rawBody)
  await saveEventIfNew({
    notificationId: event.notification_id,
    eventId: event.event_id,
    eventType: event.event_type,
    occurredAt: event.occurred_at,
    payload: event,
    signatureHeader: signature,
  })

  await enqueueBillingJob(event.notification_id)

  return new Response('ok', { status: 200 })
}

type EntitlementStatus = 'full_access' | 'grace_access' | 'no_paid_access'

function resolveEntitlement(snapshot: {
  status: string
  currentPriceIds: string[]
  nextBilledAt: string | null
  scheduledChange?: { action: string; effectiveAt: string } | null
}): EntitlementStatus {
  if (snapshot.status === 'active' || snapshot.status === 'trialing') {
    return 'full_access'
  }

  if (snapshot.status === 'past_due') {
    return 'grace_access'
  }

  return 'no_paid_access'
}