# Order Hub REST API

Read-only HTTP access to the Order Hub data lake for external clients (reporting
dashboards, BI tools, ad-hoc scripts). Same guarantees as the MCP server: only
`SELECT`/`WITH` queries, per-restaurant scoping, automatic PII obfuscation, full
audit trail.

- **Base URL** — `https://centic.ai` (prod), `http://localhost:3000` (local dev)
- **Auth** — bearer API keys issued from the admin dashboard
- **Content type** — `application/json` on all requests and responses

---

## Getting a key

1. Sign in to `/admin` as an admin user.
2. Go to **API Keys** in the sidebar → **New key**.
3. Fill in:
   - **Name** — human label ("Reporting dashboard prod", "Data science notebook", etc.).
   - **Scope**:
     - *Specific restaurants* (default) — pick one or more restaurants from the list.
     - *All restaurants (global)* — access every current and future restaurant.
   - **Expires** (optional) — after this date, the key stops working.
4. Copy the key. The full value is shown **only once**; only its prefix
   (`oh_live_XXXX…`) is stored plaintext for identification. If you lose it,
   revoke and create a new one.

Keys look like:

```
oh_live_esd7e7rzmxcqws7fpu3z898s32972awd
```

Include on every request:

```
Authorization: Bearer oh_live_esd7e7rzmxcqws7fpu3z898s32972awd
```

Revoking is instant — the next request with a revoked key returns `401 revoked_key`.

---

## Endpoints

| Method | Path | Purpose |
|---|---|---|
| `GET`  | `/api/v1/restaurants` | List restaurants your key can access |
| `GET`  | `/api/v1/restaurants/{id}/schema/tables` | Enumerate public-schema tables + row estimates |
| `GET`  | `/api/v1/restaurants/{id}/schema/tables/{name}/columns` | Column names, types, PII flag, BSM label/description |
| `GET`  | `/api/v1/restaurants/{id}/schema/bsm` | Brand Semantic Map for the restaurant |
| `POST` | `/api/v1/restaurants/{id}/query` | Execute a `SELECT` (max 10,000 rows, 30s) |
| `POST` | `/api/v1/restaurants/{id}/query/validate` | Validate a `SELECT` without executing |

`{id}` is the restaurant's cuid returned by `GET /api/v1/restaurants`.

---

### `GET /api/v1/restaurants`

Returns every restaurant your key can access. A key with `RESTAURANT` scope
returns exactly the restaurants it was assigned; a `GLOBAL` key returns every
restaurant in the system.

```bash
curl -H "Authorization: Bearer $KEY" \
  https://centic.ai/api/v1/restaurants
```

```json
{
  "restaurants": [
    {
      "id": "cmmmc71sp0002t6sx1djqfps2",
      "name": "Queens Harbour",
      "slug": "queens_harbour",
      "timezone": "America/Toronto"
    }
  ]
}
```

---

### `GET /api/v1/restaurants/{id}/schema/tables`

Lists tables in the restaurant's `public` schema with a live row estimate
(`pg_stat_user_tables.n_live_tup`).

```bash
curl -H "Authorization: Bearer $KEY" \
  "https://centic.ai/api/v1/restaurants/$RID/schema/tables"
```

```json
{
  "tables": [
    { "name": "ot_reservations", "estimated_rows": "60634" },
    { "name": "ot_guests",       "estimated_rows": "112096" },
    { "name": "sw_orders",       "estimated_rows": "63555" }
  ]
}
```

---

### `GET /api/v1/restaurants/{id}/schema/tables/{name}/columns`

Column definitions for a single table. Combines `information_schema.columns`
with the restaurant's `bsm_metadata` so you get both technical and semantic
information in one call.

```bash
curl -H "Authorization: Bearer $KEY" \
  "https://centic.ai/api/v1/restaurants/$RID/schema/tables/ot_guests/columns"
```

```json
{
  "table": "ot_guests",
  "columns": [
    { "name": "id",         "type": "text",        "nullable": false, "default": null,
      "label": null, "description": null, "is_pii": false },
    { "name": "email",      "type": "text",        "nullable": true,  "default": null,
      "label": "Guest Email", "description": "OpenTable guest email address", "is_pii": true },
    { "name": "first_name", "type": "text",        "nullable": true,  "default": null,
      "label": "Guest First Name", "description": null, "is_pii": true }
  ]
}
```

