DEV Community: Olamilekan Lamidi

Laravel Google Sheets v1.1.0: Diff Previews, Sync Methods, Dry Runs, and Retry Backoff

Olamilekan Lamidi — Mon, 01 Jun 2026 11:31:23 +0000

Google Sheets is often where business workflows begin.

For Laravel apps, that usually means imports, exports, internal dashboards, user-managed spreadsheets, and the occasional "can we sync this automatically?" request.

olamilekan/laravel-google-sheets v1.1.0 adds several features for those real-world workflows:

Laravel 13 support
Import diff previews
Model, CSV, API, and two-way sync methods
Dry-run sync command
Human-friendly validation error sheets
Retry and exponential backoff for temporary API failures
Better testing fakes
A GitHub Actions test matrix

This is a backward-compatible feature release with 17 commits since v1.0.0.

Install

composer require olamilekan/laravel-google-sheets

Publish the config:

php artisan vendor:publish --tag=google-sheets-config

Laravel 13 support

The package now supports:

Laravel 10
Laravel 11
Laravel 12
Laravel 13

That should make it easier to use the same package across older and newer Laravel applications.

Import diff previews

You can now preview an import before writing anything.

use App\Models\User;
use Olamilekan\GoogleSheets\Facades\GoogleSheets;

$preview = GoogleSheets::connection('users')
    ->diffAgainst(User::query(), key: 'email')
    ->rules([
        'name' => ['required', 'string'],
        'email' => ['required', 'email'],
    ])
    ->preview();

$preview->counts();

The preview separates rows into:

new
changed
deleted
invalid
conflicts

Example:

[
    'new' => 1,
    'changed' => 2,
    'deleted' => 0,
    'invalid' => 1,
    'conflicts' => 0,
]

You can also control which fields are compared:

$preview = GoogleSheets::connection('users')
    ->diffAgainst(User::query(), key: 'email')
    ->only(['name', 'role'])
    ->except(['updated_at'])
    ->preview();

This is useful when building admin import screens or approval flows.

Sync methods

v1.1.0 adds sync helpers for moving data between Google Sheets and common data sources.

Sync from Eloquent to Google Sheets:

use App\Models\User;
use Olamilekan\GoogleSheets\Facades\GoogleSheets;

$report = GoogleSheets::connection('users')
    ->syncFromModel(User::class, keyColumn: 'email', options: [
        'columns' => ['name', 'email', 'role'],
        'conflict' => 'app_wins',
    ]);

Sync from Google Sheets to Eloquent:

$report = GoogleSheets::connection('users')
    ->syncToModel(User::class, keyColumn: 'email');

Import and export CSV files:

$report = GoogleSheets::connection('users')
    ->importCsv(storage_path('app/users.csv'), keyColumn: 'email');

$report = GoogleSheets::connection('users')
    ->exportCsv(storage_path('app/users-export.csv'));

Sync API data into a sheet:

$report = GoogleSheets::connection('orders')
    ->syncFromApi('https://api.example.com/orders', keyColumn: 'order_id', options: [
        'data_key' => 'data',
        'headers' => ['Authorization' => 'Bearer '.$token],
    ]);

Push sheet data to an API:

$report = GoogleSheets::connection('orders')
    ->syncToApi('https://api.example.com/orders/bulk');

Each sync returns a report:

$report->counts();
$report->created();
$report->updated();
$report->conflicts();
$report->errors();

Two-way sync with conflict handling

Two-way sync needs clear conflict behavior.

$report = GoogleSheets::connection('users')
    ->syncTwoWay(User::class, keyColumn: 'email', options: [
        'conflict' => 'fail',
    ]);

Supported strategies:

app_wins
sheet_wins
skip
fail

Dry-run sync command

You can preview command-line imports without writing rows:

php artisan google-sheets:sync "App\\Imports\\UsersImport" users --dry-run

Dry runs compare rows using the import class target and key, then apply validation rules when available.

Validation error sheets

Instead of only throwing validation errors, the package can write row-level issues into an error sheet.

$validRows = GoogleSheets::connection('users')->validateWithErrorSheet([
    'name' => ['required', 'string'],
    'email' => ['required', 'email'],
]);

That gives spreadsheet users a clearer place to fix bad rows.

Queue sync jobs

Longer syncs can be queued:

GoogleSheets::connection('users')
    ->queueSync('syncFromModel', [User::class, 'email'], queue: 'imports');

Audit logs and notifications

Sync activity is logged through Laravel's logger and kept in an in-process audit log:

$records = GoogleSheets::connection('users')->syncAuditLog();

You can also send notifications:

GoogleSheets::connection('users')->syncFromModel(User::class, 'email', [
    'notify' => [
        'slack_webhook' => config('services.slack.sync_webhook'),
        'mail_to' => 'ops@example.com',
    ],
]);

Retry and backoff

Temporary Google Sheets API failures are retried with exponential backoff and jitter.

// config/google-sheets.php
'retry' => [
    'enabled' => true,
    'attempts' => 3,
    'delay' => 250,
    'max_delay' => 5000,
],

Runtime control:

$rows = GoogleSheets::withRetries(attempts: 5, delay: 500)->all();
$rows = GoogleSheets::withoutRetries()->all();

This helps with rate limits, quota throttling, backend errors, and other temporary failures.

Testing fakes

You can test spreadsheet behavior without hitting the real API:

use Olamilekan\GoogleSheets\Facades\GoogleSheets;

$fake = GoogleSheets::fake([
    'users' => [
        ['name' => 'Alice', 'email' => 'alice@example.com'],
    ],
]);

GoogleSheets::connection('users')->appendAssoc([
    ['name' => 'Bob', 'email' => 'bob@example.com'],
]);

$fake->assertAppended('users', [
    'name' => 'Bob',
    'email' => 'bob@example.com',
]);

Upgrade

composer update olamilekan/laravel-google-sheets

If you need the new config options:

php artisan vendor:publish --tag=google-sheets-config

Links

GitHub: github.com/oluwatosinolamilekan/laravel-google-sheets

Package:

composer require olamilekan/laravel-google-sheets

Release:

composer update olamilekan/laravel-google-sheets

Building A Laravel Google Sheets Package That Imports, Exports, Caches, Formats, And Tests Cleanly

Olamilekan Lamidi — Wed, 20 May 2026 15:09:45 +0000

Google Sheets often starts as a quick operational tool. A support team tracks users in a sheet, finance exports monthly reports, or an internal dashboard needs a simple spreadsheet backend. The challenge is keeping that workflow Laravel-friendly once it grows beyond a few API calls.

This package wraps the Google Sheets API with a fluent Laravel API for common application tasks: importing rows, exporting reports, managing multiple spreadsheet connections, caching reads, formatting tabs, and testing without hitting Google.

Installation

composer require olamilekan/laravel-google-sheets
php artisan vendor:publish --tag=google-sheets-config

Add your service account credentials path:

GOOGLE_SHEETS_CREDENTIALS_PATH=/path/to/service-account.json

Then configure named spreadsheet connections:

'sheets' => [
    'users' => [
        'spreadsheet_id' => env('GOOGLE_SHEETS_USERS_SPREADSHEET_ID'),
        'sheet' => 'Users',
    ],

    'reports' => [
        'spreadsheet_id' => env('GOOGLE_SHEETS_REPORTS_SPREADSHEET_ID'),
        'sheet' => 'Monthly',
    ],
],

Import Users From Google Sheets

For a simple import, read rows from a named connection:

$rows = GoogleSheets::connection('users')->all();

For a reusable import, create an import class:

use App\Models\User;
use Olamilekan\GoogleSheets\Imports\SheetImport;

class UsersImport extends SheetImport
{
    public function rules(): array
    {
        return ['email' => ['required', 'email']];
    }

    public function model(array $row): User
    {
        return User::updateOrCreate(
            ['email' => $row['email']],
            ['name' => $row['name']]
        );
    }
}

Run it from code:

GoogleSheets::import(new UsersImport(), 'users');

Or from Artisan:

php artisan google-sheets:sync "App\\Imports\\UsersImport" users

Export Reports To Google Sheets

Export classes keep reporting logic out of controllers and commands:

use App\Models\Report;
use Olamilekan\GoogleSheets\Exports\SheetExport;

class ReportsExport extends SheetExport
{
    public bool $replace = true;

    public function headings(): array
    {
        return ['Date', 'Name', 'Total'];
    }

    public function collection()
    {
        return Report::query()
            ->latest()
            ->get()
            ->map(fn (Report $report) => [
                $report->created_at->toDateString(),
                $report->name,
                $report->total,
            ]);
    }
}

Then export:

GoogleSheets::export(new ReportsExport(), 'reports');

Header-Aware Appends And Upserts

Sheets usually have headers. Instead of manually ordering every cell, append associative arrays:

GoogleSheets::connection('users')->appendAssoc([
    ['name' => 'Alice', 'email' => 'alice@example.com', 'role' => 'admin'],
]);

Upsert rows by a key column:

GoogleSheets::connection('users')->upsert('email', [
    ['name' => 'Alice Updated', 'email' => 'alice@example.com', 'role' => 'owner'],
    ['name' => 'Bob', 'email' => 'bob@example.com', 'role' => 'user'],
]);

Validation And Required Headers

Catch bad spreadsheet data before it reaches your app:

GoogleSheets::connection('users')->requireHeaders(['name', 'email', 'role']);

$rows = GoogleSheets::connection('users')->validate([
    'name' => ['required', 'string'],
    'email' => ['required', 'email'],
]);

Multi-Connection Workflows

Named connections make it easy to separate workflows:

$users = GoogleSheets::connection('users')->all();

GoogleSheets::connection('reports')->append([
    ['2026-05-17', 'Monthly Revenue', 15000],
]);

Caching

Enable caching with Laravel's cache system:

GOOGLE_SHEETS_CACHE_ENABLED=true
GOOGLE_SHEETS_CACHE_STORE=redis
GOOGLE_SHEETS_CACHE_TTL=600

Or turn it on per call:

$rows = GoogleSheets::connection('users')->enableCache(300)->all();

When writes happen, the package clears remembered read cache keys for that spreadsheet so later reads can refresh.

Formatting, Formulas, And Named Ranges

Reports often need more than raw data:

GoogleSheets::connection('reports')
    ->sheet('Monthly')
    ->boldHeader()
    ->freezeRows()
    ->autoResizeColumns(1, 4);

GoogleSheets::connection('reports')->append([
    ['Total', GoogleSheets::formula('SUM(C2:C100)')],
]);

$summary = GoogleSheets::connection('reports')
    ->namedRange('MonthlySummary')
    ->get();

Testing

You can fake Google Sheets in tests:

$fake = GoogleSheets::fake([
    'users' => [
        ['name' => 'Alice', 'email' => 'alice@example.com'],
    ],
]);

GoogleSheets::connection('users')->appendAssoc([
    ['name' => 'Bob', 'email' => 'bob@example.com'],
]);

$fake->assertAppended('users', ['name' => 'Bob', 'email' => 'bob@example.com']);

Useful Commands

php artisan google-sheets:list users
php artisan google-sheets:clear reports --sheet=Monthly --range=A2:D100
php artisan google-sheets:sync "App\\Exports\\ReportsExport" reports

Closing

The goal is to make Google Sheets feel like a natural Laravel integration: fluent for simple reads and writes, structured for import and export classes, cache-aware for production use, and fakeable in tests.

I Built fintech-fraud-sim: A TypeScript CLI for Synthetic Fraud Testing Data

Olamilekan Lamidi — Wed, 13 May 2026 20:40:02 +0000

Fraud systems are hard to test well.

Not because engineers cannot write tests, but because the data needs to tell a story. A suspicious transaction is rarely suspicious because of one field. It is usually suspicious because of patterns:

a new account with too many beneficiaries
many transactions in a short window
failed logins followed by a device change
KYC failures mixed with country mismatches
transaction amounts far above normal behavior

Using production customer data for this is risky and often unacceptable. Basic mock data is usually too flat.

So I built fintech-fraud-sim, a TypeScript CLI that generates synthetic fintech users and transactions with configurable fraud patterns.

NPM package: https://www.npmjs.com/package/fintech-fraud-sim

Quick Start

npx fintech-fraud-sim generate --users 1000 --fraud-rate 0.08

That command generates:

users.csv
transactions.csv
users.json
transactions.json
summary.json

You can also choose a format:

npx fintech-fraud-sim generate --users 5000 --fraud-rate 0.12 --format csv

Or send output to a folder:

npx fintech-fraud-sim generate --users 2000 --fraud-rate 0.05 --format json --out ./data

Why I Built It

Fraud detection products need test data in many places:

dashboards
QA fixtures
rules engines
risk scoring services
transaction monitoring demos
analytics pipelines
prototype fraud models

But realistic fraud test data is not just random rows. It needs consistent signals across users and transactions.

For example, an account takeover scenario should not only mark a transaction as suspicious. The user should also show supporting signals such as failed logins, device changes, and country mismatch.

That is the idea behind this CLI.

What the Data Looks Like

Generated users include fields like:

user_id
country
account_age_days
kyc_status
failed_kyc_attempts
device_count
ip_country
declared_country
failed_login_attempts_24h
beneficiary_count_24h
chargeback_count
is_fraud
fraud_pattern
risk_label
reason_codes

Generated transactions include:

transaction_id
user_id
timestamp
amount
currency
channel
beneficiary_id
beneficiary_country
device_id
ip_country
status
is_suspicious
fraud_pattern
reason_codes

The package does not generate real names, emails, phone numbers, BVNs, NINs, or bank account numbers.

Supported Fraud Patterns

The CLI currently supports:

Pattern	Description
`mule_account`	New account, high beneficiary count, rapid funds movement
`account_takeover`	Device change, country mismatch, failed login spike
`velocity_abuse`	Many transactions in a short period
`kyc_abuse`	Multiple failed KYC attempts and inconsistent country data
`chargeback_risk`	Prior chargebacks and high-value transactions
`transaction_spike`	Amount far above user baseline
`cross_border_anomaly`	IP or beneficiary country mismatch
`beneficiary_burst`	Many new beneficiaries within 24 hours

You can select specific patterns:

npx fintech-fraud-sim generate \
  --users 1000 \
  --fraud-rate 0.08 \
  --patterns mule,account_takeover,velocity_abuse

mule is accepted as an alias for mule_account.

Deterministic Generation

For tests and demos, deterministic output is important.

npx fintech-fraud-sim generate --users 1000 --fraud-rate 0.08 --seed demo

The same seed produces the same dataset, which makes the CLI useful in CI and repeatable test suites.

Testing

The project includes tests for:

generating users and transactions
fraud rate handling
seeded deterministic output
zero fraud rate behavior
fraud pattern logic
CSV and JSON writers
CLI execution

Example:

npm test

The test suite uses Node's built-in test runner with tsx, so TypeScript tests can run directly.

Example Use Cases

You can use the generated data to:

demo fraud dashboards
test transaction monitoring rules
validate CSV import workflows
build realistic QA fixtures
prototype risk scoring logic
test data pipelines without exposing customer records

Safety Disclaimer

fintech-fraud-sim is for testing, education, and fraud-model prototyping only. It generates synthetic data and should not be treated as real customer data or used as a production fraud decisioning system.

Final Thoughts

Good fraud testing needs more than random transactions. It needs plausible behavior.

This CLI is my attempt to make that easier for fintech builders, QA teams, and engineers working on risk systems.

NPM: https://www.npmjs.com/package/fintech-fraud-sim

PerfLens: Web Performance Audits Beyond Lighthouse

Olamilekan Lamidi — Fri, 08 May 2026 12:20:55 +0000

Lighthouse is great. I use it, and I still think it is one of the most useful performance tools available to web developers.

But I wanted something closer to the way I actually debug and ship web products, so I built PerfLens.

PerfLens is a browser-first performance auditing extension that gives you live Core Web Vitals, Lighthouse-style scoring, resource diagnostics, root cause summaries, performance history, and launch evidence directly inside your browser workflow.

Install it here: https://chromewebstore.google.com/detail/perflens/gkogamlpcnneeficmcdcnnnhobnbebdc

Why I Built It

A Lighthouse report is a strong snapshot, but performance work rarely ends with one snapshot.

During real development, I usually want to know:

What is slowing this page down right now?
Which resources are heavy?
Are my Core Web Vitals healthy?
What should I fix first?

PerfLens is built for that loop.

What It Includes

PerfLens currently supports:

Core Web Vitals: LCP, FID, CLS, INP, FCP, and TTFB.
0-100 performance scoring.
Resource breakdowns by type and size.
Audits for oversized images, missing lazy loading, render-blocking scripts, large bundles, weak caching, compression gaps, and heavy CSS.
Accessibility checks for alt text, language attributes, viewport setup, headings, and form labels.
Suggestions ranked by impact, effort, and confidence.
Root cause summaries.
Framework and build detection.
Local history per URL.
Launch evidence packs with desktop/mobile audits, screenshots, videos, JSON reports, prioritized issues, and readiness status.

PerfLens vs Lighthouse

PerfLens is not trying to replace Lighthouse. Lighthouse is still excellent for standardized lab audits.

PerfLens is focused on the browser-side workflow around the audit.

Lighthouse	PerfLens
One-time baseline report	Live browser-context auditing
Standard lab diagnostics	Page-level history and trend signals
General recommendations	Suggestions ranked by impact, effort, and confidence
Separate report flow	Popup and DevTools workflow
Audit categories	Framework/build detection and root cause stories
Report export	Launch evidence packs with screenshots, videos, JSON, and readiness status

For me, the biggest difference is that PerfLens tries to move you from "this is slow" to "this is probably why, and this is what to fix next."

Fix It With AI

When an issue appears on a localhost URL, PerfLens can identify it and generate a focused prompt for Cursor, Claude, Codex, or a custom coding agent. That makes it easier to pass local audit findings directly into your coding workflow.

Final

PerfLens came from a simple idea:

Performance auditing should be closer to the browser, closer to the product, and closer to the next fix.

If you already use Lighthouse, PerfLens can sit next to it as a practical debugging and launch-readiness companion.

Multi-Tenant Security in SaaS: Data Isolation Patterns That Actually Work

Olamilekan Lamidi — Mon, 04 May 2026 12:20:59 +0000

Multi-tenancy is the economic engine of SaaS. Sharing infrastructure across customers reduces cost and simplifies operations. But it introduces a risk that can end your business overnight: tenant data leakage.

When one customer can see another customer's data — even accidentally — the consequences are severe. Regulatory fines, contract termination, public disclosure requirements, and irreparable trust damage. I have worked on multi-tenant platforms serving hundreds of organisations, and I have learned that data isolation is not something you bolt on later. It is a foundational architectural decision that shapes everything from database design to query patterns to testing strategy.

This article covers the three main tenancy models, practical implementation patterns in Laravel and Node.js, and the security controls that prevent data leakage in production.

The Three Tenancy Models

1. Database-per-Tenant

Each tenant gets a completely separate database. Maximum isolation, maximum operational complexity.

Pros: Strongest isolation. Easy compliance with data residency requirements. Independent backup and restore per tenant.

Cons: Connection management overhead. Schema migrations must run across all databases. Does not scale well beyond a few hundred tenants.

Best for: Regulated industries (healthcare, finance), enterprise customers with strict data sovereignty requirements.

2. Schema-per-Tenant

All tenants share a database server, but each gets a separate schema (or namespace). Moderate isolation with lower overhead than separate databases.

Pros: Good isolation without connection management complexity. PostgreSQL handles this natively with schemas.

Cons: Not supported equally across all databases. MySQL schema separation is effectively database separation. Migrations still need to run per schema.

Best for: Mid-market SaaS with tens to low hundreds of tenants.

3. Shared Database with Row-Level Isolation

All tenants share the same tables. Every row includes a tenant_id column. Isolation is enforced at the application and query level.

Pros: Simplest to operate. Migrations run once. Scales to thousands of tenants. Lowest infrastructure cost.

Cons: One missed WHERE clause leaks data. Requires rigorous enforcement at every layer.

Best for: High-volume SaaS, platforms with many small tenants, startups optimising for speed.

For most SaaS applications, shared database with row-level isolation is the right starting point. It is also the hardest to get right, because isolation depends entirely on your code never forgetting to filter by tenant. The rest of this article focuses on making that bulletproof.

Tenant Context Management

The foundation of row-level tenancy is a reliable tenant context — a mechanism that ensures every database query automatically includes the correct tenant filter.

Laravel: Global Scopes and Middleware

namespace App\Http\Middleware;

use App\Services\TenantContext;
use Closure;
use Illuminate\Http\Request;

class ResolveTenant
{
    public function __construct(private TenantContext $tenantContext) {}

    public function handle(Request $request, Closure $next)
    {
        $tenantId = $this->resolveTenantId($request);

        if (!$tenantId) {
            return response()->json(['error' => 'Tenant could not be resolved.'], 403);
        }

        $this->tenantContext->set($tenantId);

        $response = $next($request);

        $this->tenantContext->clear();

        return $response;
    }

    private function resolveTenantId(Request $request): ?string
    {
        if ($request->hasHeader('X-Tenant-ID')) {
            return $this->validateTenantAccess(
                $request->user(),
                $request->header('X-Tenant-ID')
            );
        }

        $host = $request->getHost();
        $subdomain = explode('.', $host)[0] ?? null;

        if ($subdomain) {
            return $this->resolveTenantBySubdomain($subdomain);
        }

        return $request->user()?->default_tenant_id;
    }

    private function validateTenantAccess($user, string $tenantId): ?string
    {
        if (!$user) return null;

        $hasAccess = $user->tenants()->where('tenant_id', $tenantId)->exists();
        return $hasAccess ? $tenantId : null;
    }

    private function resolveTenantBySubdomain(string $subdomain): ?string
    {
        return cache()->remember(
            "tenant:subdomain:{$subdomain}",
            now()->addHours(1),
            fn () => \App\Models\Tenant::where('subdomain', $subdomain)->value('id')
        );
    }
}

namespace App\Services;

class TenantContext
{
    private ?string $tenantId = null;

    public function set(string $tenantId): void
    {
        $this->tenantId = $tenantId;
    }

    public function get(): string
    {
        if ($this->tenantId === null) {
            throw new \RuntimeException('Tenant context not set. This is a critical isolation failure.');
        }

        return $this->tenantId;
    }

    public function clear(): void
    {
        $this->tenantId = null;
    }

    public function isSet(): bool
    {
        return $this->tenantId !== null;
    }
}

Automatic Query Scoping with Eloquent

The most dangerous aspect of row-level tenancy is forgetting to add WHERE tenant_id = ? to a query. Eloquent global scopes eliminate this risk:

namespace App\Models\Traits;

use App\Models\Scopes\TenantScope;
use App\Services\TenantContext;

trait BelongsToTenant
{
    protected static function bootBelongsToTenant(): void
    {
        static::addGlobalScope(new TenantScope());

        static::creating(function ($model) {
            if (!$model->tenant_id) {
                $model->tenant_id = app(TenantContext::class)->get();
            }
        });
    }
}

namespace App\Models\Scopes;

use App\Services\TenantContext;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Scope;

class TenantScope implements Scope
{
    public function apply(Builder $builder, Model $model): void
    {
        $tenantContext = app(TenantContext::class);

        if ($tenantContext->isSet()) {
            $builder->where($model->getTable() . '.tenant_id', $tenantContext->get());
        }
    }
}

Now every model that uses the BelongsToTenant trait automatically filters by tenant:

class Invoice extends Model
{
    use BelongsToTenant;

    protected $fillable = ['tenant_id', 'number', 'amount', 'status'];
}

// These queries automatically include WHERE tenant_id = ?
$invoices = Invoice::where('status', 'pending')->get();
$invoice = Invoice::findOrFail($id);
$count = Invoice::count();

Node.js: Middleware and Query Builder Integration

import { Request, Response, NextFunction } from 'express';
import { AsyncLocalStorage } from 'async_hooks';

interface TenantContextData {
  tenantId: string;
}

export const tenantStorage = new AsyncLocalStorage<TenantContextData>();

export function getTenantId(): string {
  const context = tenantStorage.getStore();
  if (!context?.tenantId) {
    throw new Error('Tenant context not set. This is a critical isolation failure.');
  }
  return context.tenantId;
}

export function tenantMiddleware(
  req: Request,
  res: Response,
  next: NextFunction
): void {
  const tenantId = resolveTenantId(req);

  if (!tenantId) {
    res.status(403).json({ error: 'Tenant could not be resolved.' });
    return;
  }

  tenantStorage.run({ tenantId }, () => next());
}

function resolveTenantId(req: Request): string | null {
  const headerTenant = req.headers['x-tenant-id'] as string;
  if (headerTenant) return headerTenant;

  const host = req.hostname;
  const subdomain = host.split('.')[0];
  if (subdomain) return subdomain;

  return (req as any).user?.defaultTenantId ?? null;
}

Prisma Extension for Automatic Tenant Filtering

import { PrismaClient, Prisma } from '@prisma/client';

function createTenantPrisma() {
  const prisma = new PrismaClient();

  return prisma.$extends({
    query: {
      $allOperations({ model, operation, args, query }) {
        const tenantModels = ['Invoice', 'Payment', 'Project', 'Document'];

        if (!model || !tenantModels.includes(model)) {
          return query(args);
        }

        const tenantId = getTenantId();

        if (['findMany', 'findFirst', 'findUnique', 'count', 'aggregate'].includes(operation)) {
          args.where = { ...args.where, tenantId };
        }

        if (['create', 'createMany'].includes(operation)) {
          if (operation === 'create') {
            args.data = { ...args.data, tenantId };
          }
        }

        if (['update', 'updateMany', 'delete', 'deleteMany'].includes(operation)) {
          args.where = { ...args.where, tenantId };
        }

        return query(args);
      },
    },
  });
}

export const prisma = createTenantPrisma();

Row-Level Security in PostgreSQL

For an additional layer of defence, PostgreSQL's Row-Level Security (RLS) enforces tenant isolation at the database level — even if the application code has a bug:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

Set the tenant context at the beginning of each database connection:

Laravel with RLS

class SetTenantOnConnection
{
    public function handle(Request $request, Closure $next)
    {
        $tenantId = app(TenantContext::class)->get();

        DB::statement("SET app.current_tenant_id = '{$tenantId}'");

        return $next($request);
    }
}

Node.js with RLS

async function withTenantRLS<T>(
  callback: (tx: PrismaClient) => Promise<T>
): Promise<T> {
  const tenantId = getTenantId();

  return prisma.$transaction(async (tx) => {
    await tx.$executeRawUnsafe(
      `SET LOCAL app.current_tenant_id = '${tenantId}'`
    );
    return callback(tx as any);
  });
}

RLS acts as a safety net. Even if a developer writes a raw query that omits the tenant filter, PostgreSQL rejects rows that do not belong to the current tenant.

Testing for Tenant Leakage

The most critical test you can write for a multi-tenant system verifies that Tenant A cannot access Tenant B's data:

Laravel Test

class TenantIsolationTest extends TestCase
{
    public function test_tenant_cannot_access_other_tenant_data(): void
    {
        $tenantA = Tenant::factory()->create();
        $tenantB = Tenant::factory()->create();

        $invoiceA = Invoice::factory()->create(['tenant_id' => $tenantA->id]);
        $invoiceB = Invoice::factory()->create(['tenant_id' => $tenantB->id]);

        app(TenantContext::class)->set($tenantA->id);

        $visibleInvoices = Invoice::all();

        $this->assertTrue($visibleInvoices->contains($invoiceA));
        $this->assertFalse($visibleInvoices->contains($invoiceB));
    }

    public function test_tenant_cannot_access_other_tenant_by_id(): void
    {
        $tenantA = Tenant::factory()->create();
        $tenantB = Tenant::factory()->create();

        $invoiceB = Invoice::factory()->create(['tenant_id' => $tenantB->id]);

        app(TenantContext::class)->set($tenantA->id);

        $this->expectException(ModelNotFoundException::class);
        Invoice::findOrFail($invoiceB->id);
    }

    public function test_queries_without_tenant_context_throw(): void
    {
        $this->expectException(\RuntimeException::class);
        Invoice::all();
    }
}

Node.js Test

describe('Tenant Isolation', () => {
  it('should not return data from another tenant', async () => {
    const tenantA = await createTenant();
    const tenantB = await createTenant();

    await createInvoice({ tenantId: tenantA.id, amount: 100 });
    await createInvoice({ tenantId: tenantB.id, amount: 200 });

    const invoices = await tenantStorage.run(
      { tenantId: tenantA.id },
      () => prisma.invoice.findMany()
    );

    expect(invoices).toHaveLength(1);
    expect(invoices[0].tenantId).toBe(tenantA.id);
  });

  it('should throw when tenant context is missing', async () => {
    await expect(prisma.invoice.findMany()).rejects.toThrow(
      'Tenant context not set'
    );
  });
});

Run these tests on every deployment. A failing tenant isolation test should block the release.

Common Tenant Leakage Vectors

Even with global scopes and RLS, leakage can happen through:

Raw queries: Any DB::select() or prisma.$queryRaw() bypasses ORM scoping. Audit every raw query for tenant filtering.
Background jobs: Queue workers do not have HTTP request context. Tenant ID must be serialised into the job payload and restored before execution.
Cache keys: If cache keys do not include the tenant ID, one tenant's cached data can be served to another.
File storage paths: Uploaded files must be stored under tenant-specific prefixes. A flat storage structure risks cross-tenant file access.
Admin endpoints: Internal tools that query across tenants must be explicitly authorised and logged.

Laravel: Tenant-Aware Job Example

class ProcessInvoice implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    public function __construct(
        private string $tenantId,
        private int $invoiceId,
    ) {}

    public function handle(TenantContext $tenantContext): void
    {
        $tenantContext->set($this->tenantId);

        $invoice = Invoice::findOrFail($this->invoiceId);
        // Process invoice within correct tenant context
    }
}

Production Results

After implementing comprehensive tenant isolation across multi-tenant platforms:

Metric	Before	After
Tenant data leakage incidents	2–3 per year	0
Security audit findings (critical)	4	0
Time to onboard new tenant	2 days	15 minutes
Cross-tenant query bugs caught in CI	N/A	12 (prevented from reaching production)
Customer trust incidents	1 per year	0

Key Takeaways

Start with shared database + row-level isolation. It scales to thousands of tenants and has the lowest operational overhead. Move to database-per-tenant only when regulation demands it.
Never rely on developers remembering to filter. Use global scopes (Laravel) or Prisma extensions (Node.js) to make tenant filtering automatic and invisible.
Add PostgreSQL RLS as a safety net. Application-level filtering is necessary. Database-level filtering is insurance against application bugs.
Test for leakage explicitly. Write tests that create data in Tenant A, set context to Tenant B, and verify the data is invisible. Run these tests on every deployment.
Audit raw queries, background jobs, cache keys, and file paths. These are the most common vectors for accidental cross-tenant data exposure.
Serialise tenant context into background jobs. Queue workers do not inherit HTTP request context. The tenant ID must travel with the job.

Conclusion

Multi-tenant security is not a feature you add — it is a design constraint that shapes your entire architecture. The patterns in this article are not theoretical. They come from building platforms where a tenant leakage incident would mean regulatory consequences and broken customer relationships.

Whether you build in Laravel or Node.js, the principles are the same: automatic query scoping, database-level enforcement, explicit testing, and paranoid attention to the edge cases where isolation breaks down. Get this right, and your multi-tenant platform can scale with confidence.

Security by Design in Healthcare Data Platforms

Olamilekan Lamidi — Wed, 29 Apr 2026 10:15:46 +0000

Healthcare data is among the most sensitive information a system can hold. Patient records, diagnostic results, treatment histories, and genomic data are not just private — they are regulated by laws that carry serious penalties for mishandling. HIPAA in the United States, GDPR in Europe, and Nigeria's NDPR all impose strict requirements on how healthcare data must be collected, stored, processed, and shared.

Security in healthcare is not something you add after the product works. It must be woven into every layer of the system from the first line of code. I have built health-tech platforms that handle sensitive patient data collection and clinical workflows, and the lessons from that work apply broadly to any system where data protection is not optional.

This article covers the architecture patterns, implementation strategies, and operational practices for building healthcare data platforms that are secure by design — with examples in both Laravel and Node.js.

Why Healthcare Systems Are Different

Most web applications can tolerate a brief security lapse and recover. Healthcare systems cannot. The consequences of a breach are uniquely severe:

Patient harm: Leaked medical records can affect employment, insurance, and personal relationships. Unlike a leaked password, a disclosed medical condition cannot be reset.
Regulatory penalties: HIPAA violations can result in fines up to $1.5 million per violation category per year. GDPR fines can reach 4% of global annual revenue.
Operational shutdown: A breach in a healthcare system may require shutting down access to patient data while the investigation proceeds — directly affecting care delivery.
Trust destruction: Patients who learn their data was compromised may refuse to use digital health tools, undermining the entire digital health ecosystem.

