docs/maps/build-test-and-ops.md

Rama main · réplica de lectura del repositorio original.

# Build, Test & Ops Map

How the repo is laid out, how it runs locally and in production, and exactly
what CI enforces.

---

## 1. The dual-root quirk

The repo root holds Docker, docs, SQL and Make. The Node application lives one
level down in `cloudcodex/`.

```
c2/                          <- git root; docker, docs, SQL, Makefile, start.sh
├── scripts/                 <- Bash: backup.sh, restore.sh, backup-common.sh (section 4)
└── cloudcodex/              <- the npm package; package.json lives HERE
```

**Every `npm` command runs from `cloudcodex/`.** Every `make` target and
`docker compose` command runs from the root. This catches out both humans and
agents; a `npm test` at the root fails with a missing package.json.

The `.env` file lives at the **root**, and `mysql_connect.js:17` reaches up for
it with `path.resolve(dirname, '..', '.env')`. Importing `mysql_connect.js` is
what loads env for the whole process, so any module that needs env must import
it (directly or transitively) before reading `process.env`.

## 2. npm scripts (`package.json:6-21`)

| Script | Command | Notes |
|---|---|---|
| `dev` | `node server.js` | **not** `vite dev`. `vite-express` runs Vite in middleware mode inside the same process. |
| `start` | `NODE_ENV=production node server.js` | serves the prebuilt `dist/` |
| `build` | `vite build` | frontend only; the backend is not bundled |
| `preview` | `vite preview` | |
| `lint` | `eslint .` | flat config, whole package |
| `test` | `vitest run --project backend --project frontend --project design` | the three default projects; never `integration` |
| `test:watch` | `vitest --project backend --project frontend --project design` | |
| `test:coverage` | `vitest run --coverage --project backend --project frontend --project design` | v8 provider, enforces thresholds |
| `test:integration` | `vitest run --project integration` | opt-in, needs a live MySQL; see section 5 |
| `test:backend` / `test:frontend` / `test:design` | `vitest run --project <name>` | one project at a time |
| `migrate` | `node scripts/migrate.js` | applies pending `migrations/*.sql`, records them in `schema_migrations`. One-time adoption first: `-- --adopt-fresh-install` on a database `init.sql` just built, `-- --baseline` on an install that predates the runner. Run it inside the app container on the release compose file (3306 is not published there). See [data-model.md](data-model.md) and `docs/deployment.md`. |
| `backfill:doc-images` | `node scripts/backfill-doc-images.js` | once, after the `2026-09-27-who-may-see-doc-images.sql` migration: records a `doc_images` row for every `/doc-images/` image an existing document or version shows, so the authorized handler does not hide them from readers. Idempotent, but it refuses to run over a table that already has rows (run after go-live it would trust every reference saved since) unless `-- --again` or `DOC_IMAGES_PUBLIC=1`. In containers it runs like `migrate`, through `run --rm app`. |

`NODE_ENV` matters in six places: CORS localhost allowance
(`app.js:272`), where Helmet is mounted (`app.js:330`: the whole app in
production, `/api` otherwise), rate-limiter `skip` when `'test'` (`app.js:360`,
`app.js:398`, `app.js:412`), the `APP_URL` boot gate (`server.js:64`), the webhook SSRF
guard's https-only rule (`webhookTargetOptions`, `services/webhooks.js`), and
Vite's dev-vs-prod mode. `.env.example` ships it commented out; `npm run start`
and the Docker image set it, and both production compose files pin it (section 4).

## 3. Local development

`./start.sh` from the root is the one-shot bootstrap: it checks Docker, Docker
Compose, Node and npm, brings up MySQL, installs dependencies, and starts the
dev server. On native Linux (not WSL: it checks `/proc/version`) it merges
`docker-compose.linux.yml`, which re-declares the bind mounts with the `:Z`
SELinux label (`docker-compose.linux.yml:6-8`) and publishes no port.
It waits for MySQL with a TCP `mysqladmin ping` inside the container, for up to
180 s, for the same reason as the production healthcheck in section 4.

Manual equivalent:

```
docker compose up -d          # from the root: MySQL only
cd cloudcodex && npm install && npm run dev
```

Make targets (`Makefile`); the first three shell into the running container:

| Target | Effect |
|---|---|
| `make seed` | pipes `seed.sql` in |
| `make reset-db` | pipes `init.sql` then `seed.sql` in |
| `make db-shell` | interactive `mysql` CLI |
| `make backup OUT=<file>` | `scripts/backup.sh <file>` (section 4, "Backup and restore") |
| `make restore IN=<file> [ARGS=...]` | `scripts/restore.sh $(ARGS) <file>` |

The Makefile does `include .env` / `export` at the top, so it needs a populated
root `.env`, and it resolves the container via
`docker compose ps -q database`.

`init.sql`'s `DROP TABLE IF EXISTS` block now covers all 28 tables, including
`github_links`, `activity_log`, `watches` and `notifications`, which used to be
missing and made `make reset-db` fail partway on a database that already had
them. See [data-model.md](data-model.md).

## 4. Docker topologies

**Dev** (`docker-compose.yaml`): MySQL only, port 3306 published on 127.0.0.1
(`${DB_BIND:-127.0.0.1}:3306:3306`), data in a bind mount `./db-data/`, `init.sql` mounted into
`/docker-entrypoint-initdb.d/`. The app runs on the host.

**Prod** (`docker-compose-prod.yml`): MySQL plus the app.

**One MySQL release everywhere: `mysql:8.4.11`.** The three compose files, both
workflows' service containers and `start.sh`'s pre-pull name that exact tag, so
the server CI tests is the server every install runs, and a pull never moves an
install to an untested patch. `tests/compose-pins.test.js` fails on a tag
without a patch version (`mysql:8`, `mysql:8.4`) and on two files disagreeing,
so moving the pin is one commit that changes all seven references.

- MySQL uses a **named volume** `db_data`, not the bind mount, and gets a
  `mysqladmin ping` healthcheck **over TCP to `127.0.0.1`**, with a 300 s
  `start_period` (`docker-compose-prod.yml:25-39`, and the same block in
  `docker-compose-release.yml`). Not the socket: on an empty volume the image
  applies `init.sql` on a temporary server with networking off, which a socket
  ping answers, so the database read healthy while nothing listened on 3306 and
  the app exited on the instance lock's `ECONNREFUSED` until the restart policy
  recovered it (4 restarts per first boot of the prod file, measured
  2026-09-28; 0 with the TCP ping). The start period exists because the TCP
  ping now fails through the whole initialisation, and without it a slow disk
  would spend the retries and Compose would never start the app.
  `tests/compose-healthcheck.test.js` pins both files and `start.sh`'s wait
  loop, which pings the same way.
- The app builds from `cloudcodex/Dockerfile`, waits on
  `condition: service_healthy`, publishes
  `${APP_BIND:-127.0.0.1}:${PORT:-3000}:${PORT:-3000}`, and takes
  `env_file: .env`.

**The app port is published on 127.0.0.1 in both production files**
(GHSA-9fmx-frrf-xxmq). Docker's published ports are a DNAT rule in front of the
host firewall, so the old host-less mapping was reachable from anywhere that
could route to the machine, and a client reaching the app directly skipped the
TLS proxy. Only this machine reaches it now (a browser here, or the reverse
proxy); `APP_BIND` (`0.0.0.0`, or one interface's IPv4 address, never `::` or
an IPv6 address) exposes it on purpose. `:-` rather than `-`, because
`.env.example` ships `APP_BIND=` blank and
a blank host address publishes on every interface. A reverse proxy in another
container cannot reach the host's loopback: it joins this compose network,
proxies to `app:3000`, and is listed in `TRUST_PROXY` by an address pinned with
`ipv4_address`. `tests/compose-ports.test.js` parses every compose file at the
repo root with js-yaml (a declared devDependency), found by name in both
spellings Compose loads, `compose.yaml`/`compose.yml` (and `.override`) and
`docker-compose*`, so a new one fails until it has an expectation. Every
service in every file must not use `network_mode: host`, and no file may have a
top-level `include:`, since an included file's ports merge in unread; both
checks are proven on fixtures. For
each service it pins the published mappings, reads each one's effective host
in both the short syntax and the long one (`host_ip`), resolving `:-`
fallbacks as a blank `.env` would, and fails on anything beyond loopback, so
moving a pin and its file together to an all-interfaces default still fails.
The prod and dev files' database is pinned to `${DB_BIND:-127.0.0.1}:3306:3306`,
the release file's to none with `expose: ["3306"]`, and
`docker-compose.linux.yml` to none: Compose appends an override's `ports` to
the base file's, and the reader's non-vacuity cases prove it sees a flow list,
a commented key, a quoted key, a merged anchor, the long syntax and a bare
number. It also pins `.env.example`'s `APP_BIND=` and `DB_BIND=` blank.

**Both production files pin the default network** to `172.29.0.0/16`, gateway
`172.29.0.1` (distinct from any other compose project's default network). Docker presents
a proxy on the host to the container as that gateway, and `TRUST_PROXY`'s
default names `172.29.0.1/32`, so an unpinned network (Docker's next free
subnet, `172.18.0.0/16` or later, or a `192.168` range once about fifteen
networks exist) would leave the gateway untrusted and every client behind the
proxy in one bucket. The ports test pins the `ipam` block and that its gateway
is in the default, and that the app service joins no other network. Upgrading
an install created before the pin takes the project `down` before migrating
(`docs/deployment.md` "Upgrades"). Measured on a live 0.11.0 install with
Compose 5.3.1: when the database container's own configuration is unchanged,
the usual stop-then-migrate order makes `run` replace the network and
reconnect the database without its `database` alias, so the migration fails
with `ENOTFOUND database` and the app then boots unable to reach it (measured
before the instance lock, while `/api/oauth/providers` still answered 200; with
the lock it exits at boot and restarts); with `down` first it migrates and
keeps both volumes.

**The prod file's MySQL port is on 127.0.0.1 too**, for the same DNAT reason:
it used to publish `3306:3306` on every interface, the database's own
authentication the only thing between it and anything that could route to the
host. It stays published because `docs/deployment.md` has operators run a
mysql client or `npm run migrate` from the host against it; the app itself
uses `DB_HOST: database` over the compose network. `DB_BIND` widens it on
purpose. The dev file follows suit: its database runs with a development
password, often on a laptop on a shared network, and everything that uses it
(`npm run dev`, `make`, `start.sh`'s check, a `mysql` client) is on the same
machine. `make` goes through `docker exec` and does not use the port at all.
- `cloudcodex/Dockerfile` is a **two-stage** build on `node:20-slim`: the build
  stage runs `npm ci` and `npm run build`, and the runtime stage runs
  `npm ci --omit=dev`, copies the source, then copies `dist/` across from the
  build stage. `CMD ["node", "server.js"]`: Node is the container's main
  process, so `docker stop`'s SIGTERM reaches the graceful shutdown in
  `server.js` (`request-lifecycle.md` section 7). Under the old
  `CMD npm run start` the signal reached npm, Node never ran a handler, and
  Docker's SIGKILL followed.
- The runtime stage sets `ENV NODE_ENV=production` rather than relying on the
  `npm run start` script, because `vite-express` reads it when `server.js` is
  imported to decide between serving `dist/` and booting a Vite dev server. It
  is also why the image no longer needs the script at all.
  Because that `ENV` is now the only thing setting it, both production compose
  files pin `NODE_ENV: production` under the app's `environment`: Compose lets
  an `env_file` line `NODE_ENV=` (the `.env.example` of 0.9.0 to 0.11.0
  shipped it blank) replace an image `ENV` with an empty value, and
  `environment` wins over `env_file`. `.env.example` now ships it commented
  out. `tests/image-lifecycle.test.js` pins both.
- A `HEALTHCHECK` probes `GET /readyz` every 10 s (3 s timeout, 20 s start
  period, 3 retries) with `node -e "fetch(...)"` on `PORT`, because
  `node:20-slim` has no curl or wget. Healthy means the instance lock is held,
  `SELECT 1` answers and every file under the `/migrations` mount is recorded,
  so a fresh volume reads `unhealthy` until the one-time
  `--adopt-fresh-install`. Measured 2026-09-27 on the release compose file with
  a local build: `docker stop` returned in 0.45 s with exit 0 and `stopped
  cleanly on SIGTERM`, and the restarted container reported `healthy` 6 s after
  `docker start`. Re-measured after the review round with `docker run` of a
  local build: a stop during the boot's SMTP verify took 0.28 s with exit 0
  and `stopped on SIGTERM during boot`, where the previous image ignored the
  signal (Node as PID 1 with no handler yet) and was SIGKILLed at 10.3 s with
  exit 137; KILLing the lock's MySQL connection and taking the lock from
  another session stopped the container with exit 1 about a second later.
- The prod and release compose files give the app `stop_grace_period: 20s`,
  twice the shutdown's own 10 s bound, so it finishes before Docker's SIGKILL.
  `tests/image-lifecycle.test.js` pins the `CMD`, the `HEALTHCHECK` and the
  grace period.
- `src/` is copied whole rather than dropped in favour of `dist/`: the server
  imports `src/lib/githubDiff.js` directly.
- `cloudcodex/.dockerignore` excludes `node_modules`, `dist`, `tests`,
  `coverage` and `.env*`. Without it, `COPY . .` shipped the host's 405 MB
  `node_modules` on top of the tree `npm ci` had just installed, so every
  dependency was in the image twice. Single-stage with no ignore file produced
  a **2.86 GB** image; the current one is **679 MB**.
- `docker-compose-prod.yml` sets `DB_HOST: database` under the app service's
  `environment`, overriding `.env`'s `DB_HOST=localhost` (correct for dev,
  where the app runs on the host, and wrong inside the prod container).
  `environment` takes precedence over `env_file`.

### Backup and restore (`scripts/`)

`scripts/backup.sh <out>` and `scripts/restore.sh <archive>` (Bash, `set -euo
pipefail`, `umask 077`) share `scripts/backup-common.sh`: the archive format
(`ARCHIVE_MEMBERS`, `ARCHIVE_FORMAT`), `DUMP_FLAGS`, a copy of the instance
lock's name (`INSTANCE_LOCK_NAME_SQL`, pinned equal to
`services/instance-lock.js`'s by `tests/scripts/backup-common.test.js`) and two
transports behind the same functions (`db_mysql`, `db_query`, `db_dump`,
`uploads_out`, `uploads_in`, `app_version`, `target_database`), and two
checks both scripts make before they write: `check_confined_user` (the MySQL
user holds privileges on the instance's database and nothing else) and, with
Compose, `check_compose_file` (the stack was created from the file
`COMPOSE_FILE` names):

- **Compose** (default): `docker compose` on `COMPOSE_FILE`, defaulting to
  `docker-compose-release.yml`. The clients run inside the `database` service
  as its `MYSQL_USER` with `MYSQL_PWD="$MYSQL_PASSWORD"`, never root and never a
  password on argv, over `--protocol=TCP -h 127.0.0.1` (the temporary init
  server above is socket-only). The uploads go through a one-off
  `run --rm --no-deps -T app` streaming `tar` on stdin/stdout, so there is no
  bind mount and no SELinux label to get right. Both production files share
  the repo root's project name and the container names, so either file
  reaches the other's stack; `check_compose_file` reads the
  `com.docker.compose.project.config_files` label off the `database`
  container and refuses when its file names differ from `COMPOSE_FILE`'s
  (names, not paths, so a moved checkout still matches).
- **`--local`**: the `mysql`/`mysqldump` on `PATH`, `DB_HOST`/`DB_USER`/
  `DB_PASS`/`DB_NAME`, `--protocol=TCP`, and `--uploads DIR` (default
  `cloudcodex/public`). This is what `tests/integration/backup-restore.test.js`
  drives.

The archive is `database.sql`, `app_public.tar.gz` and a one-key-per-line
`manifest.json` (format, `created_at`, `database`, `app_version`, both
SHA-256s). `backup.sh` builds it in a `mktemp -d` beside the output, checks the
dump's last line is `-- Dump completed`, and publishes it with `ln` (atomic,
and a refusal if the output appeared meanwhile); an existing output is refused
up front. `restore.sh` does every check before any write (members, types,
checksums, uploads types and paths, a line-start scan of the dump for client
commands and database switches, the manifest's `app_version` against this
install's (`version_is_newer`; a newer backup needs `--allow-newer-backup`,
since `npm run migrate` only looks for missing ledger rows and would pass an
older app over a newer schema), the target name against the manifest or
`--into`, `check_confined_user`, a running `app` service, and rows), then
loads in one session that starts with `TAKE_LOCK_SQL` (take the instance lock
or fail: the only lock check, so a process holding it stops the load at line
1 and the restore says nothing was written) and, under `--replace` or when
every table is empty, drops every table first. It then requires as many tables
as the dump has `CREATE TABLE` lines, unpacks the uploads (`--replace` empties
only `avatars/` and `doc-images/`), and in Compose runs `npm run migrate` and
`up -d` unless `--no-start`.

Traps, each found while building it (2026-09-28):

- **`--one-database` needs the database as the positional argument.** With
  `-D <db>` both MySQL 8.4.11's and MariaDB 10.11's `mysql` skip **every**
  statement and exit 0, so a restore "succeeds" into an empty database. The
  scripts never pass `-D`, and the table-count check after the load catches a
  load that silently did nothing.
- **MariaDB's `mysqldump` cannot back up this schema for MySQL.** It writes the
  value of the generated column `logs.plain_content`, and MySQL refuses every
  such row (ERROR 3105). `--local` refuses a MariaDB `mysqldump`.
- **`tar | grep -q` under `pipefail` can read a hit as a miss** (tar's SIGPIPE
  wins), so every listing is captured into a variable first.
- **The line-start scan does not see a client command later on a line.**
  `SELECT 1; \T <file>` passed it and the client wrote `<file>` as whoever ran
  it. `db_mysql` passes `--binary-mode`, which turns off every client command
  in piped input except `delimiter` (a dump with triggers needs it) and
  `charset`, wherever it sits; the shell escape is also turned off by feature
  detection, `--skip-system-command` (MySQL) or `--sandbox` (MariaDB).
- The load's confinement to one schema is the **grant**, not a parser:
  `--one-database` ignores a qualified `db.table`, and the test proves a
  `CREATE TABLE <other>.x` in the dump fails with access denied when the
  restoring user has privileges on its own schema only.
- **So the user has to be one the grant confines.** `--local` first took any
  `DB_USER`, and as root a dump starting `SELECT 1; CREATE DATABASE x;` got
  past the line-start scan and created `x` (found in review, 2026-09-28).
  `check_confined_user` reads `SHOW GRANTS` (with `-r`, since batch mode
  doubles the backslash the MySQL image's `c2\_name` grant spelling carries)
  and allows only `USAGE ON *.*` and grants on `` `<db>`.* `` or its tables.
  `SHOW GRANTS` with no `FOR` lists the session's active roles, a mandatory
  one included, as `GRANT `role`` lines with their privileges (measured on
  8.4.11), so a role is refused by the same loop; a separate
  `CURRENT_ROLE()` check could never fire first, and was removed.

CI runs `shellcheck -x scripts/*.sh` (section 6).

One thing to get right when deploying: **`init.sql` only executes on a fresh
volume.** The MySQL entrypoint skips `/docker-entrypoint-initdb.d/` when the
data directory is already initialised. Editing `init.sql` and restarting
changes nothing; existing databases need the matching file from `migrations/`,
applied with `npm run migrate` (one-time adoption first, see
`docs/deployment.md`). No compose file mounts `migrations/` into the **MySQL**
container, so it is never applied from inside `make db-shell`; the release and
prod compose files mount it into the **app** container (`:ro,z`, because an
unlabelled bind mount is unreadable on an SELinux host), which is where the
runner runs. Run it with `docker compose ... run --rm app npm run migrate`, not
`exec`: on the upgrade that first ships the runner, the already-running
container is the old image, with neither the script nor the mount.

## 5. Testing

**Vitest 4, four projects** in one config (`vitest.config.js:25-86`). A single
`npm test` runs the three default ones, `backend`, `frontend` and `design`; the
fourth, `integration`, is opt-in because it needs a MySQL server:

| Project | Environment | Setup file | Includes |
|---|---|---|---|
| `backend` | node | `tests/setup.js` | `tests/routes/`, `tests/middleware/`, `tests/services/`, `tests/helpers/`, `tests/extensions/`, `tests/scripts/`, `tests/*.test.js` |
| `frontend` | jsdom + `@vitejs/plugin-react` | `tests/setup.frontend.js` | `tests/src/**` |
| `design` | node | none | `tests/design/**/*.test.js` |
| `integration` | node | `tests/setup.integration.js`, plus `globalSetup` `tests/integration/global-setup.js` | `tests/integration/**/*.test.js` |

Current state: the default run is **155 files, 3578 tests, all passing** (16
files and 281 tests of it are `design`, measured 2026-10-11 on
`track/w6-cdx-29-admin-surfaces`); the integration project is **27 files, 297
tests, all passing** (measured the same day in a `mysql:8.4.11` container with Node 22
copied in, the CI root password, and `npm ci` run inside). The older figures were (measured 2026-09-29 on `chore/deps-audit`, the upgraded
dependencies, with main merged in after W6-CDX-13, against a stock MySQL 8.4.11
at the server's default isolation and at `READ-COMMITTED`, with MySQL's 8.4.11
clients on `PATH`).

The backup file's child-process helpers (`spawn`, `childEnv`, `holdLock`,
`startServer`, `signIn`, `freePort`, `killChildren` and the boot `ADMIN`) live
in `tests/integration/app-process.js`. The lifecycle, tenancy and
grants-sufficient files use `tests/integration/server-child.js` instead (see
below), a parallel copy of the same helpers that takes the boot admin as an
argument and also opens `/collab`; the two were written on separate branches
and have not been folded into one module.

`tests/integration/backup-restore.test.js` is the backup and restore drill. It
boots `server.js`, saves a document with a pasted PNG and some Unicode through
`/api/save-document`, comments on it, stops the server with SIGTERM, writes a
Yjs update into `ydoc_state`, and runs `scripts/backup.sh --local` as a MySQL
user granted only on the file's schema. It pins the archive (mode 0600, exactly
three members, manifest keys and checksums, no MySQL or admin password in any
byte, no overwrite, no working files left) and the backup's refusal of root,
then each refusal `restore.sh` makes with nothing written: another database's
archive, a system schema, a held instance lock (by the load's own first
statement), a checksum, an extra member, a line-start switch or client
command, a client command after a statement, a user with more than its own
database (root with a mid-line `CREATE DATABASE`, a grant elsewhere, a global
privilege, a role, a mandatory role), a newer release's backup, and a link in
the uploads. The drill proper drops the schema and deletes the image file,
restores into an empty scratch schema as a user granted only on it, and
requires `html_content` and `ydoc_state` byte-identical, the comment and
`doc_images` rows equal, a server booted on the result to answer `/readyz`
200, and the image served to its reader and 404 to an anonymous caller. Then
`--replace`, a new install's empty tables restored without it (as a user
granted the way the MySQL image grants, with `_` escaped), a dump that names
another schema failing on the grant, and `--allow-newer-backup`.
`TAKE_LOCK_SQL` is also run directly against a lock holder. **It needs MySQL's
`mysql` and `mysqldump` on `PATH`** (the CI runner image has them; a MariaDB
client fails the backup by design, see section 4); it passes with the 8.4.11
and the 8.0.45 clients. `tests/scripts/backup-common.test.js` pins the lock
name, `version_is_newer`, and `check_compose_file` through a stand-in `docker`
on `PATH`.

Mutation-checked 2026-09-28, each confirmed landed, then restored. These turn
a test red: removing the checksum check, the name check, the dump scan, the
row check, `check_confined_user` in either script, its escaped-name spelling,
the version refusal, `TAKE_LOCK_SQL`'s line in the load, `--binary-mode`, or
`check_compose_file` (the unit test); passing `-D`. The owner-only mode has two
guards, `umask 077` and `chmod 600`, and removing either alone stays green;
removing both turns the mode test red.

`tests/integration/lifecycle.test.js` is the fourth test group that needs
real processes rather than a real server alone (the fork, sign-in and
`/collab` helpers it shares with `grants-sufficient.test.js` live in
`tests/integration/server-child.js`): it forks
`tests/integration/lock-holder.js` against the file's schema to prove the
instance lock (a second process exits 1 naming the holder's connection id, a
SIGKILLed holder frees it within two seconds, two schemas hold their own at
once, a 64-character schema name gets an exclusive lock of its own), then
boots `server.js` itself (`NODE_ENV=production`, a free port, an `APP_URL` on
that port, which production requires, and the boot admin), signs in through
`/api/login`, sends one Yjs update over
`/collab`, delivers SIGTERM 200 ms later, inside the three-second debounce, and
requires exit 0, close code 1001, the edit in `logs.ydoc_state` and the edit
served by a restarted server. Its last test boots `server.js`, KILLs the lock's
connection the way a MySQL restart would, takes the lock from the test's own
connection, and requires the server to exit 1 within 15 s naming that
connection and without `stopped cleanly`. `tests/integration/migrate.test.js`
adopts a 64-character schema under the runner's lock. Mutation-checked on
2026-09-27: a `flushPendingSaves` that skips every document leaves
`ydoc_state` NULL, a lock that ignores GET_LOCK's answer lets the second
process in, and a lost lock that never reaches `onSuperseded`, or a `server.js`
that ignores it, leaves the server running.

**The default run is pinned by name, not by omission.** `test`,
`test:watch` and `test:coverage` name `--project backend --project frontend
--project design`, because a bare `vitest run` runs every declared project,
integration included.
`tests/test-projects.test.js` (a backend test) fails if a declared project other
than `integration` is missing from `test`, `test:watch` or `test:coverage`, or if
`test:integration` runs anything but `integration`. A project added to
`vitest.config.js` without joining those scripts turns it red instead of
silently never running; `design` (W6-CDX-21) joined them and the guard passed
unedited.

### The design project (`tests/design/`)

It runs the gates of the vendored design package over `index.html` and `src/`,
`.css`, `.js`, `.jsx` and `.html` alike (69 files when it landed). The package
is `vendor/cloud-city-design/`, a byte copy of the public Apache-2.0
`Cloud-City-Computing/cloud-city-design` at the commit its `MANIFEST.json`
names under `upstream`; ESLint ignores `vendor/`, and `vendor/.gitattributes`
keeps git from converting its line endings. **It is never edited here**: a
change goes upstream, then a re-vendor PR copies exactly the files the upstream
manifest lists.

