SaaS Backend

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);

// 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 } }
}

// 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 }
}

// 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 } }
}

// 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: '[email protected]',
        to: user.email,
        subject: `New task: ${title}`,
        html: `<h2>You've been assigned: <strong>${title}</strong></h2>`,
      }),
    })
 
    message.ack()
  }
}

// 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 }
}