Implementation of on-demand denormalized projections

[mnonnenmacher]

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.

[Etsija]

When you have time, an example of the usage would be very helpful. I myself have noticed that for example, querying curated purls is incredibly complicated for the current normalized db. That could be one candidate of such an example.

[mnonnenmacher]

For example, for the vulnerabilities endpoint such a table could look like this (simplified):

id	ort_run_id	external_id	rating	score	scoring_system	identifier	purl	advisor_name	resolutions
1	42	CVE-2021-44228	CRITICAL	10.0	CVSS3	Maven:org.apache.logging.log4j:log4j-core:2.14.1	pkg:maven/org.apache.logging.log4j/log4j-core@2.14.1	OSV	[{"message":"Accepted risk","reason":"CANT_FIX_VULNERABILITY"}]
2	42	CVE-2022-42003	HIGH	7.5	CVSS3	Maven:com.fasterxml.jackson.core:jackson-databind:2.13.3	pkg:maven/com.fasterxml.jackson.core/jackson-databind@2.13.3	OSV	[]

Plus required indexes base on expected queries. So fields like score which are required for filtering or sorting get their own column and nested data like resolutions can be put into JSONB columns.

It can still be a challenge to generate these tables is a performant way, but once created, queries could be very simple and fast. If model changes are required, the existing data could simply be thrown away and be regenerated on demand which would simplify migrations.

[mnonnenmacher]

The flow could work like this (leaving out the projection_registry table for simplicity):

graph TD
    %% Node Definitions
    Start((GET /vulnerabilities)) --> Check{Read Model<br/>Exists?}

    %% Path: Success
    Check -- Yes --> Success[/200 OK/]
    Success --> Data[Return Data]

    %% Path: Accepted / Async
    Check -- No --> Accepted[/202 Accepted/]
    Accepted --> Header[Set Retry-After Header]
    
    %% Internal Process
    subgraph Background_Process [Background Processing]
        Accepted -. trigger .-> Create[[Generate Read Model]]
        Create --> Store[(Save to DB)]
    end

    %% Final State
    Data --> End((Exit))
    Store --> End

    %% Class Definitions for Theme Compatibility
    classDef green fill:#1b5e20,stroke:#81c784,stroke-width:2px,color:#fff
    classDef amber fill:#7f5e00,stroke:#ffd54f,stroke-width:2px,color:#fff
    classDef blue fill:#01579b,stroke:#4fc3f7,stroke-width:2px,color:#fff
    classDef standard fill:#333,stroke:#666,stroke-width:1px,color:#fff

    %% Assigning Classes
    class Success green
    class Accepted,Header amber
    class Create,Store blue
    class Start,End,Data,Check standard

Loading