# database.advancedQueries

> Btree, GIN, GiST, BRIN, HNSW — when to use what, plus partial + expression + composite indexes.



---

<!-- source: en/database/indexes.md -->
## Indexes

_Btree, GIN, GiST, BRIN, HNSW — when to use what, plus partial + expression + composite indexes._

Indexes accelerate reads at the cost of write throughput + storage. Voltro's DSL lets you declare every Postgres index type with explicit intent.

## Single-column index

Indexes are declared at the table level — there is no column-level
`.index()` modifier. The single surface is `.index(...)`:

```ts
const notes = table('notes', {
  id:       id(),
  authorId: text(),
  tenantId: text(),
})
  .index(['authorId'])        // auto-named → "notes_authorId_idx"
  .index(['tenantId'])
```

Each generates `CREATE INDEX notes_<col>_idx ON notes (<col>);` — btree, default opclass.

## Named composite index

For multi-column lookups + ORDER BY pagination:

```ts
import { table, id, text, timestamp } from '@voltro/database'

const notes = table('notes', {
  id:        id(),
  tenantId:  text(),
  createdAt: timestamp(),
})
  // Chained `.index(name, [cols], options?)` on the table — leftmost column
  // is the most-selective filter. Index fields are plain column names
  // (or `{ expr: '…' }` for an expression).
  .index('notes_tenant_created', ['tenantId', 'createdAt'])
```

Column order matters — leftmost is the most-selective filter. The framework warns when an index's leftmost column is also covered by a single-column index (redundancy).

## Indexing mixin-contributed columns

