# Upgrading and rolling back

> How to back up, upgrade and roll back a QueryProxy install, plus the breaking changes and migration notes you need to read before moving to v0.3.0.

- Documentation: Guides
- Updated: 2026-10-25
- Source: https://queryproxy.com/docs/upgrading/
- Language: en-US
- Author: Muhammet Şafak

---
QueryProxy is at `0.y.z`: breaking changes can land in any minor release and are
listed in the [changelog](https://github.com/QueryProxy/QueryProxy/blob/main/CHANGELOG.md).
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](/docs/backup-restore/).
3. **Verify the image** you are about to run (see
   [Verifying the image](#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](https://github.com/QueryProxy/QueryProxy#upgrading). With Compose:
   `docker compose pull && docker compose up -d`.
6. **Run the doctor** and check the audit chain:

   ```bash
   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.

```sh
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](https://github.com/QueryProxy/QueryProxy/blob/main/SECURITY.md)
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](#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](#audit-chain-privileges-and-what-becomes-immutable)
the audit migration needs (without them the migration fails and the container
does not start) and the
[MySQL and MariaDB time zone](#time-zone-of-mysql-and-mariadb-connections).

### 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:

| Value | Preview |
| :-- | :-- |
| `literals_redacted` (default) | SQL with literals replaced by `?` |
| `full` | The SQL as submitted (the earlier behaviour) |
| `none` | No SQL in chat messages |

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.

| Driver | Default for a new connection |
| :-- | :-- |
| PostgreSQL | `sslmode=require`. Pick `verify-ca` or `verify-full` and paste the CA certificate for certificate checks |
| MySQL / MariaDB | TLS required; the server certificate is **not** verified by default (self-signed certificates work). Tick "Verify the server certificate" and add a CA to verify |
| SQL Server | `Encrypt=true`, `TrustServerCertificate=false`. A certificate from an untrusted CA makes the connection fail; turn `TrustServerCertificate` on deliberately for self-signed certificates. `pdo_sqlsrv` must be installed |
| SQLite | No TLS |

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](/docs/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](https://devblogs.microsoft.com/microsoft365dev/retirement-of-office-365-connectors-within-microsoft-teams/)
(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](/docs/sql-guards/#coverage). 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](/docs/configuration/#read-credentials-and-timeouts)).
- 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](/docs/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:

```sql
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):

```sql
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](/docs/audit-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](/docs/configuration/#secrets-from-files).

### New things that are off until you turn them on

`/metrics` ([Monitoring](/docs/monitoring/)), the SIEM sinks
([Audit chain and SIEM export](/docs/audit-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

| Migration | Adds | Rolling it back |
| :-- | :-- | :-- |
| `2026_10_19_100000_add_read_credentials_to_connections_table` | Optional read username and password per connection | Drops both columns |
| `2026_10_20_000001_add_hash_chain_to_audit_logs_table` | Hash chain, lock table, unique indexes, triggers; chains existing records | Removes them |
| `2026_10_20_000002_add_masking_key_to_teams_table` | A masking key per team, backfilled | Drops the keys for good; `hmac:` values stop matching |
| `2026_10_21_000001_add_reminder_columns_to_query_requests_table` | `reminded_at`, `chat_renewed_at` | Drops both |
| `2026_10_21_000002_add_sensitive_filter_columns_to_query_requests_table` | `sensitive_filter_columns`, read by the sensitive-filter badge | Drops the column |
| `2026_10_21_000003_add_soft_delete_and_suspension_to_users_table` | `deleted_at`, `suspended_at` | Refused while deleted or suspended accounts exist |
| `2026_10_21_000004_protect_request_history_from_user_deletion` | `RESTRICT` foreign keys | Restores cascade or null-on-delete |
| `2026_10_21_000005_add_backend_pid_to_query_requests_table` | `backend_pid` for the Stop button | Drops the column |
| `2026_10_21_000006_record_tls_settings_on_connections` | TLS flags on existing connections | Removes all TLS settings and stored PEM |
| `2026_10_21_000007_create_metric_samples_table` | Storage for `/metrics` counters | Drops the table |

## 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](/docs/backup-restore/#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](/docs/backup-restore/)
- [Troubleshooting](/docs/troubleshooting/)
- [Production hardening](/docs/production-hardening/)