import {
  CamelCasePlugin,
  type Compilable,
  CompiledQuery,
  type Dialect,
  Kysely,
  PostgresDialect,
  type RawBuilder,
  sql,
  WithSchemaPlugin,
} from 'kysely'
import * as pg from 'pg'

import type * as AdminAuditLogs from './tables/adminAuditLogs.js'
import type * as ApiKeyAdmissionBootstraps from './tables/apiKeyAdmissionBootstraps.js'
import type * as ApiKeyAdmissions from './tables/apiKeyAdmissions.js'
import type * as ApiKeyOwnerAdmissions from './tables/apiKeyOwnerAdmissions.js'
import type * as ApiKeyOwnerTombstones from './tables/apiKeyOwnerTombstones.js'
import type * as ApiKeyRevocations from './tables/apiKeyRevocations.js'
import type * as AuthAccounts from './tables/authAccounts.js'
import type * as AuthSessions from './tables/authSessions.js'
import type * as AuthVerifications from './tables/authVerifications.js'
import type * as BillingSettings from './tables/billingSettings.js'
import type * as EarlyAccess from './tables/earlyAccess.js'
import type * as EarnVaults from './tables/earnVaults.js'
import type * as EnabledBillingSources from './tables/enabledBillingSources.js'
import type * as Invitations from './tables/invitations.js'
import type * as InviteLinks from './tables/inviteLinks.js'
import type * as Memberships from './tables/memberships.js'
import type * as Organizations from './tables/organizations.js'
import type * as Projects from './tables/projects.js'
import type * as RequestUsage from './tables/requestUsage.js'
import type * as RewardAccounts from './tables/rewardAccounts.js'
import type * as RewardCampaignDistributors from './tables/rewardCampaignDistributors.js'
import type * as RewardCampaigns from './tables/rewardCampaigns.js'
import type * as RewardCredits from './tables/rewardCredits.js'
import type * as RewardEligibilityAssociations from './tables/rewardEligibilityAssociations.js'
import type * as RewardRuns from './tables/rewardRuns.js'
import type * as RewardTransactionAttempts from './tables/rewardTransactionAttempts.js'
import type * as RoutesCatalog from './tables/routesCatalog.js'
import type * as RoutesDepositAddresses from './tables/routesDepositAddresses.js'
import type * as RoutesDepositRequestObservations from './tables/routesDepositRequestObservations.js'
import type * as RoutesDeposits from './tables/routesDeposits.js'
import type * as RoutesIdempotency from './tables/routesIdempotency.js'
import type * as RoutesSubsidies from './tables/routesSubsidies.js'
import type * as RoutesTransferEvents from './tables/routesTransferEvents.js'
import type * as RoutesTransfers from './tables/routesTransfers.js'
import type * as RoutesTransferSubsidies from './tables/routesTransferSubsidies.js'
import type * as RoutesTransferSubsidyNonces from './tables/routesTransferSubsidyNonces.js'
import type * as RoutesTransferTransactions from './tables/routesTransferTransactions.js'
import type * as SponsorshipReconciliationCursors from './tables/sponsorshipReconciliationCursors.js'
import type * as SponsorshipAttributions from './tables/sponsorshipAttributions.js'
import type * as SponsoredTransactions from './tables/sponsoredTransactions.js'
import type * as StripeCustomers from './tables/stripeCustomers.js'
import type * as Users from './tables/users.js'
import type * as VerifiedTokenRequests from './tables/verifiedTokenRequests.js'
import type * as VerifiedTokens from './tables/verifiedTokens.js'
import type * as WebhookDeliveries from './tables/webhookDeliveries.js'
import type * as WebhookQueueCompletions from './tables/webhookQueueCompletions.js'
import type * as WebhookQueueEvents from './tables/webhookQueueEvents.js'
import type * as WebhookSubscriptions from './tables/webhookSubscriptions.js'

/**
 * Kysely schema with camelCase columns. {@link CamelCasePlugin} maps table
 * records, queries, and {@link migrations} DDL to snake_case storage columns.
 */
export type Database = {
  admin_audit_logs: AdminAuditLogs.Table
  api_key_admission_bootstraps: ApiKeyAdmissionBootstraps.Table
  api_key_admissions: ApiKeyAdmissions.Table
  api_key_owner_admissions: ApiKeyOwnerAdmissions.Table
  api_key_owner_tombstones: ApiKeyOwnerTombstones.Table
  api_key_revocations: ApiKeyRevocations.Table
  auth_accounts: AuthAccounts.Table
  auth_sessions: AuthSessions.Table
  auth_verifications: AuthVerifications.Table
  billing_settings: BillingSettings.Table
  early_access: EarlyAccess.Table
  earn_vaults: EarnVaults.Table
  enabled_billing_sources: EnabledBillingSources.Table
  invitations: Invitations.Table
  invite_link_redemptions: InviteLinks.RedemptionTable
  invite_links: InviteLinks.Table
  memberships: Memberships.Table
  migrations: {
    appliedAt: string
    checksum: string
    name: string
    /** SQL as applied; null on rows written before the column existed. */
    sql: string | null
  }
  organizations: Organizations.Table
  projects: Projects.Table
  request_usage_buckets: RequestUsage.BucketTable
  request_usage_meter_events: RequestUsage.MeterEventTable
  reward_accounts: RewardAccounts.Table
  reward_campaign_distributors: RewardCampaignDistributors.Table
  reward_campaigns: RewardCampaigns.Table
  reward_credits: RewardCredits.Table
  reward_eligibility_associations: RewardEligibilityAssociations.Table
  reward_runs: RewardRuns.Table
  reward_transaction_attempts: RewardTransactionAttempts.Table
  routes_catalogs: RoutesCatalog.CatalogTable
  routes_chain_tokens: RoutesCatalog.ChainTokenTable
  routes_chains: RoutesCatalog.ChainTable
  routes_deposit_addresses: RoutesDepositAddresses.Table
  routes_deposit_request_observations: RoutesDepositRequestObservations.Table
  routes_deposits: RoutesDeposits.Table
  routes_idempotency_requests: RoutesIdempotency.Table
  routes_routes: RoutesCatalog.RouteTable
  routes_subsidies: RoutesSubsidies.Table
  routes_tokens: RoutesCatalog.TokenTable
  routes_transfer_events: RoutesTransferEvents.Table
  routes_transfer_subsidies: RoutesTransferSubsidies.Table
  routes_transfer_subsidy_nonces: RoutesTransferSubsidyNonces.Table
  routes_transfer_transactions: RoutesTransferTransactions.Table
  routes_transfers: RoutesTransfers.Table
  sponsorship_reconciliation_cursors: SponsorshipReconciliationCursors.Table
  sponsorship_attributions: SponsorshipAttributions.Table
  sponsored_transactions: SponsoredTransactions.Table
  stripe_customers: StripeCustomers.Table
  users: Users.Table
  verified_token_lists: VerifiedTokens.ListTable
  verified_token_requests: VerifiedTokenRequests.Table
  verified_tokens: VerifiedTokens.Table
  webhook_deliveries: WebhookDeliveries.Table
  webhook_queue_completions: WebhookQueueCompletions.Table
  webhook_queue_events: WebhookQueueEvents.Table
  webhook_subscriptions: WebhookSubscriptions.Table
}

/**
 * A SQL-backed authoritative store: the underlying Kysely handle (table
 * repository modules query through it), schema migrations, and transactions.
 */
export type Db = {
  /** Closes the underlying connection/pool. */
  close(): Promise<void>
  /**
   * Underlying Kysely handle. Transaction-scoped {@link Db} values carry the
   * transaction handle, so repositories called with `tx` run inside it. */
  kysely: Kysely<Database>
  /** Applies any pending schema migrations; safe to call repeatedly and concurrently. */
  migrate(): Promise<void>
  /**
   * Runs `fn` in a transaction — commits on return, rolls back on throw. `fn`
   * receives a transaction-scoped {@link Db}; pass it (not the outer `Db`) to
   * repositories so every query inside runs in the transaction.
   */
  transaction<value>(fn: (tx: Db) => Promise<value>): Promise<value>
}

/**
 * A {@link Db}, or a factory for per-request Workers/Hyperdrive connections.
 * Node can pass a long-lived singleton. Resolve at the leaf with {@link get}.
 */
export type Source = Db | (() => Db)

/**
 * Resolves a {@link Source} to a {@link Db}. Call at the request leaf so
 * factories create per-request instances; singletons resolve to themselves.
 *
 * @param source - The database or factory.
 * @returns The resolved {@link Db}.
 */
export function get(source: Source): Db {
  return typeof source === 'function' ? source() : source
}

/**
 * One schema migration. `up` builds a single idempotent Kysely DDL statement;
 * {@link Db.migrate} compiles it for the checksum, then applies it.
 */
type Migration = {
  /** Unique, ordered migration name (e.g. `0001_organizations`). */
  name: string
  /**
   * Builds the migration's DDL against `db`. Use `.ifNotExists()` so the
   * statement is safe to re-run without a wrapping transaction. */
  up: (db: Kysely<Database>) => Compilable & { execute(): Promise<unknown> }
}

/**
 * Ordered schema migrations for {@link Db.migrate}. Each `up` is one
 * idempotent statement; compiled-SQL FNV-1a checksums reject edited applied
 * migrations.
 */
