# Database Schema Upgrades & Migrations

> > Scope: how CBX creates and evolves its database schema — the runtime migration runner, the > version ledger, fresh-install bootstrapping, the per-version u…

Source: CBX documentation, version 4.0 preview (unreleased). Canonical page: https://docs.configbox.at/docs/4.0-preview/technical/migrations. Last updated 2026-08-04.

---
> **Scope:** how CBX creates and evolves its database schema — the runtime migration runner, the
> version ledger, fresh-install bootstrapping, the per-version update scripts, the idempotent-DDL
> toolkit, and how a developer adds a migration · **Last reviewed:** 2026-08-02

Paths are relative to `docroot/components/com_configbox/`. The engine is
`ConfigboxUpdateHelper` in `helpers/update.php`.

---

## 0. Overview

CBX does **not** ship schema changes in the Joomla installer. Instead it carries a set of
**versioned PHP update scripts** that are applied **at runtime**, idempotently, tracked by a version
number stored in the database. This means a code update (new files on disk) automatically brings the
schema up to date on the next request, on every platform (Joomla/Magento/WP/standalone), without a
manual SQL step.

Two parallel tracks exist:
- **Product migrations** — `helpers/updates/*.php`, tracked by sys-var `latest_update_version`.
- **Customization migrations** — `<customization dir>/updates/*.php`, tracked by sys-var
  `latest_customization_update_version` (so a customer's own schema changes evolve independently and
  survive product upgrades). The customization dir is **per platform** — exact paths in §7.

---

## 1. When migrations run (the trigger)

`ConfigboxUpdateHelper::applyUpdates()` is invoked on **every request**, early in the boot:
- `observers/System.php` → `ObserverSystem::onConfigboxInitialized()` calls `applyUpdates()`
  (`observers/System.php:9`), except on **Magento 2**, where migrations run from the module's Setup
  script instead.
- It is also exposed via `KenedoLoader::applyUpdates()` (`KenedoLoader.php:19`) for host integrations.

Running on every request is cheap in the steady state: the first thing `applyUpdates()` does after
the lock/flag checks is compare the stored version against the available scripts and return
immediately when there's nothing new.

**Opting out of the boot-time run.** Define `CB_SUPPRESS_AUTO_UPDATES` *before* booting Kenedo and
`ObserverSystem::onConfigboxInitialized()` skips `applyUpdates()`. This exists for callers that need
to own the timing — specifically the migration CLI commands: without it, merely booting to run
`configbox:migrate --status` would apply the migrations the operator asked only to *inspect*, and a
failing script would surface as a raw bootstrap stack trace instead of the command's diagnostics. It
must be a constant rather than a flag on `ConfigboxUpdateHelper`, because that class does not exist
until the boot registers its autoload entry.

---

## 2. The version ledger (`#__configbox_system_vars`)

State is a simple key/value table:
```sql
CREATE TABLE IF NOT EXISTS `#__configbox_system_vars` (
  `key`   varchar(128) NOT NULL,
  `value` text NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```
`ensureSysVarsTableExistence()` (`update.php:108`) **self-heals** by creating this table if missing,
so the migration system can bootstrap itself. Keys used:
- `latest_update_version` — highest product update applied.
- `latest_customization_update_version` — highest customization update applied.
- `failed_update_detected` — set to `'1'` if a migration threw (see §6).
- `failed_update_script` — the version string of the script that threw, so the freeze can name it.
- `failed_update_error` — the exception class, message and file:line of that failure.

Values are read/written via `ConfigboxSystemVars::getVar()/setVar()`.

---

## 3. Fresh install bootstrap

On a brand-new install, `canInstallFresh()` (`update.php:16`) detects the empty state and
`installFresh()` (`:286`) lays down a **baseline schema** rather than replaying a decade of incrementals. The
baseline version is a local variable in `installFresh()` (currently `3.6.3`) and names the bundle in
`helpers/updates/complete/`:
1. Executes `complete/<baseline>_ddl.sql` — the full baseline DDL, using the standard `#__` prefix
   placeholder, split on `;` and run query-by-query. Because of that split,
   the DDL file must never contain a `;` inside a comment or string literal. FK checks are switched
   off around creation (tables are ordered alphabetically, not by dependency).
