Skip to content

armgit / orca

Public Imported from GitHub
0

Orca is the place for working with a fleet of agents (e.g. Grok Bot, Claude Code). Run any coding agent with your own subscription. Available on desktop, mobile and remote runtime.

13.6 KB
Raw Blame
import pg from 'pg'
import { afterAll, beforeEach, describe, expect, it } from 'vitest'
import {
  AUDIT_EVENT_RETENTION_MS,
  CONFIRM_RESULT_RETENTION_MS,
  RelayCredentialStore,
  type RelayIdentity
} from './credential-store.js'
import { openRelayDatabase, type RelayDatabase } from './database.js'

// Real planner/ctid coverage: 23 cells’ 30s invite sweeps once scanned the whole table; partial indexes must remove those scans.
const databaseUrl = process.env.ORCA_RELAY_TEST_POSTGRES_URL
const describePostgres = databaseUrl ? describe : describe.skip
const schema = 'relay_credential_sweep_test'

const identity: RelayIdentity = { userId: 'user-1', relayHostId: 'abcdefghijklmnop' }
const DAY_MS = 24 * 60 * 60 * 1000
const NOW = 100 * DAY_MS

function scopedUrl(): string {
  const url = new URL(databaseUrl!)
  url.searchParams.set('options', `-c search_path=${schema}`)
  return url.toString()
}

async function onAdmin<T>(operation: (client: pg.Client) => Promise<T>): Promise<T> {
  const client = new pg.Client({ connectionString: databaseUrl })
  await client.connect()
  try {
    return await operation(client)
  } finally {
    await client.end()
  }
}

