API Reference

The server exposes a small set of fixed endpoints plus the SQL proxy.

Auth endpoints

See Auth System for request/response details.

Method	Path	Auth	Purpose
POST	/auth/magic-link	No	Request magic link
POST	/auth/magic-link/verify	No	Verify magic link token
POST	/auth/set-password	Yes	Set initial password
POST	/auth/email-change/request	Yes	Request email change — sends confirmation link to new address
POST	/auth/email-change/confirm	No (token is capability)	Confirm email change via link token
POST	/admin/invite	Yes (owner/admin)	Invite a new user
POST	/auth/login	No	Dev-only password login

Schema introspection

POST /

Returns full database metadata: tables, columns, foreign keys, row counts, and the caller’s current role. Filtered by the caller’s role permissions.

Request: {} (empty body)

Response:

{
  "relations": [{ "table_name": "products_v", "cols": [...], "can_select": true, "write_target": "products", ... }],
  "keys": [{ "constraint_type": "PRIMARY KEY", "source_table": "products", ... }],
  "stats": [{ "relname": "products", "number_rows": 42 }],
  "table_metadata": { "products": { "display_columns": ["name"] } },
  "app_metadata": { "app_description": "Order management application", "home_page": { "staff": "products-home" } },
  "column_metadata": { "products": { "name": { "display_type": "text", "label": "Product Name" }, "price": { "display_type": "currency" }, "status": { "display_type": "choice", "options": [{"value": "active", "label": "Active", "color": null}] } } },
  "apps": [{ "id": "uuid", "schema_name": "my_app", "display_name": "My App", ... }],
  "pages": [{ "slug": "dashboard", "title": "Dashboard", "role": "staff", "show_in_nav": true }],
  "curr_role": "staff"
}

The write_target field on each relation indicates where writes should go:

• "products_v" with write_target: "products" — a read view; SELECT from the view, INSERT/UPDATE/DELETE against the base table
• "products" with write_target: null — the base table (frontend can suppress from sidebar when a _v view exists)
• "table_metadata" with write_target: null — a view without a _v counterpart (read-only or direct-write)

The frontend calls this once on login to generate the CRUD views and sidebar.

SQL proxy

POST /sql

Execute a parameterized SQL query. Requires a valid JWT. This is also the endpoint for calling sys.* functions (admin only) — the /function/:name endpoint blocks sys.* calls.

Owner/admin-only sys functions available via /sql:

Function	Purpose
sys.create_table(name, columns)	Create a business table with RLS, views, and metadata
sys.alter_table(name, operations)	Modify an existing managed table
sys.drop_table(name)	Drop a managed table and its metadata
sys.create_view(name, sql)	Create a read-only custom view (report)
sys.drop_view(name)	Drop a managed custom view
sys.invite_user(email, role, display_name)	Create user + magic link token
sys.get_table_permissions()	Read RLS policies for staff/member/anon on all managed tables
sys.set_table_permissions(table, role, read, write, delete)	Modify RLS policies and grants for staff, member, or anon
sys.create_app(schema_name, display_name, description)	Create a new app (schema + registry entry); description (if provided) lands in sys.app_metadata.app_description
sys.update_app(schema_name, display_name, description)	Update display name; upserts/deletes sys.app_metadata.app_description (NULL/empty clears it)
sys.rename_app(old_name, new_name)	Rename app schema + update all metadata
sys.drop_app(schema_name)	Drop app schema + registry entry
sys.update_profile(display_name)	Update own display name (all authenticated roles). Email changes go through /auth/email-change/* endpoints.
sys.request_email_change(new_email)	Create a magic-link token for confirming a new email. Called by the /auth/email-change/request endpoint.
sys.confirm_email_change(token)	Consume an email-change link and apply the new email. Called by the /auth/email-change/confirm endpoint.

Request:

{
  "sql": "SELECT * FROM products WHERE id = $1",
  "params": ["some-uuid"]
}

Headers:

Authorization: Bearer <jwt>
Content-Type: application/json
X-App-Schema: my_app          (optional — targets a specific app schema)
X-Tenant: acme                (multi-tenant mode only — selects the target database)

App schema scoping is driven by the JWT’s app_schema claim (#763). Clients select an app via POST /auth/select-app, which re-issues the JWT with the new claim — see the auth doc for details. The X-App-Schema header is sent alongside as a defense-in-depth assertion: when both the claim and the header are present and disagree, the request is rejected with 403. When the JWT does not carry the claim (old tokens issued before #763), the header is honored as a rollout fallback. When neither is set, the request targets public. The backend resolves the chosen value against sys.apps, sets search_path to <app_schema>, public, pg_catalog, and stores the app ID in app.app_id for trigger support. Invalid or unknown schema names return 400.

Response:

{
  "rows": [{ "id": "some-uuid", "name": "Widget", "price": 9.99 }],
  "rowCount": 1
}

The query runs inside a transaction with the caller’s role and org context set. RLS policies filter the results automatically.

SQL validation: Only single-statement DML is accepted — SELECT, INSERT, UPDATE, and DELETE. DDL (CREATE TABLE, DROP TABLE, etc.) and multi-statement payloads (SELECT 1; DROP TABLE x) are rejected before reaching PostgreSQL. The server parses each query using libpg-query (PostgreSQL’s actual parser) and enforces the Extended Query Protocol, which rejects multi-statement SQL at the wire level.

Function calling

POST /function/:name

Call a PostgreSQL function by name. Parameters are passed as an array in the request body.

Request:

["arg1", "arg2"]

Response:

{
  "rows": [{ "result": "..." }],
  "rowCount": 1
}

Health check

GET /health

{ "status": "ok" }

No authentication required.

Chat service (port 3002)

The chat service runs as a separate Express app on port 3002. All chat endpoints require a valid JWT with owner or admin role. The service writes to sys.conversations and sys.messages as the authenticator role and enforces ownership via WHERE user_id = $1. Like the SQL proxy, the chat service supports multi-tenant mode (MULTI_TENANT=true) where the X-Tenant header selects the target database. JWTs issued in multi-tenant mode include a tenant claim that is validated against the X-Tenant header on every authenticated request (see Auth System — JWT structure). Tenant utilities (TenantPoolManager, validateTenant) are shared from @alchemify/db.

The AI system prompt includes the full list of available @alchemify/ui components, read at runtime from the canonical registry in packages/types/src/ui-components.ts. This ensures the AI only generates imports for components that actually exist.

Method	Path	Purpose
GET	/conversations	List user’s conversations (newest first)
POST	/conversations	Create a new conversation
GET	/conversations/:id	Get conversation with all messages
DELETE	/conversations/:id	Delete conversation (cascades to messages)
POST	/conversations/:id/chat	Send a message, stream AI response (SSE)
POST	/conversations/:id/execute-tool	Approve or reject a pending tool call
POST	/conversations/:id/continue	Continue agentic loop after tool execution (SSE)
GET	/health	Health check

POST /conversations

Request:

{ "title": "My Chat" }

Response (201):

{ "id": "uuid", "title": "My Chat", "created_at": "...", "updated_at": "..." }

POST /conversations/:id/chat

Sends a user message and streams the AI (GPT-5.1) response via Server-Sent Events (SSE).

Request:

{ "content": "Hello world" }

Response: Content-Type: text/event-stream (HTTP 200)

Pre-streaming errors (auth, validation, conversation not found) return standard JSON error responses (401/400/404).

Once streaming begins, the response is a series of SSE events:

event: token
data: {"text":"Hello"}

event: token
data: {"text":" world"}

event: done
data: {"message":{"id":"uuid","role":"assistant","content":{"type":"text","text":"Hello world"},"status":"complete"}}

If the AI request fails mid-stream:

event: error
data: {"error":"Rate limit exceeded"}

SSE event types:

Event	Data	Description
token	{ "text": "..." }	Incremental text chunk from the AI
tool_call	{ "id": "...", "name": "...", "args": {...} }	AI proposed an operation (schema change or page create/update/delete/preview)
title	{ "title": "..." }	Auto-generated conversation title, sent before done. First emitted on the user’s opening message (when title is still NULL); re-emitted whenever set_app_description runs so the title can be regenerated from the freshly-saved app description (#678).
status	{ "text": "..." }	Transient status indicator (e.g., “Querying data…” during select_data)
done	{ "message": { id, role, content, status } }	Streaming complete, final message object
error	{ "error": "..." }	AI request failed; message saved with status: "error"

When the AI returns a tool call, the stream emits a `tool_call` event followed by `done` with `streaming_continues: true`. Tool calls are auto-executed by the server — the AI calls tools back-to-back in an agentic loop without pausing for approval. Each executed tool call is persisted as a separate message, and the `done` event includes the tool result. For `preview_page` and `propose_plan`, `status` is `"complete"` (auto-completed, no execution needed) and includes a synthetic `tool_result`. For `select_data`, the server auto-executes the SELECT query using the user's database role (respecting RLS), feeds the results back to the AI, and re-enters the streaming loop — the user only sees the AI's natural language answer, with a brief `status` SSE event ("Querying data...") while the query runs. The `execute-tool` endpoint still exists for backward compatibility with older `pending_approval` messages.

**Agentic loop:** Tool calls now auto-execute within the streaming response — the server executes each tool, refreshes the schema, and re-prompts the AI in a continuous loop until the model returns plain text. The `continue` endpoint remains available for resuming after manual `execute-tool` approvals on older conversations. The `continue` endpoint streams SSE events in the same format as `/chat`.

**Tool types:**

| Tool | Purpose | Execution |
|------|---------|-----------|
| `propose_create_table` | Create a new database table | Auto-execute |
| `propose_alter_table` | Modify an existing table | Auto-execute |
| `propose_create_m2m` | Create a many-to-many junction table between two existing parent tables (composite PK on the two FKs, ON DELETE CASCADE both sides, no audit columns) | Auto-execute |
| `propose_drop_table` | Drop a table and all its data (RESTRICT — fails if FK dependents exist) | Auto-execute |
| `propose_seed_data` | Insert sample/test data into a table | Auto-execute |
| `propose_update_rows` | Update rows matching a SQL WHERE expression (supports `=`, `>`, `IN`, `BETWEEN`, `ILIKE`, etc.) | Auto-execute |
| `propose_delete_rows` | Delete rows matching a SQL WHERE expression | Auto-execute |
| `propose_create_view` | Create a read-only SQL view (supports joins, CTEs, aggregations). Plain string literals on `=`/`<>`/`IN` against a known choice column are rewritten to the canonical case before the view is created (#628). | Auto-execute |
| `propose_drop_view` | Drop a custom view created via the builder | Auto-execute |
| `configure_fk_display` | Change which columns show in FK autocomplete | Auto-execute |
| `propose_create_page` | Create a new custom TSX page | Auto-execute |
| `propose_update_page` | Update an existing page's source | Auto-execute |
| `propose_delete_page` | Delete a custom page | Auto-execute |
| `preview_page` | Read-only page preview render | Auto-complete |
| `select_data` | Query the database and return results to the AI (up to 20 rows, SELECT only) | Auto-execute (invisible) |
| `propose_plan` | App-level plan artifact: entities in topological order plus a `relations` array describing FK edges (each entry pins `from`, `to`, `column`, `on_delete`). The build pipeline walks `steps` in order and injects `relations` as FK columns on each child table — so the plan is **binding** for ordering and FK topology (#561). Emitted on the turn **after** the user confirms the discovery summary (#564), with at least one `relations` entry per described connection. Foreign keys only — the plan never contains a many-to-many junction table (#564). If the user later explicitly asks for a true many-to-many relationship between two existing tables, build mode handles it via `propose_create_m2m`. The PlanWidget renders it; later turns re-read it from history for self-check. | Auto-complete |

### `POST /conversations/:id/execute-tool`

Approve or reject a pending operation (schema change or page create/update/delete). Requires a valid JWT with `admin` role.

**Request:**

```json
{
  "message_id": "uuid",
  "approved": true,
  "modified_args": null
}

Field	Type	Required	Description
message_id	string	Yes	The assistant message containing the tool call
approved	boolean	No	false to reject (default: true)
modified_args	object	No	User-edited arguments to use instead of the AI’s original proposal

Response (200):

Schema operation:

{
  "status": "complete",
  "result": { "table": "customers", "view": "customers_v" },
  "error": null
}

Page operation:

{
  "status": "complete",
  "result": { "slug": "dashboard" },
  "error": null
}

Status values:

Status	Meaning
complete	Tool executed successfully
rejected	User rejected the proposal
error	Tool execution failed (e.g., invalid column type)

If the message was already processed, returns { "status": "complete", "already_processed": true } (idempotent).

Error responses: 400 if message_id is missing or message status is not pending_approval. 404 if the message or conversation is not found.

POST /conversations/:id/continue

After a tool execution completes, call this endpoint to continue the agentic loop. The server fetches fresh schema (reflecting any tables/views/pages just created), rebuilds the conversation history including the tool result, and streams the AI’s follow-up response via SSE.

Request: No body required. Requires Authorization header and optional X-App-Schema header (same as /chat).

Response: Content-Type: text/event-stream — same SSE event format as /chat (token, tool_call, done, error).

Preconditions: The latest message in the conversation must be a completed or errored tool-call. Returns 400 if:

• The latest message is not a tool-call (e.g., it’s a plain text message)
• The latest message is still pending (e.g., a /continue call is already in progress)

Duplicate call protection: The endpoint locks the conversation row during setup, so concurrent /continue calls are serialized — the second call will see the pending placeholder from the first and return 400.

Chat data model

Conversations and messages live in sys schema tables with public SELECT-only views:

• sys.conversations — one per user chat session, with updated_at auto-bumped on new messages
• sys.messages — role (user/assistant), content (JSONB), status (see lifecycle below)
• public.conversations / public.messages — read-only views filtered by app.user_id

Message status lifecycle:

pending → complete          (normal text response or auto-executed tool call)
pending → pending_approval  (legacy: older conversations with manual approval)
pending_approval → complete (user approved, tool executed)
pending_approval → rejected (user rejected)
pending_approval → error    (tool execution failed)
pending → error             (AI request failed)

After complete or error on a tool-call message, the frontend calls /continue to get the AI’s next response. The model sees the tool result (or error) in its context and can propose the next step or provide a plain-text reply.

Message content types:

Text messages:

{ "type": "text", "text": "Hello world" }

Tool call messages (assistant only):

{
  "type": "tool_call",
  "tool_calls": [{ "id": "call_abc", "name": "propose_create_table", "args": { "table_name": "products", "columns": [...] } }],
  "tool_result": { "result": { "table": "products", "view": "products_v" } },
  "text": "Optional explanatory text from the AI"
}

Page tool call example:

{
  "type": "tool_call",
  "tool_calls": [{ "id": "call_xyz", "name": "propose_create_page", "args": { "slug": "dashboard", "title": "Dashboard", "source": "..." } }],
  "tool_result": { "result": { "slug": "dashboard" } }
}

The tool_result field is populated after execution (approve/reject). Before that, only tool_calls and optionally text are present. Exceptions: preview_page messages include tool_result: { "result": "Preview rendered" } immediately (auto-completed); propose_plan messages include tool_result: { "result": "Plan presented" } immediately (auto-completed).

Chat tables (conversations, messages) are included in the main schema automatically.

Error responses

Auth and validation errors

Pre-query validation errors (missing parameters, invalid function names) and auth endpoints return a plain string:

{ "error": "Missing or invalid sql parameter" }

Database (SQL proxy) errors

Errors from PostgreSQL on /sql and /function/:name return a structured object:

{
  "error": {
    "message": "duplicate key value violates unique constraint \"users_email_key\"",
    "code": "23505",
    "detail": "Key (email)=(alice@example.com) already exists.",
    "hint": null,
    "position": null
  }
}

Fields (code, detail, hint, position) are null when not provided by PostgreSQL. The position field is a character offset into the SQL string where the error occurred — useful for editor integration but not typically shown to end users.

Production scrubbing: In production (NODE_ENV=production), the detail and hint fields are scrubbed to hide literal values — e.g., Key (email)=(alice@example.com) becomes Key (email)=(***). The Failing row contains (...) pattern is also scrubbed.

HTTP status mapping

The SQL proxy maps PostgreSQL error classes to HTTP status codes:

PG code / class	HTTP status	Example
42501	403	permission denied for table ...
28xxx	403	Invalid authorization
23xxx	409	Unique, FK, check, or not-null violation
40xxx	409	Serialization failure, deadlock
42xxx	400	Syntax error, undefined table
08xxx / 53xxx / 57xxx	503	Connection failure, resource exhaustion, shutdown
Other / P0001	400	Application RAISE EXCEPTION

Frontend handling

The frontend apiFetch() function detects both error shapes. For structured database errors, it formats the message with a “Database issue:” prefix using the most descriptive available field (detail > hint > message).

Status	Meaning
400	Missing/invalid input or SQL syntax error
401	Invalid credentials or token
403	Insufficient permissions
409	Constraint violation (unique, FK, check, not-null)
500	Server or database error
503	Database unavailable (connection, resource, shutdown)