Skip to content

Data Model

Previous: Architecture
Next: API Overview

Smart Estate uses PostgreSQL as the system of record.

1) Core Entities

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
  • materials_used

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

messages

  • Stores direct and ticket-scoped chat messages.
  • Key fields include:
  • ticket_id
  • sender_id
  • recipient_id
  • content
  • message_type

2) Supporting Entities

  • 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.
  • tenant_escalation_tracking supports post-completion tenant escalation history.

3) Relationship Map

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

4) Important 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.

5) Data Flow Summary

Ticket creation
  -> ticket record
  -> AI result data (`ai_result`)
  -> AI decision log (`ai_logs`)
   -> SLA timestamps
   -> assignment / messages / audit logs as work continues

Previous: Architecture
Next: API Overview