Data Model

Smart Estate uses PostgreSQL as the system of record.

Main Tables

users

• Stores tenant, technician, admin, manager, and operations accounts.
• Key fields:
• id
• name
• email
• password_hash
• role
• status
• building_id
• last_seen_at

tickets

• Stores each maintenance request and its lifecycle state.
• Key fields:
• id
• title
• description
• created_by
• building_id
• apartment_id
• assigned_technician_id
• status
• priority
• ai_result
• response_due_at
• resolution_due_at
• sla_status
• escalated
• escalation_level
• started_at
• dispatched_at
• completed_at
• duration_minutes

sla_rules

• Stores priority-based SLA windows.
• Key fields:
• priority
• response_time_minutes
• resolution_time_hours
• escalation_time_hours

ai_logs

• Stores the AI decision trail for ticket classification.
• Key fields:
• ticket_id
• predicted_category
• actual_category
• confidence_score
• auto_assigned
• model_version

Supporting Tables

• buildings stores property metadata.
• apartments stores unit-level occupancy and tenant assignment.
• messages stores ticket chat messages.
• media stores uploaded file references for tickets.
• audit_logs stores operational history.
• technician_profiles stores technician skills, status, workload, and location.
• system_settings stores global configuration.
• manager_building_assignments stores manager access scope.

Relationships

users (tenant/technician/admin/manager/operations)
   |\
   | \-- creates --> tickets
   |      |\
   |      | \-- belongs to --> buildings
   |      | \-- belongs to --> apartments
   |      | \-- assigned to --> users (technician)
   |      | \-- has --> ai_logs
   |      | \-- has --> messages
   |      | \-- has --> media
   |
   \-- writes --> audit_logs

Key Field Behavior

sla_status

• Stored on tickets.
• Valid values: on_track, at_risk, breached.

escalation_level

• Counts how many escalation windows have elapsed.
• Used by the dashboard and SLA views.

confidence_score

• Stored in ai_logs.
• Represents AI confidence for the predicted category.
• Feeds the dashboard AI confidence metric.

Data Flow Summary

Ticket creation
   -> tickets row
   -> ai_result JSON
   -> ai_logs row
   -> SLA timestamps
   -> assignment / messages / audit logs as work continues