Skip to content

Review Artifact: RLS Phase 1 & Hardening

Date: 2026-02-16
Cycle: Production DB Hardening
Status: Implementation Complete - Awaiting Verification

1. Modified Files Summary

Database (Relational)

  • db/bootstrap.sql: Minimal base tables; RLS and policies removed for idempotency.
  • db/migrations/20260216000006_rls_phase_1.sql: Hardened with safe policy creation (DO $$) and explicit ENABLE RLS.
  • db/migrations/2026021600000[1-5]*.sql: Added mandatory idempotency headers.

Backend Core

  • app/core/config.py: Added SUPABASE_SERVICE_KEY.
  • app/core/auth.py: Implemented get_user_client(token) and get_service_client(). Added JWT token to get_current_user response.
  • app/api/router.py: Grouped admin endpoints under /admin prefix.

Services & Agents

  • app/services/wallet.py: Split client usage. Read/Balance check uses user client; Deductions use service client.
  • app/services/supabase_store.py: Usage logging now uses user client with RLS support.
  • app/services/supabase_references.py: All operations switched to service_role client.
  • app/services/llm.py: Switched translation model to gpt-4o-mini.
  • app/services/retrieval.py: Implemented retrieval_language logic and enhanced metadata passing.
  • app/services/reranker.py: Updated prompt for semantic cross-lingual matching.
  • app/agents/teacher_agent.py: Updated TeacherState and nodes to flow auth_token and retrieval_query.

Documentation & Tools

  • postman/collection.json: Overwritten with hardened requests, including /admin/test-role and multilingual tests.

2. Before vs. After: Architecture Shifts

Feature Before (Soft) After (Hardened)
RLS Status Disabled Enabled on core tables.
Supabase Client One client (Anon Key) for all. Separation of User (RLS) vs Service (Admin).
Translation gpt-4o (Expensive/Slow) gpt-4o-mini (Fast/Optimized).
Arabic Support Simple translation Semantic cross-lingual reranking + English support.
Admin Access x-admin-key only Primary: JWT Role admin, Secondary: API Key.

3. RLS Policies Summary

Policies are defined in 20260216000006_rls_phase_1.sql: - profiles: auth.uid() = user_id (SELECT). - wallet: auth.uid() = user_id (SELECT). - wallet_ledger: auth.uid() = user_id (SELECT). - usage_logs: auth.uid()::text = user_id (SELECT). - Writes: Bypassed by service_role in the backend for ledger and wallet updates.

4. Multilingual & Retrieval Logic

  • Model: gpt-4o-mini.
  • Logic:
  • If namespace is English -> retrieval_language = English.
  • If Student Language != Retrieval Language -> Translate query.
  • Reranker receives both original and translated queries for better semantic context.

5. Manual Supabase Actions Required

  1. Apply Migration: Run db/migrations/20260216000006_rls_phase_1.sql in the SQL Editor.
  2. Environment Variables: Add SUPABASE_SERVICE_KEY to your .env and Render dashboard.
  3. Admin Setup: Set {"role": "admin"} in user_metadata for admin users.

6. Verification Steps

  1. Test Admin Role: Run Postman Admin -> Test Admin Role.
  2. Test Wallet (RLS): Try to GET /wallet/balance. Confirm it works only with a valid JWT.
  3. Test Multilingual: Run Postman Chat (RAG) -> Arabic Query. Confirm retrieval_query in the response body.