#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 identity

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.

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(); // bigint with identity3db.uuidPrimaryKey(); // UUID

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.

1createdAt: db.createdAt(),

#updatedAt

db.updatedAt() creates a datetime column that is automatically set to the current timestamp on every update.

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" }),

#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()), the default is "set null".
• If the column is required, the default is "cascade".

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".

#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.