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

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.

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

"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:

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:

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:

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:

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:

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

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}