Database & Schema

The database is the core of Alchemify. Authorization and data validation are enforced at the PostgreSQL level.

Schema management

The schema is split into two files:

File	Purpose
apps/server/schema.sql	Platform DDL: roles, extensions, sys.* tables, views, auth functions, get_schema()
apps/server/sample-apps/install-samples.sql	Sample business tables for dev/testing (orders, projects, recipes)

Both use IF NOT EXISTS and idempotent patterns so they’re safe to re-apply at any time. Before applying, generate the page SQL from .tsx source files:

cd apps/server && bash seed-pages.sh
psql $DATABASE_URL -f apps/server/schema.sql
psql $DATABASE_URL -f apps/server/sample-apps/install-samples.sql

Page source files

Platform and sample pages are stored as standalone .tsx files with // @key value frontmatter:

Directory	Included by	Purpose
apps/server/schema/pages/	schema.sql	Platform pages (e.g., Settings)
apps/server/schema/sample-pages/	sample-apps/install-samples.sql	Sample pages for dev (e.g., Dashboard)

Running seed-pages.sh reads the .tsx files, parses their frontmatter, and generates schema/pages.gen.sql and schema/sample-pages.gen.sql. The SQL files use \ir to include these generated files. The .gen.sql files are gitignored.

In production, business tables are created by the AI via create_table() — the sample-app file is only for development.

Migrations

Existing databases (HQ and tenants) are upgraded in two phases:

1. Base schema reapply — schema.sql is re-applied. Idempotent patterns (CREATE OR REPLACE, IF NOT EXISTS) make this safe. This picks up new/changed functions, new tables, grants, etc.
2. Pending migrations — dbmate runs migration files from apps/server/migrations/. These handle non-idempotent changes: ALTER TABLE, column renames, data backfills.

Split responsibility: this repo defines migrations (what changed); the admin repo runs them against production databases (HQ + all tenants).

Not every schema file change needs a migration — only non-idempotent ones. ops/check-migrations.sh flags schema changes without migrations at release time; idempotent-only changes can be confirmed manually.

See apps/server/migrations/CONVENTIONS.md for full details.

Two-schema layout

Alchemify uses two PostgreSQL schemas with a deny-by-default model:

Schema	Contains	Who can see it
sys	System tables (users, magic_links, pages) and auth functions	authenticator (infra), anon (auth functions), owner/admin (table management functions)
public	Business tables (customers, items, orders, order_items), views over sys tables, utility functions	App roles (owner, admin, staff, member, anon)

App roles cannot query sys tables directly. System data is exposed through auto-updatable views in public and SECURITY DEFINER functions in sys.

If you forget to create a view for a sys table, the data is hidden by default — that’s the point.

Roles

Alchemify uses PostgreSQL roles for authorization:

Role	Access
authenticator	Connection pool role. No direct data access.
owner	Full read/write (identical to admin at DB level; org-management distinction is app-level)
admin	Full read/write on all public tables and views
staff	Limited access via views (system) and RLS (business)
member	Read-only access to business data
anon	Unauthenticated — can call auth functions only

The authenticator role is granted SET ROLE to the five app roles. The server switches role per-request based on the JWT.

Views (system tables)

System tables in sys are exposed through security_barrier views in public. These views are auto-updatable (single-table, no aggregation), so admin can INSERT/UPDATE/DELETE through them.

public.users view

• Hides password_hash (never exposed)
• owner/admin sees all user records
• staff/member sees only their own record (filtered by app.user_id)
• anon has no access

public.pages view

• owner/admin sees all pages (draft and published) and can write through the view
• staff sees published pages with role staff, member, or anon (read-only)
• member sees published pages with role member or anon (read-only)
• anon sees only published pages with role anon (read-only)

public.apps view

• Exposes all sys.apps columns (id, schema_name, display_name, description, created_by, created_at, updated_at)
• All roles (including anon) can SELECT — app names aren’t sensitive, and anon calls get_schema()
• Read-only for non-admin roles (no INSERT/UPDATE/DELETE)

