# https://db.opelly.me — Postgres over HTTPS (and over postgres://)

This service gives an app or agent access to ONE Postgres 17 database through an API key.
Two ways in, same key, same permissions:

- HTTP JSON API: `https://db.opelly.me/api/v1/...` — send SQL, get JSON rows back.
- Postgres wire protocol: `postgresql://<db>:<key>@pg.opelly.me:443/<db>?sslmode=require`
  — works with any Postgres driver, ORM or migration tool.

Human docs: https://db.opelly.me/ · OpenAPI: https://db.opelly.me/api/openapi.json · This file: https://db.opelly.me/llms.txt

## Which one to use

- Use the **postgres:// URL** when the app uses an ORM or migration tool (Prisma, Drizzle, sqlx, SQLAlchemy,
  Django, ActiveRecord…), needs transactions that span several round-trips, LISTEN/NOTIFY, COPY, or a
  long-lived connection pool.
- Use the **HTTP API** for serverless/edge functions, scripts, agents, cron jobs, and anything that
  can't hold a TCP connection. Multi-statement atomic work goes through `/api/v1/transaction`.

## Keys and access

Key format: `opdb_<id>_<secret>` (id = 10 chars `[a-z0-9]`, secret = 32 chars `[A-Za-z0-9]`).
A key is bound to exactly one database and one access level:

- `read` — SELECT only. Writes/DDL fail with sqlstate `25006` (read-only transaction) or `42501`
  (permission denied) → HTTP 403. Don't retry writes with a read key; ask for a `full` key.
- `full` — acts as the database owner: INSERT/UPDATE/DELETE, CREATE/ALTER/DROP, migrations.

Keys may also have an expiry, an IP/CIDR allowlist, a custom row cap or statement timeout.
Treat the key like a password: keep it in an env var (e.g. `DB_API_KEY`), never in code or logs.

HTTP auth header: `Authorization: Bearer opdb_...` (or `X-Api-Key: opdb_...`).

## Quick start

```sh
export DB_API_KEY=opdb_xxxxxxxxxx_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

# 1. What can this key do? (database name, access level, limits, postgres_url)
curl -s https://db.opelly.me/api/v1/whoami -H "Authorization: Bearer $DB_API_KEY"

# 2. What tables exist? (columns, types, PKs, FKs, indexes, enums, your privileges)
curl -s https://db.opelly.me/api/v1/schema -H "Authorization: Bearer $DB_API_KEY"

# 3. Run one statement with parameters
curl -s https://db.opelly.me/api/v1/query -H "Authorization: Bearer $DB_API_KEY" \
  -H 'Content-Type: application/json' \
  -d '{"sql": "select id, name from users where id = $1", "params": [42]}'

# 4. Several statements atomically (all or nothing)
curl -s https://db.opelly.me/api/v1/transaction -H "Authorization: Bearer $DB_API_KEY" \
  -H 'Content-Type: application/json' \
  -d '{"statements": [
        {"sql": "insert into orders(user_id) values ($1) returning id", "params": [42]},
        {"sql": "update users set order_count = order_count + 1 where id = $1", "params": [42]}
      ]}'
```

Always start with `whoami` and `schema` before writing SQL against an unfamiliar database.

## Endpoints

| Method | Path | Auth | Purpose |
|---|---|---|---|
| GET | `/api/v1/whoami` | key | Key's database, access, limits, endpoints, postgres_url |
| GET | `/api/v1/schema[?schema=public]` | key | Tables/views, columns, PKs, FKs, indexes, enums |
| POST | `/api/v1/query` | key | Run ONE SQL statement |
| POST | `/api/v1/transaction` | key | Run up to 100 statements in one transaction |
| GET | `/api/openapi.json`, `/llms.txt`, `/`, `/healthz` | no | Docs / health |

### POST /api/v1/query

Request (only `sql` is required):
```json
{"sql": "select id, name from users where id = $1", "params": [42],
 "row_mode": "object", "max_rows": 1000, "timeout_ms": 5000}
```
- `row_mode`: `"object"` (default, rows are `{"col": value}`) or `"array"` (rows are `[value, ...]`,
  ordered like `columns`; use it for wide/large results or duplicate column names).
