Data and events

Postgres is the system of record, the job queue, the event log and the message bus. This page covers the schema, how runs are queued, how events are stored and streamed, and the three NOTIFY channels.

The schema

The schema is one file, packages/db/src/schema.ts, written with Drizzle ORM. It has about 45 tables. Here they are grouped by what they're for:

Conventions

• Primary keys are random UUIDs, except run_events.id, which is a bigserial so events have a global order.
• Timestamps are timestamptz.
• Structured values (attachments, timeline payloads, approval payloads) are jsonb with a TypeScript type attached with $type<>().
• Deleting a teammate archives it (bots.archived_at). Rows that belong to it cascade or set null as each foreign key says.
• Secrets are never stored in plain text. encryptSecret() in @open-bot/shared uses AES-256-GCM with a random 12-byte IV, and stores iv.tag.ciphertext in base64url.

Migrations

Migrations are generated SQL files in packages/db/drizzle/, from 0000_init.sql to 0020_versioned_memory.sql.

pnpm db:generate   # write a new migration from changes to schema.ts
pnpm db:migrate    # apply pending migrations

0020 runs CREATE EXTENSION IF NOT EXISTS vector, so the database must have pgvector available. The local Compose file uses the pgvector/pgvector:pg17 image.

The run queue

A queued run is a row in runs with status = 'queued'. Anything that wants a teammate to work calls enqueueRun() (apps/worker/src/context.ts), which:

1. inserts the run with its trigger, prompt and links (the message, schedule, trigger or parent run that caused it);
2. appends a first status: queued event, so the chat shows it right away;
3. sends NOTIFY openbot_worker, 'runs'.

The worker claims runs with UPDATE ... FOR UPDATE SKIP LOCKED, at most one per teammate per chat, oldest first. The query is on the Worker page.

Run states

runs.waiting_for says what a waiting run waits for: approval or takeover. runs.cancel_requested_at marks a run you stopped, until the worker processes it.

The event log

run_events is append-only. Each row has the run id, the chat id (so a chat's events are one index scan), and a jsonb payload of one of four types:

type RunEventPayload =
  | { type: "status"; status: RunStatus; error?: string; waitingFor?: "approval" | "takeover" }
  | { type: "item"; item: TimelineItem } // insert or replace an item by id
  | { type: "delta"; itemId: string; delta: string } // append text to an item
  | { type: "usage"; inputTokens: number; cachedInputTokens: number; outputTokens: number };

A TimelineItem is one thing the run did, with an id, a status (running, completed or failed) and timestamps. There are fourteen kinds: message, reasoning, command, file_change, plan, web_search, tool, computer (a desktop action with its screenshot), approval, takeover_request, handoff, user_message (steering and hand-back notes), swarm and error.

Items are written whole whenever they change, and the reducer replaces them by id. Deltas only append text. This keeps the reducer simple and makes the log easy to read in SQL.

applyRunEvent(state, payload) in @open-bot/shared folds events into a run's timeline. It is pure and shared: the web app uses it on the server to build a chat's first snapshot, and in the browser to apply live events.

Why a log

• Streaming. The stream route reads rows after a cursor, so streaming is just a query.
• Resuming. A tab that reconnects sends its last id and gets exactly what it missed.
• Replay. Every desktop action points to its screenshot in artifacts, so you can step through what a teammate did, frame by frame, at any later time.
• Late updates. An approval decided after its run ended still appends an event to that run (appendRunEvent), so the card in the chat updates.

Notification channels

packages/db/src/index.ts defines three channels:

AppEvent has about twenty types, one per area of the UI: bots, chats, approvals, takeovers, lease, computer, notification, schedules, triggers, tasks, memory, vault, purchases, connections, email, telegram, skills, profile, lessons and auth. Each one tells open tabs which cached queries to refetch.

Notifications are hints, not data. Every listener reads the rows itself, and the worker sweeps for queued runs and cancellations every 15 seconds, so a dropped notification only delays something.

Usage ledger

usage_entries records tokens per run and auth mode, with an idempotency key of run:<id>:<mode> so a retry can't count twice. Plan usage costs nothing per token, so its cost_micros is 0. API key usage is priced at the model's list prices, in millionths of a dollar. See Models and usage.