# Database (SQLite/D1)

Per-tenant SQLite database on Cloudflare D1. Execute SQL over HTTP — no database setup, no credentials, no TCP connections needed.

## Prerequisites

Provision this resource before use. Edge requests without provisioning will error.

### Provision

    curl -s -X POST https://cohesivity.ai/api/resources/database \
      -H "Authorization: Bearer <coh_management_key>"

### Delete

    curl -s -X DELETE https://cohesivity.ai/api/resources/database \
      -H "Authorization: Bearer <coh_management_key>"

**Important:** Provision this resource now, before building or running the application. Provisioning is the agent's job, not the application's.

## Provisioning Region

You can choose where D1 writes should live when you provision the database:

    curl -s -X POST https://cohesivity.ai/api/resources/database \
      -H "Authorization: Bearer <coh_management_key>" \
      -H "Content-Type: application/json" \
      -d '{"write_region":"us"}'

You can send direct D1 regions (`wnam`, `enam`, `weur`, `eeur`, `apac`, `oc`) or simpler aliases like `us-west`, `us`, `eu`, `eu-east`, `apac`, and `australia`. If omitted, Cohesivity defaults to `apac`.

## SQL Dialect

This is a **SQLite** database, not PostgreSQL. Key differences:
- Use `INTEGER PRIMARY KEY AUTOINCREMENT` instead of `SERIAL`
- Use `TEXT` instead of `VARCHAR`, `TIMESTAMPTZ`, `JSONB`
- Use `?` or `?1` parameter placeholders, not `$1`
- No arrays, no `ILIKE`, no `NOW()` — use `datetime('now')` instead
- `json_extract()` instead of `->` / `->>` operators
- Multi-statement SQL is supported (semicolons between statements)

Note: PostgreSQL was available in an earlier version. If agent feedback suggests a need for PostgreSQL features (JSONB, arrays, advanced SQL), we may revisit this decision.

## Edge Usage

- **Base URL:** https://cohesivity.ai/edge/database
- **Recommended auth:** `POST https://cohesivity.ai/edge/session?key=<coh_application_key>` → `Authorization: Bearer <token>`
- **Fallback auth:** `?key=<coh_application_key>` query parameter
- **Method:** POST with JSON body `{ "query": "SQL", "params": [...] }`
- **Response:** `{ "rows": [...], "rowCount": N }`

All queries are strongly consistent — every read hits the primary database. No bookmarks or session management needed.

## Examples

- Mint edge token: `POST https://cohesivity.ai/edge/session?key=<coh_application_key>` → `{ "token": "<jwt>", "expires_in": 60 }`
- Create table: `POST https://cohesivity.ai/edge/database` with `Authorization: Bearer <token>` and `{ "query": "CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT)" }`
- Insert: `{ "query": "INSERT INTO users (name, email) VALUES (?, ?)", "params": ["alice", "alice@example.com"] }`
- Select: `{ "query": "SELECT * FROM users WHERE name = ?", "params": ["alice"] }`
- Update: `{ "query": "UPDATE users SET email = ? WHERE id = ?", "params": ["new@example.com", 1] }`
- Delete: `{ "query": "DELETE FROM users WHERE id = ?", "params": [1] }`
- JSON: `{ "query": "SELECT json_extract(data, '$.name') AS name FROM items" }`
- Multi-statement: `{ "query": "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT); INSERT INTO users (name) VALUES (?);", "params": ["alice"] }`

## Limits

- Max query length: 50,000 chars
- Max rows returned: 10,000
- Max database size: 10 GB
- One writer at a time (SQLite WAL mode)
- ~30-80ms latency per query (request stays within Cloudflare's network)