# Database Schema Overview

The Exit WeWeb Portal uses PostgreSQL with Supabase, implementing comprehensive Row Level Security (RLS) policies for data isolation and security.

• users - System users with role-based access
• clients - Client organizations
• pending_invites - User invitation tracking

• buildings - Properties/buildings in the system
• apartments - Individual units within buildings
• inspections - Inspection records
• inspectors - Inspector profiles
• inspection_types - Types of inspections
• inspection_status - Status tracking

• client_admin_building_access - Building access for client admins
• client_manager_building_access - Building access for client managers
• client_admin_inspection_access - Inspection access for client admins
• client_manager_inspection_access - Inspection access for client managers

• fire_incidents - Fire incident records from external API
• alert_logs - Notification history for incidents
• alert_subscriptions - User alert preferences

• reports_lead - Lead inspection reports
• reports_lead_gun_data - Lead inspection gun readings
• documents - Document storage
• upload_files - File upload tracking

• l_states - US states reference
• l_countries - Countries reference
• cities - Cities reference
• activity_types - Activity type definitions
• friction_types - Friction type definitions

The system includes several optimized views for performance:

• view_inspections_dashboard_summary - Dashboard statistics
• view_building_inspections - Building inspection details
• view_apartment_inspections - Apartment inspection details
• view_grouped_gun_data - Aggregated lead inspection data
• view_positive_results_per_building - Positive inspection results
• view_negative_results_per_building - Negative inspection results

Key security functions that support RLS:

• get_user_role_safe() - Get user role (RLS-safe)
• is_super_admin_safe() - Check super admin status (RLS-safe)
• get_user_client_safe() - Get user's client (RLS-safe)
• check_building_access_v2() - Verify building access
• check_inspection_access() - Verify inspection access

The database includes:

• 96+ specialized indexes for query optimization
• Covering indexes for frequently accessed data
• Partial indexes for filtered queries
• Composite indexes for multi-column searches

All tables implement RLS policies based on user roles:

1. Super Admins - Bypass all RLS policies
2. Client Admins - Access limited to their client's data
3. Client Managers - Access limited to assigned resources

Key relationships in the system:

clients
  └── users (via client_id)
  └── buildings (via client_id)
      └── apartments
      └── inspections
          └── reports_lead
              └── reports_lead_gun_data

The database schema is managed through migrations:

• Initial schema setup
• Role-based access control implementation
• Performance optimization indexes
• Fire alert system tables
• RLS policy refinements