Egregoros

@harblinger

It's not bad! Creating a sibling to get_direct_conversations is a good idea.

>2. What Mitra already has
>The missing piece is only that nothing orders conversations by activity, and nothing exposes them at public visibility.

This is correct. However, I think the results of get_direct_conversations can be ordered by activity (post.created_at). I made a quick test, the estimated cost of the query is exactly the same.

>3.1 A bump column on conversation

...So this shouldn't be necessary.

>Three details worth deciding deliberately:
>Reposts have no conversation_id

I've never seen an imageboard with reposts. I think only comments should bump threads.

>Only public activity should bump a public thread.

If the cost is not too high, we can bump threads on private comments too. get_direct_conversations already does that.

>3.2 The endpoint

I think /api/v1/conversations is a better prefix for the endpoint.

>root_status — the OP; this is the catalog card. The query already joins post AS root, it just doesn't return it today.
>last_status — newest activity, already produced by the lateral. Its id doubles as the max_id for the next page.

Returning whole Status is expensive. Unless you need to know everything about both root_status and last_status, I recommend returning a partial entity (e.g. only a title of the root).

>3.3 Optional: the same for group timelines
>Strictly a follow-up.

👍

This should be delayed until private groups are implemented.

Okay, this is above my pay grade (I'm an SQL noob), and I hope it's not annoying slop, let me know if you want me to run any further tests:

  Numbers, from a copy of a production instance: 59,720 conversations, 256,613 posts, post is 562 MB against 128 MB shared_buffers, so it doesn't fit in cache. Postgres 18.3. Public-catalog variant, 40 rows,
  warm, EXPLAIN (ANALYZE, BUFFERS).

  Ordering by a lateral max, no stored column:

  page 0        1621 ms   356,442 buffers
  offset 5000   1784 ms   349,889 buffers

  Stored conversation.last_activity_id with a btree index:

  page 0        0.388 ms  183 buffers
  offset 5000   1.183 ms  188 buffers

  Both return the same 40 conversations in the same order (EXCEPT between them is 0 rows).

  The flat cost across page depth in the first pair comes from where the cursor lands in the plan:

  ->  Subquery Scan on last_post
        Filter: (last_post.id < '019fa02a-...'::uuid)
        ->  Limit
              ->  Sort  (Sort Key: p.id DESC)
                    ->  Index Scan using post_conversation_id_btree on post p
                          Index Searches: 58835

  The cursor is a filter on the lateral's output rather than an index condition, so all 58,835 candidate conversations are probed on every page. With the stored column it's Index Cond: (last_activity_id < ...):
  one btree descent, and constant with depth.

  One structural difference between the two queries: get_direct_conversations filters conversation.audience IS NULL, which on this instance is 467 of 59,720 rows, applied before the sort. The public variant has
  no equivalent — its candidate set is every conversation with a public root, 58,804 here — so that predicate doesn't carry over to the sibling.

  A third shape worth recording: drive from the post index instead of from conversation, scanning post by id descending and deduping on conversation_id. 26.6 ms, 2,187 buffers, page 0. It's window-approximate —
  2,000 posts yielded 1,477 distinct conversations — and cursor-paged it can emit the same conversation on two consecutive pages when a thread has a newer post above the cursor.

  Separately: max(uuid) doesn't exist in 18.3, so the §3.1 backfill needs ORDER BY post.id DESC LIMIT 1. Proposal updated.

  Measurements are from a busy production box, so there's noise in them, though not at this magnitude.

The proposal.md has also been updated (again) after running the measurements. And more (hopefully refined enough) slop re: other points you brought up -- forgive any repeats Claude misinterpreted my monkeyslop reponse so I figured I should leave in the restatements in case I wasn't clear:

  Reposts: I'd like to keep these bumping, if it's not too costly. chan-fe treats a repost as a bump today and labels it as one on the card, which reads naturally on an imageboard — it's the useful version of the
  contentless "bump" posts you see on desuarchive, using an AP primitive that already exists instead of a junk comment.

  The mechanical cost is one extra resolution in the maintenance path: post has CHECK ((conversation_id IS NULL) != (repost_of_id IS NULL)), so a repost carries no conversation_id and the update has to follow
  repost_of_id → target.conversation_id. No disclosure concern, since a repost is a public object either way.

  If you'd rather they didn't bump, that's workable but it isn't something clients can restore — once the server owns the ordering, chan-fe can't re-sort a page it only partly holds — so it'd be a behavior change
  for us rather than a preference. Either way it needs documenting, since the distinction isn't visible from the response.

  Endpoint prefix. /api/v1/conversations is fine. The only wrinkle is that the bare path is Mastodon's DM conversation list, which Mitra already serves, so the public one likely wants to be a sibling path —
  /api/v1/conversations/public or similar. No deeper implication than the name being taken. Your call.

  Partial entities. Agreed on the principle, with one addition. last_status can shrink to id and created_at — id is the next page's cursor and nothing else is read.

  But a single newest post isn't quite what the card needs. chan-fe shows the last three activities on hover, so a reader can see why a thread is on the page — "3m: reply from @x", "1h: repost by @y" — which is
  what makes filtering a decision rather than a guess. That wants, per card:

  [{ id, created_at, type: reply|repost, account: { acct, display_name } }, ...]  -- newest 3

  If that's cheap enough, it replaces both last_status and the accounts participants array from §3.2 — participants is a different question from who bumped it recently, and the activity list is the one that gets
  rendered.

  For root_status, what the card reads is: id, created_at, url, title, content, spoiler_text, sensitive, media_attachments (the thumbnail is the card, so type, preview_url, url, blurhash), account (acct,
  display_name, avatar), the three counts, and favourited/reblogged for the card's own buttons. That's a Status minus emojis, poll, card, mentions, tags, application, language and in_reply_to_id. If you'd rather
  define one lean entity and have clients fetch full statuses on demand, that works too — one request per card on first render, still far better than the current up-to-200.

  Private comments bumping. Adding to what I said earlier: the DM query can bump on private posts because the viewer is a query parameter. A public catalog has no viewer to scope to, and much of its traffic is
  anonymous, so either everyone sees a bump with no visible new content, or the ordering becomes viewer-dependent — and a viewer-dependent order can't be a stored value or a cached page, which puts it back at the
  1.6 s measured in §7.

@harblinger

True, I'm going to re-skin AP 'repost' as 'bump' eventually. Good AP primitive to fix the no-signal bump posts common on image boards (picrel).

That's an interesting idea.

Maybe reposts should be tied to conversations, but let's keep this focused on comment-bumps for now.

Just implemented mitra subs recently too :)

🔥

Filtering private comments is to prevent users not in the convo being confused by what appears to be no activity bumped threads.

You can pass the current user to the query... But no need to make it more complicated than necessary.

I hope it's not annoying slop, let me know if you want me to run any further tests

Stored conversation.last_activity_id with a btree index

Premature optimization. It's never too late to add extra column.

Endpoint prefix. /api/v1/conversations is fine. The only wrinkle is that the bare path is Mastodon's DM conversation list, which Mitra already serves, so the public one likely wants to be a sibling path — /api/v1/conversations/public or similar.

Yes, we should use /api/v1/conversations/public

slop slop slop

Do you intend to send a patch?