# PrismSQL API

**Status:** Live hosted compile API.  
**Base:** `https://www.insightits.com/api/prismsql/v1`  
**Auth:** InsightITS JWT (`Authorization: Bearer …`). Isolation is the JWT email. Never `userProfile` / body `tenant_id`.  
**Landing:** `/products/prismsql.html#api-docs`  
**Example contract:** `/docs/prismsql-schema.example.json`

This is compile-only. Success returns `sql_template` + `params`. `rows` is always `[]`. `execution_result` is always `null`. We do not connect to the customer warehouse. The model never writes SQL or `ON`.

When a prompt cannot compile honestly, the API **asks back**. It names the table it matched and lists candidate columns. It does not invent a column, pick the first of many, guess qty vs amount from the size of a number, or remap an unknown table onto another table.

---

## Routes

| Method | Path | Auth | Notes |
|--------|------|------|--------|
| POST | `/query/execute` | JWT | Compile. Alias: `/query/compile` |
| POST | `/query/validate` | JWT | Same compile path without counting a paid compile when the implementation marks `execute: false` |
| GET | `/me` | JWT | Plan, quota, saved schemas, `format.example`, `format.guide`, `dialectMatrix`, `warehouseDialect`, `stripeReady` |
| POST | `/schemas/validate` | JWT | Validate only. Returns `checklist` (required vs recommended) |
| GET | `/schemas` | JWT | List saved contracts |
| POST | `/schemas` | JWT | Save a contract (Validate first) |
| DELETE | `/schemas/<schema_id>` | JWT | Remove a saved contract |
| POST | `/checkout` | JWT | Stripe Checkout for Developer / Pro |
| POST | `/checkout/confirm` | JWT | After Stripe returns; writes `prismsql_subscribers.plan` |
| POST | `/webhook` | Stripe | Billing webhook. Not a compile route |

Super-admin product stats live under `/api/admin/ai-products/prismsql/…`, not this public compile API.

---

## Compile

`POST /query/execute`

```http
POST /api/prismsql/v1/query/execute
Authorization: Bearer <InsightITS JWT>
Content-Type: application/json
```

```json
{
  "prompt": "open invoices",
  "schema_id": "harbor_clinic_v1",
  "improvers": {
    "indexes": true,
    "allowed_values": true,
    "default_order": true,
    "soft_delete": true,
    "join_fanout": true,
    "timezone": true,
    "case_insensitive": true,
    "allowed_operators": true
  }
}
```

`schema_id` is optional when the login has one saved schema.  
`POST /query/execute` **ignores** `dialect` in the JSON body. The printer comes from the subscriber pin (dashboard database picker), then the saved contract, then PostgreSQL.

### Success shape

HTTP 200. Hosted `rows` is always empty.

```json
{
  "status": "success",
  "compile_only": true,
  "dialect": "postgresql",
  "schema_id": "harbor_clinic_v1",
  "sql_template": "SELECT ... WHERE ... = %s ... LIMIT %s",
  "placeholder": "%s",
  "params": ["you@company.com", "open", 100],
  "parameters": [],
  "rows": [],
  "row_count": 0,
  "execution_result": null,
  "metadata": {
    "compiler_version": "1.6.5",
    "chorusgraph": false,
    "prismcortex": false
  }
}
```

Isolation is a bound parameter (JWT email), never interpolated into `sql_template`.

### Clarify shape

HTTP 422. `status` is `clarify` when the compiler matched something but needs a complete prompt — two tables, two columns, or a matched table missing the column type the prompt asked for.

```json
{
  "status": "clarify",
  "reason_code": "SEMANTIC_AMBIGUOUS",
  "matched_table": "public.invoice_lines",
  "missing": "numeric_column_choice",
  "candidates": [
    {"table": "public.invoice_lines", "column": "qty"},
    {"table": "public.invoice_lines", "column": "unit_amount"}
  ],
  "message": "Matched public.invoice_lines. 'over 5' matches more than one numeric column (qty, unit_amount). Name the column and retry."
}
```

Table-level ambiguity uses `candidates[].tables` (retrieve). Column-level ambiguity uses `candidates[].column` (compiler).

