Large number of query in proposal PDF data-fetch

[mutambaraf]

What is the problem and why is it a problem

While debugging a PDF download trace (be8399523e097d96b4d48e68e39a9cd2, proposal 31753), found that the GET /download/pdf/proposal/:id endpoint in proposal-backend fires 378 individual DB queries to fetch the proposal/questionnaire data, before handing off to proposal-factory for rendering.(potential connection-pool risk under concurrent downloads)

Breakdown of query volume (single request):

• 93× first proposals
• 74× select questionaries
• 54× first generic_templates
• 38× raw (topics/topic_completenesses, templates_has_questions/questions/answers)
• 37× select templates
• 37× select generic_templates
• 19× select question_dependencies
• (remainder: faps, internal_reviews, visits, samples, instruments, technical_review, users, etc. — 1-3 each)

{"body":"Streaming a large object with a total size of 321253 \n{\"level\":\"info\",\"trace_id\":\"be8399523e097d96b4d48e68e39a9cd2\",\"span_id\":\"daaf31e8e5679356\",\"trace_flags\":\"01\",\"service_name\":\"proposal-factory\",\"timestamp\":\"2026-07-24T13:41:43.186+00:00\"}","traceid":"be8399523e097d96b4d48e68e39a9cd2","spanid":"daaf31e8e5679356","severity":"info","flags":1,"attributes":{"span_id":"daaf31e8e5679356","timestamp":"2026-07-24T13:41:43.186+00:00","trace_flags":"01","trace_id":"be8399523e097d96b4d48e68e39a9cd2"},"resources":{"container.id":"657f76b909186df3f0be9055df9aee0af9fe9bcdcd160d7dd6ae472d4b1dbaaa","deployment":"proposal-factory","pod_image":"ghcr.io/userofficeproject/user-office-factory","pod_image_tag":"v1.5.2","pod_start_time":"2026-07-24T11:14:56Z","pod_uid":"8e114950-799e-48cf-8302-63b2f9ef68aa"},"instrumentation_scope":{"name":"winston","version":"1.0.0"}}

Steps to reproduce (if it's a bug).

I could not add the full trace here but a new trace can be generated by downloading proposal with pk 31753 and then get the trace id.Grafana has a limit on the number of spans so must use tempo endpoint with either postman or curl an it requires credetials.
curl -s -G "http://:3200/api/traces/"
-H "Accept: application/json"
-o trace.json

Suggested next steps (for discussion,)

1. Check proposal-backend's actual Knex pool min/max config.
2. Reproduce with a concurrent-load test (e.g. 20 simultaneous PDF downloads) and check for pool wait times / connection errors.
3. If confirmed as a real bottleneck under load: consider batching the topic/question/answer fetch into fewer joined queries instead of the current per-item pattern.
4. Investigate unaccounted time in the factory (the factory-service-call itself takes 2.92s, but its child spans (2× browserless GET + 2× tiny files table lookups) only account for ~1.06s. The remaining ~1.86s is unaccounted for by any child span — that's the actual Puppeteer page navigation, JS execution, and PDF capture happening over the browserless WebSocket connection (not HTTP, so it isn't separately traced), plus HTML template rendering inside proposal-factory before/after those calls. That untraced ~1.86s is your biggest single chunk of the 3.32s total and the best target if you want to speed this up — it's essentially a black box right now from a tracing standpoint.)