[Bug] Dashboard tiles say “Last 14 days” but return lifetime totals

[TheCableGuy99]

Describe the bug
On the Dashboard, the four tiles (“Forwarded”, “Replies/Sent”, “Blocked”, “Aliases”) are labelled “Last 14 days”, but the backend code returns lifetime totals.
This makes the tiles appear not to “roll off” and contradicts the label.

How to reproduce

1. Open Dashboard (/) and view the four headline tiles (they show “Last 14 days” in the small caption under each number).

2. Compare with the database:

   Rolling 14-day counts:

   SELECT
     COUNT(*) FILTER (WHERE NOT is_reply AND NOT blocked) AS forwarded,
     COUNT(*) FILTER (WHERE is_reply)                     AS replies_sent,
     COUNT(*) FILTER (WHERE blocked)                      AS blocked
   FROM email_log
   WHERE created_at >= NOW() - INTERVAL '14 days';
   

   Lifetime counts (match the tiles):

   SELECT
     COUNT(*) FILTER (WHERE NOT is_reply AND NOT blocked AND NOT bounced) AS forwarded,
     COUNT(*) FILTER (WHERE is_reply AND NOT blocked AND NOT bounced)     AS replies_sent,
     COUNT(*) FILTER (WHERE NOT is_reply AND blocked AND NOT bounced)     AS blocked
   FROM email_log
   WHERE user_id IN (SELECT id FROM users);
   

3. Observe: the tile values match lifetime totals, not the 14-day window.

Expected behavior
Either:

• The tiles apply a 14-day rolling filter (to match the “Last 14 days” label), or
• The label is changed to “All time” to match the current logic.

Screenshots
(Attach a screenshot of the Dashboard showing the “Last 14 days” subtitle under each tile.
If helpful, include your SQL outputs redacted.)

Environment (If applicable):

• OS: Linux (Docker host)
• Browser: Chrome 141 (Windows 10)
• App image: simplelogin/app-ci:latest
• App version: .version file = dev (BUILD_ID unknown)
• DB: Postgres postgres:12.1
• Services: sl-app, sl-email, sl-job-runner, sl-db on same Docker network
• Self-hosted

Additional context
Evidence in code:

Template labels:

templates/dashboard/index.html
  lines ~143, ~157, ~171 → "Last 14 days"

Backend for the tiles:

app/dashboard/views/index.py → get_stats(user)

Uses:

Session.query(EmailLog)
  .filter_by(user_id=user.id, is_reply=..., blocked=..., bounced=...)
  .count()

There’s no date filter, so these are lifetime counts.

Minimal fix ideas:

Option A (match label): add a 14-day cutoff (UTC) to each EmailLog query in get_stats():

from datetime import datetime, timedelta
cut = datetime.utcnow() - timedelta(days=14)
# … add EmailLog.created_at >= cut to each query …

Option B (match logic): change the tile captions in index.html from “Last 14 days” to “All time”.

This is not a configuration issue: background jobs and DB connectivity are confirmed healthy;
the mismatch is between template text and backend query.

[nguyenkims]

Hi it's actually the last 14 days and not lifetime as Pass deletes email logs older than 14 days.

[TheCableGuy99]

Hey, thanks for clarifying that.

It appears the internal cron wasn’t actually running, so nothing was ever deleting from email_log. That made the dashboard tiles look like lifetime totals instead of last 14 days.

I fixed it by adding cron jobs to trigger the same internal tasks directly:

# Daily cleanup of old email logs
30 0 * * * /usr/bin/docker exec sl-job-runner python /code/cron.py -j delete_logs >> /var/log/sl-delete_logs.log 2>&1

# Weekly cleanup of old monitoring data
15 1 * * 0 /usr/bin/docker exec sl-job-runner python /code/cron.py -j delete_old_monitoring >> /var/log/sl-maint.log 2>&1

After running it manually once to clear the backlog, the stats instantly started reflecting a true rolling 14-day window — and the nightly cron’s keeping it tidy now.

For anyone else self-hosting: this seems to happen if the sl-job-runner container can’t connect to the database (missing DB_URI), so the internal scheduler never fires. Adding that env var or using an external cron like above fixes it.

Appreciate the pointer... it got me looking in the right place!