@ultimat3/entity - npm Package Compare versions

Comparing version 11.3.0 to 12.0.0

src/aggregate-decode.ts

+// Single responsibility: turn the TEXT an aggregate statement returns into the value the column's
+// kind holds — the Postgres driver's half of `aggregate.ts`, opposite `aggregate-fold.ts`.
+//
+// The statement casts every aggregate to `::text` on purpose. `sum(bigint)` is a `numeric` the
+// client hands back as a string anyway, `min(timestamptz)` would arrive as a millisecond `Date`,
+// and pinning all of them to text means exactly one place decides what the value becomes.
+
+import type { AggregateFn } from './aggregate';
+import type { ColumnKind } from './types';
+
+/**
+ * `sum` and `avg` stay decimal TEXT whatever the column was, and that is the point: the sum of a
+ * million `integer` rows is not an `integer`, `Number()` on it loses digits past 2^53, and a
+ * binary float loses cents on a `numeric`. A caller who wants a JS number writes the `Number()`
+ * themselves, where the loss is a decision somebody made.
+ *
+ * `min` and `max` answer the ROW's own type, because the answer is one of the values that went in:
+ * a `timestamptz` back to a `Date` (the row property's type), everything else to the text it
+ * already is — `integer` becomes a `number` because that is what the row holds, and a minimum
+ * cannot exceed a value that already fitted in one.
+ */
+export const decodeAggregate = (fn: AggregateFn, kind: ColumnKind, text: string): unknown => {
+  // Decided by the FUNCTION first: `min('likeCount')` is one of the rows' own values and fits in
+  // whatever they fit in, while `sum('likeCount')` over a million of them does not.
+  if (fn === 'sum' || fn === 'avg') return text;
+  if (kind === 'timestamptz') {
+    const at = new Date(text);
+    return Number.isNaN(at.getTime()) ? null : at;
+  }
+  if (kind === 'integer') {
+    const value = Number(text);
+    return Number.isFinite(value) ? value : text;
+  }
+  return text;
+};

src/aggregate-fold.ts

+// Single responsibility: compute an aggregate from ROWS ALREADY IN HAND — the in-memory driver's
+// half of `aggregate.ts`, split out because that file is the shared RULES (which kinds, which
+// refusals, the decimal arithmetic) and this one is one driver's execution of them.
+//
+// Every path here is exact. A `sum` goes through `sumDecimalText`, not `+`: the rows of a
+// `bigint()` or `decimal()` column are decimal STRINGS, and `Number()` on one loses digits past
+// 2^53 and cents below it — which is the whole reason those columns hand back text.
+
+import type { AggregateFn, MoneyUnit } from './aggregate';
+import { aggregateMinor, assertOneUnit, averageDecimalText, sumDecimalText } from './aggregate';
+import { valueAt } from './cursor';
+import type { EntityCore } from './entity';
+import { compareByKind } from './memory-match';
+import type { ColumnKind, MoneyValue } from './types';
+
+/** A row's value for this aggregate, or `undefined` for the absences SQL does not count. */
+const present = (value: unknown): boolean => value !== null && value !== undefined;
+
+const moneyOf = (value: unknown): MoneyValue | undefined => {
+  if (typeof value !== 'object' || value === null) return undefined;
+  const record = value as Partial<MoneyValue>;
+  return typeof record.minor === 'number' && typeof record.currency === 'string'
+    ? (record as MoneyValue)
+    : undefined;
+};
+
+/** The text form a decimal aggregate adds. `integer` rows are numbers; every other kind is text. */
+const decimalText = (value: unknown): string =>
+  typeof value === 'bigint' ? value.toString() : String(value);
+
+/**
+ * `min`/`max` by the column's DECLARED kind, never by the JS type in hand — the rule this package
+ * decides every comparison with. `compareByKind` is the same function the sort and the keyset seek
+ * read, so a minimum here is the row a `.orderBy(col, 'asc').one()` would have answered with.
+ */
+const extreme = (kind: ColumnKind, values: readonly unknown[], fn: 'min' | 'max'): unknown =>
+  values.reduce((best, value) => {
+    const order = compareByKind(kind, value, best);
+    return (fn === 'min' ? order < 0 : order > 0) ? value : best;
+  });
+
+/**
+ * The aggregate, over exactly the rows the caller's predicate matched. `null` for an empty set in
+ * every function, because that is what SQL answers — never `0`, which would claim rows were seen.
+ */
+export const foldAggregate = <Row>(
+  entity: EntityCore<Row>,
+  fn: AggregateFn,
+  property: string,
+  kind: ColumnKind,
+  rows: readonly Row[],
+): unknown => {
+  const values = rows.map((row) => valueAt(row, property)).filter(present);
+  if (values.length === 0) return null;
+  if (kind === 'money') {
+    const amounts = values.flatMap((value) => {
+      const money = moneyOf(value);
+      return money === undefined ? [] : [money];
+    });
+    if (amounts.length === 0) return null;
+    const unit = assertOneUnit(
+      entity,
+      fn,
+      property,
+      amounts.map((money): MoneyUnit => ({ currency: money.currency, scale: money.scale ?? null })),
+    );
+    if (unit === undefined) return null;
+    const minor =
+      fn === 'sum'
+        ? aggregateMinor(
+            entity,
+            fn,
+            property,
+            sumDecimalText(amounts.map((money) => String(money.minor))) ?? '0',
+          )
+        : (extreme(
+            'integer',
+            amounts.map((money) => money.minor),
+            fn === 'min' ? 'min' : 'max',
+          ) as number);
+    return {
+      minor,
+      currency: unit.currency,
+      ...(unit.scale === null ? {} : { scale: unit.scale }),
+    } satisfies MoneyValue;
+  }
+  if (fn === 'sum') return sumDecimalText(values.map(decimalText));
+  if (fn === 'avg')
+    return averageDecimalText(sumDecimalText(values.map(decimalText)), values.length);
+  return extreme(kind, values, fn);
+};

src/aggregate.ts

+// Single responsibility: what an aggregate MEANS — which column kinds each function may be applied
+// to, what its answer is shaped like, and the exact arithmetic. Both drivers read it from here, so
+// a `sum` against memory means what a `sum` against Postgres means; a rule added to one driver
+// alone is the drift the two-driver split exists to prevent.
+//
+// Nothing here is a float. `sum` and `avg` answer decimal TEXT, money answers `MoneyValue` — the
+// same reason `bigint()` and `decimal()` hand back strings: a `number` loses digits past 2^53 and
+// a binary float loses cents.
+
+import { columnFor } from './column';
+import type { EntityCore } from './entity';
+import { EntityError } from './errors';
+import type { AnyColumn, ColumnKind } from './types';
+
+/** The four, closed. A fifth is a new member here and a new case in both drivers, never one. */
+export type AggregateFn = 'sum' | 'avg' | 'min' | 'max';
+
+/**
+ * Which kinds each function may be applied to. Closed sets rather than "whatever Postgres accepts",
+ * because the bar is what BOTH drivers can answer identically.
+ *
+ * `text` and `char` are deliberately absent from `min`/`max` even though Postgres has them: text
+ * ordering there is the database's COLLATION and here it is JS's UTF-16 code-unit order, and the
+ * two disagree on ordinary data (`'a' < 'B'` under `en_US`, `'B' < 'a'` by code unit). A comparison
+ * this package cannot make agree is refused rather than answered twice differently — the same
+ * decision `memory-match.ts` records for decimal text, in the other direction.
+ *
+ * `boolean`, `uuid`, `jsonb`, `array` and `bytea` are absent everywhere: none of them has an
+ * ordering or a sum a caller would mean.
+ */
+const NUMERIC: readonly ColumnKind[] = ['integer', 'bigint', 'numeric'];
+const ORDERED: readonly ColumnKind[] = ['integer', 'bigint', 'numeric', 'timestamptz', 'date'];
+
+const ALLOWED = new Map<AggregateFn, ReadonlySet<ColumnKind>>([
+  ['sum', new Set<ColumnKind>([...NUMERIC, 'money'])],
+  ['avg', new Set<ColumnKind>(NUMERIC)],
+  ['min', new Set<ColumnKind>([...ORDERED, 'money'])],
+  ['max', new Set<ColumnKind>([...ORDERED, 'money'])],
+]);
+
+export const aggregatable = (fn: AggregateFn, kind: ColumnKind): boolean =>
+  ALLOWED.get(fn)?.has(kind) === true;
+
+/**
+ * `avg` over money is refused rather than rounded. The average of 1, 1 and 2 minor units is 4/3 of
+ * a unit, and every representable answer is a rounding — which is the defect `MoneyValue.scale`
+ * exists to prevent, so inventing one at the aggregate would reopen it one layer up. The caller
+ * decides the rounding, out of two exact numbers.
+ */
+export const notAggregatable = (
+  entityName: string,
+  fn: AggregateFn,
+  property: string,
+  kind: ColumnKind,
+  candidates: readonly string[],
+): EntityError =>
+  new EntityError({
+    code: 'X_AGGREGATE_UNSUPPORTED',
+    cause:
+      fn === 'avg' && kind === 'money'
+        ? `${entityName}.avg('${property}') — the mean of an integer number of minor units is not one, and every answer would be a silent rounding`
+        : `${entityName}.${fn}('${property}') — a ${kind} column has no ${fn} both drivers can answer the same way`,
+    fix:
+      fn === 'avg' && kind === 'money'
+        ? `${entityName}.sum('${property}') and ${entityName}.count() — divide at the call site, where the rounding is a decision somebody made`
+        : candidates.length === 0
+          ? `x entities describe ${entityName} --json   # this entity declares no column ${fn} can be applied to`
+          : `${entityName}.${fn}('${candidates[0]}')   # ${fn} takes one of: ${candidates.join(', ')}`,
+  });
+
+/**
+ * Money crossing currencies has no sum, no minimum and no maximum: 100 JPY and 100 EUR are not
+ * comparable and adding them answers a number in no currency at all. Both drivers count the
+ * distinct currencies of the rows they are about to aggregate and refuse past one, rather than
+ * silently answering in whichever currency happened to come first.
+ */
+export const mixedCurrency = (
+  entityName: string,
+  fn: AggregateFn,
+  property: string,
+  currencies: readonly string[],
+): EntityError =>
+  new EntityError({
+    code: 'X_AGGREGATE_MIXED_CURRENCY',
+    cause: `${entityName}.${fn}('${property}') covers ${currencies.length} currencies (${[...currencies].sort().join(', ')}) — they have no common unit`,
+    fix: `${entityName}.andWhere('${property}.currency', 'eq', '${[...currencies].sort()[0]}').${fn}('${property}')   # one currency per call, or countBy('${property}.currency') first`,
+  });
+
+/** Digits only, optionally signed, optionally with a fraction. What `decimal()` hands back. */
+const DECIMAL_TEXT = /^-?\d+(\.\d+)?$/;
+
+/** The scale `avg` answers at, in both drivers. Fixed, so the two cannot round to different places. */
+export const AVG_SCALE = 6;
+
+interface Decimal {
+  readonly units: bigint;
+  readonly scale: number;
+}
+
+const parseDecimal = (text: string): Decimal | undefined => {
+  if (!DECIMAL_TEXT.test(text)) return undefined;
+  const [whole = '0', fraction = ''] = text.split('.');
+  return { units: BigInt(`${whole}${fraction}`), scale: fraction.length };
+};
+
+const rescale = (value: Decimal, scale: number): bigint =>
+  value.units * 10n ** BigInt(scale - value.scale);
+
+const render = (units: bigint, scale: number): string => {
+  if (scale === 0) return units.toString();
+  const negative = units < 0n;
+  const digits = (negative ? -units : units).toString().padStart(scale + 1, '0');
+  const whole = digits.slice(0, digits.length - scale);
+  return `${negative ? '-' : ''}${whole}.${digits.slice(digits.length - scale)}`;
+};
+
+/**
+ * The exact sum of decimal text, at the widest scale any term carries — which is what Postgres'
+ * `sum(numeric)` answers, and what no `Number()` can: `0.1 + 0.2` is `0.30000000000000004` in a
+ * binary float and `0.3` here, and an `int8` past 2^53 keeps every digit.
+ *
+ * `null` for an empty set, exactly as `sum` over no rows is NULL in SQL — never `0`, which would
+ * claim rows were counted.
+ */
+export const sumDecimalText = (values: readonly string[]): string | null => {
+  if (values.length === 0) return null;
+  const parsed = values.map(parseDecimal);
+  if (parsed.some((value) => value === undefined)) return null;
+  const decimals = parsed as readonly Decimal[];
+  const scale = decimals.reduce((widest, value) => Math.max(widest, value.scale), 0);
+  return render(
+    decimals.reduce((total, value) => total + rescale(value, scale), 0n),
+    scale,
+  );
+};
+
+/**
+ * `sum / count`, rounded half away from zero at `AVG_SCALE` — which is what Postgres' `round()`
+ * does to a `numeric`, and the reason the statement asks for `round(avg(...), 6)` rather than the
+ * server's own default scale: two drivers rounding at different places answer two numbers.
+ */
+export const averageDecimalText = (total: string | null, count: number): string | null => {
+  if (total === null || count === 0) return null;
+  const parsed = parseDecimal(total);
+  if (parsed === undefined) return null;
+  // The EXACT rational, rounded once. Dividing first and rounding after would truncate digits the
+  // rounding decision depends on — and rescaling to a fixed number of digits rather than to
+  // AVG_SCALE is what made this answer 11000.000000 where Postgres said 1.100000: `rescale` takes
+  // an absolute target scale, and the first draft handed it a relative one.
+  const digits = Math.max(parsed.scale, AVG_SCALE);
+  const numerator = rescale(parsed, digits);
+  const denominator = BigInt(count) * 10n ** BigInt(digits - AVG_SCALE);
+  const negative = numerator < 0n;
+  const magnitude = negative ? -numerator : numerator;
+  // `(2m + d) / 2d` is `floor(m/d + 1/2)` in integers — half AWAY FROM ZERO once the sign is put
+  // back, which is what Postgres' `round(numeric)` does. No float, no intermediate truncation.
+  const rounded = (2n * magnitude + denominator) / (2n * denominator);
+  return render(negative ? -rounded : rounded, AVG_SCALE);
+};
+
+/**
+ * The column an aggregate runs over, resolved and judged before either driver builds anything —
+ * so `sum('title')` is refused with the same words and the same code whichever driver is attached.
+ */
+export const aggregateColumnOf = <Row>(
+  entity: EntityCore<Row>,
+  fn: AggregateFn,
+  property: string,
+): AnyColumn => {
+  const candidates = Object.entries(entity.$columns)
+    .filter(([, each]) => aggregatable(fn, each.$meta.kind))
+    .map(([name]) => name);
+  const column = columnFor(entity.$columns, property);
+  if (column === undefined) {
+    throw notAggregatable(entity.$name, fn, property, 'text', candidates);
+  }
+  if (!aggregatable(fn, column.$meta.kind)) {
+    throw notAggregatable(entity.$name, fn, property, column.$meta.kind, candidates);
+  }
+  return column;
+};
+
+/** One amount is one currency at one scale. Two of either have no common unit. */
+export interface MoneyUnit {
+  readonly currency: string;
+  readonly scale: number | null;
+}
+
+export const assertOneUnit = <Row>(
+  entity: EntityCore<Row>,
+  fn: AggregateFn,
+  property: string,
+  units: readonly MoneyUnit[],
+): MoneyUnit | undefined => {
+  const seen = new Map<string, MoneyUnit>();
+  for (const unit of units) seen.set(`${unit.currency}/${unit.scale ?? ''}`, unit);
+  if (seen.size > 1) {
+    throw mixedCurrency(entity.$name, fn, property, [...seen.values()].map(unitLabel));
+  }
+  return [...seen.values()][0];
+};
+
+/**
+ * What the refusal names. The scale rides along because it is half of what makes two amounts
+ * incomparable: `{ minor: 5, currency: 'USD' }` is five cents and `{ minor: 5, currency: 'USD',
+ * scale: 6 }` is five millionths of a dollar, and adding them is a 10,000x error with no symptom.
+ */
+const unitLabel = (unit: MoneyUnit): string =>
+  unit.scale === null ? unit.currency : `${unit.currency}@${unit.scale}`;
+
+/**
+ * The minor unit an aggregate answers with, narrowed exactly where every other reader of that
+ * column narrows it. The column is `bigint` and `MoneyValue.minor` is a `number`, so a total past
+ * ±2^53 is a REFUSAL and never a rounded amount — the same bound `parseMinor` applies to one row,
+ * applied to the sum of many, where it is far easier to reach.
+ */
+export const aggregateMinor = <Row>(
+  entity: EntityCore<Row>,
+  fn: AggregateFn,
+  property: string,
+  total: string,
+): number => {
+  const units = BigInt(total);
+  if (units <= BigInt(Number.MAX_SAFE_INTEGER) && units >= BigInt(-Number.MAX_SAFE_INTEGER)) {
+    return Number(units);
+  }
+  throw new EntityError({
+    code: 'X_AGGREGATE_UNSUPPORTED',
+    cause: `${entity.$name}.${fn}('${property}') is ${total} minor units, past ±2^53 — no JS number holds it and MoneyValue.minor is one`,
+    fix: `${entity.$name}.andWhere(…).${fn}('${property}')   # narrow the rows, or read the total as text with a hand-written statement`,
+  });
+};

src/containment.ts

