Chat system prompt gets lost: getFirstNMessages() has no ORDER BY

[Kuvwert]

Chat system prompt gets lost: getFirstNMessages() has no ORDER BY

Assistant 3.5.0 on Nextcloud 34.0.2.1 (AIO image), PostgreSQL, integration_openai 4.5.2 talking to a self-hosted OpenAI-compatible endpoint running Qwen. Context Agent isn't installed, so chat goes through the classic TextToTextChat path.

What I saw

I have a persona set up under Administration > Assistant > "Chat user instructions" (a name, a tone, a few hard rules). It worked for weeks. Then today new conversations started ignoring all of it: the model introduced itself as plain Qwen instead of my persona and followed none of the rules. I hadn't changed anything on the Nextcloud side in between.

I suspected the backend first, so I pointed integration_openai's service URL at a dummy HTTP endpoint that just prints the request body and sent "hello" in a fresh conversation. This is what Nextcloud sent:

"messages": [
  {"role": "system", "content": ""},
  {"role": "user", "content": "hello"}
]

Empty system prompt, straight out of Nextcloud. The setting itself is fine, occ config:app:get assistant chat_user_instructions returns my full text.

Where i think it goes wrong

ChattyLLMController::generateForSession() in 3.5.0 (same logic in ChatService::scheduleMessageGeneration() on main) does:

$systemPrompt = '';
$firstMessage = $this->messageMapper->getFirstNMessages($sessionId, 1);
if ($firstMessage->getRole() === 'system') {
    $systemPrompt = $firstMessage->getContent();
}

and MessageMapper::getFirstNMessages() is:

$qb->select(Message::$columns)
    ->from($this->getTableName())
    ->where($qb->expr()->eq('session_id', $qb->createPositionalParameter($sessionId, IQueryBuilder::PARAM_INT)))
    ->setMaxResults($n);

No orderBy. So "first message" means whatever row Postgres feels like returning first. For the conversation from the capture above:

SELECT id, role, left(content,50) FROM oc_assistant_chat_msgs WHERE session_id = <id> ORDER BY id;
 258 | system    | You are Herbert, my friendly assistant
 259 | human     | hello
 260 | assistant | captured

SELECT id, role, left(content,50) FROM oc_assistant_chat_msgs WHERE session_id = <id> LIMIT 1;
 260 | assistant | captured

The system message is there and correct, the app just doesn't get it. Row 260 isn't system, so the prompt stays empty.

Why it worked for weeks and then stopped: my guess is that on a fresh table a seq scan returns rows in insert order, so the system row (written at session creation) came first by luck. Every generation updates the assistant row, I had deleted a few conversations, and once autovacuum reclaims that space new rows land wherever there's a hole. From then on it's a coin flip per conversation. getMessages() in the same mapper does order by id, so this looks like an oversight rather than a design choice.

I can't give deterministic repro steps since it depends on physical row order, but the LIMIT 1 query above should show it on any instance where the assistant rows have been updated a few times.

Workaround

Adding ->orderBy('id', 'ASC') before ->setMaxResults($n) and restarting PHP fixed it right away, new conversations get the persona and ruleset again. That patch disappears with the next app update, which is why I'm filing this bug report .

Selecting the row with role = 'system' explicitly would be sturdier than relying on position. And separately (maybe intended, but it surprised me): the instructions are copied into the conversation when it's created, so editing the admin setting doesn't affect existing conversations.