Build a Kanban Board API

mkdir kanban-api && cd kanban-api
npm init -y
npm install express pg dotenv
npm install typescript tsx @types/node @types/express @types/pg --save-dev
npx tsc --init --strict --target ES2022 --module NodeNext --moduleResolution NodeNext --outDir dist --rootDir src
mkdir -p src/{routes,db,middleware}

DATABASE_URL=postgresql://localhost:5432/kanban
PORT=3000

{
  "scripts": {
    "dev": "tsx watch src/server.ts",
    "build": "tsc",
    "start": "node dist/server.js",
    "db:setup": "tsx src/db/setup.ts"
  }
}

-- src/db/schema.sql
CREATE TABLE IF NOT EXISTS boards (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS columns (
  id SERIAL PRIMARY KEY,
  board_id INTEGER NOT NULL REFERENCES boards(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  position FLOAT NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS cards (
  id SERIAL PRIMARY KEY,
  column_id INTEGER NOT NULL REFERENCES columns(id) ON DELETE CASCADE,
  title VARCHAR(500) NOT NULL,
  description TEXT,
  position FLOAT NOT NULL DEFAULT 0,
  assignee VARCHAR(255),
  labels TEXT[] DEFAULT '{}',
  due_date DATE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_columns_board_id ON columns(board_id);
CREATE INDEX idx_columns_position ON columns(board_id, position);
CREATE INDEX idx_cards_column_id ON cards(column_id);
CREATE INDEX idx_cards_position ON cards(column_id, position);

// src/db/pool.ts
import pg from "pg";
import dotenv from "dotenv";

dotenv.config();

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
});

export default pool;

// src/db/setup.ts
import * as fs from "node:fs";
import * as path from "node:path";
import pool from "./pool.js";

async function setup(): Promise<void> {
  const schemaPath = path.join(import.meta.dirname, "schema.sql");
  const sql = fs.readFileSync(schemaPath, "utf-8");

  try {
    await pool.query(sql);
    console.log("Database schema created successfully.");
  } catch (error) {
    console.error("Failed to create schema:", error);
    process.exit(1);
  } finally {
    await pool.end();
  }
}

setup();

createdb kanban
npm run db:setup

// src/routes/boards.ts
import { Router, Request, Response } from "express";
import pool from "../db/pool.js";

const router = Router();

router.get("/", async (_req: Request, res: Response) => {
  try {
    const result = await pool.query("SELECT * FROM boards ORDER BY created_at DESC");
    res.json(result.rows);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Failed to fetch boards" });
  }
});

router.get("/:id", async (req: Request, res: Response) => {
  try {
    const { id } = req.params;
    const board = await pool.query("SELECT * FROM boards WHERE id = $1", [id]);
    if (board.rows.length === 0) {
      res.status(404).json({ error: "Board not found" });
      return;
    }

    const columns = await pool.query(
      "SELECT * FROM columns WHERE board_id = $1 ORDER BY position",
      [id]
    );

    const columnIds = columns.rows.map((c) => c.id);
    let cards: pg.QueryResult | undefined;
    if (columnIds.length > 0) {
      cards = await pool.query("SELECT * FROM cards WHERE column_id = ANY($1) ORDER BY position", [
        columnIds,
      ]);
    }

    const cardsByColumn = new Map<
      number,
      typeof cards extends undefined ? never : typeof cards.rows
    >();
    for (const card of cards?.rows ?? []) {
      const existing = cardsByColumn.get(card.column_id) ?? [];
      existing.push(card);
      cardsByColumn.set(card.column_id, existing);
    }

    res.json({
      ...board.rows[0],
      columns: columns.rows.map((col) => ({
        ...col,
        cards: cardsByColumn.get(col.id) ?? [],
      })),
    });
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Failed to fetch board" });
  }
});

router.post("/", async (req: Request, res: Response) => {
  try {
    const { name, description } = req.body as { name: string; description?: string };
    if (!name) {
      res.status(400).json({ error: "Name is required" });
      return;
    }
    const result = await pool.query(
      "INSERT INTO boards (name, description) VALUES ($1, $2) RETURNING *",
      [name, description ?? null]
    );
    res.status(201).json(result.rows[0]);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Failed to create board" });
  }
});

router.delete("/:id", async (req: Request, res: Response) => {
  try {
    const result = await pool.query("DELETE FROM boards WHERE id = $1 RETURNING *", [
      req.params.id,
    ]);
    if (result.rows.length === 0) {
      res.status(404).json({ error: "Board not found" });
      return;
    }
    res.json({ message: "Board deleted" });
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Failed to delete board" });
  }
});

export default router;

// src/routes/columns.ts
import { Router, Request, Response } from "express";
import pool from "../db/pool.js";

const router = Router();

router.post("/", async (req: Request, res: Response) => {
  try {
    const { board_id, name } = req.body as { board_id: number; name: string };
    if (!board_id || !name) {
      res.status(400).json({ error: "board_id and name are required" });
      return;
    }

    // Position after the last column
    const lastCol = await pool.query(
      "SELECT COALESCE(MAX(position), 0) + 1024 AS next_pos FROM columns WHERE board_id = $1",
      [board_id]
    );
    const position = lastCol.rows[0].next_pos;

    const result = await pool.query(
      "INSERT INTO columns (board_id, name, position) VALUES ($1, $2, $3) RETURNING *",
      [board_id, name, position]
    );
    res.status(201).json(result.rows[0]);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Failed to create column" });
  }
});

router.put("/:id/reorder", async (req: Request, res: Response) => {
  try {
    const { id } = req.params;
    const { position } = req.body as { position: number };

    if (typeof position !== "number") {
      res.status(400).json({ error: "position is required" });
      return;
    }

    const result = await pool.query("UPDATE columns SET position = $1 WHERE id = $2 RETURNING *", [
      position,
      id,
    ]);

    if (result.rows.length === 0) {
      res.status(404).json({ error: "Column not found" });
      return;
    }

    res.json(result.rows[0]);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Failed to reorder column" });
  }
});

router.delete("/:id", async (req: Request, res: Response) => {
  try {
    const result = await pool.query("DELETE FROM columns WHERE id = $1 RETURNING *", [
      req.params.id,
    ]);
    if (result.rows.length === 0) {
      res.status(404).json({ error: "Column not found" });
      return;
    }
    res.json({ message: "Column deleted" });
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Failed to delete column" });
  }
});

export default router;

// src/routes/cards.ts
import { Router, Request, Response } from "express";
import pool from "../db/pool.js";

const router = Router();

router.post("/", async (req: Request, res: Response) => {
  try {
    const { column_id, title, description, assignee, labels, due_date } = req.body as {
      column_id: number;
      title: string;
      description?: string;
      assignee?: string;
      labels?: string[];
      due_date?: string;
    };

    if (!column_id || !title) {
      res.status(400).json({ error: "column_id and title are required" });
      return;
    }

    const lastCard = await pool.query(
      "SELECT COALESCE(MAX(position), 0) + 1024 AS next_pos FROM cards WHERE column_id = $1",
      [column_id]
    );

    const result = await pool.query(
      `INSERT INTO cards (column_id, title, description, position, assignee, labels, due_date)
       VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *`,
      [
        column_id,
        title,
        description ?? null,
        lastCard.rows[0].next_pos,
        assignee ?? null,
        labels ?? [],
        due_date ?? null,
      ]
    );

    res.status(201).json(result.rows[0]);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Failed to create card" });
  }
});

router.put("/:id", async (req: Request, res: Response) => {
  try {
    const { id } = req.params;
    const { title, description, assignee, labels, due_date } = req.body as {
      title?: string;
      description?: string;
      assignee?: string;
      labels?: string[];
      due_date?: string;
    };

    const result = await pool.query(
      `UPDATE cards SET
        title = COALESCE($1, title),
        description = COALESCE($2, description),
        assignee = COALESCE($3, assignee),
        labels = COALESCE($4, labels),
        due_date = COALESCE($5, due_date),
        updated_at = NOW()
       WHERE id = $6 RETURNING *`,
      [title, description, assignee, labels, due_date, id]
    );

    if (result.rows.length === 0) {
      res.status(404).json({ error: "Card not found" });
      return;
    }

    res.json(result.rows[0]);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Failed to update card" });
  }
});

router.put("/:id/move", async (req: Request, res: Response) => {
  try {
    const { id } = req.params;
    const { column_id, position } = req.body as { column_id: number; position: number };

    if (!column_id || typeof position !== "number") {
      res.status(400).json({ error: "column_id and position are required" });
      return;
    }

    const result = await pool.query(
      "UPDATE cards SET column_id = $1, position = $2, updated_at = NOW() WHERE id = $3 RETURNING *",
      [column_id, position, id]
    );

    if (result.rows.length === 0) {
      res.status(404).json({ error: "Card not found" });
      return;
    }

    res.json(result.rows[0]);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Failed to move card" });
  }
});

router.delete("/:id", async (req: Request, res: Response) => {
  try {
    const result = await pool.query("DELETE FROM cards WHERE id = $1 RETURNING *", [req.params.id]);
    if (result.rows.length === 0) {
      res.status(404).json({ error: "Card not found" });
      return;
    }
    res.json({ message: "Card deleted" });
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Failed to delete card" });
  }
});

export default router;

// src/middleware/position.ts

/**
 * Calculate a position between two existing positions.
 * Used when inserting a card between two others during drag-and-drop.
 */
export function midpoint(before: number | null, after: number | null): number {
  if (before === null && after === null) return 1024;
  if (before === null) return (after as number) / 2;
  if (after === null) return before + 1024;
  return (before + after) / 2;
}

/**
 * When positions get too close together (less than 1 apart),
 * rebalance all items in the list with even spacing.
 */
export function needsRebalance(positions: number[]): boolean {
  for (let i = 1; i < positions.length; i++) {
    if (positions[i] - positions[i - 1] < 1) return true;
  }
  return false;
}

export function rebalancePositions(count: number): number[] {
  const spacing = 1024;
  return Array.from({ length: count }, (_, i) => (i + 1) * spacing);
}

// src/middleware/error-handler.ts
import { Request, Response, NextFunction } from "express";

export function errorHandler(err: Error, _req: Request, res: Response, _next: NextFunction): void {
  console.error("Unhandled error:", err.message);
  res.status(500).json({
    error: "Internal server error",
    message: process.env.NODE_ENV === "development" ? err.message : undefined,
  });
}

// src/server.ts
import express from "express";
import dotenv from "dotenv";
import boardRoutes from "./routes/boards.js";
import columnRoutes from "./routes/columns.js";
import cardRoutes from "./routes/cards.js";
import { errorHandler } from "./middleware/error-handler.js";

dotenv.config();

const app = express();
const port = parseInt(process.env.PORT ?? "3000", 10);

app.use(express.json());

// Routes
app.use("/api/boards", boardRoutes);
app.use("/api/columns", columnRoutes);
app.use("/api/cards", cardRoutes);

// Health check
app.get("/health", (_req, res) => {
  res.json({ status: "ok" });
});

// Error handler
app.use(errorHandler);

app.listen(port, () => {
  console.log(`Kanban API running on http://localhost:${port}`);
});

npm run dev

# Create a board
curl -X POST http://localhost:3000/api/boards \
  -H "Content-Type: application/json" \
  -d '{"name": "Sprint 1", "description": "Q1 sprint board"}'

# Create columns
curl -X POST http://localhost:3000/api/columns \
  -H "Content-Type: application/json" \
  -d '{"board_id": 1, "name": "To Do"}'

curl -X POST http://localhost:3000/api/columns \
  -H "Content-Type: application/json" \
  -d '{"board_id": 1, "name": "In Progress"}'

curl -X POST http://localhost:3000/api/columns \
  -H "Content-Type: application/json" \
  -d '{"board_id": 1, "name": "Done"}'

# Create a card
curl -X POST http://localhost:3000/api/cards \
  -H "Content-Type: application/json" \
  -d '{"column_id": 1, "title": "Set up CI", "assignee": "alice"}'

# Move card to "In Progress" column
curl -X PUT http://localhost:3000/api/cards/1/move \
  -H "Content-Type: application/json" \
  -d '{"column_id": 2, "position": 1024}'

# Get full board with columns and cards
curl http://localhost:3000/api/boards/1