+// Single responsibility: what `@>`, `<@`, `&&` and a JSON key test MEAN, written once so the
+// in-memory driver answers what Postgres answers. A `jsonb` or an `arrayOf()` column was declared
+// and then unfilterable — the ten-operator vocabulary had nothing that could look inside one — so
+// an app with either had to leave the query language for hand-written SQL, which is the one path
+// in this framework with no tenancy guard on it.
+//
+// Every rule here is Postgres', reproduced rather than approximated. Where the two could not be
+// made to agree the operator is refused instead (`memory-match.ts`), never guessed at.
+
+/** Absent and NULL are one thing, exactly as they are to every other predicate. */
+const isNull = (value: unknown): boolean => value === null || value === undefined;
+
+const isRecord = (value: unknown): value is Readonly<Record<string, unknown>> =>
+  typeof value === 'object' && value !== null && !Array.isArray(value);
+
+/** Neither an object nor an array — which is exactly the set the top-level exception below covers. */
+const isScalar = (value: unknown): boolean => !isRecord(value) && !Array.isArray(value);
+
+/**
+ * Two ELEMENTS, equal. `===` plus the one case it gets wrong here: an `arrayOf(timestamp())` row
+ * holds `Date` objects, and two Dates for the same instant are two references — the same trap
+ * `sameValueOfKind` closes for a predicate, one operator along.
+ *
+ * Nothing deeper, and that is a property rather than an omission: `arrayOf()` refuses `jsonb`,
+ * `bytea`, `money` and a nested array at declaration, so an element is always a scalar or a Date.
+ */
+const sameElement = (left: unknown, right: unknown): boolean => {
+  if (left instanceof Date && right instanceof Date) return left.getTime() === right.getTime();
+  return left === right;
+};
+
+/**
+ * `left @> right` for `jsonb`, to the letter of Postgres' definition — each clause below measured
+ * on Postgres 16 rather than read off a summary, because three of them are easy to state wrongly:
+ *
+ * - two objects: every key of `right` is present in `left` and its value is contained by the one
+ *   there — recursively, which is what makes `data @> '{"a":{"b":1}}'` a NESTED match and why this
+ *   package ships no second path-expression language beside it (axiom 1).
+ * - two arrays: every element of `right` is contained by SOME element of `left`, which is why
+ *   `[1,2,3] @> [3,1]` holds and order never matters.
+ * - an array on the left and a PRIMITIVE on the right, **at the top level only**: `'[1,2]' @> '2'`
+ *   is true. Both halves of that sentence are load-bearing and both were wrong here first —
+ *   `'{"list":[1,2,3]}' @> '{"list":2}'` is FALSE (the exception does not recurse) and
+ *   `'[{"a":1}]' @> '{"a":1}'` is FALSE (it does not extend to composites).
+ * - anything else: element equality, which for a jsonb scalar is what it sounds like.
+ */
+export const jsonContains = (left: unknown, right: unknown): boolean => contains(left, right, true);
+
+const contains = (left: unknown, right: unknown, top: boolean): boolean => {
+  if (Array.isArray(left)) {
+    if (Array.isArray(right)) {
+      return right.every((item) => left.some((candidate) => contains(candidate, item, false)));
+    }
+    return top && isScalar(right) && left.some((candidate) => sameElement(candidate, right));
+  }
+  if (isRecord(left) && isRecord(right)) {
+    return Object.keys(right).every(
+      (key) => Object.hasOwn(left, key) && contains(left[key], right[key], false),
+    );
+  }
+  return isScalar(left) && isScalar(right) && sameElement(left, right);
+};
+
+/**
+ * `left @> right` for a SQL array, which is a different operator with a different rule: element
+ * containment is plain equality, never the recursive one above, because an array's elements are
+ * scalars of one declared type rather than arbitrary JSON. An empty right-hand side is contained
+ * by every array, which is what Postgres answers.
+ */
+export const arrayContains = (left: readonly unknown[], right: readonly unknown[]): boolean =>
+  right.every((item) => left.some((candidate) => sameElement(candidate, item)));
+
+/**
+ * `left && right`: they share at least one element. Arrays only — `jsonb` has no `&&` — and the
+ * bound is the opposite way round from `@>`: an EMPTY operand overlaps nothing, where it is
+ * contained by everything.
+ */
+export const arrayOverlaps = (left: readonly unknown[], right: readonly unknown[]): boolean =>
+  right.some((item) => left.some((candidate) => sameElement(candidate, item)));
+
+/**
+ * `jsonb_exists(value, key)` — the function form of the `?` operator, which is what the SQL side
+ * emits so a literal `?` can never be read as a parameter placeholder by anything on the way.
+ *
+ * Three shapes, all of them Postgres': a top-level key of an object, a string ELEMENT of an array,
+ * and a string value equal to the key. A number never matches — `jsonb_exists('[1]', '1')` is
+ * false there, and a `String(item) === key` here would have made it true.
+ */
+export const jsonHasKey = (value: unknown, key: unknown): boolean => {
+  if (typeof key !== 'string' || isNull(value)) return false;
+  if (Array.isArray(value)) return value.some((item) => item === key);
+  if (isRecord(value)) return Object.hasOwn(value, key);
+  return value === key;
+};

src/instant.ts

+// Single responsibility: a `timestamptz` value at the precision the COLUMN stores it, rather than
+// the precision a JS `Date` holds. The column keeps microseconds and a `Date` keeps milliseconds,
+// so a page position minted from a decoded row ranks rows differently from the `order by` that
+// produced them. Microseconds since the epoch is the one form both sides can be exact in, and this
+// file is the only place the two representations meet.
+
+/** A `Date` is exactly this much coarser than the column it came out of. */
+const MICROS_PER_MILLI = 1000n;
+
+const MICROS_PER_SECOND = 1_000_000n;
+
+const FRACTION_DIGITS = 6;
+
+/**
+ * What `(col at time zone 'UTC')::text` prints: `2026-01-01 00:00:00.123456`, with the fraction
+ * omitted entirely when every digit of it is zero and TRUNCATED when the trailing ones are —
+ * `.1` is a tenth of a second, not one microsecond, which is why the fraction is padded on the
+ * right and never on the left.
+ *
+ * The year is `\d{4,}` because Postgres prints one wider than four digits unpadded; a `BC` suffix
+ * matches nothing here on purpose, and an unmatched text falls back to the decoded `Date`.
+ */
+const PG_INSTANT_TEXT = /^(\d{4,})-(\d{2})-(\d{2})[ T](\d{2}):(\d{2}):(\d{2})(?:\.(\d{1,6}))?$/;
+
+/**
+ * `Date.UTC` maps years 0–99 into the 1900s, so the epoch is built by assignment instead. Whole
+ * seconds only: the fraction is added in the microsecond domain, where it is exact.
+ */
+const utcSecondMillis = (parts: readonly number[]): number => {
+  const [year = 0, month = 1, day = 1, hour = 0, minute = 0, second = 0] = parts;
+  const at = new Date(0);
+  at.setUTCFullYear(year, month - 1, day);
+  at.setUTCHours(hour, minute, second, 0);
+  return at.getTime();
+};
+
+/** Floor division: `-1n / 2n` truncates toward zero, which would place a pre-1970 instant late. */
+const floorDiv = (value: bigint, by: bigint): bigint => {
+  const remainder = ((value % by) + by) % by;
+  return (value - remainder) / by;
+};
+
+/**
+ * The exact microsecond epoch of a `timestamptz` Postgres rendered as text, or `undefined` when
+ * the text is not one — a caller with nothing to read falls back to the decoded `Date`, which is
+ * the position it always had.
+ */
+export const pgInstantMicros = (text: unknown): bigint | undefined => {
+  if (typeof text !== 'string') return undefined;
+  const match = PG_INSTANT_TEXT.exec(text);
+  if (match === null) return undefined;
+  const [, year = '', month = '', day = '', hour = '', minute = '', second = '', fraction] = match;
+  const millis = utcSecondMillis([year, month, day, hour, minute, second].map(Number));
+  if (!Number.isFinite(millis)) return undefined;
+  // The fraction is always forward in time, so it ADDS even when the second boundary is negative.
+  return BigInt(millis) * MICROS_PER_MILLI + BigInt((fraction ?? '').padEnd(FRACTION_DIGITS, '0'));
+};
+
+/**
+ * The microsecond epoch of whatever a sort key is holding: a decoded row's `Date` (milliseconds,
+ * so the last three digits are zero), a value already counted in microseconds, or the decimal a
+ * cursor carries. `undefined` for anything else, so a caller decides rather than guessing at `0`.
+ */
+export const instantMicros = (value: unknown): bigint | undefined => {
+  if (typeof value === 'bigint') return value;
+  if (value instanceof Date) {
+    const millis = value.getTime();
+    return Number.isNaN(millis) ? undefined : BigInt(millis) * MICROS_PER_MILLI;
+  }
+  if (typeof value === 'string' && /^-?\d+$/.test(value)) return BigInt(value);
+  return undefined;
+};
+
+/**
+ * The instant a seek binds, spelled so Postgres parses it back to the same microsecond. ISO 8601
+ * in UTC with all six fraction digits — `toISOString()` alone is milliseconds, which is the whole
+ * defect this file exists to close, so the fraction is written here rather than read off the
+ * `Date`.
+ */
+export const microsToIso = (micros: bigint): string => {
+  const second = floorDiv(micros, MICROS_PER_SECOND);
+  const fraction = micros - second * MICROS_PER_SECOND;
+  const whole = new Date(Number(second) * 1000).toISOString();
+  return `${whole.slice(0, whole.indexOf('.'))}.${String(fraction).padStart(FRACTION_DIGITS, '0')}Z`;
+};
+
+/**
+ * The output name the microsecond half of a sort key comes back under. Every physical column name
+ * in this framework is lower case — `columnName` is either `snake(property)`, which lower-cases,
+ * or a `.column()` name `assertColumnName` refuses unless it matches `[a-z_][a-z0-9_$]*` — so an
+ * UPPER-CASE suffix is a name no entity can declare and this alias can never shadow a column.
+ */
+export const seekAlias = (physicalColumn: string): string => `${physicalColumn}$US`;

src/memory-repo.ts

