Skip to content

API-Reference.md

Latest commit

 

History

History
387 lines (267 loc) · 7.28 KB

API-Reference.md

File metadata and controls

387 lines (267 loc) · 7.28 KB

API reference: every route, no guessing

Base URL examples use http://localhost:8000. All JSON bodies use Content-Type: application/json.

Interactive OpenAPI: /docs. OpenAPI JSON: /openapi.json.

Tip

Prefer /docs for interactive exploration — this page is for copy-paste and diffs when the UI is not handy.

flowchart TB
  H[Health]:::secondary
  Q[Query + Explain]:::primary
  S[Schema]:::accent
  C[Connections]:::primary
  E[Examples]:::accent
  HI[History]:::secondary

  subgraph surface [HTTP surface]
    H
    Q
    S
    C
    E
    HI
  end

  classDef primary fill:#8b5cf6,stroke:#7c3aed,color:#fff
  classDef secondary fill:#64748b,stroke:#475569,color:#fff
  classDef accent fill:#f5f3ff,stroke:#8b5cf6,color:#4c1d95
Loading

GET /

Response 200

{
  "service": "nl2sql-engine",
  "docs": "/docs"
}

Health

GET /health/live

Liveness for orchestrators.

{ "status": "ok" }

GET /health/ready

Response 200 — Redis reachable and connectors present:

{ "status": "ready" }

If no connectors loaded:

{ "status": "degraded", "detail": "no active connections" }

Note

degraded still returns HTTP 200 — design for Kubernetes-style probes that want a body, not a crash loop.

Query

POST /query

Body (QueryRequest):

Field Type Required Description
connection string yes Registered connection name
question string yes Natural language question (min length 1)
allow_mutations boolean | null no Overrides default execution.allow_mutations

Response 200 (QueryResponse):

{
  "sql": "SELECT \"Name\" FROM \"Artist\" LIMIT 10;",
  "validation": {
    "ok": true,
    "dialect": "postgres",
    "normalized_sql": "SELECT \"Name\" FROM \"Artist\" LIMIT 10",
    "errors": [],
    "warnings": []
  },
  "result": {
    "columns": ["Name"],
    "rows": [{ "Name": "AC/DC" }],
    "row_count": 1,
    "truncated": false,
    "execution_ms": 2.5,
    "dialect": "postgres"
  },
  "attempts": [
    {
      "index": 0,
      "sql": "SELECT \"Name\" FROM \"Artist\" LIMIT 10;",
      "validation_ok": true,
      "validation_errors": [],
      "execution_error": null
    }
  ]
}

Failure shapes:

  • result may be null if all retries exhausted.
  • validation.ok false → check validation.errors and attempts[*].validation_errors.
  • Execution errors appear on the attempt as execution_error.

Side effect: Appends an entry to Redis query history (history_store.append_entry).

Example:

curl -s -X POST http://localhost:8000/query \
  -H "Content-Type: application/json" \
  -d '{"connection":"chinook","question":"Show 5 artist names"}'

POST /query/explain

Body (ExplainSqlRequest):

{
  "connection": "chinook",
  "sql": "SELECT \"Name\" FROM \"Artist\" LIMIT 5"
}

Response 200 (ExplainSqlResponse):

{
  "markdown": "1. Selects artist names from \"Artist\".\n2. Returns at most five rows.",
  "steps": [
    { "title": "Step 1", "body": "Selects artist names from \"Artist\"." },
    { "title": "Step 2", "body": "Returns at most five rows." }
  ]
}

Schema

GET /schema/{connection}

Returns full SchemaInfo.

Query params:

Param Type Default Description
refresh boolean false Bypass cache and re-introspect

Response 200: SchemaInfo — dialect, connection_name, tables[], relationships[], raw_stats.

GET /schema/{connection}/tables

Response 200: TableInfo[] — same as schema.tables from cached/full discovery.

POST /schema/{connection}/annotate

Body (AnnotateRequest):

{
  "table": "Customer",
  "column": "Email",
  "description": "Primary contact email",
  "tags": ["pii", "contact"],
  "context": "Used for marketing opt-in only"
}

All of description, tags, context are optional; include what you need.

Response 200:

{
  "status": "ok",
  "annotation": {
    "description": "Primary contact email",
    "tags": ["pii", "contact"],
    "context": "Used for marketing opt-in only"
  }
}

Side effect: Invalidates schema cache for connection.

Important

Annotations change what the LLM sees — treat context as prompt-adjacent text, not a security boundary.

Connections

GET /connections

Response 200: Array of connector configs with secrets masked:

[
  {
    "name": "chinook",
    "type": "postgres",
    "params": {
      "dsn": "postgresql://nl2sql:***@localhost:5432/chinook"
    }
  }
]

Masking applies to param keys password, private_key, token when non-empty.

POST /connections

Body: ConnectorConfig — name, type, params.

Response 201: Masked config object.

Side effect: Persists full config to Redis (nl2sql:connections) and pool.add_or_replace.

PUT /connections/{name}

Body: ConnectorConfig — name must match path {name}.

Response 200: Masked config.

DELETE /connections/{name}

Response 204 on success.

Error 400 if {name} exists only in static config.yaml (cannot delete via API).

POST /connections/test

Body (TestRequest):

{
  "type": "postgres",
  "params": { "dsn": "postgresql://user:pass@host:5432/db" }
}

Response 200:

{ "status": "ok" }

Error 400 with detail message if connection fails.

Few-shot examples

GET /examples

Response 200: FewShotExample[]

[
  {
    "id": "550e8400-e29b-41d4-a716-446655440000",
    "natural_language": "Count customers in Brazil",
    "sql": "SELECT COUNT(*) FROM \"Customer\" WHERE \"Country\" = 'Brazil';",
    "dialect": "postgres",
    "tags": ["chinook"]
  }
]

POST /examples

Body: FewShotExample — id optional.

Response 201: Created example with assigned id.

GET /examples/{example_id}

Response 200: Single example.

Response 404 if missing.

PUT /examples/{example_id}

Body: FewShotExample — id must match path.

Response 200: Updated example.

DELETE /examples/{example_id}

Response 204

History

GET /history

Query params:

Param Type Default Description
q string null Case-insensitive substring match on question or sql
limit int 100 1–500

Response 200: Array of history objects:

[
  {
    "id": "uuid",
    "ts": 1710000000.123,
    "connection": "chinook",
    "question": "Top 5 artists",
    "sql": "SELECT ...",
    "ok": true,
    "row_count": 5,
    "error": null
  }
]

Entries are stored in Redis list nl2sql:history, newest first, max 500 retained server-side; limit only affects response size.

Error format

FastAPI HTTPException returns JSON:

{ "detail": "Human-readable message" }

Validation errors from Pydantic use FastAPI’s standard 422 structure with loc / msg fields.

CORS

Configured from CORS_ORIGINS env (comma-separated). Defaults include http://localhost:5173 and Docker web origin.