internal-packages/dashboard-agent-db/README.md
The conversation datastore for the in-dashboard agent, isolated from the main
Prisma database. Drizzle (postgres-js) over a dedicated trigger_dashboard_agent
Postgres schema.
DASHBOARD_AGENT_DATABASE_URL); migrations run over a direct
(non-pooler) connection (DASHBOARD_AGENT_DIRECT_URL), since a transaction-mode
pooler can't run the migrator.DATABASE_URL (and DIRECT_URL for
migrations); the tables live in the dedicated trigger_dashboard_agent schema,
isolated from Prisma's public.The schema is foreign-key-free — it references main entities (organizationId,
userId) by id only, because in cloud it lives in a different database.
The agent runs as an ephemeral Trigger task and must have no access to the main
database or ClickHouse (those go through the API). This is its own low-blast-radius
store: the agent connects directly here to persist conversations, and the webapp
connects here for the History tab. Conversation history correctness is owned by
chat.agent's built-in object-store snapshot — this DB is a display read-model
(list chats, render a past chat, resume the transport), never the model's source
of truth.
chats — one row per conversation: org/user scope, title, metadata (the
project/env context the chat ran in), and next_message_position, the allocator the
transcript's ordering comes from. No transcript of its own. Soft-deleted via
deleted_at, pinned via pinned_at, read-marked via last_read_at (NULL = never
read, so every watch wake in it counts as unread).
chat_messages — the transcript, one row per message. Identity is
(chat_id, message_id) and order is position, unique per chat and reserved from
chats.next_message_position by the same single statement that reads it, so
concurrent writers get disjoint contiguous ranges. role is lifted out of the
payload so the message-quota count is an index scan.
Three write modes, and only the third may change a message the chat already holds:
a new message is a plain insert; a redelivered durable event (a watch wake, a
settlement card) is ON CONFLICT DO NOTHING on (chat_id, message_id), so it
leaves the recorded row untouched; a deliberate finalisation is
finalizeChatMessage, which rewrites one body under a verified role and never
moves the id or the position. So re-sending a whole turn snapshot is a no-op.
Positions are monotonic, not gapless: a reservation whose insert then conflicts, or a batch that rolls back, leaves the slot unused. Only the relative order matters, so a gap is expected and harmless.
chat_sessions — live transport state keyed by chat_id: the session-scoped
public_access_token and last_event_id for resume. Separate table so the
secret token is isolated from list queries and the hot per-turn write stays off
the conversation row's indexes.
chat_turn_evals — one row per judged turn, written by the
dashboard-agent-eval-turn task: quality scores (grounded / answered / concise)
and insight classification (intent, outcome, capability & docs gaps). Keyed on
(chat_id, turn) so a re-delivered turn can't double-insert. A row holds the
judge's derived verdict only — never the user's question, the agent's answer or
any tool data. What is judged and what a row may carry is one file:
@internal/dashboard-agent/src/eval-policy.ts. Rows are retired after 30 days
by the webapp's dashboard-agent sweep. user_text and judge are legacy
columns nothing writes any more.
investigations — the agent's revisioned working state for a diagnostic thread.
Keyed by investigation_id so a follow-up can load one from the id alone;
revision is bumped by a single atomic revision = revision + 1 update, and the
chat_id/project_ref/environment_ref triple must match on every commit.
state is intentionally untyped JSONB — the payload shape isn't frozen yet.
watches — "tell me when X happens", checked by a periodic task. status
(active | fired | expired | cancelled) and delivery_status
(not_required | pending | delivering | delivered) are guarded in the query layer with
WHERE status = 'active' … RETURNING, so concurrent fire/expire/cancel resolves
to one winner. The org/project/env/user identity is a snapshot taken at creation
and never updated — a watch fires with exactly the access its creator had.
identity is the dedup key for the watched thing: a partial unique index on
(chat_id, project_id, environment_id, identity) WHERE status = 'active' is what
actually prevents duplicates, since a read-then-insert check can't be race-proof.
A chat may hold at most three active watches, enforced by counting and inserting
in one transaction under a per-chat advisory lock.
pnpm run db:generate # generate SQL migration from src/schema.ts (offline)
pnpm run db:migrate # apply migrations (direct url: DASHBOARD_AGENT_DIRECT_URL, falling back to DASHBOARD_AGENT_DATABASE_URL / DIRECT_URL / DATABASE_URL)
drizzle-kit is scoped to the trigger_dashboard_agent schema (schemaFilter), so
pointing it at the main OSS database never touches Prisma's tables.