Multiplayer agent harness for work.
import { DIRECTORY_MIGRATIONS } from "../directory/postgres-directory-store.ts";
import { randomUUID } from "node:crypto";
import { jsonbStringify } from "../persistence/durable-map.ts";
import { likeContains } from "../persistence/like.ts";
import { createPgPool, type PoolClient, withPgTransaction } from "../persistence/pg-pool.ts";
import { jsonbSafeStringify, pgTextSafe } from "../util/text.ts";
import { reportFailure } from "../util/errors.ts";
import type { Session, SessionEntry, SessionType, ScopeId } from "../types.ts";
import type { GoalRecord } from "../harness/goal.ts";
import type {
NewSessionPin,
SessionPin,
AttributedTurn,
EntrySearchHit,
ScopeActivity,
DistinctScope,
ParticipantPage,
ParticipantPageQuery,
ParticipantScope,
SessionDescendant,
GetEntriesOptions,
Lease,
LeaseAttempt,
LeaseHolder,
LeasePeek,
LlmRequestRecord,
NewEntry,
NewLlmRequest,
ParticipantWindow,
ScreenSample,
SessionOrigin,
SessionOriginFilter,
SessionPage,
SessionRef,
SessionStore,
SessionSummary,
SpendRow,
StoreOptions,
} from "./session-store.ts";
import { tsPrefixQuery } from "./entry-search.ts";
import { ITEMS_CUTOVER_MIGRATIONS, LEGACY_TAPE_MIGRATIONS } from "./legacy-history-migrations.ts";
import {
cronIdOf,
legacyOriginPattern,
ORIGIN_ALTERNATION,
promptEnvelopeBody,
sessionOrigin,
stableOriginPattern,
threadRefCronIdExpr,
userMessagePreview,
isWebThread,
isOverheardEntry,
DuplicateCallError,
} from "./session-store.ts";
import {
entryFromItem,
itemFromEntry,
ITEM_COLUMNS,
type ItemRow,
msOf,
AGENT_REPLY_ITEM,
SEARCHABLE_ITEM,
searchTypeExpr,
USER_ITEM_KINDS,
USER_KINDS,
} from "./items.ts";
import { SECURITY_SCREEN_STEP, screenPayloadFromEnvelope } from "../security/security-posture.ts";
const LLM_CAPTURE_PURGE_BATCH = 100;
const ENVELOPE_TOUCH_MS = 24 * 60 * 60_000;
const threadRefOriginExpr = (threadRef: string): string =>
`COALESCE(substring(${threadRef} FROM '${stableOriginPattern(ORIGIN_ALTERNATION)}'), substring(${threadRef} FROM '${legacyOriginPattern(ORIGIN_ALTERNATION)}'), 'conversation')`;
const LOOP_OR_SWARM_REF = legacyOriginPattern("(loop|swarm)");
const sessionOriginBackfillLoop = (candidates: string, keepSpendMarkers: boolean): string => `
after := '';
LOOP
SELECT array_agg(id ORDER BY id) INTO batch
FROM (SELECT id FROM sessions WHERE id > after AND ${candidates} ORDER BY id LIMIT 5000) candidates;
EXIT WHEN batch IS NULL;
UPDATE sessions SET origin = ${threadRefOriginExpr("thread_ref")}, origin_id = ${threadRefCronIdExpr("thread_ref")}
WHERE id = ANY(batch);${keepSpendMarkers ? "" : "\n DELETE FROM session_spend_dirty WHERE writer = pg_current_xact_id();"}
after := batch[cardinality(batch)];
COMMIT;
PERFORM pg_sleep(0.25);
END LOOP;`;
const SESSION_ORIGIN_BACKFILL_SQL = `DO $session_origin_backfill$
DECLARE
batch text[];
after text;
BEGIN${sessionOriginBackfillLoop(`origin IS NULL AND thread_ref !~ '${LOOP_OR_SWARM_REF}'`, false)}${sessionOriginBackfillLoop(
`(origin IS NULL OR origin = 'conversation') AND thread_ref ~ '${LOOP_OR_SWARM_REF}'`,
true,
)}
END $session_origin_backfill$`;
function rowToGoal(r: Record<string, unknown>): GoalRecord {
return {
objective: r.objective as string,
status: r.status as GoalRecord["status"],
tokensUsed: Number(r.tokens_used),
createdAt: (r.created_at as Date).getTime(),
updatedAt: (r.updated_at as Date).getTime(),
...(r.floor != null ? { floor: r.floor as GoalRecord["floor"] } : {}),
...(r.token_cap != null ? { capTokens: Number(r.token_cap) } : {}),
...(r.active_ms != null ? { activeMs: Number(r.active_ms) } : {}),
...(r.active_since != null ? { activeSince: (r.active_since as Date).getTime() } : {}),
...(r.completion_note != null ? { completionNote: r.completion_note as string } : {}),
...(r.cancel_note != null ? { cancelNote: r.cancel_note as string } : {}),
...(r.governor != null ? { governor: r.governor as GoalRecord["governor"] } : {}),
...(r.pause_reason != null ? { pauseReason: r.pause_reason as string } : {}),
};
}
export function rowToSession(r: Record<string, unknown>): Session {
return {
id: r.id as string,
type: r.type as SessionType,
scopeId: r.scope_id as ScopeId,
threadRef: r.thread_ref as string,
...(r.surface != null ? { surface: r.surface as string } : {}),
...(r.client_tools ? { clientTools: r.client_tools as Session["clientTools"] } : {}),
createdAt: Number(r.created_at),
...(r.title != null ? { title: r.title as string } : {}),
...(r.channel_name != null ? { channelName: r.channel_name as string } : {}),
...(r.forked_from_session_id != null && r.fork_boundary_seq != null
? {
forkedFrom: {
sessionId: r.forked_from_session_id as string,
...(r.forked_from_title != null ? { title: r.forked_from_title as string } : {}),
},
forkBoundarySeq: Number(r.fork_boundary_seq),
}
: {}),
...(r.parent_session_id != null ? { parentSessionId: r.parent_session_id as string } : {}),
...(r.spawn_meta != null ? { spawnMeta: JSON.parse(r.spawn_meta as string) as Session["spawnMeta"] } : {}),
};
}
function rowToParticipantSession(r: Record<string, unknown>): Session {
const s = rowToSession(r);
if (r.p_title != null) s.title = r.p_title as string;
if (r.p_archived) s.archived = true;
if (r.p_pinned) s.pinned = true;
if (r.p_color != null) s.color = r.p_color as string;
if (r.agent_last_reply != null && Number(r.agent_last_reply) > Number(r.p_read_at ?? 0) && isWebThread(s.threadRef))
s.unread = true;
if (r.user_last_activity != null) s.lastActivityAt = Number(r.user_last_activity);
if (r.has_entries != null) s.hasEntries = Boolean(r.has_entries);
return s;
}
function rowToLlmRequest(r: Record<string, unknown>): LlmRequestRecord {
return {
id: r.id as string,
sessionId: r.session_id as string,
turnSeq: r.turn_seq == null ? null : Number(r.turn_seq),
step: Number(r.step),
model: r.model as string,
scopeLabel: r.scope_label as ScopeId,
createdAt: Number(r.created_at),
promptHash: r.prompt_hash == null ? null : (r.prompt_hash as string),
...(r.prompt_body != null ? { promptEnvelope: JSON.parse(r.prompt_body as string) } : {}),
truncated: Boolean(r.truncated),
ttftMs: r.ttft_ms == null ? null : Number(r.ttft_ms),
durationMs: r.duration_ms == null ? null : Number(r.duration_ms),
stepGapMs: r.step_gap_ms == null ? null : Number(r.step_gap_ms),
toolWallMs: r.tool_wall_json != null ? (JSON.parse(r.tool_wall_json as string) as number[]) : null,
gapPhases:
r.gap_phases_json != null ? (JSON.parse(r.gap_phases_json as string) as LlmRequestRecord["gapPhases"]) : null,
usage: r.usage_json != null ? (JSON.parse(r.usage_json as string) as LlmRequestRecord["usage"]) : null,
transport:
r.transport_json != null ? (JSON.parse(r.transport_json as string) as LlmRequestRecord["transport"]) : null,
};
}
function rowToParticipantWindow(r: Record<string, unknown>): ParticipantWindow {
return {
sessionId: r.session_id as string,
principalId: r.principal_id as string,
validFrom: Number(r.valid_from),
validTo: r.valid_to == null ? null : Number(r.valid_to),
validFromSeq: r.valid_from_seq == null ? null : Number(r.valid_from_seq),
validToSeq: r.valid_to_seq == null ? null : Number(r.valid_to_seq),
};
}
function rowToEntry(r: Record<string, unknown>): SessionEntry {
return entryFromItem({
sessionId: r.session_id as string,
seq: Number(r.seq),
...(Object.fromEntries(
ITEM_COLUMNS.map(([, key, type]) => [
key,
type === "bigint" && r[key] != null ? Number(r[key]) : (r[key] ?? null),
]),
) as unknown as ItemRow),
scopeLabel: r.scope_label as ScopeId,
createdAt: Number(r.created_at),
});
}
const ITEM_FIELDS = `i.session_id, i.seq, ${ITEM_COLUMNS.map(([column, key]) => `${column === "blob_sha" ? "encode(i.blob_sha, 'hex')" : `i.${column}`} AS "${key}"`).join(",")}, ts.scope_id AS scope_label, ${msOf("i.created_at")} AS created_at`;
const ITEM_FROM = "items i JOIN sessions ts ON ts.id = i.session_id";
const TENANT = "qm_tenant_id()";
const LIST_COLUMNS = `s.id, s.type, s.scope_id, s.thread_ref, s.surface, s.created_at, s.title, s.channel_name,
s.forked_from_session_id, s.forked_from_title, s.fork_boundary_seq, s.parent_session_id`;
const USER_ACTIVITY = "COALESCE(s.last_user_activity, s.created_at)";
const AUTOMATION_SURFACES = "('cron', 'webhook', 'loop')";
const LAST_USER_ACTIVITY_BACKFILL_SQL = `DO $last_user_activity_backfill$
DECLARE
batch text[];
after text := '';
BEGIN
LOOP
SELECT array_agg(id ORDER BY id) INTO batch
FROM (SELECT id FROM sessions WHERE id > after ORDER BY id LIMIT 2000) candidates;
EXIT WHEN batch IS NULL;
WITH latest AS MATERIALIZED (
SELECT b.id, (SELECT ${msOf("MAX(e.created_at)")} FROM items e
WHERE e.tenant_id = ${TENANT} AND e.session_id = b.id AND e.kind IN ${USER_KINDS}) AS at
FROM unnest(batch) AS b(id))
UPDATE sessions s
SET last_user_activity = latest.at
FROM latest
WHERE s.id = latest.id AND latest.at IS NOT NULL
AND (s.last_user_activity IS NULL OR s.last_user_activity < latest.at);
after := batch[cardinality(batch)];
COMMIT;
PERFORM pg_sleep(0.1);
END LOOP;
END $last_user_activity_backfill$`;
const tsOf = (ms: string): string => `to_timestamp((${ms})::double precision / 1000)`;
function rowToSpendRow(r: Record<string, unknown>): SpendRow {
return {
day: Number(r.day),
model: (r.model as string | null) ?? null,
scopeId: r.scope_id as ScopeId,
origin: r.origin as SessionOrigin,
cronId: (r.cron_id as string | null) ?? null,
calls: Number(r.calls),
costUsd: Number(r.cost_usd),
input: Number(r.input),
output: Number(r.output),
cacheRead: Number(r.cache_read),
cacheWrite: Number(r.cache_write),
};
}
const LAST_ACTIVITY_DEBOUNCE_MS = 60_000;
const SEARCH_TIMEOUT_MS = 10_000;
const SPEND_INDEXABLE = `(spend_usage_json(usage_json) IS NOT NULL
AND octet_length(session_id) + octet_length(model) + octet_length(usage_json) <= 2000)`;
const ADMIN_READ_TIMEOUT_MS = 10_000;
const FIRST_PREVIEW_LEN = 160;
const LAST_PREVIEW_LEN = 100;
const storedPreview = (payload: unknown, maxLen: number): string => pgTextSafe(userMessagePreview(payload, maxLen));
export function createPostgresSessionStore(connectionString: string, opts: StoreOptions = {}): SessionStore {
const now = opts.now ?? (() => Date.now());
const leaseTtlMs = opts.leaseTtlMs ?? 5 * 60_000;
const userTurn = (col: string): string =>
`${col}.kind IN ${USER_KINDS} AND NOT (${col}.body @> '{"overheard":true}')`;
const lastActivityExpr = (col: string): string => `COALESCE(${col}.last_activity, ${col}.created_at)`;
const participantWindowBounds = (entry: string, participant: string, item = true) => {
const at = (ms: string): string => (item ? tsOf(ms) : ms);
return [
[
`(${participant}.valid_from_seq IS NOT NULL AND ${entry}.seq >= ${participant}.valid_from_seq)`,
`(${participant}.valid_from_seq IS NULL AND ${entry}.created_at >= ${at(`${participant}.valid_from`)})`,
],
[
`(${participant}.valid_to_seq IS NOT NULL AND ${entry}.seq < ${participant}.valid_to_seq)`,
`(${participant}.valid_to_seq IS NULL AND ${participant}.valid_to IS NOT NULL AND ${entry}.created_at < ${at(`${participant}.valid_to`)})`,
`(${participant}.valid_to_seq IS NULL AND ${participant}.valid_to IS NULL)`,
],
] as const;
};
const withinParticipantWindow = (entry: string, participant: string, item = true): string =>
participantWindowBounds(entry, participant, item)
.map((bounds) => `(${bounds.join(" OR ")})`)
.join(" AND ");
function transcriptItemPredicate(alias: string, participant?: string): string {
const receipt = (item: string): string => `COALESCE(${item}.body @> '{"slackReplyReceipt":true}'::jsonb, false)`;
return `NOT (
${alias}.kind = 'UserMessage' AND ${receipt(alias)}
AND EXISTS (
SELECT 1 FROM items received
WHERE received.tenant_id = ${alias}.tenant_id AND received.session_id = ${alias}.session_id
AND received.kind = 'UserMessage' AND received.external_id = ${alias}.external_id
AND NOT ${receipt("received")}
${participant ? `AND ${withinParticipantWindow("received", participant)}` : ""}
)
)`;
}
const [fromBounds, toBounds] = participantWindowBounds("x", "p");
const participantPresenceSql = fromBounds
.flatMap((from) =>
toBounds.map(
(to) => `SELECT 1 FROM items x WHERE x.tenant_id = ${TENANT} AND x.session_id = s.id AND ${from} AND ${to}`,
),
)
.join(" UNION ALL ");
const SEES_ITEMS = `CASE WHEN p.valid_to IS NULL AND p.valid_to_seq IS NULL AND p.valid_from_seq IS NOT NULL
THEN COALESCE(s.messages, 0) > p.valid_from_seq
ELSE EXISTS (${participantPresenceSql}) END`;
const HAS_CONTENT = `(NULLIF(btrim(COALESCE(p.title, s.title)), '') IS NOT NULL OR ${SEES_ITEMS})`;
const participantSessionsSql = (extraWhere: string, columns = "s.*"): string =>
`SELECT ${columns}, p.title AS p_title, p.archived AS p_archived, p.pinned AS p_pinned, p.color AS p_color,
p.read_at AS p_read_at,
(SELECT ${msOf("MAX(e.created_at)")} FROM items e
WHERE e.tenant_id = ${TENANT} AND e.session_id = s.id AND ${AGENT_REPLY_ITEM("e")}) AS agent_last_reply,
${USER_ACTIVITY} AS user_last_activity,
${SEES_ITEMS} AS has_entries
FROM sessions s
JOIN participants p ON p.session_id = s.id
WHERE p.principal_id = $1${extraWhere}`;
const participantSessions = async (principalId: string, opts?: { limit: number }): Promise<Session[]> => {
const limit = opts ? Math.max(0, Math.floor(opts.limit)) : undefined;
const rows = await q(
participantSessionsSql("", LIST_COLUMNS) +
(limit === undefined ? "" : " ORDER BY COALESCE(s.last_activity, s.created_at) DESC, s.id LIMIT $2"),
limit === undefined ? [principalId] : [principalId, limit],
);
return rows.map(rowToParticipantSession);
};
const participantScopes = async (principalId: string): Promise<ParticipantScope[]> => {
const rows = await q(
`SELECT s.scope_id, MAX(s.channel_name) AS channel_name,
COUNT(*) FILTER (WHERE ${HAS_CONTENT}) AS session_count,
MAX(${USER_ACTIVITY}) FILTER (WHERE ${HAS_CONTENT}) AS last_activity
FROM sessions s
JOIN participants p ON p.session_id = s.id
WHERE p.principal_id = $1
GROUP BY s.scope_id`,
[principalId],
);
return rows.map((r) => ({
scopeId: r.scope_id as ScopeId,
...(r.channel_name != null ? { channelName: r.channel_name as string } : {}),
sessionCount: Number(r.session_count),
lastActivityAt: r.last_activity == null ? null : Number(r.last_activity),
}));
};
const participantPage = async (principalId: string, query: ParticipantPageQuery): Promise<ParticipantPage> => {
const params: unknown[] = [principalId];
const where: string[] = [
"p.principal_id = $1",
"s.parent_session_id IS NULL",
`(s.surface IS NULL OR s.surface NOT IN ${AUTOMATION_SURFACES})`,
HAS_CONTENT,
];
const param = (value: unknown): string => {
params.push(value);
return `$${params.length}`;
};
where.push(`COALESCE(p.archived, false) = ${param(query.archived === true)}`);
if (query.pinned !== undefined) where.push(`COALESCE(p.pinned, false) = ${param(query.pinned)}`);
if (query.surface !== undefined) where.push(`s.surface = ${param(query.surface)}`);
if (query.scopeId !== undefined) where.push(`s.scope_id = ${param(query.scopeId)}`);
if (query.sessionIds !== undefined) where.push(`s.id = ANY(${param([...query.sessionIds])}::text[])`);
if (query.titleQuery?.trim()) {
const pattern = param(likeContains(query.titleQuery.trim()));
where.push(`(COALESCE(p.title, s.title) ILIKE ${pattern} OR s.channel_name ILIKE ${pattern})`);
}
if (query.before)
where.push(`(${USER_ACTIVITY}, s.id) < (${param(query.before.activity)}::bigint, ${param(query.before.id)})`);
const limit = Math.max(1, Math.min(Math.floor(query.limit), 200));
const page = await q(
`SELECT s.id, ${USER_ACTIVITY} AS activity
FROM participants p JOIN sessions s ON s.id = p.session_id
WHERE ${where.join(" AND ")}
ORDER BY ${USER_ACTIVITY} DESC, s.id DESC
LIMIT ${param(limit + 1)}`,
params,
);
const more = page.length > limit;
const ids = page.slice(0, limit).map((row) => row.id as string);
if (!ids.length) return { sessions: [], next: null };
const rows = await q(participantSessionsSql(" AND s.id = ANY($2::text[])", LIST_COLUMNS), [principalId, ids]);
const byId = new Map(rows.map((row) => [row.id as string, rowToParticipantSession(row)]));
const last = page[limit - 1];
return {
sessions: ids.flatMap((id) => byId.get(id) ?? []),
next: more && last ? { activity: Number(last.activity), id: last.id as string } : null,
};
};
const descendantsOf = async (rootIds: readonly string[]): Promise<SessionDescendant[]> => {
if (!rootIds.length) return [];
const rows = await q(
`WITH RECURSIVE tree(id, root_id, thread_ref) AS (
SELECT id, parent_session_id, thread_ref FROM sessions WHERE parent_session_id = ANY($1::text[])
UNION
SELECT s.id, t.root_id, s.thread_ref FROM sessions s JOIN tree t ON s.parent_session_id = t.id
)
SELECT id, root_id, thread_ref FROM tree`,
[[...rootIds]],
);
return rows.map((r) => ({ id: r.id as string, rootId: r.root_id as string, threadRef: r.thread_ref as string }));
};
const participantSession = async (sessionId: string, principalId: string): Promise<Session | null> => {
const rows = await q(participantSessionsSql(" AND s.id = $2"), [principalId, sessionId]);
return rows[0] ? rowToParticipantSession(rows[0]) : null;
};
const isBackground = (alias: string): string => `${alias}.origin <> 'conversation'`;
const hasOrigin = (alias: string, origin: SessionOrigin): string => `${alias}.origin = '${origin}'`;
const originFilterClause = (alias: string, origin: SessionOriginFilter): string =>
origin === "other_background" ? `${alias}.origin NOT IN ('conversation', 'cron')` : hasOrigin(alias, origin);
const spendSql = `WITH RECURSIVE ancestry AS (
SELECT s.id AS session_id, s.parent_session_id, s.origin AS origin, s.origin_id AS cron_id,
ARRAY[s.id] AS path
FROM sessions s
WHERE s.parent_session_id IS NOT NULL AND s.origin = 'conversation'
AND EXISTS (SELECT 1 FROM session_llm_requests r
WHERE r.session_id = s.id AND r.created_at >= $1 AND r.created_at < $2
AND r.usage_json IS NOT NULL)
UNION ALL
SELECT a.session_id, p.parent_session_id, p.origin, p.origin_id, a.path || p.id
FROM ancestry a JOIN sessions p ON p.id = a.parent_session_id
WHERE a.origin = 'conversation' AND NOT p.id = ANY(a.path) AND cardinality(a.path) < 64
), origins AS (
SELECT DISTINCT ON (session_id) session_id, origin, cron_id
FROM ancestry ORDER BY session_id, cardinality(path) DESC
), rollup AS MATERIALIZED (
SELECT (r.created_at / 86400000)::bigint AS day, s.scope_id,
COALESCE(o.origin, s.origin) AS origin,
COALESCE(o.cron_id, s.origin_id) AS cron_id, r.model,
COUNT(*) AS calls,
COALESCE(SUM(cost_usd), 0)::text AS cost_usd,
COALESCE(SUM(input), 0) AS input,
COALESCE(SUM(output), 0) AS output,
COALESCE(SUM(cache_read), 0) AS cache_read,
COALESCE(SUM(cache_write), 0) AS cache_write
FROM (SELECT created_at, session_id, model,
(spend_usage_json(usage_json) ->> 'costUsd')::double precision AS cost_usd,
(spend_usage_json(usage_json) ->> 'input')::bigint AS input,
(spend_usage_json(usage_json) ->> 'output')::bigint AS output,
(spend_usage_json(usage_json) ->> 'cacheRead')::bigint AS cache_read,
(spend_usage_json(usage_json) ->> 'cacheWrite')::bigint AS cache_write
FROM session_llm_requests
WHERE created_at >= $1 AND created_at < $2 AND usage_json IS NOT NULL
AND ${SPEND_INDEXABLE}
UNION ALL
SELECT created_at, session_id, model,
(usage_json::jsonb ->> 'costUsd')::double precision AS cost_usd,
(usage_json::jsonb ->> 'input')::bigint AS input,
(usage_json::jsonb ->> 'output')::bigint AS output,
(usage_json::jsonb ->> 'cacheRead')::bigint AS cache_read,
(usage_json::jsonb ->> 'cacheWrite')::bigint AS cache_write
FROM session_llm_requests
WHERE created_at >= $1 AND created_at < $2 AND usage_json IS NOT NULL
AND NOT ${SPEND_INDEXABLE}
AND EXISTS (SELECT 1 FROM sessions WHERE id = session_llm_requests.session_id)
OFFSET 0) r
JOIN sessions s ON s.id = r.session_id
LEFT JOIN origins o ON o.session_id = s.id
GROUP BY day, s.scope_id, COALESCE(o.origin, s.origin), COALESCE(o.cron_id, s.origin_id), r.model
)
SELECT * FROM rollup ORDER BY day, scope_id, origin, cron_id, model`;
const { pool, q, query } = createPgPool(
connectionString,
[
...DIRECTORY_MIGRATIONS,
{
id: "sessions/store/0001",
expectedChecksum: "cf56c9f6488806677a229698193ef6dd7cb74300c343bdb1930c17702c5ccf2f",
statements: [
`CREATE OR REPLACE FUNCTION safe_json(t text) RETURNS json
LANGUAGE plpgsql IMMUTABLE PARALLEL UNSAFE AS $safe_json$
BEGIN RETURN t::json; EXCEPTION WHEN others THEN RETURN NULL; END $safe_json$`,
`DO $safe_jsonb_parallel$
BEGIN
IF to_regprocedure('safe_jsonb(text)') IS NOT NULL THEN
ALTER FUNCTION safe_jsonb(text) PARALLEL UNSAFE;
END IF;
END $safe_jsonb_parallel$`,
`CREATE TABLE IF NOT EXISTS sessions(
id TEXT PRIMARY KEY, type TEXT NOT NULL, scope_id TEXT NOT NULL,
thread_ref TEXT UNIQUE NOT NULL, created_at BIGINT NOT NULL, title TEXT, channel_name TEXT
)`,
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS title TEXT`,
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS channel_name TEXT`,
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS surface TEXT`,
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS last_activity BIGINT`,
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS messages INT`,
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS turns INT`,
`CREATE TABLE IF NOT EXISTS session_entries(
session_id TEXT NOT NULL, seq INT NOT NULL, parent_seq INT,
type TEXT NOT NULL, payload TEXT, scope_label TEXT NOT NULL, created_at BIGINT NOT NULL,
PRIMARY KEY(session_id, seq)
)`,
`CREATE TABLE IF NOT EXISTS participants(
session_id TEXT NOT NULL, principal_id TEXT NOT NULL,
valid_from BIGINT NOT NULL, valid_to BIGINT,
valid_from_seq INT, valid_to_seq INT,
title TEXT, archived BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY(session_id, principal_id)
)`,
`CREATE TABLE IF NOT EXISTS session_leases(
session_id TEXT PRIMARY KEY, token TEXT NOT NULL, expires_at BIGINT NOT NULL
)`,
`ALTER TABLE session_leases ADD COLUMN IF NOT EXISTS holder TEXT`,
`ALTER TABLE session_leases ADD COLUMN IF NOT EXISTS acquired_at BIGINT`,
`CREATE TABLE IF NOT EXISTS session_tape(
session_id TEXT NOT NULL, seq INT NOT NULL,
kind TEXT NOT NULL, harness TEXT, payload TEXT NOT NULL, scope_label TEXT NOT NULL,
bare_text TEXT, ts TEXT, change_time TEXT, hidden BOOLEAN, overheard BOOLEAN, author TEXT,
entry_seq INT, covers_entry_seq INT, created_at BIGINT NOT NULL,
PRIMARY KEY(session_id, seq)
)`,
`ALTER TABLE participants ADD COLUMN IF NOT EXISTS title TEXT`,
`ALTER TABLE participants ADD COLUMN IF NOT EXISTS archived BOOLEAN NOT NULL DEFAULT FALSE`,
`ALTER TABLE participants ADD COLUMN IF NOT EXISTS pinned BOOLEAN NOT NULL DEFAULT FALSE`,
`ALTER TABLE participants ADD COLUMN IF NOT EXISTS color TEXT`,
`ALTER TABLE participants ADD COLUMN IF NOT EXISTS valid_from_seq INT`,
`ALTER TABLE participants ADD COLUMN IF NOT EXISTS valid_to_seq INT`,
`UPDATE participants p SET valid_from = 0, valid_from_seq = 0
FROM sessions s
WHERE s.id = p.session_id
AND s.scope_id LIKE 'group:web-project-%'
AND p.valid_to IS NULL
AND (p.valid_from_seq IS DISTINCT FROM 0 OR p.valid_from <> 0)`,
`UPDATE participants p SET title = NULL
FROM sessions s
WHERE s.id = p.session_id
AND s.scope_id LIKE 'group:web-project-%'
AND p.valid_to IS NULL
AND p.title = ''`,
`CREATE TABLE IF NOT EXISTS session_llm_requests(
id TEXT PRIMARY KEY, session_id TEXT NOT NULL, turn_seq INT,
step INT NOT NULL, model TEXT NOT NULL, scope_label TEXT NOT NULL,
request TEXT NOT NULL, truncated BOOLEAN NOT NULL DEFAULT FALSE, created_at BIGINT NOT NULL
)`,
`CREATE INDEX IF NOT EXISTS session_llm_requests_by_session
ON session_llm_requests(session_id, created_at, step)`,
`ALTER TABLE session_llm_requests ADD COLUMN IF NOT EXISTS ttft_ms INT`,
`ALTER TABLE session_llm_requests ADD COLUMN IF NOT EXISTS duration_ms INT`,
`ALTER TABLE session_llm_requests ADD COLUMN IF NOT EXISTS step_gap_ms INT`,
`ALTER TABLE session_llm_requests ADD COLUMN IF NOT EXISTS tool_wall_json TEXT`,
`ALTER TABLE session_llm_requests ADD COLUMN IF NOT EXISTS usage_json TEXT`,
`ALTER TABLE session_llm_requests ADD COLUMN IF NOT EXISTS transport_json TEXT`,
`ALTER TABLE session_llm_requests ADD COLUMN IF NOT EXISTS gap_phases_json TEXT`,
`CREATE INDEX IF NOT EXISTS sessions_by_scope ON sessions(scope_id, created_at DESC)`,
`CREATE INDEX IF NOT EXISTS sessions_by_activity ON sessions((COALESCE(last_activity, created_at)) DESC, id DESC)`,
`CREATE INDEX IF NOT EXISTS sessions_by_scope_activity
ON sessions(scope_id, (COALESCE(last_activity, created_at)) DESC, id DESC)`,
`CREATE INDEX IF NOT EXISTS session_entries_user_ts ON session_entries(created_at) WHERE type = 'user'`,
`CREATE INDEX IF NOT EXISTS session_entries_session_created ON session_entries(session_id, created_at DESC)`,
`DELETE FROM session_entries WHERE session_id IN (SELECT id FROM sessions WHERE type IN ('channel','group') AND thread_ref ~ '^[a-z0-9_]+/[^:/]+$')`,
`DELETE FROM participants WHERE session_id IN (SELECT id FROM sessions WHERE type IN ('channel','group') AND thread_ref ~ '^[a-z0-9_]+/[^:/]+$')`,
`DELETE FROM session_leases WHERE session_id IN (SELECT id FROM sessions WHERE type IN ('channel','group') AND thread_ref ~ '^[a-z0-9_]+/[^:/]+$')`,
`DELETE FROM session_llm_requests WHERE session_id IN (SELECT id FROM sessions WHERE type IN ('channel','group') AND thread_ref ~ '^[a-z0-9_]+/[^:/]+$')`,
`DELETE FROM sessions WHERE type IN ('channel','group') AND thread_ref ~ '^[a-z0-9_]+/[^:/]+$'`,
],
},
{
id: "sessions/store/0002",
expectedChecksum: "ca9877865861adf81cfb433750327462aedcfa1e60d4e87a7845d1a77f64c2ec",
statements: [
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS forked_from_session_id TEXT`,
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS forked_from_title TEXT`,
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS fork_boundary_seq INT`,
`DO $$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'sessions_fork_provenance_pair') THEN
ALTER TABLE sessions ADD CONSTRAINT sessions_fork_provenance_pair
CHECK ((forked_from_session_id IS NULL) = (fork_boundary_seq IS NULL)) NOT VALID;
END IF;
END $$`,
`ALTER TABLE session_llm_requests ALTER COLUMN request DROP NOT NULL`,
`ALTER TABLE session_llm_requests ADD COLUMN IF NOT EXISTS prompt_hash TEXT`,
`CREATE TABLE IF NOT EXISTS llm_prompt_envelopes(
hash TEXT PRIMARY KEY, body TEXT NOT NULL, created_at BIGINT NOT NULL
)`,
],
},
{
id: "sessions/store/0003",
expectedChecksum: "fd77efdccf169dee81da809a54313be30a0aaf92cdea4f6c33e34555bb2396c8",
statements: [
`CREATE OR REPLACE FUNCTION entry_search_text(payload text) RETURNS text
LANGUAGE plpgsql IMMUTABLE PARALLEL UNSAFE AS $entry_search_text$
DECLARE j json;
BEGIN
j := replace(payload, '\\u0000', '')::json;
RETURN CASE WHEN json_typeof(j -> 'text') = 'string' THEN j ->> 'text'
WHEN json_typeof(j) = 'string' THEN j #>> '{}'
ELSE NULL END;
EXCEPTION WHEN others THEN RETURN NULL;
END $entry_search_text$`,
`CREATE INDEX IF NOT EXISTS session_entries_search_fts
ON session_entries USING GIN (to_tsvector('simple', COALESCE(entry_search_text(payload), '')))
WHERE type IN ('user', 'assistant', 'text')`,
],
},
{
id: "sessions/store/0004",
expectedChecksum: "856cc6940c934b59e37d511271630f77d72b235c92ab0e2e2085207d15750a75",
statements: [
`CREATE TABLE IF NOT EXISTS session_pins(
id TEXT PRIMARY KEY, session_id TEXT NOT NULL, text TEXT, entry_seq INT,
added_by TEXT NOT NULL, created_at BIGINT NOT NULL
)`,
`CREATE INDEX IF NOT EXISTS session_pins_by_session ON session_pins(session_id, created_at)`,
],
},
{
id: "sessions/store/0005",
expectedChecksum: "4b0824aa4311df8c66b57b16f331e9dcf3dfc8d11c112a000d83f46b1fba1bd0",
statements: [
`CREATE INDEX IF NOT EXISTS session_entries_user_attributed
ON session_entries(session_id, created_at, seq)
WHERE type = 'user' AND (payload IS NULL OR payload NOT LIKE '%"overheard":true%')`,
],
},
{
id: "sessions/store/0006",
expectedChecksum: "57037691db0c87e9eeb28927758ae35e426eb8a8b7f7b5b8440bb08817f39203",
statements: [
`ALTER FUNCTION entry_search_text(text) PARALLEL SAFE`,
`ALTER TABLE session_entries ADD COLUMN IF NOT EXISTS search_tsv tsvector
GENERATED ALWAYS AS (to_tsvector('simple', COALESCE(entry_search_text(payload), ''))) STORED`,
`CREATE INDEX IF NOT EXISTS session_entries_search_tsv
ON session_entries USING GIN (search_tsv)
WHERE type IN ('user', 'assistant', 'text')`,
`DROP INDEX IF EXISTS session_entries_search_fts`,
],
},
{
id: "sessions/store/0007",
expectedChecksum: "8322f0c560da60399baf59f0364e8125e836384cd8b4d9795bd1c114d4d7d514",
statements: [`ALTER TABLE session_tape ADD COLUMN IF NOT EXISTS attachments TEXT`],
},
{
id: "sessions/store/0008",
expectedChecksum: "4b32bec77fa021de97736856a70a45bf1e51e59e574987e0d044fe748ec4cf58",
statements: [
`ALTER TABLE session_tape ADD COLUMN IF NOT EXISTS display TEXT`,
`ALTER TABLE session_tape ADD COLUMN IF NOT EXISTS security_tainted BOOLEAN`,
`ALTER TABLE session_tape ADD COLUMN IF NOT EXISTS entry_created_at BIGINT`,
],
},
{
id: "sessions/store/0009",
expectedChecksum: "40558264e0589a236a8a5a688a42eb49acf8a774371b9e1c6a3e5e0a84065c7d",
statements: [
`CREATE TABLE IF NOT EXISTS session_entry_search(
session_id TEXT NOT NULL, seq INT NOT NULL, type TEXT NOT NULL,
author TEXT, text TEXT NOT NULL, created_at BIGINT NOT NULL,
search_tsv tsvector GENERATED ALWAYS AS (to_tsvector('simple', text)) STORED,
PRIMARY KEY(session_id, seq)
)`,
`CREATE INDEX IF NOT EXISTS session_entry_search_tsv
ON session_entry_search USING GIN (search_tsv)`,
],
},
{
id: "sessions/store/0010",
expectedChecksum: "29832445f9d3bfaf0d97f11b2748a1ca533ff57c6f4bd96c594e83a6ab4f7c9a",
statements: [
`SET LOCAL lock_timeout = '3s'`,
`DROP INDEX IF EXISTS sessions_by_activity`,
`ALTER TABLE sessions SET (fillfactor = 70)`,
],
},
{
id: "sessions/store/0011",
expectedChecksum: "be590472124ab471453cfbbd7d3c1a0c57f792c97704707fba3682f4183f870d",
statements: [
`SET LOCAL lock_timeout = '3s'`,
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS origin TEXT`,
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS origin_id TEXT`,
],
},
{
id: "sessions/store/0012",
expectedChecksum: "6f52591c733daffdda7aca112ad5a21cfec5c0c36f97838d74b107a79b7100bf",
statements: [
`SET LOCAL lock_timeout = '3s'`,
`CREATE INDEX IF NOT EXISTS session_llm_requests_screens
ON session_llm_requests(created_at DESC) WHERE step = -1`,
],
},
{
id: "sessions/store/0013",
expectedChecksum: "86c8d0eff24f8f2deab4f33eccff6d0206b3a9a64943a812eec0965203dd5262",
statements: [
`CREATE EXTENSION IF NOT EXISTS btree_gin`,
`CREATE INDEX IF NOT EXISTS session_entries_session_search_tsv
ON session_entries USING GIN (session_id, search_tsv)
WHERE type IN ('user', 'assistant', 'text')`,
`CREATE INDEX IF NOT EXISTS session_entry_search_session_tsv
ON session_entry_search USING GIN (session_id, search_tsv)`,
],
},
{
id: "sessions/store/0014-search-write-through-v1",
statements: [
`SET LOCAL lock_timeout = '3s'`,
`CREATE OR REPLACE FUNCTION sync_session_entry_search() RETURNS trigger
LANGUAGE plpgsql AS $sync_session_entry_search$
DECLARE body text;
BEGIN
IF TG_OP = 'DELETE' THEN
DELETE FROM session_entry_search WHERE session_id = OLD.session_id AND seq = OLD.seq;
RETURN OLD;
END IF;
IF TG_OP = 'UPDATE' AND (OLD.session_id, OLD.seq) IS DISTINCT FROM (NEW.session_id, NEW.seq) THEN
DELETE FROM session_entry_search WHERE session_id = OLD.session_id AND seq = OLD.seq;
END IF;
IF NEW.type IN ('user', 'assistant', 'text') THEN
body := entry_search_text(NEW.payload);
END IF;
IF body IS NULL OR btrim(body) = '' THEN
DELETE FROM session_entry_search WHERE session_id = NEW.session_id AND seq = NEW.seq;
ELSE
INSERT INTO session_entry_search(session_id, seq, type, author, text, created_at)
VALUES (NEW.session_id, NEW.seq, NEW.type,
CASE WHEN NEW.type = 'user' THEN
(SELECT CASE WHEN json_typeof(j -> 'name') = 'string' THEN j ->> 'name' END
FROM (SELECT safe_json(replace(NEW.payload, '\\u0000', '')) AS j) _) END,
body, NEW.created_at)
ON CONFLICT (session_id, seq) DO UPDATE
SET type = EXCLUDED.type, author = EXCLUDED.author, text = EXCLUDED.text,
created_at = EXCLUDED.created_at;
END IF;
RETURN NEW;
END $sync_session_entry_search$`,
`CREATE TRIGGER session_entries_search_write_through
AFTER INSERT OR UPDATE OF payload, type, created_at, session_id, seq OR DELETE ON session_entries
FOR EACH ROW EXECUTE FUNCTION sync_session_entry_search()`,
],
},
{
id: "sessions/store/0015-search-backfill-v1",
statements: [
`WITH missing AS MATERIALIZED (
SELECT e.session_id, e.seq, e.type,
CASE WHEN e.type = 'user' THEN
(SELECT CASE WHEN json_typeof(j -> 'name') = 'string' THEN j ->> 'name' END
FROM (SELECT safe_json(replace(e.payload, '\\u0000', '')) AS j) _) END AS author,
entry_search_text(e.payload) AS text, e.created_at
FROM session_entries e
WHERE e.type IN ('user', 'assistant', 'text')
AND NOT EXISTS (SELECT 1 FROM session_entry_search s WHERE s.session_id = e.session_id AND s.seq = e.seq)
FOR SHARE OF e
)
INSERT INTO session_entry_search(session_id, seq, type, author, text, created_at)
SELECT session_id, seq, type, author, text, created_at FROM missing
WHERE COALESCE(btrim(text), '') <> ''
ON CONFLICT (session_id, seq) DO NOTHING`,
],
},
{
id: "sessions/store/0016-tape-source-role",
statements: [
`SET LOCAL lock_timeout = '3s'`,
`ALTER TABLE session_tape ADD COLUMN IF NOT EXISTS source_role TEXT`,
],
},
{
id: "sessions/store/0017-transcript-entries",
statements: [
`CREATE INDEX IF NOT EXISTS session_tape_transcript_entries
ON session_tape(session_id, entry_seq DESC, seq DESC)
WHERE kind = 'annotation' AND safe_json(payload)->>'event' = 'transcript_entry'`,
`CREATE OR REPLACE VIEW session_transcript_entries AS
SELECT DISTINCT ON (session_id, entry_seq)
session_id, entry_seq AS seq,
(safe_json(payload)->'entry'->>'parentSeq')::int AS parent_seq,
safe_json(payload)->'entry'->>'type' AS type,
(safe_json(payload)->'entry'->'payload')::text AS payload,
scope_label,
(safe_json(payload)->'entry'->>'at')::bigint AS created_at
FROM session_tape t
WHERE kind = 'annotation' AND safe_json(payload)->>'event' = 'transcript_entry'
ORDER BY t.session_id, t.entry_seq DESC, t.seq DESC`,
],
},
{
id: "sessions/store/0017-subagent-parentage",
statements: [
`SET LOCAL lock_timeout = '3s'`,
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS parent_session_id TEXT`,
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS spawn_meta TEXT`,
`CREATE INDEX IF NOT EXISTS idx_sessions_parent_session_id
ON sessions(parent_session_id) WHERE parent_session_id IS NOT NULL`,
],
},
{
id: "sessions/store/0016-status",
statements: ["ALTER TABLE sessions ADD COLUMN IF NOT EXISTS status JSONB"],
},
{
id: "sessions/store/0018-llm-requests-created-at",
statements: [
`SET LOCAL lock_timeout = '3s'`,
`CREATE INDEX IF NOT EXISTS session_llm_requests_created_at
ON session_llm_requests(created_at)`,
],
},
{
id: "sessions/store/0019-participant-activity-indexes",
statements: [
`CREATE INDEX CONCURRENTLY IF NOT EXISTS participants_by_principal
ON participants(principal_id, session_id)`,
`CREATE INDEX CONCURRENTLY IF NOT EXISTS session_entries_user_activity
ON session_entries(session_id, created_at DESC) WHERE type = 'user'`,
],
},
{
id: "sessions/store/0020-spend-usage-json",
statements: [
`CREATE OR REPLACE FUNCTION spend_usage_json(t text) RETURNS jsonb
LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $spend_usage_json$
SELECT CASE WHEN pg_input_is_valid(j ->> 'costUsd', 'double precision') IS NOT FALSE
AND pg_input_is_valid(j ->> 'input', 'bigint') IS NOT FALSE
AND pg_input_is_valid(j ->> 'output', 'bigint') IS NOT FALSE
AND pg_input_is_valid(j ->> 'cacheRead', 'bigint') IS NOT FALSE
AND pg_input_is_valid(j ->> 'cacheWrite', 'bigint') IS NOT FALSE THEN j END
FROM (SELECT CASE WHEN pg_input_is_valid(t, 'jsonb') THEN t::jsonb END AS j OFFSET 0) parsed
$spend_usage_json$`,
],
},
{
id: "sessions/store/0020-spend-usage-json-size",
statements: [
`CREATE OR REPLACE FUNCTION spend_usage_json(t text) RETURNS jsonb
LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $spend_usage_json$
SELECT CASE WHEN pg_input_is_valid(j ->> 'costUsd', 'double precision') IS NOT FALSE
AND pg_input_is_valid(j ->> 'input', 'bigint') IS NOT FALSE
AND pg_input_is_valid(j ->> 'output', 'bigint') IS NOT FALSE
AND pg_input_is_valid(j ->> 'cacheRead', 'bigint') IS NOT FALSE
AND pg_input_is_valid(j ->> 'cacheWrite', 'bigint') IS NOT FALSE THEN j END
FROM (SELECT CASE WHEN octet_length(t) > 2000 THEN NULL
WHEN pg_input_is_valid(t, 'jsonb') THEN t::jsonb END AS j OFFSET 0) parsed
$spend_usage_json$`,
],
},
{
id: "sessions/store/0020-spend-usage-json-size-plpgsql",
statements: [
`CREATE OR REPLACE FUNCTION spend_usage_json(t text) RETURNS jsonb
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS $spend_usage_json$
DECLARE j jsonb;
BEGIN
IF t IS NULL OR octet_length(t) > 2000 OR NOT pg_input_is_valid(t, 'jsonb') THEN
RETURN NULL;
END IF;
j := t::jsonb;
IF pg_input_is_valid(j ->> 'costUsd', 'double precision') IS NOT FALSE
AND pg_input_is_valid(j ->> 'input', 'bigint') IS NOT FALSE
AND pg_input_is_valid(j ->> 'output', 'bigint') IS NOT FALSE
AND pg_input_is_valid(j ->> 'cacheRead', 'bigint') IS NOT FALSE
AND pg_input_is_valid(j ->> 'cacheWrite', 'bigint') IS NOT FALSE THEN
RETURN j;
END IF;
RETURN NULL;
END
$spend_usage_json$`,
],
},
{
id: "sessions/store/0021-spend-covering-index",
statements: [
`CREATE INDEX CONCURRENTLY IF NOT EXISTS session_llm_requests_spend
ON session_llm_requests(created_at,
((spend_usage_json(usage_json) ->> 'costUsd')::double precision),
((spend_usage_json(usage_json) ->> 'input')::bigint),
((spend_usage_json(usage_json) ->> 'output')::bigint),
((spend_usage_json(usage_json) ->> 'cacheRead')::bigint),
((spend_usage_json(usage_json) ->> 'cacheWrite')::bigint))
INCLUDE (session_id, model, usage_json)
WHERE usage_json IS NOT NULL AND ${SPEND_INDEXABLE}`,
`CREATE INDEX CONCURRENTLY IF NOT EXISTS session_llm_requests_spend_wide
ON session_llm_requests(created_at)
WHERE usage_json IS NOT NULL AND NOT ${SPEND_INDEXABLE}`,
],
},
{
id: "sessions/store/0022-spend-days",
statements: [
`SET LOCAL lock_timeout = '3s'`,
`CREATE TABLE session_spend_days(day BIGINT PRIMARY KEY, rows JSONB NOT NULL, updated_at BIGINT NOT NULL)`,
`CREATE TABLE session_spend_dirty(
day BIGINT, session_id TEXT, writer xid8 NOT NULL DEFAULT pg_current_xact_id(),
UNIQUE(day, writer), CHECK (num_nonnulls(day, session_id) = 1)
)`,
`CREATE OR REPLACE FUNCTION invalidate_request_spend() RETURNS trigger LANGUAGE plpgsql AS $invalidate_request_spend$
BEGIN
INSERT INTO session_spend_dirty(day)
SELECT DISTINCT floor(at::numeric / 86400000)::bigint
FROM (VALUES
(CASE WHEN TG_OP <> 'INSERT' AND OLD.usage_json IS NOT NULL THEN OLD.created_at END),
(CASE WHEN TG_OP <> 'DELETE' AND NEW.usage_json IS NOT NULL THEN NEW.created_at END)
) changed(at) WHERE at IS NOT NULL
ON CONFLICT DO NOTHING;
RETURN NULL;
END $invalidate_request_spend$`,
`CREATE TRIGGER session_llm_requests_spend_dirty
AFTER INSERT OR UPDATE OF created_at, session_id, model, usage_json OR DELETE ON session_llm_requests
FOR EACH ROW EXECUTE FUNCTION invalidate_request_spend()`,
`CREATE OR REPLACE FUNCTION invalidate_session_spend() RETURNS trigger LANGUAGE plpgsql AS $invalidate_session_spend$
BEGIN
IF TG_OP = 'UPDATE' AND ROW(OLD.id, OLD.scope_id, OLD.origin, OLD.thread_ref, OLD.parent_session_id)
IS NOT DISTINCT FROM ROW(NEW.id, NEW.scope_id, NEW.origin, NEW.thread_ref, NEW.parent_session_id) THEN
RETURN NULL;
END IF;
INSERT INTO session_spend_dirty(session_id)
SELECT DISTINCT id FROM (VALUES (OLD.id), (NEW.id)) changed(id) WHERE id IS NOT NULL;
RETURN NULL;
END $invalidate_session_spend$`,
`CREATE TRIGGER sessions_spend_dirty
AFTER INSERT OR UPDATE OF id, scope_id, origin, thread_ref, parent_session_id OR DELETE ON sessions
FOR EACH ROW EXECUTE FUNCTION invalidate_session_spend()`,
],
},
{
id: "sessions/store/0023-spend-days-backfill",
statements: [
`INSERT INTO session_spend_dirty(day)
SELECT DISTINCT floor(created_at::numeric / 86400000)::bigint
FROM session_llm_requests WHERE usage_json IS NOT NULL
ON CONFLICT DO NOTHING`,
],
},
{
id: "sessions/store/0024-user-previews",
statements: [
`SET LOCAL lock_timeout = '3s'`,
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS first_user_preview TEXT`,
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS last_user_preview TEXT`,
],
},
{
id: "sessions/store/0025-org-activity-index",
statements: [
`CREATE INDEX CONCURRENTLY IF NOT EXISTS sessions_by_activity
ON sessions((COALESCE(last_activity, created_at)) DESC, id DESC)`,
],
},
{
id: "sessions/store/0026-participant-read-at",
statements: [
`ALTER TABLE participants ADD COLUMN IF NOT EXISTS read_at BIGINT`,
`UPDATE participants SET read_at = (EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::bigint WHERE read_at IS NULL`,
`ALTER TABLE sessions DROP COLUMN IF EXISTS status`,
],
},
{
id: "sessions/store/0027-agent-reply-index",
statements: [
`CREATE INDEX CONCURRENTLY IF NOT EXISTS session_entries_agent_reply
ON session_entries(session_id, created_at DESC) WHERE type IN ('assistant', 'delivery')`,
],
},
{
id: "sessions/store/0028-drop-run-tasks",
statements: [`DROP TABLE IF EXISTS task_events`, `DROP TABLE IF EXISTS tasks`],
},
...LEGACY_TAPE_MIGRATIONS,
{
id: "sessions/store/0032-session-goals",
statements: [
`SET LOCAL lock_timeout = '3s'`,
`CREATE TABLE IF NOT EXISTS session_goals(
session_id TEXT PRIMARY KEY REFERENCES sessions(id) ON DELETE CASCADE,
objective TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('active', 'paused', 'complete', 'cancelled')),
floor JSONB,
token_cap BIGINT,
tokens_used BIGINT NOT NULL DEFAULT 0,
active_ms BIGINT,
active_since TIMESTAMPTZ,
completion_note TEXT,
cancel_note TEXT,
governor JSONB,
pause_reason TEXT,
created_at TIMESTAMPTZ NOT NULL,
updated_at TIMESTAMPTZ NOT NULL
)`,
],
},
{
id: "sessions/store/0033-client-tools",
statements: [`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS client_tools JSONB`],
},
{
id: "sessions/store/0034-session-origin-backfill",
prepare: [SESSION_ORIGIN_BACKFILL_SQL],
statements: [],
},
...ITEMS_CUTOVER_MIGRATIONS,
{
id: "sessions/store/0037-drop-legacy-history",
statements: [
`SET LOCAL lock_timeout = '60s'`,
`DROP TRIGGER IF EXISTS session_entry_search ON items`,
`DROP FUNCTION IF EXISTS sync_item_search(), sync_session_entry_search()`,
`DROP VIEW IF EXISTS session_transcript_entries`,
`DROP TABLE IF EXISTS session_entry_search, legacy_session_entries, session_entries, session_tape,
legacy_tape_steps, legacy_turn_runs, legacy_tape_steps_progress, legacy_tape_skipped,
legacy_tape_writes, legacy_prep_runs`,
`DROP PROCEDURE IF EXISTS prepare_legacy_tape_steps(INT)`,
`DROP FUNCTION IF EXISTS legacy_tape_written(), legacy_tape_steps_batch(TEXT[], BIGINT),
entry_search_text(text), items_copy_payload(text), items_copy_payload(jsonb)`,
`DROP SCHEMA IF EXISTS items_copy_ops CASCADE`,
],
},
{
id: "sessions/store/0038-scoped-origin-pages",
statements: [
`CREATE INDEX CONCURRENTLY IF NOT EXISTS sessions_conversations_by_scope_activity
ON sessions(scope_id, (COALESCE(last_activity, created_at)) DESC, id DESC) WHERE origin = 'conversation'`,
`CREATE INDEX CONCURRENTLY IF NOT EXISTS sessions_other_background_by_scope_activity
ON sessions(scope_id, (COALESCE(last_activity, created_at)) DESC, id DESC) WHERE origin NOT IN ('conversation', 'cron')`,
],
},
{
id: "sessions/store/0039-items-call-id-digest-index",
statements: [
`CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS items_one_call_per_kind_digest
ON items(tenant_id, session_id, kind, md5(call_id)) WHERE call_id IS NOT NULL`,
],
},
{
id: "sessions/store/0040-items-call-id-digest-swap",
statements: [
`SET LOCAL lock_timeout = '3s'`,
`DROP INDEX IF EXISTS items_one_call_per_kind`,
`ALTER INDEX items_one_call_per_kind_digest RENAME TO items_one_call_per_kind`,
],
},
{
id: "sessions/store/0041-drop-inline-llm-request",
statements: [
`SET LOCAL lock_timeout = '15s'`,
`ALTER TABLE session_llm_requests DROP COLUMN IF EXISTS request`,
],
},
{
id: "sessions/store/0042-llm-prompt-envelopes-created-at",
statements: [
`CREATE INDEX CONCURRENTLY IF NOT EXISTS llm_prompt_envelopes_created_at
ON llm_prompt_envelopes(created_at)`,
],
},
{
id: "sessions/store/0043-last-user-activity",
statements: [
`SET LOCAL lock_timeout = '3s'`,
`ALTER TABLE sessions ADD COLUMN IF NOT EXISTS last_user_activity BIGINT`,
],
},
{
id: "sessions/store/0044-last-user-activity-backfill",
prepare: [LAST_USER_ACTIVITY_BACKFILL_SQL],
statements: [],
},
],
[
{
id: "sessions/maintenance/safe-jsonb-parallel",
statements: [
`DO $safe_jsonb_parallel$
BEGIN
IF to_regprocedure('safe_jsonb(text)') IS NOT NULL THEN
ALTER FUNCTION safe_jsonb(text) PARALLEL UNSAFE;
END IF;
END $safe_jsonb_parallel$`,
],
},
],
);
const lockSession = (client: PoolClient, sessionId: string) =>
client.query("SELECT pg_advisory_xact_lock(hashtextextended($1, 0))", [sessionId]);
const withLease = async <T>(lease: Lease, invalidMsg: string, fn: (client: PoolClient) => Promise<T>): Promise<T> =>
withPgTransaction(await pool(), async (client) => {
await lockSession(client, lease.sessionId);
const held = await client.query("SELECT token, expires_at FROM session_leases WHERE session_id = $1 FOR UPDATE", [
lease.sessionId,
]);
if (held.rows[0]?.token !== lease.token || Number(held.rows[0]!.expires_at) <= now()) throw new Error(invalidMsg);
await client.query("UPDATE session_leases SET expires_at = $2 WHERE session_id = $1", [
lease.sessionId,
now() + leaseTtlMs,
]);
return fn(client);
});
const insertEntries = async (
client: PoolClient,
sessionId: string,
entries: readonly NewEntry[],
turnId?: string,
): Promise<SessionEntry[]> => {
if (!entries.length) return [];
const max = await client.query(
`SELECT GREATEST(
COALESCE((SELECT MAX(seq) FROM items WHERE tenant_id = ${TENANT} AND session_id = $1), -1) + 1,
COALESCE((SELECT messages FROM sessions WHERE id = $1), 0)) AS n`,
[sessionId],
);
const startSeq = Number(max.rows[0]!.n);
const createdAt = now();
const items = entries.map((entry, index) => {
const seq = startSeq + index;
const item = itemFromEntry(entry.type, JSON.parse(jsonbSafeStringify(entry.payload ?? null)), {
turnId: turnId ?? null,
author: entry.author ?? null,
});
return {
...item,
seq,
scopeLabel: entry.scopeLabel as ScopeId,
};
});
for (const item of items) {
if (item.kind !== "Retraction" || typeof item.body.ts !== "string") continue;
const inBatch = items.findLast(
(other) => other.seq < item.seq && other.kind === "UserMessage" && other.externalId === item.body.ts,
);
const {
rows: [prior],
} = await client.query(
`SELECT max(seq) AS seq FROM items WHERE tenant_id=${TENANT} AND session_id=$1 AND external_id=$2 AND seq<$3`,
[sessionId, item.body.ts, item.seq],
);
item.targetSeq = inBatch?.seq ?? (prior.seq == null ? null : Number(prior.seq));
}
const full = items.map((item) => entryFromItem({ sessionId, createdAt, ...item }));
const userTurns = full.filter((entry) => entry.type === "user" && !isOverheardEntry(entry));
const firstUser = userTurns[0];
const lastUser = userTurns[userTurns.length - 1];
await client
.query(
`INSERT INTO items(tenant_id, session_id, seq, ${ITEM_COLUMNS.map(([column]) => column).join(",")}, created_at)
SELECT ${TENANT}, $1, i.seq, ${ITEM_COLUMNS.map(([column, key]) => (column === "blob_sha" ? `decode(i."${key}", 'hex')` : `i."${key}"`)).join(",")}, ${tsOf("$3::bigint")}
FROM jsonb_to_recordset($2::jsonb) AS i(seq bigint, ${ITEM_COLUMNS.map(([, key, type]) => `"${key}" ${type}`).join(",")})
ORDER BY i.seq`,
[sessionId, jsonbStringify(items), createdAt],
)
.catch((err: unknown) => {
const e = err as { code?: unknown; constraint?: unknown };
if (e?.code !== "23505" || e.constraint !== "items_one_call_per_kind") throw err;
throw new DuplicateCallError(
sessionId,
items.flatMap((item) => (item.callId ? [item.callId] : [])),
{ cause: err },
);
});
await client.query(
`UPDATE sessions
SET last_activity = CASE WHEN last_activity >= $2::bigint - ${LAST_ACTIVITY_DEBOUNCE_MS}
THEN last_activity
ELSE GREATEST(COALESCE(last_activity, 0), $2::bigint) END,
messages = $3,
turns = CASE WHEN turns IS NULL OR messages IS DISTINCT FROM $5
THEN (SELECT COUNT(*) FROM items t WHERE t.tenant_id = ${TENANT} AND t.session_id = $1 AND ${userTurn("t")})
ELSE turns + $4 END,
first_user_preview = CASE WHEN first_user_preview IS NULL AND turns = 0 AND messages = $5
THEN $6 ELSE first_user_preview END,
last_user_preview = COALESCE($7, last_user_preview),
last_user_activity = CASE WHEN $8::boolean THEN GREATEST(COALESCE(last_user_activity, 0), $2::bigint)
ELSE last_user_activity END
WHERE id = $1`,
[
sessionId,
createdAt,
startSeq + full.length,
userTurns.length,
startSeq,
firstUser ? storedPreview(firstUser.payload, FIRST_PREVIEW_LEN) : null,
lastUser ? storedPreview(lastUser.payload, LAST_PREVIEW_LEN) : null,
items.some((item) => USER_ITEM_KINDS.includes(item.kind)),
],
);
return full;
};
return {
leaseTtlMs,
async getOrCreateByThread(threadRef, type, scopeId, channelName, surface, clientTools): Promise<Session> {
const heal = async (row: Record<string, unknown>): Promise<Session> => {
const s = rowToSession(row);
if (channelName && s.channelName !== channelName) {
await q("UPDATE sessions SET channel_name = $2 WHERE id = $1", [s.id, channelName]);
s.channelName = channelName;
}
if (surface && !s.surface) {
await q("UPDATE sessions SET surface = $2 WHERE id = $1", [s.id, surface]);
s.surface = surface;
}
return s;
};
const existing = await q("SELECT * FROM sessions WHERE thread_ref = $1", [threadRef]);
if (existing[0]) return heal(existing[0]);
const session: Session = {
id: randomUUID(),
type,
scopeId,
threadRef,
createdAt: now(),
...(clientTools?.length ? { clientTools: structuredClone(clientTools) } : {}),
...(channelName ? { channelName } : {}),
...(surface ? { surface } : {}),
};
await q(
"INSERT INTO sessions(id, type, scope_id, thread_ref, created_at, channel_name, surface, last_activity, messages, turns, origin, origin_id, client_tools) VALUES ($1,$2,$3,$4,$5,$6,$7,$5,0,0,$8,$9,$10::jsonb) ON CONFLICT (thread_ref) DO NOTHING",
[
session.id,
session.type,
session.scopeId,
session.threadRef,
session.createdAt,
channelName ?? null,
surface ?? null,
sessionOrigin(threadRef),
cronIdOf(threadRef),
clientTools?.length ? JSON.stringify(clientTools) : null,
],
);
const rows = await q("SELECT * FROM sessions WHERE thread_ref = $1", [threadRef]);
return heal(rows[0]!);
},
async getByThread(threadRef): Promise<Session | null> {
const rows = await q("SELECT * FROM sessions WHERE thread_ref = $1", [threadRef]);
return rows[0] ? rowToSession(rows[0]) : null;
},
async get(id): Promise<Session | null> {
const rows = await q("SELECT * FROM sessions WHERE id = $1", [id]);
return rows[0] ? rowToSession(rows[0]) : null;
},
async updateTitle(sessionId, title): Promise<void> {
await q("UPDATE sessions SET title = $2 WHERE id = $1", [sessionId, title]);
},
async updateForkProvenance(sessionId, provenance): Promise<void> {
await q(
"UPDATE sessions SET forked_from_session_id = $2, forked_from_title = $3, fork_boundary_seq = $4 WHERE id = $1",
[sessionId, provenance.forkedFrom.sessionId, provenance.forkedFrom.title ?? null, provenance.forkBoundarySeq],
);
},
async setParentSession(sessionId, parentSessionId): Promise<void> {
await q("UPDATE sessions SET parent_session_id = $2 WHERE id = $1", [sessionId, parentSessionId]);
},
async setSpawnMeta(sessionId, meta): Promise<void> {
await q("UPDATE sessions SET spawn_meta = $2 WHERE id = $1", [sessionId, JSON.stringify(meta)]);
},
async childrenOf(parentSessionId): Promise<Session[]> {
const rows = await q("SELECT * FROM sessions WHERE parent_session_id = $1 ORDER BY created_at", [
parentSessionId,
]);
return rows.map(rowToSession);
},
async acquireLease(sessionId, holder): Promise<LeaseAttempt> {
const token = randomUUID();
const t = now();
return withPgTransaction(await pool(), async (client) => {
await lockSession(client, sessionId);
for (;;) {
const granted = await client.query(
`INSERT INTO session_leases(session_id, token, expires_at, holder, acquired_at)
SELECT $1, $2, $3, $5, $4 WHERE EXISTS (SELECT 1 FROM sessions WHERE id = $1)
ON CONFLICT (session_id) DO UPDATE
SET token = $2, expires_at = $3, holder = $5, acquired_at = $4
WHERE session_leases.expires_at <= $4
RETURNING token`,
[sessionId, token, t + leaseTtlMs, t, holder ?? null],
);
if (granted.rows[0]) return { lease: { sessionId, token } };
const held = await client.query(
"SELECT expires_at, holder, acquired_at FROM session_leases WHERE session_id = $1",
[sessionId],
);
const row = held.rows[0];
if (row) {
return {
lease: null,
...(row.holder != null ? { heldBy: row.holder as LeaseHolder } : {}),
...(row.acquired_at != null ? { heldSince: Number(row.acquired_at) } : {}),
heldUntil: Number(row.expires_at),
};
}
const exists = await client.query("SELECT 1 FROM sessions WHERE id = $1", [sessionId]);
if (!exists.rows[0]) return { lease: null };
}
});
},
async peekLease(sessionId): Promise<LeasePeek | null> {
const rows = await q("SELECT holder, expires_at FROM session_leases WHERE session_id = $1", [sessionId]);
const row = rows[0];
if (!row) return null;
return {
...(row.holder != null ? { holder: row.holder as LeaseHolder } : {}),
heldUntil: Number(row.expires_at),
};
},
async renewLease(lease): Promise<boolean> {
const t = now();
const rows = await q(
"UPDATE session_leases SET expires_at = $3 WHERE session_id = $1 AND token = $2 AND expires_at > $4 RETURNING token",
[lease.sessionId, lease.token, t + leaseTtlMs, t],
);
return rows.length > 0;
},
async releaseLease(lease): Promise<void> {
await q("DELETE FROM session_leases WHERE session_id = $1 AND token = $2", [lease.sessionId, lease.token]);
},
async forceReleaseLease(sessionId): Promise<void> {
await q("DELETE FROM session_leases WHERE session_id = $1", [sessionId]);
},
async append(lease, entry: NewEntry): Promise<SessionEntry> {
return (await this.appendMany(lease, [entry]))[0]!;
},
async appendMany(lease, entries: readonly NewEntry[]): Promise<SessionEntry[]> {
return withLease(lease, "append without a valid session lease", (client) =>
insertEntries(client, lease.sessionId, entries, lease.turnId),
);
},
async countEntriesBefore(sessionId, beforeSeq, principalId) {
const rows =
principalId === undefined
? await q(
`SELECT count(*)::int AS n FROM items i WHERE i.tenant_id = ${TENANT} AND i.session_id = $1 AND i.seq < $2 AND ${transcriptItemPredicate("i")}`,
[sessionId, beforeSeq],
)
: await q(
`SELECT count(*)::int AS n FROM items i
JOIN participants p ON p.session_id = i.session_id AND p.principal_id = $3
WHERE i.tenant_id = ${TENANT} AND i.session_id = $1 AND i.seq < $2 AND ${transcriptItemPredicate("i", "p")} AND ${withinParticipantWindow("i", "p")}`,
[sessionId, beforeSeq, principalId],
);
return Number(rows[0]?.n ?? 0);
},
async latestEntrySeq(sessionId): Promise<number> {
const rows = await q(
`SELECT COALESCE(MAX(seq), -1) AS n FROM items WHERE tenant_id = ${TENANT} AND session_id = $1`,
[sessionId],
);
return Number(rows[0]?.n ?? -1);
},
async goals(sessionIds) {
const found = new Map<string, GoalRecord>();
if (!sessionIds.length) return found;
const rows = await q("SELECT * FROM session_goals WHERE session_id = ANY($1::text[])", [[...sessionIds]]);
for (const row of rows) found.set(row.session_id as string, rowToGoal(row));
return found;
},
async setGoal(lease, goal, notices = []) {
await withLease(lease, "set goal without a valid session lease", async (client) => {
await insertEntries(client, lease.sessionId, notices, lease.turnId);
await client.query(
`INSERT INTO session_goals(session_id, objective, status, floor, token_cap, tokens_used, active_ms,
active_since, completion_note, cancel_note, governor, pause_reason,
created_at, updated_at)
VALUES ($1, $2, $3, $4::jsonb, $5, $6, $7, $8, $9, $10, $11::jsonb, $12, $13, $14)
ON CONFLICT (session_id) DO UPDATE SET
objective = EXCLUDED.objective, status = EXCLUDED.status, floor = EXCLUDED.floor,
token_cap = EXCLUDED.token_cap, tokens_used = EXCLUDED.tokens_used, active_ms = EXCLUDED.active_ms,
active_since = EXCLUDED.active_since, completion_note = EXCLUDED.completion_note,
cancel_note = EXCLUDED.cancel_note, governor = EXCLUDED.governor, pause_reason = EXCLUDED.pause_reason,
created_at = EXCLUDED.created_at, updated_at = EXCLUDED.updated_at`,
[
lease.sessionId,
goal.objective,
goal.status,
goal.floor ? JSON.stringify(goal.floor) : null,
goal.capTokens ?? null,
Math.floor(goal.tokensUsed),
goal.activeMs === undefined ? null : Math.round(goal.activeMs),
goal.activeSince === undefined ? null : new Date(goal.activeSince),
goal.completionNote ?? null,
goal.cancelNote ?? null,
goal.governor ? JSON.stringify(goal.governor) : null,
goal.pauseReason ?? null,
new Date(goal.createdAt),
new Date(goal.updatedAt),
],
);
});
},
async hasDeliveryEntry(sessionId, deliveryKey, slackTs) {
const rows = await q(
`SELECT 1 FROM items WHERE tenant_id = ${TENANT} AND session_id = $1 AND kind = 'UserMessage'
AND (body ->> 'deliveryKey' = $2 OR external_id = $3) LIMIT 1`,
[sessionId, deliveryKey, slackTs ?? null],
);
return rows.length > 0;
},
async getEntries(sessionId, opts?: GetEntriesOptions): Promise<SessionEntry[]> {
const params: unknown[] = [sessionId, opts?.sinceSeq ?? 0];
let sql = `SELECT ${ITEM_FIELDS} FROM ${ITEM_FROM} WHERE i.tenant_id = ${TENANT} AND i.session_id = $1 AND i.seq >= $2`;
if (opts?.transcript) sql += ` AND ${transcriptItemPredicate("i")}`;
if (opts?.beforeSeq !== undefined) {
params.push(opts.beforeSeq);
sql += ` AND i.seq < $${params.length}`;
}
if (opts?.turnId !== undefined) {
params.push(opts.turnId);
sql += ` AND i.turn_id = $${params.length}::uuid`;
}
sql += " ORDER BY i.seq DESC";
if (opts?.limit !== undefined) {
params.push(opts.limit);
sql += ` LIMIT $${params.length}`;
}
return (await q(sql, params)).map(rowToEntry).reverse();
},
async getContextWindow(sessionId) {
const [meta, summary] = await Promise.all([
q(`SELECT count(*)::int AS total FROM items i WHERE i.tenant_id = ${TENANT} AND i.session_id = $1`, [
sessionId,
]),
q(
`SELECT through_seq AS through FROM items
WHERE tenant_id = ${TENANT} AND session_id = $1 AND kind = 'ContextCompaction'
AND through_seq IS NOT NULL AND text IS NOT NULL ORDER BY seq DESC LIMIT 1`,
[sessionId],
),
]);
const sinceSeq = summary[0]?.through == null ? 0 : Number(summary[0].through) + 1;
const rows = await q(
`SELECT ${ITEM_FIELDS} FROM ${ITEM_FROM} WHERE i.tenant_id = ${TENANT} AND i.session_id = $1 AND i.seq >= $2 ORDER BY i.seq`,
[sessionId, sinceSeq],
);
return { entries: rows.map(rowToEntry), totalEntries: Number(meta[0]?.total ?? 0) };
},
async getEntry(sessionId, seq): Promise<SessionEntry | undefined> {
const rows = await q(
`SELECT ${ITEM_FIELDS} FROM ${ITEM_FROM} WHERE i.tenant_id = ${TENANT} AND i.session_id = $1 AND i.seq = $2`,
[sessionId, seq],
);
return rows[0] ? rowToEntry(rows[0]) : undefined;
},
async recordLlmRequest(sessionId, rec: NewLlmRequest, signal?: AbortSignal): Promise<LlmRequestRecord> {
const envelope = promptEnvelopeBody(rec.promptEnvelope);
if (envelope) {
await q(
`INSERT INTO llm_prompt_envelopes(hash, body, created_at) VALUES ($1,$2,$3)
ON CONFLICT (hash) DO UPDATE SET created_at = EXCLUDED.created_at
WHERE llm_prompt_envelopes.created_at < EXCLUDED.created_at - ${ENVELOPE_TOUCH_MS}`,
[envelope.hash, envelope.body, now()],
{ signal },
);
}
const full: LlmRequestRecord = {
id: randomUUID(),
sessionId,
turnSeq: rec.turnSeq,
step: rec.step,
model: rec.model,
scopeLabel: rec.scopeLabel as ScopeId,
createdAt: now(),
promptHash: envelope?.hash ?? null,
...(rec.promptEnvelope !== undefined ? { promptEnvelope: rec.promptEnvelope } : {}),
truncated: rec.truncated ?? false,
ttftMs: rec.ttftMs ?? null,
durationMs: rec.durationMs ?? null,
stepGapMs: rec.stepGapMs ?? null,
toolWallMs: rec.toolWallMs ?? null,
gapPhases: rec.gapPhases ?? null,
usage: rec.usage ?? null,
transport: rec.transport ?? null,
};
await q(
"INSERT INTO session_llm_requests(id, session_id, turn_seq, step, model, scope_label, prompt_hash, truncated, created_at, ttft_ms, duration_ms, step_gap_ms, tool_wall_json, usage_json, transport_json, gap_phases_json) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16)",
[
full.id,
full.sessionId,
full.turnSeq,
full.step,
full.model,
full.scopeLabel,
full.promptHash,
full.truncated,
full.createdAt,
full.ttftMs,
full.durationMs,
full.stepGapMs,
full.toolWallMs ? JSON.stringify(full.toolWallMs) : null,
full.usage ? JSON.stringify(full.usage) : null,
full.transport ? JSON.stringify(full.transport) : null,
full.gapPhases ? JSON.stringify(full.gapPhases) : null,
],
{ signal },
);
return full;
},
async listLlmRequests(sessionId, opts): Promise<LlmRequestRecord[]> {
const cols = opts?.omitRequest
? "r.id, r.session_id, r.turn_seq, r.step, r.model, r.scope_label, r.created_at, r.truncated, r.prompt_hash, r.ttft_ms, r.duration_ms, r.step_gap_ms, r.tool_wall_json, r.usage_json, r.transport_json, r.gap_phases_json"
: "r.*, e.body AS prompt_body";
const from = opts?.omitRequest
? "session_llm_requests r"
: "session_llm_requests r LEFT JOIN llm_prompt_envelopes e ON e.hash = r.prompt_hash";
const conds = ["r.session_id = $1"];
const args: unknown[] = [sessionId];
const turnSeqs = opts?.turnSeqs;
if (turnSeqs) {
args.push(turnSeqs);
conds.push(
opts?.orphans
? `(r.turn_seq = ANY($${args.length}) OR r.turn_seq IS NULL)`
: `r.turn_seq = ANY($${args.length})`,
);
} else if (opts?.orphans) {
conds.push("r.turn_seq IS NULL");
}
const rows = await q(
`SELECT ${cols} FROM ${from} WHERE ${conds.join(" AND ")} ORDER BY r.created_at ASC, r.step ASC`,
args,
);
return rows.map(rowToLlmRequest);
},
async listScreenSamples(limit): Promise<ScreenSample[]> {
const wanted = Math.max(0, Math.trunc(limit));
const pageSize = Math.max(wanted, 100);
const samples: ScreenSample[] = [];
let beforeAt: number | null = null;
let beforeId: string | null = null;
while (samples.length < wanted) {
const rows = await q(
`SELECT r.id, r.session_id, r.scope_label, r.created_at, r.model, e.body AS prompt_body
FROM session_llm_requests r JOIN llm_prompt_envelopes e ON e.hash = r.prompt_hash
WHERE r.step = $1
AND ($3::bigint IS NULL OR (r.created_at, r.id) < ($3::bigint, $4::text))
ORDER BY r.created_at DESC, r.id DESC
LIMIT $2`,
[SECURITY_SCREEN_STEP, pageSize, beforeAt, beforeId],
);
for (const r of rows) {
const payload = screenPayloadFromEnvelope(JSON.parse(r.prompt_body as string));
if (!payload) continue;
samples.push({
id: r.id as string,
sessionId: r.session_id as string,
scopeLabel: r.scope_label as ScopeId,
createdAt: Number(r.created_at),
model: r.model as string,
payload,
});
if (samples.length === wanted) return samples;
}
if (rows.length < pageSize) break;
const last = rows.at(-1)!;
beforeAt = Number(last.created_at);
beforeId = last.id as string;
}
return samples;
},
async purgeLlmCaptures(cutoff, opts) {
const deadline = opts?.deadline ?? Infinity;
let envelopes = 0;
while (Date.now() < deadline && !opts?.stop?.()) {
const deleted = await query(
`DELETE FROM llm_prompt_envelopes
WHERE hash IN (SELECT hash FROM llm_prompt_envelopes WHERE created_at < $1
ORDER BY created_at LIMIT $2 FOR UPDATE SKIP LOCKED)
AND created_at < $1`,
[cutoff, LLM_CAPTURE_PURGE_BATCH],
);
envelopes += deleted.rowCount;
if (deleted.rowCount < LLM_CAPTURE_PURGE_BATCH) break;
}
return envelopes;
},
async addParticipant(sessionId, principalId, title, opts): Promise<void> {
const includeHistory = opts?.includeHistory === true;
await q(
`WITH boundary AS (
SELECT CASE WHEN $5 THEN 0
ELSE GREATEST(COALESCE(MAX(seq) + 1, 0),
COALESCE((SELECT messages FROM sessions WHERE id = $1), 0)) END AS seq
FROM items WHERE tenant_id = ${TENANT} AND session_id = $1
)
INSERT INTO participants(session_id, principal_id, valid_from, valid_to, valid_from_seq, valid_to_seq, title)
SELECT $1,$2,$3,NULL,boundary.seq,NULL,$4 FROM boundary
ON CONFLICT (session_id, principal_id) DO UPDATE
SET valid_from = CASE
WHEN $5 THEN 0
WHEN participants.valid_to IS NULL THEN participants.valid_from
ELSE EXCLUDED.valid_from END,
valid_to = NULL,
valid_from_seq = CASE
WHEN $5 THEN 0
WHEN participants.valid_to IS NULL THEN participants.valid_from_seq
ELSE EXCLUDED.valid_from_seq END,
valid_to_seq = NULL,
title = COALESCE(EXCLUDED.title, participants.title)
WHERE participants.valid_to IS NOT NULL
OR EXCLUDED.title IS NOT NULL
OR ($5 AND (participants.valid_from_seq IS DISTINCT FROM 0 OR participants.valid_from <> 0))`,
[sessionId, principalId, includeHistory ? 0 : now(), title ?? null, includeHistory],
);
},
async removeParticipant(sessionId, principalId): Promise<void> {
await q(
`UPDATE participants SET valid_to = $3, valid_to_seq = (SELECT COALESCE(MAX(seq) + 1, 0) FROM items WHERE tenant_id = ${TENANT} AND session_id = $1) WHERE session_id = $1 AND principal_id = $2 AND valid_to IS NULL`,
[sessionId, principalId, now()],
);
},
async deleteSession(sessionId): Promise<void> {
await withPgTransaction(await pool(), async (client) => {
await lockSession(client, sessionId);
await client.query("DELETE FROM session_llm_requests WHERE session_id = $1", [sessionId]);
await client.query("DELETE FROM session_leases WHERE session_id = $1", [sessionId]);
await client.query("DELETE FROM participants WHERE session_id = $1", [sessionId]);
await client.query("DELETE FROM session_pins WHERE session_id = $1", [sessionId]);
await client.query("DELETE FROM sessions WHERE id = $1", [sessionId]);
});
},
async deleteSessionIfEmpty(sessionId): Promise<boolean> {
return withPgTransaction(await pool(), async (client) => {
await lockSession(client, sessionId);
const gone = await client.query(
`DELETE FROM sessions
WHERE id = $1
AND NOT EXISTS (SELECT 1 FROM items WHERE tenant_id = ${TENANT} AND session_id = $1)
AND NOT EXISTS (SELECT 1 FROM session_leases WHERE session_id = $1 AND expires_at > $2)`,
[sessionId, now()],
);
if (gone.rowCount === 0) return false;
await client.query("DELETE FROM session_llm_requests WHERE session_id = $1", [sessionId]);
await client.query("DELETE FROM session_leases WHERE session_id = $1", [sessionId]);
await client.query("DELETE FROM participants WHERE session_id = $1", [sessionId]);
await client.query("DELETE FROM session_pins WHERE session_id = $1", [sessionId]);
return true;
});
},
async listByParticipant(principalId, opts): Promise<Session[]> {
return participantSessions(principalId, opts);
},
async participantScopes(principalId): Promise<ParticipantScope[]> {
return participantScopes(principalId);
},
async listParticipantPage(principalId, query): Promise<ParticipantPage> {
return participantPage(principalId, query);
},
async descendantsOf(rootIds): Promise<SessionDescendant[]> {
return descendantsOf(rootIds);
},
async listDescendantsForParticipant(rootId, principalId, limit): Promise<Session[]> {
const ids = (await descendantsOf([rootId])).map((d) => d.id).slice(0, Math.max(0, Math.floor(limit)));
if (!ids.length) return [];
const rows = await q(
participantSessionsSql(" AND s.id = ANY($2::text[])", LIST_COLUMNS) + " ORDER BY s.created_at, s.id",
[principalId, ids],
);
return rows.map(rowToParticipantSession);
},
async getForParticipant(sessionId, principalId): Promise<Session | null> {
return participantSession(sessionId, principalId);
},
async updateParticipantView(sessionId, principalId, patch): Promise<void> {
if (patch.title !== undefined) {
await q("UPDATE participants SET title = $3 WHERE session_id = $1 AND principal_id = $2", [
sessionId,
principalId,
patch.title,
]);
}
if (patch.archived !== undefined) {
await q("UPDATE participants SET archived = $3 WHERE session_id = $1 AND principal_id = $2", [
sessionId,
principalId,
patch.archived,
]);
}
if (patch.pinned !== undefined) {
await q("UPDATE participants SET pinned = $3 WHERE session_id = $1 AND principal_id = $2", [
sessionId,
principalId,
patch.pinned,
]);
}
if (patch.color !== undefined) {
await q("UPDATE participants SET color = $3 WHERE session_id = $1 AND principal_id = $2", [
sessionId,
principalId,
patch.color,
]);
}
if (patch.readAt !== undefined) {
await q("UPDATE participants SET read_at = $3 WHERE session_id = $1 AND principal_id = $2", [
sessionId,
principalId,
patch.readAt,
]);
}
},
async addPin(sessionId, pin: NewSessionPin, maxPins?: number): Promise<SessionPin | null> {
const rec: SessionPin = { ...pin, id: randomUUID(), sessionId, createdAt: now() };
return withPgTransaction(await pool(), async (client) => {
await lockSession(client, sessionId);
const inserted = await client.query(
`INSERT INTO session_pins(id, session_id, text, entry_seq, added_by, created_at)
SELECT $1, $2, $3, $4, $5, $6
WHERE EXISTS (SELECT 1 FROM sessions WHERE id = $2)
AND ($7::int IS NULL OR (SELECT COUNT(*) FROM session_pins WHERE session_id = $2) < $7)
RETURNING id`,
[rec.id, sessionId, rec.text ?? null, rec.entrySeq ?? null, rec.addedBy, rec.createdAt, maxPins ?? null],
);
return inserted.rowCount ? rec : null;
});
},
async listPins(sessionId): Promise<SessionPin[]> {
const rows = await q(
"SELECT id, session_id, text, entry_seq, added_by, created_at FROM session_pins WHERE session_id = $1 ORDER BY created_at ASC, id ASC",
[sessionId],
);
return rows.map((r) => ({
id: r.id as string,
sessionId: r.session_id as string,
...(r.text != null ? { text: r.text as string } : {}),
...(r.entry_seq != null ? { entrySeq: Number(r.entry_seq) } : {}),
addedBy: r.added_by as string,
createdAt: Number(r.created_at),
}));
},
async removePin(sessionId, pinId): Promise<boolean> {
const res = await q("DELETE FROM session_pins WHERE session_id = $1 AND id = $2 RETURNING id", [
sessionId,
pinId,
]);
return res.length > 0;
},
async visibleEntries(sessionId, principalId, opts?: GetEntriesOptions): Promise<SessionEntry[]> {
const params: unknown[] = [sessionId, principalId, opts?.sinceSeq ?? 0];
let sql = `SELECT ${ITEM_FIELDS} FROM ${ITEM_FROM}
JOIN participants p ON p.session_id = i.session_id AND p.principal_id = $2
WHERE i.tenant_id = ${TENANT} AND i.session_id = $1 AND i.seq >= $3
AND ${withinParticipantWindow("i", "p")}`;
if (opts?.transcript) sql += ` AND ${transcriptItemPredicate("i", "p")}`;
if (opts?.beforeSeq !== undefined) {
params.push(opts.beforeSeq);
sql += ` AND i.seq < $${params.length}`;
}
sql += " ORDER BY i.seq DESC";
if (opts?.limit !== undefined) {
params.push(opts.limit);
sql += ` LIMIT $${params.length}`;
}
return (await q(sql, params)).map(rowToEntry).reverse();
},
async searchSessionTitles(principalId, query, limit = 20): Promise<Session[]> {
const needle = query.trim();
if (needle.length < 2) return [];
const like = `%${needle.replace(/[\\%_]/g, (c) => `\\${c}`)}%`;
const rows = await q(
participantSessionsSql(" AND COALESCE(p.title, s.title) ILIKE $2") +
" ORDER BY COALESCE(s.last_activity, s.created_at) DESC, s.id LIMIT $3",
[principalId, like, Math.max(1, Math.min(Math.floor(limit), 100))],
{ timeoutMs: SEARCH_TIMEOUT_MS },
);
return rows.map(rowToParticipantSession);
},
async searchEntries(principalId, query, limit = 40): Promise<EntrySearchHit[]> {
const ts = tsPrefixQuery(query);
if (!ts) return [];
const rows = await q(
`SELECT i.session_id, i.seq, ${searchTypeExpr("i")} AS type,
CASE WHEN i.kind IN ${USER_KINDS} AND jsonb_typeof(i.body -> 'name') = 'string'
THEN i.body ->> 'name' END AS author,
i.text, ${msOf("i.created_at")} AS created_at,
s.scope_id, COALESCE(p.title, s.title) AS title, s.channel_name, s.surface, p.archived
FROM items i
JOIN participants p ON p.session_id = i.session_id AND p.principal_id = $1
JOIN sessions s ON s.id = i.session_id
WHERE i.tenant_id = ${TENANT} AND i.search @@ to_tsquery('simple', $2)
AND ${SEARCHABLE_ITEM("i")}
AND ${withinParticipantWindow("i", "p")}
ORDER BY i.created_at DESC, i.session_id, i.seq DESC
LIMIT $3`,
[principalId, ts, Math.max(1, Math.min(limit, 200))],
{ timeoutMs: SEARCH_TIMEOUT_MS },
);
return rows.flatMap((r) => {
const text = (r.text as string | null) ?? "";
if (!text.trim()) return [];
return [
{
sessionId: r.session_id as string,
scopeId: r.scope_id as ScopeId,
...(r.title != null ? { title: r.title as string } : {}),
...(r.channel_name ? { channelName: r.channel_name as string } : {}),
...(r.surface ? { surface: r.surface as string } : {}),
...(r.archived ? { archived: true } : {}),
seq: Number(r.seq),
type: r.type as EntrySearchHit["type"],
...(r.author ? { author: r.author as string } : {}),
text,
createdAt: Number(r.created_at),
},
];
});
},
async scanAll(): Promise<Session[]> {
const rows = await q("SELECT * FROM sessions");
return rows.map(rowToSession);
},
async countSessions(): Promise<number> {
const rows = await q("SELECT COUNT(*) AS n FROM sessions");
return Number(rows[0]?.n ?? 0);
},
async listByScope(scope): Promise<Session[]> {
const rows = await q("SELECT * FROM sessions WHERE scope_id = $1 ORDER BY created_at DESC, id DESC", [scope]);
return rows.map(rowToSession);
},
async scopeHasSessions(scope): Promise<boolean> {
const rows = await q("SELECT EXISTS(SELECT 1 FROM sessions WHERE scope_id = $1) AS present", [scope]);
return Boolean(rows[0]?.present);
},
async sessionsByThreadRefs(threadRefs): Promise<SessionRef[]> {
if (threadRefs.length === 0) return [];
const rows = await q("SELECT id, thread_ref, scope_id, type, title FROM sessions WHERE thread_ref = ANY($1)", [
[...new Set(threadRefs)],
]);
return rows.map((r) => ({
id: r.id as string,
threadRef: r.thread_ref as string,
scopeId: r.scope_id as ScopeId,
type: r.type as SessionType,
title: (r.title as string | null) ?? null,
}));
},
async distinctScopes(): Promise<DistinctScope[]> {
const rows = await q("SELECT scope_id, MAX(channel_name) AS channel_name FROM sessions GROUP BY scope_id");
return rows.map((r) => ({
scopeId: r.scope_id as ScopeId,
...(r.channel_name != null ? { channelName: r.channel_name as string } : {}),
}));
},
async scopeSessionSummaries(scope, orgWide, page: SessionPage): Promise<SessionSummary[]> {
const options = { timeoutMs: ADMIN_READ_TIMEOUT_MS, ...(page.signal ? { signal: page.signal } : {}) };
const params: unknown[] = [orgWide, scope];
let categoryClause = "";
if (page.category === "background") categoryClause = ` AND ${isBackground("s")}`;
else if (page.category) categoryClause = ` AND NOT ${isBackground("s")}`;
const originClause = page.origin ? ` AND ${originFilterClause("s", page.origin)}` : "";
let cronClause = "";
if (page.cronId) {
params.push(page.cronId);
cronClause = ` AND s.origin_id = $${params.length}`;
}
let keysetClause = "";
if (page.before) {
params.push(page.before.lastActivity, page.before.id);
keysetClause = ` AND (${lastActivityExpr("s")}, s.id) < ($${params.length - 1}::bigint, $${params.length}::text)`;
}
params.push(page.limit);
const rows = await q(
`SELECT s.id, s.type, s.scope_id, s.thread_ref, s.origin, s.created_at, s.messages, s.turns,
${lastActivityExpr("s")} AS last_activity, s.first_user_preview, s.last_user_preview, s.title
FROM sessions s
WHERE ($1::boolean OR s.scope_id = $2)${categoryClause}${originClause}${cronClause}${keysetClause}
ORDER BY ${lastActivityExpr("s")} DESC, s.id DESC
LIMIT $${params.length}`,
params,
options,
);
return rows.map((r) => ({
id: r.id as string,
type: r.type as Session["type"],
origin: r.origin as SessionOrigin,
scopeId: r.scope_id as Session["scopeId"],
threadRef: r.thread_ref as Session["threadRef"],
turns: Number(r.turns ?? 0),
messages: Number(r.messages ?? 0),
lastActivity: Number(r.last_activity),
createdAt: Number(r.created_at),
firstMessage: ((r.first_user_preview ?? r.title ?? "") as string).trim(),
lastMessage: (r.last_user_preview as string | null) ?? "",
}));
},
async scopeConversationCount(scope): Promise<number> {
const rows = await q("SELECT COUNT(*) AS n FROM sessions WHERE scope_id = $1 AND origin = 'conversation'", [
scope,
]);
return Number(rows[0]?.n ?? 0);
},
async scopeActivity(): Promise<ScopeActivity[]> {
const rows = await q(
`SELECT scope_id,
COUNT(*) FILTER (WHERE origin = 'conversation') AS conversations,
COUNT(*) FILTER (WHERE origin = 'cron') AS crons,
COUNT(*) FILTER (WHERE origin NOT IN ('conversation', 'cron')) AS background,
MAX(${lastActivityExpr("sessions")}) AS last_activity
FROM sessions
GROUP BY scope_id`,
[],
{ timeoutMs: ADMIN_READ_TIMEOUT_MS },
);
return rows.map((r) => ({
scopeId: r.scope_id as ScopeId,
conversations: Number(r.conversations),
crons: Number(r.crons),
background: Number(r.background),
lastActivity: Number(r.last_activity),
}));
},
async attributedTurns(): Promise<AttributedTurn[]> {
const rows = await q(
`SELECT p.principal_id, e.session_id, (${msOf("e.created_at")} / 86400000)::bigint AS day,
COUNT(*) AS turns, ${msOf("MIN(e.created_at)")} AS first_at, ${msOf("MAX(e.created_at)")} AS last_at
FROM participants p
JOIN items e ON e.tenant_id = ${TENANT} AND e.session_id = p.session_id
WHERE ${userTurn("e")}
AND ${withinParticipantWindow("e", "p")}
GROUP BY p.principal_id, e.session_id, day`,
);
return rows.map((r) => ({
principalId: r.principal_id as string,
sessionId: r.session_id as string,
day: Number(r.day),
turns: Number(r.turns),
firstAt: Number(r.first_at),
lastAt: Number(r.last_at),
}));
},
async spendRollup(range): Promise<SpendRow[]> {
return (await q(spendSql, [range.from, range.to])).map(rowToSpendRow);
},
async spendReport(range) {
const dayMs = 86_400_000;
if (range.from < 0 || range.from % dayMs || range.to % dayMs) {
return { rows: (await q(spendSql, [range.from, range.to])).map(rowToSpendRow) };
}
let asOf = now();
const days = await q(
`WITH pending AS (SELECT DISTINCT day FROM session_spend_dirty WHERE day >= $1 AND day < $2)
SELECT COALESCE(c.day, p.day) AS day, c.rows, c.updated_at,
p.day IS NOT NULL OR EXISTS (SELECT 1 FROM session_spend_dirty WHERE session_id IS NOT NULL) AS pending
FROM (SELECT * FROM session_spend_days WHERE day >= $1 AND day < $2) c
FULL JOIN pending p ON p.day = c.day ORDER BY day`,
[range.from / dayMs, range.to / dayMs],
);
if (days.some((day) => day.rows === null)) {
return { rows: (await q(spendSql, [range.from, range.to])).map(rowToSpendRow) };
}
const rows: SpendRow[] = [];
for (const day of days) {
rows.push(...(day.rows as Record<string, unknown>[]).map(rowToSpendRow));
if (day.pending) asOf = Math.min(asOf, Number(day.updated_at));
}
return { rows, asOf };
},
async refreshSpendRollup() {
const started = Date.now();
await withPgTransaction(await pool(), async (client) => {
const changed = await client.query(
"DELETE FROM session_spend_dirty WHERE session_id IS NOT NULL RETURNING session_id",
);
if (!changed.rowCount) return;
await client.query(
`WITH RECURSIVE affected(id) AS (
SELECT unnest($1::text[])
UNION
SELECT s.id FROM sessions s JOIN affected a ON s.parent_session_id = a.id
)
INSERT INTO session_spend_dirty(day)
SELECT DISTINCT floor(r.created_at::numeric / 86400000)::bigint
FROM session_llm_requests r JOIN affected a ON a.id = r.session_id WHERE r.usage_json IS NOT NULL
ON CONFLICT DO NOTHING`,
[changed.rows.map((r) => r.session_id)],
);
});
const days = await q(
`SELECT d.day FROM (SELECT DISTINCT day FROM session_spend_dirty WHERE day IS NOT NULL) d
LEFT JOIN session_spend_days c ON c.day = d.day
ORDER BY d.day = max(d.day) OVER () DESC, c.updated_at NULLS FIRST, d.day DESC`,
);
for (const [index, { day }] of days.entries()) {
try {
await withPgTransaction(await pool(), async (client) => {
const pending = await client.query("DELETE FROM session_spend_dirty WHERE day = $1 RETURNING day", [day]);
if (!pending.rowCount) return;
const updatedAt = now();
const rows = await client.query(spendSql, [Number(day) * 86_400_000, (Number(day) + 1) * 86_400_000]);
await client.query(
`INSERT INTO session_spend_days(day, rows, updated_at) VALUES ($1, $2, $3)
ON CONFLICT (day) DO UPDATE SET rows = EXCLUDED.rows, updated_at = EXCLUDED.updated_at`,
[day, jsonbSafeStringify(rows.rows), updatedAt],
);
});
} catch (error) {
reportFailure("spend: refresh daily totals", error);
continue;
}
if (index > 0 && Date.now() - started >= 5_000) break;
}
},
async listParticipants(): Promise<ParticipantWindow[]> {
const rows = await q(
"SELECT session_id, principal_id, valid_from, valid_to, valid_from_seq, valid_to_seq FROM participants",
);
return rows.map(rowToParticipantWindow);
},
async distinctParticipants(): Promise<string[]> {
const rows = await q("SELECT DISTINCT principal_id FROM participants");
return rows.map((r) => r.principal_id as string);
},
async participantWindowsOf(sessionId): Promise<ParticipantWindow[]> {
const rows = await q(
"SELECT session_id, principal_id, valid_from, valid_to, valid_from_seq, valid_to_seq FROM participants WHERE session_id = $1",
[sessionId],
);
return rows.map(rowToParticipantWindow);
},
async participantsOf(sessionId): Promise<string[]> {
const rows = await q("SELECT principal_id FROM participants WHERE session_id = $1", [sessionId]);
return rows.map((r) => r.principal_id as string);
},
};
}