Pagination and Query Abuse

// The cursor encodes the ORDERING VALUES of the last row, not an offset.
// A tiebreaker column is required or rows with equal sort values are skipped
// or duplicated.
import { z } from 'zod';

const listQuery = z.object({
  limit: z.coerce.number().int().min(1).max(100).default(20),
  cursor: z.string().max(200).optional(),
  sort: z.enum(['createdAt', 'total']).default('createdAt'),   // allowlist
  order: z.enum(['asc', 'desc']).default('desc'),
}).strict();

const SORT_COLUMNS = {
  createdAt: 'invoices.created_at',
  total: 'invoices.total_cents',
};

const encodeCursor = (row, sortKey) =>
  Buffer.from(JSON.stringify({ v: row[sortKey], id: row.id })).toString('base64url');

function decodeCursor(cursor) {
  try {
    const parsed = JSON.parse(Buffer.from(cursor, 'base64url').toString('utf8'));
    return z.object({ v: z.union([z.string(), z.number()]), id: z.string() })
            .parse(parsed);
  } catch {
    throw new BadRequestError('invalid_cursor');
  }
}

app.get('/api/invoices', auth, validate({ query: listQuery }), async (req, res) => {
  const { limit, cursor, sort, order } = req.query;
  const column = SORT_COLUMNS[sort];
  const comparison = order === 'desc' ? '<' : '>';

  let q = db('invoices')
    .where({ user_id: req.user.id })                 // authorization first
    .orderBy(column, order)
    .orderBy('invoices.id', order)                   // ← tiebreaker, essential
    .limit(limit + 1);                               // one extra: is there more?

  if (cursor) {
    const { v, id } = decodeCursor(cursor);
    // Row-value comparison: (created_at, id) < (?, ?)
    q = q.whereRaw(`(${column}, invoices.id) ${comparison} (?, ?)`, [v, id]);
  }

  const rows = await q;
  const hasMore = rows.length > limit;
  const page = hasMore ? rows.slice(0, limit) : rows;

  res.json({
    data: page.map(InvoiceSerializer.public),
    pageInfo: {
      hasMore,
      nextCursor: hasMore ? encodeCursor(page.at(-1), sort) : null,
      // No total. If a total is genuinely needed, see the next example.
    },
  });
});

// Required index — without it this is no faster than offset pagination:
//   CREATE INDEX ON invoices (user_id, created_at DESC, id DESC);

// Note what the cursor does NOT contain: an offset, a user id, or anything an
// attacker could tamper with to change scope. It is only a position, and the
// authorization filter is applied independently on every request.

// COUNT(*) over a large filtered set is frequently the most expensive part of
// a listing endpoint, and it is usually displayed as "1,247,392 results" and
// then ignored.

// ── Option 1: do not return one ──────────────────────────────────────
// "hasMore" plus a next cursor covers most interfaces. Cheapest and best.

// ── Option 2: a capped count ─────────────────────────────────────────
async function cappedCount(query, cap = 1000) {
  // Counting a bounded subquery stops after `cap` rows.
  const rows = await db.raw(
    'SELECT COUNT(*) AS c FROM (?) AS capped', [query.clone().limit(cap)]);
  const c = Number(rows.rows[0].c);
  return { count: c, exact: c < cap, display: c < cap ? String(c) : `${cap}+` };
}

// ── Option 3: the planner's estimate, for a full-table count ─────────
async function estimatedCount(table) {
  const { rows } = await db.raw(`
    SELECT reltuples::bigint AS estimate
      FROM pg_class WHERE relname = ?`, [table]);
  return Number(rows[0].estimate);      // approximate, and effectively free
}

// ── Option 4: EXPLAIN for a filtered estimate ────────────────────────
async function estimateFiltered(query) {
  const { rows } = await db.raw(`EXPLAIN (FORMAT JSON) ${query.toString()}`);
  return rows[0]['QUERY PLAN'][0].Plan['Plan Rows'];
}

// ── Option 5: maintain a counter, if an exact total is required ──────
// A trigger or an application-level counter keeps a per-user total in a
// separate table. Exact, O(1) to read, and a write cost on every insert.

// Usage — say which kind of number you are returning
res.json({
  data: page,
  pageInfo: { hasMore, nextCursor },
  total: { value: 1000, exact: false, display: '1000+' },
});

// The honesty matters: a UI that renders "1000+" is fine, and one that renders
// a wrong exact number is a bug report.

// If a client can express a query, it can express an expensive one. Constrain
// the vocabulary rather than trying to detect misuse.

// 1. Filters: an allowlist, each mapped to an INDEXED column
const FILTERS = {
  status:     { column: 'status',        indexed: true,  op: '=' },
  customerId: { column: 'customer_id',   indexed: true,  op: '=' },
  createdAfter:{ column: 'created_at',   indexed: true,  op: '>=' },
  minTotal:   { column: 'total_cents',   indexed: true,  op: '>=' },
  // Deliberately absent: `notes` (unindexed text), `metadata` (jsonb scan).
  // If a customer needs them, add an index first — not a filter first.
};

// 2. Search: bounded, and never a leading wildcard
function searchClause(q, term) {
  const clean = String(term).trim().slice(0, 100);
  if (clean.length < 3) throw new BadRequestError('search_term_too_short');

  // ❌ LIKE '%term%' cannot use a standard index → full scan
  // ✅ prefix match uses an index, or full-text search with a GIN index
  return q.whereRaw("search_vector @@ plainto_tsquery('english', ?)", [clean]);
}

// 3. Expensive fields cost extra against the request budget
const FIELD_COST = {
  id: 0, reference: 0, total: 0, status: 0,
  lineItems: 5,                 // a join
  paymentHistory: 10,           // another join
  computedRiskScore: 50,        // a calculation per row
};

function budgetFields(requested, pageSize, budget = 1000) {
  const perRow = requested.reduce((sum, f) => sum + (FIELD_COST[f] ?? 1), 0);
  const total = perRow * pageSize;
  if (total > budget) {
    throw new BadRequestError(
      `Requested fields are too expensive for a page of ${pageSize}. ` +
      `Reduce the page size or the number of expanded fields.`);
  }
  return requested;
}

// 4. A statement timeout as the backstop for anything that slips through
await db.raw('SET LOCAL statement_timeout = 5000');

// 5. And log slow queries WITH the parameters that caused them
db.on('query-response', (_, obj) => {
  const ms = Date.now() - obj.__startedAt;
  if (ms > 1000) {
    logger.warn({ ms, sql: obj.sql.slice(0, 500), path: currentPath() },
      'slow query');
  }
});

// The pattern throughout: the client chooses from a MENU. It never composes
// the query itself — which is the same rule that prevents SQL injection.