Design the contract for a public API. How do you handle pagination, idempotency and versioning?

Resources come from workflows, not from tables

The first decision is what the nouns are, and the failure mode is exposing your schema. If your API has /user_profile_records and /user_auth_rows because those are two tables, you have published your normalisation choices as a permanent contract and every future refactor is now a client migration. Model instead on the things a client reasons about: a customer, an order, a payment. Where an entity is stored across several tables, that is your problem to hide.

The other half of the same decision is granularity. An API that requires eleven round trips to render one screen has pushed your join logic into the client and made latency the client's problem. So shape responses around what a caller does next: embed the small, always-needed related data, and expose the large or optional relations as their own addressable resources, with an explicit expansion parameter if callers need to opt into more in one request. Being able to say why you embedded one relation and linked another — response size, cache lifetime, whether it changes at a different rate — is the substance of resource modelling.

State transitions are the awkward case, and pragmatism beats purity. A cancellation is not naturally a field you write; it is an operation with preconditions, side effects and its own authorisation. Modelling it as an action sub-resource that a client posts to is clearer and safer than making clients patch a status field to a magic string, because the latter invites them to invent transitions you never intended to allow.

Errors are part of the contract as much as the success bodies. Use the status code for the class of failure and a structured, documented body for the detail, with a stable machine-readable error identifier that clients can branch on. The standard shape for that in HTTP is Problem Details, and adopting it costs nothing. What matters more is the discipline: an error identifier is as much a published API as a field name, so it cannot be renamed later.

Offset pagination is a correctness bug, not a performance one

Almost everyone knows LIMIT 20 OFFSET 10000 is slow, because the database must produce and discard ten thousand rows to return twenty, and the cost grows with the page number. That is the lesser problem and it is the one candidates volunteer. The problem that gets people is that offset pagination returns the wrong results on a dataset that changes while you page through it.

An offset addresses a position in a result set, not a position in the data. If three rows are inserted before your current position between page two and page three, everything shifts down by three and you receive three rows you already saw. If rows are deleted, the window shifts the other way and you silently never see three rows at all. Nothing errors and nothing looks wrong; a nightly export just quietly omits records. For a paginated feed sorted by recency — the single most common case, and the one where new rows arrive at the front constantly — this is not a rare race, it is the normal behaviour.

The fix is to paginate by a value rather than by a count. Sort on a stable, unique ordering and have each page ask for rows strictly after the last one it saw:

-- created_at alone is not unique, so ties would be skipped or repeated.
-- The (created_at, id) tuple gives a total order and matches the index.
SELECT id, created_at, body
FROM   posts
WHERE  (created_at, id) < ($last_created_at, $last_id)
ORDER  BY created_at DESC, id DESC
LIMIT  20;

Because the predicate is a range seek on an index rather than a count of discarded rows, page ten thousand costs what page one costs. Because the position is a data value rather than an offset, concurrent inserts and deletes do not shift the window, and the reader sees each row at most once.

Two details make it a real API rather than a query trick. The cursor must be opaque to the client — encode the tuple, sign or encrypt it, and document that its contents are not a contract — otherwise clients will parse it, and you can never change the sort. And you must accept the trade-off honestly: keyset pagination cannot jump to an arbitrary page number, and an exact total count is expensive and immediately stale. Offering "next" and "previous" instead of numbered pages is usually the right product answer; where a count is genuinely required, an approximate count clearly labelled as approximate is better than a scan on every request.

Making unsafe operations replayable

Any client that retries — and every client retries, whether you designed it or not — will eventually send the same request twice because the first response was lost rather than because the first request failed. HTTP already tells you which methods must tolerate that: GET, HEAD, PUT and DELETE are defined as idempotent, and POST is not. So a POST /payments needs help, and the standard help is a client-generated idempotency key sent as a header.

The server-side design is where the marks are. On receiving a key, atomically claim it — an insert on a unique index, not a read-then-write — and store the eventual response against it. A retry with the same key returns the stored response, including the original status code and the original resource identifier, rather than doing the work again. Three cases must be specified, because they are the ones that appear in production. If the same key arrives with a different request body, that is a client bug and must be rejected explicitly, which means you store a fingerprint of the request alongside the key; silently returning the first result would be worse, because the client believes its second, different payload was applied. If a second request arrives while the first is still in flight, you must either block until the first completes or return a conflict, never proceed in parallel. And keys expire, so document the retention window and accept that a retry arriving after it will be treated as new work.

Evolving without a version number

Versioning is the expensive answer, so the design goal is to need it rarely. Additive change is free if both sides behave: servers must not remove or rename fields, must not narrow a type or tighten validation, must not add a required request field, and must not change the meaning of an existing value; clients must ignore fields they do not recognise rather than failing on them. Under those rules you can add fields and endpoints indefinitely without a version bump.

The changes that look additive and are not deserve naming, because this is where teams break clients while believing they followed the rules. Adding a value to an enum is a breaking change for any client that validates against a closed set or switches exhaustively, which is most generated clients — so enums need a documented unknown-value behaviour from day one. Making an optional response field required is safe; making an optional request field required is not. Changing a default value alters behaviour for every caller who omitted the field. Tightening a validation rule rejects requests that previously succeeded. And loosening the ordering or the page size of a collection breaks callers who depended on the old behaviour whether or not you promised it.

When a change genuinely cannot be additive, prefer the smallest blast radius available. A new representation of one resource, or a new endpoint alongside the old one, is far cheaper than a new version of the entire API, because a whole-API version means you now operate two of everything and clients have no reason to migrate. If you do cut a version, it needs a retirement date, a machine-readable deprecation signal in responses, and metrics per version and per client so you know who is still on the old one. A version with no sunset plan is not a version, it is a second product you have accidentally agreed to maintain forever.

The contract you cannot change later is larger than your field names: it includes your error identifiers, your enum values, your defaults, your ordering, and anything a client can parse out of a cursor you told them was opaque.