`is_pii: true` means the field is automatically masked in query results (see
[Query response](#query-response-shape)).

---

### `GET /api/v1/restaurants/{id}/schema/bsm`

Returns the entire Brand Semantic Map for the restaurant — every annotated
field across every table with labels, descriptions, units, and PII flags. Best
call to load once and cache client-side to build query UIs.

```json
{
  "bsm": [
    {
      "entity": "sw_orders",
      "field": "total",
      "column_name": "total",
      "label": "Order Total",
      "description": "Total order amount including tax and gratuity",
      "metric_definition": null,
      "unit": "USD",
      "source": "silverware",
      "is_pii": false
    }
  ]
}
```

If the restaurant has no `bsm_metadata` table yet (new tenants), the endpoint
returns `{ "bsm": [], "note": "bsm_metadata not present in this data lake yet" }`.

---

### `POST /api/v1/restaurants/{id}/query`

Executes a validated `SELECT` (or `WITH ... SELECT`) against the restaurant's
data lake.

**Request**

```bash
curl -X POST \
  -H "Authorization: Bearer $KEY" \
  -H "Content-Type: application/json" \
  -d '{"sql":"SELECT business_date, SUM(total) AS gross FROM sw_orders GROUP BY 1 ORDER BY 1 DESC LIMIT 7"}' \
  "https://centic.ai/api/v1/restaurants/$RID/query"
```

**Response**

```json
{
  "columns": ["business_date", "gross"],
  "rows": [
    ["2026-06-22", 18342.55],
    ["2026-06-21", 20983.10],
    ["2026-06-20", 19104.72]
  ],
  "row_count": 3,
  "truncated": false,
  "obfuscated_fields": [],
  "duration_ms": 74
}
```

<a name="query-response-shape"></a>

**Shape notes**

- `rows` are **column-ordered arrays**, not objects. Use `columns` to know
  what each position is. This mirrors the underlying Postgres wire format and
  keeps payloads small.
- `truncated` is `true` when we hit the 10,000-row cap (results are truncated
  at 10,000 in that case). Add a tighter `LIMIT` to your query to avoid this.
- `obfuscated_fields` lists which columns had PII masking applied on this
  response. If it's non-empty, values in those columns are already redacted
  (`j***@example.com`, `First L.`, etc.).
- `duration_ms` is server-side query time only.

**Limits**

- 10,000 rows max per response
- 30 seconds statement timeout
- One `SELECT` (or `WITH … SELECT`) statement per request — no multi-statement
- No writes, DDL, `SET`, `SHOW`, `EXPLAIN ANALYZE`, or access to
  `pg_catalog` / `information_schema` (use the `/schema/*` endpoints instead)

If your query hits the 10K cap or the 30s timeout, either:
- Add an explicit `LIMIT` and paginate with `OFFSET`, or
- Push more work to Postgres (aggregate/summarize server-side rather than
  pulling raw rows client-side).

---

### `POST /api/v1/restaurants/{id}/query/validate`

Validates a query without executing it. Runs the same read-only checks as
`/query`, then does a Postgres `EXPLAIN` to catch table/column/syntax errors.

```bash
curl -X POST \
  -H "Authorization: Bearer $KEY" \
  -H "Content-Type: application/json" \
  -d '{"sql":"SELECT COUNT(*) FROM ot_reservations WHERE party_size > 4"}' \
  "https://centic.ai/api/v1/restaurants/$RID/query/validate"
```

```json
{ "valid": true }
```

or, on failure:

```json
{ "valid": false, "error": "relation \"nonexistent_table\" does not exist" }
```

Useful when a client builds queries dynamically and wants to lint before hitting
`/query` (which counts a rejected run in the audit log).

---

## Read-only guarantees

Four independent layers block writes; a request has to defeat every one:

1. **SQL validator** — rejects any statement that isn't `SELECT` or
   `WITH … SELECT`. Blocks `INSERT`, `UPDATE`, `DELETE`, `DROP`, `ALTER`,
   `CREATE`, `TRUNCATE`, `GRANT`, `REVOKE`, `COPY`, `EXECUTE`, `BEGIN`,
   `COMMIT`, `ROLLBACK`, `CALL`, `DO`, `SET search_path`, `SET role`, `SHOW`,
   `EXPLAIN ANALYZE`, and references to `pg_catalog` / `pg_roles` /
   `pg_database` / `pg_tables` / `pg_stat` / `pg_settings` /
   `information_schema`.
2. **Postgres role** — the API connects with a role that only has `SELECT`
   grants on the data lake tables.
3. **Per-transaction lockdown** — every query runs inside a transaction with
   `SET LOCAL default_transaction_read_only = on`, `SET LOCAL statement_timeout
   = 30000`, and `SET LOCAL search_path TO public`.
4. **Statement timeout** — 30-second cutoff via Postgres itself, not just the
   API layer.

## PII handling

- Every field flagged `is_pii = true` in the restaurant's `bsm_metadata` is
  automatically masked in query results.
- Masking is applied by **column name**, so `SELECT email AS e FROM …` will
  return an unmasked value. To get consistent masking, use the raw column name
  or explicitly opt out of PII columns in your query.
- The `obfuscated_fields` list on each response tells you exactly which columns
  were masked in that call.

## Auditing

Every request (successful or not) is logged with:

- Which API key made the call (by ID; the raw key value is never stored)
- Which restaurant it hit
- The SQL that was submitted
- Result status: `SUCCESS`, `ERROR`, `TIMEOUT`, or `REJECTED`
- Rows returned, duration, error message, and which PII fields were masked

Admins can review this per-restaurant under
`/admin/restaurants/{id}/query-log`.

## Error format

All errors return JSON with the same shape and a matching HTTP status:

```json
{ "error": "restaurant_not_in_scope", "message": "API key does not have access to this restaurant" }
```

| HTTP | `error` code | When it happens |
|---|---|---|
| 400 | `missing_sql` | Request body has no `sql` field |
| 400 | `invalid_body` | Body isn't valid JSON |
| 400 | `invalid_table_name` | Table name in the path has unsupported characters |
| 400 | `invalid_query` | Query failed the SQL validator (message says why) |
| 400 | `no_data_lake` | Restaurant has no data-lake connection configured |
| 401 | `missing_bearer_token` | No `Authorization: Bearer …` header |
| 401 | `invalid_key_format` | Bearer token doesn't parse as an API key |
| 401 | `unknown_key` | Key prefix isn't in the database |
| 401 | `invalid_key` | Prefix matches but the secret doesn't |
| 401 | `revoked_key` | Key has been revoked |
| 401 | `expired_key` | Key is past its `expiresAt` |
| 403 | `restaurant_not_in_scope` | Key doesn't include this restaurant |
| 404 | `table_not_found` | No such table in the `public` schema |
| 500 | `query_timeout` | Query hit the 30-second cap |
| 500 | `query_failed` | Postgres returned an error (message included) |

401 responses also set `WWW-Authenticate: Bearer error="<code>"`.

## Versioning

The current version is `v1`. Breaking changes (removed fields, new required
inputs, changed semantics) will only ship under a new `/api/v2` prefix; `/v1`
will remain stable. Additive changes (new fields, new endpoints, new optional
inputs) may land in `/v1` without notice.

## Rate limits

There are no server-enforced rate limits in v1. Keys are admin-issued only, so
abuse risk is low, but be a good citizen: batch reads and prefer `WHERE`
filters over pulling large ranges.

## Example — pull yesterday's sales for every accessible restaurant

```python
import os, requests

KEY = os.environ["OH_KEY"]
H = {"Authorization": f"Bearer {KEY}"}
BASE = "https://centic.ai/api/v1"

restaurants = requests.get(f"{BASE}/restaurants", headers=H).json()["restaurants"]
for r in restaurants:
    q = f"""
      SELECT business_date, SUM(total) AS gross
      FROM sw_orders
      WHERE business_date = CURRENT_DATE - 1
      GROUP BY 1
    """
    res = requests.post(
        f"{BASE}/restaurants/{r['id']}/query",
        headers={**H, "Content-Type": "application/json"},
        json={"sql": q},
    ).json()
    print(r["name"], res.get("rows", []))
```