Building a Mobile Service Platform

Introduction

For many service-based small businesses, operations are managed using a mix of paper ledgers and fragmented spreadsheets. When a local mobile repair shop asked me to digitize their operations, I chose to build a serverless SaaS dashboard.

By combining a React SPA with a Supabase (PostgreSQL) database, I built a system capable of tracking repair tickets, auditing financial ledgers, and triggering warnings when inventory stock drops below minimum thresholds. In this article, I will explain how to set up this architecture, configure Row Level Security (RLS) policies, and write database-level triggers to handle transactional operations securely.

The Backend-less Architecture

Historically, full-stack SaaS builds required setting up a backend server to handle API routes, database sessions, and input validation. This increases deployment overhead and hosting costs.

By utilizing Supabase, we can connect the React client directly to the database. The client queries tables using the Supabase client SDK. However, this approach exposes the database credentials to the browser, making security a primary concern. To prevent unauthorized access, we implement Row Level Security (RLS) at the database layer. RLS validates the caller's JSON Web Token (JWT) on every query, ensuring users can only read or write data they are authorized to access.

Row Level Security (RLS) In Practice

To secure the database, we define policies on each table. For example, in a repair shop CRM, only authenticated staff members should be allowed to modify repair tickets, while generic customers should have no read or write access.

Below is the SQL schema definition to enable RLS and configure policies:

-- Enable Row Level Security on the tickets table
ALTER TABLE tickets ENABLE ROW LEVEL SECURITY;

-- Create a policy allowing authenticated staff to select ticket records
CREATE POLICY "Allow authenticated staff read access"
ON tickets
FOR SELECT
TO authenticated
USING (auth.jwt() ->> 'role' = 'authenticated');

-- Create a policy allowing authenticated staff to insert ticket records
CREATE POLICY "Allow authenticated staff write access"
ON tickets
FOR INSERT
TO authenticated
WITH CHECK (auth.jwt() ->> 'role' = 'authenticated');

Managing Inventory via PostgreSQL Triggers

When a technician assigns a screen replacement part to a ticket, we need to subtract one unit from our inventory stock. Performing this operation in the client browser introduces risks: if the user closes their browser mid-update, the ticket is created but the stock count is not updated, leading to inventory discrepancies.

To resolve this, we implement PostgreSQL triggers. When a new ticket record is created or updated with linked parts, the database engine automatically updates the inventory count in a single database transaction.

Below is the trigger implementation:

-- Function to decrement part count from inventory
CREATE OR REPLACE FUNCTION decrement_part_inventory()
RETURNS TRIGGER AS $$
BEGIN
  -- Check if a part is associated with the ticket and decrement stock
  IF NEW.part_id IS NOT NULL AND (OLD.part_id IS NULL OR NEW.part_id != OLD.part_id) THEN
    UPDATE inventory 
    SET stock_count = stock_count - 1 
    WHERE id = NEW.part_id;
    
    -- Raise an exception if stock count goes negative
    IF (SELECT stock_count FROM inventory WHERE id = NEW.part_id) < 0 THEN
      RAISE EXCEPTION 'Insufficient inventory stock for the selected part.';
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Bind function to the tickets table
CREATE TRIGGER tr_decrement_part_inventory
AFTER INSERT OR UPDATE ON tickets
FOR EACH ROW
EXECUTE FUNCTION decrement_part_inventory();

Frontend State Synchronization

In a multi-user environment (where different cashiers and technicians access the dashboard concurrently), the UI must update in real-time as tickets change status.

Supabase uses PostgreSQL replication slots to stream database changes to clients via WebSockets. In the React client, we subscribe to table events to update the UI state automatically:

import { useEffect, useState } from 'react';
import { supabase } from '../supabaseClient';

export function useTickets() {
  const [tickets, setTickets] = useState([]);

  useEffect(() => {
    // Fetch initial tickets
    const fetchTickets = async () => {
      const { data } = await supabase.from('tickets').select('*');
      if (data) setTickets(data);
    };
    fetchTickets();

    // Subscribe to real-time changes
    const subscription = supabase
      .channel('realtime_tickets')
      .on('postgres_changes', { event: '*', schema: 'public', table: 'tickets' }, (payload) => {
        if (payload.eventType === 'INSERT') {
          setTickets((prev) => [payload.new, ...prev]);
        } else if (payload.eventType === 'UPDATE') {
          setTickets((prev) => prev.map((t) => t.id === payload.new.id ? payload.new : t));
        }
      })
      .subscribe();

    return () => {
      supabase.removeChannel(subscription);
    };
  }, []);

  return tickets;
}

Double-Entry Ledger Architecture

To track revenue accurately, we avoid updating a single revenue column. Instead, we use an audit ledger table:

• Ledger Entries: Every payment event (advance payments, parts costs, final settlements) is written as an immutable row.
• Audit Logs: To change a balance, we append a compensating entry rather than updating existing rows. This prevents unauthorized ledger modifications and ensures an audit log of all transactions.

Conclusion

Building this platform demonstrated that serverless architectures can securely handle business-critical operations. Shifting validation and automation logic to database-level constraints (like triggers and RLS) ensures data integrity while simplifying the frontend client design.