HandyCafe Docs
it-admin owner

Database Maintenance

The Database Maintenance page is the backup and upkeep center for the local HandyCafe database. From this page you can review the health of the database, run one-off maintenance tasks, schedule automatic maintenance and backups, and restore the server from a previous snapshot.

Database Health

The Database Health section shows the current state of the database. Use the Refresh button to reload these values at any time.

The health dashboard reports the following:

  • Database size: Total size of the main database file.
  • Write-ahead log: Size of the write-ahead log (WAL) file.
  • Page count: Number of pages the database file holds.
  • Free pages: Number of unused pages on the freelist that can be reclaimed by a vacuum.
  • Last backup: Timestamp of the most recent backup. Shows Never run if no backup has been taken.
  • Last maintenance: Timestamp of the most recent vacuum.
  • Last integrity check: Timestamp of the most recent integrity check, with a status indicator showing whether the database was healthy or whether issues were found.
  • Log entries: Number of rows currently stored in the audit log.

Below these figures the section also lists the Journal mode, Auto-vacuum setting, Page size, and the number of Backups on disk.

Expand the Tables entry to see a per-table row count for every table in the database.

Manual Actions

The Manual Actions section runs a one-off maintenance task immediately. Only one action can run at a time. Each action shows a confirmation toast when it finishes, and several actions display a result summary above this section.

  • Back up now: Creates an atomic snapshot of the current database. The new snapshot appears in the Backup History list.
  • Vacuum: Reclaims unused pages and defragments the database file. The result summary reports the size before, the size after, the space reclaimed, and the duration.
  • Integrity check: Runs a full corruption check. It is slower but thorough. The result opens in the Integrity Report dialog.
  • Quick check: Runs a fast structural sanity check. The result also opens in the Integrity Report dialog.
  • Analyze: Updates the query planner statistics.
  • Optimize: Runs SQLite's incremental optimizer.
  • Truncate WAL: Flushes and truncates the write-ahead log file. The result summary reports the WAL frame count, the number of checkpointed frames, and the duration.
  • Clean old logs: Deletes log entries older than the configured retention window. The result summary reports the rows deleted, the rows remaining, and the duration.

Integrity Report

After an integrity check or quick check, the Integrity Report dialog opens. When the database is healthy it confirms that database integrity is verified. When SQLite reports problems, the dialog lists the issues and recommends restoring from a known-good backup. The dialog also shows the duration of the check.

Auto Maintenance

The Auto Maintenance section schedules vacuum, integrity check, and logs cleanup to run automatically.

  • Enable scheduled maintenance: Turns the scheduled maintenance cycle on or off.
  • Frequency: Choose Daily, Weekly, or Monthly.
  • Hour of day: Select the local hour at which the cycle runs.

Auto Backup

The Auto Backup settings take regular snapshots so that a single corruption can never wipe out a day of data.

  • Enable scheduled backups: Turns scheduled backups on or off.
  • Frequency: Choose Daily, Weekly, or Monthly.
  • Hour of day: Select the local hour at which the backup runs.
  • Keep most recent: The number of snapshots to retain, between 1 and 365. Older snapshots beyond this count are removed.
  • Backup folder: The directory where snapshots are stored. Leave this empty to use the default folder inside the application data directory.

Log Retention

The Log Retention setting controls how long audit log entries are kept before they are removed during maintenance. Set the Retention (days) value with the slider. The supported range is 7 to 730 days.

Use the Save button at the bottom of this section to store the Auto Maintenance, Auto Backup, and Log Retention settings.

Backup History

The Backup History section lists the snapshots stored locally. For each backup the table shows:

  • File: The snapshot file name.
  • Created: The date and time the snapshot was taken.
  • Size: The size of the snapshot file.
  • Source: How the backup was triggered. Scheduled for automatic backups, Startup for backups taken when the application starts, and Manual for backups created with Back up now.

Each row offers two actions:

  • Restore: Replaces the running database with the selected snapshot. Before the swap, a pre-restore safety copy of the current database is saved automatically. The application then closes and restarts so the snapshot can take effect. You are asked to confirm before the restore begins.
  • Delete: Removes the snapshot file from disk. You are asked to confirm before the file is deleted. This cannot be undone.

If no snapshots exist yet, the section prompts you to run Back up now or wait for the next scheduled cycle.

Cloud Backup

The Cloud Backup section describes off-site encrypted snapshots stored on HandyCafe servers, restorable from any HandyCafe install.

This feature is marked Coming soon and is not yet available. The toggle and configuration fields are saved locally so the settings are ready when the feature opens for your account. The available fields are Enable cloud backup, an option to also back up media assets such as logos, posters, and idle screens, the Endpoint, and the Bucket / storage zone.

Important Notes

  • A restore closes and restarts the server application. Inform any active sessions before you restore.
  • Restoring always saves a pre-restore safety copy of the current database first, so a restore can be reversed by restoring that safety copy.
  • Keep the backup folder on storage that has enough free space for the number of snapshots set in Keep most recent.
  • Running an integrity check or a vacuum on a large database can take time. Only one manual action runs at a time.