Upgrading and rolling back

QueryProxy is at 0.y.z: breaking changes can land in any minor release and are listed in the changelog. This page covers the routine, the notes that apply when you move to v0.3.0, and how to go back.

The routine

The container runs php artisan migrate --force on every start, so an upgrade migrates the database for you. Migrations are not reversed when you start an older image. That makes the backup the only reliable way back.

1. Read the notes below for every version between yours and the target.

2. Back up the data volume and, if DB_* points at MySQL, MariaDB or PostgreSQL, the application database. See Backup and restore.

3. Verify the image you are about to run (see Verifying the image).

4. Pause approvals. Decide or leave aside what is pending, and let running requests finish. Upgrade when nothing is in the running state.

5. Pull the new image and recreate the container with the same settings. The step-by-step commands, including how to save the container’s settings before docker rm, are in the README. With Compose: docker compose pull && docker compose up -d.

6. Run the doctor and check the audit chain:

   docker exec queryproxy php artisan queryproxy:doctor
   docker exec queryproxy php artisan audit:verify

Start every container of one installation (web, worker, scheduler) on the same version. A worker on the old image that meets a database the new image has already migrated, or the other way round, can fail in ways nobody has tested.

Verifying the image

Release images are signed with cosign (keyless) and carry an SBOM and a SLSA provenance attestation. Use cosign v3 or later: older versions do not find the signatures. Replace 0.3.1 with the version you are checking.

cosign verify \
  --certificate-identity-regexp '^https://github\.com/QueryProxy/QueryProxy/\.github/workflows/release\.yml@refs/tags/v[0-9]+\.[0-9]+\.[0-9]+(-[0-9A-Za-z.-]+)?$' \
  --certificate-oidc-issuer https://token.actions.githubusercontent.com \
  ghcr.io/queryproxy/queryproxy:0.3.1

An image whose signature does not verify must not be deployed. The security policy also shows how to read the SBOM and the provenance.

Moving to v0.3.0

No image was published for 0.3.0 (the release scan stopped it on a vulnerable base image package); install 0.3.1, which carries the same changes. The steps below apply to the move from 0.2.x to 0.3.1.

v0.3.0 runs ten new migrations (they are listed under Migration notes). The changes below alter behaviour you may depend on; the first four are breaking, and two more breaking changes need action before you upgrade: the trigger privileges the audit migration needs (without them the migration fails and the container does not start) and the MySQL and MariaDB time zone.

Chat messages show a redacted SQL preview

QUERYPROXY_CHAT_INCLUDE_SQL now defaults to literals_redacted: the SQL preview in Slack and Teams replaces string and number literals with ?. It accepts three values:

The old booleans still work: true means full, false means none. When a statement cannot be redacted safely, the preview reads “SQL hidden”.

The audit screen and the audit CSV no longer carry the raw SQL for any role: the CSV column sql is now sql_redacted, and an Auditor sees redacted SQL in the request list and in the request detail. Update any script that reads the CSV column by name.

Users are soft-deleted, and overrides need a reason

Two migrations change how users and request history relate.

• users gains deleted_at and suspended_at. query_requests.user_id and reviewed_by change from cascade or null-on-delete to RESTRICT, so request history can no longer disappear with a user. Code or scripts that hard-delete users now fail on the foreign key.
• Deleting a user is a soft delete. Their sign-in, sessions and Slack and Teams identities stop working, and their pending requests are cancelled automatically (audited as request.cancelled with actor system). Approved and queued requests are not touched. A deleted user’s e-mail address stays reserved.
• A suspended user is blocked in the same way but keeps their pending requests, which are decided under the normal rules. Administrators suspend and resume users on the Users screen; nobody can suspend or delete themselves.
• Every admin override approval, on every channel, now needs a written reason (up to 1000 characters), and the reason is stored in the audit log. A Slack Approve press that would be an override is not applied; Slack answers with a link to the web reason form. The Teams action endpoint accepts reason on approve as well, and answers 422 to an override without one.

Rolling the user migration back is refused while any deleted or suspended account exists, because dropping the columns would switch those accounts back on. Remove or restore them first.

New connections require TLS; existing ones stay as they were

Every network connection you create in v0.3.0 asks for TLS and fails instead of falling back to plain text when the server offers none.

Connections that exist before the upgrade keep their old behaviour and are flagged in the connection list: PostgreSQL shows “TLS not enforced” (sslmode=prefer); MySQL, MariaDB and SQL Server show “TLS: driver default” (the result depends on the installed driver; with SQL Server, ODBC Driver 18 encrypts by default and Driver 17 does not). Edit each connection and set the TLS mode to require (PostgreSQL) or “required” (the others) to enforce it. queryproxy:doctor lists the connections that still do not enforce TLS.

SQL Server: enforcement rests on the driver. Encrypt=true makes Microsoft’s ODBC driver drop the connection when the server offers no encryption. That is the driver’s documented behaviour, not something QueryProxy implements, and the test suite cannot simulate a server that offers no TLS or alters the pre-login packet. Treat the network path to the server as untrusted until you have checked it yourself.

Rolling the migration back removes the TLS settings of every connection, stored PEM material included.

Teams: Workflows URLs now receive an Adaptive Card

Webhook URLs on *.powerplatform.com and *.logic.azure.com (Teams Workflows) now receive an Adaptive Card 1.5 message instead of a MessageCard. *.powerplatform.com joins the default webhook host allowlist. The queryproxy envelope with the action token stays at the top level of the payload, so the approval flow keeps working.

Breaking for custom flows. A Power Automate flow that reads the MessageCard fields (title, summary, sections[0].facts) from a logic.azure.com trigger now reads them empty. Read the card from attachments[0].content instead, or keep forwarding the body unchanged. See Teams integration.

