Những gì một người dùng thấy

order by (select max(created_at) from messages where channel_id = c.id) desc

-- Chapter 3.16 — ordering a user's channels, and knowing what they have not read.
--
-- Two changes with one thing in common: both answer a question `last_sequence`
-- looks like it should answer and cannot.
--
-- FR-CHN-08 wants a user's channels ordered by most recent activity.
-- `channels.last_sequence` is a per-channel monotonic counter, so two channels
-- both sitting at 50 say nothing about which took a message more recently. It
-- orders messages inside one channel and cannot order channels against each
-- other at all.
--
-- THE ALTERNATIVE WAS MEASURED AND IT IS 145x WORSE. Ordering by
-- `max(messages.created_at)` per channel, at 2,000 channels and 1,000,000
-- messages with one member in every channel:
--
--     aggregate over messages   159.737 ms  158.103 ms  158.842 ms
--                               -> Seq Scan on messages, 1,000,000 rows,
--                                  on every listing
--     indexed column              1.102 ms    1.496 ms    2.210 ms
--
-- AND THE TEST LANE SAYS THE OPPOSITE. Its busiest environment holds 579
-- messages, and the same aggregate answers in 0.870 ms there. Reporting that
-- number would have settled the question in favour of adding no column. The
-- cost grows with message volume, which is the one number a chat platform
-- guarantees will grow (research R4).
--
-- `now()` AS THE DEFAULT, AND A BACKFILL AFTER THIS FILE. Adding a column with
-- a constant default is fast — Postgres 11 and later store it in the catalogue
-- rather than rewriting the table. Setting every existing channel to its real
-- last activity is `max(messages.created_at)` per channel, which is the scan
-- above, so it does NOT belong in a migration: the workflow requires migrations
-- to be executable without downtime. It runs afterwards, in batches, as its own
-- step.
ALTER TABLE channels
    ADD COLUMN last_activity_at TIMESTAMPTZ NOT NULL DEFAULT now();
--> statement-breakpoint
 
-- The listing's ordering (FR-013), environment first because every listing is
-- inside one and the planner then walks the timestamp backward from there.
CREATE INDEX channels_environment_last_activity
    ON channels USING btree (environment_id, last_activity_at DESC NULLS LAST);
--> statement-breakpoint
 
-- FR-CHN-09's unread count needs to know how far a user has read, and nothing
-- in the schema recorded it. Verified before writing this: no `last_read`,
-- `read_at` or equivalent column in any table.
--
-- NO COUNTER COLUMN, and that is the whole design. Unread is
-- `greatest(channels.last_sequence - read_positions.sequence, 0)`, because the
-- write path already maintains `last_sequence` and chapter 2.2 made it the
-- sequencing authority. Three shapes measured for one page of 50 channels
-- against 1,000,000 messages:
--
--     count rows past the read position    9.807 ms  11.109 ms  13.431 ms
--     a cached counter on the position      2.129 ms   1.928 ms   1.226 ms
--     last_sequence - read position         1.122 ms   4.426 ms   4.497 ms
--
-- The cached counter is no faster and adds a value that can go stale. What the
-- subtraction accepts is that a tombstoned message still occupies a sequence,
-- so a deleted message counts as one unread; counting rows instead is 10x the
-- cost on the query a client runs to render its first screen (research R5,
-- FR-016).
--
-- environment_id IS DENORMALISED, AND channel_id ALREADY DETERMINES IT. The
-- column is here because feature 030's guard watches tables that carry one, and
-- a table without it is a table the guard cannot refuse a cross-environment
-- delete on. `members` is the counter-example worth knowing: it has no
-- environment_id, so the catalogue classifies it as `hop` — reached through a
-- foreign key — and no trigger protects it. A read position is per-user state
-- that a tenant's own operations mutate, so it takes the stronger
-- classification and becomes the guard's tenth table.
--
-- NO id COLUMN. The primary key is (channel_id, user_id) because that is what a
-- read position is. The guard's refusal message interpolates a key, and chapter
-- 3.13 installed `coalesce(to_jsonb(OLD) ->> 'id', to_jsonb(OLD)::text)` for
-- exactly the tables that have no `id` to interpolate.
CREATE TABLE read_positions (
    environment_id  UUID NOT NULL REFERENCES environments(id),
    channel_id      UUID NOT NULL REFERENCES channels(id),
    user_id         UUID NOT NULL REFERENCES users(id),
    -- Advances forwards only. A write naming a lower sequence than the stored
    -- one is accepted and changes nothing, so a client replaying an old
    -- acknowledgement cannot move a user's unread count backwards. A value past
    -- channels.last_sequence is refused (FR-018): a position nothing can reach
    -- makes every later count wrong.
    sequence        BIGINT NOT NULL,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT read_positions_channel_id_user_id_pk PRIMARY KEY (channel_id, user_id)
);

// Set `channels.last_activity_at` to each channel's real last activity.
//
// Chapter 3.16, T019 — and it is a SCRIPT and not part of migration 0011 for one
// reason: the constitution's workflow section requires migrations to be
// "executable without downtime", and this is a scan.
//
// Adding the column is free. Postgres 11 and later store a constant default in the
// catalogue rather than rewriting the table, so `ALTER TABLE channels ADD COLUMN
// last_activity_at TIMESTAMPTZ NOT NULL DEFAULT now()` returns immediately however
// many channels exist. What is not free is giving every existing channel its real
// value, because that is `max(messages.created_at)` per channel — the same
// aggregate research R4 measured at 159 ms over 1,000,000 messages, and the reason
// the column exists at all.
//
// SO IT RUNS AFTERWARDS, IN BATCHES, AND REPORTS. One statement per batch of
// channel ids, each batch its own transaction, so the lock is held for a batch and
// not for the table. A channel with no messages keeps the `now()` the default gave
// it: it has no activity to date from, and dating it from the epoch would sort it
// below every real channel forever.
//
// Idempotent by construction — it computes an absolute value from `messages`, so
// running it twice writes the same timestamps. Safe to re-run after a restore.
 
// The api's own pool, not a `pg` import: `pg` is a dependency of services/api and
// not of the workspace root, and `createPool` already applies this project's
// DATABASE_URL default of port 15432 — the port the code documents, against a
// compose file that defaults the host to 5432. `scripts/seed-demo-tenant.mjs`
// reaches for the same build output for the same reason.
import { createPool } from "../services/api/dist/db/client.js";
 
const BATCH = Number(process.env.RELAY_BACKFILL_BATCH ?? 500);
const pool = createPool();
 
async function main() {
  const started = Date.now();
  const { rows: all } = await pool.query(`SELECT id FROM channels ORDER BY id`);
  let batches = 0;
  let touched = 0;
 
  for (let i = 0; i < all.length; i += BATCH) {
    const ids = all.slice(i, i + BATCH).map((r) => r.id);
    // `GREATEST` and not a bare max: a channel whose newest message somehow
    // predates the row itself would otherwise move backwards, and the column is
    // NOT NULL so there is always an existing value to compare against.
    const { rowCount } = await pool.query(
      `UPDATE channels c
          SET last_activity_at = GREATEST(
                c.last_activity_at,
                COALESCE(
                  (SELECT max(m.created_at) FROM messages m WHERE m.channel_id = c.id),
                  c.last_activity_at
                )
              )
        WHERE c.id = ANY($1::uuid[])`,
      [ids],
    );
    batches += 1;
    touched += rowCount ?? 0;
  }
 
  const ms = Date.now() - started;
  // The numbers T019 asks to be recorded, printed rather than estimated.
  console.log(
    `backfill: ${all.length} channels in ${batches} batches of ${BATCH}, ` +
      `${touched} rows written, ${ms} ms`,
  );
}
 
main()
  .then(() => pool.end())
  .catch(async (error) => {
    await pool.end();
    console.error(error);
    process.exitCode = 1;
  });

@@ -2152,6 +2152,28 @@
   id: string;
   external_id: string;
   display_name: string | null;
+  /** Chapter 3.15, FR-023. Both columns have existed since chapter 2.1 and **no route
+   * has ever written or read either one** — two of the four dead columns this feature
+   * exists to give readers. They are on the row rather than fetched by a second query
+   * because every caller that wants a profile wants all of it. */
+  avatar_url: string | null;
+  metadata: Record<string, unknown>;
+  /** Chapter 3.15, FR-031. Read on the send path and at connect. Like `deleted_at`, it
+   * is selected rather than filtered so a caller can tell the states apart — a
+   * repository that hid banned users would make the ban unobservable and the refusal
+   * untestable. */
+  banned_at: string | null;
+  /** Chapter 3.15, FR-017. A deleted user KEEPS THEIR ROW: `ON DELETE SET NULL` on
+   * `messages.user_id` would satisfy the letter of "messages are preserved" and break
+   * delivery, because `backfill.controller`'s `toFrame` drops a senderless row — so
+   * "authored by a deleted user" and "authored by nobody" are different states and
+   * only one of them is the clause.
+   *
+   * Every route that names a user in its path reads this and answers 404. It is
+   * selected here rather than filtered in the query so a caller can tell the two
+   * apart: a repository that hid deleted rows would make the marker unobservable and
+   * the deletion untestable. */
+  deleted_at: string | null;
 }
 
 export interface ChannelRow {
@@ -2222,6 +2244,19 @@
   }
 }
 
+/** A write or a connect refused because the user is banned (chapter 3.15, FR-031).
+ *
+ * FIRST IN FR-021a's ORDER, and the ban check runs BEFORE the channel is read at all —
+ * so a banned user gets one answer for every channel id, whether it exists, belongs to
+ * somebody else, or was invented. Any other position leaks: check the channel first and
+ * a banned user learns which channel ids are real. */
+export class UserBannedError extends Error {
+  constructor(public readonly userId: string) {
+    super(`user banned: ${userId}`);
+    this.name = "UserBannedError";
+  }
+}
+
 /** Timestamps cross the wire as RFC 3339 strings (constitution: UTC,
  * millisecond precision) — the driver hands back a Date or a string
  * depending on the column and the query shape. */
@@ -2553,7 +2588,18 @@
       .returning({ id: users.id });
 
     if (inserted.length > 0) {
-      return { id, external_id: externalId, display_name: displayName ?? null };
+      // `deleted_at: null` on the fresh row, stated rather than spread: a user
+      // created now is not deleted, and an insert that returned the field would cost
+      // a column in the RETURNING clause to learn what the code already knows.
+      return {
+        id,
+        external_id: externalId,
+        display_name: displayName ?? null,
+        avatar_url: null,
+        metadata: {},
+        banned_at: null,
+        deleted_at: null,
+      };
     }
     const existing = await this.getUserByExternalId(externalId);
     if (existing === null) throw new Error(`user ${externalId} could not be created or read`);
@@ -2569,6 +2615,10 @@
         id: users.id,
         external_id: users.externalId,
         display_name: users.displayName,
+        avatar_url: users.avatarUrl,
+        metadata: users.metadata,
+        bannedAt: users.bannedAt,
+        deletedAt: users.deletedAt,
       })
       .from(users)
       .where(
@@ -2577,12 +2627,27 @@
           eq(users.externalId, externalId),
         ),
       );
