DEV Community: Eddy Odhiambo

Relational SQL 123s: DDL, DML, Filtering, and Turning Rows into Meaning

Eddy Odhiambo — Sun, 12 Apr 2026 11:22:06 +0000

A practical mental model for how databases are structured, how you put data in and change it, and how WHERE, aggregates, and CASE WHEN let you ask precise questions without getting lost in syntax.

This article is a concept-first tour of ideas that show up in almost every SQL course and job: DDL versus DML, the roles of CREATE, INSERT, UPDATE, and DELETE, how WHERE and common operators narrow results, and how CASE WHEN adds computed labels on top of raw values.

DDL vs DML: structure versus contents

DDL (Data Definition Language) describes structure. It answers what objects exist, what they are named, which columns they have, their data types, and which integrity rules apply (primary keys, uniqueness, nullability, and so on). Typical DDL includes CREATE SCHEMA or CREATE DATABASE, CREATE TABLE, ALTER TABLE, and DROP. When you run DDL, you are defining the shape of storage and their dividers alongside the data that will live inside.

DML (Data Manipulation Language) describes rows. It answers what is stored right now. INSERT adds rows, UPDATE modifies existing rows, DELETE removes rows, and SELECT retrieves rows. SELECT is sometimes treated as its own category because it does not change persisted data by default, but it is universally grouped with “working with data” alongside inserts and updates.

Why distinguish them? In production, DDL often goes through migrations, reviews, and backups; getting a column wrong is expensive to unwind. DML is what applications and analysts run constantly. Mixing the two mentally helps you choose the right tool, “Am I changing the contract of the table, or only the rows inside it?”

CREATE, ALTER, INSERT, UPDATE, and DELETE in practice

CREATE TABLE (and optionally CREATE SCHEMA) establishes tables and namespaces. You declare column names, types, and constraints such as PRIMARY KEY, NOT NULL, and UNIQUE. In systems like PostgreSQL, setting a search_path keeps multi-schema projects readable.

ALTER TABLE reflects evolving requirements - add a column you forgot at launch, rename a column when the business vocabulary changes, change a type when the data grows wider, or drop a column that is no longer collected. The important habit is to remember that the table your INSERT targets is the table as it exists after all prior ALTER column names in INSERT must match the new definition.

INSERT loads one or many rows. Values must respect constraints; duplicate keys or nulls in NOT NULL columns will fail fast, which is preferable to silent corruption.

UPDATE changes existing rows. A universal rule is always constrain with WHERE unless you truly intend to touch every row. A missing WHERE on UPDATE or DELETE is one of the fastest ways to turn a good day into an incident review.

DELETE removes rows, again almost always with a WHERE predicate so only the intended rows disappear. After DML, aggregates and reports reflect the new truth, counts and sums change because the underlying rows changed.

Filtering with WHERE clause: precision over intuition

The WHERE clause limits which rows are considered for SELECT, and critically for UPDATE and DELETE. Common building blocks include:

Comparison: =, <>, <, >, <=, >= (e.g. status = 'active', amount >= 100).
Boolean logic: AND, OR, NOT, with parentheses when intent is ambiguous.
BETWEEN: inclusive ranges on numbers or dates (e.g. order_date BETWEEN '2024-03-15' AND '2024-03-18').
IN / NOT IN: membership in a fixed list (e.g. region IN ('East', 'West', 'Central')).
LIKE: simple pattern matching; % matches any sequence, _ matches a single character (e.g. name LIKE 'A%' for names starting with A, description LIKE '%beta%' for a substring).

Used together, these turn vague questions (“show me the interesting orders”) into testable predicates the engine can optimize. Text literals belong in single quotes in standard SQL; numbers do not.

CASE WHEN: rules as readable columns

CASE WHEN adds derived values in the result of a query. You do not have to alter the table: you express business rules in SQL and expose them as named columns with AS. Conditions are usually evaluated in order; the first match wins, and ELSE catches the remainder.

Typical uses include banding numeric scores (grade tiers, discount brackets), normalizing messy codes into display labels, and bucketing categories (e.g. “high / medium / low” from thresholds).

CASE
    WHEN score >= 90 THEN 'Excellent'
    WHEN score >= 70 THEN 'Good'
    WHEN score >= 50 THEN 'Satisfactory'
    ELSE 'Needs attention'
END AS performance_band

The same pattern appears in reporting tools and spreadsheets; SQL makes it explicit and reusable in pipelines and APIs.

COUNT(*) and other aggregates (SUM, AVG, MIN, MAX) combine naturally with WHERE: you count or sum only the rows that pass the filter, which is how operational questions (“how many open tickets in this queue?”) map directly to queries.

The satisfying part with SQL is continuity: design the schema, load and correct data, then ask layered questions with filters, counts, and conditional labels. The database becomes a single place where **rules and questions stay aligned.

Transactional SMS Setup with Africa's Talking SDK For Single and Bulk messages

Eddy Odhiambo — Sun, 12 Apr 2026 09:29:23 +0000

A hands-on guide to working with Africa's Talking SDK to send transactional messages and a controlled bulk batch flow.

By the end of this guide, you’ll understand the full SMS delivery flow from your application to a real mobile phone.

You will:

Set up a minimal but production-relevant TypeScript backend
Connect it to Africa’s Talking SMS API using their official SDK
Build a POST /send-one endpoint (for transactional messages like OTPs)
Build a POST /send-bulk endpoint (for campaigns or notifications)
Learn how to safely move from sandbox testing to real production sending

1) Prerequisites to get started

a) Africa’s Talking Account.
You need an account to access Africas Talking API.

Go to: https://account.africastalking.com/
Sign up using your email and verify your account
Once logged in, you’ll see a dashboard with products like SMS, Voice, Airtime, etc.

b) Sandbox API Key (for testing)
Africa’s Talking provides a sandbox environment so you can test without sending real SMS or being charged. To get it:

Go to Settings → API Key
Generate a new key
Copy it immediately (you won’t see it again, its only shown once) This key is what your app uses to authenticate API requests.

c) Username (Sandbox vs Production)
The username tells Africa’s Talking which environment to route your request to. Using the wrong combination (sandbox key + production username) will cause authentication errors.
In sandbox mode, you will use