| File | Gate |
|---|---|
| `vendored.test.js` | every listed file present with its SHA-256, nothing unlisted, a full upstream commit, and the package's own `node:test` suites passing in this copy (run with the TAP reporter named and `NODE_OPTIONS` dropped, so the summary parses on Node 24, whose default reporter is spec) |
| `token-discipline.test.js` | literal colours, accent fill shades (`--brand-blue`, `--accent-100` to `-400`, and Codex's `--cx-accent-fill`) in text or edge positions, and suppressed outlines, per bucket, against `ledger.json` |
| `z-index.test.js` | every CSS `z-index` and React `zIndex` is a `var(--z-*)` that the vendored `core.css` declares, with a floor of 12 CSS declarations; the shell layers (W6-CDX-40: the sidebar at base, the account menu and the search palette above it and the editor overlays) is read from the real rules |
| `standalone.test.js` | no application source (`src`, `routes`, `services`, `middleware`, `scripts`, the root files) names a sibling product, the suite mode, a `SUITE_*` variable outside a removed entry, a `W6-CMD-` tag or the identity provider; the `Cloud City Computing` header and the two cookie names are the allowed forms (CDX-SA-5) |
| `dangling.test.js` | a `var()` with no fallback names a property some loaded stylesheet (`src/**/*.css`, the vendored `core.css`) declares, against `dangling-ledger.json` |
| `contrast.test.js` | every pair in `pairs.json` clears its minimum in each theme (dark and daylight, W6-CDX-43), and every colour binding in `src/codex.css` is in a pair, named decorative, or a `--cx-deferred-` binding that `deferred-colour.test.js` measures |
| `deferred-colour.test.js` | every text colour in the editor, comments, GitHub and read-only sections, measured where it paints (over its own background, composited, or over each surface), at 4.5:1 in both themes; every `--cx-deferred-` binding is declared in both themes and read |
| `geometry-fingerprint.test.js` | the SHA-256 of every non-colour declaration in the deferred families, against `geometry-fingerprint.json` (taken before the W6-CDX-43 colour pass; re-taken by CDX-SA-2 when the `.suite-doc-link` rule left, see that file's `_taken`); a layout change there fails (K3) |
| `bridge.test.js` | the 22 legacy names (W6-CDX-38 retired the two hover names and the three radius names, which core's values now serve) and the five fallback names in `index.css`'s `:root` are each one `var()` into `codex.css` or `core.css`, resolve through the vendored resolver, and hold no literal (the colour ones resolve to a colour) |
| `painted.test.js` | the labels the page actually paints, read from the real `src/index.css` declarations and resolved through the bridge: ink (`--cx-on-accent`) on the accent fill for the default and all eight hues at 4.5:1 or more; white on the accent dark (avatars) at 4.5:1 or more; the danger fill (`--cx-danger-fill`) with the dark ink (`--cx-on-danger`) at 4.5:1 or more at rest, pressed and on the delete hover, and the button hover an inset ring on the same fill with no background declared (W6-CDX-38); the primary hover at 4.5:1 on every hue; the focus ring (`:focus-visible`) at 3:1 on all four surfaces (the two hover steps retired in W6-CDX-38) for the default and eight hues, and no rule in `index.css` removes an outline; each toast label at 4.5:1 on the toast surface; a dialog close control at 4.5:1 on the dialog panel at rest and on hover, on the default and eight hues; the `.gh-merge-dialog__pane` clip draws its ring inside the control |
| `presence-palette.test.js` | every presence cursor colour in `services/cursor-colors.js` has a label (white, the ink or black, chosen by `src/lib/avatar-label.js`) that clears 4.5:1, measured through the real tokens |
| `tints.test.js` | every translucent tint a label sits on (the CI badges, the filter buttons, the content-match badge, and the Explore, Page Tree and Log Tree rows, forty-five sites, the W6-CDX-29 admin, settings and notification washes among them) is a `--cx-tint-` name in `codex.css`, and each label measures 4.5:1 or more on the tint composited over all four surfaces (W6-CDX-38; the labels on a danger tint step to `--cx-danger-bright`), on the default accent and the eight picker hues; the list cannot shrink below forty-five, and the list is now derived from the stylesheet itself (every rule whose background reads a `--cx-tint-` name, `DEFERRED_BARE` naming the deferred bare washes) |
| `code-blocks.test.js` | every highlight.js colour rule (the eight hex literals in `index.css`, which do not cover every class: seven `hljs-*` classes set no colour and inherit the block text, measured too) clears 4.6:1 on the editor code wash (`rgba(0, 0, 0, 0.25)`, read from the real `.editor-container .tiptap pre` declaration) composited over all five surfaces |
| `dialogs.test.js` | every dialog wrapper in any file under `src/` carries `role="dialog"` and `aria-modal="true"` in its own markup (seven today), every `close-button` is a `type="button"` button named Close, and `#modal-dimmer` paints `var(--cx-scrim)` with no `backdrop-filter` (seeded, so the scan cannot pass by finding nothing) |
| `accents.test.js` | the eight picker hues in `src/tokens/accents.js`, W6-CDX-43 (K7): the contract `STEPS` table, with H substituted, equals the `[data-theme][data-accent]` blocks of `codex.css` in both themes; 64 hue-by-surface-by-theme text pairs at 4.5:1 (the three surfaces and the picked wash); the focus ring at 3:1; on-accent at 4.5:1 on the fill and the fill hover on each fill, at least 15 degrees from the danger, warning, success and violet arcs read from `core.css`, no two closer than 25 degrees, `LEGACY_ACCENT` targets unique keys |
| `codex-css.test.js` | `codex.css` declares only `--cx-` custom properties and `color-scheme` (dark and light), under exactly `[data-theme]` (the shared block), the two themes and the two `[data-accent]` forms (W6-CDX-43) and `[data-theme='dark']`, with no radius and no `--cx-surface-3` or `-4` (W6-CDX-38), never a core name; `index.html` sets the attribute; `src/main.jsx` imports core, fonts, codex, index in that order; no font under `public/`; Vite keeps legal comments |
| `theme.test.js` | every row of `tests/design/theme.json`, dark and light columns, the theme table restated, equals its declaration in `src/codex.css` as text (W6-CDX-38; the light column, W6-CDX-43) |
| `hover-fills.test.js` | no rule in `index.css` reads `--bg-hover` or `--bg-hover-strong`, the bridge declares neither, and the deferred editor, comments and GitHub banners bind their hover to `--cx-surface-2` (Decision 12, W6-CDX-38) |
| `fallbacks.test.js` | no `var()` fallback under `src` is a colour literal, and no undeclared name carries a fallback except `--editor-font-size`, which script sets (W6-CDX-38) |
| `control-font.test.js` | `.btn`, `button`, `input`, `select` and `textarea` declare `font-family: inherit` on the shell (X4), and the rule excludes the deferred families (comments, the editor and its toolbars, Markdown, Draw.io, Version History, GitHub), which keep the browser font until W6-CDX-43 (K3). The family only: the `font` shorthand also reset size and line height, which grew the top-bar search field (W6-CDX-38) |
| `ghost-hover.test.js` | a ghost button's hover background differs from the page's and from a dialog panel's (`.modal-content`), resolved through the dark theme (declaration-level; the live hover is checked in the browser captures) (W6-CDX-38 review) |
| `sidebar.test.js` | the sidebar's background is an opaque token (`--cx-surface-1`), with no translucent literal and no opacity (W6-CDX-40; this file was `topbar.test.js`, the top bar being gone) |
| `deferred-radius.test.js` | the deferred families read `--cx-deferred-radius-*` (4, 8, 12px) in their banners, never a core radius, until W6-CDX-43 (W6-CDX-38 review) |

**The ledger is exact, not a ceiling.** `ledger.json` holds a count per file,
and per section banner inside `index.css` (`tests/design/buckets.js`,
`banners`; an em dash in a banner title becomes a hyphen in the key). A bucket
above its number fails as a regression and one below it fails as an unrecorded
fix, so a burndown PR lowers the ledger by exactly what it burned, in the same
PR. It started at 745 (646 literal colours, 77 accent positions, 22 outline
suppressions) and stood at 679 after W6-CDX-22: the `:root` bridge took its 19
literal colours out, and the dead-CSS burndown and the picker's data file took
the rest. It stood at 631 after W6-CDX-23's review (642 after its first pass): the focus rings took all 21 outline
suppressions, the button family's literals and label went, and the Toast's six
literals went; the Buttons and Focus and Accessibility buckets are deleted. It stands at 510 after W6-CDX-28: the eight content banners (Log Tree, Explore, Page Tree, the search box and results, the Error Boundary and the 404 log) burned to zero, each bridged or Codex-token fallback replacing its literal. It stands at 399 after W6-CDX-29: the admin, settings, notification, activity, watch, archive-access, squad, export and preferences banners burned to zero (68 findings in `index.css`), the five literals in `AccountMenu.jsx` (the Google mark and the swatch check) moved to tokens, and the leftover modal, login, signup and presence banners burned too (20 findings: Modal + Overlay, Login Tabs, Signup Validation Styles and Toast Notifications). What remains is the editor canvas, the comments and the GitHub surface (the roadmap row UI-later), each with a `_later` note in `ledger.json`; Version History, Read-only Document View and Markdown Editor are classified editor-adjacent and wait with the canvas. The file-level buckets in `Editor.jsx`, `GitHubPage.jsx`, `util.jsx` (the print stylesheet), the comment components and the embed picker stay with their surfaces. The label and tint steps are not ledger entries: `tints.test.js` measures them, and the syntax-highlighting literals stay in the Syntax Highlighting bucket, lightened to clear 4.6:1 on the strongest surface, a margin over the 4.5:1 line (`code-blocks.test.js`). `node tests/design/report.mjs [file]` prints the live buckets.
In `src/codex.css` (`TOKEN_DEFINITIONS`) the literal rule skips the
custom-property declarations (`maskCustomProperties` blanks them first),
because those literals are the definitions the contrast gate measures; a
literal on any other property there still counts, and `codex-css.test.js`
pins that the file declares custom properties and nothing else.

Mutation-checked when it landed: a hex in `Toast.jsx`, an `outline: none` or a
`color: var(--cx-accent-fill)` in `index.css`, a `var(--nope)`, one byte of the
vendored `core.css`, `--cx-text-faint` below 4.5:1, an unpaired colour binding,
a non-`--cx-` name, `data-theme` dropped, legal comments stripped, the import
order swapped, a fixed dangling reference restored, and one literal removed
without lowering the ledger each turned the named test red, as did two added
after review: a literal on a plain property in `codex.css`, and `NODE_OPTIONS`
passed through to the package suites. A first seed of the
hex landed inside `Toast.jsx`'s doc comment and reddened nothing, correctly: the
scanner masks comments.

`coverage.include` does not reach `tests/` or `vendor/`, so the project adds no
coverage threshold.

**`tests/namespace-urls.test.js` reads every text file in the repository**
(source, tests, docs, SQL, compose files and workflows; not `node_modules/`,
`dist/`, `coverage/` or `public/`). Every `xmlns` value must be on its short
allowlist of W3C namespaces, and no `http(s)` URL's host may end in a product
word (`workspace`, `squad`, `archive`, `log` and their plurals), since no real
top-level domain is one. It exists because the organizations-to-workspaces
rename was a text substitution that reached inside URLs: ten inline SVG icons
declared `www.w3.workspace` as their namespace until 2026-09-28, and React
rendered them regardless, so no page looked wrong. The file leaves itself out,
because its fixtures are the broken spellings.

**The appearance preference contract (W6-CDX-44).** `tests/src/lib/appearance-prefs.test.js` covers every rule of the
contract (precedence, the cookie attributes and write order, the domain rule, held writes, clearing, the reconcile,
the migration, and both re-apply paths) against a `document.cookie` jar that records each write verbatim.
`tests/src/theme-boot.test.js` runs `index.html`'s own script in each precedence case, and the accent cases cover every
legacy key. `tests/theme-boot-hash.test.js` (backend project) holds the CSP hash to the script's bytes in `index.html`
and in a fresh `vite build`'s `dist/index.html`; `tests/app.test.js` checks the production `script-src` carries it.
`tests/src/components/` covers the header's Appearance popover (focus, Tab, Escape, outside press), the shared Appearance groups and the header slot.
The design gates run in both themes as before; the deferred-families geometry fingerprint stayed unchanged.

Measured 2026-10-12 on `track/w6-cdx-44-theme-contract`: `npm run lint` clean on the changed files; the default run
(`backend`, `frontend`, `design`) **209 files, 4745 tests, all passing** (the start of the session: 203 files, 4632).

### The live-MySQL project (`tests/setup.integration.js`)

The only tests in the repo that touch a real database. The per-file setup runs
before each test file is imported and **does not** mock `mysql_connect.js`:

1. Opens an admin connection from `IT_DB_HOST` (default `127.0.0.1`),
   `IT_DB_ROOT_USER` (default `root`) and `IT_DB_ROOT_PASSWORD` (required; the
   setup throws without it). The server must answer on **3306**, because
   `mysql_connect.js` reads no `DB_PORT`.
2. Creates a throwaway schema `c2_it_<12 hex>`, builds `init.sql` into it over a
   `multipleStatements` connection (`init.sql` has no `USE`, so it builds into
   the current schema), and adopts it with the runner's exported
   `runMigrations({ adoptFreshInstall: true })`. If either step throws, it drops
   the schema before rethrowing.
3. Sets `DB_HOST`, `DB_USER`, `DB_PASS` and `DB_NAME` to that server and schema,
   **before** the test file imports any app module, which is what makes the
   pool in `mysql_connect.js` bind to it. `dotenv` never overrides a variable
   already set, so a developer's `.env` cannot redirect it.
4. Drops the schema in `afterAll`.

The admin helpers (`adminConfig`, `buildSchemaFromInitSql`, `loadInitSql`,
`queryVia`, `dropSchema`, `dropUser`, `throwawaySchemaName`) live in
`tests/integration/mysql-admin.js`;
a test that needs a second schema (as the adoption-refusal test does) builds it
with them and drops it in its own `finally`. `queryVia` wraps **one**
connection, never a pool, because the runner's advisory lock is per connection.

The global teardown drops every `c2_it_` schema, every `c2itrecipe` schema
followed by exactly 12 hex digits (`isThrowawaySchema` in `mysql-admin.js`
checks the shape, so a developer's own `c2items` survives a run pointed at
their server), and every `c2_it_` account, still on the server and fails the
run naming them. **Trap: Vitest 4 only logs an error thrown from a
globalSetup teardown ("error during close") and exits 0**, so the teardown sets
`process.exitCode = 1` before it throws; the throw alone would leave a leak
green (found by mutation, 2026-09-25). Because it counts every `c2_it_` schema,
two integration runs sharing one server at once would report each other's; give
each concurrent run its own server.

**The shared-server recipe (W6-CDX-33).** `tests/integration/instance-recipe.js`
reads the SQL block under "Several instances on one MySQL server" in
`docs/deployment.md` and runs it as root with throwaway names (a `c2itrecipe<hex>`
schema, since the recipe allows letters and digits only, and `c2_it_<hex>_app`
and `_mig` accounts), then builds the schema as the migration account by the
fresh-install path (`buildFresh`) or the upgrade path (`buildUpgraded`: every
post-baseline file applied for real, as that account). `tenancy.test.js` does
this for two instances and asserts the exact MySQL error for 54 statements
from instance A's accounts that reach for B, for the server or for DDL, plus
`SHOW DATABASES`, `information_schema`, the process list, `LOAD_FILE`, both
accounts' `SHOW GRANTS`, the app account's connection cap (1226 past it) and
each instance's lock held at once. It also reads every system view the app
account can and pins `information_schema.TABLESPACES_EXTENSIONS` as the only
one naming the other instance (MySQL lists every schema's tablespaces to every
account), and requires the recipe to say so. Its last two tests demonstrate why
the recipe bans `_` in schema names and pin the teardown's name filter. `grants-sufficient.test.js` boots `server.js` as the
app account alone and runs a smoke path, a `/collab` edit and a SIGTERM, and
fails on any privilege error in the child's log. Access-control section 9 has
the boundary; `docs/research/instance-isolation-2026-09-28/` the run, its ten
mutations and the recipe driven through the release compose file.

`tests/integration/migrate.test.js` holds four tests: a canary that
fails if `c2_query` is a mock, adoption recorded every migration file, a second
run is a no-op, and adoption refuses a schema missing a post-baseline column
(`password_reset_tokens.purpose`). Each was mutation-checked on 2026-09-25:
reintroducing the mock fails the canary, skipping the `afterAll` drop fails the
teardown, a migration that `ALTER`s a missing table fails the setup, and
dropping `--project frontend` from `test` fails the guard.

**Trap: the setup never executes a migration file.** `adoptFreshInstall`
records every file and applies none (`scripts/migrate.js` `runUnderLock`); its
only check on a post-baseline file is `schemaClaims`, which asks whether the
table, column or named key the file adds already exists. A file with broken SQL whose
objects `init.sql` already has adopts cleanly (reproduced in review,
2026-09-25). `tests/integration/upgrade-path.test.js` is what runs migration
SQL: it builds `init.sql` into a second schema, applies the undo statements in
`tests/integration/pre-runner-state.js` newest first, records the baseline with
`runMigrations({ baseline: true })`, applies every post-baseline file with a
plain `runMigrations`, and requires an information_schema fingerprint
(columns, indexes, table constraints, checks, foreign keys) equal to the
per-file schema's. A second test fails when a post-baseline file has no undo
entry or an entry names a file that is gone. Mutation-checked on 2026-09-25:
invalid SQL in `2026-09-08-token-purpose.sql` (a parse error), `VARCHAR(64)`
for `VARCHAR(32)` and the `CHECK` dropped (fingerprint mismatch), and the undo
entry removed (both tests) each turn it red. Its limits: the upgrade runs on
empty tables, so a migration's handling of existing rows is not exercised, and
the `LEGACY_BASELINE` files are never run.

`tests/integration/oauth-one-link-per-provider.test.js` is the first test that
runs a migration over rows. It builds an install from the release before
`2026-09-25-oauth-one-link-per-provider.sql` (an `init.sql` build with the key
dropped and every other file recorded), seeds a double link, and requires the
guard's refusal to name the duplicate-finding query and leave the rows, the
key, `schema_migrations` and the routine list exactly as they were, then a
retry after resolving to apply; a clean install to apply and the key to refuse
a second link per provider (`ER_DUP_ENTRY` naming it, the shape
`services/identity.js` matches); adoption to refuse a schema missing the key;
two Google sign-ins racing for one user to end with one link and one
`identity_conflict`; two GitHub links racing through the real initiation and
callback routes (`app.js` over supertest, a real session, only `fetch` to
GitHub stubbed) to end with one `github_linked=1` and one `link_conflict`; and
a GitHub relink to an account another user holds to be refused as
`already_linked_other`. Each race is held open deterministically: a
transaction takes `SELECT ... FOR UPDATE` on the user's own `users` row
(`holdUserRow`, over `holdInTransaction`), every link INSERT needs a shared lock on that row to check
its foreign key, and the test waits until `information_schema.INNODB_TRX`
shows both waiting. It is a record lock on the parent row, not a gap lock on
the child's empty range, because InnoDB takes foreign-key check locks at every
isolation level and takes no gap locks at `READ-COMMITTED`, so the file does
not depend on the server's isolation setting. Run on 2026-09-25 with the
server at `SET GLOBAL transaction_isolation = 'READ-COMMITTED'` and at the
default `REPEATABLE-READ`: all six tests pass at both, and removing the
`identity_conflict` catch still turns the Google race red at
`READ-COMMITTED`. The tests share the file's
schema, so each uses GitHub account ids the others do not. **Trap: poll
`INNODB_TRX` slower than every 100 ms.** InnoDB refreshes that table only after it has gone 100 ms
unread, so a 25 ms loop saw zero waiters for ten seconds while two were
waiting. Mutation-checked on 2026-09-25: no guard, the runner's guard cleanup
skipped, and the `identity_conflict` catch removed each turn one of them red;
the GitHub race and relink tests were red against the callback before its
`link_conflict` catch and its holder lookup (a 500 each).

`tests/integration/oauth-google-two-factor.test.js` drives Google sign-in
through `resolveIdentity` and through the real initiation and callback routes
(`google-auth-library` is the one thing stubbed, with `vi.mock`), and requires
an account with two-factor on (`totp` and `email` each) and no Google link to
be refused as `two_factor_enabled` with no `oauth_accounts` and no `sessions`
row; an account with two-factor off to link and sign in through the same flow,
which is the anchor that keeps those refusals from passing on a harness that
cannot link; an explicit NULL to count as off; and a user linked first who
turns two-factor on later to still get a session with no challenge minted (no
`password_reset_tokens` or `two_factor_codes` row). It sets the Google client
variables in `vi.hoisted`, in its own file, because `routes/oauth.js` reads
them at import. Mutation-checked on 2026-09-25: with the refusal removed from
`services/identity.js` its four refusal tests are red (the callback redirects
to `/` with a session), alongside eight mocked ones: five in
`tests/services/identity.test.js` and three across the two route seam files.

The interleaves a mocked test cannot hold open live in
`tests/integration/google-link-races.js`, a shared module (not itself a test
file) that two files run: `oauth-google-two-factor.test.js` at the server's
default isolation, and `oauth-google-two-factor-read-committed.test.js` with
every connection the app's pool opens set to READ COMMITTED (mysql2's
`createPool` is wrapped, through `vi.mock`, to add one `connection` listener
that runs `SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED`; that file's
first test proves it took on five concurrent connections). Each interleave
changes the user row between the seam's email lookup and its link INSERT: a
transaction runs the change and holds the row, the seam's lookups are plain
reads and go past it, and its link INSERT, the only statement in the ladder
that locks the user row, waits. Reaching that lock wait is the proof the
lookups already passed. The changes are the app's own enable statements
(`totp`, `email`) and an email change, and after the commit each must answer
`two_factor_enabled` with no `oauth_accounts` row; an anchor holds the row with
a change that touches neither and must link after the same wait. The old
unconditional `VALUES` INSERT waited too (its foreign-key check locks the
parent row) and then linked, which is how the race was seen; under READ
COMMITTED the conditional INSERT without `FOR SHARE` did not wait at all and
linked. Mutation-checked on 2026-09-25: `FOR SHARE` removed reddened exactly
the three READ COMMITTED refusals while the default-isolation run stayed green;
`AND email = ?` removed reddened the email-change interleave in both files and
seven mocked pins; the INSERT put back to `VALUES` reddened the default-isolation
race tests and six mocked pins; the row check loosened from `!== 1` to `=== 0`
reddened two mocked tests; and the check removed entirely reddened the race
tests (the seam reported a link it never wrote). Every race file holds the user
row through one helper, `holdInTransaction` in `tests/integration/mysql-admin.js`
(the C7 races lock it `FOR UPDATE`, these run the change itself), and waits
through `waitForLockWaits` beside it, which carries the 100 ms trap described
above. A record lock on the user row holds at every isolation level, so all of
them pass with the server at `READ-COMMITTED` as well.

`tests/integration/update-account-sessions.test.js` proves the update-account
session rotation on a real server, where the route tests can only prove the SQL
text. Each test seeds a user with two sessions minted by `generateSessionToken`
(two sign-ins, so two devices), drives `POST /api/update-account` over
supertest, and reads `sessions` back: a password change and a current-password
email change each leave exactly one row, the digest of the token the caller
was handed, with both old tokens answering 401 on a `requireAuth` route; a wrong
current password leaves the hash and both sessions untouched; the password-less
code flow writes an `email_change` row carrying `new_email`, sends the code to
the old address, and after `/update-account/confirm-email` leaves one session.
Mail is the one module stubbed (`vi.mock` of `services/email.js`), so the code
is read back off the call. A last test inserts rows that break
`chk_password_reset_tokens_new_email` both ways and requires error 3819.
Mutation-checked on 2026-09-25: accepting a wrong current password, keeping the
caller's session (`AND id != ?`), handing back the old token, and keeping the
caller's session in the confirm step each turn a live test red.

`tests/integration/sessions.test.js` proves one hashed session per sign-in
(W6-CDX-2) on a real server: two sign-ins make two rows and deleting one by its
digest leaves the other valid, no stored id equals a raw token, JavaScript's
`hashSessionToken` equals MySQL's `SHA2(?, 256)`, an insert that omits
`auth_provider` fails with `ER_NO_DEFAULT_FOR_FIELD` and one naming `oidc` fails
the CHECK. Its last test runs `migrations/2026-09-27-session-per-sign-in.sql`
for real in the file's own schema: it takes the column and CHECK off and
un-records the file, inserts a raw-token row, an upper-case-hex raw token (only
case separates it from a digest) and an existing digest, runs `runMigrations`,
and requires both raw tokens to sign in through the real
`validateAndAutoLogin`, the digest to be untouched, and the file's `UPDATE`
run again to change zero rows. Mutation-checked on 2026-09-27: dropping the
`UPDATE`, the `'c'` flag or the `DROP DEFAULT`, or the CHECK from `init.sql`,
each turns a live test red.

`tests/integration/remove-sibling-connection.test.js` proves the 0.15.0 removal migration on a real server:
it applies to the pre-migration schema (expiring revoked invitations, deleting the env webhook rows, dropping
the two columns), its undo returns them, a fresh build adopts it, and the adoption is refused on a schema that
still has the columns. The reconciliation read it replaced has no integration test any more.


Three files prove the outbound-events outbox (W6-CDX-13).
`tests/integration/webhooks-schema.test.js` proves the tables' constraints: the
`source` and `status` CHECKs, that only an `env` subscription goes without a
stored secret, both unique keys, and the cascades from a subscription and from
an event to their deliveries. `tests/integration/webhooks-emit.test.js` drives
each of the eight emitted actions through its real route with a real session
and reads exactly one `webhook_events` row and one `webhook_deliveries` row per
matching subscription (made through the admin API, so its SQL runs too); a
coalesced `log.update`, a `comment.create`, a subscription for another
workspace or for other event types, a delete naming a log outside its archive,
and no subscription at all each write nothing for that subscription or event;
a subscription disabled in the database since the cache loaded gets no
delivery, and an event it leaves with none is rolled back; the stored body is
byte-stable and is the envelope its row describes (its `occurred_at` read back
in UTC); a stored 30,000-character title is emitted as 255 code points; and with
a test-created trigger that `SIGNAL`s on every outbox insert, a save still
answers 200, writes its activity row and notifies its watcher.
The env-subscription integration file (`webhooks-env.test.js`, boots through each env state) was removed in CDX-SA-3 with the env subscription. Mutation-checked on 2026-09-28: letting the
emit throw out of `doLogActivity` turns the trigger test red (no watcher
notification); the unit suites catch dropping the title bound, the workspace
match, the no-subscription early return, the SQL re-check, the queue drop on a
retarget, the IPv4-in-IPv6 check, the every-address check and the admin gate.
`tests/integration/webhooks-worker.test.js` (W6-CDX-14) runs the delivery worker
against a real receiver on 127.0.0.1 and reads the outbox back: the body is
byte-identical to the stored row and its signature verifies; event 3 is not
received before event 2; two workers on one database send each of 50 deliveries
exactly once; a lapsed lease writes nothing after another worker delivers; 410,
422, a 302 (not followed) and a receiver that never answers (`ETIMEDOUT` after
ten seconds) each land their outcome; the backoff gaps read back from
`paused_until` are 10, 20, 40, 2560 and 3600 seconds; a 73-hour event is
dead-lettered and never sent; a name that now resolves to a private address is
not sent to; the connect goes to the approved address; the prune keeps what
the rules keep; and 20 saves with a hanging subscriber and a worker ticking
beside them are measured against 20 without one. The in-process jobs are now
three: the activity prune, the session prune, and the delivery worker with its
prune.

`tests/integration/admin-sync.test.js` proves the boot admin sync never
promotes (GHSA-w8q3-r34w-3pjh), which only a real server can: which row the
lookup returns first, and how `LOWER(name)` and the email collation compare,
are MySQL's. Each test creates the admin with a real first-boot
`ensureAdminUser()`, lets the admin give up its name, its email or both, has a
member (own password, two sessions) take the freed name or address, and boots
again: the member is still not an admin with its row and sessions unchanged,
the admin's row is unchanged, the call returns `null`, and exactly one refusal
line is logged. All four shapes run with the member's row older and newer than
the admin's, because a lookup that read only the first row passes one order and
fails the other. A last test proves `.env` still resets an existing admin's
email and password. Before the fix, five of the eight takeovers promoted the
member, two crashed on the `users.email` unique key and one synced the admin.
Mutation-checked on 2026-09-25: removing the refusal turns 8 of 9 red (five
with the member's password and email overwritten, two on the unique-key crash,
one with the admin synced), and checking only the first matched row turns the
two two-row cases red in the admin-older order.

Tests mirror the source tree:

```
routes/foo.js            -> tests/routes/foo.test.js
routes/helpers/foo.js    -> tests/helpers/foo.test.js
services/foo.js          -> tests/services/foo.test.js
middleware/foo.js        -> tests/middleware/foo.test.js
src/**                   -> tests/src/**
```

### The backend mock surface (`tests/setup.js`)

Four global `vi.mock` calls apply to **every** backend test:

- `../mysql_connect.js`: `c2_query` returns `[]`, `generateSessionToken`
  returns `'mock-session-token'`, `validateAndAutoLogin` returns `null`,
  `touchSession` and `endPool` no-op, and `openConnection` rejects, so no
  backend test can reach a real server by accident.
- `../services/email.js`: `sendEmail` and `verifyEmailConnection` stubbed.
- `sharp`: a chainable stub with `resize`/`webp`/`toFile`.
- `fs/promises`: **only `mkdir` and `unlink`.**

That last one is a sharp edge. `routes/helpers/images.js` also calls `fs.stat`,
`fs.writeFile` and `fs.readFile`, which the global mock does not provide, so a
test touching those paths must supply its own mock. `tests/helpers/images.test.js`
does.

Because `c2_query` is a mock returning `[]` by default, **most backend tests
assert against a queue of `mockResolvedValueOnce` calls in the exact order the
handler issues queries.** Adding a query to a handler, even a harmless one,
shifts that queue and breaks tests that were passing for the right reason. When
a test breaks after a route change, check the mock ordering before assuming the
change is wrong.

Helpers in `tests/helpers.js`: `TEST_USER`, `TEST_USER_2`,
`mockAuthenticated(user)`, `mockUnauthenticated()`, `resetMocks()`. Note there
is **no `ADMIN_USER`** despite the root `CLAUDE.md` listing one; admin tests
pass `mockAuthenticated({ ...TEST_USER, is_admin: true })` explicitly.

`tests/setup.frontend.js` adds jest-dom matchers and, after each test, runs
Testing Library `cleanup()`, clears `localStorage` and `sessionStorage`, and
empties `document.body`.

### Coverage thresholds

`vitest.config.js:116-210`. The global floor is deliberately low because
`src/pages/` and `src/extensions/` are untested by policy:

```
lines 43   statements 40   branches 33   functions 26
```

Above that sit **48 per-glob thresholds** (this map and the root `CLAUDE.md`
both used to say 26, which was a miscount). The 30th, `services/identity.js`,
arrived with the identity seam; the 31st, `services/session-token.js` (95 on
all four), with hashed sessions; the 32nd, `routes/doc-images-serve.js` (95
lines, 92 branches), with the authorized image handler; the 33rd to 35th,
`routes/health.js`, `services/shutdown.js` and `services/instance-lock.js`,
with W6-CDX-31; the 36th, `env-contract.js`, with the configuration
contract; the 37th, `services/session-cookie.js` (95 on all four), with the
`__Host-` session cookie (W6-CDX-3); and the 38th to 40th, `routes/webhooks.js`
(97 lines), `services/webhooks.js` (95 lines, 90 branches) and
`services/webhook-target.js` (95 lines, 88 branches), with the outbound-events
outbox (W6-CDX-13). The 41st, `services/webhook-worker.js`, with the delivery
worker (W6-CDX-14), is measured in the backend project alone, where the
timeout branch and the live-MySQL paths are not counted. Review follow-ups (2026-10-11), each with a test: `tests/integration/webhooks-order.test.js`
holds the order lock from a second connection and shows an emit waits and takes an id above
the one committed under it (mutation-checked: removing the lock turns both emit tests red),
and checks that overlapping emits never leave a gap in the visible ids;
`tests/integration/webhooks-cap.test.js` runs the 50-subscription cap through the admin API
at the limit; `tests/integration/webhooks-worker.test.js` gained a late success that cannot
clear another worker's live backoff, a lease renewed after a four-second DNS check, and a
subscription beyond the twentieth being served. The unit suite gained the round-robin
starvation test, the expired-then-next continuation, the query deadline, the skipped-tick log,
the renewal before the send, and the UTC-only SQL assertion; `tests/services/webhook-target.test.js`
gained the AWS IPv6 metadata refusal under `WEBHOOK_ALLOW_PRIVATE_TARGETS=1` and the bounded DNS
lookup; `tests/services/shutdown.test.js` gained the worker stop running first.

The security-critical and
well-covered modules are ratcheted high:

| Glob | lines |
|---|---|
| `routes/documents.js` | 95 |
| `routes/notifications.js` | 95 |
| `routes/comments.js` | 92 |
| `routes/admin.js`, `routes/archives.js` | 90 |
| `services/notifications.js` | 90 |
| `services/identity.js` | 95 |
| `routes/doc-images-serve.js` | 95 |
| `routes/helpers/**` | 88 |
| `routes/auth.js`, `routes/squads.js`, `routes/watches.js`, `mysql_connect.js` | 85 |
| `middleware/**` | 80 |
| `services/collab.js` | 65 (raised from 25 in the gap-fix pass) |
| `src/util.jsx` | 65 |

Plus `services/email.js` and `email-templates.js` at 95, `app.js` at 75 lines
but only 5 branches, `env-contract.js` at 100 (it is data only, so importing it
covers all of it), `src/editorUtils.js` and `src/userPrefs.js` at 95,
`scripts/**` at 82, and five per-hook thresholds.

`coverage.include` is an allowlist, so a directory absent from it is **invisible
to coverage rather than under-covered**. `scripts/**/*.js` was added to it when
the migration runner landed; anything new outside `routes/`, `middleware/`,
`services/`, `scripts/`, `src/` and the four named root files needs the same
treatment or it silently counts for nothing.

**The practical consequence:** adding an uncovered branch to a high-threshold
file fails CI even though every test passes. Write the test with the code. When
you raise real coverage, ratchet the threshold up in the same PR; the comment at
`vitest.config.js:110-115` explains the "achieved minus a small buffer" policy.

## 6. CI

`.github/workflows/ci.yml`, on push to `main` and on every pull request. Ubuntu, Node 20, npm
cache keyed on `cloudcodex/package-lock.json`, working directory `cloudcodex`:

```
npm ci -> npm audit --omit=dev --audit-level=moderate -> npm audit (advisory only)
       -> npm run lint -> shellcheck -x scripts/*.sh -> npm test -> npm run test:integration
       -> npm run test:coverage -> npm run build
```

The ShellCheck step runs from the repository root (`working-directory: .`) with
the runner image's own `shellcheck`; the Bash scripts are the only code no
other step reads.

**The dependency gate** is the step named `Dependency audit (production,
blocking)`, first after `npm ci`, so a finding fails in seconds under its own
name. It fails on any advisory of moderate severity or worse against a package
`npm ci --omit=dev` would install, which is the tree the Docker image ships.
Dev tools are excluded so that an advisory in a lint or test dependency does
not turn every PR red; the next step, `Dependency audit (all, advisory only)`,
runs a plain `npm audit` with `continue-on-error: true`, which leaves a red
mark on that step and a green job. Before
the gate, `npm audit` ran nowhere, and 19 production findings had
accumulated by 2026-09-28.

What it does and does not catch:

- It turns red **without a code change** when an advisory is published
  against a package already in the lockfile. The fix is an upgrade
  (`npm audit fix`, or a major read against its changelog), never a lower
  `--audit-level`.
- It needs the registry: an npm outage fails the step, which a re-run
  clears.
- `release.yml` does not run it, so a tag cut from a commit that predates
  an advisory still publishes.

The job carries a `mysql:8.4.11` **service container** (root password
`ci-root-password`, published on 3306, health-checked with `mysqladmin ping`),
and the `Integration tests (live MySQL)` step runs `npm run test:integration`
against it with `IT_DB_HOST=127.0.0.1`. The password is not a secret: it guards
an ephemeral container that lives only as long as the job. Both sit **inside**
the existing job, so the check `main` already requires covers the live-MySQL
project with no branch-protection change.

The job is named `Lint, test and build`, and that name (not the job id `test`)
is the check run context. Renaming the job renames the check, and would break
the gate until branch protection is updated to match.

**Whether that check is actually required on `main` is repository configuration,
not repository content, and it is applied by hand.** Until someone runs the
`PUT` on `/branches/main/protection`, `main` has no `required_status_checks` key
at all and a red run is mergeable on one approval. Verify rather than assume:

```
gh api repos/Cloud-City-Computing/c2/branches/main/protection/required_status_checks
```

A 404 means no status check is required yet.

`npm run build` is the newest step and the reason for the job's name: lint and
the suite never exercise the production Vite build, so a bad import or a
dependency missing from `vite.config.js`'s `manualChunks` used to pass CI and
reach `main`. The build emits a "Circular chunk" warning about the vendor chunks
and still exits 0, so that warning is not a gate failure.

Coverage is uploaded as an artifact with 14-day retention,
`if: always()`. That `if` makes the upload run even when an earlier step failed;
it does not affect the job's own pass or fail conclusion.

**There are no pre-commit hooks.** Running lint and tests locally is on you.
`npm ci` means the lockfile must be committed and current.

On **push** the filter is still `main` only, so work on a side branch does not
burn CI until it becomes a pull request. On **pull_request** there is no branch
filter: every PR runs, whatever its base. That matters for stacked PRs, whose
base is another feature branch. Under the old `pull_request: branches: [main]`
filter they reported no checks at all, and a required status check that never
reports blocks a merge permanently rather than failing it.

## 6b. Releases

`.github/workflows/release.yml`, triggered by pushing a `v*` tag. Two jobs:

1. **verify** re-runs `npm ci`, `npm run lint`, `npm test`,
   `npm run test:integration` (against the same `mysql:8.4.11` service CI uses)
   **and `npm run test:coverage`**. The integration step means a tag cannot
   publish an image whose `init.sql` does not build on MySQL 8.4, or whose
   post-baseline migrations do not upgrade a pre-runner schema to exactly what
   `init.sql` builds (section 5). A tag is not evidence the commit is green, because
   tags can point at any commit and `ci.yml` only runs on `main`. The coverage
   run is not optional padding: the 48 per-glob thresholds are CI's real gate,
   so omitting it would make the release path weaker than the thing it claims
   to be re-proving.
2. **publish** needs `verify`, then builds `./cloudcodex` with buildx and
   pushes `ghcr.io/cloud-city-computing/cloud-codex` at both the bare version
   and `:latest`, with `packages: write` and the GITHUB_TOKEN.

Deliberate details, each of which is load-bearing:

- The image is named for the **product**, not the repository. `c2` is a legacy
  codename and a string people type into `docker pull` is user-facing. The
  owner is spelled out in lowercase because ghcr.io rejects the mixed-case
  `Cloud-City-Computing` that `github.repository_owner` would give.
- A guard step fails the build when the tag does not match
  `cloudcodex/package.json`'s version **or** the default pinned in
  `docker-compose-release.yml`. A published image that misreports its own
  version is worse than no image, and a stale compose default means the
  documented `docker compose up` silently runs an older release than the
  README describes.
- **The tag name reaches that guard through `env:`, never through a `${{ }}`
  expansion inside `run:`.** A tag is attacker-choosable and `v1.0.0$(id)` is a
  valid, pushable ref, so interpolating it into the script body would be
  command execution in a job holding `packages: write` and a ghcr.io login.
- The trigger is `v[0-9]+.[0-9]+.[0-9]+`, not `v*`, so a prerelease tag cannot
  start a publish.
- **`:latest` is only moved when the tag is the highest version tag in the
  repo.** A hotfix on an older line (`v0.9.1` cut after `v1.0.0` shipped) is
  normal, and republishing `:latest` from it would downgrade everyone tracking
  that tag onto a database a newer release has already migrated. The job checks
  `git tag --sort=-v:refname` and, when it is not the newest, publishes the
  version tag alone and says so with a `::notice::`.

**Known gap: `linux/amd64` only.** arm64 would mean cross-building `sharp` and
`bcrypt` under QEMU, which is slow and fails in ways that only appear at
runtime. Apple Silicon runs the amd64 image under Docker Desktop's emulation.

**One-time setup the workflow cannot do for you: make the package public.**
A container package first created by Actions is **private**, whatever the
repository's visibility, and nothing in `release.yml` can change that. The
publish job goes green and an anonymous `docker pull` still answers
`unauthorized`, which is the worst possible failure here because the whole point
of the image is that a stranger can run it. Confirmed after the 0.9.0 publish.

Fix it once, in the web UI. Straight to the page:

https://github.com/orgs/Cloud-City-Computing/packages/container/cloud-codex/settings

**Danger Zone → Change visibility → Public.** While you are there, "Manage
Actions access" → add the `c2` repository with Write, so future publishes keep
working if the default token scope tightens.

**This cannot be scripted.** There is no REST endpoint: `GET` on the package
answers 403 asking for `read:packages`, while `PATCH .../packages/container/
cloud-codex -f visibility=public` answers **404**, because the route does not
exist. No token scope changes that. It is a click, once per package, forever.

Verify from a logged-out client rather than trusting the workflow:

```
docker logout ghcr.io
docker pull ghcr.io/cloud-city-computing/cloud-codex:0.14.0
```

`docker-compose-release.yml` consumes the published image instead of building,
pinned to `${CLOUDCODEX_VERSION:-0.14.0}` so an evaluator's install does not
move under them on the next publish. It also differs from
`docker-compose-prod.yml` in not publishing 3306 at all: the app reaches MySQL
over the compose network, and Docker's published ports are a DNAT rule that sits
in front of the host firewall, so publishing it on every interface of a VPS
exposes the database to the internet past a `ufw deny`. The prod file publishes
it on 127.0.0.1 only, for a client on the host.

**Both** compose files mount a named volume `app_public` at `/app/public`.
Uploaded avatars and extracted document images live only there:
`routes/helpers/images.js` writes the file and then replaces the base64 data URI
in `html_content` with a `/doc-images/` URL, so after extraction the file on
disk is the sole copy. Without the volume, `docker compose pull && up -d`
recreates the container and destroys every one of them while the database keeps
pointing at them.

## 7. Shippability checklist

Before calling a change done:

1. `npm run lint` clean, no new warnings.
2. `npm test` green. A schema or migration change also needs
   `npm run test:integration` green against a live MySQL, and a new migration
   file needs its undo in `tests/integration/pre-runner-state.js`, or the
   upgrade-path test is red. That test runs on empty tables: a migration that
   rewrites existing rows needs its own seeded test.
3. New env vars in `.env.example` with a comment, and an entry in
   `cloudcodex/env-contract.js` (see `request-lifecycle.md` section 1).
4. New heavy frontend deps added to `manualChunks` in `vite.config.js`.
5. New SQL in **both** `migrations/` and `init.sql`. Never add the new file to
   `LEGACY_BASELINE` in `scripts/migrate.js`; that list is closed.
6. New admin-visible behaviour documented in the relevant `docs/*.md`, and any
   architectural change reflected in the matching `docs/maps/` file.
7. UI changes verified in a browser at desktop and mobile widths.
8. No `console.log`, no commented-out code, no `.only`/`.skip` in tests, no
   unlinked `TODO`/`FIXME`.

---

## Related

- [data-model.md](data-model.md) for the `init.sql` and migration contract.
- [request-lifecycle.md](request-lifecycle.md) for what `app.js` exposes to
  Supertest.
- [open-questions.md](open-questions.md) for the ops issues above that read as
  defects.