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
Every table gets an id primary key and two timestamps by default. Add whatever else you need.
Save
The table is created immediately. You can add or remove columns later.
Column types
DYPAI supports the full set of 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 |
int / bigint | Counts, small numbers, IDs | 42 |
numeric(10,2) | Money, exact decimals | 199.99 |
float | Approximate decimals (physics, analytics) | 3.14159 |
bool | Yes/no flags | is_active, email_verified |
timestamptz | Dates + times with timezone | 2026-04-16 10:30:00+00 |
date | Just a date | 2026-04-16 |
uuid | Globally unique IDs | 550e8400-e29b-41d4-... |
jsonb | Nested or flexible data | {"color": "red", "size": "M"} |
text[] | Arrays of values | {"admin", "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 adds them automatically when you create a table. If you're making a table via SQL, add them yourself — you'll want them for debugging, sorting, and audit trails later.
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. DYPAI'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.