Using Node 24 and SQLite for a Tiny Appointment API in Pompano Beach

The problem I faced as a local plumber

Running a small plumbing business in Pompano Beach means I’m juggling phone calls, on‑site jobs, and a website that should show my availability. I didn’t want to pay for a hosted database or rely on a third‑party SaaS that could go down during hurricane season. What I needed was a local, zero‑maintenance data store that could be queried by my web front‑end and by a simple mobile form my technicians carry.

Why I chose Node 24 + SQLite

Node 24 ships with a built‑in SQLite driver (node:sqlite). It is lightweight, has no external server process, and stores data in a single file. For a service that handles a few dozen appointments per day, the performance is more than adequate. I also like that the driver is part of the Node runtime – no extra npm packages to audit.

Setting up the project

I keep the code in a folder called plumbing‑api on my home server. The steps below are exactly what I ran; you can copy‑paste them.

# 1. Initialise the project
mkdir plumbing-api && cd plumbing-api
npm init -y

# 2. Create a minimal package.json that tells Node to use ES modules
cat > package.json <<'EOF'
{
  "type": "module",
  "scripts": {
    "start": "node server.mjs"
  }
}
EOF

# 3. No external dependencies – SQLite driver is built‑in

Defining the SQLite schema

The schema lives in schema.sql. I keep it tiny: an appointments table with the fields I actually use.

-- schema.sql
CREATE TABLE IF NOT EXISTS appointments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_name TEXT NOT NULL,
  address TEXT NOT NULL,
  phone TEXT,
  service TEXT NOT NULL,
  scheduled_at TEXT NOT NULL, -- ISO‑8601 string
  notes TEXT
);

I run this once when the server starts; if the file already exists SQLite simply ignores the CREATE.

The Express server (server.mjs)

Below is the complete file I use. I kept the logic to the essentials: open the DB, run the schema, expose three routes.

// server.mjs
import express from 'express';
import { open } from 'node:sqlite';
import sqlite3 from 'sqlite3';
import fs from 'fs/promises';

const app = express();
app.use(express.json());

const DB_PATH = './appointments.db';
const SCHEMA_PATH = './schema.sql';

let db;

async function initDb() {
  db = await open({ filename: DB_PATH, driver: sqlite3.Database });
  const schema = await fs.readFile(SCHEMA_PATH, 'utf8');
  await db.exec(schema);
}

// GET /api/appointments – list all upcoming jobs
app.get('/api/appointments', async (req, res) => {
  const rows = await db.all('SELECT * FROM appointments ORDER BY scheduled_at');
  res.json(rows);
});

// POST /api/appointments – create a new job
app.post('/api/appointments', async (req, res) => {
  const { customer_name, address, phone, service, scheduled_at, notes } = req.body;
  if (!customer_name || !address || !service || !scheduled_at) {
    return res.status(400).json({ error: 'Missing required fields' });
  }
  const stmt = await db.run(
    `INSERT INTO appointments (customer_name, address, phone, service, scheduled_at, notes)
     VALUES (?,?,?,?,?,?)`,
    [customer_name, address, phone, service, scheduled_at, notes]
  );
  const newRow = await db.get('SELECT * FROM appointments WHERE id = ?', stmt.lastID);
  res.status(201).json(newRow);
});

// DELETE /api/appointments/:id – cancel a job
app.delete('/api/appointments/:id', async (req, res) => {
  const { id } = req.params;
  const info = await db.run('DELETE FROM appointments WHERE id = ?', id);
  if (info.changes === 0) {
    return res.status(404).json({ error: 'Not found' });
  }
  res.json({ success: true });
});

// Start the server after DB is ready
initDb().then(() => {
  const PORT = 3000;
  app.listen(PORT, () => console.log(`API listening on http://localhost:${PORT}`));
}).catch(err => {
  console.error('Failed to start:', err);
  process.exit(1);
});

How the API fits into my daily workflow

1. Web front‑end – My WordPress site pulls the JSON from /api/appointments and shows a “Next Available Slot” widget. The widget updates instantly because the request hits my home server on the same LAN, avoiding any external latency.
2. Technician mobile form – I built a tiny HTML page that runs on the iPad I keep in the van. It POSTs a new appointment when a job is finished, so the schedule stays current without needing a phone call.
3. Backup – The SQLite file (appointments.db) lives on a USB‑attached drive that I rotate weekly. Because the format is a single file, copying it is trivial.

Why this matters for other Pompano Beach trades

• No monthly DB fees – A plumber, HVAC contractor, or landscaper can run the same stack on a cheap Raspberry Pi or an old laptop.
• Resilience during storms – The service is completely local; even if the internet drops, the API is still reachable on the LAN, and the data is stored on a physically separate drive.
• Fast iteration – Adding a new column (e.g., payment_status) is just a ALTER TABLE in schema.sql; I can redeploy with a single npm start.

A few judgments I made

• I consider Node 24’s built‑in SQLite driver production‑ready for low‑traffic services like appointment scheduling. It avoids the overhead of pulling in better‑sqlite3 or sqlite3 packages.
• I chose Express because its routing is familiar and the middleware chain lets me add authentication later without rewriting the whole server.
• I deliberately kept error handling minimal – for a small business the priority is to get data in, not to build a fully‑featured API gateway. If you need stricter validation, add a library like zod.

Deploying on a home server

I run the service on a Raspberry Pi 4 that sits behind my router’s port‑forward. The only ports open are 80 (for the public website) and 3000 (for the API, limited to my LAN via firewall rules). The systemd unit looks like this:

[Unit]
Description=Plumbing appointment API
After=network.target

[Service]
WorkingDirectory=/home/kaleb/plumbing-api
ExecStart=/usr/bin/npm start
Restart=on-failure
User=kaleb
Environment=NODE_ENV=production

[Install]
WantedBy=multi-user.target

Running the service under systemd gives me automatic restarts and logs in journalctl.

Testing the endpoints locally

You can verify everything works with curl:

# List appointments (should be empty at first)
curl http://localhost:3000/api/appointments

# Create a test appointment
curl -X POST http://localhost:3000/api/appointments \
  -H 'Content-Type: application/json' \
  -d '{"customer_name":"John Doe","address":"123 Main St, Pompano Beach, FL","phone":"954-555-1234","service":"Drain cleaning","scheduled_at":"2026-11-01T09:00:00","notes":"First‑time customer"}'

# Delete the appointment we just created (replace 1 with the actual id)
curl -X DELETE http://localhost:3000/api/appointments/1

If those commands succeed, the API is ready to be consumed by any front‑end you build.

Next steps for a trade business

• Add simple token auth – a shared secret in an Authorization: Bearer … header keeps the API from being abused by strangers.
• Integrate with QuickBooks – a nightly script can read new rows and push them to your accounting software.
• Expose a read‑only endpoint – customers could check the status of their request without seeing other jobs.

By keeping the stack tiny, I avoid the overhead of managing a separate DB server, and I keep costs near zero. That’s the sweet spot for most local trades in Pompano Beach.

I build sites and automations for small businesses at dreamfuturestech.com.