Skip to content

Open-source AI coworkers that each get a computer of their own: a browser, files and tools, with every action decided before it happens and recorded after. Bring any AG-UI agent.

95.6 KB
Raw Blame
import {
  classifyAttachment,
  MAX_ATTACHMENTS_PER_MESSAGE,
  MAX_FILE_BYTES,
  MAX_IMAGE_BYTES,
  namesNoFormat,
} from "@openbot/shared/attachments";
import {
  and,
  eq,
  exists,
  inArray,
  isNotNull,
  isNull,
  notExists,
  or,
  sql,
} from "drizzle-orm";
import type { Context, MiddlewareHandler } from "hono";
import { Hono } from "hono";
import type { AppVariables } from "../auth/guards";
import type { Database } from "../db/client";
import {
  attachments,
  channelMemberships,
  channels,
  intelligenceChannelMappings,
} from "../db/schema";
import { sniffMimeType } from "./attachment-mime";
import type { StoredAttachment } from "./attachment-parts";

function megabytes(bytes: number): string {
  return `${(bytes / (1024 * 1024)).toFixed(0)}MB`;
}

const UUID_SHAPE =
  /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;

/**
 * Whether a value even has the shape Postgres's `uuid` column could accept.
 *
 * `attachments.id` is a `uuid`, and every id in this file arrives as text somebody else chose: a
 * path param off a URL, or a slice of a message part. Comparing text that is not uuid-shaped
 * against that column raises `22P02` and throws before any row can fail to match, so each of the
 * three entry points below asks this first and answers "no such attachment" itself.
 */
function isUuidShaped(value: string): boolean {
  return UUID_SHAPE.test(value);
}

/**
 * What a turn running in one conversation may touch: files that belong to THAT conversation's
 * channel.
 *
 * A CONDITION, NOT A LOOKUP THE CALLER DOES. The obvious shape is to resolve the thread to a
 * channel id first and pass that id in beside the actor. That was rejected twice over. It is two
 * statements where one will do, so under READ COMMITTED the mapping can change between them; and,
 * far more importantly, it makes the scope a thing a caller REMEMBERS — a second `string` argument
 * beside `actorId`, which the next person to add a call site can pass the wrong value for, or the
 * right value from the wrong run, and get no complaint from anything. As a correlated subquery the
 * scope is part of the statement that reads the row: there is no way to ask for an attachment
 * without asking this at the same time, because it is the same query.
 *
 * The two callers take `threadId` in a NAMED FIELD for the same reason. `actorId` and `threadId`
 * are both `string`, adjacent, and positionally swappable with no type error and no test failure —
 * the swap would simply widen the scope back to what it was. An object parameter cannot be
 * transposed.
 *
 * AND IT DEGRADES WHEN THE THREAD MAPS TO NO CHANNEL, which is the half that took evidence to get
 * right rather than reasoning. The tempting shape is an INNER JOIN on
 * `intelligence_channel_mappings`: no mapping, no attachment. That is wrong, because three real
 * surfaces run turns on threads this deployment deliberately keeps no channel for:
 *
 *  - A FORWARD AGENT HOP. `handoff-delivery.ts` mints a scratch thread of the addressed Bot's own
 *    (an Intelligence thread has exactly one agent, so a second Bot cannot answer inside the
 *    first's conversation) and then seeds it with the ASKING channel's history, attachment parts
 *    and all. Under an inner join every one of those files would resolve to null on a thread that
 *    maps to nothing, and because history is resolved with `onMissing: "note"` the addressed Bot
 *    would be told `[attachment "x" is no longer available]` about files that exist and that the
 *    same person may read. It would not even fail loudly: a hop's last user message is the
 *    synthetic instruction `handoff-delivery.ts` appends, so the `"fail"` branch that exists to
 *    catch exactly this never covers a hop's history.
 *  - THE DIRECT `/bot` CHAT, whose thread comes from `POST /api/threads/mint` — "a conversation
 *    this deployment keeps no channel for", in that route's own words. Its runs go through the
 *    ordinary runtime endpoint and so reach this loader.
 *  - A BACKWARDS HOP relaying into either of those.
 *
 * So the rule is: if this thread belongs to a channel, the file must belong to that same channel;
 * if it belongs to no channel, this adds nothing and the membership join above remains the whole of
 * the check. That is strictly narrower than what was here before and never narrower than a working
 * surface needs, which is the only combination that closes the gap without breaking a hop.
 *
 * NOTE WHAT IT STILL STOPS, because the degrade reads more permissive than it is. The case the
 * reviewers reproduced is a person in channels A and B naming an A-file on a turn in B. B's thread
 * IS mapped, so the degrade branch does not apply, the subquery yields B, and A ≠ B refuses. The
 * branch only opens for a thread nobody is shown.
 */
function inTheTurnsChannel(database: Database, threadId: string) {
  const channelOfThisThread = () =>
    database
      .select({ channelId: intelligenceChannelMappings.channelId })
      .from(intelligenceChannelMappings)
      .where(eq(intelligenceChannelMappings.threadId, threadId));
  return or(
    inArray(attachments.channelId, channelOfThisThread()),
    notExists(channelOfThisThread()),
  );
}

/**
 * The file behind an attachment reference, read for one person's turn. A PURE READ: nothing about
 * being shown a file says anybody sent it, and this function writes nothing.
 *
 * PER ACTOR, AND THE JOIN IS THE CHECK — the live channel and the actor's membership on it, which
 * is the join the upload route leads with and the one `GET /api/attachments/:id` below uses. The id
 * this is called with came out of `input.messages`, which is the browser's: `resolveAttachmentParts`
 * (attachment-parts.ts) reads it out of a user message's own content, and nothing between the wire
 * and here rejects a message that was never in the thread — the runtime hands the input to the agent
 * and only filters what it PERSISTS. So a signed-in person can put an `/api/attachments/<id>` part
 * for a channel they are not in on a message they compose themselves, and no uuid needs guessing to
 * do it: somebody removed from a channel still holds its ids in their local transcript. This function
 * therefore cannot assume the asker is entitled to what they named, and checks membership itself
 * rather than inferring it from where the id came from.
 *
 * Null when no row is visible to this actor — no such attachment, an id that could not name one,
 * a channel that has since been deleted, or not theirs to see, the same answer for all four as on
 * the fetch route. `resolvePart` turns that into a failed turn naming the id, which is the correct
 * outcome: a turn that refers to an attachment the asker cannot see must not proceed to a model
 * that would read the file back to them.
 *
 * THIS USED TO STAMP `attachedAt`, and the reasoning was that a load past the membership join is
 * the closest thing to evidence of a send there is. It is not close enough, in two directions at
 * once. This function is called for the message being asked about AND for every attachment in the
 * history behind it, on every turn — so a read-time stamp said "sent" about every file anybody had
 * ever been shown, including one that is still staged in somebody's composer. And it is scoped to
 * MEMBERSHIP, because members are meant to see each other's sent files, so any member could freeze
 * a colleague's staged row by naming its id in a message of their own: that row then answered the
 * colleague's own withdrawal with a 409 for ever and the sweeper would never reclaim it either.
 *
 * The send writes the column instead. It goes out through AG-UI rather than through either router
 * in this file, so the write is made where the send is actually known to have happened — in
 * `inlineAttachments` (copilot.ts), which is the one place that can tell the message being asked
 * about from the history behind it — through {@link markAttachmentsSent}.
 *
 * IT DOES READ `attachedAt`, THOUGH, WHICH IS NEW. A staged row is a file nobody has shared with
 * anybody yet, so it is its uploader's alone until a send says otherwise; a sent one belongs to the
 * conversation and every member of the channel may read it. The asymmetry used to run one way only:
 * the comment below explains at length why a reader must not WRITE a colleague's staged row, and
 * then let any member read one.
 *
 * AND SCOPED TO THE TURN'S OWN CHANNEL, which it did not used to be. See
 * {@link inTheTurnsChannel} for the shape and for why it degrades on an unmapped thread instead of
 * refusing. What it closes: somebody in channels A and B could put an id from A on a message they
 * compose in B, and this returned the bytes. They already hold that file, so it was never
 * escalation — but it left a message in B whose file lives in a channel B has nothing to do with,
 * and the day A is deleted that message's attachment is permanently broken in B while the row is
 * still there. The run carries the thread id, so the channel behind it is knowable here now.
 */
export async function loadAttachmentForTurn(
  database: Database,
  turn: { actorId: string; threadId: string },
  id: string,
): Promise<StoredAttachment | null> {
  /*
   * The same uuid-shape guard as the two routes below, and it belongs here rather than at either
   * caller because this id is the one nothing shaped. A path param at least came off a route
   * pattern; this one comes out of `attachmentIdFor` (attachment-parts.ts), which slices whatever
   * follows `/api/attachments/` in a browser-supplied message part — so a query string, a second
   * path segment, and the empty string all arrive here as "ids".
   *
   * The consequence is worse here than on a route, too. A 500 is one failed request; this throw
   * lands inside `resolvePart`, which only degrades a NULL into the `onMissing: "note"` text. A
   * throw goes straight past that degradation, and history is replayed on every turn, so a single
   * malformed part would fail this channel's every future turn for ever, with no recovery but
   * starting another channel. An id no `uuid` column could hold is an id no row has: null.
   */
  if (!isUuidShaped(id)) return null;

  /*
   * `channels` is in the join, not only `channelMemberships`, and the difference is a channel that
   * has been deleted. Deletion here is soft — `channels.deletedAt` — so the channel row and every
   * membership on it survive it, and a join that asks only "is this actor a member" answers yes for
   * ever afterwards. This is the path that hands bytes to a MODEL rather than to a browser, so
   * without the channel term somebody in a deleted channel could name an id out of their own local
   * transcript on a message they compose themselves and have the file read back to them.
   */
  const [row] = await database
    .select({
      mimeType: attachments.mimeType,
      name: attachments.name,
      bytes: attachments.bytes,
    })
    .from(attachments)
    .innerJoin(
      channels,
      and(eq(channels.id, attachments.channelId), isNull(channels.deletedAt)),
    )
    .innerJoin(
      channelMemberships,
      and(
        eq(channelMemberships.channelId, attachments.channelId),
        eq(channelMemberships.userId, turn.actorId),
      ),
    )
    .where(
      and(
        eq(attachments.id, id),
        // The conversation this turn is running in, as a term in the same statement rather than as
        // a fact a caller looked up and passed. See {@link inTheTurnsChannel}.
        inTheTurnsChannel(database, turn.threadId),
        /*
         * Sent, or this actor's own. In the same statement as the membership join rather than read
         * and then decided on, so a send committing alongside cannot be missed by a check that
         * already ran — the same discipline the withdrawal route's WHERE follows.
         *
         * The uploader's half is not a courtesy: this runs for the message being sent BEFORE
         * {@link markAttachmentsSent} stamps it, so without it the ordinary first send of a file
         * would refuse to inline the very file it is sending.
         */
        or(
          isNotNull(attachments.attachedAt),
          eq(attachments.uploadedBy, turn.actorId),
        ),
      ),
    );
  return row ?? null;
}

