# Database Connections

Connect external databases to your AVCodex agent so it can answer questions from live data: room records, device inventories, ticket history, programmer time logs, project status.

Database Connections let your agent query external databases mid-conversation. Instead of hardcoding answers or uploading static CSVs of your asset list, the agent runs live SQL against your PostgreSQL, MySQL, or SQLite database.

## What It Does

When a database connection is configured, your agent gets two tools:

| Tool | Purpose |
|------|---------|
| **inspectDatabaseSchema** | Lists tables and columns so the agent knows what data is available |
| **queryDatabase** | Runs SQL queries and returns results |

The agent calls `inspectDatabaseSchema` first, learns the real shape of your data, and writes accurate queries against the actual schema. No hallucinated column names.

```
Tech: "How many DM-NVX endpoints are deployed in the West region?"
    |
Agent calls inspectDatabaseSchema. Sees devices and rooms tables.
    |
Agent calls queryDatabase: SELECT count(*) FROM devices
  JOIN rooms USING (room_id)
  WHERE devices.model LIKE 'DM-NVX%' AND rooms.region = 'West'
    |
Agent: "There are 184 DM-NVX endpoints across 47 rooms in the West region."
```

## Requirements

- **Studio Pro plan** or higher.
- An externally reachable database (PostgreSQL, MySQL, or SQLite).
- A connection string with credentials that have appropriate permissions.

> **Note:** Database Connections run server-side. Your connection string is encrypted at rest, never sent to the model, and never returned in API responses.

## Setting Up a Connection

**1. Open Capabilities**

In your agent's **Build** page, open the **Capabilities** tab and scroll to **Database Connections**.

**2. Add a Connection**

Click **Add connection** and fill in:

- **Connection name**: a friendly label (for example "Asset DB" or "Production Ticketing").
- **Database type**: PostgreSQL, MySQL, or SQLite.
- **Connection string**: your database URL (for example `postgres://user:password@host:5432/avops`).
- **Default access level**: Read-only (recommended) or Read/Write.

**3. Test the Connection**

After saving, click the wrench icon to test. AVCodex verifies connectivity and discovers your schema (tables and columns). The schema is cached so the agent knows the exact shape.

**4. Preview Queries (optional)**

Click the edit icon, switch to **Query Preview**, and run a few SQL statements to verify the connection works the way you expect before publishing.

## Security Features

Database Connections include several layers of protection.

### Encrypted Credentials

Connection strings are encrypted with AES-256-CTR before storage. They are decrypted only at query execution time on the server, and never appear in API responses, logs, or LLM prompts.

### Read-Only Mode

When set to **Read-only** (the default), AVCodex enforces read-only mode at the database layer:

- **PostgreSQL**: `SET default_transaction_read_only = on`
- **MySQL**: `SET SESSION TRANSACTION READ ONLY`
- **SQLite**: `PRAGMA query_only = true`

So even if the agent generates an `INSERT` or `UPDATE`, the database itself rejects it.

### DDL Blocking

These statements are blocked before they reach your database, regardless of access level:

- `DROP DATABASE`
- `DROP TABLE`
- `TRUNCATE`
- `ALTER TABLE`

You can customize the blocklist in the connection settings.

### Row Limits

Every query has a configurable max row count (default 1,000). Anything beyond it is truncated. Your agent will not pull a million-row export by accident.

### Column Masking

Hide sensitive columns from the agent entirely. Masked columns are removed from:

1. The schema description (the agent does not see them).
2. Query results (values are stripped even if selected).

Use this for columns like `password_hash`, `client_billing_rate`, `vendor_cost`, `api_key`, or any PII you do not want the agent to read.

### Table Allowlist

Restrict which tables the agent can query. Queries that reference tables outside the allowlist are rejected.

> **Warning:** The table allowlist matches patterns and can be bypassed with advanced SQL constructs (CTEs, subqueries). For real data isolation, use database-level permissions on the connection credentials. The allowlist is a guardrail, not a security boundary.

