[Feature] Scheduled digest reports via email

[Polliog]

Feature Description

Add scheduled email digest reports that summarize key system activity over a configurable period, giving teams async visibility into their infrastructure health without requiring them to open the Logtide dashboard every day.

Problem/Use Case

Not everyone on a team monitors the Logtide dashboard daily. Engineering managers, on-call rotation members who aren't currently on duty, and team leads often want a periodic summary of what happened — new errors, security detections, error rate trends — without actively checking the tool. Currently there's no way to get this kind of async, push-based visibility from Logtide.

Proposed Solution

• Per-organization digest configuration: frequency (daily or weekly), delivery time, and recipient list (configurable per member)
• Report content:
  • Total log volume compared to the previous period, with a trend indicator
  • Top 5 services by error count, with delta vs the previous period
  • New error groups that appeared for the first time in this period
  • Security summary: total detections, top triggered Sigma rules, currently open incidents
  • Uptime summary when health monitors are configured (see issue [Feature] OTLP Traces Ingestion & Trace Viewer #3)
• Clean HTML email layout with a plain-text fallback for email clients that don't render HTML
• Scheduling via BullMQ repeatable jobs with cron expressions
• One-click unsubscribe link in the email footer, respecting individual recipient preferences without requiring login

Alternatives Considered

• Webhook-based push to Slack/Discord: sending the digest as a message to a channel instead of email. This is a useful complementary channel but email is more appropriate for async digest-style content that may be reviewed later, archived, or forwarded.
• Polling via the API: letting users build their own digest with the Logtide API. Valid for technical users but not accessible for non-technical stakeholders.

Implementation Details (Optional)

• Digest generation runs as a BullMQ repeatable job; report data is computed from existing continuous aggregates (logs_hourly_stats, detection_events_daily_stats) — no new heavy queries needed
• Email rendering: use a simple HTML template with inline CSS for maximum email client compatibility; avoid external CSS frameworks
• Unsubscribe: generate a signed token per recipient stored in the DB; clicking the link marks that recipient as unsubscribed for that report without requiring authentication
• The existing email notification infrastructure (already used for alerts) can be reused as the sending layer

Priority

• Critical - Blocking my usage of LogTide
• High - Would significantly improve my workflow
• Medium - Nice to have
• Low - Minor enhancement

Target Users

• DevOps Engineers
• Developers
• Security/SIEM Users
• System Administrators
• All Users

Additional Context

This feature is particularly valuable in team contexts where Logtide is deployed by one person but the results need to be visible to the broader team or management without requiring everyone to have an account and check the dashboard regularly.

Contribution

• I would like to work on implementing this feature

[DEVAANSH001]

Hi i would like to work on this feature

[Polliog]

Hi @DEVAANSH001, thanks for the interest!

Before I assign this, I'd like to align on the approach. It's a feature we care about getting right, so I'd rather spend a bit of time on design up front. A few things to discuss:

Scope. The issue as written is too large for a single PR. I'd like to split it into smaller, mergeable pieces:

1. DB schema: digest_configs (per-org), digest_recipients (per-member preferences + signed unsubscribe tokens)
2. Repeatable-job skeleton on top of our queue abstraction (packages/backend/src/queue/, backed by BullMQ or graphile-worker depending on deployment) + the log-volume section, which is the simplest aggregation and uses the existing logs_hourly_stats
3. Email rendering (HTML template + plaintext fallback, inline CSS) reusing the existing alert email infrastructure
4. Remaining report sections (top services by errors, new error groups, security summary, uptime)
5. Frontend settings UI + public unsubscribe endpoint

Starting from #1 + #2 as the first PR would be ideal: it's self-contained and lands real value even if the rest takes time.

A few technical questions to make sure we're on the same page:

• For the security section of the report, we already have detection_events_hourly_stats and detection_events_daily_stats continuous aggregates (see migration 019_continuous_aggregates.sql). Which bucket would you pull from for each frequency, and how would you handle the "top triggered Sigma rules" breakdown (direct query against detection_events with time bounds, or extend detection_events_rule_stats)?
• For the unsubscribe token: what's your thinking on signing (HMAC with a server-side secret? a short-lived signed JWT?) and token lifetime? It needs to work without auth and resist enumeration.
• Our queue layer is deployment-aware (BullMQ when Redis is present, graphile-worker in docker/docker-compose.simple.yml, which is the Redis-free deployment). Have you checked that the repeatable-job API you're planning to use is supported by both adapters, or should one be extended first?
• How would you handle organizations with no activity in the period (empty digest: skip, or send a "nothing to report" version)?

Before you start coding, please drop a short design comment here covering the points above and a rough breakdown of how you'd tackle the first PR. Once we agree on the approach I'll assign the issue to you.

Also note: we're finalizing a CLA before we start accepting larger outside contributions. If you're comfortable proceeding, I'll ping you when it's ready to sign. It shouldn't block you from starting once we align on design.

Thanks again, looking forward to your thoughts.

[DEVAANSH001]

Thanks for the detailed breakdown! Here's my thinking on each question.

1. Security aggregates

For the total detection count:

• Daily digests - detection_events_hourly_stats (sum over 24 × 1h buckets)
• Weekly digests -detection_events_daily_stats (sum over 7 × 1d buckets)

Both already group by organization_id, so the query is a simple SUM(detection_count) WHERE bucket >= period_start AND organization_id

For top triggered Sigma rules: detection_events_rule_stats works for both frequencies it's a daily bucket keyed on (organization_id, sigma_rule_id, rule_title, severity) with a detection_count.
For a daily digest I'd sum the single day's buckets; for weekly I'd sum 7 days. No need to extend it or change detection_events directly the data is already there.

2. Unsubscribe token

HMAC over recipientId + email, keyed with API_KEY_SECRET (already in env), output as URL-safe base64.
No expiry the token is valid as long as the digest_recipients row exists, which is the right tradeoff for a one-click unsubscribe that needs to survive indefinitely in forwarded emails. No JWTs the expiry/rotation would add more work.

3. Queue layer

I looked at both adapters - The current IQueueAdapter interface only exposes add(), so either way an extension is needed. we can do like

Abstraction: add scheduleRepeating(name, cronExpression, data) to IQueueAdapter

BullMQ with: queue.add - name, data, repeat: cron: cronExpression

Graphile implementation

• When scheduleRepeating is called, use cron-parser to evaluate the cron expression and calculate the next tick, then call quickAddJob with that runAt timestamp
• The cron expression is embedded in the job payload
• When the graphile worker processes the job, after executing the task it calculates the next runAt from the stored cron expression and enqueues the next iteration via quickAddJob
• This logic lives entirely inside GraphileQueueAdapter the DigestSchedulerService just calls scheduleRepeating() and never knows which backend is running

This keeps adapter-specific code out of business logic and works within graphile-worker's constraints.

4. Empty organizations

We can send with a "quiet period" summary at the top. The email renders zero-value metrics and a single "No activity during this period" callout would be a better approch.

For the send part - nodemailer smtp which is already their.

PR #1

• Migration: digest_configs (org-level: frequency, delivery hour, timezone, enabled) + digest_recipients (email, subscribed bool, unsubscribe_token, user_id nullable)
• IQueueAdapter.scheduleRepeating() + implementations in both adapters
• DigestSchedulerService: reads active configs on worker startup, registers repeatable jobs, handles config create/update/delete by re-registering
• DigestGeneratorService: queries logs_hourly_stats for log volume

The email rendering (PR #2) and remaining sections for (PR #3) .

Open to feedback on any of the above before I start.
Looking forward to the CLA when it's ready.

[Polliog]

Thanks, good answers on #1 and #4. A couple of things to reconsider on #2 and #3 before you start.

Unsubscribe token. I'd go with a random opaque token (32 bytes from crypto.randomBytes, stored in digest_recipients.unsubscribe_token with a unique index) rather than HMAC. Since the unsubscribe handler has to hit the DB anyway to mark the row, HMAC isn't buying us anything and brings two problems: reusing API_KEY_SECRET couples the email domain to API-key rotation, and if we ever want to invalidate a single recipient's link (e.g., they resubscribed then re-unsubscribed, or we want to regenerate after a leak) we can't do it without rotating the global secret. Random per-row token sidesteps all of that.

Queue / cron. Rather than implementing cron in the Graphile adapter by manually re-enqueuing, let's use graphile-worker's native crontab support (parsedCronItemsFromCrontab / parsed cron items passed to run). We're on 0.16.6 which has solid first-class cron. Manual re-enqueue is fragile: if the worker crashes between task completion and enqueueing the next tick, the schedule is lost. Native cron is persistent and survives restarts. Both BullMQ (native repeat.cron) and graphile-worker have this, so the abstraction should be "register a set of cron items at worker boot" rather than scheduleRepeating(cronExpr, data) one-by-one. Concretely: IQueueAdapter (or a sibling ICronRegistry) grows a registerCronJobs(items) method, called once at worker startup from the active digest_configs rows.

One detail on the aggregates. detection_events_rule_stats has end_offset => '1 day' and a 6-hour refresh interval, so the most recent day won't be in the CAGG when a daily digest runs. Three options: force refresh_continuous_aggregate before generating, fall back to a direct detection_events query for the most recent window, or accept the staleness and note it. I'd lean toward the direct-query fallback for the most recent bucket only and the CAGG for everything older, but up to you, whatever you pick just document the tradeoff.

PR #1 scope. One small addition: include a minimal plaintext email send at the end of DigestGeneratorService, even just subject + "log volume: N" + unsubscribe link, routed through the existing nodemailer setup. HTML rendering can stay in PR #2, but I'd like PR #1 to exercise the full pipeline end-to-end including the send, so tests (and MailHog in dev) actually validate something useful.

Branch. Please open the PR against main. We use a regular merge commit (not squash), so keep your commit history clean and meaningful on your side: each commit should stand on its own and the sequence should tell a reviewable story. Rebase on main before opening and whenever you need to pick up upstream changes.

Once you address these four points, go ahead and open the draft PR. On the CLA: realistically it's about a month out, so don't wait on it. Start coding and we'll review and merge normally; I'll ping you to sign once the CLA is ready, but it won't hold anything up.