`.index([...])` / `.unique([...])` keys accept the table's **own** columns
plus the columns each **directly applied** mixin contributes — `tenant()` →
`tenantId`, `audit()` → `createdAt` / `updatedAt` / `createdBy` / `updatedBy`,
`softDelete()` → `deletedAt` / `deletedBy`. **Order matters:** declare the
index or unique constraint **after** `.with(...)`, so the mixin columns are in
scope. Calling `.index([...])` *before* `.with(...)` sees only the own columns
(the mixin hasn't been applied yet).

```ts
export const memberships = table('memberships', {
  id:     id(),
  userId: reference(() => users),
})
  .with(audit(), tenant())            // applies tenantId + audit columns
  .unique(['tenantId', 'userId'])     // ✅ mixin column tenantId is addressable
  .index(['createdAt'])               // ✅ audit column, applied above
```

Typos are still caught — the key is the real merged column set, not an
"accept any string" escape hatch. `tenant()` already ships its OWN `tenantId`
index, so a plain per-tenant lookup needs nothing extra; this only matters for
the additional composite keys you author yourself.

**Transitive caveat.** Only **directly applied** mixin columns enter the key
union. `.with(tenant())` adds `tenantId` but NOT `audit()`'s columns — even
though `tenant()` requires `audit()` and those columns exist at runtime. To
index a transitively-required mixin's column, apply that mixin explicitly:
`.with(audit(), tenant())`.

## Index types

Every index kind is selected with the `{ kind }` option on the
table-level `.index(name, [cols], { kind })` (or `.expressionIndex(...)`).
The default is `btree`.

### Btree *(default)*

Equality + range + ordering. The right choice 90% of the time. Leave
`kind` off to get it:

```ts
table('notes', { id: id(), authorId: text() })
  .index('notes_author', ['authorId'])         // btree, default opclass
```

### GIN

For arrays, JSONB containment, and full-text search:

```ts
import { table, id, json } from '@voltro/database'

table('docs', { id: id(), tags: json<string[]>() })
  .index('docs_tags', ['tags'], { kind: 'gin' })
```

On postgres this emits `USING GIN`. GIN is postgres-only — on
mysql / mariadb a btree can't serve containment so the migrator skips
the index + warns (denormalise the path you query into a generated
column and index THAT instead); on mssql / sqlite it falls back to a
plain btree + a warning.

### GiST

For range types, geometric types (PostGIS), and full-text search where
you need ranking:

```ts
table('venues', { id: id(), location: text() })   // geography(...) in real PostGIS code
  .expressionIndex('venues_loc_gist', [{ expr: '"location"' }], { kind: 'gist' })
```

Postgres-only; the migrator warns + falls back to btree on other
dialects. See [PostGIS](/docs/database/postgis) for the spatial story.

### BRIN

Block-range index — extremely cheap for very large append-mostly tables
sorted by an indexed column (time-series, logs):

```ts
table('events', { id: id(), occurredAt: timestamp() })
  .index('events_occurred', ['occurredAt'], { kind: 'brin' })
```

On postgres this emits `USING BRIN` — tiny (~1% of the table size) +
fast for range scans on append-ordered data, useless for random-access
lookups. Other dialects have no block-range method; the migrator falls
back to a plain btree + a warning (the range scan still works, just
without BRIN's size win).

### HNSW (pgvector)

For vector similarity search. The index must cover exactly one
`vector()` column — declaring `kind: 'hnsw'` on anything else throws at
declaration time:

```ts
table('embeddings', { id: id(), embedding: vector(1536) })
  .index('emb_hnsw', ['embedding'], { kind: 'hnsw' })
```

Defaults to `m=16`, `ef_construction=64`, opclass `vector_cosine_ops`.
Override the tuning knobs via `kindOptions.hnsw`:

```ts
table('embeddings', { id: id(), embedding: vector(1536) })
  .index('emb_hnsw', ['embedding'], {
    kind: 'hnsw',
    kindOptions: { hnsw: { m: 24, efConstruction: 128 } },
  })
```

Postgres + pgvector only; on every other dialect a btree on a vector is
meaningless, so the migrator emits no index + warns (ANN queries fall
back to a sequential scan). The distance metric → opclass mapping
(`vector_cosine_ops` / `vector_l2_ops` / `vector_ip_ops`) is covered on
the [Vector columns](/docs/database/vectors) page.

### Per-dialect support matrix

`warn+btree` = the migrator emits a plain btree index + a
`[voltro:migrate]` warning (the intended method was unavailable but a
btree still helps). `skip+warn` = no index is emitted + a warning (a
btree on that column would be useless).

| `kind`             | postgres                                              | mysql       | mariadb     | mssql      | sqlite     |
|--------------------|-------------------------------------------------------|-------------|-------------|------------|------------|
| `btree` *(default)*| `USING btree` (implicit)                              | implicit    | implicit    | implicit   | implicit   |
| `gist`             | `USING GIST`                                          | warn+btree  | warn+btree  | warn+btree | warn+btree |
| `gin`              | `USING GIN`                                           | skip+warn   | skip+warn   | warn+btree | warn+btree |
| `brin`             | `USING BRIN`                                          | warn+btree  | warn+btree  | warn+btree | warn+btree |
| `hnsw`             | `USING hnsw (col <opclass>) WITH (m=…, ef_construction=…)` | skip+warn   | skip+warn   | skip+warn  | skip+warn  |

A "jsonb index" is not a separate kind — it's a `json()` column (already
`JSONB` on postgres) plus `{ kind: 'gin' }`, i.e. the `gin` row above.

## Partial indexes

Index only rows matching a predicate — drastically smaller + faster when most rows wouldn't match. Pass `{ where }`:

```ts
table('notes', { id: id(), updatedAt: timestamp(), archived: boolean() })
  .index('notes_unarchived', ['updatedAt'], { where: `"archived" = false` })
```

Useful for soft-delete tables (`WHERE deletedAt IS NULL`) and status-filtered queries. See [Partial indexes (WHERE clause)](#partial-indexes-where-clause) below for the full cross-dialect story.

## Expression indexes

Index a computed value, not a column — use `.expressionIndex(...)` with an `{ expr }` entry:

```ts
table('users', { id: id(), email: text() })
  .expressionIndex('users_email_lower', [{ expr: 'lower("email")' }])
```

Then `WHERE lower("email") = ?` uses the index. The query builder doesn't auto-rewrite `WHERE email ILIKE 'foo'` to use this — you call out the expression explicitly.

## Unique indexes

```ts
text().unique()                                 // single-column, on the column
```

Multi-column uniqueness is declared at the table level with `.unique(name, [cols])` — see [Composite UNIQUE constraints](#composite-unique-constraints) below.

## Unique among ACTIVE rows — `.uniqueActive([...])`

Enforce uniqueness only among the rows that aren't soft-deleted — "one active roadmap per (project, year)", where a soft-deleted roadmap frees the key for a new one:

```ts
table('project_roadmaps', {
  id: id(), projectId: text(), year: integer(), deletedAt: timestamp(),
})
  .softDelete()
  .uniqueActive(['projectId', 'year'])            // partial UNIQUE among deletedAt IS NULL
```

Emits a `CREATE UNIQUE INDEX … WHERE "deletedAt" IS NULL`, so a soft-deleted row leaves the active set and a NEW row with the same key inserts cleanly — no hand-written `generatedAs("CASE WHEN …")` column, and no [resurrection bug](/docs/database/migrations/troubleshooting) where a re-imported soft-deleted key collides. The predicate defaults to the `softDelete()` active set; override it for a custom one:

```ts
.uniqueActive('byActiveSlug', ['orgId', 'slug'], { where: `"status" = 'open'` })
```

Cross-dialect:

| Dialect             | Support                                                        |
|---------------------|----------------------------------------------------------------|
| postgres / sqlite / mssql | native partial `CREATE UNIQUE INDEX … WHERE`             |
| mysql / mariadb     | lowered automatically to a generated STORED column per key column (NULL when soft-deleted) + a UNIQUE over them — NULL-distinct gives the same resurrection-safe semantics. Nothing to hand-write. |

**mysql / mariadb — how the emulation works.** Those engines have no partial index, so `.uniqueActive(['projectId', 'year'])` lowers to one `CASE WHEN <predicate> THEN CAST(<col> AS CHAR(255)) ELSE NULL END` STORED column per key column plus a `UNIQUE` over them. A soft-deleted row's generated columns are all NULL, and mysql/mariadb treat NULLs as DISTINCT in a unique index, so it never collides — re-creating the key just works, exactly like the partial index elsewhere. This round-trips through the declarative differ (the generated columns are part of the declared snapshot on those dialects, so `voltro dev` never re-plans them). You write the same `.uniqueActive([...])` on every dialect.

The predicate is emitted verbatim (ANSI double-quoted identifiers; on mysql/mariadb they are re-quoted with backticks inside the generated column).

## When NOT to index

- Tables with <10k rows on a fast disk — the cost of maintaining the index outweighs the seq-scan cost.
- Columns with very low selectivity (boolean flags, `status` enums where one value dominates). Use a partial index instead.
- Write-heavy hot paths. Every index is a synchronous write on every insert/update.

## Partial indexes (WHERE clause)

```ts
table('orders', {
  id:       id(),
  orgId:    text(),
  status:   text(),
  createdAt: timestamp(),
})
  .index('byOpenStatus', ['orgId', 'createdAt'], {
    where: `"status" IN ('pending', 'approved')`,
  })
```

The DDL emits `CREATE INDEX ... WHERE ...` — only rows matching the
predicate participate in the index. Use cases:

- **Index only non-soft-deleted rows**: `where: \`"deletedAt" IS NULL\``
- **Index only active users**: `where: \`"banned" = false\``
- **Index only open tickets**: `where: \`"status" IN ('open', 'pending')\``

The result: a much smaller B-tree (faster reads, smaller cache
footprint) at the cost of one extra `WHERE` clause the query
planner has to match against.

Cross-dialect:

| Dialect             | Support                                            |
|---------------------|----------------------------------------------------|
| postgres / sqlite   | native `CREATE INDEX ... WHERE ...`                |
| mssql               | native "filtered index"                            |
| mysql / mariadb     | NOT supported → drops the WHERE + warns at migrate |

The `where` clause is emitted verbatim — caller is responsible for
quoting identifiers per the target dialect.

## Expression indexes (functions on columns)

For predicates that compute on the column rather than match exact
values, use `.expressionIndex()`:

```ts
table('users', { id: id(), email: text() })
  .expressionIndex('byEmailCi', [{ expr: 'lower("email")' }])
```

Now `WHERE lower("email") = ?` uses the index. Without it,
case-insensitive email lookup is a sequential scan.

```ts
// Mix columns + expressions
.expressionIndex('byOrgCreatedMonth', [
  'orgId',
  { expr: \`date_trunc('month', "createdAt")\` },
])

// Combine with partial-where
.expressionIndex('byActiveEmailCi',
  [{ expr: 'lower("email")' }],
  { where: \`"active" = true\` },
)
```

Why a separate method from `.index([...])`:

- `.index([cols])` validates the column names against the row type
  at compile time. Typos fail at `tsc --noEmit`.
- `.expressionIndex(name, [...])` accepts arbitrary expression
  strings — by definition the framework can't type-check them.
  Keeping the two methods separate preserves the compile-time
  safety of the regular form.

## Index names are unique per SCHEMA, not per table

Every dialect keys index names per schema (postgres `pg_class`, mysql /
mariadb `information_schema`, mssql `sys.indexes`, sqlite `sqlite_master`)
— **not per table**. So a hand-picked name reused on two tables collides:

```ts
table('ab_tests',    { /* … */ }).index('byStatusStart', ['status', 'startAt'])
table('tournaments', { /* … */ }).index('byStatusStart', ['status', 'startAt'])
// ❌ throws — 'byStatusStart' would exist twice in one schema
```

The DB creates only the first; every later `CREATE INDEX … IF NOT EXISTS
<name>` is a silent no-op, so `db plan` re-emits the un-created ones
forever and never reaches "up to date". The framework catches this when
the full schema is snapshotted (boot / `db plan`) and fails loud, naming
both tables + a suggested fix. **Auto-named** indexes (`.index([col])` →
`<table>_<col>_idx`) are table-prefixed and can't collide — only
explicit names can. Give each a distinct, table-scoped name
(`abTestsByStatusStart`, `tournamentsByStatusStart`).

## Composite UNIQUE constraints

Multi-column uniqueness — `(orgId, slug)` must be unique so two
orgs can both have a `/dashboard` slug but neither can have two of
their own:

```ts
table('org_slugs', {
  id:    id(),
  orgId: reference(() => orgs),
  slug:  text(),
})
  .unique(['orgId', 'slug'])                         // auto-named
  .unique('byOrgSlug', ['orgId', 'slug'])            // explicit name
  .unique('byOrgSlug', ['orgId', 'slug'], { dedup: 'suffix-counter' })  // with backfill policy
```

Distinct from the column-level `.unique()` modifier (single-column
only, lives on the column). Composite UNIQUE MUST be declared at
the table level.

The `dedup` policy tells the migration planner what to do when the
constraint is added to a populated table with duplicates:

- `'fail'` (default) — refuse with the conflicting rows surfaced
- `'suffix-counter'` — UPDATE conflicts to `<value>-2`, `<value>-3`, ...
- `sql\`...\`` — custom SQL fragment

Emits `CONSTRAINT <name> UNIQUE (col1, col2, ...)` inline in CREATE
TABLE on every dialect. Standard SQL.

This is what backs `ctx.store.upsert(..., { conflictColumns: ['a', 'b'] })`
— see [Bulk operations](/docs/database/bulk-operations#upsert).

## GiST indexes (PostGIS spatial)

`{ kind: 'gist' }` on `.expressionIndex()` emits `USING GIST`:

```ts
table('venues', {
  id:       id(),
  location: geography('Point', 4326),   // from @voltro/plugin-postgis
}).expressionIndex(
  'venues_loc_gist',
  [{ expr: '"location"' }],
  { kind: 'gist' },
)
```

GiST is the access method PostGIS needs for spatial predicates
(`ST_DWithin`, `ST_Contains`, etc.) — a regular B-tree index can't
serve them. Postgres-only; the migrator warns + falls back to
B-tree on other dialects.

See [PostGIS](/docs/database/postgis) for the full spatial story.

## Full-text indexes

```ts
table('posts', { id: id(), title: text(), body: text() })
  .fullTextIndex('postSearch', ['title', 'body'], {
    config:  'english',
    weights: { title: 'A', body: 'B' },
  })
```

Higher-level abstraction over expression indexes — see
[Full-text search](/docs/database/full-text-search) for the full
shape.

## Inspecting

```sh
# The migrations introspection endpoint carries the full live schema —
# every table's columns + indexes as the runtime sees them.
curl http://localhost:5191/_voltro/inspect/migrations
```

Or via the dashboard's Database tab.

For Postgres-side inspection: `pg_stat_user_indexes` shows scans + tuples read per index. Indexes with `idx_scan = 0` after weeks of traffic are deadweight — drop them.



---

<!-- source: en/database/json.md -->
## JSON columns

_Typed jsonb columns, path queries, GIN indexing, and when to denormalise into real columns._

`json<T>()` declares a `jsonb` column whose JSON shape is typed by `T`. The runtime decodes on read + validates on write — typos in your object literals fail the TypeScript check, not at runtime.

## Declaring a typed JSON column

```ts
import { table, id, json } from '@voltro/database'

interface NotePrefs {
  readonly fontSize: 'sm' | 'md' | 'lg'
  readonly collapsed: ReadonlyArray<string>
  readonly autoSave?: boolean
}

const notes = table('notes', {
  id:    id(),
  prefs: json<NotePrefs>().default({ fontSize: 'md', collapsed: [] }),
})
```

`ctx.store.select('notes')` returns `prefs: NotePrefs` — autocomplete works in your IDE, mutation inputs are checked too.

## Storage + validation per dialect

`json()` maps to each engine's native JSON type, and Voltro enforces JSON validity **on write, on every dialect** — invalid JSON is rejected by the database, not only by TypeScript:

| Dialect | Column type | Validated on write |
|---|---|---|
| postgres | `JSONB` (binary, GIN-indexable) | yes (native) |
| mysql / mariadb | `JSON` | yes (mariadb's `JSON` is `LONGTEXT` + an auto `json_valid` CHECK) |
| mssql | `NVARCHAR(MAX)` + `CHECK (ISJSON(col)=1)` | yes |
| sqlite | `TEXT` + `CHECK (json_valid(col))` | yes |

On postgres the binary `JSONB` form is what makes path queries + GIN indexing fast; the other engines store JSON as text (mariadb's `information_schema` reports the `JSON` column as `longtext` — that *is* what the JSON type is there). Either way, the `jsonField(...)` filters below and write-validation behave identically across all of them, and reads always come back as parsed objects/arrays — never raw strings.

> On postgres, prefer `jsonb` over `json` (Voltro always emits `jsonb`): binary storage, GIN-indexable, faster. Plain `json` only preserves exact byte / whitespace / key-order — never what you want for app data.

## Path filters

Filter on a value *inside* a `json()` column with `jsonField(column, ...path)`:

```ts
import { jsonField } from '@voltro/database'

database.users.where(jsonField('preferences', 'theme').eq('dark'))
database.events.where(jsonField('payload', 'amount').gt(1000))       // numeric, not lexical
database.docs.where(jsonField('meta', 'tags', 0).eq('urgent'))       // nested key + array index
database.users.where(jsonField('preferences', 'locale').inSet(['de', 'en']))
database.users.where(jsonField('preferences', 'theme').isNotNull())
```

Path segments are object keys (string) or array indices (number) → `$.theme` / `$.tags[0]`.

| Operators | Compare the extracted value as |
|---|---|
| `eq` `neq` `inSet` `notInSet` `contains` `isNull` `isNotNull` | **text** |
| `gt` `gte` `lt` `lte` | **number** (numeric ordering, not lexical) |

The same `jsonField(...)` expression is **portable across every backend** — it lowers to each dialect's json accessor: postgres `#>>`, mysql/mariadb `JSON_EXTRACT` (+ `JSON_UNQUOTE`), mssql `JSON_VALUE`, sqlite / turso `json_extract`.

Reactive subscriptions filtered by a JSON path stay live, but the matcher treats the leaf as **non-indexable**: it's re-checked on every change to the table (the same way `contains` is). For a high-traffic filter, denormalise into a real indexed column — or index the specific path (see [Indexing JSON columns](#indexing-json-columns) below).

> **Scope.** JSON-path filtering is a **server-side** query-DSL feature (query / mutation handlers). Clients can't yet send JSON-path filters over rpc — that's a deliberate later phase (it needs path allow-listing + validation). For arbitrary expressions the DSL doesn't model, drop to the `store.raw` escape hatch (see the [query builder](/docs/database/query-builder#raw-sql-escape-hatch)).

## Indexing JSON columns

### GIN index — broad coverage

A GIN index covers arbitrary containment queries on the whole `jsonb`
document. Declare it with the table-level `.index(name, [col], { kind: 'gin' })`:

```ts
import { table, id, json } from '@voltro/database'

const events = table('events', {
  id:      id(),
  payload: json<unknown>(),
})
  .index('events_payload', ['payload'], { kind: 'gin' })
```

On postgres this emits `CREATE INDEX "events_payload" ON "events" USING GIN ("payload")`,
so any containment query on `payload` is indexed.

GIN is postgres-only. On mysql / mariadb there is no GIN access method
and a btree on a JSON column can't serve containment — the migrator
**skips** the index and warns, pointing you at the single-path approach
below. On mssql / sqlite it falls back to a plain btree + a warning.
GIN is also large (often 30-50% of the table size for wide JSON); use
the next option when you only filter on one specific path.

### Index a JSON path — `jsonIndex`

When 99% of your queries look like `jsonField('payload', 'kind').eq('X')`,
index just that path with `jsonIndex(column, ...path)` inside
`.expressionIndex(...)`. It mirrors `jsonField` exactly — same column, same
segments, lowered to the same per-dialect accessor — so a filter on that
path can use the index:

```ts
import { table, id, json, jsonIndex } from '@voltro/database'

const events = table('events', { id: id(), payload: json<unknown>() })
  .expressionIndex('events_kind',   [jsonIndex('payload', 'kind')])
  .expressionIndex('events_amount', [jsonIndex('payload', 'amount').numeric()])
```

Text extraction (the default) backs `eq` / `neq` / `inSet` / `contains`;
`.numeric()` backs the range ops (`gt` / `gte` / `lt` / `lte`). Because the
index expression is byte-identical to the WHERE expression `jsonField(...)`
compiles to, the optimiser actually picks it up.

> **Dialect support is uneven — this is a hard engine limit, not a Voltro
> choice.** Only some engines can index an expression directly:
>
> | Dialect | `jsonIndex` | Notes |
> |---|---|---|
> | postgres | ✓ expression index | `((col #>> '{path}'::text[]))` |
> | mysql (8.0.13+) | ✓ functional index | `((JSON_UNQUOTE(JSON_EXTRACT(...))))` |
> | sqlite | ✓ expression index | `(json_extract(col, '$.path'))` |
> | mariadb | — skipped + warned | no expression-index support |
> | mssql | — skipped + warned | no expression-index support |
>
> On **mariadb / mssql** the migrator skips a `jsonIndex` and warns rather
> than emit DDL the engine rejects. The idiomatic indexed-JSON path there is
> a **generated / computed column** (which Voltro already supports), then
> filter on that column instead of `jsonField(...)`:
>
> ```ts
> table('events', {
>   id:      id(),
>   payload: json<unknown>(),
>   // materialise the path into a real, indexable column
>   kind:    text().generatedAs(`JSON_UNQUOTE(JSON_EXTRACT(\`payload\`, '$.kind'))`),
> }).index('events_kind', ['kind'])
> // query: ctx.store.select('events').where('kind', 'click').all()
> ```

For an arbitrary expression the `jsonIndex` shorthand doesn't model, drop to
the raw `.expressionIndex(...)` form with an `{ expr }` entry:

```ts
table('events', { id: id(), payload: json<unknown>() })
  .expressionIndex('events_kind', [{ expr: `(payload->>'kind')` }])
```

There you own per-dialect correctness (the `expr` string is emitted
verbatim; the postgres `->>` form is shown).

## Writing JSON

```ts
ctx.store.insert('notes', {
  prefs: { fontSize: 'lg', collapsed: ['archive'] },
})
```

A plain `update` writes the WHOLE JSON value. To change one field
without rewriting the rest, use **`patchJson`** — a server-side in-place
JSON merge (no read-modify-write round-trip):

```ts
// Merge an object over the top level of the column:
await ctx.store.patchJson('notes', id, 'prefs', { autoSave: true })

// Set a nested path (dot-separated; the column is the first segment):
await ctx.store.patchJson('notes', id, 'prefs.theme', 'dark')
```

`patchJson(table, pk, path, value)` returns the post-image (or `null`
when the row doesn't exist) and emits an `update` ChangeEvent so reactive
subscribers see the change. The merge is server-side on every dialect —
postgres `jsonb_set` / `||`, mysql + mariadb `JSON_SET`, mssql
`JSON_MODIFY`, sqlite `json_set` / `json_patch`.

When you need a full read-modify-write (e.g. computing the new value from
the old in JS), read the row, change the object, and write it back inside
the mutation's transaction:

```ts
const note = await ctx.store.select('notes').where('id', id).one()
await ctx.store.update('notes', id, {
  prefs: { ...note.prefs, autoSave: true },
})
```

## Validating JSON shape

Two layers of validation apply:

1. **JSON validity** — that the stored bytes are well-formed JSON — is enforced automatically by the database on every dialect (see [Storage + validation per dialect](#storage--validation-per-dialect)). You don't declare anything.
2. **JSON *shape*** — that the value matches your expected structure — is up to you: enforce it at the table level with `table().validate(Schema)`:

```ts
import { Schema } from 'effect'

table('notes', { id: id(), prefs: json<NotePrefs>() })
  .validate(Schema.Struct({
    prefs: Schema.Struct({ fontSize: Schema.Literal('sm', 'md', 'lg') }),
  }))
```

Decode failure throws a typed `TableValidationFailed` before the INSERT runs. There is no `.check()` modifier on a JSON column.

## When JSON is the wrong choice

JSON is great for:

- Free-form user-configurable data (preferences, layout configs)
- Sparse extensions (every row has different shape)
- Foreign-system payloads (Stripe webhook bodies, Slack message JSON)

JSON is a footgun for:

- **Anything you filter on heavily.** Denormalise into real columns — they're cheaper to query, easier to index, simpler to constrain.
- **Anything with strict schema.** A real column with a NOT NULL + CHECK is stronger than a JSON path constraint.
- **Joining / relating to other tables.** You can't FK from a JSON path.

Rule of thumb: if you'd write a migration to add a new field, it's a real column. If users add fields without your code changing, it's JSON.

## Anti-patterns

- **`json<any>()` everywhere.** Defeats the type-safety. Be specific.
- **Putting a foreign key inside JSON.** No FK constraint, no cascade, no clean join. Use a real `reference(() => table)` column.
- **Storing big binary as JSON.** Use the storage plugin (`@voltro/plugin-storage`) for blobs > a few KB.



---

<!-- source: en/database/recursive-cte.md -->
## Recursive CTEs

_WITH RECURSIVE for org hierarchies, comment threads, file-folder trees, category graphs — single query, no app-side loops._

`WITH RECURSIVE` lets a query reference itself. Used for tree-walks
and graph traversals that would otherwise need an app-side loop with
N round-trips. Voltro's `.recursiveCte()` ships the SQL standard
form on every supported dialect (postgres, mysql 8+, mariadb 10.2+,
mssql, sqlite 3.8+).

## When to use

- **Org hierarchy**: "find every descendant of org X" / "find every
  parent up to the root"
- **Comment threads**: "fetch a comment + every reply, recursively"
- **File-folder tree**: "list everything inside this folder, any depth"
- **Category graphs**: "products in this category OR any sub-category"
- **Dependency graphs**: "what migrations does plan X transitively
  depend on?"

When you don't have a recursive structure, plain
[`withCte()`](/docs/database/query-builder#ctes) is enough.

## Shape

Every recursive CTE has two arms joined by `UNION` (or `UNION ALL`):

1. **Anchor** — the seed query. Picks the starting rows
   non-recursively. Typically `WHERE id = <root>`.
2. **Recursion** — references the CTE by name, joining itself to the
   parent table to walk one level. Composed via `.innerJoin()`.

Compose with `union(anchor, recursion)` or `unionAll(...)` and pass
the combined descriptor to `.recursiveCte(name, ...)`.

## Example: org hierarchy

```ts
import { eq, queryFor, union } from '@voltro/database'
// `database` is YOUR project's handle — `export const database =
// databaseHandle({ ...tables })` in `database/index.ts`.
import { database } from '../database/index'

const rootId = 'org_root'

// Anchor: the root org itself
const anchor = queryFor(database.orgs).where(eq('id', rootId))

// Recursion: every org whose parentId is in the running set
const recursion = queryFor(database.orgs).as('child')
  .innerJoin('descendants', 'parent', eq('parent.id', 'child.parentId'))

const tree = await ctx.store.query(
  queryFor(database.orgs)
    .recursiveCte('descendants', union(anchor, recursion).descriptor)
    .where(eq('id', rootId))
    .descriptor,
)
// tree: every org reachable from rootId, transitively
```

Compiles to:

```sql
WITH RECURSIVE "descendants" AS (
  -- anchor
  (SELECT * FROM "orgs" WHERE "id" = $1)
  UNION
  -- recursion
  (SELECT * FROM "orgs" AS "child"
   INNER JOIN "descendants" AS "parent" ON "parent"."id" = "child"."parentId")
)
SELECT * FROM "orgs" WHERE "id" = $1
```

## Example: walk UP a tree

The recursion direction is yours to choose — join `child.parentId`
to walk up, or `parent.id` to walk down.

```ts
const node = queryFor(folders).where(eq('id', leafFolderId))
const ancestors = queryFor(folders).as('parent')
  .innerJoin('chain', 'child', eq('child.parentId', 'parent.id'))

const path = await ctx.store.query(
  queryFor(folders)
    .recursiveCte('chain', union(node, ancestors).descriptor)
    .descriptor,
)
```

## Cycle handling

Standard `UNION` semantics dedupe across iterations — if your graph
contains cycles, the recursion stops naturally when no new rows
appear in a step. For very large graphs with cycles, prefer
`unionAll` only when you've verified the graph is acyclic OR you
have a `WHERE` predicate in the recursion that prevents infinite
loops (e.g. a depth limit).

## Cross-dialect notes

| Dialect    | Supported | Notes |
|------------|-----------|-------|
| postgres   | ✓ native  | Best optimizer for recursive CTEs |
| mysql 8+   | ✓ native  | Recursion depth limited by `cte_max_recursion_depth` (default 1000) — set per-session for deeper trees |
| mariadb 10.2+ | ✓ native | Same as mysql |
| mssql      | ✓ native  | `OPTION (MAXRECURSION N)` hint NOT auto-emitted — set if you need >100-level recursion |
| sqlite 3.8+ | ✓ native | Smaller default recursion limit; check `PRAGMA recursive_triggers` |

Voltro's compiler emits identical syntax across dialects; only the
runtime defaults differ.

## Reactivity

Recursive CTE queries are reactive — coarsely. The engine registers
the subscription against every table the recursion reads (the anchor +
the recursive arm), so a write to any of them re-runs the tree-walk.
It can't pre-filter per column — a change to one ancestor can reshape
the whole result — so it re-queries on any contributing-table change.
Correct, but it re-runs the full recursion each time: fine for bounded
trees (org hierarchies, comment threads). For very hot or very large
trees, model the relationship via [Relations](/docs/database/joins) and
eager-load with `.with({...})` to get the per-field pre-filter.

## Limitations

- **No mutual recursion** between two CTEs in the same block. Each
  recursive CTE references only itself.
- **Cycle detection without `UNION` dedup**: if you use `unionAll`,
  ensure your recursion has a termination predicate. Voltro doesn't
  inject a default depth limit.
- **No reactivity**: see above.

## When NOT to use

- **Single-level parent / child** — use a regular
  [self-join](/docs/database/self-joins). A recursive CTE is overkill
  at depth 1.
- **Performance-critical hot path with a large result set** —
  recursive queries can explode on wide trees. Profile with
  `EXPLAIN ANALYZE` against realistic data. If the iteration count
  runs into the thousands, consider materializing the computed
  hierarchy into a separate table instead.
- **Arbitrary graph algorithms** (shortest path, connected
  components) — a recursive CTE can be bent into these but it gets
  ugly fast. A graph database (Neo4j, the AGE extension) is the
  better fit.

## See also

- [Plain CTEs](/docs/database/query-builder#ctes) — `withCte()` for
  non-recursive named sub-queries
- [Self-joins](/docs/database/self-joins) — for single-level
  parent/child queries
- [Sub-queries](/docs/database/sub-queries) — for non-recursive
  "rows where a column matches another query" patterns
- [Set operations](/docs/database/set-operations) — `union` /
  `unionAll`, the mechanism a recursive CTE is built on
- [Joins](/docs/database/joins) — relation-based traversal when the
  graph depth is fixed (e.g. parent + immediate children)
- [Aggregations](/docs/database/query-builder#aggregations) —
  COUNT/SUM/AVG over a recursive CTE's result set



---

<!-- source: en/database/set-operations.md -->
## Set operations (UNION / INTERSECT / EXCEPT)

_Combine the results of multiple queries — UNION dedups, UNION ALL doesn't, INTERSECT keeps rows in both, EXCEPT subtracts._

`union` / `unionAll` / `intersect` / `except` combine two or more
queries that produce the **same column shape**. The result is one
unified row set you can sort, paginate, and aggregate against.

## Quick start

```ts
import { union, eq, queryFor } from '@voltro/database'

// Active + archived tickets for an org, treated as one list
const all = await ctx.store.query(
  union(
    queryFor(database.activeTickets).where(eq('orgId', oid)),
    queryFor(database.archivedTickets).where(eq('orgId', oid)),
  ).orderBy('createdAt', 'desc').limit(50).descriptor,
)
```

Compiles to:

```sql
(SELECT * FROM "activeTickets" WHERE "orgId" = $1)
UNION
(SELECT * FROM "archivedTickets" WHERE "orgId" = $2)
ORDER BY "createdAt" DESC
LIMIT 50
```

The outer `.orderBy()` / `.limit()` apply to the combined result —
each inner query keeps its own predicate but loses its own
ordering.

## The four operations

| Helper        | Semantics                                                  |
|---------------|------------------------------------------------------------|
| `union(...)`  | Rows from any input, deduplicated                          |
| `unionAll(...)` | Rows from any input, NO dedup (faster + preserves duplicates) |
| `intersect(...)` | Rows present in EVERY input                             |
| `except(...)` | Rows in the FIRST input, NOT in any subsequent input       |

Each accepts 2+ queries. One input throws (`requires at least two
queries`).

## When to reach for each

- **`union`** — "show this user's items from two different sources,
  deduped" (a notifications feed mixed with system messages).
- **`unionAll`** — same as `union` but you know there are no
  duplicates OR you specifically want to keep them. Skipping the
  dedup pass is meaningfully faster on large inputs.
- **`intersect`** — "users who exist in BOTH the paying-customers
  list AND the active-this-week list".
- **`except`** — "all users EXCEPT those who unsubscribed". Use
  `notInSubquery` if you only need a column-level check; `except`
  when you're operating on full row shapes.

## Three-way and beyond

All four accept any number of inputs (≥ 2). The compiler chains them
with the appropriate keyword:

```ts
const combined = union(
  queryFor(database.eventsA),
  queryFor(database.eventsB),
  queryFor(database.eventsC),
)
// (SELECT * FROM "eventsA") UNION (SELECT * FROM "eventsB") UNION (SELECT * FROM "eventsC")
```

## Column shape requirement

Every input MUST produce the same column shape. The framework
doesn't enforce this at TypeScript level — the DB throws at query
time if shapes don't line up. To narrow each input, use
`.select(...cols)` on the inner queries so they project the same
column set.

## Cross-dialect

Standard SQL — every dialect we ship supports the four set ops with
identical syntax. No per-dialect dispatch.

## Reactivity

Reactive — coarsely. The engine registers the subscription against
every branch's source table, so a write to any branch (the UNION /
INTERSECT / EXCEPT side) re-runs the combined query. It re-queries on
any contributing-table change rather than pre-filtering per column, so
keep the branches' result sets bounded.

## See also

- [Sub-queries](/docs/database/sub-queries) — `notInSubquery` for
  the column-level "in A but not in B" case
- [Aggregations](/docs/database/aggregations) — `count()` etc. on
  a set-op result is a common pattern
- [CTEs](/docs/database/query-builder#ctes) — name a complex set-op
  result so you can reference it in a larger query



---

<!-- source: en/database/sub-queries.md -->
## Sub-queries (IN / NOT IN / EXISTS)

_Predicates that reference other queries — col IN (SELECT ...), EXISTS (SELECT ...)._

`inSubquery` / `notInSubquery` / `exists` / `notExists` let a WHERE
predicate reference the results of another query. Use when you'd
otherwise pull a list of IDs to the app and filter client-side.

## Quick start

```ts
import { inSubquery, eq, queryFor } from '@voltro/database'

// Find every post by an actively-banned user
const blockedUserIds = queryFor(database.blockedUsers).select('userId')

const banned = await ctx.store.query(
  queryFor(database.posts)
    .where(inSubquery('userId', blockedUserIds))
    .descriptor,
)
```

Compiles to:

```sql
SELECT * FROM "posts" WHERE "userId" IN (SELECT "userId" FROM "blockedUsers")
```

The sub-query runs as part of the same SQL statement — one round-
trip, the DB optimiser decides whether to materialise the inner set
or use a hash semi-join.

## `inSubquery` / `notInSubquery`

Both expect the sub-query to project a **single column** (use
`.select('colName')` on the inner query). The predicate matches rows
whose specified outer column appears (or doesn't) in the inner
result set.

```ts
// Not in
const visibleUsers = await ctx.store.query(
  queryFor(database.users)
    .where(notInSubquery('id', queryFor(database.blockedUsers).select('userId')))
    .descriptor,
)
```

**Empty inner set semantics:** `inSubquery` against an empty set
matches NOTHING (no row's column is "in" an empty list).
`notInSubquery` against an empty set matches EVERYTHING (every row's
column is "not in" an empty list). The framework matches SQL
exactly.

## `exists` / `notExists`

Doesn't bind to a specific outer column — the predicate's truth
depends only on whether the sub-query produces any rows at all.

```ts
import { exists, queryFor } from '@voltro/database'

// Users who have at least one post
const authors = await ctx.store.query(
  queryFor(database.users)
    .where(exists(queryFor(database.posts).where(eq('userId', 'placeholder'))))
    .descriptor,
)
```

`exists` is typically faster than `inSubquery` when the inner set is
large but you only need yes-or-no — the DB stops after the first
match.

## Composing with `and` / `or`

Sub-query predicates compose with the regular boolean combinators:

```ts
import { and, or, eq, inSubquery } from '@voltro/database'

queryFor(database.users).where(
  and(
    eq('tenantId', tenantId),
    inSubquery('id', queryFor(database.bannedUsers).select('userId')),
  ),
)
```

## Limitations (v1)

Non-correlated only. The inner query can NOT reference outer-row
columns like `WHERE inner.userId = users.id`. For correlated
sub-queries (a common shape: "user who has at least one post created
in the last hour") use a [Self-join](/docs/database/self-joins) or
an [Eager-load](/docs/database/joins#eager-loading) — both can
express the same query without the correlation reference.

## Reactivity

Sub-query predicates ARE reactive. The matcher tracks BOTH the outer
descriptor's table AND every sub-query's table. A write to the inner
table re-evaluates the outer query.

In the in-memory store the framework pre-materialises every
sub-query before evaluating the outer predicate (one pass per
sub-query, not per row). Same fast-path applies to SQL stores via
the standard `IN (SELECT ...)` query plan.

## Cross-dialect

`IN (SELECT ...)` and `EXISTS (SELECT ...)` are standard SQL on
every dialect we ship. No per-dialect dispatch.

## See also

- [Aggregations](/docs/database/aggregations) — sub-queries paired
  with `count()` etc. for "count of X where Y belongs to Z"
- [Self-joins](/docs/database/self-joins) — when the relationship
  can be expressed as a join instead
- [CTEs](/docs/database/query-builder#ctes) — for naming a
  sub-query you reuse multiple times in the same outer query



---

<!-- source: en/database/distinct.md -->
## DISTINCT + DISTINCT ON

_Dedupe row sets. .distinct() is universal; .distinctOn() picks one row per group._

`.distinct()` dedups the result row set. `.distinctOn([cols])` picks
ONE row per unique value of the listed columns — the "latest per
channel" / "best per user" pattern.

## `.distinct()` — basic dedup

```ts
await ctx.store.query(
  queryFor(database.messages)
    .select('userId')
    .distinct()
    .descriptor,
)
// → distinct user IDs that have any message
```

Cross-dialect: standard SQL, supported on every dialect we ship.

## `.distinctOn([cols])` — pick the first per group

```ts
// Latest message per channel
await ctx.store.query(
  queryFor(database.messages)
    .distinctOn(['channelId'])
    .orderBy('channelId', 'asc')
    .orderBy('createdAt', 'desc')
    .descriptor,
)
```

The order matters: postgres picks the FIRST row per
`distinctOn`-column combination AS DETERMINED BY THE FULL `orderBy`.
Always set:

1. The `distinctOn` columns first in `orderBy`
2. The tie-breaker column second (which row to pick when multiple
   match — typically a timestamp or id)

Without that order, postgres still picks one row but the choice is
unstable.

## Cross-dialect

- **Postgres** — native `DISTINCT ON` clause.
- **MySQL / MariaDB / MSSQL / SQLite** — no native `DISTINCT ON`.
  The framework falls back to plain `DISTINCT` and logs no warning
  because the fallback covers most use cases. If you specifically
  need one-row-per-group semantics on these dialects, use a
  [window function](/docs/database/aggregations#window-functions)
  with `ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)` and
  filter to `rank = 1` in a sub-query.

## When NOT to use `.distinct()`

Distinct on a row set with multiple non-unique columns is rarely
what you want — it dedups by the full row shape, including columns
you might not have intended to constrain. If you want one row per
some-key, use `.distinctOn([key])` (postgres) or `groupBy([key])`
with `aggregate({})` to be explicit.

## See also

- [Aggregations](/docs/database/aggregations) — `.groupBy()` + window
  functions for cases distinct can't express
- [Sub-queries](/docs/database/sub-queries) — `notInSubquery` for
  "rows not appearing elsewhere"



---

<!-- source: en/database/self-joins.md -->
## Self-joins + aliased projections

_Join a table to itself or to a CTE. selectJoined({...}) for aliased projections with full TypeScript inference._

When you need to reference the same table twice in one query —
parent/child trees, before/after comparisons, follower/followee
graphs — use `.as(alias)` + `.innerJoin(table, alias, on)`.

For non-self joins (a table joined to a DIFFERENT table) the
preferred pattern is [eager-loading via relations](/docs/database/joins).
This page covers the cases where eager-load doesn't fit:
self-joins, joining against a CTE, or joining against a dynamically-
named table.

## Quick start — self-join

```ts
import { eq, queryFor } from '@voltro/database'

// Posts and their parent posts
const tree = await ctx.store.query(
  queryFor(database.posts).as('children')
    .innerJoin(database.posts, 'parents', eq('parents.id', 'children.parentId'))
    .selectJoined({
      childId:     'children.id',
      childTitle:  'children.title',
      parentTitle: 'parents.title',
    })
    .descriptor,
)
// rows: Array<{ childId: string; childTitle: string; parentTitle: string }>
```

Compiles to:

```sql
SELECT
  "children"."id"    AS "childId",
  "children"."title" AS "childTitle",
  "parents"."title"  AS "parentTitle"
FROM "posts" AS "children"
INNER JOIN "posts" AS "parents" ON "parents"."id" = "children"."parentId"
```

## `.as(alias)` — aliasing the FROM table

`.as('children')` emits `FROM "posts" AS "children"`. Required when
combining with `.innerJoin(...)` so the JOIN's ON condition can
disambiguate columns from both sides.

## `.innerJoin()` vs `.leftJoin()`

Same shape, different semantics:

- `.innerJoin(target, alias, on)` — drops rows from the FROM side
  that have no JOIN-side match.
- `.leftJoin(target, alias, on)` — keeps rows from the FROM side
  even when no JOIN-side match exists. The aliased row's columns
  will be `null` for those rows.

Both have **two overloads**:

### Pass a `Table` descriptor → full type inference

```ts
queryFor(database.posts).as('children')
  .innerJoin(database.posts, 'parents', eq('parents.id', 'children.parentId'))
  .selectJoined({
    parentTitle: 'parents.title',          // ← type inferred as string
  })
// rows[0].parentTitle is typed `string`, not `unknown`
```

The framework tracks the joined Table's row type in a hidden `Joins`
type parameter. `.selectJoined({...})` reads it to resolve each
`'alias.column'` source to the joined column's actual type.

### Pass a string name → opaque type fallback

```ts
queryFor(database.posts).as('children')
  .innerJoin('some_cte', 'cte', eq('cte.userId', 'children.userId'))
  .selectJoined({
    fromCte: 'cte.value',                  // ← type stays `unknown`
  })
```

Use the string form for CTE references (the framework doesn't have
the CTE's row type at compile time) or when joining against a name
that's only known at runtime.

## `.selectJoined({...})` — picking columns

The chain method has TWO mutually-exclusive projection forms:

- `.select('col1', 'col2')` — for FROM-side rows only. Result row
  type narrows to `Pick<RowOf, 'col1' | 'col2'>`.
- `.selectJoined({outKey: 'alias.col', ...})` — for queries with
  joins. Each spec entry maps a `'<alias>.<column>'` source to an
  output key on the result row.

Mixing isn't supported; pick one. The framework defaults to
`SELECT *` when neither is set (returns the FROM-side row).

## Reading without `.selectJoined`

If you skip `.selectJoined`, the result row stays the FROM-side row
type. The joined columns ARE on the row at runtime (postgres returns
them flat with `alias.col` keys) but TypeScript doesn't see them.
For ad-hoc reads:

```ts
const rows = await ctx.store.query(
  queryFor(database.posts).as('c')
    .innerJoin(database.posts, 'p', eq('p.id', 'c.parentId'))
    .descriptor,
)
// rows[0] is typed Post
// rows[0]['p.title'] exists at runtime but needs an explicit cast
```

Use `.selectJoined({...})` for the typed path; the cast escape-hatch
is for one-off reads.

## Cross-dialect

Standard SQL — `INNER JOIN ... AS ... ON ...` is supported on every
dialect we ship with identical syntax.

## When NOT to use this

- **Joining different tables related via FK** — use
  [eager-loading](/docs/database/joins) with relations + `.with({})`.
  More ergonomic, gives you the nested-object result shape, and
  reactively subscribes to the joined tables.
- **Walking trees more than 1 level deep** — use a
  [recursive CTE](/docs/database/recursive-cte). Self-join only
  covers parent + immediate child.

## See also

- [Joins (eager-load)](/docs/database/joins) — the preferred way
  for relations-based joins
- [Recursive CTE](/docs/database/recursive-cte) — for multi-level
  tree traversal
- [Sub-queries](/docs/database/sub-queries) — for the
  "rows where column matches another query" pattern



---

<!-- source: en/database/aggregations.md -->
## Aggregations (count / sum / avg / min / max + GROUP BY + window functions)

_On-demand counts, sums, group-by, having, and window functions — without escaping to raw SQL._

The query builder's `.count()` / `.aggregate({...})` / `.groupBy()` /
`.having()` covers nearly every analytic query a typical SaaS app
needs without raw SQL. Window functions (`rowNumber`, `rank`, `lag`,
`lead`, `sumOver`) live in the same surface for percentile-style and
running-total reads.

These are **on-demand reads** — they run when called, not on a
schedule. For pre-computed read models that refresh periodically see
[Aggregates](/docs/data/aggregates) (the `*.aggregate.ts` file
convention).

Live — an on-demand aggregation as a computed reactive query: add or toggle a
todo and the `{ open, done, total }` counts update with no refetch:

```tsx
const stats = useSubscription('app', 'todos.stats')   // reactive count roll-up
```

## Quick start

```ts
import { count, sum, avg, max, eq, queryFor } from '@voltro/database'

// How many open todos does this user have right now?
const rows = await ctx.store.query(
  queryFor(database.todos).where(eq('done', false)).count().descriptor,
)
const open = rows[0]!.count        // → number
```

Result rows from any aggregate are always **an array with one entry
per group** (or exactly one entry when there's no `groupBy`). Reach
in via `[0]` for the ungrouped case.

## `count()` — the most common case

```ts
// Count every row in the filtered set
queryFor(database.posts).where(eq('userId', uid)).count()

// Count distinct values of a column
import { countDistinct } from '@voltro/database'
queryFor(database.posts).aggregate({
  authors: countDistinct('userId'),
})
```

`count()` defaults to `COUNT(*)` — every matching row, including
NULLs. `countDistinct(column)` emits `COUNT(DISTINCT column)`.

## `aggregate({...})` — bundle multiple aggregates in one query

```ts
import { count, sum, avg, max, min } from '@voltro/database'

const rows = await ctx.store.query(
  queryFor(database.orders).where(eq('orgId', oid)).aggregate({
    total:  sum('amount'),
    avgAmt: avg('amount'),
    peak:   max('createdAt'),
    earliest: min('createdAt'),
    cnt:    count(),
  }).descriptor,
)

const stats = rows[0]!
// { total: 12_345, avgAmt: 89.5, peak: Date, earliest: Date, cnt: 138 }
```

The keys of the spec map become the column names on the result row.
Each value is a helper:

| Helper                          | SQL                       | Returns |
|---------------------------------|---------------------------|---------|
| `count()`                       | `COUNT(*)`                | number  |
| `count('col')`                  | `COUNT(col)` (non-null)   | number  |
| `countDistinct('col')`          | `COUNT(DISTINCT col)`     | number  |
| `sum('col')`                    | `SUM(col)`                | number / null  |
| `avg('col')`                    | `AVG(col)`                | number / null  |
| `min('col')`                    | `MIN(col)`                | column type / null |
| `max('col')`                    | `MAX(col)`                | column type / null |

**SQL semantics on empty result sets**: `count` returns 0;
`sum/avg/min/max` return `null`. The framework matches this — don't
write `if (rows.length === 0)` defensive code, the row is always
there.

## Fast path: `.exists()` for "is there any?"

When you only need a yes/no, avoid `count() > 0` — `.exists()`
short-circuits with `SELECT 1 ... LIMIT 1`:

```ts
const rows = await ctx.store.query(
  queryFor(database.users).where(eq('email', e)).exists().descriptor,
)
const emailTaken = rows[0]!.exists      // → boolean
```

On a 10M-row table this is the difference between an index-only scan
that stops at the first match and a full count.

## `groupBy(cols)` — one row per group

```ts
const rows = await ctx.store.query(
  queryFor(database.orders).where(eq('orgId', oid))
    .groupBy(['status'])
    .aggregate({ cnt: count(), total: sum('amount') })
    .descriptor,
)
// rows: [{ cnt: 12, total: 4500 }, { cnt: 5, total: 1800 }] — one row per group
```

> **Project the group key with `column()`.** By default the result rows
> carry the **aggregate aliases only**. To fold a `GROUP BY` key column
> into the same row, add a `column(name)` entry to the spec — it must also
> appear in `.groupBy([...])` (standard SQL):
>
> ```ts
> import { column, count, sum } from '@voltro/database'
>
> queryFor(database.orders).where(eq('orgId', oid))
>   .groupBy(['status'])
>   .aggregate({ status: column<string>('status'), cnt: count(), total: sum('amount') })
> // rows: [{ status: 'open', cnt: 12, total: 4500 }, …]
> ```
> Annotate the type (`column<string>(...)`) for a precise result row, or
> rely on the default `string | number | boolean | Date | null` union.

Chained `.groupBy()` calls append columns — group by `orgId × status`:

```ts
queryFor(database.orders)
  .groupBy(['orgId'])
  .groupBy(['status'])
  .aggregate({ cnt: count() })
```

Group-by works without `aggregate()` too (returns distinct combos),
but the typical pattern is grouping + aggregating together.

## `having(predicate)` — filter groups after aggregation

`having` predicates run AFTER aggregation; they reference aggregate
aliases, not raw columns. Distinct from `.where()` (which becomes
`WHERE` and runs BEFORE grouping):

```ts
import { gt } from '@voltro/database'

interface PostCount { userId: string; postCount: number }

// "How many users posted more than 10 times in the last 30 days"
const heavy = await ctx.store.query(
  queryFor(database.posts)
    .where(gt('createdAt', daysAgo(30)))            // WHERE — pre-aggregate
    .groupBy(['userId'])
    .having(gt<PostCount, 'postCount'>('postCount', 10))   // HAVING — post-aggregate
    .aggregate({ postCount: count() })
    .descriptor,
)
// rows: [{ postCount: 14 }, { postCount: 11 }, …] — one per qualifying user
```

Chained `.having()` AND-merges, same as `.where()`.

## Window functions

Window functions compute per-row aggregates over a "window" of rows
without collapsing the row set — different from `groupBy`, which
collapses. Use for ranking, running totals, "previous-row" deltas.

### `rowNumber()` / `rank()` / `denseRank()`

```ts
import { rank, queryFor } from '@voltro/database'

const ranked = await ctx.store.query(
  queryFor(database.players).aggregate({
    rank: rank().over({
      partitionBy: ['teamId'],
      orderBy:     [{ column: 'score', direction: 'desc' }],
    }),
  }).descriptor,
)
// Each row: { rank } — rank restarts at 1 per team
```

> **Note:** to carry a plain column (`id`, `name`) alongside the window
> expression, add a `column(name)` entry to the spec — it projects
> `"col" AS "alias"` into the same result row.

`rowNumber()` always returns sequential 1, 2, 3 — ties get distinct
numbers. `rank()` gives ties the same number, then skips
(1, 1, 3, 4…). `denseRank()` gives ties the same number, no skip
(1, 1, 2, 3…).

### `lag()` / `lead()` — previous / next row

```ts
import { lag, lead } from '@voltro/database'

queryFor(database.scores).aggregate({
  prevScore:  lag('score').over({ partitionBy: ['userId'], orderBy: [{ column: 'createdAt', direction: 'asc' }] }),
  nextScore:  lead('score').over({ partitionBy: ['userId'], orderBy: [{ column: 'createdAt', direction: 'asc' }] }),
})
```

Both accept an optional `offset` — `lag('score', 3)` looks 3 rows
back.

### `sumOver()` / `avgOver()` — running totals

```ts
import { sumOver } from '@voltro/database'

queryFor(database.ledger).aggregate({
  runningSum: sumOver('amount').over({
    orderBy: [{ column: 'createdAt', direction: 'asc' }],
  }),
})
// Each row: running total of `amount` up to and including this row
```

Combine `partitionBy` + `orderBy` for per-group running totals
("total spend per user, sorted by date").

## Reactivity

`count()` and `aggregate()` subscriptions ARE reactive. The
dispatcher subscribes to the source table; any write triggers a
re-run of the same aggregate SQL.

Window-function queries are reactive — coarsely. The matcher can't
bucket on a window result (a rank shifts when ANY partition peer
changes), so the engine widens the dependency to the whole source
table: a write to it re-runs the query. That's correct but coarser
than a plain query's per-field pre-filter — every write to the source
table re-evaluates the window. Keep the source set bounded (a top-N
leaderboard, not a 10M-row scan), or materialise it on a schedule with
an [aggregate](/docs/data/aggregates).

## Cross-dialect

Standard SQL — postgres, mysql 8+, mariadb 10.2+, mssql, sqlite 3.25+, turso all support these forms with identical syntax. The framework's
compiler doesn't dispatch per dialect for any of the helpers above.

## See also

- [Aggregates (scheduled)](/docs/data/aggregates) — `*.aggregate.ts`
  for precomputed read models that refresh periodically
- [Query builder](/docs/database/query-builder) — the chain methods
  these aggregates compose with (where, orderBy, limit, etc.)
- [Sub-queries](/docs/database/sub-queries) — `inSubquery` /
  `exists` for predicates that reference other tables
- [Set operations](/docs/database/set-operations) — UNION / INTERSECT /
  EXCEPT for combining multiple aggregates



---

<!-- source: en/database/views.md -->
## SQL views

_Declare a read-only SQL VIEW with view(name, columns, select) — discovered + applied by the migrator, queried by-name like a table._

A **view** is a named, server-side `SELECT` you query by name exactly like a table, but which is never written to. Declare one with `view(name, columns, select)`: `columns` describes the projected row shape (types the result + drives the read decoder), and `select` is the raw SELECT body emitted verbatim into `CREATE VIEW`.

```ts
import { view, id, text, boolean, timestamp } from '@voltro/database'

export const activeUsers = view(
  'active_users',
  {
    id:        id(),
    email:     text(),
    active:    boolean(),
    createdAt: timestamp(),
  },
  `SELECT id, email, active, created_at AS "createdAt"
     FROM users
    WHERE deleted_at IS NULL`,
)
```

The migrator discovers the view alongside your tables and emits it **after** the base tables it reads from — no ordering wiring needed. Pass it into the schema entity list the same way you pass tables.

## Querying a view

A view is read-only. Query it with `queryForView(...)`, which returns a query with the full `.where(...)` / `.orderBy(...)` / `.take(...)` / `.with(...)` surface but **no** mutation path (there's no INSERT/UPDATE/DELETE on a view).

```ts
import { queryForView, eq } from '@voltro/database'

const rows = await ctx.store.query(
  queryForView(activeUsers).where(eq('email', someEmail)),
)
```

The projected columns are decoded to their canonical JS shapes — a `boolean()` projection comes back a real boolean, a `json<T>()` projection a parsed object, a `decimal()` projection a string — the same read codec that runs for tables.

## Idempotent per dialect

`CREATE VIEW` is emitted idempotently so re-running a migration is a no-op:

| Dialect | Emission |
|---|---|
| Postgres / MySQL / MariaDB | `CREATE OR REPLACE VIEW` |
| MSSQL | `CREATE OR ALTER VIEW` (SQL Server 2016 SP1+) |
| SQLite / Turso | `DROP VIEW IF EXISTS` + `CREATE VIEW` (no `CREATE OR REPLACE`; a view holds no data, so dropping is free) |

## When to use a view

- Collapse a recurring filter/join into a named entity your handlers query directly (`active_users`, `open_orders`).
- Expose a stable read shape while the underlying tables evolve.
- Hand a reporting/read path a denormalized projection without duplicating the join logic in every query.

You own the SELECT body's cross-dialect portability — the framework emits it verbatim, the same contract as a `raw()` column or an `expressionIndex(...)` expression. Keep to standard SQL, or gate dialect-specific views behind your deployment's known backend.