+// Single responsibility: the in-memory driver. Same `Repo` contract as `postgresRepo`, the same
+// plans, the same cursor — the only difference is where the rows are, which is the point: a test
+// that passes here means something about Postgres.
+//
+// Split from `repo.ts` when that file passed the 500-line ceiling. What stays there is the
+// CONTRACT — `Repo`, `Page`, `FindManyArgs`, `Transactor` — which `postgresRepo` implements too
+// and which nothing about storing rows in a `Map` belongs in.
+
+import { aggregateColumnOf } from './aggregate';
+import { foldAggregate } from './aggregate-fold';
+import { keyOf } from './batch-read';
+import { conflictKeyOf, conflictKeys, upsertPlan } from './bulk-write';
+import { entityNow } from './clock';
+import { narrowMoney } from './columns';
+import { countsFrom, groupColumnOf } from './count-by';
+import { cursorFor, kindOf, seekFrom, valueAt } from './cursor';
+import { type EntityCore, SOFT_DELETE_COLUMN } from './entity';
+import { notFound } from './errors';
+import { compareByKind, matchesPredicate } from './memory-match';
+import { deletePlan, idPlan, readPlan, singleKeyOf, updatePlan } from './plan';
+import type { FindManyArgs, MemoryRepo, RepoOptions, Transactor, Tx } from './repo';
+import type { QueryPlan } from './tenancy';
+import { assertRowTenant } from './tenancy';
+
+const field = (row: unknown, property: string): unknown =>
+  typeof row === 'object' && row !== null ? (row as Record<string, unknown>)[property] : undefined;
+
+/** Lexicographic over the sort keys, direction applied. `> 0` means "after the cursor". */
+const compareToSeek = <Row>(
+  entity: EntityCore<Row>,
+  plan: QueryPlan,
+  row: unknown,
+  seek: readonly unknown[],
+): number => {
+  for (const [index, entry] of plan.orderBy.entries()) {
+    // The COLUMN's kind, not the value's: the seek was revived from the same kind (`cursor.ts`),
+    // so a `bigint` column compares its stored decimal string against a revived `BigInt` as one
+    // number instead of as two pieces of text.
+    const order = compareByKind(
+      kindOf(entity, entry.column),
+      valueAt(row, entry.column),
+      seek[index],
+    );
+    if (order !== 0) return entry.direction === 'desc' ? -order : order;
+  }
+  return 0;
+};
+
+/**
+ * Where the next page starts. By sort position, not by the previous row's id: that row may have
+ * been deleted between the two requests, and an id that is no longer there would restart
+ * pagination at the top instead of continuing it.
+ */
+const afterCursor = <Row>(
+  entity: EntityCore<Row>,
+  plan: QueryPlan,
+  found: readonly Row[],
+): number => {
+  const seek = seekFrom(entity, plan);
+  if (seek === undefined) return 0;
+  const start = found.findIndex((row) => compareToSeek(entity, plan, row, seek) > 0);
+  return start === -1 ? found.length : start;
+};
+
+/**
+ * The default driver: correct semantics, no database. `x dev` uses it before the first
+ * migration and tests use it everywhere. Postgres is the production driver and implements
+ * this same interface.
+ */
+export const memoryRepo = <Row>(
+  entity: EntityCore<Row>,
+  seed: readonly Row[] = [],
+): MemoryRepo<Row> => {
+  /**
+   * A stored row's key, spelled the way `batch-read.ts` spells an id — because Postgres compares a
+   * `uuid` as a VALUE and prints it lower-cased, so `findById(UPPER)` reads the row there while
+   * `String(...)` missed it here: `null` from a read and `X_NOT_FOUND` from a write, against a row
+   * that exists, reachable from a path parameter, a client-supplied id or a legacy import.
+   */
+  const storeKey = (row: unknown): string =>
+    entity.$primaryKey
+      .map((property) => keyOf(kindOf(entity, property) ?? '', field(row, property)))
+      .join('
');
+  /** The same key, from the id a caller named rather than from a row it has in hand. */
+  const idStoreKey = (id: unknown, operation: string): string =>
+    keyOf(kindOf(entity, singleKeyOf(entity, operation)) ?? '', id);
+  const rows = new Map<string, Row>(seed.map((row) => [storeKey(row), row]));
+
+  const rowsOf = (plan: QueryPlan, args: FindManyArgs): Row[] => {
+    const visible = (row: Row): boolean =>
+      !entity.$softDelete ||
+      args.includeDeleted === true ||
+      field(row, SOFT_DELETE_COLUMN) === null ||
+      field(row, SOFT_DELETE_COLUMN) === undefined;
+    return [...rows.values()]
+      .filter((row) => plan.where.every((predicate) => matchesPredicate(entity, row, predicate)))
+      .filter(visible)
+      .sort((left, right) => {
+        for (const entry of plan.orderBy) {
+          const order = compareByKind(
+            kindOf(entity, entry.column),
+            valueAt(left, entry.column),
+            valueAt(right, entry.column),
+          );
+          if (order !== 0) return entry.direction === 'desc' ? -order : order;
+        }
+        return 0;
+      });
+  };
+
+  const select = (args: FindManyArgs, operation: string): { plan: QueryPlan; found: Row[] } => {
+    const plan = readPlan(entity, args, operation);
+    return { plan, found: rowsOf(plan, args) };
+  };
+
+  const write = (given: Row, options: RepoOptions | undefined, operation: string): Row => {
+    // `MoneyInput` lets a writer hand a `bigint`; a stored row holds the value type. The Postgres
+    // driver narrows in `bindValues` and reads its answer back through `returning *`, so without
+    // this an in-memory row would be the one row in the framework `JSON.stringify` refuses.
+    const row = narrowMoney(entity.$columns, given);
+    // Beside `$assert`, and before the row lands: a write is judged by the tenant it names as well
+    // as by the invariants it declares, and the Postgres driver runs the same pair in `writeRows`.
+    // `update` reaches here with the STORED row merged under its patch, so a patch that moves a row
+    // out of this tenant is refused by the same call that refuses an insert into another one.
+    assertRowTenant(entity.$name, entity.$tenantColumn, operation, row);
+    entity.$assert(row);
+    const key = storeKey(row);
+    const previous = rows.get(key);
+    options?.tx?.onRollback(() => {
+      if (previous === undefined) rows.delete(key);
+      else rows.set(key, previous);
+    });
+    rows.set(key, row);
+    return row;
+  };
+
+  // The same guard the read path applies: on a tenant-scoped entity an id alone is not enough
+  // to name a row, so `update`/`delete` resolve through a plan rather than through the map.
+  const addressed = (id: string, options: RepoOptions | undefined, operation: string): Row => {
+    const plan = idPlan(entity, id, options, operation);
+    const current = rows.get(idStoreKey(id, operation));
+    // A soft-deleted row is hidden from writes too — `delete` on one is `X_NOT_FOUND`, not a
+    // second stamp, which is what the Postgres driver's `deleted_at is null` clause already says.
+    const hidden =
+      current !== undefined &&
+      entity.$softDelete &&
+      field(current, SOFT_DELETE_COLUMN) !== null &&
+      field(current, SOFT_DELETE_COLUMN) !== undefined;
+    if (
+      current === undefined ||
+      hidden ||
+      !plan.where.every((predicate) => matchesPredicate(entity, current, predicate))
+    ) {
+      throw notFound(entity.$name, id);
+    }
+    return current;
+  };
+
+  // Every method is async: a repository call that fails must reject, never throw
+  // synchronously, or half the call sites would need two error paths.
+  return {
+    async findById(id, options) {
+      const { found } = select(
+        { ...options, where: [{ column: singleKeyOf(entity, 'findById'), op: 'eq', value: id }] },
+        'findById',
+      );
+      return found[0] ?? null;
+    },
+
+    async findMany(args = {}) {
+      const { plan, found } = select(args, 'findMany');
+      const start = afterCursor(entity, plan, found);
+      const page = found.slice(start, start + plan.limit);
+      const last = page.at(-1);
+      const more = start + page.length < found.length;
+      return {
+        rows: page,
+        nextCursor:
+          more && last !== undefined ? cursorFor(entity, plan, last, storeKey(last)) : null,
+      };
+    },
+
+    async insert(values, options) {
+      return write(values, options, 'insert');
+    },
+
+    async insertAll(batch, options) {
+      // The whole batch is judged before any of it lands: Postgres refuses the statement as one,
+      // so a row an invariant rejects — or one naming a tenant this actor may not write — must not
+      // leave the rows before it stored here either. `write` re-checks both per row; this loop is
+      // what makes the batch all-or-nothing, which is the half a per-row check cannot give.
+      for (const row of batch) {
+        assertRowTenant(entity.$name, entity.$tenantColumn, 'insertAll', row);
+        entity.$assert(row);
+      }
+      return batch.map((row) => write(row, options, 'insertAll'));
+    },
+
+    async upsertAll(batch, args) {
+      // The INCOMING rows, judged before any of them is matched: under `onMatch: 'nothing'` a
+      // colliding row is skipped and never reaches `write()`, so checking only what lands would
+      // let a row naming another tenant through whenever it happened to collide.
+      for (const row of batch) {
+        assertRowTenant(entity.$name, entity.$tenantColumn, 'upsertAll', row);
+        entity.$assert(row);
+      }
+      const plan = upsertPlan(entity, batch, args.onConflict, args.onMatch ?? 'update');
+      const keys = conflictKeys(entity, plan, batch);
+      // The stored rows under the same key, so "does this collide" is the question the unique
+      // index answers in Postgres and not a scan per row. A soft-deleted row still occupies its
+      // key here, because the index it would collide with there is not partial either — and a row
+      // whose target holds a null occupies none, because the index is `NULLS DISTINCT`.
+      const stored = new Map<string, Row>();
+      for (const row of rows.values()) {
+        const key = conflictKeyOf(entity, plan.on, row);
+        if (key !== undefined) stored.set(key, row);
+      }
+      const written: Row[] = [];
+      for (const [position, row] of batch.entries()) {
+        const key = keys[position];
+        const existing = key === undefined ? undefined : stored.get(key);
+        // `do nothing` writes no row, and `returning *` therefore names none: a skipped row is
+        // absent from the result rather than present and unchanged.
+        if (existing !== undefined && plan.set.length === 0) continue;
+        const merged =
+          existing === undefined
+            ? row
+            : Object.assign(
+                {},
+                existing,
+                Object.fromEntries(plan.set.map((property) => [property, field(row, property)])),
+              );
+        // `UpsertArgs extends RepoOptions`, so the args ARE the options — one bag, and a `tx`
+        // passed to an upsert registers its undo exactly as it does for every other write here.
+        const result = write(merged, args, 'upsertAll');
+        // Filed as it lands, so a later row of the same batch collides with an earlier one exactly
+        // as it would with a row the request stored a moment before it.
+        if (key !== undefined) stored.set(key, result);
+        written.push(result);
+      }
+      return written;
+    },
+
+    async update(id, patch, options) {
+      return write(Object.assign({}, addressed(id, options, 'update'), patch), options, 'update');
+    },
+
+    async delete(id, options) {
+      const current = addressed(id, options, 'delete');
+      // Soft delete hides the row without losing it; the column's presence is the switch.
+      if (entity.$softDelete) {
+        write(Object.assign({}, current, { [SOFT_DELETE_COLUMN]: entityNow() }), options, 'delete');
+        return;
+      }
+      const key = storeKey(current);
+      options?.tx?.onRollback(() => rows.set(key, current));
+      rows.delete(key);
+    },
+
+    async deleteWhere(filter, options) {
+      // `rowsOf` is the read path: the same predicates, the same tenant scoping, and the same
+      // soft-delete visibility. A row already stamped is not matched, so a second call cannot
+      // move `deletedAt` forward — which is what the Postgres driver's `deleted_at is null`
+      // clause says there.
+      const doomed = rowsOf(deletePlan(entity, filter, options, 'deleteWhere'), {});
+      for (const row of doomed) {
+        if (entity.$softDelete) {
+          write(
+            Object.assign({}, row, { [SOFT_DELETE_COLUMN]: entityNow() }),
+            options,
+            'deleteWhere',
+          );
+          continue;
+        }
+        const key = storeKey(row);
+        options?.tx?.onRollback(() => rows.set(key, row));
+        rows.delete(key);
+      }
+      return doomed.length;
+    },
+
+    async updateWhere(filter, patch, options) {
+      const plan = updatePlan(entity, filter, patch, options, 'updateWhere');
+      // The PATCH, judged whole and before the rows are read — the same call `postgresRepo` makes
+      // before its statement exists. Inside the loop below it is judged only where a row was
+      // matched, so a patch handing rows to another tenant was refused or accepted depending on
+      // what the table happened to hold: `updateWhere(filter, { orgId: theirs })` over a filter
+      // matching nothing answered `0` here and threw there, from one call.
+      assertRowTenant(entity.$name, entity.$tenantColumn, 'updateWhere', patch);
+      // `rowsOf` again, so a soft-deleted row is as unreachable here as it is through
+      // `addressed()` — patching a row the app has already deleted is not an update, it is a
+      // resurrection nobody asked for. `write` re-asserts the invariants on each result.
+      const found = rowsOf(plan, {});
+      for (const row of found) write(Object.assign({}, row, patch), options, 'updateWhere');
+      return found.length;
+    },
+
+    async count(args = {}) {
+      return select(args, 'count').found.length;
+    },
+
+    async aggregate(fn, column, args = {}) {
+      // Refused before a row is read, and by the same function the Postgres driver calls: a column
+      // that has no aggregate is that mistake in both drivers or in neither.
+      const declared = aggregateColumnOf(entity, fn, column);
+      const { found } = select(args, fn);
+      return foldAggregate(entity, fn, column, declared.$meta.kind, found);
+    },
+
+    /**
+     * Exact here, and that is the honest answer rather than a shortcut: the estimate exists
+     * because `count(*)` walks every visible row of a real table, and this driver's rows are
+     * already an array whose length is free. Filters are refused in both drivers by `plan.ts`,
+     * so the two still answer the same QUESTION.
+     */
+    async approximateCount(args = {}) {
+      return select(args, 'approximateCount').found.length;
+    },
+
+    async countBy(column, args = {}) {
+      // Refused before a row is read, and by the same function the Postgres driver calls: a column
+      // a map cannot be keyed by is that mistake in both drivers or in neither.
+      groupColumnOf(entity, column, 'countBy');
+      const { found } = select(args, 'countBy');
+      const groups = new Map<unknown, number>();
+      for (const row of found) {
+        // `?? null`, so a property this row never carried lands in the same group Postgres puts a
+        // NULL row in — and `0`, `''` and `false` stay the values they are.
+        const value = field(row, column) ?? null;
+        groups.set(value, (groups.get(value) ?? 0) + 1);
+      }
+      return countsFrom(entity, column, 'countBy', [...groups]);
+    },
+
+    reset() {
+      rows.clear();
+      for (const row of seed) rows.set(storeKey(row), row);
+    },
+  };
+};
+
+let txCounter = 0;
+
+/** In-memory transactor: undo closures registered by drivers run on failure. */
+export const memoryTransactor = (): Transactor => ({
+  async run(work) {
+    const undos: (() => void)[] = [];
+    txCounter += 1;
+    const tx: Tx = { id: `tx-${txCounter}`, onRollback: (undo) => undos.push(undo) };
+    try {
+      return await work(tx);
+    } catch (error) {
+      for (const undo of undos.reverse()) undo();
+      throw error;
+    }
+  },
+});

src/pg-write-sql.ts

+// Single responsibility: compile a WRITE into parameterised SQL — insert, upsert, update, delete.
+// Split from `pg-sql.ts` when that file passed the 500-line ceiling: "which rows does this read
+// describe" and "what does this write put there" are two jobs, and only the second one needs to
+// know what a conflict target or a jsonb cell is.
+//
+// The same rule holds on both sides of the split and it is the reason either file exists: nothing
+// is interpolated. `sql` binds every scalar and every identifier is resolved through the entity,
+// so a column name can only ever be one the entity declared. `raw()` appears once here, for the
+// `default` cell of a many-row `values` list — a closed set of one word, written in this file and
+// never derived from a value.
+
+import { identifier, join, raw, type SqlFragment, sql } from '@ultimat3/db';
+import { columnName } from './column';
+import type { EntityCore } from './entity';
+import { conditions, type ReadShape } from './pg-sql';
+import type { QueryPlan } from './tenancy';
+
+/** `on conflict (…) do update set …`, or `do nothing` when there is nothing to overwrite. */
+export interface ConflictTarget {
+  /** Physical columns of the unique index a collision is judged against. */
+  readonly columns: readonly string[];
+  /** Physical columns a colliding row takes from the incoming one. Empty is `do nothing`. */
+  readonly set: readonly string[];
+}
+
+export interface InsertShape {
+  /** Every physical column written — one list, shared by every row of the statement. */
+  readonly columns: readonly string[];
+  /** How a collision resolves. Absent, it is the caller's error, exactly as it is for one row. */
+  readonly conflict?: ConflictTarget | undefined;
+}
+
+/**
+ * The cell of a row that did not name this column. `default` is the second and last `raw()` in
+ * this file and, like `asc|desc` above it, a closed set of one word: it is what makes a row inside
+ * a many-row `insert` mean what the same row means on its own, where an unnamed column is simply
+ * left out. The seek operator used to be a third — it is chosen in TypeScript now
+ * (`seekAfter`/`seekEqual`), because a timestamp seek is not one operator.
+ */
+const DEFAULT_CELL = raw('default');
+
+const conflictSql = (conflict: ConflictTarget): SqlFragment => {
+  const target = join(conflict.columns.map(identifier));
+  return conflict.set.length === 0
+    ? sql` on conflict (${target}) do nothing`
+    : sql` on conflict (${target}) do update set ${join(
+        conflict.set.map((column) => sql`${identifier(column)} = excluded.${identifier(column)}`),
+      )}`;
+};
+
+/**
+ * The one column that cannot be bound as itself. A `jsonb` value is a plain object, and the
+ * driver seam refuses one as a parameter (`X_SQL_UNSAFE` — `isBoundValue` takes scalars, a `Date`,
+ * a `Uint8Array` and arrays of those); so `bindValues` hands over the JSON TEXT and the cell says
+ * what to do with it.
+ *
+ * `::text::jsonb` and not `::jsonb`, and the double cast is load-bearing rather than defensive.
+ * Measured against Postgres 17.10 through Bun's `sql`: with `$1::jsonb` the server describes the
+ * parameter as `jsonb`, the client JSON-ENCODES the string it was given, and `{"a":1}` is stored
+ * as the JSON *string* `"{\"a\":1}"` — `jsonb_typeof` says `string`. Pinning the parameter to
+ * `text` first makes the client send the characters and the server parse them, which is the one
+ * spelling that stores an object.
+ */
+/** Physical names of this entity's `jsonb` columns. Resolved ONCE per statement, never per cell. */
+const jsonColumns = <Row>(entity: EntityCore<Row>): ReadonlySet<string> => {
+  const names = new Set<string>();
+  for (const [property, column] of Object.entries(entity.$columns)) {
+    if (column.$meta.kind === 'jsonb') names.add(columnName(property, column.$meta));
+  }
+  return names;
+};
+
+/**
+ * `${value}`, plus the cast that column needs. The `raw()` argument is a literal written here and
+ * nowhere else — the audit point that call is stays a two-word constant, never a value.
+ */
+const cell = (json: ReadonlySet<string>, column: string, value: unknown): SqlFragment =>
+  json.has(column) ? sql`${value}${raw('::text::jsonb')}` : sql`${value}`;
+
+/**
+ * One statement for any number of rows. A single row compiles to exactly the text it always did,
+ * which is the point: `insertAll([row])` and `insert(row)` are one code path, so there is no
+ * second insert builder for the two to drift apart in.
+ */
+export const insertStatement = <Row>(
+  entity: EntityCore<Row>,
+  rows: readonly ReadonlyMap<string, unknown>[],
+  shape: InsertShape,
+): SqlFragment => {
+  const json = jsonColumns(entity);
+  const tuples = rows.map(
+    (row) =>
+      sql`(${join(
+        shape.columns.map((column) =>
+          row.has(column) ? cell(json, column, row.get(column)) : DEFAULT_CELL,
+        ),
+      )})`,
+  );
+  const conflict = shape.conflict === undefined ? sql`` : conflictSql(shape.conflict);
+  return sql`insert into ${identifier(entity.$table)} (${join(
+    shape.columns.map(identifier),
+  )}) values ${join(tuples)}${conflict} returning *`;
+};
+
+/**
+ * `returning` is a parameter and has no default, because the three callers want three different
+ * answers and the wrong one is not visible in the result: `update(id, patch)` needs the stored row,
+ * a soft delete and a filtered write need a count, and `returning *` on a filtered write over a
+ * whole tenant streams every matched row into the process for nobody to read. A default would make
+ * that the quiet case.
+ */
+export const updateStatement = <Row>(
+  entity: EntityCore<Row>,
+  plan: QueryPlan,
+  values: ReadonlyMap<string, unknown>,
+  shape: ReadShape,
+  returning: boolean,
+): SqlFragment => {
+  const json = jsonColumns(entity);
+  return sql`update ${identifier(entity.$table)} set ${join(
+    [...values].map(([column, value]) => sql`${identifier(column)} = ${cell(json, column, value)}`),
+  )} where ${conditions(entity, plan, shape)}${returning ? sql` returning *` : sql``}`;
+};
+
+/** Only reached when the entity has no soft-delete column, so there is no filter to apply. */
+export const deleteStatement = <Row>(entity: EntityCore<Row>, plan: QueryPlan): SqlFragment =>
+  sql`delete from ${identifier(entity.$table)} where ${conditions(entity, plan, {
+    includeDeleted: true,
+  })}`;

package.json

 {
   "name": "@ultimat3/entity",
-  "version": "11.3.0",
+  "version": "12.0.0",
   "description": "A table + its domain type + invariants the database also enforces",
@@ -34,7 +34,7 @@ "license": "MIT",
   "dependencies": {
-    "@ultimat3/core": "11.3.0",
-    "@ultimat3/db": "11.3.0",
-    "@ultimat3/schema": "11.3.0",
-    "@ultimat3/time": "11.3.0"
+    "@ultimat3/core": "12.0.0",
+    "@ultimat3/db": "12.0.0",
+    "@ultimat3/schema": "12.0.0",
+    "@ultimat3/time": "12.0.0"
   }
 }

README.md

@@ -171,2 +171,20 @@ # @ultimat3/entity 🗄️
 
+The primary key is appended as the final sort key **in the last declared key's direction**, so
+`orderBy('createdAt', 'desc')` runs `created_at desc, id desc` — one direction throughout, which is
+what `indexes: [{ on: ['createdAt', 'id'], order: 'desc' }]` can cover and what lets the seek go out
+as the row comparison `(created_at, id) < ($1, $2)`. Write the mixed order yourself
+(`.orderBy('createdAt', 'desc').orderBy('id', 'asc')`) and you get it, spelled out as an or-chain.
+
+A **nullable** sort key orders rather than being refused: `asc nulls last`, `desc nulls first` —
+written into the statement rather than inherited from the server, and the same spelling
+`@ultimat3/query` uses. `posts.orderBy('publishedAt', 'desc')` on a nullable column pages correctly
+in both drivers; the cursor carries "this row had none" as a position of its own. The one ordering
+still refused is a nullable **primary-key** column, which no tiebreak can make total.
+
+A `timestamp()` sort key is carried at the column's own **microsecond** precision, not at the
+millisecond a JS `Date` holds: the read projects the instant as text beside the column and the seek
+binds all six digits. Without it a `desc` page over rows sharing one millisecond — which
+`defaultNow()` produces routinely — served some of them on no page at all. Cursors do not survive
+that change: one minted by an older version is `X_CURSOR_INVALID`.
+
 **A page is bounded whether or not the caller bounded it.** `DEFAULT_PAGE_SIZE` (50) covers the read
@@ -216,2 +234,97 @@ nobody sized; `MAX_PAGE_SIZE` (10,000) covers the one they did — `limit(input.pageSize)` on a number
 
+## Aggregates
+
+```ts
+import { database, entity, integer, money, timestamp, uuid } from '@ultimat3/entity';
+
+const payments = entity('payments', {
+  columns: {
+    id: uuid().primaryKey(),
+    orgId: uuid(),
+    amount: money(),
+    installments: integer().default(1),
+    paidAt: timestamp(),
+  },
+});
+// No tenant column, because an estimate is the whole TABLE's: a tenant-scoped entity is refused.
+const events = entity('events', { columns: { id: uuid().primaryKey(), at: timestamp() } });
+const db = database({ payments, events });
+declare const orgId: string;
+declare const since: Date;
+
+await db.payments.where({ orgId }).andWhere('paidAt', 'gte', since).sum('amount');
+// { minor: 128400, currency: 'EUR' }
+
+await db.payments.where({ orgId }).avg('installments'); // '2.500000'
+await db.payments.where({ orgId }).max('paidAt');       // Date | null
+await db.events.approximateCount();                     // 12_400_000 | null
+```
+
+Over exactly the rows `count()` counts — the chain's filters, its tenancy and its soft-delete
+visibility, never its page.
+
+| | |
+|---|---|
+| Never a float | `sum`/`avg` answer decimal **text** whatever the column was (the sum of a million `integer` rows is not an `integer`, and `Number()` past 2^53 loses digits); money answers `MoneyValue` in integer minor units; `min`/`max` answer the row's own type |
+| Empty set | `null` in every function, which is what SQL answers — a `0` would claim rows were seen. `count()` is `0`, and that is the distinction |
+| `avg` | one fixed scale (6 digits), rounded half away from zero from the exact rational, so both drivers land on one number |
+| Refused | `min`/`max` on `text` (ordering is the database's collation there and JS code-unit order here); `avg` over money (every answer would be a silent rounding of a fraction of a minor unit — `sum()` and `count()` instead, dividing where the rounding is a decision); an amount covering more than one currency **or scale**; a money total past ±2^53 minor units |
+| `approximateCount()` | `reltuples` out of `pg_class`, constant time, because `count(*)` walks every visible row and no index can make it cheaper. The whole **table** — a filtered chain and every tenant-scoped entity are `X_APPROXIMATE_COUNT_FILTERED`, and `null` means the table has never been analysed |
+
+## Filtering inside a json() or arrayOf() column
+
+```ts
+import { arrayOf, database, entity, json, text, uuid } from '@ultimat3/entity';
+import { t } from '@ultimat3/schema';
+
+const posts = entity('posts', {
+  columns: {
+    id: uuid().primaryKey(),
+    tags: arrayOf(text({ max: 40 })),
+    settings: json(t.object({ notify: t.object({ email: t.boolean }) })),
+  },
+});
+const db = database({ posts });
+
+await db.posts.andWhere('tags', 'contains', ['release']).all();
+await db.posts.andWhere('tags', 'overlaps', ['release', 'beta']).all();
+await db.posts.andWhere('settings', 'contains', { notify: { email: true } }).all();
+await db.posts.andWhere('settings', 'has-key', 'notify').all();
+```
+
+`contains` is `@>`, `contained-by` is `<@`, `overlaps` is `&&`, `has-key` is the `?` operator,
+written schema-qualified as `operator(pg_catalog.?)` — the function form `jsonb_exists(col, $1)` is
+the same test and the planner will not match a GIN index to it. Their
+meaning is Postgres', to the letter, in both drivers — including that the array-contains-a-primitive
+exception applies at the **top level only** and to **primitives only**, and that `&&`'s empty
+operand overlaps nothing where `@>`'s is contained by everything.
+
+There is no jsonpath expression operator: `contains` already matches nested structure, and a path
+language inside the query language would be a second way to ask one question. `&&` on a `jsonb`
+column is refused, because Postgres has no such operator.
+
+Declare the index those operators need, or every one of them is a sequential scan:
+
+```ts
+import { arrayOf, entity, json, text, uuid } from '@ultimat3/entity';
+import { t } from '@ultimat3/schema';
+
+export const posts = entity('posts', {
+  columns: {
+    id: uuid().primaryKey(),
+    tags: arrayOf(text({ max: 40 })),
+    settings: json(t.object({ notify: t.object({ email: t.boolean }) })),
+  },
+  indexes: [{ on: ['tags'], using: 'gin' }, { on: ['settings'], using: 'gin' }],
+});
+```
+
+| | |
+|---|---|
+| Methods | `btree` (the default, and what every index without `using` is) and `gin`. Omitting it emits the statement it always emitted, and regenerates nothing |
+| Served by a GIN index | array `contains` / `contained-by` / `overlaps`, and jsonb `contains` / `has-key` — measured on the planner, not assumed |
+| Not served | jsonb `contained-by`: `<@` is not in Postgres' default `jsonb_ops` operator class, so it is a sequential scan whatever you declare |
+| Refused | a unique GIN and an ordered GIN, at `entity()` — Postgres has neither, and the refusal names the edit |
+| Naming | the method is part of what separates two indexes on the same columns, so a btree and a GIN on one column are two indexes with two names |
+
 ## Counting by a column
@@ -563,4 +676,10 @@
 ```ts
+import { crossTenant } from '@ultimat3/entity';
+
+declare const expireInvites: () => Promise<void>;
+
 // admin surfaces, background reconciliation, support tooling — greppable, and never a boolean
-await crossTenant('nightly invite expiry runs for every org', async () => { … });
+await crossTenant('nightly invite expiry runs for every org', async () => {
+  await expireInvites();
+});
 ```
@@ -567,0 +686,0 @@

src/batch.ts

@@ -59,3 +59,4 @@ // Single responsibility: what `inBatches(size)` hands back — the keyset iteration a `for await`
   // minted from it, and an ordering that cannot carry one fails on the batch *after* the first —
-  // where whatever size the caller happened to pass decides whether anyone ever finds out.
+  // where whatever size the caller happened to pass decides whether anyone ever finds out. A
+  // nullable column is NOT such an ordering `As of 2026-08-24`; a nullable primary-key column is.
   assertSeekable(entity, totalOrder(entity, chain.orderBy));
@@ -62,0 +63,0 @@ };

src/column.ts

@@ -108,3 +108,8 @@ // The chain every column builder is made of. Each link returns a new column, so a chain reads
   }
