# Keep a changing collection intact across pages

**40-minute integration lab · Python 3.11+ · Standard library · No account or network**

An enterprise importer can receive successful responses and still miss a customer case. An agent can summarize only the pages it saw and report an apparently complete list. Before promising completeness, the engineer must define which records belong to the task and what happens while that collection changes.

This original exercise runs real SQLite queries and commits over fictional IDs in a temporary database. It does not call an API, authenticate a user, execute an agent or benchmark a production service. The diagram is an explanation; the downloaded program supplies the executed evidence.

**You will keep:** first predictions, actual ordered IDs, two independent population comparisons, one changed-page-size experiment and a receiving-team contract. You need to recognize a SELECT query and a sorted list. If those are new, start with [Data 101](/training/guides/data).

## 1. Define which collection the receiver needs

**Pagination** divides a result into smaller portions. A **page size** limits each portion. A **continuation** tells the producer where to resume. These mechanics do not by themselves say whether later portions describe the same point in time.

For a live work queue, a changing view might be acceptable if operators understand its behavior. For a reconciliation export, the receiver may need a defined population at an agreed cutoff. Do not silently substitute one contract for the other. Record the filter, ordering, unique key, cutoff meaning, mutation policy and receiving purpose before counting rows.

The lab begins with four fictional records: **10, 20, 30, 40**. IDs are unique, immutable integers. Page size defaults to two. A separate writer changes the table immediately after the first page. Each case starts from a fresh database; evidence never carries across cases.

## 2. Follow a page through a source change

An **offset** counts positions to skip. In `ORDER BY id LIMIT 2 OFFSET 2`, the skipped positions belong to the query's visible collection. An insertion or deletion before that position can move the boundary.

**Keyset pagination** resumes after the last ordered key, such as `WHERE id > 20 ORDER BY id LIMIT 2`. It avoids shifting an offset, but later inserts behind the cursor may never be seen. Real nonunique sort fields need a unique tie-breaker and a corresponding continuation predicate. Changing sort values requires its own policy; this lab fixes immutable IDs.

A **high-water bound** retains an upper limit such as the initial largest ID. It can bound this traversal, but it does not preserve deleted rows, changed values or inserts below that limit. A **snapshot** retains a particular database view under an explicit transaction/isolation contract. Here the snapshot case keeps one SQLite reader transaction open in write-ahead logging (WAL) mode while a second connection commits changes.

## 3. Predict and run five independent cases

Download [Pagination consistency · Python](/templates/fde-pagination-consistency.py). Save it in a new local practice folder and run:

```bash
python3 fde-pagination-consistency.py all
```

If your `python3` is older than 3.11, use an approved Python 3.11+ interpreter. No packages are installed. The program creates and removes its own temporary database; it never accepts a production database path. WAL must be available in that temporary directory.

Before running, write the IDs you expect to receive. Then compare your prediction with `received_ids`, keeping the duplicate occurrence rather than cleaning it away.

| Case | Writer after the first page | Default received sequence | What to inspect |
| --- | --- | --- | --- |
| `offset-insert` | Insert 5 | 10, 20, 20, 30, 40 | Duplicate 20; final-current 5 missing |
| `offset-delete` | Delete 10 | 10, 20, 40 | Still-present 30 missing |
| `keyset-insert` | Insert 15 and 50 | 10, 20, 30, 40, 50 | New 50 read; new 15 missed |
| `high-water` | Insert 15 and 50 | 10, 20, 30, 40 | Initial IDs match here; current 15 is still missed |
| `snapshot` | Insert 15 and 50; delete 30 | 10, 20, 30, 40 | Initial snapshot retains 30; fresh view differs |

Run one case for a smaller report:

```bash
python3 fde-pagination-consistency.py snapshot
```

Inspect `pages`: each entry keeps the actual SQL, parameters, returned IDs and reader transaction state. `mutation` records the writer statements and committed IDs. The loop ends with an empty SQL page in this small bounded fixture. That is this program's termination rule, not a universal API rule: a service may return a short or empty page with a continuation token. Follow the receiving API's documented completion signal.

## 4. Compare against two different populations

`initial_ids` is the independently captured starting set. `final_current_ids` is the committed set after the writer changes it. Neither reference is rewritten to match the received result.

