mv-maintenance.md

Materialized-View Maintenance

Stability: Beta — see Stability Tiers.

How a materialized view is kept equal to its body: how the engine chooses a maintenance strategy at create time, and what each strategy does to the stored rows when a source row changes. Maintenance is synchronous and transactional — it rides the writing statement — so there is no window in which a view disagrees with its sources.

Maintenance strategy

Invariant: MV-006, MV-007, MV-008

Every materialized-view body is maintainable; the only question is how cheaply. At create the manager picks a maintenance strategy via a backward (maintenance-direction) cost gate (selectMaintenanceStrategy in planner/cost/index.ts): the cheapest structurally-sound strategy for the body, with an always-correct full-rebuild floor as the default when no bounded-delta arm applies. No body is rejected for its shape. Five create-time rejections remain, none shape-based:

• a non-deterministic body (random(), now(), a volatile UDF) without pragma nondeterministic_schema — no maintenance could keep a non-deterministic body equal to its plain view;
• a bag body with no provable unique key and no coarsened lineage key — there is no row identity to materialize on (see Primary key inference);
• a body that produces no relational output (degenerate);
• a body that reads no source table (select 42, a bare values list) — it reaches the floor (no bounded-delta arm models a sourceless body), which has no source to index the plan under and no write that could ever dirty it, so there is nothing to maintain;
• a body whose only sound strategy is full-rebuild and whose largest source exceeds the size threshold (pragma materialized_view_rebuild_row_threshold, default 10 000; set 0 to disable) — synchronous per-statement rebuild over a large source is pathological, so it is steered to a plain view. Below the threshold the floor maintains it transparently.

A sixth, host-conditional reject — the replicable-determinism gate — sits next to (not in place of) the determinism reject and is inert by default: it fires only when the resolved backing host declares requiresReplicableDerivations (the future sync-store; memory and store do not, so an ordinary MV sees zero behavior change). When a host demands it, the create rejects any body that calls a non-replicable function — one not asserted bit-identical across peers/platforms/app-versions, stronger than per-database determinism. Built-in functions auto-qualify (Quereus owns its collation / case-folding / numeric formatting, so a deterministic builtin cannot drift between peers' JS engines); a UDF opts in with replicable: true at registration. The check walks the whole analyzed body (projection, WHERE, GROUP BY, aggregate / TVF arguments, nested calls) and names the offending function. It is orthogonal to the determinism gate — a replicable-host body is already required deterministic — so pragma nondeterministic_schema does not lift it (that pragma waives only the weaker per-database determinism class; a replicating host's bit-identity requirement cannot be locally waived without breaking convergence). Window functions are builtin-only (no UDF path) and inherently replicable.

The gate runs at maintenance-plan registration, which on the alter table … set maintained attach path fires before a module's late durable-backing seam (ensureBackingForAttach). There the backing host is resolved leniently — skipped when no host resolves yet — which is sound only because a host declaring requiresReplicableDerivations must resolve its host capability surface eagerly (it may still materialize the physical store late). That eager-resolution invariant is documented on the capability and enforced by a defensive guard in the attach core: a host that both demands replicable derivations and defers getBackingHost to the late seam fails the attach with a loud INTERNAL error rather than silently letting a non-replicable body slip the gate.

The same host-conditional gate also covers custom collations — a second gate of the same shape under the same requiresReplicableDerivations capability. A custom collation whose sort/fold governs derived bytes (a comparison in WHERE/ON/HAVING, ORDER BY, GROUP BY, DISTINCT/set-op dedup, or the backing key) can fold or order differently across peers' platforms and so diverge derived bytes — exactly the hazard the function gate prevents. Built-in collations (BINARY/NOCASE/RTRIM) auto-qualify (pure JS string operations — </>, locale-independent toLowerCase(), ASCII-space trim — bit-identical across peers' JS engines); a custom collation opts in with db.registerCollation(name, cmp, { replicable: true }). The check has two sources: (1) the body walk reads each scalar node's resolved collationName (an explicit COLLATE, a declared/default column collation, a comparison's effective collation, or an ORDER BY / GROUP BY / DISTINCT key — all ride some scalar node's type), and (2) the maintained table's own declared backing-key collations (PK column collations + secondary UNIQUE per-column enforcement collations), which catch a key that folds under a custom collation the SELECT body never names. The gate is deliberately soundness-first: any non-builtin non-replicable collation present anywhere rejects (even a bare value passthrough of a custom-collation column that never actually folds) — a false positive is a create-time inconvenience with a clear fix (declare the collation replicable: true, or use COLLATE BINARY), while a false negative would be silent peer divergence. Like the function gate it is orthogonal to — and not lifted by — pragma nondeterministic_schema. See migration.md § Determinism requirements.

