Server-authoritative banking with an audit log

resources/qu_audit_bank

resources/qu_audit_bank/
fxmanifest.lua
server.lua

fx_version 'cerulean'
game 'gta5'

server_script '@oxmysql/lib/MySQL.lua'
server_script 'server.lua'

dependencies {
'oxmysql'
}

CREATE TABLE IF NOT EXISTS qu_audit_balances (
citizenid VARCHAR(64) NOT NULL,
balance INT NOT NULL DEFAULT 0,
PRIMARY KEY (citizenid)
);

CREATE TABLE IF NOT EXISTS qu_audit_log (
id INT AUTO_INCREMENT PRIMARY KEY,
citizenid VARCHAR(64) NOT NULL,
source INT NOT NULL,
action VARCHAR(32) NOT NULL,
amount INT NOT NULL,
balance_before INT NOT NULL,
balance_after INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Resolve the active character id. A server source is only a temporary slot;
-- never use it as the durable account key.
local function citizenIdFor(source)
if GetResourceState('qbx_core') == 'started' then
    local player = exports.qbx_core:GetPlayer(source)
    return player and player.PlayerData.citizenid
elseif GetResourceState('qb-core') == 'started' then
    local core = exports['qb-core']:GetCoreObject()
    local player = core.Functions.GetPlayer(source)
    return player and player.PlayerData.citizenid
elseif GetResourceState('es_extended') == 'started' then
    local esx = exports.es_extended:getSharedObject()
    local player = esx.GetPlayerFromId(source)
    return player and player.identifier
end
end

-- Read the balance, creating the account at 0 on first touch.
local function getBalance(citizenid)
local row = MySQL.single.await(
    'SELECT balance FROM qu_audit_balances WHERE citizenid = ?',
    { citizenid }
)
if not row then
    MySQL.insert.await(
        'INSERT INTO qu_audit_balances (citizenid, balance) VALUES (?, ?)',
        { citizenid, 0 }
    )
    return 0
end
return row.balance
end

-- The single trusted path. Every money move on the server goes through here.
-- This is a same-runtime handler fired by AddEventHandler below, so the player
-- is passed in explicitly as `src` (the magic `source` is only populated for a
-- real client -> server net event, which this is not).
AddEventHandler('qu_audit_bank:request', function(src, action, amount)
local citizenid = citizenIdFor(src)
if not citizenid then
    print('[qu_audit_bank] REJECTED: no loaded framework character for source ' .. src)
    return
end

-- Validate the intent. The client may only ask to deposit or withdraw a
-- positive whole number. It never sends a balance.
amount = tonumber(amount)
if action ~= 'deposit' and action ~= 'withdraw' then return end
if not amount or amount <= 0 or amount ~= math.floor(amount) then
    print('[qu_audit_bank] REJECTED bad amount from source ' .. src)
    return
end

-- This demo has no cash account to debit, so only an administrator may
-- create ledger value with /deposit. A real ATM would remove framework
-- cash here and continue only if that server-side removal succeeded.
if action == 'deposit' and not IsPlayerAceAllowed(src, 'qu_audit_bank.deposit') then
    print('[qu_audit_bank] REJECTED unauthorized deposit from source ' .. src)
    return
end

local balanceBefore = getBalance(citizenid)
local balanceAfter

if action == 'deposit' then
    balanceAfter = balanceBefore + amount
else -- withdraw
    if amount > balanceBefore then
        print('[qu_audit_bank] REJECTED withdraw of ' .. amount .. ', balance is only ' .. balanceBefore)
        return
    end
    balanceAfter = balanceBefore - amount
end

-- One safe UPDATE applies the change the server computed.
MySQL.update.await(
    'UPDATE qu_audit_balances SET balance = ? WHERE citizenid = ?',
    { balanceAfter, citizenid }
)

-- One INSERT records the transaction. This runs for every action.
MySQL.insert.await(
    'INSERT INTO qu_audit_log (citizenid, source, action, amount, balance_before, balance_after) VALUES (?, ?, ?, ?, ?, ?)',
    { citizenid, src, action, amount, balanceBefore, balanceAfter }
)

print('[qu_audit_bank] ' .. action .. ' ' .. amount ..
    ' for ' .. citizenid .. ': ' .. balanceBefore .. ' -> ' .. balanceAfter)
end)

-- Thin commands so you can fire the request as a real player from chat.
-- These commands are registered in a server_script, so their handlers run on
-- the server, where RegisterCommand hands you the player's server id as the
-- first argument. We pass that trusted source into the same-runtime event with
-- TriggerEvent (the correct same-runtime call; TriggerServerEvent is client-only).
RegisterCommand('deposit', function(source, args)
TriggerEvent('qu_audit_bank:request', source, 'deposit', args[1])
end, false)

RegisterCommand('withdraw', function(source, args)
TriggerEvent('qu_audit_bank:request', source, 'withdraw', args[1])
end, false)

-- Print the last few audit rows so you can read the trail.
RegisterCommand('auditlog', function()
local rows = MySQL.query.await(
    'SELECT id, citizenid, source, action, amount, balance_before, balance_after FROM qu_audit_log ORDER BY id DESC LIMIT 5',
    {}
)
for i = #rows, 1, -1 do
    local r = rows[i]
    print('[qu_audit_bank] #' .. r.id .. ' src=' .. r.source ..
        ' ' .. r.action .. ' ' .. r.amount ..
        ' (' .. r.balance_before .. ' -> ' .. r.balance_after .. ')')
end
end, true)

ensure qu_audit_bank
add_ace group.admin qu_audit_bank.deposit allow

restart qu_audit_bank

/deposit 100
/withdraw 250
/withdraw 40

auditlog