# 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 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.