# Inspections Table

Core table for managing building inspection records.

# Schema

CREATE TABLE inspections (
  id SERIAL PRIMARY KEY,
  building_id INTEGER REFERENCES buildings(id),
  inspection_type TEXT,
  date_visited DATE,
  status TEXT,
  inspector_id INTEGER REFERENCES inspectors(id),
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

# Columns

Column	Type	Description	Constraints
id	SERIAL	Primary key	NOT NULL, UNIQUE
building_id	INTEGER	Associated building	FK to buildings(id)
inspection_type	TEXT	Type of inspection	e.g., 'annual', 'fire', 'lead'
date_visited	DATE	Date of inspection	-
status	TEXT	Current status	e.g., 'scheduled', 'completed'
inspector_id	INTEGER	Assigned inspector	FK to inspectors(id)
notes	TEXT	Inspection notes	-
created_at	TIMESTAMPTZ	Creation timestamp	DEFAULT NOW()
updated_at	TIMESTAMPTZ	Last update timestamp	DEFAULT NOW()

# Indexes

Performance-optimized indexes (29 total):

idx_inspections_building_id - Building filtering
idx_inspections_date_visited - Date range queries
idx_inspections_status - Status filtering
idx_inspections_type - Type filtering
idx_inspections_building_date - Composite building + date
idx_inspections_client_access - Optimized for client queries
idx_inspections_date_building_type_optimized - Dashboard performance

# RLS Policies

# SELECT Policy: `inspections_select`

-- Super admins see all inspections
-- Others see inspections they have access to
is_super_admin_safe(auth.uid()) OR
check_inspection_access(id, auth.uid())

# INSERT Policy: `inspections_insert`

-- Super admins can create any inspection
-- Others can create if they have building access
is_super_admin_safe(auth.uid()) OR
check_building_access_v2(NEW.building_id, auth.uid())

# UPDATE Policy: `inspections_update`

-- Super admins can update any inspection
-- Others can update if they have inspection access
is_super_admin_safe(auth.uid()) OR
check_inspection_access(id, auth.uid())

# DELETE Policy: `inspections_delete`

-- Super admins can delete any inspection
-- Client admins can delete inspections they have access to
is_super_admin_safe(auth.uid()) OR
(get_user_role_safe(auth.uid()) = 'client_admin' AND
 check_inspection_access(id, auth.uid()))

# Reports Lead

Inspection can have lead paint test results:

SELECT * FROM reports_lead WHERE inspection_id = 123;

# Buildings

Each inspection belongs to a building:

SELECT i.*, b.name as building_name
FROM inspections i
JOIN buildings b ON i.building_id = b.id;

# Access Control

Inspection access is managed through:

client_admin_inspection_access
client_manager_inspection_access

# Common Queries

# Get recent inspections

SELECT 
  i.*,
  b.name as building_name,
  b.city,
  ins.name as inspector_name
FROM inspections i
JOIN buildings b ON i.building_id = b.id
LEFT JOIN inspectors ins ON i.inspector_id = ins.id
WHERE i.date_visited >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY i.date_visited DESC;

# Get inspection statistics

SELECT 
  inspection_type,
  status,
  COUNT(*) as count,
  DATE_TRUNC('month', date_visited) as month
FROM inspections
WHERE date_visited >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY inspection_type, status, month
ORDER BY month DESC;

# Assign inspection to manager

INSERT INTO client_manager_inspection_access (manager_id, inspection_id, assigned_by)
VALUES ('manager-uuid', 123, auth.uid());

# Inspection Types

Common inspection types:

annual - Annual safety inspection
fire - Fire safety inspection
lead - Lead paint inspection
structural - Structural inspection
emergency - Emergency inspection
reinspection - Follow-up inspection

# Status Values

Common status values:

scheduled - Inspection scheduled
in_progress - Currently being conducted
completed - Inspection completed
failed - Failed inspection
passed - Passed inspection
cancelled - Inspection cancelled
pending_review - Awaiting review

# Performance Optimization

The inspections table is heavily optimized:

29 specialized indexes for various access patterns
Composite indexes for common filter combinations
Partial indexes for date-based queries
Covering indexes for dashboard queries

# Dashboard Optimization

Special function for fast dashboard queries:

SELECT * FROM get_inspections_dashboard_summary_fast();

This bypasses RLS for performance while maintaining security through role checks.

# Data Volume

Table contains 55,000+ inspection records
Optimized for high-performance queries
Dashboard queries execute in <10ms with proper indexes