- An order screen works perfectly when an organisation has fifty orders.
- Then the business grows. It adds properties, suppliers, buyers, invoices and years of purchasing history.
- That was the client problem in a multi-sided procurement marketplace built on Medusa.
- We built the operational read paths around bounded database work.
The client problem
An order screen works perfectly when an organisation has fifty orders.
Then the business grows. It adds properties, suppliers, buyers, invoices and years of purchasing history. The same screen becomes slow, memory-hungry and unpredictable—not because displaying fifteen rows is difficult, but because the API loads thousands before slicing the page in application code.
That was the client problem in a multi-sided procurement marketplace built on Medusa. Buyers needed order history across properties. Supervisors needed scoped property and member views. Finance needed accounting queues. Dashboards needed counts and trends across the same data.
We built the operational read paths around bounded database work. Page limits are clamped before queries run. Filtering and counting happen in PostgreSQL or Elasticsearch. The platform retrieves only the identifiers and rows needed for the current page, then enriches that bounded set in batches.
The result is not a claim about one benchmark number. It is a growth contract: the amount returned to the API is tied to the requested page, not to the size of the entire tenant.
The client problem: a small page can hide an unbounded query
Pagination in the interface does not guarantee pagination in the system.
A common implementation loads every order available to an organisation, maps sellers and properties in JavaScript, filters the result, sorts it and finally returns rows 31 to 45.
The browser sees fifteen orders. The API carried the whole history.
The same pattern appears in property directories, member lists and invoice queues. It may remain invisible during development because fixtures are small. In production, the work grows with the organisation even though the user's request did not.
We moved the page boundary to the source of the data. The database decides which records belong to the page. The API receives a bounded result and performs only the enrichment required for those records.
Clamp the request before it reaches SQL
A public limit parameter is not a safety boundary unless the server controls it.
The shared pagination helper converts limit and offset into finite integers, replaces invalid values with defaults, prevents negative offsets and caps the maximum page size.
That cap applies before values are forwarded to SQL or repository pagination. One request cannot ask an operational route to return the entire organisation by sending an extreme number.
Different screens can choose sensible defaults while sharing the same rule. Orders may default to fifteen, whereas a lookup surface may use twenty or fifty. The maximum remains explicit.
This is not only defensive programming. It creates a predictable contract for the storefront, database and enrichment layer: every page contains at most a known number of primary records.
Put organisation and property scope inside the query
Pagination must happen after access scope is applied.
If the database selects fifteen arbitrary orders and the API removes those outside the user's properties, the page may contain only three rows even though more authorised orders exist later. Counts become misleading as well.
The buyer order route first resolves scope from authenticated organisation and property relationships. A master can query the organisation. Other roles receive the properties available to them. An optional order-property filter is verified before use.
The category query joins the appropriate organisation-order or property-order relationship, applies that scope in SQL and only then orders, limits and offsets the result.
The page therefore describes the authorised dataset. Access control and pagination are not consecutive filters with incompatible populations; they are part of the same database selection.
Fetch the page keys before hydrating rich commerce records
An order list needs more than an identifier. It may display totals, item summaries, fulfilment state, supplier, pickup status, returns and delivery claims.
Trying to express every relationship in one giant paginated graph can produce duplicate rows or make the count depend on joins to child collections.
For category-filtered order pages, the platform first asks SQL for the ordered page of order identifiers and runs a separate count over the same scope and predicates. It then gives those identifiers to Medusa's graph query to retrieve the rich commerce shape.
The page order is restored after hydration, so the list follows the deterministic SQL selection rather than the incidental order of a second query.
This two-step read model gives each layer the job it handles well: SQL selects and counts the page; Medusa reconstructs the commerce records for that bounded key set.
Enrich the current page in batches
Rich list rows often create an N+1 problem: one query for the orders, then one supplier query, return query and fulfilment query for every row.
We built page-level enrichment instead. The route gathers the order identifiers from the current page and loads related data in bounded collections.
Supplier identities are fetched for the page. Returns are queried for all page orders. Fulfilment statuses are loaded as a map. Pickup shipping options are resolved in grouped graph requests. Delivery-claim summaries are requested for the complete page set. Order-money summaries are applied in one bounded pass.
The API then attaches those results to each order in memory.
The important distinction is scale: enrichment work follows the page size, not the complete order history. Adding another year of historical orders does not add another year of seller lookups to the current fifteen-row response.
Keep count and page predicates identical
A paginated interface needs both the current records and the total number of matches.
If the count query uses different status or access rules, the user sees impossible navigation: a tab claims one hundred results while the pages contain eighty-five.
The order routes centralise category predicates for open, pending and closed purchasing states. The page query and count query consume the same scope, search and category conditions.
Search by order number also happens in SQL before pagination. The platform does not retrieve a page and then ask whether its fifteen rows contain the requested number.
This produces a coherent contract: count describes the complete filtered population, while limit and offset describe the returned window within it.
Search properties where the properties live
Property directories face the same problem.
The organisation property route applies case-insensitive name search in PostgreSQL, alongside organisation scope and supervisor-managed-property scope. It selects only the fields needed by the list, orders by property name and applies limit and offset.
A companion count query uses the same condition without the page window.
Without search text, repository pagination or a scoped SQL path still bounds the result. Supervisors receive properties connected through their manage-level relationship rather than the complete organisation followed by a JavaScript filter.
For the interface, property search behaves like a real dataset query. Page two represents the next properties matching the term, not the next arbitrary page filtered after retrieval.
Aggregate member relationships inside the page query
An organisation member row can include identity, role, invitation state and several assigned properties.
Loading members first and then querying property links per person would multiply requests. The member route joins organisation membership, property access and authentication identity, aggregates distinct property identifiers and calculates sign-in evidence inside the grouped SQL query.
Limit and offset apply to the grouped member rows, and a separate organisation-scoped count supplies the total.
The API converts each bounded row into the storefront shape, including active or pending invitation state.
This demonstrates a useful read-model principle: a list endpoint should return the summary the screen needs, not force the frontend to reconstruct it through one request per cell.
Compute dashboard totals in the database
A dashboard often becomes the worst hidden full-table endpoint.
To show six counters and a chart, an implementation loads every order and reduces the data in JavaScript. Memory consumption grows with history, and different pages may calculate statuses differently.
The marketplace dashboard uses SQL aggregation. One query calculates operational order counts and total paid values under the authorised scope. Another calculates shipping totals. Time-series spend is grouped by day, and category spend is grouped and limited to the leading categories.
The query resolves the latest relevant order-item and summary versions before aggregation. The same status semantics used by order screens are represented in the dashboard predicates.
Only compact totals and chart points enter the API process. The database does the work it was designed to do: filter, join, group and sum close to the data.
Use purpose-built read models for different surfaces
Not every operational surface should query the transactional graph in the same way.
Orders, members and properties benefit from scoped SQL and Medusa graph hydration. Catalogue discovery benefits from Elasticsearch documents designed for filtering, facets and ranking. Accounting uses dedicated queries and export projections shaped around reviewed invoices and allocations.
These are not competing sources of business truth. They are read-optimised representations of the underlying commerce state.
The key is to define how each projection is built and which filters remain authoritative. A search index may serve product pages, but inventory and access rules still shape its documents. An accounting projection may flatten an order, but validation and invoice state decide whether the order enters it.
Choosing a read model per screen prevents one universal query from becoming slow and incomprehensible.
Select the fields the list actually needs
Bounded row counts can still produce heavy responses if every row expands the complete entity graph.
The order list defines an explicit field set for status, totals, item summaries, fulfilment data and payment state. It does not ask for every order relation merely because those relations exist.
Property search similarly selects the directory fields required by the page. Dashboard queries return aggregates rather than entities.
This field discipline improves more than payload size. It gives the list a stable contract. Adding a large relation to the domain model does not automatically make every operational page fetch it.
Detail pages can remain rich. List pages should be deliberately sufficient.
Preserve deterministic ordering
Pagination requires stable order.
Orders use creation time descending for recency. Properties use name order for directory browsing. Member rows use membership time with a defined null placement. Database-level ordering happens before limit and offset.
Where a page of identifiers is hydrated through another query, the route maps the results back to the selected identifier order.
This avoids records jumping between positions because a graph layer returned them differently. It also makes page behaviour testable: the requested window is defined by scope, predicates and a known order.
Stable ordering is a small requirement that separates a reliable operational table from an apparently random feed.
Test query shape as a product contract
The relevant tests do not need to prove a particular latency to protect the architecture.
They can verify that page limits are bounded, SQL contains access predicates, count queries use the correct status conditions, selected fields remain intentional and enrichment accepts a page of identifiers rather than the full dataset.
Dashboard tests verify scoped totals and aggregation behaviour. Accounting tests protect page and count semantics. Distance-search tests use the same principle by filtering the complete candidate set before catalogue pagination.
These contracts prevent an innocent refactor from moving filtering back into JavaScript or adding one lookup per row.
Performance here is partly a property of query shape. That shape can be reviewed and tested before a large tenant exposes the mistake.
Why Medusa was the right foundation
Medusa supplied the commerce entities and graph APIs needed to hydrate rich results. PostgreSQL supplied scoped selection, counting and aggregation. Elasticsearch supplied catalogue-oriented projections.
We combined them instead of insisting that one abstraction solve every read problem.
The platform remained extensible enough to add organisation, property and seller scope directly to operational queries while still returning familiar Medusa order and product shapes.
That is the architectural value: use the commerce engine for domain truth, then design read paths around the actual questions each user interface asks.
A practical operational-read checklist
Before shipping a large operational list, ask:
- Is limit clamped before it reaches the data layer?
- Is offset prevented from becoming negative or invalid?
- Does access scope run before pagination?
- Do search and status filters run in the database?
- Does the count query use the same predicates as the page query?
- Is ordering deterministic before the page window is selected?
- Can the route fetch page identifiers before hydrating rich records?
- Are related sellers, returns and statuses loaded for the page in batches?
- Does enrichment work grow with page size rather than tenant size?
- Are grouped member or property summaries built in SQL where useful?
- Are dashboard totals aggregated without materialising every entity?
- Does each surface use a read model appropriate to its question?
- Are list fields explicit and smaller than detail fields?
- Do tests protect access predicates, counts and query shape?
These decisions make an operational screen ready to grow before growth becomes an incident.
The broader lesson
The client did not need faster JavaScript slicing.
They needed every screen to ask the database a bounded business question.
We moved organisation and property scope, search, status filtering, counting and aggregation to the data layer. Rich Medusa records are hydrated only for selected page identifiers. Related information is loaded in batches, and dashboards receive compact aggregates instead of the full order history.
For users, tables remain navigable as the organisation grows. For operators, counts and pages describe the same dataset. For the platform, API work is bounded by the question being answered rather than the amount of business accumulated over time.