Database & Schema

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

Schema management

Platform DDL — roles, extensions, sys.* tables, views, auth functions, get_schema() — lives in apps/server/schema.sql. It uses IF NOT EXISTS and idempotent patterns so it is 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

Business tables are always created by the AI via create_table() — there is no dev-only SQL fixture for business data.

Page source files

Platform pages are stored as standalone .tsx files with // @key value frontmatter in apps/server/schema/pages/, included by schema.sql.

Running seed-pages.sh reads the .tsx files, parses their frontmatter, and generates schema/seed/pages.gen.sql (gitignored). schema.sql uses \ir to include the generated file.

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. At release time, ops/schema-diff.sh auto-generates migrations by structurally diffing two databases using pg-schema-diff.

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 the registry columns (id, schema_name, display_name, created_by, created_at, updated_at) plus description derived from sys.app_metadata.app_description via LEFT JOIN — that key is the canonical (and only) source.
• 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 and resolves FK labels. 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 — user columns + {fk_col}__label for FK display, no audit columns

For each FK column, the _v view LEFT JOINs the referenced table and adds a {fk_col}__label column using the target table’s display_columns metadata. For example, orders_v includes customer_id__label resolved from customers.name.

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, created_by). App descriptions live in sys.app_metadata.app_description.

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).

Page source is validated by a Node AST walker (apps/chat/src/validate-page-source.ts, TypeScript compiler API) before every chat-service write to sys.pages — tool handlers (propose_create_page, propose_update_page, propose_create_crud_pages) and the /fix-component auto-repair endpoint alike. It rejects forbidden browser APIs — fetch(), eval(), new Function(...), XMLHttpRequest, localStorage, sessionStorage, document.cookie, document.querySelector(), document.getElementById(), navigator.sendBeacon(), window.open(), window.location, dynamic import(), <script> intrinsic tags — including qualified forms like window.fetch(...), globalThis.localStorage, document['cookie'], and optional chaining. Because it walks the AST rather than regex-matching strings, type annotations like onClick: Function are not flagged (they live under TypeNode subtrees the walker skips). Violations are collected and reported together, e.g. Page source contains forbidden patterns: fetch(), eval(). For propose_create_crud_pages the error is prefixed with the failing payload name (list_source:, view_source:, edit_source:). Trust boundary: writes made directly via the SQL proxy (admin-only path) bypass validation. This is deliberate — admins already have arbitrary DDL/DML access, so validating only the chat-service path is not a new escalation.

The /fix-component auto-repair endpoint has a stable response contract for rejected AI output:

• 422 Unprocessable Entity — the AI’s fix was rejected. Body: { error, reason, rejection: true, violations? }. The reason is a machine-readable tag: "no-op" / "stub" / "lossy" / "empty-render" for validateFixOutput rejections (output that is identical, a placeholder, substantially shortened, or has no JSX) or "forbidden-api" for validatePageSource rejections (with a violations array). The original page source is preserved. The client (useAutoFix) treats 422 as retryable within the 5-attempt budget and appends the human-readable error to the next prompt so the AI does not repeat the mistake.
• 502 Bad Gateway — genuine upstream failure (AI unreachable, invalid JSON from the model, DB error). Not retried by the client.
• 429 Too Many Requests — per-slug rate limit (5/min). Client decrements the attempt counter so the next error cycle retries.

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.
• list_filters — ordered allowlist of column names to render as interactive filter chips on CrudList (FK / choice / boolean / date columns are eligible). Omit / set null to show all eligible. Empty array [] hides all chips. Configurable via the Builder AI’s configure_list_filters tool. Tables only — values on views are ignored.
• default_filter — object that pre-populates the filter UI on first visit. Shape mirrors CrudList’s in-memory filter: scalar (col = value), null (col IS NULL), {"in": [...]} (col IN ...), or {"gte": "...", "lte": "..."} (range). Applied only when the URL has no filter param; once the user touches a chip, the URL filter=... overrides defaults (including an empty filter= sentinel meaning “user cleared all”). Configurable via configure_list_filters.
• summable_columns — array of numeric column names (int / numeric / float / money) to surface SUM and AVG for in the CrudList summary strip. Omit / null to hide the per-column section (count is always shown). Validated against pg_attribute; non-numeric or unknown columns are rejected. Configurable via the Builder AI’s configure_list_summary tool. Tables only.
• default_columns — admin-curated initial column visibility for CrudList. Per-user toggles (kept in localStorage, keyed by <schema_name>:<table_name>) override this once set. Omit / null to fall back to the heuristic in getVisibleColumns. Configurable via configure_list_summary.
• table_singular — admin-curated singular noun for one record (e.g. "Customer" for the customers table, "Order Item" for order_items). Drives CRUD heading copy: New Customer / Edit Customer instead of the plural relation.prettyName fallback. Title-cased; whitespace is trimmed; blank/whitespace-only values are rejected. The AI builder writes this at table creation time alongside display_columns; admins can override or clear it via the configure_table_singular tool (null clears, falling back to prettyName). Tables only — values on views are ignored.
• junction — marks a table as a many-to-many junction. Shape: { "left_table": "students", "right_table": "courses", "left_column": "student_id", "right_column": "course_id" }. Set exclusively by the Builder AI’s propose_create_m2m tool; never set by hand or by propose_create_table. Junction tables have a strict shape (composite PK on the two FK columns, ON DELETE CASCADE on both FKs, no id, no audit columns) and are hidden from user-facing navigation — sidebar, /tables, HomeScreen, generated default home page, parent detail-page child lists. Direct nav to /table/<junction> shows a relationship-table notice instead of a raw CrudList. The Builder admin schema view at /sys/tables keeps the junction out of the upper table list and surfaces it in a separate Relationships section as an m2m via <junction> edge between left and right (FK rows for non-junction tables render in the same section); see getSchemaEdges in apps/web/src/lib/schema.ts. Drop-order constraint: a junction must be dropped (via propose_drop_table) before either parent can be dropped — the FK CASCADE direction goes parent→junction, not the reverse. Self-referential M2M is allowed (left_table == right_table) but left_column must differ from right_column. At most one junction per unordered table pair — a→b and b→a collide and the second call is rejected. Junction edges are edited from the RecordEdit / RecordNew widgets (#668): every parent that’s adjacent to one or more junctions renders an M2M chip section per side. Self-referential junctions render two sections, each headed by the column name (follower_id → “Follower”). Per D6 (docs/design/page-widgets.md), the chip section is a sibling of the column inputs — not a standalone widget — and obeys per-junction gating (hidden when the caller has neither can_insert nor can_delete, or cannot read the other-side table). The optional junctions?: false | Array<{ junctionTable, parentColumn? }> prop lets a custom page suppress all sections (false / []) or whitelist specific ones; auto-detect via getAdjacentJunctions is the default. Save flow: edit mode issues a parent UPDATE and then public.apply_m2m_edits(text) as two separate proxy calls — UPDATE is idempotent so a re-Save converges if the second call fails. Create mode routes through public.create_with_m2m(text, text[], text, text), which wraps the parent INSERT and the M2M apply in a single function (one transaction) so a partial parent INSERT can never escape on junction failure. Both functions are SECURITY INVOKER, validate the target via public.table_metadata.meta.junction, and inject column-type casts from pg_attribute so uuid / integer / text PKs all bind correctly through their JSON-text payloads. The matching frontend pieces live in apps/web/src/lib/junctions.ts (getAdjacentJunctions), apps/web/src/lib/tableMeta.ts (the junctions field on TableMeta carries the descriptor through to the sandbox), apps/web/src/modules/widgets/record-widgets.tsx (the inline M2M chip component + edit/create save paths), and apps/web/src/components/CrudForm.tsx (auto-CRUD shell — collapsed to ~50 lines, CrudHeader + the widgets). On the parent table’s CrudList, every adjacent junction also renders a read-only chip column showing each row’s related labels — first 2-3 chips inline, then a “+N more” Popover when overflow. Each chip is a Link to the related row’s detail page. Synth column keys use the __m2m__<junctionTable>__<parentColumn> namespace and are excluded from sort, group-by, server-side filter chips, and CSV export. Visibility opts in via the column-visibility menu (default-on for new users; existing users with stored prefs opt in via the menu). The chip data is fetched via one batched useQueries call per adjacent junction (apps/web/src/lib/useM2MChips.ts), keyed [junctionTable, "m2m-chips", parentEntity, parentColumn, parentIdsKey] so junction-side invalidateQueries({ queryKey: [junctionTable] }) from the M2M edit path automatically refreshes both sides. The SQL caps each parent’s chip set at 50 via a row_number() window + FILTER (WHERE rn <= 50); a parallel count(*) OVER returns the precise total so the cell can render “+N more” without a follow-up fetch. Chip cells live in apps/web/src/components/M2MChipCell.tsx. Hidden when the other parent table isn’t in schema.relations (e.g. user lacks SELECT) or when either PK is unknown.

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 custom-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 /page/<slug> for the first matching entry. Each value is a custom-page slug; absolute routes like /table/<name> are not accepted (if you want the home to land on a table, create a one-line landing page that navigates there and set its slug here). Example: {"admin": "admin-dashboard", "staff": "staff-home"}. Validation lives in apps/web/src/lib/resolve-home-page.ts.

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

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).