-  const binding: Binding = { table, property, name: columnName(property, column.$meta) };
+  // The DERIVED name too, not only a declared one: `snake(property)` lower-cases and nothing else.
+  const binding: Binding = {
+    table,
+    property,
+    name: assertColumnName(columnName(property, column.$meta)),
+  };
   bindings.set(column, binding);
@@ -207,2 +212,11 @@ return binding;
  * different column.
+ *
+ * **Every physical name, not only a declared one — `As of 2026-08-24`.** `columnName` is
+ * `meta.name ?? snake(property)` and for three majors only the first branch reached here, so a
+ * PROPERTY name went into the DDL untouched: `snake()` lower-cases and does nothing else, and a
+ * column named `n" , "x" text); drop table t; --` produced a `create table` carrying a real
+ * `drop table` (measured through `generateMigration`). Quoting is not a defence against a value
+ * that can close the quote, which is what the paragraph above already said. `bindColumn` is where
+ * the derived name is checked, because that runs once per column at `entity()` rather than on
+ * every statement.
  */
@@ -209,0 +223,0 @@ export const assertColumnName = (name: string): string => {

src/cursor.ts

@@ -13,2 +13,3 @@ // Single responsibility: what an entity cursor *means*. The codec is `@ultimat3/core`'s — this
 import { invariantViolated } from './errors';
+import { instantMicros } from './instant';
 import type { QueryPlan } from './tenancy';
@@ -93,4 +94,32 @@ import type { AnyColumn, ColumnKind } from './types';
 
-/** Stringified so the cursor is JSON; `revive` restores the type from the column's kind. */
-const serializeSortValue = (value: unknown): string => {
+/**
+ * Stringified so the cursor is JSON; `revive` restores the type from the column's kind — and the
+ * KIND decides how, never the JS type in hand, because those are two different questions on
+ * exactly the column that made this file wrong.
+ *
+ * A `timestamptz` is carried as MICROSECONDS since the epoch, not as `toISOString()`. The column
+ * holds microseconds and a `Date` holds milliseconds, so an ISO rendition of a decoded row is the
+ * row's own position FLOORED — and a seek built from a floored position ranks rows differently
+ * from the `order by` that produced them, which silently drops every row inside the boundary
+ * millisecond. Proven against a real server: `pg-cursor-precision.live.test.ts`.
+ */
+const ABSENT_MARK = '~';
+const PRESENT_MARK = '!';
+
+/**
+ * A sort value's place in the cursor is TAGGED, so absence can be told from the text that spells
+ * it: `~` alone is NULL, `!` prefixes a present value. Positional, therefore total — a `text`
+ * column holding the four characters `null` encodes as `!null` and can never be read as an absent
+ * one, which is the collision a bare sentinel value would reopen.
+ *
+ * The tag exists because a nullable sort key is legal `As of 2026-08-24` (`asc nulls last` /
+ * `desc nulls first`, `@ultimat3/query`'s spelling), and a keyset position over one has to be able
+ * to say "the boundary row had none".
+ */
+const tagged = (text: string): string => `${PRESENT_MARK}${text}`;
+
+const serializeSortValue = (kind: ColumnKind, value: unknown): string | undefined => {
+  // `undefined`, never `'0'`: a position nothing could read would decode to the epoch, which is
+  // "start from the top" wearing a signature — the one thing a cursor must never mean.
+  if (kind === 'timestamptz') return instantMicros(value)?.toString();
   if (value instanceof Date) return value.toISOString();
@@ -113,4 +142,12 @@ if (typeof value === 'bigint') return value.toString();
   switch (kind) {
-    case 'timestamptz':
-      return new Date(text);
+    case 'timestamptz': {
+      // Microseconds since the epoch — the precision the COLUMN keeps, which a `Date` cannot.
+      // A cursor minted before that decision carries an ISO string, so this is where it is
+      // refused: `BigInt('2026-…')` is a bare `SyntaxError` with no code and no fix.
+      const micros = instantMicros(text);
+      if (micros === undefined) {
+        throw new CursorInvalidError('its position is not a microsecond instant');
+      }
+      return micros;
+    }
     case 'bigint':
@@ -131,5 +168,8 @@ return BigInt(text);
  *
- * Checked when a cursor is minted as well as when one is decoded: an ordering that cannot carry
- * a position is the author's mistake, and reporting it on the *second* page hides it behind
- * whatever page size the caller happened to use.
+ * Checked where the PLAN is built (`planFor`), which is every read either driver sends, as well as
+ * when a cursor is minted and when one is decoded. The plan is the load-bearing one: `cursorFor`
+ * runs only when a page found a row past its limit, so the refusal used to depend on how many rows
+ * the table happened to hold — green on fifteen seeded rows, `X_INVARIANT_VIOLATED` on the first
+ * read past a page of twenty in production. An ordering that cannot carry a position is the
+ * author's mistake at any row count.
  */
@@ -145,7 +185,16 @@ export const assertSeekable = <Row>(
     if (columnAt(entity, key.column).$meta.notNull) continue;
+    // An ORDINARY nullable key is orderable, `As of 2026-08-24`: NULL has a declared place
+    // (`asc nulls last` / `desc nulls first`), the cursor carries that place, and the seek reaches
+    // it. What is left is the TIEBREAK — `totalOrder` appends the primary key precisely so two
+    // rows sharing a sort value cannot straddle a page boundary, and a nullable primary-key column
+    // cannot do that job: `null = null` is unknown, so two such rows are indistinguishable to the
+    // seek and one of them is served twice or never. Reachable only through `primaryKey: [...]`,
+    // which takes the columns as declared.
+    if (!entity.$primaryKey.includes(key.column)) continue;
     throw invariantViolated(
       entity.$name,
       'cursor',
-      `${key.column} is nullable and cannot carry a cursor — order by a not-null column ` +
-        `(add .orderBy('${entity.$primaryKey[0] ?? 'id'}') or make ${key.column} not null)`,
+      `${key.column} is part of the primary key and is nullable, so no ordering can be total — ` +
+        'an ordinary nullable column orders fine (nulls last ascending, nulls first descending), ' +
+        `but the tiebreak cannot: drop .nullable() from ${key.column}`,
     );
@@ -155,2 +204,6 @@ }
 
+/** Whether a sort key may hold NULL — what decides the seek's SHAPE, not only its values. */
+export const isNullableKey = <Row>(entity: EntityCore<Row>, path: string): boolean =>
+  !columnAt(entity, path).$meta.notNull;
+
 /** Deterministic, and total over the value shapes a predicate can hold. */
@@ -191,3 +244,10 @@ const renderValue = (value: unknown): string => {
 
-/** The cursor that continues this plan after `row`. Signed by core, scoped by the plan. */
+/**
+ * The cursor that continues this plan after `row`. Signed by core, scoped by the plan.
+ *
+ * `exact` is how a driver hands over a value the DECODED row cannot hold: a `timestamptz` comes
+ * back as a `Date`, which is milliseconds, and the microseconds it dropped are the difference
+ * between a position the `order by` agrees with and one it does not. Optional because the
+ * in-memory driver stores millisecond `Date`s and therefore has nothing finer to give.
+ */
 export const cursorFor = <Row>(
@@ -198,2 +258,3 @@ entity: EntityCore<Row>,
   id: string,
+  exact?: ReadonlyMap<string, unknown>,
 ): string => {
@@ -203,3 +264,15 @@ assertSeekable(entity, plan.orderBy);
     scope: planScope(plan),
-    key: plan.orderBy.map((entry) => serializeSortValue(valueAt(row, entry.column))),
+    key: plan.orderBy.map((entry) => {
+      const value = exact?.get(entry.column) ?? valueAt(row, entry.column);
+      // A column the row never named and a stored NULL are one absence everywhere else in this
+      // package (`isNull`), and they are one position here too.
+      if (value === null || value === undefined) return ABSENT_MARK;
+      const text = serializeSortValue(kindAt(entity, entry.column), value);
+      if (text !== undefined) return tagged(text);
+      throw invariantViolated(
+        entity.$name,
+        'cursor',
+        `${entry.column} on the last row of the page holds no instant a cursor can carry`,
+      );
+    }),
     id,
@@ -228,5 +301,18 @@ });
   }
-  return plan.orderBy.map((entry, index) =>
-    reviveSortValue(kindAt(entity, entry.column), String(key[index])),
-  );
+  return plan.orderBy.map((entry, index) => {
+    // `segment` and `ABSENT_MARK`, never `token` and `NULL_KEY`: both names said CREDENTIAL to
+    // `bun run secret-compare`, whose rule is that a `===` on one leaks it a byte at a time. What
+    // this compares is a page POSITION against a one-character tag, where the repair the guard
+    // names — `timingSafeEqual` — would be constant-time nonsense. The guard reads names because a
+    // unit test cannot assert timing, so the name is the thing that has to be right.
+    const segment = String(key[index]);
+    if (segment === ABSENT_MARK) return null;
+    if (!segment.startsWith(PRESENT_MARK)) {
+      // Every cursor this package mints carries a tag. An untagged one was forged past the
+      // signature or minted before nullable sort keys existed; either way the alternative is a
+      // silent restart at the top, which is the one thing a cursor may never mean.
+      throw new CursorInvalidError('a sort value carries no null-or-value tag');
+    }
+    return reviveSortValue(kindAt(entity, entry.column), segment.slice(PRESENT_MARK.length));
+  });
 };

src/database.ts

@@ -5,2 +5,3 @@ // The one typed database handle. `db.posts` exists because `posts` was declared — nobody
 import type { EntityCore } from './entity';
+import { memoryRepo } from './memory-repo';
 import type { RelatedTables } from './preload';
@@ -10,3 +11,2 @@ import type { Table } from './query';
 import type { Repo } from './repo';
-import { memoryRepo } from './repo';
 import { observedRepo } from './row-observer';
@@ -13,0 +13,0 @@

src/describe.ts

@@ -191,2 +191,5 @@ // The projection an entity hands the rest of the toolchain: `x.manifest.json`, the migration
       order: index.order ?? null,
+      // Spread, never `?? null`: absent is what `@ultimat3/db` reads as the btree it always was,
+      // and a written-out `null` would be a field no existing snapshot carries.
+      ...(index.using === undefined ? {} : { using: index.using }),
     })),
@@ -193,0 +196,0 @@ tags: input.tags,

src/entity.ts

@@ -7,2 +7,3 @@ // `entity(name, { columns })` is the first primitive. The row type is DERIVED from the columns —
 import { renderThrowable } from '@ultimat3/core';
+import type { IndexMethod } from '@ultimat3/db';
 import { describeValue, type StandardSchemaV1 } from '@ultimat3/schema';
@@ -34,2 +35,17 @@ import { entityNow } from './clock';
   readonly where?: (columns: InvariantColumns<C>) => Expr;
+  /**
+   * The access method. Omitted is `btree`, which is Postgres' own default and what every index
+   * declared before this existed is — so an entity that names none emits the statement it always
+   * emitted and nothing regenerates.
+   *
+   * `'gin'` is the one with a caller, and it is the whole point of the containment operators:
+   * measured on Postgres 16 over 20,000 rows, `tags @> …`, `tags <@ …`, `tags && …` and
+   * `data @> …` are each a Bitmap Index Scan with one and a Seq Scan without. The set is
+   * `@ultimat3/db`'s `INDEX_METHODS`, imported rather than restated — one declaration of one fact.
+   *
+   * Two Postgres rules ride with it and both are refused HERE, where the author is, rather than at
+   * `x db gen` or inside `ROLE=migrate` as the server's own syntax error: a GIN index cannot be
+   * unique and cannot order its keys.
+   */
+  readonly using?: IndexMethod;
 }
@@ -114,5 +130,84 @@
 
-const indexName = (table: string, columns: readonly string[], unique: boolean): string =>
-  `${table}_${columns.join('_')}_${unique ? 'key' : 'idx'}`;
+/**
+ * What separates two indexes on the SAME columns: the predicate and the direction. Eight hex
+ * characters of sha256 over both — deterministic across processes, so a name is a property of the
+ * declaration and never of the run that generated it.
+ */
+/**
+ * What separates two indexes on the SAME columns: the predicate, the direction and the ACCESS
+ * METHOD. Eight hex characters of sha256 over all three — deterministic across processes, so a
+ * name is a property of the declaration and never of the run that generated it.
+ *
+ * The method belongs here for exactly the reason `where` does. A btree on an `arrayOf()` column
+ * answers `=` and an ordering; a GIN on the same column answers `@>` / `<@` / `&&`. They are two
+ * distinct indexes, and without the method in the name both are `<table>_<cols>_idx` — where the
+ * dedup below drops one in silence (the defect this discriminator was added for) or, since that
+ * dedup is now on the whole definition, two `create index` statements share one name and the
+ * migration is `42P07`.
+ */
+const indexDiscriminator = (
+  order: string | undefined,
+  where: string | null,
+  using: string | undefined,
+): string =>
+  new Bun.CryptoHasher('sha256')
+    // The method is APPENDED only when one was declared, never as an empty field: every name this
+    // function has ever minted for a partial or ordered index is therefore unchanged by the method
+    // existing, and an index that declares no method is byte-identical to the one it was.
+    .update(`${order ?? ''}|${where ?? ''}${using === undefined ? '' : `|${using}`}`)
+    .digest('hex')
+    .slice(0, 8);
 
+/**
+ * `<table>_<columns>_idx`, plus a discriminator when — and only when — the index carries a
+ * predicate, a direction or a non-default access method.
+ *
+ * Only then, because the plain name is load-bearing in two places: `unique()` on a column is an
+ * inline column clause and Postgres names the index it creates exactly `<table>_<column>_key`, so
+ * a discriminator there would make the generator emit a second `create unique index` for an index
+ * that already exists (`42P07`); and a foreign key's own index is deduped against a hand-declared
+ * one by this name.
+ *
+ * Without it, two DIFFERENT partial indexes on one column were one name — `posts_author_id_idx`
+ * for both `where status = 'published'` and `where status = 'draft'` — and the dedup below dropped
+ * the second with no error, no warning and no drift finding, since a declared index is matched by
+ * name.
+ */
+/**
+ * `NAMEDATALEN - 1`. Postgres truncates a longer identifier and says NOTHING, so two index names
+ * sharing their first 63 bytes become one index on the server — the same silent collapse the
+ * discriminator above exists to prevent, one layer down, and invisible to a drift check comparing
+ * DECLARED names because those still differ. Bytes and not characters: 63 is what the server
+ * counts, and `.length` would stop seeing the truncation the moment a name is not ASCII.
+ */
+const MAX_IDENTIFIER_BYTES = 63;
+
+const byteLength = (value: string): number => new TextEncoder().encode(value).length;
+
+const indexName = (
+  entityName: string,
+  table: string,
+  columns: readonly string[],
+  unique: boolean,
+  order?: string | undefined,
+  where: string | null = null,
+  using?: IndexMethod | undefined,
+): string => {
+  const suffix = unique ? 'key' : 'idx';
+  const base = `${table}_${columns.join('_')}`;
+  const plain = order === undefined && where === null && using === undefined;
+  const name = plain
+    ? `${base}_${suffix}`
+    : `${base}_${indexDiscriminator(order, where, using)}_${suffix}`;
+  const bytes = byteLength(name);
+  if (bytes <= MAX_IDENTIFIER_BYTES) return name;
+  throw invariantViolated(
+    entityName,
+    'index',
+    `the index on (${columns.join(', ')}) is named "${name}", which is ${bytes} bytes — ` +
+      `Postgres truncates an identifier at ${MAX_IDENTIFIER_BYTES} and does not say so, ` +
+      'so two indexes can silently become one',
+  );
+};
+
 const defaultValue = (meta: ColumnMeta): unknown => {
@@ -133,3 +228,6 @@ const declared = meta.default;
 
-  const table = init.table === undefined ? name : assertColumnName(init.table);
+  // Both branches. The declared table was checked and the fallback — which is every entity that
+  // does not rename its table — was not, so an entity NAME closed the identifier in the same way a
+  // column name could: `entity('t" (x int); drop table u; --')` emitted that `drop table` verbatim.
+  const table = assertColumnName(init.table ?? name);
   const cacheTag = `entity:${name}`;
@@ -194,3 +292,7 @@ const softDelete = Object.hasOwn(init.columns, SOFT_DELETE_COLUMN);
       return [
-        { name: indexName(table, physical, meta.unique), columns: physical, unique: meta.unique },
+        {
+          name: indexName(name, table, physical, meta.unique),
+          columns: physical,
+          unique: meta.unique,
+        },
       ];
@@ -214,4 +316,27 @@ }),
       }
