# How Logto's Database Schema and Alteration System Works: A Deep Dive

> Explore Logto's database schema and alteration system. Learn how code generation creates TypeScript types and Zod validators, with timestamped scripts managing migrations for robust data management.

- Repository: [Logto/logto](https://github.com/logto-io/logto)
- Tags: deep-dive
- Published: 2026-07-05

---

**Logto uses a code generation pipeline in `@logto/schemas` to turn JSON table definitions into TypeScript types and Zod validators, while schema migrations are handled by timestamped alteration scripts that execute via a custom CLI tracking state in the `alteration_state` table.**

Logto is an open-source authentication and identity management platform built on PostgreSQL. Its database architecture separates schema definitions from migration logic, using TypeScript code generation to keep runtime types synchronized with the underlying SQL structure.

## Schema Definition and Code Generation

Logto centralizes its database definitions in the **`@logto/schemas`** package. Rather than maintaining separate TypeScript interfaces and SQL DDL, each table is defined once in a JSON-like structure and processed by the `generateSchema` function located in [`packages/schemas/src/gen/schema.ts`](https://github.com/logto-io/logto/blob/main/packages/schemas/src/gen/schema.ts).

For a table named `users`, the generator produces four key artifacts:

- **`CreateUser`** – The insertion type with optional `tenant_id`, nullable fields, and database defaults.
- **`User`** – The type representing a row fetched from the database.
- **`UserKeys`** – A string union of all column names.
- **`user`** – A frozen object containing the table name, column mappings, and **Zod guards** (`createGuard`, `guard`, `updateGuard`) for runtime validation.

These generated schemas are compiled into the library during `pnpm build` and imported by `@logto/core` to validate payloads before database insertion. This guarantees that TypeScript compile-time checks and runtime validation remain in sync with the actual PostgreSQL schema.

## Database Alterations (Migrations)

Schema evolution is handled through **alteration scripts** stored in `packages/schemas/alterations/`. Each file follows a strict naming convention:

```

<package-version>-<unix-timestamp>-<short-description>.ts

```

For example, [`1.9.0-1693554904-add-possword-policy.ts`](https://github.com/logto-io/logto/blob/main/1.9.0-1693554904-add-possword-policy.ts) adds a password policy column to the sign-in experiences table.

Every alteration script exports an `AlterationScript` object defined in [`packages/schemas/lib/types/alteration.ts`](https://github.com/logto-io/logto/blob/main/packages/schemas/lib/types/alteration.ts). This type requires two async functions:

- **`up`** – Executes SQL statements to advance the schema using the Slonik query builder (`sql\`…\``).
- **`down`** – Reverts the change for rollback scenarios.

Both functions receive a `DatabaseTransactionConnection` parameter provided by Slonik, ensuring migrations run within atomic transactions.

## The Alteration Execution Flow

When Logto starts, it queries the **`alteration_state`** table—defined in [`packages/schemas/src/types/system.ts`](https://github.com/logto-io/logto/blob/main/packages/schemas/src/types/system.ts) and guarded by `alterationStateGuard`—to determine which scripts have already been applied. Pending alterations are executed **in chronological order** based on their Unix timestamp.

The migration CLI is triggered via the script defined in [`package.json`](https://github.com/logto-io/logto/blob/main/package.json):

```json
"scripts": {
  "alteration": "logto db alt"
}

```

Running `pnpm alteration` performs the following steps:

1. Compiles TypeScript alteration files via `pnpm build:alterations`.
2. Establishes a connection to the PostgreSQL instance.
3. Queries `alteration_state` for the last applied timestamp.
4. Executes the `up` function for each script with a newer timestamp.
5. Records successful executions back into `alteration_state`.

This immutable naming convention prevents modification of already-deployed scripts, ensuring idempotent and reproducible database states across environments.

## Practical Examples

### Creating an Alteration Script

To add a `password_policy` column to the `sign_in_experiences` table, create a file at [`packages/schemas/alterations/1.9.0-1693554904-add-possword-policy.ts`](https://github.com/logto-io/logto/blob/main/packages/schemas/alterations/1.9.0-1693554904-add-possword-policy.ts):

```typescript
import { sql } from '@silverhand/slonik';
import type { AlterationScript } from '../lib/types/alteration.js';

const alteration: AlterationScript = {
  up: async (pool) => {
    await pool.query(sql`
      alter table sign_in_experiences
      add column password_policy jsonb not null default '{}';
    `);
  },
  down: async (pool) => {
    await pool.query(sql`
      alter table sign_in_experiences
      drop column password_policy;
    `);
  },
};

export default alteration;

```

Apply the migration using:

```bash
pnpm alteration          # applies all pending alterations

# or for unreleased "next" version:

pnpm alteration deploy next

```

### Consuming Generated Schemas

After running the generator, a table definition produces a frozen schema object. For a hypothetical `products` table, the output includes:

```typescript
export type CreateProduct = { /* insertion shape */ };
export type Product = { /* full row shape */ };
export const product: GeneratedSchema<...> = Object.freeze({
  table: 'products',
  tableSingular: 'product',
  fields: { id: 'id', name: 'name', price: 'price' },
  createGuard,
  guard,
  updateGuard: guard.partial(),
});

```

Import and use this in core services:

```typescript
import { product } from '@logto/schemas';
await db.insert(product.table, newProductData);   // validated by product.createGuard

```

### Checking Applied Alterations Programmatically

You can inspect the migration history using the exported utility:

```typescript
import { getAppliedAlterations } from '@logto/schemas';
const applied = await getAppliedAlterations(pool);
console.log('Already applied:', applied.map(a => a.id));

```

## Summary

- Logto maintains schema definitions in **`@logto/schemas`** and generates TypeScript types and Zod validators via `generateSchema` in [`packages/schemas/src/gen/schema.ts`](https://github.com/logto-io/logto/blob/main/packages/schemas/src/gen/schema.ts).
- Database migrations are **immutable alteration scripts** stored in `packages/schemas/alterations/` following the pattern `<version>-<timestamp>-<description>.ts`.
- Each script implements the `AlterationScript` interface with `up` and `down` functions using Slonik's `sql` template tag.
- The **`alteration_state`** table tracks applied migrations, and the `pnpm alteration` CLI executes pending scripts chronologically.
- This architecture provides type-safe database access and version-controlled schema evolution.

## Frequently Asked Questions

### How does Logto track which database migrations have already run?

Logto tracks applied migrations in the **`alteration_state`** table, defined in [`packages/schemas/src/types/system.ts`](https://github.com/logto-io/logto/blob/main/packages/schemas/src/types/system.ts). When the CLI runs, it queries this table for the latest timestamp and executes only those alteration scripts with newer timestamps, recording each success back into the table to prevent re-execution.

### What is the naming convention for Logto alteration scripts?

Alteration scripts must follow the pattern `<package-version>-<unix-timestamp>-<short-description>.ts`, such as [`1.9.0-1693554904-add-possword-policy.ts`](https://github.com/logto-io/logto/blob/main/1.9.0-1693554904-add-possword-policy.ts). The Unix timestamp ensures chronological ordering, while the immutable file name guarantees that once a script is deployed, it cannot be modified—only superseded by new alterations.

### Can I roll back a database migration in Logto?

Yes, each `AlterationScript` in [`packages/schemas/lib/types/alteration.ts`](https://github.com/logto-io/logto/blob/main/packages/schemas/lib/types/alteration.ts) requires a **`down`** function that reverses the changes made by the corresponding `up` function. While the standard CLI applies forward migrations, the `down` implementation provides the necessary SQL logic for future rollback capabilities or manual reverting via database transactions.

### How does Logto ensure type safety between the database and application code?

Logto uses the **`generateSchema`** utility to derive TypeScript types and Zod validation schemas from a single source of truth. This generates `Create` and entity types plus runtime guards (`createGuard`, `guard`, `updateGuard`) that validate all data before it reaches PostgreSQL, ensuring the TypeScript compiler and runtime checks remain synchronized with the actual table structure.