export const migrations: readonly Migration[] = [
  {
    name: '0001_organizations',
    up: (db) =>
      db.schema
        .createTable('organizations')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('name', 'text', (c) => c.notNull())
        .addColumn('createdBy', 'text')
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull()),
  },
  {
    name: '0002_webhook_subscriptions',
    up: (db) =>
      db.schema
        .createTable('webhookSubscriptions')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('ownerType', 'text', (c) => c.notNull())
        .addColumn('ownerId', 'text', (c) => c.notNull())
        .addColumn('chainId', 'integer', (c) => c.notNull())
        .addColumn('eventType', 'text', (c) => c.notNull())
        .addColumn('status', 'text', (c) => c.notNull().defaultTo('active'))
        .addColumn('destination', 'jsonb', (c) => c.notNull())
        .addColumn('filters', 'jsonb', (c) => c.notNull().defaultTo(sql`'{}'::jsonb`))
        .addColumn('context', 'jsonb')
        .addColumn('secret', 'text', (c) => c.notNull())
        .addColumn('failureCount', 'integer', (c) => c.notNull().defaultTo(0))
        .addColumn('lastDeliveryAt', 'text')
        .addColumn('pollerCursor', 'text')
        .addColumn('expiresAt', 'text')
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull()),
  },
  {
    name: '0003_webhook_deliveries',
    up: (db) =>
      db.schema
        .createTable('webhookDeliveries')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('subscriptionId', 'text', (c) =>
          c.notNull().references('webhookSubscriptions.id').onDelete('cascade'),
        )
        .addColumn('eventId', 'text', (c) => c.notNull())
        .addColumn('attempt', 'integer', (c) => c.notNull())
        .addColumn('status', 'text', (c) => c.notNull())
        .addColumn('requestUrl', 'text', (c) => c.notNull())
        .addColumn('responseStatus', 'integer')
        .addColumn('responseMs', 'integer')
        .addColumn('error', 'text')
        .addColumn('envelope', 'jsonb', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('expiresAt', 'text', (c) => c.notNull()),
  },
  {
    name: '0004_verified_token_lists',
    up: (db) =>
      db.schema
        .createTable('verifiedTokenLists')
        .ifNotExists()
        .addColumn('chainId', 'integer', (c) => c.primaryKey())
        .addColumn('version', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull()),
  },
  {
    name: '0005_verified_tokens',
    up: (db) =>
      db.schema
        .createTable('verifiedTokens')
        .ifNotExists()
        .addColumn('chainId', 'integer', (c) => c.notNull())
        .addColumn('address', 'text', (c) => c.notNull())
        .addColumn('symbol', 'text', (c) => c.notNull())
        .addColumn('name', 'text', (c) => c.notNull())
        .addColumn('currency', 'text', (c) => c.notNull())
        .addColumn('decimals', 'integer', (c) => c.notNull())
        .addColumn('logoUri', 'text')
        .addColumn('position', 'integer', (c) => c.notNull())
        .addPrimaryKeyConstraint('verified_tokens_pkey', ['chainId', 'address']),
  },
  {
    // Case-insensitive symbol uniqueness per chain (`GET /tokens/:symbol`
    // would otherwise be ambiguous).
    name: '0006_verified_tokens_symbol_index',
    up: (db) =>
      db.schema
        .createIndex('verified_tokens_symbol_idx')
        .ifNotExists()
        .unique()
        .on('verifiedTokens')
        .expression(sql`chain_id, lower(symbol)`),
  },
  {
    // Owner list/count/cap scans and `id <` keyset paging.
    name: '0007_webhook_subscriptions_owner_index',
    up: (db) =>
      db.schema
        .createIndex('webhook_subscriptions_owner_idx')
        .ifNotExists()
        .on('webhookSubscriptions')
        .columns(['ownerType', 'ownerId', 'id']),
  },
  {
    // Partial index over active subscriptions: the poller's per-stream lookup.
    // `sql.lit` keeps the predicate literal — DDL cannot carry parameters.
    name: '0008_webhook_subscriptions_active_index',
    up: (db) =>
      db.schema
        .createIndex('webhook_subscriptions_active_idx')
        .ifNotExists()
        .on('webhookSubscriptions')
        .columns(['chainId', 'eventType'])
        .where(sql.ref('status'), '=', sql.lit('active')),
  },
  {
    name: '0009_webhook_subscriptions_expires_index',
    up: (db) =>
      db.schema
        .createIndex('webhook_subscriptions_expires_idx')
        .ifNotExists()
        .on('webhookSubscriptions')
        .column('expiresAt')
        .where('expiresAt', 'is not', null),
  },
  {
    // Per-subscription delivery-log paging.
    name: '0010_webhook_deliveries_subscription_index',
    up: (db) =>
      db.schema
        .createIndex('webhook_deliveries_subscription_idx')
        .ifNotExists()
        .on('webhookDeliveries')
        .columns(['subscriptionId', 'id']),
  },
  {
    name: '0011_webhook_deliveries_expires_index',
    up: (db) =>
      db.schema
        .createIndex('webhook_deliveries_expires_idx')
        .ifNotExists()
        .on('webhookDeliveries')
        .column('expiresAt'),
  },
  {
    name: '0012_users',
    up: (db) =>
      db.schema
        .createTable('users')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('address', 'text', (c) => c.notNull().unique())
        .addColumn('email', 'text')
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull()),
  },
  {
    name: '0013_organizations_user_id',
    // The schema builder cannot express `ADD COLUMN IF NOT EXISTS`; raw SQL
    // (snake_case: the CamelCase plugin does not map raw statements).
    up: (db) => raw(db, sql`ALTER TABLE organizations ADD COLUMN IF NOT EXISTS user_id text`),
  },
  {
    name: '0014_projects',
    up: (db) =>
      db.schema
        .createTable('projects')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('orgId', 'text', (c) => c.notNull())
        .addColumn('name', 'text', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull()),
  },
  {
    name: '0015_projects_org_index',
    up: (db) =>
      db.schema.createIndex('projects_org_idx').ifNotExists().on('projects').column('orgId'),
  },
  {
    name: '0016_memberships',
    up: (db) =>
      db.schema
        .createTable('memberships')
        .ifNotExists()
        .addColumn('orgId', 'text', (c) => c.notNull())
        .addColumn('userId', 'text', (c) => c.notNull())
        .addColumn('role', 'text', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('memberships_pk', ['orgId', 'userId']),
  },
  {
    name: '0017_memberships_user_index',
    up: (db) =>
      db.schema.createIndex('memberships_user_idx').ifNotExists().on('memberships').column('userId'), // prettier-ignore
  },
  {
    name: '0018_memberships_owner_backfill',
    // Raw SQL (snake_case: the CamelCase plugin does not map raw statements).
    up: (db) =>
      raw(
        db,
        sql`INSERT INTO memberships (org_id, user_id, role, created_at, updated_at) SELECT id, user_id, 'owner', created_at, updated_at FROM organizations WHERE user_id IS NOT NULL ON CONFLICT DO NOTHING`,
      ),
  },
  {
    name: '0019_invitations',
    up: (db) =>
      db.schema
        .createTable('invitations')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('orgId', 'text', (c) => c.notNull())
        .addColumn('email', 'text', (c) => c.notNull())
        .addColumn('role', 'text', (c) => c.notNull())
        .addColumn('invitedBy', 'text', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('expiresAt', 'text', (c) => c.notNull())
        .addColumn('acceptedAt', 'text')
        .addColumn('revokedAt', 'text'),
  },
  {
    name: '0020_invitations_org_index',
    up: (db) =>
      db.schema.createIndex('invitations_org_idx').ifNotExists().on('invitations').column('orgId'),
  },
  {
    name: '0021_invitations_email_index',
    up: (db) =>
      db.schema.createIndex('invitations_email_idx').ifNotExists().on('invitations').column('email'), // prettier-ignore
  },
  {
    name: '0022_users_address_nullable',
    // Raw SQL; a no-op when the column is already nullable.
    up: (db) => raw(db, sql`ALTER TABLE users ALTER COLUMN address DROP NOT NULL`),
  },
  {
    name: '0023_invitations_pending_unique',
    // Raw SQL: a partial unique index (snake_case; the CamelCase plugin does
    // not map raw statements). At most one live (unaccepted, unrevoked)
    // invitation per (org, email); a re-invite upserts the existing row.
    up: (db) =>
      raw(
        db,
        sql`CREATE UNIQUE INDEX IF NOT EXISTS invitations_pending_unique ON invitations (org_id, email) WHERE accepted_at IS NULL AND revoked_at IS NULL`,
      ),
  },
  {
    name: '0024_sponsored_transactions',
    up: (db) =>
      db.schema
        .createTable('sponsoredTransactions')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('orgId', 'text', (c) => c.notNull())
        .addColumn('projectId', 'text', (c) => c.notNull())
        .addColumn('apiKeyId', 'text', (c) => c.notNull())
        .addColumn('environment', 'text', (c) => c.notNull())
        .addColumn('chainId', 'integer', (c) => c.notNull())
        .addColumn('transactionHash', 'text')
        .addColumn('signPayload', 'text', (c) => c.notNull())
        .addColumn('transaction', 'text', (c) => c.notNull())
        .addColumn('status', 'text', (c) => c.notNull())
        .addColumn('feeAmount', 'text')
        .addColumn('feeToken', 'text')
        .addColumn('billable', 'boolean', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('finalizedAt', 'text')
        .addUniqueConstraint('sponsored_transactions_sign_payload_key', ['signPayload']),
  },
  {
    name: '0025_sponsored_transactions_status_index',
    up: (db) =>
      db.schema.createIndex('sponsored_transactions_status_idx').ifNotExists().on('sponsoredTransactions').columns(['status', 'createdAt']), // prettier-ignore
  },
  {
    name: '0026_sponsored_transactions_org_index',
    up: (db) =>
      db.schema.createIndex('sponsored_transactions_org_idx').ifNotExists().on('sponsoredTransactions').columns(['orgId', 'createdAt']), // prettier-ignore
  },
  {
    name: '0027_stripe_customers',
    up: (db) =>
      db.schema
        .createTable('stripeCustomers')
        .ifNotExists()
        .addColumn('orgId', 'text', (c) => c.primaryKey())
        .addColumn('stripeCustomerId', 'text', (c) => c.notNull())
        .addColumn('status', 'text', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addUniqueConstraint('stripe_customers_stripe_customer_id_key', ['stripeCustomerId']),
  },
  {
    name: '0028_billing_settings',
    up: (db) =>
      db.schema
        .createTable('billingSettings')
        .ifNotExists()
        .addColumn('orgId', 'text', (c) => c.primaryKey())
        .addColumn('spendLimit', 'text')
        .addColumn('txFeeLimit', 'text')
        .addColumn('currency', 'text', (c) => c.notNull().defaultTo('usd'))
        .addColumn('period', 'text', (c) => c.notNull().defaultTo('month'))
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull()),
  },
  {
    name: '0029_sponsored_transactions_fee_max',
    up: (db) => raw(db, sql`ALTER TABLE sponsored_transactions ADD COLUMN IF NOT EXISTS fee_max text`), // prettier-ignore
  },
  {
    name: '0030_sponsored_transactions_meter_reported_at',
    up: (db) => raw(db, sql`ALTER TABLE sponsored_transactions ADD COLUMN IF NOT EXISTS meter_reported_at text`), // prettier-ignore
  },
  {
    name: '0031_sponsored_transactions_billing_index',
    // Partial: the spend-limit aggregate only ever scans billable rows.
    up: (db) => raw(db, sql`CREATE INDEX IF NOT EXISTS sponsored_transactions_billing_idx ON sponsored_transactions (org_id, status, created_at) WHERE billable`), // prettier-ignore
  },
  {
    name: '0032_sponsored_transactions_fee_amount_rescale',
    // Rows finalized before fee scaling shipped stored attodollars (1e12× the
    // token unit); `div` is exact ceiling division (`Fees.fromGas`). The
    // threshold keeps re-runs idempotent: real scaled fees sit far below
    // 1e9 ($1,000); attodollar values far above.
    up: (db) => raw(db, sql`UPDATE sponsored_transactions SET fee_amount = div(fee_amount::numeric + 999999999999, 1000000000000)::text WHERE status = 'finalized' AND fee_amount IS NOT NULL AND fee_amount::numeric >= 1000000000`), // prettier-ignore
  },
  {
    name: '0033_sponsored_transactions_unreported_index',
    // Partial queue index: the per-minute reporter scans only unreported
    // finalized billable rows, which marking immediately evicts.
    up: (db) => raw(db, sql`CREATE INDEX IF NOT EXISTS sponsored_transactions_unreported_idx ON sponsored_transactions (finalized_at, id) WHERE billable AND status = 'finalized' AND meter_reported_at IS NULL`), // prettier-ignore
  },
  {
    name: '0034_sponsored_transactions_currency',
    up: (db) => raw(db, sql`ALTER TABLE sponsored_transactions ADD COLUMN IF NOT EXISTS currency text`), // prettier-ignore
  },
  {
    name: '0035_sponsored_transactions_currency_backfill',
    // Every fee payer to date sponsors in pathUSD, so rows predating the
    // currency snapshot are USD. The predicate keeps re-runs idempotent.
    up: (db) => raw(db, sql`UPDATE sponsored_transactions SET currency = 'usd' WHERE currency IS NULL`), // prettier-ignore
  },
  {
    name: '0036_stripe_customers_environment',
    up: (db) => raw(db, sql`ALTER TABLE stripe_customers ADD COLUMN IF NOT EXISTS environment text NOT NULL DEFAULT 'production'`), // prettier-ignore
  },
  {
    name: '0037_stripe_customers_org_environment_key',
    // Enables a second (sandbox) row per org; targeted by the upsert conflict
    // arbiter so it survives the org pkey drop in the follow-up release.
    up: (db) => raw(db, sql`CREATE UNIQUE INDEX IF NOT EXISTS stripe_customers_org_environment_key ON stripe_customers (org_id, environment)`), // prettier-ignore
  },
  {
    name: '0038_billing_settings_environment',
    up: (db) => raw(db, sql`ALTER TABLE billing_settings ADD COLUMN IF NOT EXISTS environment text NOT NULL DEFAULT 'production'`), // prettier-ignore
  },
  {
    name: '0039_billing_settings_org_environment_key',
    up: (db) => raw(db, sql`CREATE UNIQUE INDEX IF NOT EXISTS billing_settings_org_environment_key ON billing_settings (org_id, environment)`), // prettier-ignore
  },
  {
    name: '0040_stripe_customers_drop_org_pkey',
    // Release B: the `(org_id, environment)` unique index now guards uniqueness,
    // so dropping the org pkey unblocks a second (sandbox) row per org.
    up: (db) => raw(db, sql`ALTER TABLE stripe_customers DROP CONSTRAINT IF EXISTS stripe_customers_pkey`), // prettier-ignore
  },
  {
    name: '0041_billing_settings_drop_org_pkey',
    up: (db) => raw(db, sql`ALTER TABLE billing_settings DROP CONSTRAINT IF EXISTS billing_settings_pkey`), // prettier-ignore
  },
  {
    name: '0042_request_usage_buckets',
    up: (db) =>
      db.schema
        .createTable('requestUsageBuckets')
        .ifNotExists()
        .addColumn('orgId', 'text', (c) => c.notNull())
        .addColumn('environment', 'text', (c) => c.notNull())
        .addColumn('bucketStart', 'text', (c) => c.notNull())
        .addColumn('reportedCount', 'bigint', (c) => c.notNull().defaultTo(0))
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('request_usage_buckets_pkey', ['orgId', 'environment', 'bucketStart']), // prettier-ignore
  },
  {
    name: '0043_request_usage_meter_events',
    up: (db) =>
      db.schema
        .createTable('requestUsageMeterEvents')
        .ifNotExists()
        .addColumn('identifier', 'text', (c) => c.primaryKey())
        .addColumn('orgId', 'text', (c) => c.notNull())
        .addColumn('environment', 'text', (c) => c.notNull())
        .addColumn('bucketStart', 'text', (c) => c.notNull())
        .addColumn('sequence', 'integer', (c) => c.notNull())
        .addColumn('deltaCount', 'bigint', (c) => c.notNull())
        .addColumn('stripeCustomerId', 'text', (c) => c.notNull())
        .addColumn('state', 'text', (c) => c.notNull())
        .addColumn('reason', 'text')
        .addColumn('error', 'text')
        .addColumn('firstAttemptedAt', 'text', (c) => c.notNull())
        .addColumn('reportedAt', 'text')
        .addUniqueConstraint('request_usage_meter_events_bucket_sequence_key', ['orgId', 'environment', 'bucketStart', 'sequence']), // prettier-ignore
  },
  {
    name: '0044_request_usage_meter_events_pending_index',
    // Partial: the reporter scans only unreported (pending) rows.
    up: (db) => raw(db, sql`CREATE INDEX IF NOT EXISTS request_usage_meter_events_pending_idx ON request_usage_meter_events (first_attempted_at, identifier) WHERE state = 'pending'`), // prettier-ignore
  },
  {
    // Supports the admin organization's newest-first keyset pagination.
    name: '0045_organizations_admin_list_index',
    up: (db) => raw(db, sql`CREATE INDEX IF NOT EXISTS organizations_admin_list_idx ON organizations (created_at DESC, id DESC)`), // prettier-ignore
  },
  {
    // Prefix search keeps organization-name lookup index-backed.
    name: '0046_organizations_name_prefix_index',
    up: (db) => raw(db, sql`CREATE INDEX IF NOT EXISTS organizations_name_prefix_idx ON organizations (lower(name) text_pattern_ops)`), // prettier-ignore
  },
  {
    // Prefix search keeps member-email lookup index-backed.
    name: '0047_users_email_prefix_index',
    up: (db) => raw(db, sql`CREATE INDEX IF NOT EXISTS users_email_prefix_idx ON users (lower(email) text_pattern_ops) WHERE email IS NOT NULL`), // prettier-ignore
  },
  {
    // Exact owner lookup for organizations created by a user.
    name: '0048_organizations_user_index',
    up: (db) => raw(db, sql`CREATE INDEX IF NOT EXISTS organizations_user_idx ON organizations (user_id) WHERE user_id IS NOT NULL`), // prettier-ignore
  },
  {
    // Supports oldest-first admin membership keyset pagination.
    name: '0049_memberships_admin_list_index',
    up: (db) => raw(db, sql`CREATE INDEX IF NOT EXISTS memberships_admin_list_idx ON memberships (org_id, created_at, user_id)`), // prettier-ignore
  },
  {
    // Supports newest-first admin project keyset pagination.
    name: '0050_projects_admin_list_index',
    up: (db) => raw(db, sql`CREATE INDEX IF NOT EXISTS projects_admin_list_idx ON projects (org_id, created_at DESC, id DESC)`), // prettier-ignore
  },
  {
    name: '0051_admin_audit_logs',
    up: (db) =>
      db.schema
        .createTable('adminAuditLogs')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('actor', 'text', (c) => c.notNull())
        .addColumn('method', 'text', (c) => c.notNull())
        .addColumn('path', 'text', (c) => c.notNull())
        .addColumn('query', 'text')
        .addColumn('requestId', 'text', (c) => c.notNull())
        .addColumn('status', 'integer', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull()),
  },
  {
    name: '0052_admin_audit_logs_created_index',
    up: (db) =>
      db.schema
        .createIndex('admin_audit_logs_created_idx')
        .ifNotExists()
        .on('adminAuditLogs')
        .columns(['createdAt', 'id']),
  },
  {
    name: '0053_invite_links',
    up: (db) =>
      db.schema
        .createTable('inviteLinks')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('token', 'text', (c) => c.notNull().unique())
        .addColumn('orgId', 'text', (c) => c.notNull())
        .addColumn('name', 'text', (c) => c.notNull())
        .addColumn('role', 'text', (c) => c.notNull())
        .addColumn('maxUses', 'integer')
        .addColumn('useCount', 'integer', (c) => c.notNull().defaultTo(0))
        .addColumn('expiresAt', 'text')
        .addColumn('enabled', 'boolean', (c) => c.notNull().defaultTo(true))
        .addColumn('createdBy', 'text', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('lastUsedAt', 'text')
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addColumn('deletedAt', 'text'),
  },
  {
    name: '0054_invite_links_org_index',
    up: (db) =>
      db.schema
        .createIndex('invite_links_org_idx')
        .ifNotExists()
        .on('inviteLinks')
        .columns(['orgId', 'createdAt']),
  },
  {
    name: '0055_invite_link_redemptions',
    up: (db) =>
      db.schema
        .createTable('inviteLinkRedemptions')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('inviteLinkId', 'text', (c) => c.notNull())
        .addColumn('inviteLinkName', 'text', (c) => c.notNull())
        .addColumn('orgId', 'text', (c) => c.notNull())
        .addColumn('userId', 'text', (c) => c.notNull())
        .addColumn('email', 'text', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull()),
  },
  {
    name: '0056_invite_link_redemptions_org_index',
    up: (db) =>
      db.schema
        .createIndex('invite_link_redemptions_org_idx')
        .ifNotExists()
        .on('inviteLinkRedemptions')
        .columns(['orgId', 'createdAt']),
  },
  {
    name: '0057_invite_link_redemptions_link_index',
    up: (db) =>
      db.schema
        .createIndex('invite_link_redemptions_link_idx')
        .ifNotExists()
        .on('inviteLinkRedemptions')
        .columns(['inviteLinkId', 'createdAt']),
  },
  {
    name: '0058_request_usage_sequence_bigint',
    up: (db) => raw(db, sql`ALTER TABLE request_usage_meter_events ALTER COLUMN sequence TYPE bigint`), // prettier-ignore
  },
  {
    name: '0059_verified_token_requests',
    up: (db) =>
      db.schema
        .createTable('verifiedTokenRequests')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('orgId', 'text', (c) => c.notNull())
        .addColumn('requestedBy', 'text', (c) => c.notNull())
        .addColumn('chainId', 'integer', (c) => c.notNull())
        .addColumn('address', 'text', (c) => c.notNull())
        .addColumn('symbol', 'text', (c) => c.notNull())
        .addColumn('name', 'text', (c) => c.notNull())
        .addColumn('currency', 'text', (c) => c.notNull())
        .addColumn('decimals', 'integer', (c) => c.notNull())
        .addColumn('logoUri', 'text')
        .addColumn('note', 'text')
        .addColumn('status', 'text', (c) => c.notNull().defaultTo('pending'))
        .addColumn('reviewNote', 'text')
        .addColumn('reviewedBy', 'text')
        .addColumn('reviewedAt', 'text')
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull()),
  },
  {
    name: '0060_verified_token_requests_org_index',
    up: (db) =>
      db.schema
        .createIndex('verified_token_requests_org_idx')
        .ifNotExists()
        .on('verifiedTokenRequests')
        .columns(['orgId', 'createdAt']),
  },
  {
    name: '0061_verified_token_requests_pending_unique',
    // Raw SQL: a partial unique index (snake_case; the CamelCase plugin does
    // not map raw statements). At most one live pending request per
    // (chain, address) globally; addresses are stored lowercase.
    up: (db) =>
      raw(
        db,
        sql`CREATE UNIQUE INDEX IF NOT EXISTS verified_token_requests_pending_unique ON verified_token_requests (chain_id, address) WHERE status = 'pending'`,
      ),
  },
  {
    name: '0062_verified_token_requests_status_index',
    up: (db) =>
      db.schema
        .createIndex('verified_token_requests_status_idx')
        .ifNotExists()
        .on('verifiedTokenRequests')
        .columns(['status', 'createdAt']),
  },
  {
    name: '0063_verified_token_requests_logo',
    up: (db) => raw(db, sql`ALTER TABLE verified_token_requests ADD COLUMN IF NOT EXISTS logo text`), // prettier-ignore
  },
  {
    name: '0064_invitations_grants_early_access',
    up: (db) => raw(db, sql`ALTER TABLE invitations ADD COLUMN IF NOT EXISTS grants_early_access boolean NOT NULL DEFAULT false`), // prettier-ignore
  },
  {
    name: '0065_invitations_grants_early_access_backfill',
    // Pre-gate invitations came from trusted-era users; the fixed cutoff keeps
    // re-runs (test DBs replay every migration) from flipping newer rows.
    up: (db) => raw(db, sql`UPDATE invitations SET grants_early_access = true WHERE created_at < '2026-07-20T00:00:00.000Z'`), // prettier-ignore
  },
  {
    name: '0066_early_access',
    up: (db) =>
      db.schema
        .createTable('earlyAccess')
        .ifNotExists()
        .addColumn('entry', 'text', (c) => c.primaryKey())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('createdBy', 'text', (c) => c.notNull()),
  },
  {
    name: '0067_invitations_grants_early_access_cutover',
    // Every invitation present at deployment came from a trusted-era user.
    up: (db) => raw(db, sql`UPDATE invitations SET grants_early_access = true`),
  },
  {
    name: '0068_invitations_grants_early_access_legacy_default',
    // Old workers omit this column during deploy; new writes set it explicitly.
    up: (db) => raw(db, sql`ALTER TABLE invitations ALTER COLUMN grants_early_access SET DEFAULT true`), // prettier-ignore
  },
  {
    name: '0069_invite_links_allowed_email_domains',
    up: (db) => raw(db, sql`ALTER TABLE invite_links ADD COLUMN IF NOT EXISTS allowed_email_domains jsonb`), // prettier-ignore
  },
  {
    name: '0070_enabled_billing_sources',
    up: (db) =>
      db.schema
        .createTable('enabledBillingSources')
        .ifNotExists()
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('createdBy', 'text', (c) => c.notNull())
        .addColumn('orgId', 'text', (c) => c.notNull())
        .addColumn('source', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('enabled_billing_sources_pkey', ['orgId', 'source']),
  },
  {
    name: '0071_enabled_billing_sources_stripe_backfill',
    up: (db) => raw(db, sql`INSERT INTO enabled_billing_sources (created_at, created_by, org_id, source) SELECT min(created_at), 'migration', org_id, 'stripe' FROM stripe_customers GROUP BY org_id ON CONFLICT DO NOTHING`), // prettier-ignore
  },
  {
    name: '0072_enabled_billing_sources_stripe_catch_up',
    up: (db) => raw(db, sql`INSERT INTO enabled_billing_sources (created_at, created_by, org_id, source) SELECT min(created_at), 'migration', org_id, 'stripe' FROM stripe_customers GROUP BY org_id ON CONFLICT DO NOTHING`), // prettier-ignore
  },
  {
    name: '0073_enabled_billing_sources_early_access_backfill',
    up: (db) => raw(db, sql`INSERT INTO enabled_billing_sources (created_at, created_by, org_id, source) SELECT organizations.created_at, 'migration', organizations.id, 'stripe' FROM organizations INNER JOIN users ON users.id = organizations.user_id WHERE users.email IS NOT NULL AND EXISTS (SELECT 1 FROM early_access WHERE early_access.entry IN (lower(btrim(users.email)), regexp_replace(lower(btrim(users.email)), '^.*@', ''))) ON CONFLICT DO NOTHING`), // prettier-ignore
  },
  {
    name: '0074_invitations_early_access_default',
    up: (db) => raw(db, sql`ALTER TABLE invitations ALTER COLUMN grants_early_access SET DEFAULT false`), // prettier-ignore
  },
  {
    name: '0075_verified_token_lists_chain_id_bigint',
    up: (db) => raw(db, sql`ALTER TABLE verified_token_lists ALTER COLUMN chain_id TYPE bigint`), // prettier-ignore
  },
  {
    name: '0076_verified_tokens_chain_id_bigint',
    up: (db) => raw(db, sql`ALTER TABLE verified_tokens ALTER COLUMN chain_id TYPE bigint`), // prettier-ignore
  },
  {
    name: '0077_earn_vaults',
    up: (db) =>
      db.schema
        .createTable('earnVaults')
        .ifNotExists()
        .addColumn('chainId', 'integer', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('description', 'text')
        .addColumn('label', 'text', (c) => c.notNull())
        .addColumn('privateInputTokens', 'jsonb', (c) => c.notNull().defaultTo(sql`'[]'::jsonb`))
        .addColumn('privateOutputTokens', 'jsonb', (c) => c.notNull().defaultTo(sql`'[]'::jsonb`))
        .addColumn('slug', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addColumn('vaultAddress', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('earn_vaults_pkey', ['chainId', 'vaultAddress']),
  },
  {
    name: '0078_earn_vaults_chain_id_bigint',
    up: (db) => raw(db, sql`ALTER TABLE earn_vaults ALTER COLUMN chain_id TYPE bigint`), // prettier-ignore
  },
  {
    name: '0079_sponsored_transactions_project_nullable',
    up: (db) => raw(db, sql`ALTER TABLE sponsored_transactions ALTER COLUMN project_id DROP NOT NULL`), // prettier-ignore
  },
  {
    name: '0080_funding_catalogs',
    up: (db) =>
      db.schema
        .createTable('fundingCatalogs')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addColumn('version', 'text', (c) => c.notNull())
        .addCheckConstraint('funding_catalogs_id_check', sql`id = 'default'`),
  },
  {
    name: '0081_funding_chains',
    up: (db) =>
      db.schema
        .createTable('fundingChains')
        .ifNotExists()
        .addColumn('aliases', 'jsonb', (c) => c.notNull())
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('name', 'text', (c) => c.notNull())
        .addColumn('parentChainId', 'text', (c) => c.references('fundingChains.id'))
        .addColumn('slug', 'text', (c) => c.notNull().unique()),
  },
  {
    name: '0082_funding_tokens',
    up: (db) =>
      db.schema
        .createTable('fundingTokens')
        .ifNotExists()
        .addColumn('currency', 'text', (c) => c.notNull())
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('name', 'text', (c) => c.notNull())
        .addColumn('symbol', 'text', (c) => c.notNull()),
  },
  {
    name: '0083_funding_chain_tokens',
    up: (db) =>
      db.schema
        .createTable('fundingChainTokens')
        .ifNotExists()
        .addColumn('address', 'text', (c) => c.notNull())
        .addColumn('chainId', 'text', (c) =>
          c.notNull().references('fundingChains.id').onDelete('cascade'),
        )
        .addColumn('decimals', 'smallint', (c) => c.notNull())
        .addColumn('name', 'text')
        .addColumn('standard', 'text', (c) => c.notNull())
        .addColumn('tokenId', 'text', (c) =>
          c.notNull().references('fundingTokens.id').onDelete('cascade'),
        )
        .addPrimaryKeyConstraint('funding_chain_tokens_pkey', ['chainId', 'tokenId'])
        .addUniqueConstraint('funding_chain_tokens_chain_address_key', ['chainId', 'address'])
        .addCheckConstraint('funding_chain_tokens_decimals_check', sql`decimals >= 0`),
  },
  {
    name: '0084_funding_routes',
    up: (db) =>
      db.schema
        .createTable('fundingRoutes')
        .ifNotExists()
        .addColumn('destinationChainId', 'text', (c) => c.notNull())
        .addColumn('destinationTokenId', 'text', (c) => c.notNull())
        .addColumn('providerId', 'text', (c) => c.notNull())
        .addColumn('sourceChainId', 'text', (c) => c.notNull())
        .addColumn('sourceTokenId', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('funding_routes_pkey', [
          'providerId',
          'sourceChainId',
          'sourceTokenId',
          'destinationChainId',
          'destinationTokenId',
        ])
        .addForeignKeyConstraint(
          'funding_routes_source_fkey',
          ['sourceChainId', 'sourceTokenId'],
          'fundingChainTokens',
          ['chainId', 'tokenId'],
        )
        .addForeignKeyConstraint(
          'funding_routes_destination_fkey',
          ['destinationChainId', 'destinationTokenId'],
          'fundingChainTokens',
          ['chainId', 'tokenId'],
        ),
  },
  {
    name: '0085_funding_transfers',
    up: (db) =>
      db.schema
        .createTable('fundingTransfers')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('orgId', 'text', (c) => c.notNull())
        .addColumn('projectId', 'text')
        .addColumn('environment', 'text', (c) => c.notNull())
        .addColumn('apiKeyId', 'text', (c) => c.notNull())
        .addColumn('providerId', 'text', (c) => c.notNull())
        .addColumn('method', 'text', (c) => c.notNull())
        .addColumn('mode', 'text', (c) => c.notNull())
        .addColumn('status', 'text', (c) => c.notNull())
        .addColumn('statusReason', 'jsonb')
        .addColumn('version', 'integer', (c) => c.notNull())
        .addColumn('snapshot', 'jsonb', (c) => c.notNull())
        .addColumn('quoteExpiresAt', 'text', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull()),
  },
  {
    name: '0086_funding_transfers_owner_index',
    // Ids embed a timestamp, so (owner, id) serves the newest-first keyset list.
    up: (db) =>
      db.schema
        .createIndex('funding_transfers_owner_idx')
        .ifNotExists()
        .on('fundingTransfers')
        .columns(['orgId', 'environment', 'id']),
  },
  {
    name: '0087_funding_transfer_events',
    up: (db) =>
      db.schema
        .createTable('fundingTransferEvents')
        .ifNotExists()
        .addColumn('transferId', 'text', (c) =>
          c.notNull().references('fundingTransfers.id').onDelete('cascade'),
        )
        .addColumn('version', 'integer', (c) => c.notNull())
        .addColumn('status', 'text', (c) => c.notNull())
        .addColumn('snapshot', 'jsonb', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('funding_transfer_events_pkey', ['transferId', 'version']),
  },
  {
    name: '0088_funding_transfer_transactions',
    up: (db) =>
      db.schema
        .createTable('fundingTransferTransactions')
        .ifNotExists()
        .addColumn('transferId', 'text', (c) =>
          c.notNull().references('fundingTransfers.id').onDelete('cascade'),
        )
        .addColumn('role', 'text', (c) => c.notNull())
        .addColumn('chainId', 'text', (c) => c.notNull())
        .addColumn('transactionRef', 'text', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('funding_transfer_transactions_pkey', [
          'transferId',
          'role',
          'transactionRef',
        ]),
  },
  {
    name: '0089_funding_transfer_transactions_source_unique',
    // Raw SQL: a partial unique index (snake_case; the CamelCase plugin does
    // not map raw statements). A source-chain transaction can fund at most one
    // transfer; destination and refund references may legitimately be shared.
    up: (db) =>
      raw(
        db,
        sql`CREATE UNIQUE INDEX IF NOT EXISTS funding_transfer_transactions_source_unique ON funding_transfer_transactions (chain_id, transaction_ref) WHERE role = 'source'`,
      ),
  },
  {
    name: '0090_funding_idempotency_requests',
    up: (db) =>
      db.schema
        .createTable('fundingIdempotencyRequests')
        .ifNotExists()
        .addColumn('apiKeyId', 'text', (c) => c.notNull())
        .addColumn('keyHash', 'text', (c) => c.notNull())
        .addColumn('requestHash', 'text', (c) => c.notNull())
        .addColumn('status', 'text', (c) => c.notNull())
        .addColumn('response', 'text')
        .addColumn('transferId', 'text')
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('expiresAt', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('funding_idempotency_requests_pkey', ['apiKeyId', 'keyHash']),
  },
  {
    name: '0091_funding_transfers_project_index',
    up: (db) =>
      db.schema
        .createIndex('funding_transfers_project_idx')
        .ifNotExists()
        .on('fundingTransfers')
        .columns(['orgId', 'environment', 'projectId', 'id']),
  },
  {
    name: '0092_funding_transfers_owner_status_index',
    up: (db) =>
      db.schema
        .createIndex('funding_transfers_owner_status_idx')
        .ifNotExists()
        .on('fundingTransfers')
        .columns(['orgId', 'environment', 'status', 'id']),
  },
  {
    name: '0093_funding_transfers_project_status_index',
    up: (db) =>
      db.schema
        .createIndex('funding_transfers_project_status_idx')
        .ifNotExists()
        .on('fundingTransfers')
        .columns(['orgId', 'environment', 'projectId', 'status', 'id']),
  },
  {
    name: '0094_funding_routes_capabilities',
    // Raw SQL: the alter builder has no IF NOT EXISTS. Null keeps a route
    // indicative-only; the delete-and-replace publish rewrites every row.
    up: (db) => raw(db, sql`ALTER TABLE funding_routes ADD COLUMN IF NOT EXISTS capabilities jsonb`), // prettier-ignore
  },
  {
    name: '0095_funding_transfers_provider_state',
    up: (db) => raw(db, sql`ALTER TABLE funding_transfers ADD COLUMN IF NOT EXISTS provider_state jsonb`), // prettier-ignore
  },
  {
    name: '0096_funding_deposit_addresses',
    up: (db) =>
      db.schema
        .createTable('fundingDepositAddresses')
        .ifNotExists()
        .addColumn('address', 'text', (c) => c.notNull())
        .addColumn('apiKeyId', 'text', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('deliveryStrategy', 'text', (c) => c.notNull())
        .addColumn('destinationTokenKey', 'text', (c) => c.notNull())
        .addColumn('environment', 'text', (c) => c.notNull())
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('orgId', 'text', (c) => c.notNull())
        .addColumn('projectId', 'text')
        .addColumn('providerOutputToken', 'jsonb', (c) => c.notNull())
        .addColumn('providerId', 'text', (c) => c.notNull())
        .addColumn('providerRequestIds', 'jsonb', (c) => c.notNull())
        .addColumn('providerState', 'jsonb')
        .addColumn('recipient', 'text', (c) => c.notNull())
        .addColumn('refundAddress', 'text', (c) => c.notNull())
        .addColumn('snapshot', 'jsonb', (c) => c.notNull())
        .addColumn('sourceChainId', 'text', (c) => c.notNull())
        .addColumn('sourceTokenKey', 'text', (c) => c.notNull())
        .addColumn('status', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addColumn('version', 'integer', (c) => c.notNull())
        .addUniqueConstraint('funding_deposit_addresses_provider_address_key', [
          'providerId',
          'address',
        ]),
  },
  {
    name: '0097_funding_deposits',
    up: (db) =>
      db.schema
        .createTable('fundingDeposits')
        .ifNotExists()
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('depositAddressId', 'text', (c) =>
          c.notNull().references('fundingDepositAddresses.id').onDelete('cascade'),
        )
        .addColumn('environment', 'text', (c) => c.notNull())
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('orgId', 'text', (c) => c.notNull())
        .addColumn('projectId', 'text')
        .addColumn('providerOutputAmount', 'jsonb')
        .addColumn('providerOutputToken', 'jsonb', (c) => c.notNull())
        .addColumn('providerRequestIds', 'jsonb', (c) => c.notNull())
        .addColumn('providerState', 'jsonb')
        .addColumn('providerTransactionHashes', 'jsonb', (c) => c.notNull())
        .addColumn('retryState', 'jsonb')
        .addColumn('settlementTransaction', 'text')
        .addColumn('settlementTransactionHash', 'text')
        .addColumn('snapshot', 'jsonb', (c) => c.notNull())
        .addColumn('sourceChainId', 'text', (c) => c.notNull())
        .addColumn('sourceTransactionHash', 'text', (c) => c.notNull())
        .addColumn('sourceTransferIndex', 'integer', (c) => c.notNull())
        .addColumn('status', 'text', (c) => c.notNull())
        .addColumn('statusReason', 'jsonb')
        .addColumn('subsidyAmount', 'jsonb')
        .addColumn('tempoGasPaid', 'text')
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addColumn('version', 'integer', (c) => c.notNull())
        .addCheckConstraint(
          'funding_deposits_source_transfer_index_check',
          sql`source_transfer_index >= 0`,
        )
        .addUniqueConstraint('funding_deposits_source_transfer_key', [
          'sourceChainId',
          'sourceTransactionHash',
          'sourceTransferIndex',
        ]),
  },
  {
    name: '0098_funding_deposits_address_index',
    up: (db) =>
      db.schema
        .createIndex('funding_deposits_address_idx')
        .ifNotExists()
        .on('fundingDeposits')
        .columns(['depositAddressId', 'createdAt', 'id']),
  },
  {
    name: '0099_funding_deposit_addresses_identity',
    // Null project ids must compare equal so organization-scoped callers share one route address.
    up: (db) =>
      raw(
        db,
        sql`CREATE UNIQUE INDEX IF NOT EXISTS funding_deposit_addresses_identity_idx ON funding_deposit_addresses (org_id, environment, project_id, source_chain_id, source_token_key, destination_token_key, recipient, refund_address) NULLS NOT DISTINCT`,
      ),
  },
  {
    name: '0100_funding_deposit_addresses_polling',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE funding_deposit_addresses
          ADD COLUMN IF NOT EXISTS last_polled_at text,
          ADD COLUMN IF NOT EXISTS next_poll_at text NOT NULL DEFAULT '1970-01-01T00:00:00.000Z',
          ADD COLUMN IF NOT EXISTS poll_failure_count integer NOT NULL DEFAULT 0,
          ADD COLUMN IF NOT EXISTS poll_lease_until text,
          ADD COLUMN IF NOT EXISTS poll_lease_version integer NOT NULL DEFAULT 0`,
      ),
  },
  {
    name: '0101_funding_deposit_addresses_polling_index',
    up: (db) =>
      db.schema
        .createIndex('funding_deposit_addresses_polling_idx')
        .ifNotExists()
        .on('fundingDepositAddresses')
        .columns(['providerId', 'status', 'nextPollAt', 'id']),
  },
  {
    name: '0102_funding_chains_rpc_urls',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE funding_chains ADD COLUMN IF NOT EXISTS rpc_urls jsonb NOT NULL DEFAULT '[]'::jsonb`,
      ),
  },
  {
    name: '0103_funding_deposit_observations',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE funding_deposits
          ADD COLUMN IF NOT EXISTS provider_request_id text,
          ADD COLUMN IF NOT EXISTS provider_transfer_index integer NOT NULL DEFAULT 0,
          ALTER COLUMN source_transfer_index DROP NOT NULL`,
      ),
  },
  {
    name: '0104_funding_deposits_provider_observation_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE UNIQUE INDEX IF NOT EXISTS funding_deposits_provider_observation_idx ON funding_deposits (deposit_address_id, provider_request_id, source_transaction_hash, provider_transfer_index) WHERE provider_request_id IS NOT NULL`,
      ),
  },
  {
    name: '0105_funding_deposit_addresses_subsidize',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE funding_deposit_addresses ADD COLUMN IF NOT EXISTS subsidize boolean NOT NULL DEFAULT false`,
      ),
  },
  {
    name: '0106_funding_deposit_addresses_snapshot_subsidize',
    up: (db) =>
      raw(
        db,
        sql`UPDATE funding_deposit_addresses SET snapshot = jsonb_set(snapshot, '{subsidize}', to_jsonb(subsidize), true) WHERE snapshot->'subsidize' IS DISTINCT FROM to_jsonb(subsidize)`,
      ),
  },
  {
    name: '0107_funding_deposit_addresses_subsidy_identity',
    // Replace the identity atomically because subsidized and unsubsidized addresses have different delivery contracts.
    up: (db) =>
      raw(
        db,
        sql`DO $$
          BEGIN
            DROP INDEX IF EXISTS funding_deposit_addresses_identity_idx;
            CREATE UNIQUE INDEX funding_deposit_addresses_identity_idx ON funding_deposit_addresses (org_id, environment, project_id, source_chain_id, source_token_key, destination_token_key, recipient, refund_address, subsidize) NULLS NOT DISTINCT;
          END
        $$`,
      ),
  },
  {
    name: '0108_funding_deposits_recipient_index',
    // The expression indexes existing snapshots without a deployment-order-sensitive column backfill.
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS funding_deposits_recipient_idx ON funding_deposits (org_id, environment, lower(snapshot->>'recipient'), created_at, id)`,
      ),
  },
  {
    name: '0109_funding_deposits_detection_trigger',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE funding_deposits ADD COLUMN IF NOT EXISTS detection_trigger text CHECK (detection_trigger IN ('manual', 'poll', 'webhook'))`,
      ),
  },
  {
    name: '0110_funding_deposit_request_observations',
    up: (db) =>
      db.schema
        .createTable('fundingDepositRequestObservations')
        .ifNotExists()
        .addColumn('depositAddressId', 'text', (c) =>
          c.notNull().references('fundingDepositAddresses.id').onDelete('cascade'),
        )
        .addColumn('pollObservedAt', 'text')
        .addColumn('providerRequestId', 'text', (c) => c.notNull())
        .addColumn('webhookReceivedAt', 'text')
        .addPrimaryKeyConstraint('funding_deposit_request_observations_pkey', [
          'depositAddressId',
          'providerRequestId',
        ]),
  },
  {
    name: '0111_funding_deposit_observation_timestamps',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE funding_deposits
          ADD COLUMN IF NOT EXISTS poll_observed_at text,
          ADD COLUMN IF NOT EXISTS webhook_received_at text`,
      ),
  },
  {
    name: '0112_funding_deposits_source_observation_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE UNIQUE INDEX IF NOT EXISTS funding_deposits_source_observation_idx ON funding_deposits (deposit_address_id, source_transaction_hash, source_transfer_index)`,
      ),
  },
  {
    name: '0113_funding_deposits_chain_detection_trigger',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE funding_deposits DROP CONSTRAINT IF EXISTS funding_deposits_detection_trigger_check, ADD CONSTRAINT funding_deposits_detection_trigger_check CHECK (detection_trigger IN ('chain', 'manual', 'poll', 'webhook'))`,
      ),
  },
  {
    name: '0114_funding_deposit_addresses_owner_address_index',
    up: (db) =>
      db.schema
        .createIndex('funding_deposit_addresses_owner_address_idx')
        .ifNotExists()
        .on('fundingDepositAddresses')
        .columns(['orgId', 'environment', 'address', 'projectId']),
  },
  {
    name: '0115_webhook_queue_events',
    up: (db) =>
      db.schema
        .createTable('webhookQueueEvents')
        .ifNotExists()
        .addColumn('subscriptionId', 'text', (c) =>
          c.notNull().references('webhookSubscriptions.id').onDelete('cascade'),
        )
        .addColumn('eventId', 'text', (c) => c.notNull())
        .addColumn('envelope', 'jsonb', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('expiresAt', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('webhook_queue_events_pkey', ['subscriptionId', 'eventId']),
  },
  {
    name: '0116_webhook_queue_events_expires_index',
    up: (db) =>
      db.schema
        .createIndex('webhook_queue_events_expires_idx')
        .ifNotExists()
        .on('webhookQueueEvents')
        .column('expiresAt'),
  },
  {
    name: '0117_webhook_queue_events_ledger_columns',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE webhook_queue_events
          ADD COLUMN IF NOT EXISTS status text NOT NULL DEFAULT 'pending',
          ADD COLUMN IF NOT EXISTS attempt_count integer NOT NULL DEFAULT 0,
          ADD COLUMN IF NOT EXISTS attempting_at text,
          ADD COLUMN IF NOT EXISTS next_attempt_at text`,
      ),
  },
  {
    name: '0118_webhook_queue_events_status_check',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE webhook_queue_events DROP CONSTRAINT IF EXISTS webhook_queue_events_status_check, ADD CONSTRAINT webhook_queue_events_status_check CHECK (status IN ('failed', 'pending', 'skipped', 'succeeded'))`,
      ),
  },
  {
    name: '0119_webhook_queue_events_pending_index',
    up: (db) =>
      // NULLS FIRST matches the due-sweep order (null = due immediately);
      // INCLUDE lets the pending gauge take min(created_at) index-only.
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS webhook_queue_events_pending_idx ON webhook_queue_events (next_attempt_at NULLS FIRST) INCLUDE (created_at) WHERE status = 'pending'`,
      ),
  },
  {
    name: '0120_webhook_queue_events_pending_index_drop',
    up: (db) => raw(db, sql`DROP INDEX IF EXISTS webhook_queue_events_pending_idx`),
  },
  {
    name: '0121_webhook_queue_events_pending_index_expiry',
    up: (db) =>
      // Recreated with expires_at so claimable and gauge queries, which all
      // filter expiry at query time, stay index-only.
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS webhook_queue_events_pending_idx ON webhook_queue_events (next_attempt_at NULLS FIRST) INCLUDE (created_at, expires_at) WHERE status = 'pending'`,
      ),
  },
  {
    name: '0122_webhook_queue_events_expired_pending_cleanup',
    up: (db) =>
      // Pre-ledger rows already past their staging TTL were dead under the
      // old read path; pending rows no longer expire, so drop them once
      // instead of resurrecting them as eternal obligations.
      raw(
        db,
        sql`DELETE FROM webhook_queue_events WHERE status = 'pending' AND attempting_at IS NULL AND expires_at <= to_char(now() AT TIME ZONE 'utc', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')`,
      ),
  },
  {
    name: '0123_funding_deposit_addresses_owner_list_index',
    up: (db) =>
      db.schema
        .createIndex('funding_deposit_addresses_owner_list_idx')
        .ifNotExists()
        .on('fundingDepositAddresses')
        .columns(['orgId', 'environment', 'createdAt', 'id']),
  },
  {
    name: '0124_funding_deposit_addresses_project_list_index',
    up: (db) =>
      db.schema
        .createIndex('funding_deposit_addresses_project_list_idx')
        .ifNotExists()
        .on('fundingDepositAddresses')
        .columns(['orgId', 'environment', 'projectId', 'createdAt', 'id']),
  },
  {
    name: '0125_funding_deposits_owner_list_index',
    up: (db) =>
      db.schema
        .createIndex('funding_deposits_owner_list_idx')
        .ifNotExists()
        .on('fundingDeposits')
        .columns(['orgId', 'environment', 'createdAt', 'id']),
  },
  {
    name: '0126_funding_deposits_project_list_index',
    up: (db) =>
      db.schema
        .createIndex('funding_deposits_project_list_idx')
        .ifNotExists()
        .on('fundingDeposits')
        .columns(['orgId', 'environment', 'projectId', 'createdAt', 'id']),
  },
  {
    name: '0127_webhook_subscriptions_environment',
    up: (db) => raw(db, sql`ALTER TABLE webhook_subscriptions ADD COLUMN IF NOT EXISTS environment text`), // prettier-ignore
  },
  {
    name: '0128_webhook_subscriptions_project',
    up: (db) => raw(db, sql`ALTER TABLE webhook_subscriptions ADD COLUMN IF NOT EXISTS project_id text`), // prettier-ignore
  },
  {
    name: '0129_webhook_subscriptions_funding_deposit_index',
    up: (db) =>
      db.schema
        .createIndex('webhook_subscriptions_funding_deposit_idx')
        .ifNotExists()
        .on('webhookSubscriptions')
        .columns(['eventType', 'ownerType', 'ownerId', 'environment', 'projectId', 'chainId'])
        .where(sql.ref('status'), '=', sql.lit('active')),
  },
  {
    name: '0130_webhook_queue_events_observed_at',
    up: (db) =>
      raw(db, sql`ALTER TABLE webhook_queue_events ADD COLUMN IF NOT EXISTS observed_at text`),
  },
  {
    name: '0131_webhook_queue_completions',
    up: (db) =>
      db.schema
        .createTable('webhookQueueCompletions')
        .ifNotExists()
        .addColumn('subscriptionId', 'text', (c) =>
          c.notNull().references('webhookSubscriptions.id').onDelete('cascade'),
        )
        .addColumn('eventId', 'text', (c) => c.notNull())
        .addColumn('status', 'text', (c) => c.notNull())
        .addColumn('expiresAt', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('webhook_queue_completions_pkey', ['subscriptionId', 'eventId'])
        .addCheckConstraint(
          'webhook_queue_completions_status_check',
          sql`status IN ('failed', 'skipped', 'succeeded')`,
        ),
  },
  {
    name: '0132_webhook_queue_completions_expires_index',
    up: (db) =>
      db.schema
        .createIndex('webhook_queue_completions_expires_idx')
        .ifNotExists()
        .on('webhookQueueCompletions')
        .column('expiresAt'),
  },
  {
    // Stock thresholds let ~2M dead tuples accumulate before vacuuming, then
    // grind gigabytes at once; frequent small vacuums keep indexes usable.
    name: '0133_webhook_queue_events_autovacuum',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE webhook_queue_events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_limit = 2000)`,
      ),
  },
  {
    // New code stops writing queue-event expiry: completions own the dedupe
    // window, so the column is nullable and inert.
    name: '0134_webhook_queue_events_expires_nullable',
    up: (db) =>
      raw(db, sql`ALTER TABLE webhook_queue_events ALTER COLUMN expires_at DROP NOT NULL`),
  },
  {
    name: '0135_webhook_queue_completions_autovacuum',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE webhook_queue_completions SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_limit = 2000)`,
      ),
  },
  {
    name: '0136_earn_vault_zones',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE earn_vaults ADD COLUMN IF NOT EXISTS zones jsonb NOT NULL DEFAULT '[]'::jsonb`,
      ),
  },
  {
    name: '0137_funding_routes_configuration',
    up: (db) =>
      raw(db, sql`ALTER TABLE funding_routes ADD COLUMN IF NOT EXISTS configuration jsonb`),
  },
  {
    name: '0138_organizations_sponsorship_subsidy',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE organizations
          ADD COLUMN IF NOT EXISTS sponsorship_subsidy_duration_days integer,
          ADD COLUMN IF NOT EXISTS sponsorship_subsidy_project_spend_limit text`,
      ),
  },
  {
    name: '0139_projects_sponsorship_activation',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE projects
          ADD COLUMN IF NOT EXISTS sponsorship_subsidy_starts_at text,
          ADD COLUMN IF NOT EXISTS sponsorship_subsidy_ends_at text,
          DROP CONSTRAINT IF EXISTS projects_sponsorship_subsidy_window_consistent,
          ADD CONSTRAINT projects_sponsorship_subsidy_window_consistent CHECK ((sponsorship_subsidy_starts_at IS NULL) = (sponsorship_subsidy_ends_at IS NULL))`,
      ),
  },
  {
    name: '0140_projects_sponsorship_spend_limit',
    up: (db) =>
      raw(db, sql`ALTER TABLE projects ADD COLUMN IF NOT EXISTS sponsorship_spend_limit text`),
  },
  {
    name: '0141_sponsored_transactions_promotion_spend_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS sponsored_transactions_promotion_spend_idx ON sponsored_transactions (org_id, project_id, status, created_at) WHERE NOT billable`,
      ),
  },
  {
    name: '0142_funding_transfers_status_updated_at',
    up: (db) =>
      raw(db, sql`ALTER TABLE funding_transfers ADD COLUMN IF NOT EXISTS status_updated_at text`),
  },
  {
    name: '0143_funding_deposits_status_updated_at',
    up: (db) =>
      raw(db, sql`ALTER TABLE funding_deposits ADD COLUMN IF NOT EXISTS status_updated_at text`),
  },
  {
    name: '0144_funding_deposit_addresses_status_updated_at',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE funding_deposit_addresses ADD COLUMN IF NOT EXISTS status_updated_at text`,
      ),
  },
  {
    name: '0145_funding_transfers_state_metrics_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS funding_transfers_state_metrics_idx ON funding_transfers (status, environment, provider_id, method, (status_reason->>'code'), status_updated_at, updated_at)`,
      ),
  },
  {
    name: '0146_funding_deposits_state_metrics_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS funding_deposits_state_metrics_idx ON funding_deposits (status, environment, deposit_address_id, (status_reason->>'code'), status_updated_at, updated_at)`,
      ),
  },
  {
    name: '0147_funding_deposits_provider_delivered_at',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE funding_deposits ADD COLUMN IF NOT EXISTS provider_delivered_at text`,
      ),
  },
  {
    name: '0148_funding_transfers_updated_at_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS funding_transfers_updated_at_idx ON funding_transfers (updated_at DESC, id DESC)`,
      ),
  },
  {
    name: '0149_funding_deposits_updated_at_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS funding_deposits_updated_at_idx ON funding_deposits (updated_at DESC, id DESC)`,
      ),
  },
  {
    name: '0150_funding_deposit_addresses_updated_at_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS funding_deposit_addresses_updated_at_idx ON funding_deposit_addresses (updated_at DESC, id DESC)`,
      ),
  },
  {
    name: '0151_sponsorship_attributions',
    up: (db) =>
      db.schema
        .createTable('sponsorshipAttributions')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey())
        .addColumn('orgId', 'text', (c) => c.notNull())
        .addColumn('externalId', 'text')
        .addColumn('projectId', 'text')
        .addColumn('startsAt', 'text')
        .addColumn('endsAt', 'text')
        .addColumn('spendLimit', 'text')
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addCheckConstraint(
          'sponsorship_attributions_source_check',
          sql`(external_id IS NULL) <> (project_id IS NULL)`,
        )
        .addCheckConstraint(
          'sponsorship_attributions_window_check',
          sql`(starts_at IS NULL) = (ends_at IS NULL)`,
        ),
  },
  {
    name: '0152_sponsorship_attributions_external_unique',
    up: (db) =>
      raw(
        db,
        sql`CREATE UNIQUE INDEX IF NOT EXISTS sponsorship_attributions_external_unique ON sponsorship_attributions (org_id, external_id) WHERE external_id IS NOT NULL`,
      ),
  },
  {
    name: '0153_sponsorship_attributions_project_unique',
    up: (db) =>
      raw(
        db,
        sql`CREATE UNIQUE INDEX IF NOT EXISTS sponsorship_attributions_project_unique ON sponsorship_attributions (org_id, project_id) WHERE project_id IS NOT NULL`,
      ),
  },
  {
    name: '0154_sponsored_transactions_attribution_id',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE sponsored_transactions ADD COLUMN IF NOT EXISTS sponsorship_attribution_id text`,
      ),
  },
  {
    name: '0155_sponsored_transactions_attribution_spend_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS sponsored_transactions_attribution_spend_idx ON sponsored_transactions (org_id, sponsorship_attribution_id, status, created_at) WHERE NOT billable`,
      ),
  },
  {
    name: '0156_project_sponsorship_attributions',
    up: (db) =>
      raw(
        db,
        sql`INSERT INTO sponsorship_attributions (id, org_id, external_id, project_id, starts_at, ends_at, spend_limit, created_at, updated_at)
          SELECT 'sat_' || md5(projects.org_id || ':' || projects.id), projects.org_id, NULL, projects.id, projects.sponsorship_subsidy_starts_at, projects.sponsorship_subsidy_ends_at, projects.sponsorship_spend_limit, projects.created_at, projects.updated_at
          FROM projects
          WHERE projects.sponsorship_subsidy_starts_at IS NOT NULL
          ON CONFLICT (org_id, project_id) WHERE project_id IS NOT NULL DO UPDATE
          SET starts_at = EXCLUDED.starts_at, ends_at = EXCLUDED.ends_at, spend_limit = EXCLUDED.spend_limit, updated_at = EXCLUDED.updated_at
          WHERE sponsorship_attributions.starts_at IS NULL`,
      ),
  },
  {
    name: '0157_project_sponsored_transaction_attributions',
    up: (db) =>
      raw(
        db,
        sql`UPDATE sponsored_transactions
          SET sponsorship_attribution_id = sponsorship_attributions.id
          FROM sponsorship_attributions
          WHERE sponsored_transactions.sponsorship_attribution_id IS NULL
            AND sponsored_transactions.org_id = sponsorship_attributions.org_id
            AND sponsored_transactions.project_id = sponsorship_attributions.project_id`,
      ),
  },
  {
    name: '0158_reward_eligibility_associations',
    up: (db) =>
      db.schema
        .createTable('rewardEligibilityAssociations')
        .ifNotExists()
        .addColumn('chainId', 'bigint', (c) => c.notNull())
        .addColumn('vaultAddress', 'text', (c) => c.notNull())
        .addColumn('walletAddress', 'text', (c) => c.notNull())
        .addColumn('firstRegisteredAt', 'text', (c) => c.notNull())
        .addColumn('latestRegisteredAt', 'text', (c) => c.notNull())
        .addColumn('transactionHash', 'text')
        .addPrimaryKeyConstraint('reward_eligibility_associations_pkey', [
          'chainId',
          'vaultAddress',
          'walletAddress',
        ]),
  },
  {
    name: '0159_reward_eligibility_registration_order',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE reward_eligibility_associations ADD COLUMN IF NOT EXISTS registration_order bigint GENERATED ALWAYS AS IDENTITY UNIQUE`,
      ),
  },
  {
    name: '0160_reward_eligibility_page_index',
    up: (db) =>
      db.schema
        .createIndex('reward_eligibility_associations_page_idx')
        .ifNotExists()
        .on('rewardEligibilityAssociations')
        .columns(['chainId', 'vaultAddress', 'registrationOrder']),
  },
  {
    name: '0161_users_name',
    up: (db) => raw(db, sql`ALTER TABLE users ADD COLUMN IF NOT EXISTS name text`),
  },
  {
    name: '0162_users_image',
    up: (db) => raw(db, sql`ALTER TABLE users ADD COLUMN IF NOT EXISTS image text`),
  },
  {
    name: '0163_users_email_verified',
    up: (db) => raw(db, sql`ALTER TABLE users ADD COLUMN IF NOT EXISTS email_verified boolean`),
  },
  {
    name: '0164_users_auth_fields_backfill',
    up: (db) =>
      raw(
        db,
        sql`UPDATE users SET email_verified = email IS NOT NULL, name = COALESCE(NULLIF(btrim(email), ''), id) WHERE email_verified IS NULL OR name IS NULL`,
      ),
  },
  {
    name: '0165_users_auth_fields_required',
    // Defaults keep inserts from the previous Worker valid while the new
    // required Better Auth fields roll out.
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE users ALTER COLUMN email_verified SET DEFAULT false, ALTER COLUMN email_verified SET NOT NULL, ALTER COLUMN name SET DEFAULT '', ALTER COLUMN name SET NOT NULL`,
      ),
  },
  {
    name: '0166_users_email_auth_precondition',
    up: (db) =>
      raw(
        db,
        sql`DO $$ BEGIN
          IF EXISTS (SELECT 1 FROM users WHERE email IS NOT NULL AND btrim(email) = '') THEN
            RAISE EXCEPTION 'users.email contains blank values';
          END IF;
        END $$`,
      ),
  },
  {
    name: '0167_users_email_normalized',
    up: (db) => raw(db, sql`UPDATE users SET email = lower(btrim(email))`),
  },
  {
    name: '0168_users_email_index',
    // SIWE still creates address-keyed users. Enforce email uniqueness in a
    // later deployment after wallet identities are linked.
    up: (db) => raw(db, sql`CREATE INDEX IF NOT EXISTS users_email_idx ON users (email)`),
  },
  {
    name: '0169_users_email_normalized_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS users_email_normalized_idx ON users (lower(btrim(email))) WHERE email IS NOT NULL`,
      ),
  },
  {
    name: '0170_auth_sessions',
    up: (db) =>
      db.schema
        .createTable('authSessions')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey().notNull())
        .addColumn('expiresAt', 'text', (c) => c.notNull())
        .addColumn('token', 'text', (c) => c.notNull().unique())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addColumn('ipAddress', 'text')
        .addColumn('userAgent', 'text')
        .addColumn('userId', 'text', (c) => c.notNull().references('users.id').onDelete('cascade')),
  },
  {
    name: '0171_auth_sessions_user_index',
    up: (db) =>
      db.schema
        .createIndex('auth_sessions_user_id_idx')
        .ifNotExists()
        .on('authSessions')
        .column('userId'),
  },
  {
    name: '0172_auth_accounts',
    up: (db) =>
      db.schema
        .createTable('authAccounts')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey().notNull())
        .addColumn('issuer', 'text', (c) => c.notNull())
        .addColumn('accountId', 'text', (c) => c.notNull())
        .addColumn('providerId', 'text', (c) => c.notNull())
        .addColumn('userId', 'text', (c) => c.notNull().references('users.id').onDelete('cascade'))
        .addColumn('accessToken', 'text')
        .addColumn('refreshToken', 'text')
        .addColumn('idToken', 'text')
        .addColumn('accessTokenExpiresAt', 'text')
        .addColumn('refreshTokenExpiresAt', 'text')
        .addColumn('scope', 'text')
        .addColumn('password', 'text')
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull()),
  },
  {
    name: '0173_auth_accounts_user_index',
    up: (db) =>
      db.schema
        .createIndex('auth_accounts_user_id_idx')
        .ifNotExists()
        .on('authAccounts')
        .column('userId'),
  },
  {
    name: '0174_auth_accounts_identity_index',
    up: (db) =>
      db.schema
        .createIndex('auth_accounts_issuer_account_id_unique_idx')
        .ifNotExists()
        .unique()
        .on('authAccounts')
        .columns(['issuer', 'accountId']),
  },
  {
    name: '0175_auth_verifications',
    up: (db) =>
      db.schema
        .createTable('authVerifications')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey().notNull())
        .addColumn('identifier', 'text', (c) => c.notNull())
        .addColumn('value', 'text', (c) => c.notNull())
        .addColumn('expiresAt', 'text', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull()),
  },
  {
    name: '0176_auth_verifications_identifier_index',
    up: (db) =>
      db.schema
        .createIndex('auth_verifications_identifier_idx')
        .ifNotExists()
        .on('authVerifications')
        .column('identifier'),
  },
  {
    name: '0177_auth_sessions_provider',
    up: (db) => raw(db, sql`ALTER TABLE auth_sessions ADD COLUMN IF NOT EXISTS provider text`),
  },
  {
    name: '0178_reward_campaigns',
    up: (db) =>
      db.schema
        .createTable('rewardCampaigns')
        .ifNotExists()
        .addColumn('chainId', 'bigint', (c) => c.notNull())
        .addColumn('vaultAddress', 'text', (c) => c.notNull())
        .addColumn('assetAddress', 'text', (c) => c.notNull())
        .addColumn('assetDecimals', 'integer', (c) => c.notNull())
        .addColumn('earnShareAddress', 'text', (c) => c.notNull())
        .addColumn('controllerAddress', 'text')
        .addColumn('distributorAddress', 'text')
        .addColumn('config', 'jsonb', (c) => c.notNull())
        .addColumn('pendingConfig', 'jsonb')
        .addColumn('pendingEffectiveAt', 'bigint')
        .addColumn('eventCursor', 'jsonb')
        .addColumn('deliveredThrough', 'bigint', (c) => c.notNull())
        .addColumn('paused', 'boolean', (c) => c.notNull().defaultTo(false))
        .addColumn('provisioningError', 'text')
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('reward_campaigns_pkey', ['chainId', 'vaultAddress'])
        .addForeignKeyConstraint(
          'reward_campaigns_vault_fkey',
          ['chainId', 'vaultAddress'],
          'earnVaults',
          ['chainId', 'vaultAddress'],
          (c) => c.onDelete('restrict'),
        ),
  },
  {
    name: '0179_reward_accounts',
    up: (db) =>
      db.schema
        .createTable('rewardAccounts')
        .ifNotExists()
        .addColumn('chainId', 'bigint', (c) => c.notNull())
        .addColumn('vaultAddress', 'text', (c) => c.notNull())
        .addColumn('recipient', 'text', (c) => c.notNull())
        .addColumn('registrationOrder', 'bigint', (c) => c.notNull())
        .addColumn('publicEarnShares', 'numeric', (c) => c.notNull())
        .addColumn('qualifiedEarnShares', 'numeric', (c) => c.notNull())
        .addColumn('allocatedPrincipalAssets', 'numeric', (c) => c.notNull())
        .addColumn('accrualRemainder', 'numeric', (c) => c.notNull())
        .addColumn('pendingRewardAssets', 'numeric', (c) => c.notNull())
        .addColumn('cumulativeEntitlement', 'numeric', (c) => c.notNull())
        .addColumn('cumulativePaid', 'numeric', (c) => c.notNull())
        .addColumn('deferral', 'text')
        .addColumn('lots', 'jsonb', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('reward_accounts_pkey', ['chainId', 'vaultAddress', 'recipient'])
        .addForeignKeyConstraint(
          'reward_accounts_campaign_fkey',
          ['chainId', 'vaultAddress'],
          'rewardCampaigns',
          ['chainId', 'vaultAddress'],
          (c) => c.onDelete('cascade'),
        ),
  },
  {
    name: '0180_reward_runs',
    up: (db) =>
      db.schema
        .createTable('rewardRuns')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey().notNull())
        .addColumn('chainId', 'bigint', (c) => c.notNull())
        .addColumn('vaultAddress', 'text', (c) => c.notNull())
        .addColumn('startsAfter', 'bigint', (c) => c.notNull())
        .addColumn('endsAt', 'bigint', (c) => c.notNull())
        .addColumn('config', 'jsonb', (c) => c.notNull())
        .addColumn('phase', 'text', (c) => c.notNull())
        .addColumn('leaseExpiresAt', 'text')
        .addColumn('fence', 'bigint', (c) => c.notNull().defaultTo(0))
        .addColumn('evidence', 'jsonb')
        .addColumn('fundedAssets', 'numeric')
        .addColumn('mintedEarnShares', 'numeric')
        .addColumn('statement', 'jsonb')
        .addColumn('statementHash', 'text')
        .addColumn('root', 'text')
        .addColumn('rootVersion', 'bigint')
        .addColumn('liability', 'numeric')
        .addColumn('error', 'text')
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addUniqueConstraint('reward_runs_boundary_key', ['chainId', 'vaultAddress', 'startsAfter'])
        .addForeignKeyConstraint(
          'reward_runs_campaign_fkey',
          ['chainId', 'vaultAddress'],
          'rewardCampaigns',
          ['chainId', 'vaultAddress'],
          (c) => c.onDelete('cascade'),
        ),
  },
  {
    name: '0181_reward_transaction_attempts',
    up: (db) =>
      db.schema
        .createTable('rewardTransactionAttempts')
        .ifNotExists()
        .addColumn('id', 'text', (c) => c.primaryKey().notNull())
        .addColumn('chainId', 'bigint', (c) => c.notNull())
        .addColumn('vaultAddress', 'text', (c) => c.notNull())
        .addColumn('runId', 'text')
        .addColumn('intent', 'jsonb', (c) => c.notNull())
        .addColumn('intentId', 'text', (c) => c.notNull())
        .addColumn('signer', 'text', (c) => c.notNull())
        .addColumn('nonce', 'numeric')
        .addColumn('expiresAt', 'text')
        .addColumn('signedBytes', 'text')
        .addColumn('transactionHash', 'text')
        .addColumn('state', 'text', (c) => c.notNull())
        .addColumn('receipt', 'jsonb')
        .addColumn('confirmedBlockHash', 'text')
        .addColumn('replacementOf', 'text')
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addForeignKeyConstraint(
          'reward_transaction_attempts_campaign_fkey',
          ['chainId', 'vaultAddress'],
          'rewardCampaigns',
          ['chainId', 'vaultAddress'],
          (c) => c.onDelete('cascade'),
        )
        .addForeignKeyConstraint(
          'reward_transaction_attempts_run_fkey',
          ['runId'],
          'rewardRuns',
          ['id'],
          (c) => c.onDelete('cascade'),
        )
        .addForeignKeyConstraint(
          'reward_transaction_attempts_replacement_fkey',
          ['replacementOf'],
          'rewardTransactionAttempts',
          ['id'],
          (c) => c.onDelete('restrict'),
        ),
  },
  {
    name: '0182_reward_transaction_attempt_nonce_key',
    up: (db) =>
      raw(
        db,
        sql`CREATE UNIQUE INDEX IF NOT EXISTS reward_transaction_attempt_nonce_key ON reward_transaction_attempts (chain_id, signer, nonce) WHERE nonce IS NOT NULL`,
      ),
  },
  {
    name: '0183_reward_campaigns_due_index',
    up: (db) =>
      db.schema
        .createIndex('reward_campaigns_due_idx')
        .ifNotExists()
        .on('rewardCampaigns')
        .columns(['paused', 'deliveredThrough']),
  },
  {
    name: '0184_reward_transaction_attempt_live_intent_key',
    up: (db) =>
      raw(
        db,
        sql`CREATE UNIQUE INDEX IF NOT EXISTS reward_transaction_attempt_live_intent_key ON reward_transaction_attempts (intent_id) WHERE state IN ('created', 'signed', 'broadcast')`,
      ),
  },
  {
    name: '0185_reward_campaign_signer',
    up: (db) =>
      raw(db, sql`ALTER TABLE reward_campaigns ADD COLUMN IF NOT EXISTS signer_address text`),
  },
  {
    name: '0186_reward_transaction_attempt_run_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS reward_transaction_attempt_run_idx ON reward_transaction_attempts (run_id, created_at)`,
      ),
  },
  {
    name: '0187_reward_transaction_attempt_intent_run_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS reward_transaction_attempt_intent_run_idx ON reward_transaction_attempts (intent_id, run_id, created_at)`,
      ),
  },
  {
    name: '0188_reward_campaign_earn_share_decimals',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE reward_campaigns ADD COLUMN IF NOT EXISTS earn_share_decimals integer NOT NULL DEFAULT 18`,
      ),
  },
  {
    name: '0189_reward_campaign_earn_share_decimals_default',
    up: (db) =>
      raw(db, sql`ALTER TABLE reward_campaigns ALTER COLUMN earn_share_decimals DROP DEFAULT`),
  },
  {
    name: '0190_routes_tables',
    up: (db) =>
      raw(
        db,
        sql`DO $$
          DECLARE
            entry record;
            target_name text;
          BEGIN
            -- Tests replay every DDL statement on new connections, recreating empty legacy tables.
            IF to_regclass('routes_catalogs') IS NOT NULL THEN
              DROP TABLE IF EXISTS
                funding_deposit_request_observations,
                funding_deposits,
                funding_deposit_addresses,
                funding_transfer_transactions,
                funding_transfer_events,
                funding_idempotency_requests,
                funding_transfers,
                funding_routes,
                funding_chain_tokens,
                funding_tokens,
                funding_chains,
                funding_catalogs
              CASCADE;
            END IF;

            IF to_regclass('funding_catalogs') IS NOT NULL AND to_regclass('routes_catalogs') IS NULL THEN
              ALTER TABLE funding_catalogs RENAME TO routes_catalogs;
            END IF;
            IF to_regclass('funding_chain_tokens') IS NOT NULL AND to_regclass('routes_chain_tokens') IS NULL THEN
              ALTER TABLE funding_chain_tokens RENAME TO routes_chain_tokens;
            END IF;
            IF to_regclass('funding_chains') IS NOT NULL AND to_regclass('routes_chains') IS NULL THEN
              ALTER TABLE funding_chains RENAME TO routes_chains;
            END IF;
            IF to_regclass('funding_deposit_addresses') IS NOT NULL AND to_regclass('routes_deposit_addresses') IS NULL THEN
              ALTER TABLE funding_deposit_addresses RENAME TO routes_deposit_addresses;
            END IF;
            IF to_regclass('funding_deposit_request_observations') IS NOT NULL AND to_regclass('routes_deposit_request_observations') IS NULL THEN
              ALTER TABLE funding_deposit_request_observations RENAME TO routes_deposit_request_observations;
            END IF;
            IF to_regclass('funding_deposits') IS NOT NULL AND to_regclass('routes_deposits') IS NULL THEN
              ALTER TABLE funding_deposits RENAME TO routes_deposits;
            END IF;
            IF to_regclass('funding_idempotency_requests') IS NOT NULL AND to_regclass('routes_idempotency_requests') IS NULL THEN
              ALTER TABLE funding_idempotency_requests RENAME TO routes_idempotency_requests;
            END IF;
            IF to_regclass('funding_routes') IS NOT NULL AND to_regclass('routes_routes') IS NULL THEN
              ALTER TABLE funding_routes RENAME TO routes_routes;
            END IF;
            IF to_regclass('funding_tokens') IS NOT NULL AND to_regclass('routes_tokens') IS NULL THEN
              ALTER TABLE funding_tokens RENAME TO routes_tokens;
            END IF;
            IF to_regclass('funding_transfer_events') IS NOT NULL AND to_regclass('routes_transfer_events') IS NULL THEN
              ALTER TABLE funding_transfer_events RENAME TO routes_transfer_events;
            END IF;
            IF to_regclass('funding_transfer_transactions') IS NOT NULL AND to_regclass('routes_transfer_transactions') IS NULL THEN
              ALTER TABLE funding_transfer_transactions RENAME TO routes_transfer_transactions;
            END IF;
            IF to_regclass('funding_transfers') IS NOT NULL AND to_regclass('routes_transfers') IS NULL THEN
              ALTER TABLE funding_transfers RENAME TO routes_transfers;
            END IF;

            UPDATE routes_deposit_addresses
            SET snapshot = jsonb_set(snapshot, '{subsidize}', to_jsonb(subsidize), true)
            WHERE snapshot->'subsidize' IS DISTINCT FROM to_jsonb(subsidize);

            FOR entry IN
              SELECT c.conname AS name, t.relname AS table_name
              FROM pg_constraint AS c
              JOIN pg_class AS t ON t.oid = c.conrelid
              JOIN pg_namespace AS n ON n.oid = t.relnamespace
              WHERE n.nspname = current_schema() AND c.conname LIKE 'funding\_%' ESCAPE '\\'
            LOOP
              target_name := regexp_replace(entry.name, '^funding_', 'routes_');
              EXECUTE format(
                'ALTER TABLE %I.%I RENAME CONSTRAINT %I TO %I',
                current_schema(),
                entry.table_name,
                entry.name,
                target_name
              );
            END LOOP;

            FOR entry IN
              SELECT indexname AS name
              FROM pg_indexes
              WHERE schemaname = current_schema() AND indexname LIKE 'funding\_%' ESCAPE '\\'
            LOOP
              target_name := regexp_replace(entry.name, '^funding_', 'routes_');
              IF to_regclass(target_name) IS NULL THEN
                EXECUTE format('ALTER INDEX %I.%I RENAME TO %I', current_schema(), entry.name, target_name);
              ELSE
                EXECUTE format('DROP INDEX %I.%I', current_schema(), entry.name);
              END IF;
            END LOOP;
          END $$`,
      ),
  },
  {
    // Correct the deelUSD router boundary so registry reads can verify its bytecode.
    name: '0191_deelusd_earn_router_deployment_block',
    up: (db) =>
      raw(
        db,
        sql`UPDATE earn_vaults
          SET zones = jsonb_set(zones, '{0,deploymentBlock}', '35822949'::jsonb)
          WHERE chain_id = 4217
            AND vault_address = '0x4f94590b636f5878bce585e82379de81e1ec174f'
            AND zones->0->>'chainId' = '421700001'
            AND lower(zones->0->>'earnRouter') = '0x8117e0ba6239b9695f780deb010f72a2fa4bdfb6'
            AND zones->0->>'deploymentBlock' = '35816964'`,
      ),
  },
  {
    name: '0192_reward_account_eligibility_registration',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE reward_accounts ADD COLUMN IF NOT EXISTS eligibility_registered_at text`,
      ),
  },
  {
    name: '0193_routes_subsidies',
    up: (db) =>
      db.schema
        .createTable('routesSubsidies')
        .ifNotExists()
        .addColumn('apiKeyId', 'text')
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addColumn('enabled', 'boolean', (c) => c.notNull())
        .addColumn('frequency', 'text', (c) => c.notNull())
        .addColumn('maxAmount', 'text')
        .addColumn('orgId', 'text', (c) =>
          c.notNull().references('organizations.id').onDelete('cascade'),
        )
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addCheckConstraint('routes_subsidies_frequency_check', sql`frequency = 'tx'`)
        .addCheckConstraint(
          'routes_subsidies_enabled_check',
          sql`CASE WHEN enabled THEN CASE WHEN max_amount ~ '^(0|[1-9][0-9]*)([.][0-9]{1,6})?$' THEN max_amount::numeric > 0 ELSE false END ELSE max_amount IS NULL END`,
        ),
  },
  {
    name: '0194_routes_subsidies_org_unique',
    up: (db) =>
      raw(
        db,
        sql`CREATE UNIQUE INDEX IF NOT EXISTS routes_subsidies_org_unique ON routes_subsidies (org_id) WHERE api_key_id IS NULL`,
      ),
  },
  {
    name: '0195_routes_subsidies_api_key_unique',
    up: (db) =>
      raw(
        db,
        sql`CREATE UNIQUE INDEX IF NOT EXISTS routes_subsidies_api_key_unique ON routes_subsidies (api_key_id) WHERE api_key_id IS NOT NULL`,
      ),
  },
  {
    name: '0196_routes_subsidies_backfill',
    up: (db) =>
      raw(
        db,
        sql`INSERT INTO routes_subsidies (api_key_id, created_at, enabled, frequency, max_amount, org_id, updated_at) SELECT NULL, created_at, true, 'tx', '5', id, updated_at FROM organizations ON CONFLICT DO NOTHING`,
      ),
  },
  {
    name: '0197_routes_deposits_subsidy_meter_reported_at',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE routes_deposits ADD COLUMN IF NOT EXISTS subsidy_meter_reported_at text`,
      ),
  },
  {
    name: '0198_routes_deposits_unreported_subsidy_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS routes_deposits_unreported_subsidy_idx ON routes_deposits (status_updated_at, id) WHERE environment = 'production' AND status = 'completed' AND settlement_transaction_hash IS NOT NULL AND subsidy_amount IS NOT NULL AND subsidy_meter_reported_at IS NULL`,
      ),
  },
  {
    name: '0199_routes_deposits_subsidy_usage_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS routes_deposits_subsidy_usage_idx ON routes_deposits (org_id, environment, status_updated_at) WHERE status = 'completed' AND settlement_transaction_hash IS NOT NULL AND subsidy_amount IS NOT NULL`,
      ),
  },
  {
    name: '0200_routes_deposits_legacy_subsidies_reported',
    up: (db) =>
      raw(
        db,
        sql`UPDATE routes_deposits SET subsidy_meter_reported_at = coalesce(status_updated_at, updated_at) WHERE environment = 'production' AND settlement_transaction_hash IS NOT NULL AND subsidy_amount IS NOT NULL AND subsidy_meter_reported_at IS NULL`,
      ),
  },
  {
    name: '0201_routes_deposits_billable_subsidy_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS routes_deposits_billable_subsidy_idx ON routes_deposits (status_updated_at, id) WHERE environment = 'production' AND settlement_transaction_hash IS NOT NULL AND subsidy_amount IS NOT NULL AND (subsidy_amount->>'baseUnits')::numeric > 0 AND subsidy_meter_reported_at IS NULL AND (status = 'completed' OR (status IN ('action-required', 'settling') AND tempo_gas_paid IS NOT NULL))`,
      ),
  },
  {
    name: '0202_routes_deposits_provider_subsidy',
    up: (db) =>
      raw(db, sql`ALTER TABLE routes_deposits ADD COLUMN IF NOT EXISTS provider_subsidy jsonb`),
  },
  {
    name: '0203_routes_deposits_billable_subsidy_index_v2',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS routes_deposits_billable_subsidy_v2_idx ON routes_deposits (status_updated_at, id) WHERE environment = 'production' AND subsidy_meter_reported_at IS NULL AND (status = 'completed' OR (status IN ('action-required', 'settling') AND tempo_gas_paid IS NOT NULL)) AND (((provider_subsidy->'amount'->>'baseUnits')::numeric > 0) OR (settlement_transaction_hash IS NOT NULL AND (subsidy_amount->>'baseUnits')::numeric > 0))`,
      ),
  },
  {
    name: '0204_routes_deposits_subsidy_usage_index_v2',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS routes_deposits_subsidy_usage_v2_idx ON routes_deposits (org_id, environment, subsidy_meter_reported_at) WHERE (provider_subsidy->'amount'->>'baseUnits')::numeric > 0 OR (subsidy_amount->>'baseUnits')::numeric > 0`,
      ),
  },
  {
    name: '0205_sponsored_transactions_admin_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS sponsored_transactions_admin_idx ON sponsored_transactions (created_at DESC, id DESC)`,
      ),
  },
  {
    name: '0206_routes_deposits_admin_subsidy_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS routes_deposits_admin_subsidy_idx ON routes_deposits (updated_at DESC, id DESC) WHERE (provider_subsidy->'amount'->>'baseUnits')::numeric > 0 OR (subsidy_amount->>'baseUnits')::numeric > 0`,
      ),
  },
  {
    name: '0207_routes_deposit_addresses_drop_api_key',
    up: (db) => raw(db, sql`ALTER TABLE routes_deposit_addresses DROP COLUMN IF EXISTS api_key_id`),
  },
  {
    name: '0208_routes_subsidies_drop_api_key_policies',
    up: (db) =>
      raw(
        db,
        sql`DO $$
          BEGIN
            IF EXISTS (
              SELECT 1
              FROM information_schema.columns
              WHERE table_schema = current_schema()
                AND table_name = 'routes_subsidies'
                AND column_name = 'api_key_id'
            ) THEN
              DELETE FROM routes_subsidies WHERE api_key_id IS NOT NULL;
            END IF;
          END $$`,
      ),
  },
  {
    name: '0209_routes_subsidies_drop_api_key_indexes',
    up: (db) =>
      raw(
        db,
        sql`DROP INDEX IF EXISTS routes_subsidies_org_unique, routes_subsidies_api_key_unique`,
      ),
  },
  {
    name: '0210_routes_subsidies_drop_api_key',
    up: (db) => raw(db, sql`ALTER TABLE routes_subsidies DROP COLUMN IF EXISTS api_key_id`),
  },
  {
    name: '0211_routes_subsidies_org_unique',
    up: (db) =>
      raw(
        db,
        sql`CREATE UNIQUE INDEX IF NOT EXISTS routes_subsidies_org_unique ON routes_subsidies (org_id)`,
      ),
  },
  {
    name: '0212_routes_transfer_subsidy_nonces',
    up: (db) =>
      db.schema
        .createTable('routesTransferSubsidyNonces')
        .ifNotExists()
        .addColumn('account', 'text', (c) => c.notNull())
        .addColumn('chainId', 'text', (c) => c.notNull())
        .addColumn('nextNonce', 'text', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('routes_transfer_subsidy_nonces_pkey', ['chainId', 'account']),
  },
  {
    name: '0213_routes_transfers_subsidy_amount',
    up: (db) => raw(db, sql`ALTER TABLE routes_transfers ADD COLUMN IF NOT EXISTS subsidy_amount jsonb`), // prettier-ignore
  },
  {
    name: '0214_routes_transfers_subsidy_meter_reported_at',
    up: (db) => raw(db, sql`ALTER TABLE routes_transfers ADD COLUMN IF NOT EXISTS subsidy_meter_reported_at text`), // prettier-ignore
  },
  {
    name: '0215_routes_transfers_subsidy_transaction_hash',
    up: (db) => raw(db, sql`ALTER TABLE routes_transfers ADD COLUMN IF NOT EXISTS subsidy_transaction_hash text`), // prettier-ignore
  },
  {
    name: '0216_routes_transfers_billable_subsidy_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS routes_transfers_billable_subsidy_idx ON routes_transfers (status_updated_at, id) WHERE environment = 'production' AND status = 'completed' AND subsidy_transaction_hash IS NOT NULL AND subsidy_amount IS NOT NULL AND (subsidy_amount->>'baseUnits')::numeric > 0 AND subsidy_meter_reported_at IS NULL`,
      ),
  },
  {
    name: '0217_routes_transfers_subsidy_account',
    up: (db) => raw(db, sql`ALTER TABLE routes_transfers ADD COLUMN IF NOT EXISTS subsidy_account text`), // prettier-ignore
  },
  {
    name: '0218_routes_transfers_subsidy_chain_id',
    up: (db) => raw(db, sql`ALTER TABLE routes_transfers ADD COLUMN IF NOT EXISTS subsidy_chain_id text`), // prettier-ignore
  },
  {
    name: '0219_routes_transfers_subsidy_native_amount',
    up: (db) => raw(db, sql`ALTER TABLE routes_transfers ADD COLUMN IF NOT EXISTS subsidy_native_amount text`), // prettier-ignore
  },
  {
    name: '0220_routes_transfers_subsidy_token_address',
    up: (db) => raw(db, sql`ALTER TABLE routes_transfers ADD COLUMN IF NOT EXISTS subsidy_token_address text`), // prettier-ignore
  },
  {
    name: '0221_routes_transfers_subsidy_reservation_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS routes_transfers_subsidy_reservation_idx ON routes_transfers (subsidy_chain_id, subsidy_account, subsidy_token_address) WHERE status IN ('processing', 'action-required') AND subsidy_amount IS NOT NULL AND (subsidy_amount->>'baseUnits')::numeric > 0`,
      ),
  },
  {
    name: '0222_routes_transfers_subsidy_commitment_amount',
    up: (db) => raw(db, sql`ALTER TABLE routes_transfers ADD COLUMN IF NOT EXISTS subsidy_commitment_amount jsonb`), // prettier-ignore
  },
  {
    name: '0223_routes_transfers_provider_delivered_at',
    up: (db) => raw(db, sql`ALTER TABLE routes_transfers ADD COLUMN IF NOT EXISTS provider_delivered_at text`), // prettier-ignore
  },
  {
    name: '0224_routes_transfer_subsidy_nonces_blocked_at',
    up: (db) => raw(db, sql`ALTER TABLE routes_transfer_subsidy_nonces ADD COLUMN IF NOT EXISTS blocked_at text`), // prettier-ignore
  },
  {
    name: '0225_routes_transfer_subsidy_nonces_blocked_transfer_id',
    up: (db) => raw(db, sql`ALTER TABLE routes_transfer_subsidy_nonces ADD COLUMN IF NOT EXISTS blocked_transfer_id text`), // prettier-ignore
  },
  {
    name: '0226_routes_transfer_subsidy_nonces_blocked_reason',
    up: (db) => raw(db, sql`ALTER TABLE routes_transfer_subsidy_nonces ADD COLUMN IF NOT EXISTS blocked_reason text`), // prettier-ignore
  },
  {
    name: '0227_routes_transfers_subsidy_commitment_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS routes_transfers_subsidy_commitment_idx ON routes_transfers (subsidy_chain_id, subsidy_account, subsidy_token_address) WHERE status IN ('awaiting-source', 'processing', 'action-required', 'refunding') AND subsidy_commitment_amount IS NOT NULL AND (subsidy_commitment_amount->>'baseUnits')::numeric > 0`,
      ),
  },
  {
    name: '0228_routes_transfer_subsidy_nonces_block_check',
    up: (db) =>
      raw(
        db,
        sql`DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'routes_transfer_subsidy_nonces_block_check' AND conrelid = 'routes_transfer_subsidy_nonces'::regclass) THEN ALTER TABLE routes_transfer_subsidy_nonces ADD CONSTRAINT routes_transfer_subsidy_nonces_block_check CHECK ((blocked_at IS NULL AND blocked_transfer_id IS NULL AND blocked_reason IS NULL) OR (blocked_at IS NOT NULL AND blocked_transfer_id IS NOT NULL AND blocked_reason = 'broadcast_rejected')); END IF; END $$`,
      ),
  },
  {
    // The test harness replays idempotent DDL on new connections, so legacy
    // index migrations still need this unused physical column to exist.
    name: '0229_routes_subsidies_legacy_api_key_compatibility',
    up: (db) => raw(db, sql`ALTER TABLE routes_subsidies ADD COLUMN IF NOT EXISTS api_key_id text`), // prettier-ignore
  },
  {
    name: '0230_routes_transfers_admin_subsidy_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS routes_transfers_admin_subsidy_idx ON routes_transfers (updated_at DESC, id DESC) WHERE subsidy_amount IS NOT NULL AND (subsidy_amount->>'baseUnits')::numeric > 0`,
      ),
  },
  {
    name: '0231_routes_transfers_expired_subsidy_commitments_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS routes_transfers_expired_subsidy_commitments_idx ON routes_transfers (quote_expires_at, id) WHERE status = 'awaiting-source' AND subsidy_commitment_amount IS NOT NULL AND (subsidy_commitment_amount->>'baseUnits')::numeric > 0`,
      ),
  },
  {
    name: '0232_sponsorship_reconciliation_cursors',
    up: (db) =>
      db.schema
        .createTable('sponsorshipReconciliationCursors')
        .ifNotExists()
        .addColumn('blockNumber', 'text', (c) => c.notNull())
        .addColumn('chainId', 'integer', (c) => c.notNull())
        .addColumn('feePayer', 'text', (c) => c.notNull())
        .addColumn('transactionIndex', 'integer', (c) => c.notNull())
        .addColumn('updatedAt', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('sponsorship_reconciliation_cursors_pkey', [
          'chainId',
          'feePayer',
        ]),
  },
  {
    name: '0233_sponsored_transactions_recovery_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS sponsored_transactions_recovery_idx ON sponsored_transactions (finalized_at, chain_id, created_at) WHERE status = 'failed' AND transaction_hash IS NULL`,
      ),
  },
  {
    name: '0234_sponsorship_reconciliation_progress',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE sponsorship_reconciliation_cursors ADD COLUMN IF NOT EXISTS complete boolean NOT NULL DEFAULT false, ADD COLUMN IF NOT EXISTS retry_count integer NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS retry_key text, ADD COLUMN IF NOT EXISTS revision text NOT NULL DEFAULT ''`,
      ),
  },
  {
    name: '0235_routes_deposits_unattributed_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS routes_deposits_unattributed_idx ON routes_deposits (deposit_address_id, created_at, id) WHERE provider_request_id IS NULL`,
      ),
  },
  {
    name: '0236_routes_transfer_subsidies',
    up: (db) =>
      db.schema
        .createTable('routesTransferSubsidies')
        .ifNotExists()
        .addColumn('sourceChainId', 'text', (c) => c.notNull())
        .addColumn('transactionRef', 'text', (c) => c.notNull())
        .addColumn('destinationChainId', 'text', (c) => c.notNull())
        .addColumn('recipient', 'text', (c) => c.notNull())
        .addColumn('tokenAddress', 'text', (c) => c.notNull())
        .addColumn('transaction', 'jsonb', (c) => c.notNull())
        .addColumn('transferId', 'text', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('routes_transfer_subsidies_pkey', [
          'sourceChainId',
          'transactionRef',
        ]),
  },
  {
    name: '0237_routes_transfer_subsidies_capture',
    // Capture legacy Worker writes during rollout and retain settlement evidence after transfer deletion.
    up: (db) =>
      raw(
        db,
        sql`DO $$
        BEGIN
          LOCK TABLE routes_transfers IN SHARE ROW EXCLUSIVE MODE;
          INSERT INTO routes_transfer_subsidies (
            source_chain_id, transaction_ref, destination_chain_id, recipient,
            token_address, transaction, transfer_id, created_at
          )
          SELECT snapshot->'sourceChain'->>'id', snapshot->'sourceTransactionHashes'->>0,
            subsidy_chain_id, lower(snapshot->>'recipient'), subsidy_token_address,
            provider_state->'transferSubsidy', id, updated_at
          FROM routes_transfers
          WHERE subsidy_transaction_hash IS NOT NULL
            AND provider_state->'transferSubsidy' IS NOT NULL
            AND snapshot->'sourceTransactionHashes'->>0 IS NOT NULL
          ON CONFLICT DO NOTHING;

          CREATE OR REPLACE FUNCTION capture_routes_transfer_subsidy() RETURNS trigger
          LANGUAGE plpgsql SET search_path FROM CURRENT AS $capture$
          DECLARE
            settlement routes_transfer_subsidies%ROWTYPE;
            source_chain text;
            source_ref text;
          BEGIN
            IF NEW.subsidy_transaction_hash IS NULL OR NEW.provider_state->'transferSubsidy' IS NULL THEN
              RETURN NEW;
            END IF;
            source_chain := NEW.snapshot->'sourceChain'->>'id';
            source_ref := NEW.snapshot->'sourceTransactionHashes'->>0;
            IF source_ref IS NULL THEN
              RAISE EXCEPTION 'Subsidy source evidence is missing';
            END IF;
            INSERT INTO routes_transfer_subsidies (
              source_chain_id, transaction_ref, destination_chain_id, recipient,
              token_address, transaction, transfer_id, created_at
            ) VALUES (
              source_chain, source_ref, NEW.subsidy_chain_id, lower(NEW.snapshot->>'recipient'),
              NEW.subsidy_token_address, NEW.provider_state->'transferSubsidy', NEW.id, NEW.updated_at
            ) ON CONFLICT DO NOTHING;
            SELECT * INTO STRICT settlement FROM routes_transfer_subsidies
              WHERE source_chain_id = source_chain AND transaction_ref = source_ref;
            IF settlement.transaction IS DISTINCT FROM NEW.provider_state->'transferSubsidy'
              OR settlement.destination_chain_id IS DISTINCT FROM NEW.subsidy_chain_id
              OR settlement.recipient IS DISTINCT FROM lower(NEW.snapshot->>'recipient')
              OR settlement.token_address IS DISTINCT FROM NEW.subsidy_token_address THEN
              RAISE EXCEPTION 'Source payment already has a different subsidy settlement';
            END IF;
            IF settlement.transfer_id <> NEW.id THEN
              NEW.subsidy_amount := NULL;
              NEW.subsidy_commitment_amount := NULL;
              NEW.subsidy_native_amount := NULL;
            END IF;
            RETURN NEW;
          END $capture$;
          DROP TRIGGER IF EXISTS routes_transfer_subsidy_capture ON routes_transfers;
          CREATE TRIGGER routes_transfer_subsidy_capture
            BEFORE INSERT OR UPDATE OF provider_state, subsidy_transaction_hash, subsidy_amount
            ON routes_transfers FOR EACH ROW EXECUTE FUNCTION capture_routes_transfer_subsidy();
        END $$`,
      ),
  },
  {
    name: '0238_routes_transfer_source_claim_scope',
    // Derive tenancy for old and new writers before replacing global source uniqueness.
    up: (db) =>
      raw(
        db,
        sql`DO $$
        BEGIN
          ALTER TABLE routes_transfer_transactions
            ADD COLUMN IF NOT EXISTS org_id text,
            ADD COLUMN IF NOT EXISTS environment text,
            ADD COLUMN IF NOT EXISTS project_id text;
          UPDATE routes_transfer_transactions AS evidence
            SET org_id = owner.org_id, environment = owner.environment, project_id = owner.project_id
            FROM routes_transfers AS owner WHERE owner.id = evidence.transfer_id;
          ALTER TABLE routes_transfer_transactions
            ALTER COLUMN org_id SET NOT NULL, ALTER COLUMN environment SET NOT NULL;
          CREATE OR REPLACE FUNCTION scope_routes_transfer_transaction() RETURNS trigger
          LANGUAGE plpgsql SET search_path FROM CURRENT AS $scope$
          BEGIN
            SELECT org_id, environment, project_id INTO STRICT NEW.org_id, NEW.environment, NEW.project_id
              FROM routes_transfers WHERE id = NEW.transfer_id;
            RETURN NEW;
          END $scope$;
          DROP TRIGGER IF EXISTS routes_transfer_transaction_scope ON routes_transfer_transactions;
          CREATE TRIGGER routes_transfer_transaction_scope
            BEFORE INSERT OR UPDATE ON routes_transfer_transactions
            FOR EACH ROW EXECUTE FUNCTION scope_routes_transfer_transaction();
          CREATE UNIQUE INDEX IF NOT EXISTS routes_transfer_transactions_source_owner_unique
            ON routes_transfer_transactions (org_id, environment, project_id, chain_id, transaction_ref)
            NULLS NOT DISTINCT WHERE role = 'source';
          DROP INDEX IF EXISTS routes_transfer_transactions_source_unique;
        END $$`,
      ),
  },
  {
    name: '0239_routes_transfer_source_lookup_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS routes_transfer_transactions_source_lookup_idx ON routes_transfer_transactions (chain_id, transaction_ref) WHERE role = 'source'`,
      ),
  },
  {
    name: '0240_sponsored_transactions_finalization_attempted_at',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE sponsored_transactions ADD COLUMN IF NOT EXISTS finalization_attempted_at text`,
      ),
  },
  {
    name: '0241_sponsored_transactions_finalization_queue_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS sponsored_transactions_finalization_queue_idx ON sponsored_transactions (finalization_attempted_at NULLS FIRST, created_at, id) WHERE status = 'pending'`,
      ),
  },
  {
    name: '0242_api_key_owner_tombstones',
    up: (db) =>
      db.schema
        .createTable('api_key_owner_tombstones')
        .ifNotExists()
        .addColumn('id', 'text', (column) => column.primaryKey())
        .addColumn('createdAt', 'text', (column) => column.notNull()),
  },
  {
    name: '0243_api_key_owner_admissions',
    up: (db) =>
      db.schema
        .createTable('api_key_owner_admissions')
        .ifNotExists()
        .addColumn('orgId', 'text', (column) => column.primaryKey())
        .addColumn('liveKeys', 'integer', (column) => column.notNull())
        .addColumn('updatedAt', 'text', (column) => column.notNull()),
  },
  {
    name: '0244_api_key_admissions',
    up: (db) =>
      db.schema
        .createTable('api_key_admissions')
        .ifNotExists()
        .addColumn('expiresAt', 'text')
        .addColumn('id', 'text', (column) => column.notNull().unique())
        .addColumn('orgId', 'text', (column) => column.notNull())
        .addColumn('projectId', 'text')
        .addPrimaryKeyConstraint('api_key_admissions_pkey', ['orgId', 'id']),
  },
  {
    name: '0245_api_key_admission_bootstraps',
    up: (db) =>
      db.schema
        .createTable('api_key_admission_bootstraps')
        .ifNotExists()
        .addColumn('orgId', 'text', (column) => column.primaryKey())
        .addColumn('bootstrappedAt', 'text', (column) => column.notNull()),
  },
  {
    name: '0246_api_key_revocations',
    up: (db) =>
      db.schema
        .createTable('api_key_revocations')
        .ifNotExists()
        .addColumn('id', 'text', (column) => column.primaryKey())
        .addColumn('revokedAt', 'text', (column) => column.notNull()),
  },
  {
    name: '0247_api_key_revocation_lifecycle',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE api_key_revocations ADD COLUMN IF NOT EXISTS org_id text REFERENCES organizations(id) ON DELETE CASCADE, ADD COLUMN IF NOT EXISTS expires_at text`,
      ),
  },
  {
    name: '0248_api_key_revocation_expiry_backfill',
    up: (db) =>
      raw(
        db,
        sql`UPDATE api_key_revocations SET expires_at = to_char(revoked_at::timestamptz + interval '7 days', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') WHERE expires_at IS NULL`,
      ),
  },
  {
    name: '0249_api_key_revocation_expiry_required',
    up: (db) => raw(db, sql`ALTER TABLE api_key_revocations ALTER COLUMN expires_at SET NOT NULL`),
  },
  {
    name: '0250_api_key_revocation_expiry_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS api_key_revocations_expiry_idx ON api_key_revocations (expires_at, id)`,
      ),
  },
  {
    name: '0251_api_key_revocation_org_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS api_key_revocations_org_idx ON api_key_revocations (org_id) WHERE org_id IS NOT NULL`,
      ),
  },
  {
    // Triggers make migrate-before-deploy safe: older Workers still fence raw owner deletes and acquire the same locks as new key writes.
    name: '0252_api_key_owner_delete_trigger_function',
    up: (db) =>
      raw(
        db,
        sql`CREATE OR REPLACE FUNCTION api_key_fence_deleted_owner() RETURNS trigger LANGUAGE plpgsql AS $api_key_fence$
          DECLARE owner_key text;
          BEGIN
            IF TG_TABLE_NAME = 'organizations' THEN
              owner_key := 'organization:' || OLD.id;
            ELSE
              PERFORM pg_advisory_xact_lock(hashtextextended('organization:' || OLD.org_id, 0));
              owner_key := 'project:' || OLD.org_id || ':' || OLD.id;
            END IF;
            PERFORM pg_advisory_xact_lock(hashtextextended(owner_key, 0));
            EXECUTE format('INSERT INTO %I.api_key_owner_tombstones (id, created_at) VALUES ($1, $2) ON CONFLICT (id) DO NOTHING', TG_TABLE_SCHEMA)
              USING owner_key, to_char(clock_timestamp() AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"');
            RETURN OLD;
          END
        $api_key_fence$`,
      ),
  },
  {
    name: '0253_api_key_organization_delete_trigger',
    up: (db) =>
      raw(
        db,
        sql`DO $api_key_trigger$
          BEGIN
            IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'api_key_fence_deleted_organization' AND tgrelid = 'organizations'::regclass) THEN
              CREATE TRIGGER api_key_fence_deleted_organization BEFORE DELETE ON organizations FOR EACH ROW EXECUTE FUNCTION api_key_fence_deleted_owner();
            END IF;
          END
        $api_key_trigger$`,
      ),
  },
  {
    name: '0254_api_key_project_delete_trigger',
    up: (db) =>
      raw(
        db,
        sql`DO $api_key_trigger$
          BEGIN
            IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'api_key_fence_deleted_project' AND tgrelid = 'projects'::regclass) THEN
              CREATE TRIGGER api_key_fence_deleted_project BEFORE DELETE ON projects FOR EACH ROW EXECUTE FUNCTION api_key_fence_deleted_owner();
            END IF;
          END
        $api_key_trigger$`,
      ),
  },
  {
    // The table lock partitions legacy writers; a 24-hour organization-deletion embargo lets pre-fence Worker requests finish before owners can disappear.
    name: '0255_routes_organization_delete_fence',
    up: (db) =>
      raw(
        db,
        sql`DO $migration$ BEGIN LOCK TABLE routes_idempotency_requests IN SHARE ROW EXCLUSIVE MODE; EXECUTE $definition$ CREATE OR REPLACE FUNCTION api_key_fence_deleted_owner() RETURNS trigger LANGUAGE plpgsql AS $body$ DECLARE owner_key text; BEGIN IF TG_TABLE_NAME = 'organizations' THEN PERFORM pg_advisory_xact_lock(hashtextextended('routes-subsidy:' || OLD.id, 0)); PERFORM pg_advisory_xact_lock(hashtextextended('routes-ownerless-legacy', 0)); owner_key := 'organization:' || OLD.id; ELSE PERFORM pg_advisory_xact_lock(hashtextextended('organization:' || OLD.org_id, 0)); owner_key := 'project:' || OLD.org_id || ':' || OLD.id; END IF; PERFORM pg_advisory_xact_lock(hashtextextended(owner_key, 0)); EXECUTE format('INSERT INTO %I.api_key_owner_tombstones (id, created_at) VALUES ($1, $2) ON CONFLICT (id) DO NOTHING', TG_TABLE_SCHEMA) USING owner_key, to_char(clock_timestamp() AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'); IF TG_TABLE_NAME = 'organizations' THEN EXECUTE format('INSERT INTO %I.api_key_owner_tombstones (id, created_at) VALUES ($1, $2) ON CONFLICT (id) DO NOTHING', TG_TABLE_SCHEMA) USING 'routes-ownerless-legacy-cutoff', to_char(clock_timestamp() AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'); END IF; RETURN OLD; END $body$ $definition$; EXECUTE $definition$ CREATE OR REPLACE FUNCTION fence_routes_organization_delete() RETURNS trigger LANGUAGE plpgsql SET search_path FROM CURRENT AS $body$ BEGIN PERFORM pg_advisory_xact_lock(hashtextextended('routes-ownerless-legacy', 0)); IF EXISTS (SELECT 1 FROM api_key_owner_tombstones WHERE id = 'routes-legacy-attempt-drain' AND created_at::timestamptz + interval '24 hours' > CURRENT_TIMESTAMP) THEN RAISE EXCEPTION 'route lifecycle rollout drain is active' USING ERRCODE = '23503'; END IF; IF EXISTS (SELECT 1 FROM routes_deposit_addresses WHERE org_id = OLD.id AND (status != 'deactivated' OR (provider_id = 'relay' AND subsidize))) OR EXISTS (SELECT 1 FROM routes_idempotency_requests AS request WHERE request.status IN ('pending', 'provisioned') AND (request.expires_at::timestamptz > CURRENT_TIMESTAMP OR COALESCE((to_jsonb(request)->>'irrevocable')::boolean, false)) AND (((to_jsonb(request)->>'org_id') = OLD.id AND (to_jsonb(request)->>'operation') = 'deposit_address') OR to_jsonb(request)->>'org_id' IS NULL OR to_jsonb(request)->>'operation' IS NULL)) THEN RAISE EXCEPTION 'organization has active route deposit address work' USING ERRCODE = '23503'; END IF; RETURN OLD; END $body$ $definition$; EXECUTE $definition$ CREATE OR REPLACE FUNCTION fence_routes_deposit_address_owner() RETURNS trigger LANGUAGE plpgsql SET search_path FROM CURRENT AS $body$ BEGIN PERFORM pg_advisory_xact_lock(hashtextextended('routes-subsidy:' || NEW.org_id, 0)); IF EXISTS (SELECT 1 FROM api_key_owner_tombstones WHERE id = 'organization:' || NEW.org_id) THEN RAISE EXCEPTION 'route deposit address owner was deleted' USING ERRCODE = '23503'; END IF; RETURN NEW; END $body$ $definition$; EXECUTE $definition$ CREATE OR REPLACE FUNCTION fence_routes_idempotency_owner() RETURNS trigger LANGUAGE plpgsql SET search_path FROM CURRENT AS $body$ DECLARE owner_id text; BEGIN owner_id := to_jsonb(NEW)->>'org_id'; IF owner_id IS NULL THEN SELECT org_id INTO owner_id FROM api_key_admissions WHERE id = NEW.api_key_id; END IF; IF owner_id IS NULL THEN PERFORM pg_advisory_xact_lock(hashtextextended('routes-ownerless-legacy', 0)); IF EXISTS (SELECT 1 FROM api_key_owner_tombstones WHERE id = 'routes-ownerless-legacy-cutoff') THEN RAISE EXCEPTION 'ownerless route idempotency creation is disabled' USING ERRCODE = '23503'; END IF; RETURN NEW; END IF; PERFORM pg_advisory_xact_lock(hashtextextended('routes-subsidy:' || owner_id, 0)); IF EXISTS (SELECT 1 FROM api_key_owner_tombstones WHERE id = 'organization:' || owner_id) THEN RAISE EXCEPTION 'route idempotency owner was deleted' USING ERRCODE = '23503'; END IF; RETURN NEW; END $body$ $definition$; EXECUTE 'CREATE OR REPLACE TRIGGER routes_deposit_address_owner_fence BEFORE INSERT OR UPDATE OF org_id ON routes_deposit_addresses FOR EACH ROW EXECUTE FUNCTION fence_routes_deposit_address_owner()'; EXECUTE 'CREATE OR REPLACE TRIGGER routes_idempotency_owner_fence BEFORE INSERT OR UPDATE ON routes_idempotency_requests FOR EACH ROW EXECUTE FUNCTION fence_routes_idempotency_owner()'; EXECUTE 'CREATE OR REPLACE TRIGGER routes_organization_delete_fence BEFORE DELETE ON organizations FOR EACH ROW EXECUTE FUNCTION fence_routes_organization_delete()'; INSERT INTO api_key_owner_tombstones (id, created_at) VALUES ('routes-legacy-attempt-drain', to_char(clock_timestamp() AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')) ON CONFLICT (id) DO NOTHING; END $migration$`,
      ),
  },
  {
    name: '0256_routes_idempotency_organization',
    up: (db) =>
      raw(db, sql`ALTER TABLE routes_idempotency_requests ADD COLUMN IF NOT EXISTS org_id text`),
  },
  {
    name: '0257_routes_idempotency_operation',
    up: (db) =>
      raw(db, sql`ALTER TABLE routes_idempotency_requests ADD COLUMN IF NOT EXISTS operation text`),
  },
  {
    name: '0258_routes_idempotency_irrevocable',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE routes_idempotency_requests ADD COLUMN IF NOT EXISTS irrevocable boolean NOT NULL DEFAULT false`,
      ),
  },
  {
    name: '0259_routes_idempotency_organization_inflight_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS routes_idempotency_requests_organization_inflight_idx ON routes_idempotency_requests (org_id) WHERE org_id IS NOT NULL AND operation = 'deposit_address' AND status IN ('pending', 'provisioned')`,
      ),
  },
  {
    name: '0260_routes_idempotency_match_hash',
    up: (db) =>
      raw(
        db,
        sql`ALTER TABLE routes_idempotency_requests ADD COLUMN IF NOT EXISTS match_hash text`,
      ),
  },
  {
    name: '0261_routes_idempotency_match_inflight_index',
    up: (db) =>
      raw(
        db,
        sql`CREATE INDEX IF NOT EXISTS routes_idempotency_requests_match_inflight_idx ON routes_idempotency_requests (org_id, match_hash) WHERE match_hash IS NOT NULL AND operation = 'deposit_address' AND status IN ('pending', 'provisioned')`,
      ),
  },
  {
    name: '0262_routes_organization_delete_fence_unattributed_checkpoint',
    up: (db) =>
      raw(
        db,
        sql`CREATE OR REPLACE FUNCTION fence_routes_organization_delete() RETURNS trigger LANGUAGE plpgsql SET search_path FROM CURRENT AS $body$ BEGIN PERFORM pg_advisory_xact_lock(hashtextextended('routes-ownerless-legacy', 0)); IF EXISTS (SELECT 1 FROM api_key_owner_tombstones WHERE id = 'routes-legacy-attempt-drain' AND created_at::timestamptz + interval '24 hours' > CURRENT_TIMESTAMP) THEN RAISE EXCEPTION 'route lifecycle rollout drain is active' USING ERRCODE = '23503'; END IF; IF EXISTS (SELECT 1 FROM routes_deposit_addresses WHERE org_id = OLD.id AND (status != 'deactivated' OR (provider_id = 'relay' AND subsidize))) OR EXISTS (SELECT 1 FROM routes_idempotency_requests AS request WHERE request.status IN ('pending', 'provisioned') AND (request.expires_at::timestamptz > CURRENT_TIMESTAMP OR COALESCE((to_jsonb(request)->>'irrevocable')::boolean, false)) AND (((to_jsonb(request)->>'org_id') = OLD.id AND (to_jsonb(request)->>'operation') = 'deposit_address') OR to_jsonb(request)->>'org_id' IS NULL OR to_jsonb(request)->>'operation' IS NULL)) THEN RAISE EXCEPTION 'organization has active route deposit address work' USING ERRCODE = '23503'; END IF; RETURN OLD; END $body$`,
      ),
  },
  {
    // Rolling workers may insert after preflight; these triggers share the deletion lock and reject owners deleted while provisioning.
    name: '0263_routes_creation_owner_fences',
    up: (db) =>
      raw(
        db,
        sql`DO $migration$ BEGIN EXECUTE $definition$ CREATE OR REPLACE FUNCTION fence_routes_idempotency_owner() RETURNS trigger LANGUAGE plpgsql SET search_path FROM CURRENT AS $body$ DECLARE owner_id text; BEGIN owner_id := NEW.org_id; IF owner_id IS NULL THEN SELECT org_id INTO owner_id FROM api_key_admissions WHERE id = NEW.api_key_id; END IF; IF owner_id IS NULL THEN PERFORM pg_advisory_xact_lock(hashtextextended('routes-ownerless-legacy', 0)); IF EXISTS (SELECT 1 FROM api_key_owner_tombstones WHERE id = 'routes-ownerless-legacy-cutoff') THEN RAISE EXCEPTION 'ownerless route idempotency creation is disabled' USING ERRCODE = '23503'; END IF; RETURN NEW; END IF; PERFORM pg_advisory_xact_lock(hashtextextended('routes-subsidy:' || owner_id, 0)); IF EXISTS (SELECT 1 FROM api_key_owner_tombstones WHERE id = 'organization:' || owner_id) THEN RAISE EXCEPTION 'route idempotency owner was deleted' USING ERRCODE = '23503'; END IF; RETURN NEW; END $body$ $definition$; EXECUTE 'CREATE OR REPLACE TRIGGER routes_idempotency_owner_fence BEFORE INSERT OR UPDATE OF org_id, status ON routes_idempotency_requests FOR EACH ROW EXECUTE FUNCTION fence_routes_idempotency_owner()'; END $migration$`,
      ),
  },
  {
    // Existing unattributed checkpoints get one bounded drain window because their subsidy intent cannot be reconstructed.
    name: '0264_routes_legacy_checkpoint_drain',
    up: (db) =>
      raw(
        db,
        sql`WITH marker AS (INSERT INTO api_key_owner_tombstones (id, created_at) VALUES ('routes-legacy-checkpoint-drain', to_char(clock_timestamp() AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')) ON CONFLICT (id) DO UPDATE SET created_at = api_key_owner_tombstones.created_at RETURNING created_at) UPDATE routes_idempotency_requests AS request SET expires_at = to_char(marker.created_at::timestamptz + interval '24 hours', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') FROM marker WHERE request.status = 'provisioned' AND (request.org_id IS NULL OR request.operation IS NULL) AND request.expires_at::timestamptz < marker.created_at::timestamptz + interval '24 hours'`,
      ),
  },
  {
    name: '0265_routes_deposit_address_creator',
    up: (db) =>
      raw(
        db,
        sql`DO $migration$ BEGIN ALTER TABLE routes_deposit_addresses ADD COLUMN IF NOT EXISTS creator_user_id text; UPDATE routes_deposit_addresses AS address SET creator_user_id = organization.user_id FROM organizations AS organization WHERE address.org_id = organization.id AND address.creator_user_id IS NULL; CREATE INDEX IF NOT EXISTS routes_deposit_addresses_creator_user_idx ON routes_deposit_addresses (creator_user_id) WHERE creator_user_id IS NOT NULL; EXECUTE $definition$ CREATE OR REPLACE FUNCTION set_routes_deposit_address_creator() RETURNS trigger LANGUAGE plpgsql SET search_path FROM CURRENT AS $body$ BEGIN NEW.creator_user_id := (SELECT user_id FROM organizations WHERE id = NEW.org_id); RETURN NEW; END $body$ $definition$; EXECUTE 'CREATE OR REPLACE TRIGGER routes_deposit_address_creator BEFORE INSERT OR UPDATE OF org_id ON routes_deposit_addresses FOR EACH ROW EXECUTE FUNCTION set_routes_deposit_address_creator()'; END $migration$`,
      ),
  },
  {
    name: '0266_reward_credits',
    up: (db) =>
      db.schema
        .createTable('rewardCredits')
        .ifNotExists()
        .addColumn('chainId', 'bigint', (c) => c.notNull())
        .addColumn('vaultAddress', 'text', (c) => c.notNull())
        .addColumn('recipient', 'text', (c) => c.notNull())
        .addColumn('reference', 'text', (c) => c.notNull())
        .addColumn('assets', 'numeric', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('reward_credits_pkey', [
          'chainId',
          'vaultAddress',
          'recipient',
          'reference',
        ])
        // Runs rewrite reward_accounts wholesale, so the ledger must not cascade from it.
        .addForeignKeyConstraint(
          'reward_credits_campaign_fkey',
          ['chainId', 'vaultAddress'],
          'rewardCampaigns',
          ['chainId', 'vaultAddress'],
          (c) => c.onDelete('cascade'),
        ),
  },
  {
    name: '0267_reward_campaign_distributors',
    up: (db) =>
      db.schema
        .createTable('rewardCampaignDistributors')
        .ifNotExists()
        .addColumn('chainId', 'bigint', (c) => c.notNull())
        .addColumn('vaultAddress', 'text', (c) => c.notNull())
        .addColumn('distributorAddress', 'text', (c) => c.notNull())
        .addColumn('createdAt', 'text', (c) => c.notNull())
        .addPrimaryKeyConstraint('reward_campaign_distributors_pkey', [
          'chainId',
          'vaultAddress',
          'distributorAddress',
        ])
        .addForeignKeyConstraint(
          'reward_campaign_distributors_campaign_fkey',
          ['chainId', 'vaultAddress'],
          'rewardCampaigns',
          ['chainId', 'vaultAddress'],
          (c) => c.onDelete('cascade'),
        ),
  },
  {
    name: '0268_reward_campaign_distributors_backfill',
    up: (db) =>
      raw(
        db,
        sql`INSERT INTO reward_campaign_distributors (chain_id, vault_address, distributor_address, created_at) SELECT chain_id, vault_address, distributor_address, updated_at FROM reward_campaigns WHERE distributor_address IS NOT NULL ON CONFLICT DO NOTHING`,
      ),
  },
  {
    // This verified distributor paid rewards before distributor history was retained.
    name: '0269_reward_campaign_previous_distributor',
    up: (db) =>
      raw(
        db,
        sql`INSERT INTO reward_campaign_distributors (chain_id, vault_address, distributor_address, created_at) SELECT chain_id, vault_address, '0x6c857bf1fe7de1bc1d5c639c721b2633f8fe34f3', created_at FROM reward_campaigns WHERE chain_id = 4217 AND vault_address = '0xd730394f3bb85a4828e35fc5e361dcc9f894fa4f' ON CONFLICT DO NOTHING`,
      ),
  },
  {
    // Provider subsidy billing first became active with this production Worker deployment.
    name: '0270_routes_deposits_legacy_provider_subsidies_reported',
    up: (db) =>
      raw(
        db,
        sql`UPDATE routes_deposits SET subsidy_meter_reported_at = coalesce(status_updated_at, updated_at) WHERE environment = 'production' AND subsidy_meter_reported_at IS NULL AND (provider_subsidy->'amount'->>'baseUnits')::numeric > 0 AND (status = 'completed' OR (status IN ('action-required', 'settling') AND tempo_gas_paid IS NOT NULL)) AND coalesce(status_updated_at, updated_at) < '2026-09-03T06:10:21.000Z'`,
      ),
  },
]