These consequences demand a security model that assumes breaches will be attempted and builds defences at every layer.

The Security Architecture

Healthcare data security operates on multiple layers:

[Client Layer]
  - End-to-end encryption for data in transit
  - Session management with short TTLs
  - Input validation and sanitisation

[Application Layer]
  - Authentication (MFA required)
  - Role-Based Access Control (RBAC)
  - Audit logging of every data access
  - Field-level encryption for sensitive data

[Data Layer]
  - Encryption at rest (AES-256)
  - Database access controls
  - Backup encryption
  - Data retention and purging policies

[Infrastructure Layer]
  - Network segmentation
  - Intrusion detection
  - Vulnerability scanning
  - Access logging and monitoring

Authentication and Access Control

Healthcare systems require strong authentication and granular access control. Not every user should see every patient's data, and every access must be logged.

Laravel: Role-Based Access Control

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Permission extends Model
{
    protected $fillable = ['name', 'resource', 'action', 'conditions'];

    protected $casts = ['conditions' => 'array'];
}

class Role extends Model
{
    protected $fillable = ['name', 'description', 'level'];

    public function permissions()
    {
        return $this->belongsToMany(Permission::class);
    }
}

namespace App\Services;

use App\Models\User;
use Illuminate\Support\Facades\Cache;

class AccessControlService
{
    public function canAccess(User $user, string $resource, string $action, array $context = []): bool
    {
        $permissions = $this->getUserPermissions($user);

        foreach ($permissions as $permission) {
            if ($permission->resource !== $resource) continue;
            if ($permission->action !== $action && $permission->action !== '*') continue;

            if ($permission->conditions) {
                if (!$this->evaluateConditions($permission->conditions, $context, $user)) {
                    continue;
                }
            }

            $this->logAccess($user, $resource, $action, $context, true);
            return true;
        }

        $this->logAccess($user, $resource, $action, $context, false);
        return false;
    }

    private function evaluateConditions(array $conditions, array $context, User $user): bool
    {
        foreach ($conditions as $condition) {
            switch ($condition['type']) {
                case 'own_patients_only':
                    if (($context['patient_id'] ?? null) &&
                        !$user->patients()->where('id', $context['patient_id'])->exists()) {
                        return false;
                    }
                    break;

                case 'department_match':
                    if (($context['department_id'] ?? null) !== $user->department_id) {
                        return false;
                    }
                    break;

                case 'time_restricted':
                    $now = now();
                    if ($now->hour < $condition['start_hour'] || $now->hour > $condition['end_hour']) {
                        return false;
                    }
                    break;
            }
        }

        return true;
    }

    private function getUserPermissions(User $user)
    {
        return Cache::remember(
            "user_permissions:{$user->id}",
            now()->addMinutes(15),
            fn () => $user->roles()->with('permissions')->get()->pluck('permissions')->flatten()
        );
    }

    private function logAccess(User $user, string $resource, string $action, array $context, bool $granted): void
    {
        \App\Models\AccessLog::create([
            'user_id' => $user->id,
            'resource' => $resource,
            'action' => $action,
            'context' => $context,
            'granted' => $granted,
            'ip_address' => request()->ip(),
            'user_agent' => request()->userAgent(),
            'accessed_at' => now(),
        ]);
    }
}

Node.js: Middleware-Based Access Control

import { Request, Response, NextFunction } from 'express';

interface AccessRule {
  resource: string;
  action: string;
  conditions?: Array<{
    type: string;
    [key: string]: unknown;
  }>;
}

export function requireAccess(resource: string, action: string) {
  return async (req: Request, res: Response, next: NextFunction) => {
    const user = (req as any).user;

    if (!user) {
      return res.status(401).json({ error: 'Authentication required' });
    }

    const context = {
      patient_id: req.params.patientId,
      department_id: req.params.departmentId,
      ip_address: req.ip,
    };

    const granted = await checkAccess(user, resource, action, context);

    await logAccess({
      userId: user.id,
      resource,
      action,
      context,
      granted,
      ipAddress: req.ip!,
      userAgent: req.headers['user-agent'] || '',
      accessedAt: new Date(),
    });

    if (!granted) {
      return res.status(403).json({
        error: 'Access denied',
        resource,
        action,
      });
    }

    next();
  };
}

async function checkAccess(
  user: any,
  resource: string,
  action: string,
  context: Record<string, unknown>
): Promise<boolean> {
  const permissions = await getUserPermissions(user.id);

  return permissions.some((perm: AccessRule) => {
    if (perm.resource !== resource) return false;
    if (perm.action !== action && perm.action !== '*') return false;

    if (perm.conditions) {
      return perm.conditions.every((condition) =>
        evaluateCondition(condition, context, user)
      );
    }

    return true;
  });
}

function evaluateCondition(
  condition: { type: string; [key: string]: unknown },
  context: Record<string, unknown>,
  user: any
): boolean {
  switch (condition.type) {
    case 'own_patients_only':
      return user.patientIds?.includes(context.patient_id) ?? false;

    case 'department_match':
      return user.departmentId === context.department_id;

    default:
      return false;
  }
}

Apply to routes:

router.get(
  '/patients/:patientId/records',
  requireAccess('patient_records', 'read'),
  patientRecordController.list
);

router.post(
  '/patients/:patientId/records',
  requireAccess('patient_records', 'write'),
  patientRecordController.create
);

router.get(
  '/patients/:patientId/records/:recordId',
  requireAccess('patient_records', 'read'),
  patientRecordController.show
);

Field-Level Encryption

Not all data in a patient record is equally sensitive. Name and date of birth require protection, but appointment times may not. Field-level encryption encrypts specific fields within a record rather than encrypting the entire database:

Laravel: Encrypted Attributes

namespace App\Models\Traits;

use Illuminate\Support\Facades\Crypt;

trait EncryptsHealthData
{
    protected static array $encryptedFields = [];

    public function getAttribute($key)
    {
        $value = parent::getAttribute($key);

        if (in_array($key, static::$encryptedFields) && $value !== null) {
            try {
                return Crypt::decryptString($value);
            } catch (\Exception) {
                return $value;
            }
        }

        return $value;
    }

    public function setAttribute($key, $value)
    {
        if (in_array($key, static::$encryptedFields) && $value !== null) {
            $value = Crypt::encryptString($value);
        }

        return parent::setAttribute($key, $value);
    }

    public static function searchEncrypted(string $field, string $value): ?static
    {
        if (!in_array($field, static::$encryptedFields)) {
            return static::where($field, $value)->first();
        }

        $blindIndex = hash('sha256', strtolower(trim($value)) . config('app.encryption_salt'));

        return static::where("{$field}_index", $blindIndex)->first();
    }
}

class PatientRecord extends Model
{
    use EncryptsHealthData;

    protected static array $encryptedFields = [
        'diagnosis',
        'treatment_notes',
        'medication_details',
        'lab_results',
        'genetic_data',
    ];

    protected $fillable = [
        'patient_id', 'record_type', 'diagnosis', 'diagnosis_index',
        'treatment_notes', 'medication_details', 'lab_results',
        'genetic_data', 'provider_id', 'facility_id', 'recorded_at',
    ];
}

Node.js: Encryption Service

import crypto from 'crypto';

const ALGORITHM = 'aes-256-gcm';
const KEY = Buffer.from(process.env.ENCRYPTION_KEY!, 'hex');

export function encryptField(plaintext: string): string {
  const iv = crypto.randomBytes(16);
  const cipher = crypto.createCipheriv(ALGORITHM, KEY, iv);

  let encrypted = cipher.update(plaintext, 'utf8', 'hex');
  encrypted += cipher.final('hex');
  const authTag = cipher.getAuthTag().toString('hex');

  return `${iv.toString('hex')}:${authTag}:${encrypted}`;
}

export function decryptField(ciphertext: string): string {
  const [ivHex, authTagHex, encrypted] = ciphertext.split(':');

  const iv = Buffer.from(ivHex, 'hex');
  const authTag = Buffer.from(authTagHex, 'hex');
  const decipher = crypto.createDecipheriv(ALGORITHM, KEY, iv);
  decipher.setAuthTag(authTag);

  let decrypted = decipher.update(encrypted, 'hex', 'utf8');
  decrypted += decipher.final('utf8');

  return decrypted;
}

export function createBlindIndex(value: string): string {
  return crypto
    .createHmac('sha256', process.env.INDEX_KEY!)
    .update(value.toLowerCase().trim())
    .digest('hex');
}

Prisma Middleware for Automatic Encryption

import { PrismaClient } from '@prisma/client';

const ENCRYPTED_FIELDS: Record<string, string[]> = {
  PatientRecord: ['diagnosis', 'treatmentNotes', 'medicationDetails', 'labResults'],
};

const prisma = new PrismaClient();

prisma.$use(async (params, next) => {
  const model = params.model;
  if (!model || !ENCRYPTED_FIELDS[model]) return next(params);

  const fields = ENCRYPTED_FIELDS[model];

  if (['create', 'update', 'upsert'].includes(params.action)) {
    const data = params.args.data || params.args.create;
    if (data) {
      for (const field of fields) {
        if (data[field] !== undefined && data[field] !== null) {
          data[field] = encryptField(data[field]);
        }
      }
    }
  }

  const result = await next(params);

  if (result && ['findFirst', 'findUnique', 'findMany'].includes(params.action)) {
    const records = Array.isArray(result) ? result : [result];
    for (const record of records) {
      for (const field of fields) {
        if (record[field]) {
          try {
            record[field] = decryptField(record[field]);
          } catch {
            // Field may not be encrypted (legacy data)
          }
        }
      }
    }
  }

  return result;
});

Comprehensive Audit Logging

Every access to patient data must be logged. This is not optional — it is a regulatory requirement. The audit log answers: who accessed what data, when, from where, and why.

Laravel Audit Middleware

class AuditPatientAccess
{
    public function handle(Request $request, Closure $next): Response
    {
        $response = $next($request);

        $patientId = $request->route('patientId') ?? $request->input('patient_id');

        if ($patientId) {
            AuditLog::create([
                'user_id' => $request->user()->id,
                'user_role' => $request->user()->primaryRole()->name,
                'action' => $request->method() . ' ' . $request->path(),
                'resource_type' => 'patient_data',
                'resource_id' => $patientId,
                'ip_address' => $request->ip(),
                'user_agent' => $request->userAgent(),
                'response_code' => $response->getStatusCode(),
                'request_params' => $this->sanitiseParams($request->all()),
                'session_id' => $request->session()->getId(),
                'accessed_at' => now(),
            ]);
        }

        return $response;
    }

    private function sanitiseParams(array $params): array
    {
        $sensitive = ['password', 'ssn', 'credit_card', 'diagnosis'];
        return collect($params)
            ->map(fn ($value, $key) => in_array($key, $sensitive) ? '[REDACTED]' : $value)
            ->all();
    }
}

Data Retention and Purging

Healthcare regulations specify minimum and maximum retention periods. Data must be available for the required period and securely destroyed afterward:

class DataRetentionService
{
    private const RETENTION_POLICIES = [
        'patient_records' => ['min_years' => 7, 'max_years' => 10],
        'audit_logs' => ['min_years' => 6, 'max_years' => 7],
        'session_data' => ['min_years' => 0, 'max_years' => 1],
        'consent_records' => ['min_years' => 10, 'max_years' => 15],
    ];

    public function enforceRetention(): array
    {
        $results = [];

        foreach (self::RETENTION_POLICIES as $dataType => $policy) {
            $cutoff = now()->subYears($policy['max_years']);

            $count = $this->getModel($dataType)
                ->where('created_at', '<', $cutoff)
                ->count();

            if ($count > 0) {
                $this->archiveAndPurge($dataType, $cutoff);
                $results[$dataType] = ['purged' => $count, 'cutoff' => $cutoff->toDateString()];
            }
        }

        return $results;
    }

    private function archiveAndPurge(string $dataType, $cutoff): void
    {
        $model = $this->getModel($dataType);
        $records = $model->where('created_at', '<', $cutoff)->cursor();

        $archivePath = sprintf(
            'retention-archive/%s/%s',
            $dataType,
            now()->format('Y-m-d')
        );

        foreach ($records->chunk(1000) as $chunk) {
            Storage::disk('s3')->put(
                "{$archivePath}/batch-" . uniqid() . '.json.enc',
                Crypt::encryptString($chunk->toJson())
            );
        }

        $model->where('created_at', '<', $cutoff)->delete();

        AuditLog::create([
            'action' => 'data_retention_purge',
            'resource_type' => $dataType,
            'metadata' => ['cutoff' => $cutoff->toISOString()],
            'user_id' => 'system',
        ]);
    }
}

Consent Management

Patients must give informed consent for data collection and processing. Consent must be tracked, versioned, and revocable:

class ConsentService
{
    public function recordConsent(
        string $patientId,
        string $consentType,
        string $version,
        bool $granted,
        ?string $ipAddress = null
    ): ConsentRecord {
        return ConsentRecord::create([
            'patient_id' => $patientId,
            'consent_type' => $consentType,
            'version' => $version,
            'granted' => $granted,
            'granted_at' => $granted ? now() : null,
            'revoked_at' => !$granted ? now() : null,
            'ip_address' => $ipAddress,
            'metadata' => [
                'user_agent' => request()->userAgent(),
                'consent_form_version' => $version,
            ],
        ]);
    }

    public function hasActiveConsent(string $patientId, string $consentType): bool
    {
        $latest = ConsentRecord::where('patient_id', $patientId)
            ->where('consent_type', $consentType)
            ->latest()
            ->first();

        return $latest && $latest->granted && $latest->revoked_at === null;
    }

    public function revokeConsent(string $patientId, string $consentType): void
    {
        $latest = ConsentRecord::where('patient_id', $patientId)
            ->where('consent_type', $consentType)
            ->where('granted', true)
            ->whereNull('revoked_at')
            ->latest()
            ->first();

        if ($latest) {
            $latest->update(['revoked_at' => now()]);

            DataAnonymisationJob::dispatch($patientId, $consentType)->onQueue('high');
        }
    }
}

Production Results

After implementing security-by-design across healthcare data platforms:

Metric	Before	After
Security audit findings (critical)	7	0
Data access without audit trail	~15% of operations	0%
Unauthorised access attempts detected	Unknown	23/month (all blocked and logged)
Compliance audit preparation time	4 weeks	3 days
Data breach incidents	N/A (pre-platform)	0
Mean time to detect anomalous access	Unknown	< 15 minutes

Key Takeaways

Security is architecture, not a feature. It must be built into every layer — authentication, access control, encryption, audit logging, and data retention — from day one.
Log every data access. In healthcare, the question is not "if" an audit will happen but "when." Complete audit trails make the difference between a smooth audit and a compliance crisis.
Encrypt sensitive fields, not just the database. Encryption at rest protects against disk theft. Field-level encryption protects against application-layer breaches and insider threats.
Implement RBAC with conditions. "Doctor" is not a sufficient access level. "Doctor who is assigned to this patient, accessing during working hours, from a known IP" is closer to what healthcare systems require.
Manage consent as a first-class data model. Consent is not a checkbox — it is a versioned, auditable, revocable record that governs what data you are allowed to process.
Plan for data retention from the start. Healthcare regulations specify both minimum and maximum retention periods. Build the retention and purging infrastructure before you have millions of records.

Conclusion

Building healthcare data platforms requires a security mindset that goes beyond typical web application security. Every design decision — from database schema to API design to deployment configuration — must account for the sensitivity of the data and the regulatory requirements that govern it.

The patterns in this article are not theoretical. They come from building systems that handle real patient data under real regulatory scrutiny. Whether you build in Laravel or Node.js, the principles are universal: encrypt by default, log everything, enforce access at every layer, and design for the audit that will inevitably come.

From Monolith to Modular: A Strangler Migration Playbook

Olamilekan Lamidi — Tue, 28 Apr 2026 14:23:02 +0000

Every successful application eventually outgrows its original architecture. What started as a clean, well-structured monolith becomes a tangled web of dependencies where changing one feature risks breaking three others. Deployments become risky, test suites take forever, and developers spend more time navigating the codebase than writing new features.

The instinct is to rewrite. Do not. Rewrites are how engineering teams burn months of effort and deliver something that, at best, does what the old system already did. The better approach is incremental migration: systematically extracting capabilities from the monolith while keeping the system running in production the entire time.

I have led these migrations across multiple companies — taking legacy codebases with 15+ modules, hundreds of database tables, and years of accumulated technical debt and transforming them into modular, maintainable architectures without downtime. This article is the playbook.

