Self-Hosting / Databases

Never Write Directly to an App-Encrypted Database Column

Fixing a self-hosted app's broken credential straight in the database feels like a reasonable shortcut. Then the column you just wrote to turns out to be encrypted at the application layer, not just at rest. The write succeeds. The breakage shows up somewhere else entirely, later, looking nothing like what caused it.

The setup

A self-hosted app had a stale integration token stored in its own Postgres database. The fix itself was correct. A fresh, valid token, confirmed working via a direct API call. Writing it into the app's database with a plain UPDATE ... SET token_value = '<new value>' is where it went wrong. That column was encrypted at the application layer, not just protected by disk-level encryption. SQLAlchemy-Utils' EncryptedType and its equivalents in other frameworks are the common pattern. The app expects to decrypt whatever sits in that column using its own installation-specific key. A raw plaintext value written straight in breaks that expectation completely. It breaks silently, at write time. Nothing errors on the UPDATE itself.

Where the actual failure shows up

BE WARNED: the failure does not happen on write. It happens on the next read, and it can look like a completely unrelated feature broke or lost data. Here, the app's backend threw a decryption error every time it tried to read that row back. In the UI that showed up as an entire configuration section going empty. Genuinely alarming, and at a glance indistinguishable from the data having been deleted. It had not been. The row was still there. It had just become permanently unreadable by the one thing that knew how to decrypt it.

The lesson generalizes. "The page shows nothing" is not proof that data was lost. Before you assume a deletion or a data-loss event, check the application's own backend logs for a decrypt or deserialization error on read. A silently broken decrypt looks identical to missing data from outside. The cause and the fix are completely different.

There's usually no fixing it after the fact

The corrupted value cannot be repaired in place without the app's own encryption key. That key is generated once per installation, and you are not getting it back after the fact. I checked environment variables, config files and install scripts. None of them expose it, by design. Two options once this has happened:

Delete the corrupted row and re-enter the whole affected configuration through the app's own UI, not SQL. Confirm first that any foreign key relationships handle the deletion gracefully. A nullable or ON DELETE SET NULL relationship makes it safe.
Better, if you have it. Restore from a backup taken before the mistake. That brings back the intact, correctly encrypted row in one shot. Only the field you meant to fix needs re-entering afterward, through the app itself.

A compounding gotcha worth watching for after a restore

BE WARNED: a self-hosted app's own error message about licensing, auth or a missing feature is not proof of its stated cause. After a restore from backup in a real case like this, the app's admin UI started reporting a "no license" error, unrelated to anything above. The real cause was a full disk on the restored volume. That crashed the app's own database process. The frontend surfaced it as a licensing failure, because that was the error path it hit first. Freeing real disk space fixed it. Here that meant old dangling container images left over from prior version pulls, safe to prune since nothing running referenced them. Nothing about licensing was ever wrong.

Check basic resource health before you trust what a self-hosted app claims is wrong with itself. Especially anything licensing-shaped or auth-shaped. Disk space. Whether its own database process is up. Plenty of apps surface a generic or misleading error message for what is really resource exhaustion underneath.

The general rule

Before you write to any column via raw SQL on a self-hosted app's database, check whether that field is encrypted at the application layer. API tokens, passwords and other credential-shaped fields are where this lives. Look for framework-specific markers in the app's source, or in the error traceback if something has already gone wrong. EncryptedType and similar are common in Python and Ruby ORMs. If a field is app-encrypted, change it through the application's own UI or API. Every time. Raw SQL stays safe for plain, non-encrypted columns on the same table. Treat anything credential-shaped as guilty until proven otherwise.