/**
 * Adapts a raw statement to the migration contract (argument-less
 * `compile`/`execute`) for DDL the schema builders cannot express.
 */
function raw(db: Kysely<Database>, statement: RawBuilder<unknown>) {
  return {
    compile: () => statement.compile(db),
    execute: () => statement.execute(db),
  }
}

/**
 * Builds a {@link Db} for any Kysely {@link Dialect}. All backends share
 * migrations, repositories, and the required {@link CamelCasePlugin} mapping.
 *
 * @param dialect - The Kysely dialect for the target backend.
 * @returns The database.
 */
export function from(dialect: Dialect): Db {
  return fromDialect(dialect)
}

/** Builds a database with internal dialect-specific options. */
function fromDialect(dialect: Dialect, options: fromDialect.Options = {}): Db {
  const root = new Kysely<Database>({ dialect, plugins: [new CamelCasePlugin()] })
  // Qualify repository queries instead of relying on session search_path;
  // transaction poolers reset session state between statements.
  const kysely = options.schema ? root.withPlugin(new WithSchemaPlugin(options.schema)) : root
  // Builds Db over a top-level Kysely handle or transaction-scoped Kysely handle.
  function build(kysely: Kysely<Database>, transactional = false): Db {
    return {
      async close() {
        await kysely.destroy()
      },
      kysely,
      async migrate() {
        // A session advisory lock serializes the full run, including bootstrap
        // DDL and ledger inserts. Pin one connection and unlock in `finally`.
        await root.connection().execute(async (db) => {
          await sql`SELECT pg_advisory_lock(${migrateLockKey})`.execute(db)
          try {
            if (options.schema) {
              await db.executeQuery(
                CompiledQuery.raw(
                  `CREATE SCHEMA IF NOT EXISTS ${pg.escapeIdentifier(options.schema)}`,
                ),
              )
              await sql`SELECT set_config('search_path', quote_ident(${options.schema}), false)`.execute(
                db,
              )
            }
            await sql`CREATE TABLE IF NOT EXISTS migrations (name TEXT PRIMARY KEY, checksum TEXT NOT NULL, applied_at TEXT NOT NULL)`.execute(
              db,
            )
            await sql`ALTER TABLE migrations ADD COLUMN IF NOT EXISTS sql TEXT`.execute(db)
            const applied = new Map(
              (await db.selectFrom('migrations').select(['name', 'checksum', 'sql']).execute()).map(
                (row) => [row.name, row] as const,
              ),
            )
            for (const migration of migrations) {
              // Build once: compile (no I/O) for the checksum, execute to apply.
              const statement = migration.up(db)
              const compiled = statement.compile().sql
              const digest = checksum(compiled)
              const existing = applied.get(migration.name)
              if (existing !== undefined) {
                // The digest hashes kysely's rendered SQL, so a kysely upgrade
                // can flip it with unchanged sources. The stored `sql` is what
                // actually ran — diff it against the new render to tell render
                // drift from a real edit (re-baseline procedure in AGENTS.md).
                if (existing.checksum !== digest)
                  throw new Error(
                    `Migration "${migration.name}" was modified after being applied (checksum mismatch). Diff the migrations table's stored sql against the current render before re-baselining.`,
                  )
                // Backfill rows from before the column existed; the matching
                // checksum proves the current render is what was applied.
                if (existing.sql === null)
                  await db
                    .updateTable('migrations')
                    .set({ sql: compiled })
                    .where('name', '=', migration.name)
                    .execute()
                continue
              }
              await statement.execute()
              await db
                .insertInto('migrations')
                .values({
                  appliedAt: new Date().toISOString(),
                  checksum: digest,
                  name: migration.name,
                  sql: compiled,
                })
                .execute()
            }
          } finally {
            await sql`SELECT pg_advisory_unlock(${migrateLockKey})`.execute(db)
          }
        })
      },
      async transaction(fn) {
        if (transactional) throw new Error('Db.transaction does not support nesting.')
        return kysely.transaction().execute((trx) => fn(build(trx, true)))
      },
    }
  }
  return build(kysely)
}

