# Session Security **Date:** 2026-05-31 **Status:** Draft **Spec:** 122 **Tier:** All tiers (Business+ for advanced controls) **Depends on:** `foundation-auth-rbac`, `auth-2fa`, `operational-audit-trail` **Referenced by:** `foundation-auth-rbac`, `two-factor-auth` **Consolidates:** spec 162 (`inactivity-reauth`) — retired; re-auth modal and reauth endpoint folded in here --- ## Overview Spec 5 (`foundation-auth-rbac`) handles login and JWT issuance. This spec adds session lifecycle management: active session listing, remote revocation, idle timeout, suspicious login detection, and per-tenant security policies. --- ## Session Table ```sql CREATE TABLE user_sessions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, token_hash TEXT NOT NULL UNIQUE, -- SHA-256 of JWT; never store raw token device_name TEXT, -- "Chrome on MacBook" (UA parse) ip_address INET, country_code TEXT, -- from CF-IPCountry header created_at TIMESTAMPTZ DEFAULT now(), last_active_at TIMESTAMPTZ DEFAULT now(), expires_at TIMESTAMPTZ NOT NULL, revoked_at TIMESTAMPTZ, revoked_reason TEXT, -- 'user', 'admin', 'idle_timeout', 'suspicious' CONSTRAINT valid_expiry CHECK (expires_at > created_at) ); CREATE INDEX idx_sessions_user ON user_sessions(user_id, revoked_at) WHERE revoked_at IS NULL; CREATE INDEX idx_sessions_tenant ON user_sessions(tenant_id, last_active_at DESC); -- token_hash uniqueness enforced by column constraint; see Required Indexes below for the explicit UNIQUE index ``` ### Required Indexes Session lookups happen on every authenticated request — missing indexes cause full table scans that degrade under concurrent users. ```sql -- Primary lookup: session token → user (every authenticated request) CREATE UNIQUE INDEX idx_sessions_token ON user_sessions(token_hash); -- Cleanup query: delete expired sessions (cron job or TTL sweep) CREATE INDEX idx_sessions_expires_at ON user_sessions(expires_at) WHERE expires_at < NOW(); -- partial index: only non-expired rows scanned in hot path -- User session list: "show active sessions" panel (spec 122) CREATE INDEX idx_sessions_user_id ON user_sessions(user_id, created_at DESC); -- Tenant-scoped invalidation: "log out all sessions for tenant" (admin action) CREATE INDEX idx_sessions_tenant_id ON user_sessions(tenant_id); ``` **Invariant:** any migration adding a `user_sessions` table lookup pattern must include the covering index in the same migration. Query: `EXPLAIN (ANALYZE, BUFFERS)` must show `Index Scan` not `Seq Scan` for token lookup. **Maintenance:** expired session cleanup runs as a Cloudflare Cron Trigger (spec 122) every 15 minutes: ```sql DELETE FROM user_sessions WHERE expires_at < NOW() - INTERVAL '1 minute'; ``` The partial index above makes this O(expired rows), not O(all sessions). --- ## Active Sessions View `/settings/security` → **Active Sessions** section: ``` ┌──────────────────────────────────────────────────────────────┐ │ Active Sessions │ │ │ │ ● Current session │ │ Chrome · MacBook Pro · Tel Aviv, IL │ │ Started: Today 09:14 · Last active: just now │ │ [Sign out ✕] │ │ │ │ Chrome · Windows PC · Tel Aviv, IL │ │ Started: May 28 · Last active: 2 days ago │ │ [Sign out ✕] │ │ │ │ Safari · iPhone · Haifa, IL │ │ Started: May 25 · Last active: 6 days ago │ │ [Sign out ✕] │ │ │ │ [Sign out all other sessions] │ └──────────────────────────────────────────────────────────────┘ ``` Current session marked with `●`. User can revoke any session except current. **[Sign out all other sessions]** → revokes all non-current sessions in one action. --- ## Session Revocation On revoke: 1. `user_sessions.revoked_at = now()`, `revoked_reason = 'user'` 2. Token hash added to KV blocklist: `session:revoked:{tokenHash} = 1` (TTL = session `expires_at`) 3. Auth middleware checks blocklist on every request — revoked sessions rejected immediately --- ## Idle Timeout Configurable per-tenant (`tenant_security_settings.idle_timeout_minutes`). Default: none (no idle timeout). If set: auth middleware checks `last_active_at` on every authenticated request. If `last_active_at < now() - interval 'N minutes'`, session is revoked immediately and `401 Unauthorized` returned, forcing re-login. On successful request, `last_active_at` is updated. Enforcement path: 1. **Auth middleware** (every request): reject if `last_active_at` older than timeout. This is the real enforcement — minute-granularity, no delay. 2. **Nightly cron `session-idle-cleanup`**: cleans up sessions where the user closed the tab without making a final request (offline sessions that never got revoked by middleware). **Inactivity detection (client-side):** debounced listeners on `mousemove`, `keydown`, `click`, `scroll`, `touchstart` track last interaction time. | Phase | Timing | Action | |-------|--------|--------| | Warning | `idle_timeout_minutes - 2` min of inactivity | Show re-auth modal (2-min countdown) | | Auto-logout | `idle_timeout_minutes` min | Redirect to `/login?reason=idle_timeout` | **Re-auth modal** (appears 2 minutes before logout): ``` ┌──────────────────────────────────────────────────┐ │ Session expiring │ │ You've been inactive for a while. │ │ You'll be logged out in 1:47. │ │ │ │ Password (or 6-digit 2FA code if 2FA enabled): │ │ [________________________________] │ │ │ │ [Stay logged in] [Log out now] │ └──────────────────────────────────────────────────┘ ``` Modal not dismissible by Esc or outside click. Countdown updates every second. Warning window is always 2 minutes regardless of overall timeout duration. **[Stay logged in]** → `POST /api/auth/reauth` with `{ password }` or `{ totp_code }`. On 200: replace tokens, reset idle timer, close modal. On 401: inline error, countdown continues. On reaching 0: POST to `/api/auth/logout`, redirect to `/login?reason=idle_timeout`. Login page shows: "You were logged out due to inactivity." when redirected with `?reason=idle_timeout` (info banner, cleared after 10 seconds). --- ## Session Cap `max_sessions_per_user` (default 10): on login, count active sessions for the user. If at cap, reject login with `429 Too Many Requests` and message: "Maximum active sessions reached. Sign out of another device to continue." ## 2FA Policy `require_2fa_for_roles TEXT[]`: if a role is listed and the user has not enrolled 2FA (spec 47), login is allowed but they are immediately redirected to `/settings/security/2fa-setup` and cannot access other routes until enrolled. ## Suspicious Login Detection On each new login, compare: - Country code (`CF-IPCountry`) — if differs from last 3 sessions: flag - IP prefix — if `/24` differs from all recent sessions: flag If flagged: - Send email to user: "New sign-in from [City], [Country] at [time]. If this wasn't you, [revoke all sessions]" - Create audit entry: `auth.suspicious_login` No session blocking — detection only. Tenant admin can enable blocking for flagged logins in Security settings. --- ## Admin Session Management (Business+) Tenant admins can view and revoke sessions for any user in the tenant: `/settings/security` → **Team Sessions** tab (admin only, Business+): ``` ┌──────────────────────────────────────────────────────────────┐ │ Team Sessions │ │ │ │ User Active sessions Last active │ │ Dana Levi 3 sessions 5 min ago [Revoke all] │ │ Yossi Cohen 1 session 2 hours ago [Revoke all] │ │ ... │ │ │ │ [Revoke all sessions — entire team] │ └──────────────────────────────────────────────────────────────┘ ``` **[Revoke all sessions — entire team]** — emergency action, requires confirmation modal with typed confirmation ("revoke all"). --- ## Tenant Security Policy New table for per-tenant security settings: ```sql CREATE TABLE tenant_security_settings ( tenant_id UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE, idle_timeout_minutes INTEGER, -- NULL = disabled max_sessions_per_user INTEGER DEFAULT 10, -- session cap per user require_2fa_for_roles TEXT[], -- roles that must have 2FA (spec 47) block_suspicious_logins BOOLEAN DEFAULT false, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now() ); ``` Configured via `/settings/security` → **Security Policy** section. --- ## Session Cleanup Cron Daily cron `session-idle-cleanup`: 1. Revoke sessions where `last_active_at < now() - idle_timeout_minutes` (if timeout configured) 2. Revoke sessions where `expires_at < now()` and `revoked_at IS NULL` 3. Purge KV blocklist entries past TTL (automatic via KV TTL — no explicit cron step needed) --- ## Schema Delta ```sql -- (see CREATE TABLE statements above) -- Additional column on existing user_preferences table: ALTER TABLE user_preferences ADD COLUMN notify_new_login BOOLEAN DEFAULT true; -- Email notification preference for new logins. ``` --- ## API ``` GET /api/user/sessions → list active sessions for current user Returns: [{ id, device_name, ip_address, country_code, created_at, last_active_at, is_current }] Requires: authenticated DELETE /api/user/sessions/:sessionId → revoke a specific session Requires: authenticated (own sessions only) DELETE /api/user/sessions → revoke all sessions except current Requires: authenticated GET /api/admin/sessions → list active sessions for all tenant users (Business+) query: { user_id? } Requires: admin DELETE /api/admin/sessions/user/:userId → revoke all sessions for a specific user Requires: admin (Business+) DELETE /api/admin/sessions → revoke all sessions for all users in tenant body: { confirm: 'revoke all' } Requires: admin (Business+) POST /api/user/sessions/keepalive → update last_active_at for current session (used for background keepalive pings from active sessions) Requires: authenticated POST /api/auth/reauth → re-authenticate during idle timeout warning to extend session body: { password: string } | { totp_code: string } Returns: 200 { access_token, refresh_token } on success 401 { error: 'invalid_credentials' | 'invalid_totp' } on failure Requires: valid (not yet expired) session token in Authorization header GET /api/settings/security → get tenant security settings Requires: admin PUT /api/settings/security → update tenant security settings body: { idle_timeout_minutes?, max_sessions_per_user?, require_2fa_for_roles?, block_suspicious_logins? } Requires: admin (Business+ for advanced settings) ``` --- ## Architecture Decisions | Decision | Choice | Reason | |----------|--------|--------| | Store token hash | Not raw token | Raw token is a secret; storing hash prevents DB read = impersonation; SHA-256 is sufficient (token already has entropy) | | KV blocklist for revocation | Not DB-only | JWT verification runs on every request; DB lookup per request is too slow; KV lookup is O(1) at edge | | Suspicious login = notify only (default) | Not block | Blocking false-positives (VPN, new device) would lock users out; notification-only avoids lockouts while still alerting | | Nightly cron for idle cleanup | Not real-time | Real-time idle cleanup requires per-session timers — complex and expensive; nightly cron is sufficient (max staleness = 24h past idle threshold) | | Separate tenant_security_settings table | Not tenant_settings JSONB column | Security settings are queried on every auth check; dedicated indexed table is faster than parsing JSONB; also enables future indexing on require_2fa_for_roles |