u/Ambitious-Hornet-841

Been building a PostgreSQL + MongoDB analytics agent and kept hitting one weird failure: the agent would answer a question correctly, then answer the same question worded slightly differently and get it wrong. Same LLM, same trial, same DBs.

Took us an embarrassing amount of time to trace because the traces looked clean the LLM was being called, just not for the part we thought. It was only used to pick which database to hit. The actual SQL came from a template bank keyed on exact question strings, and anything the bank didn't match fell through to a heuristic that emitted things like SELECT * FROM orders LIMIT 100.

Two things we're mid changing:

Templates as few-shot examples in the prompt, not a dispatch table. The LLM sees 3 5 curated (question, SQL) pairs and generates SQL for the live question. Embedding similarity to pick the nearest few shot beats regex/string matching.

Routing informed by schema, not just keywords. Our keyword map sent the token "ticket" to MongoDB while the authoritative table was support_tickets in Postgres. Column name introspection + a short intent classifier would have caught that.

If you've shipped this architecture how did you draw the boundary between "router LLM" and "writer LLM"? Keep templates at all, or skip and rely on schema + retrieved examples?

Cross-database join keys are a silent failure mode in multi DB agents

Post:

We hit a recurring issue while building a multi database data agent for PostgreSQL + MongoDB: joins could return zero rows with no error even when the logic looked right.

The issue was format mismatch, not missing data.

Example:

PG subscriber\_id: 1234567

Mongo subscriber\_id: "CUST-1234567"

What helped:

explicit key normalization

mapping rules per entity type

logging failures by category instead of treating every miss as “query failed”

separating routing, join resolution, and execution into different steps

This changed our design: we stopped treating the problem as “better SQL” and started treating it as data contract mismatch across systems.

Question:

For people working with mixed stores, do you solve this in ETL, in the query layer, or in application logic? Where have you found the least painful boundary?