Legacy connector URLs (*.webhook.office.com, and outlook.office.com if you add that host to QUERYPROXY_CHAT_WEBHOOK_ALLOWED_HOSTS) still receive a MessageCard, but Microsoft’s retirement notice (April 14, 2026 update) says the rollout ran May 18 to May 22, 2026, after which Office 365 Connectors no longer function, so a legacy URL may already have stopped working. Move to a Workflows URL.

Reads run in a read-only session

A read request now runs inside a read-only session on the target database, with a statement timeout and a lock timeout set by QueryProxy. The details are in SQL guards. What changes for you:

• PostgreSQL: SELECT … FOR UPDATE and FOR SHARE are rejected inside a read request (“tried to change data”), even though the guard classes them as reads.
• MySQL and MariaDB: the read-only setting is applied to the session, not to a single transaction. A statement that commits implicitly (such as TRUNCATE or ALTER) therefore cannot slip out of read-only mode.
• SQL Server has no engine-level read-only session. Use a separate read credential with SELECT privileges only (see Configuration).
• Once a statement has started, QueryProxy no longer reconnects automatically after a network drop. A brief drop now fails the run instead of silently re-running it on a new session.

Connection slots

At most QUERYPROXY_CONNECTION_SLOTS requests (default 2) run against one target connection at the same time. Further requests stay queued and start when a slot frees up. Set it to 0 to switch the limit off. If you ran more workers than that against one connection, expect waiting where there was none.

Reminders

A request that stays pending for QUERYPROXY_PENDING_REMIND_AFTER hours (default 4, 0 turns it off) triggers one reminder to the team’s DBAs and admins in the portal and in chat. Requests never expire. The scheduler runs the reminder check every 15 minutes, so keep the scheduler running.

Masking

• The hash strategy is deprecated and labelled so in the UI. The new hmac strategy uses a key per team; see Data masking.
• The built-in card-number regex is replaced by a Luhn validator rule for new default sets. Existing teams keep the old rule; “Add default rules” adds the Luhn rule next to it. Delete the old one when you no longer need both.
• The TCKN content validator no longer treats a digit run glued to a letter or an underscore (TC10000000146) as a candidate. A named column is still caught by its column rule.

Time zone of MySQL and MariaDB connections

config/database.php now pins the mysql and mariadb connections of the application database to +00:00. TIMESTAMP columns are stored as UTC and shown in the session time zone. Until now the session used the database server’s time zone, so on a server that is not set to UTC every existing TIMESTAMP value (created_at, updated_at and the *_at columns in all tables) is shown shifted by the server’s UTC offset after the upgrade. A server at UTC+3 shows the values 3 hours earlier, a server at UTC-5 shows them 5 hours later. With a named zone such as Europe/Berlin the shift follows daylight saving time. DATETIME columns are not affected, and servers that already run in UTC see no change.

Check the setting before you upgrade:

SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone,
       TIMEDIFF(NOW(), UTC_TIMESTAMP());

A default install reports SYSTEM for both zone variables, so read the last column: any result other than 00:00:00 means the server is not on UTC and the shift below applies. (NOW() uses the session time zone, so run this in a plain session.)

The audit hash chain depends on this pin: audit_logs.created_at is hashed as UTC wall time and must read back unchanged.

Open chat approval tokens (chat_approval_tokens.expires_at, 24-hour lifetime) are affected too. On a UTC+N server they expire N hours early, and a token with less than N hours left becomes invalid at once; on a UTC-N server they live up to N hours longer. Tokens issued after the upgrade are exact. To avoid either effect, delete the open tokens before you upgrade; the affected requests then need a fresh chat message (the reminder re-posts a card with a new token):

DELETE FROM chat_approval_tokens WHERE used_at IS NULL;

Audit chain: privileges and what becomes immutable

The audit migration adds a hash chain and database triggers that refuse UPDATE and DELETE on audit_logs (and TRUNCATE on PostgreSQL; MySQL and MariaDB triggers cannot block it). Creating triggers needs privileges the migration user must hold:

• MySQL and MariaDB: the TRIGGER privilege. With binary logging on, also SUPER or log_bin_trust_function_creators=1; otherwise the migration fails with error 1419.
• PostgreSQL: ownership of the table.

Existing audit records are chained from the first one during the migration. The migration can be run again after a partial failure. See Audit chain and SIEM export.

Secrets from files

Each of APP_KEY, APP_PREVIOUS_KEYS, DB_PASSWORD, MAIL_PASSWORD, REDIS_PASSWORD, SLACK_BOT_USER_OAUTH_TOKEN, QUERYPROXY_ADMIN_PASSWORD, QUERYPROXY_METRICS_TOKEN and QUERYPROXY_SIEM_WEBHOOK_SECRET can now come from a file through the same name with a _FILE suffix. See Configuration.

New things that are off until you turn them on

/metrics (Monitoring), the SIEM sinks (Audit chain and SIEM export) and queryproxy:doctor add no work for installs that ignore them. The scheduler does gain three jobs: a daily audit:checkpoint, requests:remind every 15 minutes, and a one-minute heartbeat that writes a timestamp to the cache (one write a minute on the database cache store).

Migration notes

Rolling back

Use the backup, not migrate:rollback.

1. Stop the container.
2. Restore the data volume and, if you use one, the application database from the backup you took before the upgrade. The commands are in Backup and restore.
3. Start the previous version tag with the same environment file.

Do not start an older image against a database a newer image has migrated. php artisan migrate:rollback exists for each migration above, but the data it discards (masking keys, TLS settings, audit chain) does not come back, and the chain triggers would need to be removed in the right order. Restoring a backup avoids all of that.

Next steps

• Backup and restore
• Troubleshooting
• Production hardening