describePostgres('credential cleanup against PostgreSQL', () => {
  let database: RelayDatabase
  let store: RelayCredentialStore
  const opened: RelayDatabase[] = []

  beforeEach(async () => {
    await onAdmin(async (client) => {
      await client.query(`DROP SCHEMA IF EXISTS ${schema} CASCADE`)
      await client.query(`CREATE SCHEMA ${schema}`)
    })
    database = await openRelayDatabase({ databaseUrl: scopedUrl(), dataDir: '' })
    opened.push(database)
    store = new RelayCredentialStore(database, () => NOW)
  })

  afterAll(async () => {
    await Promise.all(opened.map((open) => open.close().catch(() => undefined)))
    await onAdmin((client) => client.query(`DROP SCHEMA IF EXISTS ${schema} CASCADE`))
  })

  async function seedInvites(
    count: number,
    state: string,
    updatedAt: number,
    expiresAt = NOW - DAY_MS
  ): Promise<void> {
    await database.query(
      `INSERT INTO relay_invites
       (user_id, relay_host_id, relay_device_id, token_hash, state, attempt_count,
        max_attempts, expires_at, created_at, updated_at)
       SELECT ?, ?, 'device-' || n, 'token-' || ? || '-' || n, ?, 0, 3, ?, ?, ?
       FROM generate_series(1, ?) AS n`,
      [identity.userId, identity.relayHostId, state, state, expiresAt, updatedAt, updatedAt, count]
    )
  }

  // Not through RelayDatabase: it routes anything that is not a SELECT to the row-count path, and
  // EXPLAIN on an UPDATE is neither.
  async function plan(sql: string, params: unknown[]): Promise<string> {
    const client = new pg.Client({ connectionString: scopedUrl() })
    await client.connect()
    try {
      let index = 0
      const result = await client.query(
        `EXPLAIN ${sql.replace(/\?/g, () => `$${(index += 1)}`)}`,
        params
      )
      return result.rows.map((row) => String(row['QUERY PLAN'])).join('\n')
    } finally {
      await client.end()
    }
  }

  async function cleanupPlan(prefix: string, occurrence = 0): Promise<string> {
    const statements: { sql: string; params: unknown[] }[] = []
    const probe: RelayDatabase = {
      dialect: 'postgres',
      query: async (sql, params = []) => {
        statements.push({ sql, params })
        return []
      },
      queryLocked: async () => {
        throw new Error('unexpected cleanup lock')
      },
      transaction: async (operation) => await operation(probe),
      close: async () => undefined
    }
    await new RelayCredentialStore(probe, () => NOW).cleanup()
    const statement = statements.filter((item) => item.sql.startsWith(prefix))[occurrence]
    if (!statement) {
      throw new Error(`missing production cleanup statement: ${prefix}`)
    }
    return await plan(statement.sql, statement.params)
  }

  it('plans both invite sweeps as index scans instead of scanning the whole table', async () => {
    // Production's shape: terminal invites outnumber live ones by orders of magnitude, which is
    // what makes the partial predicates worth having.
    await seedInvites(20_000, 'consumed', NOW)
    for (const state of ['available', 'reserved', 'cooldown']) {
      await seedInvites(200, state, NOW, NOW + DAY_MS)
    }
    await database.query(
      `UPDATE relay_invites SET reservation_expires_at = ? WHERE state = 'reserved'`,
      [NOW + 1]
    )
    // Only ANALYZE makes the planner's row estimates real; without it a cold table looks tiny and
    // a seq scan wins on any index.
    await database.query(`ANALYZE relay_invites`)

    const expiry = await cleanupPlan('UPDATE relay_invites', 0)
    const reservation = await cleanupPlan('UPDATE relay_invites', 1)

    // Either partial index may win; both restrict to live invites, and neither sweep may scan the whole table.
    for (const sweep of [expiry, reservation]) {
      expect(sweep).not.toContain('Seq Scan on relay_invites')
      expect(sweep).toMatch(/using relay_invites_sweep_(expiry|reservation)/)
    }
    expect(expiry).toContain('relay_invites_sweep_expiry')
  })

  it('plans the basis sweep off the composite index rather than the 1.5 GB heap', async () => {
    // Active→inactive basis flips leave dead partial-index entries; with 20k settled/50 live the planner still picks the composite index (see schema).
    await database.query(
      `INSERT INTO relay_connection_bases
       (basis_conn_id, user_id, relay_host_id, relay_device_id, owning_control_generation,
        credential_kind, deadline, active, created_at)
       SELECT 'settled-' || n, ?, ?, 'device-1', 1, 'invite', ?, 0, ?
       FROM generate_series(1, 20000) AS n`,
      [identity.userId, identity.relayHostId, NOW - 2 * DAY_MS, NOW - 2 * DAY_MS]
    )
    await database.query(
      `INSERT INTO relay_connection_bases
       (basis_conn_id, user_id, relay_host_id, relay_device_id, owning_control_generation,
        credential_kind, deadline, active, created_at)
       SELECT 'live-' || n, ?, ?, 'device-1', 1, 'invite', ?, 1, ?
       FROM generate_series(1, 50) AS n`,
      [identity.userId, identity.relayHostId, NOW + 30_000, NOW]
    )
    await database.query(`ANALYZE relay_connection_bases`)

    const sweep = await cleanupPlan('UPDATE relay_connection_bases')

    expect(sweep).not.toContain('Seq Scan on relay_connection_bases')
    expect(sweep).toContain('using relay_connection_bases_active_deadline')
  })

  it('plans the drained basis reaper off the composite index, not the heap', async () => {
    // Keep composite index for drained active=0 retention checks (5 vs 1,274 buffers at 200k rows); backlog batches correctly favor bounded seq scans.
    await database.query(
      `INSERT INTO relay_connection_bases
       (basis_conn_id, user_id, relay_host_id, relay_device_id, owning_control_generation,
        credential_kind, deadline, active, created_at)
       SELECT 'settled-' || n, ?, ?, 'device-1', 1, 'invite', ?, 0, ?
       FROM generate_series(1, 20000) AS n`,
      [identity.userId, identity.relayHostId, NOW - 60_000, NOW - 60_000]
    )
    await database.query(`ANALYZE relay_connection_bases`)

    const reaper = await cleanupPlan('DELETE FROM relay_connection_bases')

    expect(reaper).toContain('relay_connection_bases_active_deadline')
    expect(reaper).not.toContain('Seq Scan on relay_connection_bases')
  })

  it('plans the pending-authorization and rate-window sweeps as index scans', async () => {
    await database.query(
      `INSERT INTO relay_direct_authorizations
       (direct_auth_id, user_id, relay_host_id, relay_device_id, owning_control_generation,
        deadline, consumed_at)
       SELECT 'auth-' || n, ?, ?, 'device-1', 1, ?, ?
       FROM generate_series(1, 20000) AS n`,
      [identity.userId, identity.relayHostId, NOW - 1, NOW - 1]
    )
    await database.query(
      `INSERT INTO relay_rate_windows (scope_key, window_kind, window_started_at, count)
       SELECT 'scope-' || n, 'invite-mint', ?, 1 FROM generate_series(1, 20000) AS n`,
      [NOW]
    )
    await database.query(`ANALYZE relay_direct_authorizations`)
    await database.query(`ANALYZE relay_rate_windows`)

    const pending = await cleanupPlan('UPDATE relay_direct_authorizations')
    const windows = await cleanupPlan('DELETE FROM relay_rate_windows')

    expect(pending).toContain('relay_direct_authorizations_pending_deadline')
    expect(pending).not.toContain('Seq Scan on relay_direct_authorizations')
    expect(windows).toContain('relay_rate_windows_started')
    expect(windows).not.toContain('Seq Scan on relay_rate_windows')
  })

  it('reaps settled bases and consumed authorizations through ctid', async () => {
    await database.query(
      `INSERT INTO relay_connection_bases
       (basis_conn_id, user_id, relay_host_id, relay_device_id, owning_control_generation,
        credential_kind, deadline, active, created_at)
       SELECT 'settled-' || n, ?, ?, 'device-1', 1, 'invite', ?, 0, ?
       FROM generate_series(1, 5002) AS n`,
      [identity.userId, identity.relayHostId, NOW - 2 * DAY_MS, NOW - 2 * DAY_MS]
    )
    await database.query(
      `INSERT INTO relay_connection_bases
       (basis_conn_id, user_id, relay_host_id, relay_device_id, owning_control_generation,
        credential_kind, deadline, active, created_at)
       VALUES ('live', ?, ?, 'device-1', 1, 'invite', ?, 1, ?)`,
      [identity.userId, identity.relayHostId, NOW + 30_000, NOW]
    )
    await database.query(
      `INSERT INTO relay_direct_authorizations
       (direct_auth_id, user_id, relay_host_id, relay_device_id, owning_control_generation,
        deadline, consumed_at)
       SELECT 'consumed-' || n, ?, ?, 'device-1', 1, ?, ?
       FROM generate_series(1, 5002) AS n`,
      [identity.userId, identity.relayHostId, NOW - 2 * DAY_MS, NOW - 2 * DAY_MS]
    )
    await database.query(
      `INSERT INTO relay_direct_authorizations
       (direct_auth_id, user_id, relay_host_id, relay_device_id, owning_control_generation,
        deadline, consumed_at)
       VALUES ('pending', ?, ?, 'device-1', 1, ?, NULL)`,
      [identity.userId, identity.relayHostId, NOW + 30_000]
    )

    await store.cleanup()
    expect(
      await database.query(`SELECT count(*) AS total FROM relay_connection_bases`)
    ).toEqual([{ total: '3' }])
    expect(
      await database.query(`SELECT count(*) AS total FROM relay_direct_authorizations`)
    ).toEqual([{ total: '3' }])

    await store.cleanup()
    // Only the rows a reader could still accept are left.
    expect(
      await database.query(`SELECT basis_conn_id FROM relay_connection_bases`)
    ).toEqual([{ basis_conn_id: 'live' }])
    expect(
      await database.query(`SELECT direct_auth_id FROM relay_direct_authorizations`)
    ).toEqual([{ direct_auth_id: 'pending' }])
  })

  it('reaps terminal invites past retention through ctid, one bounded batch per cycle', async () => {
    await seedInvites(5_002, 'consumed', NOW - 30 * DAY_MS)
    await seedInvites(3, 'invalidated', NOW - 6 * DAY_MS)
    await seedInvites(2, 'available', NOW - 400 * DAY_MS, NOW + DAY_MS)

    await store.cleanup()
    expect(await database.query(`SELECT count(*) AS total FROM relay_invites`)).toEqual([
      { total: '7' }
    ])

    await store.cleanup()
    // The two live invites and the three inside retention survive; the batch remainder is gone.
    expect(
      await database.query(`SELECT state, count(*) AS total FROM relay_invites GROUP BY state ORDER BY state`)
    ).toEqual([
      { state: 'available', total: '2' },
      { state: 'invalidated', total: '3' }
    ])
  })

  it('plans the audit reaper off relay_audit_events_at when most rows are past retention', async () => {
    // Unordered, a LIMIT with this many matches is cheapest as a sequential scan from page 0, which
    // in production would reread every retained row once the oldest pages are reaped.
    await database.query(
      `INSERT INTO relay_audit_events (id, at, type, user_id, relay_host_id, detail_json)
       SELECT 'old-' || n, ?, 'resume-confirmed', ?, ?, '{}' FROM generate_series(1, 20000) AS n`,
      [NOW - AUDIT_EVENT_RETENTION_MS - DAY_MS, identity.userId, identity.relayHostId]
    )
    await database.query(`ANALYZE relay_audit_events`)

    const reaper = await cleanupPlan('DELETE FROM relay_audit_events')

    expect(reaper).toContain('relay_audit_events_at')
    expect(reaper).not.toContain('Seq Scan on relay_audit_events')
  })

  it('reaps old confirm results and audit events and keeps the ones inside retention', async () => {
    await database.query(
      `INSERT INTO relay_confirm_results
       (user_id, relay_host_id, req_id, basis_conn_id, tuple_json, result_json, committed_at)
       SELECT ?, ?, 'old-' || n, 'basis-1', '{}', '{}', ? FROM generate_series(1, 200) AS n`,
      [identity.userId, identity.relayHostId, NOW - CONFIRM_RESULT_RETENTION_MS - 1]
    )
    await database.query(
      `INSERT INTO relay_confirm_results
       (user_id, relay_host_id, req_id, basis_conn_id, tuple_json, result_json, committed_at)
       VALUES (?, ?, 'recent', 'basis-1', '{}', '{}', ?)`,
      [identity.userId, identity.relayHostId, NOW - DAY_MS]
    )
    await database.query(
      `INSERT INTO relay_audit_events (id, at, type, user_id, relay_host_id, detail_json)
       VALUES ('old', ?, 'resume-confirmed', ?, ?, '{}'), ('recent', ?, 'resume-confirmed', ?, ?, '{}')`,
      [
        NOW - AUDIT_EVENT_RETENTION_MS - 1, identity.userId, identity.relayHostId,
        NOW - DAY_MS, identity.userId, identity.relayHostId
      ]
    )

    await store.cleanup()

    expect(await database.query(`SELECT req_id FROM relay_confirm_results`)).toEqual([
      { req_id: 'recent' }
    ])
    expect(await database.query(`SELECT id FROM relay_audit_events`)).toEqual([{ id: 'recent' }])
  })
})