Having recently completed a deep-dive integration between AuditBoard and our internal notification systems, I've developed a workflow that moves beyond simple email alerts. The core problem with native notifications is their lack of context and inability to trigger conditional, multi-channel actions based on the *state* of a control test, not just its completion. My solution leverages AuditBoard's API, a PostgreSQL trigger function, and a message queue to create a dynamic, event-driven notification hub.
The architecture hinges on treating the `control_test` table in the AuditBoard PostgreSQL database (your replica, of course, not the primary) as the source of truth. A trigger fires on `UPDATE` operations, capturing the old and new values of key fields like `status`, `due_date`, and `owner_id`. The logic then decides on the required action.
Here is the core trigger function, simplified for clarity:
```sql
CREATE OR REPLACE FUNCTION auditboard_control_test_notify()
RETURNS TRIGGER AS $$
DECLARE
payload JSONB;
BEGIN
-- Only proceed if status, due_date, or owner has changed
IF (OLD.status IS DISTINCT FROM NEW.status) OR
(OLD.due_date IS DISTINCT FROM NEW.due_date) OR
(OLD.owner_id IS DISTINCT FROM NEW.owner_id) THEN
payload := jsonb_build_object(
'event_type', 'control_test_update',
'old', to_jsonb(OLD),
'new', to_jsonb(NEW),
'changed_at', NOW()
);
-- Send to a message queue via PG_NOTIFY for external consumers
PERFORM pg_notify('auditboard_events', payload::text);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER control_test_change_trigger
AFTER UPDATE ON control_test
FOR EACH ROW
EXECUTE FUNCTION auditboard_control_test_notify();
```
This event stream (`auditboard_events`) is consumed by a service which applies the business logic for notifications:
* **State Transition Mapping:** A status change from `In Progress` to `Ready for Review` generates a Slack message to the reviewer *and* creates a follow-up calendar event, while a change to `Failed` triggers a high-priority Teams message to the control owner and their manager, logging the incident in a separate tracking database.
* **Proactive Escalation:** The service also monitors `due_date`. If a test remains `In Progress` within 48 hours of the due date, an automated reminder is sent, with the notification channel (email vs. SMS) determined by the owner's role.
* **Context Enrichment:** The service joins the event data with other internal systems (HR directory, project management) to append information like substitute contacts during PTO or related project milestones.
Key advantages over a simple polling script or relying solely on AuditBoard's built-in features:
* **Decoupling:** The notification logic is entirely separate from AuditBoard, allowing for complex, organization-specific rules without customization constraints.
* **Audit Trail:** Every triggered event, its payload, and the resulting actions are logged to a separate table, providing a clear audit trail of the notification system itself.
* **Scalability:** The message queue pattern allows for adding new consumers easily. For example, we've added a consumer that populates a real-time dashboard of control test health metrics in Grafana.
The primary pitfalls to consider are the initial setup of the database replication (which requires careful handling of schema changes) and ensuring idempotency in your notification service to handle potential duplicate events from the message queue. For teams without direct database access, a similar pattern could be achieved by polling the AuditBoard API, though with increased latency and load. This workflow has reduced our mean time to acknowledgment for failed controls by approximately 65%, primarily by removing manual steps and context-switching for auditors.
You've chosen a solid foundation with a database trigger to capture state changes. However, this approach couples your notification logic directly to the database's transaction lifecycle, which can introduce latency and block the originating operation if the trigger logic becomes complex or the message queue experiences backpressure. Have you considered decoupling this by using Change Data Capture, like Debezium, to stream the table's change events to Kafka? This would keep the transactional path clean and allow you to replay events if your notification service fails. It also moves the conditional routing logic out of the trigger and into a dedicated service, making it easier to test and modify without touching the production database schema.
Data doesn't lie, but folks sometimes do.
Nice! The trigger approach is a pragmatic first step. I used a similar pattern last year before we moved to CDC.
One gotcha we ran into: if your notification logic does any external API calls (like enriching owner details), doing that inside the trigger can seriously slow down the DB transaction. We ended up moving just the payload assembly into the trigger and pushing the actual notification logic to a queue worker.
Your `IS DISTINCT FROM` check is smart - handles nulls nicely. Might be worth adding `last_reminder_sent_at` to that condition if you're planning on escalations for overdue items.
Prompt engineering is the new debugging
Interesting approach. I've been looking at AuditBoard's webhook options for similar notifications. How does this trigger method compare to using their official API events, especially for avoiding the replica sync lag? Also, do you handle retries if the message queue fails, or does the trigger fire only once?
This is really cool! The trigger approach makes sense for capturing those specific field changes. I'm just starting out with Postgres, so I have a rookie question: how do you manage the trigger function itself? Like, if you need to update the logic later, is there a deployment process for that, or do you just run the `CREATE OR REPLACE` manually against the replica? Thanks for sharing this!
Your decision to use `IS DISTINCT FROM` for the null-aware comparison is the correct one. Many developers initially use ``, which fails when either operand is null, silently missing state transitions from or to a null value. This is a subtle but critical detail in audit data where an owner being assigned (`NULL` -> `user_id`) is a significant event.
However, the conditional logic you've shown only checks `status` and `due_date`. If the notification logic depends on a change in `owner_id`, as your text mentions, that predicate must also be included in the `IF` statement. Otherwise, reassigning a test won't trigger the notification flow.
Latency is the enemy