Why Monoliths Become Painful

Monoliths are not inherently bad. They are fast to build, easy to deploy, and simple to reason about — at small scale. The problems emerge gradually:

Deployment coupling: Every change, no matter how small, requires deploying the entire application. A CSS fix and a payment logic change ship in the same deployment.
Dependency tangles: Module A depends on Module B which depends on Module C which depends on Module A. Circular dependencies make changes unpredictable.
Test suite slowdown: Running the full test suite takes 30+ minutes. Developers start skipping tests. Bugs escape to production.
Team bottlenecks: Multiple teams working on the same codebase create merge conflicts, code review backlogs, and release coordination overhead.
Scaling limitations: You cannot scale the payment processing component independently of the user profile component. Everything scales (or fails) together.

The Strangler Fig Pattern

The strangler fig pattern, named after the plant that gradually envelops its host tree, is the safest approach to migrating away from a monolith. The strategy:

Identify a capability to extract from the monolith
Build the new implementation alongside the old one
Route traffic to the new implementation (gradually or all at once)
Remove the old implementation once the new one is proven
Repeat for the next capability

The monolith continues to run throughout. There is no big bang cutover. Each migration step is small, reversible, and independently testable.

Phase 1: Understanding What You Have

Before you extract anything, you need a map of the current system.

Dependency Analysis

# Laravel: Find all cross-module dependencies
# Look for imports/use statements between modules

Laravel Module Boundary Analysis

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\File;

class AnalyzeModuleDependencies extends Command
{
    protected $signature = 'modules:analyze';

    public function handle(): void
    {
        $modules = $this->getModuleDirectories();
        $dependencies = [];

        foreach ($modules as $module) {
            $files = File::allFiles(app_path($module));
            $imports = [];

            foreach ($files as $file) {
                $content = $file->getContents();
                preg_match_all('/use App\\\\(\w+)\\\\/', $content, $matches);
                $imports = array_merge($imports, $matches[1]);
            }

            $externalDeps = array_unique(
                array_filter($imports, fn ($dep) => $dep !== $module)
            );

            $dependencies[$module] = $externalDeps;
        }

        foreach ($dependencies as $module => $deps) {
            $this->info("{$module} depends on: " . implode(', ', $deps));
        }

        $this->detectCircularDependencies($dependencies);
    }

    private function detectCircularDependencies(array $dependencies): void
    {
        foreach ($dependencies as $moduleA => $depsA) {
            foreach ($depsA as $moduleB) {
                if (isset($dependencies[$moduleB]) && in_array($moduleA, $dependencies[$moduleB])) {
                    $this->warn("Circular dependency: {$moduleA} <-> {$moduleB}");
                }
            }
        }
    }

    private function getModuleDirectories(): array
    {
        return collect(File::directories(app_path()))
            ->map(fn ($dir) => basename($dir))
            ->filter(fn ($dir) => !in_array($dir, ['Console', 'Exceptions', 'Http', 'Providers']))
            ->values()
            ->all();
    }
}

Node.js Dependency Mapping

import fs from 'fs';
import path from 'path';

interface ModuleDependency {
  module: string;
  dependsOn: string[];
  files: number;
  linesOfCode: number;
}

function analyzeModuleDependencies(srcDir: string): ModuleDependency[] {
  const modules = fs.readdirSync(srcDir, { withFileTypes: true })
    .filter((d) => d.isDirectory())
    .map((d) => d.name);

  return modules.map((moduleName) => {
    const moduleDir = path.join(srcDir, moduleName);
    const files = getAllFiles(moduleDir, ['.ts', '.js']);
    const imports: string[] = [];
    let totalLines = 0;

    files.forEach((file) => {
      const content = fs.readFileSync(file, 'utf-8');
      totalLines += content.split('\n').length;

      const importRegex = /from\s+['"]\.\.\/(\w+)/g;
      let match;
      while ((match = importRegex.exec(content)) !== null) {
        if (match[1] !== moduleName) {
          imports.push(match[1]);
        }
      }
    });

    return {
      module: moduleName,
      dependsOn: [...new Set(imports)],
      files: files.length,
      linesOfCode: totalLines,
    };
  });
}

function getAllFiles(dir: string, extensions: string[]): string[] {
  const results: string[] = [];
  const entries = fs.readdirSync(dir, { withFileTypes: true });

  for (const entry of entries) {
    const fullPath = path.join(dir, entry.name);
    if (entry.isDirectory()) {
      results.push(...getAllFiles(fullPath, extensions));
    } else if (extensions.some((ext) => entry.name.endsWith(ext))) {
      results.push(fullPath);
    }
  }

  return results;
}

Phase 2: Establish Module Boundaries

Before extracting services, enforce boundaries within the monolith. This is the most important step and the one most teams skip.

Laravel: Module Service Providers and Contracts

// app/Billing/Contracts/BillingService.php
namespace App\Billing\Contracts;

interface BillingService
{
    public function createInvoice(string $customerId, array $lineItems): Invoice;
    public function processPayment(string $invoiceId, array $paymentMethod): PaymentResult;
    public function getCustomerBalance(string $customerId): Money;
}

// app/Billing/BillingServiceProvider.php
namespace App\Billing;

use Illuminate\Support\ServiceProvider;

class BillingServiceProvider extends ServiceProvider
{
    public function register(): void
    {
        $this->app->bind(
            Contracts\BillingService::class,
            Services\BillingServiceImpl::class
        );
    }
}

// app/Orders/Services/OrderService.php
namespace App\Orders\Services;

use App\Billing\Contracts\BillingService;

class OrderService
{
    public function __construct(private BillingService $billing) {}

    public function completeOrder(Order $order): void
    {
        $invoice = $this->billing->createInvoice(
            $order->customer_id,
            $order->lineItems->toArray()
        );

        $order->update(['invoice_id' => $invoice->id, 'status' => 'invoiced']);
    }
}

Node.js: Module Interfaces

// src/billing/contracts.ts
export interface BillingService {
  createInvoice(customerId: string, lineItems: LineItem[]): Promise<Invoice>;
  processPayment(invoiceId: string, method: PaymentMethod): Promise<PaymentResult>;
  getCustomerBalance(customerId: string): Promise<Money>;
}

// src/billing/index.ts
export { BillingServiceImpl as BillingService } from './services/billing-service';
export type { BillingService as BillingServiceInterface } from './contracts';

// src/orders/services/order-service.ts
import type { BillingService } from '../../billing/contracts';

export class OrderService {
  constructor(private billing: BillingService) {}

  async completeOrder(order: Order): Promise<void> {
    const invoice = await this.billing.createInvoice(
      order.customerId,
      order.lineItems
    );

    await this.orderRepo.update(order.id, {
      invoiceId: invoice.id,
      status: 'invoiced',
    });
  }
}

The key insight: modules communicate through interfaces, not direct implementation references. When you later extract the billing module into its own service, you replace the implementation behind the interface. The calling code does not change.

Phase 3: Extract the First Service

Choose a module that is:

Well-bounded (few incoming dependencies)
Independently valuable
Not on the critical path (if the extraction has problems, it should not take down the whole system)

The Routing Layer

Use a proxy or feature flag to route traffic between the monolith and the new service:

Laravel: Switchable Implementation

// app/Billing/BillingServiceProvider.php
class BillingServiceProvider extends ServiceProvider
{
    public function register(): void
    {
        $this->app->bind(Contracts\BillingService::class, function ($app) {
            $useNewService = config('features.billing_service_v2', false);

            if ($useNewService) {
                return new HttpBillingServiceClient(
                    config('services.billing.url'),
                    config('services.billing.api_key'),
                );
            }

            return new Services\BillingServiceImpl();
        });
    }
}

// The HTTP client talks to the extracted billing service
class HttpBillingServiceClient implements Contracts\BillingService
{
    public function __construct(
        private string $baseUrl,
        private string $apiKey,
    ) {}

    public function createInvoice(string $customerId, array $lineItems): Invoice
    {
        $response = Http::withToken($this->apiKey)
            ->timeout(10)
            ->retry(3, 100)
            ->post("{$this->baseUrl}/api/invoices", [
                'customer_id' => $customerId,
                'line_items' => $lineItems,
            ]);

        if ($response->failed()) {
            throw new BillingServiceException("Failed to create invoice: " . $response->body());
        }

        return Invoice::fromArray($response->json());
    }
}

Node.js: Strategy Pattern Switch

import type { BillingService } from './contracts';
import { LocalBillingService } from './services/billing-service';
import { RemoteBillingService } from './services/remote-billing-service';

export function createBillingService(): BillingService {
  if (process.env.BILLING_SERVICE_V2 === 'true') {
    return new RemoteBillingService(
      process.env.BILLING_SERVICE_URL!,
      process.env.BILLING_SERVICE_API_KEY!
    );
  }

  return new LocalBillingService();
}

class RemoteBillingService implements BillingService {
  constructor(
    private baseUrl: string,
    private apiKey: string,
  ) {}

  async createInvoice(customerId: string, lineItems: LineItem[]): Promise<Invoice> {
    const response = await fetch(`${this.baseUrl}/api/invoices`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        'Authorization': `Bearer ${this.apiKey}`,
      },
      body: JSON.stringify({ customer_id: customerId, line_items: lineItems }),
    });

    if (!response.ok) {
      throw new Error(`Billing service error: ${response.status}`);
    }

    return response.json();
  }
}

Phase 4: Data Migration Strategy

The hardest part of service extraction is data ownership. When the billing module becomes its own service, who owns the billing tables?

Shared Database Phase

Initially, the new service reads from the same database as the monolith. This avoids data migration complexity during the initial extraction.

Data Sync Phase

Introduce event-based data synchronisation. The new service maintains its own datastore, kept in sync via events:

class BillingDataSyncListener
{
    public function handle(CustomerUpdated $event): void
    {
        Http::post(config('services.billing.url') . '/api/sync/customer', [
            'customer_id' => $event->customerId,
            'name' => $event->name,
            'email' => $event->email,
        ]);
    }
}

Full Ownership Phase

The new service owns its data completely. The monolith no longer writes to billing tables. All access goes through the service API.

Phase 5: Zero-Downtime Cutover

The cutover from monolith to new service should be invisible to users:

Shadow mode: Both implementations run. The new service processes requests but responses come from the monolith. Compare outputs for correctness.
Canary rollout: Route 5% of traffic to the new service. Monitor error rates, latency, and correctness. Gradually increase.
Full rollout: Route 100% to the new service. Keep the monolith implementation for 2 weeks as a rollback option.
Cleanup: Remove the old implementation from the monolith.

class BillingServiceProvider extends ServiceProvider
{
    public function register(): void
    {
        $this->app->bind(Contracts\BillingService::class, function () {
            $strategy = config('features.billing_migration_phase');

            return match ($strategy) {
                'monolith' => new Services\BillingServiceImpl(),
                'shadow' => new ShadowBillingService(
                    primary: new Services\BillingServiceImpl(),
                    shadow: new HttpBillingServiceClient(/*...*/),
                ),
                'canary' => new CanaryBillingService(
                    legacy: new Services\BillingServiceImpl(),
                    modern: new HttpBillingServiceClient(/*...*/),
                    percentage: (int) config('features.billing_canary_percentage', 5),
                ),
                'modern' => new HttpBillingServiceClient(/*...*/),
                default => new Services\BillingServiceImpl(),
            };
        });
    }
}

Measuring Migration Success

Track these metrics throughout the migration:

Metric	Purpose
Deployment frequency	Should increase as modules become independent
Lead time for changes	Should decrease as codebase complexity drops
Test suite runtime	Should decrease per module
Incidents per deployment	Should decrease with smaller change sets
Mean time to recovery	Should decrease with independent rollback capability

Production Results from Past Migrations

Metric	Before (Monolith)	After (Modular)
Deployment frequency	1–2 per week	3–5 per day
Full test suite runtime	35 minutes	8 minutes (per module)
Average deploy risk score	High	Low (per module)
Time to onboard new developer	3 weeks	1 week
Query execution time	1.8s average	180ms average (60% reduction)
System performance	Baseline	40% improvement

Key Takeaways

Never rewrite. Always migrate incrementally. The strangler fig pattern lets you replace the monolith piece by piece while keeping the system running.
Establish boundaries before extracting. Enforce module interfaces within the monolith first. This makes extraction a configuration change, not a rewrite.
Start with a low-risk module. Pick something well-bounded and non-critical for your first extraction. Build confidence before tackling core business logic.
Use feature flags for cutover. Shadow mode, canary rollouts, and instant rollback make the migration safe. Never do a big bang switchover.
Solve data ownership explicitly. Shared database → event-based sync → full ownership is the safest data migration path.
Measure continuously. Deployment frequency, test runtime, and incident rate tell you whether the migration is actually improving things.

Conclusion

Migrating from a monolith to a modular architecture is not a technical project — it is an engineering discipline applied over weeks and months. The strangler fig pattern makes it safe. Interface-first module design makes it clean. Feature flags make it reversible. And continuous measurement makes it accountable.

Whether you are working in Laravel or Node.js, the approach is the same: understand what you have, enforce boundaries, extract behind interfaces, migrate data carefully, and cut over gradually. The monolith did not become painful overnight, and the fix will not happen overnight either. But each step makes the system better, and the cumulative impact is transformative.

Why I Mentor Junior Developers: Growing the Next Generation of Tech Talent

Olamilekan Lamidi — Sun, 19 Apr 2026 21:20:52 +0000

The Mentoring Gap in Tech

The technology industry has a well-documented pipeline problem. We talk endlessly about hiring, about talent shortages, about the difficulty of finding "senior" engineers. What we talk about far less is the systematic failure to develop the talent we already have.

This gap is especially acute in emerging markets. In Lagos, where I began my career, the demand for experienced software engineers far outstrips supply. Boot camps and university programmes produce developers who can write code, but the journey from writing code to building production systems — understanding architecture, debugging complex distributed systems, making sound technical trade-offs under pressure — that journey requires guided experience. It requires mentorship.

The statistics bear this out. According to a 2023 Stack Overflow Developer Survey, over 60% of developers with fewer than five years of experience report that they lack access to meaningful technical mentorship at work. In African tech ecosystems, where the average engineering team is smaller and companies often cannot afford dedicated developer experience roles, the problem is even more pronounced.

I have seen the consequences firsthand. Junior developers who are talented but directionless, writing code that works but does not scale. Mid-level engineers who have plateaued because nobody ever showed them how to think about system design. Brilliant minds leaving the industry entirely because they felt unsupported and overwhelmed.

This is not a talent problem. It is a mentorship problem. And it is one that senior engineers — those of us who have accumulated the scars and the knowledge — have both the ability and the responsibility to solve.

My Approach to Mentoring

Over the years, I have developed a mentoring approach that goes beyond answering questions when asked. It is proactive, structured, and woven into the daily fabric of engineering work. I learned early that the most effective mentoring does not happen in scheduled one-on-ones (though those matter too). It happens in the moments where engineering decisions are being made in real time.

Code Reviews as Teaching Moments

I treat every code review as a teaching opportunity. When I review a junior developer's pull request, I do not simply approve or request changes. I explain the reasoning behind my suggestions. If I ask someone to refactor a database query, I explain what the current query does to performance at scale. If I suggest a different design pattern, I walk through the trade-offs of both approaches.

At Lordwin Group, where I led a team of five backend developers, I established a code review culture where every pull request received at least two reviews, and at least one review had to include an explanatory comment — not just "change this," but "here is why." The impact was immediate. Junior developers began anticipating architectural concerns in their own code before the review stage. The quality of initial submissions improved dramatically within three months.

I remember reviewing a pull request from a junior developer on the team who had written a working but brittle API endpoint for the gaming platform. Instead of rewriting it myself, I scheduled a 30-minute pair programming session. We walked through the request lifecycle together — how the payload was validated, how the database was queried, how the response was structured, and where the failure points were under load. By the end of the session, the developer had refactored the endpoint themselves, and more importantly, they understood the principles behind the refactoring. That developer went on to independently architect a feature module for our investment platform three months later.

Pair Programming as Skill Transfer

Pair programming is the most underutilised mentoring tool in software engineering. I use it deliberately, especially for complex features or unfamiliar domains. When I was at 2am Tech, collaborating with a cross-functional team of eight engineers building Addio — a financial platform serving 500+ organisations — I regularly paired with less experienced team members on critical backend work.

