# Buildings Table

Core table for managing properties and buildings in the system.

# Schema

CREATE TABLE buildings (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT,
  city TEXT,
  state TEXT,
  zip TEXT,
  client_id INTEGER REFERENCES clients(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  latitude NUMERIC,
  longitude NUMERIC,
  building_type TEXT,
  floors INTEGER,
  units INTEGER
);

# Columns

Column	Type	Description	Constraints
id	SERIAL	Primary key	NOT NULL, UNIQUE
name	TEXT	Building name	NOT NULL
address	TEXT	Street address	-
city	TEXT	City name	-
state	TEXT	State code	-
zip	TEXT	ZIP/postal code	-
client_id	INTEGER	Owner client organization	FK to clients(id)
created_at	TIMESTAMPTZ	Creation timestamp	DEFAULT NOW()
updated_at	TIMESTAMPTZ	Last update timestamp	DEFAULT NOW()
latitude	NUMERIC	GPS latitude	-
longitude	NUMERIC	GPS longitude	-
building_type	TEXT	Type of building	e.g., 'residential', 'commercial'
floors	INTEGER	Number of floors	-
units	INTEGER	Number of units	-

# Indexes

Performance-optimized indexes:

idx_buildings_client_id - Client filtering
idx_buildings_name - Name searches
idx_buildings_city - City filtering
idx_buildings_state - State filtering
idx_buildings_client_name - Composite client + name
idx_buildings_location - Composite state + city
idx_buildings_name_lower - Case-insensitive name search

# RLS Policies

# SELECT Policy: `buildings_select`

-- Super admins see all buildings
-- Client admins see all buildings in their client
-- Client managers see only assigned buildings
is_super_admin_safe(auth.uid()) OR
(get_user_role_safe(auth.uid()) = 'client_admin' AND 
 client_id = get_user_client_safe(auth.uid())) OR
(get_user_role_safe(auth.uid()) = 'client_manager' AND
 check_building_access_v2(id, auth.uid()))

# INSERT Policy: `buildings_insert`

-- Super admins can create any building
-- Client admins can create buildings for their client
is_super_admin_safe(auth.uid()) OR
(get_user_role_safe(auth.uid()) = 'client_admin' AND
 NEW.client_id = get_user_client_safe(auth.uid()))

# UPDATE Policy: `buildings_update`

-- Super admins can update any building
-- Client admins can update buildings in their client
is_super_admin_safe(auth.uid()) OR
(get_user_role_safe(auth.uid()) = 'client_admin' AND
 client_id = get_user_client_safe(auth.uid()))

# DELETE Policy: `buildings_delete`

-- Super admins can delete any building
-- Client admins can delete buildings in their client
is_super_admin_safe(auth.uid()) OR
(get_user_role_safe(auth.uid()) = 'client_admin' AND
 client_id = get_user_client_safe(auth.uid()))

# Apartments

Buildings can have multiple apartments:

SELECT * FROM apartments WHERE building_id = 123;

# Inspections

Buildings can have multiple inspections:

SELECT * FROM inspections WHERE building_id = 123;

# Access Control

Building access is managed through:

client_admin_building_access
client_manager_building_access

# Usage Examples

# Create a new building

INSERT INTO buildings (name, address, city, state, zip, client_id, building_type, floors, units)
VALUES ('Empire State Building', '350 5th Ave', 'New York', 'NY', '10118', 123, 'commercial', 102, 1000);

# Find buildings in a city

SELECT * FROM buildings 
WHERE city ILIKE '%New York%' 
ORDER BY name;

# Get building with inspection count

SELECT 
  b.*,
  COUNT(i.id) as inspection_count
FROM buildings b
LEFT JOIN inspections i ON b.id = i.building_id
WHERE b.client_id = 123
GROUP BY b.id;

# Assign building to client manager

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

# Performance Considerations

The table has 18 specialized indexes for various query patterns
Composite indexes optimize common filter combinations
Case-insensitive index on name for search operations
Client-based queries are highly optimized with covering indexes

# Data Volume

Table contains 60,000+ building records
Properly indexed for sub-100ms query performance
RLS policies optimized to avoid full table scans