What a user sees

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

-- Ordering a user's channels, knowing what they have not read, and a deleted user
-- who is still an author.
--
-- THREE COLUMNS AND A TABLE, IN ONE FILE, BECAUSE THEY BELONG TO ONE CHAPTER. The
-- design that produced them split them across two migrations by SUBJECT — an
-- activity-and-read-positions file and a roles-and-user-deletion file — and neither
-- file belonged to one chapter. The previous chapter took `members.role` as `0006`
-- and left the rest here, which is the whole of it: a migration's number should
-- follow the chapter that introduces it.
--
-- The first two 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 the lane'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 the previous chapter's migration names on its own constraint: 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 THE GUARD ENTRY ARRIVES IN THIS SAME CHAPTER, not in a later audit. The rule
-- the instruments chapter set is that a table joins `sentinel.sql`'s array, gains its
-- bait, and gets its case in `guard.itest.ts` in the chapter that CREATES it —
-- because a name in that array without bait behind it installs a trigger that can
-- never match and reads, in every report, exactly like protection.
--
-- 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 the endpoints
-- chapter 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)
);
--> statement-breakpoint
 
-- A DELETED USER KEEPS THEIR ROW, and this column is what says so.
--
-- This is in no SRS clause. It arrived from designing FR-USR-05's deletion path and
-- finding nowhere to record the state. Three tables reference users(id) — messages,
-- members, and read_positions above — and the clause asks that a deleted user's
-- messages be preserved "as authored by a deleted user".
--
-- ON DELETE SET NULL WOULD SATISFY THE LETTER OF THAT AND BREAK DELIVERY.
-- `backfill.controller`'s `toFrame` drops senderless rows because `messageSchema`
-- requires `user`, so a NULL author makes a message invisible to every socket.
-- "Authored by a deleted user" and "authored by nobody" are different states and only
-- the first is deliverable. ON DELETE CASCADE deletes the messages the clause says to
-- keep. A separate `deleted_users` table is a second identity space for one flag
-- (research R7).
--
-- So deletion clears the profile fields, removes the memberships and read positions,
-- sets this, and touches no messages.
--
-- (environment_id, external_id) STAYS UNIQUE, which is why presenting the same
-- external id again reuses this row and clears this column (FR-030) rather than
-- creating a second identity for one person.
ALTER TABLE users
    ADD COLUMN deleted_at TIMESTAMPTZ;

// Set `channels.last_activity_at` to each channel's real last activity.
//
// T019 — and it is a SCRIPT and not part of migration 0007 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;
  });

@@ -1,19 +1,20 @@
 import { randomUUID } from "node:crypto";
 
-import { and, asc, desc, eq, gt, inArray, lt, sql, type SQL } from "drizzle-orm";
+import { and, asc, desc, eq, gt, inArray, isNull, lt, sql, type SQL } from "drizzle-orm";
 
 import type { Db } from "./client";
 import {
   apiKeys,
   applications,
   channels,
   consumedEvents,
   environments,
   humans,
   members,
+  readPositions,
   memberships,
   messages,
   organisations,
   outbox,
   users,
 } from "./schema";
