Skip to main content

​
RBAC Audit Summary - Direct BigQuery Fetches

​
✅ Fixed Issues

​
1. CHURN Intent Handler - RBAC Enforcement Added

Location: api/routes/ai_query.py lines 3579-3650 Status: ✅ FIXED
  • Now calls authorize_target_agent_id() before get_agent_churn_intel()
  • Resolves authorized_agent_id → agent_name before querying
  • Returns 403 if user is not authorized

​
⚠️ Remaining Issues (Lower Priority)

​
1. BigQuery Functions Don’t Accept authorized_agent_id

Functions that need authorized_agent_id parameter added:
  • get_agent_churn_intel() - Currently only accepts agent_name
  • get_agent_portfolio_metrics() - Currently only accepts agent_name
  • get_agent_lifecycle_events() - Currently only accepts agent_name
  • get_agent_lost_businesses() - Currently only accepts agent_name
  • get_agent_business_summary() - Currently only accepts agent_name
  • get_agent_commission_with_authoritative_pepm() - Currently only accepts agent_name
  • get_agent_mom_comparison() - Currently only accepts agent_name
  • get_agent_commission_range() - Currently only accepts agent_name
  • get_agent_commission_single_period() - Currently only accepts agent_name
  • get_agent_trends_from_view() - Currently only accepts agent_name
Impact: These functions are called from fetch_agent_data() which receives authorized_agent_id, but can’t pass it through. However, since fetch_agent_data() is only called AFTER RBAC enforcement in the intent handlers, and the agent_name passed is already authorized, this is lower risk but should still be fixed for defense-in-depth. Recommendation: Add authorized_agent_id parameter to these functions and use SQL JOIN to filter by agent_id (like get_agent_report_from_view() does).

​
2. fetch_business_data() Passes None for authorized_agent_id

Location: api/routes/ai_query.py line 722 Status: ⚠️ TODO 
agent_data = get_agent_report_from_view(tenant_id, current_period, agent_name, business_name, authorized_agent_id=None)
Impact: get_agent_report_from_view() falls back to agent_name filter when authorized_agent_id=None, which is less secure. Fix Required: Ensure fetch_business_data() is only called after RBAC enforcement, or pass authorized_agent_id when available.

​
3. COMPANY_LOST_BUSINESSES Intent - Role Check Needed?

Location: api/routes/ai_query.py line 3553 Status: ⚠️ VERIFY 
data = fetch_agent_data("company_lost_businesses", tenant_id, period_label, None, active_period, question_text)
Question: Is this CEO-level data that should require admin/ceo role? Recommendation: Add role check if this is CEO-only data: 
if current_user.get('role') not in ('admin', 'ceo'):
    raise HTTPException(403, "Forbidden: CEO-level access required")

​
✅ Safe Patterns

​
1. Reverse Lookups (agent_id → agent_name)

Location: Lines 3470-3489, 4377-4396 Status: ✅ SAFE
  • These use authorized_agent_id from RBAC check
  • Only used for reverse lookup, not data queries

​
2. Functions That Accept authorized_agent_id

Status: ✅ SAFE
  • get_agent_report_from_view() - Uses SQL JOIN when authorized_agent_id provided
  • get_agent_ytd_summary() - Accepts and uses authorized_agent_id
  • get_agent_qoq_comparison() - Accepts and uses authorized_agent_id
  • get_agent_commission_by_business() - Accepts and uses authorized_agent_id

​
Verification Status

  • CHURN intent handler enforces RBAC before BigQuery call
  • AGENT_COMMISSION intent handler enforces RBAC before BigQuery call
  • All fetch_agent_data() calls pass authorized_agent_id when available
  • All BigQuery query functions that accept authorized_agent_id actually use it
  • COMPANY_LOST_BUSINESSES intent has role check if needed
  • No direct BigQuery calls bypass RBAC checks (except reverse lookups)

​
Next Steps

  1. Immediate: Deploy CHURN intent fix ✅
  2. Short-term: Add authorized_agent_id parameter to remaining BigQuery functions
  3. Short-term: Fix fetch_business_data() to pass authorized_agent_id
  4. Short-term: Verify COMPANY_LOST_BUSINESSES role requirements
  5. Long-term: Add integration tests for RBAC enforcement