Materialized views and refresh

Two unrelated things in the Data API carry the word materialized, and only one of them has a refresh loop.

Declaration	Level	What it means	Wired
materialization:	entity	Physical shape of the entity — table, view, contract, virtual, remote	table and remote only
compute-strategy: materialized	field	The derived value is stored in a real column instead of computed on read	yes
compute.triggers:	field	Mutations on another entity queue a recompute of that column	hook and drainer run; the enqueue insert is rejected on PostgreSQL
views:	schema	A SQL view, optionally materialized, with a refresh schedule	no

The path that works end to end is the same-entity materialized computed field. Its cross-entity outbox is wired from the enqueue hook through to the admin endpoints, but the insert that would put a task in the queue fails on PostgreSQL — see What fires a refresh. The views: block and the non-table materialization modes are declarations the migration layer reads and then does nothing with.

Entity materialization modes

materialization: is declared per entity and is documented in full on Entities. What matters here is what each value does to the physical schema.

Value	Effect on DDL	Effect at runtime
table	A real table, created and diffed.	Normal SQL execution.
view	Excluded from the migration plan. Nothing issues CREATE VIEW.	Normal SQL execution against a relation nothing created.
contract	Excluded from the migration plan.	Normal SQL execution.
virtual	Excluded from the migration plan.	Normal SQL execution.
remote	Excluded from the migration plan.	Every operation is proxied to the external service in remote:.

view, contract and virtual are the same behaviour under three names: the planner skips the entity, so no table is created, altered or dropped for it. Nothing else in the engine branches on them — reads and writes still compile to SQL against a relation of that name, which you have to create yourself.

Exclusion holds only on the planned migration path

The direct migrate path calls create-table for every entity in the schema regardless of materialization, so a materialization: view entity gets a physical table there. Only the plan/apply path honours the exclusion. Unrecognised values — including the British spelling materialisation:, which binds to nothing — silently resolve to table.

Declaring a schema-level views: block parses cleanly, and the dialects carry view-creation, materialized-view and refresh methods — SQLite’s materialized-view method returns an error. No caller invokes any of them. There is no view DDL, no refresh schedule, and no scheduler; a refresh.schedule: cron expression is stored and never read.

Materialized computed fields

A field with compute-strategy: materialized is stored. The strategy is the default for computed fields — see Fields for the declaration syntax, the script languages, and the virtual alternative. Two independent paths keep the column current:

Path	When it runs	Mechanism
Same entity	Every create and update of the owning row	The materialized hook at BeforeExec, priority 290. The value is written in the same statement.
Cross entity	A mutation on a source entity named in compute.triggers	An outbox row per affected target, drained asynchronously.

The same-entity path is synchronous and needs no triggers. Everything below concerns the cross-entity path.

A tenant whose schema declares no compute.triggers anywhere gets no outbox machinery at all: no enqueue hook, no drainer, and the admin endpoints below answer 500.

What fires a refresh

The enqueue hook runs at AfterExec, priority 650 — after the source mutation has executed and after the event hook, so an event listener sees the source write before the refresh tasks land. For each trigger that names the mutated entity it applies these gates in order:

Gate	Rule
on	The operation must be listed. An empty list matches create, update and delete.
when_columns	Update only. Skip when none of the listed columns differ between pre-image and post-image. Create and delete ignore it.
when	Script predicate. Create evaluates the post-image, delete the pre-image, update evaluates both and enqueues if either matches. An evaluation error skips the trigger and records materialized_refresh:when_error:<target entity>.<field> on the hook context, which the response never carries.
affected-row resolution	The trigger’s affected_via column is read out of the source row to produce the target row id.

Resolution is image-dependent: create reads the post-image, delete the pre-image, and update reads both — so an update that reassigns the foreign key enqueues two tasks, one for the old parent and one for the new. When the two ids are equal, one task is enqueued. A source row whose key column is missing or null yields no task.

The pre-image only exists when the payload carries the primary key

The pre-image hook reads the primary key out of the mutation payload and returns without fetching anything when it is absent. The REST update path strips the primary key from the payload before the engine sees it, and the REST delete path sends no payload at all — so on both, the pre-image is nil. when_columns then degrades to “one of these columns appears in the update”, an update that reassigns the foreign key enqueues a task for the new parent only, and a delete trigger resolves no target row and enqueues nothing.

Only affected_via resolves a target row

affected_via_expr passes load-time validation and then never enqueues anything: the enqueue hook’s script surface is boolean-only, so the expression path produces an empty target id and the task is dropped silently. affected_via_cols joins the column values into one string, but the drainer looks the target up with a single-column primary-key equality on that whole string, which matches nothing. Use the single-column affected_via form and put any further logic inside the compute script body, which has full runtime access.

Enqueue is best-effort and is not part of the source transaction. If the insert into the outbox fails, the source mutation still succeeds and the error is recorded on the request’s hook context under materialized_refresh:enqueue_error, where a pointcut script can read it through ctx.metadata. The response does not carry it. Nothing re-derives the lost task — the stored value stays stale until another qualifying mutation enqueues a new one.

The outbox insert is rejected on PostgreSQL

The outbox’s id column is CHAR(27) and the enqueue path fills it with a 36-character UUID, so PostgreSQL refuses every insert with value too long for type character(27). Because enqueue is best-effort, the source mutation still returns success and the error goes only to the hook context. data.svc serves PostgreSQL only for tenant datastores, so on a shipping deployment no refresh task is ever written: the queue stays empty, the drainer has nothing to claim, and a materialized field with compute.triggers keeps whatever the write-time path last put in it.

The outbox table

Refresh tasks live in the engine-managed system table data_materialized_refresh_tasks, created by bootstrap alongside the other data_ tables.