Prompt-gap refuses (matched table, missing date or numeric type) also return `status: clarify` with `reason_code: SEMANTIC_UNSUPPORTED`, `matched_table`, and `missing`.

### Refused shape

HTTP 4xx/5xx. Unknown tables, policy, quota, auth.

```json
{
  "status": "refused",
  "reason_code": "SEMANTIC_UNSUPPORTED",
  "message": "That question does not match tables on your saved schema."
}
```

---

## Warehouse dialect

Improver SQL is not the same on every database (`SYSTIMESTAMP` / `FETCH FIRST` / `INTERVAL '7' DAY` on Oracle; `CURRENT_TIMESTAMP` / `LIMIT` / `INTERVAL '7 days'` on PostgreSQL).

1. JWT email → `prismsql_subscribers.dialect` (dashboard picker at schema save).
2. If that pin is empty (legacy row), fall back to the saved schema contract dialect.
3. Request body `dialect` is ignored.

`GET /me` returns `warehouseDialect` / `license.warehouseDialect`. Success metadata includes `account_dialect`, `schema_dialect`, and `dialect_source` (`subscriber` | `schema` | `default`).

| Database | Developer / Sandbox | Pro | Enterprise |
|----------|---------------------|-----|------------|
| PostgreSQL | Yes | Yes | Yes |
| MySQL | — | Yes | Yes |
| MariaDB | — | Yes | Yes |
| SQLite | — | Yes | Yes |
| Snowflake | — | Yes | Yes |
| Amazon Redshift | — | Yes | Yes |
| Microsoft SQL Server | — | — | Yes |
| Oracle Database | — | — | Yes |
| Google BigQuery | — | — | Yes |
| Databricks SQL | — | — | Yes |

---

## Improvers

Every flag **defaults to `true`**. A step runs only when **both** the flag is true **and** the uploaded schema declares the matching fact.

| Request | Meaning |
|---------|---------|
| omit `improvers` | All on (default) |
| `"improvers": true` | All on |
| `"improvers": false` | All off — raw IR + isolation only |
| `"improvers": { "timezone": false }` | That one off; the rest stay on |
| `"disable_improvers": ["timezone", "default_order"]` | Same as setting those keys false |

Unknown names → `IR_INVALID`.

| Flag | Schema field | Effect when applied |
|------|----------------|---------------------|
| `indexes` | `tables.*.indexes` | Print tenant / indexed predicates first in `WHERE` |
| `allowed_values` | `column_values` | Bind only declared tokens |
| `default_order` | `default_order` | Inject `ORDER BY` if the IR omitted it **and** the prompt did not ask for a sort |
| `soft_delete` | `soft_delete` | Inject `IS NULL` / `IS NOT NULL` / `eq` |
| `join_fanout` | `join_paths[].cardinality` | `COUNT(DISTINCT)` on the one-side of 1:N; refuse one-side `SUM`; omit `LIMIT` on aggregate-only |
| `timezone` | top-level `timezone` | `CURRENT_TIMESTAMP AT TIME ZONE …`. `UTC` / omitted = no wrap |
| `case_insensitive` | `column_case` | `UPPER(col) = UPPER(%s)` |
| `allowed_operators` | `column_operators` | Refuse operators not listed (compiler `*_now` ops still allowed) |

Improvers do not invent `deleted_at`, indexes, or enums.

---

## Prompt `ORDER BY` (compiler-owned)

Prompt sort overwrites IR `order_by` and schema `default_order`, on a **declared** column only.

| Language | Inject |
|----------|--------|
| `order by` / `sort by` / `order them by` + a contract column or alias | That column |
| `last` / `recent` / `newest` / `latest` | Unique date column `DESC` (soft-delete timestamps excluded) |
| `oldest` / `earliest` | Unique date column `ASC` |
| `last name` | `last_name` column, not recency |
| `last 7 days` / `last 30 days` / `last month` | **Windows**, not sort |
| Unknown column | `SCHEMA_UNKNOWN_COLUMN` |
| Several date columns and no name | `SEMANTIC_AMBIGUOUS` + `candidates[]` |

---

## Prompt ranges (compiler-owned)

Detected in `prismsql/ranges.py` before Gemini. Injected in `prepare_ir`. Rolling intervals (month = 30 days, year = 365), not calendar months. Window digits are stripped from amount binds. ISO calendar spans without numbers in the prompt (`from January to March`) are not compiled.

