-- Migration: 043-add-backchannel-logout-clients.sql
-- Description: Add backchannel_logout_uri and backchannel_logout_session_required columns to clients table
-- Date: 2026-06-22
-- Phase: Phase 2.3 (Cross-Project Silent SSO)
-- Usage: Run via `docker exec sso-postgres psql -U postgres -d sso -f /tmp/migration.sql`
-- Note: Container DB name is `sso` (not `vietprodev_sso`).
--       If your DB is `vietprodev_sso`, replace `-d sso` with `-d vietprodev_sso`.

ALTER TABLE clients
ADD COLUMN IF NOT EXISTS backchannel_logout_uri TEXT,
ADD COLUMN IF NOT EXISTS backchannel_logout_session_required BOOLEAN NOT NULL DEFAULT FALSE;

CREATE INDEX IF NOT EXISTS idx_clients_backchannel_logout_uri
ON clients (backchannel_logout_uri)
WHERE backchannel_logout_uri IS NOT NULL;

COMMENT ON COLUMN clients.backchannel_logout_uri IS 'OIDC Back-Channel Logout URI — SSO POSTs a logout_token here when the user logs out.';
COMMENT ON COLUMN clients.backchannel_logout_session_required IS 'Whether the logout_token must include a sid (session ID) claim.';
