docs/maps/access-control.md

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

# Access Control Map

**Read this before touching any permission code.** Cloud Codex does not have one
access-control system. It has one *primary* system plus five smaller ones that
guard different verbs, and they do not agree with each other in every case.

---

## 1. The primary system: SQL fragments in `ownership.js`

`routes/helpers/ownership.js` exports four functions that compose into a query.
The pattern everywhere is:

```js
`... WHERE p.id = ? AND ${readAccessWhere('p')} LIMIT 1`,
[id, ...readAccessParams(user)]
```

`readAccessWhere(alias)` (`ownership.js:26-40`) emits a parenthesised OR of
**seven** clauses. Written out, access is granted when **any** of these holds:

| # | Clause | Reads |
|---|---|---|
| 1 | `? = TRUE` | `user.is_admin`, passed as a bound param |
| 2 | `JSON_CONTAINS(p.read_access, ?)` | the archive's per-user grant array |
| 3 | `p.created_by = ?` | archive creator |
| 4 | squad joined to workspace, `workspaces.owner_id = ?` | workspace owner, matched on the `users` FK |
| 5 | `squad_members.role = 'owner' OR can_read = TRUE` | squad membership |
| 6 | `JSON_CONTAINS(p.read_access_squads, CAST(sm.squad_id AS JSON))` | per-squad grant array |
| 7 | `p.read_access_workspace = TRUE` and the user is in *any* squad of the same workspace | workspace-wide flag |

`writeAccessWhere` (`ownership.js:52-66`) is structurally identical against
`write_access`, `can_write`, `write_access_squads`, `write_access_workspace`.
Clauses 1, 3 and 4 are shared verbatim, so **the archive creator and the
workspace owner always have write access**, and there is no way to demote them
short of changing `created_by`.

**Clause 1 is an unconditional admin bypass.** Any query that interpolates
`readAccessWhere`/`writeAccessWhere` without narrowing the `WHERE` further
matches *every* archive platform-wide for an admin, including ones an admin
should not casually stumble into.

### `system` archives, and the `excludeSystemArchives()` rule

A `system` archive is one the app creates for its own bookkeeping: today, the
hidden per-PR archives hosting GitHub PR-session documents. Since 2026-08-09
**ordinary users hold real grants on these**, because that is how PR-as-document
works at all (B1 in [open-questions.md](open-questions.md)). That makes the
distinction load-bearing rather than an admin-only curiosity:

> **Archive-as-a-place versus archive-as-an-ACL.** Any query that treats an
> archive as *somewhere a user browses, lists, manages or deletes within* must
> add `AND ${excludeSystemArchives('p')}` alongside the fragment. Any query
> resolving *whether this user may touch this document* must not.

`excludeSystemArchives(alias)` in `ownership.js` is that predicate. It takes no
parameter, so it does not disturb the seven-param contract below, and it spells
the test `NOT COALESCE(alias.\`system\`, FALSE)` because the column is
nullable and `NULL = FALSE` is NULL, which would hide the row from everyone.

Applied in:

| File | Surfaces |
|---|---|
| `routes/archives.js` | listing, log listing, rename, ACL read and write, log create/update/delete, repos |
| `routes/search.js` | search, browse, filters |
| `routes/upload.js` | `POST /archives/:archiveId/logs/upload` |
| `routes/github.js` | `POST /github/import-to-codex` |

**Those last two are the trap.** There are three ways to create a log inside an
archive, and they live in three different routers; excluding only the obvious
one in `archives.js` left the other two writing into a hidden archive that
nothing could then list or clean up. Any new archive-scoped route belongs in
this table.

Deliberately **not** applied in `checkLogReadAccess`/`checkLogWriteAccess`,
`/api/presence`, `/api/document`, `routes/favorites.js` or `routes/activity.js`:
those are document-level or per-user opt-in, and are exactly what the PR
feature rides on.

Getting this wrong is not cosmetic. Reviewed on 2026-08-09: because the grant
carries write, an unscoped `DELETE /api/archives/:id/logs/:logId` let anyone who
had opened a PR session delete that PR's shared document and, by cascade, every
mirrored review comment on it. `GET /api/archives/:id/access` likewise let them
enumerate the name and email of everyone else who had opened it.
`routes/first-run.js` had the rule right before there was a rule.

### The param contract

```js
readAccessParams(user)  // ownership.js:42-44
writeAccessParams(user) // ownership.js:68-70
// both: [Boolean(user.is_admin), JSON.stringify(user.id), user.id, user.id, user.id, user.id, user.id]
```

**Always exactly 7 params, in that order.** The fragment is string-interpolated
into the SQL, the params are bound positionally, and there is no runtime check
that the two agree. Adding a clause to `readAccessWhere` without adding the
matching param to `readAccessParams` shifts every subsequent `?` in the whole
query and silently produces wrong results rather than an error. If you change
one, change all four, and update `tests/helpers/ownership.test.js`.

Two details of the params worth internalising:

- **Param 2 is `JSON.stringify(user.id)`**, i.e. the string `"7"` for user 7,
  because `JSON_CONTAINS` needs a JSON document, not an integer. Elsewhere in
  the codebase the same arrays get appended as `CAST(? AS JSON)` with a
  `String(user.id)` argument (`routes/github.js:1715-1726`, the PR-session
  grant). Both produce the JSON number `7`, so they interoperate, but the two
  spellings are easy to confuse.
- **Every param is now the user's id** except param 2's JSON spelling. Param 4
  used to be `user.email`, because `workspaces.owner` was a `TEXT` column
  holding an email address rather than a foreign key, so changing a user's
  email silently destroyed their workspace ownership and a later account
  registering that address inherited it. `workspaces.owner_id` is an INT
  referencing `users(id) ON DELETE SET NULL` as of
  `migrations/add_workspace_owner_id.sql`. The param count is unchanged at 7.

### Callers

Never write permission SQL by hand. The wrappers already exist in
`routes/helpers/shared.js`:

| Function | Line | Returns |
|---|---|---|
| `checkLogReadAccess(logId, user)` | `shared.js:76-87` | the log row, or `undefined` |
| `checkLogWriteAccess(logId, user)` | `shared.js:93-104` | the log row, or `undefined` |
| `checkArchiveReadAccess(archiveId, user)` | `shared.js:188-197` | the archive row, or `undefined` |
| `checkArchiveWriteAccess(archiveId, user)` | `shared.js:173-182` | the archive row, or `undefined` |

Routes that need the fragment inline (search, browse, export, GitHub link
loading) interpolate it directly; see `routes/documents.js:581`,
`routes/search.js`, `routes/github.js:1023`.

## 2. The critical subtlety: everything resolves against the ARCHIVE

`checkLogReadAccess` (`shared.js:76-87`) joins `logs` to `archives` and applies
`readAccessWhere('p')` where **`p` is the `archives` table**. The log's own
columns are never consulted.

`logs.read_access` and `logs.write_access` exist in the schema
(`init.sql:300-301`). Grepping the whole backend for reads of them turns up
nothing. Since 2026-08-09 the only thing that writes them is the PR-session
log insert (`routes/github.js:1698`), which sets both to an empty
`JSON_ARRAY()`.

**They are write-only columns**, and the decision on 2026-08-09 was to keep
them that way. Any future feature that "grants access on a document" by writing
`logs.read_access` will appear to work, persist correctly, and grant nothing.
The PR-session feature used to do exactly that and was admin-only for it; it
now grants on a per-PR archive instead. See
[github-integration.md](github-integration.md) and B1 in
[open-questions.md](open-questions.md).

The practical rule: **the archive is the ACL boundary.** Per-document
permissions do not exist.

## 3. The six secondary systems

### 3a. Global feature permissions: `requirePermission(flag)`

`middleware/permissions.js:40-111`. Guards *creation* verbs, not access to
existing rows. Three flags: `create_squad`, `create_archive`, `create_log`.

Resolution order:

1. `req.user.is_admin`, allow (`permissions.js:48`).
2. Load `req.permissions` from the `permissions` table if not already loaded,
   falling back to `DEFAULT_PERMISSIONS` (`permissions.js:51-61`).
3. **Tenant check.** If `req.body.squad_id` is present and well formed, the
   caller must be inside that squad's workspace, or 403
   (`isSquadWorkspaceMember`, `permissions.js:73-84`). This sits *above* the
   global flag deliberately; see 3e.
4. Global flag set, allow (`permissions.js:87-89`).
5. Otherwise derive a squad from `req.body.squad_id`, or from
   `req.params.archiveId` via the archive's `squad_id` (`permissions.js:93-102`).
6. Workspace owner of that squad, allow (`permissions.js:105-111`).
7. `squad_members.can_create_archive` / `can_create_log`, allow
   (`permissions.js:113-125`).
8. Else 403.

`DEFAULT_PERMISSIONS` (`shared.js:48`) is
`{ create_squad: false, create_archive: false, create_log: true }`, applied to
any user with no `permissions` row. New users created through the normal paths
get a row with **all three true** via `createDefaultPermissions`
(`shared.js:207-212`), so the default only applies to rows that predate it or
were made outside those paths.

Note step 7 maps only two of the three flags (`permissions.js:114-117`). There
is no squad-level fallback for `create_squad`, which is correct: squads are
created in a workspace, not in a squad.

Currently applied on exactly two routes: `routes/archives.js:118`
(`create_archive`) and `routes/archives.js:490` (`create_log`), plus the upload
route `routes/upload.js:95` (`create_log`).

Step 3 is what makes the global flag mean "may create" rather than "may create
anywhere". `POST /api/archives` takes its `squad_id` from the body and this
middleware is its only gate, so before step 3 existed any account could plant an
archive inside any squad in any workspace. The 403 reuses the generic permission
message rather than naming the squad, so the route does not become a squad
enumeration oracle.

