Endpoints overview

An endpoint is a stored, parameterised SQL statement reachable on the public internet at:

POST https://api.persql.com/p/<namespace>/<database>/<endpoint-slug>

Endpoints are served by a separate worker (apps/runtime) that has no PerSQL session auth — only the gates you put on the spec apply. That worker is bound to the same SQLite Durable Object as the API worker, so reads and writes go through the same database.

Why endpoints

• No backend to deploy. Define the SQL once; PerSQL exposes the HTTP, validates the input, binds parameters, and returns JSON.
• Safe by construction. Only ? placeholders are bound; raw input never reaches SQL. Field types, regex patterns, and enums are enforced before binding.
• Agent-discoverable. Every database advertises its published endpoints at /.well-known/endpoints.json with full input schemas, so MCP clients and code-gen agents can consume them as tools.

Anatomy of a spec

{
  slug: "list-orders",
  name: "List orders",
  description: "Recent orders for a customer",
  kind: "query",                    // query | mutation | auth_signup | auth_login | ask
  method: "GET",                    // GET | POST
  sql: "SELECT id, total FROM orders WHERE customer_id = ? LIMIT ?",
  input: [
    { name: "customer_id", type: "integer", required: true },
    { name: "limit", type: "integer", min: 1, max: 100 }
  ],
  output: "rows",                   // rows | first_row | rows_written | session | answer
  auth: "public",                   // public | session
  rateLimit: { perMin: 60, perDay: 5000 },
  cors: ["https://acme.com"],
  captcha: false
}

See Spec reference for every field and the full validation rules.

Endpoint kinds

Kind	Use for
query	Read-only SELECTs (list/get)
mutation	INSERT/UPDATE/DELETE (output: "rows_written")
auth_signup	Hash a password, insert a user, return a session JWT
auth_login	Verify a password, return a session JWT
ask	NL→SQL grounded against allowed tables only

Hot path

When a request hits an endpoint:

1. Resolve (ns, db, slug) from KV (60s cache) or D1.
2. CORS preflight for OPTIONS.
3. Status check — only published endpoints serve traffic; paused returns 503 immediately.
4. Method match (GET / POST).
5. Per-endpoint, per-IP rate limit (KV counters).
6. Cloudflare Turnstile verification if captcha: true.
7. Decode body / query → raw input.
8. Validate input against the spec (types, required, regex, enums, numeric ranges, lengths).
9. If auth: "session", verify the bearer JWT and source $user_id, $user_email, $session_iat from claims.
10. Bind ? placeholders, execute against the DO, project the response shape declared by output.
11. Record an endpoint_call event in analytics.

Lifecycle

Endpoints have three statuses:

Status	Behaviour
draft	Not served; only the editor can call /test.
published	Served on /p/....
paused	Returns 503 without hitting the DO.

Every change writes a row to endpoint_revision so you can audit who edited what (GET /endpoints/:slug/revisions).

Testing

POST /endpoints/:slug/test runs the endpoint against the live DO with the input you provide. Mutations are not rolled back — the runtime can’t safely roll back DO writes outside an explicit transaction, and agents test against real state by design. Test against draft data or on a scratch database.

What’s next

• Spec reference — every field, every rule.
• Session auth — signup / login / $user_id.
• Ask kind — NL→SQL endpoints with allow-listed tables.
• Discovery — .well-known/endpoints.json for agents.