One engineer on the team was strong in frontend development but had limited backend experience. Rather than assigning them only frontend tasks, I paired with them on building a Node.js service that handled vendor payment workflows. Over several pairing sessions, we worked through API design, database transaction management, error handling patterns, and queue-based architecture for processing payments asynchronously. Within two months, that engineer was confidently building backend services independently and had begun contributing to our architecture discussions.

Olamilekan Lamidi does not hoard knowledge. That principle guides everything I do in a team.

Architecture Discussions as Education

I make a point of including junior and mid-level engineers in architecture discussions, even when their immediate role does not require it. At 54gene, where I worked as a full-stack developer building data collection systems for Africa's leading genomics company, we had a cross-functional team of 10+ members. I advocated for — and helped establish — weekly architecture review sessions where engineers at every level could participate.

These sessions were not presentations. They were collaborative discussions where I would propose an architecture, invite challenges, and walk through the reasoning behind decisions. Why we chose a service-oriented approach for certain modules. Why we implemented specific caching strategies for patient data forms. How we balanced data sensitivity with system performance.

The junior engineers on the team may not have been making those architectural decisions, but by witnessing and participating in the discussion, they were building the mental models they would need when they eventually did. Several of those engineers have since moved into senior roles at other companies — and I know those sessions played a part in their growth.

Structuring Mentorship Beyond Technical Skills

Technical mentoring is essential, but it is not sufficient. The engineers I mentor most effectively are the ones where I invest in three dimensions: technical skills, soft skills, and career navigation.

Technical Skills: Building Depth and Breadth

I structure technical mentoring around progressive complexity. A junior developer on my team does not jump straight from CRUD operations to distributed systems. I map out a learning path:

Foundations: Clean code, testing, version control discipline, debugging methodologies
Intermediate: API design, database optimisation, authentication patterns, error handling at scale
Advanced: System architecture, event-driven patterns, performance profiling, infrastructure as code

At Lordwin Group, I created informal "engineering growth plans" for each of the five developers I led. These were not corporate HR documents — they were living technical roadmaps. Each quarter, I sat with every team member and reviewed where they were, what they had learned, and what they needed to tackle next. One developer who started as a PHP-only backend engineer progressed to confidently working across Node.js and React within eight months through this structured approach.

Soft Skills: Communication, Estimation, and Ownership

I have watched technically brilliant engineers stall in their careers because they could not communicate their ideas, estimate their work, or take ownership of outcomes. Mentoring in these areas is harder — it requires trust and candid conversation — but the payoff is enormous.

I coach engineers on how to present technical proposals to non-technical stakeholders. How to write clear, concise pull request descriptions. How to estimate work by breaking down unknowns. How to own a failure, conduct a post-mortem, and extract lessons without blame. These are the skills that separate engineers who write code from engineers who lead teams.

Career Guidance: Navigating the Path

Especially for engineers in Lagos and across Africa, career navigation can be opaque. Which technologies should you invest in? When should you specialise versus generalise? How do you break into remote roles with international companies? How do you negotiate compensation?

I share my own career trajectory openly — from Civic MediaLab in Lagos to working with teams across Ukraine, the United States, and the United Kingdom. I discuss the decisions I made, the mistakes I made, and the skills that unlocked each transition. For engineers who aspire to work on the global stage, having someone who has walked that path is invaluable.

Impact: Developers Who Grew

Mentoring is difficult to quantify. There is no dashboard that shows "junior developer promoted to senior because Olamilekan Lamidi spent 30 minutes explaining event-driven architecture." But the evidence is there in the trajectories of the people I have worked with.

At Lordwin Group, two of the five developers I led as a senior engineer went on to secure senior engineering positions within 18 months of our time working together. One of them now leads a team of his own at a fintech company in Lagos. He told me directly that the code review culture and architecture discussions I established were the most impactful learning experiences of his early career.

At 2am Tech, the engineer I paired with on backend services went from being a frontend-only developer to a full-stack engineer within one product cycle. They credited the pairing sessions with giving them the confidence to tackle unfamiliar domains.

At 54gene, the junior engineers who participated in our architecture reviews went on to contribute meaningfully to system design decisions by the end of their tenure — a progression that typically takes much longer without structured exposure.

These are not isolated cases. They are the predictable outcome of treating mentoring as engineering work — work that deserves time, intention, and rigour.

Community Engagement: Mentoring Beyond the Workplace

My commitment to mentoring extends beyond the teams I work with directly. I have been active in the Lagos tech community, participating in developer meetups and knowledge-sharing sessions that help early-career engineers navigate the industry. I engage on platforms like ADPList, where I offer mentoring sessions focused on backend engineering, career transitions, and breaking into remote international roles.

I also contribute to the broader engineering community through technical writing — articles like this one, published on Medium, DEV.TO, and LinkedIn — that distil hard-earned lessons into accessible guidance. Every article I publish is, in a sense, a mentoring session at scale. When a junior developer in Nairobi or Accra reads about how I optimised database queries at VacancySoft or architected a real-time gaming backend at Lordwin Group, they are gaining access to production-level knowledge that they might not encounter in their current work environment.

This is not altruism. It is ecosystem building. The stronger the engineers around me, the stronger the teams I work with, the products I contribute to, and the industry as a whole.

The Business Case for Mentoring

For engineering leaders reading this, mentoring is not just a moral good — it is a strategic advantage. The data is compelling:

Faster Onboarding. Teams with structured mentoring practices onboard new engineers 40–50% faster. At Lordwin Group, new developers were contributing production code within their second week, compared to the industry average of four to six weeks. This was a direct result of the code review culture and pairing practices I established.

Better Code Quality. When junior developers understand the "why" behind architectural decisions, they write better code from the start. Our pull request rejection rate at Lordwin Group dropped by roughly 35% within four months of implementing teaching-oriented code reviews.

Reduced Turnover. Engineers who feel invested in stay. In an industry where the average tenure is under two years, creating an environment where developers are actively growing is one of the most effective retention strategies available. The Lordwin team remained remarkably stable during my tenure despite competitive market conditions.

Knowledge Distribution. In teams without mentoring culture, knowledge concentrates in one or two senior engineers. This creates catastrophic bus factor risk. By actively distributing knowledge through mentoring, I ensured that no single engineer — including myself — was a single point of failure.

A Call to Action for Senior Engineers

If you are a senior engineer reading this, I want to challenge you directly: when was the last time you deliberately taught someone something?

Not answered a question in Slack. Not pointed someone to the documentation. Actually sat down, understood where someone was struggling, and helped them build the mental model they needed to solve the problem themselves.

Olamilekan Lamidi's career was shaped by a senior engineer who chose to invest 30 minutes in a confused junior developer at Civic MediaLab in 2017. That investment compounded. It made me a better engineer, which made me a better technical lead, which made the teams I led more effective, which made the products we shipped more robust.

Every senior engineer has this power. The question is whether we choose to use it.

The technology sector does not advance through individual brilliance alone. It advances when knowledge flows freely, when experienced practitioners invest in the next generation, and when the barriers to entry are lowered not by reducing standards, but by raising support.

I mentor because I was mentored. I share because someone shared with me. And I will continue doing so because I have seen, over nine years and across six companies and four countries, that this is how you build an industry that lasts.

Designing Audit-Proof Financial Workflows with Immutable Logs

Olamilekan Lamidi — Fri, 17 Apr 2026 10:10:10 +0000

Why Mutable Records Break Under Audit

Most web applications store the current state of a record. When an invoice status changes from "pending" to "paid," the database row is updated in place. The previous state is gone.

This works fine for most applications. For financial systems, it is a liability:

No history: You cannot prove what the invoice status was yesterday at 3pm.
No attribution: You cannot tell who changed the status or why.
No reconciliation path: When numbers do not match, there is no trail to follow.
No tamper evidence: A modified record looks identical to a legitimate one.

Financial regulations — PCI DSS, SOX, FCA guidelines — require that systems maintain a complete, tamper-evident record of all financial transactions and state changes. An UPDATE statement destroys exactly the evidence you are required to preserve.

The Immutable Log Pattern

The solution is to never update or delete financial records. Instead, every change is recorded as a new, append-only event:

[Event 1] Invoice #1042 CREATED — amount: £5,000 — by: user_42 — at: 2025-03-01T09:15:00Z
[Event 2] Invoice #1042 APPROVED — approved_by: user_15 — at: 2025-03-01T14:30:00Z
[Event 3] Invoice #1042 PAYMENT_RECEIVED — amount: £5,000 — ref: PAY_8821 — at: 2025-03-05T11:00:00Z
[Event 4] Invoice #1042 RECONCILED — matched_to: BANK_TXN_4401 — at: 2025-03-06T08:45:00Z

The current state of the invoice is derived by replaying the events in order. The audit trail is the events themselves. Nothing is lost, nothing is overwritten.

Architecture Overview

[Client Request]
       |
       v
[Application Layer]
  - Validate business rules
  - Create event record
       |
       v
[DB Transaction]
  - Write event to audit_events table (append-only)
  - Update materialised view / current-state table
       |
       v
[Audit Events Table]           [Current State Table]
  (immutable, append-only)      (derived, rebuildable)
       |
       v
[Async Consumers]
  - Compliance reporting
  - Reconciliation engine
  - Notification service

The critical design decision: the audit events table is the source of truth. The current-state table is a projection that can be rebuilt from events at any time.

Implementation in Laravel

Audit Events Migration

Schema::create('audit_events', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->string('aggregate_type');
    $table->string('aggregate_id');
    $table->unsignedBigInteger('sequence_number');
    $table->string('event_type');
    $table->json('payload');
    $table->json('metadata');
    $table->string('actor_id');
    $table->string('actor_type');
    $table->string('ip_address')->nullable();
    $table->string('checksum');
    $table->timestamp('occurred_at');
    $table->timestamp('created_at');

    $table->unique(['aggregate_type', 'aggregate_id', 'sequence_number']);
    $table->index(['aggregate_type', 'aggregate_id']);
    $table->index('event_type');
    $table->index('occurred_at');
    $table->index('actor_id');
});

The checksum column stores a hash of the event data plus the previous event's checksum, forming a hash chain. Any tampering with a historical record breaks the chain.

Audit Event Service

namespace App\Services;

use App\Models\AuditEvent;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class AuditEventService
{
    public function record(
        string $aggregateType,
        string $aggregateId,
        string $eventType,
        array $payload,
        string $actorId,
        string $actorType = 'user',
        ?string $ipAddress = null,
    ): AuditEvent {
        return DB::transaction(function () use (
            $aggregateType, $aggregateId, $eventType,
            $payload, $actorId, $actorType, $ipAddress
        ) {
            $lastEvent = AuditEvent::where('aggregate_type', $aggregateType)
                ->where('aggregate_id', $aggregateId)
                ->orderByDesc('sequence_number')
                ->lockForUpdate()
                ->first();

            $sequenceNumber = $lastEvent ? $lastEvent->sequence_number + 1 : 1;
            $previousChecksum = $lastEvent?->checksum ?? 'GENESIS';

            $eventData = [
                'aggregate_type' => $aggregateType,
                'aggregate_id' => $aggregateId,
                'sequence_number' => $sequenceNumber,
                'event_type' => $eventType,
                'payload' => $payload,
                'actor_id' => $actorId,
                'occurred_at' => now(),
            ];

            $checksum = hash('sha256', $previousChecksum . json_encode($eventData));

            return AuditEvent::create([
                'id' => Str::uuid(),
                ...$eventData,
                'metadata' => [
                    'previous_checksum' => $previousChecksum,
                    'schema_version' => '1.0',
                ],
                'actor_type' => $actorType,
                'ip_address' => $ipAddress,
                'checksum' => $checksum,
                'created_at' => now(),
            ]);
        });
    }

    public function verifyChain(string $aggregateType, string $aggregateId): bool
    {
        $events = AuditEvent::where('aggregate_type', $aggregateType)
            ->where('aggregate_id', $aggregateId)
            ->orderBy('sequence_number')
            ->get();

        $previousChecksum = 'GENESIS';

        foreach ($events as $event) {
            $eventData = [
                'aggregate_type' => $event->aggregate_type,
                'aggregate_id' => $event->aggregate_id,
                'sequence_number' => $event->sequence_number,
                'event_type' => $event->event_type,
                'payload' => $event->payload,
                'actor_id' => $event->actor_id,
                'occurred_at' => $event->occurred_at,
            ];

            $expectedChecksum = hash('sha256', $previousChecksum . json_encode($eventData));

            if ($expectedChecksum !== $event->checksum) {
                return false;
            }

            $previousChecksum = $event->checksum;
        }

        return true;
    }

    public function getHistory(
        string $aggregateType,
        string $aggregateId
    ): \Illuminate\Support\Collection {
        return AuditEvent::where('aggregate_type', $aggregateType)
            ->where('aggregate_id', $aggregateId)
            ->orderBy('sequence_number')
            ->get();
    }
}

Using It in a Financial Workflow

class InvoiceService
{
    public function __construct(
        private AuditEventService $auditService,
    ) {}

    public function createInvoice(array $data, string $userId, string $ip): Invoice
    {
        return DB::transaction(function () use ($data, $userId, $ip) {
            $invoice = Invoice::create([
                'number' => $this->generateInvoiceNumber(),
                'client_id' => $data['client_id'],
                'amount' => $data['amount'],
                'currency' => $data['currency'],
                'status' => 'draft',
                'due_date' => $data['due_date'],
            ]);

            $this->auditService->record(
                aggregateType: 'invoice',
                aggregateId: (string) $invoice->id,
                eventType: 'invoice.created',
                payload: [
                    'number' => $invoice->number,
                    'amount' => $invoice->amount,
                    'currency' => $invoice->currency,
                    'client_id' => $invoice->client_id,
                ],
                actorId: $userId,
                ipAddress: $ip,
            );

            return $invoice;
        });
    }

    public function approveInvoice(Invoice $invoice, string $approverId, string $ip): Invoice
    {
        return DB::transaction(function () use ($invoice, $approverId, $ip) {
            $invoice->update(['status' => 'approved']);

            $this->auditService->record(
                aggregateType: 'invoice',
                aggregateId: (string) $invoice->id,
                eventType: 'invoice.approved',
                payload: [
                    'previous_status' => 'draft',
                    'new_status' => 'approved',
                ],
                actorId: $approverId,
                ipAddress: $ip,
            );

            return $invoice->fresh();
        });
    }
}

Implementation in Node.js

Prisma Schema

model AuditEvent {
  id              String   @id @default(uuid())
  aggregateType   String   @map("aggregate_type")
  aggregateId     String   @map("aggregate_id")
  sequenceNumber  Int      @map("sequence_number")
  eventType       String   @map("event_type")
  payload         Json
  metadata        Json
  actorId         String   @map("actor_id")
  actorType       String   @map("actor_type") @default("user")
  ipAddress       String?  @map("ip_address")
  checksum        String
  occurredAt      DateTime @map("occurred_at")
  createdAt       DateTime @default(now()) @map("created_at")

  @@unique([aggregateType, aggregateId, sequenceNumber])
  @@index([aggregateType, aggregateId])
  @@index([eventType])
  @@index([occurredAt])
  @@index([actorId])
  @@map("audit_events")
}

Audit Event Service

import { PrismaClient } from '@prisma/client';
import crypto from 'crypto';

const prisma = new PrismaClient();

interface RecordEventParams {
  aggregateType: string;
  aggregateId: string;
  eventType: string;
  payload: Record<string, unknown>;
  actorId: string;
  actorType?: string;
  ipAddress?: string;
}

export async function recordAuditEvent(params: RecordEventParams) {
  return prisma.$transaction(async (tx) => {
    const lastEvent = await tx.auditEvent.findFirst({
      where: {
        aggregateType: params.aggregateType,
        aggregateId: params.aggregateId,
      },
      orderBy: { sequenceNumber: 'desc' },
    });

    const sequenceNumber = lastEvent ? lastEvent.sequenceNumber + 1 : 1;
    const previousChecksum = lastEvent?.checksum ?? 'GENESIS';

    const eventData = {
      aggregate_type: params.aggregateType,
      aggregate_id: params.aggregateId,
      sequence_number: sequenceNumber,
      event_type: params.eventType,
      payload: params.payload,
      actor_id: params.actorId,
      occurred_at: new Date().toISOString(),
    };

    const checksum = crypto
      .createHash('sha256')
      .update(previousChecksum + JSON.stringify(eventData))
      .digest('hex');

    return tx.auditEvent.create({
      data: {
        aggregateType: params.aggregateType,
        aggregateId: params.aggregateId,
        sequenceNumber,
        eventType: params.eventType,
        payload: params.payload,
        metadata: {
          previous_checksum: previousChecksum,
          schema_version: '1.0',
        },
        actorId: params.actorId,
        actorType: params.actorType ?? 'user',
        ipAddress: params.ipAddress,
        checksum,
        occurredAt: new Date(),
      },
    });
  });
}

