# Schema as code

> schema.ts declares every collection, field, index, view and rule; the types come from it and the database follows it at each boot.

`schema.ts` replaces an admin collection editor and a type generator. You write the collections once in TypeScript. The record types come from that declaration, and at each boot the database is brought in line with it.

## A schema

```ts
import { auth, base, defineSchema, json, number, relation, select, text, view, type RecordOf } from 'locosdk/schema'

export const schema = defineSchema({
    users: auth({ fields: { name: text({ max: 255 }) }, viewRule: 'id = @request.auth.id' }),
    clients: base({
        fields: {
            code: text({ required: true }),
            owner: relation('users', { cascadeDelete: true }),
            tags: select(['vip', 'late'], { maxSelect: 2 }),
            meta: json<{ note: string }>(),
        },
        indexes: ["CREATE UNIQUE INDEX idx_clients_code ON clients (code) WHERE code != ''"],
        listRule: 'owner = @request.auth.id',
    }),
    client_tags: view({
        query: 'SELECT id, CAST(json_array_length(tags) AS INTEGER) AS n FROM clients',
        fields: { n: number() },
    }),
})

export type Client = RecordOf<typeof schema, 'clients'>
// { id, code, owner, tags: ('vip' | 'late')[], meta: { note: string } | null, created, updated }
```

## Collections

| builder | what | gets |
|---|---|---|
| `base({ fields, indexes, rules })` | a table | `id`, `created`, `updated` |
| `auth({ fields, indexes, rules, options })` | a table whose records sign in | the above, plus `email`, `emailVisibility`, `verified`, `password`, `tokenKey` and their unique indexes |
| `view({ query, fields })` | a read only collection over any SQLite SELECT | list and view only |

- The rules are `listRule`, `viewRule`, `createRule`, `updateRule`, `deleteRule`, plus `authRule` and `manageRule` on an auth collection. A rule left out is `null`: superusers only. See [Rules and filters](https://golocomotive.fr/docs/developers/rules.md).
- `autodates: ['created']` keeps only the autodates it names, `autodates: []` none. Left out, both.
- Declaring a system field by its name overrides its options.
- `auth({ options })` sets sign in options: password, codes by email, two factors, alerts, token lifetimes, mail templates. See [Auth and mail](https://golocomotive.fr/docs/developers/auth.md).

## Fields

Builders: `text`, `number`, `bool`, `date`, `autodate`, `email`, `url`, `editor`, `select`, `relation`, `json`, `file`, `password`. Options use PocketBase's own names (`required`, `min`, `max`, `pattern`, `onlyInt`, `maxSelect`, `cascadeDelete`, `maxSize`, `protected`, `presentable`, `help`…). The full table: [Field types](https://golocomotive.fr/docs/reference/fields.md).

The inferred types:

| field | TypeScript |
|---|---|
| text, email, url, editor, date, autodate | `string` |
| number | `number` |
| bool | `boolean` |
| select | its values, plus `''` unless `required`; an array when `maxSelect > 1` |
| relation, file | `string`; `string[]` when `maxSelect > 1` |
| `json<T>()` | `T \| null` |

A relation names its target collection by name. An unknown target throws at `defineSchema`.

## Views

A view is a collection over a SELECT: GROUP BY, joins, subqueries, `json_each`, `CAST`, `COALESCE` all work. Its columns get a type at sync:

- the alias `id` is the record id; `count(` is an integer;
- `CAST(... AS integer)`, `real`, `text`, `boolean` give that type;
- a column read straight from a known collection keeps that field's type (a relation stays a relation);
- anything else is json. A `*` wildcard is refused.

A declared `fields` map only types the records. The sync fails when a declared field disagrees with what it infers. Use a view for a grouping the app decides, so the database does the sums: [aggregates](https://golocomotive.fr/docs/developers/client.md#aggregates).

## Sync on boot

`createApp` compares `schema.ts` with the database, collection by name, and keeps the ids the database already holds.

Applied by itself:

- a new collection, a new field, a new index;
- a changed rule or view query;
- changed field options that keep the column type.

Refused, with one error that lists every problem:

- a dropped collection or field;
- a field whose column type changes (another type, or single to multiple);
- a dropped index on a table with rows;
- a rename, which reads as a drop plus an add.

The app does not start until the schema and the database agree. That is on purpose: no boot ever deletes data.

## Migrations

A destructive change goes in a migration. Migrations run before the sync, in file order, each in a transaction, each once (they are recorded in `_migrations`). After them, the diff is additive again.

```ts
import type { Migration } from 'locosdk/server'

export const migrations: Migration[] = [
    {
        file: '20261104_rename_label',
        up(m) {
            m.renameField('orders', 'label', 'title')
        },
    },
]

// server.ts
const app = createApp({ schema, migrations })
```

The migrator offers `dropCollection`, `dropField`, `renameField`, `renameCollection`, and `db` for raw SQL on the rows.

## Storage

The database is PocketBase's layout, byte for byte: one table per collection, one column per field, `_collections` holding the definitions. A `pb_data/data.db` opens as is. Ids are 15 characters `[a-z0-9]`, dates are UTC text `YYYY-MM-DD HH:MM:SS.sssZ`. The pragmas: WAL, foreign keys on, a 10 s busy timeout.