How do you design a SQL query for an API endpoint that returns a list with nested summary data?

In production the endpoint contract and index design evolve together. Tenant or ownership predicates are applied inside the query, not after materialization, so forbidden rows never enter an application-side filter. The cursor encodes the actual deterministic order and remains opaque to clients. I compare one query, split queries, and a dedicated read model using command count, rows read, bytes transferred, execution plans, consistency needs, and change frequency. Two queries can observe slightly different moments unless the transaction or data model defines a snapshot, while one giant join can create cartesian expansion and repeated values; that trade-off is explicit. I project nullability and enum values deliberately, bound page and nested collection sizes, propagate cancellation, and avoid evaluating user-defined filter expressions as arbitrary SQL. Load tests use realistic tenant skew and child counts, and telemetry records query shape, duration, command count, rows, and result size without sensitive parameters. When a frequently requested response needs expensive joins and aggregates, a maintained read model can trade write complexity and freshness lag for predictable reads. I revisit the query when the public contract changes instead of adding another Include and hoping.