Building a SaaS API Backend with Aerostack

CREATE TABLE projects (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);
 
CREATE TABLE tasks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  project_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'todo',
  assigned_to INTEGER,
  due_date INTEGER,
  attachment_url TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (project_id) REFERENCES projects(id)
);
 
CREATE INDEX idx_tasks_project ON tasks(project_id);
CREATE INDEX idx_tasks_status ON tasks(status);

export default async function(sdk, event) {
  const { name, description } = event.data;
  const user_id = event.user?.id; // Auto-populated by auth
  
  // Validate
  if (!name || name.length < 3) {
    throw new Error('Project name must be at least 3 characters');
  }
  
  const now = Date.now();
  
  // Create project
  const result = await sdk.db.query(`
    INSERT INTO projects (user_id, name, description, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?)
  `, [user_id, name, description, now, now]);
  
  const project = {
    id: result.lastInsertRowid,
    user_id,
    name,
    description,
    created_at: now,
    updated_at: now
  };
  
  // Invalidate user's project list cache
  await sdk.cache.delete(`projects:user:${user_id}`);
  
  return { project };
}

export default async function(sdk, event) {
  const user_id = event.user?.id;
  const cacheKey = `projects:user:${user_id}`;
  
  // Check cache
  const cached = await sdk.cache.get(cacheKey);
  if (cached) {
    return { projects: cached, from_cache: true };
  }
  
  // Query database
  const projects = await sdk.db.query(`
    SELECT id, name, description, created_at, updated_at
    FROM projects
    WHERE user_id = ?
    ORDER BY updated_at DESC
  `, [user_id]);
  
  // Cache for 5 minutes
  await sdk.cache.set(cacheKey, projects, 300);
  
  return { projects, from_cache: false };
}

export default async function(sdk, event) {
  const { project_id, title, description, assigned_to, due_date, attachment } = event.data;
  const user_id = event.user?.id;
  
  // Validate ownership
  const [project] = await sdk.db.query(
    'SELECT id FROM projects WHERE id = ? AND user_id = ?',
    [project_id, user_id]
  );
  
  if (!project) {
    throw new Error('Project not found or access denied');
  }
  
  let attachment_url = null;
  
  // Handle file upload if present
  if (attachment?.file_base64 && attachment?.filename) {
    const key = `tasks/${project_id}/${Date.now()}_${attachment.filename}`;
    attachment_url = await sdk.storage.upload(key, attachment.file_base64, {
      contentType: attachment.content_type || 'application/octet-stream'
    });
  }
  
  const now = Date.now();
  
  // Create task
  const result = await sdk.db.query(`
    INSERT INTO tasks (
      project_id, title, description, assigned_to, 
      due_date, attachment_url, created_at, updated_at
    )
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  `, [project_id, title, description, assigned_to, due_date, attachment_url, now, now]);
  
  const task = {
    id: result.lastInsertRowid,
    project_id,
    title,
    description,
    status: 'todo',
    assigned_to,
    due_date,
    attachment_url,
    created_at: now,
    updated_at: now
  };
  
  // Send email notification asynchronously
  if (assigned_to) {
    await sdk.queue.enqueue('send-task-assignment-email', {
      task_id: task.id,
      assigned_to,
      title
    });
  }
  
  // Invalidate cache
  await sdk.cache.delete(`tasks:project:${project_id}`);
  
  return { task };
}

export async function sendTaskAssignmentEmail(sdk, job) {
  const { task_id, assigned_to, title } = job.data;
  
  // Get assignee details
  const [user] = await sdk.db.query(
    'SELECT email, name FROM users WHERE id = ?',
    [assigned_to]
  );
  
  if (!user) return { error: 'User not found' };
  
  // Get email API key from secrets
  const sendgridKey = await sdk.secrets.get('SENDGRID_API_KEY');
  
  // Send email
  await sdk.fetch('https://api.sendgrid.com/v3/mail/send', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${sendgridKey}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      personalizations: [{
        to: [{ email: user.email, name: user.name }]
      }],
      from: { email: '[email protected]', name: 'Your App' },
      subject: `New task assigned: ${title}`,
      content: [{
        type: 'text/html',
        value: `
          <h2>You've been assigned a new task</h2>
          <p><strong>${title}</strong></p>
          <p><a href="https://yourapp.com/tasks/${task_id}">View Task</a></p>
        `
      }]
    })
  });
  
  return { success: true, sent_to: user.email };
}

export default async function(sdk, event) {
  const { query, project_id } = event.data;
  const user_id = event.user?.id;
  
  // Verify access
  const [project] = await sdk.db.query(
    'SELECT id FROM projects WHERE id = ? AND user_id = ?',
    [project_id, user_id]
  );
  
  if (!project) {
    throw new Error('Project not found');
  }
  
  const cacheKey = `search:${project_id}:${query}`;
  
  // Check cache
  const cached = await sdk.cache.get(cacheKey);
  if (cached) return { results: cached };
  
  // Basic SQL search
  const sqlResults = await sdk.db.query(`
    SELECT * FROM tasks 
    WHERE project_id = ? 
    AND (title LIKE ? OR description LIKE ?)
    ORDER BY created_at DESC
    LIMIT 20
  `, [project_id, `%${query}%`, `%${query}%`]);
  
  // Use AI to rank by relevance if query is complex
  if (query.split(' ').length > 2) {
    const aiRanking = await sdk.ai.chat('@cf/meta/llama-3-8b-instruct', [
      {
        role: 'system',
        content: 'Rank tasks by relevance to the search query. Return task IDs in order of relevance.'
      },
      {
        role: 'user',
        content: `Query: "${query}"\n\nTasks: ${JSON.stringify(sqlResults)}`
      }
    ]);
    
    // Reorder results based on AI ranking
    // (simplified - in production you'd parse AI response properly)
  }
  
  // Cache for 10 minutes
  await sdk.cache.set(cacheKey, sqlResults, 600);
  
  return { results: sqlResults };
}