IRIS Legacy

-- Migration: Add MCP API Keys table for authentication -- Description: Store API keys for MCP HTTP/SSE server authentication CREATE TABLE IF NOT EXISTS mcp_api_keys ( id SERIAL PRIMARY KEY, key_hash VARCHAR(64) NOT NULL UNIQUE, -- SHA256 hash of the API key key_name VARCHAR(255) NOT NULL, -- Friendly name (e.g., "Claude Desktop", "ChatGPT") user_email VARCHAR(255), -- Associated user (optional) created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP, -- NULL = never expires last_used_at TIMESTAMP, -- Track usage is_active BOOLEAN DEFAULT TRUE, -- Soft delete metadata JSONB DEFAULT '{}'::jsonb -- Additional info (IP whitelist, permissions, etc.) ); -- Index for fast lookup CREATE INDEX idx_mcp_api_keys_hash ON mcp_api_keys(key_hash) WHERE is_active = TRUE; CREATE INDEX idx_mcp_api_keys_user ON mcp_api_keys(user_email); -- Insert a default API key for development (key: "dev-key-12345") -- Hash: sha256("dev-key-12345") = 5994471abb01112afcc18159f6cc74b4f511b99806da59b3caf5a9c173cacfc5 INSERT INTO mcp_api_keys (key_hash, key_name, user_email, expires_at) VALUES ( '5994471abb01112afcc18159f6cc74b4f511b99806da59b3caf5a9c173cacfc5', 'Development Key', 'dev@trustypa.com', NULL ) ON CONFLICT (key_hash) DO NOTHING; COMMENT ON TABLE mcp_api_keys IS 'API keys for MCP HTTP/SSE server authentication'; COMMENT ON COLUMN mcp_api_keys.key_hash IS 'SHA256 hash of the API key (never store plaintext)'; COMMENT ON COLUMN mcp_api_keys.metadata IS 'JSON field for IP whitelist, rate limits, tool permissions, etc.';