supabase/README.md

Supabase Backend Configuration

This directory contains all the configuration, schema, security policies, and serverless functions for the Field Service Management (FSM) platform backend, utilizing the Supabase ecosystem.

1. Why Supabase for this Project?

The choice of Supabase is driven directly by the project's constraints (zero dedicated backend budget, limited time) and core functional requirements (mobile-first, real-time, location-based).

Project Requirement	Supabase Solution	Benefit for the MVP
Rapid MVP Deployment	Unified Platform (Database, Auth, Storage)	Allows for "Vibecoding" and direct integration with the React/Vite frontend, saving weeks of setup time.
Multi-Tenancy Security	Row-Level Security (RLS)	Secures the data at the database level, ensuring that one Tenant (e.g., Airtel) cannot view another's data, even with an exposed public key.
Location-Based Queries	PostgreSQL + PostGIS	Enables performant spatial queries (e.g., "Find jobs within 5km of the agent") which is essential for the self-assignment job board.
Mobile Evidence	Supabase Storage (S3)	Provides a simple, secure way for Field Agents to upload large files (photos of installations, expense receipts) with defined access policies.
Real-Time Visibility	Supabase Realtime	Allows the Admin Dashboard to instantly update when a Field Agent changes a Job Status on their PWA, eliminating the "30-45 minute delay" bottleneck.
Unauthenticated Tasks	Supabase Edge Functions	Provides a secure, minimal layer to handle unauthenticated public tasks (like the Customer Portal Lookup) without requiring a full, costly Next.js/Node.js server.

2. Directory Structure

The structure follows standard practices for the Supabase Command Line Interface (CLI):

supabase/
├── config.toml           # Main project configuration (ports, linked ID)
├── migrations/           # Database schema changes (.sql files)
│   └── 20251030000000_initial_schema.sql  # The initial schema (includes all tables/RLS)
├── functions/            # Serverless Edge Functions (Written in TypeScript)
│   └── customer-lookup/  # Edge Function for the Customer Portal
├── seed.sql              # SQL script to run after reset/initial deployment (initial tenants/users)

3. Local Development Setup

To manage and develop the database locally, you need the Supabase CLI.

1. Install Supabase CLI:

   # npm install -g supabase
   

2. Start Local Services: This command will spin up the local PostgreSQL database, Auth, Storage, and the Edge Function runtime in Docker containers.

   supabase start
   

   (Note: The DB runs on port 54322, Studio runs on 54323 by default).

3. Access Local Studio: Once running, navigate to http://localhost:54323 to browse your local database, view logs, and test RLS policies.

4. Database Management & Migrations

All changes to the database schema (tables, enums, functions, RLS policies) must be managed through migrations to maintain consistency between local, staging, and production environments.

1. Link to Remote Project (First Time Only): If you are setting up the remote environment:

   supabase login
   supabase link --project-ref <YOUR_PROJECT_ID>
   

2. Generate a New Migration File: When you make a change to the local DB (e.g., via the local Studio), run this command to generate a new .sql file reflecting those changes:

   supabase migration new <a_descriptive_name_for_change>
   

3. Apply Migrations (Remote Deployment): To apply all new migrations to your remote Supabase project:

   supabase db push
   

5. Security Note: Row-Level Security (RLS) is Paramount

Due to the decision to use a direct Frontend-to-Supabase pipeline:

1. NEVER Expose the Service Role Key (SRK): The SRK bypasses all security. It must not be committed to the repo or used in any client-side code.
2. RLS is the Firewall: All tables must have RLS enabled. The policies defined in the migrations/ files (e.g., job_access and agent_update_job_status) are the sole source of access control and business logic security.
3. Edge Functions for Public Tasks: The functions/customer-lookup/ Edge Function is mandatory to securely handle the unauthenticated Customer Portal without exposing all customer data.