Schema management, DB_SYNC and the one-off migration scripts

There are no migration files. Nothing in this product resembles a numbered migrations/ directory you can replay. The schema is applied by Sequelize's auto-sync during boot, and the small number of changes auto-sync physically cannot make ship as standalone scripts you run by hand, gated on the release that needs them.

That second half is the part that is easy to miss, and missing it is silent: the platform starts, the panel loads, and one table is a shape the code no longer expects.

How the schema is applied

Sequelize compares the model definitions against a fingerprint manifest and emits DDL for whatever moved. The mode is DB_SYNC in .env.

An unset DB_SYNC behaves as lazy.

What the manifest actually compares

backend/.sync-hash is a JSON manifest — version 2 — holding, per model: the physical table name, a hash of the model's declared indexes, and a hash per column of everything Sequelize can turn into DDL (type and its options, ENUM members, allowNull, default, primary key, auto-increment, uniqueness, comment, and the foreign-key reference with its ON DELETE / ON UPDATE rules).

Two things follow from that, and both matter operationally:

• It fingerprints the model definitions, not the database. A column hand-altered in MySQL does not change the fingerprint, so lazy concludes nothing moved and skips it. This is exactly why restoring a database underneath an install needs one DB_SYNC=always run — see Backup and restore.
• A comment is not a schema change. The v1 format hashed the text of every model file, so any edit anywhere forced a full alter sync of the whole schema: on 229 tables and roughly 2 800 columns that is about three minutes, because Sequelize's alter path re-emits ALTER TABLE … CHANGE COLUMN for every column of every table without diffing, plus a drop and re-add of all ~280 foreign keys. Version 2 makes the skip granular.

A manifest that is missing, unreadable or still in the v1 format is treated as "nothing can be vouched for" and falls back to a full alter sync. Deleting backend/.sync-hash therefore costs you one slow boot and nothing else.

An alter sync on MySQL also rewrites foreign keys in an order that is not stable, so a pass can try to drop a constraint an earlier statement in the same pass already removed:

Can't DROP FOREIGN KEY `user_ibfk_1`; check that it exists

That is the sync tripping over its own bookkeeping, not a broken schema. It is retried three times and usually converges. If it still cannot finish, the server starts anyway and logs that the schema may be behind the models — a running server you can fix beats a boot loop.

Where the schema step sits in an update

pnpm updator is a single &&-joined chain:

pnpm stop  →  ensure-deps  →  pnpm updator:migrate  →  pnpm seed  →  pnpm build:frontend  →  pnpm start

pnpm updator:migrate runs scripts/updator-migrate.js. Because Sequelize applies the schema during boot, the migration step is a boot: it runs backend/dist/index.js directly — not under PM2, so nothing can restart it behind your back — with CRON_MODE=off so it schedules nothing, with BICRYPTO_SCHEMA_SYNC_ONLY=true so the boot stops after the database phase, on the first port from NEXT_PUBLIC_BACKEND_PORT + 1 upward that is both bindable and not already accepting connections (4001–4020 on a default install). It then waits for GET /api/settings to answer — the observable that says initialisation finished — and kills the process.

The cap is 180 seconds. A genuinely large schema needs longer:

node scripts/updator-migrate.js --timeout=600000

The BICRYPTO_SCHEMA_SYNC_ONLY flag exists because CRON_MODE=off alone was not enough. The boot still ran the extensions phase — the ecosystem matching engine loading every open order into memory, the futures matcher, forex venue connections, the Hummingbot supervisor — and on a large install that reached the 7.7 GB heap cap and was killed. An install could grow too large to update, while the update was the thing that would have fixed it. None of that phase has any bearing on a schema sync, which takes about 38 ms.

The version-gated one-off scripts

Some changes auto-sync cannot make at all. sync({ alter: true }) never drops a column, never drops an index, never drops a table, and cannot modify an existing foreign key's ON DELETE rule. Others it would make but only after data is cleaned up first — you cannot add a UNIQUE index to a table that already holds duplicates.

Those ship as pairs of package scripts, one per release that needs them.