/**
 * The ids a send could not prove it had recorded, carried out of the transaction that has to roll
 * back before anybody is told.
 *
 * A thrown value rather than a returned one because {@link markAttachmentsSent} must undo the stamps
 * it DID write when any one of them is missing, and drizzle rolls a transaction back on a throw and
 * on nothing else. It never leaves this module: the `.catch` below turns it into the sentence a
 * person reads, so no caller has to know this type exists to handle the failure correctly.
 */
class UnrecordedSend extends Error {
  constructor(readonly unrecorded: readonly string[]) {
    super(`Attachments were not recorded as sent: ${unrecorded.join(", ")}.`);
    this.name = "UnrecordedSend";
  }
}

/**
 * Records that these attachments went out in a message, on behalf of the person who sent it.
 *
 * WRITTEN BY THE SENDER, NOT BY A READER. `attachedAt` means one thing — "this file rode in a
 * message somebody actually sent" — and three readers depend on that one meaning:
 * `cull-staged-attachments.ts` treats a null as a staged file nobody came back for and eventually
 * deletes it; the upload route above treats a null as a slot still occupied against
 * `MAX_ATTACHMENTS_PER_MESSAGE`; and `DELETE /api/attachments/:id` treats a non-null as a promise
 * to a sent message and refuses to withdraw the file. A column written as a side effect of reading
 * cannot carry that meaning, because reading is not sending: history is replayed in full on every
 * turn, for whoever happens to be running it, so a read-time stamp says "sent" about every file
 * anybody has ever been shown.
 *
 * SCOPED TO THE UPLOADER, and that is a second, separate reason a read may not write this. Reading
 * is scoped to CHANNEL MEMBERSHIP, because members are supposed to see each other's sent files —
 * so a stamp on the read path let any member freeze a colleague's still-staged row simply by naming
 * its id in a message of their own. That row then answers the colleague's own DELETE with a 409 for
 * ever, and the sweeper will not reclaim it either, because both of those read `attachedAt` and
 * `attachedAt` now says the file was sent. Only the uploader's own send may stamp the uploader's
 * own row, so `uploadedBy` is in the WHERE beside the id.
 *
 * `isNull(attachments.attachedAt)` keeps the FIRST send, not the most recent one. A message is
 * replayed as history on every later turn and a stopped run is retried; neither is a new send, and
 * neither should move a timestamp that already means something.
 *
 * Ids that could not name a row are dropped before the query rather than passed to it. These come
 * out of `attachmentIdFor` (attachment-parts.ts), which slices whatever follows
 * `/api/attachments/` in a browser-supplied message part, so a query string, a second path segment
 * and the empty string all arrive here as "ids" — and comparing text that is not uuid-shaped
 * against a `uuid` column raises Postgres `22P02` and throws, exactly as it would in
 * {@link loadAttachmentForTurn}. Nothing left to ask about is not a query at all.
 *
 * AND SCOPED TO THE TURN'S OWN CHANNEL, by the same {@link inTheTurnsChannel} term the reader uses,
 * for a harm that was reproduced rather than reasoned about. A person in channels A and B could
 * name an id from A on a message they sent in B, and this stamped the row in A. Nothing in A ever
 * referred to it, and yet it was now un-withdrawable — the withdrawal route refuses a sent
 * attachment with a 409 — and unsweepable, because the culler only reclaims rows with a null
 * `attachedAt`. The person was left holding a file they could neither use nor get rid of, in a
 * channel that had never seen it. That is the same shape as the freeze the `uploadedBy` term above
 * exists to prevent, reached from the other direction.
 *
 * THE SAME TERM AS THE READER, NOT A STRICTER ONE, and that was a decision. A stricter write — an
 * inner join, so an unmapped thread stamps nothing — is unreachable for a hop (a hop's asked
 * message is `handoff-delivery.ts`'s synthetic instruction, which names no attachment, so the
 * `ids.length === 0` return above fires first) and so looked free. It was rejected because on the
 * one surface where it IS reachable, the direct `/bot` chat, it fails in exactly the direction this
 * column's whole purpose is to avoid: a file that really was sent silently never gets stamped, and
 * the culler deletes it a day later out from under a conversation that shows it. One rule, stated
 * once, is also one rule to keep true.
 *
 * IT RAISES WHEN IT CANNOT PROVE THE STAMP LANDED, AND IT USED TO SWALLOW. The sentence that stood
 * here said that a turn is a person waiting for an answer, and that bookkeeping which could not be
 * written is not worth failing that answer over. The first half is true. The second rested on a
 * premise that is false: that by the time this runs, the answer has been earned. It has not.
 * `inlineAttachments` (copilot.ts) calls this BEFORE it hands the history back, and that history is
 * what `super.run` / `next.run` is given afterwards — so at this moment no model has been called, no
 * token has been spent, and the person's message is still in front of them. Raising here costs a
 * retry of something that never started.
 *
 * Staying silent costs the FILE. The culler reclaims every row whose `attachedAt` is null, so a turn
 * that answered happily about an attachment it never stamped leaves a message displaying a file the
 * sweeper deletes a day later; the upload cap keeps counting the slot for ever in the meantime. That
 * damage is permanent, silent, and lands on somebody who did nothing but send a file. An answer
 * somebody can ask for again is the cheaper of the two losses, and this is called at the one point
 * in the turn where that trade is still on offer — which is why the third option, refusing before
 * the turn is spent, beats both halves of the dilemma rather than splitting it.
 *
 * Refusing here is also not a new KIND of outcome on this path. `resolvePart`'s `"fail"` mode
 * already refuses this same turn at this same moment when the asked message names a file that
 * cannot be loaded or cannot be afforded — same reason, same recovery, same sentence-shaped error.
 *
 * WHAT STILL DOES NOT RAISE, because the old swallow was not protecting nothing:
 *
 *  - AN ID THAT COULD NEVER NAME A ROW, dropped before the query as before. A browser part that is
 *    not an attachment reference is not a failed send.
 *  - NOTHING TO RECORD, which is still not a query at all.
 *  - HISTORY. Only the asked message's ids are passed in, so nothing behind it is stamped or
 *    checked, and a replayed thread does not acquire new ways to fail.
 *  - A ROW THAT IS ALREADY SENT. An `attachedAt` that is already set is a SUCCESS here, not a race
 *    lost: a stopped run retried and a message replayed are both ordinary, and a rule that failed
 *    them would be failing people for doing nothing wrong.
 *
 * ZERO UPDATED ROWS IS NOT WHAT IT CHECKS. Postgres reports an UPDATE that matched nothing as a
 * successful command (https://www.postgresql.org/docs/current/sql-update.html#SQL-UPDATE-OUTPUTS),
 * so the count is silent about the failure that matters — and it is also the wrong question, in both
 * directions. It reads zero for a row that was already stamped, which is a success, and zero for a
 * colleague's already-sent file named on this message, which the `uploadedBy` term above correctly
 * declines to touch. What has to be true is not "this statement changed something" but "this id is,
 * NOW, durably recorded as sent in this conversation", so the UPDATE is followed by a SELECT asking
 * exactly that, and every id that cannot answer it is named in the refusal.
 *
 * THAT SELECT DOES NOT REPEAT THE READER'S MEMBERSHIP JOIN, deliberately. Whether this actor may
 * see these files was settled by {@link loadAttachmentForTurn} earlier in the same turn, and a
 * second, subtly different copy of an access rule is a thing to keep in step rather than a check.
 * What is asked here is only what this function is responsible for — the row still exists, it is
 * stamped, and it is in this turn's channel.
 *
 * THE SELECT IS A SECOND STATEMENT, NOT A CTE HANGING OFF THE UPDATE, and that is the whole of the
 * idempotence. A data-modifying CTE and the query reading beside it share one snapshot, taken when
 * the statement began — so a row a neighbouring session stamped a moment ago is invisible to the
 * read, while the UPDATE's own re-check correctly declines to stamp it twice. Two concurrent runs of
 * the same message, or a retry overlapping the run it retries, would then refuse each other. Under
 * READ COMMITTED a separate statement takes a fresh snapshot and sees the neighbour's commit, which
 * is the answer that is actually true.
 *
 * IN A TRANSACTION, SO A REFUSED SEND LEAVES NO STAMPS BEHIND. A message may name several files and
 * only one of them need be missing. Keeping the others' stamps would record a send for a turn that
 * never ran, which is the exact freeze the paragraphs above are about: un-withdrawable, unsweepable,
 * and referred to by nothing. Throwing inside the transaction rolls them back, so a refusal puts the
 * rows back as the turn found them and the person's retry starts from a clean state.
 *
 * NO ADVISORY LOCK, AND THAT WAS CHECKED RATHER THAN ASSUMED. The upload route holds
 * `pg_advisory_xact_lock` because it counts rows and then inserts against that count, which is two
 * facts that must not drift apart. There is no count here. The stamp and the two things that can
 * take the row out from under it — `DELETE /api/attachments/:id` and
 * `server/scripts/cull-staged-attachments.ts`, both of which carry `attached_at is null` in their
 * own WHERE — contend for the same ROW, and a row lock already serialises them: whichever commits
 * second re-evaluates its own predicate against the row as it then stands. Withdrawal first, and
 * this UPDATE matches nothing while the SELECT finds no row, so the send is refused. Stamp first,
 * and the withdrawal's `attached_at is null` no longer holds so it deletes nothing, which is the 409
 * pinned by "a send landing mid-request cannot have its file deleted out from under it" in
 * attachment-routes.test.ts. They cannot both win. An advisory lock would be a second, weaker
 * mechanism laid over the one Postgres already applies to the row itself.
 *
 * The log names the actor and the ids, because every consequence of a missing stamp — a file the
 * sweeper reclaims, a slot that never frees — is about a specific person and a specific row, and a
 * line naming neither cannot be acted on. The raised message names the ids as well. There is no
 * `app.onError` behind this server, but this refusal never becomes a response status: it leaves
 * through the run as an AG-UI error, the road `resolvePart`'s refusals already take, so what the
 * composer receives is the sentence rather than a plain-text 500.
 */
