Skip to content

Scansca API Documentation

Overview

Scansca provides a RESTful API for managing database connections and executing queries. The API is divided into two main sections:

  1. REST API: Standard HTTP endpoints for database operations
  2. MCP API: Model Context Protocol endpoints for LLM integration

REST API

Databases

Register a Database

1
POST /api/v1/databases

Register a new database connection.

Request Body:

1
2
3
4
5
6
7
8
{
  "name": "my-postgres",
  "type": "postgresql",
  "connection_string": "postgres://user:password@localhost:5432/dbname",
  "options": {
    "max_connections": 10
  }
}

Response:

1
2
3
{
  "message": "Database registered successfully"
}

List Databases

1
GET /api/v1/databases

List all registered databases.

Response:

1
2
3
4
5
6
7
8
9
{
  "databases": [
    {
      "name": "my-postgres",
      "type": "postgresql",
      "connection_string": "[REDACTED]"
    }
  ]
}

Get Database Info

1
GET /api/v1/databases/:name

Get detailed information about a specific database.

Response:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
{
  "name": "my-postgres",
  "type": "postgresql",
  "status": {
    "connected": true,
    "acquired_connections": 1,
    "idle_connections": 2,
    "total_connections": 3,
    "max_connections": 10,
    "database_version": "PostgreSQL 13.4"
  }
}

Remove Database

1
DELETE /api/v1/databases/:name

Remove a database connection.

Response:

1
2
3
{
  "message": "Database removed successfully"
}

Schema Introspection

List Schemas

1
GET /api/v1/databases/:name/schemas

List all schemas in a database.

Response:

1
2
3
4
{
  "database": "my-postgres",
  "schemas": ["public", "app", "auth"]
}

List Tables

1
GET /api/v1/databases/:name/schemas/:schema/tables

List all tables in a schema.

Response:

1
2
3
4
5
6
{
  "database": "my-postgres",
  "schema": "public",
  "tables": ["users", "products", "orders"],
  "views": ["active_users", "recent_orders"]
}

Get Table Info

1
GET /api/v1/databases/:name/schemas/:schema/tables/:table

Get detailed information about a table.

Response:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
{
  "database": "my-postgres",
  "schema": "public",
  "table": "users",
  "tableInfo": {
    "schema": "public",
    "name": "users",
    "columns": [
      {
        "name": "id",
        "data_type": "integer",
        "is_nullable": false,
        "is_primary_key": true
      },
      {
        "name": "email",
        "data_type": "varchar",
        "is_nullable": false
      }
    ],
    "primary_key": ["id"],
    "estimated_row_count": 1000
  }
}

Query Execution

Execute Query

1
POST /api/v1/query

Execute a SQL query.

Request Body:

1
2
3
4
5
{
  "database": "my-postgres",
  "query": "SELECT * FROM users WHERE id = $1",
  "params": [123]
}

Response:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
{
  "result": {
    "columns": ["id", "email", "name"],
    "rows": [
      [123, "user@example.com", "John Doe"]
    ],
    "affected_rows": 1,
    "execution_time": 0.005
  }
}

MCP API

List Tools

1
GET /mcp/v1/tools

List all available MCP tools.

Response:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
{
  "tools": [
    {
      "name": "execute_query",
      "description": "Execute a SQL query on a registered database",
      "parameters": {
        "database": {
          "type": "string",
          "description": "Database name"
        },
        "query": {
          "type": "string",
          "description": "SQL query to execute"
        },
        "params": {
          "type": "array",
          "description": "Query parameters (optional)"
        }
      }
    },
    {
      "name": "list_schemas",
      "description": "List all schemas in a database",
      "parameters": {
        "database": {
          "type": "string",
          "description": "Database name"
        }
      }
    }
  ]
}

Invoke Tool

1
POST /mcp/v1/tools/:name/invoke

Invoke a specific MCP tool.

Request Body:

1
2
3
4
{
  "database": "my-postgres",
  "query": "SELECT current_database(), current_user"
}

Response:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
{
  "status": "success",
  "result": {
    "columns": ["current_database", "current_user"],
    "rows": [
      ["scansca", "scansca_user"]
    ],
    "affected_rows": 1,
    "execution_time": 0.002
  }
}

Error Handling

All API endpoints return appropriate HTTP status codes:

  • 200 OK: Successful operation
  • 201 Created: Resource created successfully
  • 400 Bad Request: Invalid parameters
  • 404 Not Found: Resource not found
  • 500 Internal Server Error: Server error

Error responses include a JSON body with an error message:

1
2
3
{
  "error": "Database 'unknown-db' not found"
}

Future Enhancements

Planned API enhancements include:

  1. Authentication/Authorization
  2. Batch operations
  3. Additional database connectors
  4. Query history and saved queries