`create_squad` is not enforced through this middleware at all. `POST
/api/workspaces/:workspaceId/squads` (`routes/squads.js`) checks the flag
inline, and only **after** `isWorkspaceMember` (`ownership.js`): the workspace is
the tenant boundary, so holding the flag means "may create a squad", not "may
create a squad anywhere". `createDefaultPermissions` (`shared.js`) hands every
new account `create_squad`, so without that ordering any account could enrol
itself as a squad *owner* inside a workspace it has no relationship to, and squad
ownership is a live term in `readAccessWhere`/`writeAccessWhere`. A caller who is
neither the workspace owner nor a member of some squad in the workspace gets the
same `404` and the same body as a workspace that does not exist, deliberately: a
distinguishable `403` would make the route a workspace enumeration oracle. An
orphaned workspace (`owner_id` NULL because the owner account was deleted) is not
a public workspace either; with no owner to match, membership is the only way in,
and such a workspace is adopted by an admin rather than claimed by whoever asks
first. That rule and the middleware's step 3 are two halves of the same boundary;
3e below is where the whole of it is written down.

### 3b. Publish: `canPublish`

`shared.js:132-159`. Ordered bypasses: no squad context at all, allow; admin,
allow; workspace owner, allow; `squad_members.can_publish` or
`role = 'owner'`, allow; archive creator, allow; else deny.

Called from the REST publish route and from the collab WebSocket publish message
(`services/collab.js:670`), so both paths share one policy.

### 3c. Archive ownership: `isArchiveOwner`

`ownership.js:76-91`. A *narrower* check than write access, used for
destructive and administrative verbs. Admin, archive creator, workspace owner
(by email), or squad member with `role = 'owner'`. Note it does **not** honour
`can_write` or the JSON grant arrays: someone with full write access on an
archive still cannot delete it or change its ACLs.

Callers: delete archive (`archives.js:199`), manage access
(`archives.js:260`), link and unlink archive repos (`archives.js:750`,
`archives.js:799`).

### 3d. Squad management: `canManageSquad`, and its GitHub-only twin

`canManageSquad(squadId, user)` in `routes/squads.js`. Admin, workspace owner,
squad creator, or member with `can_manage_members`. Returns
`{ squad, allowed }`, and every squad-member route in that file gates on it.

**A second helper answers the same question differently.**
`userCanManageSquad(user, squadId)` in `routes/github.js` serves the two
team-sync routes only. It admits admin, workspace owner, a member whose `role`
is `owner`, or a member whose `role` is `admin` **and** who holds
`can_manage_members`. So it does not admit the squad creator, and it reads
`can_manage_members` only alongside an `admin` role, where `canManageSquad`
reads the flag on its own. Same name shape, different rule: check which file
you are in before assuming either answer.

### 3e. The tenant boundary: `isWorkspaceMember` / `isSquadWorkspaceMember`

`ownership.js:103-140`, added 2026-09-08. **The workspace is Cloud Codex's
tenant boundary**, and until these two helpers existed nothing in the codebase
could ask "is this user inside workspace W?". Four routes therefore read a
caller-supplied workspace or squad id as authorisation on its own, because
`createDefaultPermissions` (`shared.js`) hands every new account all three
global flags and each of those routes treated the flag as sufficient.

There is no `workspace_members` table. Membership of workspace W resolves as:
admin, OR `workspaces.owner_id = user`, OR a member of some squad whose
`workspace_id` is W. `isSquadWorkspaceMember(user, squadId)` looks the squad's
`workspace_id` up and delegates to `isWorkspaceMember`. Both short-circuit for
admins before touching the database, and both parameterise every id.

Enforced at exactly four points:

| Where | What it gates |
|---|---|
| `POST /api/workspaces/:workspaceId/squads` (`squads.js:101`) | `isWorkspaceMember` before the `create_squad` lookup, non-owner path only. Squad creation enrols the caller as a squad *owner*, which is a live term in clause 5 of both fragments. |
| `requirePermission(flag)` (`permissions.js:73-84`) | `isSquadWorkspaceMember` on `req.body.squad_id`, above the global-flag short circuit. Step 3 of 3a. |
| `POST /api/archives/:id/access` (`archives.js`) | the grantee, user or squad, against the archive's workspace, on `action: 'add'` only |
| `GET /api/users/search` (`routes/auth.js`) | a non-admin caller sees themselves, users who share a workspace with them, the owners of those workspaces, and unattached accounts only if they can invite, instead of every account and email on the install |

Two shapes of the same question, and they are not interchangeable. The user
branch of the archive ACL check asks *is this grantee inside the archive's
workspace*, and passes a synthetic user with `is_admin` forced false, because
`isWorkspaceMember` short-circuits on `is_admin` and the question there is the
grantee's tenancy, not their privilege. The squad branch does not use
`isSquadWorkspaceMember` at all: "is the granted squad inside this workspace" is
a direct `squads.workspace_id` comparison, not a membership test.

`POST /api/archives/:id/access` gates `add` and deliberately leaves `remove`
open to a cross-tenant grantee. Gating removal would make exactly the
pre-existing cross-tenant grants unrevokable, which is the opposite of the point.

**The UI has to source the revoke id from the grant list, not the picker.** The
Manage Archive Access modal (`ManageArchiveAccessModal` in `ArchiveBrowser.jsx`)
puts a Revoke control on each explicit grant returned by
`GET /api/archives/:id/access` and calls the same handler with that row's id.
The user search cannot serve this: `/api/users/search` is scoped to the caller's
workspace, so a cross-tenant grantee never comes back from it, and the picker
alone left the open `remove` path unreachable for precisely the grants it exists
to clear. Owner-squad members are listed alongside without a Revoke control:
they hold no `read_access` / `write_access` row, so a remove for them is a no-op.
The squad tab has the same shape, because `workspace_squads` in that response is
scoped the same way.