2. Sets `latest_update_version` to the baseline right after DDL+DML — BEFORE the files step — so a
   files-step failure cannot send the fresh baseline schema through the historic script chain.
3. **Normalizes the charset**: loops `getNonConformingCharsetTables()` through
   `convertTableToTargetCharset()`. The baseline DDL is deliberately collation-free (that is what
   makes it portable), so the tables it just created inherited whatever the server's default is; this
   pass brings them all to `TARGET_CHARSET`/`TARGET_COLLATION` (§4a) so a fresh install does not
   immediately trip the dashboard's charset health check. On an empty DB the `ALTER`s are instant.
4. `require`s `complete/<baseline>_dml.php` — baseline seed data (DML as PHP).
5. `require`s `complete/<baseline>_files.php` — baseline filesystem setup.

From there, only incremental scripts newer than the baseline run (§4). (Consequence: incremental
scripts *older* than the baseline never run on fresh installs — they exist only to migrate old
installations forward.) The repo's `deployment/database.sql` is a full Joomla+CBX dump used to
seed a dev site; the canonical product baseline for installs is the `complete/` set.

**Rebasing the baseline** (done 3.3.3.10 → 3.6.3 in July 2026): dump the schema of a DB that has
every migration applied (`mysqldump --no-data`), strip charset/collation table options and
per-column echoes (fresh installs inherit the DB default, as always), replace the dump's real prefix
with `#__` ONLY in table-name contexts (`CREATE TABLE`/`REFERENCES`/`CONSTRAINT` — a blanket replace
would corrupt the commerce2 `cbx_*_party_id` COLUMN names on the carts table), add `IF NOT EXISTS`,
and check for
semicolons inside comments/strings. The DML carries the seed logic forward (plus any seed inserts
newer scripts introduced); pure data transforms of existing rows are no-ops on an empty DB and stay
out. Verify by round-tripping the DDL through the exact `explode(';')` semantics into a scratch DB
and diffing columns/indexes/FKs against the source schema via information_schema.

---

## 4. Incremental update scripts

The core loop, `getUnprocessedUpdateScripts()` + `runUpdateScripts()` (`update.php:468`/`:512`):
1. Read the stored version (defaults to `0.0.0` if unset).
2. List every `*.php` in the updates dir, sort by **`version_compare`**.
3. Keep those whose filename version is **greater than** the stored version.
4. `require` each in order; after each file, bump the sys-var to that file's version.

So each file is named for the version it brings the schema to — e.g. `3.4.1.1.php`, `3.5.0.php` —
and is applied exactly once, in version order. There are 150+ such scripts in `helpers/updates/`,
starting at `1.0.0`; **the highest file name is the newest shipped version** (also the
"newest shipped" line of `configbox:migrate --status`), so count and ceiling are read off the
directory, not off this page.

### What an update script is
A plain PHP script (guarded by `defined('CB_VALID_ENTRY') or die();`) that does whatever the change
needs — DDL, data backfill (DML), or filesystem work — using the platform DB handle. There is no
declarative DSL; it's imperative PHP. Examples from the repo:

**Filesystem-only change** (`helpers/updates/3.5.0.php`): creates the MaxMind GeoIP data folder and a
readme — migrations aren't limited to SQL.

