# Unbase

> Unbase is a zero-config SQL (SQLite) database over HTTP. `POST /v1/projects` returns a project and a bearer token in one call — no signup, no dashboard, no separate API-key step. Every subsequent call is plain JSON over HTTPS. Each project also ships a Supabase-style per-project Auth service for your app's end users.

Base URL: `https://api.unbase.dev`

Ids: project ids are `unbase_xxxxxxxxxxxx`, account ids are `acct_...`, Auth end-user ids are `user_...`.

## Auth (API credentials)

Each project has **two keys**, both returned by `POST /v1/projects`:

- **Secret key** (a.k.a. "service role"), format `${projectId}.${signature}` (HMAC-SHA256). Full SQL read/write plus admin. Sent as `Authorization: Bearer <secretKey>`. It is the permanent data-plane credential — there is no key rotation/management API; it stays valid until the project is deleted. A key only ever authorizes the one `projectId` embedded in it — using it against a different `:id` in the URL returns `401`. **Never expose it in a browser.**
- **Anon key** (a.k.a. "publishable"), format `${projectId}.pk.${signature}`. Safe to embed in a client app. Sent in an `apikey` header, it unlocks the project's per-project **Auth service** only (`/v1/projects/:id/auth/*`, see below) — it cannot read or write your tables. The secret key is also accepted in the `apikey` header.

Other credentials:

- No separate signup/login for the project itself. Anonymous projects can later be attached to an email via the two-step magic-link claim flow (`POST /v1/claim` then `POST /v1/claim/verify`), which upgrades the plan but does not change the keys.
- Paid (`founder`/`pro`) plan upgrades are applied automatically from Stripe via `POST /v1/stripe/webhook` — not through the bearer-token API.
- The per-project Auth service issues **end-user access tokens**: HS256 JWTs signed with the project's own JWT secret (see the Auth section).

## Endpoints

### `POST /v1/projects`
Create a new project. No auth required.

Request body (optional): `{ "turnstileToken"?: string }` — only relevant to the browser/web creation flow (Cloudflare Turnstile anti-abuse check). Server-to-server / API callers can omit it entirely.

Response `201`:
```json
{ "projectId": "unbase_...", "url": "https://api.unbase.dev/v1/projects/unbase_...", "token": "unbase_....<sig>", "anonKey": "unbase_....pk.<sig>" }
```
The `token` (secret key) is shown exactly once — it is not retrievable again. Store it immediately. The `anonKey` is the publishable key for the Auth service.

New projects start on the **anonymous** plan (7-day retention unless claimed).

---

### `POST /v1/claim`
**Step 1 of 2 — initiate a claim.** Mints a signed, 30-minute magic-link token and emails a claim link (`{SITE_URL}/claim/verify?token=...`) to the given address via Resend. No auth required (the caller must know the `projectId`). The project must already exist (`404` otherwise). This does *not* claim the project — it only sends the link. Requiring the email owner to open the link proves control of the address before the claim is committed; the old single-call flow let anyone claim any known `projectId` to their own email.

Request body: `{ "id": "unbase_...", "email": "user@example.com" }`

Response `200`:
- With a Resend key configured: `{ "sent": true }` (email sent; `devLink` omitted).
- In keyless dev mode (no `RESEND_API_KEY`): `{ "sent": false, "devLink": "https://unbase.dev/claim/verify?token=..." }` — no email is sent and the full claim URL is returned inline so the flow stays usable. Do not run keyless in production.

---

### `POST /v1/claim/verify`
**Step 2 of 2 — complete the claim.** Verifies the magic-link token's HMAC signature and 30-minute expiry, then attaches the anonymous project to the email-based account (idempotent: calling with the same email reuses the account), upgrading its plan from `anonymous` to `free`. No auth required (the token itself is the proof). The existing keys keep working unchanged and the 7-day expiry is removed.

Request body: `{ "token": "<token from the magic link>" }`

Response `200`: `{ "projectId": "unbase_...", "accountId": "acct_...", "plan": "free" }`

Returns `400` if the token is malformed, tampered, or expired.

---

### `POST /v1/stripe/webhook`
Applies paid-plan upgrades/downgrades from Stripe. **Authenticated by the Stripe signature, not a bearer token** — requires the `Stripe-Signature` header, which the Worker verifies via HMAC-SHA256 against `STRIPE_WEBHOOK_SECRET` (5-minute timestamp tolerance). Body is the raw Stripe event JSON.

- `checkout.session.completed`: reads the customer email (`customer_email` or `customer_details.email`) and target plan from the session's `metadata.plan` (`founder` or `pro`, set on the Stripe Payment Link), then upgrades that account and **all** its projects to that plan.
- `customer.subscription.deleted`: downgrades the account back to `free`, but only if `metadata.email` is present on the event (Stripe omits it by default, so the integration must add it; otherwise the event is acknowledged with no downgrade).

