Managing Tool Store API Endpoints

Welcome back to our journey of building a production-grade agentic infrastructure. In the last lesson, we meticulously designed the database schema for our Tool Store, creating a solid blueprint for storing and managing tool definitions. Now it's time to bring that blueprint to life by building the interface that will allow us to interact with it.

This lesson focuses on implementing the REST API endpoints for the Tool Store. You will build the complete set of Create, Read, Update, and Delete (CRUD) operations, transforming your database schema into a fully functional service. This service will be the backbone of your tool management system, enabling you to programmatically register, modify, and retrieve tools. The final result will be a clean, standard API, much like the one shown in this Swagger interface.

To achieve this, we will adopt a layered architecture using Node.js, Express, and TypeScript. This professional standard separates concerns into distinct layers—routing, controllers, and services—making the application scalable, testable, and maintainable.

1. The Three-Layer Architecture

A robust API isn't built in a single file. We'll organize our code into a logical structure that separates responsibilities. This approach is standard in professional web development and will be familiar given your background with frameworks like Spring Boot and Laravel.

A fantastic, practical demonstration of this architecture can be found in the video "Build a CRUD Rest API with Node.js, Express, PostgreSQL & Docker" by Dipesh Malvia.

Our structure will consist of:

• Routes (/routes): Defines the API paths (e.g., /api/tools/:id) and maps them to controller functions.
• Controllers (/controllers): Handles the incoming HTTP request and outgoing response. It orchestrates the process but contains minimal business logic.
• Services (/services): Contains the core business logic, such as interacting with the database. This layer is where our SQL queries will live.

2. Implementing the Service Layer

We'll start from the bottom up: the service layer. This layer is responsible for all direct communication with the PostgreSQL database. Its job is to execute SQL queries and return the results, completely abstracting the database logic from the rest of the application.

We will create a tools.service.ts file. For the implementation, we'll draw inspiration from the "Node.js and TypeScript Tutorial" by Auth0, which demonstrates how to create a clean service layer. While it uses an in-memory store, we will adapt its structure to use our PostgreSQL database via the pg library's connection pool.

First, let's set up the database connection. In a db.ts file, we'll configure a connection pool from the pg library. A pool is much more efficient than creating a new client for every query, which is essential for a production service.

// src/common/db.ts
import { Pool } from 'pg';
import *s dotenv from 'dotenv';

dotenv.config();

const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_DATABASE,
  password: process.env.DB_PASSWORD,
  port: parseInt(process.env.DB_PORT || '5432'),
});

export default pool;

Now, in src/tools/tools.service.ts, we'll implement the CRUD functions using this pool. Each function will correspond to a specific SQL operation on the tools table you designed in the previous lesson.

// src/tools/tools.service.ts
import pool from '../common/db';
import { Tool, BaseTool } from './tool.interface'; // We'll define these interfaces next

// Create (Register) a new tool
export const create = async (toolData: BaseTool): Promise<Tool> => {
  const { workspace_id, name, description, version, category, type, configuration, input_schema, required_secret_names } = toolData;
  const result = await pool.query(
    'INSERT INTO tools (workspace_id, name, description, version, category, type, configuration, input_schema, required_secret_names) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING *',
    [workspace_id, name, description, version, category, type, configuration, input_schema, required_secret_names]
  );
  return result.rows[0];
};

// Read all tools (with filtering for multi-tenancy)
export const findAll = async (workspaceId: string): Promise<Tool[]> => {
  // Query for tools specific to the workspace OR public tools (where workspace_id is NULL)
  const result = await pool.query('SELECT * FROM tools WHERE workspace_id = $1 OR workspace_id IS NULL', [workspaceId]);
  return result.rows;
};

// Read a single tool
export const find = async (id: string, workspaceId: string): Promise<Tool | null> => {
  const result = await pool.query('SELECT * FROM tools WHERE id = $1 AND (workspace_id = $2 OR workspace_id IS NULL)', [id, workspaceId]);
  return result.rows[0] || null;
};

// Update a tool
export const update = async (id: string, toolUpdate: Partial<BaseTool>): Promise<Tool | null> => {
  // This is a simplified update. A real implementation would dynamically build the SET clause.
  const { name, description, version, configuration, input_schema } = toolUpdate;
  const result = await pool.query(
    'UPDATE tools SET name = $1, description = $2, version = $3, configuration = $4, input_schema = $5, updated_at = NOW() WHERE id = $6 RETURNING *',
    [name, description, version, configuration, input_schema, id]
  );
  return result.rows[0] || null;
};

// Delete a tool
export const remove = async (id: string): Promise<null | void> => {
  await pool.query('DELETE FROM tools WHERE id = $1', [id]);
};

Note the explicit workspace_id filtering in the findAll and find functions. This is the direct implementation of the multi-tenancy strategy we designed, ensuring one tenant cannot access another's private tools.

3. Defining Data Structures and Validation

