☕ Kopitiam Digital Waiter

Core Source Code & Sales Tracking Implementation

React 19 TypeScript Express.js SQLite Gemini AI

🔍 About This Code Showcase

This curated code snippet demonstrates how the Kopitiam Digital Waiter records sales, queries real-time statistics, and surfaces AI-powered business tips for Malaysian Kopitiam owners.

Deployment scripts and proprietary configuration details are omitted for clarity. This showcase highlights the core backend API logic and the React frontend sales recording flow.

🗄️ Core Algorithm: Sales API & Statistics Engine

The backend is a single Express.js server that manages SQLite sales data and exposes REST endpoints for the React frontend. Here is the core of the statistics engine:

📄 server.ts — Today's Stats Endpoint

app.get("/api/stats/today", (req, res) => {
  // Aggregate today's revenue, item count, and transaction count
  const stats = db.prepare(`
    SELECT
      SUM(total_cents) as totalRevenue,
      SUM(quantity) as totalItems,
      COUNT(DISTINCT id) as totalTransactions
    FROM sales
    WHERE date(datetime) = date('now')
  `).get();

  // Detect milestone: any item sold 10+ times today
  const topItem = db.prepare(`
    SELECT item_name, SUM(quantity) as qty
    FROM sales
    WHERE date(datetime) = date('now')
    GROUP BY item_name
    ORDER BY qty DESC
    LIMIT 1
  `).get();

  const alerts = [];
  if (topItem && topItem.qty >= 10) {
    const item = MENU_ITEMS.find(i => i.id === topItem.item_name);
    alerts.push(`You just hit ${topItem.qty} ${item?.name} orders!`);
  }

  // AI-aware quick tip: reference today's top seller or all-time best seller
  const topItemAllTime = db.prepare(`
    SELECT item_name, SUM(quantity) as qty
    FROM sales GROUP BY item_name ORDER BY qty DESC LIMIT 1
  `).get();

  let quickTip = "Boss, start recording sales to see tips here!";
  if (topItem && topItem.qty > 0) {
    const item = MENU_ITEMS.find(i => i.id === topItem.item_name);
    quickTip = `Boss, ${item?.name} is moving fast today! Check your inventory.`;
  } else if (topItemAllTime) {
    const item = MENU_ITEMS.find(i => i.id === topItemAllTime.item_name);
    quickTip = `Boss, ${item?.name} is usually your best seller. Ready for the rush?`;
  }

  // Top 3 items today for the live mini-analytics in logbook
  const topItemsToday = db.prepare(`
    SELECT item_name as id, SUM(quantity) as value
    FROM sales WHERE date(datetime) = date('now')
    GROUP BY item_name ORDER BY value DESC LIMIT 3
  `).all().map(row => ({
    ...row,
    name: MENU_ITEMS.find(i => i.id === row.id)?.name || row.id
  }));

  res.json({
    revenue: stats.totalRevenue || 0,
    items: stats.totalItems || 0,
    transactions: stats.totalTransactions || 0,
    alerts,
    quickTip,
    topItems: topItemsToday
  });
});
                

📄 server.ts — Analytics Endpoint (Busy Hours + Daily Trends)

app.get("/api/stats/analytics", (req, res) => {
  // Group all sales by hour-of-day to find peak selling periods
  const busyHours = db.prepare(`
    SELECT strftime('%H', datetime) as hour, SUM(quantity) as count
    FROM sales
    GROUP BY hour
    ORDER BY hour ASC
  `).all();

  // Last 7 days of daily revenue for trend chart
  const dailyTrends = db.prepare(`
    SELECT date(datetime) as date, SUM(total_cents) as revenue
    FROM sales
    GROUP BY date
    ORDER BY date DESC
    LIMIT 7
  `).all().reverse(); // Reverse so oldest is leftmost on chart

  res.json({ busyHours, dailyTrends });
});
                

⚛️ Frontend: Sales Logbook Component

The Logbook tab is where Kopitiam owners record sales. It features a menu grid with quantity selectors and a one-tap record button per item:

📄 src/App.tsx — Logbook Component

