Function purgeExpiredThreads

Thread-level retention (docs/agent-memory-tenancy.md item 7): deletes agent memory threads whose updatedAt is older than the TTL, each with its messages. The terminal-status shape purgeExpiredWorkflowRuns uses does not transfer — threads are not per-run and have no status, so there is nothing to prove "finished" about one. Time since last write IS the only signal a thread is done, which is why the TTL keys on updatedAt (the D1 memory domain stamps it on every saveThread, so an active conversation never ages out).

MESSAGES BEFORE THREADS: a message carries a createdAt but no updatedAt, so there is no per-message IDLENESS signal — a message's lifetime is its thread's, and its rows are reachable only through it. Deleting a thread first would strand its messages beyond every later firing of this purge (only purgeTenant, at offboarding, still ranges over their salted thread_id).

EVERY statement re-checks the CURRENT row rather than trusting the SELECT's id list, and that is load-bearing here in a way it is not for purgeExpiredWorkflowRuns (where terminal status is absorbing, so its re-check is belt-and-braces). "Idle" is precisely NOT absorbing — a thread can come back to life mid-purge — and the writer is not atomic either: the memory domain's saveMessages issues its message insert and its UPDATE mastra_threads SET updatedAt as two INDEPENDENT calls under one Promise.all, so a send's message can be visible while its thread still reads idle. That TORN state is what each guard answers:

• The message DELETE re-reads the thread's current updatedAt (subquery) AND bounds itself to createdAt < cutoff. The subquery alone would sweep a just-arrived message into the same statement as the genuinely old ones — it keys on the THREAD's staleness, and the bump has not landed — silently destroying the message the user just sent. createdAt is the message's own evidence of recency, and the only guard that survives a torn write.
• The thread DELETE re-checks updatedAt < cutoff AND fires only when NOT EXISTS any message for it. Sequence alone cannot carry "messages before threads": a message landing after the message DELETE leaves an idle-looking thread whose row would go out from under it. The NOT EXISTS makes the invariant — never delete a thread a message points at — a property of the statement rather than of timing.

Together: a torn or concurrent send at ANY point in the sequence keeps both its message and its thread; the thread's next firing (by then bumped, or by then genuinely idle again) decides its fate on fresh evidence.

Residuals, both accepted, neither reachable by a read path: (1) a send landing between the two DELETEs loses the already-expiring history while the thread and the new message survive — strictly better than the deletion that was milliseconds away. (2) A message inserted AFTER its thread's row is already gone is orphaned by the writer, not by this purge; D1 offers no cross-statement transaction through this seam to close it. An orphan is unreachable by recall (nothing lists a thread that does not exist), stays tenant-salted on its thread_id, and is reaped by purgeTenant at offboarding — a hygiene cost, not a leak.

mastra_resources (working memory) is deliberately untouched: a resource is the OWNER's, shared across every thread they have, so one thread aging out says nothing about it. It is reaped at offboarding (purgeTenant).

Missing tables read as zero (a deployment with no agent memory never created them). Scheduling stays with the caller — see createFlowsafeWorker's THREAD_RETENTION_DAYS duty.