- `duplicate_ids` finds repeated IDs in the received sequence.
- `missing_from_initial_ids` finds starting records not received.
- `newly_read_ids` finds received IDs outside the starting set.
- `final_current_ids_missed` finds final-current records absent from the received set.
- `fresh_reader_ids` shows what that reader sees after any retained transaction ends.

In `offset-delete`, ID 30 is missing from both reference populations. ID 10 was actually read before it was deleted. In `snapshot`, ID 30 is legitimately present in the agreed initial view, while final-current IDs 15 and 50 are absent. A current-set mismatch alone is therefore not a defect in an initial-snapshot contract. State the requested population before choosing a judgment.

The high-water case matches the initial IDs under these particular inserts. That observation does not prove a general snapshot guarantee. Discuss a deletion of an unread record or a value update: an upper bound contains no historical copy to recover them.

## 5. Change one boundary and explain the result

Keep your original output. Predict the offset deletion with a page size of one, then execute:

```bash
python3 fde-pagination-consistency.py offset-delete --page-size 1
python3 fde-pagination-consistency.py snapshot --page-size 1
```

The mutation still occurs after page one. The offset case now reads 10, 30, 40 and misses 20; the snapshot still retains 10, 20, 30, 40. Explain why the missed ID moved: changing page size also changed how much was read before the same mutation. Do not call it a performance comparison; no load or latency is measured.

An optional next experiment uses `--page-size 4`. Page one already contains all initial IDs. Predict which late inserts can be returned by each continuation. Preserve those outputs separately; a passing large-page example does not repair the smaller-page failure.

## 6. Give the receiving team a reproducible contract

Work in pairs if a reviewer is available. Each learner writes a first prediction before seeing the other account. The builder runs one case and hands over the exact script, command and output. The receiver chooses a different case, finds its query and population difference, then reproduces it without the builder taking over. Record any assistance. A solo run remains a solo run.

```text
Receiving purpose and required population:
Script revision or exact retained copy:
Original prediction / actual command / observed IDs:
Initial set / final-current set / which reference applies:
Duplicate or missing record and the SQL boundary causing it:
Changed page size / preserved original / revised explanation:
Receiving action actually attempted / help received:
Unresolved contract question / accountable owner / next evidence:
```

The **API owner** defines filter binding, ordering, continuation expiry and retry behavior. The **data owner** defines the cutoff and which records belong in the export. The **receiving engineer** verifies the real consumer's traversal and error handling. The **pod lead** coordinates the unresolved decision and next check; a local lab result grants none of these production permissions.

A production page token should be treated as opaque by the client; it is not permission to access records. Enforce current authorization independently on every request. If access changes during a long export, the security and data owners must resolve what may be delivered. Do not expose a stale authorized snapshot merely because pagination is consistent.

Holding a database transaction across disconnected HTTP requests is not a deployment design supplied by this lab. A real service may need a bounded export job, materialized result, documented token lifetime or another supported snapshot mechanism. Establish resource limits and cancellation, and test the chosen database's actual isolation. Continue in [Enterprise integration](/training/guides/integration-engineering) to combine this with consumer compatibility and event reconciliation.

## 7. Explain it without the diagram

**Explain:** Why can a successful SELECT produce a wrong export? Name the population, page boundary and concurrent change, then show the IDs.

**Debug:** A receiver removed duplicates and still missed a case. Which evidence would expose the loss? Keep the original ordered sequence and compare against an independently defined set.

**Design:** An API offers an opaque token and an estimated total. What is still unknown? Define consistency, completion, scope binding, expiry, retry, deleted records and current access before promising a complete export.

Keep the initial answer, revised explanation and help received. This exercise demonstrates local observations, not production readiness or client-placement qualification.

## Sources

Primary references checked 27 October 2026. The exercise and fictional records are original LockedIn Labs material.

- [PostgreSQL: LIMIT, OFFSET and deterministic ordering](https://www.postgresql.org/docs/current/queries-limit.html)
- [SQLite: isolation and WAL snapshots](https://www.sqlite.org/isolation.html)
- [Google AIP-158: pagination tokens and completion](https://google.aip.dev/158)