API Reference

TernoDBI provides two API services: Query Service for read operations and Admin Service for write operations. Both services require authentication via API tokens.

Authentication

All API endpoints require a valid service token passed in the Authorization header:

Authorization: Bearer <your-api-token>

Generate tokens using:

python manage.py issue_token --name "My Agent" --type query

Query Service

Base URL: /api/query/

The Query Service is designed for AI agents and provides read-only access to your databases.

Health Check

GET /api/query/health/

Returns the service status.

Response:

{
  "status": "ok",
  "version": "1.0.0"
}

List Datasources

GET /api/query/datasources/

Returns all datasources accessible to the authenticated token.

Response:

{
  "datasources": [
    {
      "id": 1,
      "name": "Production DB",
      "db_type": "postgres"
    }
  ]
}

Get Datasource Details

GET /api/query/datasources/{id}/

Returns detailed information about a specific datasource.

Get Schema

GET /api/query/datasources/{id}/schema/

Returns the complete schema for a datasource, including tables, columns, and relationships. This is the primary endpoint for LLMs to understand your database structure.

Response:

{
  "datasource": "Production DB",
  "tables": [
    {
      "id": 1,
      "name": "users",
      "public_name": "Users",
      "description": "User accounts",
      "columns": [
        {
          "name": "id",
          "public_name": "User ID",
          "data_type": "integer",
          "description": "Primary key"
        }
      ]
    }
  ]
}

List Tables

GET /api/query/datasources/{id}/tables/

Returns all visible tables for a datasource.

List Columns

GET /api/query/datasources/{id}/tables/{table_id}/columns/

Returns columns for a specific table.

List Foreign Keys

GET /api/query/datasources/{id}/foreign-keys/

Returns foreign key relationships for schema understanding.

Execute Query

POST /api/query/datasources/{id}/query/

Executes a SQL query with advanced pagination support.

Request Body:

{
  "sql": "SELECT * FROM users",
  "pagination_mode": "cursor",
  "per_page": 50,
  "page": 1,
  "cursor": "...",
  "direction": "forward",
  "order_by": [
    {"column": "id", "direction": "DESC"}
  ]
}

Response:

{
  "status": "success",
  "table_data": {
    "columns": ["id", "name", "email"],
    "data": [
      [1, "Alice", "alice@example.com"],
      [2, "Bob", "bob@example.com"]
    ],
    "page": 1,
    "per_page": 50,
    "row_count": 1000,
    "total_pages": 20,
    "has_next": true,
    "has_prev": false,
    "next_cursor": "eyJ2Ijox...",
    "prev_cursor": null
  },
  "warnings": [
    "PAGINATION_WARNING: Deep offset (50000) - consider cursor pagination"
  ]
}

Get Sample Data

GET /api/query/tables/{table_id}/sample/

Returns sample rows from a table (useful for LLM context).

Admin Service

Base URL: /api/admin/

The Admin Service requires admin type tokens and provides write access for managing datasources and metadata.

Create Datasource

POST /api/admin/datasources/

Creates a new datasource connection.

Request Body:

{
  "name": "My Database",
  "db_type": "postgres",
  "connection_string": "postgresql://user:pass@host:5432/db"
}

Update Datasource

PUT /api/admin/datasources/{id}/

Updates datasource configuration.

Delete Datasource

DELETE /api/admin/datasources/{id}/delete/

Removes a datasource and its metadata.

Sync Metadata

POST /api/admin/datasources/{id}/sync/

Re-syncs table and column metadata from the database.

Update Table Metadata

PUT /api/admin/tables/{id}/

Updates table metadata (description, public name, visibility).

Request Body:

{
  "public_name": "Customer Orders",
  "description": "All customer order records",
  "is_visible": true
}

Update Column Metadata

PUT /api/admin/columns/{id}/

Updates column metadata.

Request Body:

{
  "public_name": "Order Date",
  "description": "When the order was placed",
  "is_visible": true
}

Validate Connection

POST /api/admin/validate/

Tests a connection string without creating a datasource.

Request Body:

{
  "connection_string": "postgresql://user:pass@host:5432/db"
}

Get Table Info

GET /api/admin/datasources/{id}/tables/{table_name}/info/

Returns detailed table information including column metadata.

Get All Tables Info

GET /api/admin/datasources/{id}/tables/info/

Returns metadata for all tables in a datasource.

Error Responses

All endpoints return standard error responses:

{
  "error": "Error message",
  "code": "ERROR_CODE"
}

Common status codes:

• 400 - Bad Request (validation error)
• 401 - Unauthorized (invalid or missing token)
• 403 - Forbidden (insufficient permissions)
• 404 - Not Found
• 500 - Internal Server Error