export async function markAttachmentsSent(
  database: Database,
  turn: { actorId: string; threadId: string },
  ids: readonly string[],
): Promise<void> {
  const known = ids.filter(isUuidShaped);
  if (known.length === 0) return;

  const unrecorded = await database
    .transaction(async (transaction) => {
      await transaction
        .update(attachments)
        .set({ attachedAt: new Date() })
        .where(
          and(
            inArray(attachments.id, known),
            eq(attachments.uploadedBy, turn.actorId),
            // The channel this send actually happened in. See {@link inTheTurnsChannel}.
            inTheTurnsChannel(database, turn.threadId),
            isNull(attachments.attachedAt),
          ),
        );

      const recorded = await transaction
        .select({ id: attachments.id })
        .from(attachments)
        .where(
          and(
            inArray(attachments.id, known),
            inTheTurnsChannel(database, turn.threadId),
            isNotNull(attachments.attachedAt),
          ),
        );

      const durable = new Set(recorded.map((row) => row.id));
      const missing = known.filter((id) => !durable.has(id));
      // Thrown rather than returned, because the rollback is the point: see the paragraph above on
      // what keeping a partial set of stamps would leave behind.
      if (missing.length > 0) throw new UnrecordedSend(missing);
      return [];
    })
    .catch((error: unknown) => {
      /*
       * A failure that is not the verification's own is a failure to reach the database at all — a
       * lost connection, an exhausted pool, a `statement_timeout`. Nothing is known to have landed
       * and the transaction took back anything that had, so every id is unrecorded.
       */
      const unrecorded: readonly string[] =
        error instanceof UnrecordedSend ? error.unrecorded : known;
      // The ids that are actually unaccounted for, not the whole list that was asked about: a
      // message may name four files and have one of them go missing, and it is the one that has to
      // be findable from a log line.
      console.error(
        `Could not record attachments as sent for ${turn.actorId} in ${turn.threadId}: ${unrecorded.join(", ")}.`,
        error,
      );
      return unrecorded;
    });

  if (unrecorded.length === 0) return;

  const named = unrecorded.map((id) => `"${id}"`).join(", ");
  throw new Error(
    `Your message wasn't sent because ${unrecorded.length === 1 ? "an attachment on it" : "attachments on it"} couldn't be saved (${named}). ` +
      "Attach the files again and resend.",
  );
}

/** The columns `POST /:channelId/attachments` hands back on success. */
type InsertedAttachment = {
  id: string;
  name: string;
  mimeType: string;
  sizeBytes: number;
};

/**
 * The one row the upload's single statement comes back with, whatever it decided.
 *
 * Nullable columns are the refusal: nothing was inserted, and `waiting`, `held` and `isMember` are
 * then the three facts that say which refusal it was — asked in the SAME statement as the insert
 * rather than after it, which is the whole point (see the comment on the statement itself).
 */
type UploadAttempt = {
  id: string | null;
  name: string | null;
  mimeType: string | null;
  sizeBytes: number | null;
  waiting: number;
  /**
   * Every unsent row this person holds, in every channel and every group — the number
   * {@link MAX_STAGED_ATTACHMENTS_PER_UPLOADER} is compared against.
   *
   * Separate from `waiting` and not derivable from it: `waiting` is one bucket of one composer
   * session, this is the whole of what one person has staged, and a refusal has to name whichever
   * of the two actually refused.
   */
  held: number;
  isMember: boolean;
};

/**
 * The group a client that names none is counted in.
 *
 * A tab left open across a deploy is running the JavaScript from before it, which sends no
 * `uploadGroup` at all. Left ungrouped those rows would have NULL here, `null = <anything>` is never
 * true, and the cap would count zero of them and never refuse anything — a hole, not a fallback. So
 * every group-less upload from one person in one channel shares this one bucket, which is exactly
 * the per-channel counting this server did before the column existed. Old clients keep old
 * behaviour; new ones get the per-composer count.
 *
 * No UUID can collide with it, and `newId()` only mints UUIDs.
 */
const LEGACY_UPLOAD_GROUP = "legacy";

/**
 * How many unsent attachments ONE PERSON may hold across the whole deployment, whatever they call
 * their upload groups and whichever channels they are in.
 *
 * NOT THE CAP, AND NOT A REPLACEMENT FOR IT. {@link MAX_ATTACHMENTS_PER_MESSAGE} is the number the
 * composer knows, shows and refuses on, counted per `upload_group` for the reasons the route's own
 * comment gives at length. This is a backstop underneath it, and it exists because that cap counts
 * a bucket the CLIENT names: `uploadGroup` arrives as a form field, `uploadGroupOf` deliberately
 * does not validate its value, and a caller that mints a fresh one on every request therefore has
 * zero prior rows in every bucket it is ever counted against. The cap never fires, and nothing else
 * bounded staged `bytea` — the only remaining ceilings were the ~8 MB per-request body limit and
 * `scripts/cull-staged-attachments.ts`, whose default window is 24 hours. Reproduced through this
 * route before it was closed: 33 uploads, a fresh `newId()`-shaped group on each, 33 rows written.
 *
 * PER UPLOADER, ACROSS ALL CHANNELS, and both halves of that were forced.
 *
 * - Per uploader rather than per deployment, because a deployment-wide ceiling is a ceiling one
 *   person can sit on: fill it and every colleague's upload is refused for a sentence naming
 *   nothing they did. It also makes every upload contend on one counter.
 * - Across all channels rather than per channel, because `POST /api/channels` is open to any
 *   authenticated user (`channels/routes.ts`). A per-channel backstop is a backstop a client moves
 *   by creating a channel, which is the same defect as one it moves by choosing a string — a few
 *   more bytes per bucket, still unbounded.
 *
 * ROWS RATHER THAN BYTES, which was the harder call. A row here is at most
 * `MAX_IMAGE_BYTES` (8 MiB), so counting rows bounds bytes too, at 32 x 8 MiB = 256 MiB of staged
 * blobs per person — a real bound, derived rather than declared. A separate `sum(size_bytes)`
 * ceiling was written and dropped: it buys a tighter storage number at the cost of a second limit
 * to keep honest, a second refusal sentence for a person to make sense of, and a test that has to
 * push a quarter of a gigabyte through the route to prove it. If the byte ceiling ever needs to be
 * independent of the file ceiling — an attachment kind larger than 8 MiB, say — that is the moment
 * to add it, and this comment is where to say so.
 *
 * FOUR MESSAGES' WORTH, deliberately loose. Ordinary work never comes near it: the composer refuses
 * a ninth file per message, so reaching 32 means four full eight-file messages composed and left
 * unsent at the same time, inside the sweeper's 24-hour window. Two tabs — the case the per-group
 * cap exists to serve — is sixteen. The looseness is the point: this number is not meant to be the
 * limit anybody experiences, only the one nobody can walk past.
 *
 * NO NEW INDEX, AND THAT WAS CHECKED RATHER THAN ASSUMED. The count this drives asks
 * `uploaded_by = ? and attached_at is null`, and the obvious worry is that it degrades with an
 * uploader's HISTORY — `attachments_uploaded_by_idx` covers every row they ever uploaded, almost
 * all of them long since sent. It does not, because a better index already exists for it:
 * `attachments_staged_idx` is partial on `attached_at is null`, so it holds only the staged rows in
 * the deployment. Measured on this deployment's Postgres with 50,000 SENT rows for one uploader,
 * the planner takes that partial index and filters `uploaded_by` inside it — one shared buffer,
 * 0.011 ms. The set it scans is the deployment's staged rows, which the sweeper keeps short-lived
 * and which THIS CONSTANT now bounds per person, so the query gets cheaper for the same reason it
 * exists. A partial index on `(uploaded_by) where attached_at is null` would be tighter still; it
 * is not worth a migration until a deployment is seen where it is.
 *
 * WHAT IT CAN COST AN HONEST PERSON, stated because a backstop that cannot be reached honestly is
 * not the same as one that cannot be reached awkwardly. Staged rows in a channel that was
 * soft-deleted, or that this person was removed from, cannot be withdrawn (`DELETE
 * /api/attachments/:id` joins live channels and membership), so they hold their place until the
 * sweeper takes them — which is as long as this deployment's operator has configured, and for ever
 * if they have turned the sweep off. Thirty-two of those would refuse the next upload anywhere for
 * that whole time. That is the same shape of fault the per-channel cap used to have at EIGHT, which
 * is precisely why this sits four times higher, and why the refusal names the deployment rather than
 * a deadline it cannot promise.
 */
export const MAX_STAGED_ATTACHMENTS_PER_UPLOADER =
  4 * MAX_ATTACHMENTS_PER_MESSAGE;

/**
 * The longest group this route will count a row under.
 *
 * A real client sends `newId()`, which is a 36-character UUID, so nothing legitimate comes within
 * an order of magnitude of this. What the bound is really for is that a longer one FAILS rather
 * than merely wasting space: `upload_group` is the third column of `attachments_upload_group_idx`
 * (schema/core.ts), and a btree entry may not exceed about 2704 bytes. An incompressible group of
 * 2600 bytes makes the INSERT itself fail — measured against this deployment's Postgres, 2000 bytes
 * stored fine and 2600, 2700, 3000 and 8000 all raised.
 *
 * WHERE THAT FAILURE LANDS, SINCE THIS COMMENT USED TO GET IT WRONG. It claimed the group was also
 * interpolated into the `pg_advisory_xact_lock` key, so that an unstorable one failed there instead,
 * "one statement earlier, and before the insert is even attempted". That was true while the lock was
 * keyed on `(channel, uploader, group)`; the lock was since widened to the uploader alone — the key
 * is `attachment-cap-<actorId>` and carries no group at all — and the sentence was left behind.
 * Re-measured: the advisory lock takes an 8000-byte group and a NUL-bearing one without complaint.
 *
 * The group's first and only appearance is the upload's own CTE statement, which counts and inserts
 * together, so an over-long group is refused by the index at the moment the insert runs and there is
 * no earlier statement for it to fail in. Nothing is written either way, and the transaction's
 * `.catch` turns the raise into the route's 503 rather than into a plain-text 500.
 *
 * 128 rather than something closer to the index's own ceiling because the bound is not really about
 * the index: it is about the difference between a value a composer can plausibly have minted and
 * one nothing in this app produces.
 */
const MAX_UPLOAD_GROUP_LENGTH = 128;

/**
 * Which composer session this upload belongs to, as the browser named it.
 *
 * The VALUE needs no validation and gets none: the column is `text`, it is only ever compared for
 * equality against other rows the SAME person staged in the SAME channel, and the worst a person
 * can do by choosing their own is give themselves a second bucket of eight — which two tabs already
 * do, below.
 *
 * The SHAPE is a different question, and the comment here used to conflate the two. A group that is
 * too long for {@link MAX_UPLOAD_GROUP_LENGTH}, or that carries a U+0000 — which Postgres refuses
 * in a `text` value at all, `22021`, before any column is reached — does not give its sender a
 * second bucket. It fails the one statement the group ever appears in — the upload's own
 * count-and-insert CTE — so nothing is written and the upload is refused outright, as a 503 the
 * person can do nothing about. Both were reproduced through this route against the local Postgres.
 *
 * Neither is refused, though: both fall back to {@link LEGACY_UPLOAD_GROUP}, exactly as a request
 * that named no group at all does. A group is a client-side grouping hint, not something anybody
 * asked for, so a hint this server cannot store is a hint it can do without — and treating it as a
 * hard refusal would turn a stale or third-party client's cosmetic mistake into an upload it can
 * never complete, which is a worse answer than counting its files in the same bucket every
 * group-less upload already shares.
 */