AT_USERNAME=sandbox

In production, you use your actual account username (e.g. appname)

d) Sender ID / Shortcode
This is the name or number that appears as the sender of the SMS for example alphanumerics like SMS_APP or shortcodes like 40123

In sandbox testing, you will use:

AT_SENDER_ID=Sandbox

In production, your sender ID must be officially applied for through the Product Request menu option after which it will be approved by telecom providers. This approval process can take time (around 3 days to 1 week based on my previous applications) and may require the following:

Business registration details - registration certificate and KRA PIN
Contact Information for notifications
Use-case explanation (e.g. alerts, marketing, OTP)

With Africas Talking it costs KES 7,000 to register for each telecom provider (so KES 14,000 for Airtel and Safaricom)

e) Node.js 18+
Make sure you have Node installed, run node -v to confirm
If not, Download from https://nodejs.org/
Node is preferred because Africa’s Talking SDK supports it well and integrates naturally with TypeScript and Express

2) Minimal Project Setup

Run the following commands to prepare the environment to run the requests. You will create a clean workspace for your SMS project, initialize a Node.js project and install the necessary dependencies needed to get stuff running.

mkdir at-sms-starter
cd at-sms-starter
npm init -y
npm install africastalking express dotenv
npm install -D typescript ts-node @types/node @types/express
npx tsc --init

africastalking → official SDK to talk to the SMS API
express → lightweight web server for endpoints
dotenv → loads environment variables from .env
npm install -D typescript ts-node @types/node @types/express → Adds TypeScript support and enables running .ts files directly without compiling manually

In your package.json ensure you have this:

{
  "scripts": {
    "dev": "ts-node src/at-sms.ts"
  }
}

This will allow you to run your app with npm run dev
Next create the following files which wil have the main file and environment variables like AT_USERNAME and AT_SENDER_ID:

src/at-sms.ts
.env

3) `.env` Environment variables template and field explanations

AT_API_KEY=your_sandbox_or_live_api_key
AT_USERNAME=sandbox
AT_SENDER_ID=SandboxSender
PORT=5000
SMS_BATCH_SIZE=20
SMS_BATCH_DELAY_MS=500

What these fields do

AT_API_KEY: authentication key used in API calls
AT_USERNAME: decides your account context (sandbox for tests)
AT_SENDER_ID: sender identity recipients will see
SMS_BATCH_SIZE: recipients to process per batch (for bulk sms)
SMS_BATCH_DELAY_MS: pause between batches to avoid spikes

4) Main File with the logic

Add the following code block to your src/at-sms.ts file that we created above;

// Load environment variables from .env file into process.env
import "dotenv/config";

// Import Express types and the Express library
import express, { Request, Response } from "express";

// Import the Africa's Talking SDK
import AfricasTalking from "africastalking";

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

/**
 * Safely read an environment variable.
 * Throws an error if the variable is missing – this prevents the app from
 * running with incomplete configuration.
 */
function required(name: string): string {
  const value = process.env[name];
  if (!value) throw new Error(`Missing env var: ${name}`);
  return value;
}

/**
 * Convert any Kenyan phone number into the international format required
 * by Africa's Talking API (e.g., +2547XXXXXXXX).
 *
 * Why? The API expects numbers starting with +254. Users might enter:
 *   - 0712345678   (local)
 *   - 254712345678 (without +)
 *   - +254712345678 (already correct)
 *
 * This function handles all three cases thorugh normalization.
 */
function normalizeKenyanPhone(phone: string): string {
  // Remove spaces and dashes (e.g., "0712-345-678" -> "0712345678")
  const cleaned = phone.replace(/\s+/g, "").replace(/-/g, "");

  // Already international format with +?
  if (cleaned.startsWith("+254")) return cleaned;

  // Starts with 254 but missing the leading +?
  if (cleaned.startsWith("254")) return `+${cleaned}`;

  // Starts with 0 (common local writing) -> replace leading 0 with +254
  if (cleaned.startsWith("0")) return `+254${cleaned.slice(1)}`;

  // Fallback – return as is (maybe it's already correct, or an error will occur later)
  return cleaned;
}

/**
 * Simple sleep/pause function.
 * Used to wait between SMS batches so we don't flood the API and within 
 * the API rate limits
 */
function sleep(ms: number): Promise<void> {
  return new Promise((resolve) => setTimeout(resolve, ms));
}

// The SDK needs your API key and username. We read them from .env
const at = AfricasTalking({
  apiKey: required("AT_API_KEY"),
  username: required("AT_USERNAME"),
});

// Grab the SMS service from the initialised SDK
const sms = at.SMS;

// --------------------------------------------------------------
// CORE SMS SENDING FUNCTION
// --------------------------------------------------------------

/**
 * Sends a single SMS using the Africa's Talking SDK.
 * Handles phone normalisation and message line-ending conversion.
 */
async function sendOneSms(to: string, message: string) {
  const senderId = required("AT_SENDER_ID");

  // Convert the phone number to the format the API expects
  const normalizedTo = normalizeKenyanPhone(to);

  // Africa's Talking recommends using \r\n (CRLF) as line endings.
  // This replace ensures any \n becomes \r\n.
  const normalizedMessage = message.replace(/\r?\n/g, "\r\n");

  const response = await sms.send({
    to: normalizedTo,
    message: normalizedMessage,
    from: senderId,
  });

  return response; // Contains status, message ID, etc.
}

// --------------------------------------------------------------
// EXPRESS ROUTE: SEND ONE SMS
// --------------------------------------------------------------

app.post("/send-one", async (req: Request, res: Response) => {
  try {
    // Extract 'to' and 'message' from the JSON request body
    const { to, message } = req.body as { to?: string; message?: string };

    // Validate input – both fields are required
    if (!to || !message) {
      return res.status(400).json({ ok: false, message: "to and message are required" });
    }

    // Send the SMS using our core function
    const result = await sendOneSms(to, message);
    return res.status(200).json({ ok: true, result });
  } catch (error: any) {
    // If anything fails, send a clear error message back to the client
    return res.status(500).json({
      ok: false,
      message: error?.message ?? "Unknown error",
      response: error?.response?.data ?? null, // Sometimes the SDK provides extra error details
    });
  }
});

