Building a PHP command line tool for database maintenance

A database maintenance command should be more than a convenient wrapper around SQL. It should give operators a repeatable way to inspect, repair or retire stale records while leaving a clear audit trail. In a PHP application, a well-designed command line tool can use existing Composer dependencies and avoid exposing an administrative web page.

Safe maintenance starts with a narrow scope, a preview, conservative locking and a reliable result. Those needs are familiar across Australian support teams in Sydney, Melbourne, Brisbane and Perth, where maintenance windows must account for local public holidays, daylight saving and different operating hours.

Define one auditable maintenance operation

Start with one job that has a measurable outcome, such as removing expired sessions, normalising orphaned records or reconciling a checksum. Avoid a generic maintenance command capable of updating any table. A focused tool can define valid states, permitted records and expected row counts, making behaviour easier to review and test.

Split the work into planning and execution. The planning phase should identify the selected tenant, record range, SQL fingerprint and estimated number of affected rows. A dry-run mode should use the same selection logic as the real operation but stop before mutation. Persist a run identifier so a preview can be matched with its later execution.

The wider systems and consulting notes on Karl Katzke’s Blog provide useful context for treating the command as an operational service with ownership, versioning and support expectations, rather than as a disposable script.

Build the command around safe defaults

Build on a supported PHP 8.x release and use the command-line SAPI rather than assuming the web server’s PHP configuration. Composer can pin Symfony Console for argument parsing, while PDO and the relevant driver provide portable database access. Keep engine-specific features behind small adapters and state the supported MySQL or PostgreSQL versions in the command help.

Keep database credentials out of code and command-line arguments, where shell history or process listings may expose them. Read connection details from the environment or an injected secret provider, and reserve local .env files for development. Use explicit time zones: store timestamps in UTC, display them with Australia/Sydney or Australia/Brisbane, and avoid hard-coded AEDT offsets that become incorrect during daylight saving.

Expose predictable options such as --dry-run, --apply, --run-id and a filter for the permitted record range. Requiring an explicit apply flag helps prevent accidental mutation, while a stable exit code lets cron, systemd or a CI pipeline distinguish success from a failed or partial run. Every invocation should also print the resolved configuration without exposing passwords or personal information.

Protect data while keeping service available

Before changing data, establish a recoverable baseline. Take a database snapshot or logical backup, verify that it completed and test restoration into an isolated environment. A snapshot is not a dependable backup until the team can restore it within the required recovery window. Record the backup identifier and table checksums alongside the maintenance run.

Apply updates in bounded batches using a stable cursor, such as a primary-key range, rather than a large OFFSET query. Wrap each batch in a transaction where the operation supports it, and commit only after validating the batch result. Remember that MySQL DDL can cause implicit commits, so schema changes need a separate recovery plan instead of relying on transactional rollback.

Heavy operations should avoid blocking normal traffic. Use database locks, carefully sized transactions and short-lived tables where appropriate, then swap objects only when the surrounding system permits it. If the records contain personal information, apply the Australian Privacy Principles, minimise the data processed and define how long temporary copies and logs will be retained.

Make every run observable and repeatable

Emit structured logs with a run identifier, operation name, selected scope, row counts, duration and final status. Redact connection details and personal data before writing log messages. Machine-readable output is useful for monitoring, but summaries written for an operator should still explain what changed, what failed and whether any action remains outstanding.

Return stable exit statuses for successful completion, invalid configuration, lock contention, failed validation and unexpected database errors. A failed command must stop at the first unsafe condition rather than continuing with partial assumptions. Expose counters or a status command so an automated system can confirm the expected number of records was processed.

Build fixture-based integration tests against the same database engine used in production. Repeat each run to confirm that the command is idempotent, and test interruption during a batch. That concern scales neatly to adjacent systems: RECAP live event processing illustrates why durable job state, retries and operator-visible status matter when work arrives continuously rather than as a nightly batch.

Deploy the tool for regional operations

Package the project as a Composer application and produce a signed PHAR for hosts that do not want to duplicate the dependency tree. A systemd timer or cron entry can invoke it under a restricted operating-system account, while the same PHAR can run inside a container or Kubernetes job. Keep the runtime, extensions and database driver versions documented with the release.

Choose regional infrastructure carefully. Australian cloud regions around Sydney and Melbourne can reduce latency and support contractual data-residency requirements, but replication across borders changes the privacy analysis. For Australian managed service providers and digital agencies, a portable PHAR plus a service unit is generally easier to hand over than a custom deployment procedure.

Publish a runbook naming responsible roles, escalation contacts and rollback steps. Schedule work around Australian public holidays, daylight-saving changes and weekends, and include the applicable time zone in every run. A job that succeeds at 02:00 in Brisbane should not silently inherit a server default based on another location.

Recommendations for a safer first release

Before the first production execution, rehearse the entire workflow with realistic but non-sensitive data. The release should be judged by its operational behaviour as well as its SQL.

• Ship a read-only or dry-run command before adding mutation, then compare selected record identifiers with an independent query.
• Test restoration from the backup and record the elapsed recovery time against the agreed service objective.
• Require an explicit apply flag and make reruns idempotent after an interrupted or already-completed batch.
• Use database-level locking to prevent concurrent maintenance processes from operating on the same scope.
• Retain run identifiers, checksums and redacted logs long enough to support an incident investigation.

Start by extracting the maintenance SQL into a single reviewed service and exposing it through a PHP command with dry-run and apply modes. Run that workflow against a restored copy, obtain sign-off on the output, and schedule the first production execution during an Australian business window with an operator watching the logs and rollback path.