Automating Cleanup and Archival Jobs for Firebird Reports with Spring Boot

January 29, 2026

#1

After setting up automated report generation and email delivery, your system will gradually accumulate old reports and logs. To keep performance optimal, it’s best to automate cleanup and archival tasks. In this guide, you’ll learn how to safely delete outdated data and move older records into an archive table using Spring Boot and Firebird.

1. Create Archive Tables in Firebird

To prevent permanent data loss, you should move older records to an archive table instead of deleting them immediately.
Run this SQL in your Firebird database:

CREATE TABLE AUDIT_LOG_ARCHIVE AS 
SELECT * FROM AUDIT_LOG WHERE 1=0;

This command clones the structure of your AUDIT_LOG table without copying the data.

2. Write a Repository Method for Archiving

Add custom methods to move old data (e.g., logs older than 30 days) into the archive.

@Repository
public interface AuditLogRepository extends JpaRepository<AuditLog, Long> {

    @Modifying
    @Transactional
    @Query(value = "INSERT INTO AUDIT_LOG_ARCHIVE SELECT * FROM AUDIT_LOG WHERE LOG_DATE < CURRENT_DATE - 30", nativeQuery = true)
    void archiveOldLogs();

    @Modifying
    @Transactional
    @Query(value = "DELETE FROM AUDIT_LOG WHERE LOG_DATE < CURRENT_DATE - 30", nativeQuery = true)
    void deleteOldLogs();
}

These queries first move old logs to the archive, then remove them from the main table.

3. Schedule Automatic Cleanup

Now let’s create a scheduled job to automate this process weekly:

@Component
public class MaintenanceScheduler {

    @Autowired
    private AuditLogRepository auditRepo;

    @Scheduled(cron = "0 0 2 * * SUN") // Every Sunday at 2 AM
    public void cleanAndArchiveLogs() {
        auditRepo.archiveOldLogs();
        auditRepo.deleteOldLogs();
        System.out.println("Old logs archived and cleaned successfully.");
    }
}

This runs every Sunday at 2 AM, keeping your Firebird database clean and lightweight.

4. Add Logging for Maintenance Activities

For traceability, log every cleanup action in the database itself.

@Autowired
private MaintenanceLogRepository maintenanceRepo;

maintenanceRepo.save(new MaintenanceLog("Archived old audit logs", LocalDateTime.now()));

You can later visualize these logs in your Spring Boot dashboard, similar to how you track reports.

5. Integrate Cleanup Notifications

You may also send a short notification email whenever the cleanup job runs.

@Autowired
private EmailService emailService;

@Scheduled(cron = "0 0 2 * * SUN")
public void cleanAndArchiveLogs() throws Exception {
    auditRepo.archiveOldLogs();
    auditRepo.deleteOldLogs();
    emailService.sendEmailWithAttachment(
        "admin@company.com",
        "Weekly Cleanup Summary",
        "Old Firebird logs have been archived and deleted successfully.",
        null
    );
}

This ensures your admin always stays informed about automated maintenance.

6. Testing the Cleanup Job

To test manually, expose a REST endpoint:

@PostMapping("/run-cleanup")
public String runCleanup() {
    maintenanceScheduler.cleanAndArchiveLogs();
    return "Cleanup completed successfully!";
}

Trigger /run-cleanup in your browser or API client to confirm that the job works before waiting for the scheduled time.