Response `200`: `{ "received": true, "applied": boolean, "plan"?: string, "projects"?: number }` (`applied` is `false` for events that carry no actionable upgrade intent). Returns `400` on a bad/missing signature, `503` if `STRIPE_WEBHOOK_SECRET` is not configured.

---

### `POST /v1/projects/:id/query`
Run one SQL statement. Secret key required.

Request body: `{ "sql": "SELECT * FROM t WHERE id = ?", "params"?: [1] }`
- `params` are positional `?` placeholders, standard SQLite binding. Always use them instead of interpolating values into `sql`.
- Any statement not starting with `SELECT`, `PRAGMA`, `EXPLAIN`, or `WITH` is treated as a write for quota/billing purposes.
- Statements referencing tables prefixed `_unbase_` (internal bookkeeping) are rejected with `403`.

Response `200`:
```json
{ "rows": [ { "col": "value" } ], "rowsRead": 1, "rowsWritten": 0 }
```

Response headers on every call:
- `X-Unbase-Rows-Read`, `X-Unbase-Rows-Written` — mirror the body.
- `X-Unbase-Limit-Warning: true` — present only when the account is at or over 100% of its plan's monthly read/write/storage quota. Writes are hard-blocked at 120% of quota (`403`); reads are never blocked by quota (only by a suspended account, also `403`).

---

### `POST /v1/projects/:id/batch`
Run multiple statements as one atomic transaction (all-or-nothing). Secret key required.

Request body: `{ "statements": [ { "sql": "...", "params"?: [...] }, ... ] }`

Response `200`:
```json
{
  "results": [ { "rows": [...], "rowsRead": 0, "rowsWritten": 1 }, ... ],
  "rowsRead": 0,
  "rowsWritten": 1
}
```
`results` is positional, one entry per input statement, in order. Top-level `rowsRead`/`rowsWritten` are the sums across all statements.

---

### `GET /v1/projects/:id/usage`
Current plan, status, and this-calendar-month's row counters. Secret key required.

Response `200`:
```json
{
  "plan": "free",
  "status": "active",
  "sizeBytes": 12345,
  "usageMonth": "2026-07",
  "rowsRead": 420,
  "rowsWritten": 17
}
```
`status` is one of `active`, `limited`, `suspended`. `rowsRead`/`rowsWritten` reset at the start of each calendar month.

---

### `GET /v1/projects/:id/tables`
List the project's user tables with row counts and column info. Secret key required. Internal (`_unbase_*`), SQLite (`sqlite_*`), and Cloudflare (`_cf_*`) tables are excluded.

Response `200`: `{ "tables": [ { "name": "todos", "rowCount": 1, "columns": [ { "name": "id", "type": "INTEGER", "notnull": false, "pk": true }, ... ] } ] }`

---

### `GET /v1/projects/:id/export`
Download a full logical SQL dump of the project. Secret key required.

Response `200`, `content-type: application/sql`, `content-disposition: attachment; filename="<id>.sql"`. Body is plain-text `CREATE TABLE` + `INSERT INTO` statements wrapped in a transaction — **not** a binary `.sqlite` file. Fully replayable: `sqlite3 restored.db < dump.sql` reconstructs an equivalent database.

---

### `DELETE /v1/projects/:id`
Permanently delete the project and its stored exports. Secret key required.

Response `204`, empty body. Irreversible.

## Auth service (per-project end-user auth)

Every project has a built-in, Supabase-style Auth service under `/v1/projects/:id/auth`. It authenticates the **end users of your app** (not the project owner) and hands them JWT sessions. Callers authenticate with the project's **anon key** in an `apikey` header (the secret key also works). Base path below is `AUTH = https://api.unbase.dev/v1/projects/:id/auth`.

A **Session** (returned by signup/signin/verify/token/password/reset) is:
```json
{
  "accessToken": "<HS256 JWT>",
  "tokenType": "bearer",
  "expiresIn": 3600,
  "expiresAt": 1751824800,
  "refreshToken": "<single-use>",
  "user": { "id": "user_...", "email": "u@x.com", "emailConfirmedAt": null, "createdAt": "...", "lastSignInAt": "..." }
}
```
The `accessToken` is an **HS256 JWT signed with the project's own JWT secret** (retrievable via `.../auth/settings`), so the project owner can verify end-user tokens in their own backend. Claims: `{ sub: userId, iss: projectId, role: "authenticated", email, iat, exp }`.

