In the function checkProcessedDatetime, we check if a invite has been processed already with SELECT processed_datetime FROM ut_invitation_api_data WHERE mefe_invitation_id=?

We've come to notice, that this table can contain ids, when in fact the operation failed.

 Oct 19th 11:05:24am INFO production 5b8ec68 Running 1_process_one_invitation_all_scenario_v3.0.sql with invite id N8ANmdnuyNxiiWTWw with env 1
  Oct 19th 11:05:26am ERRO production 5b8ec68 running sql failed: error=Error 1452: Cannot add or update a child row: a foreign key constraint fails (`bugzilla
`.`bugs_activity`, CONSTRAINT `fk_bugs_activity_bug_id_bugs_bug_id` FOREIGN KEY (`bug_id`) REFERENCES `bugs` (`bug_id`) ON DELETE CASCADE ON UPDATE CASCADE)
  Oct 19th 11:05:26am ERRO production 5b8ec68 failed to run 1_process_one_invitation_all_scenario_v3.0.sql: error=Error 1452: Cannot add or update a child row:
 a foreign key constraint fails (`bugzilla`.`bugs_activity`, CONSTRAINT `fk_bugs_activity_bug_id_bugs_bug_id` FOREIGN KEY (`bug_id`) REFERENCES `bugs` (`bug_id
`) ON DELETE CASCADE ON UPDATE CASCADE) invite=map[type:type_assigned unitId:643 _id:N8ANmdnuyNxiiWTWw caseId:0 invitedBy:995 invitee:996 isOccupant:false role
:Contractor]
  Oct 19th 11:05:26am ERRO production 5b8ec68 response: duration=2.043s id=d289bce2-d34b-11e8-b426-d16c95d0b2f1 ip=54.255.152.207 method=GET path=/ size=285 B
status=500

Currently our database is publicly accessible (by IP) since Lambda's sourced connection cannot be assigned to a security group.

Furthermore the lambdas (internal) endpoints (via API gateway) are public, since there is no easy way to protect them.

IIUC we need Apex up to support VPC to unblock us here.

Refactor to remove queue

As we can see in https://travis-ci.org/unee-t/invite/builds/481250871 as well as:

[hendry@t480s invite]$ make demo

apex -r ap-southeast-1 --env demo deploy
   • updating config           env=demo function=invite
   ⨯ Error: function invite: InvalidParameterValueException: The provided execution role does not have permissions to call CreateNetworkInterface on EC2
        status code: 400, request id: ae642f67-1afd-11e9-ab3b-dfcebef64325
make: *** [Makefile:13: demo] Error 1

As documented in the README.md.

Not just cater for goreleaser:

[hendry@t480s db]$ curl -H "Authorization: Bearer SECRET" https://invite.dev.unee-t.com/version
dev, commit none, built at unknown

Simplify the SQL call https://github.com/unee-t/invite/blob/master/sql/invite_user_in_a_role_in_a_unit.sql

Use the SQL procedure introduced in the BZ DB schema version 3.20 and after the 2 variables the script needs have been defined do
CALL add_user_to_role_in_unit;

This should make the SQL significantly faster too

The problem:

The API uses the string version of the roles (see

Consequence of that issue:

• When we want to edit the designation of the roles, we need to make sure that the table ut_role_types uses the new values for the roles.
• This will make it more complex to localize the application (have different languages).

• version
• db check

I tried to run the procedure tonight, got this error message:

What I need to do:

• In the PROD environment
• Create a new user with the Unee-T Enterprise interface.

The payload:

CALL mysql.lambda_asyncarn:aws:lambda:ap-southeast-1:192458993663:function:alambda_simple{"lastName": "DO Ramasamy", "creatorId": "[sanitized]", "firstName": "Vani", "actionType": "CREATE_USER", "phoneNumber": "", "emailAddress": "[sanitized]@[santized].com", "mefeAPIRequestId": "de6bf82b-966b-11e9-b124-02012a2c6c9a", "userCreationRequestId": 883}

Expected result:

• user is created in Mongo - PASSED
• User is created in BZ - PASSED
• Unee-T Enterprise database is updated - FAILED

The problem:

The Unee-T enterprise database is NOT updated as it should after the user is created.

After we create a unit, we need to be able to invite users to the existing roles for this unit:

• Tenant,
• Landlord
• Contractor
• Management Company
• Agent

We need to have a dedicated script for that.
This script will call many (but not all) of the same:

• procedures
• variables
  that we use for the script to invite a user to case.

Payload map[_id:YrtHZP7TgaRYBaBDJ caseId:0 invitedBy:638 invitee:542 isOccupant:false mefeInvitationIdIntValue:37355 role:Management Company type:remove_user unitId:1108]

In order to improve security we need to make sure that the lambda function which performs the insert is run by a db user with limited privileges.
Ideally this user should only be able to insert data in the table 'ut_invitation_api_data' and nothing else.