View convention (_v suffix)

Business tables created via sys.create_table() (and the built-in customers/items tables) have a companion _v view that hides audit columns (created_at, updated_at, updated_by). Views use security_barrier=true. By default, SELECT is granted to owner, admin, staff, and member. sys.set_table_permissions() can revoke or grant view access for staff, member, and anon; sys.alter_table() preserves these grants when rebuilding the view.

Object	Purpose
customers	Base table — all columns, INSERT/UPDATE/DELETE go here
customers_v	Read view — id + user columns only, no audit columns

The get_schema() response includes a write_target field for _v views:

• customers_v → write_target: "customers" — frontend routes writes to the base table
• customers → write_target: null — frontend can suppress from sidebar (has a _v counterpart)
• table_metadata → write_target: null — no _v counterpart, not a business table

Row Level Security (RLS)

Business tables (customers, items, orders, order_items) use RLS policies that filter data based on session variables:

SET LOCAL ROLE 'staff';
SET LOCAL app.user_id = 'user-uuid';

The server sets these variables at the start of each transaction. Application code never checks permissions — PostgreSQL handles it.

Tables

Location	Table	Purpose
sys	users	User accounts (email, password hash, role, display_name, is_active)
sys	magic_links	One-time auth tokens
sys	pages	Custom TSX pages (slug, title, source, role, show_in_nav, status)
sys	table_metadata	Per-table config (FK display columns, M2M junction info)
sys	app_metadata	Global app config (app description, AI instructions)
sys	column_metadata	Per-column config (labels, prompts, validation hints)
sys	field_options	Choice field values (dropdown options, badge colors, sort order)
sys	apps	App registry (schema name, display name, description, created_by)
public	customers	Example business table (sample-apps/install-samples.sql)
public	items	Example business table (sample-apps/install-samples.sql)
public	orders	Example business table (sample-apps/install-samples.sql)
public	order_items	Example business table (sample-apps/install-samples.sql)

Pages

The pages table (in sys, exposed via a view in public) stores custom TSX pages that the frontend compiles and renders at runtime.

Column	Type	Default	Purpose
slug	TEXT UNIQUE NOT NULL	—	URL slug (lowercase alphanumeric + hyphens), pages served at /page/<slug>. The sys prefix is reserved — slugs matching sys or sys-* are rejected by a CHECK constraint.
title	TEXT NOT NULL	—	Display name (shown in sidebar)
source	TEXT NOT NULL	—	TSX source code
role	TEXT NOT NULL	'staff'	Minimum role: owner, admin, staff, member, or anon
show_in_nav	BOOLEAN NOT NULL	true	Whether the page appears in the sidebar
status	TEXT NOT NULL	'draft'	draft or published — non-admin roles only see published pages
published_at	TIMESTAMPTZ	NULL	Timestamp of last publish (set by sys.publish_page())
published_by	UUID	NULL	User who published (set by sys.publish_page())

The role and status fields control visibility through the view’s WHERE clause:

• owner/admin sees all pages (draft and published)
• staff sees published pages with role staff, member, or anon
• member sees published pages with role member or anon
• anon sees only published pages with role anon

Only owner/admin can insert, update, or delete pages — other roles have read-only access.

Pages with show_in_nav = true appear in the sidebar between Builder and the entity links. Set it to false for pages that should be accessible by URL but hidden from navigation (e.g., detail pages linked from other pages).

An AFTER INSERT OR UPDATE OR DELETE trigger fires pg_notify('page_change', slug) so the frontend can live-reload changed pages.

A BEFORE INSERT OR UPDATE validation trigger rejects page source containing forbidden browser APIs (e.g., fetch(), eval(), localStorage, document.cookie, <script> tags). The trigger skips validation when only metadata columns change (source unchanged). Violations are collected and reported together — for example: Page source contains forbidden patterns: fetch(), eval(). This covers all write paths: chat service tool execution, SQL proxy, and direct psql.

Metadata tables

Three metadata tables in sys store structured config for the AI and frontend. All are exposed via security_barrier views in public and included in get_schema().

