Katia Bulatova
ee854480fe
## What & why The dashboard agent's upkeep — retention deletes and the investigation sweep — ran as cron jobs on the webapp's common worker, even though it only touches the agent's own datastore. This moves that upkeep into the agent's Trigger project as scheduled tasks (TRI-13182). ## What's inside **Retention** — `internal-packages/dashboard-agent/src/maintenance.ts`, a daily task (03:00 UTC). Deletes turn evals older than 30 days, hard-deletes chats soft-deleted more than 30 days ago, and purges terminal watches and submission rows older than 7 days. It used to run every 5 minutes; nothing needs a hard delete that fast, so it is daily now, draining in bounded batches and warning if it hits the cap. It retries (3 attempts) because the next run is a day away. It connects with `DASHBOARD_AGENT_DATABASE_URL`, falling back to `DATABASE_URL` like every other task in the package (the deletes are confined to the agent's own Postgres schema), and skips when neither is set. **Investigation sweep** — `src/investigation-sweep.ts`, every 5 minutes, same as before: settles investigation cards stuck `in_progress` (30-minute window, attempt cap, force-abandon note). It keeps the fast cadence because it fixes live state the UI is showing. **What stays in the webapp.** The watch finalize/deliver sweep and batch rearm: they cover a dead agent-side tick chain — a backstop can't live inside the thing it backstops — and they need the main database and the alerts worker. The org-deletion chat purge also stays: deletion must not depend on the agent project being deployed. The removed cron job keeps a cron-less tombstone entry so already-queued items drain cleanly; remove it in a follow-up. **Test plumbing** — the drizzle migration replayer that webapp tests hand-rolled is now exported once from `@internal/dashboard-agent-db/testing`; the moved tests live in the agent package as `src/*.test.ts` against real Postgres. ## Testing Agent package: retention passes (backlog drain, batch cap, no-op guard, chat-delete cascade) and the sweep, on testcontainers Postgres. Webapp: the watch/chat suites, plus a test that a settlement card stops the dashboard spinner. Full typecheck on both.
@internal/dashboard-agent-db
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.
- Cloud: a separate PlanetScale Postgres database. The app connects over a
pooled connection (
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. - OSS / self-host: falls back to the main
DATABASE_URL(andDIRECT_URLfor migrations); the tables live in the dedicatedtrigger_dashboard_agentschema, isolated from Prisma'spublic.
The schema is foreign-key-free — it references main entities (organizationId,
userId) by id only, because in cloud it lives in a different database.
Why a separate store
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.
Tables
-
chats— one row per conversation: org/user scope, title,metadata(the project/env context the chat ran in), andnext_message_position, the allocator the transcript's ordering comes from. No transcript of its own. Soft-deleted viadeleted_at, pinned viapinned_at, read-marked vialast_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 isposition, unique per chat and reserved fromchats.next_message_positionby the same single statement that reads it, so concurrent writers get disjoint contiguous ranges.roleis 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 NOTHINGon(chat_id, message_id), so it leaves the recorded row untouched; a deliberate finalisation isfinalizeChatMessage, which rewrites one body under a verifiedroleand 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 bychat_id: the session-scopedpublic_access_tokenandlast_event_idfor 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 thedashboard-agent-eval-turntask: 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_textandjudgeare legacy columns nothing writes any more. -
investigations— the agent's revisioned working state for a diagnostic thread. Keyed byinvestigation_idso a follow-up can load one from the id alone;revisionis bumped by a single atomicrevision = revision + 1update, and thechat_id/project_ref/environment_reftriple must match on every commit.stateis 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) anddelivery_status(not_required | pending | delivering | delivered) are guarded in the query layer withWHERE 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.identityis 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.
Migrations
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.