**Adding a column (the canonical pattern for a new Kenedo property)** —
`helpers/updates/3.4.1.1.php`:
```php
<?php
defined('CB_VALID_ENTRY') or die();
$db = KenedoPlatform::getDb();

if (ConfigboxUpdateHelper::tableFieldExists('#__configbox_config', 'add_requirejs_onload') == false) {
    $query = "ALTER TABLE `#__configbox_config`
              ADD `add_requirejs_onload` VARCHAR(1) DEFAULT '1'";
    $db->setQuery($query);
    $db->query();
}
```

**Modifying a column** (`3.4.1.2.php`): wraps the `ALTER … MODIFY COLUMN` in a
`tableFieldExists(...) == true` guard so it's safe to re-run and safe on installs where the column
isn't present.

The recurring theme is **idempotency**: every script checks current schema state before changing it,
so re-running (or running against a slightly divergent install) is harmless.

### 4a. Character set and collation — `utf8mb4` / `utf8mb4_unicode_ci`

**Every CBX table and text column is `utf8mb4` with the `utf8mb4_unicode_ci` collation.** The pair is
declared once, in code:

```php
ConfigboxUpdateHelper::TARGET_CHARSET   = 'utf8mb4';
ConfigboxUpdateHelper::TARGET_COLLATION = 'utf8mb4_unicode_ci';
```

Those two constants are the single source of truth — the dashboard health check
(`models/dashboard.php`), the admin charset fixer (`views/admincharsetfixer`), the
`configbox:charset` CLI command and the fresh-install normalize pass (§3) all read them. Never
hardcode a different collation beside them.

`utf8mb4_unicode_ci` is chosen over `utf8mb4_0900_ai_ci` **deliberately**: `_0900_` exists only on
MySQL 8 (not on MySQL 5.6/5.7, not on any MariaDB), whereas `_unicode_ci` is available on every
platform CBX supports, and it matches Joomla's own default collation. `utf8`/`utf8mb3` is not an
option — it is 3-byte and cannot store emoji or parts of CJK.

**A `CREATE TABLE` in an update script must spell the collation out:**

```sql
) ENGINE=InnoDB;                                                    -- OK: inherits the DB default
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;                            -- WRONG, and silently so
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- write this
```

The middle form is the trap. Naming a charset **without** a `COLLATE` clause does not inherit the
database's collation — it takes *that charset's server-dependent default*: `utf8mb4_0900_ai_ci` on
MySQL 8, `utf8mb4_general_ci` on 5.7, `uca1400_ai_ci` on recent MariaDB. The statement that looks
most explicit is the one that drifts per server, and nothing complains at creation time. The damage
surfaces later: comparing or joining a column across two collations fails with **`Illegal mix of
collations`**, which can be months after the table was added.

This is not hypothetical. Scripts `3.7.2`, `3.7.5`, `3.7.6` and `3.7.7` omitted the clause, so the
six chat-advisor and AI-config tables sat on `utf8mb4_0900_ai_ci` while the other ~64 were
`utf8mb4_unicode_ci` — on fresh installs too, since the baseline is `3.6.3` and those scripts run
*after* the normalize pass. `3.8.3.php` is the repair, and doubles as the model for any normalize
step: loop `getNonConformingCharsetTables()` through `convertTableToTargetCharset()`, which is
idempotent because the first call returns only tables that actually deviate.

Auditing or repairing an install:

```bash
php cli/joomla.php configbox:charset --status   # list non-conforming tables, change nothing
                                               # (non-zero exit, so a deploy step can gate on it)
php cli/joomla.php configbox:charset            # convert them; safe to re-run
```

---

## 5. The idempotent-DDL toolkit

`ConfigboxUpdateHelper` provides schema-introspection helpers (all `#__`-prefix aware) so scripts can
guard their changes:

| Helper | Use |
|--------|-----|
| `tableExists($table)` (`:706`) | guard `CREATE TABLE` / dependent changes |
| `tableFieldExists($table, $col)` (`:730`) | guard `ADD`/`MODIFY COLUMN` (queries `INFORMATION_SCHEMA.COLUMNS`) |
| `getColumnNames($table)` / `getTableFields($table)` (`:562`/`:653`) | inspect columns/definitions |
| `keyExists($table, $keyName)` / `getKeyNames($table, $col)` (`:778`/`:804`) | guard index changes (`SHOW INDEX`) |
| `getFkConstraintName($table, $col)` (`:758`) | find a FK constraint name before dropping/altering it |
| `getTableList($withPrefixPlaceholders)` (`:673`) | enumerate CBX tables |

