Authoritative inventory of active PostgreSQL table triggers, statement coalescing, and cache synchronizations.

Database Triggers

Attendrix uses PostgreSQL triggers to maintain data denormalization, audit logs, updated timestamps, and statement-coalesced event queues.

CALENDAR PIPELINE INVARIANT

INV-GCAL-01

Calendar reconciliation triggers must fire at the SQL statement level (STATEMENT), never row-by-row. Bulk class generation or timetable imports produce a single coalesced queue job rather than thousands of individual events.

Core Statement & Row Triggers

1. Statement-Coalesced Google Calendar Trigger (`classes`)

Trigger: fn_classes_gcal_statement_coalesce
Table: public.classes
Timing / Event: AFTER INSERT OR UPDATE OR DELETE
Orientation: STATEMENT
Purpose: When an administrator inserts, modifies, or cancels classes in bulk, row-level triggers would flood gcal_sync_queue with hundreds of redundant sync events. The statement-level trigger aggregates affected batch IDs within the transaction and enqueues a single coalesced synchronization request.

2. Absence State Cache Sync (`absences`)

Trigger: trg_touch_user_on_absence
Table: public.absences
Timing / Event: AFTER INSERT OR DELETE
Orientation: ROW
Purpose: Touches users.updated_at and invalidates the cached attendance percentage in user_course_attendance_state, forcing immediate re-aggregation upon the next client query.

3. User Enrollment Cache Synchronization (`user_course_enrollments`)

Trigger: trg_sync_uce_to_jsonb
Table: public.user_course_enrollments
Timing / Event: AFTER INSERT OR UPDATE OR DELETE
Orientation: ROW
Purpose: Synchronizes the relational enrollment rows into the denormalized users.enrolled_courses JSONB column for single-read mobile profile hydration.

Active Triggers Inventory

The following 91 triggers are compiled from the live PostgreSQL database catalog:

Target Table	Trigger Name	Timing	Event	Orientation	Statement / Action
`absences`	`trg_absences_touch_user`	`AFTER`	`UPDATE`	`ROW`	`EXECUTE FUNCTION touch_user_on_absence_change()`
`absences`	`trg_absences_touch_user`	`AFTER`	`DELETE`	`ROW`	`EXECUTE FUNCTION touch_user_on_absence_change()`
`absences`	`trg_absences_touch_user`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION touch_user_on_absence_change()`
`academic_calendar_events`	`trg_audit_academic_calendar_events`	`AFTER`	`DELETE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`academic_calendar_events`	`trg_audit_academic_calendar_events`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`academic_calendar_events`	`trg_audit_academic_calendar_events`	`AFTER`	`UPDATE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`academic_calendar_events`	`trg_set_academic_event_formatted_date`	`BEFORE`	`INSERT`	`ROW`	`EXECUTE FUNCTION trg_auto_format_academic_event_date()`
`academic_calendar_events`	`trg_set_academic_event_formatted_date`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION trg_auto_format_academic_event_date()`
`achievement_progress`	`trg_achievement_progress_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`achievement_progress`	`trg_audit_achievement_progress`	`AFTER`	`UPDATE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`achievement_progress`	`trg_audit_achievement_progress`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`achievement_progress`	`trg_audit_achievement_progress`	`AFTER`	`DELETE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`achievement_templates`	`trg_achievement_templates_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`achievement_templates`	`trg_audit_achievement_templates`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`achievement_templates`	`trg_audit_achievement_templates`	`AFTER`	`UPDATE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`achievement_templates`	`trg_audit_achievement_templates`	`AFTER`	`DELETE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`amplix_ledger`	`trg_audit_amplix_ledger`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`app_version_control`	`trg_app_version_control_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`app_version_control`	`trg_audit_app_version_control`	`AFTER`	`DELETE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`app_version_control`	`trg_audit_app_version_control`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`app_version_control`	`trg_audit_app_version_control`	`AFTER`	`UPDATE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`app_version_control`	`trg_enforce_single_current_app_version`	`BEFORE`	`INSERT`	`ROW`	`EXECUTE FUNCTION enforce_single_current_app_version()`
`app_version_control`	`trg_enforce_single_current_app_version`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION enforce_single_current_app_version()`
`batch_courses`	`trg_check_batch_course_id_unique`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION check_batch_course_id_unique()`
`batch_courses`	`trg_check_batch_course_id_unique`	`BEFORE`	`INSERT`	`ROW`	`EXECUTE FUNCTION check_batch_course_id_unique()`
`batch_courses`	`trg_generate_batch_course_id`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION batch_courses_generate_course_id()`
`batch_courses`	`trg_generate_batch_course_id`	`BEFORE`	`INSERT`	`ROW`	`EXECUTE FUNCTION batch_courses_generate_course_id()`
`batch_courses`	`trg_sync_batch_course_meetings`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION sync_batch_course_meetings_trigger()`
`batch_courses`	`trg_sync_batch_course_meetings`	`AFTER`	`UPDATE`	`ROW`	`EXECUTE FUNCTION sync_batch_course_meetings_trigger()`
`batches`	`trg_batches_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`bus_timings`	`trg_bus_timings_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`buses`	`trg_buses_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`challenge_progress`	`trg_audit_challenge_progress`	`AFTER`	`DELETE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`challenge_progress`	`trg_audit_challenge_progress`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`challenge_progress`	`trg_audit_challenge_progress`	`AFTER`	`UPDATE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`challenge_progress`	`trg_challenge_progress_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`challenge_progress_classes`	`trg_audit_challenge_progress_classes`	`AFTER`	`UPDATE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`challenge_progress_classes`	`trg_audit_challenge_progress_classes`	`AFTER`	`DELETE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`challenge_progress_classes`	`trg_audit_challenge_progress_classes`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`challenge_templates`	`trg_audit_challenge_templates`	`AFTER`	`UPDATE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`challenge_templates`	`trg_audit_challenge_templates`	`AFTER`	`DELETE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`challenge_templates`	`trg_audit_challenge_templates`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`challenge_templates`	`trg_challenge_templates_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`challenge_templates`	`trg_validate_challenge_params`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION validate_challenge_params()`
`challenge_templates`	`trg_validate_challenge_params`	`BEFORE`	`INSERT`	`ROW`	`EXECUTE FUNCTION validate_challenge_params()`
`class_cancellation_reports`	`trg_reevaluate_community_cancellation`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION internal_reevaluate_community_cancellation()`
`class_cancellation_reports`	`trg_reevaluate_community_cancellation`	`AFTER`	`DELETE`	`ROW`	`EXECUTE FUNCTION internal_reevaluate_community_cancellation()`
`classes`	`tr_classes_gcal_statement_coalesce_del`	`AFTER`	`DELETE`	`STATEMENT`	`EXECUTE FUNCTION fn_classes_gcal_statement_coalesce()`
`classes`	`tr_classes_gcal_statement_coalesce_ins`	`AFTER`	`INSERT`	`STATEMENT`	`EXECUTE FUNCTION fn_classes_gcal_statement_coalesce()`
`classes`	`tr_classes_gcal_statement_coalesce_upd`	`AFTER`	`UPDATE`	`STATEMENT`	`EXECUTE FUNCTION fn_classes_gcal_statement_coalesce()`
`classes`	`trg_classes_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`classes`	`trg_generate_class_ref`	`BEFORE`	`INSERT`	`ROW`	`EXECUTE FUNCTION generate_class_ref()`
`classes`	`trg_generate_class_ref`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION generate_class_ref()`
`classes`	`trg_notify_class_events`	`AFTER`	`UPDATE`	`ROW`	`EXECUTE FUNCTION fn_notify_class_events()`
`classes`	`trg_notify_class_events`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION fn_notify_class_events()`
`courses`	`trg_courses_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`elective_courses`	`trg_audit_elective_courses`	`AFTER`	`UPDATE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`elective_courses`	`trg_audit_elective_courses`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`elective_courses`	`trg_audit_elective_courses`	`AFTER`	`DELETE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`elective_courses`	`trg_check_elective_course_id_unique`	`BEFORE`	`INSERT`	`ROW`	`EXECUTE FUNCTION check_elective_course_id_unique()`
`elective_courses`	`trg_check_elective_course_id_unique`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION check_elective_course_id_unique()`
`elective_courses`	`trg_elective_courses_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`elective_courses`	`trg_generate_elective_course_id`	`BEFORE`	`INSERT`	`ROW`	`EXECUTE FUNCTION generate_elective_course_id()`
`elective_courses`	`trg_generate_elective_course_id`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION generate_elective_course_id()`
`elective_courses`	`trg_validate_elective_slot`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION validate_elective_slot()`
`elective_courses`	`trg_validate_elective_slot`	`BEFORE`	`INSERT`	`ROW`	`EXECUTE FUNCTION validate_elective_slot()`
`mess_menu`	`trg_mess_menu_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`messes`	`trg_messes_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`semesters`	`trg_semesters_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`slot_occurrences`	`trg_slot_occurrences_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`student_task_records`	`trg_audit_student_task_records`	`AFTER`	`UPDATE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`student_task_records`	`trg_audit_student_task_records`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`student_task_records`	`trg_audit_student_task_records`	`AFTER`	`DELETE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`student_task_records`	`trg_student_task_records_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`tasks`	`trg_audit_tasks`	`AFTER`	`DELETE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`tasks`	`trg_audit_tasks`	`AFTER`	`UPDATE`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`tasks`	`trg_audit_tasks`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION audit_trigger()`
`tasks`	`trg_notify_task_events`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION fn_notify_task_events()`
`tasks`	`trg_notify_task_events`	`AFTER`	`DELETE`	`ROW`	`EXECUTE FUNCTION fn_notify_task_events()`
`tasks`	`trg_notify_task_events`	`AFTER`	`UPDATE`	`ROW`	`EXECUTE FUNCTION fn_notify_task_events()`
`tasks`	`trg_tasks_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`user_course_enrollments`	`trg_enforce_single_offering_per_course`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION enforce_single_offering_per_course()`
`user_course_enrollments`	`trg_enforce_single_offering_per_course`	`BEFORE`	`INSERT`	`ROW`	`EXECUTE FUNCTION enforce_single_offering_per_course()`
`user_course_enrollments`	`trg_uce_sync_jsonb`	`AFTER`	`DELETE`	`ROW`	`EXECUTE FUNCTION trg_sync_uce_to_jsonb_cache()`
`user_course_enrollments`	`trg_uce_sync_jsonb`	`AFTER`	`UPDATE`	`ROW`	`EXECUTE FUNCTION trg_sync_uce_to_jsonb_cache()`
`user_course_enrollments`	`trg_uce_sync_jsonb`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION trg_sync_uce_to_jsonb_cache()`
`user_course_enrollments`	`trg_validate_uce_referential_integrity`	`BEFORE`	`INSERT`	`ROW`	`EXECUTE FUNCTION validate_uce_referential_integrity()`
`user_course_enrollments`	`trg_validate_uce_referential_integrity`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION validate_uce_referential_integrity()`
`user_preferences`	`trg_user_preferences_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_updated_at()`
`users`	`trg_create_user_preferences`	`AFTER`	`INSERT`	`ROW`	`EXECUTE FUNCTION fn_create_user_preferences()`
`users`	`trg_users_profile_updated_at`	`BEFORE`	`UPDATE`	`ROW`	`EXECUTE FUNCTION set_profile_updated_at()`

Database Triggers

Database Triggers

Core Statement & Row Triggers

1. Statement-Coalesced Google Calendar Trigger (`classes`)

2. Absence State Cache Sync (`absences`)

3. User Enrollment Cache Synchronization (`user_course_enrollments`)

Active Triggers Inventory

Was this page helpful?

On this page

Database Triggers

Database Triggers

Core Statement & Row Triggers

1. Statement-Coalesced Google Calendar Trigger (classes)

2. Absence State Cache Sync (absences)

3. User Enrollment Cache Synchronization (user_course_enrollments)

Active Triggers Inventory

Was this page helpful?

On this page

1. Statement-Coalesced Google Calendar Trigger (`classes`)

2. Absence State Cache Sync (`absences`)

3. User Enrollment Cache Synchronization (`user_course_enrollments`)