| Language | Inject |
|----------|--------|
| `this week` / `last 7 days` / `past week` | `gte_days_ago` 7 |
| `last N days\|weeks\|months\|years`, `last month`, `last year` | `gte_days_ago` N |
| `next N days` / `next week` | `gte_now` + `lte_days_ahead` |
| `upcoming` / bare `next` without a duration | `gte_now` |
| `overdue` / `past due` | `lte_now` on the named/aliased date column, or the unique date column |
| `over` / `above` / `greater than` / `more than` | `gt` |
| `under` / `below` / `less than` | `lt` |
| `at least` / `at most` | `gte` / `lte` |
| `between X and Y` / `from X to Y` (two numbers) | `gte` + `lte` |

---

## Date column selection

Eligible columns are declared `DATE` / `TIMESTAMP` / `TIMESTAMPTZ` on the FROM table. The contract `soft_delete` column is excluded (`deleted_at` is not “last 7 days”).

| Situation | Result |
|-----------|--------|
| Table has exactly one eligible date | Assume it (`appointments` → `starts_at`, `invoices` → `due_date`, `orders` → `created_at`) |
| Prompt names a date column or a declared alias (`starts_at last 30 days`, `overdue` → `due_date`) | Use that column |
| Several date columns and the prompt does not name one | `status: clarify`, `SEMANTIC_AMBIGUOUS`, `candidates[]` listing the dates. Do **not** pick the first (`starts_at` vs `created_at`) |
| Prompt asks for a window and the table has no date | `status: clarify`, `SEMANTIC_UNSUPPORTED`, `matched_table`, `missing: "date"` |

---

## Numeric column selection

Eligible columns are declared numeric types on the FROM table. `id` and `*_id` are excluded. The compiler does **not** infer qty vs amount from the size of the number (we have no warehouse min/max).

| Situation | Result |
|-----------|--------|
| Table has exactly one eligible numeric | Assume it (`invoices.amount`, `order_items.qty`) |
| Prompt names a declared numeric (`qty over 5`) | Use that column |
| Several named compares (`qty over 5 and unit_amount over 10`) | Bind each compare to the named column |
| Several numeric columns, unnamed (`line items over 5`) | `status: clarify`, `SEMANTIC_AMBIGUOUS`, `candidates[]` (`qty`, `unit_amount`). Do **not** pick the first or prefer money |
| Several unbound amounts and several numeric columns (`line items over 5 under 10`) | Clarify: name each column. We do not know which number belongs to which column |
| Two compares on a table with one numeric (`invoices over 500 under 1000`) | Both filters on that column (AND) |
| `between 100 and 500` | One range (two digits, one clause) |
| `over 500 last 30 days` | Amount bind is 500; 30 is the window, not money |
| Prompt asks for a number range and the table has no numeric | `status: clarify`, `SEMANTIC_UNSUPPORTED`, `matched_table`, `missing: "numeric"` |

---

## Edge cases (Harbor / acme_orders)

These are the compile behaviors tests lock. `hygiene` is a declared **alias of `appointments`**, not a missing table.