### SSRF Protection

Connection strings are validated to prevent Server-Side Request Forgery:

- Private hostnames are blocked (localhost, .local, .internal).
- DNS resolution checks both IPv4 and IPv6 for private addresses.

## Per-User Access Control (RBAC)

Each connection has a **default access level** that applies to all users. Override per user when needed:

| Access Level | What It Allows |
|-------------|----------------|
| **Read-only** | `SELECT` queries only |
| **Read/Write** | `SELECT`, `INSERT`, `UPDATE`, `DELETE` |
| **None** | No access (connection hidden from this user) |

A typical pattern: programmers and senior techs get read-only on the asset DB, dispatch managers get read/write on the ticketing DB, clients get **None**.

To manage per-user overrides, use the API:

```bash
# Set a user to read/write
curl -X PUT /api/applications/{appId}/database-connections/{connectionId}/overrides/{consumerId} \
  -H "Content-Type: application/json" \
  -d '{"accessLevel": "readwrite"}'

# Remove an override (reverts to default)
curl -X DELETE /api/applications/{appId}/database-connections/{connectionId}/overrides/{consumerId}
```

## Multiple Connections

You can add up to 10 database connections per agent. With more than one configured:

- The agent sees every connection name and its schema.
- The agent must specify which connection to query.
- Each connection has independent access controls, row limits, and column masking.

Useful when your data is split across systems: an asset DB, a ticketing DB, and a programmer time-tracking DB, for example.

## Query Timeout

Queries have a configurable timeout (default 10 seconds, max 60). Long-running queries are killed so the chat does not hang.

## Best Practices

1. **Use a read-only DB user.** Create a credential with `SELECT`-only permissions. Defense in depth on top of read-only mode.
2. **Mask sensitive columns.** Add `password_hash`, `client_billing_rate`, `vendor_cost`, and any PII to the mask list. The agent will not know they exist.
3. **Pick the right row limit.** For analytical questions, 1,000 rows is usually fine. For room or device lookups, 50 to 100 is often enough.
4. **Test before publishing.** Use Query Preview to dry-run common questions ("which rooms have DSPs out of warranty?") before going live.
5. **Use a table allowlist for focus.** Even though it is not a security boundary, it helps the agent zero in on the relevant tables (rooms, devices, tickets) instead of getting distracted by unrelated schemas.

## Supported Database Types

| Database | Connection String Format | Notes |
|----------|------------------------|-------|
| **PostgreSQL** | `postgres://user:pass@host:5432/dbname` | Most common. Full schema discovery via `information_schema`. |
| **MySQL** | `mysql://user:pass@host:3306/dbname` | Schema discovery via `information_schema`. |
| **SQLite** | Relative file path (e.g. `data/avops.db`) | File-based. Relative paths only. |

> **Tip:** If your password has special characters (`#`, `*`, `@`), URL-encode them. `p@ss#word` becomes `p%40ss%23word`.

## Billing

Database queries are included in your LLM token billing. There is no extra per-query charge. You pay only for the agent tokens used to process the query and write a response.

## Troubleshooting

### "Connection must not point to private hosts"

Your database must be reachable from the internet. AVCodex servers cannot connect to databases on private networks (localhost, 10.x.x.x, 192.168.x.x).

**Fix:** Use a public IP or a connection proxy.

### "Schema not yet discovered"

The connection was saved but never tested. Click the wrench icon to test and discover the schema.

### "Table not in the allowed list"

Your table allowlist is blocking the query. Add the table or drop the allowlist.

### "Cannot execute INSERT in a read-only transaction"

The connection is read-only. Change the default access level to **Read/Write**, or set a per-user override for the specific user that needs writes.

### Query timeout

Complex queries may exceed the limit. Raise the timeout in connection settings (up to 60 seconds), or optimize with indexes and explicit `LIMIT` clauses.

*AVCodex · Your AV expertise. Amplified by AI.*