+      // Two rules Postgres has that a declaration can break, refused where the author wrote it.
+      // `@ultimat3/db` refuses both again at `createIndex` — that is not a duplicate, it is the
+      // guard for a description nobody built here — but its refusal lands at `x db gen` or, if a
+      // migration was already written, inside `ROLE=migrate` as the server's own syntax error with
+      // none of the entity's words in it.
+      if (index.using !== undefined && index.using !== 'btree') {
+        if (unique) {
+          throw invariantViolated(
+            name,
+            'index',
+            `the index on (${columns.join(', ')}) is unique and ${index.using}; ` +
+              `Postgres has no unique ${index.using} index — drop unique, or drop using`,
+          );
+        }
+        if (index.order !== undefined) {
+          throw invariantViolated(
+            name,
+            'index',
+            `the index on (${columns.join(', ')}) is ${index.using} and ${index.order}; ` +
+              'only a btree orders its keys — drop order, or drop using',
+          );
+        }
+      }
       return {
-        name: indexName(table, columns, unique),
+        name: indexName(name, table, columns, unique, index.order, where, index.using),
         columns,
@@ -221,8 +346,29 @@ unique,
         ...(where === null ? {} : { where }),
+        // Absent stays absent: `btree` written out would be a field every existing snapshot lacks,
+        // and `indexMethodOf` reads the two the same way precisely so nothing regenerates.
+        ...(index.using === undefined || index.using === 'btree' ? {} : { using: index.using }),
       };
     }),
   ];
-  // A foreign key already indexes its column; naming it again in `indexes` is not two indexes.
+  /**
+   * A foreign key already indexes its column; naming it again in `indexes` is not two indexes.
+   *
+   * On the WHOLE definition and not on the name. With the discriminator above the two rules agree
+   * exactly, so this is not a behaviour change on its own — it is which one FAILS LOUDLY if the
+   * naming is ever weakened again. Matching on the name drops the second index in silence, which
+   * is how two different partial indexes became one for three majors; matching on the definition
+   * keeps both, and two `create index` statements sharing a name is `42P07` on the next migration.
+   */
+  const identity = (index: IndexDef): string =>
+    [
+      index.name,
+      index.columns.join(','),
+      index.unique,
+      index.order ?? '',
+      index.where ?? '',
+      index.using ?? '',
+    ].join('|');
   const indexes: readonly IndexDef[] = declared.filter(
-    (index, position) => declared.findIndex((other) => other.name === index.name) === position,
+    (index, position) =>
+      declared.findIndex((other) => identity(other) === identity(index)) === position,
   );
@@ -229,0 +375,0 @@

src/errors.ts

@@ -21,2 +21,5 @@ // The entity layer's stable error codes. Each factory produces the exact command
   'X_REPO_CLIENT_PINNED',
+  'X_AGGREGATE_UNSUPPORTED',
+  'X_AGGREGATE_MIXED_CURRENCY',
+  'X_APPROXIMATE_COUNT_FILTERED',
 ] as const;
@@ -56,2 +59,5 @@
   X_REPO_CLIENT_PINNED: 'a repository pinned to its own client cannot join the open transaction',
+  X_AGGREGATE_UNSUPPORTED: 'that column has no aggregate both drivers can answer alike',
+  X_AGGREGATE_MIXED_CURRENCY: 'an amount was aggregated across currencies',
+  X_APPROXIMATE_COUNT_FILTERED: 'an estimate was asked of a filtered chain',
 };
@@ -58,0 +64,0 @@

src/index.ts

@@ -6,2 +6,4 @@ // The public surface of @ultimat3/entity. Explicit, never `export *`.
 export { t } from '@ultimat3/schema';
+export type { AggregateFn } from './aggregate';
+export { AVG_SCALE } from './aggregate';
 export type { BatchIterator } from './batch';
@@ -76,2 +78,3 @@ export type { MoneyColumns } from './column';
 } from './invariants';
+export { memoryRepo, memoryTransactor } from './memory-repo';
 export type { StatementLoop } from './n-plus-one';
@@ -116,3 +119,2 @@ export { N_PLUS_ONE_THRESHOLD, nPlusOne, preloadsFor } from './n-plus-one';
 } from './repo';
-export { memoryRepo, memoryTransactor } from './repo';
 export type { RowBulkChange, RowChange, RowChangeOp, RowObserver } from './row-observer';
@@ -119,0 +121,0 @@ export { observedRepo, rowObserver, setRowObserver } from './row-observer';

src/memory-match.ts

@@ -9,5 +9,7 @@ // Single responsibility: what a `Predicate` MEANS in the in-memory driver — equality, ordering and
 import { keyOf } from './batch-read';
+import { arrayContains, arrayOverlaps, jsonContains, jsonHasKey } from './containment';
 import { kindOf, valueAt } from './cursor';
 import type { EntityCore } from './entity';
 import { EntityError } from './errors';
+import { instantMicros } from './instant';
 import type { Predicate } from './tenancy';
@@ -50,2 +52,21 @@ import type { ColumnKind } from './types';
 ): number => {
+  // NULL is the LARGEST value, which is what `order by` means in Postgres and what `nulls last`
+  // ascending / `nulls first` descending spell out (`pg-sql.ts`'s `orderSql`, `@ultimat3/query`'s
+  // `compareValues`). Two absences are equal, so the next sort key decides — without this rule a
+  // NULL fell through to `String(left) < String(right)` and sorted as the four characters `null`,
+  // somewhere in the middle of the alphabet, which is a different listing from the one the
+  // database returns and a page boundary cut where the server cuts none.
+  if (isNull(left) || isNull(right)) {
+    return isNull(left) && isNull(right) ? 0 : isNull(left) ? 1 : -1;
+  }
+  // A `timestamptz` is compared in MICROSECONDS, which is what the column holds and what a cursor
+  // now carries (`cursor.ts`). The two sides are not the same shape and that is the point: a
+  // stored row here is a `Date` and a keyset position is a microsecond count, so a `Date`/`Date`
+  // test alone would fall through to `String(left) < String(right)` and order a page by the text
+  // of an ISO string against a decimal.
+  if (kind === 'timestamptz') {
+    const before = instantMicros(left);
+    const after = instantMicros(right);
+    if (before !== undefined && after !== undefined) return sign(before, after);
+  }
   if (left instanceof Date && right instanceof Date) return sign(left.getTime(), right.getTime());
@@ -204,3 +225,49 @@ if (kind !== undefined && DECIMAL_TEXT.has(kind)) {
       return !isNull(actual);
+    // The containment half, decided by the column's DECLARED kind exactly as everything above it
+    // is: `@>` on a `jsonb` is recursive structural containment and `@>` on an array is plain
+    // element containment, and those are two different operators that happen to share a symbol.
+    // A NULL column value matches nothing, which is what the SQL answers too.
+    case 'contains':
+      return !isNull(actual) && containsBy(kind, actual, predicate.value);
+    case 'contained-by':
+      return !isNull(actual) && containsBy(kind, predicate.value, actual);
+    // `&&` is arrays only. A `jsonb` column reaching it is refused rather than guessed at: there
+    // is no `jsonb && jsonb` in Postgres, so any answer here would be one no statement can make.
+    case 'overlaps':
+      return (
+        !isNull(actual) &&
+        arrayOverlaps(
+          asArray(entity, predicate, actual),
+          asArray(entity, predicate, predicate.value),
+        )
+      );
+    case 'has-key':
+      return jsonHasKey(actual, predicate.value);
   }
 };
+
+/** `left @> right`, under the rule the LEFT column's kind decides. */
+const containsBy = (kind: ColumnKind | undefined, left: unknown, right: unknown): boolean =>
+  kind === 'jsonb'
+    ? jsonContains(left, right)
+    : arrayContains(Array.isArray(left) ? left : [left], Array.isArray(right) ? right : [right]);
+
+/**
+ * The operand of an array-only operator. A `jsonb` column here is the caller asking for an
+ * operator Postgres does not have on that type, so it is refused where they wrote it rather than
+ * answered with something the database never would.
+ */
+const asArray = <Row>(
+  entity: EntityCore<Row>,
+  predicate: Predicate,
+  value: unknown,
+): readonly unknown[] => {
+  if (kindOf(entity, predicate.column) === 'jsonb') {
+    throw new EntityError({
+      code: 'X_INVARIANT_VIOLATED',
+      cause: `${entity.$name}.${predicate.column} is jsonb, and Postgres has no && (overlaps) operator for jsonb`,
+      fix: `${entity.$name}.andWhere('${predicate.column}', 'contains', <value>)   # @> matches nested structure; && is for arrayOf() columns`,
+    });
+  }
+  return Array.isArray(value) ? value : [value];
+};

src/pg-driver.ts

@@ -19,2 +19,4 @@ // The production driver. Same `Repo` contract as `memoryRepo`, same plans, same cursor — the
 } from '@ultimat3/db';
+import { aggregateColumnOf, aggregateMinor, assertOneUnit } from './aggregate';
+import { decodeAggregate } from './aggregate-decode';
 import {
@@ -29,2 +31,3 @@ conflictKeys,
 import { coalesceFindById } from './coalesce';
+import { moneyColumns } from './column';
 import { countsFrom, groupColumnOf, groupValue, MAX_GROUPS } from './count-by';
@@ -37,14 +40,21 @@ import { cursorFor, seekFrom, valueAt } from './cursor';
 import { forgetPreloaded, tagSiblings } from './jit-preload';
-import { bindValues, decodeRow, type PhysicalRow, physicalName } from './pg-row';
+import { bindValues, decodeRow, type PhysicalRow, physicalName, sortPrecision } from './pg-row';
 import {
-  type ConflictTarget,
+  type AggregateRow,
+  aggregateStatement,
   countByStatement,
   countStatement,
-  deleteStatement,
+  currenciesStatement,
+  estimateStatement,
   type GroupRow,
-  insertStatement,
+  type MoneyUnitRow,
   type ReadShape,
   selectStatement,
+} from './pg-sql';
+import {
+  type ConflictTarget,
+  deleteStatement,
+  insertStatement,
   updateStatement,
-} from './pg-sql';
+} from './pg-write-sql';
 import { deletePlan, idPlan, readPlan, updatePlan } from './plan';
@@ -225,3 +235,4 @@ import type { FindManyArgs, Repo, Transactor, UpsertArgs } from './repo';
       );
-      const rows = found.slice(0, plan.limit).map((row) => decodeRow(entity, row));
+      const page = found.slice(0, plan.limit);
+      const rows = page.map((row) => decodeRow(entity, row));
       // What the page leaves behind: its foreign key values, so the first `findById` for any one
@@ -236,3 +247,12 @@ // of them resolves the key for the whole page. A `for … of` loop over these rows costs one
           found.length > plan.limit && last !== undefined
-            ? cursorFor(entity, plan, last, idOf(last))
+            ? // Minted from the PHYSICAL row as well as the decoded one: a `timestamptz` decodes
+              // to a `Date`, and the microseconds that drops are the difference between a position
+              // the `order by` agrees with and one that cuts between two rows.
+              cursorFor(
+                entity,
+                plan,
+                last,
+                idOf(last),
+                sortPrecision(entity, plan.orderBy, page.at(-1)),
+              )
             : null,
@@ -384,2 +404,69 @@ };
     },
+
+    async aggregate(fn, column, args = {}) {
+      const op = fn;
+      const declared = aggregateColumnOf(entity, fn, column);
+      const plan = readPlan(entity, args, op);
+      const shape = shapeOf(args);
+      const money = declared.$meta.kind === 'money' ? moneyColumns(column, declared.$meta) : null;
+      // Money is judged BEFORE the aggregate is asked for, in its own statement: `sum(minor)` over
+      // two currencies is a number in neither, and the refusal has to name them.
+      const unit =
+        money === null
+          ? undefined
+          : assertOneUnit(
+              entity,
+              fn,
+              column,
+              (
+                await attributed(op, () =>
+                  client().query<MoneyUnitRow>(
+                    currenciesStatement(entity, plan, shape, money.currency, money.scale),
+                  ),
+                )
+              ).map((row) => ({
+                currency: String(row.group_value ?? '').trim(),
+                scale:
+                  row.group_scale === null || row.group_scale === undefined
+                    ? null
+                    : Number(row.group_scale),
+              })),
+            );
+      const row = await attributed(op, () =>
+        client().one<AggregateRow>(
+          aggregateStatement(entity, plan, shape, fn, money === null ? column : `${column}.minor`),
+        ),
+      );
+      const text = row?.agg_value;
+      if (text === null || text === undefined) return null;
+      if (money === null) return decodeAggregate(fn, declared.$meta.kind, String(text));
+      if (unit === undefined) return null;
+      return {
+        minor: aggregateMinor(entity, fn, column, String(text)),
+        currency: unit.currency,
+        ...(unit.scale === null ? {} : { scale: unit.scale }),
+      };
+    },
+
+    /**
+     * `reltuples`, the planner's own estimate — one row out of `pg_class`, constant time, and the
+     * only answer that stays constant time as the table grows. `count(*)` walks every visible row
+     * because MVCC gives it no shortcut, so past a few million it is the read that trips a web
+     * role's `statement_timeout`, and no index can help: `X_DB_STATEMENT_TIMEOUT`'s fix names one
+     * anyway, and following it changes nothing.
+     */
+    async approximateCount(args = {}) {
+      const op = 'approximateCount';
+      // Built and therefore GUARDED: tenancy still applies, and a filtered chain is refused here
+      // rather than answered with the whole table's estimate.
+      readPlan(entity, args, op);
+      const row = await attributed(op, () =>
+        client().one<{ estimate: unknown }>(estimateStatement(entity.$table)),
+      );
+      const estimate = Number(row?.estimate ?? -1);
+      // `-1` is what Postgres 14+ stores for a table nobody has analysed. That is an absence of an
+      // estimate, not an estimate of zero, and answering `0` would read exactly like an empty
+      // table to every caller.
+      return Number.isFinite(estimate) && estimate >= 0 ? estimate : null;
+    },
   };
@@ -386,0 +473,0 @@ };

src/pg-row.ts

@@ -9,4 +9,7 @@ // Single responsibility: the two-way map between a physical Postgres row and an entity row.
 import { narrowMoney } from './columns';
+import { kindOf } from './cursor';
 import type { EntityCore } from './entity';
 import { invariantViolated } from './errors';
+import { pgInstantMicros, seekAlias } from './instant';
+import type { SortKey } from './tenancy';
 import type { AnyColumn, MoneyValue, RowPatch } from './types';
@@ -129,2 +132,10 @@
  */
+/**
+ * A JS array as the literal Postgres accepts. Exported because a containment predicate binds one
+ * too (`tags @> $1`) and building a second one there is how the two spellings drift: Bun's `sql`
+ * serialises a JS array to `x,y`, which the server answers `malformed array literal`.
+ */
+export const arrayLiteral = (value: unknown): string =>
+  `{${(Array.isArray(value) ? value : [value]).map(arrayElement).join(',')}}`;
+
 const bindable = (column: AnyColumn, value: unknown): unknown => {
@@ -136,3 +147,3 @@ if (value === null || value === undefined) return null;
   if (column.$meta.kind !== 'array' || !Array.isArray(value)) return value;
-  return `{${value.map(arrayElement).join(',')}}`;
+  return arrayLiteral(value);
 };
@@ -191,1 +202,27 @@
 };
+
+/**
+ * The sort values a cursor has to be minted from when the DECODED row cannot hold them. Exactly
+ * one kind is in that position: a `timestamptz` comes back through Bun's client as a JS `Date`,
+ * which is milliseconds, while the column and the `order by` are microseconds — so a cursor minted
+ * from the decoded row cuts the page at a position no row occupies, and every row inside the
+ * boundary millisecond is served on no page at all.
+ *
+ * Keyed by the sort key PATH, because that is what `cursorFor` looks a value up by. A key the
+ * statement did not carry a precision output for is simply absent, and the decoded `Date` is the
+ * position — which is the behaviour every other kind has.
+ */
+export const sortPrecision = <Row>(
+  entity: EntityCore<Row>,
+  orderBy: readonly SortKey[],
+  source: PhysicalRow | undefined,
+): ReadonlyMap<string, unknown> => {
+  const exact = new Map<string, unknown>();
+  if (source === undefined) return exact;
+  for (const entry of orderBy) {
+    if (kindOf(entity, entry.column) !== 'timestamptz') continue;
+    const micros = pgInstantMicros(source[seekAlias(physicalName(entity, entry.column))]);
+    if (micros !== undefined) exact.set(entry.column, micros);
+  }
+  return exact;
+};

src/pg-sql.ts

@@ -7,7 +7,12 @@ // Single responsibility: compile a `QueryPlan` into parameterised SQL. Nothing here builds a
 import { identifier, join, raw, type SqlFragment, sql } from '@ultimat3/db';
