# Event Activity Logging & Daily Digest Implementation Plan > **For Claude:** REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task. **Goal:** Log all employer and admin event activity to `employer_history` and send a daily digest email to `core@hbcuconnect.com`. **Architecture:** Instrument 3 CGI scripts with `logEvent()` calls using existing `employer_history` table. Portal actions already use `Portal:` prefix; admin actions will use `Event:` prefix. New cron produces a daily digest email querying both prefixes, grouped by event then employer, with admin quick-links. **Tech Stack:** Perl CGI, DBI/MySQL, `logEvent()` from `commonFuncs.pm`, `smtp_notify()` from `niceMail.pl`, crontab --- ## Key Discovery: portal.cgi Already Logs Most Actions Portal.cgi already calls `logEvent()` with `Portal:` prefix for: save_attendee, delete_attendee, upload_file, delete_upload, save_config, save_shipping, confirm_logistics, save_upgrades. Only `confirm_attendee` is missing. ## logEvent() Signature Reference ```perl # File: admin/commonFuncs.pm # Columns: employer_id, contact, tstamp (auto NOW), event_summary, event, priority &logEvent($employer_id, $contact, $summary, $event, $ccdbh, $priority); ``` - `$contact` = who performed the action (employer name or admin username) - `$summary` = category label (used for grouping/filtering) - `$event` = detailed description - `$ccdbh` = career_center database handle (logEvent opens its own if not provided) - Has built-in duplicate detection: same employer_id + contact + summary + event on same day = skip --- ### Task 1: Add logging to confirm_attendee in portal.cgi The only unlogged employer action. All other portal actions already log. **Files:** - Modify: `events/portal.cgi:1730-1743` **Step 1: Add logEvent call after confirmAttendee() succeeds** At line 1739, after `confirmAttendee($aid, $val)` and before `print encode_json`, insert: ```perl # Fetch attendee name for log my $att_sth = $ccdbh->prepare('SELECT name FROM event_attendees WHERE attendee_id=?'); $att_sth->execute($aid); my ($att_name) = $att_sth->fetchrow_array; $att_name ||= "attendee #$aid"; require "../admin/commonFuncs.pm"; my $action_word = $val ? 'Confirmed' : 'Unconfirmed'; &logEvent($employer->{employer_id}, $employer->{company_name}, "Portal: $action_word attendee", "Portal: $action_word attendee: $att_name", $ccdbh, 0); ``` **Step 2: Syntax check** Run: `cd /var/www/vhosts/hbcuconnect.com/httpdocs/events && perl -c portal.cgi` Expected: `portal.cgi syntax OK` **Step 3: Fix ownership** Run: `chown hbcuconnect:psacln /var/www/vhosts/hbcuconnect.com/httpdocs/events/portal.cgi` **Step 4: Commit** ```bash git add events/portal.cgi git commit -m "feat(events): add activity logging for confirm_attendee in portal Co-Authored-By: Claude Opus 4.6 " ``` --- ### Task 2: Add logging to event_sponsor_manager.cgi admin actions This is the biggest gap — only `save_materials_text` currently logs. **Files:** - Modify: `events/event_sponsor_manager.cgi:61-240` **Step 1: Add logEvent calls to each action handler** For each action in `handleAjaxAction()`, add a `logEvent()` call after the successful operation. The pattern: ```perl require "../admin/commonFuncs.pm" unless defined &logEvent; &logEvent($employer_id, $ENV{REMOTE_USER} || 'admin', "Event: SUMMARY", "Event: DETAIL", $ccdbh, 0); ``` **Actions to instrument (add after each success path):** **a) send_reminder (line 70, after `if (sendMaterialsReminder(...))`):** ```perl # Inside the success block my $emp = $ccdbh->selectrow_hashref('SELECT company_name FROM employer_data WHERE employer_id=?', undef, $employer_id); require "../admin/commonFuncs.pm" unless defined &logEvent; &logEvent($employer_id, $ENV{REMOTE_USER} || 'admin', "Event: Reminder", "Event: Sent materials reminder to " . ($emp->{company_name} || "employer #$employer_id"), $ccdbh, 0); ``` **b) send_bulk_reminder (line 82, after the loop, before print):** ```perl require "../admin/commonFuncs.pm" unless defined &logEvent; &logEvent(0, $ENV{REMOTE_USER} || 'admin', "Event: Bulk Reminder", "Event: Sent bulk reminders to $sent employers for event #$company_id", $ccdbh, 0); ``` **c) generate_task_token (line 86, inside success block):** ```perl require "../admin/commonFuncs.pm" unless defined &logEvent; &logEvent(0, $ENV{REMOTE_USER} || 'admin', "Event: Portal Token", "Event: Generated portal access token for task #$task_id", $ccdbh, 0); ``` **d) save_booth_rep (line 135, after `print` on success):** Insert before the closing `}` at line 135, after `print qq~{"success":true,"attendee_id":$id}~;`: ```perl my $rep_name = $q->param('name') || 'unknown'; my $eid = $q->param('employer_id'); my $action_word = $q->param('attendee_id') ? 'Updated' : 'Added'; require "../admin/commonFuncs.pm" unless defined &logEvent; &logEvent($eid, $ENV{REMOTE_USER} || 'admin', "Event: Booth Rep", "Event: $action_word booth rep: $rep_name", $ccdbh, 0); ``` **e) delete_booth_rep (line 139, after deleteEventAttendee):** ```perl my $eid = $q->param('employer_id') || 0; require "../admin/commonFuncs.pm" unless defined &logEvent; &logEvent($eid, $ENV{REMOTE_USER} || 'admin', "Event: Booth Rep", "Event: Deleted booth rep #$attendee_id", $ccdbh, 0); ``` **f) delete_employer_file (line 144, after deleteEmployerUpload):** ```perl my $eid = $q->param('employer_id') || 0; require "../admin/commonFuncs.pm" unless defined &logEvent; &logEvent($eid, $ENV{REMOTE_USER} || 'admin', "Event: Upload", "Event: Deleted employer file #$upload_id", $ccdbh, 0); ``` **g) generate_social_graphic (line 149) — add logging inside the `generateSocialGraphic()` sub after success.** Find the sub and add after the graphic is created: ```perl require "../admin/commonFuncs.pm" unless defined &logEvent; &logEvent($employer_id, $ENV{REMOTE_USER} || 'admin', "Event: Social", "Event: Generated social graphic for $attendee_name", $ccdbh, 0); ``` **h) send_social_graphic (line 151) — add logging inside `sendSocialGraphicEmail()` after success.** ```perl require "../admin/commonFuncs.pm" unless defined &logEvent; &logEvent($employer_id, $ENV{REMOTE_USER} || 'admin', "Event: Social", "Event: Emailed social graphic to $attendee_name ($email)", $ccdbh, 0); ``` **i) send_instructions (line 157) — add logging inside `sendInstructionsToPoCs()` after success.** ```perl require "../admin/commonFuncs.pm" unless defined &logEvent; &logEvent($employer_id, $ENV{REMOTE_USER} || 'admin', "Event: Instructions", "Event: Sent materials request to employer", $ccdbh, 0); ``` **j) save_employer_config (after the update):** ```perl require "../admin/commonFuncs.pm" unless defined &logEvent; &logEvent($employer_id, $ENV{REMOTE_USER} || 'admin', "Event: Config", "Event: Updated employer event configuration", $ccdbh, 0); ``` **k) upload_event_doc (line 222, after success print):** Already has the doc title and filename in scope: ```perl require "../admin/commonFuncs.pm" unless defined &logEvent; &logEvent(0, $ENV{REMOTE_USER} || 'admin', "Event: Document", "Event: Uploaded event doc: $title ($filename) for event #$company_id", $ccdbh, 0); ``` **l) delete_event_doc (line 227, after deleteEventDocument):** ```perl require "../admin/commonFuncs.pm" unless defined &logEvent; &logEvent(0, $ENV{REMOTE_USER} || 'admin', "Event: Document", "Event: Deleted event doc #$document_id", $ccdbh, 0); ``` **Step 2: Syntax check** Run: `cd /var/www/vhosts/hbcuconnect.com/httpdocs/events && perl -c event_sponsor_manager.cgi` Expected: `event_sponsor_manager.cgi syntax OK` **Step 3: Fix ownership** Run: `chown hbcuconnect:psacln /var/www/vhosts/hbcuconnect.com/httpdocs/events/event_sponsor_manager.cgi` **Step 4: Commit** ```bash git add events/event_sponsor_manager.cgi git commit -m "feat(events): add activity logging to all admin sponsor management actions Co-Authored-By: Claude Opus 4.6 " ``` --- ### Task 3: Add logging to index.cgi workflow actions **Files:** - Modify: `events/index.cgi:229-267` - Modify: `events/events.pl` (inside the workflow subs) **Step 1: Add logEvent calls inside events.pl workflow subs** The workflow actions (`requestEventStatusUpdate`, `sendSourcingProfiles`, `approveLandingPage`, `deactivateLandingPage`) are defined in `events.pl` and called from `index.cgi`. Add logging inside each sub after the action succeeds. **a) requestEventStatusUpdate (events.pl ~line 244):** After the email sends: ```perl require "../admin/commonFuncs.pm" unless defined &logEvent; my $ccdbh = getDBHandle('career_center'); &logEvent(0, $ENV{REMOTE_USER} || 'admin', "Event: Status Update", "Event: Requested status update for $event->{company_name}", $ccdbh, 0); ``` **b) sendSourcingProfiles (events.pl ~line 96):** After the email sends: ```perl require "../admin/commonFuncs.pm" unless defined &logEvent; &logEvent(0, $ENV{REMOTE_USER} || 'admin', "Event: Sourcing", "Event: Sent sourcing profiles to $sourcer->{name} for $event->{company_name}", $ccdbh, 0); ``` **c) approveLandingPage:** Find the sub, add after the UPDATE: ```perl require "../admin/commonFuncs.pm" unless defined &logEvent; my $ccdbh = getDBHandle('career_center'); &logEvent(0, $ENV{REMOTE_USER} || 'admin', "Event: Landing Page", "Event: Approved landing page for $event->{company_name}", $ccdbh, 0); ``` **d) deactivateLandingPage:** Same pattern: ```perl require "../admin/commonFuncs.pm" unless defined &logEvent; my $ccdbh = getDBHandle('career_center'); &logEvent(0, $ENV{REMOTE_USER} || 'admin', "Event: Landing Page", "Event: Deactivated landing page for $event->{company_name}", $ccdbh, 0); ``` **Step 2: Syntax check both files** Run: `cd /var/www/vhosts/hbcuconnect.com/httpdocs/events && perl -c events.pl && perl -c index.cgi` Expected: both show `syntax OK` **Step 3: Fix ownership** Run: `chown hbcuconnect:psacln /var/www/vhosts/hbcuconnect.com/httpdocs/events/events.pl /var/www/vhosts/hbcuconnect.com/httpdocs/events/index.cgi` **Step 4: Commit** ```bash git add events/events.pl events/index.cgi git commit -m "feat(events): add activity logging to admin workflow actions Co-Authored-By: Claude Opus 4.6 " ``` --- ### Task 4: Create the daily digest cron **Files:** - Create: `crons/staff/event_activity_digest.pl` **Step 1: Write the digest script** The script: 1. Connects to career_center DB 2. Queries `employer_history` for yesterday's `Portal:` and `Event:` entries 3. Queries `resume_recommendation_clients` for active events (event_date >= today OR within last 7 days) 4. Groups activity by event (company_id/company_name), then by employer 5. Identifies stale employers (no activity in 3+ days on active events) 6. Builds HTML email using the standard template from `memory/email_template.md` 7. Sends via `smtp_notify()` to `core@hbcuconnect.com` ```perl #!/usr/bin/perl use strict; use warnings; use CGI; use DBI; use POSIX qw(strftime); require '/var/www/vhosts/hbcuconnect.com/httpdocs/cgi-bin/lib/commonDB.pl'; require '/var/www/vhosts/hbcuconnect.com/httpdocs/cgi-bin/lib/niceMail.pl'; require '/var/www/vhosts/hbcuconnect.com/httpdocs/cgi-bin/lib/commonHTML.pl'; require '/var/www/vhosts/hbcuconnect.com/httpdocs/admin/commonFuncs.pm'; my $dbh = getDBHandle('hbcu_central'); my $ccdbh = getDBHandle('career_center'); my $today = strftime('%Y-%m-%d', localtime); my $yesterday = strftime('%Y-%m-%d', localtime(time - 86400)); ############################################################################### # 1. Get active events (upcoming or within last 7 days) ############################################################################### my $events_sql = qq{ SELECT rrc.company_id, rrc.company_name, rrc.event_date, rrc.event_type, rrc.sourcer, rrc.event_manager FROM resume_recommendation_clients rrc WHERE rrc.event_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND rrc.event_date IS NOT NULL ORDER BY rrc.event_date ASC }; my $events_sth = $dbh->prepare($events_sql); $events_sth->execute(); my @active_events; my %event_map; # company_id => event hash while (my $ev = $events_sth->fetchrow_hashref) { push @active_events, $ev; $event_map{$ev->{company_id}} = $ev; } unless (@active_events) { print "No active events. Skipping digest.\n"; exit; } ############################################################################### # 2. Get yesterday's event activity from employer_history ############################################################################### my $activity_sql = qq{ SELECT eh.employer_id, eh.contact, eh.event_summary, eh.event, eh.tstamp, ed.company_name AS employer_name FROM employer_history eh LEFT JOIN employer_data ed ON eh.employer_id = ed.employer_id WHERE (eh.event_summary LIKE 'Portal:%' OR eh.event_summary LIKE 'Event:%') AND DATE(eh.tstamp) = ? ORDER BY eh.tstamp ASC }; my $activity_sth = $ccdbh->prepare($activity_sql); $activity_sth->execute($yesterday); # We need to map activities to events. Activities with employer_id link to # event_employer_config or invoice_tasks to find company_id. # For simplicity, also collect any activities that have employer_id=0 (event-level actions). my @all_activities; my %employer_to_events; # employer_id => [company_ids] # Pre-build employer-to-event mapping from event_employer_config my $eec_sql = qq{ SELECT DISTINCT employer_id, company_id FROM event_employer_config WHERE company_id IN (} . join(',', map { int($_->{company_id}) } @active_events) . qq{) }; if (@active_events) { my $eec_sth = $ccdbh->prepare($eec_sql); $eec_sth->execute(); while (my $row = $eec_sth->fetchrow_hashref) { push @{$employer_to_events{$row->{employer_id}}}, $row->{company_id}; } } while (my $act = $activity_sth->fetchrow_hashref) { push @all_activities, $act; } # Group activities by event my %event_activities; # company_id => [activities] my %ungrouped; for my $act (@all_activities) { my $eid = $act->{employer_id}; if ($eid && $employer_to_events{$eid}) { for my $cid (@{$employer_to_events{$eid}}) { push @{$event_activities{$cid}}, $act; } } elsif ($act->{event} =~ /event #(\d+)/i) { # Extract company_id from event detail text push @{$event_activities{$1}}, $act; } else { push @{$ungrouped{$eid || 0}}, $act; } } ############################################################################### # 3. Get stale employers (no activity in 3+ days on active events) ############################################################################### my @stale_employers; my $stale_sql = qq{ SELECT eec.employer_id, eec.company_id, ed.company_name, MAX(eh.tstamp) AS last_activity FROM event_employer_config eec JOIN employer_data ed ON eec.employer_id = ed.employer_id LEFT JOIN employer_history eh ON eec.employer_id = eh.employer_id AND (eh.event_summary LIKE 'Portal:%' OR eh.event_summary LIKE 'Event:%') WHERE eec.company_id IN (} . join(',', map { int($_->{company_id}) } @active_events) . qq{) GROUP BY eec.employer_id, eec.company_id HAVING last_activity IS NULL OR last_activity < DATE_SUB(CURDATE(), INTERVAL 3 DAY) ORDER BY last_activity ASC LIMIT 20 }; if (@active_events) { my $stale_sth = $ccdbh->prepare($stale_sql); $stale_sth->execute(); while (my $row = $stale_sth->fetchrow_hashref) { $row->{event_name} = $event_map{$row->{company_id}}{company_name} if $event_map{$row->{company_id}}; push @stale_employers, $row; } } ############################################################################### # 4. Build email HTML ############################################################################### my $total_activities = scalar @all_activities; # Style constants my $S_HDR = 'background-color:#1e1d3a;color:#ffffff;font-weight:bold;font-size:12px;padding:10px 12px;'; my $S_CELL = 'padding:8px 12px;font-size:13px;border-bottom:1px solid #eee;'; my $S_LINK = 'color:#993300;text-decoration:none;font-weight:bold;'; my $BASE = 'https://hbcuconnect.com'; ## Section 1: Active Events Quick Links my $events_html = ''; for my $ev (@active_events) { my $date_fmt = $ev->{event_date} ? formatEventDate($ev->{event_date}) : 'TBD'; my $type = $ev->{event_type} || 'Event'; my $cid = $ev->{company_id}; my $name = _html_esc($ev->{company_name}); my $act_count = scalar(@{$event_activities{$cid} || []}); my $badge = $act_count ? qq{$act_count} : qq{0}; $events_html .= qq{ $name$badge $date_fmt $type Manage → \n}; } ## Section 2: Activity Feed my $activity_html = ''; for my $ev (@active_events) { my $cid = $ev->{company_id}; my $acts = $event_activities{$cid}; my $name = _html_esc($ev->{company_name}); my $date_fmt = $ev->{event_date} ? formatEventDate($ev->{event_date}) : 'TBD'; $activity_html .= qq{$name ($date_fmt)\n}; if ($acts && @$acts) { for my $act (@$acts) { my $time = $act->{tstamp}; $time =~ s/^\d{4}-\d{2}-\d{2}\s//; # strip date, keep time my $who = _html_esc($act->{contact} || 'unknown'); my $detail = _html_esc($act->{event} || $act->{event_summary}); my $emp_name = _html_esc($act->{employer_name} || ''); $activity_html .= qq{ $time $detail $who \n}; } } else { $activity_html .= qq{No activity yesterday\n}; } } ## Section 3: Stale employers my $stale_html = ''; if (@stale_employers) { for my $s (@stale_employers) { my $last = $s->{last_activity} ? substr($s->{last_activity}, 0, 10) : 'never'; my $emp_name = _html_esc($s->{company_name}); my $ev_name = _html_esc($s->{event_name} || "event #$s->{company_id}"); $stale_html .= qq{• $emp_name — $ev_name — last activity: $last\n}; } } ## Assemble full email my $yesterday_fmt = strftime('%B %e, %Y', localtime(time - 86400)); $yesterday_fmt =~ s/\s+/ /g; my $html = qq{
HBCU Connect
 

