Skip to content

📈 Open source, privacy-first web analytics — a cookie-less Google Analytics alternative with built-in error tracking and performance monitoring. Self-host it or use our cloud. Get started for free!

7.5 KB
Raw Blame
/**
 * Backfills the `user_subscription` table from Paddle.
 *
 * Existing users have no subscription history rows, so every subscription that
 * predates the table has to come from Paddle itself. Subscriptions are matched
 * to users by email, which is the only key Paddle's Classic API exposes on the
 * subscription list.
 *
 * Known weakness: a user who changed their Swetrix email after subscribing will
 * not match, and their invoice history stays hidden. Those are reported as
 * unmatched at the end. Going forward the webhooks record `paddleUserId`, which
 * is stable across resubscribes and email changes.
 *
 * Usage (dry run, writes nothing):
 *   node scripts/backfill-user-subscriptions.js
 *
 * Usage (actually insert):
 *   node scripts/backfill-user-subscriptions.js --apply
 */

const path = require('path')
const crypto = require('crypto')
const mysql = require('mysql2/promise')

require('dotenv').config({ path: path.join(__dirname, '..', '.env') })

const APPLY = process.argv.includes('--apply')
const RESULTS_PER_PAGE = 200
const PADDLE_STATES = ['active', 'past_due', 'trialing', 'deleted']
const PADDLE_TIMEOUT_MS = 30_000

const {
  PADDLE_VENDOR_ID,
  PADDLE_API_KEY,
  MYSQL_HOST,
  MYSQL_USER,
  MYSQL_ROOT_PASSWORD,
  MYSQL_DATABASE,
} = process.env

const fetchPaddleSubscriptionsPage = async (state, page) => {
  const body = new URLSearchParams()
  body.set('vendor_id', String(Number(PADDLE_VENDOR_ID)))
  body.set('vendor_auth_code', PADDLE_API_KEY)
  body.set('state', state)
  body.set('page', String(page))
  body.set('results_per_page', String(RESULTS_PER_PAGE))

  const res = await fetch('https://vendors.paddle.com/api/2.0/subscription/users', {
    method: 'POST',
    headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
    body,
    signal: AbortSignal.timeout(PADDLE_TIMEOUT_MS),
  })
  const data = await res.json()

  if (!res.ok || !data?.success) {
    throw new Error(
      `Paddle subscription list failed (state=${state}, page=${page}): ${JSON.stringify(
        data?.error || data,
      )}`,
    )
  }

  return data.response || []
}

const fetchAllPaddleSubscriptions = async () => {
  const subscriptions = new Map()

  for (const state of PADDLE_STATES) {
    let page = 1

    // the default listing omits deleted subscriptions, so every state has to be
    // paged through separately
    for (;;) {
      const results = await fetchPaddleSubscriptionsPage(state, page)

      results.forEach((subscription) => {
        subscriptions.set(String(subscription.subscription_id), {
          subID: String(subscription.subscription_id),
          email: String(subscription.user_email || '').trim(),
          paddleUserId: subscription.user_id
            ? String(subscription.user_id)
            : null,
          planId: subscription.plan_id ? String(subscription.plan_id) : null,
          startedAt: subscription.signup_date || null,
          endedAt: subscription.cancellation_effective_date || null,
          state,
        })
      })

      console.log(
        `  paddle: state=${state} page=${page} -> ${results.length} subscription(s)`,
      )

      if (results.length < RESULTS_PER_PAGE) {
        break
      }

      page += 1
    }
  }

  return Array.from(subscriptions.values())
}

