Upgrading Ecosystem

Updating Ecosystem is the core update chain plus a handful of one-time schema scripts that nothing runs for you. pnpm updator applies the Sequelize schema sync and the seeders; it does not run these. Extracting the release files and restarting does not run them either.

The cost of skipping them is not cosmetic. The one that matters converts ecosystem_utxo.status from a boolean into a three-state ENUM, and the middle state — LOCKED — is what stops a Bitcoin-family withdrawal that crashed mid-broadcast from having its inputs selected again by the next withdrawal. Without it the platform can broadcast a second transaction spending coins that are already gone.

What actually has to be applied by hand

Both live in backend/scripts/. Both read the project-root .env regardless of the directory you run them from, both are dry-run by default, and both are idempotent — re-running after a successful apply is a reported no-op.

--apply is the only thing that writes. Without it the script connects, inspects, prints a report of every change it would make, and exits.

Before you start

• A MySQL dump you have actually verified — /admin/system/database/backup
• A ScyllaDB snapshot (nodetool snapshot), because nothing in the product backs it up
• A copy of .env off the box — see Updating for why this one is unrecoverable
• Shell access to the project root; none of this has an admin-panel equivalent
• The withdrawal queue quiet — no PROCESSING UTXO withdrawals in flight

The last item is worth a minute. The ECOSYS-06 script rewrites the status column of every row in ecosystem_utxo. Doing that while a withdrawal is choosing inputs is asking for a torn read of the one table that decides which coins are spendable. Stop the backend, or at minimum stop the process that owns the matching engine and the withdrawal queue.

The safe sequence

The core release also ships an :after pair — pnpm db:migrate:6.4.9:after and :after:apply — which drops old indexes, re-points foreign keys and backfills method status. Those are core concerns rather than Ecosystem ones, but they run in the same window and are documented in the Core v6.4.9 patch notes.

What db:migrate:6.4.9:before runs

The command is a chain of three scripts, and only the first is Ecosystem's. They run in order and && stops the chain on the first failure.

The second and third exist for the same structural reason as the first: the models now declare unique indexes that sync({alter:true}) cannot create while duplicate rows are present, so the sync fails and the update stops. If you do not run KYC or the NFT Marketplace those two are no-ops — they check information_schema and report nothing to do.

ECOSYS-06, in detail

This is the migration that touches money. Four changes, each guarded by an information_schema existence check so a second run changes nothing.

ecosystem_utxo.status becomes an ENUM

BOOLEAN becomes ENUM('UNSPENT','LOCKED','SPENT') NOT NULL DEFAULT 'UNSPENT', with the existing data preserved: 0 becomes UNSPENT, 1 becomes SPENT.

The conversion does not go straight from tinyint to ENUM. It snapshots the raw 0/1 values into a temporary status_backup column, widens status to VARCHAR(20), rewrites every value to a valid label, and only then tightens the column to the ENUM — because a direct cast is rejected in strict mode. The recovery path is driven by whether status_backup still exists rather than by the column type, so a crash at any point of the conversion is fully recovered by re-running the script.

Why LOCKED is not optional

UTXO withdrawals in backend/src/api/(ext)/ecosystem/utils/utxo.ts sign and broadcast the transaction before the database reservation commits. That ordering is deliberate — the coins have to be on-chain before the platform can honestly record them as gone — but it leaves a window.

Crash inside that window with only a boolean column and two things are true at once: the selected inputs are still marked spendable, so the next withdrawal can select them again and broadcast a second transaction spending coins that have already left; and the broadcast hash is nowhere, so there is nothing to check the chain against and no idempotent retry.

The fix needs both new pieces of schema:

• ecosystem_utxo.status = 'LOCKED' plus ecosystem_utxo.lockedTxId (VARCHAR(191)), written in the same committed transaction as the broadcast hash. lockedTxId carries the broadcast txid that reserved the input, so promoting LOCKED to SPENT is scoped to one withdrawal and two concurrent withdrawals on the same wallet cannot release each other's inputs.
• transaction.txHashPending (VARCHAR(191)), written outside the database transaction, before the network call, so a rollback cannot erase it.

At boot the withdrawal queue's recovery pass reads those columns and decides per row:

txHashPending is declared on the transaction model, so the schema sync would eventually add it. status as an ENUM is the piece the sync cannot get right, and LOCKED is meaningless without it.

transaction_ledger_applied

A bridge table the script creates, with UNIQUE(transactionId, walletId, currency, chain), intended so a private-ledger decrement can be applied at most once per combination — a duplicate would become a constraint violation instead of a silent double-spend.

