Overview

The Database Retention feature provides you with the flexibility to manage the lifecycle of data within your database connected to the integration. It allows for the configuration of retention policies that define how long old records are retained before being automatically purged. By enabling, disabling, and configuring retention settings, you can ensure that your database retains only necessary data, optimizes storage for specific tables, and maintains compliance with data for any retention policies.


The Database Retention Feature is only available for use after receiving the Q1 2025 release of ASR's Eleos Integration. 


Enabling, Disabling And Configuring Integration Settings

The Database Retention feature can be enabled or disabled completely within the appsettings.json file where your integration is deployed. You can also configure the interval for how often the retention process runs and purges old records. This configurable interval is in hours and the lower this value, the more frequent the purge process runs, and the higher this value the less frequent this process will run.

These settings within the appsettings.json file can be found under the general “Settings” section with the “AllowDbRetentionCleanup” setting turning this feature on or off, and the “DbRetentionHoursInterval” setting adjusting the frequency that the retention process runs:

Note: When adjusting these settings, the AllowDbRetentionCleanup setting must be either true or false and the DbRetentionHoursInterval setting must be an integer value greater than or equal to twelve. A twelve-hour interval is the minimum and most frequent this setting can be configured. Any values less than this will result in the integration defaulting this setting to twelve.

Configuring Database Retention Settings

Once the integration level settings are enabled and configured, database settings must also be set to specify which tables records should be purged from, how old records must be to be purged, and any records that should also be purged from joining tables. All the database level retention settings are configured within the ELEOS.RetentionSettings table and can be viewed by selecting all from this table. This is also where any custom retention settings should be inserted into.

SELECT * FROM ELEOS.RetentionSettings


Default Retention Settings

By default, the ELEOS.RetentionSettings table has five standard retention setting that are all disabled. Retention settings can be enabled or disabled by updating the IsActive column to either 0 for disabled or 1 for enabled. Retention settings correspond to ELEOS schema tables and work by purging records that are older than the set RetentionDays. This number of days for retaining records is also defaulted and can be updated to a custom retention period to meet any internal policies. Because each retention setting looks at different tables, how old a record is in that table is determined by the DateColumnName identified for that table. You’ll also be able to tell the date and time records were last purged by looking at the LastExecutionDt column:


Note: Before enabling a default retention setting, ensure that the RetentionDays for that setting are accurate to avoid mistakenly over purging records. There is no way for us to restore records that you've accidentally purged due to error. We recommend testing your date comparisons prior to enabling a retention setting.



1. ELEOS.MessageHistory / ELEOS.MessageFields

Removes records from ELEOS.MessageHistory and corresponding records from ELEOS.MessageFields that are older than the set RetentionDays. The date of the records is determined by their ComposedAt column. Due to table constraints message fields are purged first to then be able to remove the message from message history.

2. ELEOS.EventHistory

Removes records from ELEOS.EventHistory that are older than the set RetentionDays. The date of the records is determined by their EventDt column.

3. ELEOS.DocumentHistory / ELEOS.DocumentFields

Removes records from ELEOS.DocumentHistory and corresponding records from ELEOS.DocumentFields that are older than the set RetentionDays. The date of the records is determined by their UploadedAt column. Due to table constraints document fields are purged first to then be able to remove the document from document history.

4. ELEOS.GeotabDutyStatusLogFeed

Removes records from ELEOS.GeotabDutyStatusLogFeed that are older than the set RetentionDays. The date of the records is determined by their EditDateTime column.

5. ELEOS.DutyStatusLogs / ELEOS.TelematicClockEvents

Removes records from ELEOS.DutyStatusLogs and corresponding records from ELEOS.TelematicsClockEvents that are older than the set RetentionDays. The date of the records is determined by their DateTime column. Due to table constraints telematic clock events are purged first to then be able to remove duty status logs.

Below is an example query that can be copied and filled in for enabling a default retention setting and configuring the retention days to a new value. The green text for “--NumberOfDays” must be replaced with an integer value and the green text for “--IdOfDefaultSetting” must be replaced with an integer value that corresponds to the id of the retention setting:

UPDATE ELEOS.RetentionSettings
SET IsActive = 1
   ,RetentionDays = --NumberOfDays
WHERE RetentionId = --IdOfDefaultSetting


You can then run the query for selecting all from the ELEOS.RetentionSettings table again to see the configured default setting:

SELECT * FROM ELEOS.RetentionSettings


The above example enabled and configured the retention setting for the ELEOS.DutyStatusLogs table so that records greater than three years old or 1095 days are deleted. This will also delete corresponding records from the ELEOS.TelematicsClockEvents table that join on the same DutyStatusLogId value as defaulted in settings

Custom Retention Settings

The ELEOS.RetentionSettings table can also have custom retention settings added to it. This is done through inserting additional records into this table and ensuring the minimum required columns are set to proper database table names, column names, and or retention days:

Note: Any retention settings that are incorrectly added will not execute and instead will log an error in the log file of the integration for the retention id that failed.

Below lists the columns when inserting a custom retention setting that are the minimum required and others that can be additionally configured depending on the needs of the custom retention setting.

  • IsActive - Required and must be set to either 0 for disabling the retention setting or 1 for enabling it.
  • Description - Optional but is encouraged to be used to help indicate what the custom retention setting is for.
  • RetentionDays - Required and must be set to an integer value that represents the number of days records are kept for.
  • TableName - Required and must be set to the same table name listed in the database for the table you want to add a retention setting for.
  • DateColumnName - Required and must be set to a date time column on the table you want to add a retention setting for.
  • TableJoinColumn - Optional and is used if the retention setting needs to join to a foreign key table to purge correlated records. This is the column is the join column on the parent table which should match the foreign key table's ForeignKeyColumn. If this is configured, then the ForeignKeyTable column and ForeignKeyColumn column need to be configured as well.
  • ForeignKeyTable - Optional and is used if the retention setting needs to join to an additional table to purge correlated records. This is the additional table that records will also be purged from. If this is configured, then the TableJoinColumn column and ForeignKeyColumn column need to be configured as well. 
  • ForeignKeyColumn - Optional and is used if the retention setting needs to join to an additional table to purge correlated records. This is the key column for records from the additional table. If this is configured, then the TableJoinColumn column and ForeignKeyTable column need to be configured as well.

Below is an example query that can be copied and filled in for inserting a new custom retention setting with the minimum required values and a description value. The green text in the query should be replaced with either proper integer values or strings:

INSERT INTO ELEOS.RetentionSettings
  ( IsActive
   ,Description
   ,RetentionDays
   ,TableName
   ,DateColumnName )
VALUES
  ( 1
   ,--DescriptionOfTheCustomRetentionSetting
   ,--NumberOfDays
   ,--TableNameThatRetentionShouldApplyTo
   ,--DateColumnNameOnTableToIndicateHowOldRecordIs )


You can then run the query for selecting all from the ELEOS.RetentionSettings table again to see the added custom retention setting:

SELECT * FROM ELEOS.RetentionSettings


The above example inserted and enabled the custom retention setting for the ELEOS.DatabaseHistory table so that records greater than two years old or 730 days are deleted.