Backend Style Guide

This guide outlines the coding standards, conventions, and best practices for our backend development using Supabase and related technologies.

Technology Stack

Primary Backend Platform

Supabase as our primary backend-as-a-service platform
PostgreSQL for database operations and storage
Supabase Edge Functions for custom server-side logic (Deno/TypeScript)
Row Level Security (RLS) for data access control

Supporting Technologies

TypeScript for Edge Functions and type safety
SQL for database schema design and complex queries
PostgREST (built into Supabase) for automatic API generation
Realtime subscriptions for live data updates

Database Design Standards

Schema Conventions

Use snake_case for table names and column names: user_profiles, created_at
Include primary keys with meaningful names: id (UUID) or {table}_id
Use timestamps for audit trails: created_at, updated_at
Implement soft deletes with deleted_at when appropriate

Table Structure

CREATE TABLE user_profiles (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  display_name TEXT NOT NULL,
  avatar_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Foreign Key Relationships

Use meaningful foreign key names: lodge_id, parent_id, child_id
Implement CASCADE DELETE where appropriate for data integrity
Use UUID primary keys for better security and scalability

Row Level Security (RLS) Policies

Security Best Practices

Enable RLS on all tables containing sensitive data
Write granular policies for different user roles and scenarios
Use authenticated() and auth.uid() functions for user identification
Test policies thoroughly in development environment

Policy Examples

-- Users can only access their own profile
CREATE POLICY "Users can view own profile" ON user_profiles
  FOR SELECT USING (auth.uid() = user_id);
 
-- Lodge members can view lodge data
CREATE POLICY "Lodge members can view lodge data" ON lodges
  FOR SELECT USING (
    auth.uid() IN (
      SELECT user_id FROM lodge_members 
      WHERE lodge_id = lodges.id
    )
  );

Edge Functions Development

Function Structure

Use TypeScript for all Edge Functions
Follow RESTful conventions for HTTP endpoints
Implement proper error handling and response formatting
Use environment variables for configuration

Function Template

import { serve } from "https://deno.land/std@0.168.0/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";
 
serve(async (req) => {
  try {
    // Validate request method
    if (req.method !== 'POST') {
      return new Response('Method not allowed', { status: 405 });
    }
 
    // Initialize Supabase client
    const supabase = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
    );
 
    // Function logic here
    const result = await processRequest(req, supabase);
 
    return new Response(JSON.stringify(result), {
      headers: { 'Content-Type': 'application/json' },
      status: 200,
    });
 
  } catch (error) {
    return new Response(JSON.stringify({ error: error.message }), {
      headers: { 'Content-Type': 'application/json' },
      status: 500,
    });
  }
});

API Design Standards

RESTful Conventions

Use HTTP verbs appropriately: GET, POST, PUT, DELETE
Design resource-based URLs: /api/lodges/{id}/members
Return appropriate HTTP status codes
Include proper error messages in response body

Response Formatting

// Success response
{
  data: { /* response data */ },
  message: "Operation completed successfully"
}
 
// Error response
{
  error: "Detailed error message",
  code: "ERROR_CODE",
  details: { /* additional error context */ }
}

Real-time Features

Subscription Patterns

Use Supabase Realtime for live data updates
Design efficient subscription queries to minimize overhead
Implement proper cleanup for subscriptions
Handle connection states gracefully in client applications

Channel Naming

Use descriptive channel names: lodge:{lodge_id}:messages
Include resource identifiers for targeted updates
Follow consistent naming patterns across the application

Data Validation and Security

Input Validation

Validate all inputs at the database level using constraints
Use CHECK constraints for business logic validation
Implement custom validation functions for complex rules
Sanitize inputs to prevent SQL injection

Authentication and Authorization

Leverage Supabase Auth for user management
Implement role-based access control using RLS policies
Use JWT tokens for secure client-server communication
Never expose sensitive data in client-accessible queries

Performance Optimization

Database Performance

Create appropriate indexes for frequently queried columns
Use query optimization techniques for complex operations
Implement pagination for large data sets
Monitor query performance and optimize slow queries

Caching Strategies

Use Supabase caching for frequently accessed data
Implement application-level caching when appropriate
Cache static data that changes infrequently
Invalidate caches appropriately when data changes

Migration and Deployment

Schema Migrations

Use Supabase CLI for managing database migrations
Write reversible migrations when possible
Test migrations in development environment first
Document migration steps and potential impacts

Environment Management

Use environment variables for configuration
Maintain separate environments for development, staging, and production
Never commit secrets to version control
Use Supabase environment management features

Monitoring and Logging

Error Tracking

Implement comprehensive error logging in Edge Functions
Use structured logging for better analysis
Monitor database performance and query patterns
Set up alerts for critical errors and performance issues

Analytics and Metrics

Track API usage patterns and performance metrics
Monitor database connection usage and query performance
Implement business metrics tracking for key features
Use Supabase Dashboard for real-time monitoring

Best Practices

Code Organization

Separate concerns between database logic and business logic
Use consistent naming conventions across all backend components
Document complex queries and business logic
Version control all database schema and function changes

Security Considerations

Principle of least privilege for all database access
Regular security audits of RLS policies and permissions
Secure handling of sensitive data and PII
Compliance with data protection regulations (GDPR, CCPA)

Testing

Write unit tests for Edge Functions
Test RLS policies thoroughly with different user scenarios
Implement integration tests for complex workflows
Use test databases for development and testing

Frontend Style Guide Docs Style Guide