Using OData, Syslog or DuckDB to handle FileMaker Schema Change Notifications (via Plug-in API)

FileMaker Server v21.1 (2024) introduced functionality for plug-ins to receive notifications of changes to database schema, layout, custom functions, or scripts. Notification messaging is passed in the form of JSON. Let's show a few examples of these JSON payloads, and how a plug-in might use external tools (Syslog, DuckDB or OData) to handle things.

The FileMaker Plug-in SDK is central to this functionality, so these examples are based around using bBox, a free utility plug-in to extend FileMaker solutions. Supporting both macOS & Ubuntu Linux, bBox has functions to work with Python, JavaScript, PHP, Ruby, AppleScript, Bash/Zsh/sh, XPath, and DuckDB. The bBox plug-in includes a demo file with more than 220 examples.

Schema Change Notifications with FileMaker

With FileMaker Server v21.1 (2024) there is new functionality for plug-ins to receive notifications of changes to database schema, layout, custom functions, themes, value lists, or scripts*. These come in the form of a JSON payload. As a plug-in, bBox can capture these notifications and send them to later be processed by FileMaker or some other process.

* As shorthand, we'll refer to schema + layouts + scripts + etc collectively as 'schema'.

Here's an example of the messages for a simple script change:

{
  'account' : 'simon',
  'catalog' : 'script',
  'file' : 'bBox Plug-in Demo.fmp12',
  'id' : '165',
  'user' : 'Simon Brown'
}

{
  'account' : 'simon',
  'catalog' : 'script',
  'file' : 'bBox Plug-in Demo.fmp12',
  'id' : '<meta>',
  'user' : 'Simon Brown'
}

In the first message, we get the ID of the script that was changed. That's followed by a subsequent message indicating that the script's meta data has changed, presumably due to the modification count changing.

Once notification handling is turned on, you'll be getting messages for all files hosted on that server. Also, you'll quickly notice that a single, seemingly small change, will trigger many notifications. For instance, changing a field name will not only trigger a field related message, but also several layout catalog messages. This is due to FM modifying field references to the modified field.

Schema message handling is configured with a script step that has the following prototype:

bBox Schema Notifications [ Type: (DuckDB, OData, Syslog) PathOrURL: 'POSIX file path or URL' Script Name: 'script name' Credentials: 'user:password' ]

You can get messages sent to you three ways:

1. Syslog
2. DuckDB
3. OData (run a FileMaker script)

Syslog for Notifications

Syslog is the default method and the easiest to configure, with the last three parameters of the script step being ignored. It would probably require more work to parse the results however. You'd likely either be using the log (macOS) or journalctl (Ubuntu) commands to query syslog for the messages later.

Here is an example of tailing any bBox related messages (including the schema notifications) on macOS:

log stream --predicate 'sender == 'bBox''

Queries with the log command can be quite complex. Just for starters, here we'll instead get any messages from a 5 minute time period on December 11, 2024:

log show --predicate 'sender == 'bBox'' --start '2024-12-11 10:20:00' --end '2024-12-11 10:25:00'

For Ubuntu, journalctl has some similar functionality as the macOS log command. A simple version of its usage looks like this:

sudo journalctl -f -t bBox

DuckDB with FileMaker

DuckDB is another option.

DuckDB is similar to SQLite (which bBox supported in the past). Although not 100% overlapping with SQLite, DuckDB offers a great deal of cross-compatibility, and can even use SQLite database files. In this case, bBox can use DuckDB to quickly hand off messages for later consumption.

When this method is first selected you'll give it a file path to create the DuckDB database file, and bBox will run the following SQL to set up the table for you:

CREATE TABLE IF NOT EXISTS fm_schema_message (timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, message TEXT);

To retrieve the values later, you could use the bBox_DuckDB functions to pull in the data to FileMaker. However, if you first initialize the DuckDB file and the fm_schema_message table prior to the bBox Schema Notifications call, you can configure the database file to store the values as a .csv formatted data instead of the native DuckDB format. The data could then be imported into a FileMaker table, processed by a log aggregation utility, or whatever else you may want.

OData Message Notifications

Finally, we have OData based message handling. For this method, you'll specify a URL set to the server and database that will be processing the message data. The receiving server will need to have OData access enabled, you'll need the credentials for an account in the database file that has the fmodata extended privilege enabled, and a FileMaker script that can be called to capture the JSON message.

Below is an example of how you might set that up:

Set Variable [ $baseURL ; Value: bBox_FM_API_URL('ODATA'; 'localhost'; 'bBox Plug-In Demo' ) ]
bBox Schema Notifications [ Type: OData ; PathOrURL: $baseURL ; Script Name: 'store_schema_change' ; Credentials: 'myuser:mypass' ]

I've used the bBox_FM_API_URL function to help calculate the correct endpoint URL, but that isn't a requirement. OData script requests can't be used with script names that have any special characters, so be sure to keep your script handler's name as simple as possible.

Your FileMaker handler script would do something similar to the following to extract the message it was sent:

New Record/Request
Set Field [ MESSAGES::jsontext ; Base64Decode ( Get (ScriptParameter) )

So, there you have three examples of how FileMaker Server v21.1 (2024) supports plug-ins to receive notifications of changes to database schema, layout, custom functions, or scripts.

Please get in touch with us with any questions, or to discuss how we can help you with your FileMaker development and integration projects.