[Design Discussion] Listing Resources Across File and DB Stores

[rajithacharith]

Related Feature Issue

#963

Problem Summary

When Thunder runs in composite store mode, resources are stored across two data sources:

1. In-memory data store – populated by reading resources from files
2. Database-backed data store

When listing resources in this mode, the system must support sorting, pagination, and filtering across both data sources simultaneously. Because the data originates from separate stores, achieving correct ordering, consistent pagination, and clean API semantics becomes complex—especially without introducing confusing defaults, optional parameters, or multiple endpoints.

High-Level Approach

When Thunder runs in composite store mode, resources are listed from both in-memory (file-based) and database-backed stores. To support sorting, filtering, and pagination across both sources without complicating the API, a hybrid approach is used. A bounded number of records is fetched from each source, merged in memory, and then sorted and paginated before returning the response. A hard limit of 1,000 records will be applied in hybrid mode to keep the API simple and performant.

Architecture Overview

The listing flow queries both the in-memory store and the database store independently. Each store returns a limited result set when hybrid mode is active. These results are merged in the service layer, where sorting, filtering, and pagination are applied uniformly, producing a single ordered response to the client.

Security Considerations

This approach does not introduce new security risks, as it relies on existing access controls and authorization mechanisms for both data sources. File-based resources remain read-only, and all filtering and pagination logic is executed after authorization checks are enforced.

Impacted Areas

The resource listing API and service layer are directly impacted, particularly in composite store mode. UI components that consume the listing API may also be affected, especially in how pagination limits and informational messages (e.g., indicating additional results may exist) are displayed. Additionally, this approach will lose the previous performance improvement from querying with limit and offset, since the system now fetches all items up to the specified limit plus offset before merging and sorting in memory.

Alternatives Considered

Alternative 1: Separate Endpoints or Tabs
Provide distinct endpoints or UI tabs for file-based and DB-based resources. This would simplify sorting and pagination within each source but would require users to switch between views and complicate workflows when they need a unified list.

Alternative 2: Optional Query Parameter (source)
Introduce a query parameter to filter by source, allowing a single endpoint to serve both data stores. This approach was rejected because making the parameter optional can lead to inconsistent results and user confusion, while defaulting to one source could be misleading.

Alternative 3: Support local sorting
Always give priority to inmemory store. Avoid global sorting and give the items in inmemory store first and append the db store items.

Questions for Community Input

We would love your thoughts on this approach, especially on the hybrid pagination strategy and any potential edge cases we might have missed.