To ensure type safety and validate incoming API requests, we need to define TypeScript interfaces for our Tool and use Zod for validation, just as we did in Module 2.

// src/tools/tool.interface.ts
export interface BaseTool {
  workspace_id?: string | null;
  name: string;
  description: string;
  version: string;
  category: string;
  type: 'HTTP' | 'JAVASCRIPT'; // Or other types you support
  configuration: object;
  input_schema: object;
  required_secret_names: string[];
}

export interface Tool extends BaseTool {
  id: string;
  created_at: Date;
  updated_at: Date;
}

For validation, we'll create Zod schemas. This prevents invalid data from even reaching our service layer. The article "Transform Your Node.js REST API into an AI-Ready MCP Server" provides a relevant example of using Zod to define schemas for tool inputs.

We can create a tool.validation.ts file to hold our Zod schema for creating and updating tools.

// src/tools/tool.validation.ts
import { z } from 'zod';

export const toolSchema = z.object({
  name: z.string().min(3, "Name must be at least 3 characters long"),
  description: z.string().min(10, "Description must be at least 10 characters long"),
  version: z.string().regex(/^\d+\.\d+\.\d+$/, "Version must be in semantic format (e.g., 1.0.0)"),
  category: z.string(),
  type: z.enum(['HTTP', 'JAVASCRIPT']),
  configuration: z.object({}).passthrough(), // Allows any object structure
  input_schema: z.object({}).passthrough(),
  required_secret_names: z.array(z.string()).optional(),
  workspace_id: z.string().uuid().optional().nullable(),
});

We will use this schema in a middleware to validate the request body for our POST and PUT/PATCH endpoints.

4. Implementing the Controller and Router Layer

With the service logic and validation in place, we can now build the outermost layer: the controllers and the router. The router defines the API endpoints, and the controllers link them to the services.

The Auth0 article provides a perfect template for creating a typed Express router file.

Following this pattern, we'll create src/tools/tools.router.ts:

// src/tools/tools.router.ts
import express, { Request, Response } from "express";
import * as ToolService from "./tools.service";
import { BaseTool, Tool } from "./tool.interface";
import { toolSchema } from "./tool.validation";

export const toolsRouter = express.Router();

// Middleware for Zod validation
const validate = (schema: any) => (req: Request, res: Response, next: Function) => {
  try {
    schema.parse(req.body);
    next();
  } catch (e: any) {
    return res.status(400).send(e.errors);
  }
};

// POST /api/tools - Register a new tool
toolsRouter.post("/", validate(toolSchema), async (req: Request, res: Response) => {
  try {
    const tool: BaseTool = req.body;
    const newTool = await ToolService.create(tool);
    res.status(201).json(newTool);
  } catch (e: any) {
    res.status(500).send(e.message);
  }
});

// GET /api/tools - Find all tools for a workspace
toolsRouter.get("/", async (req: Request, res: Response) => {
  try {
    // In a real app, workspaceId would come from authenticated user session/token
    const workspaceId = req.header('x-workspace-id'); 
    if (!workspaceId) {
        return res.status(400).send("x-workspace-id header is required.");
    }

    const allTools: Tool[] = await ToolService.findAll(workspaceId);
    res.status(200).send(allTools);
  } catch (e: any) {
    res.status(500).send(e.message);
  }
});

// ... other routes for GET by ID, PUT, and DELETE would follow a similar pattern ...

// DELETE /api/tools/:id - Delete a tool
toolsRouter.delete("/:id", async (req: Request, res: Response) => {
    try {
        const id: string = req.params.id;
        await ToolService.remove(id);
        res.sendStatus(204); // No Content
    } catch (e: any) {
        res.status(500).send(e.message);
    }
});

Finally, you mount this router in your main application file (index.ts).

// src/index.ts
import express from "express";
import cors from "cors";
import helmet from "helmet";
import { toolsRouter } from "./tools/tools.router";

const app = express();

app.use(helmet());
app.use(cors());
app.use(express.json());

// Mount the router
app.use("/api/tools", toolsRouter);

const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);
});

Conclusion

In this lesson, you have successfully built a complete, production-ready REST API for your Tool Store. By following a layered architecture, you've created a system that is robust, scalable, and easy to maintain.

Key Takeaways:

• Layered Architecture: You implemented a clean separation of concerns with routes, controllers, and services, a best practice for professional application development.
• Service Layer: You created a dedicated service that encapsulates all database interactions, executing SQL queries against the schema you previously designed.
• Validation: You used Zod to define schemas for incoming data, creating a validation middleware to protect your API from invalid input.
• CRUD Endpoints: You implemented the full suite of CRUD operations (POST, GET, PUT, DELETE), enabling complete programmatic management of your tools.
• Multi-Tenancy in Practice: You enforced data isolation by adding workspace_id checks directly into your service layer queries.

You now have a functional Tool Store service. The next logical step is to build the corresponding infrastructure for managing prompts. In our upcoming lesson, you will apply these same architectural principles to design the database schema for the Prompt Store.