The four bounded-delta shapes below are the incremental arms the cost gate prefers when sound; each is recognized from the optimized/analyzed body (a superset of the coverage prover's shape in planner/analysis/coverage-prover.ts) and maintained by a corresponding maintenance arm. Anything else — a fanning join, an outer join, a set operation, a recursive CTE, a scalar (no-GROUP BY) aggregate, a >2-source join — falls to the full-rebuild floor and is maintained correctly, just not as a bounded per-row delta.

1. Covering-index shape (the inverse-projection arm):

• a single source table T with a primary key (no joins / self-joins);
• a row-preserving linear body TableReference → optional Filter → Project → optional Sort — no aggregate, set operation, DISTINCT, recursive CTE, table-valued function, or LIMIT/OFFSET;
• a passthrough or deterministic-expression projection — each output column is either a passthrough source column (a bare column reference or a simple rename, including one wrapped in collate or a no-op cast — those wrappers copy the source value verbatim, so the column-copy maintenance is exact; this is what lets the coarsened-key migration shape register here) or a deterministic scalar expression over the single source row (e.g. v + 1, lower(name), case/cast). A non-deterministic projection (random(), now(), …) is a hard reject; a non-single-row form (a subquery / cross-row reference) cannot be a per-row projection and routes the body to the full-rebuild floor. When the arm applies, maintenance stays a pure per-row function of the changed row — project(row) copies the passthrough columns and evaluates the expression columns via the runtime, so a computed backing value is byte-for-byte what select <body> would produce;
• the projection includes every PK column of T as a passthrough column, so each source row maps to a unique backing key (and the backing key identifies the source row); every backing-key column (the body's order by columns + the logical PK) must likewise be passthrough — a computed column may never land in the backing key, which the inverse-projection conflict map and the btree key both depend on;
• a partial WHERE, if present, evaluable on a single source row (compiled via compilePredicate; a subquery / cross-row WHERE routes the body to the floor).

The single source T may itself be another maintained table (an MV-over-MV chain). A reference to mv1 resolves to an ordinary TableReference against mv1 itself, so the source base is mv1 and the same eligibility checks evaluate against its (keyed) table schema unchanged. A maintenance write into mv1 then drives mv2 synchronously (see Maintenance § MV-over-MV cascade).

2. Single-source aggregate (the residual-recompute arm):

• a single source table T;
• a body of the form select g1,…, agg(…) from T [where P] group by g1,… whose GROUP BY columns are bare source columns (a computed group key routes to the floor — the group columns must be source-column indices so the backing can be keyed on them); a scalar aggregate with no GROUP BY (one global row) falls to the full-rebuild floor;
• a deterministic body — the group-by and aggregate expressions must be reproducible (random() / now() / volatile UDFs are rejected on determinism), so the recomputed slice is exactly what select <body> returns;
• the backing primary key is the group key (the group-key FD makes keysOf derive it), so each group maps to exactly one backing row.

Unlike the covering-index shape, this is maintained not by a pure projection but by a bounded key-filtered residual of the body.

3. Single-source lateral-TVF fan-out (the prefix-delete arm):

• a single base source table T with a primary key, joined to one lateral table-valued function whose arguments are per-row functions of T (select T.pk…, f.* from T cross join lateral tvf(<args over T>) f) — so each base row drives an independent fan-out of N rows; no second base table, no nested/multiple TVF, no aggregate / DISTINCT / set-op / recursion over the fan-out;
• a deterministic TVF (and deterministic argument expressions) — the residual must reproduce exactly what select <body> returns;
• the TVF advertises a per-call key, so the backing primary key is the composite product key (T.pk ∪ tvf-key) that keysOf derives across the lateral join (the base PK ∪ the TVF's own key, shifted) — a real column key, not the all-columns/isSet fallback. A TVF that advertises no per-call key makes the fan-out rows individually un-addressable, so the body routes to the full-rebuild floor instead of this arm;
• the base PK is projected and is the leading prefix of the backing PK (an order by over the fan-out that reorders the composite key so the base PK no longer leads routes to the floor — the by-prefix delete depends on the base PK leading).

This is maintained by a by-prefix delete of the base row's whole fan-out slice plus a re-fan residual: one base row owns many backing rows sharing the base-PK prefix, so the slice is replaced as a unit rather than a single point key.

4. 1:1 row-preserving inner/cross join (the join-residual arm):

• a body select … from T join P on T.fk = P.id over two base tables where the driving table T contributes exactly one MV row per governed T row, proven by the coverage prover's shared proveOneToOneJoin — no row loss via a NOT-NULL FK→PK inclusion dependency under enforced referential integrity, and no fan-out via isUnique(T.pk) at the join frame. A fanning (non-1:1) join falls to the full-rebuild floor;
• an inner or cross join only — an outer join falls to the full-rebuild floor (the lookup-side reverse residual filters P, which would drop its null-extended rows);
• no aggregate over the join (an aggregate-over-join falls to the floor). A WHERE is supported: a predicate over the driving table T only is carried by the forward residual and leaves the lookup side upsert-only (membership {T : T.fk = P.pk} is fixed by T.fk, which a P write cannot change); a predicate referencing the lookup P switches the lookup side to a delete-capable reverse residual (a membership pass deletes the stale joined rows, then the in-scope pass re-upserts survivors) — see 'join-residual';
• the backing primary key is exactly T's PK (the 1:1 join collapses the composite product key keysOf advertises to T's PK — a real column key, not the all-columns fallback), so each T row maps to one backing row;
• deterministic projections (the residual must reproduce select <body>).

This reuses the residual kernel of the aggregate arm with a 'row'/'pk' binding on T, plus a second residual keyed on P for lookup-side writes.

A table declared without an explicit primary key defaults to an all-columns PK (schema/table.ts), so the "source without a PK" rejection is effectively unreachable for memory tables. The relevant create-time failure is "projection drops a source PK column."

Full-rebuild floor

Invariant: MV-004

A body that matches no bounded-delta shape — a fanning or outer join, a set operation, a recursive CTE, a scalar aggregate, a >2-source join, or any other relation-producing body with a provable key (or a coarsened lineage key) — is maintained by full rebuild: per writing statement, the body is re-evaluated against live mid-transaction source state and the backing's contents are replaced transactionally (a keyed diff against the backing's pending layer, so the delta still commits/rolls-back with the source write and still drives the MV-over-MV cascade). The floor is deferred to a once-per-statement flush rather than run per row (see Synchronous, transactional, per-statement), so a bulk write rebuilds each affected MV once. This is what makes coverage total: the floor is always sound, so the bounded-delta arms are pure optimizations and never a coverage gate.

Maintenance (row-time, per-statement)

For each materialized view the manager caches a MaintenancePlan, indexed by every source base it reads (a single base for the single-source arms; both the driving and lookup base for the 1:1-join arm; every source for a full-rebuild plan), and dispatches on its kind. Five arms are wired: 'inverse-projection' (the covering-index shape), 'residual-recompute' (single-source aggregates), 'prefix-delete' (single-source lateral-TVF fan-out), 'join-residual' (1:1 inner/cross join), and 'full-rebuild' (the floor for every other body). The correctness oracle for all arms is the maintenance-equivalence property harness (test/incremental/maintenance-equivalence.spec.ts): over a zoo of body shapes — including the floor-maintained ones — it asserts read(MV) == evaluate(body) after each random source mutation and after rollback.

'inverse-projection' (covering-index shape)

The per-row backing delta is a pure projection of the changed row — no body re-execution, no scan, no compiled residual. project(r) copies the passthrough columns and evaluates each deterministic-expression column against the single changed row (reusing the runtime, so the value matches select <body> exactly):

source op	maintenance
insert r	if predicate(r) → upsert project(r)
delete r	if predicate(r) (was in scope) → delete the backing key of project(r)
update old→new	both images in scope and value-identical → nothing (the equal-image short-circuit); both in scope with the same backing key (collation-aware) → upsert new image only (the host reports one update); else delete old image if in scope, upsert new image if in scope

The update arm covers predicate-scope transitions and key-changing updates. The equal-image short-circuit suppresses the dominant no-op echo — a source update touching only unprojected columns, or rewriting a projected column to its existing value — before any backing-connection work (see no-op suppression); the scope check reads the source row (the predicate may reference unprojected columns), so it only fires when both images are in scope. A real same-key payload change emits the upsert alone — key identity is the backing PK comparator (a collation-equal / byte-different key is the same identity, and the upsert re-keys the stored bytes), so the host reports a single update, matching the residual arms: one cascade dispatch, one change-log entry, no secondary-index churn at an unchanged key. Only a key-changing or scope-transitioning update is genuinely two-sided (delete + upsert). This bounded O(log n) per-row cost — identical to the secondary-index maintenance a UNIQUE auto-index already performs — is why row-time is affordable for this shape and not for general bodies.

'residual-recompute' (single-source aggregate shape)

When the body is a single-source aggregate (group by over bare columns) the per-row delta is not a projection but a bounded, key-filtered re-execution of the body. At create, the body is rewritten with injectKeyFilter(body, T, groupColumns, 'gk') (the shared residual primitive in planner/analysis/key-filter.ts, also used by the assertion evaluator) and compiled once into a cached scheduler. The plan carries a BindingMode of { kind: 'group'; groupColumns }, built directly from the aggregate's bare GROUP BY columns — not via extractBindings, whose 'group' classification additionally requires the group key to cover a source unique key (and so reports 'global' for the common group by <non-key> body, which would route to the unwired rebuild/reject path).

Per source change the manager derives the affected group key(s) from the changed row, and for each:

source op	affected group key(s)	maintenance
insert r	NEW group of r	run the residual bound to the key; upsert the recomputed group row (replaces the old row at the same key; a value-identical recompute is suppressed)
delete r	OLD group of r	run residual; zero rows (emptied group) → delete the group's backing row, else upsert the recomputed row
update old→new	OLD ∪ NEW group (deduped)	per affected key: run residual; upsert the recomputed row, or delete on an emptied group

The residual runs against live mid-transaction source state (reads-own-writes, through the same emit → Scheduler path the assertion evaluator uses), so the recomputed slice is exactly what select <body> would return at that point. A group-key-changing UPDATE recomputes both the OLD and NEW groups. The backing key is the group key, so a recomputed row replaces the old one wholesale via the upsert (no delete-first); an emptied group stays correct because its residual returns zero rows, which maps to the point delete (delete-without-upsert) that removes the stale backing row. Only the recomputed row(s) whose backing key equals the affected key are upserted (a soundness net that also discards a spurious empty-group row a constant-pinned multi-column grouped aggregate can produce under a known optimizer mis-collapse).

Recompute-from-live-state makes the arm last-write-wins: every recompute of a group reads live (reads-own-writes) state, so whichever run happens last writes the authoritative row. The engine exploits this by batching per statement: instead of recomputing per touching source row, the affected group keys accumulate (deduped on canonical key values) in the DML generator's per-statement residual key batch, and each distinct key's residual runs exactly once at the end-of-statement flush — identical output to N per-row runs, at 1/N the cost (see Synchronous, transactional, per-statement for the flush mechanics, visibility semantics, and the per-statement degrade-to-rebuild demotion). The same batching applies to the other residual arms ('prefix-delete', 'join-residual'); cold callers with no statement batch in scope (the REPLACE-eviction hook) fall through to the inline per-change apply, which remains correct for the same reason.

Delta-aggregate fast path (arithmetic maintenance, zero source reads)

When every stored aggregate column is delta-maintainable by its declared algebra — AggregateFunctionSchema.algebra, see Aggregate Function Algebra — the plan (its kind stays 'residual-recompute') carries a delta descriptor and chosenStrategy: 'delta-aggregate', and the statement flush maintains each affected group by pure arithmetic on the stored backing row instead of re-executing the residual: per changed row the accumulation folds step(identity, row[arg]) in (merge) for an insert and its negate for a delete (an update retracts the OLD image into its group and inserts the NEW into its — possibly different — group); at flush, each affected group's stored row is point-read from effective state, each column rebuilt as finalize(merge(decode(stored), delta)) (a fresh identity accumulator when no row is stored), and the row upserted — or deleted when the multiplicity witness finalizes to 0. No source reads, no residual execution. The gate is declaration-driven, never name-driven (MV-007): a UDAF declaring a lawful algebra gets the fast path.

Create-time eligibility (buildDeltaAggregateDescriptor in database-materialized-views-plan-builders.ts; any failure silently leaves the plan on the plain residual):

• every aggregate column is a plain call (agg(x) / agg() — no DISTINCT/FILTER/ORDER BY/multi-arg) over a bare source column, whose algebra declares merge + negate + decode (an abelian-group column) — or is a tighten-only column (merge + decode, no negate) — or is a decomposition-maintained column (declares decompose);
• exact value domain (abelian-group columns only): the aggregate's declared result type is INTEGER-physical (count-shaped — exact regardless of argument) or its argument column's static type is INTEGER-physical (integer sum). A REAL/TEXT sum would drift under repeated add/subtract and diverge byte-exactly from live re-evaluation. A tighten-only column is exempt — its merge is idempotent selection (min/max) or set union (bit_or), not accumulating arithmetic, so it never drifts;
• a count(*) multiplicity witness is present — a zero-arg delta-maintainable column with decodeExact — the structural group-emptiness signal (finalizes to 0 ⇔ delete the backing row; without it emptiness cannot be told from an all-NULL sum);
• no post-aggregate filter (HAVING / an outer WHERE over the aggregate output) — a filtered-out group has contributions but no stored row, which would break the invariant "stored row == the group's full accumulator";
• BINARY collations on every backing-PK (group key) column — the flush's stored-row read is the host's binary equality-prefix seek, and a group key has no source-PK uniqueness collapsing collation classes to one byte form;
• a body WHERE (if any) compiles to a single-source-row predicate: an out-of-scope row image contributes nothing (its raw value — including NULL — is otherwise fed straight to step, never pre-filtered).

Retraction safety. A column whose decode is only an insert-observational witness (sum: the stored value forgets the true non-NULL contribution count) may be retracted through arithmetic only when its argument column is declared NOT NULL — then the true count equals the multiplicity and stays positive while the row exists (sum's decode witness is absorbing, count: Infinity, so it never spuriously empties). Otherwise the flush falls back to the residual for exactly the retracted groups (the residual keys are always accumulated alongside the deltas, same canonical map key); insert-only groups stay on pure arithmetic, so a bulk insert is never penalized. decodeExact columns (count, or a UDAF whose stored value is the accumulator) are retraction-safe unconditionally. A tighten-only column is never retraction-safe (see the next subsection) — it has no negate at all — and additionally forces the whole group to the residual on any retraction, not just the retracted column.

Decomposition-maintained columns (the avg class)

Some aggregates have no decode at all — the stored finalized value cannot reconstruct an accumulator: avg(x)'s quotient forgets the count. Instead they declare decompose — a scalar combine over sibling partial aggregates (avg(x) ≡ sum(x)/count(x); a geometric mean ≡ exp(sum(log x)/count)). Such a column is decomposition-maintained: it carries no independent accumulator, and is maintainable only when every partial it names is ALSO stored as a sibling column of the same MV body and each partial is itself delta-maintainable (buildDeltaAggregateDescriptor binds each partial to its stored sibling; the write-side twin of the read-side rollup's resolveMergeablePartial). Then maintenance delta-maintains the partials as ordinary columns and, at flush, sets the decomposed column to combine([finalized(partial) …]) per affected group — zero extra accumulation. avg is simply the first client of this class, not a special case: any UDAF declaring a lawful decompose gets it.

If any partial is missing or not delta-maintainable, the decomposed column is not maintainable and the whole MV falls to the residual — honest and visible, since the body was written without the partials the formula needs (select k, avg(a) group by k with no stored sum/count is correct on the residual, just not incremental). Two shape rules mirror the read side exactly:

• count(*) vs count(x) for avg. avg's decompose names count(same-arg) = count(x), the NULL-excluding count it divides by. A body storing only count(*) (not count(x)) satisfies avg's decomposition only when the argument column is NOT NULL — then count(*) excludes the same (zero) NULLs count(x) would, so the recombine stays exact. Identical to the read-side relaxation.
• Float gate on the partials. avg over a REAL column is not delta-maintainable: its sum partial fails the INTEGER-domain exact-value gate above, so the whole MV falls to the residual (consistent with a standalone REAL sum).

The empty-group and divide-by-zero cases are governed by the multiplicity witness and combine together: a fully-emptied group is deleted (multiplicity finalizes to 0) before combine runs, and a non-empty all-NULL-argument group finalizes count(x) = 0, so combine yields NULL — matching native avg. A decompose column is never the multiplicity witness (it accumulates nothing; the witness must be a real stored count(*)).

Tighten-only columns (min/max, and any join-semilattice)

Some aggregates are a join-semilattice, not an abelian group: they declare merge + decode but no negate. min/max are the builtins (merge selects the smaller/larger under the same comparator the step uses — bound at plan build to the argument column's declared type and collation, see aggregate-algebra.md § Call-site binding); any UDAF whose merge is idempotent selection or set union — bit_or, bool_or — is one too. This is the general merge-without-inverse rule; min/max are not special cases, and detection is structural (merge present, negate absent), never a name list.

An insert merges the new contribution into the stored value cheaply — min/max tighten toward the new extreme (an insert that does not beat the extreme rebuilds a value-identical row, suppressed). A retraction cannot be undone arithmetically: after the current extreme leaves, merge cannot recover the next-best (and bit_or cannot un-set a bit). So a group that accumulates any retraction — a delete, or the OLD image of an update — re-derives wholesale from the key-filtered residual (DeltaAggregateDescriptor.hasTighten), whether or not a backing row is stored (an intra-statement delete of the extreme poisons even a from-identity net-fold, so the not-retraction-safe abelian rule "residual only when a row is stored" is not enough here). The residual recomputes every column of that row — the sibling count/sum group columns included — so a mixed group + tighten row is maintained by exactly one path per group; the accumulated deltas for that group are discarded, never applied on top (no double-maintenance). Insert-only groups stay entirely on the arithmetic path, so a bulk insert is never penalized.

This is conservative: a delete of a non-extreme value still takes the residual (the arm cannot cheaply prove the deleted value was not the extreme). Correct, just not minimal — a future secondary-index-backed "is this the current extreme?" probe could skip the rescan; not built now (a tripwire, not a ticket). The create-time cost gate folds an expected-retraction fraction (deltaTightenFallbackRatio) into the 'delta-aggregate' cost, so a tighten body costs more than a pure-group body yet, for an insert-dominated estimate, still less than the always-residual arm; a genuinely retraction-heavy workload may legitimately settle on the plain residual.

Interactions. The delta path bypasses the per-statement degrade-to-rebuild crossover — it is already O(affected groups) with no residual runs, so the rebuild can never win. An OR FAIL per-row savepoint revert poisons the statement's delta accumulations (poisonResidualDeltaAccumulations, called by the DML executor on the rollback): the savepoint undoes the row's writes but a JS-side fold cannot be unwound, so the FAIL-path flush routes those entries through the plain residual over the always-accumulated keys (OR IGNORE never writes the skipped row; OR REPLACE evictions arrive as real delete changes — both accumulate correctly). The cold inline path (REPLACE-eviction, no statement batch) keeps running the compiled residual scheduler. A net-identity delta (insert then delete of the same row) rebuilds a value-identical row and is suppressed by the host (MV-016); a delta upsert/delete emits the same effective BackingRowChanges, so the MV-over-MV cascade — including a delta-aggregate consumer over a delta-aggregate producer — is unchanged.

Float-exact tripwire: the INTEGER-domain gate is what keeps the arithmetic byte-exact. If a REAL-domain sum ever needs the fast path, it needs compensated (Kahan) accumulation or a periodic rescan discipline — a design change, not a gate relaxation.

The read-side rollup (recipeForRollup, see materialized-views.md § Aggregate rollup) is the other consumer of the algebra declarations — it recombines stored aggregate MVs at coarser grain, decided entirely by each aggregate's declared merge/decode/decompose rather than a builtin-name list, so a UDAF that declares algebra rolls up for free. The write-side decomposition-maintained class (avg and any decompose UDAF) is the maintenance-direction twin of that read-side recombine, and the tighten-only class (min/max and any join-semilattice UDAF) is the merge-without-inverse extension — all three decided by the same declared algebra, no builtin-name list anywhere.

'prefix-delete' (single-source lateral-TVF fan-out shape)

When the body fans a single base row out through a lateral table-valued function, one base row owns N backing rows that all share the base-PK prefix of the composite product key (T.pk ∪ tvf-key). So this arm maintains a prefix-keyed slice (vs the point-keyed slice the residual-recompute arm maintains): the per-source-change delta is a keyed diff of the re-fan residual against the existing effective slice — the affected base prefix's current backing rows, read via the host's scanEffective (pending over committed; the same contiguous prefix range a wholesale 'delete-by-prefix' would select). It reuses the residual kernel of the aggregate arm unchanged — the affected-key derivation, the injectKeyFilter residual (pinned to the base TableReferenceNode with the 'pk' prefix, compiled + cached once), reads-own-writes execution — and differs only in the prefix-slice diff (vs a point key) and the N-row residual (vs ≤1).

source op	affected base key(s)	maintenance
insert r	NEW base PK of r	run the residual bound to the base key; diff against the (empty) slice → upsert each fanned row
delete r	OLD base PK of r	run residual (zero rows, base row gone) → delete every existing slice row
update old→new	OLD ∪ NEW base PK (deduped)	per affected base key: run residual; delete only the existing keys it no longer produces; upsert each fanned row (value-identical → suppressed)

A base-PK-changing UPDATE moves the whole prefix — the OLD base key's slice diffs to all-deletes (its residual returns nothing) and the NEW base key's fan-out is re-computed and upserted; a grown/shrunk fan-out reports exactly the appeared/disappeared rows. The body's WHERE, if any, is part of the residual, so an out-of-scope base row fans out to zero rows (the all-deletes diff removes its slice) — predicate-scope transitions need no separate predicate. Key pairing inside the diff is collation-aware over the full backing PK (the btree's identity), so a recomputed row whose key is collation-equal to an existing row replaces it via the upsert rather than also being deleted; the slice read shares 'delete-by-prefix''s binary prefix-scan soundness (the build-time gate pins the backing base-PK collation to the source PK's, and source-PK uniqueness collapses each collation class to one binary value).

