Overview
The prompt_versions table stores immutable snapshots of prompt content. Each row represents a single version, identified by version_number within a prompt. This is the “versions” half of the two-table versioning pattern.
Schema
Primary key. Auto-generated using gen_random_uuid().
Parent prompt. References prompts(id) with ON DELETE CASCADE.
Sequential version number starting at 1. Must be unique per prompt_id (enforced by unique_prompt_version constraint).
The prompt template content. May include variable placeholders like {{variable_name}} for later substitution.
Optional human-readable note describing this version (e.g., “Added error handling”, “Fixed typo in instructions”).
Creation timestamp. Defaults to timezone('utc', now()).
Constraints
ALTER TABLE prompt_versions
ADD CONSTRAINT unique_prompt_version
UNIQUE (prompt_id, version_number);
Indexes
CREATE INDEX idx_prompt_versions_prompt_id ON prompt_versions(prompt_id);
get_latest_prompt_content() and version history queries.
Row Level Security
CREATE POLICY "Users can view versions of their own prompts"
ON prompt_versions FOR SELECT
USING (
EXISTS (
SELECT 1 FROM prompts
WHERE prompts.id = prompt_versions.prompt_id
AND prompts.user_id = auth.uid()
)
);
CREATE POLICY "Users can insert versions for their own prompts"
ON prompt_versions FOR INSERT
WITH CHECK (
EXISTS (
SELECT 1 FROM prompts
WHERE prompts.id = prompt_versions.prompt_id
AND prompts.user_id = auth.uid()
)
);
Immutability
Version rows are append-only. The schema intentionally omits:
updated_at column (versions never change)- UPDATE policies (no edits allowed)
- DELETE policies (history is permanent)
If you need to “fix” a version, create a new version with incremented version_number.
Relationships
- Many-to-One with
prompts: Each version belongs to one prompt
- One-to-Many with
prompt_snapshots: Snapshots reference a specific version
Querying Versions
Get Latest Content
SELECT content FROM prompt_versions
WHERE prompt_id = 'uuid-here'
ORDER BY version_number DESC
LIMIT 1;
Get Full Version History
SELECT * FROM prompt_versions
WHERE prompt_id = 'uuid-here'
ORDER BY version_number ASC;
Count Versions
SELECT COUNT(*) FROM prompt_versions
WHERE prompt_id = 'uuid-here';
Helper Functions
get_latest_prompt_content
CREATE FUNCTION get_latest_prompt_content(p_id UUID) RETURNS TEXT AS $$
SELECT content FROM prompt_versions
WHERE prompt_id = p_id
ORDER BY version_number DESC LIMIT 1;
$$ LANGUAGE sql STABLE;
get_latest_prompt_version_id
CREATE FUNCTION get_latest_prompt_version_id(p_id UUID) RETURNS UUID AS $$
SELECT id FROM prompt_versions
WHERE prompt_id = p_id
ORDER BY version_number DESC LIMIT 1;
$$ LANGUAGE sql STABLE;
PromptWithLatestVersion type).
Triggers
Search Index Update
CREATE TRIGGER tr_prompt_versions_search_update
AFTER INSERT ON prompt_versions
FOR EACH ROW EXECUTE FUNCTION update_prompt_search_tokens_trigger();
prompts row’s search_tokens field is recomputed to include the new content. See supabase/migrations/20260208000002_search_index.sql:17.
Creating a New Version
Server Actions use this pattern:
// src/features/prompts/actions.ts (conceptual)
const { data: versions } = await supabase
.from('prompt_versions')
.select('version_number')
.eq('prompt_id', promptId)
.order('version_number', { ascending: false })
.limit(1);
const nextVersionNumber = versions?.[0]?.version_number + 1 || 1;
await supabase.from('prompt_versions').insert({
prompt_id: promptId,
version_number: nextVersionNumber,
content: newContent,
version_note: 'Updated via web editor',
});
TypeScript Types
// src/features/prompts/types/index.ts
export interface PromptVersion {
id: string;
prompt_id: string;
version_number: number;
content: string;
version_note: string | null;
created_at: string;
}
Migration File
- 20260208000001_prompt_schema.sql:12: Initial table creation and policies