Column	Contents
id	Task id, primary key.
target_entity	Entity owning the materialized field.
target_field	Field to recompute.
target_row_id	Stringified primary-key value of the row to refresh.
source_entity	Entity whose mutation triggered the task.
source_op	create, update or delete.
status	pending, claimed, done or failed.
enqueued_at	When the hook wrote the row.
claimed_by	Worker that claimed it. Null while pending.
claimed_at	When the claim happened. Null while pending.
done_at	When status became done or failed.
attempts	Incremented on every claim.
last_error	Message from the most recent failure. Empty otherwise.

Plus the four audit columns. Three indexes ship: (status, enqueued_at) for the drain, (target_entity, target_field, target_row_id) for coalescing, and (source_entity, status) for triage.

State machine:

pending ──claim──> claimed ──ok────> done
                      │
                      └──error────> failed

failed is terminal — see Failure and retry.

The drainer

One drainer runs per (tenant, datastore) inside the service process, spawned when the engine for that pair is built and stopped when it is dropped.

Setting	Default
Batch size	50 tasks per pass
Tick interval	30s
Max attempts	5
Worker id	<service>:<tenant>

A pass runs on any of four triggers: an enqueue notification (immediate, coalesced one deep, so a burst of enqueues collapses into a single pass), the periodic tick, an explicit force-drain, and one best-effort final pass on shutdown.

Each pass claims, then processes:

1. Claim. Select pending tasks with attempts below the maximum, ordered oldest first, over-fetching four times the batch size. Coalesce to at most one task per (target_entity, target_field, target_row_id) triple, keeping the oldest and marking the superseded duplicates done in one statement. Claim the keepers with a status-conditional update that also increments attempts, so a race with another worker degrades to fewer claims rather than double processing.
2. Process. Tasks in the batch are processed concurrently. For each: resolve the target field, load the whole target row by primary key, evaluate the compute script, and write the result back with a single-column update. A target row that no longer exists marks the task done, not failed. When the new value compares equal to the current one, the update is skipped.

Script bindings on the refresh path are the row as both self and row, plus tenant, entity and field, plus every column of the target row hoisted to the top level. They differ from the write-time bindings on the same-entity path, which carry user_id and the payload rather than the stored row.

The write-back does not go through the engine

The shipped store writes the recomputed value with a direct UPDATE <table> SET <field> = … WHERE <pk> = …. Row-level security, hooks, triggers and audit-column population are all bypassed on that statement, and because the enqueue hook is not in the path, a refresh never cascades into a downstream materialized field that depends on the target entity. Cycle detection in the loader still refuses a cyclic trigger graph at load time.

The outbox store emits $N-placeholder SQL and is installed for any datastore whose pool exposes a database/sql handle — there is no backend gate, and PostgreSQL is the case the SQL is written for. On PostgreSQL the store qualifies both the tasks table and the target entity tables with the tenant namespace — that qualification is the tenant isolation for this path, because it takes a raw pool handle rather than a tenant-scoped checkout.

Failure and retry

Outcome	Task status	Value
Script evaluates, value changed	done	Written
Script evaluates, value unchanged	done	Untouched
Target row deleted since enqueue	done	n/a
Target field is no longer a materialized computed field	failed	Stale
Script error, row load error, or update error	failed	Stale

A failed task is never retried

The claim query selects status = 'pending' and the failure path writes status = 'failed' unconditionally, so the attempts budget never comes into play — one failure is terminal. Re-queue by hand once the cause is fixed:

UPDATE data_materialized_refresh_tasks
SET status = 'pending', attempts = 0, last_error = ''
WHERE status = 'failed'
  AND target_entity = 'customer';

Completed and failed rows are never pruned. Trim them on your own schedule:

DELETE FROM data_materialized_refresh_tasks
WHERE status = 'done'
  AND done_at < NOW() - INTERVAL '7 days';

A queue-depth dashboard that counts all rows rather than status = 'pending' will therefore grow without bound and tell you nothing.

Operating the queue

The admin surface mounts at /admin/data/*; behind the gateway it sits under /data/. Every route takes an optional ?datastore=<name> and requires a bearer token plus the admin policy gate for its class.

Route	Class	Purpose
GET /admin/data/materialize/pending	read	Pending and failed tasks, 100 per page, ?after=<task id>
GET /admin/data/materialize/stats	read	Queue depth by status plus the drainer’s counters
POST /admin/data/materialize/drain	maintenance	Run one drain pass now

{
  "tenant": "acme:shop:dev:main",
  "datastore": "primary",
  "counts": { "Pending": 12, "Claimed": 0, "Done": 8134, "Failed": 3 },
  "runner": { "Claimed": 8149, "Done": 8146, "Failed": 3 }
}

The runner block is omitted when no drainer exists for the tenant, and its counters are cumulative since process start — they reset on restart. The durable view is the table. drain answers with tenant, datastore and dispatched, the last counting tasks dispatched regardless of outcome.

Note

The after cursor filters on id > <cursor> while the listing is ordered by status, then enqueue time, then id. Pages after the first are not a reliable continuation of the sort; for anything beyond triage, query the table directly.

All three return 403 with {"error": "tenant key missing from request context"} when the request carries no tenant identity, and 500 when the tenant has no refresh machinery — its schema declares no cross-entity triggers, so there is nothing to report on.

The same three operations are on the service CLI:

data.svc data materialize pending --tenant acme:shop:dev:main -f bootstrap.yaml
data.svc data materialize stats --tenant acme:shop:dev:main -f bootstrap.yaml
data.svc data materialize drain --tenant acme:shop:dev:main -f bootstrap.yaml

Add --datastore <name> to target a datastore other than the default and --output json for machine-readable output. drain before an assertion that depends on a materialized value — after a bulk import, for example — rather than waiting out the 30-second tick.