SaaS Backend

Build a complete project-management SaaS API with user management, CRUD, caching, file uploads, and background email jobs — in under an hour.

Demonstrates: Auth, Database, Cache, Storage, Queue, AI — all together

Time to build: 30–60 minutes

---

What you’re building

A project management API with:

• User authentication (handled by Aerostack)
• Projects and tasks CRUD
• Real-time collaboration via Realtime
• File attachments for tasks
• Email notifications via Queue
• AI-powered search with caching

---

Database schema

CREATE TABLE projects (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
  user_id TEXT NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE TABLE tasks (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
  project_id TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'todo',
  assigned_to TEXT,
  due_date INTEGER,
  attachment_url TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE INDEX idx_tasks_project ON tasks(project_id);
CREATE INDEX idx_tasks_status ON tasks(status);

---

Step 1: Create project

// POST /api/create-project
export default async function(sdk, event) {
  const { name, description } = event.data
  const userId = event.user?.id

  if (!name || name.length < 3) {
    throw new Error('Project name must be at least 3 characters')
  }

  const now = Date.now()
  const id = crypto.randomUUID()

  await sdk.db.query(
    'INSERT INTO projects (id, user_id, name, description, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)',
    [id, userId, name, description, now, now]
  )

  // Invalidate user's project list cache
  await sdk.cache.delete(`projects:user:${userId}`)

  return { project: { id, userId, name, description, created_at: now } }
}

---

Step 2: List projects with caching

// GET /api/list-projects
export default async function(sdk, event) {
  const userId = event.user?.id
  const cacheKey = `projects:user:${userId}`

  const cached = await sdk.cache.get(cacheKey)
  if (cached) return { projects: cached, from_cache: true }

  const { results } = await sdk.db.query(
    'SELECT * FROM projects WHERE user_id = ? ORDER BY updated_at DESC',
    [userId]
  )

  await sdk.cache.set(cacheKey, results, { ttl: 300 }) // 5 min

  return { projects: results, from_cache: false }
}

---

Step 3: Create task with file upload

// POST /api/create-task
export default async function(sdk, event) {
  const { projectId, title, description, assignedTo, dueDate, attachment } = event.data
  const userId = event.user?.id

  // Validate ownership
  const project = await sdk.db.queryOne(
    'SELECT id FROM projects WHERE id = ? AND user_id = ?',
    [projectId, userId]
  )
  if (!project) throw new Error('Project not found or access denied')

  let attachmentUrl = null

  // Upload file if present
  if (attachment?.file_base64 && attachment?.filename) {
    const result = await sdk.storage.upload({
      file: attachment.file_base64,
      path: `tasks/${projectId}/${Date.now()}_${attachment.filename}`,
      contentType: attachment.content_type,
    })
    attachmentUrl = result.url
  }

  const now = Date.now()
  const id = crypto.randomUUID()

  await sdk.db.query(
    `INSERT INTO tasks (id, project_id, title, description, assigned_to, due_date, attachment_url, created_at, updated_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [id, projectId, title, description, assignedTo, dueDate, attachmentUrl, now, now]
  )

  // Queue email notification
  if (assignedTo) {
    await sdk.queue.send('task:assigned', { taskId: id, assignedTo, title })
  }

  // Notify realtime subscribers
  sdk.socket.emit('task:created', { id, projectId, title }, `project/${projectId}`)

  await sdk.cache.delete(`tasks:project:${projectId}`)

  return { task: { id, projectId, title, status: 'todo', attachmentUrl } }
}

---

Step 4: Background email worker

// Worker: process 'task:assigned' queue messages
export async function handler(batch, env) {
  for (const message of batch.messages) {
    const { taskId, assignedTo, title } = message.body

    const user = await sdk.db.queryOne(
      'SELECT email, name FROM users WHERE id = ?',
      [assignedTo]
    )
    if (!user) { message.ack(); continue }

    await fetch('https://api.resend.com/emails', {
      method: 'POST',
      headers: {
        'Authorization': `Bearer ${env.RESEND_API_KEY}`,
        'Content-Type': 'application/json',
      },
      body: JSON.stringify({
        from: 'noreply@yourapp.com',
        to: user.email,
        subject: `New task: ${title}`,
        html: `<h2>You've been assigned: <strong>${title}</strong></h2>`,
      }),
    })

    message.ack()
  }
}

---

Step 5: AI-powered search

// POST /api/search-tasks
export default async function(sdk, event) {
  const { query, projectId } = event.data
  const cacheKey = `search:${projectId}:${query}`

  const cached = await sdk.cache.get(cacheKey)
  if (cached) return { results: cached }

  // Semantic search via embeddings
  const queryVector = await sdk.ai.embed(query)
  const results = await sdk.search.query(queryVector, {
    table: 'tasks',
    limit: 10,
    filter: { project_id: projectId }
  })

  await sdk.cache.set(cacheKey, results, { ttl: 600 })

  return { results }
}

---

Summary

In under an hour, you have:

• POST /api/create-project — create projects
• GET /api/list-projects — list with 5-minute cache
• POST /api/create-task — create with file upload + realtime notify + queue email
• POST /api/search-tasks — semantic AI search with cache
• Background worker — email assignments