MoneyWorks

Experimental and subject to change

External system notification has been implemented in MoneyWorks Datacentre 9.0.2. This allows another server to be notified whenever a change is made in a MoneyWorks database.

This will remove the need for external systems to poll for changes (a practice which can cause unnecessarily high load on a MoneyWorks server due to constantly opening and closing the document when no changes are being made).

Notifications are generally sent within a minute of changes being made, and you will get a timestamp that predates all of the changes, and a list of changed tables, and/or an indication of whether posting has occurred. You can use the timestamp to search for changed records or transactions posted using LastModifiedTime or TimePosted.

How to enable

Change notifications must be enabled in the document

To enable change notifications in the document, you need to execute the following once:

SetDocumentGlobal("notifyChanges", monitorTablesList)

This will set a global in the document to enable change notifications. You can turn it off by passing an empty string. You will need scripting privileges to execute this.

monitorTablesList is a comma-delimited string listing the tables you want to monitor. It can also contain the meta-table "_posted" to get notifications of posting.

So for notification for posting and changes to jobs and products:

SetDocumentGlobal("notifyChanges", "_posted,job,product")

Change notifications must be enabled on the server

Setting the document notification mode will have no effect until you also enable change notifications on the server.

To do this, modify the folder.conf file for the folder hosting the document. Obviously, to do this, you will need administrative access to the server.

Three notification modes have been implemented:

Simple outgoing POST to url

This mode will do a simple POST, with a payload of doc=docname&time=timestamp&changed=changedtables to a url nominated in the folder.conf

To enable this mode, add the following to the folder.conf for the folder containing the document

databaseModifiedPOST_URL: https://example.com/moneyworks_ping.php

and optionally, a header

databaseModifiedPOST_Header: Authorization: Basic QWxhZGRpbjpvcGVuIHNlc2FtZQ==

Within one minute of qualifying changes being made in the database, the server will POST to the url. The POST will have Content-Type: application/x-www-form-urlencoded and will contain three values: doc will identify the originating document (there may be more than one in the folder).time will identify a time in the server's timezone that predates the changes being notified. You can call back through the DC REST API and search the LastModifiedTime of any table you are interested in to find the changed records whose modified time is >= the given timestamp; changed will have a comma delimited list of table names indicating which tables have changed since the timestamp time. The list may also begin with the word _posted if transactions have been posted. The list will never include the details table. Only tables requested by notifyChanges are included in this list.

It is up to you to host an endpoint that can receive the POST and act upon it. If you are employing third party services with their own authentication or API requirements, then you may need to proxy those through such an endpoint.

This mode is relatively lightweight, in that it does not require an extre process to spin up on the server to send the notification (and does not consume a login on the server while doing so), and relatively secure in that the outgoing URL is known to the server administrator.

Potentially available on MoneyWorks Now

For a MoneyWorks Now server, we may entertain enabling this mode.

Serverside DatabaseModified script handler (sandboxed)

If you need a bit more control over the outgoing post, and want to include an actual data payload to obviate the need for a further REST request back into the server, there is a mode that allows execution of a script on the server.

To enable this mode, add the following to the folder.conf

serversideDatabaseModifiedScriptEnable: 1

You can include a script in the document whose name starts with _SERVER_ and contains a handler named DatabaseModified. Scripts not so named will not be loaded by this process on the server.

Within one minute of changes being made in the database, the server will load the script(s) (logging in as "Admin", which needs to exist) and call the DatabaseModified handler. The timestamp will identify a time in the server's timezone that predates the changes being notified. You can search the LastModifiedTime of any table you are interested in to find the changed records whose modified time is >= the given timestamp.

The tables parameter is a comma delimited list of table names indicating which tables have changed since the timestamp time. The list may also begin with the word _posted if transactions have been posted. The list will never include the details table. Only tables requested by notifyChanges are included in this list.

Executing the script requires spinning up a serverside client, which will consume a concurrent login on the server.

on DatabaseModified(timestamp, tables)
    syslog("DatabaseModifed handler " + DateToText(timestamp, DateFormYYYYMMDDHHMMSS) + " " + tables)
    let posted = false;
    let xml = ""
    foreach t in text tables // e.g. _posted,transaction,ledger,product
        if t = "_posted"
            let posted = true
        else
            let xml = xml + Export(t, "xml-terse", "" , "LastModifiedTime >= '" + DateToText(timestamp, DateFormYYYYMMDDHHMMSS) + "'")
        endif
    endfor

    let msg = ""
    if posted
        let msg = "Transactions were posted.\n\n"
    endif
    let msg = msg + "The following records were updated in the database:\n\n" + Replace(xml, "<?xml version=\"1.0\"?>", "")

    // with serversideDatabaseModifiedScriptEnable = 1 you have access to SandboxedPOST
    // the POST destination and payload size will be logged on the server

    SandboxedPOST("https://server/myscript.php", msg, "Authorization: Bearer blah", "Content-Type: plain/text")

end

The above example uploads all of the changed records. You would not do this in practice.

Potentially available on MoneyWorks Now

Under consideration to be available on MoneyWorks Now. Not sure if the url will need to be restricted—currently it is not. The destination and payload size is logged.

Serverside DatabaseModified script handler (unsandboxed)

To enable this mode, add the following to the folder.conf

serversideDatabaseModifiedScriptEnable: 2

This mode will execute DatabaseModified handlers in scripts with names beginning with _SERVER_, but in this case, the scripts have full access to CURL and File APIs (no sandboxing). This mode would only be enabled on a server you own, where you are fully in control of scripts that will be deployed in documents on the server.

on DatabaseModified(timestamp, tables)  // will execute in CLI on server
    syslog("DatabaseModifed handler " + DateToText(timestamp, DateFormYYYYMMDDHHMMSS) + " " + tables)
    let posted = false;
    let xml = ""
    foreach t in text tables
        if t = "_posted"
            let posted = true
        else
            let xml = xml + Export(t, "xml-terse", "" , "LastModifiedTime >= '" + DateToText(timestamp, DateFormYYYYMMDDHHMMSS) + "'")
        endif
    endfor

    let msg = ""
    if posted
        let msg = "Transactions were posted.\n\n"
    endif
    let msg = msg + "The following records were updated in the database:\n\n" + Replace(xml, "<?xml version=\"1.0\"?>", "")


    //__SendSMTPMail_WithCredentials(to, path, subject, message_text, attachmentName, server, replyTo, authUser, authPass)
    let to = "recip@example.com"
    let server = "mail.example.com"
    let replyto = "me@example.com"
    let user = "bot@example.com"
    let pass = "SMTPp4ss"

    Built_In:__SendSMTPMail_WithCredentials(to, "", "database changed", msg, "", "mx.cognito.co.nz", replyto, user, pass) 
end

This example emails the changes using the built in SMTP script (which in turn uses CURL, for which you need unsandboxed execution).

Not available on MoneyWorks Now

Unsandboxed mode will not be enabled for MoneyWorks Now servers. Allowing arbitrary outbound network connections from our servers seems like a bad idea.