braindrops.nicwands.com/BACKEND_GUIDE.md

# Backend Integration Guide

This guide explains how to use the backend system in Braindrops.

## Overview

Braindrops now includes an optional self-hosted backend powered by Bun. Each drop can optionally have:

- **REST API endpoints** - For data persistence and external API calls
- **WebSocket handlers** - For real-time communication
- **SQLite database** - Shared across all drops with namespaced tables

## Quick Start

### 1. Develop a Drop with Backend

```bash
npm run dev:drops image-melody
```

This automatically starts:
- Drop frontend on `http://localhost:3000`
- CMS on `http://localhost:8787`
- Backend API on `http://localhost:3001/api/image-melody`
- WebSocket on `ws://localhost:3001/ws/image-melody`

### 2. Add Backend to a Drop

Create a `backend/` folder in your drop:

```bash
drops/my-drop/
├── src/              # Frontend
├── backend/          # Backend (NEW)
│   ├── api.js        # REST endpoints
│   ├── schema.sql    # Database schema
│   └── websocket.js  # WebSocket handler (optional)
└── public/
```

### 3. Use the Template

New drops created with `npm run create` will ask if you want to include a backend. The template provides:
- Example CRUD API
- Sample database schema
- WebSocket handler
- README with documentation

## Creating a Backend

### Step 1: Define Your Database Schema

Create `backend/schema.sql`:

```sql
-- Use naming convention: <drop-name>_<table-name>
CREATE TABLE IF NOT EXISTS my_drop_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    data TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_my_drop_items_created 
    ON my_drop_items(created_at);
```

### Step 2: Create Your API

Create `backend/api.js`:

```javascript
import { dbHelpers } from '../../../backend/src/db.js'
const { query, queryOne, run } = dbHelpers

export const routes = {
  // GET /api/my-drop/items
  'GET /items': async (req) => {
    const items = query('SELECT * FROM my_drop_items ORDER BY created_at DESC')
    return Response.json({ items, count: items.length })
  },
  
  // GET /api/my-drop/items/:id
  'GET /items/:id': async (req, params) => {
    const item = queryOne('SELECT * FROM my_drop_items WHERE id = ?', [params.id])
    
    if (!item) {
      return Response.json({ error: 'Not found' }, { status: 404 })
    }
    
    return Response.json(item)
  },
  
  // POST /api/my-drop/items
  'POST /items': async (req) => {
    const body = await req.json()
    
    if (!body.name || !body.data) {
      return Response.json({ error: 'Missing fields' }, { status: 400 })
    }
    
    const result = run(
      'INSERT INTO my_drop_items (name, data) VALUES (?, ?)',
      [body.name, JSON.stringify(body.data)]
    )
    
    return Response.json({ id: result.lastInsertRowid, success: true }, { status: 201 })
  },
  
  // DELETE /api/my-drop/items/:id
  'DELETE /items/:id': async (req, params) => {
    const result = run('DELETE FROM my_drop_items WHERE id = ?', [params.id])
    
    if (result.changes === 0) {
      return Response.json({ error: 'Not found' }, { status: 404 })
    }
    
    return Response.json({ success: true })
  }
}
```

### Step 3: Add WebSocket (Optional)

Create `backend/websocket.js`:

```javascript
export const websocket = {
  open(ws) {
    console.log('Client connected')
    ws.send(JSON.stringify({ type: 'connected', message: 'Welcome!' }))
  },
  
  message(ws, message) {
    const data = JSON.parse(message)
    
    // Broadcast to all connected clients
    ws.publish('my-drop', JSON.stringify({
      type: 'broadcast',
      data: data
    }))
  },
  
  close(ws) {
    console.log('Client disconnected')
  }
}
```

## Using the Backend in Your Drop

### REST API

From your drop's frontend (`src/App.vue` or any component):

```javascript
// GET request
async function loadItems() {
  const response = await fetch('/api/my-drop/items')
  const data = await response.json()
  console.log(data.items)
}

// POST request
async function createItem(name, data) {
  const response = await fetch('/api/my-drop/items', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ name, data })
  })
  const result = await response.json()
  console.log('Created ID:', result.id)
}

// DELETE request
async function deleteItem(id) {
  const response = await fetch(`/api/my-drop/items/${id}`, {
    method: 'DELETE'
  })
  const result = await response.json()
  console.log('Deleted:', result.success)
}
```

### WebSocket

```javascript
import { ref, onMounted, onUnmounted } from 'vue'

export default {
  setup() {
    const ws = ref(null)
    const messages = ref([])
    
    onMounted(() => {
      // Connect to WebSocket
      ws.value = new WebSocket('ws://localhost:3001/ws/my-drop')
      
      ws.value.onopen = () => {
        console.log('Connected to WebSocket')
      }
      
      ws.value.onmessage = (event) => {
        const data = JSON.parse(event.data)
        messages.value.push(data)
      }
      
      ws.value.onerror = (error) => {
        console.error('WebSocket error:', error)
      }
    })
    
    onUnmounted(() => {
      if (ws.value) {
        ws.value.close()
      }
    })
    
    function sendMessage(data) {
      if (ws.value && ws.value.readyState === WebSocket.OPEN) {
        ws.value.send(JSON.stringify(data))
      }
    }
    
    return { messages, sendMessage }
  }
}
```

## Database Helpers

The backend provides helper functions for database operations:

```javascript
import { dbHelpers } from '../../../backend/src/db.js'
const { query, queryOne, run, getDb } = dbHelpers

// query(sql, params) - Get all results
const items = query('SELECT * FROM my_drop_items WHERE name LIKE ?', ['%test%'])

// queryOne(sql, params) - Get first result
const item = queryOne('SELECT * FROM my_drop_items WHERE id = ?', [1])

// run(sql, params) - Execute INSERT/UPDATE/DELETE
const result = run('INSERT INTO my_drop_items (name, data) VALUES (?, ?)', ['test', 'data'])
console.log(result.lastInsertRowid) // Get auto-increment ID
console.log(result.changes) // Number of affected rows

// getDb() - Get raw database instance
const db = getDb()
const stmt = db.prepare('SELECT * FROM my_drop_items')
const items = stmt.all()
```

## Environment Variables

### Setting Up

1. Copy the example file:
   ```bash
   cp .env.example .env
   ```

2. Add your variables:
   ```bash
   PRODUCTION_URL=https://yourdomain.com
   OPENAI_API_KEY=sk-...
   WEATHER_API_KEY=...
   ```

3. Access in your backend code:
   ```javascript
   const apiKey = process.env.OPENAI_API_KEY
   ```

### Important Notes

- `.env` is gitignored and won't be committed
- Don't put API keys in frontend code (use backend as proxy)
- Environment variables are only available in backend code, not frontend

## Example: External API Proxy

To call external APIs without exposing keys:

```javascript
// backend/api.js
export const routes = {
  'POST /weather': async (req) => {
    const body = await req.json()
    const { city } = body
    
    // Call external API with secret key
    const response = await fetch(
      `https://api.weather.com/data?city=${city}&key=${process.env.WEATHER_API_KEY}`
    )
    const data = await response.json()
    
    return Response.json(data)
  }
}
```

Then from your frontend:
```javascript
const response = await fetch('/api/my-drop/weather', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({ city: 'London' })
})
const weather = await response.json()
```

## Production Deployment

### 1. Build Everything

```bash
npm run build
```

This will:
- Build all drops
- Build the main app
- Build the backend (if you add the script)

### 2. Deploy Static Files

Upload `app/dist/` to your static host (nginx, Apache, etc.)

### 3. Deploy Backend

```bash
# Build backend
node bin/buildBackend.js

# Copy to server
scp -r backend/dist user@server:/var/www/braindrops/backend

# On server
cd /var/www/braindrops/backend
bun install --production
./start.sh
```

### 4. Configure nginx

```nginx
server {
    listen 80;
    server_name yourdomain.com;
    
    # Static files
    location / {
        root /var/www/braindrops/static;
        try_files $uri $uri/ /index.html;
    }
    
    # Backend API
    location /api/ {
        proxy_pass http://localhost:3001;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
    }
    
    # WebSocket
    location /ws/ {
        proxy_pass http://localhost:3001;
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";
    }
}
```

### 5. Set Production Environment Variables

On your server:
```bash
cd /var/www/braindrops/backend
nano .env
```

Add:
```bash
NODE_ENV=production
PRODUCTION_URL=https://yourdomain.com
# Your API keys...
```

### 6. Run Backend with Process Manager

Using PM2:
```bash
npm install -g pm2
pm2 start start.sh --name braindrops-backend
pm2 save
pm2 startup
```

Or use systemd, supervisor, etc.

## Best Practices

### Security

1. **Never expose API keys in frontend code**
2. **Validate all input** on the backend
3. **Use HTTPS** in production
4. **Sanitize database inputs** (use parameterized queries)
5. **Add rate limiting** if needed (use nginx or middleware)

### Database

1. **Use the naming convention** `<drop-name>_<table-name>`
2. **Add indexes** for frequently queried columns
3. **Store JSON as TEXT** using `JSON.stringify()`
4. **Use transactions** for multi-step operations
5. **Backup regularly** (copy `braindrops.db` file)

### API Design

1. **Use proper HTTP methods** (GET, POST, PUT, DELETE)
2. **Return appropriate status codes** (200, 201, 400, 404, 500)
3. **Use consistent response format** `{ success: true, data: ... }`
4. **Handle errors gracefully** with try/catch
5. **Document your endpoints** in comments

### WebSocket

1. **Validate messages** before broadcasting
2. **Use JSON** for structured data
3. **Handle disconnections** gracefully
4. **Implement reconnect logic** in frontend
5. **Namespace channels** by drop name

## Troubleshooting

### Backend not starting

Check if Bun is installed:
```bash
bun --version
```

Install if needed:
```bash
curl -fsSL https://bun.sh/install | bash
```

### Port already in use

Change the port in `.env`:
```bash
PORT=3002
```

### Database locked

SQLite is running in WAL mode which should prevent most locking issues. If you still encounter locks:
1. Ensure only one backend instance is running
2. Close any database browser tools
3. Delete `.db-wal` and `.db-shm` files

### CORS errors

Make sure your frontend URL is allowed in `backend/src/shared/middleware.js`.

For development, these are allowed by default:
- `http://localhost:3000` (drop dev)
- `http://localhost:5173` (app dev)

For production, set `PRODUCTION_URL` in `.env`.

## Examples

See `drops/image-melody/backend/` for a complete working example with:
- Database schema for storing melodies
- CRUD API for managing melodies
- WebSocket for broadcasting melody plays
- Full integration with frontend

## Getting Help

- Check `backend/README.md` for backend-specific docs
- Check `template/backend/README.md` for template usage
- Review the image-melody example
- Check Bun documentation: https://bun.sh/docs

## Next Steps

1. Try the example: `npm run dev:drops image-melody`
2. Test the API with curl or your browser
3. Create your own backend for a drop
4. Experiment with WebSocket real-time features
5. Deploy to production!