Phase 2c: Sender registry + self-service Gmail re-auth

### Type Feature ### Lineage `plan-pal-e-mail` → Phase 2c ### Repo `forgejo_admin/pal-e-mail` ### User Story As a platform admin (Lucas) I want to see which email senders are healthy vs expired and re-authorize them from a browser So that I don't need SSH + kubectl to manage Gmail OAuth tokens ### Context Gmail OAuth tokens expire (Google Cloud project in testing mode = 7-day refresh token expiry). Currently re-auth requires manual browser flow + kubectl cp to seed token files on the PVC. Phase 2c moves tokens to Postgres (backed up via CNPG), adds a sender management API, and provides a browser-based re-auth flow. When a send fails with 401, the sender status flips to expired. Lucas hits the re-auth endpoint, opens the Google consent URL, authorizes, callback saves new token to DB. Self-service, no CLI required. ### File Targets Files to create: - `src/pal_e_mail/models.py` — add Sender model (alias, gmail_account, credentials_json, token_json, status, last_refreshed_at, project) - `src/pal_e_mail/routes/senders.py` — GET /senders, GET /senders/{alias}, POST /senders/{alias}/auth, GET /senders/callback, DELETE /senders/{alias} - `src/pal_e_mail/services/token_store.py` — Postgres-backed token load/save adapter for gmail-sdk - `alembic/versions/003_senders.py` — migration for senders table Files to modify: - `src/pal_e_mail/services/sender.py` — use Postgres token store instead of filesystem, update sender status on failure - `src/pal_e_mail/main.py` — include senders router - `src/pal_e_mail/config.py` — add google_client_id, google_client_secret settings ### Acceptance Criteria - [ ] GET /senders lists all registered senders with status - [ ] POST /senders/{alias}/auth returns Google OAuth consent URL - [ ] OAuth callback saves tokens to Postgres - [ ] send_email() loads tokens from Postgres, not filesystem - [ ] Failed send (401) flips sender status to expired - [ ] Successful send after re-auth flips status back to healthy - [ ] DELETE /senders/{alias} removes sender ### Test Expectations - [ ] Unit test: Postgres token store load/save - [ ] Unit test: sender status lifecycle (healthy → expired → healthy) - [ ] Unit test: GET /senders returns registered senders - [ ] Unit test: OAuth flow generates correct redirect URL - [ ] Unit test: send_email uses DB tokens not filesystem - Run command: `cd ~/pal-e-mail && .venv/bin/pytest tests/ -v` ### Constraints - OAuth callback needs browser access — either Tailscale funnel for /senders paths or kubectl port-forward - credentials.json (Google client config) still needs to be seeded once - Tokens in Postgres are encrypted at rest (CNPG TDE) — no additional encryption needed - gmail-sdk token adapter must be backward-compatible (can fall back to filesystem if DB empty) ### Checklist - [ ] PR opened - [ ] Tests pass - [ ] No unrelated changes ### Related - `project-pal-e-mail` — parent project - `plan-pal-e-mail` — parent plan Phase 2c - `forgejo_admin/pal-e-mail#3` — Phase 2 (predecessor) - `reference_gmail_oauth` — memory note on current re-auth procedure

forgejo_admin commented

2026-03-22 18:57:03 +00:00

Owner

Type

Feature

Lineage

plan-pal-e-mail → Phase 2c

Repo

forgejo_admin/pal-e-mail

User Story

As a platform admin (Lucas)
I want to see which email senders are healthy vs expired and re-authorize them from a browser
So that I don't need SSH + kubectl to manage Gmail OAuth tokens

Context

Gmail OAuth tokens expire (Google Cloud project in testing mode = 7-day refresh token expiry). Currently re-auth requires manual browser flow + kubectl cp to seed token files on the PVC. Phase 2c moves tokens to Postgres (backed up via CNPG), adds a sender management API, and provides a browser-based re-auth flow. When a send fails with 401, the sender status flips to expired. Lucas hits the re-auth endpoint, opens the Google consent URL, authorizes, callback saves new token to DB. Self-service, no CLI required.

File Targets

Files to create:

src/pal_e_mail/models.py — add Sender model (alias, gmail_account, credentials_json, token_json, status, last_refreshed_at, project)
src/pal_e_mail/routes/senders.py — GET /senders, GET /senders/{alias}, POST /senders/{alias}/auth, GET /senders/callback, DELETE /senders/{alias}
src/pal_e_mail/services/token_store.py — Postgres-backed token load/save adapter for gmail-sdk
alembic/versions/003_senders.py — migration for senders table

Files to modify:

src/pal_e_mail/services/sender.py — use Postgres token store instead of filesystem, update sender status on failure
src/pal_e_mail/main.py — include senders router
src/pal_e_mail/config.py — add google_client_id, google_client_secret settings

Acceptance Criteria

GET /senders lists all registered senders with status
POST /senders/{alias}/auth returns Google OAuth consent URL
OAuth callback saves tokens to Postgres
send_email() loads tokens from Postgres, not filesystem
Failed send (401) flips sender status to expired
Successful send after re-auth flips status back to healthy
DELETE /senders/{alias} removes sender

Test Expectations

Unit test: Postgres token store load/save
Unit test: sender status lifecycle (healthy → expired → healthy)
Unit test: GET /senders returns registered senders
Unit test: OAuth flow generates correct redirect URL
Unit test: send_email uses DB tokens not filesystem
Run command: cd ~/pal-e-mail && .venv/bin/pytest tests/ -v

Constraints

OAuth callback needs browser access — either Tailscale funnel for /senders paths or kubectl port-forward
credentials.json (Google client config) still needs to be seeded once
Tokens in Postgres are encrypted at rest (CNPG TDE) — no additional encryption needed
gmail-sdk token adapter must be backward-compatible (can fall back to filesystem if DB empty)

Checklist

PR opened
Tests pass
No unrelated changes

project-pal-e-mail — parent project
plan-pal-e-mail — parent plan Phase 2c
forgejo_admin/pal-e-mail#3 — Phase 2 (predecessor)
reference_gmail_oauth — memory note on current re-auth procedure

Commenting is not possible because the repository is archived.

Rows
Columns

Phase 2c: Sender registry + self-service Gmail re-auth #6