'join-residual' (1:1 inner/cross join shape)

A 1:1 row-preserving inner/cross join (select … from T join P on T.fk = P.id) reuses the residual-recompute kernel with a 'row'/'pk' binding on the driving table T whose PK keys the backing. The plan is indexed under both source bases (rowTimeBySource[T] and rowTimeBySource[P]); maintainRowTime passes the changed base to applyMaintenancePlan, which routes a T write to the forward path and a P write to the reverse path.

Driving side (T) — the forward path. Identical to a size-1 group in the aggregate arm, driven by the same applyForwardResidual: per changed T row, run the T-keyed residual (… where T.pk = :pk0, the body with injectKeyFilter applied on T) against live state, and upsert the recomputed row (or delete the key when the residual returns nothing).

source op	affected key(s)	maintenance
insert r	NEW T.pk of r	run residual (the one joined row); upsert
delete r	OLD T.pk of r	run residual (zero rows, T row gone) → delete the backing key
update old→new	OLD ∪ NEW T.pk (deduped)	per key: run residual; upsert the joined row (value-identical → suppressed), or delete on zero rows

An FK-moving UPDATE (changing T.fk, not T.pk) recomputes the same T.pk slice against the new lookup row; a PK-changing UPDATE recomputes both the OLD and NEW T.pk.