Access control for all three: SELECT granted to all roles (owner/admin/staff/member see data, anon sees empty set). Only owner/admin can write.

sys.table_metadata

Per-table configuration (composite PK: schema_name + table_name, column: meta jsonb). The LLM populates this when creating tables. The schema_name column defaults to 'public' and scopes metadata to a specific app schema.

INSERT INTO table_metadata (table_name, meta)
VALUES ('customers', '{"display_columns": ["name"]}');

The meta JSON supports:

• display_columns — columns to show in FK autocomplete (e.g., ["name"] instead of raw IDs). Auto-detected at creation; reconfigurable via the Builder AI’s configure_fk_display tool.
• junction — (future) M2M junction table info ({ "left": "students", "right": "courses" })

sys.app_metadata

Global key/value config (composite PK: schema_name + key, column: value jsonb). Stores app-wide context for the AI and frontend. The schema_name column defaults to 'public'.

Known keys:

• app_description — one-sentence description of the app (JSON string)
• ai_instructions — additional notes/conventions for the AI builder (JSON string)
• home_page — role-keyed object mapping roles to page slugs. When a user opens the app, the frontend walks the role hierarchy (owner → admin → staff → member → anon) starting from the user’s role and redirects to the first matching page. Example: {"admin": "admin-dashboard", "staff": "dashboard"}

INSERT INTO app_metadata (key, value)
VALUES ('app_description', '"Order management application"');
-- Set a landing page per role:
INSERT INTO app_metadata (key, value)
VALUES ('home_page', '{"admin": "admin-dashboard", "staff": "dashboard"}');

sys.column_metadata

Per-column configuration (composite PK: schema_name + table_name + column_name, column: meta jsonb). Stores labels, prompts, and validation hints. The schema_name column defaults to 'public'.

INSERT INTO column_metadata (table_name, column_name, meta)
VALUES ('orders', 'status', '{"label": "Order Status", "prompt": "One of: pending, shipped, delivered"}');

sys.create_table() automatically seeds column_metadata rows for each column with a display_type field (e.g., {"display_type": "email"}), and sys.drop_table() cleans them up. For choice columns, options are stored in sys.field_options (see Choice fields).

Adding tables

Via sys.create_table() (recommended)

Admin can create business tables at runtime via the SQL proxy:

SELECT sys.create_table('orders', '[
  {"name": "customer_name", "type": "text", "required": true},
  {"name": "total", "type": "currency"},
  {"name": "status", "type": "text", "default": "''pending''"}
]'::jsonb);
-- Returns: {"table": "orders", "view": "orders_v", "foreign_keys": [], "indexes": [], "checks": []}

This creates everything in one transaction:

• Table with id SERIAL PRIMARY KEY + your columns + audit columns (created_at, updated_at, updated_by)
• _v view hiding audit columns
• RLS policies for owner, admin, staff, and member (read-only)
• Grants on table, view, and sequence
• Audit trigger (updated_at + updated_by set automatically on UPDATE)
• Metadata rows in sys.table_metadata and sys.column_metadata
• Foreign key constraints and auto-indexes (if references is used)
• Check constraints (if check is used)

Semantic types (preferred — the AI uses these):

Category	Types
Text	text, multiline, email, url, phone, color
Number	integer, currency, percent, rating
Date/Time	date, datetime
Other	boolean, choice, uuid, file, image, jsonb

These map to Postgres storage types automatically (e.g., currency → numeric(12,2), datetime → timestamptz, rating → smallint with CHECK 1-5, choice → text). The original semantic type is stored in column_metadata as display_type.

Raw Postgres types (bigint, smallint, numeric(p,s), varchar(n), char(n), real, double precision, etc.) still work — the backend infers a display_type from the storage type.

Virtual file and image types

The file and image types are convenience shorthands for file upload columns. Both work identically under the hood — when you specify either type, the backend automatically:

• Creates a UUID column
• Adds a foreign key constraint to sys.files(id) with ON DELETE SET NULL
• Creates an auto-index on the column

