email_agent/docs/retention.md

# Retention — one month of bodies, a capped action log

Status: **complete.** Built, tested, and deployed on `.13` (own container,
port 8790). This closes the AGENTS.md §7 data-model rule that was still
outstanding after Phase 7 ([AUD-73](/AUD/issues/AUD-73)). Parent
[AUD-68](/AUD/issues/AUD-68). Code commits `17d605d`…`d9f8d34`.

**DoD:** a stored message older than 30 days has its body cleared and its
metadata stays; `actions_log` is size-capped; tests green; deployed on `.13`.
— **met** (§4, §5).

## 1. What was built

AGENTS.md §7 says:

> Retention: keep message bodies for **one month**. Logs are append-only and
> size-capped.

The bounded `-retain` job enforces both halves.

| Piece | Where | Behaviour |
|---|---|---|
| `Store.Retain` / `Store.RetainWithAudit` | `internal/store/retention.go` | One transaction. Blanks `messages.body_text` for messages received before the cutoff, deletes the oldest `actions_log` rows beyond the cap. |
| Config | `internal/config/config.go` | `EMAIL_AGENT_RETENTION_DAYS` (default 30), `EMAIL_AGENT_ACTIONS_LOG_MAX_ROWS` (default 50000). |
| CLI | `cmd/email-agent/main.go` | `-retain` opens the store, runs one pass, prints a one-line result, exits. |
| Tests | `internal/store/retention_test.go`, `internal/config/config_test.go` | Body clearing, metadata retention, log cap (with and without the audit row), disabled policy, config defaults/overrides. |

## 2. Decisions

1. **The window is 30 days from `received_at`.** `received_at` is the time the
   service stored the message (`internal/mailfetch` sets it during the IMAP
   scan), which is the same clock the rest of the app uses. It is not the
   `Date` header, which a sender controls.
2. **Clear the body, keep the metadata.** `body_text` is set to `NULL`. Sender,
   recipient, subject, source address (`X-Pm-Original-To`), classification,
   folder, Gmail label, and all timestamps stay. A cleared body is much smaller
   than a deleted row and keeps the inbox history truthful. The UI already
   renders an empty body (`COALESCE(body_text, '')`), so nothing else changes.
3. **`NULL`, not `''`.** The update predicate is `body_text IS NOT NULL AND
   body_text <> ''`, so a second pass is a true no-op and never re-counts a
   cleared row. The test asserts this.
4. **The cap is a row count, not an age.** AGENTS.md says "size-capped", and the
   log is append-only, so the job keeps the newest `N` rows by monotonic `id`
   and deletes the rest. The default `N=50000` holds a long triage history
   while keeping the SQLite file modest.
5. **Retention is logged, and the audit row counts against the cap.** The job
   writes one `retain` row to `actions_log` (FR-13: every action is logged).
   That row is written **inside the same transaction and before the cap**, so
   after a pass `actions_log` holds at most `N` rows *including* the audit row.
   Without that ordering the table would sit at `N+1` between runs.
6. **One transaction.** Body clearing and the cap commit together, so a partial
   pass never leaves the database mixed. Both statements are idempotent.
7. **Bounded and explicit.** The job is a CLI flag, like `-fetch` and
   `-classify`. The HTTP server never runs retention on a request, so no page
   load can start a slow write.
8. **Re-fetch is harmless.** If an old message is re-fetched inside the lookback
   window, `UpsertMessage` restores its body; the next retention pass clears it
   again. The stored metadata is never lost.

## 3. Configuration

| Variable | Default | Purpose |
|---|---|---|
| `EMAIL_AGENT_RETENTION_DAYS` | `30` | Body window in days. |
| `EMAIL_AGENT_ACTIONS_LOG_MAX_ROWS` | `50000` | Newest `actions_log` rows kept. |

Both are passed through by `docker-compose.yml` and listed in `.env.example`.
A non-positive value disables that half of the pass, which is useful for a
diagnostic run.

## 4. Verification

### 4.1 Unit tests (green)

`go test ./internal/store/ ./internal/config/` — all pass. The retention tests:

- `TestRetainClearsOldBodiesKeepsMetadata` — a 45-day-old body becomes `NULL`;
  a 2-day-old body is untouched; `MessageByID` still returns the subject and
  source address; a second pass clears 0.