Use these instead of assuming a prior state — installs in the wild diverge (manual edits, partial
upgrades, the parallel customization track).

---

## 6. Safety: locking & failure handling

**Concurrency lock.** `applyUpdates()` first runs a cheap, lock-free pending check
(`updatesArePending()`) — on the countless requests where the install is up to date, that is the
whole cost. Only when work is pending does it try to acquire a **MySQL advisory lock**
(`GET_LOCK`, non-blocking, name hashed from schema + table prefix in `getUpdateLockName()`).
Exactly one process wins and migrates; losers log a skip line to `upgrade_errors` and return
`'busy'`. The winner **re-checks pending work under the lock** (the racer it beat may have just
finished it). The lock lives in the database — the resource actually being protected — so it also
guards multi-webserver setups, and MySQL releases it automatically if the holding connection dies,
so a killed process can never freeze future update runs. (Earlier versions used a tmp-file marker;
it was not atomic, was per-host, and a stale marker after a hard kill silently blocked all updates.)

**Fail-stop.** If any script throws, `applyUpdates()` catches the `Throwable`, sets
`failed_update_detected = '1'`, logs the message + file/line + trace to the `upgrade_errors` log,
releases the lock (a `finally` block guarantees it), and rethrows. While that flag is set, **all future migration runs are
skipped** until an admin investigates and clears it — this prevents a half-applied schema from being
repeatedly hammered. The admin **Dashboard** surfaces "failed upgrade" as a critical issue
(`models/admindashboard.php`).

Because there is **no down-migration / rollback** concept, the guard-and-fail-stop approach is the
safety net: scripts must be forward-only and idempotent.

**Diagnosing a failure.** Each script logs a line to `upgrade_errors` *before* it runs
(`runUpdateScripts()`), which is the only breadcrumb left when a script kills the process outright —
OOM, `max_execution_time`, a segfault — where no catch block ever executes. On a caught failure the
version in flight is recorded to `failed_update_script` and the exception to `failed_update_error`,
so the freeze can explain itself long after the fact.

**Lifting the freeze.** Nothing clears `failed_update_detected` automatically; it is an explicit
operator decision. Use the CLI:

```
php cli/joomla.php configbox:migrate --status              # what is pending / why it is frozen (changes nothing)
php cli/joomla.php configbox:migrate:unblock --dry-run     # explain the block, incl. the log tail
php cli/joomla.php configbox:migrate:unblock               # clear the freeze (prompts unless -n)
php cli/joomla.php configbox:migrate -v                    # apply, with full failure diagnostics
```

`configbox:migrate:unblock --skip-version=<v>` additionally moves the version pointer so that script
and everything below it counts as applied **without running** — the escape hatch for a script that
cannot be made to pass because its effect is already in the schema. It is a loaded gun: if the schema
does not actually match, later migrations fail in more confusing ways. Prefer fixing the script.
`--track=customization` targets the customization pointer instead of the core one.

---

## 7. Customization migrations

Customers/integrators get their own track so their schema changes don't collide with product
upgrades: drop versioned `*.php` scripts into the **customization dir's `updates/` folder**.
That dir is a different place on each platform (`KenedoPlatform::getDirCustomization()` is the
authority):

| Platform | Custom migration scripts live in |
|---|---|
| Joomla | `components/com_configbox/data/customization/updates/` |
| WordPress | `wp-content/plugins/configbox-customization/updates/` — a **separate plugin directory**, deliberately outside the `configbox` plugin so it survives plugin updates |
| Magento 2 | the `Rovexo_ConfigboxCustomizations` module's `customizations/updates/` dir (resolved via the module reader; without that module installed there is no customization dir) |

