```
╔════════════════════════════════════════════════════════════════════════════╗
║                                                                            ║
║   DATABASE                                                                 ║
║   MySQL 8 schema: 28 tables modelling the workspace → log hierarchy.       ║
║                                                                            ║
╚════════════════════════════════════════════════════════════════════════════╝
```

# Database

Cloud Codex uses **MySQL 8** running inside Docker. The schema is initialized
via [`init.sql`](../init.sql), with incremental upgrades for live deployments
in [`migrations/`](../migrations/). Optional seed data is in
[`seed.sql`](../seed.sql).

## Connection

```bash
docker compose up -d
mysql -h 127.0.0.1 -P 3306 -u <DB_USER> -p <DB_NAME>
```

See `.env.example` for the required environment variables (`DB_HOST`, `DB_USER`, `DB_PASS`, `DB_NAME`).

---

## Schema Overview

The database models a **workspace → squad → archive → log** hierarchy with layered access control at every level.

```
   workspaces
     └── squads
           ├── squad_members
           ├── squad_invitations
           └── archives
                 ├── archive_repos          (GitHub bulk-import link)
                 └── logs
                       ├── versions
                       ├── doc_images       (which images it holds)
                       ├── comments
                       │     └── comment_replies
                       ├── github_links     (per-doc file sync)
                       ├── github_embed_refs
                       ├── github_pr_sessions
                       └── favorites

   users  ──< sessions / oauth_accounts / permissions
          ──< user_invitations / password_reset_tokens / two_factor_codes
          ──< notifications  ──┐
          ──< watches  ────────┤  fan-out paths from
                               └─ activity_log ── webhook_events (outbox)

   webhook_subscriptions ──< webhook_deliveries >── webhook_events
```

---

## Tables

### `workspaces`

Top-level organizational container, owned by a single user. Workspaces can only be created by admins.

| Column       | Type                    | Notes                              |
|--------------|-------------------------|------------------------------------|
| `id`         | INT AUTO_INCREMENT PK   |                                    |
| `name`       | TEXT NOT NULL           | Display name                       |
| `owner_id`   | INT NULL                | FK → `users(id)` ON DELETE SET NULL. Nullable so deleting a user leaves the workspace ownerless rather than deleting it |
| `created_at` | TIMESTAMP               | Defaults to current timestamp      |

**Relationships:** Has many `squads`.

---

### `users`

Registered user accounts. User creation is invite-only (see `user_invitations`).

| Column               | Type                                 | Notes                                        |
|----------------------|--------------------------------------|----------------------------------------------|
| `id`                 | INT AUTO_INCREMENT PK                |                                              |
| `name`               | VARCHAR(32) UNIQUE NOT NULL          | Username; alphanumeric + underscores only    |
| `email`              | VARCHAR(255) UNIQUE NOT NULL         |                                              |
| `password_hash`      | TEXT                                 | bcrypt hash; NULL for OAuth-only accounts    |
| `avatar_url`         | VARCHAR(512)                         | Relative path to uploaded avatar             |
| `two_factor_method`  | ENUM('none', 'email', 'totp')        | Defaults to `'none'`                         |
| `totp_secret`        | VARCHAR(64)                          | TOTP shared secret (only set when TOTP is enabled) |
| `is_admin`           | BOOLEAN                              | Super-admin flag; synced from `.env` on startup |
| `notification_prefs` | JSON                                 | Per-type email opt-in/out (NULL = use defaults from `services/notifications.js`) |
| `created_at`         | TIMESTAMP                            |                                              |

**Relationships:** Has many `sessions`, `oauth_accounts`, `permissions`, `squad_members`, `notifications`, `watches`.

---

### `oauth_accounts`

Links a Cloud Codex user to an external OAuth provider (Google or GitHub).

| Column              | Type                          | Notes                                              |
|---------------------|-------------------------------|----------------------------------------------------|
| `id`                | INT AUTO_INCREMENT PK         |                                                    |
| `user_id`           | INT FK → users                | ON DELETE CASCADE                                  |
| `provider`          | ENUM('google', 'github')      |                                                    |
| `provider_user_id`  | VARCHAR(255)                  | ID from the OAuth provider                        |
| `provider_email`    | VARCHAR(255)                  | Email returned by the provider                    |
| `provider_username` | VARCHAR(255)                  | GitHub login; matched by Squad-to-Team sync       |
| `provider_avatar_url` | VARCHAR(500)                | Provider avatar                                   |
| `encrypted_token`   | TEXT                          | AES-256-GCM encrypted access token (GitHub only)  |
| `token_status`      | ENUM('active','revoked','unknown') | `revoked` once GitHub refuses the token      |
| `created_at`        | TIMESTAMP                     |                                                    |