- `POST {AUTH}/signup { email, password }` — `201`, Session. Password min length 8. Requires `apikey`.
- `POST {AUTH}/signin { email, password }` — `200`, Session. Requires `apikey`.
- `POST {AUTH}/magiclink { email, redirectTo? }` — `{ sent, devLink? }`. Emails the end user a link to `redirectTo?token=...` (falls back to `<SITE_URL>/auth/callback?token=...`); your app then calls `.../auth/verify` with the token. Keyless dev mode returns `devLink` inline. Requires `apikey`.
- `POST {AUTH}/verify { token }` — `200`, Session (passwordless; creates the user on first verify). Requires `apikey`.
- `POST {AUTH}/token { refreshToken }` — `200`, Session (refresh-token rotation; single-use). Requires `apikey`.
- `POST {AUTH}/password { currentPassword?, newPassword }` — `200`, fresh Session. Changes the signed-in user's password: `Authorization: Bearer <accessToken>` + `apikey`. `currentPassword` is required if the user already has one (`401` if wrong); passwordless (magic-link) users omit it to set one for the first time. New password min length 8. Revokes the user's other sessions.
- `POST {AUTH}/recover { email, redirectTo? }` — `{ sent, devLink? }`. Starts a forgot-password flow: emails a reset link to `redirectTo?token=...` (falls back to `<SITE_URL>/auth/reset?token=...`); your app then calls `.../auth/reset`. Always reports success (no account-existence leak). Keyless dev mode returns `devLink` inline. Requires `apikey`.
- `POST {AUTH}/reset { token, newPassword }` — `200`, fresh Session. Completes a forgot-password flow: sets the new password (min length 8), revokes all prior sessions. Requires `apikey`.
- `POST {AUTH}/user` — `Authorization: Bearer <accessToken>` + `apikey`, body `{}` → `{ user }`.
- `POST {AUTH}/logout { refreshToken }` — `204`. Requires `apikey`.
- `GET {AUTH}/users` — **admin**, `Authorization: Bearer <secretKey>` → `{ users: [...] }` (secret key only; anon key rejected).
- `GET {AUTH}/settings` — **admin**, `Authorization: Bearer <secretKey>` → `{ jwtSecret, userCount, anonKey, authUrl }` (secret key only).

## Errors

All errors are JSON:
```json
{ "error": { "code": "unauthorized", "message": "human-readable explanation" } }
```

| HTTP | code | Meaning |
|---|---|---|
| 400 | `bad_request` | Malformed request (e.g. missing `sql`, empty `statements`) |
| 401 | `unauthorized` | Missing, invalid, or mismatched (wrong `projectId`) key/token |
| 403 | `forbidden` | Quota hard-limit exceeded (writes only), account suspended, or SQL touched a reserved `_unbase_*` table |
| 404 | `not_found` | Unknown route or project |
| 409 | `conflict` | Auth signup where the email already exists |
| 413 | `payload_too_large` | Response would exceed the 2 MB response cap — narrow the query |
| 429 | `rate_limited` | More than 3 project creations from one IP in an hour |
| 503 | `service_unavailable` | Shared free-tier capacity temporarily saturated (circuit breaker). Writes only; reads unaffected. Retry shortly. |

## Plans & limits

| | Anonymous | Free (email) | Founder (€5/mo) | Pro (€15/mo) |
|---|---|---|---|---|
| Projects | 1 | Unlimited | Unlimited | Unlimited |
| Total storage | 25 MB | 100 MB | 2 GB | 10 GB |
| Row reads / month | 100,000 | 1,000,000 | 25,000,000 | 100,000,000 |
| Row writes / month | 10,000 | 50,000 | 1,000,000 | 5,000,000 |
| Retention | 7 days unless claimed | Forever | Forever | Forever |
| Export | Yes | Yes | Yes | Yes |

New projects start on **anonymous**. The claim flow (`POST /v1/claim` → `POST /v1/claim/verify`) moves a project to **free**. Paid plans (`founder`/`pro`) are applied automatically from Stripe via `POST /v1/stripe/webhook`, not assigned through the bearer-token API.

## Minimal working example (curl)

```bash
# 1. Create
curl -s -X POST https://api.unbase.dev/v1/projects
# => { "projectId": "unbase_abc123", "url": "...", "token": "unbase_abc123.SIGNATURE", "anonKey": "unbase_abc123.pk.SIGNATURE" }

# 2. Query (use the returned projectId + secret token)
curl -s -X POST https://api.unbase.dev/v1/projects/unbase_abc123/query \
  -H "Authorization: Bearer unbase_abc123.SIGNATURE" \
  -d '{"sql":"CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT)"}'

curl -s -X POST https://api.unbase.dev/v1/projects/unbase_abc123/query \
  -H "Authorization: Bearer unbase_abc123.SIGNATURE" \
  -d '{"sql":"INSERT INTO t (v) VALUES (?)","params":["hello"]}'

curl -s -X POST https://api.unbase.dev/v1/projects/unbase_abc123/query \
  -H "Authorization: Bearer unbase_abc123.SIGNATURE" \
  -d '{"sql":"SELECT * FROM t"}'

# 3. Sign up an end user (anon key in the apikey header)
curl -s -X POST https://api.unbase.dev/v1/projects/unbase_abc123/auth/signup \
  -H "apikey: unbase_abc123.pk.SIGNATURE" \
  -d '{"email":"user@example.com","password":"hunter2!!"}'
```

## Further reading

- Full OpenAPI 3.0 spec: `/docs/openapi.yaml`
- Human quickstart: `/docs/quickstart.md`