-- ============================================
-- Dynamic Prompt Configuration System
-- ============================================
-- Allows runtime editing of chatbot prompts without redeployment
-- Date: 2026-02-01
-- Main prompt configuration table
CREATE TABLE IF NOT EXISTS prompt_configs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Identifier for the prompt (e.g., 'gordie_base', 'gordie_context_bill', 'factcheck_system')
key VARCHAR(100) NOT NULL UNIQUE,
-- Human-readable name and description
name VARCHAR(255) NOT NULL,
description TEXT,
-- The actual prompt content
content TEXT NOT NULL,
-- Categorization
category VARCHAR(50) NOT NULL DEFAULT 'chatbot', -- 'chatbot', 'factcheck', 'tools', 'other'
-- Version tracking for auditing
version INT NOT NULL DEFAULT 1,
-- Whether this prompt is currently active
is_active BOOLEAN NOT NULL DEFAULT true,
-- Metadata
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_by UUID REFERENCES auth.users(id),
-- For tracking changes
previous_content TEXT -- Store previous version on update
);
-- Prompt history table for full audit trail
CREATE TABLE IF NOT EXISTS prompt_config_history (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
prompt_config_id UUID NOT NULL REFERENCES prompt_configs(id) ON DELETE CASCADE,
version INT NOT NULL,
content TEXT NOT NULL,
changed_by UUID REFERENCES auth.users(id),
changed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
change_reason TEXT
);
-- Indexes for faster lookups
CREATE INDEX idx_prompt_configs_key ON prompt_configs(key);
CREATE INDEX idx_prompt_configs_category ON prompt_configs(category);
CREATE INDEX idx_prompt_configs_active ON prompt_configs(is_active) WHERE is_active = true;
CREATE INDEX idx_prompt_config_history_config_id ON prompt_config_history(prompt_config_id);
-- Enable Row Level Security
ALTER TABLE prompt_configs ENABLE ROW LEVEL SECURITY;
ALTER TABLE prompt_config_history ENABLE ROW LEVEL SECURITY;
-- RLS Policies: Anyone can read active prompts (cached server-side)
-- Only admins can modify
CREATE POLICY "Anyone can read active prompts"
ON prompt_configs
FOR SELECT
USING (is_active = true);
CREATE POLICY "Admins can manage prompts"
ON prompt_configs
FOR ALL
USING (
EXISTS (
SELECT 1 FROM public.user_profiles
WHERE id = auth.uid() AND is_admin = true
)
);
CREATE POLICY "Admins can view prompt history"
ON prompt_config_history
FOR SELECT
USING (
EXISTS (
SELECT 1 FROM public.user_profiles
WHERE id = auth.uid() AND is_admin = true
)
);
CREATE POLICY "Admins can insert prompt history"
ON prompt_config_history
FOR INSERT
WITH CHECK (
EXISTS (
SELECT 1 FROM public.user_profiles
WHERE id = auth.uid() AND is_admin = true
)
);
-- Function to auto-update updated_at timestamp and track history
CREATE OR REPLACE FUNCTION update_prompt_config_with_history()
RETURNS TRIGGER AS $$
BEGIN
-- Store the previous version in history
IF OLD.content IS DISTINCT FROM NEW.content THEN
INSERT INTO prompt_config_history (
prompt_config_id,
version,
content,
changed_by,
change_reason
) VALUES (
OLD.id,
OLD.version,
OLD.content,
NEW.updated_by,
'Updated to version ' || (OLD.version + 1)
);
NEW.version = OLD.version + 1;
NEW.previous_content = OLD.content;
END IF;
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Trigger to automatically update and track history
CREATE TRIGGER prompt_config_update_trigger
BEFORE UPDATE ON prompt_configs
FOR EACH ROW
EXECUTE FUNCTION update_prompt_config_with_history();
-- Grant permissions
GRANT SELECT ON prompt_configs TO authenticated;
GRANT SELECT ON prompt_configs TO anon;
GRANT SELECT ON prompt_config_history TO authenticated;
-- Service role needs full access for admin API
GRANT ALL ON prompt_configs TO service_role;
GRANT ALL ON prompt_config_history TO service_role;
-- Comments for documentation
COMMENT ON TABLE prompt_configs IS 'Dynamic prompt configurations for chatbot, factcheck, and other AI features';
COMMENT ON COLUMN prompt_configs.key IS 'Unique identifier used in code (e.g., gordie_base, context_bill)';
COMMENT ON COLUMN prompt_configs.content IS 'The actual prompt text, supports {{variables}} for interpolation';
COMMENT ON COLUMN prompt_configs.category IS 'Categorization: chatbot, factcheck, tools, other';
COMMENT ON COLUMN prompt_configs.version IS 'Auto-incremented version number for tracking changes';
COMMENT ON TABLE prompt_config_history IS 'Audit trail of all prompt changes';
