# Data Model Map 28 tables in one MySQL 8 schema, InnoDB throughout. `init.sql` is the canonical definition; `migrations/` is the incremental path for databases that already exist. Both must be kept in sync, and there is a live trap in how `init.sql` is re-applied. For the human-readable column-by-column reference see `docs/database.md`. This map covers the parts that change how you write code. --- ## 1. The hierarchy ``` workspaces └── squads (workspace_id, nullable) └── archives (squad_id, nullable) ◄── THE ACL BOUNDARY └── logs (archive_id) ├── versions ├── comments ── comment_replies ├── favorites ├── github_links (1:1) ├── github_embed_refs └── github_pr_sessions ``` Every nullable parent key is load-bearing: - `squads.workspace_id` nullable, and `archives.squad_id` is `ON DELETE SET NULL` (`init.sql:264`). Deleting a squad **orphans** its archives rather than cascading. An orphaned archive has no squad, so clauses 4 through 7 of the access fragments all evaluate false and only the creator, an explicit grant, or an admin can reach it. See [access-control.md](access-control.md). - `archives.squad_id NULL` is also how the GitHub PR-session system archive is built deliberately (`github.js:1648-1656`), one archive per PR. - `logs.archive_id` is `ON DELETE CASCADE` (`init.sql:303`), so deleting an archive destroys its documents, versions, comments and favourites. `workspaces.owner_id` is an INT referencing `users(id) ON DELETE SET NULL`. Clause 4 of both access fragments joins on it (`ownership.js:30`). Deleting the owning user leaves the workspace intact but ownerless, which is why the column is nullable. Ownerless does **not** mean locked down: only clause 4 stops matching. Squad membership (clause 5), per-squad grants (clause 6) and the workspace-wide flag (clause 7) are untouched, so the squad still reaches everything in it, and `routes/workspaces.js` still lists the workspace for any squad member, squad creator, or holder of an archive `read_access` grant. It was a `TEXT` column holding an **email address** until `migrations/add_workspace_owner_id.sql`. Under that shape, changing a user's email silently destroyed their workspace ownership, and deleting the user left a dangling string that a later account registering the same address would inherit. The FK is declared as a trailing `ALTER TABLE` in `init.sql` rather than inline, because `workspaces` is created before `users` (the same reason `user_invitations.squad_id` is declared that way). ## 2. The ACL columns Only `archives` carries a working ACL. Six columns, in read/write pairs (`init.sql:257-262`): | Column | Type | Meaning | |---|---|---| | `read_access` / `write_access` | `JSON` array, default `JSON_ARRAY()` | user ids | | `read_access_squads` / `write_access_squads` | `JSON` array | squad ids | | `read_access_workspace` / `write_access_workspace` | `BOOLEAN`, default `FALSE` | workspace-wide flag | `logs.read_access` and `logs.write_access` (`init.sql:300-301`) exist with the same shape and are **read by nothing**. `versions.read_access` (`init.sql:372`) is likewise never consulted. Treat all three as dead columns; see [open-questions.md](open-questions.md). ## 3. `logs`: the document row ```sql html_content MEDIUMTEXT markdown_content MEDIUMTEXT ydoc_state LONGBLOB plain_content MEDIUMTEXT GENERATED ALWAYS AS (REGEXP_REPLACE(html_content, '<[^>]+>', '')) STORED FULLTEXT INDEX ft_logs_search (title, plain_content) ``` `plain_content` is computed by MySQL on every `html_content` write and is the only body text the FULLTEXT index sees (`init.sql:293`, `init.sql:302`). Consequences: - **Never write `plain_content`.** It is generated; an INSERT naming it errors. - **Search sees `html_content` only.** A document whose live state lives in `ydoc_state` but whose HTML has not been re-saved is stale in search. See [documents-and-collab.md](documents-and-collab.md). - The tag-strip is a regex, not a parser, so entities such as `&` survive into the index verbatim. - **All three content columns are `MEDIUMTEXT` (16 MiB)** since 2026-08-09, so the app's own 2 MiB ceiling (`documents.js:29`, `collab.js:56`) is now the real limit. Until then `html_content` and `plain_content` were `TEXT` (64 KiB) and the column was the true ceiling: measured, a 40 KiB save returned 200 and a 70 KiB save returned an opaque 500 with the edit lost, because the shipped image runs `STRICT_TRANS_TABLES`. `plain_content` had to widen with it (stripping tags from prose barely shrinks it), and so did `versions.html_content`, which publish copies the document into. See B2 in [open-questions.md](open-questions.md) and `migrations/widen_log_content.sql`. - `logs.parent_id` self-references with `ON DELETE SET NULL` (`init.sql:304`), giving documents a tree shape rendered by `PageTree.jsx`. Nothing in the schema holds a parent to the child's archive or forbids a cycle; the routes do (see [notifications-and-activity.md](notifications-and-activity.md) on the tree route and `isLogInArchive`). - `logs.version` is an integer counter bumped on publish and restore; the `versions` table holds the snapshots. ### `doc_images`: which documents hold which image (W6-CDX-34) ```sql hash CHAR(16) NOT NULL -- the file name without .webp log_id INT NOT NULL -- FK logs ON DELETE CASCADE uploaded_by INT NULL -- FK users ON DELETE SET NULL PRIMARY KEY (hash, log_id), INDEX idx_doc_images_log (log_id) ``` `init.sql:380`, `migrations/2026-09-27-who-may-see-doc-images.sql`. Image files stay on disk (`public/doc-images/`, content-addressed by the first 16 hex digits of the upload's SHA-256), and a document's HTML names them by URL; this table is the only thing that says which documents hold an image, and the `/doc-images` handler serves an image only through it (who counts is [access-control.md](access-control.md) section 3f). Rows are written three ways: the upload route, with `uploaded_by` set; `recordDocImages` for an `html_content` write, `uploaded_by` set only for images whose bytes that write supplied, and a reference recorded only by the write that adds it; and the one-time `npm run backfill:doc-images`, with `uploaded_by` NULL, from `logs.html_content` and `versions.html_content`. Everything uses `INSERT IGNORE`, so a repeat is free. Nothing deletes a row except the cascade: an image a document stops showing stays readable to that document's readers, and a file no document holds is never removed (both deferred by the spec). **The upgrade gap.** The migration creates the table empty, so on an existing install every image is hidden from its readers until the backfill runs. The backfill trusts every reference already stored, so it belongs right after the migration and before the app starts, and it refuses to run over a table that already has rows unless `--again` or `DOC_IMAGES_PUBLIC=1`; `DOC_IMAGES_PUBLIC=1` bridges the gap when that order is not possible. ## 4. Sessions and auth tables `sessions.id CHAR(64)` is the primary key and holds the **SHA-256 digest of the token**, lowercase hex, never the token itself (`init.sql`, `CREATE TABLE sessions`). The token is minted by `createNewSessionToken` (`mysql_connect.js:154-158`) and handed to the browser; `hashSessionToken` (`services/session-token.js`) is the one definition of what is stored, and every lookup and delete by token binds its output. A hex digest is also 64 characters, so the column did not change. There are **many rows per user by design**, one per sign-in: `generateSessionToken` (`mysql_connect.js:188`) only ever inserts, and the `user_id` index serves the deletes that sign every device out (password reset, update-account). `sessions.auth_provider VARCHAR(16) NOT NULL` records which flow minted the row, with `chk_sessions_auth_provider` restricting it to `local`, `google` and `oidc` (widened by `migrations/2026-10-11-user-identities.sql`, W6-CDX-5). No default, for `purpose`'s reason below: a flow that forgets to name itself fails at insert with error 1364, which `tests/integration/sessions.test.js` pins on a live server along with the CHECK. Both arrived in `migrations/2026-09-27-session-per-sign-in.sql`, which backfilled existing rows to `local` (nothing recorded which flow made them), dropped the default, and hashed every stored id in place with `SHA2(id, 256)` where the id held a character outside `[0-9a-f]` (case-sensitively, the `'c'` flag, because the column's collation ignores case). That condition makes the hash idempotent: a digest is never hashed again. The hash rides in a file that adds a column because `--adopt-fresh-install` refuses any post-baseline file it cannot check against the schema. Expired rows are deleted daily by `pruneExpiredSessions` in `server.js`; nothing else removes them. **Sealed ID token (W6-CDX-6, `migrations/2026-10-13-session-id-token.sql`).** `oidc_id_token TEXT NULL` holds the OIDC ID token, sealed by `services/id-token-seal.js` (AES-256-GCM, the key scrypt-derived from `OIDC_CLIENT_SECRET`), that sign-out sends as `id_token_hint`. It is never the raw token. `chk_sessions_id_token_oidc_only` says a sealed token belongs to an oidc session; nothing requires one, so rows signed in before this migration keep NULL and sign out locally. The column is read by `getSessionOidcIdToken` (`mysql_connect.js:302`) and by the logout route, and it is carried by the update-account rotation. **Rotation columns (W6-CDX-6 review, `migrations/2026-10-14-session-rotation.sql`).** `signed_in_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP` is when the person signed in. A rotation (a password or email change) copies it onto the replacement, and the back-channel bound reads it (`COALESCE(signed_in_at, created_at)`), so a rotated session still answers to the logout that preceded the change. `rotated_from CHAR(64) NULL`, indexed by `idx_sessions_rotated_from`, is the digest of the session the replacement replaced; logout deletes the sessions that name the token it presents. **OIDC session provenance (W6-CDX-5, `migrations/2026-10-11-user-identities.sql`).** Three columns are added to `sessions`. `identity_id INT NULL` is the `user_identities` row an OIDC sign-in used, with `fk_sessions_identity ON DELETE CASCADE`, so deleting an identity ends the sessions signed in through it. `provider_sid VARCHAR(255) NULL`, indexed by `idx_sessions_provider_sid`, is the issuer's sid, which back-channel logout (W6-CDX-6) matches on. `chk_sessions_identity_pairing` keeps the two together in both directions: `(auth_provider = 'oidc') = (identity_id IS NOT NULL)`. The map used to say MySQL refuses a CHECK over a foreign-key column; that is true only for `ON UPDATE CASCADE` and `ON DELETE SET NULL` (error 3823, measured on 8.4.11), so the pairing is both a CHECK and a refusal in `generateSessionToken`, before any SQL. `user_identities` has `UNIQUE (issuer, subject)`, `UNIQUE (user_id, issuer)` (one identity per issuer per user), and a `utf8mb4_bin` subject, because an OIDC `sub` is case-sensitive. An OIDC session's expiry is `OIDC_SESSION_TTL_HOURS` from sign-in, and a rotation keeps the absolute expiry (`getSessionProvenance`, `mysql_connect.js:264`). The ID token is not stored. `password_reset_tokens` is a **five-flow pool**, not a reset table. It also stores the short-lived 2FA handoff token issued during login, the TOTP enrolment `setupToken`, the 2FA-disable `confirmToken`, and the email-change `confirmToken` that `POST /api/update-account` mints for an account with no password. A row in that table is not necessarily a password reset. Which flow minted a row is recorded in `purpose VARCHAR(32) NOT NULL`, with a `CHECK` constraint restricting it to `password_reset`, `two_factor_login`, `totp_setup`, `two_factor_disable`, `email_change`, added by `migrations/2026-09-08-token-purpose.sql` and widened to `email_change` by `migrations/2026-09-25-token-purpose-email-change.sql` (the older file is applied on existing installs and never edited; the newer one drops and re-adds the constraint under the same name in one `ALTER`). **`new_email VARCHAR(255) NULL`** (same newer migration) is the address an `email_change` row confirms, so `POST /api/update-account/confirm-email` applies exactly the address the code was sent for, never one the confirming request carries. `chk_password_reset_tokens_new_email`, `CHECK ((purpose = 'email_change') = (new_email IS NOT NULL))`, holds the two together: an `email_change` row without an address, or any other row with one, is error 3819 at insert (`tests/integration/update-account-sessions.test.js` proves both directions on MySQL 8.4). Because that constraint reads `purpose`, dropping the column now needs the constraint dropped in the same statement (`tests/integration/migrate.test.js` and `pre-runner-state.js` do). Before the purpose column, nothing recorded the flow and **no reader constrained it**, so a token was interchangeable across flows. The sharpest pairing: `POST /api/login` mints the 2FA challenge row and returns that token to the caller in the response body, and `POST /api/reset-password` read the same table by token alone, so the login endpoint's own token was accepted as a password reset token. Impact was a persistent password rewrite plus a full session wipe of the victim, i.e. lockout and an integrity defect; 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, so it was **not** account takeover. Two of the other three readers were lower still, because `/2fa/totp/confirm` and `/2fa/disable/confirm` sit behind `requireAuth` and additionally require `tokenRecord.user_id === req.user.id`. **`/2fa/verify` is neither.** It is unauthenticated, there is no `req.user` to compare against, and its guard checks only `used` and `expires_at`. What actually stops a stray token there is the separate code check that follows: a TOTP validation, or a matching unused row in `two_factor_codes`. Anyone adding a branch to `/2fa/verify` that skips that check is minting a full session for whatever `user_id` the token row names, from an unauthenticated endpoint. Four rules follow, and all four are load-bearing: - The five minters in `routes/auth.js` bind a `TOKEN_PURPOSE` value from `routes/helpers/shared.js`; the five readers all carry `AND purpose = ?`. The email-change minter also invalidates the user's earlier unused `email_change` rows (scoped to that purpose, as forgot-password is), so an older `confirmToken` cannot pair with a newer code and apply its own address. - **No `DEFAULT`, and `VARCHAR` + `CHECK` rather than `ENUM`.** A sixth flow that forgets to name its purpose fails at insert (error 1364) instead of silently minting a password reset token, and a value outside the set fails too (error 3819). `ENUM` cannot deliver that: 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`, which is the exact defect the column exists to close. Measured on `mysql:8` (8.4.8), the image shipped then. - Forgot-password's `UPDATE ... SET used = TRUE WHERE user_id = ?` is scoped to `purpose = 'password_reset'` too. Unscoped, asking for a password reset silently killed the user's in-flight 2FA login, TOTP enrolment or 2FA-disable confirmation. - `routes/admin.js`'s `DELETE FROM password_reset_tokens WHERE user_id = ? AND used = FALSE` stays **purpose-agnostic on purpose**. It is the admin recovery path for a user locked out of 2FA, and it is meant to clear whatever the user is mid-flow on. **The migration fails closed and its order is load-bearing.** Legacy rows cannot be classified after the fact and every token here is short-lived (10 minutes to 1 hour), so the migration deletes them rather than inventing a default; in-flight resets and challenges must be restarted. Because the column is `NOT NULL` with no default and no compose file overrides `sql_mode` (so MySQL 8's `STRICT_TRANS_TABLES` applies), the schema is incompatible with the app in **both** directions: old code against the new schema is error 1364 on all four minters, new code against the old schema is error 1054. Required order is **stop every writer, apply, start the new image** ("every writer", because `docker-compose.yaml` defines only a `database` service and in dev the writer is `npm run dev` on the host). Stopping the writers also closes a partial-failure race where a row inserted between the `DELETE` and the `MODIFY` leaves the column a nullable `VARCHAR(32)`, since MySQL implicitly commits DDL. There is no rollback: reverting the app lands you in old-code-against-new-schema and getting back means dropping the column by hand. See the migration header and `docs/deployment.md`. `two_factor_codes` holds 6-digit email OTPs; TOTP secrets live on `users.totp_secret` instead. `users.two_factor_method` is `ENUM('none','email','totp')`, nullable with a `'none'` default. Two readers decide on it: `POST /api/login` demands a code for `email` or `totp`, and `resolveIdentity`'s Google link-by-email rung refuses to link a user with either as `two_factor_enabled` and writes no `oauth_accounts` row; its link is an `INSERT ... SELECT` from `users` that copies the row `FOR SHARE`, only while the column is NULL or `'none'` and the row still holds the looked-up email, so the test is made again at insert time at any isolation level. A Google identity already linked is not asked, even after the user turns two-factor on (see [request-lifecycle.md](request-lifecycle.md), the resolution seam). `user_invitations` is what makes signup invite-only. The `users` table is only ever written with a valid invite token, an admin action, or an OAuth flow against a configured provider. Beyond `email`/`token`/`invited_by`, it also carries `squad_id` (nullable), `role`, and the same seven `can_read` through `can_publish` permission booleans as `squad_members`, mirroring `squad_invitations`'s vocabulary rather than inventing a second one. An invitation created with a squad attached (`POST /api/admin/invitations` with `squadId`) makes `POST /api/create-account` insert a `squad_members` row for the new account, inside the same transaction as the account and permissions rows, using `addSquadMember` in `routes/helpers/shared.js`. **This is a second way a `squad_members` row gets created**, alongside accepting a `squad_invitations` row, and the only one that requires no separate accept step: membership lands atomically with the account itself. `user_invitations.squad_id`'s foreign key to `squads(id)` is a trailing `ALTER TABLE` at the end of `init.sql`, **not** part of the inline `CREATE TABLE user_invitations` block. `init.sql` declares `user_invitations` before `squads`, so an inline FK there would reference a table that does not exist yet and abort the whole file on a fresh volume; the constraint is added right after `CREATE TABLE squads` instead. `migrations/add_first_run.sql` does not have this ordering problem, since any database old enough to need the migration already has a `squads` table, so it adds the FK constraint inline in the same `ALTER TABLE` that adds the other eight columns. Reviewers have misread the split placement as inconsistency once already; it is a fresh-install ordering constraint, not a mistake. `users.onboarded_at` (`TIMESTAMP NULL`, default `NULL`) marks that a user has completed the first-run welcome. It is `NULL` for every user that predates it, so each sees the welcome once, including the admin, who previously never saw an onboarding flow at all. `routes/first-run.js` is the only writer. `users.deactivated_at` (`TIMESTAMP NULL`, default `NULL`, `migrations/2026-10-12-user-deactivation.sql`, W6-CDX-8) is set by hand until the sync and an admin control arrive. `NULL` is an active user. A set value is refused by every sign-in: `validateAndAutoLogin` reads the user `AND deactivated_at IS NULL`, `resolveIdentity` answers `account_deactivated` on each oidc and Google rung before any write, `POST /api/login` and `POST /api/2fa/verify` refuse after the password or the challenge is proven, and the 60-second socket re-check (`services/socket-sweep.js:30`) closes open sockets with no live session. The user keeps their authorship; `DELETE` is not involved. ## 5. Squads and membership `squad_members` (`init.sql:211-227`) is unique on `(squad_id, user_id)` and carries `role ENUM('member','admin','owner')` plus seven permission booleans. Which of those are actually enforced, and where, is tabulated in [access-control.md](access-control.md). Short version: `admin` as a role is inert. (A `squad_permissions` table also existed and was enforced by nothing; it was removed on 2026-08-09.) `squad_invitations` is unique on `(squad_id, invited_user_id, status)` (`init.sql:248`). Because `status` is part of the key, a user can hold one pending, one accepted, and one declined invitation to the same squad simultaneously; re-inviting after a decline works without cleanup. A `squad_members` row now has three writers, not two: `addSquadOwnerMember` (workspace/squad creation), accepting a `squad_invitations` row, and, as of the first-run work, `addSquadMember` called from `POST /api/create-account` when the invitation that created the account carried a `squad_id`. See Section 4 above for the `user_invitations` columns that drive it. ## 6. The five GitHub tables | Table | Key | Written by | Read by | |---|---|---|---| | `user_identities` | unique `(issuer, subject)` and unique `(user_id, issuer)` | `services/identity.js` (the OIDC ladder; never links a user with two-factor on by email, creates a user only from an open invitation), `routes/oidc.js` (`last_login_at` by the ladder) | `resolveIdentity` (the `(issuer, subject)` lookup), the reader check by subject (`routes/workspaces.js`), `sessions.identity_id` | | `oauth_accounts` | unique `(provider, provider_user_id)` and unique `(user_id, provider)` | `services/identity.js` (Google; never links a user with two-factor on by email), `routes/oauth.js` (GitHub) | `resolveIdentity` (Google subject lookup and the one-Google-row check), `getGitHubToken` (`github.js:54`), team sync identity match | | `archive_repos` | unique `(archive_id, repo_full_name)` | `routes/archives.js:765` | bulk import | | `github_links` | **unique `(log_id)`** | link CRUD, import, every sync route | status/pull/push/resolve | | `github_pr_sessions` | unique `(repo_owner, repo_name, pr_number)` | `github.js:1677` | PR session lookup | | `github_embed_refs` | index on `(repo_owner, repo_name, embed_type)` | **nothing** | `/api/logs/by-github-ref` | `github_links` being unique on `log_id` is the reason a document links to at most one file. `oauth_accounts` carries two keys that say different things. `uq_provider_user (provider, provider_user_id)`: one provider account belongs to at most one user. `uq_oauth_user_provider (user_id, provider)`: a user holds at most one account per provider. The second is also checked in `resolveIdentity`, which refuses a user matched by email who already holds another Google subject as `identity_conflict`, and by the GitHub link callback, which updates the caller's GitHub row or inserts one only when there is none. The key is what closes the race those checks leave: the Google ladder answers its `ER_DUP_ENTRY` with the same `identity_conflict`, and the GitHub callback with `/account?github_error=link_conflict` (see [open-questions.md](open-questions.md) C7). It arrived in `migrations/2026-09-25-oauth-one-link-per-provider.sql`, which refuses on an install already holding a double link rather than choosing which row to delete. `github_embed_refs` has no writer anywhere in the codebase; see [github-integration.md](github-integration.md). `oauth_accounts.encrypted_token` holds an AES-256-GCM blob whose key derives from `GITHUB_CLIENT_SECRET`; `token_status ENUM('active','revoked','unknown')` is flipped to `revoked` when GitHub rejects the token. ## 7. Comments `comments` carries **two** anchoring schemes: - Internal: `selection_start` / `selection_end` / `selected_text`, character offsets into the document. - External: `external_kind ENUM('pr_file_line','pr_general','issue_thread')`, `external_ref`, `external_id`, for comments attached to a GitHub PR or issue through the PR-session mechanism (`init.sql:401-403`). `tag` includes `pr_review` alongside the five user-facing tags. `status` is `open`/`resolved`/`dismissed`, with `resolved_by` FK `SET NULL`. `comment_replies` cascade-deletes with the parent comment. ## 8. Activity, watches, notifications `activity_log.id` is `BIGINT` (`init.sql:440`), the only table that expects that volume, and it is pruned at 365 days by `pruneOldActivity` in `server.js`. It has four composite indexes covering the workspace, squad, resource, and user read paths. **It has a foreign key on `user_id` only.** `workspace_id`, `squad_id`, `resource_type` and `resource_id` are unconstrained, which is deliberate for a polymorphic append-only log: deleting a document leaves its activity rows in place, and the read query's access clause simply stops matching them. `watches` is likewise polymorphic, unique on `(user_id, resource_type, resource_id)`, with a FK on `user_id` only. Deleting a document therefore **orphans** its watch rows. They are harmless because `fanOutToWatchers` bails when the log row is gone (`activity.js:190-194`), but they accumulate and nothing prunes them. `notifications` has FKs on both `user_id` (cascade) and `actor_id` (`SET NULL`), plus a covering index for the unread badge (`idx_notifications_user_unread`). ### The webhook outbox (W6-CDX-13, `migrations/2026-09-28-webhooks.sql`) Three tables at the end of `init.sql` (`init.sql:488-539`), written only when a subscription matches an event (`notifications-and-activity.md` section 2a): - **`webhook_subscriptions`**: `url VARCHAR(2048)`, `secret`, `source` (`env` or `admin`, `chk_webhook_subscriptions_source`), `enabled`, `event_types JSON` (`NULL` means every emitted type), `workspace_id` (`NULL` means every workspace), `disabled_reason`, and the per-subscription backoff state `consecutive_failures` and `paused_until`. `chk_webhook_subscriptions_secret` is `(source = 'env') = (secret IS NULL)`: the env row's secret lives only in `WEBHOOK_SECRET`, and an admin row always has one. **An admin row's secret is stored in plaintext**, write-only through the API (deferred by the spec: whoever holds a dump can sign events to that one receiver). `workspace_id` has **no FK on purpose**: the env row names a workspace by number, and deleting that workspace must not delete the subscription. `created_by` is `SET NULL`. - **`webhook_events`**: the outbox. `id BIGINT` is the envelope's `sequence`, `event_uuid CHAR(36)` (unique) its `id`, and `body MEDIUMBLOB` the exact bytes every delivery sends, so a retry is byte-identical. `occurred_at DATETIME(3)` is stored **in UTC** from the Node clock (`utcDatetime` in `services/webhooks.js`), not the session time zone, so compare it with `UTC_TIMESTAMP(3)`, not `NOW(3)`. Indexed on `created_at` for the prune. - **`webhook_sequence_lock`**: one row (`id = 1`), created by `2026-10-10-webhook-sequence-lock.sql`. `emitEvent` takes it `FOR UPDATE` before its insert, so outbox ids are allocated in commit order, and it reads `occurred_at` only after that lock is held, so `occurred_at` is non-decreasing in `id` per instance. It holds no data. - **`webhook_deliveries`**: one row per (subscription, event), unique on the pair; `status` is `pending`, `delivered` or `dead` (`chk_webhook_deliveries_status`), with `attempts`, the lease columns `leased_by` and `lease_expires_at`, and `last_status`, `last_error`, `delivered_at`. `idx_webhook_deliveries_head (subscription_id, status, event_id)` is the head-of-queue read the delivery worker (`services/webhook-worker.js`) makes. Both FKs cascade, so deleting a subscription or pruning an event removes its deliveries. ## 9. `init.sql` versus `migrations/` **The rule:** every schema change lands in *both*. A new column needs a file in `migrations/` for existing databases and an edit to `init.sql` for fresh ones. As of this writing the two are in sync; the p0 and p3 migration columns are all present in `init.sql` (p0: `oauth_accounts` at `init.sql:78-81` and `github_links` at `init.sql:347-350`; p3: `squads` at `init.sql:188-193` and `versions` at `init.sql:367-369`). ### The runner and `schema_migrations` `cloudcodex/scripts/migrate.js`, run as `npm run migrate` from `cloudcodex/`, applies pending files in lexicographic order and records each in `schema_migrations`: | column | type | note | |---|---|---| | `filename` | `VARCHAR(255) PRIMARY KEY` | the file's name, the identity | | `checksum` | `CHAR(64) NOT NULL` | sha256 of the file's bytes when applied | | `applied_ms` | `INT NOT NULL` | wall time the apply took, `0` for a baseline row | | `applied_at` | `TIMESTAMP DEFAULT CURRENT_TIMESTAMP` | | That table is **runner-owned bookkeeping and is deliberately not in `init.sql`**. It is created by `ensureBookkeeping()` outside any transaction. Adding it to `init.sql` would break the runner: a fresh install would arrive with the table present and empty, the runner would read "nothing applied", and it would replay every shipped delta against the schema those deltas are already folded into. That is the one exception to the dual-tracking rule above, which still stands for every table the application itself reads. Bookkeeping is what makes re-running safe: the files are individually idempotent-ish (`CREATE TABLE IF NOT EXISTS`) but the `ALTER TABLE ... ADD COLUMN` files are not, and re-running `p0_github_sync.sql` errors on a duplicate column. The runner never re-runs a recorded file, and a recorded file whose sha256 no longer matches is a hard stop over the whole set before anything is applied, with the message that applied migrations are immutable. Three states, decided in the runner: 1. No `users` table: **refuse.** Run `init.sql` first. The runner never bootstraps a schema, because the shipped files are deltas against `init.sql` and `drop_squad_permissions.sql` sorts ahead of most `add_*.sql` files. 2. `users` present, `schema_migrations` missing: **refuse**, and print both adoption commands. With no bookkeeping the runner cannot tell a fully-migrated database from a partly-migrated one, and either guess is destructive. 3. `schema_migrations` present: apply pending, with the drift guard. Two adoption modes, and they are **not** interchangeable, because the two situations have different correct sets: - `--baseline` records the **closed** list in `LEGACY_BASELINE`, the thirteen files that shipped before the runner existed, and applies none. For an install that predates the runner and was migrated by hand. It is not a scan of `migrations/`: any file added after the runner landed stays pending and is applied by the next ordinary run. A sweep would mark a genuinely-unapplied file as applied and leave the app running against a schema that never got the change. **Never append to that list.** - `--adopt-fresh-install` records **every** file on disk and applies none. For a database `init.sql` has just built. Correct there and only there, because the dual-tracking rule means `init.sql` already contains every migration, so applying any of them is a duplicate-column error. Two guards on that second one, because it is the mode that can bury a migration on purpose. It **refuses once `schema_migrations` holds a row**, which keeps it off a tracked install. And for every file that postdates `LEGACY_BASELINE` it **checks the live schema first**: `schemaClaims()` reads the `CREATE TABLE`, `ALTER TABLE ... ADD COLUMN` and named `ADD [UNIQUE] {KEY|INDEX}` out of the file (comments stripped, because the headers quote the DDL that reverses them), and `assertSchemaAlreadyHas()` asks `information_schema` (`tables`, `columns`, `statistics`) whether each of those is already there, refusing unless it is. A file it can read none of those out of is refused too, with the by-hand `INSERT`. Emptiness cannot be the guard: an install that predates the runner has zero bookkeeping rows **by definition**, which is precisely the population reaching for an adoption flag, and `bootstrapInstance()` seeds a workspace, squad, archive and document on first admin boot, so "no content yet" is not a signal either. The pre-runner thirteen are exempt from the check, because adopting exactly those is what `--baseline` does anyway and because two of them (a `DROP` and a `MODIFY`) add nothing an ADD-shaped check could look for. The mode prints the exact list of files it is about to adopt before adopting them, and both adoption modes write their rows in **one transaction**, so an interrupted run leaves no partial bookkeeping to refuse over later. One honest limit on both: an adopted row records the sha256 of the file **as it is on disk at adoption time**, not of whatever that install actually ran years ago. Drift that predates adoption is therefore invisible to the guard forever. That is inherent to adopting a baseline rather than a defect, but it means the guard's promise is "nothing has changed since adoption", not "this is what ran". The apply phase is serialised by a MySQL advisory lock (`GET_LOCK(CONCAT('cloudcodex_migrate:', DATABASE()), 10)`; for a schema name longer than 45 characters, which would push the name past MySQL's 64, `cloudcodex_migrate#` and the first 40 hex characters of its SHA-256 instead, so every name that fits is the one earlier releases used). Without it two concurrent runs both compute the same pending set, MySQL serialises the DDL, and the loser gets a duplicate-column error that the runner would report as "may be partially migrated" when the database is in fact correct. The lock is connection-scoped, which suits the CLI's single dedicated connection, but the NAME is scoped to the MySQL **server**, not to a database, which is why it carries `DATABASE()`: without that, two Cloud Codex schemas on one server serialise against each other and the loser is told a run is in progress against its own database. `0` (someone holds it) and `NULL` (the attempt errored) get different messages. **MySQL implicitly commits DDL**, so a transaction per file cannot make a migration atomic the way it can on Postgres. The runner opens one anyway for the DML that honours it, and on failure it stops at the failing file and reports that the database **may be partially migrated** rather than promising a rollback it cannot deliver. Take a dump first. One failure is the opposite of partial: `ER_DUP_FIELDNAME`, `ER_TABLE_EXISTS_ERROR` and `ER_DUP_KEYNAME` mean the object is already there, and the run may have changed nothing. The usual cause is a fresh install baselined with `--baseline` instead of `--adopt-fresh-install`, which leaves the newer files pending and then dies on their first `ALTER`, minutes after install, with no dump to restore. The runner names the code, says which flag that database wanted, and prints the `INSERT INTO schema_migrations` that records the file by hand. **A refusal is neither.** A migration that must not run over some data (the first is `2026-09-25-oauth-one-link-per-provider.sql`, over a double link) does it with a guard: MySQL has no conditional error outside a stored program, so the file creates a throwaway procedure that `SIGNAL`s, calls it, and drops it before its DDL. mysql2 reports the SIGNAL as `ER_SIGNAL_EXCEPTION`, and the runner (`describeRefusal`) leads with the guard's own message, says the file stays pending, and does not tell the operator to restore a dump. The guard would outlive a refusal, because `CREATE PROCEDURE` commits on its own, MySQL stops the batch at the SIGNAL before the file's `DROP`, and a routine cannot drop itself (`ER_SP_NO_DROP_SP`), so the runner drops the procedures the file created (`proceduresCreated`), after the `ROLLBACK` and only on a refusal. After any other failure it leaves the schema as the failure left it. Such a file runs only through the runner: the `mysql` client splits it on the semicolons inside the procedure body. ### Trap 1: `init.sql` only runs on a fresh volume The compose files mount it into `/docker-entrypoint-initdb.d/` (`docker-compose.yaml:27`, `docker-compose-prod.yml:24`, `docker-compose-release.yml:36`). The MySQL entrypoint **skips that directory entirely when the data directory is already initialised.** Editing `init.sql` and restarting the container does nothing. Dev volume is the bind mount `./db-data/`; prod is the named volume `db_data`. The same skip turns an unreadable `init.sql` into an empty database: on an SELinux-enforcing host a mount with no relabel fails the first boot with "Permission denied", MySQL restarts on a now non-empty data directory, and the app finds no tables. The prod and release files mount it `:ro,z` (the dev base gets `:Z` from `docker-compose.linux.yml`), and `tests/compose-mounts.test.js` fails if any host bind mount in the prod or release file loses its label. ### Trap 2 (fixed): `make reset-db` used to be an incomplete reset `make reset-db` (`Makefile:19-22`) pipes `init.sql` then `seed.sql` into the running container. `init.sql`'s `DROP TABLE IF EXISTS` list (`init.sql:12-37`) now covers all 28 tables (`doc_images` joined it with its table, and the three webhook tables share one multi-table `DROP` on its first line). It used to omit `github_links`, `activity_log`, `watches` and `notifications`, whose `CREATE TABLE` statements don't use `IF NOT EXISTS`, so `reset-db` failed partway through with a duplicate-table error on a database that already had those four. Fixed by adding them to the DROP block; order doesn't matter since it runs under `SET FOREIGN_KEY_CHECKS = 0`. ## 10. Adding a table or column: checklist 1. Add it to `init.sql` in dependency order, and to the `DROP TABLE IF EXISTS` block at the top if it is a new table. 2. Add a `migrations/-.sql` for existing databases, and do **not** add it to `LEGACY_BASELINE` in `scripts/migrate.js`. Lexicographic order is the apply order; a date prefix sorts new files correctly among themselves and, since digits sort ahead of letters, harmlessly ahead of the already-applied legacy set. 3. Index anything you will filter or join on. Look at `idx_activity_workspace_time` and `idx_notifications_user_unread` for the composite-index style already in use. 4. Decide the FK behaviour deliberately: `CASCADE` for owned children, `SET NULL` for attribution (`created_by`, `resolved_by`), no FK for polymorphic references. 5. If it is user-scoped and holds content, decide how it interacts with the archive ACL. Do not add a per-row ACL column expecting it to work; only `archives` is wired into `ownership.js`. 6. Update `docs/database.md` and the test fixtures. --- ## Related - [access-control.md](access-control.md) for how the ACL columns resolve. - [documents-and-collab.md](documents-and-collab.md) for the three content columns and their divergence. - [build-test-and-ops.md](build-test-and-ops.md) for the Docker volume topologies behind trap 1.