Are these API endpoints just for the UI, or do you need them for non-UI things? If UI only I'm tempted to include them in the UI component.
...and...
- Searchable/filterable on some common fields ie recipient, sender, date, subject via api endpoint
It'll have to be via API since there is no way the frontend could handle that much data, and we wouldn't want to load that much every time anyway, so we have to paginate every return from the API
Here is an example of an API endpoint that handles search and pagination: https://github.com/fagersta/portal/blob/master/route_api_workorders.go#L682
You'll note here where it gets the total number of matching rows/results: https://github.com/fagersta/portal/blob/master/route_api_workorders.go#L959
We might want to omit that since some filter criteria may be so loose as to return so many results that it'd be very slow (and abusive to the db engine) to even count them. So maybe we go for a "Page 1 of many" style of user feedback if there are more than 1000 results or whatever. That's probably a small detail I can bother with later though.
- Retrieve a single mail via api endpoint (like /api/v1/emails/) on some common fields ie recipient, sender, date, subject via api endpoint
This is simply retrieving a single email via UID e.g.
SELECT * FROM tblEmails WHERE [Id] = @p1where * is whatever fields we want (probably all of em anyway).
- A whitelist of email addresses should be excluded from monitoring, e.g. recruiting & system mailboxes (whitelist is provided and managed by an existing 'Managed List' object)
Is this the managed list that you've already set up?
Yeah, we've gone for the opt-in strategy (monitor users in the list) rather than the opt-out (monitor all users unless they're in the list), so we should only monitor the addresses/users present in the managed list already set up.
I don't know enough about how permissions works in Fagersta to estimate this.
List of existing permission definitions are kept in tblFunctions
+---------------------+---------------+-------+
| TBLFUNCTIONS | TYPE | NULL |
+---------------------+---------------+-------+
| FunctionName | nvarchar(32) | false |
| FunctionDescription | nvarchar(255) | true |
+---------------------+---------------+-------+
A few rows for example:
select top 3 * from tblFunctions where functiondescription like 'sale%' FUNCTIONNAME | FUNCTIONDESCRIPTION
----------------------------------+-----------------------------------------------
ApproveSaleContract | Sale: Approve sales contract
ChangeReqdDate | Sale: Change the Date Required
ChangeSaleInvoicingBlockedUntil | Sale: Modify the invoicing block expiry date
----------------------------------+-----------------------------------------------
The assignment of permissions to users is kept in tblPermissions
+----------------+---------------+-------+
| TBLPERMISSIONS | TYPE | NULL |
+----------------+---------------+-------+
| User | nvarchar(255) | false |
| FunctionName | nvarchar(255) | false |
+----------------+---------------+-------+
A few rows for example:
select top 3 * from tblPermissions where [user]='MichaelP' USER | FUNCTIONNAME
-----------+-------------------
MichaelP | AllocateSalesman
MichaelP | AltoSunsetReport
MichaelP | ApprovePO
-----------+-------------------
Using permissions is super easy, only 2 functions you need.
Get all users that have the "WorkOrderBookinApprovalNotify" permission: https://github.com/fagersta/portal/blob/master/route_api_permissions.go#L98
Existing use for example: https://github.com/fagersta/portal/blob/master/route_api_workorders.go#L1523
bookinApprovalUserNames, err := fetchUsersWithPermission(ctx, tx, "WorkOrderBookinApprovalNotify")
if err != nil {
return nil, errors.Wrap(err, "saveWorkOrder: couldn't get list of users with the 'WorkOrderBookinApprovalNotify' permission")
}Get list of all possessed permissions for a single user: https://github.com/fagersta/portal/blob/master/route_api_permissions.go#L57 Existing use for example: https://github.com/fagersta/portal/blob/master/route_api_request_pricing.go#L90
permissions, err := fetchPermissionsForUser(r.Context(), a.db, userID)
if err != nil {
panic(errors.Errorf("handleAPIRequestPricingsList: couldn't get user permissions"))
}There's also the challenge that there isn't a good way to associate the user who sent/received the email with the current user, as there isn't a direct link between tblLogons & the email address used in the email
Right now there is an unbroken link between username and email address in the form of []mail.Address{{Address: some_stockman_username + brand.EmailDomain}} (or to put it another way ${some_stockman_username}@fagersta.com.au).
To make it more robust for future though I'll add an emailAddress column to tblLogon for the users email address. I'll take care of this today and notify you once I have.