// --------------------------------------------------------------
// EXPRESS ROUTE: BULK SMS (WITH BATCHING & DELAY)
// --------------------------------------------------------------

app.post("/send-bulk", async (req: Request, res: Response) => {
  // Read batch settings from .env (with sensible defaults)
  const batchSize = Number(process.env.SMS_BATCH_SIZE || 20);
  const batchDelayMs = Number(process.env.SMS_BATCH_DELAY_MS || 500);

  try {
    // Extract array of phone numbers and the common message
    const { phones, message } = req.body as { phones?: string[]; message?: string };

    // Validate inputs
    if (!phones || phones.length === 0 || !message) {
      return res.status(400).json({
        ok: false,
        message: "phones (array) and message are required",
      });
    }

    // We'll store the result for every phone (success or failure)
    const results: Array<{ phone: string; success: boolean; data?: any; error?: any }> = [];

    // ----------------------------------------------------------
    // BATCH PROCESSING LOOP
    // ----------------------------------------------------------
    // We split the phones array into chunks of size 'batchSize'.
    // For each batch we send SMS one by one, then wait before the next batch.
    for (let i = 0; i < phones.length; i += batchSize) {
      // Get the current batch (e.g., phones[0..19], then 20..39, ...)
      const batch = phones.slice(i, i + batchSize);

      // Send an SMS to each phone in the batch (sequentially, not parallel)
      for (const phone of batch) {
        try {
          const data = await sendOneSms(phone, message);
          results.push({ phone, success: true, data });
        } catch (error: any) {
          // If this specific phone fails, record the error but continue with others
          results.push({
            phone,
            success: false,
            error: error?.response?.data ?? error?.message ?? "Failed",
          });
        }
      }

      // After finishing a batch, wait (unless it was the last batch)
      if (i + batchSize < phones.length) {
        await sleep(batchDelayMs);
      }
    }

    // ----------------------------------------------------------
    // SEND SUMMARY BACK TO CLIENT
    // ----------------------------------------------------------
    const sent = results.filter((r) => r.success).length;
    const failed = results.length - sent;

    return res.status(200).json({
      ok: true,
      summary: { total: results.length, sent, failed, batchSize, batchDelayMs },
      results, // Detailed per-phone result
    });
  } catch (error: any) {
    return res.status(500).json({
      ok: false,
      message: error?.message ?? "Unknown error",
      response: error?.response?.data ?? null,
    });
  }
});

// --------------------------------------------------------------
// START THE SERVER
// --------------------------------------------------------------
const port = Number(process.env.PORT || 4200);
app.listen(port, () => {
  console.log(`AT SMS starter running on http://localhost:${port}`);
  console.log(`Single send: POST http://localhost:${port}/send-one`);
  console.log(`Bulk send: POST http://localhost:${port}/send-bulk`);
});

5) Run and Test the app

Start the server with npm run dev
You should see the console messages indicating the server is running.

Test sending one SMS (replace the phone number with a any number)
On Windows (Command Prompt) run

curl -X POST http://localhost:4200/send-one ^
  -H "Content-Type: application/json" ^
  -d "{\"to\":\"0712345678\",\"message\":\"Hello from AT sandbox\"}"

On macOS / Linux / Git Bash run

curl -X POST http://localhost:4200/send-one \
  -H "Content-Type: application/json" \
  -d '{"to":"0712345678","message":"Hello from AT sandbox"}'

Test Sending bulk SMS

curl -X POST http://localhost:4200/send-bulk \
  -H "Content-Type: application/json" \
  -d '{"phones":["0712345678","0722000000"],"message":"Monthly reminder: your account summary is ready."}'

Note: In sandbox mode, messages are not actually delivered to real phones. On the africas Talking Dashboard under Bulk SMS -> Outbox, you will be able to view all messages sent through your account (sandbox/production) and their statuses whether successful or not.

6) When to Introduce Queues (BullMQ, RabbitMQ, etc.)

This guide uses a simple loop because it is easier for beginners to follow. Move to a queue approach when:

recipient lists become large (hundreds/thousands)
request-response timeout becomes likely (HTTP requests can time out after ~30 seconds)
you need retry persistence across server restarts
multiple admins can trigger campaigns concurrently

At that stage, keep the same sendOneSms function and move the orchestration into worker jobs.

8) Troubleshooting

Symptom	Likely cause	Fix
`401` or auth errors	Invalid key/username pair	Confirm both are from same environment
Message rejected	Sender ID not approved	Use approved sender in production
API says success but user reports no SMS	Network/operator delivery delay	Check AT message status and logs
Too many failures in bulk	Rate pressure or invalid numbers	Reduce batch size, validate numbers first

You do not need a complex architecture to get started. A clean single-file starter teaches the exact moving parts: credentials, send API, formatting, and rate-aware bulk execution. Once this is stable, you can scale safely with queue workers and observability.

Africas Talking also have guides and tutorials on their site which you can use for further assistance and reference - Africa's Talking Developer Docs

A Practical Guide to Publishing and Embedding Power BI Reports with IFrames

Eddy Odhiambo — Tue, 07 Apr 2026 11:31:08 +0000

A step-by-step beginner-friendly walkthrough for publishing a Power BI report and embedding it on a website portal.

Power BI is one of those tools that feels simple at first, then becomes very powerful as soon as you start sharing your work with other people. Building the report is one part of the process. Making it accessible and useful to others is the real final step.

In this guide, I will walk through how to:

Create a Power BI workspace
Upload and publish your .pbix report
Generate an embed link (iframe)
Add the report to a public to make it accessible by others

Why publishing matters in Power BI

Creating visuals in Power BI Desktop is useful, but reports become more impactful when they can be accessed by others. Publishing gives your report a home in Power BI Service, where it can be viewed, shared, and embedded into a website.

If you only keep reports in Desktop:

Access is limited to the local machine
Collaboration is hard
Stakeholders cannot view updates easily
There is no web link for presentation or portfolio use

Publishing solves all that by moving your report to the cloud and giving you options to share it safely.

What you need before starting

Before you begin, make sure these are ready:

A Power BI account (work or school account, depending on your setup)
A finished .pbix file that has the dashboards and reports
Access to Power BI Service at https://app.powerbi.com
A GitHub account (for hosting the website and uploading the .pbix file)
Your report iframe code gotten from Power BI Service site

