Mobile Service Manager Case Study | Repair Shop SaaS

Overview

The Mobile Service Management System is a specialized operational dashboard and CRM built for a busy brick-and-mortar mobile repair shop. The shop struggled with tracking incoming customer devices, managing sparse part inventories, identifying low-stock parts, and audit-logging advance and pending payments.

The platform is built as a single-tenant SaaS application, utilizing a React frontend bundle compiled via Vite and served from Vercel. All database operations, authentication routes, files storage, and business logic triggers are managed directly through Supabase (PostgreSQL), utilizing row-level security (RLS) and custom database functions to handle data integrity and logging.

Problem Statement

Before implementing this system, the shop managed its daily operations using paper tickets and spreadsheets. This process introduced several business pain points:

Ticket Tracking: There was no centralized view of repairs. Technicians had to look through paper slips to determine if a screen replacement or motherboard repair had been finished, causing delay and communication gaps.
Inventory Blind Spots: Spare parts (like specific phone screens, charging ports, and batteries) would run out without warning, halting repairs and forcing customers to wait for new shipments.
Financial Discrepancies: Repair jobs often involve advance deposits, parts costs, and pending balances. Paper records failed to consistently audit these transactions, leading to direct revenue losses.

The Solution

The Mobile Service Manager digitizes the entire operation. When a customer walks in, the service desk worker inputs their details and generates a repair ticket. This assigns a technician, records the initial fault description, notes the advance deposit, and prints or emails a receipt.

The application dynamically updates as the repair progresses. Technicians check off milestones (e.g., "Diagnosed", "Parts Allocated", "Testing", "Completed"). If a repair requires a part, the system checks inventory and subtracts the unit. If the stock falls below a threshold, the dashboard flashes a warning. When the repair is finished, the system calculates the final balance, records the payment method, and archives the ticket.

System Architecture

The architecture utilizes serverless database integrations to eliminate backend maintenance overhead while keeping security constraints tight.

Rather than introducing a separate Node.js or Python backend layer to route database requests, the React client communicates directly with the Supabase REST API via the client library. To secure this directly-exposed endpoint, we configured PostgreSQL Row Level Security (RLS). Under RLS, requests from unauthorized clients or different user roles are rejected directly at the database engine level, regardless of the API endpoint structure.

Here is a block diagram of the setup:

[ React SPA Frontend (Vite + Vercel) ]
                  │
                  ▼  (HTTPS Queries with JWT auth token)
        [ Supabase API Gateway ]
                  │
                  ▼
      [ PostgreSQL DB Engine ]
         ├── RLS Policies (Auth check)
         ├── Database Tables (Tickets, Inventory, Ledger)
         └── DB Triggers (Low-stock checks, audit logs)

Triggers are implemented in PL/pgSQL to handle database-level computations, like automatically subtracting items from inventory when tickets are updated, or logging payment events to an immutable general ledger.

Tech Stack In-Depth

Frontend SPA: React 18, Vite, and React Router. Vite handles asset building, creating highly optimized chunks that load immediately on mobile tablets used by the shop.
State & Fetching: React hooks and local states managing state synchronization.
Database: PostgreSQL hosted on Supabase, leveraging relational structures, custom schemas, and indexes on customer phone numbers and ticket IDs to ensure rapid queries.
Security: Supabase Auth using Email/Password authentication. All tables use strict Row Level Security (RLS) to restrict access to authenticated staff only.
Hosting: Vercel for continuous integration and static deployments.

Core Features

1. Repair Ticket Life Cycle

The ticketing system tracks every stage of a repair. It logs diagnostic findings, estimated completion times, assigned technicians, and custom notes. Tickets are searchable by customer name, phone number, device brand, or ID.

2. Inventory & Low Stock Alerts

The inventory table holds details on spare parts, including purchase prices, selling prices, counts, and minimum stock threshold values. If a technician links a part to a ticket, the count drops. If it hits the threshold, the system flags the part for reorder.

3. Advance and Pending Payments

To prevent cash leaks, the app enforces double-entry audit records. Every ticket is linked to a ledger table. Whenever a payment is made (advance, final, or parts cost), it adds a new row referencing the transaction ID, date, and cashier.

4. Operations Analytics

The analytics panel aggregates ledger data to display daily revenue, parts expenses, net profit, outstanding balances, and technician performance metrics.

Engineering Challenges & Solutions

Challenge 1: Securing Database Updates Directly from the Client

Without an intermediary backend API to sanitize inputs, a user could manipulate their local JavaScript payload to update database fields, such as modifying their target user roles, or marking a ticket as paid without logging a transaction.

Solution: We implemented database-level validation rules and Row Level Security (RLS). For instance, the tickets table was protected with a policy that only allows updates to the is_paid boolean if a matching row is simultaneously created in the ledger table. We achieved this by routing updates through a PostgreSQL database trigger function that validates balance constraints before committing the transaction:

-- PostgreSQL trigger function to enforce payment integrity
CREATE OR REPLACE FUNCTION validate_ticket_payment()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.is_paid = TRUE AND OLD.is_paid = FALSE THEN
    -- Check if a ledger entry exists for the ticket
    IF NOT EXISTS (
      SELECT 1 FROM ledger 
      WHERE ticket_id = NEW.id AND type = 'payment_final'
    ) THEN
      RAISE EXCEPTION 'Cannot mark ticket as paid without a corresponding final payment ledger entry.';
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Challenge 2: Multi-User Race Conditions

In a busy repair shop, multiple cashiers and technicians access the system simultaneously. If two cashiers attempt to sell the same spare screen, a race condition could result in negative inventory counts.

Solution: We implemented database transaction locks. When updating inventory counts, the Supabase client calls a custom RPC function that locks the specific row using the SELECT ... FOR UPDATE query, guaranteeing that inventory updates are processed sequentially.

Implementation Details

To establish clean connections, we defined unified schemas inside Supabase. Tables were structured as follows:

customers: id (UUID), name (TEXT), phone (TEXT, indexed), created_at (TIMESTAMPTZ)
tickets: id (UUID), customer_id (UUID), device_model (TEXT), status (TEXT), price_estimate (NUMERIC), advance_paid (NUMERIC), is_paid (BOOLEAN)
inventory: id (UUID), part_name (TEXT), stock_count (INTEGER), min_threshold (INTEGER), selling_price (NUMERIC)
ledger: id (UUID), ticket_id (UUID), amount (NUMERIC), type (TEXT), cashier_id (UUID), created_at (TIMESTAMPTZ)

Results & Impact

Since its deployment in November, the Mobile Service Manager has processed:

Over 350 successful repair tickets.
Zero payment discrepancies due to automated audit logs.
A 40% reduction in part stockouts, thanks to the automated warning flag system on the dashboard.

Lessons Learned

By building this system, I realized that serverless platforms like Supabase are fully capable of hosting business SaaS logic when paired with PostgreSQL triggers and RLS policies. It shifts processing from server administration to database architecture, making applications easier to manage and deploy.

Future Improvements

The next step for this CRM is to build automated WhatsApp integration alerts to notify customers as soon as their tickets transition to "Ready for Pickup", reducing the delay between repair completion and payment collections.

Mobile Service Manager: Digitizing Repair Shop Operations