Never stop coding. Free MIT AI gateway: one endpoint, 375 providers (150+ free), 1,400+ models Kimi, Claude, GPT, Gemini, GLM, DeepSeek, MiniMax. Works with Claude Code, Codex, Cursor, OpenCode, Cline & Copilot. Quota-aware auto-fallback, RTK+Caveman compression saves 15-95% tokens, MCP/A2A, Desktop/PWA. Built by 900+ contributors
TypeScript a2aai-agentsai-gatewayanthropicclaudeclaude-codeclinecodexcopilotcursordeepseekfree-aigeminikimillm-gatewaymcpopenaiopenai-proxyqwentoken-saver
/**
* db/proxyLogs.ts — Read queries over the `proxy_logs` table.
* Extracted from the /api/logs/export route handler.
*
* Hard Rule #5: routes must not embed raw SQL — these queries live here so the
* /api/logs/export route can delegate.
*
* NOTE: The SELECT * intentionally returns the historical `public_ip` column,
* NOT `clientIp`. This differs from GET /api/usage/proxy-logs which exposes
* the value as `clientIp`. Callers of the export endpoint should read
* `public_ip`. This inconsistency will be resolved in a future DB migration
* (#2880).
*
* Sliced out of #3500 (proxy_logs cluster, slice 4).
*/
import { getDbInstance } from "./core";
import { normalizeProxyHostForLog } from "../proxyLogHost";
import { sanitizeTimingMs } from "@omniroute/open-sse/utils/timingMs.ts";
// ---------------------------------------------------------------------------
// Queries
// ---------------------------------------------------------------------------
/**
* Returns all proxy_logs rows with timestamp >= `since`, ordered by timestamp
* descending (most recent first).
*
* @param since - ISO-8601 timestamp lower bound, e.g. "2024-01-01T00:00:00.000Z".
*/
export function exportProxyLogsSince(since: string): Record<string, unknown>[] {
const db = getDbInstance();
const stmt = db.prepare(
"SELECT * FROM proxy_logs WHERE timestamp >= @since ORDER BY timestamp DESC"
);
return stmt.all({ since }) as Record<string, unknown>[];
}
/**
* Total number of proxy_logs rows with timestamp >= `since` — a cheap
* aggregate query that never materializes the matching rows themselves.
* Used by /api/logs/export to report `totalAvailable` without paying the
* cost of fetching every row just to count them (#13123).
*/
export function countProxyLogsSince(since: string): number {
const db = getDbInstance();
const row = db
.prepare("SELECT COUNT(*) AS count FROM proxy_logs WHERE timestamp >= @since")
.get({ since }) as { count: number };
return row.count;
}
const PAGE_SIZE = 500;
/**
* Streams proxy_logs rows with timestamp >= `since`, up to `limit` rows,
* ordered by timestamp descending — paginated via SQL LIMIT/OFFSET in fixed
* batches, never buffering more than `PAGE_SIZE` rows at once (#13123: the
* previous `exportProxyLogsSince()` + slice-after-fetch approach still
* materialized every matching row before the row cap was even applied). Note:
* the `SqliteAdapter` (`./adapters/types.ts`) intentionally exposes only
* `run`/`get`/`all` — no `.iterate()` cursor — so LIMIT/OFFSET batching is
* the cursor-equivalent available without widening that shared interface
* across all 4 driver adapters.
* This is a live, best-effort export, not a transactionally consistent snapshot:
* concurrent inserts/deletes can shift OFFSET pages and repeat or omit rows.
* The preflight COUNT is an estimate; the route reports its emitted count last.
*/
export function* iterateProxyLogsSince(
since: string,
limit: number
): Generator<Record<string, unknown>, void, void> {
const db = getDbInstance();
let offset = 0;
let yielded = 0;
while (yielded < limit) {
const pageLimit = Math.min(PAGE_SIZE, limit - yielded);
const stmt = db.prepare(
"SELECT * FROM proxy_logs WHERE timestamp >= @since ORDER BY timestamp DESC LIMIT @pageLimit OFFSET @offset"
);
const page = stmt.all({ since, pageLimit, offset }) as Record<string, unknown>[];
if (page.length === 0) break;
for (const row of page) {
yield row;
yielded++;
}
offset += page.length;
if (page.length < pageLimit) break;
}
}
// 24h window for "last known egress IP" lookups. This helper answers a
// different question from proxyEgress.ts (#10677): that module reports which
// connections share an egress IP *right now*, derived from their proxy config
// and a live probe (5 min cache), while the lock needs the IP a connection
// actually *left through* on its recent traffic — history, which only
// proxy_logs holds. Hence a local window constant rather than a dependency.
// Exported so callers can build `since` without duplicating the window.
export const EGRESS_IP_LOOKUP_WINDOW_MS = 24 * 60 * 60 * 1000;
/**
* Last non-null egress IP observed for a connection within the window, or
* null. Best-effort by design: egress_ip is only populated once the egress IP
* has been probed (cache TTL 5 min), so a cold cache yields null and the
* caller must fall back to today's behavior. Synchronous read (#10539 — no
* in-memory cache to go stale). The table has no index on connection_id
* (migration 134, YAGNI); the scan is bounded by the window via
* idx_pl_timestamp and this helper only runs at 429 frequency.
*/
export function getRecentEgressIpForConnection(
connectionId: string,
since: string
): { egressIp: string; at: string } | null {
const db = getDbInstance();
const row = db
.prepare(
`SELECT egress_ip, timestamp FROM proxy_logs
WHERE connection_id = ? AND egress_ip IS NOT NULL AND timestamp >= ?
ORDER BY timestamp DESC LIMIT 1`
)
.get(connectionId, since) as { egress_ip: string; timestamp: string } | undefined;
if (!row) return null;
return { egressIp: row.egress_ip, at: row.timestamp };
}
/**
* Normalizes a proxy host lookup key: trim, strip one bracket pair, lowercase.
* Anything that is not a non-empty string normalizes to "" so callers never
* throw on missing or mistyped input — the reader below maps "" to null.
*/
function normalizeProxyHostKey(host: unknown): string {
return normalizeProxyHostForLog(host) ?? "";
}
/**
* Last non-null egress IP observed through a proxy endpoint within the
* window, keyed by (host, port), or null. Best-effort by design: egress_ip is
* only populated once the egress IP has been probed (cache TTL 5 min), so a
* cold cache yields null and the caller must fall back to today's behavior.
* Synchronous read (no in-memory cache to go stale). The table has no index
* on (proxy_host, proxy_port) (migration 134, YAGNI); the scan is bounded by
* the window via idx_pl_timestamp, same as getRecentEgressIpForConnection.
* The host key is normalized (trim, strip one bracket pair, lowercase);
* IPv4-mapped IPv6 forms are intentionally not unmapped. The port must be an
* integer in 1-65535 — anything else returns null instead of throwing.
*/
export function getRecentEgressIpForProxy(
host: string,
port: number
): { egressIp: string; at: string } | null {
const h = normalizeProxyHostKey(host);
if (!h) return null;
if (!Number.isInteger(port) || port < 1 || port > 65535) return null;
const db = getDbInstance();
const since = new Date(Date.now() - EGRESS_IP_LOOKUP_WINDOW_MS).toISOString();
const row = db
.prepare(
`SELECT egress_ip, timestamp FROM proxy_logs
WHERE proxy_host = ? AND proxy_port = ?
AND egress_ip IS NOT NULL AND timestamp >= ?
ORDER BY timestamp DESC LIMIT 1`
)
.get(h, port, since) as { egress_ip: string; timestamp: string } | undefined;
if (!row) return null;
return { egressIp: row.egress_ip, at: row.timestamp };
}
/**
* Distinct non-null egress IPs observed through a proxy endpoint since
* `sinceIso` (up to `limit`). Same host-key normalization and port validation
* as `getRecentEgressIpForProxy`: anything unusable yields `[]`, never a throw.
*/
export function getRecentEgressIpsForProxy(
host: string,
port: number,
sinceIso: string,
limit = 3
): string[] {
const h = normalizeProxyHostKey(host);
if (!h) return [];
if (!Number.isInteger(port) || port < 1 || port > 65535) return [];
if (typeof sinceIso !== "string" || !sinceIso) return [];
const capped = Number.isInteger(limit) && limit > 0 ? Math.min(limit, 10) : 3;
const db = getDbInstance();
const rows = db
.prepare(
`SELECT DISTINCT egress_ip FROM proxy_logs
WHERE proxy_host = ? AND proxy_port = ?
AND egress_ip IS NOT NULL AND timestamp >= ?
LIMIT ?`
)
.all(h, port, sinceIso, capped) as Array<{ egress_ip: string }>;
return rows.map((r) => r.egress_ip).filter((ip) => typeof ip === "string" && ip);
}
export type PoolEgressObservationCounts = {
connections: number;
distinctExits: number;
maxConnectionsOnOneExit: number;
};
export type PoolEgressFailureFamily = {
family: string;
count: number;
};
export type PoolEgressFailureExit = {
exit: string;
failures: number;
byFamily: PoolEgressFailureFamily[];
};
export type PoolEgressFailureBreakdown = {
byExit: PoolEgressFailureExit[];
byFamily: PoolEgressFailureFamily[];
unattributed: number;
attributionNote: string;
};
export type PoolEgressObservation = PoolEgressObservationCounts & {
failures: PoolEgressFailureBreakdown;
};
// Per-family breakdowns only cover requests whose per-family attribution was
// logged: `proxy_logs.correlation_id` is NULL for legacy rows and whenever the
// attribution flag is off (`isRotationAttributionEnabled()` gates the proxy
// side), while `call_logs.correlation_id` is written by `saveCallLog` with
// no gate. Unmatchable rows land in the explicit `unattributed` bucket.
export const POOL_EGRESS_ATTRIBUTION_NOTE =
"per-family breakdown covers only requests logged with attribution on";
/**
* Per-exit failure breakdown for a proxy pool's members since `since`: one row
* per failed proxied request (`status != 'success'`, i.e. `error` and
* `timeout`), grouped by egress IP and by call-log error family. Families come
* from `call_logs.error_type` (persisted vocabulary + `unknown`; NULL means
* "legacy row / not a failure") joined on `correlation_id`; rows with no
* correlation or no match land in `unattributed`, never in a family.
*
* Same member join and same `egress_ip IS NOT NULL AND connection_id IS NOT
* NULL` filters as the `connections` counters above, so failure totals stay
* reconcilable with them (no host:port fallback; note `failures` counts rows
* via `COUNT(*)` while `connections` counts `DISTINCT connection_id`, so the
* two are comparable but not equatable). A retry can share one
* correlation_id across several call_logs rows (precedent:
* agenticConversations.ts keeps the first match), so a correlated scalar
* sub-query reads `error_type` from the first row by `rowid`, one index seek
* per failed request on `idx_cl_correlation_id` instead of a pass over the
* whole call_logs table. The family is computed in a derived table and grouped
* outside it: grouping on the scalar sub-query's alias in the same SELECT
* merges distinct families on SQLite. 0 new indexes: the window rides
* `idx_pl_timestamp`.
* `scope`/`scopeId` must already be normalized; an empty pool yields empties.
*/
export function getPoolEgressFailureBreakdown(
scope: string,
scopeId: string | null,
since: string
): PoolEgressFailureBreakdown {
const db = getDbInstance();
const rows = db
.prepare(
`SELECT exit, family, COUNT(*) AS n
FROM (
SELECT l.egress_ip AS exit,
(SELECT c.error_type FROM call_logs c
WHERE c.correlation_id = l.correlation_id
ORDER BY c.rowid LIMIT 1) AS family
FROM proxy_logs l
JOIN proxy_registry r ON l.proxy_host = r.host AND l.proxy_port = r.port
WHERE r.id IN (SELECT proxy_id FROM proxy_assignments WHERE scope = ? AND scope_id IS ?)
AND l.timestamp >= ? AND l.status != 'success'
AND l.egress_ip IS NOT NULL AND l.connection_id IS NOT NULL
)
GROUP BY exit, family
ORDER BY exit ASC`
)
.all(scope, scopeId, since) as Array<{ exit: string; family: string | null; n: number }>;
const byExitMap = new Map<string, Map<string, number>>();
const byFamilyMap = new Map<string, number>();
let unattributed = 0;
for (const row of rows) {
const family = row.family ?? "unattributed";
let exitFamilies = byExitMap.get(row.exit);
if (!exitFamilies) {
exitFamilies = new Map<string, number>();
byExitMap.set(row.exit, exitFamilies);
}
exitFamilies.set(family, (exitFamilies.get(family) ?? 0) + row.n);
byFamilyMap.set(family, (byFamilyMap.get(family) ?? 0) + row.n);
if (family === "unattributed") unattributed += row.n;
}
const sortFamilies = (entries: Array<[string, number]>): PoolEgressFailureFamily[] =>
entries
.map(([family, count]) => ({ family, count }))
.sort((a, b) => b.count - a.count || (a.family < b.family ? -1 : 1));
return {
byExit: [...byExitMap.entries()].map(([exit, families]) => ({
exit,
failures: [...families.values()].reduce((sum, n) => sum + n, 0),
byFamily: sortFamilies([...families.entries()]),
})),
byFamily: sortFamilies([...byFamilyMap.entries()]),
unattributed,
attributionNote: POOL_EGRESS_ATTRIBUTION_NOTE,
};
}
/**
* Deferred per-attempt upstream timing patch. Rows are inserted with the
* first-chunk duration unknown on slow streams (NULL); the capture layer
* patches it once the first useful body byte arrives. Only non-negative
* integers are written - anything else is rejected (returns false) so
* partially migrated databases and clock skew never corrupt the row.
* Returns true when exactly one row was patched.
*/
export function updateAttemptTiming(
id: string,
patch: { headersMs?: number | null; firstChunkMs?: number | null }
): boolean {
if (typeof id !== "string" || !id) return false;
const clean = {
headersMs: sanitizeTimingValue(patch.headersMs),
firstChunkMs: sanitizeTimingValue(patch.firstChunkMs),
};
if (clean.headersMs === undefined && clean.firstChunkMs === undefined) return false;
const db = getDbInstance();
const sets: string[] = [];
const params: Record<string, unknown> = { id };
if (clean.headersMs !== undefined) {
sets.push("headers_ms = @headersMs");
params.headersMs = clean.headersMs;
}
if (clean.firstChunkMs !== undefined) {
sets.push("first_chunk_ms = @firstChunkMs");
params.firstChunkMs = clean.firstChunkMs;
}
const result = db.prepare(`UPDATE proxy_logs SET ${sets.join(", ")} WHERE id = @id`).run(params);
return Number(result.changes) === 1;
}
// Undefined = column left untouched; an invalid value is never written.
function sanitizeTimingValue(value: number | null | undefined): number | undefined {
return value === undefined ? undefined : (sanitizeTimingMs(value) ?? undefined);
}
/**
* How many distinct observed egress IPs served a proxy pool's members since `since`, how
* many OmniRoute connections went through them, and the most connections seen behind one
* egress IP over that window. Members are matched to log rows by host and port, so two
* registry rows sharing one entry point count together. Only numbers leave this function.
* `scope` and `scopeId` must already be normalized (normalizeScope and
* normalizeAssignmentScopeId); an empty pool simply matches no rows.
*/
export function getPoolEgressObservation(
scope: string,
scopeId: string | null,
since: string
): PoolEgressObservation {
const db = getDbInstance();
const perExit = db
.prepare(
`SELECT COUNT(DISTINCT l.connection_id) AS n
FROM proxy_logs l
JOIN proxy_registry r ON l.proxy_host = r.host AND l.proxy_port = r.port
WHERE r.id IN (SELECT proxy_id FROM proxy_assignments WHERE scope = ? AND scope_id IS ?)
AND l.timestamp >= ? AND l.egress_ip IS NOT NULL AND l.connection_id IS NOT NULL
GROUP BY l.egress_ip`
)
.all(scope, scopeId, since) as Array<{ n: number }>;
const total = db
.prepare(
`SELECT COUNT(DISTINCT l.connection_id) AS n
FROM proxy_logs l
JOIN proxy_registry r ON l.proxy_host = r.host AND l.proxy_port = r.port
WHERE r.id IN (SELECT proxy_id FROM proxy_assignments WHERE scope = ? AND scope_id IS ?)
AND l.timestamp >= ? AND l.egress_ip IS NOT NULL AND l.connection_id IS NOT NULL`
)
.get(scope, scopeId, since) as { n: number };
return {
connections: total.n,
distinctExits: perExit.length,
maxConnectionsOnOneExit: perExit.reduce((max, row) => Math.max(max, row.n), 0),
failures: getPoolEgressFailureBreakdown(scope, scopeId, since),
};
}