llm integration

[DanielFGray]

LLM Integration

AI-powered Postgres assistant integrated into the workbench.

Goals

• SQL writing assistance — help compose queries, explain syntax, suggest schema designs
• Query analysis & optimization — analyze EXPLAIN plans, suggest indexes, rewrite slow queries (ties into existing pev2 visualization)
• Schema review & best practices — naming issues, normalization suggestions, anti-pattern detection
• Conversational DB exploration — natural language to SQL, ask questions about your data

Architecture

BYOK (Bring Your Own Key) — users configure their own LLM provider + API key. Server proxies requests (solves CORS), never persists keys.

Premium tier — GitHub sponsors could get access to a hosted model (inference cost offset by sponsorship).

Existing Infrastructure

Server-side multi-provider abstraction already built at server/ai/:

• @effect/ai with four providers: OpenAI, Anthropic, Google, OpenRouter
• Model registry with auto-detection (resolveProvider("gpt-4o") → "openai")
• generateText() with BYOK via Redacted API keys
• generateTextFromEnv() for server-configured keys (premium tier)

Client-side VS Code Chat service already registered (getChatServiceOverride()).

What's Needed

1. Server: streaming chat route — POST /api/ai/chat using AiLanguageModel.streamText() from @effect/ai, returns SSE stream. Client sends API key per-request in a header.

2. Client: LanguageModelChatProvider — registers with vscode.lm.registerLanguageModelChatProvider(), proxies to server streaming endpoint. Makes the multi-provider backend available to any VS Code chat feature.

3. Client: @pg ChatParticipant — registered via vscode.chat.createChatParticipant() with commands:

   • /explain — explain current query or EXPLAIN plan
   • /optimize — suggest improvements for a query
   • /schema — review current schema for anti-patterns
   • /ask — natural language to SQL
   • Assembles on-demand context from existing introspection data (tables, columns, types, indexes) and current editor content.

4. Client: API key settings UI — new section in Account Settings (or dedicated panel) for provider + API key configuration. Stored in vscode.SecretStorage.

UX Surfaces

• Chat sidebar — VS Code Chat panel (@pg participant) for open-ended questions
• Inline notebook cells — natural language cells in SQL notebooks → generated SQL in next cell
• Both entry points share the same backend

References

• VS Code Chat API: chat-sample
• VS Code inline completions: inline-completions
• VS Code diffs: QuickDiffProvider, source-control-sample
• postgresai — Apache 2.0 Postgres observability with AI-ready structured output (health checks less relevant for in-memory PGlite, but domain knowledge is useful)
• Postgres RAG: part 1, part 2
• premsql