`applyUpdates()` runs them with the same logic, tracked by
`latest_customization_update_version`. This pairs with the other
customization-dir extension points (custom models, property definitions via
`model_property_customization`, rule conditions, calc terms — see the other docs).

---

## 8. How to add a migration (developer workflow)

To add, say, a new column backing a new Kenedo property:

1. **Pick a version** higher than the newest file already in `helpers/updates/` — equivalently, the
   "newest shipped" line of `configbox:migrate --status`. Do **not** derive it from the
   `<version>` in `administrator/components/com_configbox/configbox.xml`: the manifest is not bumped
   per migration and lags far behind the update scripts (it reads `3.4.0` while shipped scripts are
   well past that) — a script versioned off the manifest would sort *below* already-applied ones and
   never run.
2. **Create `helpers/updates/<version>.php`** with an idempotent change:
   ```php
   <?php
   defined('CB_VALID_ENTRY') or die();
   $db = KenedoPlatform::getDb();
   if (ConfigboxUpdateHelper::tableFieldExists('#__configbox_elements', 'my_new_flag') == false) {
       $db->setQuery("ALTER TABLE `#__configbox_elements` ADD `my_new_flag` VARCHAR(1) DEFAULT '0'");
       $db->query();
   }
   ```
3. **Use `#__` as the table prefix placeholder** (the DB layer rewrites it at query time).
4. **Add the matching property definition** to the model's `getPropertyDefinitions()` (see
   `com_configbox_kenedo_mvc.md` §4) so the column becomes a real field in form/list/SQL.
5. If shipping a new baseline, also update the `complete/` DDL/DML so fresh installs include the
   change without replaying the incremental.
6. Test by bumping a dev install: the script runs on the next request and `latest_update_version`
   advances to your file's version.

**Conventions & cautions**
- **Forward-only & idempotent** — guard every DDL with the introspection helpers; assume re-runs.
- **No destructive changes without guards** — dropping columns/tables is irreversible (no rollback).
- **Keep scripts fast and self-contained** — they run inline on a normal request.
- **Don't reorder/rename** already-shipped scripts — version order + the stored ledger assume stable
  filenames.
- A failed script halts the whole chain until `failed_update_detected` is cleared, so make scripts
  defensive (check before you change).

---

## 9. Relationship to packaging/installer

The Joomla component installer (`administrator/components/com_configbox/installer_file.php`) does
**not** carry the schema; its postflight mainly purges caches and removes legacy modules. All real
schema/version work happens through this runtime migration system, which is why simply deploying new
component files (e.g. via the git submodule) is enough to evolve the database on the next request.

---

## 10. Summary

- Migrations are **runtime, idempotent, forward-only PHP scripts**, applied automatically on each
  request and tracked in `#__configbox_system_vars`.
- Fresh installs get a **baseline** (`complete/<baseline>_*`, currently `3.6.3`) then incrementals;
  existing installs replay only the scripts newer than their stored version.
- A **lock** prevents concurrent runs; a **fail-stop flag** halts the chain on error (surfaced on the
  dashboard); there is **no rollback**.
- Schema introspection helpers (`tableFieldExists`, `keyExists`, …) make scripts safe to re-run.
- Adding a field = a versioned `helpers/updates/<version>.php` migration **plus** a property
  definition on the model.

---

## See also

- `com_configbox_cli_commands.md` §1.2–§1.3 — `configbox:migrate`, `configbox:migrate --status` and
  `configbox:migrate:unblock`, the operator-facing side of this engine (deploy gating, failure
  diagnostics, the `--skip-version` escape hatch).
- The **`configbox-migrations` skill** in the dev tooling (the CBX dev sites' `.claude` setup) —
  the authoring workflow for writing, running and debugging update scripts; use it before writing
  or debugging a migration.
- `com_configbox_kenedo_mvc.md` §4 — the property definition that pairs with a new column.