typescript-postgres-pagination-postfilter-data-loss

Answer

Root cause: the slice query was scoped only by status (WHERE status=$1 ... LIMIT 100 OFFSET $n) and the enrolled-class filter ran in memory afterward. With 127 platform-wide quizzes and 6 enrolled, page 1 trimmed to 6 rows, every later offset window contained only non-enrolled quizzes (empty pages forever), and the unscoped COUNT reported 120/127 — the "capped at 6 of 127" symptom. The fix pushes both predicates into SQL and applies them to both the slice and the COUNT so pagination operates over the already-filtered set.

1. Repository interface — new scoped method

// src/modules/quiz/ports/quiz-repository.ts
export interface QuizFilter {
  status: QuizStatus;
  classIds: number[]; // enrolled classes; [] => matches nothing (never "all")
}

export interface QuizPage {
  rows: Quiz[];
  total: number; // scoped COUNT, not platform-wide
}

export interface QuizRepository {
  listByStatus(filter: { status: QuizStatus }, page: Page): Promise<QuizPage>; // admin/teacher path
  listByStatusAndClasses(filter: QuizFilter, page: Page): Promise<QuizPage>;   // NEW: student path
}

2. Postgres implementation — filter inside SQL for slice and COUNT

// src/modules/quiz/infra/postgres-quiz-repository.ts
export class PostgresQuizRepository implements QuizRepository {
  constructor(private readonly pool: Pool) {}

  async listByStatusAndClasses(filter: QuizFilter, page: Page): Promise<QuizPage> {
    const { status, classIds } = filter;
    const offset = page.page * page.size;

    const [rowsResult, countResult] = await Promise.all([
      // slice: WHERE status AND class_id = ANY($1)  — scoped BEFORE LIMIT/OFFSET
      this.pool.query<Quiz>(
        `SELECT * FROM quizzes
          WHERE status = $1 AND class_id = ANY($2)
          ORDER BY created_at DESC, id DESC          -- id tiebreak => stable pagination
          LIMIT $3 OFFSET $4`,
        [status, classIds, page.size, offset],
      ),
      // count: same WHERE clause — total must reflect the filtered set
      this.pool.query<{ count: string }>(
        `SELECT count(*)::text AS count
           FROM quizzes
          WHERE status = $1 AND class_id = ANY($2)`,
        [status, classIds],
      ),
    ]);

    return { rows: rowsResult.rows, total: Number(countResult.rows[0].count) };
  }
}

3. In-memory implementation — same semantics: filter first, then paginate

// src/modules/quiz/infra/memory-quiz-repository.ts
export class MemoryQuizRepository implements QuizRepository {
  constructor(private readonly quizzes: Quiz[]) {}

  async listByStatusAndClasses(filter: QuizFilter, page: Page): Promise<QuizPage> {
    const { status, classIds } = filter;
    // ORDER MATTERS: filter (status + class) BEFORE slice.
    // Slicing first and filtering after is exactly the data-loss bug.
    const scoped = this.quizzes
      .filter(q => q.status === status && classIds.includes(q.class_id))
      .sort((a, b) => b.created_at.localeCompare(a.created_at) || b.id - a.id);

    return {
      rows: scoped.slice(page.page * page.size, (page.page + 1) * page.size),
      total: scoped.length,
    };
  }
}

4. Service pass-through

// src/modules/quiz/quiz-service.ts
export class QuizService {
  constructor(private readonly repo: QuizRepository) {}

  // student + optional status => scoped query (the fix)
  listStudentQuizzes(studentId: number, status: QuizStatus, page: Page): Promise<QuizPage> {
    const classIds = await this.enrollmentRepo.classIdsFor(studentId);
    return this.repo.listByStatusAndClasses({ status, classIds }, page);
  }
}

5. Route branch — use the scoped method for student+status

// src/routes/quizzes.ts
router.get('/quizzes', async (req, res) => {
  const { role } = req.user;
  const { status, page } = parse(req.query);

  const result = role === 'student' && status
    ? await quizService.listStudentQuizzes(req.user.id, status, page) // scoped: status AND class
    : await quizService.listByStatus(status, page);                    // existing unscoped path

  res.json({ ...result, hasMore: (page.page + 1) * page.size < result.total });
});

Note on the old empty-page fallback: it only handled the all-or-nothing case (a fully empty first page). It never fixed the wrong total, the short page-1, or the empty tail pages — which is why the filter had to move into SQL rather than patching the fallback.