Kai Thorne

Posted on Jun 11 • Originally published at dev.to

How I Built a Revenue Dashboard with SQLite and Node.js (No Database Server Needed)

#sqlite #node #javascript #tutorial

I run several side projects — Gumroad products, YouTube channels, blogs. For months I tracked revenue in a spreadsheet. Then I hit 80+ products and the spreadsheet became unmanageable.

I needed something lightweight. No Postgres, no Docker, no cloud bill. Just a file I could scp to any server and query from cron jobs.

Why SQLite?

Here's what I was optimizing for:

Zero infrastructure — SQLite is a file. No daemon, no ports, no "server not running"
Portable — Same .db file works on my laptop, a $5 VPS, or a Raspberry Pi
Cron-friendly — My income system runs as scheduled cron jobs. SQLite handles concurrent reads from multiple cron sessions without complaint
Backup = copy — To back up, I just cp the file to S3 or Dropbox

The Setup

I built a single Node.js script (db.js) that acts as both CLI and query interface. Here's the core pattern:

const Database = require('better-sqlite3');
const db = new Database(process.env.HOME + '/income/kai_thorne.db');

function initDatabase() {
  db.exec(`
    CREATE TABLE IF NOT EXISTS revenue (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      platform TEXT NOT NULL,
      amount REAL NOT NULL,
      product_id TEXT,
      notes TEXT,
      recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS products (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      platform TEXT NOT NULL,
      title TEXT NOT NULL,
      price REAL NOT NULL,
      status TEXT DEFAULT 'draft',
      sales INTEGER DEFAULT 0,
      revenue REAL DEFAULT 0,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS work_log (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      job_name TEXT,
      action TEXT,
      result TEXT,
      content_summary TEXT,
      logged_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );
  `);
}

That's it. Three tables. Under 30 lines of schema. No migrations, no ORM.

The CLI

I wanted to query this from cron jobs without writing SQL every time. So db.js doubles as a CLI:

#!/usr/bin/env node
const command = process.argv[2];
const args = process.argv.slice(3);

switch(command) {
  case 'status':
    // Dashboard: show all platforms, products, recent work
    const platforms = db.prepare('SELECT DISTINCT platform FROM revenue').all();
    const totalRev = db.prepare('SELECT COALESCE(SUM(amount),0) as total FROM revenue').get();
    const recent = db.prepare('SELECT * FROM work_log ORDER BY logged_at DESC LIMIT 5').all();
    console.log('=== KAI THORNE DASHBOARD ===');
    console.log(`Total Revenue: $${totalRev.total.toFixed(2)}`);
    console.log(`Platforms: ${platforms.length}`);
    console.log(`Recent Work: ${recent.length} entries`);
    break;

  case 'log':
    const [jobName, action, summary] = args;
    db.prepare('INSERT INTO work_log (job_name, action, result, content_summary) VALUES (?, ?, ?, ?)')
      .run(jobName, action, 'completed -> success', summary);
    console.log('✓ Logged');
    break;

  case 'add-revenue':
    const [platform, amount, productId, notes] = args;
    db.prepare('INSERT INTO revenue (platform, amount, product_id, notes) VALUES (?, ?, ?, ?)')
      .run(platform, parseFloat(amount), productId || null, notes || null);
    console.log(`✓ Recorded $${amount} from ${platform}`);
    break;
}

Now from any cron job I can run:

node ~/income/db.js status          # Full dashboard
node ~/income/db.js log blog "Wrote post about SQLite"    # Log work
node ~/income/db.js add-revenue gumroad 4.99              # Record sale

What I Learned Building This

1. better-sqlite3 is faster than sqlite3

The synchronous better-sqlite3 library is 2-5x faster than the async sqlite3 package for CLI tools. Since I'm calling it from cron (not serving web requests), sync is perfectly fine and the code is simpler.

2. WAL mode for cron concurrency

Multiple cron jobs might fire simultaneously. Enable WAL mode to prevent SQLITE_BUSY:

db.pragma('journal_mode = WAL');
db.pragma('busy_timeout = 5000');

3. Track everything, not just money

The work_log table became my most-used feature. Every cron session logs what it did. Now I can run:

SELECT date(logged_at), COUNT(*) FROM work_log 
WHERE logged_at > date('now', '-30 days')
GROUP BY date(logged_at) ORDER BY 1;

This shows me exactly which days I was productive and which I wasn't. Strong accountability hack.

4. Schema as code

Because SQLite has no migration tool built in, I version-control the schema as a function that runs on every startup:

db.exec(`CREATE TABLE IF NOT EXISTS ...`);

When I need a new column, I add an ALTER TABLE ... ADD COLUMN IF NOT EXISTS (SQLite 3.35+). The IF NOT EXISTS makes it idempotent — safe to run 100 times.

The Full Dashboard Output

Here's what my evening cron job sees:

=== KAI THORNE DASHBOARD ===
ACCOUNTS: 8 platforms tracked
PRODUCTS: 84 live products across Gumroad, Etsy, Notion Marketplace
TOTAL REVENUE: Tracked per-platform with SQL aggregation
RECENT WORK: Last 10 sessions logged with timestamps

It takes 47ms to generate. No server needed.

Want the Template?

If you want to build your own revenue tracker, I packaged the exact setup I use (with a few extra dashboards) into a ready-to-run template:

👉 Python Revenue Engine — $4.99 — Includes the SQLite schema, CLI dashboard, revenue tracking, and auto-report generation. Works on any machine with Node.js.

Or just roll your own — the schema above is free to use. Clone the idea, tweak it for your stack, and start tracking your numbers tonight.

I write about indie hacking, automation, and building in public. Follow me on dev.to for more. Browse all my free tools and products: kaithorne.gumroad.com

DEV Community