docs/database.md
Rama main · réplica de lectura del repositorio original.
```
╔════════════════════════════════════════════════════════════════════════════╗
║ ║
║ 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.