Data Maintenance

This document describes various data maintenance features available in the APIHUB backend.

• Revisions TTL
  • Configuration
  • How job works
  • Deletion modes
    • Standard mode
    • Last Revision Cleanup mode
    • Release Revisions Cleanup mode
    • Delete All mode
• Ad-hoc comparisons TTL
  • Configuration
  • How job works
• Soft Deleted Data TTL
  • Configuration
  • How job works
  • Affected Tables and Handling
• Unreferenced Data Cleanup
  • Configuration
  • How job works
• Maintenance Vacuum
  • Configuration
  • How job works
• Cleanup Job Schedules

Revisions TTL

APIHUB backend implements an automatic cleanup mechanism for old package revisions to reduce migration size. The system runs a scheduled job that removes revisions older than a configured time-to-live (TTL) period and meet the configured conditions.

Configuration

The revisions cleanup job is configured via configuration properties:

Configuration property	Default value	Description
cleanup.revisions.ttlDays	365	Number of days to keep revisions before they become eligible for deletion
cleanup.revisions.deleteLastRevision	false	Whether to delete the last revision of a version even if it’s the only one
cleanup.revisions.deleteReleaseRevisions	false	Whether to delete revisions with “release” status
cleanup.revisions.schedule	0 21 * * 0	Cron schedule for the cleanup job (Sunday 9:00 PM by default)

The job timeout is automatically calculated based on the schedule interval to ensure it completes before the next run.

How job works

The revisions cleanup job performs the following steps:

1. Checks if any migrations are running - if so, it skips execution to avoid conflicts.
2. Processes packages in batches, examining each version’s revisions.
3. For each revision, checks:
   • If it’s older than the TTL value
   • If it’s the last revision (and whether deletion of last revisions is enabled)
   • If it has “release” status (and whether deletion of release revisions is enabled)
   • If it has any valid references from dashboards
4. Deletes eligible revisions and tracks the deletion in the activity log.
5. Cleans up related data like ad-hoc comparisons, default release version and previous version for other versions (in case of version deletion).

Deletion modes

The cleanup job has different modes that can be configured:

Standard mode

By default (REVISIONS_CLEANUP_DELETE_LAST_REVISION=false and REVISIONS_CLEANUP_DELETE_RELEASE_REVISIONS=false), the job will delete revisions that are:

• Older than the configured TTL (default: 365 days)
• Not the last revision of a version
• Not in “release” status
• Not referenced by dashboards

Last Revision Cleanup mode

When REVISIONS_CLEANUP_DELETE_LAST_REVISION=true and REVISIONS_CLEANUP_DELETE_RELEASE_REVISIONS=false:

• Deletes revisions older than the TTL, including the last revision of a version
• Preserves revisions with “release” status
• Can completely remove versions if all revisions meet deletion criteria and none are releases
• Never deletes revisions referenced by dashboards

Release Revisions Cleanup mode

When REVISIONS_CLEANUP_DELETE_LAST_REVISION=false and REVISIONS_CLEANUP_DELETE_RELEASE_REVISIONS=true:

• Deletes revisions older than the TTL, including those with “release” status
• Preserves the last revision of each version
• Never deletes revisions referenced by dashboards

This should be used with caution as release revisions often represent important milestones.

Delete All mode

When both REVISIONS_CLEANUP_DELETE_LAST_REVISION=true and REVISIONS_CLEANUP_DELETE_RELEASE_REVISIONS=true:

• Deletes any revision older than the TTL regardless of whether it’s the last revision or has “release” status
• Can completely remove versions if all revisions meet deletion criteria
• Never deletes revisions referenced by dashboards

WARNING: Delete All mode is not recommended for use with short TTL and should only be used with recent database backups. The job in this mode can potentially delete all published versions in the system if the TTL is too short.

Ad-hoc comparisons TTL

APIHUB backend implements an automatic cleanup mechanism for version/operation comparisons to reduce database size and migration size. The system runs a scheduled job that removes old and irrelevant comparisons, primarily focusing on “ ad-hoc” comparisons that are created for temporary analysis.

Comparisons configuration

The comparisons cleanup job is configured via configuration properties:

Configuration property	Default value	Description
cleanup.comparisons.ttlDays	30	Number of days to keep ad-hoc comparisons before they become eligible for deletion
cleanup.comparisons.schedule	0 5 * * 0	Cron schedule for the cleanup job (Sunday 5:00 AM by default)
cleanup.comparisons.timeoutMinutes	360	Maximum execution time for the cleanup in minutes. After the timeout, the job will not be terminated immediately. ‘VACUUM FULL’ will be performed on the affected tables prior to job termination. Must be greater than 0. The service fails to start if the value is zero or negative.

The job includes a vacuum phase that runs after the main cleanup to optimize affected database tables.

How comparisons cleanup works

The comparisons cleanup job performs the following steps:

1. Checks if any migrations are running - if so, it skips execution to avoid conflicts.
2. Iterates through all version comparisons in the system.
3. For each comparison, it checks for several deletion criteria. A comparison is deleted if any of the following are true:
   • It is an ad-hoc comparison older than the configured TTL. An ad-hoc comparison is one that was created between two arbitrary versions, not as part of a version’s changelog.
   • It is an outdated changelog comparison, meaning it does not point to the latest revision of the previous version.
   • It is a comparison for a revision that no longer exists.
4. Deletes eligible version comparisons and related operation comparisons.
5. Performs VACUUM FULL on affected version_comparison, operation_comparison and version_comparison_notification tables to optimize database size.

Soft Deleted Data TTL

APIHUB backend implements an automatic cleanup mechanism for soft-deleted data to permanently remove data that has been previously marked for deletion. The system runs a scheduled job that removes soft-deleted data older than a configured time-to-live (TTL) period.

Soft deleted data configuration

The soft deleted data cleanup job is configured via configuration properties:

Configuration property	Default value	Description
cleanup.softDeletedData.ttlDays	730	Number of days to keep soft-deleted data before permanent deletion
cleanup.softDeletedData.schedule	0 22 * * 5	Cron schedule for the cleanup job (Friday 10:00 PM by default)
cleanup.softDeletedData.timeoutMinutes	600	Maximum execution time for the cleanup in minutes. After the timeout, the job will not be terminated immediately. ‘VACUUM FULL’ will be performed on the affected tables prior to job termination. Must be greater than 0. The service fails to start if the value is zero or negative.

How soft deleted data cleanup works

The soft deleted data cleanup job performs the following steps:

1. Checks if any migrations are running - if so, it skips execution to avoid conflicts.
2. Processes soft-deleted packages in batches, permanently deleting those older than the TTL. Deletes API keys and package transitions associated with packages. Any other data related to packages is removed via cascade deletion.
3. Processes soft-deleted package revisions in batches, permanently deleting those older than the TTL. Data related to revisions is removed via cascade deletion.
4. Performs VACUUM FULL on affected database tables to reclaim disk space and optimize performance.

Affected Tables and Handling

The cleanup job affects the following database tables:

• package_group - package entities
• published_version - package revisions
• activity_tracking – related to package_group, but linked to a revision; however, these should not be deleted when a revision is deleted. Only remove records when deleting a package via cascade deletion.
• apihub_api_keys – remove the API keys that were issued for the packages being removed
• build – related to package_group, all related records are automatically removed via cascade deletion
• build_depends – related to build, all related records are automatically removed via cascade deletion
• build_result – related to build, all related records are automatically removed via cascade deletion
• build_src – related to build, all related records are automatically removed via cascade deletion
• error_build_notification – related to build, all related records are automatically removed via cascade deletion
• favorite_packages – related to package_group, all related records are automatically removed via cascade deletion
• operation – related to published_version, all related records are automatically removed via cascade deletion
• operation_group – related to published_version, all related records are automatically removed via cascade deletion
• grouped_operation – related to operation and operation_group (many-to-many relationship), all related records are automatically removed via cascade deletion
• operation_group_history – do not touch the records, as this table can be useful for analysis
• operation_open_count – related to package_group, all related records are automatically removed via cascade deletion
• package_export_config – related to package_group, all related records are automatically removed via cascade deletion
• package_member_role – related to package_group, all related records are automatically removed via cascade deletion
• package_service – related to package_group, all related records are automatically removed via cascade deletion
• package_transition – has no relations, remove records that contain the packages being removed in the new_package_id column
• published_document_open_count – related to package_group, all related records are automatically removed via cascade deletion
• published_sources – related to published_version, all related records are automatically removed via cascade deletion
• published_version_open_count – related to package_group, all related records are automatically removed via cascade deletion
• published_version_notification – related to published_version, all related records are automatically removed via cascade deletion
• published_version_reference – related to published_version, all related records are automatically removed via cascade deletion
• published_version_revision_content – related to published_version and published_data, all related records are automatically removed via cascade deletion
• published_version_validation – related to published_version, all related records are automatically removed via cascade deletion
• shared_url_info – related to package_group, all related records are automatically removed via cascade deletion
• transformed_content_data – related to operation_group, all related records are automatically removed via cascade deletion
• version_comparison_notification – related to version_comparison, all related records are automatically removed via cascade deletion

