# Unified Schema
URL: /docs/schema
LLM index: /llms.txt
Description: One source of truth for fields, constraints, defaults, references, and relationships.

# Unified Schema

The schema DSL is the center of `@farming-labs/orm`. Everything else either reads
the schema directly or consumes its normalized manifest form.

## Core building blocks

- `defineSchema(...)` defines the top-level schema object
- `model(...)` defines one model
- `tableName(...)` builds a structured table reference when you need a namespace
- field builders such as `id()`, `string()`, `boolean()`, and `datetime()`
- relation helpers such as `belongsTo(...)`, `hasOne(...)`, `hasMany(...)`, and `manyToMany(...)`
- model-level constraints such as compound `unique` keys and `indexes`

## Full schema example

```ts
import {
  belongsTo,
  boolean,
  datetime,
  defineSchema,
  hasMany,
  hasOne,
  id,
  manyToMany,
  model,
  string,
} from "@farming-labs/orm";

export const appSchema = defineSchema({
  user: model({
    table: "users",
    description: "Primary application users.",
    fields: {
      id: id(),
      name: string(),
      email: string().unique().map("email_address").describe("Canonical login email"),
      emailVerified: boolean().default(false),
      createdAt: datetime().defaultNow(),
    },
    relations: {
      profile: hasOne("profile", { foreignKey: "userId" }),
      sessions: hasMany("session", { foreignKey: "userId" }),
      organizations: manyToMany("organization", {
        through: "membership",
        from: "userId",
        to: "organizationId",
      }),
    },
  }),

  profile: model({
    table: "profiles",
    fields: {
      id: id(),
      userId: string().unique().references("user.id"),
      bio: string().nullable(),
    },
    relations: {
      user: belongsTo("user", { foreignKey: "userId" }),
    },
  }),

  session: model({
    table: "sessions",
    fields: {
      id: id(),
      userId: string().references("user.id"),
      token: string().unique(),
      expiresAt: datetime(),
    },
    relations: {
      user: belongsTo("user", { foreignKey: "userId" }),
    },
  }),

  account: model({
    table: "accounts",
    fields: {
      id: id(),
      userId: string().references("user.id"),
      provider: string(),
      accountId: string(),
    },
    constraints: {
      unique: [["provider", "accountId"]],
      indexes: [["userId", "provider"]],
    },
    relations: {
      user: belongsTo("user", { foreignKey: "userId" }),
    },
  }),

  organization: model({
    table: "organizations",
    fields: {
      id: id(),
      slug: string().unique(),
      name: string(),
    },
  }),

  membership: model({
    table: "memberships",
    fields: {
      id: id(),
      userId: string().references("user.id"),
      organizationId: string().references("organization.id"),
      role: string(),
    },
  }),
});
```

## Namespaced tables

Flat table names still work:

```ts
table: "users";
```

When you need a Postgres schema namespace, use `tableName(...)`:

```ts
import { tableName } from "@farming-labs/orm";

user: model({
  table: tableName("users", { schema: "auth" }),
  fields: {
    id: id(),
    email: string().unique(),
  },
});
```

That keeps the logical model name as `user` while generators and the SQL runtime
emit qualified identifiers like `"auth"."users"`.

Use the structured helper instead of passing `"auth.users"` as a flat string.
Schema-qualified strings are rejected so the ORM can keep table names and
namespaces unambiguous.

Generated numeric IDs are currently first-class on the SQL-family runtimes,
Prisma, and the in-memory driver. MongoDB and Mongoose keep manual numeric IDs
only for now.

## Field builders

### Scalar field types

| Builder                                           | Output type | Notes                                            |
| ------------------------------------------------- | ----------- | ------------------------------------------------ |
| `id()`                                            | `string`    | Unique by default and generated as an id         |
| `id({ type: "integer" })`                         | `number`    | Manual numeric primary key support               |
| `id({ type: "integer", generated: "increment" })` | `number`    | Auto-generated numeric primary key support       |
| `string()`                                        | `string`    | Plain string scalar                              |
| `boolean()`                                       | `boolean`   | Boolean scalar                                   |
| `datetime()`                                      | `Date`      | Represents a JavaScript `Date` in runtime typing |