The 6.6.3 pair carries no :before or :after because it changes no schema at all — it repairs the contents of two columns and can be run at any point, before or after the sync, with the platform up or down.

"Before" means before the schema sync — the data has to be cleaned up so the sync can then create the index or tighten the column. "After" means after it — these drop or rewrite things the sync has just declined to touch. In terms of the update chain: run a :before chain after pnpm stop and before pnpm updator:migrate, and an :after chain once the platform is back up.

Every script in every chain is idempotent. Each guards its DDL with an INFORMATION_SCHEMA existence check and discovers real constraint and index names at runtime rather than hardcoding them, so a second run — with or without --apply — is a clean no-op. Run the report form freely.

What each script fixes

All live in backend/scripts/ and can also be run individually, with the same --apply rule.

Seed data

pnpm seed

That is sequelize-cli db:seed:all --config ./config.js inside backend/. It runs every seeder in backend/seeders/ on every invocation — there is no "already applied" ledger — which is why each one is written as an idempotent insert: it reads what is already there and inserts only what is missing. The permissions seeder, for example, selects the existing permission rows and bulk inserts only the names not present.

Seeders carry the reference data the code expects to exist: the permission list, the four roles and the Super Admin account, fiat currencies, deposit gateways, notification templates, ecosystem blockchains and tokens, exchange rows, KYC services, the extension catalogue, blog and ecommerce slugs, reward conditions and the newer per-release rows.

It runs after the schema step and never before it, because it writes through the new models.

Types must regenerate before a build

pnpm --filter backend types:generate

This parses every model in backend/models/ with a TypeScript AST and writes backend/types/models.ts, caching a hash of the model files in backend/.types-hash so an unchanged tree is a no-op. types:generate:force ignores the cache. It runs automatically as prebuild and predev, so a normal pnpm build:backend already does it.

MySQL strict mode, and why some changes are a script

Under STRICT_TRANS_TABLES, a direct CHANGE COLUMN from tinyint to an ENUM is rejected — Data truncated for column 'status' — because the existing 0 and 1 are not members of the target ENUM. Sequelize's alter sync emits exactly that statement and fails.

The conversion has to go in three steps: widen to VARCHAR (so 0/1 become the strings '0'/'1'), rewrite every value to a valid ENUM label, and only then tighten to the ENUM. That sequence cannot be expressed as a model definition, which is why migration-ecosys06-broadcast-hash-lock.mjs exists as a script rather than as a column change — and why it snapshots the raw values first and can resume a run interrupted mid-conversion.

The same reasoning is behind every other script in the list: dropping something, rewriting a foreign-key rule, or cleaning data so a constraint can be added are all outside what an alter sync can do.

Before you migrate: take the dump

mysqldump -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p \
  --single-transaction --quick --routines --triggers --events \
  --default-character-set=utf8mb4 \
  "$DB_NAME" | gzip > /var/backups/pre-migration-$(date +%F-%H%M).sql.gz

--single-transaction is what the built-in backup screen is missing — it gives an InnoDB-consistent dump without locking the platform. --default-character-set=utf8mb4 is what keeps emoji in tickets and display names intact. Full detail in Backup and restore.

Telling drift from a failed migration

They present the same way — a column or index the code expects is not there — and the fix is different.

For drift, force one full reconcile and then take the flag back out:

DB_SYNC=always pnpm start

To see the duplicate constraints an alter sync is tripping over:

SELECT TABLE_NAME, COLUMN_NAME, COUNT(*)
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE() AND REFERENCED_TABLE_NAME IS NOT NULL
GROUP BY TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME
HAVING COUNT(*) > 1;

The symptom-first version of this is under The schema migration fails in Troubleshooting. For repair scripts that fix data rather than schema, see Repair and reconcile scripts.

A fresh install imports initial.sql

initial.sql at the project root is the schema a brand-new install loads before the seeders run, and it is generated from the models rather than hand-kept — a table missing from it is not a slow first boot, it is a failed install. pnpm initial-sql regenerates it; pnpm initial-sql:check compares it against the models but needs a reachable MySQL, which is why it is not part of the routine gate. Neither is something an operator runs on a live install.