| Prompt | Contract | Result |
|--------|----------|--------|
| `list my support tickets` | Harbor | `refused` `SEMANTIC_UNSUPPORTED` — tickets is not on the contract. **Not** remapped to patients |
| `hygiene appointments over 500` | Harbor | `clarify` `SEMANTIC_UNSUPPORTED` `matched_table: public.appointments` `missing: numeric` — table matched; no amount/qty column |
| `invoice line items last 30 days` | Harbor | `clarify` `SEMANTIC_UNSUPPORTED` `matched_table: public.invoice_lines` `missing: date` — no date column |
| `invoice line items qty over 5 last 30 days` | Harbor | `clarify` missing **date** (window is applied first; lines still have no date) |
| `invoices over 500 last 30 days` | Harbor | Compile `amount > 500` and rolling 30 days on `due_date` |
| `order items over 5` | acme_orders | Compile `qty > 5` (only numeric column) |
| `invoice line items over 5` | Harbor | `clarify` `SEMANTIC_AMBIGUOUS` candidates `qty`, `unit_amount` |
| `invoice line items qty over 5` | Harbor | Compile `qty > 5` |
| `invoice line items qty over 5 and unit_amount over 10` | Harbor | Both filters |
| `invoices over 500 under 1000` | Harbor | Both compares on `amount` |
| `invoice line items over 5 under 10` | Harbor | `clarify` — two amounts, two columns |
| `hygiene appointments last 30 days` | Harbor | Compile window on `starts_at` (only date) |
| `open orders over 100 last 7 days` | acme_orders | Window on `created_at`; `deleted_at` is soft-delete, not the date |
| `starts_at last 30 days` when `created_at` also exists | two-date contract | Named date wins |
| `last 30 days` when `starts_at` and `created_at` both exist | two-date contract | `clarify` with both columns in `candidates[]` |
| IR names a table outside the retrieved subgraph (`secrets`) | any | `SCHEMA_UNKNOWN_TABLE` — subgraph check runs **before** range inject, so a leaked table is not mislabeled as “no amount column” |
| `from January to March` (no numbers) | any | Not compiled. We do not invent calendar dates |

### Clarify examples

**Matched table, no numeric column**

```json
{
  "status": "clarify",
  "reason_code": "SEMANTIC_UNSUPPORTED",
  "matched_table": "public.appointments",
  "missing": "numeric",
  "message": "Matched public.appointments. 'over 500' needs a numeric column (amount, qty, or similar) on that table. None is declared. Name the table that holds that number, or add that column to this table on your schema contract."
}
```

**Matched table, no date column**

```json
{
  "status": "clarify",
  "reason_code": "SEMANTIC_UNSUPPORTED",
  "matched_table": "public.invoice_lines",
  "missing": "date",
  "message": "Matched public.invoice_lines. 'last 30 days' needs a date or timestamp column on that table. None is declared. Name a table that has a date, or add that column to this table on your schema contract."
}
```

**Several numeric columns, unnamed**

```json
{
  "status": "clarify",
  "reason_code": "SEMANTIC_AMBIGUOUS",
  "matched_table": "public.invoice_lines",
  "missing": "numeric_column_choice",
  "candidates": [
    {"table": "public.invoice_lines", "column": "qty"},
    {"table": "public.invoice_lines", "column": "unit_amount"}
  ],
  "message": "Matched public.invoice_lines. 'over 5' matches more than one numeric column (qty, unit_amount). Name the column and retry."
}
```

**Several date columns, unnamed**

```json
{
  "status": "clarify",
  "reason_code": "SEMANTIC_AMBIGUOUS",
  "matched_table": "public.appointments",
  "missing": "date_column_choice",
  "candidates": [
    {"table": "public.appointments", "column": "starts_at"},
    {"table": "public.appointments", "column": "created_at"}
  ],
  "message": "Matched public.appointments. 'last 30 days' matches more than one date column (starts_at, created_at). Name the column and retry."
}
```

Retry by naming the column in the prompt (`qty over 5`, `starts_at last 30 days`) or by adding the missing column to the schema contract if it exists on the warehouse. Do not expect the compiler to steal `invoices.amount` for hygiene.

---

## Reason codes