function uploadGroupOf(formData: FormData): string {
  const value = formData.get("uploadGroup");
  if (typeof value !== "string") return LEGACY_UPLOAD_GROUP;
  if (value.length === 0 || value.length > MAX_UPLOAD_GROUP_LENGTH) {
    return LEGACY_UPLOAD_GROUP;
  }
  // A NUL is not a length problem and would survive the check above, so it is asked separately:
  // Postgres refuses U+0000 in a `text` value outright (`22021`), which takes down the whole
  // count-and-insert statement the group is bound into — the only statement it reaches, since the
  // advisory lock above it is keyed on the uploader and carries no group.
  return value.includes("\u0000") ? LEGACY_UPLOAD_GROUP : value;
}

/**
 * The longest filename this route will store, in UTF-8 bytes.
 *
 * The fetch route echoes a stored name into `Content-Disposition` TWICE — once quoted, once
 * percent-encoded for `filename*` — and percent-encoding can triple a non-ASCII byte, so the header
 * runs to roughly four times the name. Nothing capped the name, and a multipart `filename`
 * parameter can be as long as the body allows: a 4000-character name was measured through these
 * two routes producing a 32 KB `Content-Disposition`. Bun serves that without complaint, but every
 * common reverse proxy caps response headers at 4-8 KB, so behind an ingress that attachment is not
 * a large download — it is a row nobody can ever fetch again, and no part of this app would say
 * why.
 *
 * 255 bytes because that is the limit almost every filesystem imposes, so it is the number a name
 * that came off somebody's disk has already been through. It holds the header under about 1 KB.
 *
 * TRUNCATED RATHER THAN REFUSED, and the extension is not preserved. A name is a property OF a file
 * somebody chose to send; refusing the file over it would be refusing content this app can read for
 * a reason that has nothing to do with the content. The stored name is what the 201 hands back, so
 * the composer shows what was actually kept rather than what was sent. Rebuilding a `.png` tail
 * onto the cut name was considered and dropped: it is more code and more edge cases (no dot, a dot
 * at the end, a 300-byte "extension") for a case that only arises with a name no filesystem would
 * have held in the first place.
 */
const MAX_FILENAME_BYTES = 255;

const utf8 = new TextEncoder();

/** A filename cut to {@link MAX_FILENAME_BYTES}, never through the middle of a character. */
function withinFilenameLimit(name: string): string {
  if (utf8.encode(name).length <= MAX_FILENAME_BYTES) return name;
  let kept = "";
  let bytes = 0;
  // Code points, not code units, so the cut cannot split a character into a lone surrogate — and
  // never more than 256 iterations, because it stops the moment the budget is spent.
  for (const character of name) {
    const size = utf8.encode(character).length;
    if (bytes + size > MAX_FILENAME_BYTES) break;
    bytes += size;
    kept += character;
  }
  return kept;
}

/**
 * The parenthetical that names a refused file's type, or nothing at all when there is nothing to
 * name.
 *
 * WHAT IS ASKED IS `namesNoFormat`, NOT WHETHER THE STRING IS EMPTY, and the difference is the
 * whole point. This started as an emptiness test, because `sniffMimeType` used to hand back the
 * claim itself when the bytes corroborated nothing — and the claim can be `""`, which this sentence
 * interpolated into `'archive' is not a file type this app can read ().` An empty parenthetical is
 * strictly worse than the generic sentence it was written to improve on, and the composer shows
 * this string verbatim, so it is the only explanation anybody gets.
 *
 * `sniffMimeType` no longer returns a claim that names nothing — it answers
 * `application/octet-stream` instead, which is the honest answer to "what are these bytes" and the
 * right contract for a sniffer. That silently defeated an emptiness test: the parenthetical came
 * back, now reading `(application/octet-stream)`, which names a string nobody chose and tells the
 * reader less than saying nothing would. Both changes are right; only asking the question the
 * shared list already answers composes them. A ninth thing that names no format gets added to
 * `MIME_NAMES_NOTHING` and this sentence keeps working.
 *
 * A BLANK CLAIM IS REACHED THROUGH THE FILENAME, not through the request. Measured against this
 * deployment's Bun: the multipart parser ignores a part's own `Content-Type` header entirely and
 * derives `File.type` from the filename's EXTENSION — `photo.png` declared `text/plain` arrives as
 * `image/png`, `drawing.svg` declared `text/plain` arrives as `image/svg+xml`, and a name with no
 * extension at all arrives as `""`. So this is not an exotic path: it is any file whose name has no
 * dot in it, and the bytes not being valid UTF-8 is the rest of it.
 */
function describeType(mimeType: string): string {
  return namesNoFormat(mimeType) ? "" : ` (${mimeType})`;
}

/**
 * A channel's upload door: one file in, one staged row out, or a reason it was refused.
 *
 * THE TRADE-OFF THE GROUPING MAKES, WRITTEN DOWN BECAUSE IT IS A REAL BEHAVIOUR CHANGE. The cap
 * counted every unsent row this person had in this channel; it now counts the ones staged by one
 * composer session. Two tabs open on the same channel are two sessions, so they get eight each
 * rather than eight between them. That is accepted deliberately: the cap is documented and enforced
 * as a PER-MESSAGE limit everywhere else (`MAX_ATTACHMENTS_PER_MESSAGE`, and the composer's own
 * screen), two tabs are two messages, and the alternative — the per-channel count — is what made a
 * closed tab's leftovers refuse a pick the client had already accepted, naming files nobody could
 * see. A cap that occasionally allows a second message's worth is a far smaller fault than one that
 * bricks uploads in a channel for 24 hours.
 *
 * THAT PARAGRAPH DESCRIBED HALF THE PICTURE UNTIL `MAX_STAGED_ATTACHMENTS_PER_UPLOADER` EXISTED.
 * "A second bucket of eight" is what a second TAB gets. It is not what a CALLER gets, because the
 * bucket is named by a form field this route does not validate: a fresh group on every request has
 * nothing in it to count, so the cap never fires, and the number of buckets is however many strings
 * the caller cares to type. Nothing else bounded staged `bytea` — the body limit is per request,
 * and the culler runs on a 24-hour window — so one authenticated member could stage without limit
 * into the only table in this deployment that holds blobs. Reproduced through this route before it
 * was closed, not argued from the code.
 *
 * SO THERE ARE TWO NUMBERS NOW, AND THEY ARE DIFFERENT KINDS OF NUMBER. The cap is the one the
 * composer knows, shows and is refused by, counted over a bucket the client names — because the set
 * on the screen is the only set the client can reason about. The backstop is counted over every
 * unsent row one person holds, in every channel and every group, because that is the only scope a
 * client cannot move. It sits four messages higher so that everything the paragraph above accepts —
 * two tabs, a closed tab's leftovers, a stopped run — still fits comfortably underneath it. The
 * trade survives; what it no longer does is run to infinity.
 *
 * The refusal is the point of this route as much as the upload is. `classifyAttachment` and
 * `sniffMimeType` between them already decide, byte-for-byte, whether a file is something a Bot can
 * read; this handler's job is to turn "no" into a sentence a person sees, naming what the file
 * actually was rather than "unsupported file type" — the composer surfaces the string verbatim, so
 * it is the only explanation anybody gets.
 *
 * Mounted at `/api/channels` by whoever wires the app together; this file only builds the router.
 */