The difference is purely semantic: file renders as a download link, image renders as an <img> tag. The distinction is stored in column_metadata as display_type.

This means {"name": "photo", "type": "image"} is equivalent to {"name": "photo", "type": "uuid", "references": "files", "on_delete": "set null"}, but shorter and less error-prone. Do not specify references on file/image columns — the backend rejects it to avoid confusion.

The get_schema() function reports these columns as data_type: "file" or "image" (not "uuid"), so the frontend knows which widget to render. Legacy columns created with "type": "uuid", "references": "files" are reported as "file".

Both types work with alter_table() add operations but cannot be used with change_type — drop the column and add a new one instead.

Choice fields

The choice type creates a text column backed by sys.field_options for dropdown/badge rendering. Options are defined inline when creating a column:

SELECT sys.create_table('tasks', '[
  {"name": "status", "type": "choice", "default": "''todo''",
   "options": [
     {"value": "todo", "label": "To Do"},
     {"value": "in_progress", "label": "In Progress"},
     {"value": "done", "label": "Done"}
   ]}
]'::jsonb);

Options can be simple strings (["open", "closed"]) or objects with value, label, and color fields. Validation is soft — the database accepts any text value; the UI renders a dropdown from the configured options.

The get_schema() response includes options in column_metadata for choice columns:

{"display_type": "choice", "label": "Status", "options": [
  {"value": "todo", "label": "To Do", "color": null}, ...
]}

alter_table() operations maintain field_options automatically: add inserts options, drop removes them, rename updates the column name, and change_type away from choice clears them (changing to choice with options inserts them).

Column spec

Each column object supports:

Field	Type	Default	Notes
name	text	required	snake_case column name
type	text	required	A semantic type (e.g., email, currency, datetime) or raw Postgres type. Use file for file upload columns, image for image columns.
required	boolean	false	Adds NOT NULL constraint
references	text	—	Target table name (always references id). Table must already exist in public (self-referencing FKs require manual DDL).
on_delete	text	"no action"	One of: no action, cascade, restrict, set null, set default
default	text	—	SQL expression: "0", "false", "'pending'", "now()"
check	text	—	SQL expression. $COL is replaced with the column name. e.g. "$COL > 0"
options	array	—	For choice type only. Array of strings or {value, label?, color?} objects.

Example with foreign keys

SELECT sys.create_table('order_items', '[
  {"name": "order_id", "type": "integer", "required": true, "references": "orders", "on_delete": "cascade"},
  {"name": "item_id", "type": "integer", "required": true, "references": "items"},
  {"name": "quantity", "type": "integer", "required": true, "default": "1", "check": "$COL > 0"},
  {"name": "price", "type": "currency", "required": true}
]'::jsonb);

This automatically:

• Creates FK constraints (order_items_order_id_fkey, order_items_item_id_fkey)
• Creates indexes on FK columns (order_items_order_id_idx, order_items_item_id_idx)
• Adds the check constraint (order_items_quantity_check)
• Returns all constraint/index names in the response

Via sys.alter_table() (modifying existing tables)

Admin can modify tables that were created via sys.create_table():

SELECT sys.alter_table('orders', '[
  {"op": "add", "name": "notes", "type": "text"},
  {"op": "drop", "name": "status"},
  {"op": "rename", "old_name": "total", "new_name": "amount"},
  {"op": "change_type", "name": "amount", "new_type": "numeric(12,2)"}
]'::jsonb);
-- Returns: {"table": "orders", "operations_applied": 4}

Supported operations:

Operation	Required fields	Description
add	name, type (optional: required)	Add a new column
drop	name	Drop an existing column and its data
rename	old_name, new_name	Rename a column (data preserved)
change_type	name, new_type	Change a column’s type (uses USING col::new_type). Changing to rating adds a CHECK (1-5) — fails with a friendly error if existing data is outside that range. Changing away from rating drops the CHECK.

Constraints:

