Editing SNS (section)

== pg_cron Failure Alerts via Lambda and SNS ==
This setup ensures that failures in pg_cron jobs are detected and summarized by a Lambda function. The Lambda publishes a single consolidated alert to an Amazon SNS topic, which then delivers the alert to subscribed email addresses.

=== Architecture ===
# '''pg_cron jobs''' → Log failures in PostgreSQL. <code>'''select''' * '''from''' cron.job_run_details ''jrd'' '''where''' ''jrd''.status = 'failed'</code>
# '''AWS Lambda function''' → Queries the database for recent failures and generates a summary. '''<code>postgreCron_logs</code>'''
# '''Amazon SNS topic (pgcron-failure-alerts)''' → Receives the alert from Lambda. <code>pgcron-failure-alerts</code>
# '''SNS Subscriptions''' → Deliver the alert to multiple email addresses. 
# '''EventBridge Scheduler''' → Triggers the Lambda function daily at '''8:15 AM'''. 

==== 1. Lambda Function ====

==== A Lambda function was created with the following responsibilities: ====

* Connect to PostgreSQL database.
* Query job failures in the last 25 hours (lookback period configurable).
* Consolidate all failures into a '''single summary message'''.
* Publish the summary to SNS.

Sample log output<blockquote><code>🚨[FAILURE]🚨 runid=62994 jobid=105 status=failed cmd=REFRESH MATERIALIZED VIEW kotahi.mv_tta_clients</code>

<code>🚨[FAILURE]🚨 runid=62999 jobid=130 status=failed cmd=REFRESH MATERIALIZED VIEW kotahi.mvw_tta_report</code>

<code>🚨[FAILURE]🚨 runid=63009 jobid=76 status=failed cmd=select * from rpt.fn_risk_strat()</code>

<code>🚨[FAILURE]🚨 runid=63130 jobid=132 status=failed cmd=REFRESH MATERIALIZED VIEW CONCURRENTLY kotahi.mv_tta_report</code>

<code>[ALERT] Summary sent to SNS topic. count=4</code></blockquote>

==== 2. SNS Topic ====
'''Topic ARN:''' arn:aws:sns:ap-southeast-2:516630523714:pgcron-failure-alerts

The Lambda IAM role was granted permission to '''publish to this SNS topic''' using this policy:<blockquote><code>{</code>

<code>  "Version": "2012-10-17",</code>

<code>  "Statement": [</code>

<code>    {</code>

<code>      "Sid": "AllowPublishToPgCronTopic",</code>

<code>      "Effect": "Allow",</code>

<code>      "Action": "sns:Publish",</code>

<code>      "Resource": "arn:aws:sns:ap-southeast-2:516630523714:pgcron-failure-alerts"</code>

<code>    }</code>

<code>  ]</code>

<code>}</code></blockquote>

==== 3. Email Subscriptions ====


Subscription Steps:<blockquote><code>aws sns subscribe \</code>

<code>  --topic-arn arn:aws:sns:ap-southeast-2:516630523714:pgcron-failure-alerts \</code>

<code>  --protocol email \</code>

<code>  --notification-endpoint xxxxx@kautepasifika.co.nz</code>

<code>aws sns subscribe \</code>

<code>  --topic-arn arn:aws:sns:ap-southeast-2:516630523714:pgcron-failure-alerts \</code>

<code>  --protocol email \</code>

<code>  --notification-endpoint yyyyyy@kautepasifika.co.nz</code></blockquote>