Schema
Columns
A column is a col.* builder, refined by modifiers. The builder decides the
Postgres type; the modifiers say everything else: whether the column may be
null, its default, its validation, who may read and write it.
import { defineTable, col } from "@rebasepro/server/schema";
export const products = defineTable("products", { columns: { id: col.uuid().primaryKey().defaultRandom(), sku: col.varchar({ length: 32 }).notNull().unique(), name: col.text().notNull().validate({ min: 2, max: 120, trim: true }), price: col.numeric({ precision: 10, scale: 2 }).notNull().validate({ min: 0 }), status: col.enum(["draft", "active", "retired"]).notNull().default("draft"), tags: col.text().array().validate({ maxItems: 10 }), photo: col.storage({ path: "products/", accept: ["image/*"], maxBytes: "5MB" }), createdBy: col.uuid().onCreateUser().readOnly(), createdAt: col.timestamp({ withTimezone: true }).notNull().onCreateNow(), updatedAt: col.timestamp({ withTimezone: true }).notNull().onUpdateNow(), }, rls: "admin-only",});Builders are immutable: every modifier returns a new builder, so a column can be declared once and shared between tables.
After the dot, a column reads like Drizzle’s
pg-core: the same builder names, an optional SQL column name first, then
Drizzle’s config object, then the chain — col.varchar({ length: 32 }),
col.timestamp({ withTimezone: true }), col.text().array(),
.default(sql`now()`). A Drizzle spelling that would mean something else here
does not compile, and the error says what to write instead. Two differences to
keep in mind:
- A foreign key is a relation. Declare it under
relations:author: rel.belongsTo(() => users, { localKey: "author_id" }). The relation’s key is whatincludenames..references()does not exist, and says so. See Relations. .defaultNow()is a database default only:DEFAULT now(), which a client may still override..onCreateNow()is a server stamp: the server writes the time on create, whatever the client sent.
Builders
Section titled “Builders”One builder per Postgres type. The wire type is what a client reads in a row and
sends in a write, and what the SDK and InferRow type
the column as. Every builder takes an optional SQL column name as its first
argument (col.text("legacy_title")); without it the SQL name is the
snake_case of the key.
| Builder | Postgres type | Wire type | Notes |
|---|---|---|---|
col.uuid() |
UUID |
string |
.defaultRandom() for gen_random_uuid() |
col.text() |
TEXT |
string |
col.text({ enum: [...] }) accepts only those values; see Closed value sets |
col.varchar({ length }) |
VARCHAR(n) |
string |
the length is required; takes enum too |
col.char({ length }) |
CHAR(n) |
string |
the length is required; takes enum too |
col.enum(values) |
a Postgres enum type, <table>_<column> |
the union of values |
see Closed value sets |
pgEnum(name, values), then status() |
a named Postgres enum type | the union of values |
every column of it shares the one type |
col.smallint() |
SMALLINT |
number |
|
col.integer() |
INTEGER |
number |
|
col.bigint({ mode: "number" }) |
BIGINT |
number |
read and written as a JavaScript number |
col.smallserial() |
SMALLSERIAL |
number |
has a default; prefer col.smallint().generatedByDefaultAsIdentity() in a new schema |
col.serial() |
SERIAL |
number |
has a default; prefer col.integer().generatedByDefaultAsIdentity() |
col.bigserial({ mode: "number" }) |
BIGSERIAL |
number |
has a default; prefer col.bigint({ mode: "number" }).generatedByDefaultAsIdentity() |
col.real() |
REAL |
number |
|
col.doublePrecision() |
DOUBLE PRECISION |
number |
|
col.numeric({ precision?, scale? }) |
NUMERIC, NUMERIC(p), NUMERIC(p, s) |
number |
a scale needs a precision; col.decimal() is the same builder |
col.boolean() |
BOOLEAN |
boolean |
|
col.timestamp({ withTimezone: true }) |
TIMESTAMPTZ |
string (ISO 8601) |
a write also takes a Date; a timestamp without time zone is col.custom({ dataType: "timestamp" }) |
col.date() |
DATE |
string (YYYY-MM-DD) |
a write also takes a Date |
col.time() |
TIME |
string (HH:MM:SS) |
a write also takes a Date |
col.jsonb() / col.jsonb(shape) |
JSONB |
unknown (.$type<T>() types it), or the shape’s type |
see JSON documents |
col.json() / col.json(shape) |
JSON |
as jsonb |
keeps the text it was given; prefer jsonb |
<builder>.array() |
<inner>[] |
an array of the inner wire type | col.text().array(); no arrays of arrays; see Arrays |
col.vector({ dimensions, index? }) |
vector(dimensions) (pgvector) |
number[] |
an HNSW cosine index unless index says otherwise; see Vector search |
col.bytea() |
BYTEA |
string (base64) |
|
col.geopoint() |
JSONB |
{ latitude: number; longitude: number } |
|
col.storage(options) |
TEXT |
string (the file’s path) |
.multiple() makes it TEXT[]; see Storage columns |
col.custom({ dataType }) |
any other Postgres type, verbatim ("inet", "citext") |
string (.$type<T>() types it) |
Rebase names the type in DDL and passes values through |
Modifiers
Section titled “Modifiers”Every builder has the first group. The rest exist only on the builders they make sense for; calling one elsewhere is a type error.
| Modifier | On | Database | API and types |
|---|---|---|---|
.notNull() |
every column | NOT NULL |
never null on a row; required on create unless it has a default |
.primaryKey() |
every column | part of the primary key, NOT NULL |
absent from the Update type |
.unique() |
every column | single-column UNIQUE |
a duplicate is a 409 |
.default(value) |
every column | DEFAULT <value> |
applied by the driver on create too; optional on create |
.default(sql`expr`) |
every column | DEFAULT <expr> |
optional on create; sql is imported beside col |
.dbDefault() |
every column | leaves the database’s existing default alone | optional on create |
.generatedAlwaysAs(sql`expr`) |
every column | GENERATED ALWAYS AS (<expr>) STORED |
never writable, by anyone |
.$type<T>() |
every column | none | types the value for TypeScript only, as in Drizzle |
.array() |
every column | <type>[] |
see Arrays |
.readOnly() |
every column | none | client writes refused; absent from Insert and Update |
.private() |
every column | none | never on the wire, in the SDK or in OpenAPI |
.access({ read?, write? }) |
every column | none | per-role read and write; see Field access |
.describe(text) |
every column | none | the description in OpenAPI and MCP |
.validate(rules) |
every column | none | checked before every write; rules depend on the type, see Validation |
.transform({ beforeSave?, afterRead? }) |
every column | none | field-level hooks; value is T | null unless .notNull() comes first; see Table hooks |
.oneOf(values) |
numeric columns | none | only these values accepted; typed as their union (a text column takes { enum: [...] }) |
.defaultRandom() |
col.uuid() |
DEFAULT gen_random_uuid() |
optional on create |
.generatedByDefaultAsIdentity() |
col.smallint(), col.integer(), col.bigint({ mode: "number" }) |
GENERATED BY DEFAULT AS IDENTITY |
optional on create |
.defaultNow() |
timestamptz, date, time |
DEFAULT now() |
optional on create; a client may still send a value |
.onCreateNow() |
timestamptz, date, time |
DEFAULT now() |
the server stamps the time on create |
.onUpdateNow() |
timestamptz, date, time |
DEFAULT now() and a BEFORE UPDATE trigger |
the server stamps the time on create and on every update |
.onCreateUser() |
text columns, col.uuid() |
none | the server writes the caller’s id on create |
.onUpdateUser() |
text columns, col.uuid() |
none | the server writes the caller’s id on every write |
.userId() |
text columns, col.uuid() |
none | the column holds a user’s id; the panel offers a user picker; see User columns |
.multiple() |
col.storage() |
TEXT[] |
the column holds several file paths |
Null, required and defaults
Section titled “Null, required and defaults”Columns are nullable by default. .notNull() and .primaryKey() make a column
NOT NULL, and the API then requires it on create unless it has a default. A
NOT NULL column is T on a row; any other column is T | null.
A default is any of .default(), .default(sql`…`), .defaultNow(),
.dbDefault(), .defaultRandom(), .generatedByDefaultAsIdentity(), a stamp,
or a serial builder. A column with a
default may be left out of a create:
import { defineTable, col, sql } from "@rebasepro/server/schema";
export const tickets = defineTable("tickets", { columns: { id: col.integer().primaryKey().generatedByDefaultAsIdentity(), code: col.text().notNull().default(sql`gen_short_id()`), priority: col.smallint().notNull().default(3), legacy: col.text().dbDefault(), }, rls: "admin-only",});.default(value)is written as the column’sDEFAULTand also applied by the driver on create, so a row comes out the same whoever inserts it. A temporal column also takes aDate, stored as its ISO string. An enum column’s default must be one of its values..default(sql`expression`)is evaluated by Postgres. The expression is written into the DDL as is, so thesqltag takes no${…}..defaultNow()is.default(sql`now()`)..dbDefault()says the database already holds a default that the schema does not state. A create may omit the column, and a schema push does not drop the default.
.generatedAlwaysAs() cannot have a default or a stamp: Postgres computes the column
on every write. A primary-key column cannot be .private().
Stamps
Section titled “Stamps”A stamp is a value the server writes into a column on its own. It overwrites whatever a client sends for that column.
| Stamp | Writes | When |
|---|---|---|
.onCreateNow() |
the current time | on create |
.onUpdateNow() |
the current time | on create and on every update |
.onCreateUser() |
the caller’s user id | on create |
.onUpdateUser() |
the caller’s user id | on create and on every update |
A column holds one stamp. Calling a second, different one throws when the
module loads; .onUpdateNow() alone already stamps the row when it is created.
Stamp columns stay nullable unless you write .notNull().
.onCreateUser() is how a column that a row-level security rule compares with
the caller (ownerField) gets its value. A column the caller fills is a column
the caller can lie about.
User columns
Section titled “User columns”.userId() says a column holds the id of a user. It is a meaning, not a
type: the column stays text (or varchar, char, uuid), and the
descriptor the server serves carries semantic: "user".
import { defineTable, col } from "@rebasepro/server/schema";
export const tickets = defineTable("tickets", { columns: { id: col.uuid().primaryKey().defaultRandom(), subject: col.text().notNull(), assignedTo: col.text().userId().describe("Team member assigned to this ticket"), watchers: col.text().userId().array() }, rls: "admin-only"});Two things follow from it:
- The admin panel renders a user picker for the column instead of a text input, and shows the user rather than the raw id.
- A caller who may write the table may look users up to fill it. The picker’s read answers them even when row-level security hides other users’ rows from them.
It works on col.text(), col.varchar(), col.char() and col.uuid(),
on the items of an array (col.text().userId().array()), and on
json.string() inside a JSON document. Anywhere else it
throws when the module loads. It declares no foreign key: write
rel.belongsTo(() => users) when the database should enforce one. To have the
server fill the column with the caller’s id, use a stamp instead.
Validation
Section titled “Validation”.validate(rules) takes the rules for the column’s type. The API checks them
before a create or an update and refuses the write with a 400 naming the
column. Calling .validate() twice merges the rules.
| Rule | Text columns and col.storage() |
Numeric columns | timestamptz, date, time |
Arrays |
|---|---|---|---|---|
min, max |
length | inclusive bounds | earliest and latest value (ISO string or Date) |
|
length |
exact length | |||
matches, matchesFlags, matchesMessage |
a pattern (string or RegExp) and the error |
|||
email, url |
the value is an address or a URL | |||
trim, lowercase, uppercase |
the value is normalized before it is stored | |||
lessThan, moreThan |
exclusive bounds | |||
positive, negative, integer |
sign, whole number | |||
minItems, maxItems, uniqueItems |
item count, no duplicates | |||
requiredMessage |
✓ | ✓ | ✓ | ✓ |
requiredMessage is the error a create gets when it leaves out a .notNull()
column without a default. Every other column type (uuid, enum, boolean,
JSON, vector, bytea, geopoint) accepts requiredMessage alone.
A RegExp is stored as its source and flags, and a Date bound as its ISO
string, so the rules served on the wire are the rules the server checks.
import { defineTable, col } from "@rebasepro/server/schema";
export const signups = defineTable("signups", { columns: { id: col.uuid().primaryKey().defaultRandom(), email: col.text().notNull().validate({ email: true, lowercase: true, trim: true, requiredMessage: "Enter your e-mail address." }), handle: col.varchar({ length: 32 }).validate({ matches: /^[a-z0-9_]+$/, matchesMessage: "Lower-case letters, digits and _ only." }), age: col.smallint().validate({ integer: true, min: 13 }), birthday: col.date().validate({ max: "2013-01-01" }), }, rls: "admin-only",});On col.varchar({ length }) and col.char({ length }) the length is enforced
by Postgres and by the API. A max that differs from it contradicts it; state
the length once.
Closed value sets
Section titled “Closed value sets”Two ways to accept only certain values:
- A Postgres enum type, so the database enforces the set too.
col.enum(values)creates one named<table>_<column>. A type with a name of your choosing, shared by several columns, is declared once withpgEnum(name, values)— as in Drizzle — and called in each column:postStatus(). Two declarations of the same name must list the same values in the same order. - A set the API checks, with no Postgres type:
{ enum: [...] }oncol.text(),col.varchar()orcol.char(), and.oneOf(values)on a numeric column. It is also the form for an array of known strings:col.text({ enum: ["a", "b"] }).array().
All of them type the column as the union of their values: status below is
"draft" | "published" on the row, kind is "news" | "guide", and rating
is 1 | 2 | 3 | 4 | 5.
import { defineTable, col, pgEnum } from "@rebasepro/server/schema";
const reviewStatus = pgEnum("review_status", ["draft", "published"]);
export const reviews = defineTable("reviews", { columns: { id: col.uuid().primaryKey().defaultRandom(), status: reviewStatus().notNull().default("draft"), kind: col.text({ enum: ["news", "guide"] }), rating: col.smallint().notNull().oneOf([1, 2, 3, 4, 5]), labels: col.text({ enum: ["bug", "praise", "question"] }).array(), }, rls: "admin-only",});Labels and colors for the values are presentation. They belong to the CMS, not to the table.
Arrays
Section titled “Arrays”.array() makes a Postgres array of the column’s type, as in Drizzle:
col.text().array() is TEXT[], col.integer().array() is INTEGER[]. What
is chained before .array() describes each item: its { enum }, .validate(),
.oneOf(), storage options and .transform(). What comes after it describes
the array column: its own .validate() takes minItems, maxItems and
uniqueItems.
Modifiers that describe a column (.notNull(), a default, .unique(),
.access()) go after .array() — col.text().array().notNull().default([]);
before it they do not compile. An array of arrays is refused; use
col.jsonb() for nested lists. An array holds scalars — text, uuid, numbers,
booleans, dates, enums: an .array() of jsonb, json, vector, bytea,
geopoints or col.custom() is refused, and a list of documents, points or
vectors is col.jsonb(json.array(…)). A custom list type is named as itself:
col.custom({ dataType: "inet[]" }).
Each item is served as its type is served on a scalar column: a
timestamptz[] item as an RFC 3339 string, a numeric[] item as a number, and
a null item as null.
Keys, identity and serials
Section titled “Keys, identity and serials”.generatedByDefaultAsIdentity() makes an integer column GENERATED BY DEFAULT AS IDENTITY. It is the form for an integer key in a new schema
(.generatedAlwaysAsIdentity() does not compile: Rebase creates identities by
default only). The serial builders (col.smallserial(), col.serial(),
col.bigserial({ mode: "number" })) exist for schemas that
already use them; a column pointing at a serial key is the plain integer of the
same width. See Primary keys for composite keys.
col.bigint({ mode: "number" }) is read and written as a JavaScript number
(Drizzle’s mode: "bigint" does not compile), which is exact up to
2⁵³ − 1. col.numeric() is a number too, so it carries the precision of a
double on the wire, whatever precision Postgres stores.
Who may read and write a column
Section titled “Who may read and write a column”Row-level security decides which rows a caller reaches. Three modifiers narrow what they see of a row and may set:
.readOnly()refuses every client write to the column, on create and on update, whatever the caller’s role. The database (a default, a trigger) and server code still fill it. The column is absent from theInsertandUpdatetypes..private()keeps the column on the server: absent from every row on the wire, from the SDK, from OpenAPI, and refused in filters and sorts. Hooks and server code still see it. A private column cannot also declare.access()..access({ read, write })lists the roles that receive the column and the roles that may write it. An omitted list leaves that direction to the row; an empty list closes it to every caller.
Field access covers what a caller sees when a column is withheld, and every door the rule applies to.
Storage columns
Section titled “Storage columns”col.storage(options) holds the path of a file in a storage bucket. The upload
route enforces accept and maxBytes, and the orphan sweep counts the file as
referenced while a row holds its path. .multiple() makes the column hold
several paths (TEXT[]).
import { defineTable, col } from "@rebasepro/server/schema";
export const listings = defineTable("listings", { columns: { id: col.uuid().primaryKey().defaultRandom(), cover: col.storage({ path: "listings/{id}/", accept: ["image/*"], maxBytes: "10MB", imageResize: { maxWidth: 1600, format: "webp", quality: 80 } }), photos: col.storage({ path: "listings/{id}/gallery/", accept: ["image/*"] }).multiple(), terms: col.storage({ path: "listings/terms/", accept: ["application/pdf"], public: true }), }, rls: "admin-only",});| Option | What it does |
|---|---|
path |
Required. Where files go, as a path template: "posts/heroes/", "users/{id}/". |
source |
The bucket, declared in backend/resources.ts. The default bucket when omitted. |
accept |
MIME types accepted, with wildcards: ["image/*", "application/pdf"]. |
maxBytes |
The largest file accepted: a number of bytes, or a size such as "10MB", "512 kb", "1.5GB". Binary multiples: 1 MB is 1,048,576 bytes. An unreadable size throws. |
imageResize |
Resize images before storing them: maxWidth, maxHeight, mode ("contain" or "cover"), format ("original", "jpeg", "png", "webp"), quality. |
public |
Files are readable without a signed URL. |
fileName |
The stored file name, as a template. The uploaded name when omitted. |
metadata |
Metadata stored with every file. |
includeBucketUrl |
Store the bucket URL in front of the path. |
storeUrl |
Store the download URL instead of the path. |
A storage column takes the text column’s validation rules. A file inside a JSON
document is json.storage(), with the same options; see
JSON documents. Who may upload and download which paths is
storage authorization; serving and
caching the files is Storage.
What is refused
Section titled “What is refused”When the module loads, a builder throws on:
col.varchar()orcol.char()without a length; alengthordimensionsthat is not a positive whole number, andcol.numeric({ scale })with no precision;col.enum([]),pgEnum(name, { … }), and.array()of an array;- a
sqldefault or generated expression with an interpolation; - a second, different stamp on one column, and
.oneOf()with a value that is not a string or a number; .access()whosereadorwriteis not a list of non-empty role names;col.storage()without apath, or with amaxBytesthat is not a size.
When the schema is compiled, the table is refused for:
- a default together with
.generatedAlwaysAs(), or a stamp together with it; .generatedByDefaultAsIdentity()on a non-integer column,.defaultRandom()on a non-uuidcolumn, a time stamp on a non-temporal column, a user stamp on a column that is neither text noruuid;- a
.private()primary key, or atimestamptzortimeprimary key; - a primary key on a
jsonb,json, geopoint,vector,bytea, array or custom list column: a key is a scalar (text,varchar,char,uuid, an enum, a number, a boolean or a date).schema introspectskips a table keyed this way and says why; - an
.array()ofjsonb,json,vector,bytea, geopoints orcol.custom()(see Arrays); - a
.validate()rule the column kind, its items or a JSON node does not check (integeron a text column,trimon a number,minItemson a scalar); - a column that is both
.private()and.access(); - a
.transform()on a column and another on its JSON shape; - a
col.custom({ dataType })whose type name is not the shape of a Postgres type name; .oneOf([]), and.oneOf()on a column that is neither text nor numeric;- an enum with a blank or repeated value, or a default that is not one of its values;
- two keys that resolve to the same SQL column, including once Postgres truncates the names to 63 bytes;
- a named enum type given two different lists of values.
Each refusal names the table, the column and what to write instead.
Drizzle spellings that would mean something else here do not compile, and the
compiler prints what to write: col.timestamp() without withTimezone: true,
.references(), .$defaultFn(), .$onUpdate(), .generatedAlwaysAsIdentity(),
.unique("name"), numeric’s mode: "string", and col.inet(), col.interval()
and the other types Rebase names only through col.custom({ dataType }).
Next steps
Section titled “Next steps”- JSON documents: the shape of a
jsonbcolumn - Relations: foreign keys come from
rel.*, not from columns - Field access:
.access(),.private()and.readOnly()in detail - Type inference: what each modifier does to
InferRowandInferInsert