LibreDB Agent Phase 1: read-only database operations agent (epic)

[cevheri]

Summary

Turn LibreDB Studio from a SQL-generating AI assistant into a fully integrated, open-source Database Operations Agent: it discovers database context, plans, works with policy-bound read-only tools, and backs every finding with verifiable artifacts and citations.

Phase 1 proves a production-quality read-only agent core on PostgreSQL and SQLite. The product stays open-source, local-first, model/provider-neutral and compatible with the existing SQL/NoSQL provider architecture.

Product decisions (fixed for Phase 1)

• Three independent axes, never merged into one "mode" field:
  • executionMode: planning (server-enforced empty tool set, zero live DB access) | agent (server-selected read-only tools only)
  • workflowType: investigation | query-optimization | database-assessment (Phase 1); architecture and migration are future workflows, not modes
  • connectionScope: immutable single-connection scope; the model can never choose a free connectionId — the server injects it from run context
• Canonical operation risk classes R0-R6 with a fail-closed decision pipeline. The Phase 1 tool registry contains only R0 (metadata) and verified R1 (bounded read) descriptors; R2-R6 tools are not registered at all, not even disabled.
• Database-native enforcement is the security boundary, never a parser: PostgreSQL dedicated read-only execution profile (BEGIN READ ONLY, least-privilege role, isolated pool) and SQLite read-only open + PRAGMA query_only with adapter contract tests. EXPLAIN ANALYZE is default-deny (it executes the query).
• Database content (identifiers, comments, samples) is untrusted input: tool-result labeling/quoting policy plus prompt-injection fixtures are part of the Phase 1 security suite.
• Every tool returns a structured outcome (ok | error | denied | truncated | cancelled + errorClass); policy denials are never retried as if they were wrong SQL. Bounded repair loop (max 3 attempts per run).
• Final answers require artifact citations or an explicit failure/partial statement.
• Orchestration uses AI SDK 7 WorkflowAgent + Workflow SDK directly; no second agent loop, no framework adapter layer.
• Models: Gemini, OpenAI, Claude, Ollama, OpenAI-compatible custom via the AI SDK provider registry, mapped from the existing LLM settings (single source of truth). Models failing the capability probe fall back explicitly to chat/NL2SQL.
• UX (validated with an interactive prototype): right agent rail is the single primary AI surface (resizable, Planning as default mode, user can switch to Agent at any time); agent artifacts open in the shared BottomPanel (ResultsGrid/Explain/Charts) with an explicit "Agent run #N - read-only" provenance badge; the agent never auto-writes to the editor or auto-runs the editor path ("Apply to editor" is explicit).
• Phase 1 is standalone-only: the rail is gated behind an additive/optional capability prop and stays invisible in the embedded platform build. Delivering the agent to platform hosts is a separate roadmap item.
• Everything ships behind a feature flag (default off) so trunk-based releases keep flowing.

Milestones

Each milestone is a child issue, one maintainer-loop run each, releasable trunk after each:

• Raise the runtime baseline to Node 24 LTS across app, package and channels #326 Node 24 LTS baseline (pre-requisite release, before any agent PR)
• Agent M0: AI SDK 7 / Workflow SDK runtime and World spike #327 M0 - AI SDK 7 / Workflow SDK runtime and World spike
• Agent M1: canonical DatabaseOperations, risk classes and fail-closed policy layer #328 M1 - canonical DatabaseOperations, risk classes, fail-closed policy layer
• Agent M2: WorkflowAgent core, provider registry, context snapshots and the investigation workflow #329 M2 - WorkflowAgent core, provider registry, context snapshots, investigation workflow
• Agent M3: query-optimization and database-assessment templates plus eval gates #330 M3 - query-optimization and database-assessment templates, eval gates
• Agent M4: workspace agent rail primacy and legacy AI surface demotion #331 M4 - workspace surface unification and legacy AI demotion
• Agent observability: OpenTelemetry export to a user-configured endpoint #332 Agent observability: OpenTelemetry export to a user-configured endpoint (ships with the M2 preview)

