#Special Columns

The db object from alepha/orm provides helper methods for database-specific column types. These extend the base Zod schemas (z) with attributes that control how columns behave at the database level.

1import { z } from "alepha";2import { $entity, db } from "alepha/orm";

The db object is an instance of DatabaseTypeProvider.

#Primary Key

db.primaryKey() creates an auto-generated primary key column.

1db.primaryKey(); // UUID, app-generated time-ordered UUIDv7 - default2db.primaryKey(z.uuid()); // UUID, same app-side UUIDv7 generation3db.primaryKey(z.integer()); // integer with identity (auto-increment)4db.primaryKey(z.bigint()); // bigint with identity5db.primaryKey(z.text()); // natural key (a slug, an external id): no default

Calling db.primaryKey() with no argument creates a UUID column. Ids are generated in the application as UUIDv7 - time-ordered, so ORDER BY id matches insertion order and index locality stays as good as an integer key - and work identically on PostgreSQL, SQLite, and Cloudflare D1, on any database version. Unlike integer keys, they never leak row counts, can be generated before the row is inserted, and merge safely across databases.

A text primary key other than z.uuid() has no generated value: the caller supplies it on every insert, and omitting it fails validation.

Note that a UUIDv7 embeds its creation timestamp: anyone holding an id can read when the row was created. If that matters - or when humans need to read the ids - use an integer identity key instead, with a $sequence for display numbers.

There are also explicit shortcut methods:

1db.identityPrimaryKey(); // integer with identity2db.bigIdentityPrimaryKey(); // 64-bit integer with identity, read as a JS number3db.uuidPrimaryKey(); // UUID

The identity forms take optional PostgreSQL identity options (e.g. { mode: "byDefault" }) as their last argument.

Every entity must have exactly one primary key. Multiple primary keys are not supported.

#Timestamps

#createdAt

db.createdAt() creates a datetime column that is automatically set to the current timestamp when a row is inserted (a database default, so a raw INSERT gets it too).

1createdAt: db.createdAt(),

#updatedAt

db.updatedAt() creates a datetime column that is set to the current timestamp on insert, and stamped again by every update the repository issues (updateOne, updateById, updateMany, save, the conflict branch of an upsert). It is the repository that writes it, not a database trigger: a raw query() UPDATE leaves it alone. To skip the stamp for one write, pass { touch: false } (see Leaving updatedAt alone).

1updatedAt: db.updatedAt(),

#deletedAt

db.deletedAt() creates an optional datetime column for soft delete functionality. When present in an entity schema, all delete operations set this column to the current timestamp instead of removing the row. All query operations automatically filter out rows where deletedAt is not NULL.

1deletedAt: db.deletedAt(),

The column is nullable: NULL means the row is active, a timestamp means it has been soft-deleted.

Use { force: true } in repository operations to bypass soft delete behavior.

#Version (Optimistic Locking)

db.version() creates an integer column for optimistic concurrency control. It defaults to 0 and is incremented by every UPDATE the repository issues on the row: save(), updateOne/updateById, updateMany, the conflict branch of upsert/upsertMany, and a soft delete.

1version: db.version(),

When save() is called, it includes the version the entity was loaded with in the WHERE clause. If any writer changed the row since, a DbVersionMismatchError is thrown, which answers 409 Conflict over HTTP. This prevents lost updates in concurrent scenarios, and it needs no transaction: it is one conditional UPDATE, so it holds on Cloudflare D1 too.

On a mismatch the entity object is left exactly as it was loaded, so retrying save() with it fails again instead of overwriting the other write. Read the row again and reapply the change.

A raw repo.query() UPDATE does not touch the version (nor updatedAt): write version = version + 1 yourself when you update a versioned table by hand.

#Enum

z.enum() creates a native PostgreSQL ENUM type column by default.

1role: z.enum(["admin", "user", "moderator"]),

You can share an enum type across multiple tables by specifying a custom name:

1status: z.enum(["pending", "active", "archived"]).meta({ name: "status_enum" }),

To store as a TEXT column instead of a real PostgreSQL ENUM, use mode: "text":

1status: z.enum(["pending", "active", "archived"]).meta({ mode: "text" }),

Without a name, the PostgreSQL type is named <table>_<column>_enum. SQLite has no enum type: there the column is always TEXT, and the schema validates the value.

#Column Types

Plain Zod schemas map to columns without a helper. A camelCase property becomes a snake_case column (createdAt is stored as created_at); the repository and its where clauses keep using the property name.

The different storage of timestamps is why raw date SQL is not portable: see Portable SQL.

#Default Values

db.default() wraps a schema with a default value at the database level.

1isActive: db.default(z.boolean(), true),2score: db.default(z.integer(), 0),

When the column is omitted during insert, the database uses the default value.

#Foreign Key Reference

db.ref() creates a foreign key reference to another entity's column.

 1import { z } from "alepha"; 2import { $entity, db } from "alepha/orm"; 3  4const team = $entity({ 5  name: "teams", 6  schema: z.object({ 7    id: db.primaryKey(z.uuid()), 8    name: z.text(), 9  }),10});11 12const player = $entity({13  name: "players",14  schema: z.object({15    id: db.primaryKey(z.uuid()),16    name: z.text(),17    teamId: db.ref(z.uuid(), () => team.cols.id),18  }),19});

The second argument is a lazy function returning the target entity column. This handles circular references.

#onDelete / onUpdate Actions

By default, db.ref() infers the onDelete action from the column type:

• If the column is optional (.optional(), inside or outside db.ref()), the default is "set null".
• If the column is required, the default is "cascade".

Passing an actions object replaces that inference entirely: { onUpdate: "cascade" } alone leaves onDelete unset. You can override this behavior with explicit actions:

1teamId: db.ref(z.uuid().optional(), () => team.cols.id, {2  onDelete: "set null",3  onUpdate: "cascade",4}),

Available actions: "cascade", "restrict", "no action", "set null", "set default".

db.ref(z.uuid().optional(), ...) and db.ref(z.uuid(), ...).optional() are the same column: the db.* helpers are read through .optional(), .nullable(), .default() and .readonly(). A helper under any other wrapper (.transform(), a pipe) would be lost, so the entity is refused at registration with an error naming the column.

The helpers return a copy: db.ref(userIdSchema, ...) leaves userIdSchema a plain uuid everywhere else it is used.

#Full Example

 1import { z } from "alepha"; 2import { $entity, db } from "alepha/orm"; 3  4const user = $entity({ 5  name: "users", 6  schema: z.object({ 7    id: db.primaryKey(z.uuid()), 8    email: z.email(), 9    name: z.text(),10    role: z.enum(["admin", "user", "moderator"]),11    isActive: db.default(z.boolean(), true),12    createdAt: db.createdAt(),13    updatedAt: db.updatedAt(),14    deletedAt: db.deletedAt(),15    version: db.version(),16  }),17  indexes: [{ column: "email", unique: true }],18});

#Page Schema

db.page() creates a page schema for use with paginated API responses. It wraps an entity schema with pagination metadata.

1const userPage = db.page(user.schema);2// Produces: { content: User[], page: { size, totalElements, totalPages, ... } }

This is used internally by Repository.paginate() and can be used in action response schemas.