Prompt Template API: CRUD Operations

Welcome back! In our last lesson, we meticulously designed the database schema for our Prompt Store, establishing a two-table structure to manage prompts as versioned, auditable contracts. We now have a solid blueprint for how to store prompt identities, their versioned content, and crucial metadata.

This lesson bridges the gap between that design and a functioning service. We will implement the REST API endpoints for creating, retrieving, and updating the prompt templates stored in our database. By the end of this session, you will have built the backend service that brings our Prompt Store to life, making it a queryable and manageable resource for our future agentic system. This process will mirror the layered architecture you've used before, reinforcing best practices for building scalable microservices.

1. Architectural Approach: Express.js Microservice

As a developer with extensive experience in both monolithic and microservice architectures, you know there are multiple ways to build an API. We could use Next.js API routes, which you're familiar with, or we could build a dedicated microservice. For this Prompt Store, we will take the latter approach and use Express.js with TypeScript.

This choice offers several advantages for our project:

• Decoupling: It creates a distinct, standalone service for prompt management, completely independent of any front-end or other agentic services. This is a hallmark of robust, scalable backend systems.
• Focus: The service will have a single responsibility: managing prompts. This aligns with the "S" in SOLID principles (Single Responsibility Principle).
• Portability: A self-contained Express service is easy to containerize with Docker and deploy anywhere, which will be covered in a later module.

This approach is a standard industry practice for building backend services, and the provided resources offer excellent guidance on implementing it.

2. Project Setup and Dependencies

Let's begin by setting up our project. We will follow a structure similar to what's demonstrated in the "TypeScript Express" tutorials.

First, create a new directory for our service, e.g., prompt-store-service, and initialize a Node.js project:

mkdir prompt-store-service
cd prompt-store-service
npm init -y

Next, we'll install the necessary dependencies. We need Express for the web server, TypeScript and its related tools, a PostgreSQL driver (pg), and an ORM to interact with our database. The article "Building an API with TypeScript, Express.js and Sequelize" uses Sequelize, which is an excellent choice for a mature, promise-based ORM for Node.js with strong TypeScript support.

# Core dependencies
npm install express body-parser pg sequelize




# Development dependencies
npm install --save-dev typescript ts-node nodemon @types/express @types/node @types/sequelize

Now, configure TypeScript by creating a tsconfig.json file in the root directory. You can use the configuration from the provided resources as a starting point. This file will instruct the TypeScript compiler how to transpile our code. Similarly, a nodemon.json file can be set up to automatically restart the server during development, a workflow detailed in the "REST API Tutorial" video.

3. Application Structure

To keep our code organized and maintainable, we will implement a layered architecture. This is a pattern you've likely employed many times in your career.

• src/
  • config/: Database connection settings (e.g., database.ts).
  • controllers/: Handles HTTP requests and responses (e.g., prompt.controller.ts).
  • models/: Defines the Sequelize models for our prompts and prompt_versions tables.
  • routes/: Defines the API routes and maps them to controllers (e.g., prompt.routes.ts).
  • services/: Encapsulates the core business logic.
  • app.ts: The main application file where we configure Express.
  • server.ts: The entry point that starts the server.

This structure cleanly separates concerns, making the application easier to understand, debug, and extend.

4. Implementing the CRUD Endpoints

Now, we'll implement the API endpoints based on the schema we designed. I'll focus on the key operations: creating a new prompt, creating a new version of a prompt, and retrieving prompts.

The following video and article provide excellent, practical examples of setting up Express, defining routes, and writing controller logic. We will adapt their patterns for our specific needs.

A. Creating Prompts and Versions

This is the most critical part of our API, as it involves transactional logic to maintain data integrity.

1. POST /prompts - Create a new prompt with its first version

This endpoint creates a new "master" prompt and its initial version in one atomic operation.

• Route: router.post('/', promptController.createPrompt);

• Request Body:

  {
    "name": "document-summarizer",
    "structure_type": "CHAT",
    "content": [
      { "role": "system", "content": "You are a helpful assistant that summarizes documents." },
      { "role": "user", "content": "Summarize the following document:\n\n{{document}}" }
    ],
    "metadata": { "author": "dev@example.com" },
    "tags": ["initial-release"]
  }
  

• Controller (prompt.controller.ts): The controller's job is to extract the body from the request and pass it to a service.

