ADR-0049 — Conversation recency GSI (GSI5): bound the all-properties /conversations read

Status: Accepted Date: 2026-06-07 Deciders: Gera Related: ADR-0030 (single-table design), src/lib/data/dynamo/helpers.ts (GSI constants), src/app/api/conversations/route.ts (the reader), docs/architecture/metrics-platform.md

The problem

The all-properties /conversations list read (getConversationsMeta() with no propertyId) fanned out a Query across every property partition plus the UNASSIGNED partition, then assembled the union in memory. On prod (~1,934 conversation meta rows across the property fleet) that fan-out measured ~998ms wall-clock — it grows linearly with both property count and conversation count, and every page load paid the full scan even though the UI only renders the newest page.

The existing indexes can't serve a bounded newest-N read:

• GSI3 (entityType-createdAt-index) stamps GSI3SK = conversation.id (a UUID) for conversations — not a timestamp. A Query on it returns conversations sorted by UUID, which is meaningless for recency. (Its name is historical; for conversations the SK is the id.)
• GSI1 (phone-index) / GSI2 (conversationId-index) are point-lookup indexes, not list-by-recency.

So a recency-sorted read genuinely requires a new index.

Decision

Add a fifth GSI, conv-recency-index (GSI5), single-partition, sorted by recency:

ProjectionType ALL (the list read needs the same meta fields the partition rows carry). A descending Query (ScanIndexForward: false) with Limit: N returns the global newest-N conversations in one ~1MB read.

Why single-partition

A global newest-N read wants exactly one Query — sharding GSI5PK would force a scatter-gather + merge, defeating the purpose. The write rate (conversations are created/updated at human-conversation cadence, not machine cadence) and the partition size (~1,934 rows of meta-only projection) are both well within DynamoDB single-partition limits. If conversation volume ever approaches the ~10GB / 3,000-RCU partition ceiling, the migration is a date-bucketed GSI5PK (CONV_RECENCY#<yyyy-mm>) — but that is not remotely close today.

Why bounded newest-N (cap 1000), not GSI-native cursor pagination

The /conversations route applies heavy post-read filters (synthetic / gauntlet exclusion, type, scope, re-attribution) and an in-memory sort + cursor over the result set. Native GSI page-size pagination would interleave badly with those filters (a native page of N could be entirely filtered out, yielding an empty UI page with a non-null cursor). Instead the route reads the newest ALL_PROPERTIES_RECENCY_CAP = 1000 conversations and runs its existing filter/sort/cursor logic over that bounded set.

Trade-off (explicit): conversations whose lastMessageAt falls below the newest-1000 horizon are not returned by the all-properties view. Given the fleet is ~1,934 rows today and the operator works the recent end of the list, this tail-trim is acceptable; property-scoped views (the renewals page) are unaffected — they still read the full property partition. Documented inline at the reader.

Write path

saveConversation re-stamps GSI5PK/GSI5SK on every full save (mirroring how the meta row already re-stamps GSI3). saveNewMessages does NOT rewrite the meta row, so GSI5SK tracks the persisted lastMessageAt exactly — it is as fresh as that field. A conversation with no time value at all gets no GSI5 keys and is simply absent from the index (exactly as DynamoDB omits any item missing a GSI key attribute). Stamping is mirrored into both data-layer trees (src/lib/data/ + agents/clara/lib/data/) because the Lambda inbound funnel also writes conversations.

Rollout (drain-before-flip, per the spine migration protocol)

GSIs back-populate only from items that already carry the key attributes, so the order is load-bearing:

1. Ship the write-path stamping (new rows carry GSI5 keys).
2. scripts/add-conv-recency-gsi.ts --table propflow-{dev,stage,prod} --wait — provision the index (idempotent; one GSI add at a time).
3. scripts/backfill-conv-recency-gsi.ts --mode=apply --confirm-table=propflow-prod — stamp GSI5 keys onto the pre-existing cohort (enumerates via GSI3 ENTITY_CONVERSATION; dry-run default; --confirm-table guard).
4. Only then does the bounded reader flip go live (it ships in the same branch; the prod data-work above runs before merge so the reader never queries an unpopulated index).

scripts/create-dynamo-table.sh also gains the conv-recency-index block so a fresh table (dev/test) is born with it.

Consequences

Amended 2026-07-18 (PR #4094): the bounded read now PAGINATES on LastEvaluatedKey up to limit — a single Query is capped by DynamoDB's 1MB response bound before Limit, which silently truncated the prod read at 678 rows (~May-28 horizon) and made the recency filter's "Last 90 days"/"All time" options under-report. ALL_PROPERTIES_RECENCY_CAP was also raised 1000 → 2500 so the horizon sits outside every window the filter offers (prod holds ~2,028 conversations total). The bullets below describe the original shape.

• All-properties /conversations first page: ~998ms fan-out → one bounded Query (~tens of ms + the route's in-memory filter over ≤1000 rows).
• New single-partition GSI on the prod table (one-time backfill of ~1,934 rows).
• The unbounded getConversationsMeta() (no limit) path is retained for callers that genuinely need every conversation (spine-rooted scans) — it still fans out. Only the limit-bearing all-properties call uses the index.
• Drift guard: conv-recency-index is added to the domain-table index-name allowlist in domain-gsi-index-names.drift.test.ts (the source-of-truth set of real table indexes), and that guard's literal-scan was widened to cover queryGSIBounded(...).

Alternatives considered

• Reuse GSI3 with a timestamp SK for conversations. Rejected: GSI3SK is the id by long-standing contract; rewriting it for conversations risks every GSI3-by-conversation reader and is a larger blast radius than a new index.
• GSI4. Reserved for the renewal-saga work (ADR-0035 D2). Using GSI5 keeps GSI4 free for that migration.
• In-memory LRU cache of the conversation list. Rejected: stale-on-write, doesn't survive cold starts, and still O(fleet) on the miss — treats the symptom, not the unbounded scan.