Getting Started

Follow these steps to set up and run the project locally.

1. Clone the Repository

git clone <your-repo-url>
cd <project-folder>

composer install

cp .env.example .env php artisan serve

Part 1: Query Optimization

Controller Example: UserSearchController@index

• Smarter Query Structure

  • Instead of joining users → form_data → form_options directly (which gets expensive), we first collect the matching option_ids and then filter users with a lightweight EXISTS subquery.

  This way, we avoid scanning unnecessary rows and reduce the chance of duplicate user results.

• Helpful Indexes

  We added composite indexes to match the way our queries filter data:

      form_data
  
      (tenant_id, option_id, user_id)
  
      (tenant_id, user_id, option_id)
  
      form_options
  
      (tenant_id, label) (good for prefix matches; for larger text searches we can add FULLTEXT later)
  
      users
  
      (tenant_id, id) (makes tenant-scoped pagination much faster) 
  

  These indexes ensure MySQL can use efficient index lookups instead of full table scans.

• Handling Labels Without Blowing Up Rows

  Instead of pulling all labels in the main query, we only fetch the list of user_ids for the current page.

  Then, in a second query, we grab the labels grouped by user.

  This avoids row explosion and keeps pagination accurate.

• Caching Layer

  Query results are cached per tenant, keyword, and page (or cursor).

  If Redis/Memcached is available, we use tagged cache (e.g., tenant:{id}, user_search) so it’s easy to invalidate when data changes.

  Default cache time is 5 minutes, which gives us a good balance between freshness and performance.

Part 2: Queue Race Conditions

Problem

When multiple Laravel servers process jobs that update user balances (e.g., invoice created/updated/deleted), jobs may be executed out of order.
This causes incorrect balances — a race condition.

Example:

Job A (invoice created): +100

Job B (invoice deleted): -100

If B runs before A, final balance becomes +100 instead of 0. Root causes: distributed workers, no per-user ordering, non-idempotent updates, and no transactional ordering guarantee.

---

Proposed Solutions

Solution 1: Per-User Queuing (Partitioning by User ID)

All jobs related to the same user are sent to the same queue/stream partition, keyed by user_id.

Guarantees sequential processing of events per user.

Example: with Redis Streams or Kafka, partitioning can be done as partition = user_id % N.

This allows the system to scale horizontally while still maintaining strict order for each individual user.

Best choice when ordering is critical, such as financial transactions or balance updates.

Solution 2: Distributed Locking (Redis Lock / Database Lock)

-Before processing a job, the worker acquires a lock on that user_id.

-While one job for that user is running, other jobs wait until the lock is released.

-Ensures no two jobs for the same user run at the same time.

public function handle()
    {
    $lockKey = "lock:user:{$this->userId}";
    $lock = Redis::funnel($lockKey)->limit(1)->block(10);

        $lock->then(function () {
            $this->updateUserBalance();
        }, function () {
            return false;
        });

    }

Task

Redis Stream / Kafka-style Per-User Queuing in Laravel

To prevent race conditions when updating user balances, we implemented per-user queue partitioning. Each job is assigned to a queue based on the user ID, ensuring events for the same user are processed sequentially:

$this->onQueue('balance-user-' . ($userId % 10));

%10 creates 10 partitions/queues. You can adjust the number based on system load.

This approach is similar to Kafka partitions, where all messages for a given key (user ID) go to the same partition.

Concurrent jobs for different users can run in parallel, improving throughput while maintaining correct order per user.

We also use DB::transaction() with lockForUpdate() to guarantee safe updates even if multiple jobs for the same user are processed simultaneously.

The lockForUpdate() inside a transaction ensures that only one job can update the user's balance at a time, preventing race conditions even if two jobs are picked up simultaneously.

Part 3: Multi-Tenant Data Isolation

We isolate all data in the app using tenant_id

Task

Write a base Eloquent scope or trait to auto-apply tenant_id in all queries.

The TenantScope automatically adds (app/Traits/TenantScope.php)

WHERE tenant_id = current_user_tenant

to all queries on the model.

This means we no longer need to manually filter by tenant_id in every query.

then in our User model we can write

protected static function booted()
{
    static::addGlobalScope(new TenantScope);
}

The scope works for:

all(), find(), first(), paginate()

Queries with additional conditions (where, orderBy, etc.)

Eager loading (with) of relationships

Task

. Explain how you’d enforce tenant isolation at:

• Controller/service layer
• (Optional) Database level

We enforce tenant isolation at multiple layers to ensure that each tenant only accesses their own data.

1. Controller / Service Layer

Even with the global scope, critical operations are double-checked:

    {
        if ($user->tenant_id !== auth()->user()->tenant_id) {
            abort(403, 'Unauthorized access');
        }

        $user->update($request->only(['name', 'email']));
    } 

Acts as a safety net for updates, deletes, or sensitive actions. Can be applied in service classes as well.

2. Database Level Unique indexes including tenant_id

$table->unique(['tenant_id', 'user_id'], 'uq_tenant_user');

Prevents duplicate or cross-tenant conflicts. Ensures relationships remain tenant-specific.

###Task

3. How would you test for tenant data leaks?

we can write a Test case where we will make sure users from one tenant cannot see or manipulate data from another tenant

