Extending Alchemify

Alchemify is designed to be extended. The “ownable code” philosophy means you can modify anything — there’s no black box.

Adding a new table

Using sys.create_table() (recommended)

Admin can create business tables at runtime — no DDL files to edit:

SELECT sys.create_table('orders', '[
  {"name": "customer_name", "type": "text", "required": true},
  {"name": "total", "type": "currency"}
]'::jsonb);

This creates the table, _v view, RLS policies, grants, audit trigger, and metadata row in one transaction. The table appears in the sidebar with full CRUD immediately.

No API routes to write. No ORM models to update. No frontend code to change. The schema drives everything.

Manual DDL (for custom needs)

If you need custom RLS policies, foreign keys, or other features:

1. Define the table in apps/server/schema.sql.
2. Add a _v view, RLS policies, grants, and an audit trigger.
3. Re-apply: psql -U postgres -d alchemify -f apps/server/schema.sql.

System tables

System tables go in sys with a view in public:

1. Define the table in sys schema (e.g., CREATE TABLE IF NOT EXISTS sys.my_table ...).
2. Create a security_barrier view in public with role-based WHERE filtering.
3. Grant appropriate permissions on the view (not the underlying table).
4. System views don’t appear in the CRUD sidebar (no write_target). Business _v views do appear — the frontend uses write_target to route writes to the base table.

Form validation

CrudForm validates field values on submit using validateField(col, raw, displayType?) in apps/web/src/lib/validate.ts. If a displayType is provided (from sys.column_metadata), semantic validation runs first:

displayType	Check
email	Regex: user@host.tld format
url	new URL() try/catch
phone	Regex: digits, spaces, parens, hyphens, optional + prefix
color	Regex: #rrggbb hex format
percent	Float, 0–100
rating	Integer, 1–5

Other display types (currency, multiline, etc.) fall through to data_type validation:

data_type	Check
integer, bigint, smallint	Round-trip parseInt — rejects decimals, scientific notation, trailing text
numeric, real, double precision	parseFloat — rejects non-numeric strings
uuid	Regex for standard UUID format
json, jsonb	JSON.parse in try/catch

Empty values are skipped (handled separately by the required-field check).

To add validation for a new type, add a branch in validateField(). Tests are in apps/web/src/lib/validate.test.ts.

Display types and formatting

The CRUD UI consumes display_type from sys.column_metadata for widget selection, validation, and display formatting. The known display types are defined in KNOWN_DISPLAY_TYPES in schema.ts:

text, multiline, email, url, phone, color, integer, currency, percent, rating, boolean, choice, date, datetime, uuid, file, image, jsonb

The getDisplayType(table, col, schema, dataType?) helper returns the display type only if it’s in this whitelist — unknown values are ignored. When no explicit metadata exists and the column’s Postgres data_type is boolean, it automatically returns "boolean" (no column_metadata entry needed). The FormattedValue component (apps/web/src/components/FormattedValue.tsx) renders values with display-type-aware formatting in detail and list views — booleans show a green check icon for true and a gray X icon for false (null renders as blank).

Column labels

Column headers and field labels use fieldLabel() from apps/web/src/lib/schema.ts. System columns get human-friendly names (id → “ID”, created_at → “Created on”, updated_at → “Last updated”); all other columns fall through to prettyLabel() which converts snake_case to Title Case.

Seeding test data

The Builder AI can generate sample data for any table via the propose_seed_data tool. When the user asks for test/sample data:

1. The AI calls propose_seed_data with just table_name and an optional count (1-20, default 3)
2. The server generates realistic rows via a focused AI call with response_format: json_object
3. A preview widget shows the data in a read-only table
4. The user clicks “Insert” to confirm (or “Cancel” to reject)
5. The server validates the table and columns against get_schema(), then runs a parameterized multi-row INSERT
6. If a constraint error occurs (unique violation, FK violation, NOT NULL, check constraint), the server asks the AI to fix the data and retries (up to 3 attempts total)
7. Non-constraint errors fail immediately without retry

The seed data handler runs each INSERT attempt in its own short transaction (separate from the withAuth transaction) so that retries don’t hold connections across OpenAI round-trips. All row values are passed as $N parameters — never interpolated into SQL.

Configuring FK display columns

When a table is created, sys.create_table() auto-detects which columns to show in FK autocomplete dropdowns (priority: name > title > label > email > first text column). This is stored in sys.table_metadata as display_columns.

To change display columns after table creation, users can ask the Builder AI (e.g., “show email instead of name for customers”). The configure_fk_display tool validates the requested columns against the actual table schema and updates sys.table_metadata. You can also update it directly:

UPDATE sys.table_metadata
SET meta = jsonb_set(meta, '{display_columns}', '["email", "phone"]'::jsonb)
WHERE table_name = 'customers' AND schema_name = 'public';

Adding a custom endpoint

If you need logic beyond what SQL can express, add a route in apps/server/src/app.ts:

app.post('/my-endpoint', async (req, res) => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(`SET LOCAL ROLE 'staff'`);
    // your logic here
    await client.query('COMMIT');
    res.json({ result });
  } catch (error) {
    await client.query('ROLLBACK');
    res.status(500).json({ error: 'Something went wrong' });
  } finally {
    client.release();
  }
});

Follow the same transaction pattern: BEGIN → SET LOCAL ROLE → work → COMMIT.

Adding a SQL function

For reusable logic, put it in a PostgreSQL function:

CREATE OR REPLACE FUNCTION my_function(arg text)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY INVOKER  -- runs as the caller's role, respects RLS
AS $$
BEGIN
  -- your logic