Step 1: Creating your workspace

In Power BI Service, workspaces are like project rooms where reports and dashboards live.

How to do it

Open https://app.powerbi.com
On the left sidebar, click Workspaces
Click New workspace and give it a preferred name and description of your liking

This step matters because it keeps your work, reports and content organized

Tip: Keep the workspace name professional and clear, for example North America Region - Sales Reports.

Step 2: Uploading and publishing your reports

Now you need to place your .pbix file in the workspace so it can be opened in the browser. We are already assuming you have the report and dashboard report ready as a .pbix file

Option A: Publish directly from Power BI Desktop (recommended & faster)

Open your report in Power BI Desktop
Click Publish from the menu options in top bar

Choose the workspace name you created above
Wait for the success confirmation

Option B: Upload from Power BI Service

Open the target workspace in Power BI Service
Click Import or New > Report or Workbook > From this Computer

Select your Reports/Dashboard .pbix file
Wait for import completion

What to check after upload

The report appears under the workspace content
The dataset/model is visible
Opening the report shows all visuals correctly

Your report should display like this:

Step 3: Generating the embed code (iframe)

Once your report is in Power BI Service, you can generate code that lets the report appear inside a web page.

How to generate iframe embed code

Open the report in Power BI Service
Click File or Share (depends on UI version)
Look for Embed report or Publish to web
Choose the appropriate embed option
Copy the generated <iframe ... ></iframe> code

If prompted, confirm that you understand visibility settings.

Important note on privacy

There are different embedding methods in Power BI:

Publish to web: public access, good for portfolio/demos, not for sensitive data
Secure embedding/organization methods: for internal/private use

Always confirm data is safe to make public before using it.

Now that you have the iframe lik, you can share it with your developer or add it yourself to the index.html file of your public site for others to view.

You can use a clean layout with a heading, short description, and responsive iframe container.

Common issues and how to fix them quickly

1) Report not showing on website

Possible causes:

Wrong iframe copied
Embed permission not set

Fix:

Re-copy iframe from Power BI Service
Check on the permissions applied and confirm if accurate
Refresh with hard reload

2) Blank area where report should appear

Possible causes:

Browser blocked mixed content
Iframe width/height too small

Fix:

Ensure https is used
Set iframe width to 100% and enough height (700px+)

3) Wrong workspace or missing report

Possible causes:

Uploaded to a different workspace
Report upload failed silently

Fix:

Confirm workspace name
Re-upload the .pbix file and wait for completion

That's all you need to publish and embed your POwer BI Dashboards and Reports online, easy as 123

M-Pesa B2B API Integration Guide (TypeScript): Build, Run, and Understand It End-to-End

Eddy Odhiambo — Mon, 30 Mar 2026 14:53:08 +0000

A practical, beginner-friendly walkthrough for sending M-Pesa B2B payments from your M-Pesa Paybill/Till to another business's Paybill/Till Accounts through Node.js and Express

What You Will Build

By the end of this guide, you will have:

A minimal TypeScript app that requests a Daraja OAuth token
A B2B payment request (BusinessPayBill) sender
Local webhook endpoints for ResultURL and QueueTimeOutURL
A simple correlation store so you can track request-to-callback flow

This guide intentionally uses a simple standalone structure so you can run it without any custom internal framework.

1) How M-Pesa B2B API Works (Mental Model)

The M-Pesa B2B API is asynchronous (you do not have to wait for response and can proceed to other taks). The first API response you will receive confirms acceptance for processing, not final settlement of the transaction.

Important: Your source of truth is the callback payload sent back to (ResultURL / QueueTimeOutURL), not only the initial POST response.

2) Prerequisites

A Daraja app with consumer key , consumer secret and b2b api enabled. You can create and enable these on daraja portal.
A working Paybill from Safaricom (Use test credentials for sandbox)
Initiator name + security credential (or initiator password if you are generating credential externally) - this is the user set in the mpesa org portal with businessAPIOrgInitiator role enabled for the user.
Public callback URLs for local development (for example, ngrok)
Node.js 18+ installed

2.1) Setting up the initiator credentials & certificates for encryption

We have already determined that you need the initiator credentials for you to make B2B payment requests through the API. An initiator is like the user authorized to make transactions on the paybill/till.

To get these credentials, you will have to login to the Mpesa Org Portal with the login credentials you received from Safaricom Mpesa via email and create a new user with the following role:
`BusinessAPIOrgInitiator' - this roles allows the created user to make API requests on the paybill/till i.e. the B2B API requests.
Below is the roles your user should have to be able to make B2B API requests:

The username for this user becomes the initiator name and password for this user is what will be encrypted to get the securityCredential.

Now onto the security credential encryption, please note that this is not the initiator password itself but an encrypted version of it. To encrypt you must:

Download Safaricom public certificate from the Daraja API documentation page. Here is the link - Getting Started
Encrypt your initiator password using it. The steps for encrytption are on the API Documentation page

If you're testing you can use sandbox provided credential in the simulator
In production you will have to generate it using OpenSSL

3) Minimal Project Setup

Go to an empty directory and run the following to create an app directory and setup express and typescript:
`

   mkdir mpesa-b2b-starter
   cd mpesa-b2b-starter
   npm init -y
   npm install axios express dotenv
   npm install -D typescript ts-node @types/node @types/express
   npx tsc --init

Update your package.json scripts:

{
  "scripts": {
    "dev": "ts-node src/mpesa-b2b.ts"
  }
}

Create your environment file and the main file (always remember not to deploy these to GitHub - add the .env to gitignore).

src/mpesa-b2b.ts
.env

4) Environment Variables Explained

You can use this .env template and just add your own values:

# Daraja credentials
MPESA_ENV=sandbox
MPESA_CONSUMER_KEY=your_consumer_key
MPESA_CONSUMER_SECRET=your_consumer_secret

# B2B initiator setup
MPESA_INITIATOR_NAME=testapi (this is the test user for sandbox testing)
MPESA_SECURITY_CREDENTIAL=your_pre_encrypted_security_credential

