Topic

Challenge: broken pagination / soft-delete leak

Definition

A soft-delete leak occurs when a query intended to exclude logically deleted rows omits the deletion filter, causing removed records to still appear in lists, counts, or pagination totals.

In simpler words

Deleted tickets are not really gone from the database. If a query forgets to filter them out, they reappear in lists and mess up page counts.

Combines pagination correctness with a realistic soft-delete pitfall.

Open official example (opens in new tab)

After this you can

Explain why soft delete requires filtering in every read query, not only the list
Spot a pagination total that disagrees with the visible rows
Choose a fix that keeps the data query and the count query consistent

Why soft-delete leaks are easy to introduce

Definition

Soft delete marks a row as removed, for example with a deletion timestamp, without a physical delete, so every subsequent read query must explicitly exclude it, unlike a hard delete which removes the row from all future query results automatically.

In simpler words

Marking a row deleted does not stop it from showing up unless every query remembers to check for that mark.

A new query, index, or export path added later can easily forget the exclusion condition older queries already had. The leak often appears in only one endpoint, not all of them.

When the count query and the data query use different filters, the pagination total disagrees with what a user actually sees, producing an empty last page or missing rows.

Keep in mind

Every read query on a soft-deletable entity needs the same exclusion filter; factor it into one shared place.
A pagination total and the data query must share filters or pagination math breaks.
A leak that shows up only on later pages is a strong hint the count and data queries disagree.

Challenge lab

Bug report

Deleted tickets still show up on later pages of the tickets list, and the last page is sometimes empty.

What is broken

The paginated data query filters out soft-deleted tickets, but the separate count query used for the pagination total does not, so the total overcounts and the page math misaligns.

Broken snippet

async list(page: number, limit: number) {
  const [data] = await this.ticketsRepo.findAndCount({
    where: { deletedAt: null },
    skip: (page - 1) * limit,
    take: limit,
  });
  const total = await this.ticketsRepo.count(); // forgot the deletedAt filter
  return { data, meta: { page, limit, total } };
}

Pass criteria

Soft-deleted tickets never appear in list results
The pagination total matches the same filter used for the actual page of data
The pagination page count is consistent with what a user can actually page through

Checking your session…

Choose the correct fix

Apply the same deletedAt-is-null filter, or a shared query builder, to both the data query and the count query.Have the frontend filter out any ticket with a deletion timestamp set after receiving the response.Switch to permanently deleting rows on every delete action to avoid the filter entirely.Increase the default page size so deleted tickets are less noticeable among more results.

Test

Check your understanding

At least 10 questions — mix of concept, syntax, practical, and logic. Score ≥80% (enforced by the API) to save progress.

Checking your session…

15 questions · concept 5 · syntax 3 · practical 4 · logic 3

1. How should soft-delete pagination be fixed?

Concept

Apply the same deletedAt filter to page data and total count queriesSubtract the deleted count from the total after counting all rowsUse withDeleted:true on the data query to match the countCount only the current page’s rows instead of the whole set

2. Why do mismatched count/data break UX?

Syntax

The client caches the first page and never refetchesThe API returns rows in a different order each requestPage counts disagree with visible rows; last pages look empty/wrongThe total overflows the number type in the browser

3. Why not filter only in the browser?

Practical

The browser cannot apply an IsNull() filter to the dataClient filtering runs before the response is receivedDeleted rows still inflate payloads and totals from the APIDeleted rows must be decrypted on the client first

4. Why not inflate totals with soft-deleted rows?

Logic

Inflated totals slow the COUNT query significantlyThe extra count breaks the take/skip arithmetic on page onePostgres rejects a COUNT that includes NULL deletedAt rowsUsers see fewer rows than meta claims

5. What shared approach helps?

Concept

A database view that pre-joins the deleted rowsA separate count endpoint the client calls firstA trigger that decrements the total on each soft deleteOne query builder/helper applying soft-delete filters for list and count

6. Does increasing page size fix soft-delete math?

Practical

No — it only hides the inconsistencyYes — a single page avoids the trailing-page bugYes — a larger take recomputes the total correctlyYes — fewer pages means fewer count queries to drift

7. Deleted rows leak onto later pages and the last page is sometimes empty. What is the fix?

Syntaxadvanced

const [data] = await repo.findAndCount({
  where: { deletedAt: null },
  skip: (page - 1) * limit,
  take: limit,
});
const total = await repo.count(); // total

Add withDeleted:true to the findAndCount call to include all rowsCompute total as data.length so it matches the pageIncrease take so the deleted rows fall outside the windowApply the same deletedAt IS NULL filter to the count query so total matches the page data

8. How do soft deletes interact with page?

Logic

Hidden rows shift the offset so pages overlap slightlyHidden rows should not occupy slots in the page windowHidden rows should appear grayed out within the pageHidden rows only affect the final page’s count

9. Which test catches the bug?

Concept

Assert the deleted row returns a 404 on direct fetchAssert the total equals the row count before deletionAssert each page returns exactly limit rowsAssert list length and meta.total agree after soft deletes

10. Which approach is wrong?

Practical

Sharing one deletedAt filter across data and countCounting all rows including soft-deleted ones so totals look largerFiltering soft-deleted rows in the SQL queryReusing a query helper for both list and count

11. How does a shared where helper prevent pagination bugs?

Conceptintermediate

function activeWhere() {
  return { deletedAt: IsNull() };
}

Both the data and count queries use the same filter, so totals match rowsIt adds a database index on the deletedAt columnIt converts soft deletes into hard deletes on readIt memoizes the count so it never drifts

12. Why does the last page render empty?

Syntaxadvanced

// total=100 (includes deleted), 10 deleted, limit=10
// page 10 requested

The inflated total advertises more pages than the filtered data actually fillstake is applied before the deletedAt filter runsThe count query caches a value from before the deletesPostgres returns deleted rows on the last page only

13. For a normal list, is this default filter correct?

Practicalintermediate

repo.findAndCount({ take, skip }); // default excludes soft-deleted

No — findAndCount includes soft-deleted rows by defaultYes — default finds exclude soft-deleted rows; keep the count consistent with itNo — you must pass withDeleted:false explicitlyNo — the count ignores the soft-delete default

14. Why is bumping the page size not a real fix?

Logicadvanced

limit = 1000; // "fix" so deletes are less noticeable

It only hides the count/data mismatch instead of correcting itA limit of 1000 exceeds Postgres’s max row returnLarger pages make the COUNT query recompute correctlyIt forces a sequential scan that skips deleted rows

15. What class of bug does asserting data vs meta catch?

Conceptintermediate

expect(res.data.length).toBeLessThanOrEqual(res.meta.total);

Off-by-one errors in the take/skip arithmeticN+1 queries when loading each row’s relationsRace conditions between concurrent delete requestsCount/data mismatches from inconsistent soft-delete filters

Checking your session…