declare namespace fromDialect {
  /** Internal database construction options. */
  type Options = {
    /** Schema that Kysely qualifies on every repository query. */
    schema?: string | undefined
  }
}

/**
 * Creates a Postgres-backed {@link Db} via Kysely's `PostgresDialect`. Optional
 * `schema` qualifies repository queries and scopes migrations.
 *
 * @param options - Postgres options.
 * @returns The database.
 */
export function postgres(options: postgres.Options): Db {
  const { connectionString, maxConnections, schema } = options
  // Bounded connect and pool-checkout wait: the pg default (0) waits forever,
  // so an unresponsive origin parks callers and their invocation memory
  // invisibly instead of failing into the retry paths.
  const pool = new pg.Pool({
    connectionString,
    connectionTimeoutMillis: 30_000,
    ...(maxConnections === undefined && !schema ? {} : { max: maxConnections ?? 1 }),
  })
  return fromDialect(new PostgresDialect({ pool }), { schema })
}

export declare namespace postgres {
  /** Options for {@link postgres}. */
  type Options = {
    /** Postgres connection string (`postgresql://…`). */
    connectionString: string
    /** Maximum connections held by this local pool; defaults to `1` with schema scoping. */
    maxConnections?: number | undefined
    /** Schema to qualify and create during migration (isolates concurrent tests). */
    schema?: string | undefined
  }
}

/**
 * Fixed key for the {@link Db.migrate} Postgres session advisory lock: the
 * ASCII bytes of `"tapi"`, making all migrators serialize together.
 */
const migrateLockKey = 0x74617069

/**
 * Deterministic FNV-1a hash of migration SQL, so an applied migration whose
 * source later changes is rejected (instead of silently diverging). Not
 * cryptographic — only needs collision resistance for typos.
 */
function checksum(sql: string): string {
  let hash = 0x811c9dc5
  for (let i = 0; i < sql.length; i++) {
    hash ^= sql.charCodeAt(i)
    hash = Math.imul(hash, 0x01000193)
  }
  return (hash >>> 0).toString(16).padStart(8, '0')
}