Database Conventions

Alchemify targets users who aren’t database experts. These conventions provide sensible defaults so the AI can create tables without asking low-level design questions. They cover 90% of business apps; the remaining 10% can override via manual DDL.

Primary keys

Serial integers for business tables. Readable, compact, and simple. System tables (sys.users, etc.) use UUIDs where distributed identity or token usage requires it.

Foreign keys

Block deletion by default (NO ACTION). A customer with orders can’t be deleted — that’s almost always the right behavior.

Cascade only for true parent-child ownership — deleting an order deletes its line items. The test: “does this child make sense without its parent?” If no, cascade.

Indexes are created automatically on all FK columns (Postgres doesn’t do this by default, and missing indexes cause slow joins and deletes). This is enforced by sys.create_table() — every column with references gets an auto-index.

Timestamps and audit columns

Every business table gets:

Column	Type	Purpose
created_at	timestamptz NOT NULL DEFAULT now()	Row creation time
updated_at	timestamptz NOT NULL DEFAULT now()	Last modification time (set by trigger)
updated_by	uuid	User who last modified (set by trigger from app.user_id)

These are hidden from the _v view and from CRUD forms and detail views — the frontend reads clean data while the audit trail exists in the base table.

Nullability

Nullable by default. Columns are nullable unless the AI or user specifies required: true. Making everything NOT NULL by default causes insert errors when users provide partial data. The AI decides per-column based on context (names are typically required, notes are not).

Semantic types

The AI uses semantic types instead of raw Postgres types. This makes column types meaningful to non-technical users (e.g., email instead of text, currency instead of numeric(12,2)).

Category	Types
Text	text, multiline (long text), email, url, phone, color
Number	integer, currency (money), percent, rating (1-5 stars)
Date/Time	date, datetime
Other	boolean, choice, uuid, file, image, jsonb

The backend maps these to Postgres storage types and records the semantic type in column_metadata as display_type. Raw Postgres types still work — the backend infers a display_type from the storage type.

Text types

Always text for string columns. Postgres treats text and varchar(n) identically in terms of performance. Length validation belongs in the application layer. varchar(n) is available for users who want it, but the AI defaults to text. For richer semantics, use email, url, phone, multiline, or color — all map to text storage.

Numeric types

currency for money-like columns (maps to numeric(12,2)). percent for percentage values (maps to numeric(5,2)). rating for star ratings (maps to smallint with an automatic CHECK constraint enforcing 1-5). For general numeric data, plain integer or raw numeric (arbitrary precision) is fine. Never use real or double precision for money — floating-point rounding errors are unacceptable for financial data.

Naming

• snake_case for all identifiers (tables, columns, functions)
• Plural for table names (customers, not customer)
• _v suffix for read views (customers_v)
• _id suffix for foreign key columns (customer_id)

Row Level Security

Always enabled. Every business table gets:

• admin_all policy — full access for admin role
• staff_all policy — full access for staff role

These are the baseline. More restrictive policies (e.g., staff sees only their own rows) can be added later without changing the default.

Default values

• Booleans default to false
• Dates and timestamps have no default (force explicit input, except audit columns)
• Text has no default
• The AI adds contextual defaults when appropriate (e.g., status TEXT DEFAULT 'pending')

Soft delete

Hard delete. No deleted_at column pattern by default. Soft delete adds complexity to every query (WHERE deleted_at IS NULL), view definitions, and RLS policies. For the rare case where audit trail matters, that’s a future feature — don’t build it into every table.

File uploads

Use type file for general file upload columns and image for image columns. Both virtual types expand to a UUID column with a foreign key to sys.files(id) and ON DELETE SET NULL. The difference is semantic: file renders as a download link, image renders as an <img> tag. The get_schema() function reports these as data_type: "file" or "image" so the frontend renders the correct widget. Prefer "type": "file" or "type": "image" over the longer "type": "uuid", "references": "files" form.

Check constraints

Not automatic. Check constraints are domain-specific (quantity > 0 makes sense for order items, not for a temperature column). The AI adds them when contextually appropriate via the check field in sys.create_table(). Use $COL as a placeholder for the column name: "$COL > 0".

Summary

These conventions are enforced by sys.create_table() where possible (audit columns, RLS, naming validation, FK auto-indexing, defaults, check constraints) and followed by the AI for decisions that require context (FK on_delete behavior, type choices, which columns need constraints). The goal is to eliminate design decisions that users shouldn’t have to think about while keeping the escape hatch of manual DDL for custom needs.