That guard is not in force today. Nothing in the product writes to or reads this table: searching backend/src, backend/models, backend/dist and the frontend for transaction_ledger_applied finds the migration script and nothing else. Like the ECOSYS-09 columns below, it is schema laid down ahead of the code that will use it — creating it costs nothing, and it is not the reason ECOSYS-06 is urgent. The UTXO status ENUM is.

No Sequelize model declares this table either, which means the schema sync will neither create it nor repair it. If this script did not create it, it does not exist on your install.

The foreign key to transaction(id) is only added when the parent table is present, and the script reads the parent column's exact type, character set and collation before creating the child. On this codebase transaction.id is char(36) / utf8mb4_bin rather than the table default, and a mismatch makes InnoDB reject the constraint with errno 150.

Two indexes

• idx_status_wallet_locked on ecosystem_utxo (status, walletId) — the scan of LOCKED outputs during recovery and validation.
• idx_status_trxid_recovery on transaction (status, trxId, createdAt) — the scan for broadcast PROCESSING rows at boot.

Both are declared on their models as well, so the sync would create them too. The script adds them ahead of it so the recovery pass is fast on its first run.

If the ecosystem_utxo table is absent — Ecosystem not installed — the script reports [SKIP] for the UTXO steps and carries on with the rest. It is safe on a core-only install.

ECOSYS-09, and what it is not

Ecosystem orders do not live in MySQL. There is no ecosystemOrder table. Orders, candles, the order book, the trade tape, the open-orders index and stop orders are all in ScyllaDB, in the keyspace named by SCYLLA_KEYSPACE (trading by default). Any migration touching an order is CQL, not SQL, and information_schema has nothing to say about it.

migration-ecosys-09-worst-case-hold.mjs adds two columns to <keyspace>.orders:

Both identifiers are created quoted, so they keep their camel case the way the rest of the orders schema does.

If the orders table does not exist in the keyspace the script prints [ABORT] and changes nothing — that means the Ecosystem Scylla schema has never been initialised, which is a boot problem, not a migration problem. See Install.

What the backend applies for you, and when

The Scylla client creates its keyspaces, tables and materialized views on first connection, and runs a small column migration list on every boot — the runMigrations() function in backend/src/api/(ext)/ecosystem/utils/scylla/client.ts. That list is per keyspace: marketMakerId, botId and walletType on the trading orders table; reduceOnly, positionId and isTaker on the futures one. Each statement is an ALTER TABLE ... ADD whose "already exists" error is swallowed, which is what makes it safe to run on every start.

The ECOSYS-09 columns are not in that list. Nothing adds them at boot.

There is a second-order trap here. pnpm updator does not apply the Scylla schema either. Its migration step runs backend/dist/index.js directly with CRON_MODE=off and BICRYPTO_SCHEMA_SYNC_ONLY=true, and that flag skips the entire Extensions boot phase — no matching engine, no futures matcher, no Scylla initialisation. That is deliberate: booting the whole server to migrate a schema once reached the 7.7 GB heap cap on a large install and made it impossible to update. The consequence for you is that every Scylla-side change, including runMigrations(), lands on the first normal backend start after the update, not during it.

backend/dist is what production runs

backend/dist is tracked in the repository, ships pre-built inside every release package, and is the file PM2 actually executes — production.config.js and production.backend.config.js both point at ./backend/dist/index.js. The update chain never runs build:backend for exactly that reason.

Two things follow:

• Editing backend/src changes nothing on a production box. The running process is loading dist. A source-only patch — a hotfix pasted into a .ts file, a merge that touched only src — is invisible until dist is rebuilt with pnpm bundle.
• A release replaces dist wholesale. Any local change you made to it is gone. updator:migrate refuses to start and halts the update if backend/dist/index.js is missing, telling you to restore it or run pnpm bundle.

Verifying

The MySQL side, from a shell:

SHOW COLUMNS FROM ecosystem_utxo LIKE 'status';
SHOW COLUMNS FROM ecosystem_utxo LIKE 'lockedTxId';
SHOW INDEX FROM ecosystem_utxo WHERE Key_name = 'idx_status_wallet_locked';
SHOW TABLES LIKE 'transaction_ledger_applied';

The Scylla side:

cqlsh -u bicrypto -p '…' -e "DESCRIBE TABLE trading.orders;"

Re-running either script with no --apply is the cheapest check of all — a correctly migrated install reports Schema already up to date — nothing to change.

When it goes wrong

Related

• Installing Ecosystem — ScyllaDB, the vault key, first boot
• Updating — the core update chain, link by link
• Operations — the repair scripts and what backups do not cover
• Environment reference — every variable the addon reads
• Troubleshooting — symptom-first diagnosis