Document: loomworks-list-loading-cr-1-engine-cursor-contract-v0_3
Version: v0.3
Status: BUILT and pushed (feature branch, not merged) — see As-built footer
Date: 2026-06-27
Supersedes: loomworks-list-loading-cr-1-engine-cursor-contract-v0_2 (2026-06-27), …-v0_1
Queue item: Companion build queue Item 1 — list-loading (cross-cutting pagination)
Handoff: loomworks-list-loading-cr-drafting-handoff-v0_1
Step-0 sources: loomworks-list-loading-step-0-report-v0_1 (live endpoint inventory) + loomworks-list-loading-cr-1-step0perstep-report-v0_1 (per-claim live-string verification; the report header's 707e381 is the loomworks-record HEAD at read time, not an engine SHA — the engine read pass and build both ran on baseline 46ebaeb / alembic 0095, post-Stele)
Repo: loomworks-engine
Two-role note: This CR is scoping/drafting. CC executes. Nothing commits or pushes without the Operator's explicit authorisation — staged by explicit path, per-step commits, suite green at each step, verified against playground_dev, push on separate authorisation.
The v0.1 → Step-0-per-step verification pass (read-only, nothing committed to the engine; the read-pass report's 707e381 header is the loomworks-record HEAD, not the engine baseline — see Step-0 sources above) surfaced five findings. Three change the build materially; two close decisions. Recorded here per corrections-preserved discipline — the v0.1 positions are named alongside the v0.2 corrections.
| Finding | v0.1 position | v0.2 correction |
|---|---|---|
| Codec is not reuse-as-is | "Reuse Group A's codec, don't write a second" — treated as a confidence note | The codec (dashboard.py:61–115, private) is datetime-bound, DESC-bound, module-private. Generalising it is now mandatory and first — new Step 0 lifts it to a shared module and makes it type-agnostic + direction-aware. Every endpoint step depends on it. |
| Two JSON-text sorts need an index | Step 8 assertions flagged as "expensive COALESCE, surface"; Step 7 shapings classed as "clean Group D timestamp upgrade" | Step 7 shapings is not a clean timestamp sort — it sorts by (payload ->> 'created_at') JSON text, same shape as assertions. Both are unindexed JSON-text keysets. Operator decision: add two functional indexes. CR-1 now carries one migration. |
| Posture change | "Pure-read throughout, no migration" | GET handlers stay pure-read (Memory-read-boundary classification intact); but CR-1 is no longer no-schema-change — it adds two functional indexes. Named explicitly. Rationale: "we will not want to return to this" — pay the structural cost once, now. |
| D-3 revised up | Lean toward degenerate group_id-only keyset fallback |
Every AssertionGroup carries its own created_at+id (memory/base.py:118) → real composite (group.created_at, group.id). Plus a known-limitation note: the handler materialises the whole snapshot in Python, so the cursor bounds response payload size, not server cost. |
| D-7 blast radius | "One frontend caller (useConversation.ts)" |
Two OL callers — useConversation.ts (Axis 11, passes before) and ChatView.tsx (home, no before). Decision survives; CR-2's frontend pass updates both. |
Decisions now closed: D-1 (confirmed viable), D-2 (codec fix unblocks it), D-3 (revised, closed), D-4 (collapses pending a build-time null check), D-5 + D-8 (index — Operator decided).
Decisions surviving to CR-2: D-6 (renders ASC preserved), D-7 (before→cursor, two files).
CC began the build and hit a hard Postgres constraint writing the Step 0b migration. Empirical findings on playground_dev, then the resolution — recorded per corrections-preserved discipline.
The blocker. The shapings functional index on (payload ->> 'created_at') builds cleanly (plain text, no cast). The assertions index does not: assertions sort by COALESCE((payload ->> 'created_at')::timestamptz, last_updated_at) DESC, object_id ASC, and Postgres rejects a functional index on that expression because the text → timestamptz cast is STABLE, not IMMUTABLE (it can read the session TimeZone). Postgres will not freeze a session-dependent expression into an index.
Empirical facts (playground_dev, 112 assertion rows). Zero rows have a null payload.created_at — the last_updated_at fallback arm is never exercised today. Every created_at value is explicit-UTC ISO8601 (…Z) — so lexical text order currently equals chronological order exactly. (CC's throwaway test indexes were verified rolled back — no leak; engine working tree unchanged.)
The sub-fork (D-5 reopened at build). v0.2 chose "add the index" but reserved the re-scope trigger: "if it needs a computed sort column → re-scope before proceeding." That trigger fired. Three options were on the table:
1. Text keyset — drop the cast, index (payload ->> 'created_at') as plain text like shapings. Correct for all 112 current rows; structurally identical to shapings. Risk: correctness depends on every value staying …Z — a single non-UTC value (…+05:00) makes text order diverge from time order at a page boundary (the R2 failure), and nothing in the schema enforces UTC.
2. IMMUTABLE wrapper — wrap the conversion in a UTC-pinned IMMUTABLE function, index that, preserve exact parsed-timestamp-with-fallback semantics.
3. Text keyset + write-time UTC-normalisation guard — option 1 plus a write-path guard enforcing …Z; removes the risk but expands CR-1 into write-path scope.
Operator decision: option 2 (IMMUTABLE wrapper). Rationale: it is the only option whose correctness does not depend on an unenforced invariant. Text order equaling time order is a property of today's data, not of the schema; the wrapper sorts by the real instant, so a non-UTC value lands at its true moment and the null fallback survives. "It is the only option that actually survives." The wrapper's UTC pin is load-bearing — it is what makes the function IMMUTABLE and therefore indexable; removing it silently makes the index wrong under a non-UTC session. This is named in the migration and the CR so no future editor strips it.
Consequence for the build. Step 0b now creates two differently-shaped indexes: shapings on plain text (payload ->> 'created_at'); assertions on a new UTC-pinned IMMUTABLE wrapper function. Steps 7 (shapings) and 8 (assertions) reference their respective indexed expressions verbatim in both ORDER BY and keyset WHERE, so the planner uses each index. D-4 (Step 6) also resolved empirically below.
Brings every unbounded listing endpoint in loomworks-engine to one keyset-pagination contract:
{ts, id}, where ts is the endpoint's sort value (a datetime in most cases, an int for shape-events). This is the Group A shape, generalised beyond datetime.limit (bounded)has_more flagtotal_countTen endpoints are touched: five from scratch (currently wholesale) and five conform-upgrades (currently offset- or created_at-only). The conform-upgrade set includes /operator/conversation-history — the Axis 11 R2 follow-on the prior phase deliberately left open (manifest Entry 109).
Before any endpoint, Step 0 lifts and generalises the shared cursor codec. Step 0b adds two functional indexes (assertions, shapings) so their JSON-text keyset is fast. Then the ten endpoints, each consuming the shared codec.
The four already-conformant endpoints (/me/dashboard/needs_you, /recent, /active, /operator/inbox) get no engine work. Their home consumption is a frontend question for CR-2.
CR-1 is engine-only. The reusable load-more control, per-surface page-size defaults, the lists.page_size override, and home-lens wiring are all CR-2, drafted against this merged contract.
Every GET handler in scope is a pure read — confirmed SELECT-only, no writes, no derivation, no provenance/FORAY contact (Step 0e + per-step guard re-confirmed all five Group E endpoints). The Memory-read-boundary classification Axis 11 carried still holds for the read paths.
What changed from v0.1: CR-1 is no longer no-schema-change. Step 0b adds two functional indexes — shapings on plain text (payload ->> 'created_at'), assertions on a UTC-pinned IMMUTABLE wrapper function (the raw cast is non-IMMUTABLE; see §5 and the v0.3 change log). A functional index on a read path is a low-risk migration — it adds no column, changes no data, alters no behaviour, only speeds a sort — but it is a schema change and bumps the alembic head. The CR names this rather than smoothing it: the v0.1 "pure-read throughout, no migration" line is superseded.
Request params:
- limit: int — bounded per endpoint (see step table). Default 50 unless the endpoint's current default differs and the Operator keeps it.
- cursor: Optional[str] — opaque base64 of {"ts": <sort-value>, "id": <object-id>}. ts is a datetime for most endpoints, an int for shape-events. Absent on first page.
Response additions (alongside the existing item list):
- has_more: bool
- total_count: int
- next_cursor: Optional[str] — null when has_more is false.
Cursor semantics (the R2 lesson):
- ORDER BY is composite on (sort_key, id) in the endpoint's chosen direction.
- The boundary filter is the composite tuple comparison, not a sort_key-alone strict comparison. DESC: WHERE (sort_key, id) < (:cursor_val, :cursor_id). ASC (renders only): WHERE (sort_key, id) > (:cursor_val, :cursor_id). Row-value comparison, so a row sharing the boundary value is not skipped.
- sort_key is the endpoint's existing sort value (named per endpoint). id is the stable object id in each ORDER BY tail.
total_count semantics: count of the full filtered set the endpoint would return wholesale (filters applied, pagination not) — so the frontend renders "showing N of TOTAL" (no silent truncation).
The codec lives private in src/loomworks/api/routers/dashboard.py:61–115: _encode_cursor, _decode_cursor, _cursor_clause, _cursor_params, _next_cursor. Step-0-per-step confirmed it is generic over column name (takes alias_ts/alias_id, ts_attr/id_attr) — the CR's earlier hardcoded-created_at worry does not bite. But it diverges three ways that block the new endpoints:
isoformat/fromisoformat. Blocks shape-events (int version, D-2) and complicates null handling (D-4).< — blocks renders (ASC >, Step 9).dashboard.py; the other routers can't import it.Step 0 work:
- Lift the five functions into a shared module. Lean: src/loomworks/api/pagination.py. [CC confirms the idiomatic home — if the codebase already has a shared-util convention that fits better, use it.]
- Make the codec type-agnostic: ts serialises a datetime (ISO8601) or an int (shape-events version). Encode the value-type so decode round-trips correctly.
- Make it direction-aware: the clause builder emits < for DESC and > for ASC.
- Group A's four endpoints (needs_you, recent, active, inbox) currently import the private functions. After the lift, re-point their imports to the shared module and confirm the four stay green unchanged — they are the regression anchor for Step 0.
Step 0 ships green with the four Group A endpoints behaving identically through the relocated codec. No endpoint behaviour changes in Step 0 — it is pure refactor + generalisation.
Assertions and shapings both sort by a value extracted from JSON at query time, with no index on the expression. A keyset cursor over an unindexed JSON-text sort is a sequential scan that grows with the table. Operator decision (D-5 + D-8): add the indexes. Build-time finding reshaped how assertions is indexed (v0.3 change log) — the two indexes are differently shaped.
Shapings index — plain text (builds cleanly).
Functional index on (payload ->> 'created_at') for the shapings base table. Plain text, no cast, IMMUTABLE — Postgres accepts it directly.
Assertions index — UTC-pinned IMMUTABLE wrapper (the build-time fork).
A plain functional index on the assertions sort COALESCE((payload ->> 'created_at')::timestamptz, last_updated_at) is rejected by Postgres — the text → timestamptz cast is STABLE (reads session TimeZone), not IMMUTABLE. So:
- Define an IMMUTABLE wrapper function that parses payload ->> 'created_at' to a timestamp pinned to UTC (… AT TIME ZONE 'UTC', so it never reads session state), applies the last_updated_at fallback, and is marked IMMUTABLE.
- Index the assertions base table on wrapper(payload, last_updated_at) DESC, object_id ASC.
[CC settles the wrapper signature and the exact base table for each — both endpoints read JSON payload; the index goes on the base table the GET selects from (assertions appear to source from current_memory_objects filtered to object_type='assertion' — confirm). Confirm the wrapper reproduces the live COALESCE semantics exactly (fallback arm included) and that it parses to UTC, not session-local.]
The UTC pin is load-bearing. It is what makes the wrapper IMMUTABLE and therefore indexable. Removing it — or letting the parse read the session timezone — silently makes the index disagree with the query under a non-UTC session. The migration carries a comment saying so. Do not "simplify" the wrapper by dropping the UTC pin.
Planner contract (both endpoints). Steps 7 and 8 must reference the identical indexed expression in ORDER BY and in the keyset WHERE boundary — plain (payload ->> 'created_at') for shapings, the wrapper call for assertions. If the query expression and the index expression drift, the planner falls back to a seq scan and the migration's value is lost. The post-step EXPLAIN check (§10) verifies each index is actually used.
One alembic revision creates both (the wrapper function + two indexes), head bumped. Additive and reversible (DROP INDEX + DROP FUNCTION downgrade).
Inspect-first. Before editing any endpoint, CC reads the live handler and confirms the exact sort key, the exact id column in the ORDER BY tail, the response-model name, and whether total_count already exists.
Live-string-from-codebase. Route paths, model names, column names, the codec import — read live, substituted.
Halt threshold. >30 tests touched by one step → halt and surface. Any endpoint whose sort key cannot map to the (sort_key, id) composite → halt; do not improvise a cursor shape.
Pure-read guard. If any GET handler in scope is found to write, derive, or touch a provenance/FORAY thread, halt — the read classification breaks and the CR re-scopes. (Step-0-per-step re-confirmed all five Group E endpoints SELECT-only; this guard is a per-step backstop.)
Test posture per endpoint: first page returns ≤ limit; has_more true iff a further page exists; next_cursor round-trips with no skip and no repeat; boundary-tie test — two rows sharing the sort value straddling the page boundary are both returned across the two pages (the R2 regression guard); total_count equals the wholesale count under the same filters. Suite green at each step.
Step 0 (codec) and Step 0b (indexes) first. Then the ten endpoints: five from-scratch (steps 1–5), five conform-upgrades (steps 6–10), reference endpoint last.
GET /engagements (from scratch)| Group | E — wholesale |
| Returns | engagement summaries (home "Your engagements") |
| Live sort | e.id (no timestamp sort today) |
| D-1 — CLOSED | engagements.created_at exists, non-null (migration 0001, confirmed). Sort by created_at DESC, id tiebreak — newest engagement first. |
| Delta | add limit + composite (created_at, id) cursor + has_more + total_count; change sort from e.id to (created_at DESC, id). |
| Bound | limit 1–100, default 50. |
GET /engagements/{id}/considerations (from scratch)| Group | E — wholesale, filters only |
| Live sort | last_updated_at DESC |
| Filters (preserve) | triggering_reason, current_stage, terminal_state, since |
| Delta | add limit + composite (last_updated_at, object_id) cursor + has_more + total_count; keep all four filters. |
| Bound | limit 1–100, default 50. |
GET /engagements/{id}/compositions (from scratch)| Group | E — wholesale, state filter only |
| Live sort | created_at DESC — no id tiebreak in the live ORDER BY (confirmed) |
| Filter (preserve) | state |
| Delta | add limit + composite (created_at, object_id) cursor + has_more + total_count; keep state; add the object_id tiebreak to the ORDER BY (the live query lacks it — required for a stable keyset). |
| Bound | limit 1–100, default 50. |
GET /engagements/{id}/shape-events (from scratch)| Group | E — wholesale, filters only |
| Live sort | engagement_version_at_production ASC, object_id ASC — int version, not a timestamp |
| Filters (preserve) | declared_shape_type_id, state, version range, self_consumer |
| D-2 — CLOSED | Keep the version-based sort; cursor is composite (engagement_version_at_production, object_id). The generalised codec (Step 0) now serialises an int ts, so this works without a sort change. Production order is the meaningful order — preserved. This is the one endpoint where the contract's "(sort_key, id)" reads as an int pair, ASC (> boundary). |
| Delta | add limit + composite int cursor + has_more + total_count; keep all filters and the ASC version sort. |
| Bound | limit 1–100, default 50. |
GET /engagements/{id}/assertion-groups (from scratch)| Group | E — wholesale |
| Live order | snapshot dict order (no DB sort) |
| D-3 — CLOSED (revised up) | Every AssertionGroup carries its own created_at+id (memory/base.py:118, confirmed). Sort (group.created_at DESC, group.id); cursor composite on that pair. Not the degenerate id-only fallback v0.1 leaned toward. |
| Known limitation (recorded, not blocking) | The handler materialises the whole engagement snapshot in Python, then sorts/slices. So the cursor bounds the response payload (what crosses the wire, what the frontend renders) — it does not reduce server cost; the full snapshot is built regardless. The cursor still does its job (no unbounded payload, total_count honest), but snapshot-build cost is a separate concern. Flagged as a follow-on — not fixed here; whether to push the sort/slice into the snapshot computation is its own scoping question. |
| Delta | sort the materialised groups by (created_at DESC, id); apply cursor-slice + limit; surface has_more + total_count (total = full group count). |
| Bound | limit 1–100, default 50. |
GET /engagements/{id}/manifestations (conform-upgrade, Group D)| Group | D — offset only, no total_count, no has_more |
| Live sort | derived_at DESC NULLS LAST |
| Live pagination | limit (def 50, ≤500) + offset |
| D-4 — collapsed (empirical) | derived_at is nullable in schema but build-time scan found zero null rows on playground_dev — consistent with the "always set in production" expectation. Use the simple composite (derived_at, object_id); no null-rank machinery. [CC re-confirms zero null derived_at at build before relying on this; if the scan ever shows nulls, fall back to the null-rank-flag path — the boundary must keep nulls stably last under DESC NULLS LAST.] |
| Delta | replace offset with composite cursor; add total_count + has_more + next_cursor. |
| Bound | keep limit ≤500, default 50. |
GET /engagements/{id}/shapings (conform-upgrade — RECLASSIFIED, JSON-text sort)| Group | D by pagination posture, but sorts like Step 8 |
| Live sort | (payload ->> 'created_at') DESC — JSON text, no id tiebreak (confirmed — v0.1 wrongly called this a clean created_at DESC column sort) |
| Live pagination | limit (def 50, ≤500) + offset, no total_count |
| Depends on | Step 0b functional index on shapings (payload ->> 'created_at') |
| D-8 — CLOSED | Same fork as D-5 — Operator chose index. Keyset runs over the indexed JSON-text expression. |
| Delta | replace offset with composite cursor on ((payload ->> 'created_at'), object_id); add the object_id tiebreak (live sort lacks it); add total_count + has_more + next_cursor. The cursor ts is the extracted JSON value; the WHERE boundary applies the identical (payload ->> 'created_at') expression so the planner uses the Step-0b index. |
| Bound | keep limit ≤500, default 50. |
GET /engagements/{id}/assertions (conform-upgrade, Group C — JSON-text COALESCE sort)| Group | C — offset + total_count already present, no cursor, no has_more |
| Live sort | COALESCE(payload ->> 'created_at', last_updated_at) DESC, object_id ASC (confirmed verbatim; mixed direction) |
| Live pagination | limit (def 50, ≤200) + offset; total_count already returned |
| Depends on | Step 0b assertions index — the UTC-pinned IMMUTABLE wrapper (not a plain text index; the raw COALESCE cast is non-IMMUTABLE, see §5) |
| D-5 — CLOSED (build-time sub-fork → wrapper) | Operator chose the IMMUTABLE wrapper over a text keyset: it is the only option whose correctness survives a future non-UTC value. The cursor ts is the wrapper's parsed-UTC value; the WHERE boundary applies the identical wrapper expression on both sides so the keyset uses the Step-0b index. Empirical: 112 rows, zero null payload created_at, all …Z — but the wrapper is chosen precisely so correctness does not depend on that staying true. |
| Delta | replace offset with composite cursor over (wrapper(payload, last_updated_at), object_id); keep total_count (already present); add has_more + next_cursor. Respect mixed direction (DESC on the wrapped value, ASC on object_id) in the boundary comparison. |
| Bound | keep limit ≤200, default 50. |
GET /engagements/{id}/renders (conform-upgrade, Group C — the ASC endpoint)| Group | C — offset + total_count already present |
| Live sort | created_at ASC, object_id ASC — oldest-first, the one ascending default |
| Live pagination | limit (def 50, ≤500) + offset; total_count already returned |
| D-6 — survives to CR-2 (default: preserve ASC) | Renders order looks deliberately chronological-ascending (the Goosey-at-13 sequence note). Lean: preserve ASC. The codec is direction-aware after Step 0, so ASC is supported. The boundary comparison is > not <. |
| Delta | replace offset with composite (created_at, object_id) cursor ascending; keep total_count; add has_more + next_cursor. |
| Bound | keep limit ≤500, default 50. |
GET /operator/conversation-history (conform-upgrade, Group B — the Axis 11 R2 follow-on)| Group | B — created_at-only before cursor, no total_count |
| Live shape (confirmed) | param before: Optional[datetime]; boundary WHERE created_at < before (strict, ts-only); ORDER BY created_at DESC, id DESC; response ConversationHistoryResponse(turns, has_more), no total_count |
| Delta | upgrade the ts-only before cursor to the composite (created_at, id) opaque cursor — closes the R2 boundary-tie gap; add total_count; keep has_more. |
| D-7 — survives to CR-2 (blast radius two files, not one) | Switching before: datetime → opaque cursor is breaking for its OL callers. Step-0-per-step found two: useConversation.ts (Axis 11, passes before) and ChatView.tsx (home, no before). Lean: replace before with cursor cleanly; CR-2's frontend pass updates both call sites against the merged contract. Handoff already sequences CR-2 after CR-1 merges, so the coordinated swap is the intended shape. |
| Bound | limit 1–100, default 50 (unchanged). |
| # | Decision | Default |
|---|---|---|
| D-6 | Preserve renders' ASC (oldest-first) sort? | Yes — preserve; codec is direction-aware |
| D-7 | conversation-history before → cursor swap |
Replace; CR-2 updates the two OL call sites |
All build-time decisions (D-1 through D-5, D-8) are closed in §7. D-4 carries one build-time check (null-row scan on playground_dev); the path is specified for both outcomes.
lists.page_size voice-adjustable override, home-lens wiring — all CR-2.needs_you, recent, active, inbox) — no engine work beyond the Step-0 import re-point; their home consumption is CR-2's frontend question./operator/dashboard preview — wholesale correct, out of delta.CR-1 done when: Step 0 codec lifted, generalised (type-agnostic + direction-aware), four Group A endpoints re-pointed and still green; Step 0b landed — shapings text index + assertions UTC-pinned IMMUTABLE wrapper function + index, alembic head bumped, both indexes confirmed present on playground_dev; all ten endpoints return the §3 contract; every endpoint has a boundary-tie test; total_count matches the wholesale count under each endpoint's filters; EXPLAIN confirms the shapings text index and the assertions wrapper index are each used on the keyset boundary (not a seq scan); D-4 zero-null re-confirmed at build; suite green at each step; verified against playground_dev. Then CR-2 drafts against this merged contract.
DUNIN7 — Done In Seven LLC — Miami, Florida Loomworks list-loading — CR-1: engine cursor contract — v0.3 — 2026-06-27 Engine CR: pure-read GET paths + one migration (shapings text index + assertions UTC-pinned IMMUTABLE wrapper index). Step 0 codec + Step 0b indexes, then ten endpoints. Build paused at Step 0b for the assertions-index fork; resolved to the wrapper. Supersedes v0.2, v0.1.
CR-1 built to completion and pushed to feature branch list-loading-cr-1-engine-cursor-contract (not merged to main — nothing live yet). Final facts:
46ebaeb / alembic 0095 (post-Stele). Final HEAD: 46d655e. Step 0 (codec) committed 669dc6c.lw_assertion_sort_key(payload jsonb, last_updated_at timestamptz) (IMMUTABLE, SET TimeZone='UTC') + ix_cmo_assertion_sort_key + ix_cmo_shaping_created_at. Reversible. Both indexes + function confirmed present on playground_dev.ix_cmo_assertion_sort_key; shapings → Bitmap Index Scan on ix_cmo_shaping_created_at.playground_dev 12 manifestations, 0 null derived_at → simple composite (derived_at, object_id), null-rank machinery dropped.src/loomworks/pagination.py (lifted from dashboard.py; four Group A endpoints re-pointed, unchanged, green).git merge-base --is-ancestor 707e381 46ebaeb returned DIVERGED — a false positive: 707e381 is a loomworks-record SHA, not an engine commit (the read-pass report header inherited record-repo context). The gate's real question — were the live strings read on the build line — is answered by the green suite: a read off a different line would have failed the build against the real post-Stele schema. No Step-0-per-step re-run warranted.SET TimeZone='UTC' + ::timestamptz rather than the literal ::timestamp AT TIME ZONE 'UTC'. Identical results for all current (…Z) data; they diverge only on a future non-Z offset value, where ::timestamptz sorts by the real parsed instant (matching the live ORDER BY) and AT TIME ZONE would interpret the naive part as UTC. The built form (::timestamptz) is the real-instant semantics and is the recommended keep. One-line switch if the literal form is preferred.Next: merge decision on the engine feature branch. CR-2 (frontend load-more control + per-surface defaults + lists.page_size override + home-lens wiring) drafts against the merged contract — does not start until this branch lands on main.