export async function verifyAuditChain(
  aggregateType: string,
  aggregateId: string
): Promise<boolean> {
  const events = await prisma.auditEvent.findMany({
    where: { aggregateType, aggregateId },
    orderBy: { sequenceNumber: 'asc' },
  });

  let previousChecksum = 'GENESIS';

  for (const event of events) {
    const eventData = {
      aggregate_type: event.aggregateType,
      aggregate_id: event.aggregateId,
      sequence_number: event.sequenceNumber,
      event_type: event.eventType,
      payload: event.payload,
      actor_id: event.actorId,
      occurred_at: event.occurredAt.toISOString(),
    };

    const expectedChecksum = crypto
      .createHash('sha256')
      .update(previousChecksum + JSON.stringify(eventData))
      .digest('hex');

    if (expectedChecksum !== event.checksum) {
      return false;
    }

    previousChecksum = event.checksum;
  }

  return true;
}

export async function getAggregateHistory(
  aggregateType: string,
  aggregateId: string
) {
  return prisma.auditEvent.findMany({
    where: { aggregateType, aggregateId },
    orderBy: { sequenceNumber: 'asc' },
  });
}

Express Route for Audit Queries

import { Router } from 'express';

const router = Router();

router.get('/audit/:aggregateType/:aggregateId', async (req, res) => {
  const { aggregateType, aggregateId } = req.params;

  const history = await getAggregateHistory(aggregateType, aggregateId);

  res.json({
    aggregate: { type: aggregateType, id: aggregateId },
    eventCount: history.length,
    events: history.map((e) => ({
      sequence: e.sequenceNumber,
      type: e.eventType,
      payload: e.payload,
      actor: e.actorId,
      occurredAt: e.occurredAt,
    })),
  });
});

router.get('/audit/:aggregateType/:aggregateId/verify', async (req, res) => {
  const { aggregateType, aggregateId } = req.params;
  const valid = await verifyAuditChain(aggregateType, aggregateId);

  res.json({
    aggregate: { type: aggregateType, id: aggregateId },
    chainValid: valid,
    verifiedAt: new Date().toISOString(),
  });
});

Retention and Archival Strategy

Audit logs grow fast. A system processing 10,000 transactions per day generates millions of events per year. You need a retention strategy:

Hot storage (0–90 days): Primary database. Full query capability. Used for day-to-day operations and recent audits.
Warm storage (90 days–2 years): Move older events to a read-optimised store (e.g., partitioned table, read replica, or columnar database). Still queryable but not on the critical path.
Cold storage (2+ years): Archive to object storage (S3, GCS) in a structured format (Parquet, JSON Lines). Retained for regulatory compliance periods (typically 5–7 years for financial data).

Laravel Archival Command

class ArchiveAuditEvents extends Command
{
    protected $signature = 'audit:archive {--older-than=90}';

    public function handle(): void
    {
        $cutoff = now()->subDays((int) $this->option('older-than'));

        $events = AuditEvent::where('occurred_at', '<', $cutoff)
            ->orderBy('occurred_at')
            ->cursor();

        $batch = [];
        $batchCount = 0;

        foreach ($events as $event) {
            $batch[] = $event->toArray();

            if (count($batch) >= 10000) {
                $this->uploadBatch($batch, $batchCount);
                $batch = [];
                $batchCount++;
            }
        }

        if (!empty($batch)) {
            $this->uploadBatch($batch, $batchCount);
        }

        AuditEvent::where('occurred_at', '<', $cutoff)->delete();

        $this->info("Archived and purged events older than {$cutoff->toDateString()}.");
    }

    private function uploadBatch(array $batch, int $index): void
    {
        $filename = sprintf('audit-archive/%s/batch-%04d.json', now()->format('Y/m'), $index);

        Storage::disk('s3')->put(
            $filename,
            collect($batch)->map(fn ($e) => json_encode($e))->implode("\n")
        );
    }
}

Reconciliation with Immutable Logs

One of the most valuable outcomes of immutable logs is automated reconciliation. Instead of manually comparing spreadsheets, you can programmatically verify that every financial event has a matching counterpart:

class ReconciliationService
{
    public function reconcilePayments(string $date): array
    {
        $internalEvents = AuditEvent::where('event_type', 'payment.completed')
            ->whereDate('occurred_at', $date)
            ->get()
            ->keyBy(fn ($e) => $e->payload['gateway_reference']);

        $bankStatements = $this->fetchBankStatements($date);

        $matched = [];
        $unmatchedInternal = [];
        $unmatchedBank = [];

        foreach ($bankStatements as $statement) {
            if ($internalEvents->has($statement->reference)) {
                $matched[] = $statement->reference;
                $internalEvents->forget($statement->reference);
            } else {
                $unmatchedBank[] = $statement;
            }
        }

        $unmatchedInternal = $internalEvents->values()->all();

        return [
            'date' => $date,
            'matched_count' => count($matched),
            'unmatched_internal' => count($unmatchedInternal),
            'unmatched_bank' => count($unmatchedBank),
            'discrepancies' => [...$unmatchedInternal, ...$unmatchedBank],
        ];
    }
}

Production Results

After implementing immutable audit logging across financial platforms:

Metric	Before	After
Reconciliation time (weekly)	4+ hours manual	15 minutes automated
Audit preparation time	2–3 weeks	2 days
Unresolved discrepancies	8–12 per month	0–1 per month
Compliance audit findings	Multiple per review	Zero material findings
Data dispute resolution time	3–5 days	< 1 hour

Key Takeaways

Never update financial records in place. Append new events instead. The current state is a projection of the event history.
Use hash chains for tamper evidence. Each event's checksum includes the previous event's checksum. Any modification breaks the chain.
Record who, what, when, and why. Every event needs an actor, a timestamp, and the business context. IP addresses and session identifiers add forensic value.
Plan for retention from day one. Audit logs grow quickly. Tiered storage (hot/warm/cold) keeps costs manageable while satisfying compliance requirements.
Automate reconciliation. Immutable logs enable programmatic reconciliation that replaces hours of manual spreadsheet comparison.
Write events and business data in the same transaction. If they are not atomic, you risk inconsistency between your source of truth and your audit trail.

Conclusion

Audit-proof financial workflows are not about generating more logs — they are about designing systems where every state change is captured, chained, and verifiable. Append-only writes, hash-chain integrity, and tiered retention create a system that answers any auditor's question with evidence, not explanations.

Whether you build in Laravel or Node.js, the patterns are the same. This infrastructure pays dividends every time a regulator asks a question or a discrepancy needs resolution.

Database Optimization Strategies That Cut Query Execution Time by 60%: A Practical Guide

Olamilekan Lamidi — Wed, 15 Apr 2026 23:40:02 +0000

If your web application is slow, the database is almost certainly the bottleneck. Not the frontend framework. Not the web server. Not the network. The database.

I have spent nine years building backend systems across seven companies, and in every single engagement, database performance has been the most impactful area of optimization. At VacancySoft, where I currently serve as a Full Stack Engineer, I reduced query execution times by 60% across our core platform. At Univelcity, I achieved a 45% reduction in API response times, with database optimization accounting for the majority of those gains. At Lordwin Group, I optimised a real-time gaming backend to sustain 3,000+ concurrent users with sub-100ms query responses.

This article is the practical guide I wish I had when I started. It covers the exact strategies, tools, and techniques I use to diagnose and fix database performance problems in production systems running PostgreSQL and MySQL.

Strategy 1: Query Analysis and Profiling — Know Before You Optimize

The biggest mistake engineers make with database optimization is guessing. They add indexes randomly, rewrite queries based on intuition, or throw caching at the problem without understanding what is actually slow.

I start every optimization effort the same way: profiling.

Enable Slow Query Logging

In PostgreSQL:

-- postgresql.conf
log_min_duration_statement = 200  -- Log queries taking > 200ms
log_statement = 'none'            -- Don't log all queries, just slow ones

In MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.2;  -- 200ms threshold
SET GLOBAL log_queries_not_using_indexes = 'ON';

At VacancySoft, I established a weekly ritual of reviewing the slow query log. This alone surfaced dozens of optimization opportunities that no amount of code review would have caught. Queries that looked clean in the ORM were generating catastrophic execution plans.

Use EXPLAIN ANALYZE Religiously

EXPLAIN shows you the query plan. EXPLAIN ANALYZE actually executes the query and shows real timing data. The difference matters.

EXPLAIN ANALYZE
SELECT v.id, v.title, o.name AS organisation_name, s.name AS sector_name
FROM vacancies v
JOIN organisations o ON o.id = v.organisation_id
JOIN sectors s ON s.id = v.sector_id
WHERE v.status = 'active'
  AND v.created_at >= NOW() - INTERVAL '30 days'
ORDER BY v.created_at DESC
LIMIT 20;

Before optimization, this query at VacancySoft returned an EXPLAIN ANALYZE output like this:

Sort  (cost=15234.56..15234.61 rows=20 width=128) (actual time=1842.331..1842.345 rows=20 loops=1)
  Sort Key: v.created_at DESC
  ->  Hash Join  (cost=8234.12..15234.43 rows=20 width=128) (actual time=1201.442..1841.892 rows=24531 loops=1)
        ->  Seq Scan on vacancies v  (cost=0.00..6842.00 rows=24531 width=96) (actual time=0.021..892.134 rows=24531 loops=1)
              Filter: ((status = 'active') AND (created_at >= ...))
              Rows Removed by Filter: 187234
Planning Time: 0.892 ms
Execution Time: 1843.012 ms

Two immediate red flags: a sequential scan on the vacancies table scanning over 200,000 rows and filtering out 187,000 of them, and a hash join that is materialising far more rows than the final result needs.

This is the kind of visibility that transforms optimization from guesswork into engineering. Olamilekan Lamidi's first rule of database optimization: never optimise a query you have not profiled.

Strategy 2: Indexing Strategies — The Highest-Impact Change

Indexing is the single most impactful optimization technique in my toolkit. A well-placed index can turn a 2-second query into a 5-millisecond query. But indexes are not free — they consume storage, slow down writes, and can mislead the query planner if poorly designed.

Composite Indexes

Order matters in composite indexes. The index (status, created_at) is useful for queries that filter on status and sort or filter on created_at. It is useless for queries that filter only on created_at.

For the vacancy search query above, I created:

CREATE INDEX idx_vacancies_status_created_at
ON vacancies (status, created_at DESC);

This single index eliminated the sequential scan. The query planner now uses an index scan that reads only the relevant rows:

Index Scan using idx_vacancies_status_created_at on vacancies v
  (cost=0.42..234.56 rows=20 width=96) (actual time=0.089..2.341 rows=20 loops=1)
  Index Cond: ((status = 'active') AND (created_at >= ...))

Execution time: from 1,843ms to 12ms. A 99.3% reduction from a single composite index.

Partial Indexes

At VacancySoft, the majority of our queries filter on status = 'active'. Only about 15% of vacancies are active at any given time. A partial index dramatically reduces the index size:

CREATE INDEX idx_vacancies_active_created_at
ON vacancies (created_at DESC)
WHERE status = 'active';

This index is roughly 85% smaller than the full composite index, which means it fits in memory more easily and produces faster index scans.

Covering Indexes (Index-Only Scans)

A covering index includes all columns that a query needs, allowing PostgreSQL to serve the query entirely from the index without touching the heap (the actual table data). This eliminates random I/O, which is the most expensive operation on traditional storage.

CREATE INDEX idx_vacancies_covering
ON vacancies (status, created_at DESC)
INCLUDE (id, title, organisation_id, sector_id)
WHERE status = 'active';

With this covering index, PostgreSQL executes an index-only scan:

Index Only Scan using idx_vacancies_covering on vacancies v
  (cost=0.42..45.23 rows=20 width=96) (actual time=0.067..0.412 rows=20 loops=1)
  Heap Fetches: 0

Heap Fetches: 0 is the goal. The database never touches the table at all.

My Indexing Methodology

I follow a systematic process:

Identify the top 20 slowest queries from the slow query log.
Run EXPLAIN ANALYZE on each one.
Look for sequential scans on tables with more than 10,000 rows.
Check for sort operations that are not backed by an index.
Create targeted indexes and verify the plan changes.
Monitor write performance to ensure indexes are not degrading INSERT/UPDATE speed beyond acceptable thresholds.

At VacancySoft, this methodology across 15+ modules produced the 60% aggregate reduction in query execution time.

Strategy 3: Query Refactoring — Fixing What the ORM Gets Wrong

ORMs are excellent for productivity but frequently generate suboptimal SQL. The N+1 query problem is the most well-known example, but I encounter more subtle issues regularly.

Eliminating N+1 Queries

In Laravel (which I used extensively at 2am Tech, Lordwin Group, and Viaduct):

// Bad: N+1 problem — 1 query for organisations + N queries for vacancies
$organisations = Organisation::all();
foreach ($organisations as $org) {
    echo $org->vacancies->count(); // Triggers a query per organisation
}

// Good: Eager loading — 2 queries total
$organisations = Organisation::with('vacancies')->get();

// Better: When you only need the count
$organisations = Organisation::withCount('vacancies')->get();
foreach ($organisations as $org) {
    echo $org->vacancies_count; // No additional queries
}

At 2am Tech, the Addio financial platform was suffering from response times exceeding 3 seconds on the organisation dashboard. Profiling revealed over 150 queries per page load, almost all of them N+1 queries from Eloquent relationships. I refactored the critical paths to use eager loading and withCount, reducing the query count to 8 and the response time to 400ms.

Replacing Subqueries with JOINs and CTEs

ORMs often generate correlated subqueries where a JOIN or CTE would be far more efficient:

-- ORM-generated: correlated subquery (executes once per row)
SELECT o.id, o.name,
  (SELECT COUNT(*) FROM vacancies v WHERE v.organisation_id = o.id AND v.status = 'active')
    AS active_vacancy_count
FROM organisations o
WHERE o.sector_id = 5;

-- Refactored: single pass with JOIN
SELECT o.id, o.name, COUNT(v.id) AS active_vacancy_count
FROM organisations o
LEFT JOIN vacancies v ON v.organisation_id = o.id AND v.status = 'active'
WHERE o.sector_id = 5
GROUP BY o.id, o.name;

Window Functions for Complex Analytics

At VacancySoft, we needed to show month-over-month vacancy trends per sector. The initial implementation used multiple queries with application-level aggregation. I replaced it with a single query using window functions:

WITH monthly_counts AS (
  SELECT
    s.name AS sector_name,
    DATE_TRUNC('month', v.created_at) AS month,
    COUNT(*) AS vacancy_count
  FROM vacancies v
  JOIN sectors s ON s.id = v.sector_id
  WHERE v.created_at >= NOW() - INTERVAL '12 months'
  GROUP BY s.name, DATE_TRUNC('month', v.created_at)
)
SELECT
  sector_name,
  month,
  vacancy_count,
  LAG(vacancy_count) OVER (PARTITION BY sector_name ORDER BY month) AS prev_month_count,
  ROUND(
    (vacancy_count - LAG(vacancy_count) OVER (PARTITION BY sector_name ORDER BY month))::numeric
    / NULLIF(LAG(vacancy_count) OVER (PARTITION BY sector_name ORDER BY month), 0) * 100,
    1
  ) AS month_over_month_pct
FROM monthly_counts
ORDER BY sector_name, month;

This single query replaced five application-level queries and a significant amount of JavaScript aggregation logic. Response time for the trends endpoint dropped from 2.1 seconds to 280ms.

Strategy 4: Schema Redesign for Performance

Sometimes the problem is not the query — it is the schema. Normalization is taught as gospel in database courses, but production systems often require strategic denormalization.

Materialized Views

At VacancySoft, our analytics dashboard required aggregating data across five tables. Even with optimized queries and proper indexes, the aggregation was too slow for real-time display. I introduced materialized views:

CREATE MATERIALIZED VIEW mv_sector_analytics AS
SELECT
  s.id AS sector_id,
  s.name AS sector_name,
  COUNT(v.id) AS total_vacancies,
  COUNT(v.id) FILTER (WHERE v.status = 'active') AS active_vacancies,
  COUNT(DISTINCT v.organisation_id) AS hiring_organisations,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY v.salary_max) AS median_salary,
  DATE_TRUNC('day', NOW()) AS snapshot_date
FROM sectors s
LEFT JOIN vacancies v ON v.sector_id = s.id
  AND v.created_at >= NOW() - INTERVAL '90 days'
GROUP BY s.id, s.name;

CREATE UNIQUE INDEX ON mv_sector_analytics (sector_id);

-- Refresh concurrently (no lock on reads)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sector_analytics;

I set up a cron job to refresh this view every 15 minutes. The dashboard query that previously took 3.8 seconds now completes in under 10ms because it reads from the pre-computed view.

Strategic Denormalization

For the organisation profile pages, rather than joining five tables on every request, I added a cached_stats JSONB column on the organisations table:

ALTER TABLE organisations ADD COLUMN cached_stats JSONB DEFAULT '{}';

-- Updated by a background job every hour
UPDATE organisations SET cached_stats = jsonb_build_object(
  'active_vacancies', (SELECT COUNT(*) FROM vacancies WHERE organisation_id = organisations.id AND status = 'active'),
  'total_vacancies_90d', (SELECT COUNT(*) FROM vacancies WHERE organisation_id = organisations.id AND created_at >= NOW() - INTERVAL '90 days'),
  'avg_time_to_fill', (SELECT AVG(EXTRACT(EPOCH FROM (filled_at - created_at)) / 86400) FROM vacancies WHERE organisation_id = organisations.id AND filled_at IS NOT NULL),
  'updated_at', NOW()
);

This is a tradeoff: the data can be up to one hour stale, and we have the overhead of the background update job. But for this use case, slight staleness is acceptable, and the performance gain — from 450ms to 8ms per profile page — is transformative.

Strategy 5: Connection Pooling and Read Replicas

At scale, query optimization alone is not enough. You also need to manage how connections to the database are handled.

Connection Pooling with PgBouncer

PostgreSQL creates a new process for each connection, which is expensive. At VacancySoft, I deployed PgBouncer in front of PostgreSQL in transaction pooling mode:

; pgbouncer.ini
[databases]
vacancysoft = host=127.0.0.1 port=5432 dbname=vacancysoft

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 500
default_pool_size = 25
reserve_pool_size = 5

This allows 500 application connections to share 25 database connections. Without pooling, our application would regularly hit PostgreSQL's connection limit during traffic spikes, causing cascading failures.

Read Replicas

For read-heavy workloads (which describe most web applications), I split reads and writes across primary and replica databases:

// Database configuration with read/write splitting
const knex = require('knex');

const primary = knex({
  client: 'pg',
  connection: process.env.DATABASE_PRIMARY_URL,
});

const replica = knex({
  client: 'pg',
  connection: process.env.DATABASE_REPLICA_URL,
});

// Service layer decides which connection to use
class VacancyService {
  async search(filters) {
    return replica('vacancies')
      .where(filters)
      .orderBy('created_at', 'desc');
  }

  async create(data) {
    return primary('vacancies')
      .insert(data)
      .returning('*');
  }
}

At VacancySoft, this split reduced load on the primary database by approximately 70%, since the vast majority of our traffic is read operations (search, analytics, reporting).

Strategy 6: Caching Layers

The fastest database query is the one you never make. Caching is the final layer of my optimization strategy.

Redis for Hot Data

const Redis = require('ioredis');
const redis = new Redis(process.env.REDIS_URL);

async function getSectorAnalytics(sectorId) {
  const cacheKey = `sector:${sectorId}:analytics`;
  const cached = await redis.get(cacheKey);

  if (cached) {
    return JSON.parse(cached);
  }

  const data = await db('mv_sector_analytics')
    .where('sector_id', sectorId)
    .first();

  await redis.setex(cacheKey, 900, JSON.stringify(data)); // 15-minute TTL

  return data;
}

Cache Invalidation Strategy

Cache invalidation is famously one of the two hard problems in computer science. My approach is pragmatic:

TTL-based expiration for data that can tolerate staleness (analytics, aggregations).
Event-driven invalidation for data that must be fresh (user profiles, active listings). When a vacancy is created or updated, I publish an event that invalidates the relevant cache keys.
Cache warming for predictable access patterns. At VacancySoft, I pre-warm the cache for the top 50 sectors and top 100 organisations every 15 minutes, so the first user to access these pages never experiences a cache miss.

// Event-driven cache invalidation
eventBus.on('vacancy:created', async (vacancy) => {
  await redis.del(`sector:${vacancy.sector_id}:analytics`);
  await redis.del(`org:${vacancy.organisation_id}:stats`);
  await redis.del('homepage:featured_vacancies');
});

Results: Before and After

Here is a summary of the measurable impact these strategies produced across my career, as implemented by Olamilekan Lamidi:

Company	Metric	Before	After	Improvement
VacancySoft	Average query execution time	850ms	340ms	60% reduction
VacancySoft	Dashboard load time	3.8s	0.4s	89% reduction
VacancySoft	p95 API response time	1.2s	180ms	85% reduction
Univelcity	API response time (avg)	620ms	340ms	45% reduction
Univelcity	Queries per page load	45	8	82% reduction
Lordwin Group	Real-time query latency	220ms	35ms	84% reduction
2am Tech	Organisation dashboard load	3.1s	400ms	87% reduction

Key Takeaways

Profile before you optimize. EXPLAIN ANALYZE and slow query logs should be your starting point, not your intuition.
Composite and partial indexes are the highest-leverage optimization in most applications. One well-placed index can eliminate 99% of query time.
ORMs are productivity tools, not performance tools. Review the SQL they generate, especially for complex queries.
Materialized views and strategic denormalization are not anti-patterns — they are essential tools for read-heavy workloads.
Connection pooling is mandatory once you are handling more than a few hundred concurrent connections.
Caching is the last layer, not the first. Optimize the underlying queries before adding cache complexity.

Database optimization is not a one-time project. It is an ongoing discipline. The queries that perform well today may become bottlenecks tomorrow as data grows and access patterns change. Build profiling and monitoring into your workflow, and treat database performance as a first-class engineering concern.

The Art of API Design: Principles I Learned Building APIs That Handle 50,000+ Daily Requests

Olamilekan Lamidi — Sun, 12 Apr 2026 21:02:36 +0000

APIs are the backbone of modern software. They power every mobile app you use, every SaaS dashboard you log into, and every integration between systems that makes the modern web work. Yet despite their ubiquity, most APIs are designed poorly. They leak database schema into response payloads, return cryptic error codes, break backward compatibility without warning, and crumble under load.

I know this because I have spent the past nine years building, consuming, and debugging APIs across seven companies, multiple industries, and technology stacks spanning PHP (Laravel, Symfony) and JavaScript (Node.js, Express, NestJS). I have built APIs for health-tech platforms at 54gene, financial infrastructure at 2am Tech, real-time gaming at Lordwin Group, on-demand delivery at Viaduct, education technology at Univelcity, and enterprise SaaS at VacancySoft. Collectively, these systems have handled millions of requests and served tens of thousands of users.

Through that experience, I have distilled a set of principles that separate well-designed APIs from fragile, frustrating ones. These are not theoretical musings — they are battle-tested patterns drawn from production systems that process 50,000+ daily requests at VacancySoft and have served over 8,000 users at Univelcity with 20,000+ requests per day.

This article is a deep dive into those principles.

Principle 1: Design for the Consumer, Not the Database

The most common API design mistake I encounter is what I call "schema leaking" — when an API's resource structure mirrors the underlying database tables rather than reflecting how consumers actually use the data.

Early in my career at Univelcity, I fell into this trap. We had a users table, a courses table, and a enrollments join table. The first version of our API had three corresponding endpoints that returned raw table structures. Clients had to make three separate requests and stitch data together themselves.

The fix was to think in terms of resources, not tables. A consumer doesn't care about your join table. They care about "a student's enrolled courses" or "a course's enrolled students." The API should model those concepts directly:

// Bad: Database-centric design
GET /users/123
GET /enrollments?user_id=123
GET /courses/456

// Good: Consumer-centric design
GET /students/123/courses
GET /courses/456/students

At VacancySoft, I apply this principle rigorously. Our APIs expose business concepts — vacancies, organisations, sectors, market trends — not the complex graph of PostgreSQL tables underneath. When we introduced a "market snapshot" feature, rather than forcing clients to aggregate data from four different endpoints, I designed a single /market-snapshots resource that returned a pre-composed view of the data clients needed. This reduced client-side complexity dramatically and cut the average number of API calls per page load from seven to two.

The key insight: your database schema is an implementation detail. Your API contract is a product. Treat it like one.

Principle 2: Versioning Strategies That Don't Break Clients

API versioning is one of those topics where everyone has an opinion and nobody agrees. I have used three approaches across my career: URI versioning, header versioning, and query parameter versioning. Here is what I have learned.

URI versioning (/api/v1/vacancies) is what I use in most production systems and what we use at VacancySoft. It is explicit, easy to understand, easy to route, and easy to document. The tradeoff is that it can lead to code duplication if you are not careful about abstracting shared logic between versions.

Header versioning (Accept: application/vnd.api+json; version=2) is cleaner in theory but harder to test, harder to debug with basic tools like curl, and harder for less experienced developers to consume.

Query parameter versioning (/api/vacancies?version=2) is the worst of both worlds and I do not recommend it.

My approach at VacancySoft:

// Express router with versioned routes
const v1Router = express.Router();
const v2Router = express.Router();

app.use('/api/v1', v1Router);
app.use('/api/v2', v2Router);

// Shared business logic lives in services, not controllers
// Controllers are thin version-specific adapters
v1Router.get('/vacancies', async (req, res) => {
  const data = await vacancyService.search(req.query);
  res.json(transformV1(data));
});

v2Router.get('/vacancies', async (req, res) => {
  const data = await vacancyService.search(req.query);
  res.json(transformV2(data));
});

The critical rule I follow: never remove a version without a deprecation period and proactive communication to consumers. At VacancySoft, when we deprecated v1 of our vacancy search endpoint, I implemented a deprecation header (Sunset: Sat, 01 Jul 2024 00:00:00 GMT) six months in advance and added logging to track which clients were still using v1. This gave us data-driven confidence to retire the old version without breaking anyone.

Principle 3: Pagination, Filtering, and Sorting at Scale

Pagination seems simple until you are dealing with datasets of hundreds of thousands of records that change frequently. I have implemented both offset-based and cursor-based pagination in production, and the difference matters enormously at scale.

Offset pagination (?page=5&per_page=20) is intuitive but fundamentally broken for large datasets. The database still has to scan all rows up to the offset, meaning page 500 is orders of magnitude slower than page 1. It also produces inconsistent results when records are inserted or deleted between requests.

At VacancySoft, where our vacancy dataset exceeds hundreds of thousands of records and is updated continuously, I implemented cursor-based pagination:

// Cursor-based pagination implementation
async function getVacancies(cursor, limit = 20) {
  const query = knex('vacancies')
    .orderBy('created_at', 'desc')
    .orderBy('id', 'desc')
    .limit(limit + 1);

  if (cursor) {
    const { created_at, id } = decodeCursor(cursor);
    query.where(function () {
      this.where('created_at', '<', created_at)
        .orWhere(function () {
          this.where('created_at', '=', created_at)
            .andWhere('id', '<', id);
        });
    });
  }

  const results = await query;
  const hasMore = results.length > limit;
  const items = hasMore ? results.slice(0, -1) : results;

  return {
    data: items,
    pagination: {
      has_more: hasMore,
      next_cursor: hasMore
        ? encodeCursor(items[items.length - 1])
        : null,
    },
  };
}

function encodeCursor(record) {
  return Buffer.from(
    JSON.stringify({
      created_at: record.created_at,
      id: record.id,
    })
  ).toString('base64');
}

This approach uses a composite cursor of (created_at, id) to ensure stable ordering even with duplicate timestamps. It performs consistently regardless of how deep into the dataset a client paginates, because the database uses an index seek rather than a scan.

For filtering, I standardise on a predictable pattern:

GET /api/v2/vacancies?sector=technology&region=london&salary_min=50000&sort=-created_at

The - prefix for descending sort is a convention I adopted from JSON:API and have used consistently across every API I have built since 2020. It is intuitive and self-documenting.

Principle 4: Error Handling That Helps Developers

Nothing frustrates an API consumer more than unhelpful errors. I have received 500 Internal Server Error with an empty body from third-party APIs, and I have vowed never to do that to my consumers.

Every API I build follows a consistent error schema:

{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "The request body contains invalid fields.",
    "details": [
      {
        "field": "email",
        "message": "Must be a valid email address.",
        "received": "not-an-email"
      },
      {
        "field": "salary_min",
        "message": "Must be a positive integer.",
        "received": -500
      }
    ],
    "request_id": "req_8f3a2b1c",
    "documentation_url": "https://api.example.com/docs/errors#VALIDATION_ERROR"
  }
}

Key principles:

Machine-readable error codes (VALIDATION_ERROR, not just HTTP status codes) so clients can programmatically handle different error types.
Human-readable messages so developers can understand what went wrong without consulting documentation.
Field-level detail for validation errors so frontend developers know exactly which fields to highlight.
Request IDs so that when a consumer reports an issue, I can trace it through our logging infrastructure.
Documentation URLs that link directly to the relevant error documentation.

At VacancySoft, I implemented a centralised error handling middleware that ensures every error — whether it is a validation failure, an authorization error, or an unexpected exception — passes through the same formatting pipeline:

// Centralised error handler middleware
function errorHandler(err, req, res, next) {
  const requestId = req.headers['x-request-id'] || generateRequestId();

  if (err instanceof ValidationError) {
    return res.status(422).json({
      error: {
        code: 'VALIDATION_ERROR',
        message: err.message,
        details: err.details,
        request_id: requestId,
      },
    });
  }

  if (err instanceof AuthorizationError) {
    return res.status(403).json({
      error: {
        code: 'FORBIDDEN',
        message: 'You do not have permission to access this resource.',
        request_id: requestId,
      },
    });
  }

  // Unexpected errors: log full stack, return safe message
  logger.error({
    request_id: requestId,
    error: err.message,
    stack: err.stack,
    path: req.path,
    method: req.method,
  });

  return res.status(500).json({
    error: {
      code: 'INTERNAL_ERROR',
      message: 'An unexpected error occurred. Please try again.',
      request_id: requestId,
    },
  });
}

This approach ensures that internal details (stack traces, database errors) never leak to consumers, while still providing enough information for debugging.

Principle 5: Rate Limiting and Throttling for Protection

Any API exposed to the internet will eventually be abused — whether by a misconfigured client making thousands of requests in a loop, a scraper, or a deliberate attack. Rate limiting is not optional.

I implement rate limiting at multiple layers:

Application-level rate limiting using Redis-backed token bucket or sliding window algorithms:

const rateLimit = require('express-rate-limit');
const RedisStore = require('rate-limit-redis');
const Redis = require('ioredis');

const redisClient = new Redis(process.env.REDIS_URL);

const apiLimiter = rateLimit({
  store: new RedisStore({
    client: redisClient,
    prefix: 'rl:',
  }),
  windowMs: 60 * 1000, // 1 minute
  max: 100,            // 100 requests per minute
  standardHeaders: true,
  legacyHeaders: false,
  handler: (req, res) => {
    res.status(429).json({
      error: {
        code: 'RATE_LIMIT_EXCEEDED',
        message: 'Too many requests. Please retry after the window resets.',
        retry_after: res.getHeader('Retry-After'),
      },
    });
  },
});

// Stricter limits for authentication endpoints
const authLimiter = rateLimit({
  store: new RedisStore({ client: redisClient, prefix: 'rl:auth:' }),
  windowMs: 15 * 60 * 1000, // 15 minutes
  max: 10,                   // 10 attempts per 15 minutes
  standardHeaders: true,
});

app.use('/api/', apiLimiter);
app.use('/api/auth/', authLimiter);

Infrastructure-level rate limiting via Nginx or a CDN as the first line of defence. At VacancySoft, we use Nginx rate limiting in front of our Node.js services to catch abusive traffic before it reaches the application layer. This dual-layer approach proved critical during a traffic spike when a partner's integration went haywire and started hitting our API at 10x the normal rate. Nginx absorbed the burst, and the application-level limiter gracefully degraded the remaining overflow with proper 429 responses.

I always include RateLimit-* headers in responses so well-behaved clients can self-throttle:

RateLimit-Limit: 100
RateLimit-Remaining: 67
RateLimit-Reset: 1719849600

Principle 6: Authentication and Authorization Patterns

Authentication (who are you?) and authorization (what can you do?) are distinct concerns that I see conflated constantly. Over my career I have implemented multiple patterns, and the right choice depends heavily on context.

