Page two, again

You are the engineer on call. Three tickets have come in against the comment feed. Some of them are bugs. Some of them may not be.

Your job is triage and a minimal fix — not a rewrite.

The tickets

FEED-441 — opened by Ama (Support)

Comments are appearing on page 1 and then again on page 2. Different comments go missing entirely — a user linked us a comment of their own that they cannot find by paging through the thread it is in, and it is definitely still there, the direct link works.

Worst on the big threads. The busy live ones are unusable.

FEED-455 — opened by Ama (Support)

Four users this week with a completely empty feed. Not slow — empty. "No comments yet" on threads with thousands.

All four are long-standing accounts. The only thing I can find in common is that they all use the block feature a lot. One of them has 200-odd blocks. A brand new account with no blocks has never reported this.

FEED-460 — opened by Dev (Ops)

Sort order is broken on page 1. Newest first is the entire point of this feed and I am looking at a thread whose top comment is from March. Scroll down and the rest of the page is correctly ordered, newest at the top of the remainder. Just that one at the top is wrong.

Comment on FEED-455 from Sam (Data), 2 days ago

Seen this before at my last place. It is the connection pool — pg defaults to 10 clients and we are saturating it under load, so some queries come back partial instead of erroring. That is also why it looks random. Bump max to 50 and add a statement timeout.

The schema

CREATE TABLE users (
  id          uuid PRIMARY KEY,
  handle      text NOT NULL UNIQUE,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE threads (
  id          uuid PRIMARY KEY,
  title       text NOT NULL
);

CREATE TABLE comments (
  id          uuid PRIMARY KEY,
  thread_id   uuid NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
  author_id   uuid NOT NULL REFERENCES users(id),
  body        text NOT NULL,
  is_pinned   boolean NOT NULL DEFAULT false,
  is_deleted  boolean NOT NULL DEFAULT false,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX comments_thread_created_idx
  ON comments (thread_id, created_at DESC);

CREATE TABLE blocks (
  user_id          uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  -- Nulled rather than deleted when the blocked account is removed, so the
  -- block survives an account being recreated on the same handle.
  blocked_user_id  uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at       timestamptz NOT NULL DEFAULT now()
);

The code

/**
 * feed/comments.ts — the comment feed query. Serves the thread view and the
 * mobile API. Roughly 4k requests/second at peak.
 */
import { Pool } from "pg";
import { logger } from "../logger";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const PAGE_SIZE = 20;

export interface Comment {
  id: string;
  authorHandle: string;
  body: string;
  isPinned: boolean;
  createdAt: Date;
}

export interface Page {
  comments: Comment[];
  /** Total matching this thread + viewer, for the "N comments" header. */
  total: number;
  hasMore: boolean;
}

/**
 * One page of a thread, as seen by one viewer.
 *
 * `page` is 1-based and comes straight off the query string.
 */
export async function getCommentPage(
  threadId: string,
  viewerId: string,
  page: number
): Promise<Page> {
  const offset = (Math.max(1, page) - 1) * PAGE_SIZE;

  const rows = await pool.query<{
    id: string;
    handle: string;
    body: string;
    is_pinned: boolean;
    created_at: Date;
  }>(
    `SELECT c.id, u.handle, c.body, c.is_pinned, c.created_at
       FROM comments c
       JOIN users u ON u.id = c.author_id
      WHERE c.thread_id = $1
        AND c.is_deleted = false
        AND c.author_id NOT IN (
              SELECT b.blocked_user_id
                FROM blocks b
               WHERE b.user_id = $2
            )
      ORDER BY c.is_pinned DESC, c.created_at DESC
      LIMIT $3 OFFSET $4`,
    [threadId, viewerId, PAGE_SIZE, offset]
  );

  const counted = await pool.query<{ count: string }>(
    `SELECT count(*) AS count
       FROM comments c
      WHERE c.thread_id = $1
        AND c.is_deleted = false`,
    [threadId]
  );

  const total = Number(counted.rows[0].count);

  logger.debug("feed page served", {
    threadId,
    viewerId,
    page,
    returned: rows.rowCount,
    total,
  });

  return {
    comments: rows.rows.map((r) => ({
      id: r.id,
      authorHandle: r.handle,
      body: r.body,
      isPinned: r.is_pinned,
      createdAt: r.created_at,
    })),
    total,
    hasMore: offset + rows.rowCount < total,
  };
}

Answer in exactly these sections

Triage

A table with one row per ticket, in order:

Verdict is exactly one of REAL or NOT A BUG. Cause is one line — for a real bug, what is actually wrong; for a non-bug, what the reporter is actually looking at and why the code above does not have the defect they describe.

The theory on the ticket

One paragraph on Sam's connection-pool explanation. Right, partly right, or wrong — and if wrong, say what a saturated pg pool actually does to a query, because it is not what he says it does.

The patches

For each ticket you marked REAL, and only those:

• Quote the exact line or lines that are wrong, verbatim from the code or schema above.
• Give the exact replacement.
• One line on why that fixes it.

For FEED-441 specifically, if the correct fix changes the shape of the API rather than one line, say so plainly and give the smallest version of it you would actually ship.

Do not give me the whole file back.

Anything else

At most two further defects nobody has reported, each one you can quote a line for.

If there is nothing else worth raising, write exactly Nothing else.

The standard I'm holding you to

Every claim should be checkable against the schema and the code above, or against documented PostgreSQL behavior you can state exactly. One of these bugs turns on a rule about how SQL evaluates a specific operator — if that is your diagnosis, state the rule, do not just name it. A confident diagnosis of a bug that is not here costs you more than missing one that is.