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`	`/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": "dashboard" } },
  "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", ... }],
  "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)`	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)
`sys.update_app(schema_name, display_name, description)`	Update app display name and description
`sys.rename_app(old_name, new_name)`	Rename app schema + update all metadata
`sys.drop_app(schema_name)`	Drop app schema + registry entry
`sys.list_apps()`	List all registered apps (also granted to staff, member)
`sys.update_profile(email, display_name)`	Update own profile (all authenticated roles)

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)

The X-App-Schema header selects which app schema to use for the request. When omitted, the request targets the public schema (backward compatible). When set, the backend resolves the 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.

Live events (SSE)

`GET /events`

Server-Sent Events stream for live page updates. When pages are created, updated, or deleted in sys.pages, a PostgreSQL trigger fires pg_notify('page_change', schema_name:slug). The server listens on this channel and broadcasts events to all connected SSE clients.

Auth: Requires a valid JWT with any authenticated role (owner, admin, staff, or member). Token can be passed via Authorization: Bearer <token> header or ?token=<token> query parameter (the browser EventSource API cannot send custom headers).

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

: connected

event: page_change
data: {"schema":"sample_orders","slug":"dashboard"}

: heartbeat

event: page_change
data: {"schema":"public","slug":"settings"}

Event types:

Event	Data	Description
`page_change`	`{ "schema": "sample_orders", "slug": "dashboard" }`	A page was inserted, updated, or deleted
`: connected`	(comment)	Confirms the stream is open
`: heartbeat`	(comment)	Keepalive every 30 seconds

Client example:

const es = new EventSource(`/events?token=${jwt}`);
es.addEventListener("page_change", (e) => {
  const { schema, slug } = JSON.parse(e.data);
  // Re-fetch page if currently viewing this slug
});

The browser EventSource API handles reconnection automatically on network interruption.

Chat service (port 3001)

The chat service runs as a separate Express app on port 3001. 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 (first message only, sent before `done`)
`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_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) | 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` | Structured build plan checklist (rendered as progress tracker widget) | 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).

If using the chat service, apply the chat schema after the main schema:

psql $DATABASE_URL -f apps/chat/chat-schema.sql

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)