Lookup side (P) — the reverse path. A write to P cannot be keyed on T's PK (one P row joins many T rows), so the plan carries a second residual keyed on P's PK (the body with injectKeyFilter applied on P). Per changed P key (OLD ∪ NEW, deduped) it runs … where P.pk = :pk0 against live state — returning every currently in-scope joined row, each carrying its T.pk backing key — and upserts each. For a no-WHERE or T-only-WHERE body no delete is performed; a P-referencing WHERE adds a delete pass (see WHERE handling below).

source op	affected key(s)	maintenance
insert p	NEW P.pk	run reverse residual (zero rows if no T references it → no-op)
delete p	OLD P.pk	run reverse residual (RI-admissible only when childless → zero rows)
update old→new	OLD ∪ NEW P.pk (deduped)	run reverse residual; upsert each joined row

The upsert-only reverse path is sound because, for an inner/cross join with enforced RI and no lookup-referencing WHERE, the set of T rows joined to a given P row is { T : T.fk = P.pk } — determined entirely by T.fk (a T column a P write cannot change). So a P change only re-derives the lookup-projected columns of existing backing rows (an upsert at the unchanged T.pk), never adds or removes one. A T-side membership change is the forward path's job; the two paths fire independently and, reading live state, converge under last-write-wins exactly as the other residual arms do.