# Shortcodes and payment details
MPESA_PARTY_A=600000
MPESA_PARTY_B=600111
MPESA_AMOUNT=100
MPESA_ACCOUNT_REF=INV10001
MPESA_REMARKS=Sandbox B2B test payment
MPESA_REQUESTER=254700000000

# Local webhook server
PORT=5000
PUBLIC_BASE_URL=https://your-ngrok-subdomain.ngrok.io

Field meanings

MPESA_PARTY_A: Sender shortcode/paybill (your business)
MPESA_PARTY_B: Receiving shortcode/paybill (destination business)
MPESA_SECURITY_CREDENTIAL: Encrypted initiator secret expected by Daraja. (for testing use value presented in daraja's simulator.)
PUBLIC_BASE_URL: Must be publicly reachable for callbacks from Mpesa daraja

5) This is the main file that will create and send the B2B payment request:

Create src/mpesa-b2b.ts if not already and add the following code:

// IMPORTANT: The M-Pesa API is ASYNCHRONOUS. The initial POST response only tells you if the request was accepted for processing. 
//The final settlement status comes via a callback (ResultURL) a few seconds later.
// ------------------------------------------

// Load environment variables from .env file
import "dotenv/config";

// Axios for HTTP requests to Daraja
import axios from "axios";

// Express for our local webhook server (receives callbacks)
import express, { Request, Response } from "express";

// --------------------------------------------------------------
// 1. TYPE DEFINITIONS
// --------------------------------------------------------------

// We store pending transactions in memory so we can match incoming callbacks with the original request details.
type PendingTx = {
  createdAt: string;      // When the payment request was sent
  amount: string;         // Amount requested (string for logging)
  partyA: string;         // Your shortcode (sender)
  partyB: string;         // Destination shortcode (receiver)
  accountReference: string; // Invoice or order reference
};

// --------------------------------------------------------------
// 2. SETUP EXPRESS SERVER
// --------------------------------------------------------------
const app = express();
app.use(express.json()); // Automatically parse JSON request bodies

// In-memory store: maps OriginatorConversationID -> PendingTx
// This allows us to correlate the callback with the original request.
const pendingByOriginatorConversationId = new Map<string, PendingTx>();

// Determine if we are in sandbox or production mode
const env = process.env.MPESA_ENV === "production" ? "production" : "sandbox";

// Daraja base URL changes depending on environment
const baseUrl =
  env === "production"
    ? "https://api.safaricom.co.ke"
    : "https://sandbox.safaricom.co.ke";

// --------------------------------------------------------------
// 3. HELPER FUNCTION: SAFELY READ ENVIRONMENT VARIABLES
// --------------------------------------------------------------
function required(name: string): string {
  const value = process.env[name];
  if (!value) throw new Error(`Missing env var: ${name}`);
  return value;
}

// --------------------------------------------------------------
// 4. OBTAIN OAUTH ACCESS TOKEN FROM DARAJA
// --------------------------------------------------------------
/**
 * Daraja requires a Bearer token for all API calls (except OAuth itself).
 * The token is obtained by sending your Consumer Key and Consumer Secret
 * as a Basic Auth header.
 *
 * Token expires after ~1 hour; in production you should cache and refresh it.
 */
async function getAccessToken(): Promise<string> {
  const consumerKey = required("MPESA_CONSUMER_KEY");
  const consumerSecret = required("MPESA_CONSUMER_SECRET");

  // Combine key and secret with a colon, then Base64 encode
  const auth = Buffer.from(`${consumerKey}:${consumerSecret}`).toString("base64");

  // Make GET request to the OAuth endpoint
  const response = await axios.get(
    `${baseUrl}/oauth/v1/generate?grant_type=client_credentials`,
    {
      headers: { Authorization: `Basic ${auth}` },
      timeout: 20000, // 20 seconds timeout
    }
  );

  if (!response.data?.access_token) {
    throw new Error("No access_token returned from Daraja OAuth endpoint.");
  }

  return response.data.access_token as string;
}

// --------------------------------------------------------------
// 5. CORE B2B PAYMENT FUNCTION
// --------------------------------------------------------------
/**
 * Sends a B2B payment request to Daraja.
 * Steps:
 *   1. Get a fresh access token.
 *   2. Build the B2B request payload according to Daraja spec.
 *   3. POST to /mpesa/b2b/v1/paymentrequest.
 *   4. Store pending transaction details for later callback correlation.
 *   5. Return the immediate API response.
 */
async function sendB2BPayment() {
  // ---- Step 1: Get token ----
  const token = await getAccessToken();

  // ---- Step 2: Read required fields from .env ----
  const initiator = required("MPESA_INITIATOR_NAME");
  const securityCredential = required("MPESA_SECURITY_CREDENTIAL");
  const partyA = required("MPESA_PARTY_A");          // Your shortcode
  const partyB = required("MPESA_PARTY_B");          // Destination shortcode
  const amount = required("MPESA_AMOUNT");
  const accountReference = required("MPESA_ACCOUNT_REF").slice(0, 13); // Max 13 chars
  const remarks = required("MPESA_REMARKS").slice(0, 100);             // Max 100 chars
  const requester = required("MPESA_REQUESTER");     // Mobile number of person requesting
  const publicBaseUrl = required("PUBLIC_BASE_URL"); // e.g., https://abc123.ngrok.io

  // Build callback URLs that M-Pesa will call after processing
  const resultUrl = `${publicBaseUrl}/callbacks/mpesa/b2b/result`;
  const timeoutUrl = `${publicBaseUrl}/callbacks/mpesa/b2b/timeout`;

  // ---- Step 3: Construct the B2B payload ----
  // Field explanations:
  // - CommandID: "BusinessPayBill" means Paybill to Paybill.
  // - SenderIdentifierType: "4" = Shortcode (Paybill/Till)
  // - RecieverIdentifierType: "4" = Shortcode (note the typo in API spec "Reciever")
  // - SecurityCredential: Encrypted initiator password (NOT plain text)
  // - Initiator: Username of the B2B initiator (set in M-Pesa Org Portal)
  // - Requester: Optional mobile number for audit
  const payload = {
    Initiator: initiator,
    SecurityCredential: securityCredential,
    CommandID: "BusinessPayBill",
    SenderIdentifierType: "4",
    RecieverIdentifierType: "4",
    Amount: String(Math.floor(Number(amount))), // Ensure integer
    PartyA: partyA,
    PartyB: partyB,
    AccountReference: accountReference,
    Requester: requester,
    Remarks: remarks,
    QueueTimeOutURL: timeoutUrl,
    ResultURL: resultUrl,
  };

  // ---- Step 4: Send the request ----
  const response = await axios.post(
    `${baseUrl}/mpesa/b2b/v1/paymentrequest`,
    payload,
    {
      headers: {
        Authorization: `Bearer ${token}`,
        "Content-Type": "application/json",
      },
      timeout: 30000, // 30 seconds
    }
  );

  // ---- Step 5: Store pending transaction for callback correlation ----
  // The immediate response contains an OriginatorConversationID.
  // When the callback arrives, it will include the same ID, allowing us to
  // link the callback to the original request.
  const originatorConversationId = response.data?.OriginatorConversationID as
    | string
    | undefined;

  if (originatorConversationId) {
    pendingByOriginatorConversationId.set(originatorConversationId, {
      createdAt: new Date().toISOString(),
      amount: payload.Amount,
      partyA,
      partyB,
      accountReference,
    });
  }

  // Return the raw API response (for logging / debugging)
  return response.data;
}

