# Indexes

You don't write the indexes your queries need. Jadpo sees every query, so
it creates every index they need, including indexes over several columns
and partial ones for `visible:`. A page of a user's visible todos, newest
first, gets `(owner_id, created_at desc, id desc)` where `deleted_at` is
empty. Every index change appears in its migration, and in the build's
schema report beside the query that needed it.

## Indexes for lookups from outside the app

Use an `indexes:` section for lookups made from outside the application,
such as a reporting export, that no query of yours needs:

```jadpo
entity Todo {
    id: Uuid
    due_at: Instant?

    indexes: {
        by_due_at: [due_at]  // for the reporting export; no query needs it
    }
}
```

An index is a list of fields, or a block of `fields:` and `kind:`.

## Special kinds of index

A special kind of index takes a `kind:` option, where the store supports
it:

- `kind: IndexKind.geographic` makes a GiST index on one geometry field
  ([Indexes](geospatial-storage.md#indexes) under Geospatial).
- `kind: IndexKind.hnsw` makes pgvector's HNSW index on one vector field,
  with `m:` and `ef_construction:` for tuning
  ([The vector index](vector-index.md) under AI).

Full-text and JSON indexes are kinds too.

Not built yet: index kinds other than geographic and HNSW.

## How the derived indexes are chosen

For each query, and each list it includes, an index holds the fields it
always tests with `==` or `in`, then its order's fields. Jadpo doesn't add
one when an index or unique key the table already has serves them. A
reference's own index goes once a query's index starts with its column.

- **Partial indexes.** The index a query needs on an entity with a
  lifecycle holds only the records `visible:` lets queries see
  (`WHERE "deleted_at" IS NULL`). The exception is a `visible:` that reads
  `clock.now`, which changes as time passes. A reference's own index then
  stays, since deleting the record it points at still finds hidden
  records.
- **Sorted text.** A text field a query sorts by is indexed in code-point
  order (`"title" COLLATE "C"`), the order a query sorts text in
  ([Order and pages](order-and-pages.md)), so the
  sorted read uses the index. Its name ends `_sorted_idx`, to keep it apart
  from an index on the same columns that tests them with `==`.
- **Geometry.** A geometry field a `geo.` condition tests gets a GiST
  index, and a `Point` field a box filter tests gets one on the point cast
  to `geometry` ([Indexes](geospatial-storage.md#indexes) under Geospatial).

A derived index records why it exists: the first query, alphabetically,
that needs it. The migration diff compares only what defines the index:
its table, columns or expression, kind, partial condition, operator class,
code-point columns and settings. So a query that sorts first needs no
migration, and `jadpo check` and `jadpo migrate new` find nothing to do.
The next migration written for another change records the new reason in
its snapshot, with no step for the index. Snapshots keep their format, so
every existing one reads.