WHERE handling. A predicate over the driving table T only needs no special reverse handling: the forward residual already carries it (an out-of-scope T row yields zero residual rows → delete), and a T-column predicate cannot move the membership set {T : T.fk = P.pk}, so the lookup side stays upsert-only. A predicate referencing the lookup P can move membership (a P write flips the predicate for the rows joined to it), so the upsert-only path is no longer sound — the reverse path becomes delete-capable: per affected P key it runs a membership residual (select T.pk … where P.pk = :pk0, no WHERE) and the in-scope reverse residual (with the WHERE) against the same live state, then applies the keyed diff — delete only the membership keys the in-scope recompute no longer produces (rows that left scope), upsert every in-scope row (a row entering scope inserts; an unchanged in-scope row's upsert is suppressed, so an in-scope P write that changes nothing projected reports nothing). Outer joins and fanning joins are not made bounded-delta this way; they fall to the full-rebuild floor.

The join soundness predicates (proveOneToOneJoin = the no-row-loss descent + proveJoinNoFanout) are factored out of coverage-prover.ts and shared by the base-table coverage prover and this MV gate, so the 1:1-join logic lives in one place.

The classification input suppresses one optimizer rule. Both reads above locate the body's WHERE as a Filter at or above the join, so they only work while the WHERE is still there. join-predicate-pushdown moves a single-side conjunct into the branch it constrains, which hides it from both. buildMaintenancePlan therefore builds its analyzed body with that rule disabled (ANALYSIS_DISABLED_RULES in core/database-materialized-views-plan-builders.ts) — classification only; every residual an arm compiles goes back through the full optimize() and keeps the pushdown. Without the suppression, every WHERE-bearing 1:1-join MV degrades to the full-rebuild floor: still correct, but a whole-source rescan per write. Teaching the two reads to see through a pushed predicate, so the suppression can go away, is backlog/debt-mv-shape-analysis-blind-to-pushed-predicates.

'full-rebuild' (the floor for every other body)

Any body matching no bounded-delta shape is maintained by re-evaluating it in full. At registration the optimized body (read-side rewrite suppressed, so it reads its sources, not the backing it populates) is emitted once into a cached scheduler. Per writing statement — not per row (see Synchronous, transactional, per-statement) — the manager runs that scheduler to completion against live mid-transaction source state, collects the rows, and applies a single 'replace-all' MaintenanceOp: a keyed diff of the recomputed rows against the backing's current pending-layer contents by backing PK — delete removed keys, upsert present keys, skip byte-identical rows. The diff is transactional (it rides the backing's pending TransactionLayer, committing/rolling-back with the source write) and emits the minimal effective BackingRowChange[], so the MV-over-MV cascade drives consumers off a full-rebuild producer unchanged. The plan is indexed under every source the body reads, so a write to any of them dirties it for the next flush.