// --------------------------------------------------------------
// 6. WEBHOOK: RESULT URL (FINAL TRANSACTION STATUS)
// --------------------------------------------------------------
/**
 * M-Pesa calls this endpoint after processing the B2B payment.
 * It contains the final ResultCode (0 = success, others = error).
 *
 * IMPORTANT: This is your source of truth for transaction settlement.
 * Do NOT rely only on the initial POST response.
 */
app.post("/callbacks/mpesa/b2b/result", (req: Request, res: Response) => {
  // Daraja sometimes nests the result inside Result or Body.Result.
  // This line tries all common locations.
  const body = req.body?.Result ?? req.body?.Body?.Result ?? req.body;

  const originatorConversationId = body?.OriginatorConversationID as
    | string
    | undefined;
  const resultCode = body?.ResultCode;   // "0" = success
  const resultDesc = body?.ResultDesc;   // Human-readable message

  // Look up the pending transaction using the ID from the callback
  const pending = originatorConversationId
    ? pendingByOriginatorConversationId.get(originatorConversationId)
    : undefined;

  // Log everything – in production you'd store this in a database
  console.log("B2B Result callback:", {
    originatorConversationId,
    resultCode,
    resultDesc,
    pending,          // Shows original request details
    body,             // Full raw payload
  });

  // You must respond with a specific JSON object to acknowledge receipt.
  // ResultCode "0" means your server accepted the callback.
  res.status(200).json({ ResultCode: "0", ResultDesc: "Accepted" });
});

// --------------------------------------------------------------
// 7. WEBHOOK: TIMEOUT URL (WHEN DARAJA CAN'T REACH YOUR RESULT URL)
// --------------------------------------------------------------
/**
 * If M-Pesa attempts to call your ResultURL but gets a timeout or network error,
 * it will call this TimeoutURL instead. This usually indicates a problem with
 * your server's availability.
 */
app.post("/callbacks/mpesa/b2b/timeout", (req: Request, res: Response) => {
  const body = req.body?.Result ?? req.body?.Body?.Result ?? req.body;
  console.log("B2B Timeout callback – check your ResultURL server!", body);
  // Still acknowledge to prevent repeated retries
  res.status(200).json({ ResultCode: "0", ResultDesc: "Accepted" });
});

// --------------------------------------------------------------
// 8. EXPRESS ROUTE: TRIGGER A B2B PAYMENT
// --------------------------------------------------------------
/**
 * This endpoint is called by you (or your frontend) to initiate a B2B payment.
 * POST /send-b2b
 *
 * Example using curl:
 *   curl -X POST http://localhost:5000/send-b2b
 */
app.post("/send-b2b", async (_req: Request, res: Response) => {
  try {
    const data = await sendB2BPayment();
    res.status(200).json({ ok: true, data });
  } catch (error: any) {
    // Log and return a user-friendly error
    res.status(500).json({
      ok: false,
      message: error?.message ?? "Unknown error",
      response: error?.response?.data ?? null, // Often contains Daraja's error details
    });
  }
});

// --------------------------------------------------------------
// 9. START THE SERVER
// --------------------------------------------------------------
const port = Number(process.env.PORT || 5000);
app.listen(port, () => {
  console.log(`M-Pesa B2B server running on http://localhost:${port}`);
  console.log(`Trigger B2B by POST http://localhost:${port}/send-b2b`);
  console.log(`ResultURL: ${process.env.PUBLIC_BASE_URL}/callbacks/mpesa/b2b/result`);
  console.log(`TimeoutURL: ${process.env.PUBLIC_BASE_URL}/callbacks/mpesa/b2b/timeout`);
});

6) Run and Test

Start your public tunnel:
- ngrok example: ngrok http 5000
Put the HTTPS tunnel URL in PUBLIC_BASE_URL inside .env
Start app:
- npm run dev
Trigger payment:
- curl -X POST http://localhost:5000/send-b2b
Watch logs for:
- immediate request response (accept/reject)
- later callback payload on /callbacks/mpesa/b2b/result

7) Common Errors (and What They Mean)

Symptom	Most likely cause	Fix
`401` on token request	Wrong consumer key/secret	Regenerate and update `.env`
`Invalid SecurityCredential`	Credential does not match initiator/certificate	Regenerate credential correctly
Initial call succeeds but no callback	Callback URL unreachable	Verify public HTTPS URL and firewall
`ResponseCode: 0` but transfer not complete	Async model misunderstood	Wait for result callback before marking success

8) Production Hardening Checklist

Store secrets in a vault/Secret Key Managers, not in files committed to git
Make callback handlers idempotent (same callback may retry)
Persist ConversationID and OriginatorConversationID for reconciliation
Redact sensitive fields from your logs
Add retries with backoff only for safe transient failures
Set system admin alerts on spikes in timeout/error result codes

You can get more detailed info from safaricom's portal on the link below

Safaricom Daraja Developer Portal

Data Modeling in Power BI Explained (Without the Headache)

