웹훅 구현 부록

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'
}

create table lemon_webhook_inbox (
  id bigserial primary key,
  dedupe_key text not null unique,
  event_name text not null,
  resource_type text not null,
  resource_id text not null,
  test_mode boolean not null,
  payload jsonb not null,
  status text not null default 'pending',
  received_at timestamptz not null default now(),
  processed_at timestamptz,
  last_error text
);

create table billing_subscriptions (
  id bigserial primary key,
  workspace_id text not null,
  lemon_subscription_id text not null unique,
  lemon_customer_id text,
  variant_id text not null,
  status text not null,
  ends_at timestamptz,
  renews_at timestamptz,
  entitlement_status text not null,
  snapshot_updated_at timestamptz,
  reconciled_at timestamptz
);

import { createHmac, createHash, timingSafeEqual } from 'node:crypto'

export async function POST(request: Request) {
  const secret = process.env.LEMON_WEBHOOK_SECRET
  const signature = request.headers.get('X-Signature')
  const eventName = request.headers.get('X-Event-Name')
  const rawBody = await request.text()
  if (!secret || !signature || !eventName) return new Response('Invalid request', { status: 400 })

  const expected = createHmac('sha256', secret).update(rawBody).digest('hex')
  const actualBytes = Buffer.from(signature, 'utf8')
  const expectedBytes = Buffer.from(expected, 'utf8')
  if (actualBytes.length !== expectedBytes.length || !timingSafeEqual(actualBytes, expectedBytes)) {
    return new Response('Invalid signature', { status: 401 })
  }

  const event = JSON.parse(rawBody)
  if (event.meta?.event_name !== eventName || !event.data?.id || !event.data?.type) {
    return new Response('Invalid payload', { status: 400 })
  }
  const digest = createHash('sha256').update(rawBody).digest('hex')
  const dedupeKey = `${eventName}:${event.data.type}:${event.data.id}:${digest}`
  // 앱에서 구현: inbox와 작업 outbox를 한 DB 트랜잭션으로 기록한다.
  await persistInboxAndOutbox({ dedupeKey, eventName, event })
  return new Response('ok', { status: 200 })
}

inbox pending
→ eventName과 resource ID 확인
→ Lemon Squeezy API에서 최신 order/subscription 조회
→ 내부 주문·구독 snapshot upsert
→ variant 매핑과 status/ends_at로 권한 재계산
→ 이메일·분석·회계 outbox 기록
→ inbox processed