@@ -558,12 +559,34 @@ export async function provisionOrganisation(
 }
 
 export interface UserRow {
   id: string;
   external_id: string;
   display_name: string | null;
+  /** 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>;
+  /** 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;
+  /** 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 {
   id: string;
   external_id: string;
   /** The column has been `"public" | "private"` with a CHECK constraint since
@@ -628,12 +651,25 @@ export class ChannelArchivedError extends Error {
   constructor(public readonly channelId: string) {
     super(`channel archived: ${channelId}`);
     this.name = "ChannelArchivedError";
   }
 }
 
+/** A write or a connect refused because the user is banned (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. */
 function toIso(value: Date | string): string {
   return value instanceof Date ? value.toISOString() : String(value);
 }
@@ -658,13 +694,24 @@ export class Repository {
         displayName: displayName ?? null,
       })
       .onConflictDoNothing({ target: [users.environmentId, users.externalId] })
       .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`);
     // The DISPLAY NAME OF THE EXISTING ROW WINS. A second call is not an update:
     // FR-CHN-04 asks for membership, and quietly renaming a user because someone
     // re-sent a member list would be a write nobody asked for.
@@ -674,21 +721,40 @@ export class Repository {
   async getUserByExternalId(externalId: string): Promise<UserRow | null> {
     const rows = await this.db
       .select({
         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(
         and(
           eq(users.environmentId, this.environmentId),
           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 the isolation harness 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 the endpoint over it, which is fine for a fixture and
    * cannot back an endpoint: a repeated `external_id` raises against
@@ -921,31 +987,33 @@ export class Repository {
   }
 
   /** Remove members by user id, up to a hundred in one call, reporting each
    * (FR-006, FR-007, FR-008).
    *
    * BULK, BECAUSE THE REQUIREMENT ALWAYS WAS. FR-006 says "up to 100 in one
-   * request" and FR-007 says the result is reported per user — which is chapter
-   * the endpoints chapter's `addMembers` shape in both halves. `contracts/membership.md` specified a
+   * request" and FR-007 says the result is reported per user — which is the
+   * endpoints chapter's `addMembers` shape in both halves. `contracts/membership.md` specified a
    * single-user `DELETE …/members/:userExternalId` for ten analysis passes, having
    * read "the shape the channel-endpoints chapter chose" as *named outcomes* and dropped *bulk*.
    * Every pass compared requirements to tasks, both said "removal", and identifier
    * coverage read 100% the whole time.
    *
    * NO MESSAGES ARE TOUCHED (FR-008). The removed user's messages stay in history
    * attributed to them: `messages.user_id` still points at a row that still exists,
    * and their socket stops receiving the channel on its next resume because the
    * session is built from `members`.
    *
-   * NO READ POSITION TO REMOVE YET, AND THAT IS AN OBLIGATION AND NOT A GAP.
-   * `read_positions` is per-member state keyed by `(channel_id, user_id)`, so
-   * leaving a removed member's row would leave a non-member's position in a
-   * per-member table. The table arrives in the next chapter, and the delete has to
-   * arrive with it — this comment is here so that the chapter which creates the
-   * table finds the requirement rather than deducing it. `channels.itest.ts` gets
-   * the case in the same edit.
+   * THE READ POSITION GOES WITH THE MEMBERSHIP, and the previous chapter wrote this
+   * requirement down here rather than leaving it to be deduced. `read_positions` is
+   * per-member state keyed by `(channel_id, user_id)`, so leaving a removed member's
+   * row would leave a non-member's position in a per-member table.
+   *
+   * Adding the user back therefore starts their unread count at the channel's whole
+   * history, which is the same thing "no row means position zero" says for a new
+   * member — so the delete costs nothing a rejoin has to undo.
+   *
    * SCOPED THROUGH THE CHANNEL, and the caller has already read it scoped. `members`
    * carries no `environment_id` — the catalogue calls it a `hop` — so the join is
    * what keeps a foreign channel's rows out of reach.
    */
   async removeMembers(
     channelId: string,
@@ -977,12 +1045,31 @@ export class Repository {
                        AND c.environment_id = ${this.environmentId})`,
         ),
       )
       .returning({ userId: members.userId });
     const removed = new Set(deleted.map((r) => r.userId));
 
+    // SCOPED BY environment_id AND NOT BY THE CHANNEL JOIN, because this table
+    // carries one. `members` above needs the `EXISTS` over `channels` to stay inside
+    // a tenant; `read_positions` was given `environment_id` precisely so the guard
+    // could watch it, and the same column makes this predicate direct.
+    //
+    // EVERY id THE CALLER NAMED, not just the ones a membership was removed for. A
+    // user with a read position and no membership is the state this is cleaning up,
+    // and refusing to touch it because the membership was already gone would leave
+    // exactly the row the delete exists for.
+    await this.db
+      .delete(readPositions)
+      .where(
+        and(
+          eq(readPositions.channelId, channelId),
+          eq(readPositions.environmentId, this.environmentId),
+          inArray(readPositions.userId, userIds),
+        ),
+      );
+
     for (const id of userIds) {
       outcome.set(id, removed.has(id) ? "removed" : "not_a_member");
     }
     return outcome;
   }
 
@@ -1025,12 +1112,512 @@ export class Repository {
           eq(users.environmentId, this.environmentId),
         ),
       );
     return rows.map((r) => r.channel_id);
   }
 