Eddy Odhiambo — Sun, 29 Mar 2026 23:39:09 +0000

What really happens when tables “talk” to each other in Power BI. Joins, relationships, star schemas, and the mistakes that quietly break your dashboards.

As I continue my Data Science & Engineering journey at LuxDev HQ, I’m noticing the same pattern everywhere: people rush to pretty charts, then spend hours wondering why the numbers don’t line up. Nine times out of ten, the issue isn’t the visual. It’s the model underneath.

This article is my attempt to explain data modeling in Power BI in plain language: what it is, how joins and relationships differ, how fact and dimension tables fit in, and how star, snowflake, and flat layouts show up in real work. I’ll also walk through where in Power BI you actually click to build these things.

Why data modeling matters before you touch a chart

Power BI makes it easy to drag fields onto a canvas. That’s the fun part. The hard part is making sure that when you filter by “March” or “Customer A,” the engine is counting the right rows the right number of times.

When the model is wrong, you get symptoms like:

Totals that don’t match your source system
Duplicates that appear out of nowhere
Slicers that seem to “half work”
Reports that were fine in Excel but fall apart in Power BI

Almost always, that traces back to how tables are connected, either in Power Query (merges/joins) or in the Model (relationships). Fixing visuals won’t fix that but fixing the model might.

Data modeling decides how your tables are structured and how they connect, so that measures and filters behave correctly.

In Power BI, you’re usually doing two related things:

Shaping data — cleaning, renaming, sometimes merging tables into one wider table (Power Query).
Connecting tables — leaving tables separate but linking them with relationships so DAX can aggregate across them (Model view).

Both matter. They’re not the same tool, and mixing them up is where most people often get stuck.

Part 1 — Joins: combining tables in Power Query

A join answers: “If I stack these two tables side by side on a key, which rows do I keep?”

In Power BI Desktop, joins show up when you merge queries, You pick two tables, the matching column(s), and a join kind. That’s the same logic used in SQL joins.

Below is a simple mental picture: Table A (left) and Table B (right), matched on something like CustomerID.

INNER JOIN — only the overlap

This keeps rows where the key exists in both tables. For example you have Orders and Customers. You only want orders where you actually have a customer record (no “orphan” orders).

When it’s useful: Clean reporting, official numbers, “show me only valid combinations.”

LEFT JOIN — everything on the left, plus matches from the right

This keeps all rows from the left table. If there’s no match on the right, you still keep the row; the extra columns from the right show as null (blank).

When it’s useful: Operational reality — you rarely throw away transactions just because someone forgot to create the customer card.

RIGHT JOIN — everything on the right, plus matches from the left

Maintains the same idea as LEFT JOIN, but the “keep everything” side is the right table.

In practice, many people swap which table is left vs right and use a LEFT join instead, because it’s easier to read. RIGHT isn’t wrong; it’s just less common.

FULL OUTER JOIN — everything from both sides

This keeps all rows from both tables. Where there’s no match, you get blanks on the missing side. For example during reconciliation in System A payments vs System B payments*, you would want to see what’s in A only, what’s in B only, and what’s in both.

When it’s useful: During audits, matching two different sources, finding gaps.

LEFT ANTI JOIN — “only in the left, not in the right”

Returns rows from the left table that have no match in the right table. For example payments that appear in M-Pesa (or a bank file) but never landed in your internal system.

When it’s useful: Exception lists, “what are we missing?”, data quality checks.

RIGHT ANTI JOIN — “only in the right, not in the left”

Same as LEFT ANTI, but flipped: rows that exist only in the right table for example customer records that exist in a CRM but have never placed an order.

Part 2 — Relationships: connecting tables in the Model

Relationships are built on the Model view (the diagram with boxes and lines). Drag from one column to another, or click on Modeling → Manage Relationships then add/edit/delete in a list. You will need to set:

Cardinality (one-to-many, many-to-many, one-to-one)
Cross-filter direction (single or both)
Active vs inactive when you have more than one path between the same tables

Cardinality in everyday terms

One-to-many (1:M) — this is the default star-schema pattern for example one customer having many orders. The “many” side is usually your fact table (transactions). The “one” side is the dimension (customer, product, date).

Many-to-many (M:M) — this duplicates on both sides of the relationship. Power BI can model this, but it’s easier to get wrong measures if you don’t understand what’s happening. Often the fix is a bridge table or reshaping the data.

One-to-one (1:1) mostly rare but sometimes used when one entity was split across two tables for maintenance or row size reasons.

Active vs inactive relationships

You can have multiple relationships between the same two tables, but only one is active at a time for a given path.

Joins vs relationships — side by side

	Joins (Power Query merge)	Relationships (Model)
Where	Power Query Editor	Model view / Manage Relationships
Effect	Can combine into one table (wider result)	Tables stay separate; engine knows how they link
When it runs	During refresh (data load)	At query time for visuals and DAX
Flexibility	Fixed shape after refresh	Multiple measures can use different logic (e.g. `USERELATIONSHIP`)

From Spreadsheets to Insights: How Excel Powers Real-World Data Analysis

Eddy Odhiambo — Sun, 29 Mar 2026 16:09:44 +0000

A technical look at what Excel really is, where it wins in production workflows, and the formulas analysts lean on when money and operations are on the line.

Why excel still matters

In a world of warehouses, notebooks, and orchestration pipelines, Microsoft Excel remains the default “operating system” for ad‑hoc analysis, financial reconciliations, regulatory exports, and operational reporting. It is not nostalgia, it is latency: the time from question to answer when a stakeholder or manager needs a number today.

This article is gives a crisp mental model of Excel as an analytical tool, plus concrete patterns that show up in real financial and operational systems from my observations as I begin on my Data Science & Engineering Journey at LuxDev HQ.

What Excel is (and is not)

Excel is a grid-based calculation environment: rows, columns, cells that can hold values, text, or formulas that reference other cells. Under the hood you get:

Recalculation when inputs change
Built-in functions (statistical, financial, text, logical, lookup)
PivotTables for aggregation without writing formulas for every slice
What-if tools (Goal Seek, Scenario Manager, Data Tables)
Lightweight automation via Office Scripts / VBA (platform-dependent)
Integration with external data (Power Query on desktop, connectors in Microsoft 365)