-    return rows[0] ?? null;
+    const row = rows[0];
+    return row === undefined
+      ? null
+      : {
+          id: row.id,
+          external_id: row.external_id,
+          display_name: row.display_name,
+          avatar_url: row.avatar_url,
+          // `as` AND NOT `?? {}`. The column is `notNull().default({})`, so the driver
+          // never hands back null — and chapter 3.12 removed `addMember`'s
+          // `(inserted.rowCount ?? 0)` for exactly this reason: an arm nothing can take,
+          // bought for nothing, in the one file constitution VI asks 100% of.
+          metadata: row.metadata as Record<string, unknown>,
+          banned_at: row.bannedAt === null ? null : toIso(row.bannedAt),
+          deleted_at: row.deletedAt === null ? null : toIso(row.deletedAt),
+        };
   }
 
   /** IDEMPOTENT ON THE CUSTOMER'S OWN IDENTIFIER (FR-017, FR-CHN-02).
    *
-   * This was a plain insert until chapter 3.12, which is fine for a fixture and
+   * This was a plain insert until chapter 3.13, which is fine for a fixture and
    * cannot back an endpoint: a repeated `external_id` raises against
    * `channels_environment_id_external_id_unique`, and `ProtocolErrorFilter`
    * renders a unique violation as `internal_error`. The second call in an
@@ -2672,7 +2737,7 @@
    * is the layer's one raw SQL island, permitted by ADR-16 and kept inside
    * the wall like everything else.
    *
-   * THREE OUTCOMES, NOT A BOOLEAN (chapter 3.12, R14a). Until then this returned
+   * THREE OUTCOMES, NOT A BOOLEAN (chapter 3.13, R14a). Until then this returned
    * `false` for all of: the channel is not yours, the user is not yours, and you
    * asked twice. Conflating the first two is right and is the whole point — a
    * foreign id must be indistinguishable from an absent one. Conflating the third
@@ -2953,6 +3018,506 @@
     return rows.map((r) => r.channel_id);
   }
 
+  /** Upsert a user by external id, updating the profile fields present (chapter 3.15,
+   * FR-025, FR-026).
+   *
+   * NOT `createUser`, AND THE DIFFERENCE IS THE POINT. `createUser` is deliberately not an
+   * update: its comment says so — "the display name of the existing row wins; quietly
+   * renaming a user because someone re-sent a member list would be a write nobody asked
+   * for". That is right for the member-add, which asks for membership and happens to need a
+   * user. FR-026 asks for the opposite here: an entry naming an existing user **updates**
+   * it, because this route's subject IS the user record.
+   *
+   * Two functions rather than a flag, so neither route can accidentally get the other's
+   * behaviour. The member-add's caller keeps `createUser`.
+   *
+   * IT ALSO REVIVES A DELETED USER, which is FR-030 and not an accident.
+   * `(environment_id, external_id)` is unique and the row is still there, so presenting
+   * the id again has no other honest answer than reusing it. `deleted_at` is cleared and
+   * the profile takes whatever this call carries — a revived row does not inherit the
+   * profile the deletion wiped.
+   *
+   * `status` REPORTS WHICH HAPPENED, per entry, in the shape chapter 3.13 chose for
+   * `addMember`: a partial outcome is reported per entry rather than collapsed into one
+   * status code. */
+  async upsertUser(
+    externalId: string,
+    profile: {
+      display_name?: string | null | undefined;
+      avatar_url?: string | null | undefined;
+      metadata?: Record<string, unknown> | undefined;
+    },
+  ): Promise<{ user: UserRow; status: "created" | "updated" | "revived" }> {
+    const id = randomUUID();
+    const inserted = await this.db
+      .insert(users)
+      .values({
+        id,
+        environmentId: this.environmentId,
+        externalId,
+        displayName: profile.display_name ?? null,
+        avatarUrl: profile.avatar_url ?? null,
+        ...(profile.metadata === undefined ? {} : { metadata: profile.metadata }),
+      })
+      .onConflictDoNothing({ target: [users.environmentId, users.externalId] })
+      .returning({ id: users.id });
+
+    if (inserted.length > 0) {
+      return {
+        user: {
+          id,
+          external_id: externalId,
+          display_name: profile.display_name ?? null,
+          avatar_url: profile.avatar_url ?? null,
+          metadata: profile.metadata ?? {},
+          banned_at: null,
+          deleted_at: null,
+        },
+        status: "created",
+      };
+    }
+
+    // ONE UNREACHABLE THROW, NOT TWO, and the count is the reason. An earlier version
+    // read the row, threw if it was absent, updated it, read it back, and threw again if
+    // THAT was absent — two statements for one impossible state (the winner of an
+    // `ON CONFLICT` race having its row deleted between two statements of the same call,
+    // which nothing in the api can do). `repository.ts` already carried two throws of
+    // that class from chapter 3.12 and its lines ratchet sat at 99; a third took the file
+    // to 98.92 and the gate went red. The instrument was right: the second throw bought
+    // nothing the first did not already say.
+    //
+    // The pre-image is read for ONE fact the update cannot return — whether the row was
+    // deleted before this call, which is what makes the difference between `updated` and
+    // `revived`. `UPDATE ... RETURNING` gives post-update values, so there is no way to
+    // learn it from the write itself.
+    const [before] = await this.db
+      .select({ id: users.id, deletedAt: users.deletedAt })
+      .from(users)
+      .where(
+        and(
+          eq(users.environmentId, this.environmentId),
+          eq(users.externalId, externalId),
+        ),
+      )
+      .limit(1);
+
+    if (before !== undefined) {
+      await this.db
+        .update(users)
+        .set({
+          // Absent stays absent, exactly as the single PATCH treats it — except
+          // `deleted_at`, which a revival always clears.
+          ...(profile.display_name === undefined
+            ? {}
+            : { displayName: profile.display_name }),
+          ...(profile.avatar_url === undefined ? {} : { avatarUrl: profile.avatar_url }),
+          ...(profile.metadata === undefined ? {} : { metadata: profile.metadata }),
+          deletedAt: null,
+        })
+        .where(and(eq(users.id, before.id), eq(users.environmentId, this.environmentId)));
+    }
+
+    const after = await this.getUserByExternalId(externalId);
+    if (after === null) throw new Error(`user ${externalId} could not be created or read`);
+    return { user: after, status: before?.deletedAt != null ? "revived" : "updated" };
+  }
+
+  /** Ban and unban a user, tenant-wide (chapter 3.15, FR-031, FR-032).
+   *
+   * TENANT-SCOPED AND NOT A REMOVAL. A ban stops the user connecting and sending
+   * anywhere in the environment; it takes no membership away and hides no history. So
+   * banning a member of a private channel leaves them a member — the channel's other
+   * members still see their messages, and lifting the ban restores everything without
+   * anybody being re-added. `deleteUser` is the operation that removes memberships, and
+   * these two are deliberately not it.
+   *
+   * IDEMPOTENT, both directions, and neither reports which happened. Unlike the deletion,
+   * nothing downstream needs to tell "banned now" from "was already banned": the route
+   * answers 200 either way because the caller's intent — this user must not connect — is
+   * satisfied either way.
+   *
+   * `banned_at` HAD NO WRITER, the same omission `channels.archived_at` had. The column
+   * has been in the schema since chapter 2.1 with zero references outside tests. */
+  async banUser(userId: string): Promise<void> {
+    await this.db
+      .update(users)
+      .set({ bannedAt: new Date() })
+      .where(
+        and(
+          eq(users.id, userId),
+          eq(users.environmentId, this.environmentId),
+          isNull(users.bannedAt),
+        ),
+      );
+  }
+
+  async unbanUser(userId: string): Promise<void> {
+    await this.db
+      .update(users)
+      .set({ bannedAt: null })
+      .where(
+        and(eq(users.id, userId), eq(users.environmentId, this.environmentId)),
+      );
+  }
+
+  /** Delete a user, keeping the row (chapter 3.15, FR-027, FR-028, FR-029).
+   *
+   * WHAT GOES: the profile fields, the memberships, the read positions.
+   * WHAT STAYS: the row, the messages, and every `usage_active_users` row.
+   *
+   * THE ROW IS THE WHOLE ARGUMENT. `ON DELETE SET NULL` on `messages.user_id` satisfies
+   * the letter of "messages are preserved" and breaks delivery:
+   * `backfill.controller`'s `toFrame` drops a senderless row, so every message the user
+   * ever sent would silently disappear from every reconnecting client. "Authored by a
+   * deleted user" and "authored by nobody" are different states and only one of them is
+   * FR-028.
+   *
+   * `usage_active_users` IS UNTOUCHED (FR-029). Billing history does not vanish with a
+   * profile — a customer who deleted a user in March still owes for March.
+   *
+   * MEMBERSHIPS AND READ POSITIONS GO TOGETHER, and the read position goes because the
+   * membership does: a position is per-member state keyed by channel and user, so keeping
+   * it would leave a row pointing at a membership that no longer exists. It is the same
+   * deletion the member-removal path already performs.
+   *
+   * IDEMPOTENT, and it reports which happened, so the route can answer 200 twice while a
+   * user who never existed still gets 404. */
+  async deleteUser(userId: string): Promise<boolean> {
+    return this.db.transaction(async (tx) => {
+      const [alive] = await tx
+        .select({ id: users.id, deletedAt: users.deletedAt })
+        .from(users)
+        .where(
+          and(eq(users.id, userId), eq(users.environmentId, this.environmentId)),
+        )
+        .limit(1);
+      if (alive === undefined) return false;
+
+      await tx.delete(readPositions).where(eq(readPositions.userId, userId));
+      await tx.delete(members).where(eq(members.userId, userId));
+      await tx
+        .update(users)
+        .set({
+          displayName: null,
+          avatarUrl: null,
+          metadata: {},
+          deletedAt: alive.deletedAt ?? new Date(),
+        })
+        .where(eq(users.id, userId));
+      return true;
+    });
+  }
+
+  /** Write a user's profile (chapter 3.15, FR-023, FR-024).
+   *
+   * THE FIRST WRITER `users.avatar_url` AND `users.metadata` HAVE EVER HAD. Both columns
+   * have been in the schema since chapter 2.1 with zero references outside tests — two of
+   * the four columns this feature was specified to give readers, and giving them a reader
+   * meant giving them a writer first.
+   *
+   * PARTIAL BY CONSTRUCTION, and `undefined` is not `null`. A field absent from the patch
+   * is absent from the `set`, so it keeps its value; a field present and null is written
+   * null, which clears it. `exactOptionalPropertyTypes` makes the two distinguishable in
+   * the type rather than by convention (ADR-15's strictness).
+   *
+   * AN EMPTY PATCH DOES NOT ISSUE AN UPDATE. Drizzle throws on a `set` with no columns,
+   * and issuing `SET` with nothing to set would be a write that means nothing anyway. The
+   * caller gets the current row, which is the honest answer to a request that asked for no
+   * change.
+   *
+   * SCOPED AND ALIVE. The `where` carries the environment and `deleted_at IS NULL`: a
+   * deleted user's profile is not editable, and the route above answers 404 for the same
+   * reason. Returning null is how the caller tells "no such user" from "wrote nothing". */
+  async updateUserProfile(
+    userId: string,
+    patch: {
+      display_name?: string | null | undefined;
+      avatar_url?: string | null | undefined;
+      metadata?: Record<string, unknown> | undefined;
+    },
+  ): Promise<UserRow | null> {
+    const set: Record<string, unknown> = {};
+    if (patch.display_name !== undefined) set["displayName"] = patch.display_name;
+    if (patch.avatar_url !== undefined) set["avatarUrl"] = patch.avatar_url;
+    if (patch.metadata !== undefined) set["metadata"] = patch.metadata;
+
+    if (Object.keys(set).length > 0) {
+      const updated = await this.db
+        .update(users)
+        .set(set)
+        .where(
+          and(
+            eq(users.id, userId),
+            eq(users.environmentId, this.environmentId),
+            isNull(users.deletedAt),
+          ),
+        )
+        .returning({ externalId: users.externalId });
+      if (updated.length === 0) return null;
+      return this.getUserByExternalId(updated[0]!.externalId);
+    }
+
+    const [row] = await this.db
+      .select({ externalId: users.externalId })
+      .from(users)
+      .where(
+        and(
+          eq(users.id, userId),
+          eq(users.environmentId, this.environmentId),
+          isNull(users.deletedAt),
+        ),
+      )
+      .limit(1);
+    return row === undefined ? null : this.getUserByExternalId(row.externalId);
+  }
+
+  /** Record a read position (chapter 3.15, FR-017, FR-018).
+   *
+   * FORWARDS ONLY, and the clamp is in SQL rather than in a read-then-write. `greatest`
+   * on the conflict target means a replayed acknowledgement from a client that fell
+   * behind is a 200 that changes nothing, and two concurrent writes cannot lose the
+   * higher one — a read followed by a write would, whichever order they interleave.
+   *
+   * PAST THE END IS REFUSED (FR-018). A position beyond `channels.last_sequence` makes
+   * every count derived from it wrong for every message that arrives afterwards, and it
+   * cannot come from a client that has actually read anything. `null` is how the caller
+   * learns to answer 400; the alternative — clamping silently — would accept a client
+   * bug and hide it.
+   *
+   * THE SEQUENCE IS READ IN THE SAME TRANSACTION as the upsert, so the bound cannot
+   * move between the check and the write. It can only move UP, so a racing send makes
+   * the check conservative rather than wrong. */
+  async setReadPosition(
+    channelId: string,
+    userId: string,
+    sequence: number,
+  ): Promise<{ sequence: number } | null> {
+    return this.db.transaction(async (tx) => {
+      const [channel] = await tx
+        .select({ lastSequence: channels.lastSequence })
+        .from(channels)
+        .where(
+          and(eq(channels.id, channelId), eq(channels.environmentId, this.environmentId)),
+        )
+        .limit(1);
+      if (channel === undefined || sequence > channel.lastSequence) return null;
+
+      const [row] = await tx
+        .insert(readPositions)
+        .values({
+          environmentId: this.environmentId,
+          channelId,
+          userId,
+          sequence,
+        })
+        .onConflictDoUpdate({
+          target: [readPositions.channelId, readPositions.userId],
+          set: {
+            sequence: sql`greatest(${readPositions.sequence}, excluded.sequence)`,
+            updatedAt: new Date(),
+          },
+        })
+        .returning({ sequence: readPositions.sequence });
+      return row ?? null;
+    });
+  }
+
+  /** Mark a user deleted, keeping the row (chapter 3.15, FR-017).
+   *
+   * THE ROW SURVIVES ON PURPOSE. `ON DELETE SET NULL` on `messages.user_id` would
+   * satisfy "messages are preserved" and break delivery: `toFrame` drops a senderless
+   * row from a resume, so a deleted author would silently remove their messages from
+   * every reconnecting client. The marker keeps authorship and removes the user from
+   * the API.
+   *
+   * IDEMPOTENT, and it reports which happened. Deleting a user twice is not an error —
+   * a customer's retry after a timeout is the ordinary case — but the caller still has
+   * to be able to answer 404 the second time, and `false` is how it knows.
+   *
+   * The deletion route is this method's production caller and arrives in a later
+   * phase. It exists now because the listing has to answer 404 for a deleted user,
+   * and a 404 branch with no way to reach it is a branch no test can cover. */
+  async markUserDeleted(userId: string): Promise<boolean> {
+    const updated = await this.db
+      .update(users)
+      .set({ deletedAt: new Date() })
+      .where(
+        and(
+          eq(users.id, userId),
+          eq(users.environmentId, this.environmentId),
+          isNull(users.deletedAt),
+        ),
+      )
+      .returning({ id: users.id });
+    return updated.length > 0;
+  }
+
+  /** A user's channels, most recently active first, keyset-paginated (chapter
+   * 3.15, FR-013, FR-CHN-08).
+   *
+   * `id` IS PART OF THE KEY AND NOT DECORATION. `last_activity_at` is not unique:
+   * two channels can take a message in the same millisecond, and a keyset on a
+   * non-unique column either skips a row or repeats one at every page boundary
+   * where a tie straddles it. Postgres row comparison — `(a, b) < (x, y)` — gives
+   * the strict lexicographic "everything after this exact row" the cursor means,
+   * in one predicate the planner can drive an index with.
+   *
+   * MEMBERSHIP IS THE JOIN, NOT A FILTER AFTER THE FACT (FR-015). The listing set
+   * is the membership set: `members_user_channel` is an index on
+   * `(user_id, channel_id)`, so the join drives from the user's own rows and a
+   * channel they are not in is never a candidate. A public channel they could read
+   * by id does not appear here — the read set and the subscription set are
+   * deliberately different sets, and the chapter says so.
+   *
+   * ARCHIVED CHANNELS APPEAR, with `archived_at` on the row (FR-022). A customer
+   * who archived a channel still has to be able to find it, and hiding it here
+   * would make the archive a delete.
+   *
+   * SCOPED THROUGH `users`, the way `channelsForUser` is: `members` carries no
+   * `environment_id` of its own (it is a hop table, two links from a tenant), so
+   * the scope is asserted on the parent that has one. */
+  async listChannelsForUser(
+    userId: string,
+    { limit, after }: { limit: number; after?: { activityAt: Date; id: string } },
+  ): Promise<{
+    rows: Array<{
+      id: string;
+      external_id: string;
+      type: ChannelRow["type"];
+      name: string | null;
+      role: string;
+      archived_at: string | null;
+      last_activity_at: string;
+      last_sequence: number;
+      unread: number;
+      last_message: {
+        sequence: number;
+        text: string | null;
+        user: { id: string } | null;
+        created_at: string;
+      } | null;
+    }>;
+    nextCursor: { activityAt: Date; id: string } | null;
+  }> {
+    // ONE ROW MORE THAN ASKED FOR, which is how the caller learns whether there is
+    // a next page without a second count query. The extra row is dropped before
+    // returning and its predecessor becomes the cursor.
+    const rows = await this.db
+      .select({
+        id: channels.id,
+        externalId: channels.externalId,
+        type: sql<ChannelRow["type"]>`${channels.type}`,
+        name: channels.name,
+        role: members.role,
+        archivedAt: channels.archivedAt,
+        lastActivityAt: channels.lastActivityAt,
+        lastSequence: channels.lastSequence,
+        // THE UNREAD COUNT, WITH NO COUNTER (FR-016). `channels.last_sequence` has been
+        // the sequencing authority since chapter 2.2 and the write path maintains it, so
+        // this has nothing to invalidate and nothing to backfill. Measured for one page
+        // of 50 channels against 1,000,000 messages: counting rows past the position is
+        // 9.8-13.4 ms, a cached counter is 1.2-2.1 ms, and this subtraction is
+        // 1.1-4.5 ms. The counter is no faster and adds a value that can go stale.
+        //
+        // `greatest(..., 0)` is defence against a bug, not a reachable state: a position
+        // is refused past `last_sequence` when it is written and `last_sequence` never
+        // goes backwards. It costs nothing and turns a negative count into zero rather
+        // than into a client bug report. `repository.itest.ts` plants a position above
+        // the end to cover the arm, because nothing else can reach it.
+        //
+        // A MISSING ROW IS POSITION ZERO (FR-017a). `coalesce` on the left join, not a
+        // seeded row on join: a new member's unread count is the channel's whole
+        // history, which is the same answer a re-added member gets, because removal
+        // deleted their position with their membership.
+        unread: sql<number>`greatest(${channels.lastSequence} - coalesce(${readPositions.sequence}, 0), 0)`,
+        // THE LAST MESSAGE, AND A TOMBSTONE IS STILL THE LAST MESSAGE (FR-019).
+        //
+        // The row AT `last_sequence`, reported with `text: null` when it is a tombstone,
+        // rather than walking back to the last row that still has text. The walk-back is
+        // a second query per channel and it would disagree with the count beside it,
+        // which counts the tombstone because the sequence is kept. One rule for both
+        // fields. A client that wants a preview renders "message deleted" from the null.
+        //
+        // A LATERAL SUBQUERY AND NOT A JOIN, because `messages_channel_id_sequence_unique`
+        // makes this an index lookup per row of an already-bounded page — 26 lookups, not
+        // a join against the whole message table. A join would also have to carry the
+        // ordering, and the planner would have to be talked out of sorting messages.
+        lastMessage: sql<{
+          sequence: number;
+          text: string | null;
+          user_external_id: string | null;
+          created_at: string;
+        } | null>`(
+          select json_build_object(
+            'sequence', m.sequence,
+            'text', m.text,
+            'user_external_id', mu.external_id,
+            'created_at', m.created_at
+          )
+            from messages m
+            left join users mu on mu.id = m.user_id
+           where m.channel_id = ${channels.id} and m.sequence = ${channels.lastSequence}
+        )`,
+      })
+      .from(members)
+      .innerJoin(channels, eq(channels.id, members.channelId))
+      .innerJoin(users, eq(users.id, members.userId))
+      .leftJoin(
+        readPositions,
+        and(
+          eq(readPositions.channelId, members.channelId),
+          eq(readPositions.userId, members.userId),
+        ),
+      )
+      .where(
+        and(
+          eq(members.userId, userId),
+          eq(users.environmentId, this.environmentId),
+          eq(channels.environmentId, this.environmentId),
+          after === undefined
+            ? undefined
+            : sql`(${channels.lastActivityAt}, ${channels.id}) < (${after.activityAt}, ${after.id})`,
+        ),
+      )
+      .orderBy(desc(channels.lastActivityAt), desc(channels.id))
+      .limit(limit + 1);
+
+    const page = rows.slice(0, limit);
+    const last = page.at(-1);
+    return {
+      rows: page.map((r) => ({
+        id: r.id,
+        external_id: r.externalId,
+        type: r.type,
+        name: r.name,
+        role: r.role,
+        archived_at: r.archivedAt === null ? null : toIso(r.archivedAt),
+        last_activity_at: toIso(r.lastActivityAt),
+        last_sequence: r.lastSequence,
+        unread: Number(r.unread),
+        // `null` when the channel has never had a message: `last_sequence` is 0 and no
+        // row carries sequence 0, so the subquery finds nothing. Distinct from a
+        // tombstone, which IS a row and reports itself with a null text.
+        last_message:
+          r.lastMessage === null
+            ? null
+            : {
+                sequence: Number(r.lastMessage.sequence),
+                text: r.lastMessage.text,
+                user:
+                  r.lastMessage.user_external_id === null
+                    ? null
+                    : { id: r.lastMessage.user_external_id },
+                created_at: r.lastMessage.created_at,
+              },
+      })),
+      nextCursor:
+        rows.length > limit && last !== undefined
+          ? { activityAt: last.lastActivityAt, id: last.id }
+          : null,
+    };
+  }
+
   /** The write path (chapters 2.2 + 2.3): sequence assignment under the
    * channel row lock (ADR-03), with idempotency enforcement via the
    * partial unique index (DR-03). The transaction IS the ordering
@@ -2994,6 +3559,29 @@
       // only one of them can be `now()`.
       const period = periodOf(new Date());
 
+      // ── THE BAN, FIRST, AND AHEAD OF THE CHANNEL READ (FR-031, FR-021a) ─────
+      //
+      // T072 left this slot and only Phase 15 can fill it, because until now nothing
+      // wrote `banned_at`. The position is the requirement: **before the channel is
+      // resolved**, so a banned user gets one answer for every channel id — real,
+      // foreign or invented. Put it after the channel read and the refusal for a
+      // channel that exists differs from the refusal for one that does not, and a
+      // banned user can enumerate channel ids.
+      //
+      // ONLY FOR AN ATTRIBUTED SEND. A key-authenticated REST send carries no user, so
+      // there is nobody to be banned; the tenant acting for itself is not a banned
+      // user's send by proxy, because the tenant is who bans.
+      if (userId !== undefined) {
+        const [sender] = await tx
+          .select({ bannedAt: users.bannedAt })
+          .from(users)
+          .where(
+            and(eq(users.id, userId), eq(users.environmentId, this.environmentId)),
+          )
+          .limit(1);
+        if (sender?.bannedAt != null) throw new UserBannedError(userId);
+      }
+
       const [channel] = await tx
         .select({
           id: channels.id,
@@ -3153,9 +3741,27 @@
       }
 
       // The sequence is spent only by a message that actually landed.
+      //
+      // AND `lastActivityAt` MOVES IN THE SAME STATEMENT (chapter 3.15, FR-014).
+      // The listing orders a user's channels by their most recent activity, and
+      // FR-014's answer to what that means is: a message. Not a join, not a
+      // rename, not an archive — a column that moved for those would order by
+      // something its own name does not say, which is what T108 tests.
+      //
+      // ONE STATEMENT AND NO NEW TRANSACTION. The write path already updates this
+      // row here, so the column costs an extra assignment rather than an extra
+      // round trip. It also lands on the INSERTED branch only, beside the
+      // sequence: a recognised idempotent retry returned above without reaching
+      // this line, which is the behaviour the ordering wants — a duplicate send
+      // is not new activity.
+      //
+      // `createdAt` FROM THE ROW, NOT `now()`. The message carries a timestamp
+      // the database assigned; reading the clock a second time here would let the
+      // ordering key and the message it orders by disagree by microseconds, and
+      // the cursor is keyed on this column.
       await tx
         .update(channels)
-        .set({ lastSequence: seq })
+        .set({ lastSequence: seq, lastActivityAt: inserted[0]!.createdAt })
         .where(eq(channels.id, channel.id));
 
       const createdAt = toIso(inserted[0]!.createdAt);

@@ -62,7 +62,7 @@
     const channel = await repoA.getChannelByExternalId("support");
     expect(await repoA.addMember(channel!.id, user!.id)).toBe("added");
     // Asked twice is a SUCCESS and not a failure, and telling those apart is
-    // what chapter 3.12 changed here: the endpoint over this call has to be
+    // what chapter 3.13 changed here: the endpoint over this call has to be
     // idempotent, and a unique violation reached the wire as `internal_error`.
     expect(await repoA.addMember(channel!.id, user!.id)).toBe("already_a_member");
     // B holds A's REAL ids — and still cannot write or read through them. The
@@ -280,3 +280,222 @@
     expect(await repoA.memberRole(channel.id, user.id)).toBe("owner");
   });
 });
+
+// ── T113: THE TIE AT A PAGE BOUNDARY (chapter 3.15, FR-013) ───────────────────
+//
+// HERE AND NOT IN `users.itest.ts`, because constructing the tie takes a raw UPDATE:
+// `last_activity_at` is written from the message's `created_at`, `now()` is the
+// transaction timestamp, and every send is its own transaction — so two channels
+// cannot be made to share the value through the API. This suite is on the
+// driver-exempt list (the layer under test IS the query layer); the route suite is
+// not, and adding a setter to the repository so it could reach one would have put a
+// method in production code whose only caller is a test.
+describe("the listing's keyset survives a shared last_activity_at (chapter 3.15)", () => {
+  it("returns each tied channel exactly once across pages", async () => {
+    const user = await repoA.createUser("tie-lister", "Tie Lister");
+    const shared = new Date("2026-08-20T12:00:00.000Z");
+    const ids: string[] = [];
+    for (const label of ["tie-a", "tie-b", "tie-c"]) {
+      const c = await repoA.createChannel(label, "public");
+      await repoA.addMember(c.id, user.id);
+      ids.push(c.id);
+    }
+    // All three at the same instant, to the millisecond.
+    await db.execute(
+      sql`UPDATE channels SET last_activity_at = ${shared} WHERE id IN (${sql.join(
+        ids.map((id) => sql`${id}`),
+        sql`, `,
+      )})`,
+    );
+
+    const seen: string[] = [];
+    let after: { activityAt: Date; id: string } | undefined;
+    for (let page = 0; page < 6; page++) {
+      const { rows, nextCursor } = await repoA.listChannelsForUser(user.id, {
+        limit: 1,
+        ...(after === undefined ? {} : { after }),
+      });
+      seen.push(...rows.map((r) => r.external_id));
+      if (nextCursor === null) break;
+      after = nextCursor;
+    }
+
+    // THREE ROWS, ONCE EACH. A keyset on the timestamp alone would either skip the
+    // second tied row (using `<`) or return the first one for ever (using `<=`);
+    // both failures are invisible without a tie in the fixture.
+    expect(seen).toHaveLength(3);
+    expect(new Set(seen)).toEqual(new Set(["tie-a", "tie-b", "tie-c"]));
+  });
+
+  it("orders tied channels by id descending, so the order is total", async () => {
+    const user = await repoA.createUser("tie-order", "Tie Order");
+    const shared = new Date("2026-08-19T12:00:00.000Z");
+    const made: string[] = [];
+    for (const label of ["order-a", "order-b"]) {
+      const c = await repoA.createChannel(label, "public");
+      await repoA.addMember(c.id, user.id);
+      made.push(c.id);
+    }
+    await db.execute(
+      sql`UPDATE channels SET last_activity_at = ${shared} WHERE id IN (${sql.join(
+        made.map((id) => sql`${id}`),
+        sql`, `,
+      )})`,
+    );
+    const { rows } = await repoA.listChannelsForUser(user.id, { limit: 10 });
+    const tied = rows.filter((r) => r.external_id.startsWith("order-"));
+    // Whichever uuid sorts higher comes first — the point is that SOME total order
+    // exists and the query commits to it, not which id wins.
+    const expected = [...made].sort().reverse();
+    expect(tied.map((r) => r.id)).toEqual(expected);
+  });
+});
+
+// ── THE TOMBSTONE, AND THE CLAMP (chapter 3.15, FR-016, FR-019) ───────────────
+//
+// BOTH STATES ARE UNREACHABLE THROUGH THE API, for different reasons, and both are
+// constructed here because this suite may hold raw SQL.
+//
+// FR-MSG-08 — "deleting a message shall replace its content with a tombstone retaining
+// sequence number, author, timestamps" — IS NOT IMPLEMENTED. `messages.deleted_at` and a
+// null `text` are in the schema, `backfill.controller` passes `text` straight through so
+// a null already reaches the wire, and NOTHING IN THE PLATFORM WRITES EITHER. The
+// tombstone is a live reader with no writer, which is the reverse of the dead columns
+// this feature is otherwise about. The listing's rule for it is implemented and tested
+// now so the day FR-MSG-08's chapter ships, the count and the preview already agree.
+describe("the listing's tombstone rule and its clamp (chapter 3.15)", () => {
+  it("reports a tombstoned last message with a null text, and still counts it", async () => {
+    const user = await repoA.createUser("tomb-reader", "Tomb Reader");
+    const channel = await repoA.createChannel("tombstoned", "public");
+    await repoA.addMember(channel.id, user.id);
+    await repoA.sendMessage(channel.id, { text: "kept", userId: user.id });
+    const last = await repoA.sendMessage(channel.id, { text: "doomed", userId: user.id });
+
+    // What FR-MSG-08 will do when it exists.
+    await db.execute(
+      sql`UPDATE messages SET text = NULL, deleted_at = now() WHERE id = ${last.id}`,
+    );
+
+    const { rows } = await repoA.listChannelsForUser(user.id, { limit: 10 });
+    const row = rows.find((r) => r.external_id === "tombstoned")!;
+
+    // THE ROW AT `last_sequence`, NOT THE LAST ROW WITH TEXT. Walking back would be a
+    // second query per channel and would disagree with the count beside it.
+    expect(row.last_message?.sequence).toBe(last.seq);
+    expect(row.last_message?.text).toBeNull();
+    expect(row.last_message?.user).not.toBeNull();
+
+    // AND THE APPROXIMATION FR-016 REQUIRES BE STATED (T124): a deleted message still
+    // counts as one unread, because a tombstone keeps its sequence and therefore its
+    // place in the arithmetic. Counting rows instead would make a deleted message stop
+    // being unread, at 10x the cost on the query a client runs to render its first
+    // screen.
+    expect(row.unread).toBe(2);
+  });
+
+  it("reports null for a channel that has never had a message", async () => {
+    const user = await repoA.createUser("empty-reader", "Empty Reader");
+    const channel = await repoA.createChannel("never-used", "public");
+    await repoA.addMember(channel.id, user.id);
+    const { rows } = await repoA.listChannelsForUser(user.id, { limit: 10 });
+    const row = rows.find((r) => r.external_id === "never-used")!;
+    // DISTINCT FROM A TOMBSTONE. `last_sequence` is 0, no row carries sequence 0, so the
+    // subquery finds nothing — where a tombstone IS a row and reports itself with a null
+    // text. A client can tell "no messages" from "the last one was deleted".
+    expect(row.last_message).toBeNull();
+    expect(row.unread).toBe(0);
+  });
+
+  // ── T127: the clamp's arm ───────────────────────────────────────────────────
+  it("clamps a read position above the channel's end to zero rather than negative", async () => {
+    const user = await repoA.createUser("clamp-reader", "Clamp Reader");
+    const channel = await repoA.createChannel("clamped", "public");
+    await repoA.addMember(channel.id, user.id);
+    await repoA.sendMessage(channel.id, { text: "one", userId: user.id });
+
+    // `setReadPosition` REFUSES THIS, which is why the arm needs planting. The clamp is
+    // defence against a bug — a position past `last_sequence` cannot be written and
+    // `last_sequence` never goes backwards — so this is the only way the branch is ever
+    // covered. Chapter 3.12 found three instruments that had never produced output for
+    // exactly this reason.
+    expect(await repoA.setReadPosition(channel.id, user.id, 99)).toBeNull();
+    await db.execute(
+      sql`INSERT INTO read_positions (environment_id, channel_id, user_id, sequence)
+          SELECT environment_id, ${channel.id}, ${user.id}, 99 FROM channels WHERE id = ${channel.id}
+          ON CONFLICT (channel_id, user_id) DO UPDATE SET sequence = 99`,
+    );
+
+    const { rows } = await repoA.listChannelsForUser(user.id, { limit: 10 });
+    expect(rows.find((r) => r.external_id === "clamped")!.unread).toBe(0);
+  });
+});
+
+// ── THE ARMS THE ROUTES CANNOT REACH (chapter 3.15, T174a, T174b) ─────────────
+//
+// Every one of these is a repository function answering "no" to something its own route
+// answers first. `deleteUser` on an id that does not exist, `updateUserProfile` on a
+// deleted row, an upsert that creates without a display name — the service layer 404s or
+// validates ahead of each, so the arm is unreachable THROUGH the API and perfectly
+// reachable one layer down.
+//
+// IN-PROCESS ON PURPOSE (T174b). Five of this feature's tests drive new repository code
+// through the gateway's api CHILD PROCESS, whose coverage is not attributable. Chapter 3.5
+// added six operations to this file the same way and branches went 85.91% → 78.22% on the
+// next run: the instrument was right and the code was untested.
+describe("the repository's own refusals (chapter 3.15)", () => {
+  it("returns false when deleting a user that does not exist", async () => {
+    expect(await repoA.deleteUser("00000000-0000-4000-8000-000000000000")).toBe(false);
+  });
+
+  it("returns null when patching a deleted user's profile", async () => {
+    const doomed = await repoA.createUser("arm-patch-deleted", "Doomed");
+    await repoA.deleteUser(doomed.id);
+    // The route answers 404 before reaching this, because `requireUser` reads the marker.
+    // One layer down, the `isNull(deletedAt)` in the WHERE is what refuses.
+    expect(await repoA.updateUserProfile(doomed.id, { display_name: "nope" })).toBeNull();
+    // And the same for an empty patch, which takes the other branch entirely — no UPDATE
+    // is issued, so the refusal comes from the SELECT.
+    expect(await repoA.updateUserProfile(doomed.id, {})).toBeNull();
+  });
+
+  it("returns null for an empty patch on a user that does not exist", async () => {
+    expect(
+      await repoA.updateUserProfile("00000000-0000-4000-8000-000000000001", {}),
+    ).toBeNull();
+  });
+
+  it("creates through the upsert with no profile fields at all", async () => {
+    const { user, status } = await repoA.upsertUser("arm-bare-upsert", {});
+    expect(status).toBe("created");
+    expect(user.display_name).toBeNull();
+    expect(user.avatar_url).toBeNull();
+    expect(user.metadata).toEqual({});
+  });
+
+  it("updates an avatar through the upsert", async () => {
+    await repoA.upsertUser("arm-avatar", { display_name: "First" });
+    const { user, status } = await repoA.upsertUser("arm-avatar", {
+      avatar_url: "https://cdn.example.com/arm.png",
+    });
+    expect(status).toBe("updated");
+    expect(user.avatar_url).toBe("https://cdn.example.com/arm.png");
+    // The name the entry omitted is untouched, which is the other side of the same branch.
+    expect(user.display_name).toBe("First");
+  });
+
+  it("reports a last message with no author as null", async () => {
+    // AN UNATTRIBUTED MESSAGE, which is what a key-authenticated REST send writes — no
+    // `userId`, by design since chapter 3.3. The listing's `last_message.user` is then
+    // null, and that arm has no route that can reach it: every send through the public
+    // channel route now carries a user, and the internal one resolves theirs.
+    const reader = await repoA.createUser("arm-no-author", "Reader");
+    const channel = await repoA.createChannel("arm-unattributed", "public");
+    await repoA.addMember(channel.id, reader.id);
+    await repoA.sendMessage(channel.id, { text: "from the tenant, not a user" });
+
+    const { rows } = await repoA.listChannelsForUser(reader.id, { limit: 10 });
+    const row = rows.find((r) => r.external_id === "arm-unattributed")!;
+    expect(row.last_message?.text).toBe("from the tenant, not a user");
+    expect(row.last_message?.user).toBeNull();
+  });
+});

import { z } from "zod";
 
// THE USER SURFACE'S BODIES AND QUERIES (chapter 3.15, FR-013, FR-016, FR-017).
//
// `strictObject` throughout, the same as `channels.schema.ts` and
// `messages.schema.ts`: constitution VI rejects unknown fields on a write endpoint,
// and `channels.itest.ts:118` is the assertion that keeps it honest. A caller who
// writes `Limit` instead of `limit` finds out on the first call.
 
/** 4 KB, and FR-USR-03 names that number the way FR-CHN-01 names 8 KB for a channel
 * (chapter 3.15, FR-024). Two bounds, half an order of magnitude apart, and the SRS chose
 * both — but "the SRS says so" is not a reason, so here is the one that holds.
 *
 * **THE BOUND TRACKS ROW CARDINALITY.** Measured on the test lane: 94,144 users against
 * 27,337 channels, and a user belongs to 1 channel on average while a channel holds 10
 * users. Users outnumber channels 3.4:1 here and the ratio only grows — a channel is a
 * conversation a customer creates deliberately, a user row appears for every end user who
 * ever authenticates, implicitly (FR-USR-02). At a million end users, 4 KB each is 4 GB of
 * jsonb that every profile read walks past.
 *
 * The channel's 8 KB buys something the user's does not: channel metadata is where a
 * customer puts routing and configuration for a shared object, read once per conversation.
 * A user's metadata is per-person annotation. Different multipliers, different budgets.
 *
 * Measured on the JSON text, like the channels bound, because that is what the column
 * stores and what the row costs. */
export const USER_METADATA_BYTES = 4 * 1024;
 
const userMetadataSchema = z
  .record(z.string(), z.unknown())
  .refine(
    (value) => Buffer.byteLength(JSON.stringify(value), "utf8") <= USER_METADATA_BYTES,
    { message: `metadata must be at most ${USER_METADATA_BYTES} bytes of JSON` },
  );
 
/** The profile body (chapter 3.15, FR-023, FR-024).
 *
 * A PATCH, so every field is optional — and `strictObject`, so a misspelled one is a
 * refusal. An empty body is accepted and changes nothing: unlike the member-role PATCH,
 * which carries exactly one required field, a profile PATCH with no fields is a coherent
 * request that asks for the current state, and the response is the profile.
 *
 * `avatar_url` IS VALIDATED AS A URL AND NOT AS A STRING. The column has existed since
 * chapter 2.1 with nothing writing it, so this is the first thing that ever decides what
 * belongs in it, and the decision is worth making now rather than after a customer has
 * stored `"none"` in a million rows. `z.url()` refuses a relative path; the field's own
 * name promises a URL.
 *
 * `null` CLEARS, and it is distinct from absent. `{"display_name": null}` removes the
 * name; `{}` leaves it. Both columns are nullable, so the API can express the difference
 * and a PATCH that could only set would leave a customer unable to undo one. */
export const userProfileBodySchema = z.strictObject({
  display_name: z.string().min(1).max(255).nullable().optional(),
  avatar_url: z.string().url().max(2048).nullable().optional(),
  metadata: userMetadataSchema.optional(),
});
 
export type UserProfileBody = z.infer<typeof userProfileBodySchema>;
 
/** An entry in the bulk upsert (chapter 3.15, FR-025, FR-026).
 *
 * THE PROFILE FIELDS, NOT JUST AN ID. FR-026 says an entry naming an existing user
 * **updates** it, so the entry carries what there is to update. An entry that was only an
 * external id could not distinguish "create this user" from "update nothing about them".
 *
 * `strictObject`, and the same 4 KB metadata bound and URL validation the single PATCH
 * uses — one schema fragment, so the two routes cannot drift into accepting different
 * things for the same column. */
export const upsertUserEntrySchema = z.strictObject({
  external_id: z.string().min(1).max(255),
  display_name: z.string().min(1).max(255).nullable().optional(),
  avatar_url: z.string().url().max(2048).nullable().optional(),
  metadata: userMetadataSchema.optional(),
});
 
/** FR-025's bound: 100 in one request, and `field: "users"` on 101.
 *
 * THE SAME 100 AS THE MEMBER-ADD AND THE REMOVAL, for the same reason: all three are "how
 * much a customer's server may hand over in one call", and three different ceilings would
 * be three numbers to remember for one idea.
 *
 * THE FIELD IS `users` AND THE CHANNEL ROUTES' IS `user_ids`, which is a real
 * inconsistency and the shipped name wins on the routes that shipped. This route is new,
 * so it takes the name that describes what it carries — these are whole user records, not
 * a list of ids. */
export const upsertUsersBodySchema = z.strictObject({
  users: z.array(upsertUserEntrySchema).min(1).max(100),
});
 
export type UpsertUsersBody = z.infer<typeof upsertUsersBodySchema>;
export type UpsertUserEntry = z.infer<typeof upsertUserEntrySchema>;
 
/** FR-013's page bound: the same 100 as the member-add and the upsert.
 *
 * ONE NUMBER FOR THE CONCEPT, not three that happen to agree. A page of channels, a
 * batch of members and a batch of users are all "how much a customer's server may
 * ask for in one request", and three different ceilings would be three things to
 * remember. */
export const LISTING_LIMIT_MAX = 100;
const LISTING_LIMIT_DEFAULT = 25;
 
/** The cursor is opaque to the client and a keyset to us: base64 of the JSON pair
 * `(last_activity_at, id)`.
 *
 * DECODED HERE AND NOT IN THE SERVICE, because a malformed cursor is a validation
 * failure with `field: "cursor"` — the shape `ZodValidationPipe` already produces
 * (chapter 3.14 gave every validation error its field). Decoding it downstream would
 * make it a 500 or a hand-rolled 400 that names nothing.
 *
 * OPAQUE IS NOT SECURITY. Base64 of JSON is readable by anyone who wants to read it;
 * what opacity buys is that the pair is ours to change without breaking a client that
 * treated the string as a token. A client that decodes it and constructs its own is
 * outside the contract. */
const cursorPayload = z.strictObject({
  a: z.string().min(1),
  id: z.string().uuid(),
});
 
export function encodeCursor(activityAt: string, id: string): string {
  return Buffer.from(JSON.stringify({ a: activityAt, id }), "utf8").toString(
    "base64url",
  );
}
 
export const listingQuerySchema = z.strictObject({
  limit: z.coerce
    .number()
    .int()
    .min(1)
    .max(LISTING_LIMIT_MAX)
    .default(LISTING_LIMIT_DEFAULT),
  cursor: z
    .string()
    .optional()
    .transform((raw, ctx) => {
      if (raw === undefined) return undefined;
      let parsed: unknown;
      try {
        parsed = JSON.parse(Buffer.from(raw, "base64url").toString("utf8"));
      } catch {
        ctx.addIssue({ code: "custom", message: "cursor is not a valid cursor" });
        return z.NEVER;
      }
      const shape = cursorPayload.safeParse(parsed);
      if (!shape.success) {
        ctx.addIssue({ code: "custom", message: "cursor is not a valid cursor" });
        return z.NEVER;
      }
      const activityAt = new Date(shape.data.a);
      if (Number.isNaN(activityAt.getTime())) {
        ctx.addIssue({ code: "custom", message: "cursor is not a valid cursor" });
        return z.NEVER;
      }
      return { activityAt, id: shape.data.id };
    }),
});
 
export type ListingQuery = z.infer<typeof listingQuerySchema>;
 
/** The read-position body (chapter 3.15, FR-017).
 *
 * `strictObject` and a required non-negative integer. Zero is legal and means "I have
 * read nothing", which is also what a missing row means — a client that wants to reset
 * writes 0 rather than deleting anything. */
export const readPositionBodySchema = z.strictObject({
  sequence: z.number().int().min(0),
});
 
export type ReadPositionBody = z.infer<typeof readPositionBodySchema>;

import { Module, Scope } from "@nestjs/common";
import { REQUEST } from "@nestjs/core";
 
import { AuthModule } from "../auth/auth.module";
import { createDb, createPool, type Db } from "../db/client";
import { Repository } from "../db/repository";
import { UsersController } from "./users.controller";
import { UsersService } from "./users.service";
import type { RequestWithTenant } from "../messages/request-with-tenant";
 
// The channels module's shape, for the channels module's reasons (chapter 3.15).
//
// A SEPARATE MODULE AND NOT A ROUTE ON `ChannelsController`. Five SRS clauses need
// routes whose subject is a user — the listing, the profile read, the upsert, the
// deletion, the ban — and hanging them off the channels controller would put user
// lifecycle behind a channel path. `POST /v1/channels/users` is a sentence about
// nothing.
@Module({
  imports: [AuthModule],
  controllers: [UsersController],
  providers: [
    {
      provide: "DB",
      useFactory: (): Db => createDb(createPool()),
      scope: Scope.DEFAULT,
    },
    {
      provide: Repository,
      scope: Scope.REQUEST,
      inject: ["DB", REQUEST],
      useFactory: (db: Db, req: RequestWithTenant) =>
        new Repository(db, req.principal?.environmentId ?? ""),
    },
    UsersService,
  ],
})
export class UsersModule {}

@@ -11,6 +11,11 @@
 import { InternalModule } from "./internal/internal.module";
 import { MessagesModule } from "./messages/messages.module";
 import { ChannelsModule } from "./channels/channels.module";
+// Chapter 3.15. Registered here for the reason `ChannelsModule` is: without this
+// line the module is compiled, exported, imported by nothing, and none of the user
+// routes exist. The file appeared in no task until an enumeration asked which
+// chapter fences it.
+import { UsersModule } from "./users/users.module";
 import { ConsumerModule } from "./consumer/consumer.module";
 import { NotificationsModule } from "./notifications/notifications.module";
 import { QuotasModule } from "./quotas/quotas.module";
@@ -32,6 +37,7 @@
     AuthModule,
     MessagesModule,
     ChannelsModule,
+    UsersModule,
     InternalModule,
     TenancyModule,
     OutboxModule,

import { HttpStatus, Injectable, NotFoundException } from "@nestjs/common";
 
import { protocolError } from "../protocol-error";
import { Repository, type UserRow } from "../db/repository";
import {
  encodeCursor,
  type ListingQuery,
  type UpsertUsersBody,
  type UserProfileBody,
} from "./users.schema";
 
/** The user surface (chapter 3.15, FR-013 and the clauses after it).
 *
 * EVERY ROUTE HERE NAMES ITS USER IN THE PATH, and the credential is the tenant's.
 * So "the caller" on these routes is the application, never the user named — a
 * distinction four documents got wrong for twelve analysis passes, because FR-015's
 * "a channel the caller is not a member of MUST NOT appear in their listing" is
 * vacuous when the caller is an application key: a key is a member of nothing and an
 * empty list satisfied it. The requirement is about the user the PATH names. */
@Injectable()
export class UsersService {
  constructor(private readonly repo: Repository) {}
 
  /** A deleted user is a 404 on every route that names them (FR-017).
   *
   * The row survives deletion — a message keeps its author, and `toFrame` drops a
   * senderless row, so "authored by a deleted user" and "authored by nobody" are
   * different states and only one of them is the clause. The marker is what makes the
   * row invisible to the API without making the message anonymous. */
  private async requireUser(externalId: string): Promise<UserRow> {
    const user = await this.repo.getUserByExternalId(externalId);
    if (!user || user.deleted_at !== null) {
      throw new NotFoundException("user not found");
    }
    return user;
  }
 
  /** The profile as the API shapes it (FR-023).
   *
   * `deleted_at` IS NOT ON THE WIRE. It is read on every route that names a user and it
   * decides a 404; a client never sees a deleted user at all, so returning the marker
   * would be returning a field whose only possible value is null. */
  private static profile(user: UserRow): {
    external_id: string;
    display_name: string | null;
    avatar_url: string | null;
    metadata: Record<string, unknown>;
  } {
    return {
      external_id: user.external_id,
      display_name: user.display_name,
      avatar_url: user.avatar_url,
      metadata: user.metadata,
    };
  }
 
  async readProfile(externalId: string): Promise<ReturnType<typeof UsersService.profile>> {
    return UsersService.profile(await this.requireUser(externalId));
  }
 
  async updateProfile(
    externalId: string,
    patch: UserProfileBody,
  ): Promise<ReturnType<typeof UsersService.profile>> {
    const user = await this.requireUser(externalId);
    const updated = await this.repo.updateUserProfile(user.id, patch);
    // `null` here means the row went away between the two statements — a deletion racing
    // a patch. 404 is the same answer the read gives, which is the answer that does not
    // depend on which of the two won.
    if (updated === null) throw new NotFoundException("user not found");
    return UsersService.profile(updated);
  }
 
  async listChannels(
    externalId: string,
    query: ListingQuery,
  ): Promise<{
    data: Array<Record<string, unknown>>;
    next_cursor: string | null;
  }> {
    const user = await this.requireUser(externalId);
    const { rows, nextCursor } = await this.repo.listChannelsForUser(user.id, {
      limit: query.limit,
      ...(query.cursor === undefined ? {} : { after: query.cursor }),
    });
    return {
      data: rows.map((r) => ({
        // BOTH IDS, the shape `POST /v1/channels` already returns. `contracts/listing.md`
        // showed `"id": "c_support"` — an external id under the name `id` — which would
        // have made `id` mean the uuid on one route and the customer's own string on
        // another, in one API. The contract is corrected; the create route's shape wins
        // because it shipped.
        id: r.id,
        external_id: r.external_id,
        type: r.type,
        name: r.name,
        role: r.role,
        archived_at: r.archived_at,
        unread: r.unread,
        last_activity_at: r.last_activity_at,
        last_message: r.last_message,
      })),
      next_cursor:
        nextCursor === null
          ? null
          : encodeCursor(nextCursor.activityAt.toISOString(), nextCursor.id),
    };
  }
 
  /** Record a read position for the user the path names (chapter 3.15, FR-017, FR-018).
   *
   * THE MEMBERSHIP THIS REFUSAL TALKS ABOUT IS THE PATH'S USER, NOT THE CALLER. Under an
   * application credential the caller has no membership at all — it is the tenant — so
   * "the caller is not a member" is a sentence about nothing on this route. The
   * authorization table's member and non-member columns said nothing for this row until
   * an analysis pass noticed that, and the same mistake sat in five other places.
   *
   * AND THIS IS `not_a_member`'s ONLY EMITTER IN THE WHOLE FEATURE. A read position is
   * per-member state keyed by channel and user, and removal deletes the row with the
   * membership, so refusing a non-member here is the rule the rest of the table keeps.
   * Everywhere else a private channel answers the not-found envelope instead, because a
   * 403 would announce that the channel exists.
   *
   * SO THE ORDER MATTERS: visibility first, then membership. A private channel the user
   * is not in answers 404 — indistinguishable from a channel that does not exist. A
   * PUBLIC channel they are not in answers 403 `not_a_member`, which reveals only that a
   * public channel exists, and a public channel is readable by any user of the tenant
   * anyway. */
  async setReadPosition(
    externalId: string,
    channelId: string,
    sequence: number,
  ): Promise<{ sequence: number }> {
    const user = await this.requireUser(externalId);
 
    // Visibility for THE PATH'S USER, which is what makes the two refusals different.
    if (!(await this.repo.channelVisibleTo(channelId, user.id))) {
      throw new NotFoundException("channel not found");
    }
    if (!(await this.repo.isMember(channelId, user.id))) {
      throw protocolError(
        "not_a_member",
        "the user is not a member of this channel",
        HttpStatus.FORBIDDEN,
      );
    }
 
    const written = await this.repo.setReadPosition(channelId, user.id, sequence);
    if (written === null) {
      throw protocolError(
        "invalid_request",
        "sequence is past the channel's last message",
        HttpStatus.BAD_REQUEST,
        "sequence",
      );
    }
    return { sequence: written.sequence };
  }
 
  /** Up to 100 users in one call, reported per entry (chapter 3.15, FR-025, FR-026).
   *
   * SEQUENTIAL AND NOT A SINGLE MULTI-ROW STATEMENT. Each entry is its own upsert because
   * each carries its own partial profile: a bulk `INSERT ... ON CONFLICT DO UPDATE` has one
   * `SET` clause for every row, so "leave display_name alone for entry 3 and set it for
   * entry 7" cannot be expressed. 100 round trips inside one request is the cost of
   * FR-026's per-entry semantics, and the bound is what keeps it bounded.
   *
   * NO TRANSACTION AROUND THE BATCH, deliberately. The result array reports per entry, so a
   * caller learns exactly which entries landed; wrapping the batch would turn one bad entry
   * into a hundred silent non-writes and the array would be a lie. Validation already
   * rejected the whole body before any write, so what remains here are failures the
   * database raises, which per-entry reporting is the right shape for.
   */
  async upsertUsers(body: UpsertUsersBody): Promise<{
    data: Array<{
      external_id: string;
      status: "created" | "updated" | "revived";
      display_name: string | null;
      avatar_url: string | null;
      metadata: Record<string, unknown>;
    }>;
  }> {
    const data = [];
    for (const entry of body.users) {
      const { external_id, ...profile } = entry;
      const { user, status } = await this.repo.upsertUser(external_id, profile);
      data.push({
        external_id: user.external_id,
        status,
        display_name: user.display_name,
        avatar_url: user.avatar_url,
        metadata: user.metadata,
      });
    }
    return { data };
  }
 
  /** Delete a user (chapter 3.15, FR-027 to FR-029).
   *
   * IDEMPOTENT AT 200 AND 404 FOR A USER WHO NEVER EXISTED. `requireUser` cannot be used
   * here — it 404s a user who is already deleted, and deleting twice is the ordinary
   * outcome of a customer's retry after a timeout. So the row is read without the
   * liveness filter, and only "no row at all" is a 404. */
  async deleteUser(externalId: string): Promise<{ external_id: string; deleted: true }> {
    const user = await this.repo.getUserByExternalId(externalId);
    if (!user) throw new NotFoundException("user not found");
    await this.repo.deleteUser(user.id);
    return { external_id: externalId, deleted: true };
  }
 
  /** Ban and unban, tenant-wide (chapter 3.15, FR-031, FR-032).
   *
   * BOTH IDEMPOTENT AND BOTH 200. Banning a banned user and unbanning an unbanned one
   * are the ordinary outcomes of a retry, and the caller's intent is satisfied either
   * way. A 409 here would make a customer's reconciliation loop — "ensure these users
   * are banned" — have to distinguish success from success.
   *
   * A DELETED USER CANNOT BE BANNED, because `requireUser` 404s them. They already
   * cannot connect: the session route resolves the user and a deleted row has no
   * channels, and every route naming them answers 404. Banning one would be a state with
   * no observable difference.
   */
  async setBanned(externalId: string, banned: boolean): Promise<{ external_id: string; banned: boolean }> {
    const user = await this.requireUser(externalId);
    if (banned) await this.repo.banUser(user.id);
    else await this.repo.unbanUser(user.id);
    return { external_id: externalId, banned };
  }
}

import {
  Body,
  Controller,
  Delete,
  Get,
  HttpCode,
  Param,
  Patch,
  Post,
  Put,
  Query,
  UseGuards,
} from "@nestjs/common";
 
import { Accepts, CredentialGuard } from "../auth/credential.guard";
import { ZodValidationPipe } from "../messages/zod-validation.pipe";
import {
  listingQuerySchema,
  readPositionBodySchema,
  upsertUsersBodySchema,
  userProfileBodySchema,
  type ListingQuery,
  type ReadPositionBody,
  type UpsertUsersBody,
  type UserProfileBody,
} from "./users.schema";
import { UsersService } from "./users.service";
 
/** The user surface (chapter 3.15).
 *
 * `@Accepts("application")` AT THE CLASS LEVEL. Every route here is the tenant
 * acting on a user it names in the path — a customer's server listing a user's
 * channels, reading their profile, upserting them, banning them. A user token on
 * these routes would be a user acting on themselves through a path that says who
 * they are, which is a different route shape and not one the SRS asks for.
 *
 * Declared rather than defaulted, because chapter 3.15 found the cost of leaving it
 * out: `MessagesController` declared no `@Accepts`, the guard fell back to accepting
 * either class, and the membership check behind it was gated on a user id the public
 * route never supplied. */
@Controller("v1/users")
@UseGuards(CredentialGuard)
@Accepts("application")
export class UsersController {
  constructor(private readonly users: UsersService) {}
 
  @Get(":externalId/channels")
  async listChannels(
    @Param("externalId") externalId: string,
    @Query(new ZodValidationPipe(listingQuerySchema)) query: ListingQuery,
  ): Promise<{ data: Array<Record<string, unknown>>; next_cursor: string | null }> {
    return this.users.listChannels(externalId, query);
  }
 
  /** A read position for the user the path names (chapter 3.15, FR-017).
   *
   * `@Accepts("application", "user")` AT THE METHOD LEVEL, and it is the only route on
   * this controller that takes a user token: a user records their own position, and the
   * tenant records one on behalf of the user it names. Method-level wins over the
   * class-level `@Accepts("application")` because the guard resolves
   * `[handler, class]` in that order.
   *
   * `:channelId` IS THE UUID, like every other channel route in this API.
   * `contracts/listing.md` writes it `:channelExternalId`, and that file says of itself
   * that its paths are written with the customer's identifiers in place while the router
   * names channel parameters `:channelId` — a classification entry copied from it
   * verbatim will not match a derived target. */
  @Put(":externalId/channels/:channelId/read")
  @Accepts("application", "user")
  async setReadPosition(
    @Param("externalId") externalId: string,
    @Param("channelId") channelId: string,
    @Body(new ZodValidationPipe(readPositionBodySchema)) body: ReadPositionBody,
  ): Promise<{ sequence: number }> {
    return this.users.setReadPosition(externalId, channelId, body.sequence);
  }
 
  /** The profile, read and written (chapter 3.15, FR-023, FR-024).
   *
   * TWO OF ITS THREE FIELDS HAVE NEVER BEEN WRITTEN BY ANY ROUTE. `users.avatar_url` and
   * `users.metadata` have been in the schema since chapter 2.1 with no reference outside
   * tests. This pair of routes is what the feature's headline was about.
   *
   * `:externalId` LAST IN THE FILE AND NOT FIRST. Nest matches routes in declaration
   * order, so `GET :externalId` declared above `GET :externalId/channels` would still be
   * fine — the paths differ in segment count — but keeping the more specific route first
   * is the habit that stops the next route from shadowing something. */
  @Get(":externalId")
  async readProfile(@Param("externalId") externalId: string) {
    return this.users.readProfile(externalId);
  }
 
  @Patch(":externalId")
  async updateProfile(
    @Param("externalId") externalId: string,
    @Body(new ZodValidationPipe(userProfileBodySchema)) body: UserProfileBody,
  ) {
    return this.users.updateProfile(externalId, body);
  }
 
  /** Up to 100 users in one call (chapter 3.15, FR-025).
   *
   * DECLARED BEFORE THE `:externalId` ROUTES. Nest matches in declaration order and
   * `POST /v1/users` and `PATCH /v1/users/:externalId` differ in both method and segment
   * count, so nothing shadows anything here — but a bare-path route below a parameterised
   * one is the shape that eventually does, and the habit costs nothing.
   *
   * 200 AND NOT 201, because the array reports created, updated and revived per entry.
   * One status code for a mixed outcome would have to pick a lie. */
  @Post()
  @HttpCode(200)
  async upsertUsers(
    @Body(new ZodValidationPipe(upsertUsersBodySchema)) body: UpsertUsersBody,
  ) {
    return this.users.upsertUsers(body);
  }
 
  /** Delete a user, keeping their row and their messages (chapter 3.15, FR-027). */
  @Delete(":externalId")
  async deleteUser(@Param("externalId") externalId: string) {
    return this.users.deleteUser(externalId);
  }
 
  /** The ban pair (chapter 3.15, FR-031).
   *
   * TWO ROUTES ON ONE PATH RATHER THAN A `PATCH` WITH A BOOLEAN. `POST …/ban` and
   * `DELETE …/ban` say what they do in the method, and a customer's reconciliation loop
   * can issue either without reading the current state first. A `{"banned": false}` body
   * would be a second way to spell the same thing.
   *
   * `@HttpCode(200)` on the POST for the reason the upsert has it: nothing is created,
   * and banning an already-banned user is a 200 too. */
  @Post(":externalId/ban")
  @HttpCode(200)
  async ban(@Param("externalId") externalId: string) {
    return this.users.setBanned(externalId, true);
  }
 
  @Delete(":externalId/ban")
  async unban(@Param("externalId") externalId: string) {
    return this.users.setBanned(externalId, false);
  }
}

@@ -126,6 +126,10 @@
   waitFor: <T = Record<string, unknown>>(type: string, timeoutMs?: number) => Promise<T>;
   frames: () => { type: string }[];
   opened: () => Promise<void>;
+  /** The close code, once it arrives (chapter 3.15, T151). Added because a refusal at
+   * connect IS a close code — the frame is only the explanation — and asserting the
+   * frame alone would pass whether the socket closed 4003, 4001 or not at all. */
+  closedWith: (timeoutMs?: number) => Promise<number>;
 }
 
 function read(socket: WebSocket): Reader {
@@ -160,7 +164,16 @@
     }
   };
 
-  return { socket, waitFor, frames: () => [...buffer], opened };
+  const closedWith = async (timeoutMs = 5_000): Promise<number> => {
+    const deadline = Date.now() + timeoutMs;
+    for (;;) {
+      if (closed !== null) return closed;
+      if (Date.now() > deadline) throw new Error("socket never closed");
+      await new Promise((r) => setTimeout(r, 25));
+    }
+  };
+
+  return { socket, waitFor, frames: () => [...buffer], opened, closedWith };
 }
 
 /** Absence needs a deadline rather than a race, so three of the four attacks below
@@ -420,6 +433,194 @@
     expect(client.frames().filter((f) => f.type === "message.created")).toEqual([]);
   });
 
+  // ── T151, T153: THE BAN AT THE DOOR, AND WHAT IT DOES TO AN OPEN SOCKET ───
+  //
+  // FR-032 asks what a ban does to a connection that is ALREADY OPEN, and T153 named two
+  // candidate answers — "closed at the next heartbeat" and "closed immediately" — noting
+  // they differ in whether the gateway has to be told.
+  //
+  // **THE ANSWER IS NEITHER, AND IT IS ALREADY BUILT.** A banned socket stops being able
+  // to SEND the instant the ban lands, because a socket send goes through the api's
+  // `/internal/messages`, which is the same repository path the ban check sits at the top
+  // of. It keeps RECEIVING until it closes for any other reason, because delivery never
+  // asks the api anything.
+  //
+  // That is not a compromise invented here — it is the shape chapter 3.2 already chose
+  // for an expired token, whose comment in `session.ts` says it in as many words: "the
+  // socket is still up and still RECEIVES, because delivery never asks the api anything.
+  // Writing does."
+  //
+  // WHY NOT CLOSE IT. Closing an open socket on ban needs the api to tell the gateway,
+  // which is new plumbing on the fan-out for an event that happens rarely; re-checking at
+  // each heartbeat needs an api call on every ping of every connection. Both buy the
+  // difference between "cannot speak" and "cannot listen", for a user the tenant has
+  // already silenced.
+  it("refuses a banned user at connect with 4003, not 4001", async () => {
+    await tenants.attacker.banSelf();
+    try {
+      const client = connect(tenants.attacker.token);
+      // The error frame arrives first, because a close reason is a short string.
+      const err = await client.waitFor<{ payload: { code: string } }>("error");
+      expect(err.payload.code).toBe("user_banned");
+      const closed = await client.closedWith();
+      // 4003 AND NOT 4001. The token is valid and the user is refused; 4001 would send a
+      // client round the re-authentication loop for ever.
+      expect(closed).toBe(4003);
+    } finally {
+      await tenants.attacker.unbanSelf();
+    }
+  });
+
+  it("stops an already-open socket from sending, and keeps delivering to it", async () => {
+    const client = connect(tenants.victim.token);
+    await client.waitFor("connection.ack");
+
+    await tenants.victim.banSelf();
+    try {
+      // SENDING STOPS. The frame is accepted by the gateway and refused by the api, so
+      // the client is told rather than disconnected.
+      client.socket.send(
+        JSON.stringify({
+          type: "message.send",
+          payload: {
+            idem_key: randomUUID(),
+            channel: tenants.victim.channelId,
+            text: "banned mid-connection",
+          },
+        }),
+      );
+      const err = await client.waitFor<{ payload: { code: string } }>("error");
+      expect(err.payload.code).toBe("user_banned");
+
+      // AND THE SOCKET IS STILL OPEN. Stated as an assertion because it is the half of
+      // FR-032 a reader will not guess: a ban silences a connection, it does not sever
+      // it, and the next reconnect is where the door closes.
+      expect(client.socket.readyState).toBe(1);
+    } finally {
+      await tenants.victim.unbanSelf();
+    }
+  });
+
+  // ── T144: A DELETED USER'S MESSAGE STILL REACHES A SOCKET (FR-028) ────────
+  //
+  // THIS IS THE ASSERTION THAT WOULD HAVE CAUGHT `ON DELETE SET NULL`, and the reason
+  // R7 chose to keep the row over satisfying the letter of the clause.
+  //
+  // `backfill.controller`'s `toFrame` turns a row into a frame **or into nothing**, and
+  // one of the two rows it drops is a senderless one — `messageSchema.user` is
+  // `z.string().min(1)`, so a null author cannot be a `message.created` payload at all.
+  // Nulling `messages.user_id` on deletion would therefore preserve every message in
+  // storage and remove it from every reconnecting client, silently, with a sequence gap
+  // as the only trace.
+  //
+  // THE RESUME PATH IS THE ONE THAT CARRIES IT. The backfill runs at connect, from the
+  // client's cursor, which is the only place in this suite where a stored message becomes
+  // a frame — the live fan-out does not reach this suite at all (see T134).
+  it("delivers a deleted user's message on resume, still attributed to them", async () => {
+    // ITS OWN FIXTURE. The first version deleted the shared `victim`, which took that
+    // tenant's membership with it and made the next test's profile PATCH answer 404 —
+    // the same shared-fixture mutation Phase 7 hit twice.
+    const { userExternalId, channelId, seq, witnessToken } =
+      await tenants.victim.seedDeletable();
+
+    const deleted = await fetch(`${api.url}/v1/users/${userExternalId}`, {
+      method: "DELETE",
+      headers: { authorization: `Bearer ${tenants.victim.credential}` },
+    });
+    expect(deleted.status).toBe(200);
+
+    // A REMAINING MEMBER RESUMES. The deletion took the doomed user's own membership, so
+    // their session no longer carries the channel — and the case that matters is that the
+    // message survives for everybody else.
+    const client = connect(witnessToken, `&cursor=${channelId}:0`);
+    const ack = await client.waitFor<{ payload: { cursor: Record<string, number> } }>(
+      "connection.ack",
+    );
+    expect(Object.keys(ack.payload.cursor)).toContain(channelId);
+
+    const mine = await client.waitFor<{ payload: Record<string, unknown> }>(
+      "message.created",
+    );
+    // THE FRAME ARRIVED, and its `user` is the deleted user's external id. Both halves
+    // matter: absent means `toFrame` dropped the row, and a null `user` means
+    // `messageSchema` would have refused it.
+    expect(mine.payload["seq"]).toBe(seq);
+    expect(mine.payload["user"]).toBe(userExternalId);
+    expect(mine.payload["text"]).toBe("sent before the deletion");
+  });
+
+  // ── T134: THE PROFILE IS STORED AND THE WIRE DID NOT MOVE ─────────────────
+  //
+  // Chapter 3.15 gives `users.display_name`, `users.avatar_url` and `users.metadata` a
+  // route that writes them and a route that reads them. **None of that reaches a
+  // socket.** `connection.ack` names who you are with a bare external id string, and
+  // `messageSchema` carries `user` the same way — no display name, no avatar, no
+  // metadata.
+  //
+  // ASSERTED RATHER THAN ASSUMED, because "we did not change the protocol" is the claim a
+  // test replaces. A later change that enriched `user` into an object would break every
+  // client parsing frames against the published schema.
+  //
+  // THE MESSAGE HALF IS CHECKED AGAINST THE SCHEMA AND NOT AGAINST A LIVE FRAME, because
+  // no `message.created` ever arrives in this suite: `say()` writes through the
+  // repository, the api publishes to no fan-out, and nothing here drains the outbox.
+  // Chapter 3.12 recorded that as its own finding — a REST-sent message reaches no socket,
+  // ever — and `public-surface.itest.ts` is what pins it. Waiting for a frame here is a
+  // 5-second timeout, which is how this test was written the first time.
+  it("keeps the socket's identity a bare external id, whatever the profile holds", async () => {
+    // A full profile written through the public route.
+    const patched = await fetch(`${api.url}/v1/users/${tenants.victim.userExternalId}`, {
+      method: "PATCH",
+      headers: {
+        "content-type": "application/json",
+        authorization: `Bearer ${tenants.victim.credential}`,
+      },
+      body: JSON.stringify({
+        display_name: "A Name On The Wire",
+        avatar_url: "https://cdn.example.com/face.png",
+        metadata: { seen: "by nobody" },
+      }),
+    });
+    expect(patched.status).toBe(200);
+
+    // THE LIVE HALF: the handshake, after the profile exists.
+    const client = connect(tenants.victim.token);
+    const ack = await client.waitFor<{ payload: { user: unknown } }>("connection.ack");
+    expect(typeof ack.payload.user).toBe("string");
+    expect(ack.payload.user).toBe(tenants.victim.userExternalId);
+
+    // THE CONTRACT HALF: the frame union refuses an enriched identity. If somebody widens
+    // `messageSchema.user` to an object, this stops failing — and that is the change this
+    // assertion exists to catch, because it is the one that breaks published clients.
+    const enriched = frameSchema.safeParse({
+      type: "message.created",
+      payload: {
+        id: randomUUID(),
+        channel: tenants.victim.channelId,
+        seq: 1,
+        user: { id: tenants.victim.userExternalId, display_name: "A Name On The Wire" },
+        text: "hello",
+        created_at: new Date().toISOString(),
+      },
+    });
+    expect(enriched.success).toBe(false);
+
+    // And the six keys, exactly: a seventh would also have to be added deliberately.
+    const bare = frameSchema.safeParse({
+      type: "message.created",
+      payload: {
+        id: randomUUID(),
+        channel: tenants.victim.channelId,
+        seq: 1,
+        user: tenants.victim.userExternalId,
+        text: "hello",
+        created_at: new Date().toISOString(),
+        avatar_url: "https://cdn.example.com/face.png",
+      },
+    });
+    expect(bare.success).toBe(false);
+  });
+
   // ── THE SAME-TENANT NON-MEMBER, ON THE SOCKET (chapter 3.15, T087) ─────────
   //
   // The protocol's frame union has exactly one inbound member — `message.send` —

import "reflect-metadata";
 
import { Test } from "@nestjs/testing";
import type { INestApplication } from "@nestjs/common";
import { afterAll, beforeAll, describe, expect, it } from "vitest";
 
import { AppModule } from "../app.module";
import { createDb, createPool, type Db } from "../db/client";
import { mintUserToken } from "../auth/user-token";
import { periodOf } from "../quotas/period";
import {
  createApiKey,
  createEnvironment,
  environmentSigningSecret,
  Repository,
  usageFor,
} from "../db/repository";
 
// THE LISTING, END TO END (chapter 3.15, FR-013 to FR-015, FR-022, SC-007).
//
// Every route in this suite names a user in the path and carries the TENANT's
// credential, so "the caller" here is the application and never the user named. That
// distinction is why FR-015 had to be restated: "a channel the caller is not a member
// of MUST NOT appear in their listing" is vacuous when the caller is an application
// key — a key is a member of nothing and an empty list satisfies it. The requirement
// is about the user the path names, and that is what these tests assert.
 
describe("a user's channel listing", () => {
  let app: INestApplication;
  let url: string;
  let db: Db;
  let credential: string;
  let repo: Repository;
  let member: { id: string };
  /** Three channels with staggered activity, oldest first in creation order. */
  let oldest: string;
  let middle: string;
  let newest: string;
  let notAMember: string;
  let publicNotAMember: string;
  let tokenFor: (subject: string) => Promise<string>;
  let environmentId: string;
 
  beforeAll(async () => {
    db = createDb(createPool());
    const env = await createEnvironment(db, { name: "users-itest" });
    environmentId = env.id;
    repo = new Repository(db, env.id);
    credential = (await createApiKey(db, { environmentId: env.id })).credential;
    member = await repo.createUser("lister", "A Lister");
 
    // ACTIVITY IS ASSERTED BY SENDING, not by writing the column. The listing orders
    // by `last_activity_at` and the write path is what moves it; a fixture that set
    // the column directly would test the ordering against a value no send produced.
    const seed = async (label: string): Promise<string> => {
      const c = await repo.createChannel(label, "public");
      await repo.addMember(c.id, member.id);
      await repo.sendMessage(c.id, { text: `first in ${label}`, userId: member.id });
      return c.id;
    };
    oldest = await seed("oldest");
    middle = await seed("middle");
    newest = await seed("newest");
 
    // A private channel of the same tenant the user is NOT in, and a public one they
    // are not in either. The second is the one worth having: a public channel is
    // readable by any user of the tenant, so the read set and the listing set are
    // different sets and only a test says so.
    notAMember = (await repo.createChannel("private-elsewhere", "private")).id;
    publicNotAMember = (await repo.createChannel("public-elsewhere", "public")).id;
 
    const signingSecret = (await environmentSigningSecret(db, env.id))!.signingSecret;
    tokenFor = async (subject: string) =>
      (
        await mintUserToken(signingSecret, {
          user: subject,
          environmentId: env.id,
          ttlSeconds: 3600,
        })
      ).token;
 
    app = (
      await Test.createTestingModule({ imports: [AppModule] }).compile()
    ).createNestApplication({ logger: false });
    await app.listen(0);
    url = await app.getUrl();
  }, 60_000);
 
  afterAll(async () => {
    await app?.close();
  });
 
  const list = (externalId: string, query = "", key = credential) =>
    fetch(`${url}/v1/users/${externalId}/channels${query}`, {
      headers: { authorization: `Bearer ${key}` },
    });
 
  // ── T112: the ordering (SC-007) ─────────────────────────────────────────────
  it("returns the user's channels, most recently active first", async () => {
    const res = await list("lister");
    expect(res.status).toBe(200);
    const body = (await res.json()) as { data: Array<{ external_id: string }> };
    expect(body.data.map((c) => c.external_id)).toEqual(["newest", "middle", "oldest"]);
  });
 
  it("moves a channel to the front when it takes a message", async () => {
    await repo.sendMessage(oldest, { text: "back from the dead", userId: member.id });
    const body = (await (await list("lister")).json()) as {
      data: Array<{ external_id: string }>;
    };
    expect(body.data.map((c) => c.external_id)).toEqual(["oldest", "newest", "middle"]);
  });
 
  // ── T108: only a message is activity ────────────────────────────────────────
  //
  // THE TASK NAMED THREE NON-MESSAGE WRITES AND THIS PLATFORM HAS TWO. There is no
  // rename: `POST /v1/channels` is idempotent on the external id and its repeat
  // branch returns the existing row WITHOUT writing `name` or `metadata`, and no
  // other route or repository function updates them. So a rename cannot move the
  // column because a rename cannot happen — which is worth stating rather than
  // testing, and is the second task this feature has that named an operation the
  // platform does not have (T087's subscribe frame was the first).
  it("does not move for a join or an archive", async () => {
    const before = (await (await list("lister")).json()) as {
      data: Array<{ external_id: string; last_activity_at: string }>;
    };
    const stamps = new Map(before.data.map((c) => [c.external_id, c.last_activity_at]));
 
    // Two writes to these rows, neither of them a message.
    const joiner = await repo.createUser("joiner", "A Joiner");
    await repo.addMember(middle, joiner.id);
    await repo.archiveChannel(newest);
 
    const after = (await (await list("lister")).json()) as {
      data: Array<{ external_id: string; last_activity_at: string }>;
    };
    for (const c of after.data) {
      expect(c.last_activity_at, `${c.external_id} moved`).toBe(stamps.get(c.external_id));
    }
    // And the order is the order it was.
    expect(after.data.map((c) => c.external_id)).toEqual(before.data.map((c) => c.external_id));
    await repo.unarchiveChannel(newest);
  });
 
  // ── T114: membership is the listing set (FR-015) ─────────────────────────────
  it("omits a private channel the user is not a member of", async () => {
    const body = (await (await list("lister")).json()) as { data: Array<{ external_id: string }> };
    expect(body.data.map((c) => c.external_id)).not.toContain("private-elsewhere");
    expect(notAMember).toBeTruthy();
  });
 
  it("omits a PUBLIC channel the user is not a member of, which they could read by id", async () => {
    // The control for the assertion above: this channel is readable by this tenant's
    // users, so its absence from the listing is a decision and not an accident of
    // visibility. Without this test, "the listing only shows what you can see" would
    // pass and be the wrong rule.
    const readable = await fetch(`${url}/v1/channels/${publicNotAMember}`, {
      headers: { authorization: `Bearer ${credential}` },
    });
    expect(readable.status).toBe(200);
 
    const body = (await (await list("lister")).json()) as { data: Array<{ external_id: string }> };
    expect(body.data.map((c) => c.external_id)).not.toContain("public-elsewhere");
  });
 
  // ── T115: an archived channel appears, with a flag (FR-022) ──────────────────
  it("lists an archived channel and says it is archived", async () => {
    await repo.archiveChannel(middle);
    const body = (await (await list("lister")).json()) as {
      data: Array<{ external_id: string; archived_at: string | null }>;
    };
    const row = body.data.find((c) => c.external_id === "middle");
    expect(row).toBeDefined();
    expect(row?.archived_at).not.toBeNull();
    // Every other channel reports null rather than the field being absent.
    expect(body.data.filter((c) => c.archived_at === null).length).toBe(
      body.data.length - 1,
    );
    await repo.unarchiveChannel(middle);
  });
 
  // ── T116b: the role is in the projection ────────────────────────────────────
  it("returns each channel's role for the user the path names", async () => {
    await repo.setMemberRole(newest, member.id, "moderator");
    const body = (await (await list("lister")).json()) as {
      data: Array<{ external_id: string; role: string }>;
    };
    expect(body.data.find((c) => c.external_id === "newest")?.role).toBe("moderator");
    expect(body.data.find((c) => c.external_id === "oldest")?.role).toBe("member");
    await repo.setMemberRole(newest, member.id, "member");
  });
 
  // ── T113: the cursor ────────────────────────────────────────────────────────
  //
  // THE TIE IS TESTED IN `repository.itest.ts` AND NOT HERE. Two channels sharing a
  // `last_activity_at` cannot be produced through the API: `now()` is the
  // transaction timestamp and every send is its own transaction, so constructing the
  // collision takes a raw UPDATE. `repository.itest.ts` is on the driver-exempt list
  // — the layer under test IS the query layer — and this suite is not. Adding a
  // `setLastActivityAt` to the repository to get around that would have put a method
  // in production code whose only caller is a test, in a feature about columns
  // nothing reads.
  it("pages through every channel exactly once", async () => {
    const seen: string[] = [];
    let cursor: string | null = null;
    let pages = 0;
    do {
      const q = `?limit=2${cursor === null ? "" : `&cursor=${cursor}`}`;
      const body = (await (await list("lister", q)).json()) as {
        data: Array<{ external_id: string }>;
        next_cursor: string | null;
      };
      seen.push(...body.data.map((c) => c.external_id));
      cursor = body.next_cursor;
      pages++;
      expect(pages, "the cursor did not terminate").toBeLessThan(20);
    } while (cursor !== null);
 
    expect(seen.length).toBe(new Set(seen).size);
    expect(new Set(seen)).toEqual(new Set(["oldest", "middle", "newest"]));
    expect(pages).toBeGreaterThan(1);
  });
 
  // ── T117: the cursor's refusals ─────────────────────────────────────────────
  // THREE WAYS A CURSOR CAN BE MALFORMED, and each is its own arm: the base64 does not
  // decode to JSON, the JSON decodes to the wrong shape, or the timestamp inside it is
  // not a date. All three answer identically, which is the point — a client learns "your
  // cursor is not a cursor" and nothing about which internal check caught it — and all
  // three need their own test, because one refusal reaching the wire says nothing about
  // whether the other two paths work.
  it.each([
    ["not JSON at all", "not-a-cursor"],
    [
      "JSON of the wrong shape",
      Buffer.from(JSON.stringify({ nope: 1 }), "utf8").toString("base64url"),
    ],
    [
      "a timestamp that is not a date",
      Buffer.from(
        JSON.stringify({ a: "the day before yesterday", id: "00000000-0000-4000-8000-000000000000" }),
        "utf8",
      ).toString("base64url"),
    ],
  ])("refuses a cursor that is %s with 400 and names the field", async (_what, cursor) => {
    const res = await list("lister", `?cursor=${cursor}`);
    expect(res.status).toBe(400);
    const body = (await res.json()) as { code: string; field?: string };
    expect(body.code).toBe("invalid_request");
    expect(body.field).toBe("cursor");
  });
 
  it("refuses a limit over 100 with 400 and names the field", async () => {
    const res = await list("lister", "?limit=101");
    expect(res.status).toBe(400);
    const body = (await res.json()) as { code: string; field?: string };
    expect(body.code).toBe("invalid_request");
    expect(body.field).toBe("limit");
  });
 
  it("answers a cursor naming another tenant's channel exactly as it answers an invented one", async () => {
    // T117 ASKED FOR 400 HERE AND 400 IS THE LEAK.
    //
    // The task's reason is right and its mechanism inverts it: "anything that
    // distinguishes 'exists elsewhere' from 'malformed' is the leak the suite exists
    // to catch". To answer 400 for a FOREIGN id, the server has to look the id up in
    // the global `channels` table and find it — and then a uuid that exists in
    // another tenant gets a different answer from a uuid that exists nowhere. That is
    // the distinction SC-002 forbids, built on purpose.
    //
    // So the cursor is validated for SHAPE and its id is never resolved. A keyset
    // position does not have to exist: `(last_activity_at, id) < (ts, id)` is a
    // comparison, not a lookup. A foreign id, an invented uuid and the id of a
    // channel deleted since the cursor was minted all name the same position in this
    // tenant's ordering, and all three get the same page.
    //
    // The lookup would also break a real client: a user removed from a channel
    // between pages would find their cursor rejected mid-pagination.
    const other = new Repository(
      db,
      (await createEnvironment(db, { name: "users-itest-foreign" })).id,
    );
    const theirs = await other.createChannel("theirs", "public");
    const at = new Date().toISOString();
    const cursorOf = (id: string) =>
      Buffer.from(JSON.stringify({ a: at, id }), "utf8").toString("base64url");
 
    const foreign = await list("lister", `?cursor=${cursorOf(theirs.id)}`);
    const invented = await list(
      "lister",
      `?cursor=${cursorOf("00000000-0000-4000-8000-000000000000")}`,
    );
    expect(foreign.status).toBe(invented.status);
    expect(await foreign.text()).toBe(await invented.text());
  });
 
  // ── T109c: unknown fields are refused, not ignored ───────────────────────────
  it("refuses an unknown query field rather than ignoring it", async () => {
    const res = await list("lister", "?limit=2&Limit=3");
    expect(res.status).toBe(400);
    expect(((await res.json()) as { code: string }).code).toBe("invalid_request");
  });
 
  // ── T111: a user who does not exist, and one who is deleted ──────────────────
  it("answers 404 for a user this tenant does not have", async () => {
    expect((await list("nobody")).status).toBe(404);
  });
 
  it("answers 404 for a deleted user", async () => {
    const doomed = await repo.createUser("doomed", "Doomed");
    await repo.addMember(oldest, doomed.id);
    expect((await list("doomed")).status).toBe(200);
    await repo.markUserDeleted(doomed.id);
    expect((await list("doomed")).status).toBe(404);
  });
 
  // ══ THE UNREAD COUNT (chapter 3.15, FR-016 to FR-018, SC-008) ═══════════════
 
  const setRead = (user: string, channelId: string, sequence: number, key = credential) =>
    fetch(`${url}/v1/users/${user}/channels/${channelId}/read`, {
      method: "PUT",
      headers: { "content-type": "application/json", authorization: `Bearer ${key}` },
      body: JSON.stringify({ sequence }),
    });
 
  const unreadFor = async (user: string, external: string): Promise<number> => {
    const body = (await (await list(user, "?limit=100")).json()) as {
      data: Array<{ external_id: string; unread: number }>;
    };
    return body.data.find((c) => c.external_id === external)!.unread;
  };
 
  // ── T122: it rises, and it falls to zero (SC-008) ───────────────────────────
  it("rises with each message and falls to zero when the position reaches the end", async () => {
    const c = await repo.createChannel("counting", "public");
    await repo.addMember(c.id, member.id);
    const sender = await repo.createUser("sender", "A Sender");
    await repo.addMember(c.id, sender.id);
 
    expect(await unreadFor("lister", "counting")).toBe(0);
    const first = await repo.sendMessage(c.id, { text: "one", userId: sender.id });
    expect(await unreadFor("lister", "counting")).toBe(1);
    await repo.sendMessage(c.id, { text: "two", userId: sender.id });
    const third = await repo.sendMessage(c.id, { text: "three", userId: sender.id });
    expect(await unreadFor("lister", "counting")).toBe(3);
    expect(first.seq).toBe(1);
 
    const res = await setRead("lister", c.id, third.seq);
    expect(res.status).toBe(200);
    expect(await unreadFor("lister", "counting")).toBe(0);
  });
 
  // ── T123: no row means position zero (FR-017a) ───────────────────────────────
  it("gives a new member the channel's whole history as unread, seeding nothing", async () => {
    const c = await repo.createChannel("pre-existing", "public");
    const author = await repo.createUser("author", "An Author");
    await repo.addMember(c.id, author.id);
    for (const t of ["a", "b", "c", "d"]) {
      await repo.sendMessage(c.id, { text: t, userId: author.id });
    }
    // The member arrives AFTER the history exists.
    const late = await repo.createUser("latecomer", "A Latecomer");
    await repo.addMember(c.id, late.id);
    expect(await unreadFor("latecomer", "pre-existing")).toBe(4);
  });
 
  // ── T123a: the re-added member gets the same answer (T059a, moved here) ──────
  it("gives a re-added member the whole history again, because removal took the position", async () => {
    const c = await repo.createChannel("rejoined", "public");
    const author = await repo.createUser("rejoin-author", "Author");
    await repo.addMember(c.id, author.id);
    const rejoiner = await repo.createUser("rejoiner", "A Rejoiner");
    await repo.addMember(c.id, rejoiner.id);
    await repo.sendMessage(c.id, { text: "one", userId: author.id });
    const two = await repo.sendMessage(c.id, { text: "two", userId: author.id });
    await setRead("rejoiner", c.id, two.seq);
    expect(await unreadFor("rejoiner", "rejoined")).toBe(0);
 
    await repo.removeMembers(c.id, [rejoiner.id]);
    await repo.addMember(c.id, rejoiner.id);
 
    // TWO, NOT ZERO. Removal deleted the read position with the membership, so there
    // is no row, and no row means zero — the same rule a brand-new member gets. The
    // alternative, keeping the position through a removal, would mean a re-added
    // member silently misses everything sent while they were out.
    expect(await unreadFor("rejoiner", "rejoined")).toBe(2);
  });
 
  // ── T126: a sender's own message, and the answer is not the assumed one ──────
  //
  // THE SPEC ASSUMED "a user's own message is read by them" and left the scenario as
  // "whether it counts as unread for its author is stated and tested". Measured: it
  // COUNTS. The write path does not advance the sender's own read position, so a user
  // who sends a message sees their own unread count go to one until they acknowledge it.
  //
  // NOT CHANGED, and the reason is the cost of where it would go. Advancing the position
  // server-side is a second statement on a second table inside the send transaction —
  // the platform's highest-frequency operation, forever, for every attributed message.
  // `last_activity_at` was put in the statement that already updates `channels` for
  // exactly this reason; a read-position upsert has no statement to join.
  //
  // The client pays nothing instead: the send response already carries the sequence it
  // just wrote, so acknowledging is one field it already holds. And the public REST send
  // attributes no user at all, so a server-side advance would work on some sends and not
  // others — the worst of the three options.
  it("does raise the sender's own count until they acknowledge it", async () => {
    const c = await repo.createChannel("own-messages", "public");
    const talker = await repo.createUser("talker", "A Talker");
    await repo.addMember(c.id, talker.id);
    const sent = await repo.sendMessage(c.id, { text: "hello", userId: talker.id });
 
    // ONE, not zero. This is the assertion that would have been hidden by a test that
    // acknowledged first and then checked for zero — which is what this test did until
    // the count was measured rather than assumed.
    expect(await unreadFor("talker", "own-messages")).toBe(1);
 
    await setRead("talker", c.id, sent.seq);
    expect(await unreadFor("talker", "own-messages")).toBe(0);
  });
 
  // ── T125: the refusals ───────────────────────────────────────────────────────
  it("refuses a position past the channel's last message with 400 and names the field", async () => {
    const c = await repo.createChannel("past-the-end", "public");
    await repo.addMember(c.id, member.id);
    await repo.sendMessage(c.id, { text: "only one", userId: member.id });
    const res = await setRead("lister", c.id, 99);
    expect(res.status).toBe(400);
    const body = (await res.json()) as { code: string; field?: string };
    expect(body.code).toBe("invalid_request");
    expect(body.field).toBe("sequence");
  });
 
  it("accepts a replayed lower position as a 200 that changes nothing", async () => {
    const c = await repo.createChannel("replayed", "public");
    await repo.addMember(c.id, member.id);
    await repo.sendMessage(c.id, { text: "one", userId: member.id });
    const two = await repo.sendMessage(c.id, { text: "two", userId: member.id });
    expect((await setRead("lister", c.id, two.seq)).status).toBe(200);
 
    const replay = await setRead("lister", c.id, 1);
    expect(replay.status).toBe(200);
    // The stored position is unchanged, which is the whole point: a client replaying an
    // old acknowledgement must not move the count backwards.
    expect(((await replay.json()) as { sequence: number }).sequence).toBe(two.seq);
    expect(await unreadFor("lister", "replayed")).toBe(0);
  });
 
  // ── T120: whose membership the refusal is about ─────────────────────────────
  it("refuses a public channel the PATH'S USER is not a member of with not_a_member", async () => {
    // The caller is an application credential, which is a member of nothing. If the
    // refusal were about the caller, every one of these calls would fail.
    const res = await setRead("lister", publicNotAMember, 0);
    expect(res.status).toBe(403);
    expect(((await res.json()) as { code: string }).code).toBe("not_a_member");
  });
 
  it("answers 404 for a private channel the path's user is not a member of", async () => {
    // NOT 403. `not_a_member` on a private channel would announce that it exists, which
    // is the leak the four attack shapes exist to catch. This is the one route in the
    // feature that emits `not_a_member` at all, and only for public channels.
    const res = await setRead("lister", notAMember, 0);
    expect(res.status).toBe(404);
  });
 
  it("takes a user token as well as an application credential", async () => {
    // The only route on this controller that does: a user records their own position.
    // Method-level `@Accepts` wins over the class-level one, which is the mechanism
    // chapter 3.12 built and this is the first route to rely on it.
    const c = await repo.createChannel("own-token", "public");
    await repo.addMember(c.id, member.id);
    const one = await repo.sendMessage(c.id, { text: "one", userId: member.id });
    const res = await setRead("lister", c.id, one.seq, await tokenFor("lister"));
    expect(res.status).toBe(200);
  });
 
  // ══ THE PROFILE (chapter 3.15, FR-023, FR-024, SC-011) ══════════════════════
 
  const profile = (user: string, key = credential) =>
    fetch(`${url}/v1/users/${user}`, { headers: { authorization: `Bearer ${key}` } });
 
  const patchProfile = (user: string, body: unknown, key = credential) =>
    fetch(`${url}/v1/users/${user}`, {
      method: "PATCH",
      headers: { "content-type": "application/json", authorization: `Bearer ${key}` },
      body: JSON.stringify(body),
    });
 
  // ── T131: the round trip, all three fields (SC-011) ─────────────────────────
  it("round-trips display name, avatar url and metadata", async () => {
    await repo.createUser("profiled", "Before");
    const res = await patchProfile("profiled", {
      display_name: "After",
      avatar_url: "https://cdn.example.com/a/b.png",
      metadata: { team: "support", tier: 3 },
    });
    expect(res.status).toBe(200);
 
    const body = (await (await profile("profiled")).json()) as {
      external_id: string;
      display_name: string | null;
      avatar_url: string | null;
      metadata: Record<string, unknown>;
    };
    expect(body).toEqual({
      external_id: "profiled",
      display_name: "After",
      avatar_url: "https://cdn.example.com/a/b.png",
      metadata: { team: "support", tier: 3 },
    });
  });
 
  it("patches one field without clearing the others", async () => {
    await patchProfile("profiled", { display_name: "Renamed" });
    const body = (await (await profile("profiled")).json()) as {
      display_name: string | null;
      avatar_url: string | null;
      metadata: Record<string, unknown>;
    };
    // ABSENT IS NOT NULL. The two fields left out of the patch keep their values.
    expect(body.display_name).toBe("Renamed");
    expect(body.avatar_url).toBe("https://cdn.example.com/a/b.png");
    expect(body.metadata).toEqual({ team: "support", tier: 3 });
  });
 
  it("clears a field when the patch names it null", async () => {
    await patchProfile("profiled", { avatar_url: null });
    const body = (await (await profile("profiled")).json()) as { avatar_url: string | null };
    expect(body.avatar_url).toBeNull();
  });
 
  it("accepts an empty patch and changes nothing", async () => {
    const before = await (await profile("profiled")).text();
    const res = await patchProfile("profiled", {});
    expect(res.status).toBe(200);
    expect(await (await profile("profiled")).text()).toBe(before);
  });
 
  // ── T132: FR-024's two bounds, each naming its field ────────────────────────
  it("refuses metadata over 4 KB with 400 and names the field", async () => {
    const res = await patchProfile("profiled", {
      metadata: { blob: "x".repeat(4 * 1024) },
    });
    expect(res.status).toBe(400);
    const body = (await res.json()) as { code: string; field?: string };
    expect(body.code).toBe("invalid_request");
    expect(body.field).toBe("metadata");
  });
 
  it("accepts metadata just under 4 KB", async () => {
    // THE CONTROL FOR THE BOUND. Without it, a refusal that rejected all metadata would
    // pass the test above.
    const res = await patchProfile("profiled", { metadata: { blob: "x".repeat(4_000) } });
    expect(res.status).toBe(200);
  });
 
  it("refuses a malformed avatar url with 400 and names the field", async () => {
    const res = await patchProfile("profiled", { avatar_url: "not-a-url" });
    expect(res.status).toBe(400);
    const body = (await res.json()) as { code: string; field?: string };
    expect(body.code).toBe("invalid_request");
    expect(body.field).toBe("avatar_url");
  });
 
  it("refuses an unknown profile field rather than ignoring it", async () => {
    const res = await patchProfile("profiled", { displayName: "camelCase" });
    expect(res.status).toBe(400);
  });
 
  // ── T129: a deleted user has no profile, on both routes ──────────────────────
  it("answers 404 on both profile routes for a deleted user", async () => {
    const gone = await repo.createUser("profile-deleted", "Going");
    expect((await profile("profile-deleted")).status).toBe(200);
    await repo.markUserDeleted(gone.id);
    expect((await profile("profile-deleted")).status).toBe(404);
    expect((await patchProfile("profile-deleted", { display_name: "x" })).status).toBe(404);
  });
 
  it("answers 404 for a user this tenant does not have", async () => {
    expect((await profile("nobody-at-all")).status).toBe(404);
  });
 
  // ══ THE BULK UPSERT AND THE DELETION (FR-025 to FR-030, SC-012) ═════════════
 
  const upsert = (users: unknown, key = credential) =>
    fetch(`${url}/v1/users`, {
      method: "POST",
      headers: { "content-type": "application/json", authorization: `Bearer ${key}` },
      body: JSON.stringify({ users }),
    });
 
  const removeUser = (user: string, key = credential) =>
    fetch(`${url}/v1/users/${user}`, {
      method: "DELETE",
      headers: { authorization: `Bearer ${key}` },
    });
 
  // ── T138: 100 accepted, 101 refused (SC-012) ────────────────────────────────
  it("upserts 100 users in one request", async () => {
    const entries = Array.from({ length: 100 }, (_, i) => ({
      external_id: `bulk-${i}`,
      display_name: `Bulk ${i}`,
    }));
    const res = await upsert(entries);
    expect(res.status).toBe(200);
    const body = (await res.json()) as { data: Array<{ status: string }> };
    expect(body.data).toHaveLength(100);
    expect(body.data.every((e) => e.status === "created")).toBe(true);
  });
 
  it("refuses 101 with 400 and names the field", async () => {
    const entries = Array.from({ length: 101 }, (_, i) => ({ external_id: `over-${i}` }));
    const res = await upsert(entries);
    expect(res.status).toBe(400);
    const body = (await res.json()) as { code: string; field?: string };
    expect(body.code).toBe("invalid_request");
    expect(body.field).toBe("users");
  });
 
  // ── T139: an existing user is UPDATED, not refused (FR-026) ──────────────────
  it("updates an entry that names an existing user", async () => {
    await upsert([{ external_id: "bulk-updatable", display_name: "First" }]);
    const res = await upsert([
      { external_id: "bulk-updatable", display_name: "Second", metadata: { seen: 2 } },
    ]);
    const body = (await res.json()) as {
      data: Array<{ status: string; display_name: string | null }>;
    };
    // `updated`, and the name actually moved. This is where `upsertUser` differs from
    // `createUser`, whose whole comment is about NOT doing this: the member-add asks for
    // membership and must not rename anybody, and this route's subject IS the user.
    expect(body.data[0]!.status).toBe("updated");
    expect(body.data[0]!.display_name).toBe("Second");
    const read = (await (await profile("bulk-updatable")).json()) as {
      display_name: string | null;
      metadata: Record<string, unknown>;
    };
    expect(read.display_name).toBe("Second");
    expect(read.metadata).toEqual({ seen: 2 });
  });
 
  it("leaves a field the entry omits alone", async () => {
    await upsert([{ external_id: "bulk-partial", display_name: "Name", metadata: { a: 1 } }]);
    await upsert([{ external_id: "bulk-partial", metadata: { a: 2 } }]);
    const read = (await (await profile("bulk-partial")).json()) as {
      display_name: string | null;
      metadata: Record<string, unknown>;
    };
    expect(read.display_name).toBe("Name");
    expect(read.metadata).toEqual({ a: 2 });
  });
 
  // ── T140: a failing entry names its index ───────────────────────────────────
  it("names the failing entry's index in the field path", async () => {
    const entries: unknown[] = Array.from({ length: 9 }, (_, i) => ({
      external_id: `indexed-${i}`,
    }));
    entries[7] = { external_id: "indexed-7", metadata: { blob: "x".repeat(4 * 1024) } };
    const res = await upsert(entries);
    expect(res.status).toBe(400);
    const body = (await res.json()) as { field?: string };
    // `users.7.metadata` — the index, not just the leaf. A caller sending 100 entries
    // cannot act on "metadata is too big" without being told which one.
    expect(body.field).toBe("users.7.metadata");
  });
 
  // ── T143, T145: what the deletion keeps and what it takes ───────────────────
  it("keeps the row, the messages and their attribution, and takes the rest", async () => {
    const doomed = await repo.createUser("deletable", "Doomed");
    const channel = await repo.createChannel("deletion-witness", "public");
    await repo.addMember(channel.id, doomed.id);
    const sent = await repo.sendMessage(channel.id, {
      text: "still here afterwards",
      userId: doomed.id,
      userExternalId: "deletable",
    });
    await setRead("deletable", channel.id, sent.seq);
    await patchProfile("deletable", {
      avatar_url: "https://cdn.example.com/doomed.png",
      metadata: { doomed: true },
    });
 
    expect((await removeUser("deletable")).status).toBe(200);
 
    // THE MESSAGE IS STILL THERE AND STILL THEIRS (FR-028). The history route is the
    // reader; `user` is the external id the send recorded.
    const history = await fetch(
      `${url}/v1/channels/${channel.id}/messages?limit=10`,
      { headers: { authorization: `Bearer ${credential}` } },
    );
    const messages = (await history.json()) as {
      messages: Array<{ seq: number; text: string | null; user: string | null }>;
    };
    const mine = messages.messages.find((m) => m.seq === sent.seq);
    expect(mine?.text).toBe("still here afterwards");
    // ATTRIBUTED, not senderless. `ON DELETE SET NULL` would have made this null, which
    // reads the same as a message that never had an author — and `toFrame` drops those.
    expect(mine?.user).toBe("deletable");
 
    // The profile is gone and the user is invisible to the API.
    expect((await profile("deletable")).status).toBe(404);
    // The membership and the read position went with it: the listing 404s the user, so
    // the membership is asserted from the channel's side.
    const remaining = await repo.countMembers(channel.id);
    expect(remaining).toBe(0);
  });
 
  it("leaves usage_active_users rows alone (FR-029)", async () => {
    // Billing history does not vanish with a profile: a customer who deleted a user in
    // March still owes for March.
    //
    // THE ROW IS WRITTEN BY A SEND, not by a helper. `sendMessage` inserts into
    // `usage_active_users` when the send is attributed — that is the only writer — so the
    // fixture has to send. Read back through `usageFor`, which is the reader the billing
    // surface uses, rather than a raw count this suite may not hold.
    const billed = await repo.createUser("billed", "Billed");
    const channel = await repo.createChannel("billing-witness", "public");
    await repo.addMember(channel.id, billed.id);
    await repo.sendMessage(channel.id, {
      text: "counts toward the month",
      userId: billed.id,
      userExternalId: "billed",
    });
    const before = await usageFor(db, environmentId, periodOf(new Date()));
    expect(before.activeUsers).toBeGreaterThan(0);
 
    expect((await removeUser("billed")).status).toBe(200);
 
    const after = await usageFor(db, environmentId, periodOf(new Date()));
    expect(after.activeUsers).toBe(before.activeUsers);
    expect(after.messagesSent).toBe(before.messagesSent);
  });
 
  it("answers 200 on a second delete and 404 for a user who never existed", async () => {
    const twice = await repo.createUser("twice-deleted", "Twice");
    expect(twice.external_id).toBe("twice-deleted");
    expect((await removeUser("twice-deleted")).status).toBe(200);
    expect((await removeUser("twice-deleted")).status).toBe(200);
    expect((await removeUser("never-existed-at-all")).status).toBe(404);
  });
 
  // ── T146: the id comes back and the row is reused (FR-030) ──────────────────
  it("reuses the row when the same external id is presented again", async () => {
    const revived = await repo.createUser("revivable", "Before Deletion");
    await patchProfile("revivable", { metadata: { before: true } });
    await removeUser("revivable");
    expect((await profile("revivable")).status).toBe(404);
 
    const res = await upsert([{ external_id: "revivable" }]);
    const body = (await res.json()) as { data: Array<{ status: string }> };
    expect(body.data[0]!.status).toBe("revived");
 
    const back = (await (await profile("revivable")).json()) as {
      external_id: string;
      display_name: string | null;
      avatar_url: string | null;
      metadata: Record<string, unknown>;
    };
    // THE SAME ROW, EMPTY. `(environment_id, external_id)` is unique and the row never
    // left, so there is no other honest answer than reusing it — and a revived row does
    // not inherit the profile the deletion wiped.
    expect(back).toEqual({
      external_id: "revivable",
      display_name: null,
      avatar_url: null,
      metadata: {},
    });
    const after = await repo.getUserByExternalId("revivable");
    expect(after?.id).toBe(revived.id);
  });
 
  // ── T147: deleting a channel's owner ────────────────────────────────────────
  it("deletes a channel owner and leaves the channel ownerless", async () => {
    // FR-CHN-04's roles and FR-USR-05's deletion meet here, and the chapter has to say
    // what happens. MEASURED: the membership row goes, so the channel has no owner and
    // no route can appoint one — `PATCH .../members/:userExternalId` sets the role of an
    // EXISTING member, so a channel whose only owner is deleted cannot get another
    // without somebody being added first.
    //
    // Left as it is, and stated rather than fixed: nothing in the platform reads
    // `members.role` to authorize anything, so an ownerless channel behaves exactly like
    // an owned one. The day a permission consults the column, this becomes a real
    // question — and the answer will be a route, not a cascade.
    const channel = await repo.createChannel("ownerless", "public");
    const owner = await repo.createUser("the-owner", "The Owner");
    const other = await repo.createUser("the-other", "The Other");
    await repo.addMember(channel.id, owner.id, "owner");
    await repo.addMember(channel.id, other.id);
    expect(await repo.memberRole(channel.id, owner.id)).toBe("owner");
 
    await removeUser("the-owner");
 
    expect(await repo.memberRole(channel.id, owner.id)).toBeNull();
    expect(await repo.memberRole(channel.id, other.id)).toBe("member");
    // The channel is still there and still usable by its remaining member.
    const still = await fetch(`${url}/v1/channels/${channel.id}`, {
      headers: { authorization: `Bearer ${credential}` },
    });
    expect(still.status).toBe(200);
  });
 
  // ══ THE BAN (chapter 3.15, FR-031, FR-032, SC-013) ══════════════════════════
 
  const ban = (user: string, key = credential) =>
    fetch(`${url}/v1/users/${user}/ban`, {
      method: "POST",
      headers: { authorization: `Bearer ${key}` },
    });
 
  const unban = (user: string, key = credential) =>
    fetch(`${url}/v1/users/${user}/ban`, {
      method: "DELETE",
      headers: { authorization: `Bearer ${key}` },
    });
 
  const sendAs = (channelId: string, token: string, text: string) =>
    fetch(`${url}/v1/channels/${channelId}/messages`, {
      method: "POST",
      headers: { "content-type": "application/json", authorization: `Bearer ${token}` },
      body: JSON.stringify({ text }),
    });
 
  // ── T152: banned cannot send, history survives, lifting restores ────────────
  it("refuses a banned user's send and restores it when the ban lifts", async () => {
    const channel = await repo.createChannel("ban-witness", "public");
    const speaker = await repo.createUser("bannable", "Bannable");
    await repo.addMember(channel.id, speaker.id);
    const token = await tokenFor("bannable");
 
    // THE CONTROL FIRST. A refusal proves nothing unless the same call worked a moment
    // ago — chapter 3.12's fourteen green tests are why this line exists.
    expect((await sendAs(channel.id, token, "before the ban")).status).toBe(201);
 
    expect((await ban("bannable")).status).toBe(200);
    const refused = await sendAs(channel.id, token, "during the ban");
    expect(refused.status).toBe(403);
    expect(((await refused.json()) as { code: string }).code).toBe("user_banned");
 
    // HISTORY IS UNTOUCHED. A ban is not a deletion: their earlier message is still
    // there and still theirs, readable by the tenant.
    const history = await fetch(`${url}/v1/channels/${channel.id}/messages?limit=10`, {
      headers: { authorization: `Bearer ${credential}` },
    });
    const messages = (await history.json()) as {
      messages: Array<{ text: string | null; user: string | null }>;
    };
    expect(messages.messages.some((m) => m.text === "before the ban")).toBe(true);
    expect(messages.messages.some((m) => m.text === "during the ban")).toBe(false);
 
    expect((await unban("bannable")).status).toBe(200);
    expect((await sendAs(channel.id, token, "after the ban")).status).toBe(201);
  });
 
  it("answers 200 on a repeated ban and a repeated unban", async () => {
    await repo.createUser("twice-banned", "Twice");
    expect((await ban("twice-banned")).status).toBe(200);
    expect((await ban("twice-banned")).status).toBe(200);
    expect((await unban("twice-banned")).status).toBe(200);
    expect((await unban("twice-banned")).status).toBe(200);
  });
 
  it("answers 404 for a user this tenant does not have, and for a deleted one", async () => {
    expect((await ban("never-heard-of")).status).toBe(404);
    const gone = await repo.createUser("ban-then-delete", "Gone");
    await repo.deleteUser(gone.id);
    // A DELETED USER CANNOT BE BANNED, and does not need to be: every route naming them
    // answers 404 and their session carries no channels. Banning one would be a state
    // with no observable difference.
    expect((await ban("ban-then-delete")).status).toBe(404);
  });
 
  // ── T154: the two edge cases the spec names ─────────────────────────────────
  it("bans a private channel's member without removing them", async () => {
    // THE BAN IS TENANT-SCOPED, so it is not a removal. The membership survives, the
    // channel still lists them, and lifting the ban restores everything with nobody
    // re-added.
    const priv = await repo.createChannel("ban-private", "private");
    const member2 = await repo.createUser("private-bannable", "Private Bannable");
    await repo.addMember(priv.id, member2.id);
    const token = await tokenFor("private-bannable");
    expect((await sendAs(priv.id, token, "a member speaks")).status).toBe(201);
 
    await ban("private-bannable");
    const refused = await sendAs(priv.id, token, "still a member, still banned");
    expect(refused.status).toBe(403);
    expect(((await refused.json()) as { code: string }).code).toBe("user_banned");
    // Still a member, and the listing still shows the channel.
    expect(await repo.isMember(priv.id, member2.id)).toBe(true);
    const listed = (await (await list("private-bannable", "?limit=100")).json()) as {
      data: Array<{ external_id: string }>;
    };
    expect(listed.data.map((c) => c.external_id)).toContain("ban-private");
 
    await unban("private-bannable");
    expect((await sendAs(priv.id, token, "and back")).status).toBe(201);
  });
 
  it("does not let implicit creation undo a ban", async () => {
    // A token minted for a banned user's identifier must not revive them. `createUser`
    // is idempotent and touches no other column, so the row — and the ban on it —
    // survives a mint. The upsert is the route that clears state, and it clears
    // `deleted_at` only.
    const target = await repo.createUser("mint-after-ban", "Minted");
    await ban("mint-after-ban");
    const token = await tokenFor("mint-after-ban");
    expect(token.length).toBeGreaterThan(0);
 
    const channel = await repo.createChannel("mint-room", "public");
    await repo.addMember(channel.id, target.id);
    const refused = await sendAs(channel.id, token, "minted my way in");
    expect(refused.status).toBe(403);
 
    // And an upsert naming them does not lift it either: the upsert clears `deleted_at`
    // because FR-030 asks it to, and says nothing about `banned_at`.
    await upsert([{ external_id: "mint-after-ban", display_name: "Renamed" }]);
    expect((await sendAs(channel.id, token, "upserted my way in")).status).toBe(403);
  });
});