Architecture

Alchemify uses a thin SQL proxy architecture. Instead of building REST endpoints for every resource, the server is a ~100-line proxy that:

1. Verifies the JWT from the Authorization header
2. Calls SET LOCAL ROLE to switch to the caller’s database role
3. Executes the SQL query
4. Returns the result

Authorization lives in PostgreSQL, not in application code. Views filter system tables, RLS policies filter business tables. The server never checks permissions — it trusts the database.

Both Express apps set Content-Security-Policy headers on every response, restricting scripts, connections, and framing to 'self'. This provides defense-in-depth alongside the iframe sandbox used for custom pages.

Why this approach

• Less code — no REST routes to generate or maintain
• Smaller attack surface — easier to audit ~100 lines than a full API layer
• Schema = API — add a table and the frontend can query it immediately
• Battle-tested security — PostgreSQL views and RLS are mature and well-understood

Repo layout

apps/
  web/             # Frontend app (Vite + React)
  server/          # Thin SQL proxy + auth endpoints
  chat/            # Builder chat service (separate Express app)
  docs/            # This documentation site (Starlight)
packages/
  db/              # Shared auth/database helpers + tenant utilities (@alchemify/db)
  types/           # Shared TypeScript types
  server/schema.sql  # Database schema (roles, tables, RLS, functions)

Key roles

Role	Purpose
authenticator	Login role for the connection pool. Cannot access data directly.
owner	Full access (identical to admin at DB level; org-management distinction is app-level)
admin	Full access to all data
staff	Limited access (filtered by views and RLS)
member	Read-only access to business data
anon	Unauthenticated access (very limited)

The authenticator role connects to PostgreSQL. On each request, the server calls SET LOCAL ROLE to impersonate the appropriate app role based on the JWT.

Request flow

Client → JWT in header → Server verifies JWT
  → BEGIN
  → SET LOCAL ROLE 'staff'
  → SET LOCAL app.user_id = '...'
  → Execute SQL
  → COMMIT
  → Return result

For auth operations (login, magic links, invite), the server calls sys.-prefixed SECURITY DEFINER functions (e.g., sys.create_magic_link($1)). These functions have their own search_path = sys, public so they can access system tables directly.

Every request is logged as structured JSON via pino — including request ID, role, and response time — with no sensitive data (passwords, tokens, or SQL parameter values).

Frontend: Schema-Driven CRUD + TSX-in-DB

The frontend has two complementary systems:

Schema-driven CRUD (auto-generated)

The app shell calls get_schema() on login to fetch database metadata (tables, columns, keys, permissions). From this it auto-generates:

• Sidebar — collapsible sections for Tables, Views, and Pages, sorted alphabetically
• List view (/:schema/table/:entity or /:schema/view/:entity) — DataTable with all rows, clickable to detail
• Detail view (/:schema/table/:entity/:id) — field/value display with edit and delete actions
• Create form (/:schema/table/:entity/new) — type-appropriate inputs generated from column metadata
• Edit form (/:schema/table/:entity/:id/edit) — pre-filled form

No code to write — add a table, apply RLS, and it appears in the UI with full CRUD.

TSX-in-DB (custom pages)

For pages that go beyond CRUD (dashboards, reports, custom workflows), pages are stored as TSX source code in the database and compiled in the browser at runtime using Sucrase.

1. The app shell is a static Vite bundle served from a CDN. It contains React, the component library, and the compilation pipeline — but no page content.
2. Pages are rows in the sys.pages table, exposed via a view in public. Each row has a source column containing TSX code.
3. When a user navigates to a page, the shell fetches the TSX source and compiles it in-browser.
4. The compiled component is rendered inside an error boundary that catches both compilation and runtime errors.

Why this hybrid

• Zero config for standard CRUD — schema drives everything
• Full React power for custom pages — not limited to a template language
• Zero-deploy page updates — change a row, the page updates live (via DB NOTIFY + Socket.io)
• Free tier at zero marginal cost — the shell is static, pages are just database rows

For details on the compilation pipeline and module system, see the Frontend page.