Backend: API & Database

Scaffold the Monorepo

We need two projects — /client and /server — in a single repository. AI sets this up in one prompt.

AI will generate the full scaffolding. Here's what the project structure looks like after setup:

The verification step: both projects should run simultaneously and the server should respond to a health check.

Build the Database Layer

SQLite with better-sqlite3 — synchronous, zero-config, perfect for a weekend build. Notice how we share the types from Part 1 as context.

The key output is the schema migration. Here's what it should look like:

CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  name TEXT NOT NULL,
  created_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS projects (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  owner_id TEXT NOT NULL REFERENCES users(id),
  archived INTEGER DEFAULT 0,
  created_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS tasks (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id),
  title TEXT NOT NULL,
  description TEXT DEFAULT '',
  status TEXT DEFAULT 'todo'
    CHECK (status IN ('todo','in_progress','review','done')),
  priority TEXT DEFAULT 'medium'
    CHECK (priority IN ('low','medium','high','urgent')),
  assignee TEXT,
  due_date TEXT,
  position REAL DEFAULT 0,
  completed_at TEXT,
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_tasks_project ON tasks(project_id);
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
CREATE INDEX IF NOT EXISTS idx_projects_owner ON projects(owner_id);

Verification: run the migration and confirm the tables exist.

Build Authentication

JWT-based auth with bcrypt password hashing. This is where security matters most — and where AI's defaults are most dangerous. We specify security requirements explicitly.

AI generates the auth routes and middleware. The critical things to verify in the output:

• Password hashing — bcrypt.hashSync(password, 12), never plain text
• JWT secret from env — process.env.JWT_SECRET, not a hardcoded string
• Generic errors — Both "wrong email" and "wrong password" return the same message
• Parameterized queries — db.prepare('SELECT * FROM users WHERE email = ?').get(email)

Verification: test the full auth flow with curl.

Project Endpoints

Notice the pattern: we always paste existing code so AI matches the style. We specify the behavior precisely. And we reference the earlier critique — DELETE actually archives.

// GET /projects — only the user's non-archived projects
router.get('/', authMiddleware, (req, res) => {
  const projects = db
    .prepare('SELECT * FROM projects WHERE owner_id = ? AND archived = 0 ORDER BY created_at DESC')
    .all(req.user.id);

  res.json({ projects });
});

// DELETE /projects/:id — soft delete (archive)
router.delete('/:id', authMiddleware, (req, res) => {
  const project = db
    .prepare('SELECT * FROM projects WHERE id = ? AND owner_id = ?')
    .get(req.params.id, req.user.id);

  if (!project) return res.status(404).json({ error: 'Project not found', code: 'NOT_FOUND' });

  db.prepare('UPDATE projects SET archived = 1 WHERE id = ?').run(req.params.id);
  res.json({ success: true });
});

Task Endpoints

The most complex set of endpoints. Tasks have more fields, more validation, and the kanban move operation requires special logic for position calculation.

The move endpoint is the trickiest part. Here's the core logic:

router.patch('/:id/move', authMiddleware, (req, res) => {
  const { status, position } = req.body as MoveTaskInput;
  const task = db.prepare('SELECT * FROM tasks WHERE id = ?').get(req.params.id);

  if (!task) return res.status(404).json({ error: 'Task not found', code: 'NOT_FOUND' });

  // Verify ownership through project
  const project = db
    .prepare('SELECT * FROM projects WHERE id = ? AND owner_id = ?')
    .get(task.project_id, req.user.id);

  if (!project) return res.status(403).json({ error: 'Forbidden', code: 'FORBIDDEN' });

  const updates: Record<string, unknown> = {
    status,
    position,
    updated_at: new Date().toISOString(),
  };

  // If moving to 'done', record completion time
  if (status === 'done' && task.status !== 'done') {
    updates.completed_at = new Date().toISOString();
  }

  // If moving away from 'done', clear completion time
  if (status !== 'done' && task.status === 'done') {
    updates.completed_at = null;
  }

  db.prepare(
    'UPDATE tasks SET status = ?, position = ?, updated_at = ?, completed_at = ? WHERE id = ?'
  ).run(updates.status, updates.position, updates.updated_at, updates.completed_at, req.params.id);

  const updated = db.prepare('SELECT * FROM tasks WHERE id = ?').get(req.params.id);
  res.json({ task: updated });
});

Now: Adversarial Review

With all five milestones of the backend complete, we run the adversarial review from the manual before moving on.

Four real issues found. Fix all of them before moving to the frontend. This 2-minute review just prevented four bugs that would have appeared in production.

End of Saturday: What We Have

The entire backend is built, reviewed, and working. Let's verify everything one more time with a comprehensive test sequence: