``` ╔════════════════════════════════════════════════════════════════════════════╗ ║ ║ ║ SECURITY ║ ║ Defense-in-depth: edge → middleware → SQL → at-rest encryption. ║ ║ ║ ╚════════════════════════════════════════════════════════════════════════════╝ ``` # Security Cloud Codex follows security best practices across all layers of the stack. The model is **defense-in-depth** — a request is checked at every level between the network edge and the database, and stored secrets are encrypted even if the DB is compromised. ``` request │ ▼ ┌──────────────────────────────────────────────────────────┐ │ edge: helmet: every response in prod, /api in dev │ │ (CSP, X-Frame DENY, X-CT, Referrer, COOP) │ │ CORS allowlist (no localhost in prod) │ │ Origin required on a cookie-only /api write │ │ express-rate-limit (auth 20/15m, user search │ │ 60/15m) │ └──────────────────────────────────────────────────────────┘ │ ▼ ┌──────────────────────────────────────────────────────────┐ │ parsing: JSON 2 MB cap │ │ multer file size cap (10 MB) │ └──────────────────────────────────────────────────────────┘ │ ▼ ┌──────────────────────────────────────────────────────────┐ │ auth: requireAuth (Bearer token / session cookie) │ │ validateAndAutoLogin → user object on req │ │ requireAdmin / requirePermission │ │ workspace tenant check on a body squad_id │ └──────────────────────────────────────────────────────────┘ │ ▼ ┌──────────────────────────────────────────────────────────┐ │ input: DOMPurify (sanitizeHtml on every user-supplied │ │ HTML write — REST AND WebSocket paths) │ │ length caps + isValidId on path params │ └──────────────────────────────────────────────────────────┘ │ ▼ ┌──────────────────────────────────────────────────────────┐ │ access: ownership.js readAccessWhere/writeAccessWhere │ │ 7-step cascade in a single SQL fragment │ └──────────────────────────────────────────────────────────┘ │ ▼ ┌──────────────────────────────────────────────────────────┐ │ storage: parameterized SQL (mysql2 prepared statements) │ │ bcrypt 12 rounds for passwords │ │ AES-256-GCM (scrypt-derived) for OAuth tokens │ └──────────────────────────────────────────────────────────┘ │ ▼ MySQL 8 ``` --- ## SQL Injection Prevention All database queries use parameterized prepared statements via `mysql2`. User-supplied values are never interpolated directly into query strings. --- ## Password Storage Passwords are hashed with **bcrypt** at 12 salt rounds. Comparisons use constant-time equality to prevent timing attacks. --- ## Session Management Session tokens are 64-character cryptographically random strings (`crypto.getRandomValues`) with a 7-day expiry. IP address and user-agent are recorded per session. **Every sign-in is its own session**, so each device holds its own token and signing out of one leaves the others signed in. **The database stores only a SHA-256 digest of each token** (`sessions.id`), and every lookup and delete hashes the presented token first, so a copy of the `sessions` table, from a backup or a dump, yields nothing a browser can present. Expired sessions are deleted daily. A successful password reset deletes every session of the user. ### The session cookie In the browser the token lives in a cookie. **On https it is named `__Host-sessionToken`**, which a browser accepts only with `Secure`, `Path=/` and no `Domain`, so no other host under your domain can set it. Over plain http it is `sessionToken`, the name every earlier release used, because a browser keeps neither a `__Host-` nor a `Secure` cookie there. It is `SameSite=Strict` and never carries a `Domain`. It is not `HttpOnly`: the page reads the token to authenticate its two WebSockets. The prefix matters when other sites share your registrable domain (`docs.example.com` beside `app.example.com`). Script on any of them can set `sessionToken=; Domain=example.com; Path=/api`, and a browser sends the cookie with the longer path first. The server and the page both take the prefixed cookie over a legacy one wherever it sits, and match the name exactly: a cookie whose name only looks like `__Host-sessionToken` (one that starts with a Unicode space, say) is a different cookie to the browser, free of the prefix rules, and is never read as this one. A lone legacy cookie authenticates only while `LEGACY_SESSION_COOKIE` allows it: - **Unset (the default)**, a browser still holding the older `sessionToken` stays signed in. On its next visit over https the page asks the server to confirm that session and moves it to `__Host-sessionToken`; it never uses a lone legacy cookie before the server has said yes. - **`LEGACY_SESSION_COOKIE=0`** makes a lone `sessionToken` authenticate nobody, on the server and in the page. Set it on an https instance that shares its domain with hosts you do not fully control. Browsers that have not visited since the upgrade sign in again. The same rule covers the short-lived cookie that ties a Google sign-in or a GitHub link to the browser that started it. On https it is `__Host-oauth_state_google` or `__Host-oauth_state_github` (Secure, `Path=/`, `HttpOnly`, `SameSite=Lax`), and the callback reads the state under that exact name only, so another host under your domain cannot hand a browser a state of its own. Over plain http the names stay `oauth_state_google` and `oauth_state_github`, at `Path=/api/oauth`. ### Cross-site request forgery There are no CSRF tokens; two rules do the job instead. CORS refuses any `Origin` that is not the app's own host, `APP_URL`'s host, `CORS_ORIGIN`, or (outside production) localhost. And **an `/api` write (`POST`, `PUT`, `PATCH`, `DELETE`) whose only credential is the session cookie is refused with 403 unless it carries an `Origin` that the same rule accepts**. A browser attaches a cookie by itself but a bearer header only when the page's own script adds one, so a request carrying `Authorization: Bearer` (every call the app makes through its API helper, and every server-to-server caller) is not affected, and a browser sends `Origin` on every same-origin write. `SameSite=Strict` alone would not be enough: it treats hosts under one registrable domain as the same site. A script that posts with only a cookie must send an `Origin` or use the bearer header. --- ## Account Changes `POST /api/update-account` changes a user's name, email or password. A session on its own is enough for the name and for nothing else: - **An email or password change needs the current password** (`currentPassword`), compared with bcrypt exactly as sign-in compares it. Missing is a 400 and wrong is a 401, and neither changes anything. The check runs before the new address is tested for uniqueness, so a session alone cannot probe which addresses have accounts. The route shares the sign-in rate limit (20 requests per 15 minutes per IP). - **After an email or password change, every session of the user is deleted, the caller's included**, in the same transaction as the write, and the caller is handed a freshly generated session token. Every other device is signed out, a stolen session included, and the caller continues on its new one. (While sessions were one per user this route's older "delete all but the caller's" rule deleted nothing that mattered, because every device held the caller's token.) - **After an email change, a notice goes to the old address** when mail is enabled, so the owner hears about a change that was not theirs. - **An account with no password** (one an external sign-in created) has no current password to give. Its email change is confirmed with a 6-digit code emailed to its **current** address (ten minutes, one pending change at a time, the new address bound to the confirmation token so the code applies exactly the address it was sent for), completed at `POST /api/update-account/confirm-email`, which rotates sessions the same way. With mail disabled that change is refused with a sentence saying why. Such an account sets a first password only through Forgot Password. `POST /api/logout` deletes the `sessions` row for the presented token, and only that one: other devices stay signed in. It resolves the token through the same exported `extractSessionToken` that `requireAuth` uses (Authorization header, then the session cookie, then a `req.body.token` fallback), so a logout terminates the server-side session and not just the client's copy of the token. --- ## Single-purpose Tokens `password_reset_tokens` is a shared pool: five flows mint into it (password reset, the 2FA login challenge, TOTP enrolment, the 2FA-disable confirmation, and the email-change confirmation for an account with no password) and five flows read out of it. Only an `email_change` row carries `new_email`, and a second `CHECK` refuses a row that breaks that pairing either way. **Every row records which flow minted it, in a `purpose` column that is `NOT NULL` with no `DEFAULT`, and every reader constrains on it.** The values live in one place, `TOKEN_PURPOSE` in `routes/helpers/shared.js`, and mirror the `CHECK` constraint in `init.sql`. The column is `VARCHAR(32)` plus a `CHECK` rather than an `ENUM` on purpose: MySQL gives a `NOT NULL` `ENUM` with no `DEFAULT` an implicit default of the first listed value even under `STRICT_TRANS_TABLES`, so an omitted purpose would silently become `password_reset`. As written, omitting the column is error 1364 and an unknown value is error 3819. The rule exists because without it a token was interchangeable across flows. `POST /api/login` mints the 2FA challenge row and hands that token back to the caller in the response body, so an unconstrained `POST /api/reset-password` accepted it. That was a persistent password rewrite plus a full session wipe of the victim, so lockout and an integrity defect, not account takeover: reset-password issues no session and does not clear `two_factor_method`, and the caller must already hold the victim's password to reach the challenge at all. Consequences for anyone adding a sixth flow: - Name a new purpose in `TOKEN_PURPOSE`, add it to the `CHECK` constraint in `init.sql`, and ship a new migration that drops and re-adds the constraint under the same name, as `migrations/2026-09-25-token-purpose-email-change.sql` does. Never edit an applied migration file: the runner's checksum guard refuses it. Omitting the column fails the insert loudly, which is the point of having no default. - A new reader of this table gets `AND purpose = ?`. A token lookup by token alone is a defect. - Bulk invalidation is purpose-scoped too. Forgot-password's `UPDATE ... SET used = TRUE` binds `password_reset`; unscoped it silently killed the user's in-flight 2FA login. - The one deliberate exception is the admin 2FA reset in `routes/admin.js`, which deletes every unused row for a user regardless of purpose. It is the lockout recovery path and is meant to clear whatever the user is mid-flow on. --- ## Doors into an account Every request is made by a signed-in user acting as themselves, through a session: the bearer token from sign-in or the `__Host-sessionToken` cookie. There is no machine credential. The static service token and the machine JWTs were removed in 0.15.0; a script signs in as a user and sends that session. Which sign-in methods an account may use depends on `AUTH_PROVIDERS`. With local sign-in on, an account linked to an identity provider may use every door it has: its password, its reset, its two-factor setup, and the issuer. **OIDC-only mode makes the issuer the only door** (`AUTH_PROVIDERS=oidc`), and that is the setting for an operator who needs the issuer to govern every account. --- ## Outbound Webhooks Webhooks ([api/webhooks.md](api/webhooks.md)) make the server send a request to an address someone configured, and they carry workspace activity out of the instance. Off unless a subscription exists. | Property | Rule | | --- | --- | | Who configures one | An instance admin (`/api/admin/webhooks`, `requireAdmin` on every route). | | Receiver address | `services/webhook-target.js` checks every address the host resolves to, not only the first, and the IPv4 address inside a mapped, NAT64 or compatible IPv6 one. Link-local and cloud-metadata (`169.254.0.0/16`, `fe80::/10`, and AWS's IPv6 endpoint `fd00:ec2::254`), unspecified and reserved addresses, and the IPv6 transition prefixes that reach an IPv4 host through a relay (6to4 `2002::/16`, Teredo `2001::/32`, local-use NAT64 `64:ff9b:1::/48`), are refused always; loopback and private ranges unless `WEBHOOK_ALLOW_PRIVATE_TARGETS=1`, which is instance-wide. `https` only in production; no user name or password in the URL; at most 2,048 characters once normalised. Checked when a subscription is created; the delivery worker checks again at send time and connects only to the addresses that check approved (`pinnedLookup`), so DNS cannot be rebound in between. A send-time refusal that is not a DNS failure disables the subscription with the same sentence. | | Secrets | Generated by the server (32 random bytes) and returned once; lists and errors show only an 8-hex SHA-256 fingerprint. **An admin subscription's secret is stored in plaintext**: a database dump lets its holder sign events to that one receiver. | | What an event carries | Ids, a document title or archive name cut to 255 code points, a version number, and the actor's user id and name. Never an email, document content, a comment or a membership. | | Tenant scope | An event carries the workspace its resource belongs to. A subscription limited to a workspace gets only that workspace's events, and the database re-checks the match when the delivery is written. | --- ## HTML Sanitization **DOMPurify** is applied at three points: on server writes, on WebSocket broadcast, and on client rendering. `data:` URIs are restricted to `` tags to prevent script injection via data URIs. --- ## Document Images An image pasted or uploaded into a document is stored as a file named by the first 16 hex digits of its SHA-256 and shown through its `/doc-images/.webp` URL. **Knowing that URL is not access.** `GET /doc-images/:file` serves the image only to the user who uploaded it and to users who can read a document that holds it, through the same archive read check as the document itself; everyone else (anonymous, signed in without access, a missing file, a malformed name) gets one identical empty `404` with `Cache-Control: no-store`. A served image is `Cache-Control: private, max-age=86400`, so no shared cache keeps it, though a browser may keep it for a day, including after its user signs out. Which documents hold an image is the `doc_images` table. The ways into it are checked too: - The upload (`POST /api/doc-images/upload`) needs a `logId` the caller can write, and processes nothing without one. - A reference is recorded against a document only by the write that adds it, and only if that writer supplied the image's bytes or can already see it. So pasting someone else's image URL into a document you can write stores the reference and grants nothing, and a later save, publish or restore by someone who can see the image (an owner fixing a typo, an admin publishing) does not grant it either, because that write did not add it. In the live editor, where every client saves the whole shared document, a reference is credited to the writer whose own edit put it there, not to whoever's client sent the save. - Export (HTML, DOCX, markdown) inlines only the images the exporting user can see, so it cannot be used to read an image file the handler would refuse. `DOC_IMAGES_PUBLIC=1` turns all of this off and serves every image to anyone with its address, which is how every earlier release behaved. It exists for the upgrade window before `npm run backfill:doc-images` has run, and should be unset afterwards. The backfill trusts every reference already stored, so it runs once, before the new release serves anyone, and refuses to run over a table that already has rows unless told `--again`. Avatars stay public by decision. --- ## OAuth Token Encryption GitHub access tokens are encrypted at rest using **AES-256-GCM** with a key derived from `GITHUB_CLIENT_SECRET` via scrypt. OAuth state tokens are single-use and expire after 10 minutes. --- ## OIDC Sign-in: What the Issuer Is Trusted For An instance can sign people in through one OIDC issuer (`OIDC_ISSUER_URL`, for example Keycloak or Authentik). The issuer is trusted for **two facts and nothing else**: - **a stable `sub`**, which, with the issuer's own `iss`, names one person; and - **a verified email**: the address and its `email_verified` come from one source (USERINFO when it returns an address, else the ID token), and must be exactly `email_verified: true`. The issuer does not decide what a person may do here. Which local account a verified identity is, and whether it may be linked, is decided by the local ladder in `services/identity.js`: a linked `(issuer, sub)` signs in, an email match links only when the account has no second factor, and nothing creates an account. OIDC never auto-creates a user. An issuer that omits `email_verified` is refused, because the claim is omitted for an unverified address. The sign-in flow is protected by a signed, ten-minute flow cookie (state, nonce, PKCE verifier and the validated `returnTo`), `__Host-oidcFlow` on https. The `returnTo` is validated at login and again at the callback, by one rule (`services/return-to.js`), and a refused value lands on `/`. Plain `http` is accepted for an issuer only on a loopback host. A change of email at the issuer does not change the local account's address: sign-in is keyed on `(issuer, subject)`, and password reset keeps mailing the address the account holds, so an issuer-side change cannot take over a local account. The ID token is not stored by this release; sign-out ending the issuer session is a later change. ## Security Headers **Helmet** applies one policy, a strict Content Security Policy plus standard security headers (`X-Frame-Options: DENY`, `X-Content-Type-Options`, `Referrer-Policy: no-referrer`, `Strict-Transport-Security` and the others Helmet sets by default), with two scopes (`HELMET_OPTIONS` in `cloudcodex/app.js`): - **In production** (`NODE_ENV=production`, which the Docker image and `npm run start` set) it covers **every response but the two probes**: the single-page app's HTML and built assets, the `/avatars` static files, `/doc-images` responses, and `/api`. It is mounted before those and before the handlers `vite-express` appends at listen time. `GET /healthz` and `GET /readyz` are mounted ahead of it, and of CORS and every limiter, and answer small JSON bodies with `Cache-Control: no-store`. - **In development** it stays on **`/api`**, so the Vite dev server's inline module scripts still load. The policy: | Directive | Value | Why | | --- | --- | --- | | `default-src` | `'self'` | | | `script-src` | `'self'` | No inline or remote script. The built app has none; the PDF export prints its window from the opener rather than writing a script into it, because an `about:blank` popup inherits this policy. | | `style-src` | `'self' 'unsafe-inline'` | Components set inline styles. | | `img-src` | `'self' data: blob: https:` | Documents hold any https image (pasted, or imported from GitHub) and a linked GitHub account's avatar is remote. An image cannot run script. | | `connect-src` | `'self' ws: wss:` | The collab and notification WebSockets. The sockets' own `Origin` check, not this, is what refuses a cross-origin upgrade. | | `font-src` | `'self' data:` | | | `object-src` | `'none'` | | | `frame-ancestors` | `'none'` | Nothing may frame the app (with `X-Frame-Options: DENY` for older browsers). | | `upgrade-insecure-requests` | off | TLS is the reverse proxy's job; an install evaluated over plain `http://` must still load its own assets. | Helmet's defaults `base-uri 'self'`, `form-action 'self'` and `script-src-attr 'none'` also apply. **`Cross-Origin-Opener-Policy` is `same-origin-allow-popups`** rather than Helmet's `same-origin`: the draw.io editor is a popup on `embed.diagrams.net` that answers through `window.opener`, and `same-origin` severs that link for a cross-origin popup. The page is still isolated from any window that opens it. A violation found later is fixed by widening the one directive it needs, with a comment saying why, never by dropping the policy. --- ## Rate Limiting **express-rate-limit** enforces per-endpoint limits: | Scope | Limit | | --- | --- | | Auth endpoints (sign-in, sign-up, password reset, 2FA verification and confirmations, the Google callback, account changes and their email confirmation, the reader check) | 20 requests / 15 min, one bucket per IP | | User search (`GET /api/users/search`) | 60 requests / 15 min | | WebSocket messages | 60 messages / second | "Per IP" is the address that connected, unless that peer is a proxy `TRUST_PROXY` names, in which case it is the client the proxy names in `X-Forwarded-For`. The default names proxies by address and nothing else: loopback and the gateway of the network the production compose files pin (`127.0.0.1/32, ::1/128, 172.29.0.1/32`), which is where a proxy on the host arrives from. So a public client, a LAN, VPN or VPC neighbour, and another container reaching the app over the compose network cannot pick their own key, even behind a proxy that appends the header. That holds while the app port stays on its loopback publish: every process on the host that reaches the published port, and with `APP_BIND` widened every container on the host (another container going to the gateway address, or a container on another Docker network going to the host's address), arrives as the gateway and is trusted. The default bridge's gateway, `172.17.0.1`, is not trusted: an IPv4-only Docker network presents every IPv6 client of an all-interfaces or IPv6 publish as its gateway, which `docker run -p PORT:PORT` does. A hop count, `true`, or a range wider than an IPv4 /8, wider than an IPv6 /16 outside `fc00::/7` and `fe80::/10`, or holding more than an IPv4 /8 of `::ffff:0:0/96` is refused at boot unless `TRUST_PROXY_ALLOW_HOP_COUNT=true`, and an entry not in standard notation always is; a peer the list leaves out that sends the header is logged once, and the resolved list is printed at boot (GHSA-9fmx-frrf-xxmq). The production compose files publish the app port on `127.0.0.1` unless `APP_BIND` says otherwise, and `docker-compose-prod.yml` publishes MySQL on `127.0.0.1` unless `DB_BIND` says otherwise. See [deployment.md](deployment.md#rate-limiters). --- ## WebSocket Hardening - Origin validation on connection upgrade: an upgrade with no `Origin`, or one whose host is not the request's `Host` (another host included), is refused with 403 - Authentication timeout (unauthenticated connections are closed after a short window) - 5 MB message size limit - Per-user connection caps to prevent resource exhaustion --- ## Content Size Limits | Resource | Limit | | --- | --- | | Document content | 2 MB | | Image uploads | 10 MB | --- ## CORS The API allows a request with no `Origin` (a same-origin read, a server-to-server call) and one whose `Origin` is the app's own host, `APP_URL`'s host, or exactly `CORS_ORIGIN`. The `localhost` bypass that is active in development is disabled in production builds. The same rule decides whether a cookie-only write may proceed (see [Cross-site request forgery](#cross-site-request-forgery)). --- ## Input Validation Length limits are enforced on all user-provided strings at the API boundary before any database interaction occurs. --- ## Email Header Injection Prevention All fields used to construct outbound emails (name, subject, address) are sanitized before being passed to Nodemailer to prevent header injection attacks. --- ## The Workspace Tenant Boundary The **workspace is the tenant boundary**. A global permission flag (`create_squad`, `create_archive`, `create_log`) means "this account may create this kind of thing", never "this account may create it anywhere". `createDefaultPermissions` hands every new account all three flags, so any route that accepts a caller-supplied workspace or squad id has to resolve that id against the caller's own workspaces before acting on it. `isWorkspaceMember(user, workspaceId)` and `isSquadWorkspaceMember(user, squadId)` in `routes/helpers/ownership.js` are that check. There is no `workspace_members` table: membership of workspace W is admin, OR `workspaces.owner_id = user`, OR membership of some squad whose `workspace_id` is W. | Enforced at | What it gates | | --- | --- | | `POST /api/workspaces/:workspaceId/squads` | `isWorkspaceMember` before the `create_squad` lookup, on the non-owner path. Refuses with the byte-for-byte not-found body, so the route is not a workspace enumeration oracle. | | `requirePermission(flag)` (`middleware/permissions.js`) | `isSquadWorkspaceMember` on a body-supplied `squad_id`, above the global-flag short circuit | | `POST /api/archives/:id/access` | the grantee, user or squad, has to be inside the archive's workspace. On `add` only: gating `remove` would make pre-existing cross-tenant grants unrevokable. | | `GET /api/users/search` | a non-admin caller sees themselves, users who share a workspace with them, and the owners of those workspaces. Accounts with no squad membership anywhere are added only for a caller who can invite: a squad `owner`/`admin` role, `can_manage_members`, or a workspace of their own. Without that last disjunct an SSO or squad-less-invitation account is invisible to everyone but a platform admin and can never be invited into a squad. | The 7-param `readAccessWhere` / `writeAccessWhere` fragments are not part of this and were not changed. The boundary is a precondition on creating a row or naming a grantee; the fragments answer the separate question of who may reach a row that already exists. Two refusals are deliberate: - An **orphaned workspace** (`owner_id` NULL because the owner account was deleted) is not a public workspace. 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. - An **orphaned squad** (`workspace_id` NULL) has no tenant to test against, so `isSquadWorkspaceMember` answers false for everyone but an admin, and the archive ACL check refuses every grantee on an archive owned by such a squad. Only an archive with **no** squad at all skips that check. All four `INSERT INTO squads` sites set `workspace_id`, so only legacy or hand-edited rows can be in this state. [maps/access-control.md](maps/access-control.md) section 3e carries the full resolution table, the behaviour change that came with the orphaned-squad rule, and the reason the archive-derived squad is deliberately not checked in the middleware. **Between instances**, when several share one MySQL server, the boundary is not in the application at all: it is the MySQL grant. Each instance's app account holds `SELECT`, `INSERT`, `UPDATE` and `DELETE` on its own schema and nothing else, per the recipe in [deployment.md](deployment.md#several-instances-on-one-mysql-server), and `tests/integration/tenancy.test.js` proves that recipe against a live server. The grant keeps another instance's rows out of reach, not its name: every MySQL account can list every schema's name and table names, so the recipe names schemas opaquely. Section 9 of [maps/access-control.md](maps/access-control.md) says what it does and does not cover. --- ## Auditing for Pre-existing Cross-tenant Rows Those checks are **prospective**. They stop new cross-tenant rows; they remove none of the rows created before them, and every such row keeps working afterwards, because each one is an ordinary row that the access fragments resolve correctly. An owner `squad_members` row still satisfies clause 5 of `readAccessWhere`, a planted archive still matches `created_by` in clause 3, and a cross-tenant grant still matches clause 2. **There is no cleanup migration and no `DELETE`, deliberately.** No query can tell an attack row from an install that used these routes exactly as they behaved before the fix. A contractor added to a client workspace, a shared platform squad, an admin who set a workspace up from a personal account: all leave the same rows behind. Deleting squads, archives or grants automatically would destroy legitimate data. The three queries below are read-only. They produce a list, and the disposition of every row on it is the operator's. Run them with `make db-shell`. Each returns nothing on an install that was never used this way. **Run them in order.** A user that query 1 reports has a `squad_members` row in that workspace, so queries 2 and 3 read them as a member and will not report them again. Query 1 is the prerequisite for interpreting the other two. ### 1. Squads whose creator is outside the squad's workspace **This one cannot use the obvious membership test, and getting it wrong produces a false green.** The squad-creation route calls `addSquadOwnerMember` on success, so planting a squad writes a `squad_members` row for the planter against the squad they just planted. A naive "the creator is not a member of this workspace" predicate finds that row, through the very squad under examination, and filters out its own evidence. Two planted squads in one workspace also alibi each other, so excluding the current row alone is not enough either. The predicate below is therefore: the creator is not `workspaces.owner_id`, and holds no `squad_members` row anywhere in that workspace, as of the moment the squad was created, once **every squad in that workspace created by that same user** is excluded. `sm.joined_at <= s.created_at` is the second half of that, and it is not optional. Without it a single membership of any squad the creator did not make, acquired at any time, clears every squad they ever planted in that workspace. Being onboarded properly later is the most likely thing to happen to a planter, so the query would go quiet exactly when the operator most needs it. And because query 2's extra clauses are only applied after reading query 1's output, a silent query 1 hides the planted archives too. `squad_members.joined_at` defaults to the insert time and the invitation-accept `ON DUPLICATE KEY UPDATE` (`squads.js`) does not touch it, so it survives re-invitation. ```sql SELECT s.id AS squad_id, s.name AS squad_name, s.workspace_id AS workspace_id, s.created_by AS creator_id, u.email AS creator_email, s.created_at AS created_at FROM squads s JOIN workspaces w ON w.id = s.workspace_id LEFT JOIN users u ON u.id = s.created_by WHERE s.created_by IS NOT NULL -- the creator is not the workspace owner AND (w.owner_id IS NULL OR w.owner_id <> s.created_by) -- and holds no squad membership in this workspace other than the ones the -- squads they created handed them AND NOT EXISTS ( SELECT 1 FROM squad_members sm JOIN squads other ON other.id = sm.squad_id WHERE other.workspace_id = s.workspace_id AND sm.user_id = s.created_by AND (other.created_by IS NULL OR other.created_by <> s.created_by) AND sm.joined_at <= s.created_at ) -- and is not a platform admin, who may legitimately act in any workspace AND NOT EXISTS ( SELECT 1 FROM users a WHERE a.id = s.created_by AND a.is_admin = TRUE ) ORDER BY s.workspace_id, s.id; ``` Reading the output: - Squads with `workspace_id` NULL are not listed. The `JOIN workspaces` drops them, and correctly: an orphaned squad has no workspace for its creator to be outside of. - A squad in an **orphaned workspace** (`owner_id` NULL) whose creator had no other footing there *is* listed. That is the shape the current rule refuses, and it is worth an operator's eye even though it may predate the rule. - `is_admin` is current state, not state at creation time. An account that was an admin when it created the squad and is not one now will be listed; an account promoted since will not. - Membership is read **as of the squad's creation**, so a squad created by someone with no footing in that workspace stays on the list even after they are legitimately onboarded. That is deliberate: later onboarding does not retroactively authorise the creation. It also means a genuine member who created a squad *before* joining any other squad in that workspace is listed, and is one of the shapes the operator dispositions by hand. ### 2. Archives whose creator is outside the owning squad's workspace Planting an archive writes no `squad_members` row, so this one can use the straightforward membership test. ```sql SELECT p.id AS archive_id, p.name AS archive_name, p.squad_id AS squad_id, s.workspace_id AS workspace_id, p.created_by AS creator_id, u.email AS creator_email, p.created_at AS created_at FROM archives p JOIN squads s ON s.id = p.squad_id JOIN workspaces w ON w.id = s.workspace_id LEFT JOIN users u ON u.id = p.created_by WHERE p.created_by IS NOT NULL AND (w.owner_id IS NULL OR w.owner_id <> p.created_by) AND NOT EXISTS ( SELECT 1 FROM squad_members sm JOIN squads m ON m.id = sm.squad_id WHERE m.workspace_id = s.workspace_id AND sm.user_id = p.created_by ) AND NOT EXISTS ( SELECT 1 FROM users a WHERE a.id = p.created_by AND a.is_admin = TRUE ) ORDER BY s.workspace_id, p.id; ``` `system` archives (the hidden per-PR ones) have `squad_id` NULL, so the `JOIN squads` drops them. They have no workspace either, so there is nothing to compare a creator against. **If query 1 returned rows, this query under-reports.** Someone who planted a squad in a workspace *does* hold a `squad_members` row there, so any archive they also planted in that same workspace passes the membership test above. To see those too, add the same two exclusions query 1 uses: ```sql AND (m.created_by IS NULL OR m.created_by <> p.created_by) AND sm.joined_at <= p.created_at ``` as the last lines inside the `NOT EXISTS` above. Both are needed, for two different alibis. The first drops the memberships the planter's own squads handed them. The second drops memberships acquired *after* the archive was created, so that a planter who is later onboarded legitimately does not thereby clear the archive they planted before it: without it, one `squad_members` row from any squad they did not create hides every archive they planted in that workspace, which is exactly the shape query 1 guards against one level up. Only do this after reading query 1's output, because it also reports archives created by anyone whose only membership of a workspace came from a squad they made themselves, or who created the archive before joining any other squad there. ### 3. ACL grants naming a user or squad outside the archive's workspace The four ACL columns hold JSON arrays of ids (`JSON_ARRAY(...)` on insert, `JSON.stringify`d arrays on update), so `JSON_TABLE` expands them into rows. Writing an ACL grant creates no `squad_members` row, so the membership test is straightforward here as well. **This query under-reports one shape, deliberately, and an empty result is not proof of none.** A grantee who holds any `squad_members` row in the archive's workspace clears the user branch, so a grant naming someone who planted a squad there, or who was onboarded legitimately afterwards, is not listed. There is no "as of" time to test against: an ACL grant carries no timestamp of its own. Query 1 covers only the first half of that. It names users whose footing in a workspace came from a squad they created themselves, so the follow-up below is worth running for each user query 1 reports, and only for those users: ```sql -- substitute the user id query 1 reported for SELECT id, name, squad_id, read_access, write_access FROM archives WHERE JSON_CONTAINS(read_access, CAST( AS JSON)) OR JSON_CONTAINS(write_access, CAST( AS JSON)); ``` **The other half is not recoverable by any of these three queries.** A grant to someone who was onboarded into the archive's workspace legitimately *after* the grant was written is invisible to all of them: query 3 skips them because they now hold a `squad_members` row there, query 1 never names them because they created nothing, query 2 is about archives rather than grants, and the `JSON_CONTAINS` follow-up above only runs for the user ids query 1 named. An ACL grant carries no timestamp, so there is nothing to compare a `joined_at` against, and no rewrite of these queries fixes it. **An empty query 3 therefore bounds nothing on its own.** If you need certainty for a particular account, read its grants directly with the `JSON_CONTAINS` query above, substituting that account's id whether or not query 1 reported it. ```sql WITH acl AS ( SELECT p.id AS archive_id, s.workspace_id AS workspace_id, 'read_access' AS acl_column, 'user' AS grantee_kind, g.grantee_id AS grantee_id FROM archives p, squads s, JSON_TABLE(p.read_access, '$[*]' COLUMNS (grantee_id INT PATH '$')) AS g WHERE s.id = p.squad_id UNION ALL SELECT p.id, s.workspace_id, 'write_access', 'user', g.grantee_id FROM archives p, squads s, JSON_TABLE(p.write_access, '$[*]' COLUMNS (grantee_id INT PATH '$')) AS g WHERE s.id = p.squad_id UNION ALL SELECT p.id, s.workspace_id, 'read_access_squads', 'squad', g.grantee_id FROM archives p, squads s, JSON_TABLE(p.read_access_squads, '$[*]' COLUMNS (grantee_id INT PATH '$')) AS g WHERE s.id = p.squad_id UNION ALL SELECT p.id, s.workspace_id, 'write_access_squads', 'squad', g.grantee_id FROM archives p, squads s, JSON_TABLE(p.write_access_squads, '$[*]' COLUMNS (grantee_id INT PATH '$')) AS g WHERE s.id = p.squad_id ) SELECT a.archive_id, p.name AS archive_name, a.workspace_id, a.acl_column, a.grantee_kind, a.grantee_id, COALESCE(u.email, t.name) AS grantee_label FROM acl a JOIN archives p ON p.id = a.archive_id LEFT JOIN users u ON a.grantee_kind = 'user' AND u.id = a.grantee_id LEFT JOIN squads t ON a.grantee_kind = 'squad' AND t.id = a.grantee_id WHERE a.workspace_id IS NOT NULL AND ( ( a.grantee_kind = 'squad' AND NOT EXISTS ( SELECT 1 FROM squads gs WHERE gs.id = a.grantee_id AND gs.workspace_id = a.workspace_id ) ) OR ( a.grantee_kind = 'user' AND NOT EXISTS ( SELECT 1 FROM workspaces gw WHERE gw.id = a.workspace_id AND gw.owner_id = a.grantee_id ) AND NOT EXISTS ( SELECT 1 FROM squad_members sm JOIN squads ms ON ms.id = sm.squad_id WHERE ms.workspace_id = a.workspace_id AND sm.user_id = a.grantee_id ) AND NOT EXISTS ( SELECT 1 FROM users au WHERE au.id = a.grantee_id AND au.is_admin = TRUE ) ) ) ORDER BY a.workspace_id, a.archive_id, a.acl_column, a.grantee_id; ``` A `grantee_label` of NULL means the grant names an id that no longer exists. Those are harmless (nothing matches them) but they are worth removing through `POST /api/archives/:id/access` with `action: 'remove'`, which stays open to cross-tenant grantees precisely so pre-existing grants can be revoked. **Use that API call for them, not the UI.** `GET /api/archives/:id/access` resolves each grantee id through a `users` / `squads` lookup and drops the misses, so a grant naming a deleted account has no row in the modal at all. In the UI, open **Manage Archive Access** on the archive: every explicit user and squad grant *whose grantee still exists* is listed with a Revoke control, sourced from `GET /api/archives/:id/access` rather than from the user search, which is workspace-scoped and so cannot find a grantee outside the tenant. Rows inherited from the owning squad are shown without a Revoke control, because they hold no ACL row to remove. **Read the row before trusting the word "revoke".** A grantee can hold the archive by a second route the ACL entry does not own: membership of the owning squad or of a granted squad (clauses 5 and 6 of the access fragments), or the workspace-wide flag (clause 7). Removing the ACL row leaves all of those intact. Such a row is marked `also inherited from ...`, its control reads **Remove Grant** rather than Revoke, and the confirmation and the toast say the grant was removed rather than that access was revoked. To actually take that user's access away, change the route named on the row: their squad membership or its `can_read` / `can_write` flags, the squad grant, or the workspace-wide flag on the Workspace tab.