export function createChannelAttachmentRoutes(
  database: Database,
  requireUser: MiddlewareHandler<{ Variables: AppVariables }>,
): Hono<{ Variables: AppVariables }> {
  const routes = new Hono<{ Variables: AppVariables }>();

  routes.post("/:channelId/attachments", requireUser, async (context) => {
    const actor = context.var.actor;
    const channelId = context.req.param("channelId");

    /*
     * The join IS the membership check, not a separate select run after one: a row comes back only
     * when this channel exists, is not soft-deleted, and this actor has a membership row on it. No
     * row, for any of those three reasons, reads the same from here on: a member of somebody else's
     * channel learns nothing about whether it exists.
     *
     * NOT THE CHECK THE INSERT STANDS ON, though — this one only refuses early, before the file is
     * read off the wire. The insert below carries the same join itself, because a check up here and
     * an insert down there are two statements with the whole of the upload between them.
     */
    const membership = await database
      .select({ id: channels.id })
      .from(channels)
      .innerJoin(
        channelMemberships,
        and(
          eq(channelMemberships.channelId, channels.id),
          eq(channelMemberships.userId, actor.id),
        ),
      )
      .where(and(eq(channels.id, channelId), isNull(channels.deletedAt)))
      // Guarded like every other database call on these routes, and this is the one that runs
      // FIRST: without it a database that cannot answer takes the door out before the body is even
      // read, as a plain-text 500 the composer can only report as "could not upload".
      .catch((error: unknown) => {
        console.error(
          `Could not check ${actor.id}'s membership of ${channelId} for an upload.`,
          error,
        );
        return null;
      });

    if (!membership) {
      return context.json(
        { error: "Couldn't upload that file. Try again." },
        503,
      );
    }

    if (membership.length === 0) {
      return context.json({ error: "You're not in this conversation." }, 403);
    }

    /*
     * Every other body parse in this server is `.catch(() => null)` so a
     * malformed body reads as a refusal, not a crash. `formData()` throws
     * `ERR_FORMDATA_PARSE_ERROR` on a non-multipart body, and there is no
     * `app.onError` to catch it — left unguarded, that throw becomes a
     * plain-text 500 instead of the `{ error }` body the client reads off
     * every failure.
     */
    const formData = await context.req.formData().catch(() => null);
    if (!formData) {
      return context.json(
        { error: 'Send the file as multipart form data under a "file" field.' },
        400,
      );
    }
    const file = formData.get("file");
    if (!(file instanceof File)) {
      return context.json(
        { error: 'Attach a file under the "file" field.' },
        400,
      );
    }

    const bytes = new Uint8Array(await file.arrayBuffer());
    const mimeType = sniffMimeType(bytes, file.type);
    const kind = classifyAttachment(mimeType);

    // Bounded once, here, so the same name is what gets stored AND what every refusal below quotes
    // back: a sentence naming a file by a name the row does not carry would be its own small lie.
    const name = withinFilenameLimit(file.name);

    if (kind === "unsupported-image") {
      const error =
        mimeType === "image/svg+xml"
          ? `'${name}' can't be attached. SVG files aren't accepted.`
          : `'${name}' can't be attached. OpenBot can't read ${mimeType} images.`;
      return context.json({ error }, 415);
    }

    if (kind === "unsupported") {
      return context.json(
        {
          error: `'${name}' isn't a file type OpenBot can read${describeType(mimeType)}.`,
        },
        415,
      );
    }

    if (kind === "image" && bytes.byteLength > MAX_IMAGE_BYTES) {
      return context.json(
        {
          error: `'${name}' is larger than the ${megabytes(
            MAX_IMAGE_BYTES,
          )} limit for images.`,
        },
        413,
      );
    }

    if (kind === "text" && bytes.byteLength > MAX_FILE_BYTES) {
      return context.json(
        {
          error: `'${name}' is larger than the ${megabytes(
            MAX_FILE_BYTES,
          )} limit for files.`,
        },
        413,
      );
    }

    const uploadGroup = uploadGroupOf(formData);

    /*
     * THE COUNT AND THE INSERT ARE ONE STATEMENT, UNDER ONE LOCK.
     *
     * It used to be a count, a comparison, and then an insert. Two uploads in flight at once both
     * counted seven and both inserted, and the person held nine — the same time-of-check /
     * time-of-use hole `withEnabledCapLock` in routines/store.ts was written for, and this file is
     * the more reachable one: dropping eight files on the composer fires eight uploads in parallel
     * by design.
     *
     * BOTH HALVES ARE LOAD-BEARING, and one without the other does not close it. Folding the guard
     * into the insert's own `where` means no row can be written by a statement whose count did not
     * permit it. But under READ COMMITTED that count still runs against a snapshot taken before the
     * other transaction committed, so two such statements can still each see seven. The advisory
     * lock is what makes the counts authoritative: it serialises every upload by one uploader, so
     * the second one's counts are both taken after the first one's row is committed and visible.
     *
     * THE KEY IS THE UPLOADER, AND IT USED TO BE (channel, uploader, group). That was the right
     * scope while the group cap was the only thing being counted, and it is the wrong scope now:
     * `MAX_STAGED_ATTACHMENTS_PER_UPLOADER` is counted over everything one person has staged, so a
     * lock keyed on the group serialises the uploads that share a bucket and nothing else — which
     * is to say it serialises none of the uploads a client varying its group sends. Two of those
     * arriving together would both count 31, both pass, and the person would hold 33: the same
     * READ COMMITTED hole the cap itself had to be fixed for, rebuilt one level up.
     *
     * Widening it costs one person's own parallel uploads their parallelism ACROSS TABS AND
     * CHANNELS rather than only within one composer. That is cheap where it lands: the bytes are
     * already off the wire, sniffed and classified before this transaction opens, so what takes
     * turns is one INSERT each, and eight files dropped on one composer already took turns here.
     * Nobody else's uploads wait on this person's.
     *
     * One lock rather than two — an uploader lock plus the old group lock — because the wider one
     * strictly contains the narrower: holding it makes BOTH counts authoritative, and a second lock
     * would add a second round trip and an acquisition order to get wrong.
     *
     * Transaction-scoped (`_xact_`), so the commit or the rollback releases it rather than us
     * remembering to. `hashtext` collisions are harmless: two unrelated uploaders sharing a hash
     * take turns, which is slower and not wrong.
     *
     * THE MEMBERSHIP IS IN THE SAME STATEMENT, for the same reason and against a slower race. The
     * handler checks membership at the top and used to insert on the strength of what it had read —
     * with `await file.arrayBuffer()`, the sniff and the classification in between, so the window is
     * as wide as reading an upload off the wire, not as wide as a scheduler tick. A removal landing
     * in it put a file into a channel its uploader had just been taken out of. Selecting the row to
     * insert FROM the channel-and-membership join closes that: the values are only produced if the
     * join still produces a row when the insert runs, so there is no moment at which a non-member's
     * file can land.
     *
     * Zero rows back is now the refusal for either reason — the cap, or no live channel this actor
     * is in — so the branch below has to ask which before it can name one.
     */
    const outcome = await database
      .transaction(async (transaction) => {
        await transaction.execute(
          sql`select pg_advisory_xact_lock(hashtext(${`attachment-cap-${actor.id}`}))`,
        );

        /*
         * THE COUNT THE REFUSAL REPORTS IS THE COUNT THAT REFUSED, because they are the same number
         * in the same statement.
         *
         * This used to be an insert followed, on the refusal path, by a second SELECT that counted
         * the staged rows again. Both ran inside this transaction, but READ COMMITTED gives each
         * STATEMENT its own snapshot, and the advisory lock serialises other UPLOADS in this group —
         * not `DELETE /api/attachments/:id`. A withdrawal committing between the two produced
         * "you already have 7 attachments waiting" while the cap is 8, and a composer dropping a
         * whole queued message produced 0. The insert was right to refuse both times; only the
         * sentence was wrong, and a sentence that contradicts the refusal is worse than no number.
         *
         * As CTEs there is one snapshot for all three parts: `staged` is literally the number
         * `written`'s `where` compared against, so the refusal cannot report a count that would not
         * have refused. `membership` is in the same statement for the same reason it was folded into
         * the insert in the first place — a check that ran afterwards would be answering a question
         * about a later state than the one that decided.
         *
         * `staged` aggregates without a GROUP BY, so it is always exactly one row, and the LEFT JOIN
         * therefore always yields exactly one row whether or not anything was inserted. That is what
         * lets one shape carry all three outcomes.
         */
        const [attempt] = (await transaction.execute(sql`
        with membership as (
          select channels.id
          from channels
          join channel_memberships
            on channel_memberships.channel_id = channels.id
            and channel_memberships.user_id = ${actor.id}::text
          where channels.id = ${channelId}::text
            and channels.deleted_at is null
        ),
        staged as (
          select count(*)::int as waiting
          from attachments
          where attachments.channel_id = ${channelId}::text
            and attachments.uploaded_by = ${actor.id}::text
            and attachments.upload_group = ${uploadGroup}::text
            and attachments.attached_at is null
        ),
        held as (
          select count(*)::int as waiting
          from attachments
          where attachments.uploaded_by = ${actor.id}::text
            and attachments.attached_at is null
        ),
        written as (
          insert into attachments (channel_id, uploaded_by, upload_group, name, mime_type, size_bytes, bytes)
          select
            membership.id,
            ${actor.id}::text,
            ${uploadGroup}::text,
            ${name}::text,
            ${mimeType}::text,
            ${bytes.byteLength}::integer,
            ${Buffer.from(bytes)}::bytea
          from membership, staged, held
          where staged.waiting < ${MAX_ATTACHMENTS_PER_MESSAGE}::integer
            and held.waiting < ${MAX_STAGED_ATTACHMENTS_PER_UPLOADER}::integer
          returning id, name, mime_type, size_bytes
        )
        select
          written.id as "id",
          written.name as "name",
          written.mime_type as "mimeType",
          written.size_bytes as "sizeBytes",
          staged.waiting as "waiting",
          held.waiting as "held",
          exists (select 1 from membership) as "isMember"
        from staged
        cross join held
        left join written on true
      `)) as unknown as UploadAttempt[];

        if (attempt.id !== null) {
          return {
            inserted: {
              id: attempt.id,
              name: attempt.name as string,
              mimeType: attempt.mimeType as string,
              sizeBytes: attempt.sizeBytes as number,
            } satisfies InsertedAttachment,
          };
        }

        /*
         * Membership first, because "you are not in this channel" and "you have too many files
         * staged" are both reasons for nothing being written and only one of them is true.
         *
         * Then the per-message cap ahead of the backstop, because where both are true the cap is
         * the one that can be acted on: it is the limit the composer already shows, counted over
         * the files on the screen in front of the person. Being told about everything they hold
         * everywhere, while this composer sits at eight, sends them hunting through other channels
         * for a problem that is on this one. The backstop's sentence is only ever the answer when
         * the cap would have let this file through.
         */
        if (!attempt.isMember) return { forbidden: true } as const;
        if (attempt.waiting >= MAX_ATTACHMENTS_PER_MESSAGE) {
          return { staged: attempt.waiting };
        }
        return { held: attempt.held };
      })
      /*
       * EVERY OTHER FAILURE ON THIS ROUTE ANSWERS `{ error }`, AND SO DOES THIS ONE.
       *
       * The `formData()` call three dozen lines above is wrapped for exactly this reason — there is
       * no `app.onError` anywhere behind this router, so an unguarded throw is Hono's default
       * plain-text `Internal Server Error` — and then the database calls underneath it were not.
       * The composer reads `{ error }` off every failed upload and falls back to a generic
       * `Could not upload "<name>"` when the body will not parse as JSON, so a lost connection
       * during a rollout, a `statement_timeout`, a lock timeout on the advisory lock, a `53100`
       * disk-full on an 8 MiB insert and a serialisation failure were all the same unactionable
       * sentence, with nothing written to the log either.
       *
       * LOGGED WITH WHAT IT WOULD TAKE TO ACT ON IT — who, which channel, and how big the file was
       * — because these are operator faults rather than uploader mistakes, and the one refusal
       * whose cause lives on this side of the wire is the one nobody could otherwise see.
       *
       * 503 rather than 500: every failure in that list is a "the store is not able to take this
       * right now" and is worth retrying, which is what the sentence tells the person to do.
       */
      .catch((error: unknown) => {
        console.error(
          `Could not store an attachment for ${actor.id} in ${channelId} (${mimeType}, ${bytes.byteLength} bytes).`,
          error,
        );
        return { unavailable: true } as const;
      });

    if ("unavailable" in outcome) {
      return context.json(
        { error: "Couldn't upload that file. Try again." },
        503,
      );
    }

    // The same sentence and the same status as the check at the top of the handler, because from
    // the uploader's side it is the same refusal — it just became true later than that check ran.
    if ("forbidden" in outcome) {
      return context.json({ error: "You're not in this conversation." }, 403);
    }

    /*
     * NOT "IN THIS CHANNEL", WHICH IS THE COUNT THIS SERVER STOPPED COMPUTING.
     *
     * The cap is counted per composer session now — that is what `upload_group` is for, and the
     * header comment above explains why — so a person with a full tab A and an empty tab B was
     * being told about a channel total nobody computes, and sent hunting for files that are on
     * another screen. That is the exact confusion the grouping was introduced to end.
     *
     * The limit is named beside the count because the limit is the actionable half: the count says
     * what is true now, and `MAX_ATTACHMENTS_PER_MESSAGE` says what to do about it. The count can
     * no longer be below the limit — the statement that refused is the statement that counted — so
     * the two can never contradict each other in the same sentence.
     */
    if ("staged" in outcome) {
      return context.json(
        {
          error: `You can attach ${MAX_ATTACHMENTS_PER_MESSAGE} files to a message, and ${outcome.staged} are already waiting to send.`,
        },
        409,
      );
    }

    /*
     * THE BACKSTOP'S REFUSAL, AND IT DELIBERATELY DOES NOT SOUND LIKE THE CAP'S.
     *
     * Nobody reaching this has filled the composer in front of them — the branch above would have
     * answered if they had. They are holding four messages' worth of unsent files somewhere, and
     * the sentence has to say so, or this is the 409 nobody could act on all over again: "you can
     * attach 8 files to a message", said to a composer holding one, is an instruction to go looking
     * for seven files that are not there.
     *
     * NAMES THE SCOPE AND THE WAY OUT. "Across your channels" because the files need not be in this
     * one, and sending or removing because those are the two things a person can do to clear a row.
     *
     * AND IT NAMES THE SWEEP WITHOUT PROMISING A SCHEDULE, WHICH IS A CORRECTION. Some of these rows
     * cannot be withdrawn at all — a channel that was soft-deleted, or one this person was removed
     * from, still holds their staged rows and `DELETE /api/attachments/:id` will not take them — so a
     * sentence offering only "remove some" would be asking for something impossible, and the sentence
     * has to say what becomes of those. It used to say they are "cleared within a day", which this
     * server is in no position to promise: `attachments.culler.olderThanHours` is the operator's to
     * set, and `attachments.culler.enabled: false` is a documented way to keep every staged row for
     * ever (charts/openbot/README.md). A deployment that has done either is one where this sentence
     * was simply a lie, told to the one person who could not act on it. What is true whatever the
     * chart says is that those rows are the deployment's to clear and not this person's, and that is
     * what it says now.
     *
     * The count comes from the statement that refused, exactly as the cap's does, so the number and
     * the decision cannot contradict each other.
     */
    if ("held" in outcome) {
      return context.json(
        {
          error: `You have ${outcome.held} files waiting to send across your conversations, which is the limit. Send or remove some first. Files in a conversation you can't open anymore have to be cleared by an admin.`,
        },
        409,
      );
    }

    return context.json(outcome.inserted, 201);
  });

  return routes;
}

