A catalogue can display the correct total while still skipping records between pages. An audit must compare the unique identifiers received by the client, not only the headline result count. BUILD A REPRODUCIBLE DATASET In a test environment, create 75 records and give several dozen the same creation date and price. Export the expected IDs through a separate query. Walk every page with a size of 25 and compare the two sets. This proposed test makes a fault visible that random data with different timestamps can conceal. Record duplicates and missing IDs separately instead of treating equal counts as proof of correctness. DEFINE A COMPLETE ORDER PostgreSQL does not promise your intended ordering without ORDER BY. Break ties with a unique criterion, for example ORDER BY created_at DESC, id DESC. Use the same ordering rule in the list, export and next-page operation. Audit price, name and popularity sorts separately; each needs a defined treatment for equal values. A stable secondary key is part of the API contract rather than a cosmetic display choice. ALLOW FOR DATA CHANGING DURING A VISIT Deterministic ordering does not freeze the catalogue. New rows between requests can shift OFFSET positions. For a feed, consider a cursor holding the created_at and id pair and continue after the last received pair. This is a design option, not a universal replacement. For an export representing one snapshot, define consistency boundaries and the snapshot mechanism separately. Do not advertise snapshot consistency merely because a cursor exists. CHECK THE CLIENT CONTRACT A cursor must correspond to the active filters and sorting. After a category change, the client starts a new traversal. Distinguish the end of a list from a server failure: an empty result and a failed request need different, understandable states. Cap page size, validate inputs and keep secrets out of cursor values. Keep a short test log containing the sort, filters, received IDs and any concurrent changes. ACCEPTANCE CRITERION With unchanged data, each expected ID must appear exactly once. Repeat while adding and removing rows and document the behaviour you intend to support. A clear notice that a catalogue changed is more useful than silently missing items. Domain catalogue: https://domainevery.com/en/domains Primary sources: https://www.postgresql.org/docs/current/queries-limit.html https://www.postgresql.org/docs/current/queries-order.html https://www.postgresql.org/docs/current/transaction-iso.html