• The table must have been created via sys.create_table() (checked via sys.metadata)
• Reserved columns (id, created_at, updated_at, updated_by) cannot be added, dropped, renamed, or type-changed
• Same type support as create_table() (semantic types and raw Postgres types)
• The _v view is dropped before operations and rebuilt afterward, so the view always reflects the current columns

Via sys.set_table_permissions() (managing role access)

Admin can modify RLS policies and grants for staff, member, and anon roles on tables created via sys.create_table():

-- Read-only: staff can SELECT but not INSERT/UPDATE/DELETE
SELECT sys.set_table_permissions('orders', 'staff', true, false, false);

-- Full access (default state for staff after create_table)
SELECT sys.set_table_permissions('orders', 'staff', true, true, true);

-- Grant read access to anon
SELECT sys.set_table_permissions('orders', 'anon', true, false, false);

-- Grant member write+delete
SELECT sys.set_table_permissions('orders', 'member', true, true, true);

-- No access: role sees zero rows, DML is blocked
SELECT sys.set_table_permissions('orders', 'staff', false, false, false);

The function drops all existing policies for the specified role on the table and recreates them based on the flags. When all three are true, a single FOR ALL policy is used. Otherwise, individual policies are created (e.g., staff_select, member_insert, anon_delete). Owner and admin policies are never touched.

In addition to RLS policies, the function keeps GRANTs in sync:

• Base table: GRANT/REVOKE SELECT, INSERT, UPDATE, DELETE as needed
• View (_v): GRANT/REVOKE SELECT based on can_read
• Sequence: GRANT/REVOKE USAGE, SELECT based on can_write (needed for SERIAL INSERT)

Only staff, member, and anon roles can be modified — passing 'admin' or 'owner' raises an error (they always have full access via their own policies).

Querying current permissions:

SELECT * FROM sys.get_table_permissions();
-- Returns: role_name, table_name, can_read, can_write, can_delete
-- 3 rows per table (staff, member, anon)
-- Only includes tables with RLS enabled

Default permissions after create_table():

• staff: full access (read, write, delete)
• member: read-only
• anon: no access

This function uses SECURITY DEFINER (it reads pg_policies which requires catalog access). It checks what permissive policies exist for each role on each table.

User management

Listing users

The public.users view provides user data without the password_hash column. Owner/admin see all rows; staff/member see only their own row.

SELECT id, email, role, display_name, is_active, created_at
FROM public.users ORDER BY created_at;

sys.invite_user(email, role)

Creates a new user account (no password) and generates a magic link token:

SELECT * FROM sys.invite_user('alice@example.com', 'staff');
-- Returns: magic_link_token

The invited user authenticates via the returned magic link token. The role must be owner, admin, staff, or member. Only owner can invite users with owner or admin roles — admins can only invite staff and member. Duplicate emails raise an error.

sys.update_profile(new_email, new_display_name)

Lets any authenticated user update their own profile:

SELECT * FROM sys.update_profile('newemail@example.com', 'Jane Doe');
-- Returns: user_id, user_email, user_display_name

• new_email: NULL keeps the current email; validates format
• new_display_name: NULL clears the display name; empty string treated as NULL; max 200 chars
• Email uniqueness enforced by the existing UNIQUE constraint

Granted to all authenticated roles (owner, admin, staff, member).

App management (schema-per-app)

Apps are managed via sys.* functions called through the SQL proxy. Each app gets its own PostgreSQL schema.

sys.create_app(schema_name, display_name, description) — Creates a new schema, grants role access, and inserts a registry entry in sys.apps. Returns the new app row as jsonb.

sys.update_app(schema_name, display_name, description) — Updates the display name and description. Returns the updated app row as jsonb.

sys.rename_app(old_name, new_name) — Renames the schema via ALTER SCHEMA and updates schema_name in sys.apps, sys.table_metadata, sys.app_metadata, sys.column_metadata, sys.field_options, and sys.pages. Same validation as create_app (lowercase [a-z][a-z0-9_]*, no reserved names/prefixes). No-op if old == new. Returns the updated app row as jsonb.

