API design and pagination

Key points

• Model resources, not actions: GET /orders/42, POST /orders. The verb belongs in the method, not in the path.
• Status codes carry meaning: 201 + Location on create, 202 for async, 409 on conflict.
• Never break a field. Add rather than rename, make new fields optional, and version at the edge when you truly must break.
• Offset paging re-reads what it skips and shifts pages as rows arrive: OFFSET 100000 reads 100,020 rows to return 20.
• Cursor paging returns the last row key. The next page is one index seek, whatever the page number, and pages never overlap.
• Pick the protocol per caller: REST for the public API, gRPC between your own services, GraphQL when clients need different shapes.

Example

import java.util.ArrayList;
import java.util.List;

public class Main {

    record Order(long id, String customer) {}

    // Rows in id order: exactly what an index on (id) already gives you.
    static final List<Order> ROWS = new ArrayList<>();
    static {
        for (long i = 1; i <= 12; i++) ROWS.add(new Order(i, "cust-" + (i % 4)));
    }

    // Cursor paging: "the next N rows after this key" - one seek, nothing skipped.
    static List<Order> page(long afterId, int size) {
        List<Order> out = new ArrayList<>();
        for (Order o : ROWS) {
            if (o.id() > afterId && out.size() < size) out.add(o);
        }
        return out;
    }

    public static void main(String[] args) {
        long cursor = 0;
        for (int p = 1; p <= 3; p++) {
            List<Order> rows = page(cursor, 5);
            if (rows.isEmpty()) break;
            System.out.println("page " + p + "  after=" + cursor + "  -> "
                    + rows.stream().map(Order::id).toList());
            cursor = rows.get(rows.size() - 1).id();          // the next cursor
        }

        // A brand new row lands at the top of the table. Offset paging would now
        // repeat a row on the next page; a cursor cannot, because it names a key.
        ROWS.add(0, new Order(0, "cust-new"));
        System.out.println("after an insert, after=5 -> "
                + page(5, 5).stream().map(Order::id).toList());
    }
}

Resources plus honest status codes, and cursors instead of OFFSET the moment the table is big.