For service-to-service communication within a backend, I use short-lived JWT tokens with asymmetric key signing (RS256). The issuing service signs with a private key, and consuming services verify with the public key without needing to call back to the auth service:

const jwt = require('jsonwebtoken');

// Token generation (auth service)
function generateAccessToken(user) {
  return jwt.sign(
    {
      sub: user.id,
      email: user.email,
      roles: user.roles,
      permissions: user.permissions,
    },
    privateKey,
    {
      algorithm: 'RS256',
      expiresIn: '15m',
      issuer: 'auth.vacancysoft.com',
    }
  );
}

// Middleware for route-level authorization
function requirePermission(permission) {
  return (req, res, next) => {
    if (!req.user || !req.user.permissions.includes(permission)) {
      return res.status(403).json({
        error: {
          code: 'FORBIDDEN',
          message: `Required permission: ${permission}`,
        },
      });
    }
    next();
  };
}

// Usage
router.delete(
  '/vacancies/:id',
  authenticate,
  requirePermission('vacancies:delete'),
  vacancyController.destroy
);

At VacancySoft, I implemented a role-based access control (RBAC) system with granular permissions. Rather than checking if (user.role === 'admin') throughout the codebase, permissions are encoded in the JWT and checked via middleware. This made it trivial to add new roles — when the product team introduced a "regional analyst" role, I added the appropriate permissions without touching a single controller.

For external-facing APIs, I use API keys for identification combined with OAuth 2.0 for delegated access when third-party integrations need to act on behalf of users.

Principle 7: Documentation as a First-Class Citizen

I have a rule: if an API endpoint ships without documentation, it does not ship. Documentation is not an afterthought — it is a deliverable.

I use the OpenAPI 3.0 specification (Swagger) for every API I build, and I generate the spec from code annotations rather than maintaining a separate document that inevitably drifts:

/**
 * @openapi
 * /api/v2/vacancies:
 *   get:
 *     summary: Search vacancies
 *     description: >
 *       Returns a paginated, filtered list of vacancies.
 *       Supports cursor-based pagination for stable results.
 *     tags: [Vacancies]
 *     parameters:
 *       - in: query
 *         name: sector
 *         schema:
 *           type: string
 *         description: Filter by sector slug
 *       - in: query
 *         name: cursor
 *         schema:
 *           type: string
 *         description: Pagination cursor from previous response
 *     responses:
 *       200:
 *         description: Paginated list of vacancies
 *         content:
 *           application/json:
 *             schema:
 *               $ref: '#/components/schemas/VacancyListResponse'
 *       429:
 *         description: Rate limit exceeded
 */
router.get('/vacancies', vacancyController.search);

At VacancySoft, our Swagger documentation is auto-generated from these annotations and published to an internal documentation portal. Every endpoint includes request/response examples, error codes, and authentication requirements. When new team members onboard, they can explore and test every endpoint interactively without reading a single line of source code.

I also generate Postman collections from the OpenAPI spec, which our QA team and integration partners use for testing. This single-source-of-truth approach eliminates the documentation drift that plagues most API teams.

The Results

These principles are not academic exercises. Applied consistently at VacancySoft, they have produced measurable outcomes:

Metric	Result
Daily request volume	50,000+
API uptime	99.8%+ over the last 12 months
Average response time (p50)	45ms
Average response time (p95)	180ms
Client integration time	Reduced from weeks to days
Breaking changes in production	Zero in the last 18 months
Support tickets related to API confusion	Down 70% after error schema standardisation

Olamilekan Lamidi's approach to API design has been shaped by one core belief: an API is a product, and its consumers are your users. Every decision — from resource naming to error formatting to pagination strategy — should be made with the consumer's experience in mind.

Key Takeaways

Model resources around consumer use cases, not database tables.
Use URI versioning with a clear deprecation policy and sunset headers.
Implement cursor-based pagination for any dataset that changes frequently or exceeds a few thousand records.
Standardise error responses with machine-readable codes, human-readable messages, and request IDs.
Rate limit at multiple layers — infrastructure and application — and communicate limits via headers.
Separate authentication from authorization and encode permissions in tokens for stateless checks.
Treat documentation as a deliverable, not an afterthought. Generate it from code.

These principles have served me across every company and technology stack I have worked with. Whether you are building a Laravel API in PHP or an Express API in Node.js, the fundamentals remain the same: design for clarity, build for resilience, and always, always think about the person on the other side of the request.

Engineering Real-Time Delivery Tracking That Increased Checkout Conversions by 20%

Olamilekan Lamidi — Thu, 09 Apr 2026 22:46:21 +0000

In e-commerce and on-demand delivery, the moment a user clicks "Place Order" begins an anxiety cycle. Where is my order? When will it arrive? This uncertainty kills conversions.

Between November 2020 and July 2021, I worked as a Full-Stack Developer at Viaduct, where I engineered a real-time delivery tracking system for an on-demand platform serving 5,000+ active users. The platform connected customers with local vendors for same-day delivery. Before I joined, users were abandoning carts and cancelling orders at alarming rates because they had zero visibility into delivery status.

This article details how I designed and built the tracking system — Google Maps integration, WebSocket-powered live updates, a Vue.js reactive interface, and the Laravel backend tying it together. The result: a 20% increase in checkout conversions.

The Problem: Delivery Opacity Was Killing Revenue

When I joined Viaduct, the delivery platform was functional but opaque. Users placed orders, received a confirmation email, and waited. The only updates were manual — a dispatcher marked orders "out for delivery" when a driver departed and "delivered" when reported back.

The consequences were measurable:

Cart abandonment: 32% of users abandoned at checkout. Exit surveys showed the top reason was "uncertain delivery time."

Post-order cancellations: 15% of completed checkouts were cancelled within 30 minutes, most citing "no delivery updates."

Support overload: 200+ daily calls asking "where is my order?" — each averaging 4 minutes, consuming 13 person-hours daily.

Driver accountability gaps: Without GPS tracking, there was no way to verify delivery times, identify route inefficiencies, or resolve disputes.

My Role: Full-Stack Architect of the Tracking System

I designed and built the entire real-time tracking system end-to-end: the Laravel backend infrastructure, Google Maps API integration, the WebSocket communication layer, and the Vue.js frontend tracking interface. I owned it from initial technical design through production deployment and post-launch optimisation.

Technical Deep Dive: Building the Real-Time Tracking Infrastructure

1. Driver Location Collection Architecture

The foundation of real-time tracking is reliable location data. I built a location reporting service on drivers' devices, transmitting GPS coordinates at configurable intervals.

class DriverLocationController extends Controller
{
    public function updateLocation(UpdateLocationRequest $request): JsonResponse
    {
        $driver = $request->user();
        $validated = $request->validated();

        $location = DriverLocation::create([
            'driver_id' => $driver->id,
            'latitude' => $validated['latitude'],
            'longitude' => $validated['longitude'],
            'accuracy' => $validated['accuracy'],
            'speed' => $validated['speed'] ?? null,
            'heading' => $validated['heading'] ?? null,
            'recorded_at' => Carbon::createFromTimestamp($validated['timestamp']),
        ]);

        if ($activeDelivery = $driver->activeDelivery) {
            $this->broadcastLocationUpdate($activeDelivery, $location);
            $this->updateEstimatedArrival($activeDelivery, $location);
        }

        return response()->json(['status' => 'recorded'], 200);
    }

    private function broadcastLocationUpdate(Delivery $delivery, DriverLocation $location): void
    {
        Redis::publish("delivery:{$delivery->id}:location", json_encode([
            'delivery_id' => $delivery->id,
            'driver' => [
                'latitude' => $location->latitude,
                'longitude' => $location->longitude,
                'heading' => $location->heading,
            ],
            'timestamp' => $location->recorded_at->toIso8601String(),
        ]));
    }
}

Location updates published to Redis channels, bridging the Laravel backend and the WebSocket server. This decoupled location ingestion (high-frequency HTTP POSTs from drivers) from location broadcasting (persistent WebSocket connections to customers), allowing each to scale independently.

2. Google Maps Integration for ETA Calculation

Accurate ETAs were critical. I integrated the Google Maps Directions API with live traffic data:

class GoogleMapsService
{
    public static function getDirections(array $origin, array $destination): ?DirectionsResult
    {
        $response = Http::get('https://maps.googleapis.com/maps/api/directions/json', [
            'origin' => "{$origin['lat']},{$origin['lng']}",
            'destination' => "{$destination['lat']},{$destination['lng']}",
            'mode' => 'driving',
            'departure_time' => 'now',
            'traffic_model' => 'best_guess',
            'key' => config('services.google_maps.api_key'),
        ]);

        if (!$response->successful() || $response->json('status') !== 'OK') {
            return null;
        }

        $leg = $response->json('routes.0.legs.0');
        return new DirectionsResult(
            durationMinutes: (int) ceil($leg['duration_in_traffic']['value'] / 60),
            distanceKm: round($leg['distance']['value'] / 1000, 1),
            polyline: $response->json('routes.0.overview_polyline.points'),
        );
    }
}

Using departure_time=now and traffic_model=best_guess ensured ETAs reflected live traffic — during rush hours, static estimates might show 10 minutes while actual time was 25. However, calling Google Maps on every location update (every 10 seconds per driver) would be prohibitively expensive. I implemented intelligent throttling using Haversine distance:

class ETAService
{
    private const ETA_CACHE_TTL = 60;
    private const MIN_DISTANCE_FOR_RECALC = 0.2; // km

    public function shouldRecalculateETA(Delivery $delivery, DriverLocation $newLocation): bool
    {
        if (!$delivery->eta_updated_at) return true;
        if (now()->diffInSeconds($delivery->eta_updated_at) < self::ETA_CACHE_TTL) return false;

        $lastCalcLocation = Cache::get("eta_calc_location:{$delivery->id}");
        if (!$lastCalcLocation) return true;

        return $this->haversineDistance(
            $lastCalcLocation['lat'], $lastCalcLocation['lng'],
            $newLocation->latitude, $newLocation->longitude
        ) >= self::MIN_DISTANCE_FOR_RECALC;
    }

    private function haversineDistance(float $lat1, float $lon1, float $lat2, float $lon2): float
    {
        $dLat = deg2rad($lat2 - $lat1);
        $dLon = deg2rad($lon2 - $lon1);
        $a = sin($dLat/2)**2 + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * sin($dLon/2)**2;
        return 6371 * 2 * atan2(sqrt($a), sqrt(1 - $a));
    }
}

ETAs were recalculated only when the driver moved 200m+ and 60+ seconds had elapsed, reducing Google Maps API calls by ~80%.

3. WebSocket Server for Live Customer Updates

The real-time frontend was powered by a Node.js WebSocket server subscribed to Redis channels:

const wss = new WebSocket.Server({ port: 6001 });
const subscriber = new Redis(process.env.REDIS_URL);
const deliverySubscriptions = new Map();

wss.on('connection', (ws) => {
  ws.on('message', (raw) => {
    const msg = JSON.parse(raw);
    if (msg.type === 'subscribe_delivery') {
      if (!deliverySubscriptions.has(msg.deliveryId)) {
        deliverySubscriptions.set(msg.deliveryId, new Set());
        subscriber.subscribe(
          `delivery:${msg.deliveryId}:location`,
          `delivery:${msg.deliveryId}:eta`,
          `delivery:${msg.deliveryId}:status`
        );
      }
      deliverySubscriptions.get(msg.deliveryId).add(ws);
    }
  });

  ws.on('close', () => {
    if (ws.deliveryId) {
      const subs = deliverySubscriptions.get(ws.deliveryId);
      if (subs) { subs.delete(ws); if (subs.size === 0) deliverySubscriptions.delete(ws.deliveryId); }
    }
  });
});

subscriber.on('message', (channel, message) => {
  const [, deliveryId, eventType] = channel.split(':');
  const subs = deliverySubscriptions.get(deliveryId);
  if (!subs) return;
  const payload = JSON.stringify({ type: `delivery_${eventType}`, data: JSON.parse(message) });
  for (const ws of subs) { if (ws.readyState === WebSocket.OPEN) ws.send(payload); }
});

The server was deliberately stateless — it bridged Redis pub/sub to WebSocket connections with no business logic, making it horizontally scalable.

4. Vue.js Real-Time Tracking Interface

The customer-facing tracking page used Vue.js Composition API. The core was a WebSocket composable with exponential backoff reconnection:

export function useDeliveryWebSocket(deliveryId) {
  const driverLocation = ref(null);
  const eta = ref(null);
  const deliveryStatus = ref('confirmed');
  let ws = null, reconnectAttempts = 0;

  function connect() {
    ws = new WebSocket(process.env.VUE_APP_WS_URL);
    ws.onopen = () => {
      reconnectAttempts = 0;
      ws.send(JSON.stringify({ type: 'subscribe_delivery', deliveryId }));
    };
    ws.onmessage = ({ data }) => {
      const msg = JSON.parse(data);
      if (msg.type === 'delivery_location') driverLocation.value = msg.data.driver;
      else if (msg.type === 'delivery_eta') eta.value = msg.data.eta_minutes;
      else if (msg.type === 'delivery_status') deliveryStatus.value = msg.data.status;
    };
    ws.onclose = () => {
      if (reconnectAttempts < 10)
        setTimeout(connect, Math.min(1000 * 2 ** reconnectAttempts++, 30000));
    };
  }
  return { driverLocation, eta, deliveryStatus, connect, disconnect: () => ws?.close() };
}

The map component initialised Google Maps, rendered destination and driver markers, and animated the driver marker between positions using cubic easing. Without animation, the marker would "teleport" every 10 seconds — with smooth interpolation, users perceived continuous movement, dramatically improving perceived quality.

5. Delivery Status Lifecycle and Performance

Every status transition published to Redis for real-time updates, sent push notifications, and recorded metrics:

class Delivery extends Model
{
    public function markPickedUp(): void
    {
        $this->update(['status' => 'picked_up', 'picked_up_at' => now()]);
        Redis::publish("delivery:{$this->id}:status", json_encode([
            'delivery_id' => $this->id, 'status' => 'picked_up',
            'driver' => ['name' => $this->driver->name, 'phone' => $this->driver->phone],
        ]));
        $this->order->customer->notify(new DeliveryPickedUpNotification($this));
    }

    public function markDelivered(): void
    {
        $this->update(['status' => 'delivered', 'delivered_at' => now()]);
        Redis::publish("delivery:{$this->id}:status", json_encode([
            'delivery_id' => $this->id, 'status' => 'delivered',
        ]));
        $this->order->customer->notify(new DeliveryCompletedNotification($this));
        DeliveryMetric::create([
            'delivery_id' => $this->id, 'driver_id' => $this->driver_id,
            'estimated_minutes' => $this->estimated_arrival_minutes,
            'actual_minutes' => $this->picked_up_at?->diffInMinutes($this->delivered_at),
        ]);
    }
}

The DeliveryMetric model enabled comparing estimated vs. actual delivery times, identifying underperforming drivers and slow routes.

For performance at scale, I buffered location writes in Redis and flushed to PostgreSQL in batches every 30 seconds, reducing database write load by 90% while maintaining real-time delivery through the pub/sub layer. Vendor discovery pages used PostGIS spatial queries (ST_DWithin, ST_Distance) with aggressive caching, returning results in under 50ms across thousands of records.

The Results: Measurable Business Impact

The system launched after eight weeks of development. Results over a 60-day observation period:

Metric	Before	After	Change
Checkout conversion rate	68%	88%	+20 percentage points
Post-order cancellation rate	15%	4%	73% reduction
"Where is my order?" support calls	200+/day	~35/day	82% reduction
Customer satisfaction score	3.4/5	4.3/5	26% improvement
Active platform users	~3,200	5,000+	56% growth
Tracking page views per order	0	4.2	New engagement channel

The support call reduction alone freed 11 person-hours daily — redirected to proactive customer outreach and vendor management.

Technical Lessons

Decouple ingestion from delivery. Location collection (Laravel) and broadcasting (Node.js WebSocket) had different performance profiles. Separate services connected by Redis allowed independent scaling.
Animate transitions, don't teleport. Users perceive smooth movement as real-time tracking and position jumps as a broken system.
Throttle expensive API calls intelligently. The Haversine check before ETA recalculation saved thousands of unnecessary Google Maps calls daily.
Buffer writes, stream reads. Batch-writing to the database while streaming through Redis provided both persistence and live performance.
Measure business metrics. WebSocket latency matters, but checkout conversion rate proved the system's value.