# 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/.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('')`. 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 | |---|---|---| | `_app` | ``SELECT, INSERT, UPDATE, DELETE ON ``.*``, `MAX_USER_CONNECTIONS 15` | the app (`DB_USER`) | | `_mig` | ``ALL PRIVILEGES ON ``.*``, 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 `/`. 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.