Archiving, Summary Statistics and Log Purging

[rallen2010]

I've been working on a group of functionality that is "Audit & Logging Phase 2" and comprises several individual features that can be assembled to form a feature for in Flows for APEX v23.1. I'm looking for advice and requirements for how to put these together.

What we have now (in v22.2)

Way back in 21.1 ( I think) we added Logging and Auditing Phase 1. This added:

• A Process Logging Table, flow_flow_event_log to record changes to models and their meta-data (which we haven't used yet!)
• An Instance Event log, flow_instance_event_log to record important events in a process instance, like instance creation, start, reset, completion, termination, errors, etc.
• A Step Event Log, flow_step_event_log which contains one row for each completed instance step, containing timestamps for step becoming current, work starting, step completion, reservation, priority, due date, etc.
• A Variable Event Log, flow_variable_event_log, which records the value of a process variable every time it is set.
• all the functionality inside the engine to populate these log tables (except for the process model table)
• granular settings to define which combination of these logs you want to collect for your system.
  As we were short of time, we didn't provide any built-in mechanisms for managing these logs, for archiving them, or purging them. And we didn't do much with them. That was left for a future release....

What do we want to provide next?

We now want to take this all a few steps further, in the following directions:

• Archive
  • It should be possible to archive all of the log data for a completed process instance into OCI object storage. (MUST)
  • it should also be possible to archive all log data for a completed instance into relational tables outside the Flows for APEX tables. (NICE).
  • archiving will be controlled by a configuration parameter logging_archive_instance_summaries = "TRUE" or "FALSE" (default: false).
  • we will add a configuration parameter instance_archive_destination to contain a JSON snippet specifying the archive location, as follows:

{
  "destinationType" : "TABLE|OCI-API|OCI-PREAUTH",
  "tableDetails" : {
                    "tableName" : "MY_ARCHIVE_TABLE" ,
                    "idColumn"  : "id",
                    "timestampColumn" : "archive_ts",
                    "blobColumn" : "summary_blob"
                   },
   "ociApiDetails" : {
                    "baseUrl" : "my_url",
                    "bucketName" : "myBucket" ,
                    "documentPrefix" : "F4A-inst-arc",
                    "credentialApexStaticId" : "MY_CREDENTIAL"
                    },
   "ociPreAuthDetails" : {
                    "preAuthUrl" : "my_preauth_url",
                    "documentPrefix" : "F4A-inst-arc",
                    "credentialApexStaticId" : "MY_CREDENTIAL"     
                    }
}   

• Summary Data
  • The actual log data is high volume, so we're looking at summary data on a daily, monthly, and quarterly basis for Instance Events flow_instance_stats and Step Events flow_step_stats. Instance Duration, Step Duration, and Step Waiting Times should be help with quartile, median, and max information in the summaries. (MUST)
  • There should be a create_log_summary procedure which can be called from APEX Automations after midnight each day to generate daily summaries, a Month To Date summary for the current month, and to create monthly and quarterly summaries as required on the appropriate days. (MUST)
  • There should be a log / history table showing significant events in the summary creation process. (MUST)
  • Note that summaries are created based on the task completion date or the instance completion date - to prevent still running, long running business processes from distorting summaries.
• Charting & Visualization
  • The existing dashboard charting should be enhanced to show instance, step, and daily information, so that instance and / or step execution rates, processing times, waiting times, etc. can be seen for recent days, weeks, months, and quarters. (NICE)
  • Instance statistics should be visible (linked) to the Flow Manager as well as the Flow Monitor. (NICE).
• Debug / Instance Event Log
  • It should be possible to see a full chronological display of all of the activities performed for a process instance, including instance events, step events, and variable events -- dependant on appropriate logging options being set when the instance was running. (MUST) (done)
  • It should be possible to get a JSON object representation of all of the logged activities performed by an instance -- whether it is still running or has been completed. (MUST) (done)
• Log Purging
  • It should be possible to specify that all of the log data for an instance ( including instance, step and variable logs as appropriate ) can be retained for n days after instance / step completion, before being deleted. (MUST) (n >= 31?)
  • It should be possible, optionally, to require an instance archive be created first before archive is allowed. (no Purge before archive). (nice or must?)
  • Daily summaries can also take space, and it should be possible to delete daily summary data aged > m days since instance completion. (m >= 31).(MUST)
  • Monthly summaries that are more than p months old should also be purgeable. (p >= 6 months). (MUST)
  • Quarterly summaries that are more than q months old should also be purgeable. (p >= 12 months). (MUST)
  • New configuration parameters will be introduced to store n, m, p & Q on a per-Flows installation (i.e., workspace) level. (MUST)
• Model Level Logging and Audit Trail
  • Changes to the process meta data (version information, creation dates, editing dates) should be logged. (MUST)
  • Changes to the model itself should be audited (NICE)
• Separation of Admin API from Workflow API
  • As part of creating the Log File management and Statistics functionality, create a flow_admin_api package to contain admin functions to be called from outside Flows for APEX.

Pulling This Functionality Together - Intended Operation

Automation Jobs

Daily Instance Archiver

• If archiving is enabled via a configuration parameter archive_all_instance_logs set to true), a daily job should create an instance archive documents for each completed or terminated process instance, and store it in the specified location (i.e., archive table or OCI Object Storage bucket).
  • The archive should be recorded in the flow_processes.prcs_archived_ts column if successful.
  • If the instance deletion API flow_api_pkg.delete_process is called on an instance that has not yet been archived on a 'secure' site, then the flow_api_pkg.delete_process will archive the instance before deleting it. This is a low frequency event , but on a secure installation, this might be important as it prevents a user from performing a workflow that ends up being non-accountable as it would not appear in the archives.

Daily Stats Generator

• A daily job should generate daily statistics summaries for completed steps and completed instances for days not yet summarised. This should update the MTD statistics, as well as create monthly or quarterly summaries if required.

Log Purging

It is envisioned that the instance, step, and variable logs will be retained for some period of time - maybe 30,90, or 365 days, depending on customer business requirements - but will eventually be purged. The retention period, specified as the number of days since the process instance was completed (with completed or terminated status), is stored in the system configuration parameter logging_retain_logs_after_prcs_completion_days. A routine will be provided that can be scheduled from an APEX Automation to periodically purge log records for process instances exceeding this age. If a site has archive_all_instance_logs set to true, the purge routine checks that instances are recorded as having been archived.

Stats Purge

Statistics will also be purged when the exceed configured ages. Thes are specified using the following configuration parameters:

• stats_retain_daily_summaries_days - Unit Days

• stats_retain_monthly_summaries_months - Unit Months

• stats_retain_quarerly_summaries_months - Unit Months

  Comments please...