- `max_rows`, `timeout_ms` can only LOWER the key's limits.
- Exactly one statement. `;`-separated scripts are rejected. BEGIN/COMMIT/ROLLBACK/SAVEPOINT-style
  transaction control is rejected — use `/api/v1/transaction`.

Response 200:
```json
{"command": "SELECT", "row_count": 1,
 "columns": [{"name": "id", "type": "int4"}, {"name": "name", "type": "text"}],
 "rows": [{"id": 42, "name": "Ada"}],
 "notices": [], "warnings": [], "duration_ms": 1.7}
```
- `command`: Postgres command tag verb (`SELECT`, `INSERT`, `UPDATE`, `DELETE`, `CREATE TABLE`, …).
- `row_count`: rows returned for SELECT / `RETURNING`; rows affected for INSERT/UPDATE/DELETE.
- Need the inserted id? Use `insert ... returning id`.
- Duplicate column names in object mode get `_2`, `_3` suffixes plus a `warnings` entry.
- Each statement runs in its own transaction. If the result would exceed `max_rows` or 16 MiB, it is
  rolled back and you get `413 result_too_large` — a capped `UPDATE ... RETURNING` never half-applies.
- `VACUUM`, `CREATE INDEX CONCURRENTLY` etc. are automatically retried outside a transaction.

### POST /api/v1/transaction

```json
{"statements": [{"sql": "insert into t(a) values ($1) returning id", "params": [1]},
                {"sql": "update counters set n = n + 1"}],
 "read_only": false, "row_mode": "object", "max_rows": 1000, "timeout_ms": 10000}
```
Response 200: `{"results": [<query result without duration_ms>, ...], "duration_ms": 3.1}` —
one result per statement, in order. Any failure rolls back everything; the error has
`statement_index` (0-based). Max 100 statements. Statements can't see each other's results
through params — use CTEs (`with x as (insert ... returning id) insert ... select id from x`) or
a postgres:// connection when a later statement needs an earlier value.

### GET /api/v1/whoami
```json
{"key_id": "k7x2m9q4ab", "label": "blog prod", "database": "town", "access": "read",
 "expires_at": null,
 "limits": {"max_rows": 10000, "max_response_bytes": 16777216, "statement_timeout_ms": 30000,
            "max_concurrent_requests": 8, "max_postgres_connections": 10},
 "endpoints": {"query": "https://db.opelly.me/api/v1/query", "transaction": "https://db.opelly.me/api/v1/transaction",
               "schema": "https://db.opelly.me/api/v1/schema", "docs": "https://db.opelly.me/llms.txt"},
 "postgres_url": "postgresql://town:<key>@pg.opelly.me:443/town?sslmode=require"}
```

### GET /api/v1/schema
Optional `?schema=public` filter.
```json
{"database": "town", "schemas": ["public"],
 "tables": [{"schema": "public", "name": "users", "kind": "table", "comment": null, "estimated_rows": 120,
   "columns": [{"name": "id", "type": "bigint", "nullable": false,
                "default": "nextval('users_id_seq'::regclass)", "comment": null}],
   "primary_key": ["id"],
   "foreign_keys": [{"name": "posts_user_id_fkey", "columns": ["user_id"],
                     "references": {"schema": "public", "table": "users", "columns": ["id"]}}],
   "indexes": [{"name": "users_pkey", "definition": "CREATE UNIQUE INDEX ...", "unique": true, "primary": true}],
   "privileges": {"select": true, "insert": false, "update": false, "delete": false}}],
 "enums": [{"schema": "public", "name": "mood", "values": ["sad", "ok", "happy"]}]}
```
`kind` ∈ `table`, `view`, `materialized_view`, `partitioned_table`, `foreign_table`.
`estimated_rows` comes from planner statistics (cheap, approximate) — don't `count(*)` big tables to find out.

## Parameters

Use `$1, $2, …` placeholders with values in `params`. NEVER build SQL by string-interpolating user input.