+  /** Upsert a user by external id, updating the profile fields present
+   * (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 the channel-endpoints chapter 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 the isolation harness 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 (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 (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 (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 (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 (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 (the
+   * channel-control chapter, 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
    * guarantee: the lock serialises assignment per channel, and the ack
    * that matters happens only after commit (FR-MSG-05).
    *
@@ -1058,12 +1645,36 @@ export class Repository {
       text: string;
       metadata?: unknown;
       idempotencyKey?: string;
     },
   ): Promise<MessageRow> {
     return this.db.transaction(async (tx) => {
+
+      // ── 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,
           lastSequence: channels.lastSequence,
           // `channels.type` has been a `"public" | "private"` column
           // with a CHECK since chapter 2.1 and nothing decided on it until now — it
@@ -1186,15 +1797,33 @@ export class Repository {
           )),
           duplicate: true,
         };
       }
 
       // The sequence is spent only by a message that actually landed.
+      //
+      // AND `lastActivityAt` MOVES IN THE SAME STATEMENT (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);
 
       // THE EVENT COMMITS WITH THE MESSAGE (ADR-06).
       //

@@ -277,6 +277,225 @@ describe("members_role_check names the channel's three (FR-011, R8)", () => {
     const channel = await repoA.createChannel("role-at-insert", "public");
     const user = await repoA.createUser("role-at-insert-user");
     await repoA.addMember(channel.id, user.id, "owner");
     expect(await repoA.memberRole(channel.id, user.id)).toBe("owner");
   });
 });
+
+// ── T113: THE TIE AT A PAGE BOUNDARY (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", () => {
+  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 (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", () => {
+  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. The isolation harness 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 (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. The webhook dispatcher chapter
+// 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", () => {
+  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 the outbox chapter. 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 (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
 * (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 (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 (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
 * (the error-registry chapter 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 (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.
//
// 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 {}

@@ -8,12 +8,17 @@ import { APP_FILTER } from "@nestjs/core";
 import { AuthModule } from "./auth/auth.module";
 import { AuthenticateMiddleware } from "./auth/authenticate.middleware";
 import { HealthController } from "./health.controller";
 import { InternalModule } from "./internal/internal.module";
 import { MessagesModule } from "./messages/messages.module";
 import { ChannelsModule } from "./channels/channels.module";
+// 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 { OutboxModule } from "./outbox/outbox.module";
 import { TenancyModule } from "./tenancy/tenancy.module";
 import { LOGGER, apiLogger } from "./logger";
 import { ProtocolErrorFilter } from "./protocol-error.filter";
 import { RequestContextMiddleware } from "./request-context.middleware";
@@ -24,12 +29,13 @@ import { RequestContextMiddleware } from "./request-context.middleware";
 // point — including tests — gets the same error envelope for free.
 @Module({
   imports: [
     AuthModule,
     MessagesModule,
     ChannelsModule,
+    UsersModule,
     InternalModule,
     TenancyModule,
     OutboxModule,
     ConsumerModule,
   ],
   controllers: [HealthController],

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 (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 (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 (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 (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 (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.
 *
 * `@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 the channel-control chapter 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 (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 (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 (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 (FR-027). */
  @Delete(":externalId")
  async deleteUser(@Param("externalId") externalId: string) {
    return this.users.deleteUser(externalId);
  }
 
  /** The ban pair (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);
  }
}

@@ -37,47 +37,108 @@ function declaredFrameTypes(): string[] {
     const value = shape?.type?.value;
     if (typeof value === "string") types.push(value);
   }
   return types;
 }
 
-async function closeCode(socket: WebSocket): Promise<number> {
-  return new Promise((resolve, reject) => {
-    socket.on("close", (code) => resolve(code));
-    socket.on("error", () => undefined);
-    setTimeout(() => reject(new Error("no close within 5s")), 5_000);
-  });
+/** A SOCKET WITH A BUFFER, and the buffer is the point.
+ *
+ * The obvious shape — await `open`, then attach a `message` listener, then read —
+ * loses the handshake. `connection.ack` is sent the moment the upgrade completes,
+ * and awaiting `open` yields to the event loop first: the frame arrives with no
+ * listener attached and is gone. Every test in this file that waited for a second
+ * frame timed out at exactly 5000ms until the listener moved to construction time.
+ *
+ * So frames are collected from the instant the socket exists, and `waitFor` reads
+ * the buffer before it waits. */
+interface Reader {
+  socket: WebSocket;
+  waitFor: <T = Record<string, unknown>>(type: string, timeoutMs?: number) => Promise<T>;
+  frames: () => { type: string }[];
+  opened: () => Promise<void>;
+  /** The close code, once it arrives (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>;
 }
 
-async function firstFrame(socket: WebSocket, type: string): Promise<Record<string, unknown>> {
-  return new Promise((resolve, reject) => {
-    socket.on("message", (raw) => {
-      const frame = JSON.parse(raw.toString()) as Record<string, unknown>;
-      if (frame.type === type) resolve(frame);
-    });
-    socket.on("close", (code) => reject(new Error(`closed ${code}`)));
-    setTimeout(() => reject(new Error(`no ${type} within 5s`)), 5_000);
+function read(socket: WebSocket): Reader {
+  const buffer: { type: string }[] = [];
+  let closed: number | null = null;
+  socket.on("message", (raw) => buffer.push(JSON.parse(raw.toString()) as { type: string }));
+  socket.on("close", (code) => {
+    closed = code;
   });
-}
+  socket.on("error", () => undefined);
+
+  const opened = () =>
+    new Promise<void>((resolve, reject) => {
+      if (socket.readyState === WebSocket.OPEN) return resolve();
+      socket.on("open", () => resolve());
+      socket.on("close", (code) => reject(new Error(`closed ${code} before opening`)));
+      setTimeout(() => reject(new Error("socket never opened")), 5_000);
+    });
 
-/** ABSENCE NEEDS A DEADLINE RATHER THAN A RACE. `firstFrame` resolves on the frame it
- * is waiting for, which is the right shape for "this is refused" and no shape at all
- * for "nothing was delivered". This buffers everything a socket receives so a test can
- * wait a fixed window and then read what the buffer holds. */
-function collect(socket: WebSocket): () => Record<string, unknown>[] {
-  const frames: Record<string, unknown>[] = [];
-  socket.on("message", (raw) => {
-    frames.push(JSON.parse(raw.toString()) as Record<string, unknown>);
-  });
-  return () => [...frames];
+  const waitFor = async <T>(type: string, timeoutMs = 5_000): Promise<T> => {
+    const deadline = Date.now() + timeoutMs;
+    for (;;) {
+      const found = buffer.find((f) => f.type === type);
+      if (found) return found as T;
+      if (closed !== null) throw new Error(`closed ${closed} before a ${type} arrived`);
+      if (Date.now() > deadline) {
+        throw new Error(
+          `no ${type} within ${timeoutMs}ms — saw ${buffer.map((f) => f.type).join(", ") || "nothing"}`,
+        );
+      }
+      await new Promise((resolve) => setTimeout(resolve, 20));
+    }
+  };
+
+  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 };
 }
 
 async function quiet(ms: number): Promise<void> {
   await new Promise((resolve) => setTimeout(resolve, ms));
 }
 