- `TestRetainCapsActionsLog` — 5 rows with a cap of 2 leaves the 2 newest; a
  smaller table is untouched.
- `TestRetainWithAuditCountsItsOwnRow` — 5 rows + 1 audit with a cap of 3 leaves
  exactly 3 rows, newest is the `retain` row.
- `TestRetainDisabledPolicyIsNoOp` — a zero policy changes nothing.
- `TestRetentionDefaultsAndOverrides` — config defaults and overrides.

### 4.2 Deployed binary, seeded scratch database

Run with the deployed image (`email-agent:latest`, commit `d9f8d34`) against a
scratch database that held one 45-day-old and one 2-day-old message and 10
action rows, with the cap set to 5:

```
$ email-agent -retain      # EMAIL_AGENT_ACTIONS_LOG_MAX_ROWS=5
bodies_cleared=1 body_cutoff=2026-09-12 actions_deleted=7 actions_max_rows=5

# message rows afterwards
message: ('retention-fresh', 'Fresh mail',        'fresh body content', 'hello@where-woof.com')
message: ('retention-old',   'Old forwarded mail', NULL,                 'hello@where-woof.com')

actions_log rows after cap: 5
newest action: ('retain', 'retention_days=30 max_rows=5 bodies_cleared=1', 'scheduler')
```

The old body is `NULL`; its subject and source address are intact; the fresh
body is untouched; `actions_log` is exactly at the cap, including the audit row.

### 4.3 Live deployment, production database

```
$ docker exec email-agent email-agent -retain
bodies_cleared=0 body_cutoff=2026-09-12 actions_deleted=0 actions_max_rows=50000
```

`bodies_cleared=0` is correct: the oldest stored message was received on
2026-10-11, well inside the 30-day window. The same command was run twice and
the database is unchanged, which shows the job is idempotent. The live database
holds 103 messages (102 with a body) and one `retain` audit row per run:

```
audit: ('2026-10-12 23:44:28', 'scheduler', 'retention_days=30 max_rows=50000 bodies_cleared=0')
schema_version: 6
```

No schema change was needed; retention operates on existing columns, so the
live database stayed at `schema_version: 6`.

## 5. Deployment

On `.13`, from the repo, with the host secrets in scope:

```sh
set -a; . ~/.config/environment.d/10-secrets.conf; set +a
EMAIL_AGENT_VERSION=retention EMAIL_AGENT_COMMIT=$(git rev-parse --short HEAD) \
  docker compose build
docker compose up -d
```

`https://mail.lab.audasmedia.com.au/health` → `200`, reporting
`version: retention`, `commit: d9f8d34`. The container is the project's own
`email-agent` on port `8790`; no other service changed.

## 6. Scheduling

The job is a bounded one-shot, so the n8n schedule (FR-1) can call it the same
way it calls `-fetch` and `-classify`, for example once a day:

```sh
docker exec email-agent email-agent -retain
```

No n8n workflow was added in this change. The build spec's own wording allows
either an n8n step **or** a bounded CLI flag ("Run it on the existing n8n
schedule, or add a bounded CLI flag"), and the flag is the deployable unit. Note
that no `email_agent` workflow exists in n8n yet, so wiring the schedule is a
separate task that touches the shared n8n service.

## 7. Rollback

1. `git revert <docs-commit> d9f8d34 3766ce1 17d605d && docker compose up -d --build`.
2. No schema change to undo. Bodies already cleared are not recoverable from the
   database; a later `-fetch` restores the body of any message still inside the
   IMAP lookback window.
3. The action-log cap only deletes rows; there is no undo for a trimmed log.

## 8. Not done / notes

- **Reclassification after a clear.** A cleared body is unavailable to
  `-classify-all` and to the LLM reply draft. That is the intended trade-off of
  the one-month rule; the message keeps its existing classification. Run
  classification before retention if a full reclassification is needed.
- **The n8n schedule is not wired** (§6). This is a shared-service change and
  was not made here.
- **The audit row detail** records `retention_days`, `max_rows`, and
  `bodies_cleared`; `actions_deleted` is not in the detail because the row is
  written before the cap runs. The CLI output and the `actions_log` count show
  the deleted number.