-import { columnFor, columnName } from './column';
+import type { AggregateFn } from './aggregate';
+import { AVG_SCALE } from './aggregate';
+import { columnFor } from './column';
+import { isNullableKey, kindOf } from './cursor';
 import type { EntityCore } from './entity';
 import { SOFT_DELETE_COLUMN } from './entity';
-import { allColumns, columnsOf, physicalName } from './pg-row';
+import { microsToIso, seekAlias } from './instant';
+import { allColumns, arrayLiteral, columnsOf, physicalName } from './pg-row';
 import type { Predicate, QueryPlan, SortKey } from './tenancy';
+import type { ColumnKind } from './types';
 
@@ -69,2 +74,27 @@ /** Nothing matches. `in ()` is a syntax error in Postgres, so an empty set needs a constant. */
       return sql`${column} is not null`;
+    // The containment half. The OPERAND crosses as a bound parameter in every one of them — a
+    // jsonb operand as its TEXT with the same `::text::jsonb` cast an insert cell uses (the driver
+    // seam refuses a plain object as a parameter), an array operand as the array literal
+    // `bindValues` already builds. What is written into the statement is the operator, and the
+    // operator is chosen here from a closed set of four.
+    case 'contains':
+      return containmentSql(entity, predicate, 'contains');
+    case 'contained-by':
+      return containmentSql(entity, predicate, 'contained-by');
+    case 'overlaps':
+      return containmentSql(entity, predicate, 'overlaps');
+    // The `?` OPERATOR, schema-qualified — not `jsonb_exists(col, $1)`, which is the same test and
+    // is **not indexable**. Measured on Postgres 16 over 20,000 rows with a GIN index and
+    // `enable_seqscan = off`: `data ? 'k'` plans as a Bitmap Index Scan and
+    // `jsonb_exists(data, 'k')` is a Seq Scan the planner will not convert, because an index is
+    // matched against an OPERATOR expression and a bare function call is not one. Shipping the
+    // function form would have made `has-key` the one containment operator a declared GIN index
+    // cannot serve — which is the whole reason the index is declarable.
+    //
+    // `operator(pg_catalog.?)` rather than a bare `?`: both round-trip through Bun's client today
+    // (measured), and the qualified spelling is immune to any client that reads `?` as a
+    // placeholder and to a search_path that shadows the operator. Postgres matches the index
+    // against it identically — verified in the same EXPLAIN run.
+    case 'has-key':
+      return sql`${column} operator(pg_catalog.?) ${String(predicate.value)}`;
   }
@@ -74,33 +104,114 @@ };
 /**
- * A cursor's timestamp is the row's own value FLOORED: a `Date` holds milliseconds and a
- * `timestamptz` column holds microseconds, so `created_at > '…123'` is satisfied by the very row
- * at `…123456` the cursor was minted from — the same row, returned again, on every page boundary.
- * Under `desc` the same gap does the opposite and silently drops every row inside that
- * millisecond, which no `id` tiebreak can recover because the first `or` term never matched.
+ * A containment operand, cast to the column's own type. `jsonb` is the one that cannot bind as
+ * itself — the seam refuses a plain object (`X_SQL_UNSAFE`) — so it crosses as TEXT and the cast
+ * turns it back, exactly as an insert cell does; `::text::jsonb` and not `::jsonb`, because with
+ * the single cast the client JSON-encodes the string it was given and `{"a":1}` is stored as the
+ * JSON *string*.
+ */
+const containmentSql = <Row>(
+  entity: EntityCore<Row>,
+  predicate: Predicate,
+  op: 'contains' | 'contained-by' | 'overlaps',
+): SqlFragment => {
+  const column = columnRef(entity, predicate.column);
+  const kind = kindOf(entity, predicate.column);
+  const operand =
+    kind === 'jsonb'
+      ? sql`${JSON.stringify(predicate.value ?? null)}::text::jsonb`
+      : sql`${arrayLiteral(predicate.value)}`;
+  if (op === 'contains') return sql`${column} @> ${operand}`;
+  if (op === 'contained-by') return sql`${column} <@ ${operand}`;
+  return sql`${column} && ${operand}`;
+};
+
+/**
+ * The bind a seek compares against, at the precision the COLUMN keeps. Every kind but one binds
+ * the revived value itself; a `timestamptz` cursor carries MICROSECONDS since the epoch
+ * (`cursor.ts`), because binding a JS `Date` is the row's own position floored to the millisecond
+ * — and the `order by` beside it sorts at microseconds, so the two ranked rows differently and a
+ * `desc` page dropped every row inside the boundary millisecond. Proven against a real server:
+ * `pg-cursor-precision.live.test.ts`.
  *
- * So a timestamp seek compares against the millisecond WINDOW its value stands for — what
- * `date_trunc('milliseconds', …)` would say, spelled as a half-open range so the column stays
- * bare and an index can still range-scan it. `timestamptz` is the only sort kind revived as a
- * `Date` (`cursor.ts`), so the type test IS the kind test.
+ * The cast is part of this template and not a `raw()` call: what crosses as a parameter is the ISO
+ * text, and `${…}::timestamptz` is what makes the server parse it as an instant rather than infer
+ * a type for it. The column stays BARE on the left, so an index can still range-scan.
  */
-const nextMillisecond = (value: Date): Date => new Date(value.getTime() + 1);
+const seekBind = (kind: ColumnKind | undefined, value: unknown): SqlFragment | null => {
+  // `null` and not a bound parameter: NULL is never the VALUE being tested, it is the shape of the
+  // term. `col = $n` and `col > $n` are both unknown against a NULL bind, so binding one would
+  // answer no rows where the ordering says there are some.
+  if (value === null || value === undefined) return null;
+  return kind === 'timestamptz' && typeof value === 'bigint'
+    ? sql`${microsToIso(value)}::timestamptz`
+    : sql`${value}`;
+};
 
