Bug Description

Two related issues with the new Analytics feature in v0.8.0 when using PostgreSQL and OpenAI-compatible providers (OpenRouter).

Bug 1: Inconsistent token counts in streaming responses

Severity: High — analytics token data is unreliable

Token usage is recorded for some messages but not others, seemingly at random. The behavior appears timing-dependent rather than model- or user-specific.

Root cause: Race condition in the frontend streaming handler. OpenRouter (and other OpenAI-compatible providers) send usage data in the last SSE chunk before [DONE]. However, chatCompletedHandler sometimes fires before the usage chunk is processed, resulting in the message being saved with 0 tokens.

Evidence:

• OpenRouter returns usage in every streaming response (verified via direct curl)
• The frontend openAIStreamToIterator correctly parses parsedData.usage from SSE chunks
• But token counts appear intermittently in the analytics dashboard — same model, same user, some messages have tokens, others don't
• Non-streaming calls (title generation, follow-up suggestions, tag generation) reliably record usage because the full response body includes it

Possibly related: #15850 ("missing tokens when streaming on fast inference providers")

Bug 2: Per-model analytics crashes on PostgreSQL

Severity: Medium — per-model analytics page returns HTTP 500

GET /api/v1/analytics/models/{model_id}/overview?days=30 returns 500.

Error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidColumnReference) 
for SELECT DISTINCT, ORDER BY expressions must appear in select list

[SQL: SELECT DISTINCT chat_message.chat_id
FROM chat_message
WHERE chat_message.model_id = %(model_id_1)s ORDER BY chat_message.created_at DESC
 LIMIT %(param_1)s OFFSET %(param_2)s]

Root cause: get_chat_ids_by_model_id() in chat_messages.py:298 uses SELECT DISTINCT chat_id ... ORDER BY created_at DESC. PostgreSQL requires that ORDER BY columns appear in the SELECT list when using DISTINCT. SQLite does not have this restriction.

Fix: Either add created_at to the SELECT list and use a subquery, or use GROUP BY instead of DISTINCT.

Environment

• Open WebUI version: 0.8.0
• Database: PostgreSQL 16.3 (Amazon RDS)
• Deployment: AWS ECS Fargate (2 tasks)
• API providers: OpenRouter, AWS Bedrock (via bedrock-access-gateway)
• "Usage" capability: Enabled on affected models

Steps to Reproduce

Bug 1 (inconsistent tokens):

1. Enable "Usage" capability on a model
2. Send multiple chat messages using the same model
3. Check Admin Panel → Analytics → Users or Tokens view
4. Observe that some messages record token counts, others show 0

Bug 2 (PostgreSQL crash):

1. Use PostgreSQL as the database backend
2. Go to Admin Panel → Analytics
3. Click on any model to view its detail/overview page
4. Observe HTTP 500 error

Expected Behavior

1. All streaming messages should reliably record token usage from the provider's response
2. Per-model analytics should work on PostgreSQL

Additional Context

The main analytics dashboard endpoints (/analytics/summary, /analytics/users, /analytics/models, /analytics/tokens, /analytics/daily) all return 200 OK. Only the per-model overview endpoint crashes.

OpenRouter has deprecated stream_options.include_usage — usage is now always included in every streaming response regardless of that flag. So the data is available; it's just not being reliably captured.