• Service (prompt.service.ts): This is where the core logic resides. You will use a Sequelize transaction to ensure both database inserts succeed or fail together.

  // Inside a service method
  const t = await sequelize.transaction();
  try {
    // 1. Create the parent prompt
    const prompt = await Prompt.create({
      name: body.name,
      structure_type: body.structure_type,
      workspace_id: body.workspace_id // assuming multi-tenancy
    }, { transaction: t });
  
    // 2. Create the first version
    const promptVersion = await PromptVersion.create({
      prompt_id: prompt.id,
      version: 1,
      content: body.content,
      metadata: body.metadata,
      tags: body.tags,
      is_active: true
    }, { transaction: t });
  
    // 3. Commit the transaction
    await t.commit();
    
    return { ...prompt.toJSON(), version: promptVersion.toJSON() };
  
  } catch (error) {
    // 4. Rollback on error
    await t.rollback();
    throw new Error("Failed to create prompt and initial version.");
  }
  

  Your experience with backend development means you understand the critical importance of this transactional integrity.

2. POST /prompts/:promptId/versions - Create a new version for an existing prompt

This endpoint adds a new version to a prompt that already exists.

• Route: router.post('/:promptId/versions', promptController.createVersion);
• Request Body: Same as before, but may include an optional boolean make_active.
• Service Logic: This also requires a transaction.
  1. Fetch the promptId from the URL parameters.
  2. If body.make_active is true, first update the current active version for this promptId by setting is_active = false. The unique partial index we designed in the last lesson provides a strong database-level guarantee against race conditions, but it's good practice to handle this explicitly in the service layer.
  3. Calculate the new version number (e.g., max(version) + 1 for the given promptId).
  4. Create the new PromptVersion record.
  5. Commit the transaction.

B. Retrieving Prompts

For agents to use our Prompt Store, they need efficient ways to retrieve prompts. We'll implement a few key GET endpoints.

1. GET /prompts
Lists all parent prompts. This could be enhanced with query parameters to include active versions, filter by tags, etc.

2. GET /prompts/:promptId
Retrieves a single parent prompt and all of its associated versions, allowing a user to see the entire history.

3. GET /prompts/:promptId/versions/:versionNumber
Fetches a specific, immutable version of a prompt. This is crucial for auditing and debugging, as it allows you to retrieve the exact "contract" used for a past generation.

4. GET /prompts/name/:name/active
This is perhaps the most useful endpoint for a running agent. An agent often knows the name of the prompt it needs (e.g., summarize-document). This endpoint allows it to fetch the currently active version of that prompt by its unique name within a workspace. The implementation would involve a JOIN between the prompts and prompt_versions tables.

C. Updating Prompts

According to our "prompt as contract" model, a version's content is immutable. However, we might want to update its metadata or promote a different version to be the active one.

1. PUT /prompts/:promptId/versions/:versionNumber - Update metadata
This endpoint could be used to modify mutable fields like tags or metadata on a specific version.

2. PUT /prompts/:promptId/versions/:versionNumber/activate - Promote a version
This is a key workflow endpoint. It allows a developer to promote a specific, tested version to become the new "active" version.

• Service Logic: Inside a transaction:
  1. Find the current active version for the promptId and set is_active to false.
  2. Find the target version (versionNumber) and set is_active to true.
  3. Commit.

Conclusion

In this lesson, you have translated our database design into a functional, production-ready REST API. By building a dedicated Express.js microservice, you've created a clean, scalable, and decoupled component for our agentic infrastructure.

Key Takeaways:

• Microservice Architecture: Using Express.js for a dedicated Prompt Store service promotes decoupling and scalability.
• Layered Structure: Organizing code into routes, controllers, services, and models enhances maintainability.
• Transactional Integrity: Using database transactions is essential for complex operations like creating a prompt with its first version or activating a new version, ensuring data consistency.
• Workflow-Oriented Endpoints: Beyond basic CRUD, creating endpoints like GET /prompts/name/:name/active and PUT /.../activate provides a user-friendly API that maps directly to common development and operational workflows.
• Immutability in Practice: The API design reinforces the "prompt as contract" principle by making prompt content immutable while allowing metadata updates and version promotion.

With both the Tool Store and Prompt Store APIs now implemented, we have two of the most critical foundational services for our system. In the upcoming lessons, we will start consuming these services, beginning with implementing dynamic loading of tools and prompts into our agents at runtime.