sys.drop_app(schema_name) — Removes metadata, drops the schema with CASCADE, and deletes the registry entry.

sys.list_apps() — Returns all registered apps ordered by display name. Granted to owner, admin, staff, and member.

The public.apps view exposes all sys.apps columns (SELECT only, all roles including anon). The get_schema() response includes an apps key with the full list.

Via sys.create_view() (custom reports)

Admin can create read-only SQL views (reports) at runtime. These appear in get_schema() as relations with is_custom_view: true and are read-only (SELECT only, no write_target).

SELECT sys.create_view('order_summary',
  'SELECT o.id, c.name AS customer_name, o.order_date, o.status
   FROM orders o JOIN customers c ON c.id = o.customer_id');
-- Returns: {"view": "order_summary"}

This creates:

• A view in public (no security_barrier — these are reports, not RLS boundaries)
• SELECT grants to owner, admin, staff, member (not anon)
• A sys.table_metadata row with {"is_view": true}

Views support any valid SELECT — joins, CTEs, aggregations, subqueries:

SELECT sys.create_view('monthly_revenue',
  'WITH order_totals AS (
     SELECT order_id, SUM(quantity * unit_price) AS total
     FROM order_items GROUP BY order_id
   ) SELECT date_trunc(''month'', o.order_date) AS month, SUM(ot.total) AS revenue
     FROM orders o JOIN order_totals ot ON ot.order_id = o.id GROUP BY 1');

Name rules (same as create_table): lowercase [a-z][a-z0-9_]*, no pg_/sys_/information_schema prefix, no _v suffix, no collision with existing tables/views, no platform view names (users, pages, files, table_metadata, app_metadata, column_metadata).

No alter_view: drop and recreate instead.

sys.drop_view(name)

Drops a custom view created via sys.create_view():

SELECT sys.drop_view('order_summary');

Only views tracked in sys.table_metadata with is_view: true can be dropped — this prevents accidentally dropping platform views or _v views.

Manual DDL (for custom needs)

If you need custom RLS policies or other features beyond what sys.create_table() provides, add DDL to apps/server/sample-apps/install-samples.sql (for dev/testing):

1. Add the CREATE TABLE statement.
2. Add a _v view (exclude audit columns, use security_barrier=true).
3. Add RLS policies, grants, and an audit trigger.
4. Re-apply: psql $DATABASE_URL -f apps/server/sample-apps/install-samples.sql.

System tables

New system tables go in sys with a view in public:

1. Add CREATE TABLE IF NOT EXISTS sys.my_table ... to schema.sql.
2. Create a security_barrier view in public with appropriate WHERE filtering.
3. Grant SELECT/INSERT/UPDATE/DELETE on the view to the relevant roles.
4. No RLS needed on the sys table — the view handles access control.

SECURITY DEFINER audit

Every sys function’s security mode is chosen deliberately. SECURITY DEFINER is used only when the function needs to access data that the caller’s role cannot reach directly.

Function	DEFINER	Reason
login, create_magic_link, verify_magic_link	Yes	Anon needs sys.users/magic_links access
set_initial_password, update_profile	Yes	Session-based self-update of sys.users
publish_page, unpublish_page	Yes	Audit column writes (published_by)
create_table, alter_table, drop_table	Yes	DDL + metadata + RLS policy management
invite_user	Yes	sys.users + magic_links writes
set_table_permissions	Yes	DDL (CREATE/DROP POLICY) + GRANT/REVOKE
create_app, drop_app	Yes	DDL (schema lifecycle) + sys.apps writes
update_app, rename_app	Yes	sys.apps writes + DDL (schema rename)
list_apps	Yes	sys.apps reads (single-org, no filtering)
create_view, drop_view	Yes	DDL + metadata
enforce_user_changes	Yes	Trigger: cross-user invariants
get_table_permissions	Yes	Reads pg_policies for staff/member/anon
get_schema	No	Must be INVOKER (HAS_TABLE_PRIVILEGE)
All other triggers/utilities	No	Pure computation or no privilege needed