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

```typescript
import { z } from "alepha";
import { $entity, db } from "alepha/orm";
```

The `db` object is an instance of `DatabaseTypeProvider`.

## Primary Key

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

```typescript
db.primaryKey(); // UUID, app-generated time-ordered UUIDv7 - default
db.primaryKey(z.uuid()); // UUID, same app-side UUIDv7 generation
db.primaryKey(z.integer()); // integer with identity (auto-increment)
db.primaryKey(z.bigint()); // bigint with identity
db.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](https://www.rfc-editor.org/rfc/rfc9562) - 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:

```typescript
db.identityPrimaryKey(); // integer with identity
db.bigIdentityPrimaryKey(); // 64-bit integer with identity, read as a JS number
db.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).

```typescript
createdAt: 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](https://latest.alepha.dev/docs/guides-persistence-repository.md#leaving-updatedat-alone)).

```typescript
updatedAt: 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.

```typescript
deletedAt: 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.

```typescript
version: 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.

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

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

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

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

```typescript
status: 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.

| Schema                                   | PostgreSQL                  | SQLite               |
| ---------------------------------------- | --------------------------- | -------------------- |
| `z.text()`, `z.email()`, ...             | `text`                      | `text`               |
| `z.uuid()`                               | `uuid`                      | `text`               |
| `z.integer()`                            | `integer`                   | `integer`            |
| `z.int64()`                              | `bigint` (read as a number) | `integer`            |
| `z.bigint()`                             | `bigint`                    | `text` (non-key)     |
| `z.number()`                             | `double precision`          | `real`               |
| `z.boolean()`                            | `boolean`                   | `integer` (0 / 1)    |
| `z.enum()`                               | ENUM type (see above)       | `text`               |
| `z.datetime()`                           | `timestamptz`               | `integer` (epoch ms) |
| `z.date()`                               | `date`                      | `integer` (epoch ms) |
| `z.binary()` (base64 string)             | `bytea`                     | JSON text            |
| `z.object()`, `z.record()`, `z.any()`    | `jsonb`                     | JSON text            |
| `z.array()` of objects                   | `jsonb`                     | JSON text            |
| `z.array()` of text / numbers / booleans | native array (`text[]`...)  | JSON text            |

The different storage of timestamps is why raw date SQL is not portable: see [Portable SQL](https://latest.alepha.dev/docs/guides-persistence-portable-sql.md).

## Default Values

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

```typescript
isActive: db.default(z.boolean(), true),
score: 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.

```typescript
import { z } from "alepha";
import { $entity, db } from "alepha/orm";

const team = $entity({
  name: "teams",
  schema: z.object({
    id: db.primaryKey(z.uuid()),
    name: z.text(),
  }),
});

const player = $entity({
  name: "players",
  schema: z.object({
    id: db.primaryKey(z.uuid()),
    name: z.text(),
    teamId: db.ref(z.uuid(), () => team.cols.id),
  }),
});
```

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:

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

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

```typescript
import { z } from "alepha";
import { $entity, db } from "alepha/orm";

const user = $entity({
  name: "users",
  schema: z.object({
    id: db.primaryKey(z.uuid()),
    email: z.email(),
    name: z.text(),
    role: z.enum(["admin", "user", "moderator"]),
    isActive: db.default(z.boolean(), true),
    createdAt: db.createdAt(),
    updatedAt: db.updatedAt(),
    deletedAt: db.deletedAt(),
    version: db.version(),
  }),
  indexes: [{ column: "email", unique: true }],
});
```

## Page Schema

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

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

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