perf: cache small auth lookup tables instead of querying on every auth check (replaces #14633)

Why

Follow-up to OpenHands/OpenHands#14633, re-scoped per the analysis in that thread: adding indexes will not reduce load on user_authorizations and blocked_email_domains.

Both tables hold only ~31 rows (a single ~8 KB page). For a table that small the Postgres planner will keep choosing a sequential scan over any index — a 1-page seq scan (≈ seq_page_cost + 31·cpu_tuple_cost) is cheaper than index + heap access, so a new index would simply go unused and the seq_scan counts wouldn't move. The evidence confirms this: seq_tup_read / seq_scan ≈ 31 for both tables (each scan reads all 31 rows in ~1 page).

The cost driver is query volume, not per-scan cost. These tables are read on every authentication/authorization path via UserAuthorizationStore.get_authorization_type — millions of calls — and an index can't reduce the number of calls. The effective fix is to stop hitting the DB on the hot path: cache these small, rarely-changing tables in memory with a short TTL (and/or invalidate on write).

Validation

Hot path to change:

• UserAuthorizationStore.get_matching_authorizations / get_authorization_type — enterprise/storage/user_authorization_store.py:52
• Callers: enterprise/server/auth/saas_user_auth.py:740, enterprise/server/auth/user/default_user_authorizer.py:64

Proposed approach:

• Load the full user_authorizations rule set (and the blocked-domain list) into process memory behind a short TTL cache (e.g. cachetools.TTLCache or an @lru_cache wrapped with a TTL) and evaluate the email/provider match in Python instead of issuing a query per request. Invalidate on the create/delete paths in the same store.
• TTL keeps a single replica's staleness window small (seconds–minutes); writes are rare so a short TTL is acceptable.

How to verify the fix:

• seq_scan / calls for both tables in pg_stat_user_tables drop to roughly (replicas × table_count) / TTL instead of tracking auth request volume.
• Existing auth tests still pass: enterprise/tests/unit/test_saas_user_auth.py, enterprise/tests/unit/storage/test_user_authorization_store.py.
• Add a test asserting a second lookup within the TTL window issues no DB query (mock the session/store) and that a create/delete invalidates the cache.

Related: OpenHands/OpenHands#14633 (closed in favor of this).

This was created by an AI assistant.