terno-dbi 0.1.3

Database Intelligence Layer - Multi-database connectivity with SQLShield integration

TernoDBI: Database Interface Layer

TernoDBI is a database interface layer designed for Security and Accuracy, bridging the gap between AI Agents and Enterprise Data. It provides a unified, secure API for interacting with warehouse-scale databases while enforcing strict access controls and optimizing schema context for LLMs.

---

Key Features

• Multi-Database Support: Unified connection handling for Postgres, MySQL, Snowflake, BigQuery, Databricks, Oracle, and SQLite.
• Split MCP Architecture:
  • Query Server: Read-only operations (list tables, schema info, execute SELECT queries) optimized for agents.
  • Admin Server: Write/Management operations (rename tables, update metadata, manage descriptions) for human-in-the-loop workflows.
• Enterprise Security:
  • Row Level Security (RLS): Define strict SQL-based filters (e.g., department_id = 5) automatically injected into every query.
  • Privacy-by-Default: Hide sensitive tables/columns from LLM context unless explicitly exposed to specific Roles.
  • SQLShield: Automatic AST-based SQL validation preventing injection and destructive operations.
• LLM-Ready Schema Enrichment:
  • Semantic Metadata: Decouple physical DB names (t_users_v2) from user-facing semantic names (Customers).
  • Statistical Profiling: Automatic cardinality and distribution stats to help LLMs generate correct filters.
• High-Performance Pagination:
  • Cursor-Based: O(1) performance (HMAC-signed). Benchmarks show ~28x speedup over offset pagination.
  • Streaming: Server-side cursor support for exporting millions of rows.

---

Documentation

Detailed guides for setting up and using TernoDBI:

• Setup Guide: Installation, Environment Variables, and Server Startup.
• Architecture: System design, request flow, and component breakdown.
• MCP Integration: How to connect agents (Claude Desktop, Terno Agents).
• Security & SQLShield: Deep dive into our security model and token system.

---

Installation

pip install terno-dbi
# OR for local development
pip install -e .

---

Configuration

There are no manual configuration files to copy. Simply install the package and run it. The CLI handles environment setup and database initialization automatically.

Essential Variables

Variable	Description	Default
DBI_SECRET_KEY	Cryptographic key for signing cursors/tokens.	Unsafe Default
DBI_DEBUG	Toggle debug mode.	True
DATABASE_ENGINE	MYSQL, POSTGRESQL, or empty for SQLite.	SQLite

Database Setup Scenarios

• Standalone SQLite: Set DATABASE_ENGINE= (empty). DB created in server/db.sqlite3.
• Shared SQLite (Embedded): Set DJANGO_PROJECT_PATH=/path/to/other/django. TernoDBI will attach to that project's database.
• Production (Postgres/MySQL): Set DATABASE_ENGINE=POSTGRESQL and provide POSTGRES_DB, POSTGRES_USER, etc.

---

Usage

1. Running the API Server

ternodbi start

This command automatically:

1. Runs all required database migrations.
2. Creates a default admin user (username: admin, password: admin) on first boot.
3. Starts the server securely on 127.0.0.1:8376.

2. Management Commands (CLI)

Use the built-in CLI to manage access tokens for your agents:

# General Query Token
ternodbi manage issue_token --name "Claude Agent" --type query --expires 30

# Admin Token (Full Access)
ternodbi manage issue_token --name "System Admin" --type admin

# Scoped Token (Specific Datasource)
ternodbi manage issue_token --name "Finance Data Only" --type query --datasource 1

3. Query API & Pagination

Offset Mode (Default) - Best for UI.

POST /api/query/datasources/1/query/
{
    "sql": "SELECT * FROM users",
    "pagination_mode": "offset",
    "page": 2,
    "per_page": 50
}

Cursor Mode (High Performance) - Best for Agents & Data Export.

POST /api/query/datasources/1/query/
{
    "sql": "SELECT * FROM users",
    "pagination_mode": "cursor",
    "per_page": 50,
    "cursor": "eyJ2IjoxLCJ2YWx..." 
}

---

MCP Server Integration

Claude Desktop Setup

Step 1: Download & Install Claude Desktop

Download Claude Desktop from https://claude.ai/download and install it on your machine.

Step 2: Open Configuration

1. Launch Claude Desktop
2. Go to Account → Settings
3. Navigate to Developer section
4. Click Edit Config to open claude_desktop_config.json

Step 3: Add MCP Server Configuration

Add the following configuration to your claude_desktop_config.json:

Local Development:

{
  "mcpServers": {
    "ternodbi-admin": {
      "command": "/path/to/your/venv/bin/dbi-mcp",
      "args": ["admin"],
      "env": {
        "TERNODBI_API_URL": "http://127.0.0.1:8376",
        "TERNODBI_API_KEY": "dbi_admin_..."
      }
    },
    "ternodbi-query": {
      "command": "/path/to/your/venv/bin/dbi-mcp",
      "args": ["query"],
      "env": {
        "TERNODBI_API_URL": "http://127.0.0.1:8376",
        "TERNODBI_API_KEY": "dbi_query_..."
      }
    }
  }
}

Note: Run ternodbi mcp-config in your terminal to automatically generate the exact absolute paths and environment variables for your current installation.

Production (UVX):

{
  "mcpServers": {
    "ternodbi-query": {
      "command": "uvx",
      "args": ["--from", "terno-dbi", "dbi-mcp", "query"],
      "env": {
        "TERNODBI_API_URL": "https://dbi.yourdomain.com",
        "TERNODBI_API_KEY": "dbi_query_..."
      }
    }
  }
}

Step 4: Restart & Verify

1. Save and close the claude_desktop_config.json file.
2. Completely quit Claude Desktop (not just close the window).
3. Reopen Claude Desktop.
4. Ask Claude: "Show me all datasources".

---

Security Deep Dive

Row Level Security (RLS)

RLS filters (TableRowFilter) are injected into the AST of every query via sqlshield.

• Logic: Global Filters AND (Role A Filter OR Role B Filter).
• Example: region = 'US' is automatically appended if the user is in the "US Region" group.

Privacy & Column Hiding

• PrivateColumnSelector: Columns marked as private (e.g., salary) are removed from the schema context sent to the LLM.
• Access: Only roles explicitly granted permission in GroupColumnSelector will see these columns.

---

Testing & Quality

• Coverage: 99% unit test coverage for core services.
• Benchmarks: Validated performance gains (~28x) for large datasets.

To run tests:

pytest tests/unit/services/pagination/

---

License

Apache 2.0 - See LICENSE for details.