IRIS Legacy

-- Migration: Add thread_credentials table for persistent credential storage -- Date: 2025-11-25 -- Purpose: Store user credentials (PEC, Microsoft) by thread_id to survive agent memory limits CREATE TABLE IF NOT EXISTS thread_credentials ( id SERIAL PRIMARY KEY, thread_id VARCHAR(500) NOT NULL, credential_type VARCHAR(50) NOT NULL, -- 'pec', 'microsoft', 'infocert' -- PEC credentials pec_email VARCHAR(255), pec_password TEXT, -- Should be encrypted in production! -- Microsoft credentials user_email VARCHAR(255), -- Infocert credentials infocert_username VARCHAR(255), infocert_password TEXT, -- Should be encrypted in production! infocert_pin VARCHAR(50), -- Metadata created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, last_used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP, -- Optional expiry -- Indexes CONSTRAINT unique_thread_credential UNIQUE(thread_id, credential_type) ); -- Index for fast lookups by thread_id CREATE INDEX idx_thread_credentials_thread_id ON thread_credentials(thread_id); CREATE INDEX idx_thread_credentials_type ON thread_credentials(credential_type); CREATE INDEX idx_thread_credentials_expires ON thread_credentials(expires_at); -- Function to update updated_at timestamp CREATE OR REPLACE FUNCTION update_thread_credentials_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = CURRENT_TIMESTAMP; RETURN NEW; END; $$ LANGUAGE plpgsql; -- Trigger to auto-update timestamp CREATE TRIGGER trigger_update_thread_credentials_timestamp BEFORE UPDATE ON thread_credentials FOR EACH ROW EXECUTE FUNCTION update_thread_credentials_timestamp(); COMMENT ON TABLE thread_credentials IS 'Persistent credential storage by thread_id to survive agent memory limits (8 messages)'; COMMENT ON COLUMN thread_credentials.thread_id IS 'Agent conversation thread ID - credentials valid for entire thread'; COMMENT ON COLUMN thread_credentials.credential_type IS 'Type of credential: pec, microsoft, infocert'; COMMENT ON COLUMN thread_credentials.expires_at IS 'Optional expiry timestamp - credentials auto-deleted after this';