# Data model

> Status: built. The schema is `db/migrations/`: `00001_init.sql` and the migrations after it,
> such as `00002_no_administrators.sql`. This document explains the result.

Postgres is the source of truth for everything. Typesense holds a derived index that can be dropped
and rebuilt at any time ([search.md](https://codna.me/docs/search.md)). This document specifies every table, the state
machines that govern them, and — at the end — worked traces showing exactly which rows each of
CoDNA's core scenarios writes.

## Conventions

- **Primary keys** are `uuid` generated application-side as **UUIDv7**. They sort by creation time,
  which keeps B-tree inserts local and makes "most recent" queries an index scan rather than a sort.
- **Timestamps** are `timestamptz`, always UTC. Every table has `created_at`; mutable tables also
  have `updated_at`.
- **Enums vs text.** Postgres enums are used where the set of values is closed by design (`space`,
  `permission`, `link_state`). Plain `text` with a `CHECK` is used where the set is expected to grow
  (`event_type`, `notification_type`, `grant_reason`) — adding a value there is a one-line migration,
  whereas `ALTER TYPE` cannot remove one and complicates rollback.
- **Soft deletes** apply only to `documents`. Everything else is either immutable history or hard-deleted.
- **`sqlc`** generates Go types from `db/queries/*.sql`; nothing in this schema depends on an ORM's
  conventions.

```sql
CREATE TYPE space           AS ENUM ('raw', 'wiki', 'output');
CREATE TYPE permission      AS ENUM ('read', 'write');
CREATE TYPE principal_type  AS ENUM ('user', 'group');
CREATE TYPE document_status AS ENUM ('draft', 'active', 'archived');
CREATE TYPE link_state      AS ENUM ('resolved', 'stale_text', 'pending', 'broken');
CREATE TYPE decision_status AS ENUM ('pending', 'accepted', 'rejected');
CREATE TYPE automation_kind AS ENUM ('event', 'schedule');
CREATE TYPE run_status      AS ENUM ('ok', 'failed', 'timeout', 'rejected');
```

---

## Identity

```sql
CREATE TABLE users (
  id            uuid PRIMARY KEY,
  email         text        NOT NULL,
  name          text        NOT NULL,
  kind          text        NOT NULL DEFAULT 'person',  -- person | management | agent
  is_active     boolean     NOT NULL DEFAULT true,
  password_hash text,                                   -- argon2id; null: identity provider only
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT users_kind_ck CHECK (kind IN ('person', 'management', 'agent'))
);
CREATE UNIQUE INDEX users_email_key      ON users (lower(email));
CREATE UNIQUE INDEX users_one_management ON users (kind) WHERE kind = 'management';
```

A unique index on `lower(email)` rather than the `citext` extension — one less extension for a
self-hoster to install, and the behaviour is identical for this use.

**Three kinds of user.** A *person* signs in, and reads and writes the knowledge base. *Management*
runs the instance and never reads what a document says
([permissions.md](https://codna.me/docs/permissions.md#management)). An *agent* connects over MCP as itself and knows
what its responsible people share with it ([below](#agents)). There is exactly one management row, made by
migration 00002: the fixed id `00000000-0000-0000-0000-000000000001`, the name `Management`, an
empty email and no password, so nothing can sign in as it or link an identity-provider account to
it. Every lookup an outside value can reach — a login, a share recipient, an identity provider's
claim — finds people only.

**There is no role.** `users.role` held `admin` or `member` until there were no administrators
([adr/0013](https://codna.me/docs/adr/0013-no-administrators.md)). Migration 00002 left it in place, unread, so the
previous binary kept working while an upgrade replaced it; migration 00009 drops it and its type.

Sessions (`sessions`) and identity-provider links (`identities`) hang off `users`; how each
resolves to an actor is in [permissions.md](https://codna.me/docs/permissions.md#the-actor).

### Agents

```sql
CREATE TABLE agents (
  user_id     uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  slug        text NOT NULL,                 -- agent:<slug> in a share
  description text NOT NULL DEFAULT '',
  created_at  timestamptz NOT NULL DEFAULT now(),
  deleted_at  timestamptz,
  CONSTRAINT agents_slug_ck CHECK (slug ~ '^[a-z0-9][a-z0-9-]{0,38}[a-z0-9]$')
);
CREATE UNIQUE INDEX agents_slug_key ON agents (slug) WHERE deleted_at IS NULL;

CREATE TABLE agent_responsible (
  agent_id uuid NOT NULL REFERENCES agents(user_id) ON DELETE CASCADE,
  user_id  uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  added_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (agent_id, user_id)
);
```

An agent is a `users` row with `kind = 'agent'` ([adr/0021](https://codna.me/docs/adr/0021-agents-are-members.md)), so
grants, revisions and usage name it like anyone else. Its email is `<id>@agents.invalid`, an
address nothing can be sent to. Its one `identities` row is its service account, written when
management makes it and never on first sight. A deleted agent keeps its rows, without its identity,
and its slug is free again. `agent_responsible` is who answers for it, and so who may share with
it.

```sql
CREATE TABLE agent_reads (            -- what it opened: a row per page per day
  agent_id uuid, document_id uuid, day date, count bigint, last_at timestamptz,
  PRIMARY KEY (agent_id, document_id, day)
);
CREATE TABLE agent_views (            -- who viewed as it: a row per person per hour
  agent_id uuid, viewer_id uuid, hour timestamptz, requests bigint, last_at timestamptz,
  PRIMARY KEY (agent_id, viewer_id, hour)
);
```

Both feed the agent's page and are kept 90 days. Neither holds what the agent was asked.

```sql
CREATE TABLE groups (
  id          uuid PRIMARY KEY,
  slug        text NOT NULL,
  name        text NOT NULL,
  description text,
  kind        text NOT NULL DEFAULT 'custom',   -- custom | everyone
  created_by  uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT groups_kind_ck CHECK (kind IN ('custom', 'everyone'))
);
CREATE UNIQUE INDEX groups_slug_key     ON groups (slug);
CREATE UNIQUE INDEX groups_one_everyone ON groups (kind) WHERE kind = 'everyone';
-- and a BEFORE DELETE trigger, groups_keep_everyone, that refuses to delete the everyone row

CREATE TABLE group_members (
  group_id uuid NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
  user_id  uuid NOT NULL REFERENCES users(id)  ON DELETE CASCADE,
  added_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (group_id, user_id)
);
CREATE INDEX group_members_user_idx ON group_members (user_id);
```

**Everyone has no rows in `group_members`.** It is the one `kind = 'everyone'` group, made by
00002 with the fixed id `00000000-0000-0000-0000-000000000002` and the slug `everyone`. The queries
that load a person's groups add it to every person, so someone who joins later is in it from their
first request and nothing has to keep it up to date. The trigger is there because deleting a group
deletes every grant made to it, and the previous binary does not know Everyone is special.

**The tables existed before anything managed groups, on purpose.** The search index stores ACL
entries as `user:<id>` and `group:<id>` strings, and introducing the `group:` form later would have
meant reindexing every document in the knowledge base. Groups are now changed by management only
([permissions.md](https://codna.me/docs/permissions.md#groups-and-everyone)).

### Service tokens and the management audit

```sql
CREATE TABLE service_tokens (
  id           uuid PRIMARY KEY,
  user_id      uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,  -- management
  name         text NOT NULL,
  token_hash   text NOT NULL UNIQUE,   -- SHA-256 of the token, never the token
  prefix       text NOT NULL,          -- 'codna_' and six more characters, to tell tokens apart
  scope        text NOT NULL,
  expires_at   timestamptz,
  last_used_at timestamptz,
  revoked_at   timestamptz,
  created_at   timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT service_tokens_scope_ck CHECK (scope = 'manage' OR revoked_at IS NOT NULL)
);
CREATE INDEX service_tokens_user_idx ON service_tokens (user_id);
```

Every live token is a `manage` token that acts as management. Migration 00002 moved existing manage
tokens to the management row and revoked the `admin` and `read` tokens issued before it; the CHECK
keeps any live token a manage token.

```sql
CREATE TABLE management_audit (
  id                 uuid PRIMARY KEY,
  action             text NOT NULL,   -- user.create, group.member_add, token.revoke, …
  via                text NOT NULL,   -- token | cli
  token_id           uuid REFERENCES service_tokens(id) ON DELETE SET NULL,
  target_user_id     uuid REFERENCES users(id) ON DELETE SET NULL,
  target_group_id    uuid,
  target_document_id uuid,
  details            jsonb NOT NULL DEFAULT '{}',
  request_id         text,
  created_at         timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT management_audit_via_ck CHECK (via IN ('token', 'cli'))
);
CREATE INDEX management_audit_created_idx  ON management_audit (created_at DESC);
CREATE INDEX management_audit_user_idx     ON management_audit (target_user_id, created_at DESC)
  WHERE target_user_id IS NOT NULL;
CREATE INDEX management_audit_document_idx ON management_audit (target_document_id, created_at DESC)
  WHERE target_document_id IS NOT NULL;
```

What management did, through what, and to whom — append-only, and never content. Each row is
written in the transaction that makes the change, so a change and its record commit together or not
at all. `action` has no CHECK on purpose: an audit log must never fail to record. Break-glass
(`document.unshare`, `document.quarantine`, `document.release`) fills `target_document_id`; a
transfer (`user.transfer`) names the person who left in `target_user_id`; and every
`codna export --all` on the host adds a row too, `export.all`, with what it wrote.

---

## Documents

```sql
CREATE TABLE documents (
  id                uuid PRIMARY KEY,
  space             space           NOT NULL,
  path              text            NOT NULL DEFAULT '',   -- 'engineering/onboarding', '' = space root
  slug              text            NOT NULL,              -- 'laptop-setup'
  title             text            NOT NULL,
  content           text            NOT NULL DEFAULT '',   -- markdown body, WITHOUT frontmatter
  frontmatter_extra jsonb           NOT NULL DEFAULT '{}', -- keys CoDNA does not own
  tags              text[]          NOT NULL DEFAULT '{}',
  status            document_status NOT NULL DEFAULT 'active',
  version           integer         NOT NULL DEFAULT 1,
  content_hash      bytea           NOT NULL,              -- sha256(title || content || tags)
  created_by        uuid            NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  updated_by        uuid            REFERENCES users(id) ON DELETE SET NULL,
  source_ingestion_id uuid,                                -- set for raw documents
  created_at        timestamptz     NOT NULL DEFAULT now(),
  updated_at        timestamptz     NOT NULL DEFAULT now(),
  deleted_at        timestamptz,                           -- tombstone
  purge_after       timestamptz,                           -- hard-delete eligibility
  quarantined_at    timestamptz                            -- held by management (00004)
);

CREATE UNIQUE INDEX documents_address_key
  ON documents (space, path, slug) WHERE deleted_at IS NULL;
CREATE INDEX documents_space_status_idx ON documents (space, status) WHERE deleted_at IS NULL;
CREATE INDEX documents_created_by_idx   ON documents (created_by);
CREATE INDEX documents_updated_at_idx   ON documents (updated_at DESC);
CREATE INDEX documents_tags_idx         ON documents USING gin (tags);
CREATE INDEX documents_purge_idx        ON documents (purge_after) WHERE deleted_at IS NOT NULL;
CREATE INDEX documents_quarantined_idx  ON documents (quarantined_at)
  WHERE quarantined_at IS NOT NULL;
```

`created_by` is the owner: whoever created the document, until management hands it to someone else
when they leave ([Owner changes](#owner-changes)). `quarantined_at` is set while management holds
the document out of circulation ([Quarantines](#quarantines)).

Two identities, and the distinction matters throughout:

- `id` — permanent, never changes, what links resolve to.
- `(space, path, slug)` — the human address. Mutable. What people type and what export writes to disk.

The unique index is **partial** (`WHERE deleted_at IS NULL`) so a tombstoned document does not block
a new one from taking its address.

`content_hash` exists so a save that changes nothing is detected before a revision is written — agents
re-submitting identical content should not inflate the history you asked to keep readable.

`content` holds the body **without** frontmatter. Frontmatter is a projection of the columns,
reassembled on export; see [export.md](https://codna.me/docs/export.md).

### Folders

There is no folders table. A folder is what the `path` of a document implies — `codna/adr` implies
`codna` as well — and only documents the person asking can read imply anything, so a folder holding
nothing they can read does not exist for them. Two endpoints read folders, with the same folders and
the same counts: the live documents the person can read in a folder and beneath it, drafts and
archived ones included, tombstones never.

- `GET /api/documents/tree?space=wiki&path=codna` — one level: a folder's subfolders, each with its
  count and whether it has subfolders, and the documents directly in it. How the sidebar walks a
  space.
- `GET /api/folders?space=wiki` — every folder of a space at once, for choosing where a document
  goes, so a typo is a folder that is not offered rather than a new one:

  ```json
  {"space": "wiki", "folders": [{"path": "codna", "documents": 24}, {"path": "codna/adr", "documents": 14}, {"path": "codna-old", "documents": 2}]}
  ```

  Each folder comes straight after its parent (`codna/adr` before `codna-old`). At most 5000: past
  that, the folders that sort last are left out, never a folder without its parent.

### Revisions

```sql
CREATE TABLE document_revisions (
  id                uuid PRIMARY KEY,
  document_id       uuid    NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  version           integer NOT NULL,
  title             text    NOT NULL,
  path              text    NOT NULL,
  slug              text    NOT NULL,
  content           text    NOT NULL,
  tags              text[]  NOT NULL DEFAULT '{}',
  frontmatter_extra jsonb   NOT NULL DEFAULT '{}',
  change_kind       text    NOT NULL,   -- create|content|move|rename|tags|status|restore|delete
  change_summary    text,
  author_id         uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at        timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT document_revisions_kind_ck CHECK (change_kind IN
    ('create','content','move','rename','tags','status','restore','delete'))
);
CREATE UNIQUE INDEX document_revisions_version_key ON document_revisions (document_id, version);
CREATE INDEX document_revisions_doc_idx ON document_revisions (document_id, version DESC);
CREATE INDEX document_revisions_created_idx ON document_revisions (created_at);           -- 00011
CREATE INDEX document_revisions_author_idx ON document_revisions (author_id, created_at); -- 00011
```

**Full snapshots, not a diff chain.** Postgres TOAST compresses `text` transparently, so the storage
difference at knowledge-base scale is small, and in exchange: restoring any version is a single row
read, exporting history needs no replay, and a corrupted row cannot invalidate every later version.
Diffs are computed on read with `hexops/gotextdiff` — cheap on documents of this size.

Every change writes a revision, including moves and renames. That is what later answers *"why did
this link go stale?"* — the revision with `change_kind='move'` names the actor and the moment.

The current state in `documents` is always equal to the highest-numbered revision. `documents`
exists as a separate table so the common read — "give me the latest" — never has to sort.

**Revisions are kept forever.** No pruning by age or count, and no configuration to turn it off. A
knowledge base whose history silently thins out is one nobody can audit, and the question history
answers — *who changed this, when, and to what* — is most often asked about the distant past.
Pruning can be added later if a deployment needs it; a history already discarded cannot be recovered.

Note the contrast with the two things that **are** time-bounded: processed outbox events
(`CODNA_EVENT_RETENTION`) and tombstoned documents awaiting purge (`CODNA_PURGE_AFTER`). Neither is
history — one is transport, the other is a deletion already requested.

### Address history

```sql
CREATE TABLE document_path_history (
  id          uuid PRIMARY KEY,
  document_id uuid  NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  space       space NOT NULL,
  path        text  NOT NULL,
  slug        text  NOT NULL,
  valid_from  timestamptz NOT NULL,
  valid_to    timestamptz NOT NULL DEFAULT now(),
  revision_id uuid REFERENCES document_revisions(id) ON DELETE SET NULL
);
CREATE INDEX document_path_history_addr_idx ON document_path_history (space, path, slug);
CREATE INDEX document_path_history_doc_idx  ON document_path_history (document_id);
```

Every address a document has vacated. Two jobs: resolving a link written against an old address, and
letting the UI say *"this link points at `runbooks/deploy.md`, which moved to `ops/deploy.md`"*
instead of just flagging it stale.

### Owner changes

```sql
CREATE TABLE document_owner_changes (
  id          uuid PRIMARY KEY,
  document_id uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  from_user   uuid REFERENCES users(id) ON DELETE SET NULL,
  to_user     uuid REFERENCES users(id) ON DELETE SET NULL,
  transfer_id uuid NOT NULL,             -- shared by every document one transfer moved
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX document_owner_changes_document_idx
  ON document_owner_changes (document_id, created_at DESC);
CREATE INDEX document_owner_changes_transfer_idx ON document_owner_changes (transfer_id);
```

When someone leaves, management hands what they owned to someone who stays
([permissions.md](https://codna.me/docs/permissions.md#when-someone-leaves)), and `documents.created_by` is rewritten.
This table remembers what it was, so a document's history still says who made it and who has it now.
Nothing else about a transferred document changes: not its version, not `updated_at`, not its
grants, and its revisions keep their authors.

### Files

```sql
CREATE TABLE attachments (
  id            uuid PRIMARY KEY,
  document_id   uuid   NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  name          text   NOT NULL,         -- its name in the page's <slug>.files/ folder
  mime_type     text   NOT NULL,         -- sniffed from the bytes at upload
  byte_size     bigint NOT NULL,
  sha256        bytea  NOT NULL,
  storage_key   text   NOT NULL,         -- files/2026/10/<uuid>.png in the storage root
  created_by    uuid   REFERENCES users(id) ON DELETE SET NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),
  referenced_at timestamptz              -- when maintenance first found a revision naming it
);
CREATE UNIQUE INDEX attachments_name_key ON attachments (document_id, name);
CREATE INDEX attachments_sha_idx ON attachments (document_id, sha256);
CREATE INDEX attachments_unreferenced_idx ON attachments (created_at) WHERE referenced_at IS NULL;
```

A page's images and PDFs ([files.md](https://codna.me/docs/files.md)). A file belongs to one document and has its
permission; nothing else grants access to one. Rows are never updated except to set
`referenced_at`: a file never changes. They go when their page is purged, or, if no revision of the
page ever named them, thirty days after upload. Maintenance reads the storage keys before the rows
go and deletes the bytes after.

---

## The link graph

```sql
CREATE TABLE document_links (
  id                  uuid PRIMARY KEY,
  source_id           uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  target_id           uuid REFERENCES documents(id) ON DELETE SET NULL,
  href_as_written     text NOT NULL,     -- '../onboarding/laptop-setup.md'
  resolved_address    text,              -- 'wiki:engineering/onboarding/laptop-setup'
  link_text           text,              -- the [display text]
  occurrence_count    integer NOT NULL DEFAULT 1,
  state               link_state NOT NULL,
  first_seen_revision uuid REFERENCES document_revisions(id) ON DELETE SET NULL,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  kept_at             timestamptz        -- its author kept a stale link as written (00005)
);
CREATE UNIQUE INDEX document_links_edge_key ON document_links (source_id, href_as_written);
CREATE INDEX document_links_target_idx ON document_links (target_id) WHERE target_id IS NOT NULL;
CREATE INDEX document_links_state_idx  ON document_links (state) WHERE state <> 'resolved';
```

Storing **both** the href as written and the resolved `target_id` is the whole trick. The edge is
anchored to the id, so the graph survives any rename; the href is just text that may fall out of
date, and falling out of date is a *fact to surface*, not a failure.

`kept_at` records one answer to that fact: the author kept a stale link as written. While it is set
the link is not listed as a decision. Every change of state clears it (an edit that re-extracts the
same stale link to the same target keeps it), so a link that later breaks is asked about again.
Links whose source is in `raw` are never listed as decisions at all: raw is never edited, so no
answer could be carried out.

### Link states

| state | `target_id` | meaning | surfaced to the user as |
|---|---|---|---|
| `resolved` | set | target exists; href matches its current address | nothing |
| `stale_text` | set | target moved or was renamed; href text is now wrong | "update this link, keep it as written, or drop it?" |
| `pending` | null | href points at an address with no document | nothing yet — resolves itself if one appears |
| `broken` | set | target is tombstoned | "target deleted — repoint, drop, or restore?" |

```
                    target created at that address
   pending ──────────────────────────────────────────▶ resolved
      ▲                                                 │   ▲
      │ target purged                     target moved  │   │ href edited to match
      │                                    or renamed   ▼   │      (or target moved back)
   broken ◀──────────── target deleted ──────────── stale_text
      │                                                 │
      └───────────── target restored ───────────────────┘
```

Transitions are computed by the link resolver, an outbox consumer. Nothing here is a trigger — the
rules live in Go where they can be tested.

Full extraction and resolution rules, including how a relative href becomes an address, are in
[linking.md](https://codna.me/docs/linking.md).

### Link suggestions

```sql
CREATE TABLE link_suggestions (
  id          uuid PRIMARY KEY,
  source_id   uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  target_id   uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  reason      text NOT NULL,   -- vector_similar|tag_overlap|co_citation|agent_proposed
  score       real,
  rationale   text,            -- human-readable: why the agent thinks these belong together
  suggested_by uuid REFERENCES users(id) ON DELETE SET NULL,
  status      decision_status NOT NULL DEFAULT 'pending',
  decided_by  uuid REFERENCES users(id) ON DELETE SET NULL,
  decided_at  timestamptz,
  created_at  timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT link_suggestions_reason_ck CHECK (reason IN
    ('vector_similar','tag_overlap','co_citation','agent_proposed')),
  CONSTRAINT link_suggestions_no_self CHECK (source_id <> target_id)
);
CREATE UNIQUE INDEX link_suggestions_pair_key ON link_suggestions (source_id, target_id);
CREATE INDEX link_suggestions_pending_idx ON link_suggestions (source_id) WHERE status = 'pending';
```

Linking is the agent's job to *propose* and the user's to *decide*. The unique constraint on
`(source_id, target_id)` is what makes the row durable rather than transient: **a rejected
suggestion stays rejected.** Without it an agent would re-propose the same link on every pass, and
users would learn to ignore suggestions entirely.

Accepting a suggestion does not create an edge directly — it edits the source document to contain a
real markdown link, which produces a revision, which the resolver then turns into a `document_links`
row. The body is always the truth about what links to what.

---

## Access

```sql
CREATE TABLE document_access (
  id             uuid PRIMARY KEY,
  document_id    uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  principal_type principal_type NOT NULL,
  principal_id   uuid NOT NULL,          -- users.id or groups.id
  permission     permission NOT NULL DEFAULT 'read',
  granted_by     uuid REFERENCES users(id) ON DELETE SET NULL,
  grant_reason   text NOT NULL DEFAULT 'direct',  -- direct|bulk_tag|link_suggestion|script|import|folder
  batch_id       uuid,                   -- groups one user action across many documents
  created_at     timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT document_access_reason_ck CHECK (grant_reason IN
    ('direct','bulk_tag','link_suggestion','script','import','folder'))
);
CREATE UNIQUE INDEX document_access_key ON document_access (document_id, principal_type, principal_id);
CREATE INDEX document_access_principal_idx ON document_access (principal_type, principal_id);
CREATE INDEX document_access_batch_idx ON document_access (batch_id) WHERE batch_id IS NOT NULL;
```

The complete ACL. The rules that read it:

- **Creator** — their own documents, no rows required.
- **Everyone else** — needs a row here, or a row for a group they belong to. Every person belongs to
  Everyone, so a row for that group reaches them all.
- **Management** — nothing, whatever the rows say. Nobody sees every document
  ([adr/0013](https://codna.me/docs/adr/0013-no-administrators.md)).

`grant_reason` and `batch_id` are what make access explicable. "Why can Dana see this?" has an
answer — *granted by Tornike on 24 Sep, as part of sharing the onboarding guide, because it was
linked from it* — rather than an opaque row. `batch_id` ties the whole multi-document share
together so it can be reviewed or revoked as one action.

### Access events

```sql
CREATE TABLE access_events (
  id             uuid PRIMARY KEY,
  document_id    uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  principal_type principal_type NOT NULL,
  principal_id   uuid NOT NULL,
  permission     permission,
  action         text NOT NULL,          -- granted|revoked|permission_changed
  actor_id       uuid REFERENCES users(id) ON DELETE SET NULL,
  grant_reason   text,
  batch_id       uuid,
  created_at     timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT access_events_action_ck CHECK (action IN ('granted','revoked','permission_changed'))
);
CREATE INDEX access_events_principal_idx ON access_events (principal_type, principal_id, created_at DESC);
CREATE INDEX access_events_document_idx  ON access_events (document_id, created_at DESC);
```

Append-only, never updated or deleted.

**This table exists because `document_access` cannot answer "what was I given access to recently?"**
The ACL is current state: a grant that was revoked and re-granted leaves one row with one timestamp,
and a revoked grant leaves no row at all. History has to live somewhere else, and an agent that
wants to open a conversation with *"three documents were shared with you since we last spoke"* reads
this table, not the ACL.

Shares management takes away are recorded here too, with `grant_reason` `management` (one taken
away) or `quarantine`; shares a release gives back are recorded as granted, with `release`.

### Quarantines

```sql
CREATE TABLE document_quarantines (
  id               uuid PRIMARY KEY,
  document_id      uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  reason           text NOT NULL,          -- management's words, shown to the owner
  saved_grants     jsonb NOT NULL,         -- the shares moved aside, for a release to give back
  quarantined_at   timestamptz NOT NULL DEFAULT now(),
  released_at      timestamptz,
  restored_sharing boolean                 -- whether the release gave them back
);
CREATE UNIQUE INDEX document_quarantines_active ON document_quarantines (document_id)
  WHERE released_at IS NULL;
```

**A quarantine is the grants moved aside, not a rule.** Quarantining sets
`documents.quarantined_at`, deletes the document's rows from `document_access` and keeps them in
`saved_grants`, so the visibility queries and the search filter show the document to its owner alone
without knowing quarantines exist. The policy reads `quarantined_at` for one thing: no share while
it is set ([permissions.md](https://codna.me/docs/permissions.md#break-glass-without-reading)). A release gives the saved
grants back as they were — `granted_by`, `grant_reason`, `batch_id` and `created_at` included —
except to people since deactivated and groups since deleted.

One row per quarantine, kept after release so the history says what was done and why; the partial
unique index allows one open quarantine per document. Migration 00004's Down refuses while any
document is quarantined: dropping the column would put a held document back into circulation,
re-sharable, with the record of why gone.

### Folder shares

```sql
CREATE TABLE folder_shares (
  id             uuid PRIMARY KEY,
  owner_id       uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  space          space NOT NULL,
  path           text NOT NULL,          -- a folder and every folder beneath it; '' is the space
  principal_type principal_type NOT NULL,
  principal_id   uuid NOT NULL,
  permission     permission NOT NULL DEFAULT 'read',
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX folder_shares_key
  ON folder_shares (owner_id, space, path, principal_type, principal_id);
```

A standing rule about the owner's own documents in a folder ([adr/0014](https://codna.me/docs/adr/0014-standing-folder-shares.md)).
It grants nothing itself: each document it reaches gets an ordinary `document_access` row with
`grant_reason = 'folder'`, now for what is there and from the outbox for what arrives later. So the
visibility queries and the search filter know nothing about folder shares, and need not.
Migration 00008's Down turns those grants into `direct` ones before dropping the table: the access
stays, without the rule behind it.

### Declined share suggestions

```sql
CREATE TABLE share_suggestion_decisions (
  id             uuid PRIMARY KEY,
  document_id    uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  principal_type principal_type NOT NULL,
  principal_id   uuid NOT NULL,
  decided_by     uuid REFERENCES users(id) ON DELETE SET NULL,
  status         decision_status NOT NULL DEFAULT 'rejected',
  created_at     timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX share_suggestion_decisions_key
  ON share_suggestion_decisions (document_id, principal_type, principal_id);
```

The same reasoning as `link_suggestions`: a suggestion declined once must not come back every time
the user shares something nearby.

---

## Automations

```sql
CREATE TABLE automations (
  id             uuid PRIMARY KEY,
  name           text NOT NULL,
  description    text,
  kind           automation_kind NOT NULL,
  enabled        boolean NOT NULL DEFAULT true,

  -- kind = 'event'
  trigger_events text[] NOT NULL DEFAULT '{}',
  scope          jsonb  NOT NULL DEFAULT '{}',  -- {space, pathPrefix, tags} cheap pre-filter

  -- kind = 'schedule'
  schedule       text,                          -- cron expression, 5-field
  timezone       text NOT NULL DEFAULT 'UTC',
  next_run_at    timestamptz,
  last_run_at    timestamptz,

  script         text NOT NULL,                 -- JavaScript, usually agent-authored
  script_hash    bytea NOT NULL,
  owner_id       uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT automations_shape_ck CHECK (
    (kind = 'event'    AND cardinality(trigger_events) > 0 AND schedule IS NULL) OR
    (kind = 'schedule' AND schedule IS NOT NULL)
  )
);
CREATE INDEX automations_due_idx ON automations (next_run_at)
  WHERE enabled AND kind = 'schedule';
CREATE INDEX automations_trigger_idx ON automations USING gin (trigger_events)
  WHERE enabled AND kind = 'event';
CREATE INDEX automations_owner_idx ON automations (owner_id);
```

Two kinds, one script contract:

- **`event`** — fires from the outbox. Triggers span document lifecycle, access changes, link-graph
  changes, and ingestion. The `scope` JSONB is a cheap pre-filter evaluated in SQL so the sandbox is
  not started for events the script would immediately ignore.
- **`schedule`** — fires from a cron tick. Has **no subject document**, which is why the capability
  API includes bounded reads and not only actions.

`next_run_at` is computed with `adhocore/gronx` and stored, rather than held by an in-process
scheduler. Due automations are claimed with:

```sql
UPDATE automations SET last_run_at = now(), next_run_at = $2
WHERE id = (
  SELECT id FROM automations
  WHERE enabled AND kind = 'schedule' AND next_run_at <= now()
  ORDER BY next_run_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
RETURNING *;
```

Keeping the schedule in Postgres rather than in memory is what lets CoDNA run more than one replica
without double-firing or dropping ticks.

```sql
CREATE TABLE automation_runs (
  id            uuid PRIMARY KEY,
  automation_id uuid NOT NULL REFERENCES automations(id) ON DELETE CASCADE,
  event_id      uuid,                    -- null for scheduled runs
  status        run_status NOT NULL,
  duration_ms   integer,
  memory_peak   bigint,                  -- bytes, as reported by QuickJS
  limit_hit     text,                    -- null|memory|execution_time|actions|reads
  actions       jsonb NOT NULL DEFAULT '[]',  -- proposed, with applied/rejected verdict each
  reads         integer NOT NULL DEFAULT 0,
  logs          text,
  error         text,
  created_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX automation_runs_automation_idx ON automation_runs (automation_id, created_at DESC);
```

Every run is recorded with the actions it proposed **and whether each was applied or rejected**.
A script that keeps trying to share outside its owner's permissions is visible, attributable, and
switched off — rather than silently failing forever.

Full contract, capability API, and limits in [automations.md](https://codna.me/docs/automations.md).

---

## Plumbing

### The outbox

```sql
CREATE TABLE events (
  id           uuid PRIMARY KEY,
  event_type   text NOT NULL,   -- document.created, access.granted, link.became_broken, …
  subject_type text NOT NULL,   -- document|access|link|ingestion
  subject_id   uuid,
  document_id  uuid REFERENCES documents(id) ON DELETE CASCADE,
  actor_id     uuid REFERENCES users(id) ON DELETE SET NULL,
  revision_id  uuid REFERENCES document_revisions(id) ON DELETE SET NULL,
  payload      jsonb NOT NULL DEFAULT '{}',
  created_at   timestamptz NOT NULL DEFAULT now(),
  processed_at timestamptz,
  attempts     integer NOT NULL DEFAULT 0,
  last_error   text,
  claimed_at   timestamptz,   -- in-flight lease
  claimed_by   text,
  retry_at     timestamptz,   -- a failed event waits until then
  handled_by   text[] NOT NULL DEFAULT '{}'  -- consumers that already succeeded
);
CREATE INDEX events_unprocessed_idx ON events (created_at) WHERE processed_at IS NULL;
CREATE INDEX events_claimed_idx ON events (claimed_at) WHERE processed_at IS NULL;
CREATE INDEX events_document_idx ON events (document_id, created_at DESC);
```

Written in the same transaction as the change it describes. Drained by a worker that wakes on
`LISTEN`/`NOTIFY` and falls back to a periodic poll, so a missed notification delays work rather
than losing it.

**`SKIP LOCKED` alone is not a queue.** It prevents two workers colliding on a row *during* the
claiming statement, but that statement commits immediately — and the row stays unprocessed for as
long as the consumer takes to handle it. In that window every other worker will claim it again.
This was caught by a test that ran six concurrent claimers and found single events claimed five
times over.

The `claimed_at` lease closes the window: a claimed row is invisible to other workers until its
lease expires. An expired lease means the worker holding it died, so the event returns to the pool
— which is exactly what makes delivery at-least-once rather than at-most-once.

Consumers must therefore be idempotent.

### Notifications

```sql
CREATE TABLE notifications (
  id               uuid PRIMARY KEY,
  recipient_id     uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type             text NOT NULL,
  subject_type     text NOT NULL,
  subject_id       uuid,
  payload          jsonb NOT NULL DEFAULT '{}',
  requires_decision boolean NOT NULL DEFAULT false,
  status           text NOT NULL DEFAULT 'unread',  -- unread|read|resolved|dismissed
  batch_id         uuid,
  created_at       timestamptz NOT NULL DEFAULT now(),
  read_at          timestamptz,
  resolved_at      timestamptz,
  CONSTRAINT notifications_status_ck CHECK (status IN ('unread','read','resolved','dismissed'))
);
CREATE INDEX notifications_inbox_idx ON notifications (recipient_id, status, created_at DESC);
CREATE INDEX notifications_pending_idx ON notifications (recipient_id)
  WHERE requires_decision AND status IN ('unread','read');
```

Types: `access.granted`, `access.revoked`, `access.reshared` (to a document's creator, when
someone else shared it), `link.target_moved`, `link.target_deleted`,
`link.suggested`, `automation.failed`, `ingestion.ready`, `automation.message`, and those that
management's changes raise for the person concerned: `group.added`, `group.removed`,
`account.password_reset`, `documents.transferred` (to the new owner), `agent.responsible_added` and
`agent.responsible_removed`, and `document.quarantined`, `document.released` and
`document.access_revoked` (to the document's owner).

`requires_decision` separates *information* ("you were given access to 3 documents") from *work*
("a document you link to was deleted — repoint, drop, or restore?"). Only the second kind should
follow a user around until it is resolved.

The inbox badge comes from `GET /api/me/inbox-counts`: the person's link decisions (from the link
graph), the links proposed into documents they can edit, and their *unread information* — a
notification that requires a decision is counted once, as the decision, never again as unread.
`POST /api/me/notifications/read` marks all of that information read in one statement; decisions
stay until they are answered.

### Ingestions

```sql
CREATE TABLE ingestions (
  id          uuid PRIMARY KEY,
  user_id     uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  filename    text NOT NULL,
  mime_type   text NOT NULL,
  byte_size   bigint NOT NULL DEFAULT 0,
  storage_key text,                    -- 'local://raw/2026/09/<uuid>.pdf' — the untouched original
  source      text,                    -- URL, Slack permalink, whatever it came from
  status      text NOT NULL DEFAULT 'pending',  -- pending|processing|ready|failed
  error       text,
  document_id uuid REFERENCES documents(id) ON DELETE SET NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT ingestions_status_ck CHECK (status IN ('pending','processing','ready','failed'))
);
CREATE INDEX ingestions_user_idx   ON ingestions (user_id, created_at DESC);
CREATE INDEX ingestions_status_idx ON ingestions (status) WHERE status <> 'ready';
```

The original file is always kept. A conversion that turns out to be wrong can be redone without
asking anyone to re-upload. See [ingestion.md](https://codna.me/docs/ingestion.md).

### Imports

```sql
CREATE TABLE imports (
  id           uuid PRIMARY KEY,
  user_id      uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  ingestion_id uuid NOT NULL REFERENCES ingestions(id) ON DELETE RESTRICT,  -- the kept archive
  format       text NOT NULL,          -- notion | confluence | markdown | files
  space        space NOT NULL DEFAULT 'raw',
  path         text NOT NULL,          -- the folder the pages land in
  status       text NOT NULL DEFAULT 'queued',   -- queued|running|done|failed
  done         integer NOT NULL DEFAULT 0,       -- pages written so far,
  total        integer NOT NULL DEFAULT 0,       -- of how many
  report       jsonb NOT NULL DEFAULT '{}',      -- what landed, what was skipped and why
  error        text,
  attempts     integer NOT NULL DEFAULT 0,
  worker_id    text,
  lease_until  timestamptz,            -- renewed while a worker reads the archive
  created_at, started_at, finished_at, updated_at timestamptz
);

CREATE TABLE import_pages (
  import_id   uuid NOT NULL REFERENCES imports(id) ON DELETE CASCADE,
  source_key  text NOT NULL,           -- a Notion page id, a Confluence page id, a path
  document_id uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  PRIMARY KEY (import_id, source_key)
);
```

An import reads an export archive into a folder of raw pages, in the background
([ingestion.md](https://codna.me/docs/ingestion.md#imports)). `import_pages` is what each page became: a re-import into the
same folder finds its pages there, wherever they have been moved, and adds a version to those that
changed ([adr/0016](https://codna.me/docs/adr/0016-imports-into-raw.md)). A row is written as each page is, so an import
picked up again after its worker died redoes nothing.

### Usage counts

```sql
CREATE TABLE usage_daily (
  day      date        NOT NULL,                       -- UTC
  user_id  uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  activity text        NOT NULL,                       -- sign_in | web | mcp
  tool     text        NOT NULL DEFAULT '',            -- the MCP tool, or (unknown); '' otherwise
  outcome  text        NOT NULL DEFAULT '',            -- ok | refused | error for mcp; '' otherwise
  count    bigint      NOT NULL DEFAULT 0,             -- for web, the hours of the day seen
  last_at  timestamptz NOT NULL,
  PRIMARY KEY (day, user_id, activity, tool, outcome)
);
CREATE INDEX usage_daily_user_idx ON usage_daily (user_id, day);
```

How the instance is used, as a count per person, day and kind: one upsert per sign-in and per tool
call, and one per person per hour of web use. Never what was read, searched for or written —
[permissions.md](https://codna.me/docs/permissions.md#how-the-instance-is-used) has what the report built on it says.
Writing is not here: the revisions already record who saved what when. Rows are small and few
(people × tools × days), and are kept like the revisions.

---

## Share suggestions: the traversal

Sharing walks outward from the shared document over resolved edges, cycle-safe and depth-limited —
**depth 1 by default**, meaning direct links only. The query below takes the depth as a parameter;
see [permissions.md](https://codna.me/docs/permissions.md#share-suggestions) for why the default is not deeper.

```sql
WITH RECURSIVE reachable AS (
    SELECT l.target_id AS id, 1 AS depth, ARRAY[l.source_id, l.target_id] AS via
    FROM document_links l
    WHERE l.source_id = $1
      AND l.target_id IS NOT NULL
      AND l.state IN ('resolved', 'stale_text')

    UNION ALL

    SELECT l.target_id, r.depth + 1, r.via || l.target_id
    FROM document_links l
    JOIN reachable r ON l.source_id = r.id
    WHERE l.target_id IS NOT NULL
      AND l.state IN ('resolved', 'stale_text')
      AND r.depth < $2              -- default 3
      AND NOT l.target_id = ANY(r.via)   -- cycle guard
)
SELECT DISTINCT ON (id) id, depth, via
FROM reachable
ORDER BY id, depth          -- shallowest path wins
```

The result is then filtered to documents the sharer may actually share, minus what the recipient
can already see — by a grant of their own, or one to a group they see through (every group of a
person's, Everyone included; Everyone, for a group) — minus anything in
`share_suggestion_decisions`.

`via` is returned, not just `depth`, so the agent can *explain* each suggestion — "suggested because
onboarding links to laptop-setup, which links to this" — rather than presenting an unexplained list.
An opaque list gets approved blindly, which is how over-sharing happens.

`stale_text` edges are included: the href text being out of date says nothing about whether the
documents belong together.

---

## Worked traces

Each scenario below names the exact rows written. If a scenario cannot be traced to rows, the model
is wrong.

### 1 · Tornike creates a wiki document

```
documents            + 1  version=1, created_by=tornike, content_hash=sha256(…)
document_revisions   + 1  version=1, change_kind='create'
events               + 1  document.created
```
Then, from the outbox:
```
document_links       + n  one per markdown link found in the body
                          state='resolved' where the target exists, 'pending' where it does not
Typesense            upsert  acl_read=['user:tornike']
```
No `document_access` rows. The creator's access is implicit — the alternative, a self-grant row on
every document, is redundant data that can drift out of agreement with `created_by`.

### 2 · Tornike shares `onboarding` with Dana, and accepts two suggestions

The traversal above returns `laptop-setup` (depth 1), `vpn-access` (depth 2), `payroll` (depth 2).
Tornike accepts the first two and declines `payroll`. One `batch_id` covers the whole action:

```
document_access      + 3  (onboarding, laptop-setup, vpn-access) → user:dana, read
                          grant_reason: 'direct' | 'link_suggestion' | 'link_suggestion'
                          batch_id: B
access_events        + 3  action='granted', batch_id=B
share_suggestion_decisions + 1  (payroll, user:dana, 'rejected')
events               + 3  access.granted
notifications        + 1  to dana, type='access.granted', batch_id=B
                          payload: { count: 3, documents: [...] }
Typesense            upsert ×3  acl_read += 'user:dana'
```

One notification for the batch, not three. `payroll` will not be suggested for Dana again.

### 3 · "Share everything tagged `runbook` with the ops team"

A one-time expansion, not a standing rule:

```sql
SELECT id FROM documents
WHERE tags @> ARRAY['runbook'] AND deleted_at IS NULL AND status = 'active'
```
```
document_access      + n  grant_reason='bulk_tag', batch_id=B
access_events        + n  action='granted', batch_id=B
notifications        + 1  per recipient, batch_id=B
Typesense            upsert ×n
```

A document tagged `runbook` tomorrow is **not** shared — the set was resolved once. To make it
continuous, the user asks their agent for an automation (trace 7). A folder is different: a folder
share stands, and covers what is put there later
([adr/0014](https://codna.me/docs/adr/0014-standing-folder-shares.md)):

```
folder_shares        + 1  (owner, space, path, principal, permission)
document_access      + n  grant_reason='folder'   — the owner's documents there now
   └─ later, from the outbox: document.created / moved / restored into the folder
      document_access + 1  grant_reason='folder', with its event, reindex and notification
```

### 4 · `runbooks/deploy.md` is renamed to `ops/deploy.md`

Three documents link to it.

```
documents            path/slug updated, its own relative links rebased, version 4 → 5
document_revisions   + 1  version=5, change_kind='move'
document_path_history + 1  ('wiki', 'runbooks', 'deploy'), valid_to=now()
events               + 1  document.moved
```
From the outbox:
```
document_links       3 edges → state='stale_text'   (target_id unchanged — the graph is intact)
notifications        + 3  one per linking document's author
                          requires_decision=true, type='link.target_moved'
                          payload: { from: 'runbooks/deploy.md', to: 'ops/deploy.md' }
Typesense            upsert  (path changed)
```

**No linking document is edited.** Rewriting them would manufacture a revision on each one and
pollute exactly the history this design exists to keep readable. The authors decide.

### 5 · `payroll-2027` is deleted, two documents link to it

```
documents            deleted_at=now(), purge_after=now()+30d
document_revisions   + 1  change_kind='delete'
events               + 1  document.deleted
```
From the outbox:
```
document_links       2 edges → state='broken'   (target_id retained, so restore is possible)
notifications        + 2  requires_decision=true, type='link.target_deleted'
                          actions offered: repoint · drop link · restore target
Typesense            delete
```
The row survives with its full history. A restore flips `deleted_at` to null, returns the edges to
`resolved`, and resolves the notifications. Purge only hard-deletes once no edge is still pending.

### 6 · "What was I given access to recently?"

```sql
SELECT ae.*, d.title, d.space, d.path, d.slug
FROM access_events ae
JOIN documents d ON d.id = ae.document_id
WHERE ae.principal_type = 'user' AND ae.principal_id = $1
  AND ae.action = 'granted'
  AND ae.created_at > $2            -- last time the agent checked
  AND d.deleted_at IS NULL
  AND EXISTS (                      -- still granted, not since revoked
    SELECT 1 FROM document_access da
    WHERE da.document_id = ae.document_id
      AND da.principal_type = 'user' AND da.principal_id = $1
  )
ORDER BY ae.created_at DESC;
```

The `EXISTS` clause matters: the event log is append-only, so a grant that was later revoked is
still in it. Announcing access that no longer exists would be worse than saying nothing.

### 7 · A tag-triggered automation makes trace 3 continuous

```
automations  + 1  kind='event', trigger_events={document.created,document.updated}
                  scope={"tags":["runbook"]}, owner_id=tornike
```
Later, someone tags a document `runbook`:
```
events             + 1  document.updated
  └─ matcher: scope matches → sandbox runs
     script proposes: share('ops-team@example.com','read')
     host validates against TORNIKE's permissions → allowed
automation_runs    + 1  status='ok', actions=[{share…, applied:true}], memory_peak=…
document_access    + 1  grant_reason='script'
access_events      + 1  action='granted'
notifications      + 1  to the ops team members
Typesense          upsert
```
If Tornike cannot share that document, the action is recorded in `automation_runs` with
`applied:false` and a reason. The script does not fail silently and does not gain access its author
lacks.

### 8 · A scheduled automation, with no subject document

```
automations  + 1  kind='schedule', schedule='0 9 * * 1', timezone='Asia/Tbilisi'
                  next_run_at=<next Monday 09:00 local, stored UTC>
```
On the tick:
```
automations        claimed via FOR UPDATE SKIP LOCKED; next_run_at recomputed
  └─ sandbox runs with NO doc in context
     codna.find({space:'wiki', updatedBefore:'-180d', tags:['runbook']})
       → executed as OWNER, ACL-filtered, capped at 200 rows
     script proposes: notify(author) per result
automation_runs    + 1  reads=1, actions=[…]
notifications      + n  type='automation.message'
```

The read is what makes scheduled scripts possible at all, and scoping it to the owner is what keeps
them safe: a person's weekly script sees exactly what that person could see by searching.

### 9 · Dana leaves, and what she owned goes to Tornike

Management deactivates her, then transfers ([permissions.md](https://codna.me/docs/permissions.md#when-someone-leaves)):

```
users                  is_active=false (dana)
sessions               − every one of dana's
management_audit       + 1  action='user.deactivate', target_user_id=dana
```
```
documents              created_by: dana → tornike, × n (live and trashed)
                          version and updated_at unchanged; grants untouched
document_owner_changes + n  from_user=dana, to_user=tornike, transfer_id=T
events                 + n  document.transferred
automations            owner_id: dana → tornike, enabled=false, × m
notifications          + 1  to tornike, type='documents.transferred'
management_audit       + 1  action='user.transfer', target_user_id=dana
```
From the outbox:
```
Typesense              upsert per live document  acl_read: user:dana → user:tornike
```
Whoever Dana shared with still reads what she shared, and Tornike can now take back any of it. Her
revisions keep her as their author; `document_owner_changes` says the documents were hers.

### 10 · A page reported as holding a password is quarantined, then released

Someone reports it by its id. Management looks at it without reading it — owner, space, status,
dates and who it is shared with — and quarantines it with a reason
([permissions.md](https://codna.me/docs/permissions.md#break-glass-without-reading)):

```
documents              quarantined_at=now()   (first, so it holds the row's lock)
document_access        − k  every grant, moved into …
document_quarantines   + 1  reason, saved_grants=[k grants]
access_events          + k  action='revoked', grant_reason='quarantine'
events                 + 1  access.revoked
notifications          + 1  to the owner, type='document.quarantined'
management_audit       + 1  action='document.quarantine', target_document_id
```
From the outbox:
```
Typesense              upsert  acl_read=['user:<owner>']
```
The owner, the only one who can still see it, looks: the "password" is a placeholder in an example.
Management releases it with `restore_sharing`:

```
documents              quarantined_at=NULL
document_access        + k' the saved grants as they were, k' ≤ k — none to people since
                          deactivated or groups since deleted, which the release reports
access_events          + k' action='granted', grant_reason='release'
events                 + 1  access.granted
document_quarantines   released_at=now(), restored_sharing=true
notifications          + 1  to the owner, type='document.released'
management_audit       + 1  action='document.release', target_document_id
```

At no point does management receive the title, the address or a word of the body. Had the password
been real, editing it out would not have been enough before giving the shares back: revisions are
kept forever, and everyone who can read a document can read its history.