Note: cascade deletion is a database feature that automatically deletes related records in other tables when a primary record is deleted.

Unreferenced Data Cleanup

APIHUB backend implements an automatic cleanup mechanism for unreferenced data to reduce database size and improve performance. The system runs a scheduled job that removes data that is no longer referenced by any active entities in the system.

Unreferenced data configuration

The unreferenced data cleanup job is configured via configuration properties:

Configuration property	Default value	Description
cleanup.unreferencedData.schedule	0 15 * * 6	Cron schedule for the cleanup job (Saturday 3:00 PM by default)
cleanup.unreferencedData.timeoutMinutes	360	Maximum execution time for the cleanup in minutes. After the timeout, the job will not be terminated immediately. ‘VACUUM FULL’ will be performed on the affected tables prior to job termination. Must be greater than 0. The service fails to start if the value is zero or negative.

The job includes a vacuum phase that runs after the main cleanup to optimize affected database tables.

How unreferenced data cleanup works

The unreferenced data cleanup job performs the following steps:

1. Checks if any migrations are running - if so, it skips execution to avoid conflicts.
2. Processes and deletes unreferenced data in batches for each data type:
   • Operation data - removes operation_data records that are no longer referenced by any operation. Records in ts_rest_operation_data, ts_graphql_operation_data, ts_operation_data and fts_operation_data will be removed by cascade deletion.
   • Operation group templates - removes operation_group_template records that are no longer referenced by any operation group.
   • Source archives - removes published_sources_archives records that are no longer referenced by any published source.
   • Published data - removes published_data records that are no longer referenced by any published document.
3. Performs VACUUM FULL on affected database tables to reclaim disk space and optimize performance.

Note: Unlike other cleanup jobs, this job does not use a TTL (Time-To-Live) configuration. It removes all unreferenced data regardless of age, as unreferenced data serves no purpose in the system.

Builds Cleanup

APIHUB backend implements an automatic cleanup mechanism for build data to reduce database and S3 storage size. The system runs a scheduled job that removes builds older than a fixed retention period: 1 week for successful builds and 30 days for failed ones. When S3 storage is enabled, the job also removes the build results of these builds from the bucket, and deletes expired objects that no longer correspond to any build in the database. The notifications a failed build stored (error_build_notification) are removed with its sources, and the hasNotifications flag of the build is cleared, so the publish status stops offering messages that no longer exist.

Configuration

The builds cleanup job is configured via configuration properties:

Configuration property	Default value	Description
cleanup.builds.schedule	0 1 * * 0	Cron schedule for the cleanup job (Sunday 1:00 AM by default)
cleanup.builds.timeoutMinutes	360	Maximum execution time for the removal of expired builds in minutes. After the timeout, the running database operations are cancelled. Must be greater than 0. The service fails to start if the value is zero or negative.
cleanup.builds.expiredS3Files.timeoutMinutes	360	Maximum execution time for the expired S3 files phase in minutes. After the timeout, the sweep stops and resumes on the next run. Must be greater than 0. The service fails to start if the value is zero or negative.

