BaaS (Backend as a Service) Worker

A Cloudflare Worker that provides a comprehensive backend service with database operations, authentication, and file storage capabilities.

Features

• Database Operations: RESTful API for D1 database CRUD operations
• Authentication: User authentication and session management
• Row-Level Security: Granular access control for database tables
• File Storage: Entity-linked file storage with permission-based access
• Secrets Management: Secure storage and retrieval of sensitive configuration
• AI Assistants: Integration with AI services

Storage API

The Storage API allows clients to upload, download, and manage files, with access control tied to database entity permissions.

Architecture

Files are stored in Cloudflare R2 and linked to database entities. Access permissions are inherited from the database's Row-Level Security (RLS) policies - if a user has access to an entity, they can access its associated files.

Database Integration

All files are linked to database entities. To list files associated with any entity, you should query the database table directly. This ensures the database remains the single source of truth for file references.

File Organization

Files are stored in R2 with the following path structure:

{table}/{id}/{field}/{filename}

For example, a user's profile picture might be stored at:

users/user123/avatar/profile.jpg

API Endpoints

Upload a File

POST /storage/upload
Content-Type: multipart/form-data

Form Data:
- file: The file to upload
- entity: JSON object with {table, id, field}
- metadata: (optional) JSON object with custom metadata

Download a File

GET /storage/download/:table/:id/:field/:filename

Delete a File

DELETE /storage/delete/:table/:id/:field/:filename

Example Usage: User Profile Picture

Upload a profile picture:

const formData = new FormData();
formData.append('file', fileInput.files[0]);
formData.append('entity', JSON.stringify({
  table: 'users',
  id: 'user123',
  field: 'avatar'
}));

fetch('/storage/upload', {
  method: 'POST',
  body: formData,
  headers: {
    'Authorization': 'Bearer your-token'
  }
});

Display a profile picture:

<img src="/storage/download/users/user123/avatar/profile.jpg" alt="Profile Picture">

Listing user profile pictures:

// Query the database to find file references
fetch('/db/tables/users?select=id,avatar')
  .then(response => response.json())
  .then(data => {
    // Process the file references from the database
    const users = data.data;
    users.forEach(user => {
      if (user.avatar) {
        // Display avatar using the download endpoint
        const avatarUrl = `/storage/download/users/${user.id}/avatar/${user.avatar}`;
        // Use the URL as needed
      }
    });
  });

Access Control

File operations inherit RLS policies from the referenced entity:

• To upload/update a file: User needs UPDATE permission on the entity
• To download files: User needs SELECT permission on the entity
• To delete a file: User needs DELETE permission on the entity

Setup and Deployment

1. Configure Cloudflare Workers
2. Set up D1 Database
3. Create R2 Bucket
4. Deploy the worker

Environment Variables

• DB: D1 Database binding
• STORAGE_BUCKET: R2 Bucket binding for file storage
• BYPASS_AUTH: Set to true to bypass authentication (development only)
• VERSION: API version information

D1 Database API with Row Level Security

To run the worker locally:

npm install
npm run dev

Before first run, you need to migrate the database:

npx wrangler d1 execute BAAS_DATABASE_NAME --local --file=./schema.sql  

To deploy:

npm run deploy

Row Level Security (RLS)

This API implements Row Level Security, allowing you to define access policies at the row level. RLS restricts which rows can be retrieved by normal database operations based on user identity.

How RLS Works

1. Each database operation (SELECT/INSERT/UPDATE/DELETE) is filtered through security policies.
2. Policies define conditions for when operations are allowed.
3. Two types of security checks:
   • using_clause: Filters which rows users can SELECT, UPDATE, or DELETE
   • withcheck_clause: Filters which rows users can INSERT or UPDATE

Setting Up RLS Policies

Policies are stored in the _policies table. Here's how to create a policy:

-- Example: Allow users to see only their own todos
INSERT INTO _policies (table_name, action, using_clause) 
VALUES ('todos', 'select', 'user_id = $$CURRENT_USER$$');

-- Example: Allow users to insert only todos they own
INSERT INTO _policies (table_name, action, withcheck_clause) 
VALUES ('todos', 'insert', 'user_id = $$CURRENT_USER$$');

-- Example: Allow users to update their own todos
INSERT INTO _policies (table_name, action, using_clause, withcheck_clause) 
VALUES (
  'todos', 
  'update', 
  'user_id = $$CURRENT_USER$$', 
  'user_id = $$CURRENT_USER$$'
);

-- Example: Allow users to delete their own todos
INSERT INTO _policies (table_name, action, using_clause) 
VALUES ('todos', 'delete', 'user_id = $$CURRENT_USER$$');

Special variables in policy expressions:

• $$CURRENT_USER$$: The ID of the authenticated user
• $$CURRENT_ROLE$$: The role of the authenticated user (if available)

Testing with cURL

Here are examples of how to use cURL to test the database endpoints:

SELECT - Get all records

# Get all todos
curl -X GET "http://localhost:8787/db/tables/todos"

# Filter by field
curl -X GET "http://localhost:8787/db/tables/todos?completed=false"

# Advanced filtering, limit, and ordering
curl -X GET "http://localhost:8787/db/tables/todos?title.like=important&limit=10&order=created_at%20DESC"

SELECT - Get a specific record

# Get todo by ID
curl -X GET "http://localhost:8787/db/tables/todos/123"

INSERT - Create a new record

# Create a new todo
curl -X POST "http://localhost:8787/db/tables/todos" \
  -H "Content-Type: application/json" \
  -d '{"title": "Buy groceries", "user_id": "user123", "completed": false}'

UPDATE - Update records

# Update todo by ID
curl -X PATCH "http://localhost:8787/db/tables/todos/123" \
  -H "Content-Type: application/json" \
  -d '{"completed": true}'

# Update with a WHERE condition
curl -X PATCH "http://localhost:8787/db/tables/todos" \
  -H "Content-Type: application/json" \
  -d '{
    "data": {"completed": true},
    "where": {"user_id": "user123"}
  }'

DELETE - Delete records

# Delete todo by ID
curl -X DELETE "http://localhost:8787/db/tables/todos/123"

# Delete with a WHERE condition
curl -X DELETE "http://localhost:8787/db/tables/todos" \
  -H "Content-Type: application/json" \
  -d '{
    "where": {"completed": true}
  }'

Advanced filtering examples

# Greater than
curl -X GET "http://localhost:8787/db/tables/todos?priority.gt=3"

# Less than or equal to
curl -X GET "http://localhost:8787/db/tables/todos?created_at.lte=2023-12-31"

# Not equal
curl -X GET "http://localhost:8787/db/tables/todos?status.neq=cancelled"

# LIKE pattern matching
curl -X GET "http://localhost:8787/db/tables/todos?title.like=meeting"