/**
 * Replaces every character that cannot legally appear in a header value with `_`.
 *
 * RFC 9110 allows visible ASCII, SP, HTAB and obs-text (%x80-FF) in a field value and nothing
 * else: the C0 controls and DEL are not representable there at all. Two of them, CR and LF, are
 * the header-injection pair, and for a long time they were the only two this file took out. The
 * rest are just as fatal, only more quietly. Bun's serializer throws on a NUL, and it throws from
 * inside `c.body` after the response has already begun, with no `app.onError` behind this router
 * to turn that into anything — so one such name is a 500 on EVERY fetch of that attachment rather
 * than a served file. The ones Bun does pass through (0x01-0x08, 0x0B, 0x0C, 0x0E-0x1F, 0x7F) are
 * still illegal on the wire, arrive mangled at the client, and entitle any proxy in between to
 * reject or re-parse the whole header over one byte.
 *
 * A NUL cannot reach a stored name today — `attachments.name` is a Postgres `text` column and
 * Postgres refuses U+0000 in one — but the name is whatever the uploader's browser called the
 * file, and header-safety is this function's job to guarantee rather than a column type three
 * files away's to imply.
 *
 * HTAB goes too, and the list above says it is legal. Both are true: a tab is legal in a field
 * VALUE and meaningless in a FILENAME, where it would only ever arrive as an accident of whatever
 * produced the name and read back as ragged whitespace. The predicate below takes the whole C0
 * range rather than carving one character out of it for no gain.
 *
 * `_` rather than deletion, matching `foldToLatin1` below: the substitution shows up in the
 * downloaded filename, where deletion would silently join whatever sat on either side.
 */
function withoutControlCharacters(name: string): string {
  return Array.from(name)
    .map((char) => {
      const codePoint = char.codePointAt(0) ?? 0;
      return codePoint < 0x20 || codePoint === 0x7f ? "_" : char;
    })
    .join("");
}

/**
 * Escapes a stored filename for the quoted `filename` parameter of `Content-Disposition`.
 *
 * The name came from whatever the uploader's browser called the file, so it can contain a quote
 * or a backslash, either of which would end the `filename="..."` value early or splice in
 * attacker-controlled header syntax. Neither survives here, and nothing that could not appear in
 * a header value at all survives `withoutControlCharacters` above.
 */