const main = async () => {
  if (!PADDLE_VENDOR_ID || !PADDLE_API_KEY) {
    throw new Error('PADDLE_VENDOR_ID / PADDLE_API_KEY are not set')
  }

  console.log(
    APPLY
      ? 'Running in APPLY mode - rows will be inserted'
      : 'Running in DRY RUN mode - nothing will be written (pass --apply to insert)',
  )

  console.log('\nFetching subscriptions from Paddle...')
  const subscriptions = await fetchAllPaddleSubscriptions()
  console.log(`Fetched ${subscriptions.length} distinct subscription(s)`)

  const connection = await mysql.createConnection({
    host: MYSQL_HOST,
    port: 3306,
    user: MYSQL_USER,
    password: MYSQL_ROOT_PASSWORD,
    database: MYSQL_DATABASE,
    timezone: 'Z',
  })

  try {
    // Paddle reports every date in UTC and they are inserted as literal
    // strings, so the session has to interpret them as UTC too
    await connection.query("SET time_zone = '+00:00'")

    const [users] = await connection.query('SELECT `id`, `email` FROM `user`')
    const usersByEmail = new Map(
      users
        .filter(({ email }) => !!email)
        .map(({ id, email }) => [email.trim().toLowerCase(), id]),
    )

    let existingRows = []

    try {
      ;[existingRows] = await connection.query(
        'SELECT `subID` FROM `user_subscription`',
      )
    } catch (reason) {
      // a dry run is useful before the migration has been applied; an apply is not
      if (reason.code !== 'ER_NO_SUCH_TABLE' || APPLY) {
        throw reason
      }

      console.log(
        '\nNote: `user_subscription` does not exist yet - run migrations/mysql/2026_07_25_user_subscriptions.sql before --apply.',
      )
    }

    const existing = new Set(existingRows.map(({ subID }) => subID))

    const toInsert = []
    const unmatched = []

    subscriptions.forEach((subscription) => {
      const userId = usersByEmail.get(subscription.email.toLowerCase())

      if (!userId) {
        unmatched.push(subscription)
        return
      }

      toInsert.push([
        crypto.randomUUID(),
        userId,
        subscription.subID,
        subscription.paddleUserId,
        subscription.planId,
        subscription.startedAt,
        subscription.endedAt,
        subscription.email,
      ])
    })

    const fresh = toInsert.filter(([, , subID]) => !existing.has(subID))

    console.log(`\nMatched to a user: ${toInsert.length}`)
    console.log(`  already recorded: ${toInsert.length - fresh.length}`)
    console.log(`  to insert:        ${fresh.length}`)

    fresh.forEach(([, userId, subID, , , startedAt, endedAt, email]) => {
      console.log(
        `    + ${subID}  ${email}  user=${userId}  started=${startedAt || '?'}${
          endedAt ? `  ended=${endedAt}` : ''
        }`,
      )
    })

    if (unmatched.length > 0) {
      console.log(
        `\nNo Swetrix user matches these ${unmatched.length} subscription(s) - most are deleted accounts, the rest are users who changed their email after subscribing:`,
      )
      unmatched.forEach(({ subID, email, state }) => {
        console.log(`    ? ${subID}  ${email || '<no email>'}  state=${state}`)
      })
    }

    if (!APPLY) {
      console.log('\nDry run complete, nothing was written.')
      return
    }

    if (toInsert.length === 0) {
      console.log('\nNothing to insert.')
      return
    }

    // every matched subscription goes through the same statement: rows that
    // already exist only get their empty columns filled in, so this is
    // rerunnable and never overwrites richer data written by the webhooks
    await connection.query(
      'INSERT INTO `user_subscription` (`id`, `userId`, `subID`, `paddleUserId`, `planId`, `startedAt`, `endedAt`) VALUES ? ' +
        'ON DUPLICATE KEY UPDATE `paddleUserId` = COALESCE(`paddleUserId`, VALUES(`paddleUserId`)), ' +
        '`planId` = COALESCE(`planId`, VALUES(`planId`)), ' +
        '`startedAt` = COALESCE(`startedAt`, VALUES(`startedAt`)), ' +
        '`endedAt` = COALESCE(`endedAt`, VALUES(`endedAt`))',
      [toInsert.map((row) => row.slice(0, 7))],
    )

    const [[{ total }]] = await connection.query(
      'SELECT COUNT(*) AS total FROM `user_subscription`',
    )

    console.log(
      `\nInserted ${fresh.length} new row(s); ${total} subscription(s) recorded in total.`,
    )
  } finally {
    await connection.end()
  }
}

main().catch((reason) => {
  console.error(reason)
  process.exit(1)
})