Metadata-Version: 2.4
Name: terno-dbi
Version: 0.1.0
Summary: Database Interface Layer - Multi-database connectivity with SQLShield integration
Project-URL: Homepage, https://github.com/ternoai/ternodbi
Project-URL: Repository, https://github.com/ternoai/ternodbi
Project-URL: Documentation, https://docs.ternoai.com/dbi
Author-email: TernoAI <team@ternoai.com>
License-Expression: Apache-2.0
License-File: LICENSE
Keywords: ai,database,django,mcp,sql,sqlalchemy
Classifier: Development Status :: 3 - Alpha
Classifier: Framework :: Django
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Requires-Python: >=3.10
Requires-Dist: databricks-sql-connector>=2.0
Requires-Dist: databricks-sqlalchemy>=2.0.8
Requires-Dist: django-reversion==5.1.0
Requires-Dist: django>=4.2
Requires-Dist: google-cloud-bigquery>=3.0
Requires-Dist: markdown>=3.0
Requires-Dist: mcp>=1.25.0
Requires-Dist: oracledb>=1.4
Requires-Dist: pandas>=2.0
Requires-Dist: psycopg2-binary>=2.9
Requires-Dist: pyarrow>=14.0
Requires-Dist: pymysql>=1.1
Requires-Dist: python-dotenv>=1.0.0
Requires-Dist: requests>=2.32.5
Requires-Dist: snowflake-sqlalchemy>=1.8.2
Requires-Dist: sqlalchemy-bigquery>=1.9
Requires-Dist: sqlalchemy>=2.0
Requires-Dist: sqlglot>=28.5.0
Requires-Dist: sqlshield>=0.0.20
Provides-Extra: agents
Requires-Dist: crewai>=0.28.0; extra == 'agents'
Requires-Dist: langchain-core>=0.3.15; extra == 'agents'
Requires-Dist: langchain-openai>=0.2.5; extra == 'agents'
Requires-Dist: langchain>=0.3.7; extra == 'agents'
Requires-Dist: langgraph-checkpoint>=4.0.0; extra == 'agents'
Requires-Dist: langgraph-prebuilt>=1.0.7; extra == 'agents'
Requires-Dist: langgraph-sdk>=0.3.3; extra == 'agents'
Requires-Dist: langgraph==1.0.7; extra == 'agents'
Requires-Dist: pyautogen<0.4; extra == 'agents'
Provides-Extra: all-db
Requires-Dist: databricks-sql-connector>=2.0; extra == 'all-db'
Requires-Dist: google-cloud-bigquery>=3.0; extra == 'all-db'
Requires-Dist: oracledb>=1.4; extra == 'all-db'
Requires-Dist: psycopg2-binary>=2.9; extra == 'all-db'
Requires-Dist: pymysql>=1.1; extra == 'all-db'
Requires-Dist: snowflake-sqlalchemy>=1.5; extra == 'all-db'
Requires-Dist: sqlalchemy-bigquery>=1.9; extra == 'all-db'
Provides-Extra: bigquery
Requires-Dist: google-cloud-bigquery>=3.0; extra == 'bigquery'
Requires-Dist: sqlalchemy-bigquery>=1.9; extra == 'bigquery'
Provides-Extra: celery
Requires-Dist: celery>=5.3; extra == 'celery'
Requires-Dist: redis>=5.0; extra == 'celery'
Provides-Extra: databricks
Requires-Dist: databricks-sql-connector>=2.0; extra == 'databricks'
Provides-Extra: dev
Requires-Dist: pytest-django>=4.5; extra == 'dev'
Requires-Dist: pytest>=7.0; extra == 'dev'
Requires-Dist: ruff>=0.1; extra == 'dev'
Provides-Extra: mcp
Requires-Dist: mcp>=1.0; extra == 'mcp'
Provides-Extra: mysql
Requires-Dist: pymysql>=1.1; extra == 'mysql'
Provides-Extra: oracle
Requires-Dist: oracledb>=1.4; extra == 'oracle'
Provides-Extra: postgres
Requires-Dist: psycopg2-binary>=2.9; extra == 'postgres'
Provides-Extra: snowflake
Requires-Dist: snowflake-sqlalchemy>=1.5; extra == 'snowflake'
Description-Content-Type: text/markdown

# TernoDBI: Database Interface Layer

[![License](https://img.shields.io/badge/License-Apache%202.0-blue.svg)](https://opensource.org/licenses/Apache-2.0)
[![Python](https://img.shields.io/badge/python-3.10%2B-blue)](https://www.python.org/downloads/)
[![Django](https://img.shields.io/badge/django-4.2%2B-green)](https://www.djangoproject.com/)
[![Coverage](https://img.shields.io/badge/coverage-99%25-brightgreen)](tests/unit/services/pagination/)

**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](docs/setup.md)**: Installation, Environment Variables, and Server Startup.
*   **[Architecture](docs/architecture.md)**: System design, request flow, and component breakdown.
*   **[MCP Integration](docs/mcp-guide.md)**: How to connect agents (Claude Desktop, Terno Agents).
*   **[Security & SQLShield](docs/security.md)**: Deep dive into our security model and token system.

---

## Installation

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

---

## Configuration

Copy the sample environment file to start:

```bash
cp server/env-sample.sh server/env.sh
source server/env.sh
```

### 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

```bash
cd server
python manage.py migrate
python manage.py runserver 0.0.0.0:8000
```

### 2. Management Commands (CLI)

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

```bash
# General Query Token
python manage.py issue_token --name "Claude Agent" --type query --expires 30

# Admin Token (Full Access)
python manage.py issue_token --name "System Admin" --type admin

# Scoped Token (Specific Datasource)
python manage.py issue_token --name "Finance Data Only" --type query --datasource 1
```

### 3. Query API & Pagination

**Offset Mode (Default)** - Best for UI.
```json
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.
```json
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](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`
![Claude Desktop Settings](assets/config.png)

#### Step 3: Add MCP Server Configuration

Add the following configuration to your `claude_desktop_config.json`:

**Local Development:**
```json
{
  "mcpServers": {
    "ternodbi-admin": {
      "command": "/path/to/your/venv/bin/dbi-mcp",
      "args": ["admin"],
      "env": {
        "TERNODBI_API_URL": "http://127.0.0.1:8000",
        "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:8000",
        "TERNODBI_API_KEY": "dbi_query_..."
      }
    }
  }
}
```
> [!TIP]
> Run `which dbi-mcp` in your terminal to find the absolute path to use in the configuration above.
**Production (UVX):**
```json
{
  "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:
```bash
pytest tests/unit/services/pagination/
```

---

## License
Apache 2.0 - See [LICENSE](LICENSE) for details.
