Data Model

Status: Accepted Owner: @bilal Last Updated: 2026-02-15

Entity relationships and database design for Envo.

Entity Relationship Diagram

erDiagram
    users ||--o{ user_organisation_access : "has access via"
    organisations ||--o{ user_organisation_access : "grants access to"
    organisations ||--o{ properties : "owns"
    organisations ||--o{ tenants : "manages"
    organisations ||--o{ vendors : "works with"
    organisations ||--o{ issues : "tracks"
    organisations ||--o{ conversations : "has"
    properties ||--o{ tenants : "houses"
    properties ||--o{ issues : "has"
    properties ||--o{ property_documents : "requires"
    tenants ||--o{ issues : "reports"
    tenants ||--o{ conversations : "initiates"
    vendors ||--o{ issues : "resolves"
    issues ||--o{ attachments : "has"
    issues ||--o{ events : "generates"
    conversations ||--o{ messages : "contains"
    conversations ||--o{ issues : "creates"

Core Entities

Users & Access

• Users synced from Supabase Auth
• One user can access multiple organisations via user_organisation_access
• Access is role-based with optional expiry (for Envo support)

Organisation Hierarchy

• Properties belong to one organisation
• Tenants belong to one property (and one organisation for RLS)
• Issues reference organisation, property, and tenant
• Vendors are per-organisation

Communications (Tenant Engine)

• Conversations are the parent record for all tenant communications
• Messages store individual inbound/outbound messages with media
• Conversations can result in issue creation
• Full transcript and media stored for regulatory compliance

Property Compliance

• Properties have required compliance documents (Gas Safety, EPC, EICR, HMO License)
• Documents track validity period and expiry dates
• Automatic alerts before expiry (default 30 days)

Key Enums

Domain	Enum	Values
Access	access_role	owner, admin, staff, envo_support
Issues	issue_status	new, in_progress, vendor_assigned, completed, cancelled
Issues	urgency_level	low, medium, high, emergency
Issues	issue_category	plumbing, electrical, heating, structural, … (14 total)
Comms	conversation_channel	whatsapp, voice, email, chat, sms
Comms	conversation_status	active, resolved, escalated, archived

Status Transitions

Issue status transitions are enforced by database trigger:

stateDiagram-v2
    [*] --> new
    new --> in_progress
    new --> vendor_assigned
    new --> cancelled
    in_progress --> vendor_assigned
    in_progress --> completed
    vendor_assigned --> in_progress
    vendor_assigned --> completed
    completed --> [*]
    cancelled --> new : Reopened

Audit Strategy

Two-tier approach:

1. Business Events (events table) — issue_created, status_changed, vendor_assigned, notification_sent, etc.
2. Detailed Audit (audit.log) — field-level changes with old/new values on sensitive tables

Soft Deletes

All primary entities use deleted_at TIMESTAMPTZ (NULL = active). Views: active_properties, active_tenants, open_issues, urgent_issues, etc.

Data Validation

• Phone numbers: E.164 format (+[country][number])
• Tenants/vendors: must have phone OR email
• Completed issues: must have resolution notes

Schema Location

Source of truth: envo-dashboard/prisma/schema.prisma

See also: Security, System Design, Glossary