+/** OPEN AND BUFFER IN ONE STEP. Every socket in this file goes through here, because
+ * `read` has to attach its listener at construction time — the whole point of the
+ * buffer — and a `new WebSocket(…)` written inline is a socket whose handshake frame
+ * nobody is listening for. */
+/** EVERY SOCKET THIS FILE OPENS, so `afterAll` can close them.
+ *
+ * `server.close()` waits for its connections. A test that asserts a refusal has no
+ * reason to close the socket it was refused on, and fifteen tests leaving one open
+ * each made the teardown hook time out at 10s — with every test passing, which reads
+ * as a suite that works and a harness that does not. */
+const sockets: WebSocket[] = [];
+
+function connect(base: string, token: string, query = ""): Reader {
+  const socket = new WebSocket(`${base}/v1/ws?token=${token}${query}`);
+  sockets.push(socket);
+  return read(socket);
+}
+
+function closeAll(): void {
+  for (const socket of sockets) {
+    if (socket.readyState === WebSocket.OPEN || socket.readyState === WebSocket.CONNECTING) {
+      socket.close();
+    }
+  }
+  sockets.length = 0;
+}
+
 describe("the socket refuses another tenant's identifiers", () => {
   let t: SocketTenants;
   let server: Server;
   let url: string;
 
   beforeAll(async () => {
@@ -91,54 +152,57 @@ describe("the socket refuses another tenant's identifiers", () => {
     attachSessions({ server, api: createApiClient(t.apiUrl), logger: silent });
     await new Promise<void>((resolve) => server.listen(0, resolve));
     url = `ws://127.0.0.1:${(server.address() as AddressInfo).port}`;
   }, 90_000);
 
   afterAll(async () => {
+    // SOCKETS FIRST. `server.close()` waits for its connections, so a socket left
+    // open by a passing test is a teardown that hangs.
+    closeAll();
     await new Promise<void>((resolve) => server?.close(() => resolve()));
     t?.stop();
   });
 
   it("derives the frame types from the protocol, and finds some", () => {
     const types = declaredFrameTypes();
     // An empty derivation is a broken derivation, not a small protocol.
     expect(types.length).toBeGreaterThan(1);
     expect(types).toContain("message.send");
   });
 
   it("a connection ack names nothing belonging to the other tenant", async () => {
-    const socket = new WebSocket(`${url}/v1/ws?token=${t.attacker.token}`);
-    const ack = await firstFrame(socket, "connection.ack");
+    const socket = connect(url, t.attacker.token);
+    const ack = await socket.waitFor("connection.ack");
     const serialised = JSON.stringify(ack);
     expect(serialised).not.toContain(t.victim.channelId);
     expect(serialised).not.toContain(t.victim.environmentId);
     expect(serialised).not.toContain(t.victim.userId);
-    socket.close();
+    socket.socket.close();
   }, 20_000);
 
   it("message.send to the other tenant's channel is refused", async () => {
-    const socket = new WebSocket(`${url}/v1/ws?token=${t.attacker.token}`);
-    await firstFrame(socket, "connection.ack");
-    socket.send(
+    const socket = connect(url, t.attacker.token);
+    await socket.waitFor("connection.ack");
+    socket.socket.send(
       JSON.stringify({
         type: "message.send",
         payload: {
           idem_key: randomUUID(),
           channel: t.victim.channelId,
           text: "from the attacker",
         },
       }),
     );
-    const error = await firstFrame(socket, "error");
+    const error = await socket.waitFor("error");
     const payload = error.payload as { code?: string; message?: string };
     // The refusal must not name what it refused. An error that echoes the channel id
     // back tells the attacker the channel exists, which is the leak the HTTP gauntlet
     // proves is absent on every route — the socket does not get an exemption.
     expect(JSON.stringify(payload)).not.toContain(t.victim.channelId);
     expect(payload.code).toBeTruthy();
-    socket.close();
+    socket.socket.close();
   }, 20_000);
 
   it("every declared frame type that is not message.send is refused inbound", async () => {
     // SCHEMA VALIDATION RUNS BEFORE THE TYPE CHECK, and that shapes what this can
     // claim. A frame whose payload does not match its own schema is answered
     // `invalid_frame` and never reaches the rule that says clients may not utter a
@@ -147,29 +211,29 @@ describe("the socket refuses another tenant's identifiers", () => {
     //
     // So: every declared type must be refused SOMEHOW, and one well-formed server
     // frame must be refused BY THE RULE.
     const inboundOnly = declaredFrameTypes().filter((type) => type !== "message.send");
     expect(inboundOnly.length).toBeGreaterThan(0);
     for (const type of inboundOnly) {
-      const socket = new WebSocket(`${url}/v1/ws?token=${t.attacker.token}`);
-      await firstFrame(socket, "connection.ack");
-      socket.send(JSON.stringify({ type, payload: {} }));
-      const error = await firstFrame(socket, "error");
+      const socket = connect(url, t.attacker.token);
+      await socket.waitFor("connection.ack");
+      socket.socket.send(JSON.stringify({ type, payload: {} }));
+      const error = await socket.waitFor("error");
       expect((error.payload as { code?: string }).code, `${type} was not refused`).toBeTruthy();
-      socket.close();
+      socket.socket.close();
     }
 
     // `message.ack` carries `{ seq }`, which is the easiest valid server frame to
     // build — so it is the one that proves the rule rather than the parser.
-    const socket = new WebSocket(`${url}/v1/ws?token=${t.attacker.token}`);
-    await firstFrame(socket, "connection.ack");
-    socket.send(JSON.stringify({ type: "message.ack", payload: { seq: 1 } }));
-    const error = await firstFrame(socket, "error");
+    const socket = connect(url, t.attacker.token);
+    await socket.waitFor("connection.ack");
+    socket.socket.send(JSON.stringify({ type: "message.ack", payload: { seq: 1 } }));
+    const error = await socket.waitFor("error");
     expect((error.payload as { code?: string }).code).toBe("unknown_frame_type");
     // A protocol violation closes the connection (EIR-WS-06's 4002).
-    await expect(closeCode(socket)).resolves.toBe(4002);
+    await expect(socket.closedWith()).resolves.toBe(4002);
   }, 60_000);
 
   // ── THE SOCKET'S SEND INTO A PRIVATE CHANNEL OF ITS OWN TENANT ─────────────
   //
   // FR-001. Every attack above crosses a tenant boundary; this one
   // does not. The attacker's own tenant holds a private channel they are not a
@@ -186,36 +250,36 @@ describe("the socket refuses another tenant's identifiers", () => {
     // refused; it does not say the refusal is indistinguishable from one for a
     // channel that exists nowhere, and indistinguishability is the property SC-002
     // asks for.
     //
     // ONE SOCKET FOR BOTH, because opening a second would let a difference in
     // connection state stand in for a difference in the answer.
-    const socket = new WebSocket(`${url}/v1/ws?token=${t.attacker.token}`);
-    await firstFrame(socket, "connection.ack");
+    const socket = connect(url, t.attacker.token);
+    await socket.waitFor("connection.ack");
 
     const text = `not a member ${randomUUID()}`;
-    socket.send(
+    socket.socket.send(
       JSON.stringify({
         type: "message.send",
         payload: { idem_key: randomUUID(), channel: t.attacker.privateChannelId, text },
       }),
     );
-    const refused = await firstFrame(socket, "error");
+    const refused = await socket.waitFor("error");
 
-    socket.send(
+    socket.socket.send(
       JSON.stringify({
         type: "message.send",
         payload: {
           idem_key: randomUUID(),
           channel: "00000000-0000-4000-8000-000000000000",
           text: `nowhere ${randomUUID()}`,
         },
       }),
     );
-    const absent = await firstFrame(socket, "error");
-    socket.close();
+    const absent = await socket.waitFor("error");
+    socket.socket.close();
 
     const refusedCode = (refused.payload as { code?: string }).code;
     expect(refusedCode, "the private-channel send was not refused at all").toBeTruthy();
     expect(refusedCode).toBe((absent.payload as { code?: string }).code);
 
     // AND READ THE CHANNEL, rather than inferring its state from the refusal. A
@@ -251,38 +315,34 @@ describe("the socket refuses another tenant's identifiers", () => {
     // A CONTROL AND THE CASE, in that order, on one fixture. Asserting only that a
     // removed member's cursor is refused would pass against a server that accepts no
     // cursor at all — so the same token presents the same cursor twice, and the
     // difference between the two acks is the whole assertion.
     await t.attacker.say(`before removal ${randomUUID()}`);
 
-    const asMember = new WebSocket(
-      `${url}/v1/ws?token=${t.attacker.token}&cursor=${t.attacker.channelId}:0`,
-    );
-    const first = await firstFrame(asMember, "connection.ack");
+    const asMember = connect(url, t.attacker.token, `&cursor=${t.attacker.channelId}:0`);
+    const first = await asMember.waitFor("connection.ack");
     const beforeCursor = (first.payload as { cursor?: Record<string, number> }).cursor ?? {};
     expect(Object.keys(beforeCursor)).toContain(t.attacker.channelId);
-    asMember.close();
+    asMember.socket.close();
 
     // Through the PUBLIC ROUTE, so the test asserts the consequence of the API rather
     // than of a direct write — a repository call would prove the session reads
     // `members` and nothing about whether the endpoint gets there.
     await t.attacker.removeSelf();
 
-    const afterRemoval = new WebSocket(
-      `${url}/v1/ws?token=${t.attacker.token}&cursor=${t.attacker.channelId}:0`,
-    );
-    const frames = collect(afterRemoval);
-    const second = await firstFrame(afterRemoval, "connection.ack");
+    const afterRemoval = connect(url, t.attacker.token, `&cursor=${t.attacker.channelId}:0`);
+    const frames = afterRemoval.frames;
+    const second = await afterRemoval.waitFor("connection.ack");
     const afterCursor = (second.payload as { cursor?: Record<string, number> }).cursor ?? {};
     expect(Object.keys(afterCursor)).not.toContain(t.attacker.channelId);
 
     // And nothing is backfilled from it either. The ack's cursor is what the server
     // ACCEPTED; this is what it DELIVERED, and the two can disagree.
     await quiet(1_000);
     expect(frames().filter((f) => f.type === "message.created")).toEqual([]);
-    afterRemoval.close();
+    afterRemoval.socket.close();
 
     // PUT IT BACK. This test mutates state every later test in the file leans on,
     // and the next one to need it failed on its control rather than on its subject.
     await t.attacker.rejoinSelf();
   });
 
@@ -301,19 +361,17 @@ describe("the socket refuses another tenant's identifiers", () => {
   // sentence this test exists to replace: archiving could plausibly have been
   // implemented by removing memberships, and then a member would silently lose the
   // channel from their session.
   it("keeps an archived channel in the session and its cursor accepted", async () => {
     await t.attacker.archiveOwnChannel();
     try {
-      const socket = new WebSocket(
-        `${url}/v1/ws?token=${t.attacker.token}&cursor=${t.attacker.channelId}:0`,
-      );
-      const ack = await firstFrame(socket, "connection.ack");
+      const socket = connect(url, t.attacker.token, `&cursor=${t.attacker.channelId}:0`);
+      const ack = await socket.waitFor("connection.ack");
       const cursor = (ack.payload as { cursor?: Record<string, number> }).cursor ?? {};
       expect(Object.keys(cursor)).toContain(t.attacker.channelId);
-      socket.close();
+      socket.socket.close();
     } finally {
       // IN A `finally`, BECAUSE THE TEST ABOVE LEARNED THIS THE OTHER WAY. It left a
       // removed membership behind and the next test failed on its control rather
       // than on its subject. An assertion that throws must still put the state back,
       // or the diagnosis lands in a file that did nothing wrong.
       await t.attacker.unarchiveOwnChannel();
@@ -324,17 +382,15 @@ describe("the socket refuses another tenant's identifiers", () => {
   it("a cursor naming the other tenant's channel backfills nothing", async () => {
     // Something to backfill, planted before the socket opens — a resume that finds an
     // empty channel proves nothing about whether it would have delivered.
     const text = `before the resume ${randomUUID()}`;
     await t.victim.say(text);
 
-    const socket = new WebSocket(
-      `${url}/v1/ws?token=${t.attacker.token}&cursor=${t.victim.channelId}:1`,
-    );
-    const frames = collect(socket);
-    const ack = await firstFrame(socket, "connection.ack");
+    const socket = connect(url, t.attacker.token, `&cursor=${t.victim.channelId}:1`);
+    const frames = socket.frames;
+    const ack = await socket.waitFor("connection.ack");
 
     // THE ACK ECHOES WHAT THE SERVER ACCEPTED, not what the client presented. A
     // channel this token cannot see is not in it — and asserting on the echo is the
     // cheap half, because a server that silently dropped the cursor and a server that
     // honoured it both answer with an ack.
     const cursor = (ack.payload as { cursor?: Record<string, number> }).cursor ?? {};
@@ -345,70 +401,258 @@ describe("the socket refuses another tenant's identifiers", () => {
     await quiet(1_000);
     const delivered = frames().filter((f) => f.type === "message.created");
     expect(delivered).toEqual([]);
     // And the buffer is not empty for an unrelated reason — the ack is in it, so a
     // collector that attached too late would fail here rather than pass vacuously.
     expect(frames().some((f) => f.type === "connection.ack")).toBe(true);
-    socket.close();
+    socket.socket.close();
   });
 
   it("a token minted by one tenant cannot open a session for the other", async () => {
     // The attacker asks its OWN api for a token naming the victim's user. Either the
     // mint refuses, or the session it opens must resolve nothing of the victim's.
     const borrowed = await mintToken(
       t.apiUrl,
       t.attacker.credential,
       t.victim.userExternalId,
     ).catch(() => null); // refused at the mint is the stronger answer
     if (borrowed === null) return;
-    const socket = new WebSocket(`${url}/v1/ws?token=${borrowed}`);
+    const socket = connect(url, borrowed);
     try {
-      const ack = await firstFrame(socket, "connection.ack");
+      const ack = await socket.waitFor("connection.ack");
       expect(JSON.stringify(ack)).not.toContain(t.victim.channelId);
     } catch {
       // A closed socket is a refusal, which is also correct.
-      await expect(closeCode(socket)).resolves.toBeGreaterThan(0);
+      await expect(socket.closedWith()).resolves.toBeGreaterThan(0);
     }
-    socket.close();
+    socket.socket.close();
   }, 20_000);
 
   // ── THE SAME-TENANT NON-MEMBER, ON THE SOCKET (T087) ───────────────────────
+
+  // ── 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 the credentials chapter 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 t.attacker.banSelf();
+    try {
+      const client = connect(url, t.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 t.attacker.unbanSelf();
+    }
+  });
+
+  it("stops an already-open socket from sending, and keeps delivering to it", async () => {
+    const client = connect(url, t.victim.token);
+    await client.waitFor("connection.ack");
+
+    await t.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: t.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 t.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 the removal test hit.
+    const { userExternalId, channelId, seq, witnessToken } =
+      await t.victim.seedDeletable();
+
+    const deleted = await fetch(`${t.apiUrl}/v1/users/${userExternalId}`, {
+      method: "DELETE",
+      headers: { authorization: `Bearer ${t.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 socket = connect(url, witnessToken, `&cursor=${channelId}:0`);
+    const ack = await socket.waitFor("connection.ack");
+    const cursor = (ack.payload as { cursor?: Record<string, number> }).cursor ?? {};
+    expect(Object.keys(cursor)).toContain(channelId);
+
+    const mine = await socket.waitFor("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 as Record<string, unknown>)["seq"]).toBe(seq);
+    expect((mine.payload as Record<string, unknown>)["user"]).toBe(userExternalId);
+    const frame = mine.payload as Record<string, unknown>;
+    expect(frame["text"]).toBe("sent before the deletion");
+    socket.socket.close();
+  });
+
+  // ── T134: THE PROFILE IS STORED AND THE WIRE DID NOT MOVE ─────────────────
+  //
+  // This chapter 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.
+  // The isolation harness 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(`${t.apiUrl}/v1/users/${t.victim.userExternalId}`, {
+      method: "PATCH",
+      headers: {
+        "content-type": "application/json",
+        authorization: `Bearer ${t.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 socket = connect(url, t.victim.token);
+    const ack = await socket.waitFor("connection.ack");
+    const identity = (ack.payload as { user?: unknown }).user;
+    expect(typeof identity).toBe("string");
+    expect(identity).toBe(t.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: t.victim.channelId,
+        seq: 1,
+        user: { id: t.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: t.victim.channelId,
+        seq: 1,
+        user: t.victim.userExternalId,
+        text: "hello",
+        created_at: new Date().toISOString(),
+        avatar_url: "https://cdn.example.com/face.png",
+      },
+    });
+    expect(bare.success).toBe(false);
+    socket.socket.close();
+  });
+
   //
   // The protocol's frame union has exactly one inbound member — `message.send` — so
   // there is no "subscribe" frame to attack: what a socket may see is decided at
   // connect, from the session's channel list, and a cursor is the only thing a client
   // gets to assert about it. So the socket's version of "a non-member reaches a
   // private channel" is a cursor naming one.
   //
   // The tenant's own user is not a member of the tenant's own private channel —
   // `seedSocketTenants` creates it and adds nobody — which makes this the same-tenant
   // case rather than the cross-tenant one every other attack here uses.
   it("a same-tenant non-member's cursor for a private channel is not accepted", async () => {
-    const socket = new WebSocket(
-      `${url}/v1/ws?token=${t.attacker.token}&cursor=${t.attacker.privateChannelId}:0`,
-    );
-    const frames = collect(socket);
-    const ack = await firstFrame(socket, "connection.ack");
+    const socket = connect(url, t.attacker.token, `&cursor=${t.attacker.privateChannelId}:0`);
+    const frames = socket.frames;
+    const ack = await socket.waitFor("connection.ack");
     const cursor = (ack.payload as { cursor?: Record<string, number> }).cursor ?? {};
     expect(Object.keys(cursor)).not.toContain(t.attacker.privateChannelId);
 
     // THE CONTROL IS THE REMOVAL TEST ABOVE: the same token's cursor for a channel it
     // IS a member of gets accepted there. Without that pair, an empty cursor set here
     // would pass whether the session was scoped or simply broken.
     await quiet(1_000);
     expect(frames().filter((f) => f.type === "message.created")).toEqual([]);
-    socket.close();
+    socket.socket.close();
   });
 
   // ── T048: the subscribe ────────────────────────────────────────────────────
   it("nothing from the other tenant's channel is delivered", async () => {
-    const socket = new WebSocket(`${url}/v1/ws?token=${t.attacker.token}`);
-    const frames = collect(socket);
-    await firstFrame(socket, "connection.ack");
+    const socket = connect(url, t.attacker.token);
+    const frames = socket.frames;
+    await socket.waitFor("connection.ack");
     await t.victim.say(`the victim speaks ${randomUUID()}`);
     // A DEADLINE RATHER THAN A RACE, and longer than the others because this one is
     // waiting on a fan-out that has to travel through Redis before it could arrive.
     await quiet(1_500);
     expect(frames().filter((f) => f.type === "message.created")).toEqual([]);
-    socket.close();
+    socket.socket.close();
   });
 });

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 {
  createApiKey,
  createEnvironment,
  environmentSigningSecret,
  Repository,
} from "../db/repository";
 
// THE LISTING, END TO END (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>;
 
  beforeAll(async () => {
    db = createDb(createPool());
    const env = await createEnvironment(db, { name: "users-itest" });
    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 (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
    // the isolation harness 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 (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);
  });
 
  // ── FR-029's HALF THAT WAITS FOR THE COUNTERS ──────────────────────────────
  //
  // "Billing history does not vanish with a profile": a customer who deleted a user
  // in March still owes for March. The assertion is that `usage_active_users` is
  // untouched by a deletion — and there is no `usage_active_users` yet. The usage
  // counters arrive with quotas, in movement VII.
  //
  // WHAT THE DELETION ALREADY DOES IS WHAT MAKES THAT ASSERTION POSSIBLE LATER: it
  // clears the profile fields, removes the memberships and read positions, sets
  // `deleted_at`, and touches nothing else. A deletion written as a CASCADE would
  // have to be unpicked when the counters arrive; this one does not, and the reason
  // is recorded on the column rather than here.
 
  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 (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 — the isolation harness'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);
  });
});