Tables & Schema
A table holds one kind of thing — products, orders, messages, invoices. Each column is a piece of information about that thing (a price, a date, a user ID). Getting the schema right upfront saves pain later.
Creating a table
Open the Database tab
In your project dashboard, click Database in the sidebar.
Click New Table
Give the table a lowercase, plural name — products, orders, messages. Stick to snake_case.
Add columns
The editor seeds an id uuid primary key. Add whatever else you need — including created_at / updated_at timestamps, which you add yourself (see Timestamps below).
Save
The table is created immediately. You can add or remove columns later.
Or just ask your AI
Your AI client can create and alter tables for you over MCP — it runs schema changes as migrations (apply_migration) so they're applied safely. Describe the table in plain language and let it write the SQL.
Column types
The visual editor's type picker covers the most common PostgreSQL types. The ones you'll reach for most:
| Type | Use for | Example |
|---|---|---|
text | Names, descriptions, any string | "Blue cotton t-shirt" |
varchar(n) | Strings with a hard max length | varchar(50) for a username |
integer / bigint / smallint | Counts, small numbers, IDs | 42 |
numeric(10,2) | Money, exact decimals | 199.99 |
double precision / real | Approximate decimals (physics, analytics) | 3.14159 |
boolean | Yes/no flags | is_active, email_verified |
timestamptz / timestamp | Dates + times (with / without timezone) | 2026-04-16 10:30:00+00 |
date / time | Just a date / just a time | 2026-04-16 |
uuid | Globally unique IDs | 550e8400-e29b-41d4-... |
jsonb / json | Nested or flexible data | {"color": "red", "size": "M"} |
bytea | Raw binary | (small blobs) |
Arrays are SQL-only
Array types like text[] are valid PostgreSQL and work great, but they're not in the visual type picker — add them through the SQL editor (or ask your AI). Everything in the table above is available in the visual editor.
When in doubt, prefer text over varchar
varchar(n) feels safer but text is identical in performance and doesn't break when you need that extra character. Use varchar only when the max length is a real business rule.
Primary keys
Use uuid with a default. It's random, collision-safe, and doesn't leak row count information:
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
Use bigserial (auto-incrementing integer) only when you genuinely need ordering or shorter URLs. For most apps, UUID is the right answer.
Timestamps
Every table should have these two columns:
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
The visual editor seeds the id primary key but not the timestamps — add created_at / updated_at yourself (in the column picker or via SQL). You'll want them for debugging, sorting, and audit trails later, so it's worth adding them on every table.
To keep updated_at fresh, either update it in your workflow (SET updated_at = now()) or add a trigger. Most apps just update it in the workflow — simpler.
⚠️ User references: use TEXT, not UUID
This is the #1 mistake people make. HeroMate's auth system uses a 32-character ID (not a UUID), so any column pointing to a user must be TEXT.
Correct
CREATE TABLE public.tasks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id TEXT NOT NULL REFERENCES system.users(id) ON DELETE CASCADE,
title TEXT NOT NULL,
done BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
Wrong (will crash at runtime)
-- ❌ Don't do this
user_id UUID REFERENCES system.users(id)
When an endpoint tries WHERE user_id = ${current_user_id}, Postgres will fail with:
invalid input syntax for type uuid: "G1LIBXsbMLxUrs99ebCaL9X4auxW26AC"
Rule of thumb: any column that links a row to a user = TEXT. Other foreign keys (product_id → products.id, order_id → orders.id) keep their natural UUID.
In workflow placeholders
Write WHERE user_id = ${current_user_id} — no quotes, no ::uuid cast. The engine binds the parameter with the correct type automatically.
Indexes
Add an index to any column you filter or sort by often. The visual editor has a separate Indexes panel per table:
Common patterns:
- Index every foreign key (
user_id,product_id, etc.) — most queries filter by them - Index
created_at DESCif you list recent items - Skip indexes on small tables (fewer than ~1k rows) — Postgres is fast enough without them
Indexes cost on writes
Every index makes inserts and updates slightly slower. Add them when queries are slow, not pre-emptively.
Example: a well-modeled tasks table
Here's how a typical user-owned table looks with all the conventions applied:
CREATE TABLE public.tasks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id TEXT NOT NULL REFERENCES system.users(id) ON DELETE CASCADE,
title TEXT NOT NULL,
description TEXT,
done BOOLEAN NOT NULL DEFAULT false,
due_date DATE,
priority INT NOT NULL DEFAULT 0,
tags TEXT[] NOT NULL DEFAULT '{}',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_tasks_user_id ON public.tasks(user_id);
CREATE INDEX idx_tasks_created_at ON public.tasks(created_at DESC);
Copy this as a starting point when you build anything that belongs to a user.