Database Schema Overview

​

Core Tables

​

Database Relationships

​

Key Design Patterns

​

Role-Based Access

• guest: Hotel guests who report incidents via temporary sessions
• empleado: Staff members assigned to specific areas
• admin: Administrators who manage users and sessions

​

Status Workflow

1. pendiente - Initially reported, awaiting assignment
2. recibida - Accepted by staff member
3. en_progreso - Staff is actively working on it
4. resuelta - Completed with resolution details

​

Guest Access Model

• Sessions are created by admins with expiration dates
• Each session generates a unique access code
• Sessions are tied to specific room codes
• QR codes enable quick mobile access

​

Query Examples

​

Fetch Incident with Related Data

const { data, error } = await supabase
  .from("incidents")
  .select(`
    *, 
    areas(name), 
    rooms(room_code),
    incident_resolutions(description, created_at, resolved_by),
    incident_evidence(image_url)
  `)
  .eq("id", incidentId)
  .single();

​

Load Area-Specific Incidents

const { data, error } = await supabase
  .from("incidents")
  .select("id, title, description, priority, status, created_at, areas(name), rooms(room_code)")
  .eq("status", "pendiente")
  .eq("area_id", areaId)
  .order("created_at", { ascending: false });

​

Create Guest Session

const { error } = await supabase.from("guest_sessions").insert({
  room_id: roomId,
  access_code: accessCode,
  expires_at: expiresAt.toISOString(),
  active: true,
});

​

Storage Buckets

• incident-evidence: Stores photos uploaded when resolving incidents
• Path format: {user_id}/{timestamp}.{extension}

​

Next Steps