Designing a Versioned Prompt Template Database

Welcome back. In our previous lesson, you successfully constructed the REST API for our Tool Store, creating a functional, layered service to manage tool definitions. We now have a solid foundation for one of the key resources our agents will use.

In this lesson, we shift our focus to the second critical resource: prompts. We will address the learning outcome of designing a database schema for storing prompt templates with metadata and versioning. Just as we treated tools as managed assets, we will now establish a systematic way to handle prompts. This involves moving beyond simple string variables in code to treating prompts as versioned, auditable, and configurable components of our production system. We'll focus exclusively on the database design here, laying the groundwork for implementing the corresponding API in our next session.

1. Why a Dedicated Prompt Store?

As applications grow, managing prompts as loose files or hardcoded strings becomes a significant liability. This ad-hoc approach leads to inconsistency, difficulty in tracking changes, and an inability to audit which prompt version produced a specific output.

The video "Prompt Management 101" by Dave Ebbelaar provides a great overview of the common pitfalls and the need for a more structured approach.

The problems described in the video—phantom regressions, wasted debugging cycles, and lack of an audit trail—underscore why a robust prompt management system isn't an optional extra, but a core component of a production-grade AI infrastructure. As seen in many real-world architectures, a "Prompt Catalog" or "Prompt Store" is a standard, centralized service.

2. Core Principle: Prompts as Versioned Contracts

To solve the problems of ad-hoc prompt management, we will adopt a powerful principle: treat prompts as versioned contracts. This means a prompt is not just a piece of text; it's a formal agreement on how an agent should behave. Any change to this contract creates a new version, ensuring that every output from our system can be traced back to the exact prompt version that generated it.

This concept is thoroughly explored in the blog post "Prompt templates evaluation contracts" from Ademar Tutor's blog.

At a low level, database versioning can be implemented in complex ways, such as with delta tables that track additions and deletions.

However, for our use case, a more direct and practical approach is sufficient. Instead of tracking deltas, we will store each version of a prompt as a complete, immutable row in our database. This is simpler to implement and query, and perfectly aligns with the "prompt as contract" model.

3. Designing the Database Schema

To create a flexible and scalable Prompt Store, we will use a two-table design in PostgreSQL. This separates the identity of a prompt from its versioned content.

1. prompts table: This table defines the core identity of a prompt template.
2. prompt_versions table: This table stores the specific, versioned content and metadata for each prompt.

This separation allows us to enforce certain rules (like the prompt's structural type) at the parent level while allowing for rich versioning and metadata at the child level. Let's examine the fields for each table.

The prompts Table

This table acts as the master record for a named prompt within a specific workspace.

Column Name	Data Type	Constraints & Description
id	UUID	Primary Key. A unique identifier for the prompt record.
workspace_id	UUID	Foreign Key to a workspaces table. Can be NULL for global/system-wide prompts. Enforces multi-tenancy.
name	VARCHAR(255)	The unique, human-readable name of the prompt (e.g., summarize-document). We'll add a UNIQUE constraint on (workspace_id, name).
structure_type	ENUM	The structural type of the prompt, e.g., 'TEXT' or 'CHAT'. This is immutable; a prompt cannot change its fundamental structure.
created_at	TIMESTAMPTZ	Timestamp of creation.
updated_at	TIMESTAMPTZ	Timestamp of the last update (e.g., if metadata on the parent record changes).

The structure_type is particularly important. A simple 'TEXT' prompt might just be a string template, while a 'CHAT' prompt could be a structured array of messages (e.g., with system, user, and assistant roles), as is common with modern LLM APIs. Storing this at the parent level enforces consistency across all versions of a prompt. This idea is well-documented in professional tooling, as seen in Comet's Opik documentation.

The prompt_versions Table

This is the heart of our versioning system. Each row represents a specific "contract" version of a prompt.

Column Name	Data Type	Constraints & Description
id	UUID	Primary Key. A unique identifier for this specific version.
prompt_id	UUID	Foreign Key to prompts.id. Links this version back to its parent prompt.
version	INTEGER	The version number. This can be an auto-incrementing integer scoped to the prompt_id.
content	JSONB	The prompt template itself. Using JSONB is highly flexible. It can store a simple string or a complex JSON object for chat templates with multiple messages and roles.
metadata	JSONB	A flexible field for storing metadata like { "author": "user@example.com", "target_model": "gpt-4", "change_description": "Initial version" }.
tags	TEXT[]	A PostgreSQL array to store tags like production, staging, or beta. Useful for filtering and promotion workflows.
is_active	BOOLEAN	A flag to indicate if this is the currently active version for its parent prompt.
created_at	TIMESTAMPTZ	Timestamp of when this version was created.

The combination of JSONB for content/metadata and TEXT[] for tags gives us immense power and flexibility. More importantly, the is_active flag is how we implement the "active contract" pattern. To ensure that only one version of a prompt can be active at any time, we can use a powerful PostgreSQL feature: a unique partial index.

CREATE UNIQUE INDEX one_active_version_per_prompt
ON prompt_versions (prompt_id)
WHERE is_active = TRUE;

Your experience with backend development means you'll appreciate how this database-level constraint provides a robust guarantee for our business logic, preventing data inconsistencies before they can even occur.

To see how this all fits together, let's revisit the "Prompt templates evaluation contracts" article, which provides a schema very similar to our design.

The Opik documentation also offers a comprehensive list of fields used for version filtering in their system, which further validates our choices for metadata, tags, and other fields.

Conclusion

In this lesson, you have designed a comprehensive, production-ready database schema for a multi-tenant Prompt Store. By embracing the "prompts as versioned contracts" principle and using a two-table structure, you've laid the groundwork for a system that is scalable, auditable, and robust.

Key Takeaways:

• Prompt as Contract: Prompts are critical configurations that require systematic management, including versioning and audit trails.
• Two-Table Schema: Separating a prompt's identity (prompts table) from its versioned content (prompt_versions table) provides a clean and scalable design.
• Flexible Data Types: Using JSONB for content and metadata allows the schema to gracefully handle both simple text and complex, structured chat prompts without modification.
• Database-Level Guarantees: Employing constraints like unique partial indexes (is_active) offloads business logic to the database, ensuring data integrity.
• Rich Metadata: Including fields for version, tags, and free-form metadata enables powerful querying, filtering, and management workflows.

You now have a complete blueprint for our Prompt Store. In the next lesson, we will bring this design to life by implementing the REST API for creating, retrieving, and managing these prompt templates, applying the same layered architecture you used for the Tool Store.