### Field methods

| Method                       | Purpose                                                 |
| ---------------------------- | ------------------------------------------------------- |
| `.unique()`                  | Marks the field unique                                  |
| `.nullable()`                | Makes the runtime output `T \| null`                    |
| `.default(value)`            | Stores a literal default                                |
| `.defaultNow()`              | Marks the field as generated from `now`                 |
| `.references("model.field")` | Declares a foreign-key style reference                  |
| `.map("column_name")`        | Maps a field name to a different physical column name   |
| `.describe("...")`           | Adds documentation metadata for the manifest/docs layer |

## Model-level constraints

Use model constraints when uniqueness or indexing belongs to a combination of
fields instead of a single field.

```ts
account: model({
  table: "accounts",
  fields: {
    id: id(),
    userId: string().references("user.id"),
    provider: string(),
    accountId: string(),
  },
  constraints: {
    unique: [["provider", "accountId"]],
    indexes: [["userId", "provider"]],
  },
});
```

### Constraint keys

| Key       | Purpose                                 |
| --------- | --------------------------------------- |
| `unique`  | Declares one or more unique field sets  |
| `indexes` | Declares one or more non-unique indexes |

### Why this matters

- generators emit real compound uniques and indexes
- the runtime now understands declared compound unique keys
- `findUnique(...)` and `upsert(...)` can use those same field sets directly

Example:

```ts
await orm.account.findUnique({
  where: {
    provider: "github",
    accountId: "gh_ada",
  },
});
```

```ts
await orm.account.upsert({
  where: {
    provider: "github",
    accountId: "gh_ada",
  },
  create: {
    userId: "user_1",
    provider: "github",
    accountId: "gh_ada",
  },
  update: {
    userId: "user_2",
  },
});
```

## Relation helpers

### `belongsTo`

Use this when the current model holds the foreign key.

```ts
session: model({
  table: "sessions",
  fields: {
    id: id(),
    userId: string().references("user.id"),
  },
  relations: {
    user: belongsTo("user", { foreignKey: "userId" }),
  },
});
```

### `hasOne`

Use this when the target model stores a unique foreign key back to the current model.

```ts
user: model({
  table: "users",
  fields: { id: id() },
  relations: {
    profile: hasOne("profile", { foreignKey: "userId" }),
  },
});
```

### `hasMany`

Use this when the target model stores a non-unique foreign key back to the current model.

```ts
user: model({
  table: "users",
  fields: { id: id() },
  relations: {
    sessions: hasMany("session", { foreignKey: "userId" }),
  },
});
```

### `manyToMany`

Use this when you have a join model and want higher-level relation metadata.

```ts
organizations: manyToMany("organization", {
  through: "membership",
  from: "userId",
  to: "organizationId",
});
```

In plain English:

- `through` is the join model
- `from` is the join-table field pointing back to the current model
- `to` is the join-table field pointing at the target model

## Mapped names and why they matter

This field:

```ts
email: string().unique().map("email_address");
```

means:

- the logical field name is `email`
- the physical column name is `email_address`
- the typed client still uses `email`
- generators output the mapped database name

## What generators use vs. what runtime uses

Today, it is important to separate two layers of value:

- **Generators** primarily use field-level manifest data such as column names, defaults, uniqueness, and references
- **Runtime helpers** can use relation metadata such as `hasMany(...)` and `manyToMany(...)`

That means the current schema relation graph is already useful, but not every
generator consumes every relation helper yet.

## Manifest layer

Internally, the schema is normalized into a manifest that contains:

- model names and table names
- field names and mapped columns
- scalar kinds
- nullability
- uniqueness
- compound unique constraints
- indexes
- generated/default values
- references
- descriptions

That is the handoff point used by the code generators.

## Design guidance

- Keep model names logical and stable
- Use `.map(...)` only when the physical database naming must differ
- Prefer `references(...)` whenever a foreign-key relationship is real
- Use relation helpers to express how the library thinks about the graph, not just how the database stores it
- Keep backend-specific translation logic out of the library package whenever possible