Event Activity Digest

$yesterday_fmt — $total_activities total activities

Active Events

$events_html
Event Date Type Action

Yesterday’s Activity

$activity_html
}; if (@stale_employers) { $html .= qq{

⚠ Employers with No Activity (3+ Days)

$stale_html
}; } $html .= qq{
Daily event activity digest from HBCU Connect event management system.
}; ############################################################################### # 5. Send email ############################################################################### my $subject = "Event Activity Digest — $yesterday_fmt"; # Use plain text in subject line (no HTML entities) my $subject_plain = "Event Activity Digest - " . strftime('%B %e, %Y', localtime(time - 86400)); $subject_plain =~ s/\s+/ /g; require '/var/www/vhosts/hbcuconnect.com/httpdocs/cgi-bin/lib/config.pl'; my $CONF = &getConfiguration(); smtp_notify("","","", 'core@hbcuconnect.com', # to 'noreply@hbcuconnect.com', # from $subject_plain, # subject $html, # text (unused, html takes precedence) 'HBCU Connect Events', # from_name '', # cc $html # html ); print "Event activity digest sent. $total_activities activities across " . scalar(@active_events) . " active events.\n"; ############################################################################### # Utility ############################################################################### sub _html_esc { my $s = shift || ''; $s =~ s/&/&/g; $s =~ s//>/g; $s =~ s/"/"/g; return $s; } sub formatEventDate { my $d = shift; return '' unless $d; my @months = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec); if ($d =~ /^(\d{4})-(\d{2})-(\d{2})/) { return $months[$2-1] . " $3, $1"; } return $d; } ``` **Step 2: Syntax check** Run: `cd /var/www/vhosts/hbcuconnect.com/httpdocs/crons/staff && perl -c event_activity_digest.pl` Expected: `event_activity_digest.pl syntax OK` **Step 3: Fix ownership** Run: `chown hbcuconnect:psacln /var/www/vhosts/hbcuconnect.com/httpdocs/crons/staff/event_activity_digest.pl` **Step 4: Test run** Run: `cd /var/www/vhosts/hbcuconnect.com/httpdocs/crons/staff && perl event_activity_digest.pl` Expected: Either "No active events" or "Event activity digest sent" **Step 5: Commit** ```bash git add crons/staff/event_activity_digest.pl git commit -m "feat(events): add daily event activity digest cron Sends to core@hbcuconnect.com at 7am with: - Active events quick links to event_sponsor_manager.cgi - Yesterday's activity feed grouped by event/employer - Stale employer warnings (no activity in 3+ days) Co-Authored-By: Claude Opus 4.6 " ``` --- ### Task 5: Add crontab entry **Step 1: Add the cron entry** Run: `crontab -l` to see current format, then add: ``` 0 7 * * * cd /var/www/vhosts/hbcuconnect.com/httpdocs/crons/staff && perl event_activity_digest.pl >> /var/log/cron-jobs/event_activity_digest.log 2>&1 ``` **Step 2: Verify** Run: `crontab -l | grep event_activity` Expected: shows the new entry --- ## Notes - `logEvent()` has built-in same-day duplicate detection (same employer_id + contact + summary + event = skip). This is fine for our use case but means if the same employer performs the exact same action twice in a day, only the first is logged. - The digest email uses `smtp_notify()` (not `html_notify()`) to go through SendGrid SMTP reliably. - The stale employer query is capped at 20 results to keep the email manageable. - `formatEventDate()` is defined locally in the cron script since it runs standalone. `events.pl` has its own copy but would require CGI context to load.