Rules an app follows

Gate with duckweb_match, never with WHERE route = …. An ungrouped aggregate emits its row even when its filter matched nothing, so a WHERE clause lets one route’s output leak into every other route. Joining against duckweb_match gates the branch relationally, and a non-matching route then costs nothing — DuckDB prunes the whole branch.

Keep aggregates out of the top level of a branch. Put them in a scalar subquery, so the branch’s row count comes from duckweb_match:

-- leaks on every route
SELECT count(*) FROM duckweb_match(route, '/x'), notes
-- correct
SELECT duckweb_chunk((SELECT count(*)::VARCHAR FROM notes))
FROM duckweb_match(route, '/x')

Emit the head first and the terminator last. render carries no ORDER BY, because sorting is blocking and would defeat streaming. Ordering therefore rests on preserve_insertion_order, which the transport sets explicitly and test/run-ordering.sh checks against a parallel plan.

Watch for macro parameters shadowing columns. render(method, route, headers, body) binds body, so SELECT body FROM templates reads the parameter, not the column, and silently yields the argument. Qualify columns: SELECT t.body FROM templates t.

Every route must be written out literally. duckweb_match reads its pattern at bind time, so patterns cannot come from a table.

Everything render mentions must exist, on every route. A macro is bound as a single query, so one missing table or function makes every route fail, not only the one that needed it. A writable table the app reads has to be created before the first request arrives.

render returns exactly one column. The server reads the first and refuses anything else; a stray seq column beside the chunk is the usual way this goes wrong.

Expensive work belongs in rows, not in a scalar subquery. An uncorrelated subquery is evaluated before the first row is emitted, so it delays the head and defeats streaming. (SELECT count(*) FROM t) is fine when it is cheap; when it is not, produce rows instead.

Slot ids and script re-runs. The client re-parses the whole response on each flush rather than appending to it, because insertAdjacentHTML parses a chunk on its own: a fragment belonging inside an open element — a <tr>, an <li> — has its tags discarded and only its text survives. A browser reading the same bytes from a socket keeps that context. The consequence for apps is that any script a response emits runs on every flush, so it must be idempotent.

Checkpoint an app database before copying it. DuckDB leaves the contents in a .wal beside the file, and a .duckdb copied without it serves nothing at all. toyapp/build.sh runs CHECKPOINT and then fails if the wal survives.