-- OAuth Analytics Queries
-- Use these queries to analyze how users are connecting to Geenie
-- ========================================
-- ACTIVE SESSIONS BY CLIENT
-- ========================================
-- Shows which OAuth clients are currently being used
SELECT
oc.client_id,
oc.name AS client_name,
COUNT(os.session_id) AS active_sessions,
MAX(os.last_used_at) AS last_used,
MAX(os.expires_at) AS expires_at
FROM oauth_clients oc
LEFT JOIN oauth_sessions os ON oc.client_id = os.client_id
AND os.expires_at > NOW()
GROUP BY oc.client_id, oc.name
ORDER BY active_sessions DESC, last_used DESC;
-- ========================================
-- USER SESSIONS WITH CLIENT INFO
-- ========================================
-- Shows which users are using OAuth (claude.ai) vs Desktop (API keys)
-- OAuth users will have entries here, Desktop users won't
SELECT
u.email,
os.session_id,
oc.name AS client_name,
oc.client_id,
os.created_at,
os.last_used_at,
os.expires_at,
CASE
WHEN os.expires_at > NOW() THEN 'Active'
ELSE 'Expired'
END AS status
FROM oauth_sessions os
JOIN auth.users u ON os.user_id = u.id
LEFT JOIN oauth_clients oc ON os.client_id = oc.client_id
WHERE os.expires_at > NOW()
ORDER BY os.last_used_at DESC;
-- ========================================
-- OAUTH VS DESKTOP USAGE
-- ========================================
-- Count of users using OAuth (claude.ai connector) vs Desktop
-- Note: This only counts users who have active OAuth sessions
-- Desktop users won't appear here (they use API keys, not sessions)
SELECT
'OAuth (claude.ai)' AS connection_type,
COUNT(DISTINCT os.user_id) AS user_count
FROM oauth_sessions os
WHERE os.expires_at > NOW()
UNION ALL
SELECT
'Desktop (API keys)' AS connection_type,
COUNT(DISTINCT s.user_id) AS user_count
FROM subscriptions s
WHERE s.status IN ('active', 'trialing')
AND s.user_id NOT IN (
SELECT user_id FROM oauth_sessions WHERE expires_at > NOW()
);
-- ========================================
-- SPECIFIC USER'S ACTIVE SESSION
-- ========================================
-- Replace the user_id with your actual user_id
SELECT
u.email,
os.session_id,
oc.name AS client_name,
oc.client_id,
os.created_at,
os.last_used_at,
os.expires_at
FROM oauth_sessions os
JOIN auth.users u ON os.user_id = u.id
LEFT JOIN oauth_clients oc ON os.client_id = oc.client_id
WHERE os.user_id = '38324fc2-749d-4489-af87-728f968a0840'
AND os.expires_at > NOW()
ORDER BY os.created_at DESC;
-- ========================================
-- CLEANUP OLD UNUSED CLIENTS
-- ========================================
-- Delete OAuth clients that have:
-- 1. No active sessions
-- 2. Haven't been used in over 30 days
-- 3. Are not the most recent client for any user
DELETE FROM oauth_clients
WHERE client_id NOT IN (
-- Keep clients with active sessions
SELECT DISTINCT client_id
FROM oauth_sessions
WHERE expires_at > NOW()
AND client_id IS NOT NULL
)
AND created_at < NOW() - INTERVAL '30 days';
-- ========================================
-- SESSION CLEANUP (Automatic)
-- ========================================
-- This query is run automatically by the cleanup function
-- but you can run it manually to clean up expired sessions
DELETE FROM oauth_sessions
WHERE expires_at < NOW();
