#Special Columns

The db object from alepha/orm provides helper methods for database-specific column types. These extend the base t type system 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()            // integer with identity (auto-increment) - default2db.primaryKey(z.uuid())    // UUID with auto-generated default3db.primaryKey(z.integer()) // integer with identity4db.primaryKey(z.bigint())  // bigint with identity

Calling db.primaryKey() with no argument creates an integer (identity) column. This is the default primary key type.

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 automatically incremented when the save() method is used on the repository.

1version: db.version(),

When save() is called, it includes the current version in the WHERE clause. If the version in the database has changed since the entity was fetched, a DbVersionMismatchError is thrown. This prevents lost updates in concurrent scenarios.

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

#Organization (Multi-Tenancy)

db.organization() marks the column that scopes a row to a tenant. The repository then filters every read by the resolved tenant and stamps it on every write — you never write the predicate yourself.

1const invoice = $entity({2  name: "invoices",3  schema: z.object({4    id: db.primaryKey(),5    organizationId: db.organization(),6    total: z.integer(),7  }),8});

The tenant is resolved from currentTenantAtom first, then from the authenticated user's organization. An app-level middleware typically writes the atom from the request Host.

#Declare whether the app is multi-tenant

Scoping only protects you if an unresolved tenant is an error rather than a wildcard. That is an application-wide decision, so it lives in an atom rather than on each entity:

1import { tenancyAtom } from "alepha/security";2 3// main.server.ts4alepha.set(tenancyAtom, { mode: "multi" });

Set it once, at the composition root. Without it, a $job or an admin script that forgets to resolve a tenant reads and writes across all of them — including on the framework's own tables (users, files, audits, parameters, API keys, payments).

#Overriding per entity

strict overrides the mode in both directions, for the rare entity that is genuinely special:

1// Always fail closed, even in a single-tenant app.2organizationId: db.organization({ strict: true }),3 4// Never fail closed, even in "multi" — a shared reference table.5organizationId: db.organization({ strict: false }),

Leave it out unless you mean it: an entity that says nothing follows the application, which is where the decision belongs.

::: warning strict and nullable are different questions nullable is a schema fact — it is written into your migration. mode is a runtime policy and never changes generated SQL. An entity that fails closed because the app is in multi mode still has a nullable column; only an explicit strict: true implies NOT NULL, because such a table has no "global row" concept. :::

#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: [18    { column: "email", unique: true },19  ],20});

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