| JSON param | Sent to Postgres as |
|---|---|
| `null` | NULL |
| `true`/`false` | boolean |
| number | numeric text (exact) |
| string | text (Postgres converts to the inferred type: uuid, timestamptz, date, enum, …) |
| object | JSON text (for json/jsonb params) |
| array | Postgres array (`{1,2,3}`), or JSON text when the parameter's type is json/jsonb |

Postgres infers each parameter's type from context. Cast when it can't: `select $1::int + 1`,
`where id = any($1::bigint[])`, `values ($1::jsonb)`.

## Result type mapping

| Postgres | JSON |
|---|---|
| NULL | `null` |
| bool | `true` / `false` |
| int2, int4, int8, oid | number. int8 is exact in the JSON text, but JavaScript `JSON.parse` loses precision above 2^53 — select `col::text` if that matters |
| float4, float8, numeric | number (exact digits); `NaN` / `Infinity` → string |
| json, jsonb | nested JSON |
| arrays | JSON arrays, elements mapped recursively |
| timestamptz | `"2026-11-19T12:34:56.789+00:00"` (session TimeZone is UTC) |
| timestamp / date / time | `"2026-11-19T12:34:56.789"` / `"2026-11-19"` / `"12:34:56"` |
| bytea | `"\\x0a0b…"` (Postgres hex text) |
| anything else (uuid, text, interval, inet, enums, pgvector, …) | string (Postgres text form) |

Each column in `columns` has its Postgres type name (`int4`, `text`, `timestamptz`, `_int4` for int4[] …).

## Limits

| Limit | Default |
|---|---|
| Rows per statement result | 10,000 (per-key configurable up to 100,000) |
| Response size | 16 MiB |
| Statement timeout | 30 s (per-key configurable up to 300 s); the statement is cancelled in Postgres |
| Request body | 2 MiB |
| Concurrent HTTP requests per key | 8 |
| Postgres (wire) connections per key | 10 |
| Statements per transaction | 100 |

Paginate instead of pulling everything: `order by id limit 500`, then keyset
`where id > $1 order by id limit 500` with the last id seen. Avoid large OFFSETs.
Aggregate in SQL (`count`, `sum`, `group by`) instead of fetching rows to count them.

## Errors

All errors look like:
```json
{"error": {"code": "sql_error", "message": "relation \"userz\" does not exist", "sqlstate": "42P01",
           "detail": null, "hint": null, "position": 15, "statement_index": null,
           "docs": "https://db.opelly.me/#errors"}}
```
`position` is a 1-based character offset into `sql`. `statement_index` is set for `/transaction`.

| HTTP | code | Meaning → what to do |
|---|---|---|
| 400 | `invalid_request` | Bad JSON, missing `sql`, multiple statements, or BEGIN/COMMIT → fix the request; split scripts into `/transaction` statements |
| 400 | `sql_error` | Postgres rejected the SQL → read `message`/`hint`/`sqlstate`, check `/api/v1/schema`, fix the SQL. `23505` unique violation, `23503` FK violation, `42P01` no such table, `42703` no such column, `22P02` bad input syntax |
| 401 | `unauthorized` | Missing, unknown, revoked or expired key → check the header; get a new key |
| 403 | `forbidden` | IP not allowed, database disabled, `42501` permission denied, or `25006` write with a read key → don't retry; needs a different key |
| 404 | `not_found` | Unknown route → see this file |
| 408 | `timeout` | Statement exceeded its timeout and was cancelled → add indexes/filters, paginate, or raise the key's timeout |
| 413 | `result_too_large` | Over `max_rows` or 16 MiB; nothing was applied → add `LIMIT`, paginate, select fewer columns |
| 429 | `too_many_requests` | Too many concurrent requests for this key, or too many failed auths → back off and retry with jitter |
| 503 | `upstream_unavailable` | Postgres unreachable or pool exhausted → retry with backoff |
| 500 | `internal` | Proxy bug → retry once; report if persistent |

Retry only 429/503 (and 408 if the query is safe to repeat). Never blindly retry non-idempotent writes after a timeout.

## Postgres wire endpoint (postgres://)