**Unique constraints:**

- `uq_provider_user (provider, provider_user_id)`: a provider account belongs to at most one user.
- `uq_oauth_user_provider (user_id, provider)`: a user holds at most one account per provider.
  Google sign-in also checks this before it links a user matched by email, and answers a second
  Google account with `identity_conflict`; the key is what refuses the second of two sign-ins that
  pass that check at the same instant, and the answer is the same (`docs/maps/open-questions.md`
  C7). Added by `migrations/2026-09-25-oauth-one-link-per-provider.sql`, which refuses to run on
  an install that already holds a double link; the CHANGELOG's Migration section says how to
  resolve one.

---

### `sessions`

Active login sessions, one row per sign-in. Tokens are 64-character cryptographically random strings, and only their SHA-256 digest is stored.

| Column           | Type            | Notes                                  |
|------------------|-----------------|----------------------------------------|
| `id`             | CHAR(64) PK     | SHA-256 of the session token, lowercase hex; never the token itself |
| `user_id`        | INT FK → users  | ON DELETE CASCADE                      |
| `auth_provider`  | VARCHAR(16)     | The flow that minted it: `local` or `google` (CHECK). NOT NULL with no default |
| `ip_address`     | VARCHAR(45)     | IPv4 or IPv6                           |
| `user_agent`     | TEXT            |                                        |
| `created_at`     | TIMESTAMP       |                                        |
| `last_active_at` | TIMESTAMP       | Updated on each authenticated request |
| `expires_at`     | TIMESTAMP       | 7 days after sign-in, never extended   |

**Indexes:** `user_id`, `expires_at`. **Constraints:** `chk_sessions_auth_provider`.

> Every sign-in inserts its own row, so each device has its own session and signing out of one leaves the others signed in. Nothing refreshes a row in place; expired rows are deleted by a daily prune in `server.js`. Added by `migrations/2026-09-27-session-per-sign-in.sql`, which also hashed the rows already stored.

---

### `password_reset_tokens`

Single-use short-lived tokens. Five flows share this table, so it is not only
password resets.

| Column       | Type            | Notes                              |
|--------------|-----------------|------------------------------------|
| `id`         | INT AUTO_INCREMENT PK |                              |
| `user_id`    | INT FK → users  | ON DELETE CASCADE                  |
| `token`      | CHAR(64) UNIQUE | Cryptographically random           |
| `purpose`    | VARCHAR(32) NOT NULL | Which flow minted the row, CHECK-constrained |
| `new_email`  | VARCHAR(255) NULL | The address an `email_change` row confirms; NULL on every other purpose |
| `expires_at` | TIMESTAMP       | 10 minutes to 1 hour by flow       |
| `used`       | BOOLEAN         | Marked TRUE once consumed          |
| `created_at` | TIMESTAMP       |                                    |

