Reports and scheduled emails

Admin reports answer operational questions from the database. Some are also emailed on a schedule, so the people who need them do not have to log in to admin. The first of these is level advancements (TUN-914): instructors and trainers who completed a full level in a week.

Level advancements

Admin: Reports › Members › Level Advancements.

When a level counts as complete

The platform stores the level a member is at (members.level_instructor, level_trainer) but not when they reached it, and those columns are not reliable. So the report rebuilds completion from the logbook:

• A level is complete when every open skill in its category is approved: the entry is open, suspended or not_current. Pending and denied entries do not count. This is the same rule as hasLevel() in member-upgrades.
• It completed on the first approval of the last skill needed. A skill logged twice counts once, at its first approval.
• Trainer levels include service milestones (e.g. “12 Months Instructor Experience”). They are logbook skills, so a trainer level completes when the milestone is signed off.
• Programmes covered: instructor levels 1–4 and trainer levels 1–4.

Dates are Pacific time

Most of the members concerned are in the US, so ops agreed on PST. A week is Monday 00:00 to the next Monday 00:00 in America/Los_Angeles, including daylight saving: the weeks the clocks change are 167 or 169 hours long. The page and the email label this “PST” throughout, though summer dates are PDT.

Who can see it

Enforced on /api/admin/reports/progression and by the tab’s roles in admin. Tunnel managers see every tunnel’s members, not just their own.

Edge cases

• A skill added to a level later raises the bar for everyone. A member who had every skill before the addition only completes the level again when the new skill is approved, and appears in the report then.
• A deleted approval (an entry removed after the fact) can move a completion date or remove it, because the report is recalculated each time. The emailed CSV is the record of what was reported that week.
• Duplicate entries for the same skill do not change the date.

Scheduled report emails

A daily job, scheduled_reports, runs at 08:00 UTC (midnight PST, 1am PDT). For each report in the registry (scheduled/registry.js) it works out the last complete period and sends it if that period has not been sent.

Rules

• Each period is sent once. The period is claimed in scheduled_report_runs before sending, and (report_key, period_start) is unique. A Worker retry or a manual trigger cannot send it again.
• A missed send heals itself. A failed run, or a claim older than an hour, is retried by the next day’s run. A weekly report therefore goes out on Monday, or the first day after that it succeeds.
• No recipients, no send. A report with no recipients is skipped with a warning and not claimed, so adding recipients later still sends the period.
• Empty periods still send, saying there is nothing to report, so a quiet week can be told apart from a broken job.
• The email has the rows as a table (first 200) and all of them as a CSV, and links to the admin report for that period.
• The CSV starts with a UTF-8 byte-order mark so Excel reads accents, and a cell starting with =, +, - or @ is prefixed with ' because member names are typed by members and spreadsheets would run them as formulas.

Recipients

Managed under Tools › Scheduled Emails, which also lists the last six sends per report with status and error. Only Super Admin (role 1) can open Tools. The report page links there for users who can.

Addresses are stored lowercased. Outside production every email goes to EMAIL_ALERT instead, whoever the recipients are (only the to address is redirected, so reports never use cc or bcc).

Adding a report

Add an entry to registry.js: key, title, intro, cadence (weekly or monthly), time zone, admin path, columns and run(period), which returns the rows. Then add its recipients in admin. No Worker, migration or cron change is needed. Operational detail and test commands are on the cron jobs page.

Related

• Skill levels: the level ladders this report reads.
• Logbook: entry states and what counts as approved.
• Cron jobs: how the job is fired and monitored.