| Code | HTTP | Typical `status` | Meaning |
|------|------|------------------|---------|
| `IR_INVALID` | 422 | refused | Prompt / IR could not be understood; unknown improver name |
| `SCHEMA_UNKNOWN_TABLE` | 422 | refused | Table not on the eligible subgraph or contract |
| `SCHEMA_UNKNOWN_COLUMN` | 422 | refused | Column not on the saved schema (including unknown `ORDER BY`) |
| `SCHEMA_UNKNOWN_JOIN_PATH` | 422 | refused | Join is not a declared `path_id` |
| `SCHEMA_DRIFT_DETECTED` | 409 | refused | Stored contract fingerprint mismatch |
| `SCHEMA_INVALID` | 422 | refused | Uploaded JSON failed validation |
| `SCHEMA_NOT_CONFIGURED` | 409 | refused | Save a schema in the dashboard first |
| `TIER_LIMIT` | 403 | refused | Dialect or action not on this plan |
| `QUOTA_EXCEEDED` | 429 | refused | Monthly compile quota used |
| `AUTH_USER_DENIED` | 401/403 | refused | Missing/invalid JWT |
| `AUTH_COLUMN_DENIED` | 403 | refused | Column not on allowlist |
| `SEMANTIC_UNDEFINED_METRIC` | 422 | refused | Metric is not defined |
| `SEMANTIC_UNSUPPORTED` | 422 | refused **or** clarify | Unknown table (generic message) **or** matched table missing date/numeric (`matched_table` + `missing`) |
| `SEMANTIC_AMBIGUOUS` | 422 | clarify | Two tables, or several unnamed date/numeric columns (`candidates[]`) |
| `TYPE_INVALID_OPERATOR` | 422 | refused | Operator not valid for that column |
| `TYPE_BIND_MISMATCH` | 422 | refused | Bind did not match the prompt span / type |
| `POLICY_FORBIDDEN_OPERATION` | 403 | refused | Operation not allowed |
| `POLICY_MISSING_LIMIT` | 422 | refused | Row limit required |
| `SAFETY_CARTESIAN_JOIN` | 403 | refused | Join would cartesian |
| `SAFETY_EXCESSIVE_COST` | 403 | refused | Static cost guard (hosted does not EXPLAIN your warehouse) |
| `SAFETY_EXPLAIN_TIMEOUT` | 403 | refused | Planning timeout (not used on compile-only hosted path) |
| `SAFETY_STATEMENT_TIMEOUT` | 403 | refused | Statement timeout |
| `SAFETY_RESULT_TOO_LARGE` | 403 | refused | Result too large |
| `SYSTEM_MANIFEST_UNAVAILABLE` | 503 | refused | Contract store unavailable |
| `SYSTEM_COMPILER_PANIC` | 503 | refused | Compiler failure |
| `SYSTEM_EXECUTOR_UNAVAILABLE` | 503 | refused | Executor (not used to run customer SQL on hosted) |
| `SYSTEM_GUARD_UNAVAILABLE` | 503 | refused | PrismGuard unavailable |
| `SYSTEM_VECTORPRISM_UNAVAILABLE` | 503 | refused | Retrieve unavailable |
| `SYSTEM_LLM_UNAVAILABLE` | 503 | refused | IR model unreachable |

Jailbreak / injection on the prompt is PrismGuard HTTP 403, not a compile `reason_code` with SQL.

---

## Schema contract fields (upload)

`POST /schemas` and `POST /schemas/validate` accept the same JSON as the dashboard example (`GET /me` → `format.example`).

Validate returns a `checklist` of required vs recommended fields (present / missing). Required missing → refuse. Recommended missing does **not** refuse save; the matching improver is a no-op. `GET /me` → `format.guide` is the same field list.

New optional facts (do not invent columns that are not on the warehouse):

```json
{
  "timezone": "America/Los_Angeles",
  "tables": {
    "public.invoices": {
      "column_values": { "status": ["open", "unpaid", "paid", "void"] },
      "column_operators": { "status": ["eq", "in", "neq"], "amount": ["eq", "gt", "gte", "lt", "lte"] },
      "column_case": { "last_name": "insensitive" },
      "default_order": [{ "column": "due_date", "direction": "DESC" }],
      "soft_delete": { "column": "deleted_at", "operator": "is_null" },
      "indexes": [{ "name": "invoices_clinic_status", "columns": ["clinic_owner_email", "status"] }]
    }
  },
  "join_paths": [
    {
      "path_id": "path_lines_invoices",
      "cardinality": "N:1"
    }
  ]
}
```

`soft_delete.operator` is `is_null`, `is_not_null`, or `eq` (with `value`).  
`cardinality` is left→right.

---

## What this API does not do

- No warehouse DSN, no `EXPLAIN` on hosted, no rows.
- No LLM-written SQL / `ON` / `raw_sql`.
- No silent remap (`tickets` → `patients`, hygiene → `invoices.amount`).
- No picking the first of several date or numeric columns.
- No guessing qty vs amount from the magnitude of the number.
- Improvers do not invent `deleted_at`, indexes, or enums. If the file omitted them, turning the flag on does nothing.

QA checklist: `docs/prismsql-qa.md`. Design: `docs/prismsql-design.md`.