Supabase Custom Notes Service

A production-ready Supabase backend for a personal notes service with custom Edge Functions. This service provides a secure and scalable way to create and manage personal notes with features like tag filtering, pagination, and robust security.

Table of Contents

1. Project Overview
2. Setup Instructions
   • Using Supabase Web Interface
   • Using Supabase CLI
3. Testing the API
4. API Documentation
5. Schema Design
6. Security Implementation
7. Troubleshooting

Project Overview

This project implements a custom notes service using Supabase with the following components:

• PostgreSQL Database: Securely stores notes with proper user isolation
• Edge Functions: Serverless API endpoints for creating and retrieving notes
• Row Level Security: Ensures data protection by allowing users to access only their own data
• Tags Support: Allows categorizing and filtering notes by tags

Setup Instructions

Using Supabase Web Interface

Step 1: Create a Supabase Project

1. Go to app.supabase.com and sign in
2. Click "New Project"
3. Fill in the project details:
   • Name your project (e.g., "notes-service")
   • Set a secure database password
   • Choose a region close to your users
4. Click "Create new project"

Step 2: Set Up the Database Schema

1. In your Supabase dashboard, go to "SQL Editor" in the left sidebar
2. Click "New Query"
3. Copy and paste the entire content of the schema.sql file
4. Click "Run" to execute the SQL

Step 3: Create Edge Functions

1. In the Supabase dashboard, go to "Edge Functions" in the left sidebar
2. Click "Create a new function"
3. Name your function "get_notes"
4. Delete the existing content in the default index.ts file
5. Copy the content of get_notes.ts into the editor
6. Click "Deploy Function"
7. Repeat steps 2-6 for the "post_notes" function using the content from post_notes.ts

Using Supabase CLI

Step 1: Install Prerequisites

# Install Node.js if not already installed
# Install Supabase CLI
npm install -g supabase

Step 2: Initialize Project

# Create a new directory
mkdir notes-service
cd notes-service

# Initialize Supabase project
supabase init

Step 3: Link to Supabase Project

# Login to Supabase
supabase login

# Link to your existing project
supabase link --project-ref your-project-ref

Step 4: Apply Database Schema

# Create schema.sql file with the provided schema
# Then apply it to your project
supabase db push

Step 5: Create Function Files

1. Create function folders and files:

mkdir -p supabase/functions/get_notes
mkdir -p supabase/functions/post_notes

2. Create supabase/functions/get_notes/index.ts with the content of get_notes.ts
3. Create supabase/functions/post_notes/index.ts with the content of post_notes.ts

Step 6: Deploy Functions

supabase functions deploy get_notes
supabase functions deploy post_notes

Testing the API

Step 1: Get Authentication Token

1. In the Supabase dashboard, go to "Settings" → "API" in the left sidebar
2. Copy the "anon" key or "service_role" key (for testing purposes)
3. Create a user or note the ID of an existing user:
   • Go to "Authentication" → "Users"
   • Copy the UUID of a user

Step 2: Test Creating a Note (POST)

Using curl (replace placeholders with your actual values):

curl -X POST https://your-project-ref.functions.supabase.co/post_notes \
  -H "Authorization: Bearer your-jwt-token" \
  -H "Content-Type: application/json" \
  -d '{
    "title": "My First Note",
    "content": "This is a test note from Supabase Edge Function",
    "user_id": "the-user-uuid",
    "tags": ["test", "api"]
  }'

Expected response:

{
  "message": "Note created successfully",
  "data": {
    "id": "generated-uuid",
    "user_id": "the-user-uuid",
    "title": "My First Note",
    "content": "This is a test note from Supabase Edge Function",
    "created_at": "2025-04-26T18:00:00.000Z",
    "updated_at": "2025-04-26T18:00:00.000Z",
    "tags": ["test", "api"]
  }
}

Step 3: Test Retrieving Notes (GET)

Basic retrieval:

curl -X GET "https://your-project-ref.functions.supabase.co/get_notes?user_id=the-user-uuid" \
  -H "Authorization: Bearer your-jwt-token"

With filtering and pagination:

curl -X GET "https://your-project-ref.functions.supabase.co/get_notes?user_id=the-user-uuid&tag=test&limit=5&offset=0&orderBy=created_at&order=desc" \
  -H "Authorization: Bearer your-jwt-token"

Expected response:

{
  "message": "Notes retrieved successfully",
  "data": [
    {
      "id": "note-uuid",
      "user_id": "the-user-uuid",
      "title": "My First Note",
      "content": "This is a test note from Supabase Edge Function",
      "created_at": "2025-04-26T18:00:00.000Z",
      "updated_at": "2025-04-26T18:00:00.000Z",
      "tags": ["test", "api"]
    }
  ],
  "pagination": {
    "offset": 0,
    "limit": 5,
    "total": 1
  }
}

API Documentation

GET /notes

Retrieves notes for a specific user with optional filtering and pagination.

Parameters:

• user_id (required): The UUID of the user whose notes to retrieve
• tag (optional): Filter notes by specified tag
• limit (optional): Maximum number of notes to return (default: 100)
• offset (optional): Number of notes to skip (default: 0)
• orderBy (optional): Field to order by (default: created_at)
• order (optional): Order direction, 'asc' or 'desc' (default: desc)

Response: JSON object containing notes and pagination information

POST /notes

Creates a new note for a specific user.

Request Body:

{
  "user_id": "user-uuid",
  "title": "Note Title",
  "content": "Note content goes here",
  "tags": ["tag1", "tag2"] // Optional
}

Response: JSON object containing the created note

Schema Design

The database schema is designed with the following considerations:

• UUID Primary Key: Used a UUID as the primary key with auto-generation for security and distribution benefits compared to sequential IDs.
• References to User ID: Foreign key to auth.users with CASCADE delete to maintain referential integrity.
• Timestamps: Included both created_at and updated_at fields with defaults to track note history.
• Tags Array: Added a tags array for easy categorization and filtering.
• Row Level Security (RLS): Implemented RLS policies to ensure users can only access their own notes.
• Indexing: Created an index on user_id to optimize query performance when fetching notes.
• Automatic Updates: Added a trigger to automatically update the updated_at timestamp.

Security Implementation

This service implements several security measures:

1. Row Level Security (RLS): Database-level policies ensure users can only access their own notes
2. JWT Authentication: Enforced authentication for all API endpoints
3. Input Validation: All inputs are validated before processing
4. UUID Primary Keys: Non-sequential IDs prevent enumeration attacks
5. Parameterized Queries: Prevents SQL injection attacks

Troubleshooting

Common Issues

1. Authentication Errors:

   • Ensure you're using a valid JWT token in the Authorization header
   • Check that the user exists in your Supabase project

2. Function Deployment Issues:

   • Check the function logs in the Supabase dashboard
   • Verify TypeScript syntax in your function files

3. Database Schema Issues:

   • Ensure the schema was applied correctly
   • Check for any error messages in the SQL Editor

4. CORS Issues:

   • If calling from a frontend application, check the CORS configuration in your Edge Functions

Viewing Function Logs

1. In the Supabase dashboard, go to "Edge Functions"
2. Click on the function name
3. Select the "Logs" tab to view recent invocations and any errors