Day 4 - Generic Netlify Edge Function for Supabase Table CRUD

This post is part of the 'The 12 Days of Full Stack Dev' series, an adventure through full stack fintech app development. Join me through the first 12 days of December as we develop a variety of new features, fixes, and components within the Full Stack Craft fintech family. For the full list of days and topics in this series, see the original post: The 12 Days of Full Stack Dev.

Reusability is Always Useful

For too long now, I've been building hard coded Netlify Edge Functions to interact with Supabase tables. Each function was tailored to a specific table and operation, leading to a lot of repetitive code and maintenance headaches. Today, I decided to change that by creating a generic Netlify Edge Function that can handle CRUD operations for any Supabase table.

Just for review, CRUD stands for Create, Read, Update, and Delete - the four basic operations we typically need to perform with database records.

Security is Also a Concern

Now, we can't make this too generic, as that would present a security risk - people just passing in whatever queries to whatever tables they wanted. However, for tables that have the user's email in them, we can rely on the authentication state within the Netlify Edge Function itself that we are creating, reading, updating, or deleting records for the authenticated user only. We can also include a whitelist of tables that this function is allowed to operate on. As far as I can tell from various probing, this is a pretty secure serverless function:

import { Handler } from '@netlify/functions'
import { getUserData } from './utils/getUserData'
import UserRole from './enums/UserState'
import { fscfSupabase } from './services/supabase'

interface ICRUDRequest {
  action: 'CREATE' | 'READ' | 'UPDATE' | 'DELETE' // the CRUD action to perform
  table: string // the Supabase table to perform the action on
  data?: any // the data to be used for create or update actions
  id?: string // the id used for either update or delete actions
}

const tableWhitelist = ['wheelscreener_dashboard_configs', 'leapsscreener_dashboard_configs', 'optionscreener_dashboard_configs']

const handler: Handler = async (event, context) => {
  if (!event.body) {
    return {
      statusCode: 400,
      body: 'No body'
    }
  }
  const body: ICRUDRequest = JSON.parse(event.body)
  const { action, table, data, id } = body

  if (!tableWhitelist.includes(table)) {
    return {
      statusCode: 400,
      body: JSON.stringify({ success: false, error: 'Table not allowed' })
    }
  }

  const { user } = context.clientContext as any
  const { userState } = getUserData(user)
  const { email } = user

  if (!email) {
    return {
      statusCode: 401,
      body: JSON.stringify({ success: false, error: 'Unauthorized - no email found' })
    }
  }

  // Create - create an entry in the table with the users's email
  if (action === 'CREATE') {
    const { error } = await fscfSupabase.from(table).insert({ ...data, email })
    if (error) {
      return {
        statusCode: 500,
        body: JSON.stringify({ success: false, error: error.message })
      }
    }
    return {
      statusCode: 200,
      body: JSON.stringify({ success: true })
    }
  }

  // Read - read all entries from the table that match the user's email
  if (action === 'READ') {
    // subscription is available to any user with an account (free and paying)
    if (userState !== UserRole.PUBLIC) {
      const { data: readData, error } = await fscfSupabase.from(table).select('*').eq('email', email)
      if (error) {
        return {
          statusCode: 500,
          body: JSON.stringify({ success: false, error: error.message })
        }
      }
      return {
        statusCode: 200,
        body: JSON.stringify(readData)
      }
    }

    // unauthorized - return empty array
    return {
      statusCode: 401,
      body: JSON.stringify([])
    }
  }

  // Update - update an entry in the table that matches both the user's email and the query ID
  if (action === 'UPDATE') {
    // use email of user updating the entry
    const { error } = await fscfSupabase.from(table).update({ ...data }).eq('email', email).eq('id', id)
    if (error) {
      return {
        statusCode: 500,
        body: JSON.stringify({ success: false, error: error.message })
      }
    }
    return {
      statusCode: 200,
      body: JSON.stringify({ success: true })
    }
  }

  // Delete - delete an entry from the table that matches both the user's email and the query ID
  if (action === 'DELETE') {
    const { error } = await fscfSupabase.from(table).delete().eq('email', email).eq('id', id)
    if (error) {
      return {
        statusCode: 500,
        body: JSON.stringify({ success: false, error: error.message })
      }
    }
    return {
      statusCode: 200,
      body: JSON.stringify({ success: true })
    }
  }

  // unauthorized - return empty array
  return {
    statusCode: 401,
    body: JSON.stringify([])
  }
}
export { handler }

Note of course such a function would only work with tables according to this minimum schema shape:

CREATE TABLE user_data (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT NOT NULL,
    -- more columns here, which could be typed (or not) in ICRUDRequest --
)

Thanks!

Thanks for stopping by and see you for Day 5, where I'll be using this new Netlify Edge Function to store custom table layouts in both The Wheel Screener and LEAPS Screener!