The body re-evaluation is unbounded by design (it is the floor), so the cost gate prefers any sound bounded-delta arm over it and the size threshold rejects a full-rebuild-only body over a large source rather than paying it per statement.

Value-identical (no-op) write suppression

Invariant: MV-016

A maintenance write whose recomputed backing image is value-identical to the existing effective backing row changes nothing — so it writes nothing and reports nothing: no backing op, no effective BackingRowChange, no cascade. This is accuracy, not an optimization: the effective-change contract demands fidelity to what actually changed, and nothing did. It is a universal efficiency win (a source update touching only unprojected columns fires zero downstream work, at every consumer level) and the echo-prevention prerequisite for change-logged (synced) backings — a suppressed write produces no change-log entry, so a replicated maintenance write cannot echo. Suppression operates at two layers:

• Arm-level (the cheap common case). The 'inverse-projection' update arm short-circuits an equal-image update — old and new projected images value-identical, both in scope — before any backing-connection work.
• Host-level (the normative backstop). A point upsert whose row is value-identical to the connection's effective existing row (pending state over committed — never committed-only, so a same-transaction prior write is respected) writes nothing and reports nothing. This is part of the BackingHost contract — the normative statement lives in vtab/backing-host.ts — and both hosts implement it (memory applyMaintenanceToLayer, the store host's applyMaintenance). The residual arms lean on it: their keyed-diff apply (recompute → upsert-changed / delete-disappeared) emits upserts whose unchanged members the host then suppresses.

