A bot economy with SQLite

An economy turns a server into a game: earn coins, claim a daily bonus, spend in a shop, pay your friends. The trap is that money bugs are the worst bugs — a crash mid-payment that duplicates or loses coins destroys trust. This recipe builds the scaffold on better-sqlite3 with transactions, so every coin movement is all-or-nothing.

We use better-sqlite3 — a real SQL database in one file, no separate service. It ships prebuilt binaries and installs cleanly on the Node.js application. Add it from the Packages page (search better-sqlite3, install, restart). Comfortable with the leveling recipe? This is the same toolkit, with transactions added.

The data layer

Put the money logic in economy-db.js. Every function that moves coins is a transaction — better-sqlite3 runs the whole block or none of it, so a crash can never leave coins half-moved:

const Database = require('better-sqlite3');
const db = new Database('economy.db');
db.pragma('journal_mode = WAL');

db.exec(`
  CREATE TABLE IF NOT EXISTS wallets (
    user_id    TEXT    PRIMARY KEY,
    balance    INTEGER NOT NULL DEFAULT 0,
    last_daily INTEGER NOT NULL DEFAULT 0
  )
`);

const DAILY_AMOUNT = 250;
const DAY_MS = 24 * 60 * 60 * 1000;

const ensure    = db.prepare('INSERT OR IGNORE INTO wallets (user_id) VALUES (?)');
const getWallet = db.prepare('SELECT balance, last_daily FROM wallets WHERE user_id = ?');
const changeBal = db.prepare('UPDATE wallets SET balance = balance + ? WHERE user_id = ?');
const setDaily  = db.prepare('UPDATE wallets SET last_daily = ? WHERE user_id = ?');

function getBalance(userId) {
  ensure.run(userId);
  return getWallet.get(userId).balance;
}

function addCoins(userId, amount) {
  ensure.run(userId);
  changeBal.run(amount, userId);
  return getWallet.get(userId).balance;
}

// Claim once per 24h. Returns {claimed:true, amount, balance} or {claimed:false, retryAfterMs}.
function claimDaily(userId) {
  ensure.run(userId);
  const { last_daily } = getWallet.get(userId);
  const now = Date.now();
  if (now - last_daily < DAY_MS) return { claimed: false, retryAfterMs: DAY_MS - (now - last_daily) };
  const claim = db.transaction(() => {
    changeBal.run(DAILY_AMOUNT, userId);
    setDaily.run(now, userId);
  });
  claim();
  return { claimed: true, amount: DAILY_AMOUNT, balance: getWallet.get(userId).balance };
}

// Atomic transfer. Returns true on success, false if the sender is short.
const transfer = db.transaction((fromId, toId, amount) => {
  ensure.run(fromId); ensure.run(toId);
  if (getWallet.get(fromId).balance < amount) return false;
  changeBal.run(-amount, fromId);
  changeBal.run(amount, toId);
  return true;
});

const SHOP = [
  { id: 'coffee', name: '☕ Coffee', price: 50 },
  { id: 'crown',  name: '👑 Crown',  price: 1000 },
];

// Atomic purchase. Returns {ok:true, balance, item} or {ok:false, reason}.
function buyItem(userId, itemId) {
  const item = SHOP.find(i => i.id === itemId);
  if (!item) return { ok: false, reason: 'no such item' };
  const purchase = db.transaction(() => {
    ensure.run(userId);
    if (getWallet.get(userId).balance < item.price) return { ok: false, reason: 'not enough coins' };
    changeBal.run(-item.price, userId);
    return { ok: true, balance: getWallet.get(userId).balance, item };
  });
  return purchase();
}

module.exports = { getBalance, addCoins, claimDaily, transfer, buyItem, SHOP };

What earns its place here:

• db.transaction(fn) wraps a group of statements into one atomic unit. In transfer, deducting from one wallet and crediting another either both happen or neither does — the failure that would otherwise vaporise coins can't occur. A transaction that returns a value passes it straight back out, which is how transfer and buyItem report success.
• INSERT OR IGNORE (ensure) creates a wallet the first time someone is seen and does nothing thereafter, so every other function can assume the row exists.
• claimDaily is a real cooldown — it compares now against the stored last_daily, and only on success does it credit coins and stamp the new time, together, in a transaction.
• balance is an INTEGER of whole coins. Never store money as a floating-point number — 0.1 + 0.2 isn't 0.3 in floats, and that rounding drift is exactly the kind of bug an economy can't afford. Count whole coins.
• WAL mode (journal_mode = WAL) lets reads and writes overlap smoothly — a good default for a busy bot.

The commands

The bot file wires each command to a data function. /buy offers the shop items as fixed choices, and /pay takes a user and an amount:

require('dotenv').config();
const { Client, Events, GatewayIntentBits, SlashCommandBuilder } = require('discord.js');
const { getBalance, claimDaily, transfer, buyItem, SHOP } = require('./economy-db.js');

const client = new Client({ intents: [GatewayIntentBits.Guilds] });

client.once(Events.ClientReady, async (c) => {
  await c.application.commands.set([
    new SlashCommandBuilder().setName('balance').setDescription('Check your coins'),
    new SlashCommandBuilder().setName('daily').setDescription('Claim your daily coins'),
    new SlashCommandBuilder().setName('shop').setDescription('See what you can buy'),
    new SlashCommandBuilder().setName('buy').setDescription('Buy an item')
      .addStringOption(o => o.setName('item').setDescription('Item id').setRequired(true)
        .addChoices(...SHOP.map(i => ({ name: i.name, value: i.id })))),
    new SlashCommandBuilder().setName('pay').setDescription('Send coins to someone')
      .addUserOption(o => o.setName('to').setDescription('Who').setRequired(true))
      .addIntegerOption(o => o.setName('amount').setDescription('How many').setRequired(true).setMinValue(1)),
  ]);
  console.log(`Listening as ${c.user.tag}`);
});

client.on(Events.InteractionCreate, async (interaction) => {
  if (!interaction.isChatInputCommand()) return;
  const uid = interaction.user.id;

  switch (interaction.commandName) {
    case 'balance':
      return interaction.reply(`💰 You have **${getBalance(uid)}** coins.`);
    case 'daily': {
      const r = claimDaily(uid);
      return interaction.reply(r.claimed
        ? `🎁 You claimed **${r.amount}** coins. Balance: ${r.balance}.`
        : `⏳ Already claimed. Come back in ${Math.ceil(r.retryAfterMs / 3600000)}h.`);
    }
    case 'shop':
      return interaction.reply(SHOP.map(i => `${i.name} — **${i.price}** coins (\`${i.id}\`)`).join('\n'));
    case 'buy': {
      const r = buyItem(uid, interaction.options.getString('item'));
      return interaction.reply(r.ok ? `✅ Bought ${r.item.name}! Balance: ${r.balance}.` : `❌ ${r.reason}.`);
    }
    case 'pay': {
      const to = interaction.options.getUser('to');
      const amount = interaction.options.getInteger('amount');
      const ok = transfer(uid, to.id, amount);
      return interaction.reply(ok ? `✅ Sent ${amount} coins to ${to}.` : "❌ You don't have enough coins.");
    }
  }
});

client.login(process.env.DISCORD_TOKEN);

A few finishing touches:

• setMinValue(1) on /pay's amount stops anyone "paying" a negative number to steal coins — Discord rejects it before your code runs.
• addChoices turns the shop into a dropdown on /buy, so users pick a real item id instead of guessing. When your shop grows past a fixed handful, swap those choices for autocomplete.
• The switch on commandName keeps five commands readable in one handler.

🎯 Good to know: This shop charges for items but doesn't track what people own — buying a Crown deducts coins and says "bought". To grant a role, an item in an inventory, or a perk, add an inventory table and record the purchase inside the same buyItem transaction, so the coins and the item always move together.

Where the data lives

⚠️ Heads up: economy.db lives in /home/container on the server. It survives restarts, but a reinstall wipes it — and switching the server's application (or a template that switches it) is a reinstall. For play money that's usually fine; back it up before risky changes and keep it out of Git. If coins must never be lost, use a managed database, which outlives reinstalls.

Verify it works

Start the bot. Run /daily — you get 250 coins; run it again and it's blocked with a countdown. /balance shows your total. /buy item:👑 Crown deducts 1000 (or refuses if you're short). /pay @friend 100 moves coins; try to pay more than you have and it's refused with your balance untouched — that refusal-without-loss is the transaction doing its job. Restart and check /balance: your coins are still there.

Troubleshooting

• Cannot find module 'better-sqlite3' — install it from the Packages page and restart; it ships prebuilt binaries, so there's no compiler step.
• Coins go missing or double after an error — a coin-moving function isn't wrapped in db.transaction(...). Every debit-and-credit pair must be one transaction.
• /daily can be claimed repeatedly — the cooldown compares last_daily to now; make sure the successful branch stamps setDaily(now) inside the transaction.
• Someone paid a negative amount — the amount option is missing setMinValue(1). Add it, and reject amount <= 0 in code as a belt-and-braces check.
• Balances reset after a reinstall — expected: economy.db is local. Back it up, or move to a managed database.

Next steps

• A leveling system with SQLite
• Per-user cooldowns
• Evolving your SQLite schema safely