Dory Automation API

[dory-finn]

🚀 Feature: Introduce Dory Automation API (Replace Debug-Based Integrations)

Background

We previously explored integrating Dory with tools like OpenCLI. However, current approaches rely on browser/Electron debug mode (e.g. --remote-debugging-port), which introduces several issues:

• ❌ High setup complexity for users
• ❌ Security concerns (debug ports exposed)
• ❌ Fragile integration (UI/DOM dependent)
• ❌ Not scalable for automation / agent workflows

This indicates a deeper architectural gap:

Dory does not yet expose its core capabilities as programmable APIs.

---

Goal

Introduce a minimal Automation API layer that allows external systems (CLI, agents, workflows) to interact with Dory without relying on UI or debug mode.

This will enable:

• CLI integrations (future dory-cli)
• OpenCLI / MCP adapters
• Scheduled jobs / automation workflows
• AI-driven actions outside UI

---

Core Idea

Separate Product Capabilities from UI Interaction

Instead of:

External tools controlling Dory via UI/debug

We move to:

External tools calling Dory via stable APIs

---

MVP Scope (Minimal Automation API)

1. Execute SQL

POST /api/automation/query/execute

{
  "connectionId": "xxx",
  "sql": "SELECT * FROM users LIMIT 100"
}

---

2. List Connections

GET /api/automation/connections

---

3. Get Schema

GET /api/automation/schema?connectionId=xxx

---

4. AI Ask (Optional but recommended)

POST /api/automation/ai/ask

{
  "connectionId": "xxx",
  "question": "Top 10 slow queries yesterday"
}

---

Authentication

Reuse existing auth system (Better Auth):

Options:

• Cookie-based session (desktop/web)
• Bearer token (for CLI / external usage)

---

Non-Goals (for now)

• ❌ Full CLI implementation (dory-cli)
• ❌ Complex workflow orchestration
• ❌ Multi-step automation pipelines
• ❌ Permission system redesign

---

Future Extensions

• CLI wrapper (dory run, dory query)
• MCP server integration
• Scheduled jobs (cron-like)
• AI agents triggering actions
• Export / data pipelines

---

Why This Matters

This unlocks a new layer for Dory:

UI Layer        → SQL Editor / Explorer / AI UI
Automation API  → (NEW) programmatic interface
Execution Layer → DB connectors / AI engine

It shifts Dory from:

A UI-based database tool

to:

A programmable AI-native data platform

---

Open Questions

• Should Automation API be public or gated behind feature flag?
• Do we need a separate base path (/automation) or reuse existing APIs?
• How to handle long-running queries (streaming / polling)?
• Should AI endpoints be included in MVP?

---

Next Steps

• Define API contracts (types + responses)
• Implement /query/execute
• Add auth support (token-based)
• Test via curl / simple script
• Evaluate OpenCLI adapter (without debug mode)

---

Notes

This is a foundational step for:

• AI workflows
• External integrations
• Future CLI / MCP ecosystem

Avoid building around debug-based control going forward.