Value identity is byte-faithful, not collation-aware (rowsValueIdentical, util/comparison.ts): per-column compareSqlValues under BINARY — numeric-storage-class tolerant (bigint 5n ≡ number 5) but byte-exact for text. A collation-equal / byte-different write (e.g. a case-only PK rewrite under NOCASE) is a real change: select returns stored bytes, so the maintained backing must re-key to the new bytes and report an update — the maintenance-equivalence oracle compares byte-exactly and pins this (the lateral-TVF NOCASE suite). The column collation still governs key identity — which existing row an upsert replaces, which existing slice row a recomputed row pairs with in a keyed diff — just never the skip. This is one discipline everywhere: the wholesale replace-all diff pairs keys collation-aware (the backing PK comparator) but applies the SAME byte-faithful rowsValueIdentical skip as the point-op upsert (test/vtab/maintenance-replace-all.spec.ts), so a collation-equal / byte-different paired row re-keys the stored bytes rather than being skipped.

The suppression never skips a real change — the maintenance-equivalence harness is the oracle for that, and per-arm no-op probes (maintenance-equivalence.spec.ts § no-op write suppression) pin zero effective changes for unprojected-column updates and same-value rewrites, zero consumer dispatch through an MV-over-MV level, and that key-changing updates, emptied groups/fan-outs, and predicate-scope transitions still report. Suppression leaves backing state identical by definition, so the covering-UNIQUE enforcement scan (which reads backing state) is untouched, and rollback observability is unchanged.

MV-over-MV cascade

Invariant: MV-005

A maintenance write into a maintained table is itself a row-write that every MV reading that table must see. After a plan maintains its backing, the manager looks up rowTimeBySource[backingBase] — the backing base is the maintained table's own qualified name; when non-empty, each effective per-row backing change is routed back through maintainRowTime, recursively. The backing host's applyMaintenance (memory: applyMaintenanceToLayer) returns the BackingRowChange[] it actually realized (a delete-key that found a row → delete; an upsert → update when it replaced an existing row, else insert), so the cascade needs no source re-read — the host already knows each op's before-image.