**A grant another clause shadows is worded as a grant, not as access.** Clause 2
is one of seven, and three of the others are visible in the same response:
`owner_squad_members` (clause 5), `granted_squad_user_ids` (clause 6, and it
folds the owning squad's members in too) and `read_workspace` / `write_workspace`
(clause 7). A user in `read_access` who is *also* an owning-squad member renders
as a granted row, not an inherited one, so before this the operator was told
`Successfully revoked write access for Alice` while clause 5 still resolved for
her. `inheritedAccessSource()` in `ArchiveBrowser.jsx` names the shadowing route
from those three fields; the row gains an `also inherited from ...` note, the
control reads **Remove Grant**, and the confirmation and toast talk about the
explicit grant being removed. The control stays, because the ACL entry is real
and clearing it is meaningful. The workspace-flag arm can over-warn (clause 7
also requires membership of some squad in the workspace, which the response does
not carry per user), which is the safe direction for a control that used to
claim the opposite of the truth.

**The ACL check enters on an orphaned squad rather than skipping it.** The guard
is `if (owning)`, not `if (owning?.workspace_id)`. An archive with no squad at
all yields no row from the `JOIN squads` and skips the check, as documented. A
squad row whose `workspace_id` is NULL goes *through* it and is refused, because
neither branch can match a NULL tenant: `isWorkspaceMember` binds `Number(null)`,
which is workspace 0, and `squads.workspace_id = NULL` evaluates to NULL rather
than true. Until 2026-09-08 the guard tested the column, so an orphaned squad
skipped the check entirely and its archive owner could grant to any user or
squad in any workspace: the one place the boundary failed open.

**`GET /api/users/search` cannot be scoped on shared membership alone.** An
account with no `squad_members` row anywhere shares no workspace with anyone,
and that is where every account starts, since Google SSO auto-provisioning
(`resolveIdentity` in `services/identity.js`, called from `oauth.js`) and an admin invitation with no squad (`admin.js`) both write the
`users` row and nothing else. Membership-only scoping made such an account
invisible to every non-admin caller, which severs the squad invite flow: the
picker in `InviteMemberModal` is driven entirely by this endpoint, so the
account became unaddable by anyone but a platform admin, with an empty list as
the only symptom. Two further disjuncts close that: **owners of the caller's own
workspaces** (a workspace owner who joined no squad was invisible to their own
members), and **accounts with no squad membership at all, gated on the caller
being able to invite** (a `squad_members` row with `role` owner or admin or
`can_manage_members`, or owning a workspace). The gate is the point. Ungated,
the second disjunct would hand most of a young install's user table to every
caller, which is the enumeration the boundary exists to stop.

**The 7-param fragments were deliberately not touched.** These helpers add no
clause to `readAccessWhere`/`writeAccessWhere` and no param to
`readAccessParams`/`writeAccessParams`, so the contract in section 1 is
unchanged at seven. The boundary is a precondition on *creating* a row or naming
a grantee; the fragments answer the different question of who may reach a row
that already exists. Folding one into the other would have meant an eighth param
and a rewrite of every caller, for a check most of those callers do not need.

**Why the archive-derived squad is not part of the middleware check.** Step 5 of
3a derives a squad from `req.params.archiveId` for `create_log`. That derivation
stays exactly where it is, below the global flag, and is not hoisted into step 3:

- Both `create_log` routes re-check with `writeAccessWhere` immediately after
  the middleware (`archives.js:506-516`, `upload.js:107-119`), so the
  archive-derived path was never open the way the body path was.
- Checking it in the middleware would be a behaviour regression. A caller
  holding the global `create_log` flag plus an explicit `write_access` JSON
  grant matches clause 2 of the fragment and can create logs today without
  belonging to the workspace at all. An early archive-derived tenant check would
  403 them before the fragment is ever consulted.
- Hoisting the `SELECT squad_id FROM archives` above the global flag would also
  fire a query that does not run on that path today, shifting the `c2_query`
  mock queue in the archives and upload tests.

**Refusals that are decisions, not oversights:**

- An **orphaned workspace** (`owner_id` NULL after the owner account was
  deleted) is not a public workspace. Membership is then the only way in, and
  such a workspace is adopted by an admin rather than claimed by whoever asks
  first. The squad-creation route answers `404` with the byte-for-byte
  not-found body rather than a distinguishable `403`, so it is not a workspace
  enumeration oracle.
- An **orphaned squad** (`workspace_id` NULL) has no tenant to resolve, so
  `isSquadWorkspaceMember` answers false for everyone but an admin rather than
  reading "no workspace" as "any workspace".

**The orphaned-squad rule is a behaviour change with a cost, and it was priced
in.** Because step 3 runs above the global flag, a body-supplied squad is now
validated on the no-flag path too, so an orphaned squad no longer reaches the
`squad_members` fallback at step 7 either. A member holding `can_create_archive`
on an orphaned squad used to get `201` and now gets `403`. That was accepted
rather than special-cased: an orphaned squad has no tenant, so "is this user
inside its tenant?" is unanswerable, and failing closed is the right answer to
an unanswerable question. It is also consistent with the orphaned-workspace
rule above. All four `INSERT INTO squads` sites set `workspace_id`
(`squads.js:116`, `workspaces.js:80`, `admin.js:162`, `admin.js:253`), so only
legacy or hand-edited rows can be in this state.

**The fix is prospective.** It stops new cross-tenant rows and removes none of
the ones already there, each of which still resolves through the fragments as an
ordinary row: an owner `squad_members` row satisfies clause 5, a planted archive
matches `created_by` in clause 3, a cross-tenant grant matches clause 2.
`docs/security.md` carries three read-only enumeration queries for finding them
and ships no cleanup migration on purpose, because no query separates an attack
row from an install that used these routes exactly as they behaved. See B16 in
[open-questions.md](open-questions.md).

### 3f. Document images: `readableDocImageHashes` (W6-CDX-34)

`routes/helpers/images.js`. A stored image (`public/doc-images/<hash>.webp`,
the first 16 hex digits of the uploaded bytes' SHA-256) is visible to a user
when either holds:

1. **they uploaded it**: some `doc_images` row for the hash has
   `uploaded_by = user.id`. This survives the uploader losing access to the
   document, but not the document being deleted (the row cascades away);
2. **they can read a document that holds it**: some `doc_images` row for the
   hash names a log whose archive passes `readAccessWhere('p')`, the same
   fragment `checkLogReadAccess` uses, so an admin sees every image.

One query answers both, for a batch of hashes: the `IN (...)` list first, then
`user.id`, then the seven `readAccessParams(user)`. Three callers ask it, and
nothing else may read an image file for a user:

| Caller | What a "no" does |
|---|---|
| `docImagesHandler` (`routes/doc-images-serve.js`), the `/doc-images` mount | the empty 404 ([request-lifecycle.md](request-lifecycle.md) section 2) |
| `inlineImagesForExport` / `inlineImagesForMarkdownExport` (HTML, DOCX, markdown export) | the reference stays a URL instead of becoming a data URI |
| `recordDocImages`, for every `html_content` write that extracts images | the reference is stored but gets no row for this document |

**The write-path gate is the part that is easy to lose.** `doc_images` rows
are how a document grants its readers an image, so whoever can write a
document can grant. `recordDocImages(logId, html, writer, { saved, introduced })`
therefore records a hash as the writer's own (`uploaded_by = writer.id`) only
when this write decoded its bytes (the `saved` set `extractImagesFromHtml`
fills), and records a reference (with `uploaded_by` NULL) only when it is in
`introduced`, the references this write put there, and the writer can already
see that image. Every other reference in the HTML is left alone, however much
the writer can see. Holding the bytes is not a leak, because they hash to the
same name.

Both halves matter. Without the readability check, knowing an image's address
is enough to read it: paste the URL into any document you can write, and the
handler serves it to you as that document's reader. Without the "introduced"
limit, that planted reference is granted anyway by the next save, publish or
restore from anyone who can see the image (the owner fixing a typo, any
admin), a confused deputy. What each write may vouch for:

| Write | `introduced` |
|---|---|
| `POST /api/save-document` | `introducedDocImages`: the references the new HTML adds over `old_content`; with a live session open on the document, only those the session credits to this writer |
| `POST .../publish`, `POST .../restore` | none: they store what the document already held |
| `POST /api/archives/:id/logs/upload` (import) | every reference: the document is new |
| live editor save and publish | per writer, the references `DocImageCredits` credits to them (below) |

In the live editor every client's save carries the whole shared document, so
the sender is not the one to ask. `services/collab.js` keeps a
`DocImageCredits` per open document (registered by `openDocImageCredits`, so
the REST save can ask it too) that credits a reference to the writer whose own
sync frame first put it in: the frame's bytes named it, it was not in the
shared document before and is after, and the stored HTML does not already show
it. So the first client filling the shared document from stored HTML, or an
edit that recreates a node, claims nothing, and a reference typed a character
at a time is nobody's. REST save and restore tell the session what they stored
(`noteStoredDocImages`), so what the restoring editor then pushes in is not
credited either. The REST routes record before they write, so a failure fails
the request (and a retry, whose previous HTML is unchanged, still sees what it
adds); the live editor records after its ack, in its own try/catch.

The upload route (`POST /api/doc-images/upload`) is the other writer: it
requires a `logId` the caller can write (`checkLogWriteAccess`) before it
processes anything, and records each image as the uploader's.

**What the credits do not cover.** A writer who can see an image and moves a
planted reference (cut, then paste after a save) vouches for it, as they would
by pasting it themselves. If the server restarts before a planted edit reaches
the database, a client re-sending its copy of the shared document on reconnect
is credited with it. Both need the victim writer's own action or a restart
inside a few seconds; they are accepted, and noted here so they are not
forgotten.

**What is not gated.** `insertDocImageRows` writes whatever it is handed; only
the upload route (after its write check) and the one-time backfill
(`scripts/backfill-doc-images.js`, which trusts what existing documents
already show, since those images were public until this change) call it
directly. The backfill refuses to run over a table that already has rows unless
`DOC_IMAGES_PUBLIC=1` or `--again`, because run after go-live it would trust
every reference saved since. The GitHub import and pull paths store remote
markdown without recording rows, and no later save records a reference it
did not add, so an image reference arriving that way stays hidden (fails
closed) until someone who can see the image puts it in again: removes it and
saves, then adds it back. With `DOC_IMAGES_PUBLIC=1` the handler and export ask
nothing; recording still happens.

## 4. How membership itself is granted

The checks above all assume a `squad_members` row already exists; this
section is about how one gets created. Three paths, one of them new:

1. **Squad/workspace creation.** `addSquadOwnerMember` inserts the creator as
   `role = 'owner'` with every flag `TRUE`.
2. **Accepting a `squad_invitations` row.** A pending invitation the recipient
   must explicitly accept (`routes/squads.js`), which inserts the
   `squad_members` row with whatever role and flags that invitation carried.
3. **An invitation-carried squad on `user_invitations`, accepted through
   signup.** `POST /api/admin/invitations` can attach a `squadId`, `role` and
   permission flags to the invitation. When the invited person creates their
   account (`POST /api/create-account`), `addSquadMember`
   (`routes/helpers/shared.js`) inserts the `squad_members` row inside the
   same transaction as the account itself. **This is the only one of the
   three that does not require the recipient to accept a separate
   `squad_invitations` row**: membership is a side effect of accepting the
   account invitation, not a second, independent grant the user has to act on
   afterward. See [data-model.md](data-model.md) for the `user_invitations`
   columns that carry it.

All three insert into the same table with the same shape, so every check
elsewhere in this map (clauses 5 and 6 of the SQL fragments, the per-flag
table below) applies identically regardless of which path created the row.

## 5. Per-member flags and where each is enforced

`squad_members` (`init.sql:211-227`) carries `role` plus seven booleans. Their
enforcement is uneven, which is worth knowing before you assume a flag does
something:

| Flag | Enforced by |
|---|---|
| `can_read` | clause 5 of `readAccessWhere` (`ownership.js:31`) |
| `can_write` | clause 5 of `writeAccessWhere` (`ownership.js:57`) |
| `can_create_log` | `requirePermission('create_log')` step 7 (`permissions.js:116`) |
| `can_create_archive` | `requirePermission('create_archive')` step 7 (`permissions.js:115`) |
| `can_manage_members` | `canManageSquad` (`squads.js`), and `userCanManageSquad` (`github.js`) on the team-sync routes only, where it counts only alongside an `admin` role |
| `can_publish` | `canPublish` (`shared.js:132-159`) |
| `can_delete_version` | version delete route only (`documents.js:531-543`) |

`role` is an enum of `member`/`admin`/`owner`, but only `owner` is load-bearing
in the SQL fragments (`ownership.js:31`, `ownership.js:57`). `admin` is treated
as an ordinary member by every access check; it only affects UI and the squad
management helper's `can_manage_members` grant path.

**`squad_permissions` no longer exists.** It was a settings table with no
enforcement path: read and written by `GET`/`PUT /api/squads/:id/permissions`
and by nothing else, while `requirePermission` consulted the global
`permissions` table and the `squad_members` columns. Toggling it persisted a
value that changed no behaviour. Removed 2026-08-09 along with both routes,
since `squad_members.can_create_*` already answers the same question and is
enforced. See [open-questions.md](open-questions.md) A3.

## 6. Admin

`users.is_admin` short-circuits every layer: clause 1 of both SQL fragments,
step 1 of `requirePermission`, the first bypass in `canPublish` and
`isArchiveOwner`, and `requireAdmin` (`middleware/auth.js`) for the
`/api/admin/*` surface.

The admin user is reconciled from `.env` on every boot by `ensureAdminUser()`
(`server.js`, a top-level `await` before the port opens; defined in
`routes/admin.js`), which is why `ADMIN_EMAIL` is boot-fatal if unset, and
`ADMIN_USERNAME` and `ADMIN_PASSWORD` are boot-fatal only while local sign-in is on
(`server.js`, the "Require Admin credentials" block; the rule is `localSignInEnabled()`
in `services/auth-providers.js`, and the contract kind is `required-with-local`).

**The OIDC-only-mode rules, in one place (W6-CDX-8).** A list without `local` is OIDC-only mode
(`services/auth-providers.js`, `parseAuthProviders` at `:86`). Local sign-in is refused by `requireLocalSignIn`
(`middleware/auth.js:175`) on every route that is a local human sign-in, with 404 before any read. Google
sign-in is refused by `requireProviderOffered` (`middleware/auth.js:187`). The account update refuses an email or
password change with 403. A session whose `auth_provider` is not `oidc` is refused by `validateAndAutoLogin`
(`mysql_connect.js`), and the boot purges those rows (`services/sessions-oidc-only.js:18`). An invitation admits a
verified email in one transaction (`services/identity.js:216`, `createFromInvitation`, reached from step 3 of the
oidc ladder at `:71`), matched with a binary collation so an accented address never matches its unaccented twin.
A deactivated user (`users.deactivated_at`) is refused on every rung (`HIT_SQL` at `identity.js:106` joins the
user row), by `validateAndAutoLogin`, by local password login, by `POST /api/2fa/verify`, and by the 60-second socket re-check
(`documents-and-collab.md`, section 2).

**On an OIDC-only instance (no `local` in `AUTH_PROVIDERS`, W6-CDX-8) the match is
`LOWER(email) = LOWER(ADMIN_EMAIL)` alone**, never the name. A new admin is created with
no `password_hash`, under `ADMIN_USERNAME` when no account holds that name and under
`deriveUniqueUsername(ADMIN_EMAIL)` otherwise. An existing admin is synced by email only
(`UPDATE users SET email = ?`), and no password is written or read. A non-admin holding
the address is refused as below. The starter workspace is named from the admin's row
(`bootstrapInstance`, `routes/admin.js:170`, `admin_name`), so an OIDC-only instance with no `ADMIN_USERNAME` still
seeds its `General` squad. The OIDC rungs can create users, but only through an
invitation (`resolveIdentity`, `createFromInvitation`), never through the admin sync, and
no path promotes a user by email or name collision.
The create is safe under two boots at once: an `INSERT` that loses the race on the unique key is read again
once (`ensureAdminUser({ retried: true })`), and the second look decides, so the losing boot adopts the admin
the winner made, or refuses a non-admin, and never makes a second one.

**The boot sync creates or syncs, and never promotes** (GHSA-w8q3-r34w-3pjh).
It reads every row matching `LOWER(name) = LOWER(ADMIN_USERNAME)` or
`email = ADMIN_EMAIL`, with no `LIMIT`: `users.name` and `users.email` are each
`UNIQUE` (`init.sql`, `CREATE TABLE users`), so the two can be two different
rows, as when the admin renamed or changed address and a member took the old
one. Then, in `ensureAdminUser`:

| Rows matched | What boot does | Returns |
|---|---|---|
| none | `INSERT` the admin (`is_admin = TRUE`) and its `permissions` row | the new id |
| any row with `is_admin` false | nothing: no `UPDATE`, no `INSERT`, no hash; one `console.error` line naming the account, "refusing to promote it" | `null` |
| only admins | `UPDATE users SET password_hash, email` on the lowest id: `.env` resets the admin's password and email at every boot. `is_admin` is not in the `SET`, so this `UPDATE` cannot promote | that id |

Every outcome logs one `admin sync:` line (`created`, `synced`, or the
refusal) and none logs the password. `null` flows into
`bootstrapInstance(null)`, which returns `false` before any query, so a refused
boot seeds nothing. **In application code the only way to make an existing
account an admin is `PUT /api/admin/users/:id/admin`**, the admin console's
toggle, and an account promoted that way is synced from `.env` from the next
boot on. Two admins matching (one by name, one by email) is not refused: the
lower id is synced, and when the other holds `ADMIN_EMAIL` the `UPDATE` fails
on the unique key, which `server.js` logs as `admin user sync failed` and boots
on. The refusal is proved on MySQL, in both row orders, by
`tests/integration/admin-sync.test.js`.

## 7. Checklist for adding a protected route

1. `requireAuth` first, always. There are no internal endpoints and no machine
   credential (removed in 0.15.0); the only surfaces are public HTTP and the two
   WebSockets, and every one of them is reached by a session.
2. Creation verb, add `requirePermission('<flag>')`. If the route takes a
   workspace or squad id from the caller rather than deriving it from a row the
   caller already reaches, gate it with `isWorkspaceMember` /
   `isSquadWorkspaceMember` as well: the flag means "may create", never "may
   create anywhere" (3e).
3. Reading or writing an existing document or archive, call one of the four
   `check*Access` helpers, or interpolate the fragment with the matching
   `*Params` spread. Never hand-roll the SQL. A new path that writes
   `html_content` through `extractImagesFromHtml` calls `recordDocImages`
   with the same `saved` set and, as `introduced`, only the references that
   write adds (3f), and one that reads image files for a user asks
   `readableDocImageHashes` first.
4. Destructive or ACL-changing, use `isArchiveOwner`, not write access.
5. Wrap in `asyncHandler`, end the router with `router.use(errorHandler)`.
6. Add the negative test. Every route test file in `tests/routes/` already has
   an access-denied case to copy; `tests/helpers/ownership.test.js` covers the
   fragments themselves, and its glob carries an 88% line threshold
   (`vitest.config.js`, the `routes/helpers/**` entry).

---

## 8. Between instances: the MySQL grant (W6-CDX-33)

Everything above runs **inside** one instance, where the tenant boundary is the
workspace (3e: `isWorkspaceMember` in `ownership.js`). Several instances can
share one MySQL server, one schema each, and nothing in the application
separates them: no query names a schema, and `c2_query` runs as whatever
account `DB_USER` names. **The boundary between instances is that account's
grant**, set up by the recipe in `docs/deployment.md` ("Several instances on
one MySQL server"):

| Account | Grant | Used by |
|---|---|---|
| `<schema>_app` | ``SELECT, INSERT, UPDATE, DELETE ON `<schema>`.*``, `MAX_USER_CONNECTIONS 15` | the app (`DB_USER`) |
| `<schema>_mig` | ``ALL PRIVILEGES ON `<schema>`.*``, no `GRANT OPTION`, `MAX_USER_CONNECTIONS 3` | `init.sql` and `npm run migrate` |

Nothing global for either (`SHOW GRANTS` is exactly `USAGE ON *.*` plus the one
schema line), so neither holds `PROCESS`, `FILE`, `SUPER` or `CREATE USER`.

**Proved, not asserted.** `tests/integration/tenancy.test.js` reads the SQL
block out of `docs/deployment.md`, runs it as root for two instances on one
server, builds one by the fresh-install path and the other by the upgrade path
(every post-baseline migration applied for real, the `CREATE PROCEDURE` guard
included), both as their migration account, and then tries 44 statements as
instance A's app account and 10 as its migration account, reaching for B, for
the server, and (the app account) for DDL on its own schema, each asserted to
fail with the exact MySQL 8.4.11 error: 1142 (table access) for reads, writes, joins, subqueries,
a CTE named after a real table, `UNION`, `TABLE`, `HANDLER`, `DESCRIBE`,
`PREPARE` and a `RENAME` into B; 1044 (database access) for `USE`,
`SHOW TABLES FROM`, `LOCK TABLES`, `DROP DATABASE` and a `GRANT` either way;
1370 for `CALL` of B's routine; 1095 for `KILL` of B's connection; 1227 for
`INTO OUTFILE`, `SET GLOBAL` and `CREATE USER`; 1045 for `LOAD DATA INFILE`.
B holds a row A would find if any of them succeeded. `SHOW DATABASES` answers
exactly `information_schema`, `performance_schema` and A's own schema;
`information_schema` has no row about B's tables, columns, routines or schema;
the process list shows A its own connections only; `LOAD_FILE` returns `NULL`.
**Names are not rows, though.** The same file reads every `information_schema`
and `performance_schema` view A's app account can read and looks for B's schema
name: exactly one has it, `information_schema.TABLESPACES_EXTENSIONS`, which
MySQL 8.4 shows to every account without a privilege check and which lists
every file-per-table tablespace as `<schema>/<table>`. So the grant keeps B's
rows, `SHOW DATABASES` entry and grants from A, but not B's schema and table
names; the test pins that one view (a second one naming B, or none, turns it
red) and that the recipe says so, and the recipe asks for opaque schema names
for that reason.
The app account has no DDL on its own schema either. The same file proves each
instance's single-writer lock is held at once on one server, and that the app
account's connection cap refuses the connection past it with 1226 while
instance B still connects. `tests/integration/grants-sufficient.test.js` boots
`server.js` as the app account alone and runs boot, a signed-in smoke path
(create, save, a `FOR UPDATE` rename, comment, publish, search, deletes,
sign-out), a `/collab` edit and a SIGTERM flush, with no privilege error in
the log.

**Schema names are letters and digits.** In a database-level `GRANT`, `_` and
`%` in the schema name are wildcards while `partial_revokes` is off (the
default), so `GRANT ... ON c2_acme.*` also opens `c2xacme`. The last test in
`tenancy.test.js` demonstrates it on a lookalike schema; the first pins that
the documented recipe's grants name a backticked `[a-z0-9]+` schema. Escaping
the underscore (`c2\_acme`) is not a fix: with `partial_revokes` on, the
backslash is read literally and the app loses its own schema.

**What neither boundary protects against.** An operator with the server's
root password reaches every schema. Every account can list every schema's
name and table names (`TABLESPACES_EXTENSIONS`, above) and read the server's
global counters (`performance_schema.global_status`). `app_public` (avatars, document images) is
a volume, not a table, so two instances mounting one volume share files the
grant never sees; each needs its own. The instances share the server's CPU,
memory and disk. And inside an instance, an admin (`is_admin`) passes every
check above (section 6).

Mutation-checked on 2026-09-28: adding `GRANT SELECT ON *.*` to the app account
in the recipe turns 26 tests red; dropping its `MAX_USER_CONNECTIONS` turns the
cap test red; a migration account granted a hand-picked list without the
routine privileges fails the upgrade path on the `DROP PROCEDURE IF EXISTS`;
an app account without `DELETE` turns `grants-sufficient.test.js` red on the
comment delete; an activity insert redirected to a denied table turns it red
on the log check; and a recipe that also grants the app account `PROCESS`
turns the view sweep red, because the `INNODB_TABLES` family then names B
too.

---

## Related

- [data-model.md](data-model.md) for the ACL column families and their defaults.
- [github-integration.md](github-integration.md) for the PR-session path that
  writes the write-only log ACL columns.
- [open-questions.md](open-questions.md) for the items above that read as
  defects rather than design.
- [../security.md](../security.md) for the tenant-boundary summary and the three
  read-only queries that enumerate cross-tenant rows predating 3e.
- [../deployment.md](../deployment.md), "Several instances on one MySQL
  server", for the recipe section 8 proves.