^{Originally posted by mnonnenmacher April 27, 2026}
Querying complex data across our current normalized schema often requires very complex queries, tinkering with the schema and has become a performance bottleneck and slows down the development of new features for the UI. This proposal introduces a Request-Triggered Projection pattern to build specialized read models on demand.

This concrete proposal addresses similar issues as #1915.

Use Cases

The architecture described below was chosen to address several conflicting requirements:

• Avoid "Ghost" Computation: Since many runs are triggered by CI and never inspected in the UI, projections are built on-demand rather than on-write to save significant CPU and storage resources.
• Prevent Redundant Processing: To allow horizontal scaling, the architecture uses atomic database locks to ensure that multiple simultaneous API requests for the same run do not trigger duplicate work.
• Consistency Across Nodes: By storing the state of building projections into a shared PostgreSQL table, all API instances stay synchronized on the status of a specific projection.
• UX for Long-Running Tasks: The architecture supports the Asynchronous Request-Reply pattern, allowing the UI to provide feedback to the user while complex data is being prepared.
• Simplified Maintenance: Breaking changes are handled by a simple version-incrementing logic that invalidates old projections, while a TTL (Time-To-Live) mechanism prevents the database from growing indefinitely with stale data.

1. Infrastructure Components

• Metadata Table (projection_registry):
  • id: Primary Key (e.g., run_id + projection_type)
  • status: PENDING, BUILDING, READY, ERROR
  • schema_version: Current version of the projection logic.
  • created_at, last_accessed_at: To facilitate TTL/Cleanup.
• Projection Tables: Dedicated "wide" tables (e.g., projected_vulnerabilities) using standard columns for filtered/sorted fields and a JSONB columns for additional payload.
• RabbitMQ: A projection-builder-queue to decouple the API from the heavy lifting.

2. The Request Flow (API)

1. Check Registry: Search for the projection by ID and schema_version.
2. State Logic:
   • If READY: Return data immediately (200 OK). Update last_accessed_at.
   • If BUILDING/PENDING: Return 202 Accepted with a Retry-After: 5 header.
   • If MISSING/STALE: Use INSERT ... ON CONFLICT DO NOTHING. If the row is successfully inserted/claimed, dispatch a build message to RabbitMQ. Return 202 Accepted.

3. The Builder Flow (Worker)

1. Set status to BUILDING.
2. Get the required data and INSERT results into the projection table.
3. On success: Update status to READY and set schema_version.
4. On failure: Update status to ERROR for visibility and dead-letter the message.

4. Lifecycle & Maintenance

• TTL Cleanup: A scheduled job will delete projection table rows and registry entries where last_accessed_at < NOW() - INTERVAL '30 days'.
• Breaking Changes: Incrementing the SCHEMA_VERSION constant in the code will automatically invalidate existing projections, triggering a rebuild on the next user request.
• Concurrency: Use PostgreSQL atomic upserts to prevent multiple API nodes from triggering duplicate RabbitMQ messages for the same run.

5. Edge Cases

• Build Failures: The UI should handle the ERROR state by allowing a "Retry" which clears the registry entry.
• Partial Data: Workers must use transactions to ensure a projection is only marked READY once all data is fully committed.