{"id":193023,"date":"2025-08-16T17:51:11","date_gmt":"2025-08-16T15:51:11","guid":{"rendered":"https:\/\/fivemx.com\/?p=193023"},"modified":"2025-12-23T16:43:20","modified_gmt":"2025-12-23T15:43:20","slug":"migracao-de-identificadores-sql","status":"publish","type":"post","link":"https:\/\/fivemx.com\/pt\/sql-identifiers-migration\/","title":{"rendered":"Migra\u00e7\u00e3o de SQL e Identificadores: steam\/license \u2192 citize&amp;#\u2026"},"content":{"rendered":"\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>Use\u2011case:<\/strong> You\u2019re moving from ESX to QBCore or QBOX (qbx_core) and need a clean, auditable migration of player identifiers and balances. This guide gives you production\u2011ready SQL, a reversible plan, and validation steps.<\/p>\n<\/blockquote>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Related reads:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Adapter Patterns: ESX\u2194QBCore\u2194QBOX (Exports, Events &amp; Player Models)<\/strong> \u2014 <a href=\"https:\/\/fivemx.com\/adapter-patterns\/\">https:\/\/fivemx.com\/adapter-patterns\/<\/a><\/li>\n\n\n\n<li><strong>Converting FiveM Scripts \u2013 ESX, QBCore, QBOX (Framework Guide)<\/strong> \u2014 <a href=\"https:\/\/fivemx.com\/converting-fivem-scripts\/\">https:\/\/fivemx.com\/converting-fivem-scripts\/<\/a> <em>(Pillar)<\/em><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">What changes between ESX and QBCore\/QBOX<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><th>Topic<\/th><th>ESX (common)<\/th><th>QBCore \/ QBOX (common)<\/th><\/tr><tr><td><strong>Primary player key<\/strong><\/td><td><code>identifier<\/code> (e.g., <code>license:xxx<\/code> or legacy <code>steam:xxx<\/code>)<\/td><td><code>citizenid<\/code> (server\u2011generated token)<\/td><\/tr><tr><td><strong>Alt identifiers<\/strong><\/td><td><code>users.identifier<\/code>, sometimes a separate <code>identifiers<\/code> table<\/td><td>columns like <code>license<\/code>, <code>steam<\/code>, <code>fivem<\/code> stored alongside <code>citizenid<\/code><\/td><\/tr><tr><td><strong>Money model<\/strong><\/td><td>Separate accounts (cash\/bank\/black_money) via <code>users.accounts<\/code> (JSON) <strong>or<\/strong> <code>user_accounts<\/code> rows<\/td><td>Single <code>money<\/code> JSON on <code>players<\/code> (e.g., <code>{ \"cash\": 0, \"bank\": 5000 }<\/code>); optional extra wallets<\/td><\/tr><tr><td><strong>Vehicles<\/strong><\/td><td><code>owned_vehicles.owner<\/code> refers to ESX <code>identifier<\/code><\/td><td><code>player_vehicles.citizenid<\/code> (or <code>license<\/code> on some forks)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">QBOX generally follows QB\u2019s DB shape. Treat QBOX as &#8220;QB schema + qbx additions.&#8221; Always diff your live schema.<\/p>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">Golden rules (don\u2019t skip)<\/h2>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><strong>Freeze writes<\/strong> during migration (stop the game server + any external bots touching DB).<\/li>\n\n\n\n<li><strong>Full backup<\/strong> and a <strong>dump of table structures<\/strong>. Store both with timestamps.<\/li>\n\n\n\n<li><strong>Work in a transaction<\/strong> per table if possible; keep steps idempotent.<\/li>\n\n\n\n<li><strong>Create a crosswalk<\/strong> (<code>old_identifier<\/code> \u2192 <code>citizenid<\/code>) you can reuse or roll back to.<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\">Target you\u2019re aiming for (QB\/QBOX baseline)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">A typical <code>players<\/code> table (columns vary by fork):<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Inspect your actual schema and adjust.\nDESCRIBE players; -- Expect columns like: citizenid, license, name, money, charinfo, job, gang, metadata<\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>citizenid<\/strong>: primary key used across QB\/QBOX.<\/li>\n\n\n\n<li><strong>license\/steam<\/strong>: keep for forensics and re\u2011linking.<\/li>\n\n\n\n<li><strong>money (JSON)<\/strong>: e.g. <code>{\"cash\":123,\"bank\":456}<\/code>. Some servers add <code>crypto<\/code>, <code>dirty<\/code>, etc.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">Step 0 \u2014 Snapshot &amp; staging<\/h2>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\"># MySQL\/MariaDB backup\nmysqldump -u root -p --routines --triggers yourdb &gt; yourdb_$(date +%F_%H%M).sql\n\n# Optional: structure\u2011only snapshot\nmysqldump -u root -p --no-data yourdb &gt; yourdb_schema_$(date +%F_%H%M).sql<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Spin up a staging copy. Run everything there first<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 1 \u2014 Build the <strong>crosswalk<\/strong> table<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">We\u2019ll map every ESX <code>identifier<\/code> to a new <code>citizenid<\/code>. If you already have a <code>players<\/code> table with citizenids, you\u2019ll invert the mapping (see <em>Existing QB players<\/em> note below).<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- 1) Create crosswalk\nCREATE TABLE IF NOT EXISTS identifier_crosswalk (\n  old_identifier VARCHAR(60) PRIMARY KEY,\n  citizenid      VARCHAR(20) NOT NULL,\n  license        VARCHAR(60) NULL,\n  steam          VARCHAR(60) NULL,\n  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\n\n-- 2) Seed from ESX users (adjust table\/column names to your ESX flavor)\n-- Common ESX has `users.identifier` holding license:xxx or steam:xxx\nINSERT IGNORE INTO identifier_crosswalk (old_identifier, license, steam, citizenid)\nSELECT\n  u.identifier AS old_identifier,\n  CASE WHEN u.identifier LIKE 'license:%' THEN u.identifier ELSE NULL END AS license,\n  CASE WHEN u.identifier LIKE 'steam:%'   THEN u.identifier ELSE NULL END AS steam,\n  UPPER(SUBSTRING(REPLACE(UUID(),'-',''),1,10)) AS citizenid\nFROM users u;\n\n-- 3) If you have a separate `identifiers` table, merge best\u2011known values\n-- Example (optional): prefer license when available\nUPDATE identifier_crosswalk x\nJOIN (\n  SELECT i1.identifier AS old_identifier,\n         MAX(CASE WHEN i1.type='license' THEN i1.value END) AS license,\n         MAX(CASE WHEN i1.type='steam'   THEN i1.value END) AS steam\n  FROM identifiers i1\n  GROUP BY i1.identifier\n) i ON i.old_identifier = x.old_identifier\nSET x.license = COALESCE(i.license, x.license),\n    x.steam   = COALESCE(i.steam,   x.steam);\n\n-- 4) Uniqueness &amp; indexes\nALTER TABLE identifier_crosswalk\n  ADD UNIQUE KEY ux_cid (citizenid),\n  ADD KEY ix_license (license),\n  ADD KEY ix_steam (steam);<\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>Existing QB players?<\/strong> If you already have <code>players<\/code> rows, create the crosswalk by selecting their <code>license<\/code>\/<code>steam<\/code> and <strong>existing<\/strong> <code>citizenid<\/code> instead of generating new ones. Your crosswalk must never assign a new citizenid to an existing QB player.<\/p>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">Step 2 \u2014 Normalize\/prepare target <code>players<\/code> rows<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Create any missing <code>players<\/code> rows based on ESX <code>users<\/code>.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Ensure `players` exists and inspect its columns first.\n-- We\u2019ll insert shells for missing citizens only.\n\nINSERT INTO players (citizenid, license, name, money, charinfo, metadata)\nSELECT\n  x.citizenid,\n  COALESCE(NULLIF(x.license,''), NULLIF(x.steam,'')) AS license_like,\n  COALESCE(u.firstname, '') || ' ' || COALESCE(u.lastname, '') AS name_like,\n  '{\"cash\":0,\"bank\":0}' AS money,\n  JSON_OBJECT(\n    'firstName', COALESCE(u.firstname,''),\n    'lastName',  COALESCE(u.lastname,''),\n    'birthdate', COALESCE(u.dateofbirth,''),\n    'gender',    COALESCE(u.sex,'')\n  ) AS charinfo,\n  JSON_OBJECT('esx_identifier', u.identifier) AS metadata\nFROM users u\nJOIN identifier_crosswalk x ON x.old_identifier = u.identifier\nLEFT JOIN players p ON p.citizenid = x.citizenid\nWHERE p.citizenid IS NULL;<\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>Note:<\/strong> Use your SQL flavor\u2019s string concat (<code>CONCAT<\/code> in MySQL) and JSON functions accordingly. For MySQL 5.7, replace <code>JSON_OBJECT<\/code> with manual string building if needed.<\/p>\n<\/blockquote>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>MySQL\u2011safe variant:<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">INSERT INTO players (citizenid, license, name, money, charinfo, metadata)\nSELECT\n  x.citizenid,\n  COALESCE(NULLIF(x.license,''), NULLIF(x.steam,'')) AS license_like,\n  TRIM(CONCAT(COALESCE(u.firstname,''), ' ', COALESCE(u.lastname,''))) AS name_like,\n  '{\"cash\":0,\"bank\":0}' AS money,\n  CONCAT('{',\n    '\"firstName\":\"', REPLACE(COALESCE(u.firstname,''),'\"','\\\"'), '\",',\n    '\"lastName\":\"',  REPLACE(COALESCE(u.lastname,''),'\"','\\\"'),  '\",',\n    '\"birthdate\":\"', REPLACE(COALESCE(u.dateofbirth,''),'\"','\\\"'),'\",',\n    '\"gender\":\"',    REPLACE(COALESCE(u.sex,''),'\"','\\\"'),       '\"',\n  '}') AS charinfo,\n  CONCAT('{',\n    '\"esx_identifier\":\"', REPLACE(u.identifier,'\"','\\\"'), '\"',\n  '}') AS metadata\nFROM users u\nJOIN identifier_crosswalk x ON x.old_identifier = u.identifier\nLEFT JOIN players p ON p.citizenid = x.citizenid\nWHERE p.citizenid IS NULL;<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">Step 3 \u2014 Migrate <strong>Accounts \u2192 Money<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">There are two common ESX patterns:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">A) ESX stores balances inside <code>users.accounts<\/code> JSON<\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Example: users.accounts = '{\"bank\":5000, \"money\":750, \"black_money\":200}'\n\n-- 1) Extract from ESX JSON safely\n-- Create a temp view\/table with parsed numbers\nCREATE TEMPORARY TABLE esx_balances AS\nSELECT\n  u.identifier,\n  COALESCE(JSON_EXTRACT(u.accounts, '$.money'), 0)        AS esx_cash,\n  COALESCE(JSON_EXTRACT(u.accounts, '$.bank'),  0)        AS esx_bank,\n  COALESCE(JSON_EXTRACT(u.accounts, '$.black_money'), 0)  AS esx_black\nFROM users u;\n\n-- 2) Merge into QB\/QBOX money JSON\n-- Decide how to handle black_money (see Options below)\nUPDATE players p\nJOIN identifier_crosswalk x ON x.citizenid = p.citizenid\nJOIN esx_balances b ON b.identifier = x.old_identifier\nSET p.money = JSON_OBJECT(\n  'cash', CAST(b.esx_cash AS UNSIGNED),\n  'bank', CAST(b.esx_bank AS UNSIGNED)\n);<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>If MySQL without native JSON operations<\/strong> (or old version): build JSON strings using <code>CONCAT<\/code>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">B) ESX stores balances in <code>user_accounts<\/code> rows<\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Example: user_accounts(identifier, account, money)\nCREATE TEMPORARY TABLE esx_balances AS\nSELECT ua.identifier,\n       SUM(CASE WHEN ua.account='money'        THEN ua.money ELSE 0 END) AS esx_cash,\n       SUM(CASE WHEN ua.account='bank'         THEN ua.money ELSE 0 END) AS esx_bank,\n       SUM(CASE WHEN ua.account='black_money'  THEN ua.money ELSE 0 END) AS esx_black\nFROM user_accounts ua\nGROUP BY ua.identifier;\n\nUPDATE players p\nJOIN identifier_crosswalk x ON x.citizenid = p.citizenid\nJOIN esx_balances b ON b.identifier = x.old_identifier\nSET p.money = JSON_OBJECT(\n  'cash', CAST(b.esx_cash AS UNSIGNED),\n  'bank', CAST(b.esx_bank AS UNSIGNED)\n);<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Handling <strong>black_money<\/strong> (choose one)<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Option 1 (recommended):<\/strong> Create a dedicated wallet key in QB money JSON, e.g. <code>\"dirty\"<\/code>.<\/li>\n\n\n\n<li><strong>Option 2:<\/strong> Convert to items (e.g., marked bills) and credit inventory instead (requires item migration; out of scope here).<\/li>\n\n\n\n<li><strong>Option 3:<\/strong> Zero it out (strongly discouraged unless you\u2019ve announced a wipe).<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Option 1 implementation:<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Add dirty wallet in JSON (servers that support extra wallets)\nUPDATE players p\nJOIN identifier_crosswalk x ON x.citizenid = p.citizenid\nJOIN esx_balances b ON b.identifier = x.old_identifier\nSET p.money = JSON_MERGE_PATCH(p.money, JSON_OBJECT('dirty', CAST(b.esx_black AS UNSIGNED)));<\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">Ensure your framework\/resources actually respect the extra wallet. Otherwise prefer Option 2.<\/p>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">Step 4 \u2014 Re\u2011key foreign tables that referenced ESX <code>identifier<\/code><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Typical tables to fix:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>owned_vehicles.owner<\/code> \u2192 map to <code>citizenid<\/code> (QB: <code>player_vehicles.citizenid<\/code>)<\/li>\n\n\n\n<li>Any custom tables containing <code>identifier<\/code> columns (houses, billing, gangs, businesses)<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Vehicles (ESX \u2192 QB)<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- If you keep ESX `owned_vehicles`, re\u2011key owner \u2192 citizenid for forward compatibility\nALTER TABLE owned_vehicles ADD COLUMN citizenid VARCHAR(20) NULL;\n\nUPDATE owned_vehicles v\nJOIN identifier_crosswalk x ON x.old_identifier = v.owner\nSET v.citizenid = x.citizenid\nWHERE v.citizenid IS NULL;\n\nCREATE INDEX ix_ov_cid ON owned_vehicles (citizenid);<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">**Vehicles into QB\u2019s **&#8220; (minimal fields; adjust to your schema):<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">INSERT IGNORE INTO player_vehicles (citizenid, plate, vehicle, state, garage)\nSELECT x.citizenid, v.plate, v.vehicle, 0 AS state, 'A' AS garage\nFROM owned_vehicles v\nJOIN identifier_crosswalk x ON x.old_identifier = v.owner;<\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">Validate JSON field names (<code>vehicle<\/code> vs <code>mods\/props<\/code>) and column list against your actual QB\/QBOX schema.<\/p>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">Step 5 \u2014 Constraints, indexes, and integrity checks<\/h2>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Ensure primary\/unique keys\nALTER TABLE players\n  ADD UNIQUE KEY ux_players_citizenid (citizenid);\n\n-- Optional: keep a quick lookup by license\/steam\nALTER TABLE players\n  ADD KEY ix_players_license (license);\n\n-- Spot orphaned crosswalks (no players row)\nSELECT x.*\nFROM identifier_crosswalk x\nLEFT JOIN players p ON p.citizenid = x.citizenid\nWHERE p.citizenid IS NULL;\n\n-- Spot players with zeroed wallets (sanity)\nSELECT citizenid, money FROM players\nWHERE JSON_EXTRACT(money, '$.cash') IS NULL OR JSON_EXTRACT(money, '$.bank') IS NULL;\n\n-- Detect duplicates (same human with multiple identifiers)\nSELECT old_identifier, COUNT(*)\nFROM identifier_crosswalk\nGROUP BY old_identifier\nHAVING COUNT(*) &gt; 1;<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">Step 6 \u2014 Validation suite<\/h2>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><strong>Row counts:<\/strong> <code>COUNT(users)<\/code> \u2248 <code>COUNT(players)<\/code> (within expected deltas).<\/li>\n\n\n\n<li><strong>Balance totals:<\/strong> Sum of ESX cash\/bank \u2248 Sum of QB wallets after migration.<\/li>\n\n\n\n<li><strong>Sample audit:<\/strong> Pick 10 players by name; verify <code>citizenid<\/code>, balances, vehicles.<\/li>\n\n\n\n<li><strong>Login test:<\/strong> Bring server up in staging; log in a few known players; verify UIs.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Totals check examples:<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- ESX totals\nSELECT\n  SUM(COALESCE(JSON_EXTRACT(accounts,'$.money'),0)) AS esx_cash_total,\n  SUM(COALESCE(JSON_EXTRACT(accounts,'$.bank'),0))  AS esx_bank_total\nFROM users;\n\n-- QB totals\nSELECT\n  SUM(COALESCE(JSON_EXTRACT(money,'$.cash'),0)) AS qb_cash_total,\n  SUM(COALESCE(JSON_EXTRACT(money,'$.bank'),0)) AS qb_bank_total\nFROM players;<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">Step 7 \u2014 Runtime compatibility (adapters)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Even after migration, some legacy scripts may still reference ESX <code>identifier<\/code>. Keep the <strong>crosswalk<\/strong> and use a helper to resolve &#8220; (or inverse) at runtime.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Lua helper (server):<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">--- lookup_citizenid.lua\nlocal function getCitizenIdByIdentifier(identifier)\n    local result = MySQL.query.await('SELECT citizenid FROM identifier_crosswalk WHERE old_identifier = ? LIMIT 1', { identifier })\n    if result and result[1] then return result[1].citizenid end\n    return nil\nend\n\nreturn { getCitizenIdByIdentifier = getCitizenIdByIdentifier }<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Use this in legacy event handlers until all scripts are QB\/QBOX\u2011native. See the adapter patterns article for full interface shims.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Adapter Patterns:<\/strong> <a href=\"https:\/\/fivemx.com\/adapter-patterns\/\">https:\/\/fivemx.com\/adapter-patterns\/<\/a><\/li>\n\n\n\n<li><strong>Full conversion guide (Pillar):<\/strong> <a href=\"https:\/\/fivemx.com\/converting-fivem-scripts\/\">https:\/\/fivemx.com\/converting-fivem-scripts\/<\/a><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">Rollback strategy<\/h2>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li>Keep <code>identifier_crosswalk<\/code> and a <strong>pre\u2011migration backup<\/strong>.<\/li>\n\n\n\n<li>If something goes wrong, drop the new <code>players<\/code> rows created in this window and restore the backup.<\/li>\n\n\n\n<li>Re\u2011run migration after fixing data edge cases.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Simple label to mark your window:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Tag new rows\nUPDATE players SET metadata = JSON_MERGE_PATCH(COALESCE(metadata,'{}'), JSON_OBJECT('migration_tag','esx_to_qb_2025_08_16'))\nWHERE citizenid IN (SELECT citizenid FROM identifier_crosswalk);<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">Edge cases &amp; tips<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Multiple characters per human:<\/strong> If your ESX used one <code>identifier<\/code> per account (no multi\u2011char), but you plan multi\u2011char on QB, consider generating additional citizens later via in\u2011game flows, not here.<\/li>\n\n\n\n<li><strong>Name collisions:<\/strong> Two ESX users with same firstname\/lastname are fine; <strong>citizenid<\/strong> is the key.<\/li>\n\n\n\n<li><strong>Missing <\/strong><strong>&#8220;<\/strong><strong> values:<\/strong> Prefer whatever stable identifier you have (<code>steam<\/code>, <code>license2<\/code>, <code>fivem<\/code>). Populate <code>players.license<\/code> with the best available.<\/li>\n\n\n\n<li><strong>Old MySQL without JSON:<\/strong> Use plain text JSON strings and parse in app code; plan to upgrade.<\/li>\n\n\n\n<li><strong>Black money policy:<\/strong> Communicate your decision. If converting to items, run a separate, transparent item migration.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">Cutover checklist (production)<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">FAQ<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q: Can I keep using ESX <\/strong><strong>&#8220;<\/strong><strong> anywhere?<\/strong><br>A: Yes, but treat it as <strong>legacy<\/strong>. Use the crosswalk to resolve when needed, and update scripts to citizenid ASAP.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q: Does QBOX require different SQL?<\/strong><br>A: Not for identifiers\/money; QBOX tracks QB\u2019s schema closely. Validate column names before running.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q: What about inventories, jobs, gangs?<\/strong><br>A: Outside this article\u2019s scope. Handle them after identifiers\/money stabilize. Use the Pillar guide for full coverage.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">Next steps<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Implement runtime shims from <strong>Adapter Patterns<\/strong>: <a href=\"https:\/\/fivemx.com\/adapter-patterns\/\">https:\/\/fivemx.com\/adapter-patterns\/<\/a><\/li>\n\n\n\n<li>Finish your full migration using the <strong>Framework Guide (Pillar)<\/strong>: <a href=\"https:\/\/fivemx.com\/converting-fivem-scripts\/\">https:\/\/fivemx.com\/converting-fivem-scripts\/<\/a><\/li>\n\n\n\n<li>Document your local deviations (custom wallets, extra columns) inside your repo.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<h2 class=\"wp-block-heading\">Appendix \u2014 Idempotent wrappers<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Wrap critical UPDATE\/INSERTs with guards so you can re\u2011run safely.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Example guard: only update players with untouched money\nUPDATE players p\nJOIN identifier_crosswalk x ON x.citizenid = p.citizenid\nJOIN esx_balances b ON b.identifier = x.old_identifier\nSET p.money = JSON_OBJECT('cash', CAST(b.esx_cash AS UNSIGNED), 'bank', CAST(b.esx_bank AS UNSIGNED))\nWHERE JSON_EXTRACT(p.money, '$.cash') = 0 AND JSON_EXTRACT(p.money, '$.bank') = 0;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Keep the crosswalk forever. It\u2019s your Rosetta Stone for old logs and scripts.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Use\u2011case: You\u2019re moving from ESX to QBCore or QBOX (qbx_core) and need a clean, auditable migration of player identifiers and balances. This guide gives you production\u2011ready SQL, a reversible plan, and validation steps. Related reads: What changes between ESX and QBCore\/QBOX Topic ESX (common) QBCore \/ QBOX (common) Primary player key identifier (e.g., license:xxx or [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":193022,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2882],"tags":[],"class_list":["post-193023","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-framework-conversion"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/posts\/193023","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/comments?post=193023"}],"version-history":[{"count":0,"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/posts\/193023\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/media\/193022"}],"wp:attachment":[{"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/media?parent=193023"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/categories?post=193023"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fivemx.com\/pt\/wp-json\/wp\/v2\/tags?post=193023"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}