Commercial boundary (open-core, stated upfront)

• The Phase 1-2 agent core is MIT and stays MIT: nothing shipped free is later gated or removed.
• Natural paid/platform candidates, delivered through libredb-platform rather than this repo: team approval workflows and audit export (Phase 2 adjuncts), scheduled/continuous Observe & Operate (Phase 3 - inherently server-side), fleet/migration orchestration (Phase 4), the governance suite (Phase 5).
• Platform enablement of the agent (ESM server subpath export + tenant-aware policy) is its own roadmap item, scheduled after M4.

Release and announcement beats

• M2 complete -> public "experimental agent preview": blog + demo, channel release notes, OTel export documented (Agent observability: OpenTelemetry export to a user-configured endpoint #332), and a draft data-flow document published alongside.
• M4 complete -> full launch, and the 1.0 candidacy decision (the agent launch is the natural 1.0 moment after the 0.9/0.10 series).
• Product usage telemetry (a project-hosted collector with a consent flow) is deliberately deferred: revisit at the 1.0 decision. The OTel instrumentation from Agent observability: OpenTelemetry export to a user-configured endpoint #332 keeps that path cheap without adding dependencies or consent UX now.
• Every public claim at these beats follows the existing proof-first rule: competitive statements are re-verified at publish time, not recalled from memory.

Cross-cutting gates

• TDD with the 100% line-coverage gate; the six local gates before every push.
• CI evals run on deterministic mock providers; real-model evals are a scheduled/manual job.
• Hard invariants enforced by policy unit tests, not only by evals: Planning DB executions = 0, R2+ executions = 0, duplicate executions = 0, citation required for every final finding.
• Provider triad rule applies to any provider-touching change (code + docs + integration tests in the same PR).

Out of scope (Phase 1)

DML/DDL/maintenance execution, multi-connection/migration, continuous autonomous remediation, real Context Graph, platform tenant orchestration, agent framework adapters (LangChain/LangGraph/Deep Agents), MCP surface, Turso/libSQL as a user-facing DatabaseProvider.

[cevheri]

M0 spike (#327) is done — full report with evidence is at #327 (comment)

Headlines for the epic:

• Pinned: ai@7.0.59, workflow@4.8.1, @workflow/world-local@4.2.4, @workflow/world-postgres@4.3.3 — the stable 4.x line. 5.x exists only under the beta tag; a durability layer is the wrong place to inherit churn during a preview milestone.
• Turso World: closed. It exists (@workflow-worlds/turso@0.2.2) but is community, not official — a 0.x adapter against a 4.x contract, last published 2026-02-09, single maintainer, and the repository carries no licence file despite npm metadata claiming MIT.
• World decision: two official modes, mirroring STORAGE_PROVIDER — world-local as the zero-config default, world-postgres opt-in for multi-replica. Reading only the docs would have got this wrong: the local World re-enqueues pending/running runs on start by default, and the shared helper documents that the handler is idempotent via event-log replay, which is precisely Agent M2: WorkflowAgent core, provider registry, context snapshots and the investigation workflow #329's resume-without-duplicate-tool-executions criterion.
• A custom better-sqlite3 World is no longer worth building — the contract is ~25-30 methods across runs/steps/events/hooks plus a queue plus chunked streaming, all of which must be correct under crash. It was evaluated as a first-class option, as this epic asked, and lost on measurement.
• Runtime spike passed on the real repository: with the agent dependencies installed, typecheck, build, build:lib and attw --profile node16 are all green and no AI SDK or Workflow SDK type reaches the published declaration surface. The CJS consumer contract survives.

Two items M2 must carry, recorded in the report rather than discovered later: the local queue transport is HTTP loopback to the app's own port and has to be proven safe behind src/proxy.ts, and world-local is single-instance so Helm replicaCount > 1 requires the Postgres World.