function escapeFilename(name: string): string {
  return withoutControlCharacters(name)
    .replace(/\\/g, "\\\\")
    .replace(/"/g, '\\"');
}

/**
 * Folds a filename down to Latin-1 for the quoted `filename` parameter.
 *
 * Bun's header serializer throws on any value carrying a code unit above U+00FF, and it throws
 * from inside `c.body` after headers have already started being written — there is no
 * `app.onError` behind this router to turn that into a response, so an uploader who legitimately
 * named their file `メモ.txt` or `笔记.md` would 500 the whole handler. Folding those code points
 * to `_` here keeps the quoted parameter header-safe for every client; `filename*` below is what
 * carries the real name back intact, for the clients that read it.
 */
function foldToLatin1(name: string): string {
  return Array.from(name)
    .map((char) => {
      const codePoint = char.codePointAt(0) ?? 0;
      return codePoint > 0xff ? "_" : char;
    })
    .join("");
}

/**
 * Percent-encodes a filename for the RFC 5987 `filename*` parameter (`encodeURIComponent` leaves
 * `'`, `(`, `)` and `*` unescaped, none of which are legal in `attr-char`).
 */
function encodeRfc5987ValueChars(value: string): string {
  return encodeURIComponent(value).replace(
    /['()*]/g,
    (char) => `%${char.charCodeAt(0).toString(16).toUpperCase()}`,
  );
}

/**
 * Builds the `filename="..."; filename*=UTF-8''...` pair for a `Content-Disposition` value: an
 * ASCII-folded fallback every client can parse, plus the exact name for the clients that support
 * the extended form.
 *
 * Exported only so a test can reach the NUL case, which no route can: Postgres will not store a
 * U+0000 in `attachments.name`, so calling this directly is the one way to show it never hands
 * the serializer a byte that would throw.
 */
export function contentDispositionFilename(name: string): string {
  // Both parameters are built from the SAME neutralised name, so the fallback and the extended
  // form can never disagree about what the file is called. `encodeRfc5987ValueChars` would
  // otherwise percent-encode a control character into something a header can legally carry
  // (`%0B`) and hand the client back a name it should never have been offered.
  const safe = withoutControlCharacters(name);
  const quoted = foldToLatin1(escapeFilename(safe));
  const extended = encodeRfc5987ValueChars(safe);
  return `filename="${quoted}"; filename*=UTF-8''${extended}`;
}

/**
 * What `GET /api/attachments/:id` says about caching, on the 200 and on the 304 alike so the two
 * cannot drift.
 *
 * `private` because these are somebody's own files: no proxy and no CDN in between may hold a copy
 * that a different person could be served.
 *
 * `no-cache` — WHICH IS NOT `no-store`. The browser still keeps its copy; it just may not use that
 * copy without asking here first. This replaced `max-age=3600`, and the hour was not a small
 * mistake: deleting an attachment makes this route answer 404, and the browser never asked, so an
 * `<img>` already on the page went on painting the file from its own cache at full natural width
 * for the rest of the hour and the "this attachment is unavailable" path could not be reached at
 * all. The same hour kept bytes readable after a sign-out and after a removal from the channel.
 *
 * WHAT IS TRADED AWAY IS A ROUND TRIP, and knowingly. Every fetch now costs a request to this
 * server even when nothing has changed, where before one in an hour did. The ETag below buys back
 * the expensive half — a revalidation that still holds is a 304 with no body — but the request
 * itself, and the row lookup behind it, are the price of the property being bought: not freshness
 * of CONTENT, which cannot change for a given id, but freshness of EXISTENCE and of entitlement,
 * re-decided against this actor's membership on every single fetch.
 */
const ATTACHMENT_CACHE_CONTROL = "private, no-cache";

/**
 * Whether an `If-None-Match` header says the client already holds this exact representation.
 *
 * Weak comparison, as RFC 9110 requires for `If-None-Match`: `W/"x"` and `"x"` are a match, and so
 * is a list containing either. `*` matches whenever any representation exists at all, which by the
 * time this is asked it does.
 *
 * A header nobody sent is not a match, which is the ordinary first fetch.
 */
function ifNoneMatchHolds(header: string | undefined, etag: string): boolean {
  if (!header) return false;
  return header
    .split(",")
    .map((candidate) => candidate.trim())
    .some(
      (candidate) =>
        candidate === "*" || candidate.replace(/^W\//, "") === etag,
    );
}

/**
 * The headers a stored attachment is served under — by `GET` and by `HEAD` alike, from one place,
 * because a probe that disagreed with the fetch about the type or the disposition would be worse
 * than no probe at all.
 */
function attachmentHeaders(
  row: { name: string; mimeType: string },
  etag: string,
): Record<string, string> {
  const kind = classifyAttachment(row.mimeType);
  return {
    // The type this server sniffed from the bytes at upload time, never the client's original
    // claim — that claim is exactly what this header exists to override, so trusting it here
    // would undo the sniff.
    "Content-Type": row.mimeType,
    // Without this, a browser that decides it knows better than the declared type will sniff
    // the bytes itself, which is how a "text" file with HTML in it becomes a page rendered on
    // this app's own origin instead of the download or plain text it was declared to be.
    "X-Content-Type-Options": "nosniff",
    // Only an image opens inline. Everything else — including a stray file whose sniffed type
    // is not one of the accepted image formats — downloads instead, because inline is exactly
    // what would let script-carrying content (the SVG case refused at upload) run if it ever
    // reached this endpoint another way.
    "Content-Disposition":
      kind === "image"
        ? "inline"
        : `attachment; ${contentDispositionFilename(row.name)}`,
    // What may be reused, and on what terms: see {@link ATTACHMENT_CACHE_CONTROL}. The short of
    // it is that a stored copy may be kept but not used without asking here again, so a deletion
    // or a removal from the channel is seen on the next fetch rather than up to an hour later.
    "Cache-Control": ATTACHMENT_CACHE_CONTROL,
    // Paired with that: the ask is cheap, because a client that still holds this id's bytes gets
    // a 304 instead of them.
    ETag: etag,
  };
}

type AttachmentContext = Context<{ Variables: AppVariables }>;

/**
 * "No such attachment", said the one way, by every read on `GET /api/attachments/:id` and by the
 * withdrawal below.
 *
 * A FUNCTION RATHER THAN FOUR LITERALS, because this sentence is the whole of what an outsider is
 * told and its value is that it never varies. `readVisibleAttachment` returns no row for four
 * different reasons — no such id, a channel since deleted, not a member of it, a colleague's staged
 * draft — and the uuid-shape guards answer a fifth before any query runs. If any one of those ever
 * came back phrased differently, or with a different status, the difference would be exactly the bit
 * of information the uniform 404 exists to withhold.
 */
function noSuchAttachment(context: AttachmentContext) {
  return context.json({ error: "No such attachment." }, 404);
}

/**
 * What `GET /api/attachments/:id` says when the store could not be asked — the fetch, the probe and
 * the revalidation alike.
 *
 * THE PROBE ANSWERS THIS BY CALLING THIS, WHICH IT DID NOT USED TO. `HEAD` had its own
 * `context.body(null, 503)` beside a comment claiming its refusals were "the GET's, exactly". They
 * were not: Hono answers a HEAD by dispatching the GET handler and re-wrapping the response as
 * `new Response(null, <it>)`, so the headers survive even though the body does not — and a
 * `context.body(null, ...)` sets no `Content-Type` where `context.json` sets `application/json`.
 * Reproduced against an unreachable database: GET gave `503 application/json`, HEAD gave `503` with
 * no `Content-Type` at all. That is a probe answering a question the fetch would not, which is the
 * one thing the two are not allowed to do. There is now no second spelling to drift from.
 */
function couldNotReadAttachment(context: AttachmentContext) {
  return context.json(
    { error: "Couldn't open that attachment. Try again." },
    503,
  );
}

/**
 * Which attachment `GET /api/attachments/:id` may answer about, as a WHERE the fetch, the probe and
 * the revalidation all pass the same way.
 *
 * THE CONDITION IS THE ACCESS CHECK. It holds only when this attachment exists, the channel it was
 * uploaded into has not been deleted, and this actor has a membership row on that channel. No row,
 * for any of those reasons, is a 404 rather than a 403: a 403 would mean "yes, that id exists, but
 * it is not yours", which is a free bit of information for somebody probing ids for attachments they
 * cannot see. 404 is the same answer for "no such attachment", "the channel is gone" and "not
 * yours", so guessing ids learns nothing either way.
 *
 * `channels` is inside the `EXISTS` beside the membership, and not only the membership, because
 * channels soft-delete: the channel row and every membership on it outlive the deletion, so a
 * membership-only test says yes for ever and the bytes stay downloadable — and inlinable — after the
 * channel they belong to is gone.
 *
 * AND A STAGED ROW IS ITS UPLOADER'S ALONE. Membership is what lets people see each other's SENT
 * files; a row with no `attachedAt` has been shared with nobody, so a colleague's half-composed
 * draft is not a channel's to read. Reaching one needs its v4 uuid, which only the uploader's own
 * 201 ever carried, so this is a boundary rather than a leak anybody has — but it is the boundary
 * the write side already assumes, and the read side used not to keep.
 *
 * On the ordinary send that costs nothing: the stamp is written before the message is persisted, so
 * by the time another member's browser can name the id the row is no longer staged. What it does
 * change is the turns that fail before the stamp — a Bot that is no longer registered, a run that
 * throws mid-inline — where the message is persisted anyway and the row stays staged for ever.
 * Their attachments now read "unavailable" to everybody but the sender rather than being served out
 * of a draft nobody sent. That is the more honest answer of the two, and the underlying leak — a
 * persisted message whose rows were never stamped — is a copilot.ts fault worth fixing on its own.
 *
 * A CONDITION RATHER THAN A WHOLE QUERY, WHICH IS WHAT LETS THE THREE READS DIFFER IN NOTHING BUT
 * THEIR COLUMNS. The fetch wants the bytes, the probe wants the size, and a revalidation wants
 * nothing at all — and until this was factored out, each of them carried its own copy of the joins
 * and the WHERE. That is a standing invitation to drift: a rule added to one and not the others
 * turns `HEAD` into a way to learn that an id exists, or that a colleague has a draft, which the
 * uniform 404 exists to hide. Stated once, there is no second copy to leave behind. A helper that
 * owned the columns too was written first and abandoned: drizzle tracks the legal builder methods in
 * the selection's own type, and over a generic selection TypeScript cannot resolve that, so the
 * joins would not chain.
 *
 * AS A CORRELATED `EXISTS` RATHER THAN AS TWO INNER JOINS, which is the shape the withdrawal below
 * already uses for the same rule. A condition composes where a join does not — that is the whole
 * reason this is reusable — and it cannot multiply the attachment row if a membership is ever
 * recorded twice, which a join silently would.
 */
function visibleToActor(database: Database, actorId: string, id: string) {
  return and(
    eq(attachments.id, id),
    exists(
      database
        .select({ member: sql`1` })
        .from(channelMemberships)
        .innerJoin(channels, eq(channels.id, channelMemberships.channelId))
        .where(
          and(
            eq(channelMemberships.channelId, attachments.channelId),
            eq(channelMemberships.userId, actorId),
            isNull(channels.deletedAt),
          ),
        ),
    ),
    or(isNotNull(attachments.attachedAt), eq(attachments.uploadedBy, actorId)),
  );
}

/**
 * The rows a read on this route came back with, or null when the store could not be asked.
 *
 * A DATABASE THAT COULD NOT ANSWER IS NOT AN ANSWER OF "NO", and on this route the difference
 * matters more than anywhere else in this file. 404 here is load-bearing in the client: the
 * transcript treats it as "this attachment is gone" and paints the unavailable tile in its place,
 * and {@link ATTACHMENT_CACHE_CONTROL} is `no-cache`, so it asks again on every paint. Answering a
 * lost connection with 404 would tell every member that a file which is still there has been
 * withdrawn. An empty array is the real "no row", and that alone is the 404.
 *
 * There is no `app.onError` behind this router, so the alternative to catching here is Hono's
 * plain-text default, which the client cannot read as `{ error }` and which leaves no server-side
 * trace of a fault that is this side's to fix. One log line for all three reads, naming the row and
 * the asker, because that is what it would take to act on it.
 */
async function readOrNull<TRows>(
  query: PromiseLike<TRows>,
  actorId: string,
  id: string,
): Promise<TRows | null> {
  try {
    return await query;
  } catch (error) {
    console.error(`Could not read attachment ${id} for ${actorId}.`, error);
    return null;
  }
}

/**
 * An attachment's own door: fetch its bytes back, or take it off the shelf before it is sent.
 *
 * Both routes below start from the same join as `createChannelAttachmentRoutes` above — a channel
 * that has not been deleted, and the actor's membership on it — because an attachment is only ever
 * visible to the channel it was uploaded into, sender and recipients alike, and only for as long as
 * that channel is.
 *
 * Mounted at `/api/attachments` by whoever wires the app together; this file only builds the
 * router.
 */
export function createAttachmentRoutes(
  database: Database,
  requireUser: MiddlewareHandler<{ Variables: AppVariables }>,
): Hono<{ Variables: AppVariables }> {
  const routes = new Hono<{ Variables: AppVariables }>();

  routes.get("/:id", requireUser, async (context) => {
    const actor = context.var.actor;
    const id = context.req.param("id");

    // `attachments.id` is a Postgres `uuid` column: comparing it against arbitrary path text
    // raises `22P02` and throws before any row can fail to match. That throw would escape as a
    // 500, defeating the "404 hides both reasons" design this route otherwise relies on for
    // security — an id that cannot possibly be a uuid gets the same answer as one that just
    // isn't a match.
    if (!isUuidShaped(id)) {
      return noSuchAttachment(context);
    }

    /*
     * THE VALIDATOR IS KNOWN BEFORE THE ROW IS, AND THAT IS WHAT MAKES A 304 CHEAP.
     *
     * An attachment's bytes never change once stored — nothing in this file or anywhere else
     * updates `attachments.bytes` — so a given id names one exact representation for as long as it
     * names anything. A strong ETag of the id therefore needs no digest of the file to compute, and
     * no row either: whether the client's `If-None-Match` still holds is decidable HERE, before a
     * single column has been chosen.
     *
     * It used to be asked after the row came back, with `bytes` already in the select list, so every
     * revalidation read the whole file out of Postgres in order to send an empty body. The comment
     * that stood here called that "the cost of keeping the authorisation and the answer in one
     * statement", which was a false trade: the two were never in tension, because the validator
     * never needed the row.
     *
     * NOR IS IT A CORNER. {@link ATTACHMENT_CACHE_CONTROL} is `private, no-cache` deliberately, so
     * EVERY image paint in every viewing member's transcript revalidates here — the 304 is the
     * common path on this route, not the rare one. Measured through this route against an 8 MiB
     * attachment, 12 runs each side: 8,388,608 bytes read and 25.09ms median per 304 before, 0 bytes
     * and 0.65ms after.
     *
     * WHAT IS NOT GIVEN UP IS THE AUTHORISATION. The question a conditional request answers on this
     * route is not "have the bytes changed" — they cannot — but "is this still there and still
     * yours", so a 304 is still earned by the same channel-and-membership join a 200 is, in the same
     * single statement. Somebody removed from the channel gets 404 on their next revalidation, not
     * 304. Only the blob left the select list; the decision did not.
     *
     * AND IT IS ASKED AHEAD OF THE `HEAD` BRANCH, so the probe gets the same saving and the same
     * answer from the same lines. A revalidating HEAD and a revalidating GET differ in nothing but
     * the body Hono strips, which is what "the probe answers exactly what the fetch would" is
     * supposed to mean.
     */
    const etag = `"${id}"`;
    if (ifNoneMatchHolds(context.req.header("If-None-Match"), etag)) {
      // One column, and it is the id the WHERE has already fixed: this statement is asked whether a
      // row comes back, never for anything in it. A `select` naming no field at all is not a
      // statement Postgres would take, so "nothing" has to be spelled as the cheapest something.
      const visible = await readOrNull(
        database
          .select({ id: attachments.id })
          .from(attachments)
          .where(visibleToActor(database, actor.id, id)),
        actor.id,
        id,
      );
      if (!visible) return couldNotReadAttachment(context);
      if (!visible[0]) return noSuchAttachment(context);

      return context.body(null, 304, {
        ETag: etag,
        "Cache-Control": ATTACHMENT_CACHE_CONTROL,
      });
    }

    /*
     * A PROBE THAT DOES NOT READ THE FILE, AND WHY IT IS A BRANCH RATHER THAN A ROUTE.
     *
     * Hono intercepts HEAD before routing and re-dispatches it as a GET, returning
     * `new Response(null, <the GET's response>)` (hono-base.js, `#dispatch`). A
     * `routes.on("HEAD", ...)` handler is therefore never reached — that was written first and
     * verified not to run — so the only place that can answer a HEAD cheaply is here, in the
     * handler Hono actually calls. The REQUEST object is passed through untouched, which is why
     * the method is still readable at this point.
     *
     * It is worth answering cheaply. The transcript probes every document tile it draws with
     * `HEAD /api/attachments/<id>`, from every viewing member's browser, and
     * {@link ATTACHMENT_CACHE_CONTROL} is `no-cache`, so each probe re-ran a full `bytea` read of a
     * file nobody was going to be sent — measured: a HEAD of an 8 MiB attachment came back 200 with
     * an empty body, having read all 8 MiB out of Postgres.
     *
     * Same access decision and the same headers — `size_bytes` in place of `bytes`, and
     * `Content-Length` set by hand because a body-less response has nothing to derive it from and
     * the size is the thing a probe is usually asking for. The refusals have to be indistinguishable
     * from the fetch's, or a probe becomes a way to learn something a fetch would not tell you, so
     * they are not repeated here at all: {@link readVisibleAttachment} makes the decision and
     * {@link noSuchAttachment} and {@link couldNotReadAttachment} phrase both of its refusals, for
     * this branch and the fetch below alike. `attachment-routes.test.ts` still asserts that case by
     * case rather than leaving it to this comment.
     *
     * Revalidation is not handled here either, because the branch above already answered it for both
     * methods before this one was reached.
     */
    if (context.req.method === "HEAD") {
      const probed = await readOrNull(
        database
          .select({
            name: attachments.name,
            mimeType: attachments.mimeType,
            sizeBytes: attachments.sizeBytes,
          })
          .from(attachments)
          .where(visibleToActor(database, actor.id, id)),
        actor.id,
        id,
      );

      if (!probed) return couldNotReadAttachment(context);
      const metadata = probed[0];
      if (!metadata) return noSuchAttachment(context);

      return context.body(null, 200, {
        ...attachmentHeaders(metadata, etag),
        "Content-Length": String(metadata.sizeBytes),
      });
    }

    /*
     * The only read on this route that is going to send a body, and so the only one that may name
     * `bytes`.
     *
     * WHAT THAT COLUMN COSTS IS THE POINT, NOT AN ASIDE. `attachments.bytes` is a `bytea` of up to
     * {@link MAX_IMAGE_BYTES}, TOASTed out of line, and naming it in a select list is what makes
     * Postgres fetch and de-TOAST the whole file into this process. The branch above measured
     * 8,388,608 bytes read per 304 while this column was in every read's select list; the rule that
     * keeps it out is that only the answer carrying a body may ask for it.
     */
    const rows = await readOrNull(
      database
        .select({
          name: attachments.name,
          mimeType: attachments.mimeType,
          bytes: attachments.bytes,
        })
        .from(attachments)
        .where(visibleToActor(database, actor.id, id)),
      actor.id,
      id,
    );

    if (!rows) {
      return couldNotReadAttachment(context);
    }

    const row = rows[0];
    if (!row) {
      return noSuchAttachment(context);
    }

    /*
     * A VIEW OVER THE DRIVER'S BUFFER, NEVER `Uint8Array.from` OVER IT.
     *
     * `row.bytes` is a Node `Buffer` — the driver's mapping for `bytea` — and a `Buffer` is both
     * array-like and iterable. `%TypedArray%.from` prefers the ITERATOR, so it walks the file one
     * element at a time on the single JS thread: 8.4 million steps for a file at `MAX_IMAGE_BYTES`.
     * Measured on this repo's Bun 1.4 over an 8 MiB buffer: `Uint8Array.from` 62-87ms, this view
     * 0.0004ms, `new Uint8Array(buffer)` (a copy, no iterator) 0.13ms. End to end, one 8 MiB fetch
     * through this route went from 120-140ms to 36-38ms.
     *
     * That cost lands where it hurts most. {@link ATTACHMENT_CACHE_CONTROL} is `no-cache`
     * deliberately, so EVERY image paint revalidates here; a transcript with a handful of large
     * images stalls the event loop — for every other person's request on this process too — on
     * every scroll-back. It is exactly the CPU the 304 path above was written to avoid paying.
     *
     * The offset and the length are both passed, rather than `new Uint8Array(row.bytes.buffer)`,
     * because a `Buffer` need not own the whole of its `ArrayBuffer`: Node pools small allocations,
     * so a short row can arrive as a window into a larger block. Dropping the offset would serve
     * whatever else shares that block. The bytes are not copied, which is safe because nothing here
     * or downstream writes through this view.
     *
     * The cast narrows `ArrayBufferLike` to `ArrayBuffer`, which is the only difference between
     * what Node's `Buffer` promises and what hono's `Data` accepts: `ArrayBufferLike` admits a
     * `SharedArrayBuffer`, and a database driver decoding a `bytea` off a socket does not allocate
     * one. It is a type-level narrowing with no run-time step, which is the whole point — the
     * expression it replaced was a run-time conversion standing in for a compile-time one.
     */
    const bytes = new Uint8Array(
      row.bytes.buffer as ArrayBuffer,
      row.bytes.byteOffset,
      row.bytes.byteLength,
    );

    return context.body(bytes, 200, attachmentHeaders(row, etag));
  });

  routes.delete("/:id", requireUser, async (context) => {
    const actor = context.var.actor;
    const id = context.req.param("id");

    // Same uuid-shape guard, and the same sentence, as the GET route above.
    if (!isUuidShaped(id)) {
      return noSuchAttachment(context);
    }

    /*
     * THE WHOLE DECISION IS THE DELETE, and that is what makes the refusal below mean anything.
     *
     * This route used to read `attachedAt`, decide on what it read, and then delete by id alone.
     * Those are two statements with a gap between them, and the send that writes `attachedAt` is
     * the third party that fits in it: stamp the row after the read and before the delete, and the
     * file goes out from under a message that already claims it, leaving the transcript pointing at
     * nothing. The window is not theoretical — it is the sender's own turn racing their own
     * composer, which still offers the file for withdrawal until the send is recorded.
     *
     * Stating the whole rule in the WHERE closes it, because Postgres re-checks that WHERE against
     * the row as it stands when the delete actually gets the row: `attachedAt IS NULL` no longer
     * holds, nothing is deleted, and the returning list is empty. There is no moment at which a
     * sent attachment is deletable.
     *
     * `uploadedBy` for the reason it was there before. Membership only ever gated visibility, and a
     * sent attachment is refused below regardless of who asks; what membership-only scoping still
     * allowed was a member deleting a colleague's *unsent* draft, which does real damage — the
     * colleague's composer keeps pointing at a now-missing row, and their send fails later when
     * nothing can resolve it. A non-uploader gets the same 404 as a non-member.
     *
     * A LIVE CHANNEL AND MEMBERSHIP ON IT, AS A CORRELATED `EXISTS` RATHER THAN AS A JOIN, because
     * the join this route used to lead with lived in a separate SELECT and a delete cannot carry
     * one. It is here because "is a member of this live channel" is an access-control boundary, and
     * the case dropping it would open — somebody removed from a channel withdrawing their own
     * still-unsent draft — is low-harm enough that letting it through would be a boundary loosened
     * for no reason at all, inside a change that is nominally about when a column gets written. The
     * subquery correlates on `attachments.channelId`, so it is one statement and the whole rule is
     * still evaluated at the moment the row is taken.
     *
     * `channels` is inside that `EXISTS` beside the membership, and not only the membership,
     * because channels soft-delete: a membership row outlives its channel's deletion, so a
     * membership-only test kept saying yes and this route kept acting inside a channel nobody can
     * open. Same scope as the fetch route above and as the upload route, which is what makes "the
     * same join" true rather than merely claimed.
     */
    const removed = await database
      .delete(attachments)
      .where(
        and(
          eq(attachments.id, id),
          eq(attachments.uploadedBy, actor.id),
          isNull(attachments.attachedAt),
          exists(
            database
              .select({ member: sql`1` })
              .from(channelMemberships)
              .innerJoin(
                channels,
                eq(channels.id, channelMemberships.channelId),
              )
              .where(
                and(
                  eq(channelMemberships.channelId, attachments.channelId),
                  eq(channelMemberships.userId, actor.id),
                  isNull(channels.deletedAt),
                ),
              ),
          ),
        ),
      )
      .returning({ id: attachments.id })
      // As on the two routes above, and here the mistaken answer would be the worst of the three:
      // an unguarded throw is a plain-text 500, and the composer's only other reading of a failed
      // withdrawal is that the file is still staged. Say that the store could not be reached.
      .catch((error: unknown) => {
        console.error(
          `Could not withdraw attachment ${id} for ${actor.id}.`,
          error,
        );
        return null;
      });

    if (!removed) {
      return context.json(
        {
          error: "Couldn't remove that attachment. Try again.",
        },
        503,
      );
    }

    if (removed.length > 0) {
      return context.body(null, 204);
    }

    /*
     * Nothing was withdrawn, and only now is it worth asking why — 404 or 409 is a question about
     * how to answer, not about what to do, so it is asked after the act rather than before it.
     *
     * The same join-is-the-check shape, and the same 404-hides-every-reason answer, as the GET
     * route above: no row, whether because there is no such attachment, because its channel has
     * been deleted, because the actor is not in that channel, or because somebody else uploaded it,
     * reads identically from here. A 403 would mean "yes, that id exists, but it is not yours",
     * which is a free bit of information for anybody probing ids.
     *
     * The channel scope has to be on THIS query as well as on the delete above, or the two would
     * disagree: a draft in a deleted channel would refuse to be withdrawn and then be explained
     * with a 409 that says it was already sent, which is not what happened.
     *
     * IT ASKS WHETHER A ROW IS THERE, AND NOTHING ABOUT THE ROW — which is why it no longer selects
     * `attachedAt`. It used to, and never read it: the 409 below is unconditional. That was not
     * merely a wasted column, it was a claim this query looked like it was checking and was not.
     *
     * The 409 is unconditional because by this point it is the only answer left, and that follows
     * from the two WHEREs rather than from a column. This query repeats every term of the delete's
     * except `attachedAt is null` — same id, same uploader, same live channel, same membership — so
     * a row coming back means all of those still hold, and the one term the delete had that this one
     * does not is therefore the one that refused it. Nothing ever sets `attachedAt` back to null, so
     * a row that was stamped when the delete ran is still stamped now. Reading the column could only
     * ever confirm what the pair of statements has already established.
     */
    const rows = await database
      .select({ id: attachments.id })
      .from(attachments)
      .innerJoin(
        channels,
        and(eq(channels.id, attachments.channelId), isNull(channels.deletedAt)),
      )
      .innerJoin(
        channelMemberships,
        and(
          eq(channelMemberships.channelId, attachments.channelId),
          eq(channelMemberships.userId, actor.id),
        ),
      )
      .where(and(eq(attachments.id, id), eq(attachments.uploadedBy, actor.id)))
      // Only the explanation is left to find, but a failure to find it is still not a 404: the
      // delete above already declined to withdraw anything, and answering "no such attachment"
      // because the second query failed would tell the composer to drop a row that is still there.
      .catch((error: unknown) => {
        console.error(
          `Could not explain a refused withdrawal of ${id} for ${actor.id}.`,
          error,
        );
        return null;
      });

    if (!rows) {
      return context.json(
        {
          error: "Couldn't remove that attachment. Try again.",
        },
        503,
      );
    }

    const row = rows[0];
    if (!row) {
      return noSuchAttachment(context);
    }

    // Once an attachment rides in a sent message, it is part of that message's record: pulling it
    // out from under a message that already claims it would leave the message pointing at nothing.
    // A staged attachment has made no such promise yet, so only that one may still be withdrawn —
    // and the delete above is the only thing that decides whether it still is one.
    return context.json(
      { error: "This attachment is already part of a sent message." },
      409,
    );
  });

  return routes;
}