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 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 FundingCatalog from './tables/fundingCatalog.js'
import type * as FundingDepositAddresses from './tables/fundingDepositAddresses.js'
import type * as FundingDepositRequestObservations from './tables/fundingDepositRequestObservations.js'
import type * as FundingDeposits from './tables/fundingDeposits.js'
import type * as FundingIdempotency from './tables/fundingIdempotency.js'
import type * as FundingTransferEvents from './tables/fundingTransferEvents.js'
import type * as FundingTransfers from './tables/fundingTransfers.js'
import type * as FundingTransferTransactions from './tables/fundingTransferTransactions.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 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
  billing_settings: BillingSettings.Table
  early_access: EarlyAccess.Table
  earn_vaults: EarnVaults.Table
  enabled_billing_sources: EnabledBillingSources.Table
  funding_catalogs: FundingCatalog.CatalogTable
  funding_chain_tokens: FundingCatalog.ChainTokenTable
  funding_chains: FundingCatalog.ChainTable
  funding_deposit_addresses: FundingDepositAddresses.Table
  funding_deposit_request_observations: FundingDepositRequestObservations.Table
  funding_deposits: FundingDeposits.Table
  funding_idempotency_requests: FundingIdempotency.Table
  funding_routes: FundingCatalog.RouteTable
  funding_tokens: FundingCatalog.TokenTable
  funding_transfer_events: FundingTransferEvents.Table
  funding_transfer_transactions: FundingTransferTransactions.Table
  funding_transfers: FundingTransfers.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
  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`,
      ),
  },
]

/**
 * 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>): Db {
    // Prevents same-handle nested transactions from deadlocking capped pools.
    // Nested work must use the callback's scoped `tx`.
    let active = false
    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 (active) throw new Error('Db.transaction does not support nesting.')
        active = true
        try {
          return await kysely.transaction().execute((trx) => fn(build(trx)))
        } finally {
          active = false
        }
      },
    }
  }
  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, scopes migrations, and caps the pool.
 *
 * @param options - Postgres options.
 * @returns The database.
 */
export function postgres(options: postgres.Options): Db {
  const { connectionString, 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,
    ...(schema ? { max: 1 } : {}),
  })
  return fromDialect(new PostgresDialect({ pool }), { schema })
}

export declare namespace postgres {
  /** Options for {@link postgres}. */
  type Options = {
    /** Postgres connection string (`postgresql://…`). */
    connectionString: string
    /** 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')
}