```
postgresql://<database>:<key>@pg.opelly.me:443/<database>?sslmode=require
```
- Port is **443**. TLS is mandatory. `sslmode=require` works with every driver; with libpq ≥ 16 you can verify the certificate via `sslmode=verify-full&sslrootcert=system` (don't put `sslrootcert=system` in URLs for node-postgres and other non-libpq drivers — they treat it as a file path).
- The **password is the key**. The database name in the URL must equal the key's database.
  The user name is ignored — use the database name by convention.
- Everything Postgres supports works: transactions, prepared statements, COPY, LISTEN/NOTIFY,
  migrations — limited by the key's role (`read` = SELECT only).
- Both classic STARTTLS and `sslnegotiation=direct` work. Query cancel needs a client that sends cancel
  requests over TLS (libpq ≥ 17, most native drivers); otherwise rely on `statement_timeout`.
- Keep pools small (≤ 10 connections per key).

psql:
```sh
psql "postgresql://town:$DB_API_KEY@pg.opelly.me:443/town?sslmode=require"
```

Node (node-postgres):
```js
import pg from "pg";
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, ssl: true, max: 5 });
const { rows } = await pool.query("select * from users where id = $1", [42]);
```

Python (psycopg 3):
```python
import os, psycopg
with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    rows = conn.execute("select * from users where id = %s", (42,)).fetchall()
```

Go (pgx v5):
```go
pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
var name string
err = pool.QueryRow(ctx, "select name from users where id = $1", 42).Scan(&name)
```

Rust (sqlx):
```rust
let pool = sqlx::postgres::PgPoolOptions::new().max_connections(5)
    .connect(&std::env::var("DATABASE_URL")?).await?;
let name: String = sqlx::query_scalar("select name from users where id = $1")
    .bind(42_i64).fetch_one(&pool).await?;
```

Prisma (`schema.prisma`): `datasource db { provider = "postgresql"  url = env("DATABASE_URL") }` with
`DATABASE_URL="postgresql://town:<key>@pg.opelly.me:443/town?sslmode=require&connection_limit=5"`.
Migrations (`prisma migrate deploy`, sqlx migrate, alembic, …) need a `full` key.

## HTTP client snippets

JavaScript (fetch; works in Node 18+, Deno, Bun, Cloudflare Workers, browsers — but never ship a key to a browser):
```js
async function sql(sql, params = []) {
  const res = await fetch("https://db.opelly.me/api/v1/query", {
    method: "POST",
    headers: { "Authorization": `Bearer ${process.env.DB_API_KEY}`, "Content-Type": "application/json" },
    body: JSON.stringify({ sql, params }),
  });
  const body = await res.json();
  if (!res.ok) throw new Error(`${body.error.code}: ${body.error.message}`);
  return body.rows;
}
const users = await sql("select id, name from users where created_at > $1 order by id limit 100", ["2026-01-01"]);
```

Python (requests):
```python
import os, requests
S = requests.Session()
S.headers["Authorization"] = f"Bearer {os.environ['DB_API_KEY']}"

def sql(sql, params=()):
    r = S.post("https://db.opelly.me/api/v1/query", json={"sql": sql, "params": list(params)}, timeout=60)
    body = r.json()
    if not r.ok:
        raise RuntimeError(f"{body['error']['code']}: {body['error']['message']}")
    return body["rows"]

rows = sql("select id, name from users where id = $1", [42])
```

## Rules of thumb for agents

1. `GET /api/v1/whoami`, then `GET /api/v1/schema` — learn the access level and real column names first.
2. Always use `$n` parameters; never interpolate values into SQL strings.
   (Server-side only: the API deliberately sends no CORS headers, so browsers can't call it directly — keep
   the key in your backend or serverless function, never in frontend code.)
3. Paginate (`LIMIT` + keyset) and aggregate in SQL; results over 10,000 rows / 16 MiB are refused.
4. A `read` key can't write — a 403 with sqlstate `25006`/`42501` means you need a `full` key, not a retry.
5. Group dependent writes into one `/api/v1/transaction` call (or one statement with CTEs).
6. Prefer the postgres:// URL for apps with an ORM, migrations, or connection pools.
7. Schema changes (`full` key) should be idempotent where possible: `create table if not exists`, `add column if not exists`.
