Engagement Scoring & At-Risk Automation

Kononia features a dual-layer tracking system to monitor member activity: a real-time event-driven trigger system for attendance monitoring, and a nightly batch calculation for complex engagement analytics.

---

1. Real-Time Attendance & At-Risk Tracking

The database monitors attendance records immediately as they are created or modified to flag members who are missing.

Trigger Mechanism (trg_update_attendance_stats)

This trigger runs AFTER INSERT on public.attendance_records to execute the function update_person_attendance_stats().

• Present / Late Record: If a person’s status is logged as 'present' or 'late', the system:
  1. Sets consecutive_absences to 0.
  2. Sets at_risk_flag to false.
  3. Updates last_attendance_at to the check-in time or the attendance date.
• Absent Record: If a person’s status is logged as 'absent', the system:
  1. Lookups the organization’s custom warning threshold in public.org_settings_kv (under key 'absence_alert_threshold'). If not configured, it defaults to 3 absences.
  2. Increments consecutive_absences by 1.
  3. Sets at_risk_flag to true if the new absence count meets or exceeds the threshold.

---

2. Nightly Engagement Analytics (people_engagement_scores)

For more complex trends (such as donation patterns, volunteer frequency, and class participation), Kononia uses a dedicated materialized view.

• Storage Entity: people_engagement_scores
• Execution Schedule: Refreshed nightly via pg_cron.
• Performance Design: High-complexity aggregations (e.g., combining last 90 days of attendance, total donations, and active group memberships) are separated from the main transactional tables. This ensures search operations and dashboard renders remain fast during peak Sunday school/liturgy hours.

---

3. Automated Pastoral Case Generation

When a member is flagged as “At Risk” due to consecutive absences, the system automatically escalates the issue to local servants.

• Trigger Function: auto_generate_absence_pastoral_cases()
• Schedule: Executed nightly via pg_cron.
• Automation Logic:
  1. Identifies all people with at_risk_flag = true who do not have an active, open pastoral case of type auto_absence.
  2. Resolves their household’s assigned shepherd via families.assigned_servant_id.
  3. Automatically inserts a new record into public.pastoral_cases with a priority level of 'urgent' (if consecutive absences are $\ge 6$) or 'normal'.
  4. Notes are initialized automatically, notifying the servant to log a home visit or phone call.