Database Maintenance¶

Target Audience: Administrators Difficulty: Intermediate Prerequisites: Database access; basic SQL knowledge

Overview ¶

MultiFlexi uses Phinx for database schema management. Migrations run automatically during package install/upgrade via the postinst script. This page covers manual maintenance tasks.

Supported Databases ¶

Backend	Package	Recommended for
MySQL 8.0+	`multiflexi-mysql`	Production
MariaDB 10+	`multiflexi-mysql`	Production
PostgreSQL	`multiflexi-postgresql`	Production
SQLite 3	`multiflexi-sqlite`	Dev / Testing

Checking Migration Status ¶

sudo -u multiflexi php /usr/share/multiflexi/vendor/bin/phinx status \
  -c /etc/multiflexi/phinx.php

Output shows each migration with its status (up = applied, down = pending).

Running Pending Migrations ¶

Migrations run automatically on upgrade, but can be triggered manually:

sudo -u multiflexi php /usr/share/multiflexi/vendor/bin/phinx migrate \
  -c /etc/multiflexi/phinx.php

Rolling Back a Migration ¶

Warning

Only roll back if the last migration was applied accidentally or in error. Rolling back in production data is destructive.

# Roll back the last applied migration
sudo -u multiflexi php /usr/share/multiflexi/vendor/bin/phinx rollback \
  -c /etc/multiflexi/phinx.php

# Roll back to a specific version
sudo -u multiflexi php /usr/share/multiflexi/vendor/bin/phinx rollback \
  -c /etc/multiflexi/phinx.php -t 20251130113650

Core Database Tables ¶

Understanding the schema helps with maintenance and debugging.

Table	Contents
`user`	Administrator and user accounts
`company`	Multi-tenant company records
`applications`	Registered application definitions
`app_to_company`	Which apps are assigned to which companies
`run_template`	Job execution templates
`job`	Individual job execution records
`artifacts`	Job output files (stdout, stderr, results)
`config_registry`	RunTemplate configuration field values
`company_env`	Company-level environment variables
`credential_prototype`	Credential type definitions (JSON-based)
`credential_type`	Company-level credential instances
`logger`	Job execution log entries
`token`	API authentication tokens

Cleaning Up Old Data ¶

Tip

Install multiflexi-housekeeper to automate all routine cleanup tasks. It runs hourly via a systemd timer and handles data retention, orphaned jobs, log pruning, and more — no manual SQL required.

sudo apt install multiflexi-housekeeper
# Verify the timer is active
systemctl list-timers multiflexi-housekeeper

See HouseKeeper — Periodic Maintenance for the full feature list and configuration options.

For manual or one-off cleanup:

# Via CLI — prune log and job tables, keeping the latest 10 000 records
multiflexi-cli prune --logs --jobs --keep 10000

# Via SQL (MySQL/MariaDB) — remove jobs and cascade-delete their artifacts
mysql -u root -p multiflexi -e "
  DELETE FROM job
  WHERE DatCreate < NOW() - INTERVAL 90 DAY
    AND status IN ('ok', 'failed');
"

Note

The artifacts table has a CASCADE DELETE constraint on job_id. Deleting a job record automatically removes all its associated artifacts.

Performance Maintenance ¶

MySQL / MariaDB ¶

# Rebuild table indexes and reclaim space
mysql -u root -p multiflexi -e "OPTIMIZE TABLE job, artifacts, logger;"

# Analyze table statistics for query planner
mysql -u root -p multiflexi -e "ANALYZE TABLE job, run_template, applications;"

PostgreSQL ¶

sudo -u postgres psql multiflexi -c "VACUUM ANALYZE;"

# Full vacuum (reclaims disk space, requires exclusive lock)
sudo -u postgres psql multiflexi -c "VACUUM FULL ANALYZE job;"

Checking Database Size ¶

MySQL / MariaDB ¶

mysql -u root -p multiflexi -e "
  SELECT table_name,
         ROUND(data_length/1024/1024, 2) AS data_MB,
         ROUND(index_length/1024/1024, 2) AS index_MB
  FROM information_schema.tables
  WHERE table_schema = 'multiflexi'
  ORDER BY data_length + index_length DESC;
"

PostgreSQL ¶

sudo -u postgres psql multiflexi -c "
  SELECT relname, pg_size_pretty(pg_total_relation_size(oid))
  FROM pg_class WHERE relkind = 'r' ORDER BY pg_total_relation_size(oid) DESC;
"

Changing the Database Backend ¶

To migrate from SQLite (development) to MySQL (production):

Back up all data: multiflexi-cli export > backup.json (if available) or use a SQL dump
Install the new database backend: sudo apt install multiflexi-mysql
Restore data to the new database
Update /etc/multiflexi/multiflexi.env with new DB_* settings
Run migrations: phinx migrate