`purpose` is one of `password_reset`, `two_factor_login`, `totp_setup`,
`two_factor_disable`, `email_change`, enforced by a `CHECK` constraint and mirrored by
`TOKEN_PURPOSE` in `cloudcodex/routes/helpers/shared.js`. It has **no
`DEFAULT`**, deliberately: a new 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). It is `VARCHAR` plus `CHECK`
rather than `ENUM` for exactly that reason: 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`. Every reader of this table constrains on `purpose`, so a
token minted by one flow is not accepted by another.

`chk_password_reset_tokens_new_email`, `CHECK ((purpose = 'email_change') =
(new_email IS NOT NULL))`, requires an address on every `email_change` row and
refuses one on any other. The email-change confirmation applies exactly that
address, so the code emailed for one address cannot move the account to
another.

---

### `two_factor_codes`

Short-lived 6-digit codes sent by email for the email-based 2FA flow.

| Column       | Type            | Notes                              |
|--------------|-----------------|------------------------------------|
| `id`         | INT AUTO_INCREMENT PK |                              |
| `user_id`    | INT FK → users  | ON DELETE CASCADE                  |
| `code`       | CHAR(6)         | Numeric OTP                        |
| `expires_at` | TIMESTAMP       | Short window (e.g. 10 min)         |
| `used`       | BOOLEAN         | Marked TRUE once verified          |
| `created_at` | TIMESTAMP       |                                    |

---

### `user_invitations`

Admin-issued email invitations required for new account creation.

| Column       | Type                    | Notes                                             |
|--------------|-------------------------|---------------------------------------------------|
| `id`         | INT AUTO_INCREMENT PK   |                                                   |
| `email`      | VARCHAR(255)            | Target email; must match at signup                |
| `token`      | CHAR(64) UNIQUE         | Included in the invitation link                   |
| `invited_by` | INT FK → users          | ON DELETE CASCADE                                 |
| `expires_at` | TIMESTAMP               | 7-day expiry                                      |
| `accepted`   | BOOLEAN                 | Marked TRUE when account is created               |
| `created_at` | TIMESTAMP               |                                                   |

---

### `squads`

A team or group within a workspace. Squads own archives.

| Column         | Type                    | Notes                           |
|----------------|-------------------------|---------------------------------|
| `id`           | INT AUTO_INCREMENT PK   |                                 |
| `workspace_id` | INT FK → workspaces     | ON DELETE CASCADE               |
| `name`         | TEXT NOT NULL           |                                 |
| `created_at`   | TIMESTAMP               |                                 |
| `created_by`   | INT FK → users          | ON DELETE SET NULL              |

**Relationships:** Has many `squad_members` and `archives`.

---

### `permissions`

Global per-user permissions for top-level actions. One row per user.

| Column            | Type            | Notes                                         |
|-------------------|-----------------|-----------------------------------------------|
| `id`              | INT AUTO_INCREMENT PK |                                         |
| `user_id`         | INT FK → users  | ON DELETE CASCADE                             |
| `create_squad`    | BOOLEAN         | Can create squads in any workspace            |
| `create_archive`  | BOOLEAN         | Can create archives in any squad              |
| `create_log`      | BOOLEAN         | Defaults to TRUE — can create documents       |

> Admins bypass all permission checks. Workspace owners also bypass permission checks within their workspace.

---

### `squad_members`

Member roster for a squad with granular per-member permission flags.

| Column                | Type                                  | Notes                                |
|-----------------------|---------------------------------------|--------------------------------------|
| `id`                  | INT AUTO_INCREMENT PK                 |                                      |
| `squad_id`            | INT FK → squads                       | ON DELETE CASCADE                    |
| `user_id`             | INT FK → users                        | ON DELETE CASCADE                    |
| `role`                | ENUM('member', 'admin', 'owner')      | `owner` has implicit full access     |
| `can_read`            | BOOLEAN                               | Can view archives                    |
| `can_write`           | BOOLEAN                               | Can edit documents                   |
| `can_create_log`      | BOOLEAN                               | Can create new documents             |
| `can_create_archive`  | BOOLEAN                               | Can create new archives              |
| `can_manage_members`  | BOOLEAN                               | Can invite/remove squad members      |
| `can_delete_version`  | BOOLEAN                               | Can delete published versions        |
| `can_publish`         | BOOLEAN                               | Can publish new versions             |
| `joined_at`           | TIMESTAMP                             |                                      |

**Unique constraint:** `(squad_id, user_id)`.

---

### `squad_invitations`

Pending/historical invitations to join a squad. Mirrors the permission flags of `squad_members`.

| Column              | Type                                        | Notes                  |
|---------------------|---------------------------------------------|------------------------|
| `id`                | INT AUTO_INCREMENT PK                       |                        |
| `squad_id`          | INT FK → squads                             | ON DELETE CASCADE      |
| `invited_by`        | INT FK → users                              | ON DELETE CASCADE      |
| `invited_user_id`   | INT FK → users                              | ON DELETE CASCADE      |
| `role`              | ENUM('member', 'admin', 'owner')            |                        |
| `can_read` … `can_publish` | BOOLEAN columns (same as squad_members) |               |
| `status`            | ENUM('pending', 'accepted', 'declined')     | Defaults to `'pending'`|
| `created_at`        | TIMESTAMP                                   |                        |
| `responded_at`      | TIMESTAMP NULL                              | Set when user responds |

**Unique constraint:** `(squad_id, invited_user_id, status)` — prevents duplicate pending invites.

---

### `archives`

A collection of related documents. Archives are the primary organizational unit for content, belonging to a squad.

| Column                   | Type              | Notes                                                           |
|--------------------------|-------------------|-----------------------------------------------------------------|
| `id`                     | INT AUTO_INCREMENT PK |                                                             |
| `squad_id`               | INT FK → squads   | ON DELETE SET NULL; NULL for personal/standalone archives       |
| `name`                   | TEXT NOT NULL     |                                                                 |
| `created_at`             | TIMESTAMP         |                                                                 |
| `created_by`             | INT FK → users    | ON DELETE SET NULL                                              |
| `read_access`            | JSON ARRAY        | User IDs who can read                                           |
| `write_access`           | JSON ARRAY        | User IDs who can write                                          |
| `read_access_squads`     | JSON ARRAY        | Squad IDs whose members inherit read access                     |
| `write_access_squads`    | JSON ARRAY        | Squad IDs whose members inherit write access                    |
| `read_access_workspace`  | BOOLEAN           | If TRUE, all members of the parent workspace can read           |
| `write_access_workspace` | BOOLEAN           | If TRUE, all members of the parent workspace can write          |

> Access is layered: direct user grants → squad membership grants → workspace-wide flag. Archive creators and squad/workspace owners always have full access regardless of the JSON arrays. See [Access Control](./access-control.md) for full details.

---

### `archive_repos`

Links a GitHub repository to an archive for the GitHub integration feature.

| Column          | Type               | Notes                           |
|-----------------|--------------------|---------------------------------|
| `id`            | INT AUTO_INCREMENT PK |                              |
| `archive_id`    | INT FK → archives  | ON DELETE CASCADE               |
| `repo_full_name`| VARCHAR(255)       | e.g. `owner/repo-name`          |
| `repo_owner`    | VARCHAR(255)       |                                 |
| `repo_name`     | VARCHAR(255)       |                                 |
| `linked_by`     | INT FK → users     | ON DELETE CASCADE               |
| `linked_at`     | TIMESTAMP          |                                 |

**Unique constraint:** `(archive_id, repo_full_name)`.

---

### `logs`

An individual document (referred to as a "log" in the data model). This is the core content entity.

| Column              | Type                | Notes                                                                 |
|---------------------|---------------------|-----------------------------------------------------------------------|
| `id`                | INT AUTO_INCREMENT PK |                                                                     |
| `archive_id`        | INT FK → archives   | ON DELETE CASCADE                                                     |
| `title`             | TEXT NOT NULL       |                                                                       |
| `html_content`      | MEDIUMTEXT          | Rendered HTML from the editor; sanitized before storage               |
| `markdown_content`  | MEDIUMTEXT          | Optional markdown source; NULL when rich text is the canonical format |
| `ydoc_state`        | LONGBLOB            | Binary Yjs CRDT state for real-time collaboration                     |
| `plain_content`     | MEDIUMTEXT (GENERATED) | Strips HTML tags from `html_content`; stored for full-text search  |
| `parent_id`         | INT FK → logs       | ON DELETE SET NULL; used to nest logs into a tree                     |
| `created_at`        | TIMESTAMP           |                                                                       |
| `created_by`        | INT FK → users      | ON DELETE SET NULL                                                    |
| `updated_at`        | TIMESTAMP           | Auto-updated on every change                                          |
| `updated_by`        | INT FK → users      | ON DELETE SET NULL                                                    |
| `version`           | INT DEFAULT 0       | Incremented each time a version is published                          |
| `read_access`       | JSON ARRAY          | Per-log read grants (user IDs); supplemental to archive-level access  |
| `write_access`      | JSON ARRAY          | Per-log write grants                                                  |

**Full-text index:** `FULLTEXT (title, plain_content)` — used by the search API for fast, relevance-ranked queries.

> `ydoc_state` is written by the collaborative editing service every few seconds while a document is being actively edited. `html_content` is written on explicit save or publish events.

---

### `github_links`

Links a specific GitHub file to a log, enabling live file sync. One link per log.

| Column        | Type               | Notes                                          |
|---------------|--------------------|------------------------------------------------|
| `id`          | INT AUTO_INCREMENT PK |                                             |
| `log_id`      | INT FK → logs      | ON DELETE CASCADE; UNIQUE (one link per log)   |
| `repo_owner`  | VARCHAR(255)       |                                                |
| `repo_name`   | VARCHAR(255)       |                                                |
| `file_path`   | VARCHAR(500)       | Path within the repo (e.g. `docs/guide.md`)   |
| `branch`      | VARCHAR(255)       |                                                |
| `file_sha`    | VARCHAR(64)        | Last known SHA from GitHub; used for edit checks |
| `linked_by`   | INT FK → users     | ON DELETE CASCADE                              |
| `linked_at`   | TIMESTAMP          |                                                |
| `updated_at`  | TIMESTAMP          |                                                |

---

### `versions`

Published version snapshots of a document's HTML content.

| Column         | Type               | Notes                                           |
|----------------|--------------------|-------------------------------------------------|
| `id`           | INT AUTO_INCREMENT PK |                                              |
| `log_id`       | INT FK → logs      | ON DELETE CASCADE                               |
| `version`      | INT NOT NULL       | Monotonically increasing version number         |
| `title`        | VARCHAR(255)       | Optional human-readable version label           |
| `notes`        | TEXT               | Optional release notes (up to 5000 chars)       |
| `html_content` | MEDIUMTEXT         | Snapshot of document HTML at publish time       |
| `created_at`   | TIMESTAMP          |                                                 |
| `created_by`   | INT FK → users     | ON DELETE SET NULL                              |
| `read_access`  | JSON ARRAY         | Future use; currently mirrors the parent log    |

---

### `doc_images`

Which documents hold which stored image. The `/doc-images` handler serves an
image only to its uploader and to users who can read a document named here
(see `docs/security.md`, Document Images).

| Column        | Type             | Notes                                                        |
|---------------|------------------|--------------------------------------------------------------|
| `hash`        | CHAR(16) NOT NULL | The image file's name without `.webp`                       |
| `log_id`      | INT FK → logs    | ON DELETE CASCADE                                            |
| `uploaded_by` | INT FK → users   | ON DELETE SET NULL; set only when this row's writer supplied the image's bytes |
| `created_at`  | TIMESTAMP        |                                                              |

**Primary key:** `(hash, log_id)`. **Index:** `idx_doc_images_log (log_id)`.
Written by the upload route, by a document write that adds a reference (only
from a writer who can see the image), and once by `npm run backfill:doc-images`
on an install upgraded to it.

---

### `comments`

Threaded annotations on a document, optionally anchored to a text selection.

| Column           | Type                                                    | Notes                                |
|------------------|---------------------------------------------------------|--------------------------------------|
| `id`             | INT AUTO_INCREMENT PK                                   |                                      |
| `log_id`         | INT FK → logs                                           | ON DELETE CASCADE                    |
| `user_id`        | INT FK → users                                          | ON DELETE CASCADE                    |
| `content`        | TEXT NOT NULL                                           | Up to 10,000 characters              |
| `tag`            | ENUM('comment', 'suggestion', 'question', 'issue', 'note') | Defaults to `'comment'`           |
| `status`         | ENUM('open', 'resolved', 'dismissed')                   | Defaults to `'open'`                 |
| `selection_start`| INT                                                     | Character offset anchor start        |
| `selection_end`  | INT                                                     | Character offset anchor end          |
| `selected_text`  | TEXT                                                    | Copy of the selected text (up to 500 chars) |
| `resolved_by`    | INT FK → users                                          | ON DELETE SET NULL                   |
| `resolved_at`    | TIMESTAMP NULL                                          |                                      |
| `created_at`     | TIMESTAMP                                               |                                      |
| `updated_at`     | TIMESTAMP                                               |                                      |

**Indexes:** `(log_id)`, `(log_id, status)`.

---

### `comment_replies`

Replies to a top-level comment. One level of nesting only.

| Column       | Type               | Notes                        |
|--------------|--------------------|------------------------------|
| `id`         | INT AUTO_INCREMENT PK |                           |
| `comment_id` | INT FK → comments  | ON DELETE CASCADE            |
| `user_id`    | INT FK → users     | ON DELETE CASCADE            |
| `content`    | TEXT NOT NULL       |                              |
| `created_at` | TIMESTAMP           |                              |
| `updated_at` | TIMESTAMP           |                              |

**Index:** `(comment_id)`.

---

### `favorites`

Per-user bookmark list for quick access to logs.

| Column       | Type              | Notes                        |
|--------------|-------------------|------------------------------|
| `id`         | INT AUTO_INCREMENT PK |                          |
| `user_id`    | INT FK → users    | ON DELETE CASCADE            |
| `log_id`     | INT FK → logs     | ON DELETE CASCADE            |
| `created_at` | TIMESTAMP         |                              |

**Unique constraint:** `(user_id, log_id)`.

---

### `notifications`

Inbox entries surfaced to a single user. Created by
`services/notifications.js` for every user-facing alert (mention, comment on
my doc, watched-doc activity, squad invite). Self-suppressed and coalesced
within a 60-second window per (user, type, resource).

| Column          | Type                  | Notes                                              |
|-----------------|-----------------------|----------------------------------------------------|
| `id`            | INT AUTO_INCREMENT PK |                                                    |
| `user_id`       | INT FK → users        | ON DELETE CASCADE; recipient                       |
| `type`          | VARCHAR(50)           | e.g. `mention`, `comment_on_my_doc`, `watched_publish` |
| `actor_id`      | INT FK → users        | ON DELETE SET NULL; who caused it (NULL for system)|
| `title`         | VARCHAR(255)          | Sanitized + plain-text                             |
| `body`          | TEXT                  | Sanitized; up to 2000 chars                        |
| `link_url`      | VARCHAR(512)          | Where clicking the notification goes               |
| `resource_type` | VARCHAR(30)           | e.g. `log`, `comment`, `version`, `squad`          |
| `resource_id`   | INT                   | Used for the coalesce key                          |
| `metadata`      | JSON                  | Free-form per-type detail                          |
| `read_at`       | TIMESTAMP NULL        | NULL until marked read                             |
| `created_at`    | TIMESTAMP             |                                                    |

**Indexes:** `(user_id, created_at)`, `(user_id, read_at, created_at)`,
`(resource_type, resource_id)`.

---

### `watches`

Subscription rows linking a user to a log or archive. Activity events on a
watched resource fan out to per-user `notifications` rows. Created manually
from the document UI or automatically (e.g. when a user is mentioned).

| Column          | Type                  | Notes                                  |
|-----------------|-----------------------|----------------------------------------|
| `id`            | INT AUTO_INCREMENT PK |                                        |
| `user_id`       | INT FK → users        | ON DELETE CASCADE                      |
| `resource_type` | VARCHAR(30)           | `'log'` or `'archive'`                 |
| `resource_id`   | INT                   |                                        |
| `source`        | VARCHAR(20)           | `'manual'` or `'auto'` (e.g. `'mention'`) |
| `created_at`    | TIMESTAMP             |                                        |

**Unique constraint:** `(user_id, resource_type, resource_id)` — one row
per (user, resource).
**Index:** `(resource_type, resource_id)` — fast fan-out lookups.

---

### `activity_log`

Workspace-scoped chronological feed of meaningful events: documents
created/published/restored, comments added/resolved, archive grants
changed, squad memberships moved. Read access is filtered at query time
via `routes/helpers/ownership.js`. Auto-pruned daily by `server.js` —
entries older than 365 days are removed.

| Column          | Type                  | Notes                                       |
|-----------------|-----------------------|---------------------------------------------|
| `id`            | BIGINT AUTO_INCREMENT PK |                                          |
| `workspace_id`  | INT                   | Always present                              |
| `squad_id`      | INT                   | Optional                                    |
| `user_id`       | INT FK → users        | ON DELETE CASCADE; actor                    |
| `action`        | VARCHAR(50)           | e.g. `log.published`, `comment.resolved`    |
| `resource_type` | VARCHAR(30)           | `log`, `archive`, `comment`, `version`, `squad` |
| `resource_id`   | INT                   |                                             |
| `metadata`      | JSON                  | Free-form per-action detail                 |
| `created_at`    | TIMESTAMP             |                                             |

**Indexes:** `(workspace_id, created_at)`, `(squad_id, created_at)`,
`(resource_type, resource_id, created_at)`, `(user_id, created_at)`.

---

### `webhook_subscriptions`

Who receives outbound events ([api/webhooks.md](api/webhooks.md)). Rows are made
by an instance admin through `/api/admin/webhooks` (`source = 'admin'`). The
`source = 'env'` rows that the environment once declared were deleted by the
0.15.0 migration, and the worker never sends a row that is not `admin`.

| Column                 | Type                  | Notes                                       |
|------------------------|-----------------------|---------------------------------------------|
| `id`                   | INT AUTO_INCREMENT PK |                                             |
| `url`                  | VARCHAR(2048)         | The receiver, checked by the SSRF guard     |
| `secret`               | VARCHAR(255)          | Admin rows only, never returned by the API |
| `source`               | VARCHAR(8)            | `env` (legacy, unsent) or `admin` (CHECK)   |
| `enabled`              | BOOLEAN               | Default TRUE                                |
| `event_types`          | JSON                  | `NULL` = every emitted type, else an array  |
| `workspace_id`         | INT                   | `NULL` = every workspace. No FK, on purpose |
| `disabled_reason`      | VARCHAR(255)          | Why it is off, for the admin console        |
| `consecutive_failures` | INT                   | Backoff state for the delivery worker       |
| `paused_until`         | DATETIME(3)           | Backoff state for the delivery worker       |
| `created_by`           | INT FK → users        | ON DELETE SET NULL                          |
| `created_at`           | TIMESTAMP             |                                             |

**Constraints:** `chk_webhook_subscriptions_source`, and
`chk_webhook_subscriptions_secret`, `(source = 'env') = (secret IS NULL)`.

### `webhook_events`

The outbox: one row per emitted event, written only when a subscription
matches it. `body` is the exact bytes every delivery of it sends.

| Column        | Type                     | Notes                                      |
|---------------|--------------------------|--------------------------------------------|
| `id`          | BIGINT AUTO_INCREMENT PK | The envelope's `sequence`                  |
| `event_uuid`  | CHAR(36)                 | The envelope's `id`, unique; the idempotency key |
| `type`        | VARCHAR(32)              | One of the eight emitted actions           |
| `workspace_id`| INT                      | The event's workspace                      |
| `occurred_at` | DATETIME(3)              | UTC                                        |
| `body`        | MEDIUMBLOB               | The serialized envelope, UTF-8             |
| `created_at`  | TIMESTAMP                | Indexed                                    |

### `webhook_deliveries`

One row per (subscription, event), worked in event order per subscription by
the delivery worker (`services/webhook-worker.js`). `attempts` counts failed sends.

| Column             | Type                     | Notes                                    |
|--------------------|--------------------------|------------------------------------------|
| `id`               | BIGINT AUTO_INCREMENT PK | Sent as `X-Codex-Delivery`               |
| `subscription_id`  | INT FK → webhook_subscriptions | ON DELETE CASCADE                  |
| `event_id`         | BIGINT FK → webhook_events | ON DELETE CASCADE                      |
| `status`           | VARCHAR(12)              | `pending`, `delivered` or `dead` (CHECK) |
| `attempts`         | INT                      |                                          |
| `leased_by`, `lease_expires_at` | CHAR(36), DATETIME(3) | The worker's lease               |
| `last_status`, `last_error` | SMALLINT, VARCHAR(255) | The last attempt's outcome       |
| `delivered_at`     | DATETIME(3)              |                                          |
| `created_at`       | TIMESTAMP                |                                          |

**Indexes:** `UNIQUE (subscription_id, event_id)`,
`(subscription_id, status, event_id)` for the head of each queue.

---

### `github_embed_refs`

Tracks every GitHub embed (code snippet, issue, pull request, file) that
appears inside a log. Each row stores a pinned `ref_value` (and pinned SHA
where applicable) so the embed remains stable even if the source moves.

| Column         | Type                    | Notes                                      |
|----------------|-------------------------|--------------------------------------------|
| `id`           | INT AUTO_INCREMENT PK   |                                            |
| `log_id`       | INT FK → logs           | ON DELETE CASCADE                          |
| `embed_type`   | ENUM('code','issue','pr','file') |                                   |
| `repo_owner`   | VARCHAR(255)            |                                            |
| `repo_name`    | VARCHAR(255)            |                                            |
| `ref_value`    | VARCHAR(500)            | File path / issue number / PR number / SHA |
| `pinned_sha`   | VARCHAR(64)             | Optional commit SHA the embed is pinned to |
| `branch`       | VARCHAR(255)            | Branch the embed was captured from         |
| `last_seen_at` | TIMESTAMP NULL          | Updated when the embed is rendered         |
| `created_at`   | TIMESTAMP               |                                            |

**Indexes:** `(log_id)`, `(repo_owner, repo_name, embed_type)`.

---

### `github_pr_sessions`

A long-lived session per GitHub PR that the team is collaborating on
through Cloud Codex. Backs the `GitHubMergeDialog` UI and links PR-side
conversation back to any documents tied to the changed files.

| Column        | Type                  | Notes                                |
|---------------|-----------------------|--------------------------------------|
| `id`          | INT AUTO_INCREMENT PK |                                      |
| `log_id`      | INT FK → logs         | ON DELETE CASCADE                    |
| `repo_owner`  | VARCHAR(255)          |                                      |
| `repo_name`   | VARCHAR(255)          |                                      |
| `pr_number`   | INT                   |                                      |
| `opened_by`   | INT FK → users        | ON DELETE CASCADE                    |
| `opened_at`   | TIMESTAMP             |                                      |

**Unique constraint:** `(repo_owner, repo_name, pr_number)`.
**Index:** `(log_id)`.

---


### `webhook_sequence_lock`

One row, `id = 1` (`CHECK (id = 1)`), that holds no data. `emitEvent` takes it
`FOR UPDATE` as the first statement of its transaction, so the outbox ids it
allocates after that point are allocated in commit order. Created by
`migrations/2026-10-10-webhook-sequence-lock.sql` and by `init.sql`.

## Entity Relationship Diagram

```
   workspaces ──< squads ──< archives ──< logs ──< versions
                    │            │           │
                    ├──< squad_members (users)
                    ├──< squad_invitations (users)
                    └──< archive_repos

   logs ──< comments ──< comment_replies
        ──< favorites
        ──< github_links              (per-doc file sync)
        ──< github_embed_refs         (code/issue/PR/file embeds)
        ──< github_pr_sessions        (long-lived PR session state)

   users ──< sessions
         ──< oauth_accounts
         ──< permissions
         ──< password_reset_tokens
         ──< two_factor_codes
         ──< user_invitations
         ──< notifications            (per-user inbox)
         ──< watches                  (per-user subscriptions)

   activity_log ─► (workspace, squad, user, resource_type+id)
                    no FK on workspace/squad/resource — pruned daily
