# 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()` — in QUERY context (SELECT/INSERT/UPDATE values), use `datetime('now')`.
- **DEFAULT must be a literal constant. `datetime('now')` is REJECTED in column DEFAULT clauses** — D1 enforces literal-constant defaults (error: `"default value of column [...] is not constant"`). Pass timestamps from your application as parameters instead: `new Date().toISOString()`. If you want a DB-side default, use a parenthesized expression like `DEFAULT (unixepoch('now') * 1000)`, which is allowed.
- `RETURNING` is supported. `INSERT INTO users (name) VALUES (?) RETURNING id, name` returns the inserted row in one round-trip — use this instead of `INSERT` followed by a `SELECT last_insert_rowid()`.
- `ON CONFLICT` is supported. `INSERT INTO users (id, name) VALUES (?, ?) ON CONFLICT(id) DO UPDATE SET name = excluded.name` works as expected (upsert pattern).
- `json_extract()` instead of `->` / `->>` operators
- Multi-statement SQL is supported without params (semicolons between statements). Parameterized requests must contain one statement. The top-level `rowCount` is aggregate across all statements; per-statement detail is in the `per_statement` array of the response (see Edge Usage below).

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.

> **Server-side only.** `coh_application_key` is a secret. Call this from your `vercel-hosting` API routes, `cloudflare-workers`, or your own server tier — never from a browser, mobile app, or other client-side code. See the canonical key-secrecy directive in `.cohesivity` for details.

## 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 (single-statement):** `{ "rows": [...], "rowCount": N }`
- **Response (multi-statement):** same shape plus `per_statement: [{ "rowCount": ..., "rowsRead": ..., "rowsWritten": ... }, ...]`. Top-level `rows` reflect the LAST statement; top-level `rowCount` is aggregate. Iterate `per_statement` for per-statement results.

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 (42);" }`

## Limits

- Max query length: 50,000 chars
- Max rows returned: 10,000
- Storage is governed by the Launch Rate Limits section below and the account bucket/fluid model
- One writer at a time (SQLite WAL mode)
- ~30-80ms latency per query (request stays within Cloudflare's network)

## Launch Rate Limits

Ephemeral tenants pause as a whole if any authoritative hard cap below is exceeded. Claimed tiers use account-scoped buckets shared across every project owned by the Cohesivity user; OpenAI, Deepgram, and Exa are fluid-only after tier, rate, and concurrency checks; Deepgram has no fixed monthly usage bucket for claimed tiers.

**Ephemeral**

- rows read: 250000 per ephemeral tenant lifetime before claim or expiry
- rows written: 50000 per ephemeral tenant lifetime before claim or expiry
- requests: 30 per minute

**Claimed Free**

- requests: 120 per minute
- rows read: 10000000 per month
- rows written: 1000000 per month

**Claimed Plus**

- requests: 600 per minute
- rows read: 100000000 per month
- rows written: 10000000 per month

**Claimed Pro**

- requests: 3000 per minute
- rows read: 500000000 per month
- rows written: 50000000 per month