Indexes

19.1 Overview

Indexes improve query performance by creating database-level data structures that accelerate filtering, sorting, and uniqueness enforcement. ADL generates some indexes automatically and allows domain authors to declare additional indexes explicitly.

Indexes are a database concern — they do not change API behavior. A query that works without an index works identically with one, only faster. The domain author’s job is to anticipate which query patterns the application will use most heavily and declare indexes to support them.

19.2 Custom Index Declaration

Custom indexes are declared in the indexes: block of a resource:

resources:
  Post:
    indexes:
      - fields: [authorId]
        description: "FK lookup — posts by author"

      - fields: [status, publishedAt]
        description: "Published posts sorted by date"

      - fields: [email]
        unique: true
        description: "Unique email constraint"

      - name: idx_post_search
        fields: [title]
        description: "Title search optimization"

Declaration

Multi-Field Indexes

Indexes on multiple fields create a composite index. The field order matters — the index is most effective when queries filter on the leftmost fields:

indexes:
  - fields: [status, createdAt]
    description: "Status filter + date sort"

This index accelerates:

• ?filter[status]=published&sort=-createdAt — both fields covered ✅
• ?filter[status]=published — leftmost field covered ✅
• ?sort=-createdAt — only the second field, less effective ⚠️

Rule: Declare composite indexes with the most selective field first (the field that eliminates the most rows).

Unique Indexes

A unique index enforces that no two records have the same combination of values across the indexed fields:

indexes:
  - fields: [email]
    unique: true

  - fields: [orderId, productId]
    unique: true
    description: "One item per product per order"

Single-field unique indexes are equivalent to the unique: true constraint on a field (§7.7). Multi-field unique indexes enforce combination uniqueness — each individual field value may repeat, but the combination must be unique.

Unique index violations return HTTP 409:

{
  "status": "error",
  "code": 409,
  "error_code": "DUPLICATE",
  "error_message": "A record with this value already exists"
}

DDL Generation

Indexes are generated as CREATE INDEX (or CREATE UNIQUE INDEX) statements after the resource’s CREATE TABLE:

CREATE INDEX idx_blog_posts_authorId ON blog_posts (authorId);
CREATE INDEX idx_blog_posts_status_publishedAt ON blog_posts (status, publishedAt);
CREATE UNIQUE INDEX idx_blog_posts_email ON blog_posts (email);

Auto-generated index names follow the pattern idx_{table}_{field1}_{field2}. Custom names override this convention.

19.3 Auto-Generated Indexes

The ADL engine automatically generates indexes for the following cases. These do not need to be declared in the indexes: block.

What Is NOT Auto-Generated

Foreign key fields do NOT receive automatic indexes. This is a deliberate design choice — not all FK fields are queried frequently enough to justify an index. The domain author SHOULD declare indexes on FK fields that are used in common query patterns:

indexes:
  - fields: [authorId]
    description: "FK lookup — posts by author"
  - fields: [categoryId]
    description: "FK lookup — products by category"

The linter (Appendix L) checks for FK fields without indexes and produces a warning.

19.4 Indexing Practices

Index Every FK

Every belongsTo foreign key field SHOULD have an index. Without it, relationship queries (?include=posts, nested list endpoints) require full table scans on the child table:

indexes:
  - fields: [authorId]
  - fields: [categoryId]
  - fields: [projectId]

Index Status and State Machine Fields

Status fields are the most common filter in list queries. Index them:

indexes:
  - fields: [status]

For resources with state machines, the state field is the status field — same recommendation applies.

Index Composite Query Patterns

Identify the most common multi-field query patterns and declare composite indexes for them:

# "Show me published posts, newest first"
- fields: [status, publishedAt]

# "Show me this user's orders by date"
- fields: [customerId, createdAt]

# "Show me active products in a category"
- fields: [categoryId, isActive]

# "Lookup by external system reference"
- fields: [externalSystem, externalId]
  unique: true

Index Fields Used in Computed Field Triggers

Aggregation computed fields query child tables on every trigger event. The FK field used in the aggregation query SHOULD be indexed:

# Order.orderTotal aggregates from OrderItem where orderId = ?
# The OrderItem.orderId field MUST be indexed:
indexes:
  - fields: [orderId]
    description: "Aggregation trigger — orderTotal recalculation"

Index Soft Delete Fields

Resources with softDelete: true filter by deletedAt IS NULL on every list query. An index on deletedAt or a composite index starting with deletedAt improves this:

indexes:
  - fields: [deletedAt, status]
    description: "Active records by status"

Avoid Over-Indexing

Every index adds write overhead — each INSERT, UPDATE, and DELETE must update every index on the table. For write-heavy resources, limit indexes to the query patterns that actually occur. The linter warns about indexes on fields that are never used in filters or sorts (based on static analysis of the domain definition).

19.5 Migration Behavior

Indexes participate in the migration system:

Index migration is always non-destructive — adding or removing an index does not affect data. Unlike column changes, index changes never require the allow_destructive flag.

Unique Index Addition on Existing Data

Adding a unique index to a table with existing data may fail if duplicate values already exist. The migration system checks for duplicates before creating the index. If duplicates are found, the migration plan reports an error:

{
  "status": "error",
  "code": 422,
  "error_code": "MIGRATION_ERROR",
  "error_message": "Cannot create unique index on field 'email': 3 duplicate values exist"
}

The domain author must resolve duplicates before the unique index can be applied.

19.6 Schema Endpoint

The schema introspection endpoint returns index definitions:

{
  "indexes": [
    { "name": "idx_blog_posts_authorId", "fields": ["authorId"], "unique": false },
    { "name": "idx_blog_posts_status_publishedAt", "fields": ["status", "publishedAt"], "unique": false },
    { "name": "idx_blog_posts_email", "fields": ["email"], "unique": true }
  ]
}

End of Chapter 19.

Next: Chapter 20 — Scopes