Lesson 4 / 7 · 10 min

The mini CRM: contacts, orders, and labels

Two more tables and the bot becomes a CRM the business can query. Checkpoint: real follow-up lists from SQL.

Right now conversations evaporate when they end. Two more tables, contacts and orders, turn the same bot into a system that answers business questions: who are my customers, who asked but never paid, who should I follow up today.

Step 1: two tables (add to db.js)

db.exec(
  "CREATE TABLE IF NOT EXISTS contacts(" +
  "  phone TEXT PRIMARY KEY," +
  "  name TEXT," +
  "  labels TEXT NOT NULL DEFAULT ''," +
  "  first_seen INTEGER," +
  "  last_seen INTEGER);" +
  "CREATE TABLE IF NOT EXISTS orders(" +
  "  id TEXT PRIMARY KEY," +
  "  phone TEXT NOT NULL," +
  "  item TEXT, qty INTEGER, total INTEGER," +
  "  address TEXT," +
  "  status TEXT NOT NULL DEFAULT 'draft'," +
  "  created_at INTEGER, paid_at INTEGER);"
);

export function touchContact(phone) {
  db.prepare(
    "INSERT INTO contacts(phone, first_seen, last_seen) " +
    "VALUES (?, unixepoch(), unixepoch()) " +
    "ON CONFLICT(phone) DO UPDATE SET last_seen = unixepoch()",
  ).run(phone);
}

export function addLabel(phone, label) {
  const row = db.prepare("SELECT labels FROM contacts WHERE phone = ?").get(phone);
  const labels = new Set((row?.labels || "").split(",").filter(Boolean));
  labels.add(label);
  db.prepare("UPDATE contacts SET labels = ? WHERE phone = ?")
    .run([...labels].join(","), phone);
}

touchContact runs on every inbound message; addLabel is called by the flow at meaningful moments.

Step 2: create the order at CONFIRM

// flow.js, inside case "CONFIRM" when the answer is "ya":
import { db, addLabel } from "./db.js";

const orderId = "WA-" + Date.now();
db.prepare(
  "INSERT INTO orders(id, phone, item, qty, total, address, " +
  "status, created_at) VALUES (?, ?, ?, ?, ?, ?, 'confirmed', unixepoch())",
).run(orderId, session.phone, session.data.item, session.data.qty,
      session.data.total, session.data.address);
session.data.orderId = orderId;
addLabel(session.phone, "quoted");

The order exists from the moment the customer says YA; payment (next lesson) only flips its status.

Step 3: the questions the business can now ask

-- who to follow up: confirmed > 1 day, still unpaid
SELECT o.phone, o.item, o.qty, o.total
FROM orders o
WHERE o.status = 'confirmed'
  AND o.created_at < unixepoch() - 86400;

-- this week's revenue
SELECT SUM(total) FROM orders
WHERE status = 'paid' AND paid_at > unixepoch() - 7*86400;

-- contacts gone quiet for 30+ days (win-back list)
SELECT phone FROM contacts
WHERE last_seen < unixepoch() - 30*86400;

Run these with: sqlite3 data/bot.db. Three queries replace a paid dashboard at this stage.

Checkpoint: place two test orders, then run the follow-up query and see them listed. The moment a list of phone numbers comes out of a query, you have a CRM, small, but real, and every label you add sharpens it.

What you take away

contacts + orders turn conversations into queryable business data
Orders are created at CONFIRM with status confirmed; payment flips them to paid
Labels applied by the flow build your pipeline automatically
Three SQL queries give follow-up, revenue, and win-back lists

Curated sources

Hand-picked references for going deeper, reviewed by our team.

Previous lesson3. Sessions and the order flow state machine Next lesson5. Payments in chat with the Midtrans sandbox