# SaaS Backend

> Build a complete SaaS backend on Aerostack — user management, CRUD APIs, file uploads, caching, and background jobs in under an hour.

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

```sql
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

```ts
// POST /api/create-project

  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

```ts
// GET /api/list-projects

  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

```ts
// POST /api/create-task

  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

```ts
// Worker: process 'task:assigned' queue messages

  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

```ts
// POST /api/search-tasks

  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