The expired S3 files phase runs only when S3 storage is enabled. The two timeouts are independent: the phase starts after the expired builds are removed and counts its own timeout from that moment, even when cleanup.builds.timeoutMinutes has already expired. The job therefore runs up to the sum of both values, which is 12 hours with the default settings. Take this into account when scheduling the job, so that it does not overlap with the jobs that follow.

How job works

The builds cleanup job performs the following steps:

1. Checks if any migrations are running - if so, it skips execution to avoid conflicts.
2. Checks the build_cleanup_run table - if the previous run happened within the current schedule interval, it skips execution.
3. With S3 storage disabled, deletes expired builds from build_src, build_result and error_build_notification, then performs VACUUM FULL on the three tables.
4. With S3 storage enabled, removes the build results of expired builds from the bucket, deletes the corresponding build_src and error_build_notification records and performs VACUUM FULL on both tables.
5. With S3 storage enabled, runs the expired S3 files phase: walks the build_result/ prefix in the bucket and removes every object older than 45 days, regardless of whether it is still referenced in the database. The threshold is a constant in the code, not a configuration property. It keeps a margin above the 30-day retention of failed builds, otherwise the sweep would remove files of builds that are still referenced. The sweep restarts from the beginning of the prefix on every run, as no progress is stored between runs.
6. Writes the results of the phase to the build_cleanup_run table, into the expired_s3_files_count and expired_s3_files_details columns. The number of removed notifications is recorded in the error_build_notification column of the same table.

Maintenance Vacuum

APIHUB backend runs a dedicated scheduled maintenance vacuum job to execute VACUUM FULL ANALYZE for eligible public tables. This job is independent from migration stages and should be scheduled during low-traffic windows because it may lock tables.

Maintenance vacuum configuration

The maintenance vacuum job is configured via configuration properties:

Configuration property	Default value	Description
cleanup.maintenanceVacuum.schedule	0 2 * * 1	Cron schedule for maintenance vacuum job (Monday 2:00 AM by default)
cleanup.maintenanceVacuum.timeoutMinutes	300	Maximum duration of maintenance vacuum phase (VACUUM FULL ANALYZE) in minutes. Must be greater than 0. The service fails to start if the value is zero or negative.

How maintenance vacuum works

The maintenance vacuum job performs the following steps:

1. Checks if any migrations are running - if so, it skips execution to avoid conflicts.
2. Fetches all public tables (via pg_stat_all_tables).
3. Executes VACUUM FULL ANALYZE for each eligible table.

Cleanup Job Schedules

All cleanup jobs run on predefined schedules to avoid conflicts and distribute system load:

Job type	Default schedule	Description	Day/Time	Cleanup phase timeout	Vacuum phase timeout
Revisions Cleanup	0 21 * * 0	Sunday at 9:00 PM	Every Sunday	Interval between runs minus one hour	—
Comparisons Cleanup	0 5 * * 0	Sunday at 5:00 AM	Every Sunday	Configured via cleanup.comparisons.timeoutMinutes	3 hours (not configurable)
Soft Deleted Data Cleanup	0 22 * * 5	Friday at 10:00 PM	Every Friday	Configured via cleanup.softDeletedData.timeoutMinutes	6 hours (not configurable)
Unreferenced Data Cleanup	0 15 * * 6	Saturday at 3:00 PM	Every Saturday	Configured via cleanup.unreferencedData.timeoutMinutes	3 hours (not configurable)
Builds Cleanup	0 1 * * 0	Sunday at 1:00 AM	Every Sunday	Configured via cleanup.builds.timeoutMinutes, plus cleanup.builds.expiredS3Files.timeoutMinutes for the expired S3 files phase on top	Shared with cleanup phase
Maintenance Vacuum	0 2 * * 1	Monday at 2:00 AM	Every Monday	—	Configured via cleanup.maintenanceVacuum.timeoutMinutes

Note: when scheduling Comparisons Cleanup, Soft Deleted Data Cleanup, Unreferenced Data Cleanup and Builds Cleanup jobs, it is important to keep in mind that each job consists of two phases: cleanup and vacuuming of the affected tables. Builds Cleanup has a third phase, the expired S3 files sweep, when S3 storage is enabled. All phases of a job should be completed before the next job starts in order to avoid excessive system load and database table locks.