Supabase — New API Endpoint Checklist

1. Before You Build

What does this endpoint do? Stated clearly in one sentence
Who calls this? Frontend user role, another service, or webhook
What data does it read or write?
Is this a read, write, delete, or multi-step operation?
Does this already exist as a direct table query, RPC, or Edge Function?
Does this need a new table, or does it use existing tables?
What are the failure scenarios? Missing data, unauthorized, duplicate, invalid input
What should happen in each failure case?

2. Choosing the Right API Type

API type chosen and justified
Not using Edge Function when a simpler table query or RPC would suffice
Not using direct table query when logic involves multiple tables — use RPC

3. Auth & Permission Requirements

Is this endpoint protected or public?
If protected: auth validated at the start — auth.uid() is not null
Role/permission read from database — never from request payload
Workspace membership verified if workspace-scoped
If public: confirmed intentionally public
If public: no sensitive data exposed
RLS policies enforce data access — endpoint does not manually filter by user
Service role key not used in this flow unless server-side admin task

4. Input Validation

Every input must be validated at three layers:

Every required field validated as NOT NULL / NOT EMPTY at API level
String fields: max length validated
Email fields: format validated
Phone fields: E.164 format validated if applicable
Enum/status fields: allowed values validated
Numeric fields: range validated
Date/datetime: valid date format and range validated if applicable
Foreign key IDs: existence validated + access check
File uploads: size and MIME type validated in Edge Function
UUID fields: format validated before querying

Validation Error Response

{
  "error": "VALIDATION_ERROR",
  "message": "Validation failed.",
  "fields": {
    "name": "Name is required.",
    "email": "Enter a valid email address."
  }
}

{
  "data": {
    ...
  },
  "message": "Project created successfully."
}

{
  "error": "ERROR_CODE",
  "message": "Human-readable description of the error."
}

Response Rules

Success response always includes data or null + message
Error response always includes error code + human-readable message
HTTP status codes are correct
Frontend never has to guess what happened — error code is always included
No stack traces or internal error details in response — log them, do not return them

6. Standard Error Codes

7. RPC Function Checklist

Only applies when building a Postgres RPC function.

Function uses SECURITY DEFINER if it needs to bypass RLS for controlled operations
SECURITY DEFINER functions manually verify auth.uid() at the start
Function is VOLATILE or STABLE — not incorrectly marked IMMUTABLE
Function parameters are typed correctly: UUID, TEXT, INT — not generic anyelement
Function returns a consistent structure — JSON or typed row
Function handles all error paths with RAISE EXCEPTION
Transaction wraps multi-step operations
Function tested in isolation with valid and invalid inputs

Example RPC Pattern

CREATE OR REPLACE FUNCTION create_project(
  p_workspace_id UUID,
  p_name TEXT,
  p_description TEXT DEFAULT NULL
)
RETURNS JSON
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
  v_project projects%ROWTYPE;
BEGIN
  IF p_name IS NULL OR trim(p_name) = '' THEN
    RAISE EXCEPTION 'VALIDATION_ERROR: name is required';
  END IF;

  IF length(trim(p_name)) > 100 THEN
    RAISE EXCEPTION 'VALIDATION_ERROR: name must be 100 characters or fewer';
  END IF;

  IF EXISTS (
    SELECT 1
    FROM projects
    WHERE workspace_id = p_workspace_id
      AND name = trim(p_name)
  ) THEN
    RAISE EXCEPTION 'DUPLICATE: a project with this name already exists';
  END IF;

  INSERT INTO projects (workspace_id, name, description, created_by)
  VALUES (p_workspace_id, trim(p_name), p_description, auth.uid())
  RETURNING * INTO v_project;

  RETURN json_build_object('data', row_to_json(v_project));
END;
$$;

8. Documentation Requirements

Every API endpoint needs documentation before it is handed off to the frontend team.

Endpoint name / function name documented
HTTP method and URL or RPC call format documented
Auth requirement stated — public / authenticated / role required
Request payload documented — every field: name, type, required/optional, constraints
Success response documented with full JSON example
Error responses documented — all possible error codes + example response
Frontend code example provided — Supabase client call
Test cases listed — success, missing required field, unauthorized, not found, duplicate

9. Testing Requirements

Success case: correct data in → correct response out
Missing required field: correct error returned
Invalid format: validation error returned
Unauthorized: AUTH_REQUIRED returned
Forbidden: wrong workspace or wrong role returns FORBIDDEN
Duplicate record: DUPLICATE returned
Not found: wrong ID returns NOT_FOUND
Edge cases specific to this endpoint tested
Tested as each role: anonymous, member, admin, non-member
Tested with empty strings, null values, oversized strings
Tested with invalid UUID formats
RLS prevents cross-workspace data access

Before Marking Done

10. API Endpoint Checklist