GET /api/v2/chats — no time-window filter, misleading meta.total

[filipexyz]

Summary

GET /api/v2/chats cannot be used to fetch chats by activity window, so consumers that need "chats with activity since X" (dashboards, exports, analytics, sync jobs) are forced to either:

1. Hit Postgres directly (bypassing the API entirely), or
2. Paginate through every chat for an instance and filter client-side (O(N) per query, blows up memory on busy instances).

This came up while debugging an empty live-chats panel in a downstream consumer (khal-platform). The consumer's PG-direct query is documented inline as "bypasses unfilterable /api/v2/chats HTTP path" — and the docstring spells out why: "Without this, 'Hoje' pulls every DM chat ever, blows up memory, and times out."

Hitting PG directly is a workaround, not a fix — it leaks the schema and access model out of the API boundary.

Reproduction

# Even with a time-shaped param the API silently ignores it
curl -H "Authorization: Bearer $OMNI_KEY" \
  "$OMNI/api/v2/chats?instanceId=$INSTANCE&chatType=dm&lastMessageAfter=2026-05-23T00:00:00Z&limit=1"

# → identical response to a call without lastMessageAfter; param dropped by zod schema

Schema at packages/api/src/routes/v2/chats.ts:191-215 (listQuerySchema) accepts:
instanceId, channel, chatType, excludeChatTypes, search, includeArchived, unreadOnly, pendingOnly, attentionOnly, label, includeHidden, sort, limit, cursor

— no lastMessageAfter/lastMessageBefore, no createdAfter/createdBefore.

Secondary issue: `meta.total` is not the real total

packages/api/src/services/chats.ts:249:

return {
  items: items.map(withIsGroup),
  hasMore,
  cursor: lastItem?.lastMessageAt?.toISOString(),
  total: items.length + (hasMore ? 1 : 0),   // ← only counts the current page
};

For an instance with 22,000+ chats and ?limit=1, the API returns meta.total: 2. This contract is misleading — clients reading total for sizing/pagination UI get nonsense.

Either:

• Compute the real count (SELECT count(*) ... WHERE <same conditions>) — one extra cheap query, opt-in via includeTotal=true to avoid the cost when not needed; or
• Drop the total field and rely on hasMore + cursor alone (cleaner, and matches what the value actually means today).

Proposed fix

Add server-side time filters to the chats list schema. Suggested shape (mirrors what GET /chats/:id/messages already does with before/after):

const listQuerySchema = z.object({
  // ...existing fields...
  lastMessageAfter: optionalDateParam('lastMessageAfter'),   // last_message_at >= X
  lastMessageBefore: optionalDateParam('lastMessageBefore'), // last_message_at <= X
  createdAfter: optionalDateParam('createdAfter'),           // created_at >= X
  createdBefore: optionalDateParam('createdBefore'),         // created_at <= X
});

And in ChatsService.applyStateFilters (or a sibling applyTimeFilters):

if (options.lastMessageAfter)  conditions.push(sql`${chats.lastMessageAt} >= ${options.lastMessageAfter}`);
if (options.lastMessageBefore) conditions.push(sql`${chats.lastMessageAt} <= ${options.lastMessageBefore}`);
if (options.createdAfter)      conditions.push(sql`${chats.createdAt}     >= ${options.createdAfter}`);
if (options.createdBefore)     conditions.push(sql`${chats.createdAt}     <= ${options.createdBefore}`);

Indexes — likely already exist for sort order, but worth confirming chats(instance_id, last_message_at DESC) covers the common (instanceId, lastMessageAfter) pattern with a clean range scan, not a sort+limit.

The optionalDateParam('after') schema helper is already imported and used by listMessagesQuerySchema at line 697 of the same file.

Impact

Today, every consumer that wants "chats with activity in window W":

• has to read Postgres directly, OR
• pulls all chats and filters client-side (slow, leaky, and not portable across deployments)

Closing this gap means downstream apps can drop their POSTGRES_URL_OMNI dependency and rely on the API alone.

Test plan

• Unit: listQuerySchema accepts the four new params, rejects bad ISO strings
• Integration: GET /api/v2/chats?instanceId=…&lastMessageAfter=… returns only chats with lastMessageAt >= cutoff
• Integration: combining lastMessageAfter + cursor (pagination through a filtered window) yields stable, non-overlapping pages
• Regression: no change to existing callers that don't pass the new params
• Decide on meta.total semantics (real count via opt-in, or remove the field) and update the docs

🤖 Generated with Claude Code