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.
TypeScript adeagent-ideai-agentsautomationbotclaude-codecodexghosttygrok-bothermes-agentmobile-appmuseopenclawopencodeorchestrationpiruntimeterminalworktreesyc-backed
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' }])
})
})