We need a method for the BZFE to signal the MEFE each time something relevant happens

[franck-boullier]

As part of the MVP, we know that some interactions will be triggered in the Bugzilla Front End / BZFE first (the Unee-T dashboard).

The first thing we need to do is to send an alert to the relevant user(s) of the MEFE (email and/or SMS) each time a bug/case is updated in a product this MEFE user has been invited to.

We need to better understand how we can do that.

This is a show stopper: we can't have the BZFE user invite anyone to use the Unee-T MEFE (case.unee-t.com) without this.

@sebdeckers that'll be cool if you can have a go at this :-)

[franck-boullier]

Clarification: we do NOT want to capture events that happen solely in the BZFE and which can't be seen/refelected/displayed in the MEFE
BUT
We need to have a solution where an interaction that happens in the BZFE updates something in the MEFE.

Example:

• A new message is added via the BZFE
• Status is changed
• A solution is created
• Assignee is changed
• etc...

[kaihendry]

I highly recommend we design a system of messages based on AWS SNS. They are easy to subscribe to and work with. The best way in my opinion to trigger a lambda function to do something.

I have been thinking how both the front end and back end could start relaying events to SNS for either to subscribe to. For example with unee-t/bugzilla-customisation#46 it's silly of me to poll api/pending-invitations/done?accessToken=blablabla which I think I need to do since the Frontend doesn't have a way to a ready made way to hook into or rather subscribe to events in SNS parlance.

[franck-boullier]

@kaihendry I REALLY like the SNS idea.

Each time a case is successfully updated, record(s) are added to the table 'bugs_activity' to log these changes.

Maybe we can monitor this table and use the insert events into this table as a trigger to send the notifications we need??

[franck-boullier]

• bug_id -> case id
• who -> the user id in the BZFE
• fieldid -> a FK to the table that stores information on all the fields for a case
• added -> new value for the field
• removed -> old value for the field

[franck-boullier]

as for unee-t/bugzilla-customisation#46 this should also be possible to have the MEFE use SNS to trigger the scripts we need...

[franck-boullier]

@kaihendry
I stumbled upon this: https://aws.amazon.com/blogs/database/capturing-data-changes-in-amazon-aurora-using-aws-lambda/

It seems that we could use a lambda function to do what we need to capture changes directly from the BZ database.

Pre-requisite seem that we migrate from MariaDb to AWS Aurora which we probably want to test first but which does not seem that much of a stretch as Aurora is supposed to be fully compatible with MariaDb/MySQL...

I appears to me that this could 'relatively' rapidly solve our notification issue in a rather elegant way...
What do you think?

[franck-boullier]

"Pre-requisite seem that we migrate from MariaDb to AWS Aurora "
This might not even be necessary apparently if we look at the details of how this could be done:
1- Create a DB procedure which calls the lambda function
2- Create a trigger which calls this procedure each time a new record is added to the table we need to monitor
🤔

[franck-boullier]

We can have a table in the BZ DB (Aurora - Unee-T Db schema v3.0) which is updated each time something changes in a case:

• Case is created
• Case is modified
• A new comment is added.

Table looks like this

We 'just' need to have the relevant Lambda function’s ARN to finish implementing the SQL steps described there https://aws.amazon.com/blogs/database/capturing-data-changes-in-amazon-aurora-using-aws-lambda/

This will give us a way to have notification and use AWS to send these where we need these to go