ai-cfia / ai-cfia/insect-project
Persist report data, generated emails, and metadata (Postgres-only)
- Dominant language
- Python
- Stars
- 0
- Forks
- 0
- PR merge metrics
- No merged PRs in 30d
Description
# Outcome
- Persist the exact datasets used to render each report.
- Persist the generated email HTML and subject.
- Persist send metadata for audit, idempotency, troubleshooting, and re-send support.
# Acceptance criteria
- Postgres-only design; no object storage used.
- Schema includes:
- `reports_sent`:
- report_type, idempotency_key (unique), period_start/period_end or date_on
- environment, code_commit (optional), model_version (optional)
- data_hash, email_hash
- recipients_group, recipients_count
- send_status: pending|sent|failed, error_message, sent_at
- created_at, updated_at
- `report_artifacts`:
- report_id (FK to reports_sent)
- artifact_type: data_ca_json|data_us_json|comments_data_json|email_html|subject_text|metadata_json
- content_json (JSONB) for data/metadata OR content_text (TEXT) for email/subject
- content_hash, size_bytes, created_at
- Idempotency enforced by deterministic key:
- observations: observations:YYYY-MM-DD
- comments: comments:YYYY-MM-DD_to_YYYY-MM-DD
- Privacy: do not store individual email addresses; only recipients_group and recipients_count.
- Flow:
- Insert `reports_sent` with send_status=pending and artifacts after render.
- Attempt send; update send_status to sent or failed (capture error_message).
- Retention:
- email_html retained 90 days; data_json/metadata_json retained 1 year (via scheduled delete job).
- Config:
- Feature flag to toggle persistence.
- DSN env var for Postgres.
- Tests:
- Unit tests for persistence functions (hashing, inserts/updates, idempotency).
- Integration tests for happy path and failure path (send error).
- Docs:
- Update README/ops notes with schema, env vars, and retention.
# Context
- Reports are currently emailed only and not persisted.
- Same email content goes to all recipients; no per-recipient storage needed.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.