function Logbook({ menu, onRecord, stats }) {
  const [quantities, setQuantities] = useState<Record<string, number>>({});
  const [recording, setRecording] = useState<string | null>(null);

  const updateQty = (id: string, delta: number) => {
    setQuantities(prev => ({
      ...prev,
      [id]: Math.max(0, (prev[id] || 0) + delta)
    }));
  };

  const handleRecord = async (id: string) => {
    const qty = quantities[id] || 1;
    setRecording(id);
    await onRecord(id, qty); // POST to /api/sales
    setQuantities(prev => ({ ...prev, [id]: 0 })); // Reset qty after recording
    setTimeout(() => setRecording(null), 1000);
  };

  return (
    <div className="space-y-6">
      {/* Today's Summary Bar at top of logbook */}
      {stats && (
        <div className="grid grid-cols-1 gap-4 lg:grid-cols-3">
          <div className="flex items-center justify-between rounded-2xl bg-emerald-600 p-4 text-white lg:col-span-2">
            <div>
              <p className="text-xs font-medium opacity-80">Today's Sales</p>
              <p className="text-xl font-black">RM {(stats.revenue / 100).toFixed(2)}</p>
            </div>
          </div>
          {/* Live mini top-items panel */}
          <TopItemsPanel items={stats.topItems} />
        </div>
      )}

      {/* Menu grid — one card per item */}
      <div className="grid grid-cols-2 gap-4 sm:grid-cols-3 lg:grid-cols-4">
        {menu.map(item => (
          <MenuCard
            key={item.id}
            item={item}
            quantity={quantities[item.id] || 0}
            onDecrease={() => updateQty(item.id, -1)}
            onIncrease={() => updateQty(item.id, 1)}
            onRecord={() => handleRecord(item.id)}
            isRecording={recording === item.id}
          />
        ))}
      </div>
    </div>
  );
}
                

🗃️ Database Schema & CSV Seeding

The app uses SQLite for lightweight, zero-config data storage. On first launch it seeds from historical Kopitiam sales data:

📄 server.ts — Database Initialization & CSV Seeding

// Create sales table if it doesn't exist
db.exec(`
  CREATE TABLE IF NOT EXISTS sales (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    datetime DATETIME DEFAULT CURRENT_TIMESTAMP,
    item_name TEXT,
    quantity INTEGER,
    price_cents INTEGER,
    total_cents INTEGER
  )
`);

// Seed from CSV only if the database is empty
const count = db.prepare("SELECT COUNT(*) as count FROM sales").get();
if (count.count === 0) {
  const csvContent = fs.readFileSync("Kopitiam_dataset.csv", "utf-8");
  const records = parse(csvContent, { columns: true, skip_empty_lines: true });

  const insert = db.prepare(`
    INSERT INTO sales (datetime, item_name, quantity, price_cents, total_cents)
    VALUES (?, ?, ?, ?, ?)
  `);

  // Use a transaction for fast bulk insert
  db.transaction(() => {
    for (const record of records) {
      // Parse datetime format: "11/7/2019 15:35" → ISO string
      const [datePart, timePart] = record.datetime.split(" ");
      const [day, month, year] = datePart.split("/");
      const isoDate = `${year}-${month.padStart(2,'0')}-${day.padStart(2,'0')}T${timePart}:00`;

      for (const item of MENU_ITEMS) {
        const qty = parseInt(record[item.id]);
        if (qty > 0) {
          insert.run(isoDate, item.id, qty, item.price, qty * item.price);
        }
      }
    }
  })();
}
                

⚙️ Technical Implementation Notes

Key Design Decisions

Single-process architecture: Express serves both the API and the compiled React app, simplifying Cloud Run deployment to one container
Prices stored in cents: All monetary values use integers (cents) to avoid floating-point rounding errors in financial calculations
CSV seeding on startup: Owners get immediate analytics from historical data without any manual import step
Transactional bulk insert: SQLite transaction wraps the entire CSV seed for 10–100x faster initial load
Optimistic UI updates: Frontend refreshes all stats after each sale recording to keep the dashboard in sync
Framer Motion animations: Smooth tab transitions and button feedback give the app a polished, native-app feel

Why SQLite?

Zero configuration: No database server to provision — ideal for a single-owner small business tool
Fast reads: All analytics queries run in milliseconds on typical Kopitiam sales volumes
Portable: The entire database is a single file, making backups trivial

🚀 Try Live Demo 📖 View Full Project Details