PostgREST Integration Guide

🎯 Overview

NVLP provides two API interfaces for different use cases:

✅ PostgREST is ideal for:

• Simple CRUD operations (Create, Read, Update, Delete)
• Filtering and querying data
• Bulk operations
• High-performance read operations
• Complex queries with relationships

❌ Use Edge Functions for:

• Complex transaction creation with validation
• Business logic enforcement
• Multi-table operations with constraints
• Balance calculations and updates

🚀 Quick Start

1. Environment Setup

# Environment variables required
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_ANON_KEY=your-anon-key

2. Basic Configuration

// Direct fetch approach
const POSTGREST_URL = `${process.env.SUPABASE_URL}/rest/v1`;
const headers = {
  'apikey': process.env.SUPABASE_ANON_KEY,
  'Authorization': `Bearer ${userToken}`,
  'Content-Type': 'application/json',
  'Accept': 'application/json'
};

3. Simple Query Example

// Get all active budgets
const response = await fetch(`${POSTGREST_URL}/budgets?is_active=eq.true&order=created_at.desc`, {
  headers
});
const budgets = await response.json();

🔐 Authentication Setup

Token-Based Authentication

PostgREST requires a valid JWT token for all authenticated requests:

// Get token from Supabase auth
const { data: { session } } = await supabaseClient.auth.getSession();
const token = session?.access_token;

// Include in all requests
const headers = {
  'apikey': process.env.SUPABASE_ANON_KEY,
  'Authorization': `Bearer ${token}`,
  'Content-Profile': 'public',
  'Accept-Profile': 'public'
};

Automatic Token Refresh

async function getValidToken() {
  let session = await supabaseClient.auth.getSession();
  
  // Check if token is about to expire
  if (isTokenExpiring(session.data.session?.access_token)) {
    const { data } = await supabaseClient.auth.refreshSession();
    session = data;
  }
  
  return session.session?.access_token;
}

function isTokenExpiring(token) {
  if (!token) return true;
  
  try {
    const payload = JSON.parse(atob(token.split('.')[1]));
    const expiryTime = payload.exp * 1000;
    const bufferMs = 5 * 60 * 1000; // 5 minute buffer
    return Date.now() > (expiryTime - bufferMs);
  } catch {
    return true;
  }
}

🛠 Client Integration Options

Option 1: NVLP Unified Client (Recommended)

The unified client provides a fluent query builder interface with automatic authentication:

import { createNVLPClient, SupabaseSessionProvider } from '@nvlp/client';
import { createClient } from '@supabase/supabase-js';

// Setup
const supabaseClient = createClient(supabaseUrl, supabaseAnonKey);
const sessionProvider = new SupabaseSessionProvider(supabaseClient);

const nvlp = createNVLPClient({
  supabaseUrl,
  supabaseAnonKey,
  sessionProvider,
});

// Usage examples
const budgets = await nvlp.budgets
  .select('*')
  .eq('is_active', true)
  .order('created_at', false)
  .get();

const envelope = await nvlp.envelopes
  .select('*, category:categories(*)')
  .eq('id', envelopeId)
  .single();

Option 2: Direct Fetch with Helper Functions

class PostgRESTClient {
  constructor(baseUrl, apiKey) {
    this.baseUrl = baseUrl;
    this.apiKey = apiKey;
    this.token = null;
  }

  setToken(token) {
    this.token = token;
  }

  getHeaders(additional = {}) {
    return {
      'apikey': this.apiKey,
      'Authorization': `Bearer ${this.token}`,
      'Content-Type': 'application/json',
      'Accept': 'application/json',
      ...additional
    };
  }

  async query(table, params = {}) {
    const url = new URL(`${this.baseUrl}/${table}`);
    Object.entries(params).forEach(([key, value]) => {
      url.searchParams.append(key, value);
    });

    const response = await fetch(url, {
      headers: this.getHeaders()
    });

    if (!response.ok) {
      throw new Error(`PostgREST query failed: ${response.status} ${response.statusText}`);
    }

    return response.json();
  }
}

🔍 Query Builder Usage

Basic Filtering

// Equality
await nvlp.budgets.eq('is_active', true).get();

// Comparison operators
await nvlp.envelopes.gt('current_balance', 0).get();
await nvlp.transactions.gte('transaction_date', '2025-01-01').get();
await nvlp.envelopes.lt('current_balance', 0).get();

// Pattern matching
await nvlp.payees.like('name', '*grocery*').get();
await nvlp.categories.ilike('name', '*FOOD*').get(); // Case-insensitive

// List membership
await nvlp.envelopes.in('envelope_type', ['regular', 'savings']).get();

// Null checks
await nvlp.categories.isNull('parent_id').get(); // Top-level categories

Complex Filtering

// OR conditions
await nvlp.transactions
  .or('transaction_type.eq.income,transaction_type.eq.allocation')
  .get();

// Complex OR with AND
await nvlp.transactions
  .eq('budget_id', budgetId)
  .or('(from_envelope_id.eq.' + envelopeId + ',to_envelope_id.eq.' + envelopeId + ')')
  .get();

Selecting Columns and Relationships

// Select specific columns
await nvlp.budgets
  .select('id,name,description')
  .get();

// Select with relationships
await nvlp.envelopes
  .select('*, category:categories(name), budget:budgets(name)')
  .eq('budget_id', budgetId)
  .get();

📚 Common Patterns

1. Budget Data Loading