Excel is not a production database, a source of truth for concurrent writes at scale, or a substitute for versioned ELT. It is unbeatable for exploration, reconciliation, and human-in-the-loop workflows that bridge raw extracts and decisions.

Some practical real-world use cases of excel

1. Sales and revenue operations

Imagine downloading thousands of lines of sales data from CRM platforms and management asks for breakdowns by product or region or comparisons between months. This is where excel comes in, instead of going back to the IT team to help you add those factors in the reports or waiting for a dashboard update, you just open Excel and reshape the data yourself.

2. Managing Stocks, Inventory and supply chain

Even in businesses with proper systems, people still fall back to Excel to answer questions like what should be reordered this week, what product is running out? Most stores do not have ERP systems to automate such so they rely on excel to keep simple sheets with Item names, Quantities, Minimum levels etc.

3. Internal dashboards (the “manager view”)

Sometimes, people don’t want dashboards or tools, they just need a file they can open and understand immediately. Excel makes this easy through pivot-driven summaries + conditional formatting with a summary at the top, a few highlighted numbers, and maybe some color to show what’s good or bad

No logins, no loading screens, just open and see what’s going on.

Making sense of data with lookups in excel

A very common situation in Excel is this; You have one list with basic data, and another list with extra details and you want to connect them. For example, one sheet has product codes and another sheet has the product names and prices. Excel can help you “fill in the gaps” automatically through its lookup functions

`VLOOKUP` — vertical join in one formula

Syntax:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: what you’re matching (e.g. customer ID in the transaction row).
table_array: the lookup table; the first column must contain the key.
col_index_num: which column to return (1 = key column).
range_lookup: FALSE for exact match (almost always what you want in finance). TRUE means approximate match (sorted data—for tax brackets, depreciation bands, etc.).

Think of it like this - “Find this item in another table, and bring me the information next to it.”

Example: “You have a product code, and you want Excel to return the price.”

Assume Transactions!A2 has PID P-1001. Tariffs are on sheet Tariffs with columns PID | ListPrice | Cost | MarginPct.

=VLOOKUP(A2, Tariffs!$A$2:$D$500, 4, FALSE)

`HLOOKUP` — follows the same idea as VLOOKUP but with horizontal keys

Syntax:

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

`INDEX` + `MATCH`

VLOOKUP breaks when someone inserts a column to the left of the return field and that's why most people use the INDEX + MATCH. It decouples where the key is from where the value is.

=INDEX(Tariffs!$D$2:$D$500, MATCH(A2, Tariffs!$A$2:$A$500, 0))

MATCH finds the row of the exact PID.
INDEX returns the margin from column D.

On modern Excels after 2021, XLOOKUP replaces most VLOOKUP/HLOOKUP/INDEX/MATCH patterns with one readable function which makes it easier.

Other functions like SUMIFS and COUNTIFS come in when doing multi-criteria analysis, or when dealing with conditions and/or adding filters to your numbers.

What I learned: a personal reflection

When I started working with data to view and manage reports, I treated Excel as “the boring office app” and chased fancier tools first. That was backwards. Learning Excel forced me to ask better questions, understand joins between data (better than how SQL did) and those questions didn’t go away when I move to bigger tools, they just improved my understanding.

DEV Community: Eddy Odhiambo

Relational SQL 123s: DDL, DML, Filtering, and Turning Rows into Meaning

DDL vs DML: structure versus contents

CREATE, ALTER, INSERT, UPDATE, and DELETE in practice

Filtering with WHERE clause: precision over intuition

CASE WHEN: rules as readable columns

Transactional SMS Setup with Africa's Talking SDK For Single and Bulk messages

1) Prerequisites to get started

2) Minimal Project Setup

3) .env Environment variables template and field explanations

4) Main File with the logic

5) Run and Test the app

6) When to Introduce Queues (BullMQ, RabbitMQ, etc.)

8) Troubleshooting

A Practical Guide to Publishing and Embedding Power BI Reports with IFrames

Why publishing matters in Power BI

What you need before starting

Step 1: Creating your workspace

How to do it

Step 2: Uploading and publishing your reports

Option A: Publish directly from Power BI Desktop (recommended & faster)

Option B: Upload from Power BI Service

What to check after upload

Step 3: Generating the embed code (iframe)

How to generate iframe embed code

Important note on privacy

Common issues and how to fix them quickly

1) Report not showing on website

2) Blank area where report should appear

3) Wrong workspace or missing report

M-Pesa B2B API Integration Guide (TypeScript): Build, Run, and Understand It End-to-End

What You Will Build

1) How M-Pesa B2B API Works (Mental Model)

2) Prerequisites

2.1) Setting up the initiator credentials & certificates for encryption

3) Minimal Project Setup

4) Environment Variables Explained

Field meanings

5) This is the main file that will create and send the B2B payment request:

6) Run and Test

7) Common Errors (and What They Mean)

8) Production Hardening Checklist

Data Modeling in Power BI Explained (Without the Headache)

Why data modeling matters before you touch a chart

Part 1 — Joins: combining tables in Power Query

INNER JOIN — only the overlap

LEFT JOIN — everything on the left, plus matches from the right

RIGHT JOIN — everything on the right, plus matches from the left

FULL OUTER JOIN — everything from both sides

LEFT ANTI JOIN — “only in the left, not in the right”

RIGHT ANTI JOIN — “only in the right, not in the left”

Part 2 — Relationships: connecting tables in the Model

Cardinality in everyday terms

Active vs inactive relationships

Joins vs relationships — side by side

From Spreadsheets to Insights: How Excel Powers Real-World Data Analysis

Why excel still matters

What Excel is (and is not)

Some practical real-world use cases of excel

1. Sales and revenue operations

2. Managing Stocks, Inventory and supply chain

3. Internal dashboards (the “manager view”)

Making sense of data with lookups in excel

VLOOKUP — vertical join in one formula

HLOOKUP — follows the same idea as VLOOKUP but with horizontal keys

INDEX + MATCH

What I learned: a personal reflection

3) `.env` Environment variables template and field explanations

`VLOOKUP` — vertical join in one formula

`HLOOKUP` — follows the same idea as VLOOKUP but with horizontal keys

`INDEX` + `MATCH`