Because a consumer MV can only be created once its producer exists (and an MV's sources are fixed at create), the dependency graph is acyclic. Synchronous depth-first recursion is therefore DAG-ordered — a producer's backing is fully written before its consumers run — and the whole chain commits/rolls-back atomically on the live transaction (a depth-≥2 backing connection registers lazily on its first cascade write, and Database.registerConnection replays the active savepoint stack onto it, including the statement-atomicity savepoint, so a rollback reverts every level in lockstep). A non-chained MV keeps today's cost exactly (one map lookup, no recursion) via the leaf fast path (!rowTimeBySource.has(backingBase)). A defense-in-depth depth guard (bounded by the count of registered row-time MVs) is the backstop for the structurally-impossible cycle.

Reads-own-writes through the chain. Cascade writes ride the same per-statement backing connection a select/enforcement scan resolves to. The enforcement-relevant path — an 'inverse-projection' covering structure over a source table — applies per row, synchronously, so a later same-statement source row's enforcement scan (lookupCoveringConflicts) observes every row already written this statement. Residual and full-rebuild links of a chain defer to the end-of-statement flush (their backings are never read by enforcement — see below), and the flush's worklist rounds re-drive the cascade until the chain converges.

Synchronous, transactional, per-statement

Invariant: MV-002, MV-003

Maintenance is driven from the runtime DML write boundary (runtime/emit/dml-executor.ts), immediately after each source row is recorded (_recordInsert/_recordUpdate/_recordDelete), via Database._maintainRowTimeCoveringStructures(sourceBase, change). A cheap synchronous guard (_hasRowTimeCoveringStructures) makes this a no-op fast path for tables no materialized view reads, so non-covered writes pay effectively nothing.

Maintenance is amortized per statement. The DML generator owns three per-statement structures, created at generator entry and threaded through every maintenance call: a BackingConnectionCache (a Map<backingBase, VirtualTableConnection> — each backing's connection is resolved once per (statement, backing) instead of once per source row, and a multi-level cascade amortizes each level's backing too), a deferred-rebuild set (Set<mvKey> — the full-rebuild floor), and a residual key batch (ResidualKeyBatch — the residual arms). Only the 'inverse-projection' arm applies its ops immediately per row (to the cached connection's pending layer): its delta is a cheap pure projection, and the covering-UNIQUE enforcement scan depends on its per-row visibility. The residual arms and the full-rebuild floor defer to a single end-of-statement flush — see below.

Enforcement-visibility invariant — do not "optimize" this into a correctness bug. Covering-MV UNIQUE enforcement runs inside the source vtab's update() (checkUniqueViaMaterializedView → Database._lookupCoveringConflicts) and scans the backing table, relying on it reflecting every prior row of the same statement. Only an 'inverse-projection' MV can serve as a covering structure — findRowTimeCoveringStructure declines every other plan kind, and lookupCoveringConflicts hard-returns [] for a non-'inverse-projection' plan — and that arm keeps per-row apply, so a later same-statement row's enforcement scan always observes an earlier row's backing write (e.g. insert into t values (1,'a'),(2,'a') over a covering unique(x) detects the intra-statement duplicate). This is exactly why the deferred arms need no buffer-unioning in the conflict probe: nothing ever reads a residual or full-rebuild backing mid-statement for enforcement. Keeping 'inverse-projection' per-row-immediate is load-bearing; deferring it would break enforcement unless the probe also read the not-yet-flushed batch.

Reads-own-writes therefore holds within a statement for the enforcement scan (inverse-projection backings) and between statements within a transaction for every arm: the flush runs before the statement completes, so a select from any MV between two statements of one transaction sees fully-maintained state — the MV stays contractually indistinguishable from the plain view at statement granularity. What changed with statement batching is only mid-statement backing visibility of the residual arms (now end-of-statement, matching the full-rebuild floor's precedent): a statement that both writes a source and reads a residual/full-rebuild MV of it (e.g. via a subquery) sees statement-start backing state. The cold enforcement/eviction paths (lookupCoveringConflicts, the memory/store REPLACE-eviction maintenance) omit the per-statement structures and re-resolve the same connection deterministically, so they observe and contribute to the same statement's backing state.

The residual arms and full-rebuild defer; inverse-projection alone is per-row-immediate. A full-rebuild re-evaluates the whole body, and a residual arm runs a key-filtered scheduler per affected key — running either per source row is O(rows × work); both instead run once per statement. During the row loop, maintainRowTime marks a 'full-rebuild' plan dirty in the deferred-rebuild set and accumulates a residual-arm plan's affected binding keys (OLD ∪ NEW per change, deduped on canonical key values — the same dedup the per-row apply did within one change, extended across the statement) into the residual key batch; a 'join-residual' plan's forward (T) and lookup (P) keys accumulate under separate bindings so the flush runs the correct residual variant per key. After the row loop and inside the statement-atomicity savepoint — so a failed flush (including a derived-row validation failure or parent-side RESTRICT over the flushed delta) rolls the whole statement back with the same attribution, and an ABORT-class statement that only accumulated before aborting unwinds with the statement (no flush needed) — the generator drains both structures via Database._flushDeferredMaintenance → MaterializedViewManager.flushDeferredMaintenance. A bare autocommit write flushes and commits in lockstep with the source write; a single-row statement's one-key batch costs exactly the former per-row apply (one residual run, one applyMaintenance). Per residual MV the flush runs the residual once per distinct affected key against live post-statement state, applies the same keyed diff as the per-row path (upsert recomputed slice / delete emptied key; last-write-wins-against-live-state makes recompute-at-flush trivially correct — the same argument as per-row soundness, evaluated once instead of N times), and batches all of one MV's ops into one applyMaintenance call. Degrade-to-rebuild (per-statement re-cost): before running per-key residuals the flush evaluates shouldDegradeToRebuild(distinctKeys, plan.sourceStats) — when k residual runs cost more than one whole-body rebuild (a statement touching most groups), it runs the plan's registration-compiled whole-body scheduler and applies a single 'replace-all' keyed diff instead; the stored strategy is unchanged, so a later low-cardinality statement reverts (stateless per statement). OR FAIL is the exception: it runs with no statement-scope savepoint (it keeps the rows that already succeeded), so a mid-statement abort does not unwind the surviving rows — the generator therefore also drains both structures on the FAIL throw path, before re-raising the conflict error, so every deferred backing reflects the surviving rows rather than lagging them (the failing row's own per-row savepoint already reverted its writes; a reverted row's accumulated key recomputes value-identically and is suppressed). An OR IGNORE/OR REPLACE per-row savepoint that reverts a row may likewise leave its key in the batch — harmless for the same reason. Deferring these arms does not violate the enforcement-visibility invariant: only an 'inverse-projection' MV is ever a covering structure (findRowTimeCoveringStructure declines every other kind; lookupCoveringConflicts reads only 'inverse-projection' backings), so nothing reads a deferred backing mid-statement. The flush drains as a worklist over the producer→consumer DAG: each apply (applyResidualBatch / applyFullRebuild) routes its effective BackingRowChange[] back through maintainRowTime with the same structures — a residual consumer accumulates keys into the batch, a full-rebuild consumer re-dirties the set, an inverse-projection consumer applies inline. It proceeds in rounds (snapshot both structures, clear them, apply each member, collect re-accumulations for the next round), so a consumer flushed too early is re-accumulated by its producer's same-round flush and reconverges; the DAG is acyclic, so the round count is bounded by the registered-row-time-MV count (assertFlushRounds — the worklist analogue of the cascade's depth guard). Cold callers (enforcement/eviction) pass no per-statement structures; any deferred-arm plan they reach falls through to a safe inline per-change apply.

The backing write is routed through the same backing connection a select from the MV would use in this transaction (obtained/registered lazily, matched by BackingHost.ownsConnection). The privileged write BackingHost.applyMaintenance(connection, ops) applies the ordered delete-key / upsert ops to that connection's pending transaction state, bypassing user-DML read-only enforcement (in the memory host this is MemoryTableManager.applyMaintenanceToLayer: it writes the pending TransactionLayer, bypasses validateMutationPermissions, and reuses recordUpsert/recordDelete so secondary-index bookkeeping stays correct). Because the connection is in the Database's active set:

• a later read of the MV in the same transaction sees the pending writes for free (reads-own-writes);
• the pending layer is committed atomically by the existing coordinated commit (database-transaction.ts) and discarded by the existing rollback broadcast — so a rollback (or a failed source write inside the statement savepoint) reverts the backing delta in lockstep; and
• an autocommit insert into T rides the statement-level autocommit boundary, so source and backing commit together — no orphaned/uncommitted backing pending layer.

Because maintenance is part of the writing transaction and never re-reads the source, it cannot "diverge" from its sources between writes: there is no post-commit window and no asynchronous failure mode. A maintenance error fails (and rolls back) the source write itself.

Each realized maintenance delta is also recorded into the transaction change log (recordMaintenanceChanges), so a maintained table is a first-class changed base at COMMIT: an assertion whose body names a materialized view is dispatched like one over any other table — see incremental-maintenance.md § Recording changes.

Database.watch on a materialized view still projects to the MV's sources, which widens the watch's granularity (the maintained table would fire on its own now) — see Change-scope projection.

Everything above is driven from inside the engine's own write path. Two seams exist for writes the engine did not execute: the vtab-internal two-arg DatabaseInternal._maintainRowTimeCoveringStructures(sourceBase, change) (the REPLACE-eviction hook a source vtab calls from within a statement — MV-only, cold, per-row) and the batch ingestion seam (the host-facing surface for everything else).