max_lines — Per-column override for the list-view line clamp. Only meaningful on columns with display_type: multiline; ignored elsewhere. Default is 3 lines; valid override values are integers in [1..6]. Out-of-range or non-integer values fall back to the default.

UPDATE column_metadata
SET meta = meta || '{"max_lines": 2}'::jsonb
WHERE table_name = 'tasks' AND column_name = 'description';

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)

Name rules: lowercase [a-z][a-z0-9_]*, no pg_/sys_/information_schema prefix, no _v suffix. The names users and files are reserved for platform tables — FKs to sys.users / sys.files resolve through the public.users / public.files views, so an app-local table of the same name would shadow them under the app schema’s search_path.

Semantic types (preferred — the AI uses these):

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

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

Use decimal for fractional numbers that aren’t money or percentages (hours, weights, temperatures, measurements) — it renders as a plain number without currency styling. Use time for time-of-day without a date (opening hours, appointment slots); use datetime when both date and time matter.

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 enforced at the database level: every choice column carries a {table}_{column}_choice_check CHECK constraint that rejects any value outside the declared option set, so imports, chat-built forms, admin SQL, and any other write path all see the same allowed set. NULL is still permitted on nullable columns.

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 and the CHECK constraint together: add inserts options and installs the CHECK; drop removes both; rename carries the CHECK to the new column name; change_type away from choice drops options and the CHECK; change_type to choice with options inserts options and installs the CHECK, replacing any prior one.

Column-header sort on a choice column honors the declared option order (the sort_order column on sys.field_options, populated from the order in which options were declared). The list query builder used by both the sidebar CrudList and the in-page CrudList widget rewrites ORDER BY <choice_col> to a CASE expression keyed off the option list — so a priority column declared [critical, high, medium, low] sorts in that order, not alphabetically. Rows whose value isn’t in the declared set sort last (NULL bucket via NULLS LAST).

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, display_name)

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

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

The invited user authenticates via the returned magic link token. display_name is required (non-empty, trimmed, max 200 chars). 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_display_name)

Lets any authenticated user update their own display name:

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

• new_display_name: NULL keeps the current display name; empty/whitespace-only strings are rejected; max 200 chars. display_name cannot be cleared (NOT NULL).

Email changes go through sys.request_email_change and sys.confirm_email_change (see Auth System) — the direct email path was removed so a typo can’t lock a user out and a stolen session can’t silently hijack the account.

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. When description is provided (non-empty after trim()), it is written to sys.app_metadata.app_description (the canonical location). Returns the new app row as jsonb with description derived from app_metadata.

sys.update_app(schema_name, display_name, description) — Updates the display name on sys.apps and upserts/deletes sys.app_metadata.app_description: a NULL or whitespace-only description removes the metadata row, otherwise the trimmed value is upserted. Returns the updated app row as jsonb with description reflecting the post-update state.

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.

The public.apps view exposes all sys.apps columns ordered by display name (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).

Column metadata inheritance: a view column whose name matches a base column it depends on (passthrough projection like SELECT status FROM tasks) inherits that base column’s column_metadata entry — display_type, label, choice options, colors. So choice columns render as colored badges through views, the same as on the base table. Caveats: aliased outputs (SELECT status AS state) don’t inherit (correct — different name); transformed expressions that keep the same name (lower(status) AS status) currently inherit too (known false positive); ambiguous joins where two source tables contribute a same-named column skip inheritance; views of views aren’t traversed. Explicit column_metadata rows on the view always win over inherited.

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.

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
request_email_change, confirm_email_change	Yes	Authenticated self-update of sys.users.email via verification link
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)
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