```

---

## Migrations

`init.sql` is the canonical schema (used by fresh installs and by tests).
Live deployments apply incremental upgrades from the `migrations/`
directory with `npm run migrate`. The runner records each applied file in
`schema_migrations` (filename, sha256, elapsed ms, timestamp), so re-running is
a no-op and an edited applied file is a hard stop. Every database needs one
adoption command before its first ordinary run: `--adopt-fresh-install` if
`init.sql` just built it, `--baseline` if it predates the runner. See
[deployment.md](deployment.md#upgrades) for which, and for where to run it on
each compose file. The current set:

| File                            | Adds                                                |
|---------------------------------|-----------------------------------------------------|
| `add_github_links.sql`          | `github_links` table                                |
| `add_markdown_content.sql`      | `logs.markdown_content` column                      |
| `add_activity_log.sql`          | `activity_log` table                                |
| `add_watches.sql`               | `watches` table                                     |
| `add_notifications.sql`         | `notifications` table + `users.notification_prefs`  |
| `p0_github_sync.sql`            | GitHub sync state on `github_links`                 |
| `p1_github_embeds.sql`          | `github_embed_refs` table                           |
| `p2_github_collab.sql`          | `github_pr_sessions` table                          |
| `p3_github_polish.sql`          | indexes / column tweaks for the GitHub stack        |
| `add_first_run.sql`             | squad grant columns on `user_invitations` + `users.onboarded_at` |
| `add_workspace_owner_id.sql`    | `workspaces.owner_id` FK, replacing the `owner` email column |
| `drop_squad_permissions.sql`    | drops the unenforced `squad_permissions` table      |
| `widen_log_content.sql`         | `MEDIUMTEXT` for the three document content columns |
| `2026-09-08-token-purpose.sql`  | `password_reset_tokens.purpose` + its `CHECK`       |
| `2026-09-25-oauth-one-link-per-provider.sql` | `UNIQUE (user_id, provider)` on `oauth_accounts` |
| `2026-09-25-token-purpose-email-change.sql` | `password_reset_tokens.new_email`, `email_change` in the purpose `CHECK`, and `chk_password_reset_tokens_new_email` |
| `2026-09-27-session-per-sign-in.sql` | `sessions.auth_provider` + its `CHECK`, and hashes the stored session ids |
| `2026-09-27-who-may-see-doc-images.sql` | `doc_images` table (then run `npm run backfill:doc-images` once) |
| `2026-09-28-webhooks.sql` | `webhook_subscriptions`, `webhook_events` and `webhook_deliveries` tables |
| `2026-10-10-webhook-sequence-lock.sql` | `webhook_sequence_lock`, the one-row table an emit locks so outbox ids are allocated in commit order |

> **Rule:** any column or table added as a migration must also be present
> in `init.sql`. Both must stay in sync — fresh installs and existing
> deployments must converge to the same schema.
>
> `schema_migrations` is the one exception: it is runner-owned bookkeeping and
> is deliberately **not** in `init.sql`. A fresh install that already had the
> table would read "nothing applied" and try to replay every delta above
> against the schema they are already folded into.

---

## Key Design Notes

- **Invite-only signups:** New users must have a matching `user_invitations` token. No public registration.
- **JSON access arrays:** `read_access` and `write_access` on `archives` store arrays of user IDs. This avoids a separate join table for common cases and keeps access queries compact.
- **Access cascade:** A workspace owner, squad owner, or archive creator always has full access — access arrays are supplemental grants, not the only mechanism. See the [access control doc](./access-control.md) for full resolution order.
- **Generated column for search:** `plain_content` on `logs` is a STORED generated column that strips HTML. The `FULLTEXT` index runs on this column plus `title`, enabling boolean mode relevance-ranked search without a separate search service.
- **Dual document state:** A log has `html_content` (human-readable, set on explicit save) and `ydoc_state` (binary Yjs CRDT, written frequently during live editing). When a user opens a document, the Tiptap editor hydrates from `html_content`; ongoing edits propagate as CRDT updates via WebSocket.
- **Soft parent references:** `logs.parent_id` self-references to form a document tree within an archive. Deleting a parent sets children's `parent_id` to NULL (ON DELETE SET NULL) rather than cascading deletes.