-- Conversation State Table for MCP Tool Factory (M3)
-- Cloud-hosted persistence via Supabase PostgreSQL
DROP TABLE IF EXISTS conversations CASCADE;
CREATE TABLE conversations (
conversation_id TEXT PRIMARY KEY,
identity JSONB NOT NULL,
intent_history JSONB NOT NULL DEFAULT '[]'::jsonb,
permissions JSONB NOT NULL DEFAULT '[]'::jsonb,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Create indexes for better query performance
CREATE INDEX idx_conversations_updated_at ON conversations(updated_at DESC);
CREATE INDEX idx_conversations_identity ON conversations USING GIN (identity);
-- Add comment explaining table purpose
COMMENT ON TABLE conversations IS 'MCP Tool Factory conversation state persistence (M3). Stores WHO (identity), WHAT (intent history), HOW (permissions) dimensions.';
COMMENT ON COLUMN conversations.conversation_id IS 'Primary key: unique conversation identifier';
COMMENT ON COLUMN conversations.identity IS 'WHO dimension: {toolName, version, capabilities[]}';
COMMENT ON COLUMN conversations.intent_history IS 'WHAT dimension: [{action, alignment, timestamp}]';
COMMENT ON COLUMN conversations.permissions IS 'HOW dimension: [{level, scope, grantedAt}]';