async function loadBudgetData(budgetId) {
  // Load all budget-related data in parallel
  const [budget, categories, envelopes, payees, incomeSources] = await Promise.all([
    nvlp.budgets.eq('id', budgetId).single(),
    nvlp.categories.eq('budget_id', budgetId).order('display_order').get(),
    nvlp.envelopes.eq('budget_id', budgetId).eq('is_active', true).order('display_order').get(),
    nvlp.payees.eq('budget_id', budgetId).eq('is_active', true).order('name').get(),
    nvlp.incomeSources.eq('budget_id', budgetId).eq('is_active', true).order('name').get()
  ]);

  return { budget, categories, envelopes, payees, incomeSources };
}

2. Transaction History with Details

async function getTransactionHistory(budgetId, limit = 50, offset = 0) {
  return await nvlp.transactions
    .select(`
      *,
      from_envelope:envelopes!from_envelope_id(name),
      to_envelope:envelopes!to_envelope_id(name),
      payee:payees(name),
      income_source:income_sources(name),
      category:categories(name)
    `)
    .eq('budget_id', budgetId)
    .eq('is_deleted', false)
    .order('transaction_date', false)
    .order('created_at', false)
    .limit(limit)
    .offset(offset)
    .get();
}

3. Envelope Balance Monitoring

async function getNegativeBalanceEnvelopes(budgetId) {
  return await nvlp.envelopes
    .select('*, category:categories(name)')
    .eq('budget_id', budgetId)
    .eq('is_active', true)
    .lt('current_balance', 0)
    .order('current_balance') // Worst first
    .get();
}

⚡ Performance Optimization

1. Use Indexes Effectively

Always include these filters when possible:

// Budget-scoped queries (uses budget_id indexes)
await nvlp.envelopes.eq('budget_id', budgetId).get();

// Active record filters (uses partial indexes)
await nvlp.envelopes.eq('is_active', true).get();
await nvlp.transactions.eq('is_deleted', false).get();

// Date range queries (uses btree indexes)
await nvlp.transactions
  .gte('transaction_date', '2025-01-01')
  .lte('transaction_date', '2025-01-31')
  .get();

2. Optimize Column Selection

await nvlp.transactions.select('*').get();

await nvlp.transactions
  .select('id,amount,description,transaction_date,transaction_type')
  .get();

🚨 Error Handling

HTTP Status Codes

async function handlePostgRESTRequest(operation) {
  try {
    const result = await operation();
    return { data: result, error: null };
  } catch (error) {
    switch (error.status || error.response?.status) {
      case 400:
        throw new Error(`Bad Request: Invalid query parameters`);
      case 401:
        throw new Error('Unauthorized: Please log in again');
      case 403:
        throw new Error('Forbidden: You don\'t have access to this resource');
      case 404:
        throw new Error('Not Found: Resource doesn\'t exist');
      case 409:
        throw new Error(`Conflict: Constraint violation`);
      default:
        throw new Error(`PostgREST Error: ${error.message}`);
    }
  }
}

Retry Logic with Exponential Backoff

async function withRetry(operation, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await operation();
    } catch (error) {
      // Handle auth errors with refresh
      if (error.status === 401 && attempt < maxRetries) {
        const shouldRetry = await handleRLSError(error);
        if (shouldRetry) {
          continue;
        }
      }
      
      // Handle temporary errors
      if ([408, 429, 500, 502, 503, 504].includes(error.status) && attempt < maxRetries) {
        const delay = Math.min(1000 * Math.pow(2, attempt - 1), 10000);
        await new Promise(resolve => setTimeout(resolve, delay));
        continue;
      }
      
      throw error;
    }
  }
}

🧪 Testing

Unit Testing PostgREST Operations

// Mock PostgREST client for testing
class MockPostgRESTClient {
  constructor(mockData = {}) {
    this.mockData = mockData;
    this.operations = [];
  }

  from(table) {
    return new MockQueryBuilder(table, this.mockData[table] || [], this.operations);
  }
}

// Test example
describe('Budget Operations', () => {
  let mockClient;

  beforeEach(() => {
    mockClient = new MockPostgRESTClient({
      budgets: [
        { id: '1', name: 'Test Budget', is_active: true },
        { id: '2', name: 'Inactive Budget', is_active: false }
      ]
    });
  });

  test('should get active budgets only', async () => {
    const budgets = await mockClient.from('budgets')
      .eq('is_active', true)
      .get();

    expect(budgets).toHaveLength(1);
    expect(budgets[0].name).toBe('Test Budget');
  });
});

🏭 Production Considerations

1. Connection Management

Use connection pooling for high-traffic applications:

// Custom fetch with pooling (using undici or similar)
import { Agent } from 'undici';

const agent = new Agent({
  connections: 100,
  keepAliveTimeout: 10000,
  keepAliveMaxTimeout: 10000
});

const customFetch = (url, options) => {
  return fetch(url, {
    ...options,
    dispatcher: agent
  });
};

2. Security Best Practices

// Input validation and sanitization
function sanitizePostgRESTInput(input) {
  if (typeof input === 'string') {
    // Prevent injection attacks in PostgREST filters
    return input.replace(/[;'"\\]/g, '');
  }
  return input;
}

// Request timeout to prevent hanging requests
const REQUEST_TIMEOUT = 30000; // 30 seconds

function withTimeout(promise, timeoutMs = REQUEST_TIMEOUT) {
  return Promise.race([
    promise,
    new Promise((_, reject) => 
      setTimeout(() => reject(new Error('Request timeout')), timeoutMs)
    )
  ]);
}