public function test_tenant_data_isolated()
{
    $tenantAUser = User::factory()->create(['tenant_id' => 1]);
    $tenantBUser = User::factory()->create(['tenant_id' => 2]);

    $this->actingAs($tenantAUser);

    // Tenant A should only see their data
    $users = User::all();
    $this->assertTrue($users->every(fn($u) => $u->tenant_id === 1));

    // Try to access Tenant B's data directly
    $tenantBData = User::where('tenant_id', 2)->get();
    $this->assertTrue($tenantBData->isEmpty()); // Tenant scope should prevent this
}

we can also login as a user from one tenant Try to manually access URLs/data of another tenant (e.g., /users/10 where 10 belongs to another tenant).

or else we can do one thing we can Encrypt the ID 10 and if we want to use it somewhere in the controller then we can Decrypt it.

for Crypt

/users/{encryptedId}
users/eyJpdiI6IkQzNEg5Mkd

for Hashids

/users/{hashid}
users/xBf5e6

Part 3: Fast Dashboard Aggregation

We want to render a per-branch dashboard with the following metrics:

• Total revenue this month
• Total unpaid invoices
• New users this month
• Session attendance breakdown

Task

Write an optimized query or set of queries using Eloquent/Query Builder to generate this dashboard data.

Controller Example: DashboardController@index

DB-level aggregations: Using DB::table() with selectRaw() avoids N+1 queries and unnecessary model hydration.

Month filtering: Limits scanning to the current month for performance.

Session attendance: Using a DATE column (attended_day) instead of attended_at ensures indexes can be used efficiently.

Indexes recommended:

invoices: (branch_id, created_at)

users: (branch_id, created_at)

session_attendance: (branch_id, attended_day)

Task

Propose a caching strategy

Reduce repeated database queries and improve dashboard load time, especially for high-traffic branches.

• Cache per branch and per month to avoid conflicts: $cacheKey = "dashboard:branch:{$branchId}:month:" . now()->format('Y-m');

• Cache Duration $ttl = now()->addMinutes(5);

• Cache Implementation

Use Redis for fast, in-memory storage.

Wrap the dashboard query in Cache::remember()

$dashboardData = Cache::remember($cacheKey, now()->addMinutes(5), function () use ($branchId) {
    $monthStart = now()->startOfMonth()->startOfDay();
    $nextMonthStart = $monthStart->copy()->addMonth()->startOfDay();
    $monthStartDate = $monthStart->toDateString();
    $nextMonthStartDate = $nextMonthStart->toDateString();

    $invoiceAgg = DB::table('invoices')
        ->where('branch_id', $branchId)
        ->selectRaw('
            COALESCE(SUM(CASE WHEN status = ? AND created_at >= ? AND created_at < ? THEN amount ELSE 0 END), 0) AS totalRevenue,
            SUM(CASE WHEN status = ? THEN 1 ELSE 0 END) AS unpaidInvoices
        ', ['paid', $monthStart, $nextMonthStart, 'unpaid'])
        ->first();

    $totalRevenue = (float) ($invoiceAgg->totalRevenue ?? 0.0);
    $unpaidInvoices = (int) ($invoiceAgg->unpaidInvoices ?? 0);

    $newUsers = DB::table('users')
        ->where('branch_id', $branchId)
        ->where('created_at', '>=', $monthStart)
        ->where('created_at', '<', $nextMonthStart)
        ->count();

    $sessionAttendance = DB::table('session_attendance')
        ->where('branch_id', $branchId)
        ->where('attended_day', '>=', $monthStartDate)
        ->where('attended_day', '<', $nextMonthStartDate)
        ->select('attended_day as date', DB::raw('COUNT(*) as total'))
        ->groupBy('attended_day')
        ->orderBy('attended_day')
        ->get();

    return [
        'totalRevenue' => $totalRevenue,
        'unpaidInvoices' => $unpaidInvoices,
        'newUsers' => $newUsers,
        'sessionAttendance' => $sessionAttendance,
    ];
});

• Cache Invalidation

Let the cache expire every 5–10 minutes

Cache::forget("dashboard:branch:{$branchId}:month:" . now()->format('Y-m'));

Task

Suggest a solution to support 100+ branches accessing this concurrently.

• Branch-specific cache

  Each branch has its own cache key

  (dashboard:branch:{branchId}:month:{YYYY-MM})

  so requests don’t conflict.

• Clear the cache when invoices, users, or session attendances are updated.

• Use in-memory caching for fast access and high concurrency (Redis)

• Database Optimization

  • Indexing
  • Partitioning

• Horizontal Scaling

  • Multiple web servers behind a load balancer to handle concurrent requests
  • Database replication: Read replicas can serve analytics queries to reduce load on the primary database.

• Pre-aggregated tables or materialized views

• Lazy-load session attendance data if the dataset is huge (e.g., last 30 days only)

• Use pagination or charts with pre-aggregated intervals to reduce payload size

Bonus: Migration & Infrastructure Notes

• Laravel Version Upgrades

Laravel 12.30 for latest features, Advanced Query Builder, Eager loading now detects N+1 queries,AI-Powered Debugging Assistant and improved caching support.

Performance Optimizations:

Asynchronous cache handling improves response time under heavy loads

Advanced memory management for efficient processing

• MySQL Optimizations

Added composite indexes for commonly filtered columns:

users(branch_id, created_at)

invoices(branch_id, status, created_at)

session_attendance(branch_id, attended_day)

Used lockForUpdate() in transactions to handle concurrent updates safely.

Applied conditional aggregation queries for dashboard metrics to minimize table scans.

• Multi-Region / Multi-Tenant Setup

Implemented tenant isolation using tenant_id and a global scope trait.

Ensured each tenant’s data is strictly scoped at the query and model level.