-/** Strictly past the cursor's position in this key's direction. */
-const seekAfter = (column: SqlFragment, direction: string, value: unknown): SqlFragment => {
-  if (direction === 'desc') return sql`${column} < ${value}`;
-  // `>= v + 1ms` is `trunc(col) > v`; `< v` already is `trunc(col) < v`, so only asc moves.
-  if (value instanceof Date) return sql`${column} >= ${nextMillisecond(value)}`;
-  return sql`${column} > ${value}`;
+/**
+ * Strictly past the cursor's position in this key's direction, under the ordering `orderSql`
+ * writes — so NULL is the largest value on both sides of the comparison.
+ *
+ * `desc` is `nulls first`: a NULL cursor is at the very top, and every non-null row follows it
+ * (`is not null`); a value cursor's `< $n` already excludes the NULLs above it. `asc` is
+ * `nulls last`: the NULLs follow every value, so a value cursor has to REACH them explicitly or
+ * page two ends at the first NULL — and a NULL cursor is the end of the listing, which is why the
+ * ascending null case answers `undefined` and `seekSql` drops the whole term rather than emitting
+ * SQL that can never be true.
+ *
+ * `or col is null` only when the column can actually hold one: on a not-null column it is dead SQL
+ * the planner has to defeat before it can seek the index, on every paged read.
+ */
+const seekAfter = (
+  column: SqlFragment,
+  direction: string,
+  bind: SqlFragment | null,
+  nullable: boolean,
+): SqlFragment | undefined => {
+  if (direction === 'desc') {
+    return bind === null ? sql`${column} is not null` : sql`${column} < ${bind}`;
+  }
+  if (bind === null) return undefined;
+  return nullable ? sql`(${column} > ${bind} or ${column} is null)` : sql`${column} > ${bind}`;
 };
 
-/** At the cursor's position for this key — the prefix a later key's tiebreak hangs off. */
-const seekEqual = (column: SqlFragment, value: unknown): SqlFragment =>
-  value instanceof Date
-    ? sql`(${column} >= ${value} and ${column} < ${nextMillisecond(value)})`
-    : sql`${column} = ${value}`;
+/**
+ * At the cursor's position for this key — the prefix a later key's tiebreak hangs off. `= $n` is
+ * never true of a NULL in Postgres, so an absent position is `is null`: the same pair
+ * `predicateSql` above already emits for `eq`, one page later.
+ */
+const seekEqual = (column: SqlFragment, bind: SqlFragment | null): SqlFragment =>
+  bind === null ? sql`${column} is null` : sql`${column} = ${bind}`;
 
 /**
- * The keyset seek, spelled out rather than as a row comparison: `(a, b) > (x, y)` requires every
- * key to sort the same way, and a listing that is `published_at desc, id asc` does not.
+ * The keyset seek. Two shapes, and which one is legal is decided by the ORDER, never by taste.
+ *
+ * Every key sorting the same way is a ROW COMPARISON — `(a, b) < ($1, $2)`. That is the shape
+ * Postgres can push into a multicolumn index: measured on Postgres 16 over 20,000 rows with an
+ * index on `(org, at desc, id desc)`, the row form plans as an Index Only Scan carrying the whole
+ * seek as its Index Cond, while the or-chain below plans as a BitmapOr of two index scans plus a
+ * Sort over everything they matched.
+ *
+ * A MIXED order — `published_at desc, id asc` — has no row comparison, so it is spelled out as the
+ * or-chain instead. `totalOrder` no longer produces one by accident (the tiebreak follows the last
+ * declared key's direction), so this branch is now reached only by a caller who wrote the mixed
+ * order themselves.
+ *
+ * Either way every term is a plain comparison against a bare column, which is what carrying the
+ * cursor at the column's own precision bought: the equality class this seek cuts on is exactly the
+ * one `orderSql` sorts by, so no row can fall between the two.
  */
+/**
+ * A row comparison `(a, b) < ($1, $2)` is legal only when the ordering it stands for is the one
+ * Postgres gives it — and a row comparison has NO null ordering: a NULL anywhere in either side
+ * makes the whole comparison unknown, so under `asc nulls last` every NULL row would be excluded
+ * from the very page the ordering puts it on. Uniform direction is therefore not enough; every key
+ * has to be a column that cannot hold a NULL.
+ */
+const rowComparable = <Row>(entity: EntityCore<Row>, orderBy: readonly SortKey[]): boolean => {
+  const [first] = orderBy;
+  if (first === undefined || orderBy.length < 2) return false;
+  return orderBy.every(
+    (entry) => entry.direction === first.direction && !isNullableKey(entity, entry.column),
+  );
+};
+
 const seekSql = <Row>(
@@ -111,15 +222,37 @@ entity: EntityCore<Row>,
 ): SqlFragment => {
-  const terms = orderBy.map((entry, index) => {
+  const binds = orderBy.map((entry, index) => seekBind(kindOf(entity, entry.column), seek[index]));
+  // One key is already a scalar comparison; `(("id") > ($1))` is the same plan spelled worse.
+  if (rowComparable(entity, orderBy)) {
+    const columns = join(orderBy.map((entry) => columnRef(entity, entry.column)));
+    // Not-null columns, so no bind here can be `null` — but the type says it can, and `sql`null``
+    // for one would be a seek from a position no row is after.
+    const values = join(binds.map((bind) => bind ?? sql`null`));
+    return orderBy[0]?.direction === 'desc'
+      ? sql`((${columns}) < (${values}))`
+      : sql`((${columns}) > (${values}))`;
+  }
+  const terms = orderBy.flatMap((entry, index) => {
+    const after = seekAfter(
+      columnRef(entity, entry.column),
+      entry.direction,
+      binds[index] ?? null,
+      isNullableKey(entity, entry.column),
+    );
+    // Nothing sorts after a NULL under `nulls last`, so this key's term is dead SQL — dropped
+    // rather than emitted. The remaining keys still carry the page: the equality prefix below
+    // reaches them as `col is null`.
+    if (after === undefined) return [];
     const equal = orderBy
       .slice(0, index)
-      .map((earlier, position) => seekEqual(columnRef(entity, earlier.column), seek[position]));
-    return sql`(${join(
-      [...equal, seekAfter(columnRef(entity, entry.column), entry.direction, seek[index])],
-      ' and ',
-    )})`;
+      .map((earlier, position) =>
+        seekEqual(columnRef(entity, earlier.column), binds[position] ?? null),
+      );
+    return [sql`(${join([...equal, after], ' and ')})`];
   });
-  return sql`(${join(terms, ' or ')})`;
+  // Every key NULL under an ascending order is the very end of the listing — no row follows it,
+  // and `()` is a syntax error.
+  return terms.length === 0 ? NEVER : sql`(${join(terms, ' or ')})`;
 };
 
-const conditions = <Row>(
+export const conditions = <Row>(
   entity: EntityCore<Row>,
@@ -137,2 +270,16 @@ plan: QueryPlan,
 
+/**
+ * NULL's place in the ordering, WRITTEN DOWN rather than inherited from the server's default —
+ * `asc nulls last`, `desc nulls first`. Identical to `@ultimat3/query`'s `orderTerm`, deliberately:
+ * two pagination systems in one framework disagreeing about where a NULL sorts is the ambiguity
+ * axiom 1 exists to forbid, and until 2026-08-24 this package refused a nullable sort key outright
+ * rather than answer the question. Saying it out loud is also what keeps a driver whose default
+ * differs from re-opening the divergence.
+ *
+ * `raw()` is the same closed set of one word it always was — now four words instead of two, all
+ * written here and never derived from a value.
+ */
+const NULLS_LAST = raw('asc nulls last');
+const NULLS_FIRST = raw('desc nulls first');
+
 const orderSql = <Row>(entity: EntityCore<Row>, orderBy: readonly SortKey[]): SqlFragment =>
@@ -142,3 +289,3 @@ join(
       (entry) =>
-        sql`${columnRef(entity, entry.column)} ${raw(entry.direction === 'desc' ? 'desc' : 'asc')}`,
+        sql`${columnRef(entity, entry.column)} ${entry.direction === 'desc' ? NULLS_FIRST : NULLS_LAST}`,
     ),
@@ -148,2 +295,25 @@ );
 /**
+ * The microsecond half of every `timestamptz` sort key, under an output name no entity can declare
+ * (`seekAlias`). Bun's client hands a `timestamptz` back as a JS `Date`, which is milliseconds, so
+ * the row itself CANNOT carry the value the `order by` actually sorted by — a cursor minted from
+ * it cuts the page at a position no row occupies, and every row inside the boundary millisecond is
+ * then served on no page at all.
+ *
+ * `at time zone 'UTC'` rather than a bare `::text`: the bare cast renders in the session's
+ * `TimeZone`, and a page position must not depend on a connection setting.
+ */
+const seekPrecision = <Row>(entity: EntityCore<Row>, plan: QueryPlan): readonly SqlFragment[] => {
+  const seen = new Set<string>();
+  return plan.orderBy.flatMap((entry) => {
+    if (kindOf(entity, entry.column) !== 'timestamptz') return [];
+    const physical = physicalName(entity, entry.column);
+    if (seen.has(physical)) return [];
+    seen.add(physical);
+    return [
+      sql`(${identifier(physical)} at time zone 'UTC')::text as ${identifier(seekAlias(physical))}`,
+    ];
+  });
+};
+
+/**
  * A projection always carries the primary key and the sort keys even when the caller did not
@@ -153,3 +323,6 @@ * ask for them: without those values the page cannot produce the cursor that continues it.
 const projection = <Row>(entity: EntityCore<Row>, plan: QueryPlan): SqlFragment => {
-  if (plan.select === undefined) return join(allColumns(entity).map(identifier));
+  const precise = seekPrecision(entity, plan);
+  if (plan.select === undefined) {
+    return join([...allColumns(entity).map(identifier), ...precise]);
+  }
   const wanted = new Set([
@@ -164,3 +337,3 @@ ...plan.select,
   });
-  return join(names.map(identifier));
+  return join([...names.map(identifier), ...precise]);
 };
@@ -216,113 +389,86 @@
 
-/** `on conflict (…) do update set …`, or `do nothing` when there is nothing to overwrite. */
-export interface ConflictTarget {
-  /** Physical columns of the unique index a collision is judged against. */
-  readonly columns: readonly string[];
-  /** Physical columns a colliding row takes from the incoming one. Empty is `do nothing`. */
-  readonly set: readonly string[];
-}
-
-export interface InsertShape {
-  /** Every physical column written — one list, shared by every row of the statement. */
-  readonly columns: readonly string[];
-  /** How a collision resolves. Absent, it is the caller's error, exactly as it is for one row. */
-  readonly conflict?: ConflictTarget | undefined;
-}
-
 /**
- * The cell of a row that did not name this column. `default` is the second and last `raw()` in
- * this file and, like `asc|desc` above it, a closed set of one word: it is what makes a row inside
- * a many-row `insert` mean what the same row means on its own, where an unnamed column is simply
- * left out. The seek operator used to be a third — it is chosen in TypeScript now
- * (`seekAfter`/`seekEqual`), because a timestamp seek is not one operator.
- */
-const DEFAULT_CELL = raw('default');
-
-const conflictSql = (conflict: ConflictTarget): SqlFragment => {
-  const target = join(conflict.columns.map(identifier));
-  return conflict.set.length === 0
-    ? sql` on conflict (${target}) do nothing`
-    : sql` on conflict (${target}) do update set ${join(
-        conflict.set.map((column) => sql`${identifier(column)} = excluded.${identifier(column)}`),
-      )}`;
-};
-
-/**
- * The one column that cannot be bound as itself. A `jsonb` value is a plain object, and the
- * driver seam refuses one as a parameter (`X_SQL_UNSAFE` — `isBoundValue` takes scalars, a `Date`,
- * a `Uint8Array` and arrays of those); so `bindValues` hands over the JSON TEXT and the cell says
- * what to do with it.
+ * One aggregate over exactly the rows `countStatement` would have counted — the same predicates,
+ * the same soft-delete filter, one function more. Four outputs and always the same four names, so
+ * neither driver reads a column an entity could also have declared:
  *
- * `::text::jsonb` and not `::jsonb`, and the double cast is load-bearing rather than defensive.
- * Measured against Postgres 17.10 through Bun's `sql`: with `$1::jsonb` the server describes the
- * parameter as `jsonb`, the client JSON-ENCODES the string it was given, and `{"a":1}` is stored
- * as the JSON *string* `"{\"a\":1}"` — `jsonb_typeof` says `string`. Pinning the parameter to
- * `text` first makes the client send the characters and the server parse them, which is the one
- * spelling that stores an object.
+ * - `agg_value` — the aggregate itself, as TEXT. `::text` and never a float: `sum(bigint)` is a
+ *   `numeric` Bun would hand back as a string anyway, and pinning it makes `integer` behave the
+ *   same. `aggregate.ts` re-parses it by the column's kind.
+ * - `agg_count` — how many non-null values went in, which is what tells `null` ("no rows") from a
+ *   legitimate zero, and what `avg` divides by.
+ *
+ * `avg` is `round(avg(...), AVG_SCALE)` rather than the server's own scale, because the in-memory
+ * driver has to reach the same digits and "whatever numeric division gives you" is not a rule two
+ * implementations can share.
  */
-/** Physical names of this entity's `jsonb` columns. Resolved ONCE per statement, never per cell. */
-const jsonColumns = <Row>(entity: EntityCore<Row>): ReadonlySet<string> => {
-  const names = new Set<string>();
-  for (const [property, column] of Object.entries(entity.$columns)) {
-    if (column.$meta.kind === 'jsonb') names.add(columnName(property, column.$meta));
-  }
-  return names;
-};
-
-/**
- * `${value}`, plus the cast that column needs. The `raw()` argument is a literal written here and
- * nowhere else — the audit point that call is stays a two-word constant, never a value.
- */
-const cell = (json: ReadonlySet<string>, column: string, value: unknown): SqlFragment =>
-  json.has(column) ? sql`${value}${raw('::text::jsonb')}` : sql`${value}`;
-
-/**
- * One statement for any number of rows. A single row compiles to exactly the text it always did,
- * which is the point: `insertAll([row])` and `insert(row)` are one code path, so there is no
- * second insert builder for the two to drift apart in.
- */
-export const insertStatement = <Row>(
+export const aggregateStatement = <Row>(
   entity: EntityCore<Row>,
-  rows: readonly ReadonlyMap<string, unknown>[],
-  shape: InsertShape,
+  plan: QueryPlan,
+  shape: ReadShape,
+  fn: AggregateFn,
+  column: string,
 ): SqlFragment => {
-  const json = jsonColumns(entity);
-  const tuples = rows.map(
-    (row) =>
-      sql`(${join(
-        shape.columns.map((column) =>
-          row.has(column) ? cell(json, column, row.get(column)) : DEFAULT_CELL,
-        ),
-      )})`,
-  );
-  const conflict = shape.conflict === undefined ? sql`` : conflictSql(shape.conflict);
-  return sql`insert into ${identifier(entity.$table)} (${join(
-    shape.columns.map(identifier),
-  )}) values ${join(tuples)}${conflict} returning *`;
+  const target = columnRef(entity, column);
+  const value =
+    fn === 'sum'
+      ? sql`sum(${target})`
+      : fn === 'avg'
+        ? sql`round(avg(${target}), ${AVG_SCALE})`
+        : fn === 'min'
+          ? sql`min(${target})`
+          : sql`max(${target})`;
+  return sql`select ${value}::text as agg_value, count(${target}) as agg_count from ${identifier(
+    entity.$table,
+  )} where ${conditions(entity, plan, shape)}`;
 };
 
+/** What an aggregate comes back as. Both names are fixed, so neither can be a column's. */
+export interface AggregateRow {
+  readonly agg_value: unknown;
+  readonly agg_count: unknown;
+}
+
 /**
- * `returning` is a parameter and has no default, because the three callers want three different
- * answers and the wrong one is not visible in the result: `update(id, patch)` needs the stored row,
- * a soft delete and a filtered write need a count, and `returning *` on a filtered write over a
- * whole tenant streams every matched row into the process for nobody to read. A default would make
- * that the quiet case.
+ * The distinct currencies among the rows an aggregate is about to cover. A separate statement
+ * rather than a clever one: `sum(minor)` over two currencies is a number in neither, and the only
+ * honest answer is to refuse — which needs the list, not a boolean.
+ *
+ * Bounded at three, because the refusal names them and a caller with three already knows.
  */
-export const updateStatement = <Row>(
+export const currenciesStatement = <Row>(
   entity: EntityCore<Row>,
   plan: QueryPlan,
-  values: ReadonlyMap<string, unknown>,
   shape: ReadShape,
-  returning: boolean,
+  currencyColumn: string,
+  scaleColumn: string | null,
 ): SqlFragment => {
-  const json = jsonColumns(entity);
-  return sql`update ${identifier(entity.$table)} set ${join(
-    [...values].map(([column, value]) => sql`${identifier(column)} = ${cell(json, column, value)}`),
-  )} where ${conditions(entity, plan, shape)}${returning ? sql` returning *` : sql``}`;
+  const currency = identifier(currencyColumn);
+  // The SCALE is half of what makes two amounts incomparable and it is the half with no symptom:
+  // `{ minor: 5, currency: 'USD' }` is five cents and the same row at `scale: 6` is five millionths
+  // of a dollar. A table with no scale column has one unit per currency by construction.
+  const scale = scaleColumn === null ? sql`null` : identifier(scaleColumn);
+  return sql`select distinct ${currency} as group_value, ${scale} as group_scale from ${identifier(
+    entity.$table,
+  )} where ${conditions(entity, plan, shape)} and ${currency} is not null limit 3`;
 };
 
-/** Only reached when the entity has no soft-delete column, so there is no filter to apply. */
-export const deleteStatement = <Row>(entity: EntityCore<Row>, plan: QueryPlan): SqlFragment =>
-  sql`delete from ${identifier(entity.$table)} where ${conditions(entity, plan, {
-    includeDeleted: true,
-  })}`;
+/** One `(currency, scale)` pair the rows an aggregate covers actually use. */
+export interface MoneyUnitRow {
+  readonly group_value: unknown;
+  readonly group_scale: unknown;
+}
+
+/**
+ * The planner's own row estimate for a table — `reltuples`, which is what `ANALYZE` last wrote and
+ * what every query plan in the database is already costed against. `count(*)` walks every visible
+ * row (MVCC gives no shortcut), so on a large table it is the read that exceeds a web role's
+ * `statement_timeout`, and no index can make it cheaper: the `fix:` on that timeout tells an author
+ * to add one, and following it changes nothing.
+ *
+ * `to_regclass` rather than a name comparison, so a search_path change cannot silently answer for a
+ * different schema's table of the same name — and `-1` is what Postgres 14+ stores for a table that
+ * has never been analysed, which is an answer, not an estimate.
+ */
+export const estimateStatement = (table: string): SqlFragment =>
+  sql`select reltuples::bigint as estimate from pg_class where oid = to_regclass(${table})`;

src/plan.ts

@@ -6,2 +6,3 @@ // Single responsibility: turn repository arguments into the `QueryPlan` a driver executes.
 
+import { assertSeekable } from './cursor';
 import type { EntityCore } from './entity';
@@ -53,5 +54,15 @@ import { EntityError, invariantViolated, patchEmpty, writeUnfiltered } from './errors';
  *
+ * The tiebreak takes the LAST DECLARED key's direction rather than an unconditional `asc`, and
+ * that is not a preference. `IndexInit.order` is ONE direction for a whole index, so
+ * `orderBy('createdAt', 'desc')` used to run `created_at desc, id asc` — an order this framework's
+ * own DSL cannot declare an index for, whatever the author wrote. It also decided the seek's
+ * shape: a mixed order has no row comparison, so the seek fell to the or-chain, which measured on
+ * Postgres 16 as a BitmapOr plus a Sort where `(created_at, id) < ($1, $2)` is an Index Only Scan.
+ * A caller who wants the mixed order still writes it — naming the key itself is what turns the
+ * append off.
+ *
  * Exported because a chain can be judged before it runs: `inBatches()` refuses an ordering that
- * cannot carry a cursor, and it has to be looking at the order the driver will send rather than at
- * the one the caller typed.
+ * cannot carry a cursor — an undeclared column, or a nullable key in the TIEBREAK, never an
+ * ordinary nullable one — and it has to be looking at the order the driver will send rather than
+ * at the one the caller typed.
  */
@@ -61,8 +72,11 @@ export const totalOrder = <Row>(
   ordered: readonly SortKey[],
-): readonly SortKey[] => [
-  ...ordered,
-  ...entity.$primaryKey
-    .filter((property) => !ordered.some((entry) => entry.column === property))
-    .map((property) => ({ column: property, direction: 'asc' as const })),
-];
+): readonly SortKey[] => {
+  const direction = ordered.at(-1)?.direction ?? 'asc';
+  return [
+    ...ordered,
+    ...entity.$primaryKey
+      .filter((property) => !ordered.some((entry) => entry.column === property))
+      .map((property) => ({ column: property, direction })),
+  ];
+};
 
@@ -89,4 +103,16 @@ /**
 
+/**
+ * Whether an ordering can carry a page position is a property of the ORDER, so it is decided here
+ * — where the order the driver will send is first known — and not in `cursorFor`, which runs only
+ * when a page found one row past its limit. That is what made the refusal depend on the table:
+ * `orderBy('publishedAt', 'desc').limit(20)` over a nullable column was green on fifteen seeded
+ * rows for as long as the test suite existed and `X_INVARIANT_VIOLATED` on the first read past
+ * twenty in production. `assertBatchable` has always judged `inBatches()` this way.
+ *
+ * `cursorFor` and `seekFrom` still call it: they are reached from a driver directly.
+ */
 export const planFor = <Row>(entity: EntityCore<Row>, args: FindManyArgs): QueryPlan => {
   if (args.limit !== undefined) assertPageSize(entity.$name, args.limit);
+  const orderBy = totalOrder(entity, args.orderBy ?? []);
+  assertSeekable(entity, orderBy);
   const scoped =
@@ -99,3 +125,3 @@ args.orgId === undefined || entity.$tenantColumn === null
     where: [...(args.where ?? []), ...scoped],
-    orderBy: totalOrder(entity, args.orderBy ?? []),
+    orderBy,
     limit: args.limit ?? DEFAULT_PAGE_SIZE,
@@ -117,5 +143,40 @@ ...(args.cursor === undefined || args.cursor === null ? {} : { cursor: args.cursor }),
   operation: string,
-): QueryPlan => scopedPlan(entity.$name, entity.$tenantColumn, operation, planFor(entity, args));
+): QueryPlan => {
+  const plan = planFor(entity, args);
+  // BEFORE tenancy, deliberately. Applied after, a tenant-scoped entity had no reachable call at
+  // all: unscoped it was `X_TENANCY_UNSCOPED` and scoped it was the refusal below, so the method
+  // was declared and unusable — the defect class this repo keeps re-shipping. One refusal, and it
+  // names which of the two situations the caller is in.
+  if (operation === 'approximateCount') assertEstimable(entity, plan);
+  return scopedPlan(entity.$name, entity.$tenantColumn, operation, plan);
+};
 
 /**
+ * An estimate is the TABLE's. `reltuples` knows nothing about a predicate, so a filtered chain
+ * asking for one would be answered a different question from the one it asked — and a caller
+ * reading `posts.where({ orgId }).approximateCount()` as "roughly how many of mine" would be
+ * handed every other tenant's rows too, which is the reading that matters.
+ *
+ * A TENANT-SCOPED entity is therefore refused outright, filters or none: its whole-table estimate
+ * is a number about every tenant, and a per-tenant row count is not a thing the planner holds.
+ * That refusal is not a limitation of this method, it is what the method means.
+ */
+const assertEstimable = <Row>(entity: EntityCore<Row>, plan: QueryPlan): void => {
+  if (entity.$tenantColumn !== null) {
+    throw new EntityError({
+      code: 'X_APPROXIMATE_COUNT_FILTERED',
+      cause: `${entity.$name}.approximateCount() is a whole-TABLE estimate and ${entity.$name} is scoped by ${entity.$tenantColumn} — the planner holds one number for every tenant together`,
+      fix: `${entity.$name}.count()   # the exact answer, scoped to the acting actor's tenant as every other read is`,
+    });
+  }
+  if (plan.where.length === 0) return;
+  const named = plan.where.map((each) => each.column).join(', ');
+  throw new EntityError({
+    code: 'X_APPROXIMATE_COUNT_FILTERED',
+    cause: `${entity.$name}.approximateCount() carries ${plan.where.length} predicate(s) (${named}) — the planner estimates the TABLE and knows nothing about them`,
+    fix: `${entity.$name}.count()   # the exact answer for a filtered chain; approximateCount() answers for the whole table only`,
+  });
+};
+
+/**
  * The plan for an id-addressed write. A write is a query too: without the same guard,
@@ -122,0 +183,0 @@ * `update(id, patch)` on a tenant-scoped entity is a cross-tenant write that no read path

src/query.ts

@@ -18,3 +18,3 @@ // The chainable read. Every chain terminates in a cursor page — `page()` returns rows plus the
 import type { Operator, Predicate, QueryPlan, SortDirection, SortKey } from './tenancy';
-import type { ColumnMap, IdOf, Insertable, RowPatch } from './types';
+import type { ColumnMap, IdOf, Insertable, MoneyValue, RowPatch } from './types';
 
@@ -63,4 +63,8 @@ /**
    * `await using` does the same for a handle kept in a variable. A chain that cannot carry a
-   * cursor — a nullable sort column — and a chain that also called `limit()` are refused here,
-   * not one batch later.
+   * cursor and a chain that also called `limit()` are refused here, not one batch later. An
+   * ORDINARY nullable sort column is not one of those: it orders `nulls last` ascending and
+   * `nulls first` descending, and the cursor carries that position. What is refused is a sort key
+   * that leaves the order un-total — an undeclared column, a money property named without its
+   * part, or a nullable PRIMARY-KEY column, where `null = null` is unknown and two such rows are
+   * one position to the seek (`cursor.ts`'s `assertSeekable`).
    */
@@ -88,2 +92,38 @@ inBatches(size: number): BatchIterator<Row>;
   countBy<K extends keyof Row & string>(column: K): Promise<ReadonlyMap<Row[K], number>>;
+  /**
+   * The four SQL aggregates, over exactly the rows `count()` counts — the chain's filters, its
+   * tenancy and its soft-delete visibility, never its page. "Total spend this month" is
+   * `payments.where({ orgId }).andWhere('paidAt', 'gte', from).sum('amount')`, one statement,
+   * rather than a page loop or a hand-written query outside every guard this layer applies.
+   *
+   * **Never a float.** `sum` and `avg` answer decimal TEXT whatever the column was — the sum of a
+   * million `integer` rows is not an `integer` and `Number()` on it loses digits past 2^53 — and a
+   * money column answers a `MoneyValue` in integer minor units. `min`/`max` answer the row's own
+   * type, because the answer is one of the values that went in.
+   *
+   * `null` for an empty set in every one of them, which is what SQL answers: a `0` would claim
+   * rows were seen.
+   *
+   * Refused rather than answered: a kind with no aggregate the two drivers can agree on (`text`
+   * ordering is the database's collation here and JS code-unit order there), `avg` over money
+   * (every answer would be a silent rounding of a fraction of a minor unit), and an amount
+   * covering more than one currency or scale.
+   */
+  sum<K extends keyof Row & string>(
+    column: K,
+  ): Promise<(Row[K] extends MoneyValue | null ? MoneyValue : string) | null>;
+  avg<K extends keyof Row & string>(column: K): Promise<string | null>;
+  min<K extends keyof Row & string>(column: K): Promise<Row[K] | null>;
+  max<K extends keyof Row & string>(column: K): Promise<Row[K] | null>;
+  /**
+   * The planner's own row estimate for the table — `reltuples`, one row out of `pg_class`, and the
+   * only count that stays constant time as the table grows. `count()` walks every visible row
+   * because MVCC gives it no shortcut, so past a few million it is the read that trips a web
+   * role's `statement_timeout` and no index can make it cheaper.
+   *
+   * The whole TABLE, never the chain's filters — a filtered chain is `X_APPROXIMATE_COUNT_FILTERED`
+   * rather than an estimate that answers a different question than the one asked. `null` when the
+   * table has never been analysed, which is the absence of an estimate and not an estimate of zero.
+   */
+  approximateCount(): Promise<number | null>;
   /** The plan this chain describes. Safe to log — `describePlan()` elides values. */
@@ -282,2 +322,18 @@ plan(): QueryPlan;
 
+    // The one cast on each of these, and it is the same seam `countBy` and `select()` have: the
+    // driver contract is row-agnostic because a column name is a runtime string there, while the
+    // chain knows which property it just named and therefore what comes back.
+    sum: async <K extends keyof Row & string>(column: K) =>
+      (await repo.aggregate('sum', column, args())) as
+        | (Row[K] extends MoneyValue | null ? MoneyValue : string)
+        | null,
+    avg: async (column: keyof Row & string) =>
+      (await repo.aggregate('avg', column, args())) as string | null,
+    min: async <K extends keyof Row & string>(column: K) =>
+      (await repo.aggregate('min', column, args())) as Row[K] | null,
+    max: async <K extends keyof Row & string>(column: K) =>
+      (await repo.aggregate('max', column, args())) as Row[K] | null,
+
+    approximateCount: () => repo.approximateCount(args()),
+
     async countBy<K extends keyof Row & string>(column: K) {
@@ -284,0 +340,0 @@ // The one cast on this terminal, and it is the same seam `select()` has: the driver contract

src/registry.ts

@@ -6,2 +6,3 @@ // The entity registry. Every `entity()` call registers here, which is what makes
 
+import type { IndexMethod } from '@ultimat3/db';
 import { entityDuplicate } from './errors';
@@ -76,2 +77,8 @@ import type { InvariantKind } from './invariants';
   readonly order: 'asc' | 'desc' | null;
+  /**
+   * The access method, `undefined` for the `btree` every index was before this existed. Absent
+   * rather than `null`, matching `IndexDescriptionLike.using` in `@ultimat3/db`: a snapshot that
+   * predates the field and an index that declares nothing read the same, so nothing regenerates.
+   */
+  readonly using?: IndexMethod | undefined;
 }
@@ -78,0 +85,0 @@

src/repo.ts

@@ -10,14 +10,4 @@ // The repository seam. Two rules are structural rather than advisory:
 
-import { keyOf } from './batch-read';
-import { conflictKeyOf, conflictKeys, upsertPlan } from './bulk-write';
-import { entityNow } from './clock';
-import { narrowMoney } from './columns';
-import { countsFrom, groupColumnOf } from './count-by';
-import { cursorFor, kindOf, seekFrom, valueAt } from './cursor';
-import { type EntityCore, SOFT_DELETE_COLUMN } from './entity';
-import { notFound } from './errors';
-import { compareByKind, matchesPredicate } from './memory-match';
-import { deletePlan, idPlan, readPlan, singleKeyOf, updatePlan } from './plan';
-import type { Predicate, QueryPlan, SortKey } from './tenancy';
-import { assertRowTenant } from './tenancy';
+import type { AggregateFn } from './aggregate';
+import type { Predicate, SortKey } from './tenancy';
 import type { IdOf, RowPatch } from './types';
@@ -140,2 +130,16 @@
   countBy(column: string, args?: FindManyArgs): Promise<ReadonlyMap<unknown, number>>;
+  /**
+   * One aggregate over exactly the rows `count(args)` counts. Row-agnostic here and typed on the
+   * chain, the same seam `countBy` has: a column name is a runtime string at this contract.
+   *
+   * `null` for an empty set in every function, which is what SQL answers — a `0` would claim rows
+   * were seen. A `sum` or an `avg` comes back as decimal TEXT and a money aggregate as a
+   * `MoneyValue`; neither is ever a float.
+   */
+  aggregate(fn: AggregateFn, column: string, args?: FindManyArgs): Promise<unknown>;
+  /**
+   * The planner's own row estimate for the table — not a count, and never filtered. `null` when
+   * the table has never been analysed, which is a fact and not an estimate.
+   */
+  approximateCount(args?: FindManyArgs): Promise<number | null>;
 }
@@ -159,317 +163,1 @@
 }
-
-const field = (row: unknown, property: string): unknown =>
-  typeof row === 'object' && row !== null ? (row as Record<string, unknown>)[property] : undefined;
-
-/** Lexicographic over the sort keys, direction applied. `> 0` means "after the cursor". */
-const compareToSeek = <Row>(
-  entity: EntityCore<Row>,
-  plan: QueryPlan,
-  row: unknown,
-  seek: readonly unknown[],
-): number => {
-  for (const [index, entry] of plan.orderBy.entries()) {
-    // The COLUMN's kind, not the value's: the seek was revived from the same kind (`cursor.ts`),
-    // so a `bigint` column compares its stored decimal string against a revived `BigInt` as one
-    // number instead of as two pieces of text.
-    const order = compareByKind(
-      kindOf(entity, entry.column),
-      valueAt(row, entry.column),
-      seek[index],
-    );
-    if (order !== 0) return entry.direction === 'desc' ? -order : order;
-  }
-  return 0;
-};
-
-/**
- * Where the next page starts. By sort position, not by the previous row's id: that row may have
- * been deleted between the two requests, and an id that is no longer there would restart
- * pagination at the top instead of continuing it.
- */
-const afterCursor = <Row>(
-  entity: EntityCore<Row>,
-  plan: QueryPlan,
-  found: readonly Row[],
-): number => {
-  const seek = seekFrom(entity, plan);
-  if (seek === undefined) return 0;
-  const start = found.findIndex((row) => compareToSeek(entity, plan, row, seek) > 0);
-  return start === -1 ? found.length : start;
-};
-
-/**
- * The default driver: correct semantics, no database. `x dev` uses it before the first
- * migration and tests use it everywhere. Postgres is the production driver and implements
- * this same interface.
- */
-export const memoryRepo = <Row>(
-  entity: EntityCore<Row>,
-  seed: readonly Row[] = [],
-): MemoryRepo<Row> => {
-  /**
-   * A stored row's key, spelled the way `batch-read.ts` spells an id — because Postgres compares a
-   * `uuid` as a VALUE and prints it lower-cased, so `findById(UPPER)` reads the row there while
-   * `String(...)` missed it here: `null` from a read and `X_NOT_FOUND` from a write, against a row
-   * that exists, reachable from a path parameter, a client-supplied id or a legacy import.
-   */
-  const storeKey = (row: unknown): string =>
-    entity.$primaryKey
-      .map((property) => keyOf(kindOf(entity, property) ?? '', field(row, property)))
-      .join('
');
-  /** The same key, from the id a caller named rather than from a row it has in hand. */
-  const idStoreKey = (id: unknown, operation: string): string =>
-    keyOf(kindOf(entity, singleKeyOf(entity, operation)) ?? '', id);
-  const rows = new Map<string, Row>(seed.map((row) => [storeKey(row), row]));
-
-  const rowsOf = (plan: QueryPlan, args: FindManyArgs): Row[] => {
-    const visible = (row: Row): boolean =>
-      !entity.$softDelete ||
-      args.includeDeleted === true ||
-      field(row, SOFT_DELETE_COLUMN) === null ||
-      field(row, SOFT_DELETE_COLUMN) === undefined;
-    return [...rows.values()]
-      .filter((row) => plan.where.every((predicate) => matchesPredicate(entity, row, predicate)))
-      .filter(visible)
-      .sort((left, right) => {
-        for (const entry of plan.orderBy) {
-          const order = compareByKind(
-            kindOf(entity, entry.column),
-            valueAt(left, entry.column),
-            valueAt(right, entry.column),
-          );
-          if (order !== 0) return entry.direction === 'desc' ? -order : order;
-        }
-        return 0;
-      });
-  };
-
-  const select = (args: FindManyArgs, operation: string): { plan: QueryPlan; found: Row[] } => {
-    const plan = readPlan(entity, args, operation);
-    return { plan, found: rowsOf(plan, args) };
-  };
-
-  const write = (given: Row, options: RepoOptions | undefined, operation: string): Row => {
-    // `MoneyInput` lets a writer hand a `bigint`; a stored row holds the value type. The Postgres
-    // driver narrows in `bindValues` and reads its answer back through `returning *`, so without
-    // this an in-memory row would be the one row in the framework `JSON.stringify` refuses.
-    const row = narrowMoney(entity.$columns, given);
-    // Beside `$assert`, and before the row lands: a write is judged by the tenant it names as well
-    // as by the invariants it declares, and the Postgres driver runs the same pair in `writeRows`.
-    // `update` reaches here with the STORED row merged under its patch, so a patch that moves a row
-    // out of this tenant is refused by the same call that refuses an insert into another one.
-    assertRowTenant(entity.$name, entity.$tenantColumn, operation, row);
-    entity.$assert(row);
-    const key = storeKey(row);
-    const previous = rows.get(key);
-    options?.tx?.onRollback(() => {
-      if (previous === undefined) rows.delete(key);
-      else rows.set(key, previous);
-    });
-    rows.set(key, row);
-    return row;
-  };
-
-  // The same guard the read path applies: on a tenant-scoped entity an id alone is not enough
-  // to name a row, so `update`/`delete` resolve through a plan rather than through the map.
-  const addressed = (id: string, options: RepoOptions | undefined, operation: string): Row => {
-    const plan = idPlan(entity, id, options, operation);
-    const current = rows.get(idStoreKey(id, operation));
-    // A soft-deleted row is hidden from writes too — `delete` on one is `X_NOT_FOUND`, not a
-    // second stamp, which is what the Postgres driver's `deleted_at is null` clause already says.
-    const hidden =
-      current !== undefined &&
-      entity.$softDelete &&
-      field(current, SOFT_DELETE_COLUMN) !== null &&
-      field(current, SOFT_DELETE_COLUMN) !== undefined;
-    if (
-      current === undefined ||
-      hidden ||
-      !plan.where.every((predicate) => matchesPredicate(entity, current, predicate))
-    ) {
-      throw notFound(entity.$name, id);
-    }
-    return current;
-  };
-
-  // Every method is async: a repository call that fails must reject, never throw
-  // synchronously, or half the call sites would need two error paths.
-  return {
-    async findById(id, options) {
-      const { found } = select(
-        { ...options, where: [{ column: singleKeyOf(entity, 'findById'), op: 'eq', value: id }] },
-        'findById',
-      );
-      return found[0] ?? null;
-    },
-
-    async findMany(args = {}) {
-      const { plan, found } = select(args, 'findMany');
-      const start = afterCursor(entity, plan, found);
-      const page = found.slice(start, start + plan.limit);
-      const last = page.at(-1);
-      const more = start + page.length < found.length;
-      return {
-        rows: page,
-        nextCursor:
-          more && last !== undefined ? cursorFor(entity, plan, last, storeKey(last)) : null,
-      };
-    },
-
-    async insert(values, options) {
-      return write(values, options, 'insert');
-    },
-
-    async insertAll(batch, options) {
-      // The whole batch is judged before any of it lands: Postgres refuses the statement as one,
-      // so a row an invariant rejects — or one naming a tenant this actor may not write — must not
-      // leave the rows before it stored here either. `write` re-checks both per row; this loop is
-      // what makes the batch all-or-nothing, which is the half a per-row check cannot give.
-      for (const row of batch) {
-        assertRowTenant(entity.$name, entity.$tenantColumn, 'insertAll', row);
-        entity.$assert(row);
-      }
-      return batch.map((row) => write(row, options, 'insertAll'));
-    },
-
-    async upsertAll(batch, args) {
-      // The INCOMING rows, judged before any of them is matched: under `onMatch: 'nothing'` a
-      // colliding row is skipped and never reaches `write()`, so checking only what lands would
-      // let a row naming another tenant through whenever it happened to collide.
-      for (const row of batch) {
-        assertRowTenant(entity.$name, entity.$tenantColumn, 'upsertAll', row);
-        entity.$assert(row);
-      }
-      const plan = upsertPlan(entity, batch, args.onConflict, args.onMatch ?? 'update');
-      const keys = conflictKeys(entity, plan, batch);
-      // The stored rows under the same key, so "does this collide" is the question the unique
-      // index answers in Postgres and not a scan per row. A soft-deleted row still occupies its
-      // key here, because the index it would collide with there is not partial either — and a row
-      // whose target holds a null occupies none, because the index is `NULLS DISTINCT`.
-      const stored = new Map<string, Row>();
-      for (const row of rows.values()) {
-        const key = conflictKeyOf(entity, plan.on, row);
-        if (key !== undefined) stored.set(key, row);
-      }
-      const written: Row[] = [];
-      for (const [position, row] of batch.entries()) {
-        const key = keys[position];
-        const existing = key === undefined ? undefined : stored.get(key);
-        // `do nothing` writes no row, and `returning *` therefore names none: a skipped row is
-        // absent from the result rather than present and unchanged.
-        if (existing !== undefined && plan.set.length === 0) continue;
-        const merged =
-          existing === undefined
-            ? row
-            : Object.assign(
-                {},
-                existing,
-                Object.fromEntries(plan.set.map((property) => [property, field(row, property)])),
-              );
-        // `UpsertArgs extends RepoOptions`, so the args ARE the options — one bag, and a `tx`
-        // passed to an upsert registers its undo exactly as it does for every other write here.
-        const result = write(merged, args, 'upsertAll');
-        // Filed as it lands, so a later row of the same batch collides with an earlier one exactly
-        // as it would with a row the request stored a moment before it.
-        if (key !== undefined) stored.set(key, result);
-        written.push(result);
-      }
-      return written;
-    },
-
-    async update(id, patch, options) {
-      return write(Object.assign({}, addressed(id, options, 'update'), patch), options, 'update');
-    },
-
-    async delete(id, options) {
-      const current = addressed(id, options, 'delete');
-      // Soft delete hides the row without losing it; the column's presence is the switch.
-      if (entity.$softDelete) {
-        write(Object.assign({}, current, { [SOFT_DELETE_COLUMN]: entityNow() }), options, 'delete');
-        return;
-      }
-      const key = storeKey(current);
-      options?.tx?.onRollback(() => rows.set(key, current));
-      rows.delete(key);
-    },
-
-    async deleteWhere(filter, options) {
-      // `rowsOf` is the read path: the same predicates, the same tenant scoping, and the same
-      // soft-delete visibility. A row already stamped is not matched, so a second call cannot
-      // move `deletedAt` forward — which is what the Postgres driver's `deleted_at is null`
-      // clause says there.
-      const doomed = rowsOf(deletePlan(entity, filter, options, 'deleteWhere'), {});
-      for (const row of doomed) {
-        if (entity.$softDelete) {
-          write(
-            Object.assign({}, row, { [SOFT_DELETE_COLUMN]: entityNow() }),
-            options,
-            'deleteWhere',
-          );
-          continue;
-        }
-        const key = storeKey(row);
-        options?.tx?.onRollback(() => rows.set(key, row));
-        rows.delete(key);
-      }
-      return doomed.length;
-    },
-
-    async updateWhere(filter, patch, options) {
-      const plan = updatePlan(entity, filter, patch, options, 'updateWhere');
-      // The PATCH, judged whole and before the rows are read — the same call `postgresRepo` makes
-      // before its statement exists. Inside the loop below it is judged only where a row was
-      // matched, so a patch handing rows to another tenant was refused or accepted depending on
-      // what the table happened to hold: `updateWhere(filter, { orgId: theirs })` over a filter
-      // matching nothing answered `0` here and threw there, from one call.
-      assertRowTenant(entity.$name, entity.$tenantColumn, 'updateWhere', patch);
-      // `rowsOf` again, so a soft-deleted row is as unreachable here as it is through
-      // `addressed()` — patching a row the app has already deleted is not an update, it is a
-      // resurrection nobody asked for. `write` re-asserts the invariants on each result.
-      const found = rowsOf(plan, {});
-      for (const row of found) write(Object.assign({}, row, patch), options, 'updateWhere');
-      return found.length;
-    },
-
-    async count(args = {}) {
-      return select(args, 'count').found.length;
-    },
-
-    async countBy(column, args = {}) {
-      // Refused before a row is read, and by the same function the Postgres driver calls: a column
-      // a map cannot be keyed by is that mistake in both drivers or in neither.
-      groupColumnOf(entity, column, 'countBy');
-      const { found } = select(args, 'countBy');
-      const groups = new Map<unknown, number>();
-      for (const row of found) {
-        // `?? null`, so a property this row never carried lands in the same group Postgres puts a
-        // NULL row in — and `0`, `''` and `false` stay the values they are.
-        const value = field(row, column) ?? null;
-        groups.set(value, (groups.get(value) ?? 0) + 1);
-      }
-      return countsFrom(entity, column, 'countBy', [...groups]);
-    },
-
-    reset() {
-      rows.clear();
-      for (const row of seed) rows.set(storeKey(row), row);
-    },
-  };
-};
-
-let txCounter = 0;
-
-/** In-memory transactor: undo closures registered by drivers run on failure. */
-export const memoryTransactor = (): Transactor => ({
-  async run(work) {
-    const undos: (() => void)[] = [];
-    txCounter += 1;
-    const tx: Tx = { id: `tx-${txCounter}`, onRollback: (undo) => undos.push(undo) };
-    try {
-      return await work(tx);
-    } catch (error) {
-      for (const undo of undos.reverse()) undo();
-      throw error;
-    }
-  },
-});

src/tenancy.ts

@@ -16,2 +16,14 @@ // Multi-tenancy is a guard, not a convention. An entity with a tenant column is read AND written
 
+/**
+ * The predicate vocabulary, closed. The last four are the CONTAINMENT half, added 2026-08-24: a
+ * `json()` or `arrayOf()` column was declared, written and then unfilterable — the ten operators
+ * before them could compare a column to a scalar and nothing else — so an app storing either had
+ * to leave the query language for hand-written SQL, which is the one read path in this framework
+ * with no tenancy guard on it. Their meaning is Postgres', written once in `containment.ts` and
+ * read by both drivers.
+ *
+ * There is deliberately no jsonpath EXPRESSION operator beside them: `contains` already matches
+ * nested structure (`data @> '{"a":{"b":1}}'`), and a path language inside the query language
+ * would be a second way to ask one question.
+ */
 export type Operator =
@@ -27,3 +39,7 @@ | 'eq'
   | 'is-null'
-  | 'is-not-null';
+  | 'is-not-null'
+  | 'contains'
+  | 'contained-by'
+  | 'overlaps'
+  | 'has-key';
 
@@ -200,3 +216,3 @@ export interface Predicate {
  *
- * Runtime only. There is no build-time tenancy step in `x verify` — its 17 steps check none — and
+ * Runtime only. There is no build-time tenancy step in `x verify` — its 20 steps check none — and
  * there cannot usefully be one: the tenant is a request-time value, so a compiler could only prove
@@ -203,0 +219,0 @@ * that some argument was passed, which is exactly the thing that was never a guarantee. That is

src/types.ts

@@ -9,2 +9,4 @@ // The structural vocabulary of a column. The physical layer is this package's own hand-written
 
+import type { IndexMethod } from '@ultimat3/db';
+
 /**
@@ -272,2 +274,10 @@ * Postgres types the builders emit. `money` expands to `bigint` + `char(3)` (+ a nullable
   readonly where?: string;
+  /**
+   * The access method. Absent is `btree`, which is Postgres' own default and what every index
+   * declared before this field existed is — so an entity that names none emits the statement it
+   * always emitted, byte for byte, and no app's sidecar regenerates. `@ultimat3/db` owns the
+   * closed set (`INDEX_METHODS`); redeclaring the union here would be the second declaration of
+   * one fact that this release exists to stop.
+   */
+  readonly using?: IndexMethod;
 }

No alert changes

Improved metrics

Total package byte prevSize

607177

19.34%

Number of package files

49

16.67%

Lines of code

9252

17.65%

Number of lines in readme file

733

19.38%

Dependency changes

• + Added

  @ultimat3/core@12.0.0
  (transitive)

• + Added

  @ultimat3/db@12.0.0
  (transitive)

• + Added

  @ultimat3/schema@12.0.0
  (transitive)

• + Added

  @ultimat3/time@12.0.0
  (transitive)

• - Removed

  @ultimat3/core@11.3.0
  (transitive)

• - Removed

  @ultimat3/db@11.3.0
  (transitive)

• - Removed

  @ultimat3/schema@11.3.0
  (transitive)

• - Removed

  @ultimat3/time@11.3.0
  (transitive)

• Updated

  @ultimat3/core@12.0.0

• Updated

  @ultimat3/db@12.0.0

• Updated

  @ultimat3/schema@12.0.0

• Updated

  @ultimat3/time@12.0.0