Class MaterializationRefresher

Full-rebuild refresh of a single materialized result, then updates LastRefreshedAt / RowCount / Status='Active' (and NextRefreshAt when provided via options). Returns a structured result rather than throwing (errors are logged + reported).

Applies the incremental-watermark safety overlap: returns rawMax - WATERMARK_SAFETY_OVERLAP_MS (null passes through). Persisting the reduced value makes the next incremental pass RE-scan the last overlap window, so a source row whose transaction commits after the fingerprint probe — but whose __mj_UpdatedAt predates the probed MAX — is re-processed (idempotent MERGE) instead of being skipped forever. Pure and unit-testable; extracted from probeSourceFingerprint so the skew-safety math is verifiable in isolation.

PostgreSQL dirty-group recompute (see buildDirtyGroupRecomputeCore).

SQL Server dirty-group recompute (see buildDirtyGroupRecomputeCore).

External-source full rebuild PLAN (Phase 1.5), cross-engine and PARAMETERIZED. Pure (no IO) → fully unit-testable (asserts on the emitted SQL + the params arrays). Three parts, run in order:

• preStatements — DROP + CREATE the shadow table (pure DDL, no params).
• insertBatches — batched multi-row INSERTs as {sql, params}. NON-NULL values are bound as positional parameters (@pN on SQL Server, $N on PostgreSQL) instead of inlined as literals; NULLs are emitted as the literal NULL (no bind param — sidesteps driver null-typing quirks and carries no injection risk). This keeps the SQL TEXT small and constant regardless of row width or value size, so a large external mirror no longer builds enormous statements that pressure the Node heap or blow the database's parser/packet limits (the prior inline-VALUES limitation). Batches are sized by the engine's bind-parameter ceiling (SQL Server 2100 / PostgreSQL 65535, with headroom), capped at 1000 rows/statement.
• postStatements — the atomic wrapper-view swap: transactional on SQL Server (the view only ever points at the canonical name; the transaction's Sch-M lock keeps readers on the old snapshot until commit — no "Invalid object name …__shadow" window; CREATE VIEW runs via EXEC() as its own batch), CASCADE-repoint sequence on PostgreSQL.

NOTE: the source rows are already fully materialized in memory by the EDS read (RunViewExternal / RunQueryExternal return the complete result set), so this fixes the SQL-text/packet half of the scale problem; true end-to-end streaming would require a streaming read API on the EDS router (future work).

Builds the ordered SQL statements for a PostgreSQL full rebuild with atomic swap (plan §11.2) — the PG counterpart to buildFullRebuildStatementsSQLServer. Pure (no IO), unit-testable.

Engine differences vs. SQL Server:

• Identifier quoting: schema bare, object double-quoted (__mj."materialized_x"), matching the CodeGen provider's QuoteSchema convention so the view repoint references the same names.
• Surrogate (query case): the synthetic surrogate is generated as the first column via ROW_NUMBER() OVER () (a stable 1..N snapshot id; deterministic hashing is §5/Phase 3). It MUST be first because CodeGen prepends the surrogate, and PG's CREATE OR REPLACE VIEW is strict about column order (SQLSTATE 42P16) — an appended surrogate would break the repoint.
• Swap: CREATE OR REPLACE VIEW (not CREATE OR ALTER), ALTER TABLE ... RENAME TO (not sp_rename), and DROP TABLE IF EXISTS ... CASCADE (PG blocks dropping a table a view depends on; CASCADE clears a transient wrapper-view dependency from a partially-failed prior run — the view is recreated within this sequence, so the stable contract is restored before the method returns).

Builds the ordered SQL statements for a SQL Server full rebuild with atomic swap (plan §11.2). Pure (no IO) so the swap sequence is unit-testable. Each returned string runs as its own batch.

• query case (surrogateColumn set): the synthetic IDENTITY surrogate is (re)generated via SELECT IDENTITY(int,1,1) AS <surrogate>, src.* INTO <shadow>;
• base-view case (no surrogate): SELECT * INTO <shadow> copies the source shape (incl. its PK column).

Phase 3: SQL expression computing the combined-key surrogate — SHA2_256 (lowercase hex) over the canonical key columns in declared key order (§17.1). Deterministic WITHIN an engine (the incremental-MERGE / dirty-group match key); cross-engine identity is best-effort.

COLLISION SAFETY: each canonical part is hashed to a FIXED-WIDTH 64-char hex string FIRST, and the per-part hashes are what get delimited + hashed. A naive part1 + CHAR(31) + part2 collides when a key value itself contains the CHAR(31) delimiter (or the CHAR(30) NULL sentinel) — e.g. ('x\x1f','y') and ('x','\x1fy') both flatten to x\x1f\x1fy. Hashing each part first makes every part pure hex (0-9a-f), which can NEVER contain a control char, so the delimiter is unambiguous and distinct tuples can no longer canonicalize to the same string. Each canonical part is NULL-free (COALESCE'd), so the inner hash inputs are never NULL. (This feature is unreleased, so no existing surrogates need migrating; a full rebuild regenerates them under the new scheme.) NOTE: the PostgreSQL digest() used here requires the pgcrypto extension. MJ's PostgreSQL baseline already runs CREATE EXTENSION IF NOT EXISTS "pgcrypto", so keyed PG materializations get it for free; a deployment that dropped that baseline step would see refreshes fail with a clear function digest(...) does not exist — provision pgcrypto to resolve.

PostgreSQL incremental upsert — the INSERT…ON CONFLICT counterpart of the SQL Server MERGE above.

Phase 4 (RefreshStrategy = 'Incremental'): incrementally refresh a keyed ADDITIVE aggregation by recomputing only the changed groups and UPSERTING them onto the surrogate key — an in-place MERGE (SQL Server) / INSERT…ON CONFLICT (PostgreSQL) rather than the DirtyGroupRecompute delete-then-insert. The recomputed source is identical (the aggregation restricted to groups with a source row changed since the watermark); the difference is that a surviving group's row is UPDATED in place — no churn, no transient absence, one atomic statement. Correct for insert/update; a net source-count drop (deletes) still falls back to full rebuild via the RefreshOne guard. Requires the surrogate to be unique (it is the materialized table's PK). Pure (no IO) / unit-testable.

Phase 3 (DirtyGroupRecompute): a NULL-safe equality predicate matching the key columns of two aliases ((a.[k] = b.[k] OR (a.[k] IS NULL AND b.[k] IS NULL)) AND ...). Two NULL keys are treated as equal (a materialized aggregation can legitimately have a NULL grouping value — it's one group). Portable across SQL Server and PostgreSQL (the OR ... IS NULL form works on both; we avoid IS NOT DISTINCT FROM, which SQL Server lacks pre-2022). Pure/unit-testable.

Phase 3: SQL expression producing the CANONICAL TEXT of one key column for the combined-key surrogate hash (§17.1). Deterministic within an engine; a NULL is replaced by a control-char-wrapped sentinel (CHAR(30)) so it can't collide with a literal value. type is the column's SQL-Server-style type (EntityFieldInfo.SQLFullType); the base type drives the canonical cast.

Coerce a JS value fetched from an external source into a driver-bindable parameter value: null/undefined → null (the caller emits a literal NULL for these); non-finite numbers → null; plain objects → JSON text (matches the inferSqlType text mapping for object columns); Date and primitives (boolean/number/string) pass through — the driver binds them to the shadow column type.

True if an entity is read-RLS-protected — any of its role permissions carries a non-empty ReadRLSFilterID. Matches CodeGenLib's entityHasRowLevelSecurity. Deliberately WIDER than the runtime's own reader (GetUserRowLevelSecurityInfo since #4358 collects a filter only from an Allow row whose CanRead is set): a leftover filter beside a cleared flag counts here as "protected", which errs conservative for a leak gate. Used to refuse refreshing a local mirror of an EXTERNAL RLS-protected entity — a mirror can't reproduce remote RLS.

The composite row-restriction test the Leak-1 gate uses: role RLS or an API-key row filter.

entityHasReadRLS covers only the ROLE layer. EntityInfo's equivalent role-only accessor is deprecated precisely because it omits API-key row filters, so a gate built on it alone judges an entity fenced only by a key filter to be unrestricted. CodeGen's mint and drift gates compose both layers; this is the runtime half, kept deliberately symmetric with them.

Selects the materializations due for refresh: those with no NextRefreshAt (never run) or whose NextRefreshAt is at/before now. Pure (unit-testable); the caller supplies the candidate rows (e.g. all non-disabled, scheduled materializations).

Infer a column's SQL type from its fetched values (external-query materialization, where no field metadata is available). All-null → nvarchar(max)/text; ALL-numbers → int/integer (bigint when any value exceeds signed-32-bit) else float/double precision; ALL-booleans → bit/boolean; ALL Date OBJECTS → datetime2/timestamptz; ANYTHING ELSE, including a column whose values are HETEROGENEOUS across rows or arrive as date STRINGS → nvarchar(max)/text.

The type is decided from EVERY present value, not just the first: a loosely-typed source (REST/GraphQL) can return a field that is a number in one row and a string in another; typing the column from row 1 would make later rows fail to bind. Falling back to text (which accepts any value) is the safe answer.

Date-like STRINGS (ISO-8601 over JSON transport) are deliberately kept as text, NOT coerced to a temporal column: coerceExternalParamValue binds the raw string and relies on implicit conversion, which can reject offset-bearing / edge ISO forms and fail the entire rebuild. Text loses nothing that matters here — fixed-format ISO-8601 strings sort and range-compare CHRONOLOGICALLY under lexicographic text ordering, so ORDER BY / > 'YYYY-MM-DD…' filters stay correct. Only genuine Date objects, whose bind is well-defined, are typed as datetime2/timestamptz.

Non-throwing form of the identifier check. Needed by callers on the FAILURE path, which is precisely where assertSafeObjectNames may have just thrown — those callers must be able to re-check and decline quietly rather than re-enter (or bypass) the assertion that already rejected the value. exposed for unit testing; not part of the supported surface.

A globally-unique, length-safe shadow-table name for one refresh run. Deliberately NOT derived from the materialized table name: two refreshes of the SAME materialization (a manual "refresh now" racing the scheduled sweep, or overlapping sweeps under ConcurrencyMode=Concurrent) must not share a shadow, or one run's DROP TABLE …__shadow would yank the table the other is mid-build. A fixed short prefix + a random token keeps it well under both engines' identifier limits (PG 63 / SQL Server 128) regardless of how long the canonical table name is. The shadow is renamed INTO the canonical name on success (so it leaves no residue), and dropped by RefreshOne's failure cleanup on a caught error; only a hard process crash between shadow creation and swap can leak one — a harmless orphan table with no dependents.

Map a SQL-Server-style SQLFullType (e.g. nvarchar(255), int, bit) to a PostgreSQL column type.

Forced-full-rebuild cadence counter transition. Increments on a genuine incremental refresh; resets to 0 on any full rebuild — so the counter measures how many refreshes we've gone WITHOUT a full reconcile. Pure (no IO) so the increment/reset semantics are unit-testable. Null-safe: an unset counter is 0.

Phase 3: parse the materialization's KeyColumns metadata (JSON array of {name, type}) into the hash-key column list, or undefined when it isn't keyed. A null/empty/malformed value yields undefined — the caller then uses the synthetic IDENTITY/ROW_NUMBER surrogate (Phase 1/2 behavior).

Quote a SQL identifier for the engine, ESCAPING the closing delimiter so a column name that contains it can't break out of the quotes: ]→]] (SQL Server), "→"" (PostgreSQL). Column names in the keyed/incremental builders are CodeGen-derived (entity field / KeyColumns names), so this is a consistency + robustness guard (matching buildExternalRebuildPlan's escId), not a live-injection fix.

Resolves the SQL that the READ path would execute for queryId on the engine we are refreshing against, so the snapshot is built from the same statement live serves. Mirrors the read path's QueryInfo.GetPlatformSQL(PlatformKey), whose precedence is: MJ: Query SQLs child row for the platform → legacy PlatformVariants → base SQL.

GetPlatformSQL lives on the metadata QueryInfo, not on the generated MJQueryEntity, so the variant is resolved through the provider's query metadata. Falls back to the entity's own SQL when the query isn't present in that metadata (e.g. a provider whose cache hasn't loaded it), which reproduces exactly the previous behavior rather than failing the refresh.

Forced-full-rebuild cadence decision: should this refresh be forced to a full rebuild? True once the count of consecutive incremental refreshes since the last full rebuild has reached FULL_REBUILD_EVERY_N_INCREMENTAL_REFRESHES. Pure (no IO) so the cadence boundary is unit-testable without a provider/DB. Null-safe: an unset counter is treated as 0.

A SQL datetime literal (ISO-8601 UTC) parsed by both SQL Server and PostgreSQL.

Remove a TOP-LEVEL ORDER BY from a source SELECT so it can be wrapped in a derived table for the rebuild. See resolveSourceSelect for why (SQL Server error 1033) and why it's safe (a snapshot is unordered).

Two guards keep this from corrupting results:

• PostgreSQL is a no-op — PG permits ORDER BY inside a derived table, so there's nothing to fix and we skip the parser round-trip entirely.
• A query with a row-LIMITING clause (SQL Server TOP or OFFSET/FETCH) is left UNCHANGED — there the ORDER BY is both (a) LEGAL in a derived table and (b) SEMANTICALLY REQUIRED: it decides WHICH rows TOP/FETCH keep, so stripping it would materialize an arbitrary subset (a silent wrong-data bug). Only a BARE top-level ORDER BY (pure presentation sort, no limiting) is both illegal-in-derived-table and safe to drop.

Uses the SQL parser; on any parse/shape surprise, or no top-level ORDER BY, returns the SQL unchanged (an ORDER BY nested inside a subquery is legal and left intact).