END;
$$;

Use SECURITY INVOKER (the default) for functions that should respect RLS. Use SECURITY DEFINER only for privileged operations like auth — and put those in the sys schema with SET search_path = sys, public.

Writing a page

Pages are TSX stored in the sys.pages table (exposed via a view in public). Each row has:

Column	Default	Purpose
slug	—	URL slug (unique, lowercase alphanumeric + hyphens), e.g. dashboard. Pages served at /page/<slug>.
title	—	Display name (shown in sidebar)
source	—	TSX source code
role	'staff'	Minimum role to view: owner, admin, staff, member, or anon
show_in_nav	true	Whether the page appears in the sidebar
status	'draft'	draft or published — non-admin users only see published pages
published_at	NULL	Timestamp of last publish
published_by	NULL	User ID that published the page

Platform pages (.tsx files)

Platform pages shipped with Alchemify are stored as standalone .tsx files with comment frontmatter:

// @slug settings
// @title Settings
// @role admin
// @nav true

import { Page, PageHeader } from "@alchemify/layout";
// ... component code

These live in apps/server/schema/pages/ (platform) and apps/server/schema/sample-pages/ (dev samples). Running seed-pages.sh generates the SQL INSERT statements that schema.sql and sample-app.sql include via \ir. See Database & Schema for details.

To add a new platform page, create a .tsx file in the appropriate directory with the four required frontmatter keys (slug, title, role, nav), then re-run seed-pages.sh.

Runtime pages (SQL insert)

Pages can also be inserted at runtime by an admin:

INSERT INTO pages (slug, title, source, role) VALUES (
  'dashboard',
  'Dashboard',
  'import { Page, PageHeader } from ''@alchemify/layout'';
export default function Dashboard() {
  return <Page><PageHeader title="Dashboard" /></Page>;
}',
  'staff'
);

To create a page that’s accessible by URL but hidden from the sidebar:

INSERT INTO pages (slug, title, source, show_in_nav) VALUES (
  'reports-detail', 'Report Detail', '...', false
);

The view controls who sees what — owner/admin sees all pages (draft and published), staff sees published staff + member + anon pages, member sees published member + anon pages, anon sees only published anon pages. Pages with show_in_nav = true appear in the sidebar between Builder and the entity links. When a page is inserted, updated, or deleted, a NOTIFY trigger fires so the frontend live-reloads the change.

Draft/published workflow

Pages are created with status = 'draft' by default. Non-admin users (staff, member, anon) only see published pages. Owner and admin see all pages regardless of status.

When the Builder AI updates a page’s source code, the page is automatically reset to draft status. This ensures that AI-generated changes are reviewed before going live.

To publish a page:

SELECT * FROM sys.publish_page('dashboard');

To unpublish (revert to draft):

SELECT * FROM sys.unpublish_page('dashboard');

Both functions are restricted to owner and admin roles. Platform seed pages (from seed-pages.sh) are inserted as published.

Convention

Export a default function component. It receives no props — use hooks for data and context:

import { Page, PageHeader, Loading } from '@alchemify/layout';
import { useSql } from '@alchemify/data';
import { sql } from '@alchemify/sql';
import { DataTable } from '@alchemify/ui';

export default function Orders() {
  const { data, isLoading, error } = useSql(
    sql`SELECT * FROM orders`
  );

  if (isLoading) return <Loading />;
  if (error) return <div>Error: {error.message}</div>;

  return (
    <Page>
      <PageHeader title="Orders" description="All orders" />
      <DataTable columns={['id', 'customer', 'total', 'status']} data={data} />
    </Page>
  );
}

For custom query keys or caching behavior, fall back to useQuery + exec:

import { useQuery, exec } from '@alchemify/data';
import { sql } from '@alchemify/sql';

const { data } = useQuery({
  queryKey: ['my-custom-key'],
  queryFn: () => exec(sql`SELECT * FROM orders`),
});

Null comparisons

sql\WHERE col = ${null}`sendscol = $1withNULL, which PostgreSQL evaluates as unknown (never matches). Use sql`WHERE col IS NULL“ instead.

Available modules

Package	Exports
@alchemify/ui	Button, Card (+ sub-components), Input, Badge, Table (+ sub-components), DataTable, Label, Checkbox, Textarea, AlertDialog (+ sub-components), FileField
@alchemify/data	useSql, useQuery, useMutation, useQueryClient, exec, rawSql
@alchemify/sql	sql, joinSQL, raw
@alchemify/auth	useAuth
@alchemify/nav	useNavigate, useParams, useSearchParams, Link, NavLink
@alchemify/layout	Page, PageHeader, Loading, ErrorBoundary
@alchemify/util	cn, formatDate, toast, groupBy, nest

The canonical list of @alchemify/ui exports lives in packages/types/src/ui-components.ts. The chat service reads this registry at runtime to build the AI system prompt, so the AI only suggests components that actually exist. When adding or removing UI components, update this registry alongside apps/web/src/modules/ui.ts — a test in packages/types automatically verifies they stay in sync.

For details on the compilation pipeline and module registry, see Frontend Architecture.

Tech stack reference

Layer	Technology
Runtime	Node.js 20+
Language	TypeScript
Server	Express
Database	PostgreSQL 14+
Auth	Custom (JWT + bcrypt)
Frontend	React 19, React Router 7, TanStack Query 5
Styling	Tailwind CSS 4, shadcn/ui, Radix UI
In-browser compiler	Sucrase
Build	Vite 6
Testing	Vitest + Supertest
Package manager	pnpm