> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# IT Service Management Entity Relationship Diagram

> > Module: IT Service Management > Prefix: it_ > Tables: 35 > Last Updated: 2026-01-11

> **Module:** IT Service Management\
> **Prefix:** `it_`\
> **Tables:** 35\
> **Last Updated:** 2026-01-11

## Overview

The IT Service Management module provides help desk ticketing, IT asset management, SLA tracking, knowledge base, and change management capabilities.

## Tickets ERD

```mermaid theme={null}
erDiagram
    it_tickets ||--o{ it_ticket_comments : "has comments"
    it_tickets ||--o{ it_ticket_attachments : "has attachments"
    it_tickets ||--o{ it_ticket_history : logs
    it_tickets }o--|| it_ticket_categories : categorized
    it_tickets }o--|| it_sla_policies : "governed by"
    
    it_tickets {
        uuid id PK
        uuid organization_id FK
        uuid category_id FK
        uuid sla_policy_id FK
        string ticket_number
        string subject
        text description
        enum priority
        enum status
        uuid requester_id FK
        uuid assigned_to FK
        uuid assigned_group_id FK
        timestamp due_date
        timestamp first_response_at
        timestamp resolved_at
        timestamp closed_at
        jsonb custom_fields
    }
    
    it_ticket_categories {
        uuid id PK
        uuid organization_id FK
        string name
        string code
        uuid parent_id FK
        uuid default_assignee_id FK
        uuid default_sla_id FK
        integer display_order
    }
    
    it_ticket_comments {
        uuid id PK
        uuid ticket_id FK
        uuid author_id FK
        text comment_text
        boolean is_internal
        timestamp created_at
        jsonb attachments
    }
    
    it_ticket_attachments {
        uuid id PK
        uuid ticket_id FK
        uuid comment_id FK
        string file_name
        string file_path
        string mime_type
        integer file_size
        uuid uploaded_by FK
        timestamp uploaded_at
    }
    
    it_ticket_history {
        uuid id PK
        uuid ticket_id FK
        string field_name
        text old_value
        text new_value
        uuid changed_by FK
        timestamp changed_at
    }
```

## SLA Management ERD

```mermaid theme={null}
erDiagram
    it_sla_policies ||--o{ it_sla_targets : defines
    it_sla_policies ||--o{ it_tickets : governs
    it_tickets ||--o{ it_sla_breaches : "may breach"
    
    it_sla_policies {
        uuid id PK
        uuid organization_id FK
        string name
        text description
        boolean is_active
        boolean is_default
        jsonb business_hours
    }
    
    it_sla_targets {
        uuid id PK
        uuid policy_id FK
        enum priority
        integer first_response_minutes
        integer resolution_minutes
        integer update_frequency_minutes
    }
    
    it_sla_breaches {
        uuid id PK
        uuid ticket_id FK
        enum breach_type
        timestamp breached_at
        integer minutes_overdue
        boolean is_acknowledged
        uuid acknowledged_by FK
    }
```

## IT Assets ERD

```mermaid theme={null}
erDiagram
    it_assets ||--o{ it_asset_assignments : assigned
    it_assets ||--o{ it_asset_software : "has software"
    it_assets ||--o{ it_tickets : "related to"
    it_assets }o--|| it_asset_types : "of type"
    
    it_assets {
        uuid id PK
        uuid organization_id FK
        uuid asset_type_id FK
        string asset_tag
        string serial_number
        string name
        string manufacturer
        string model
        enum status
        uuid assigned_to FK
        date purchase_date
        decimal purchase_cost
        date warranty_expiration
        jsonb specifications
        jsonb custom_fields
    }
    
    it_asset_types {
        uuid id PK
        uuid organization_id FK
        string name
        string code
        text description
        integer useful_life_years
        jsonb required_fields
    }
    
    it_asset_assignments {
        uuid id PK
        uuid asset_id FK
        uuid user_id FK
        date assigned_date
        date returned_date
        uuid assigned_by FK
        text notes
    }
    
    it_asset_software {
        uuid id PK
        uuid asset_id FK
        uuid software_id FK
        string license_key
        date installed_date
        string version
    }
```

## Software & Licenses ERD

```mermaid theme={null}
erDiagram
    it_software ||--o{ it_software_licenses : "has licenses"
    it_software ||--o{ it_asset_software : "installed on"
    it_software_licenses ||--o{ it_license_assignments : assigns
    
    it_software {
        uuid id PK
        uuid organization_id FK
        string name
        string vendor
        string version
        enum license_type
        boolean is_approved
        text description
    }
    
    it_software_licenses {
        uuid id PK
        uuid software_id FK
        string license_key
        integer total_seats
        integer used_seats
        date purchase_date
        date expiration_date
        decimal cost
        string vendor_contract
    }
    
    it_license_assignments {
        uuid id PK
        uuid license_id FK
        uuid user_id FK
        uuid asset_id FK
        date assigned_date
        date revoked_date
    }
```

## Knowledge Base ERD

```mermaid theme={null}
erDiagram
    it_kb_articles ||--o{ it_kb_article_versions : "has versions"
    it_kb_articles ||--o{ it_kb_article_feedback : receives
    it_kb_articles }o--|| it_kb_categories : categorized
    it_kb_articles ||--o{ it_tickets : "resolves"
    
    it_kb_articles {
        uuid id PK
        uuid organization_id FK
        uuid category_id FK
        string title
        text content
        enum status
        uuid author_id FK
        timestamp published_at
        integer view_count
        integer helpful_count
        jsonb tags
    }
    
    it_kb_categories {
        uuid id PK
        uuid organization_id FK
        string name
        uuid parent_id FK
        integer display_order
        string icon
    }
    
    it_kb_article_versions {
        uuid id PK
        uuid article_id FK
        integer version_number
        text content
        uuid created_by FK
        timestamp created_at
        text change_notes
    }
    
    it_kb_article_feedback {
        uuid id PK
        uuid article_id FK
        uuid user_id FK
        boolean is_helpful
        text feedback_text
        timestamp created_at
    }
```

## Change Management ERD

```mermaid theme={null}
erDiagram
    it_change_requests ||--o{ it_change_approvals : requires
    it_change_requests ||--o{ it_change_tasks : includes
    it_change_requests }o--|| it_change_types : "of type"
    
    it_change_requests {
        uuid id PK
        uuid organization_id FK
        uuid change_type_id FK
        string change_number
        string title
        text description
        text justification
        text rollback_plan
        enum priority
        enum status
        uuid requester_id FK
        uuid implementer_id FK
        timestamp planned_start
        timestamp planned_end
        timestamp actual_start
        timestamp actual_end
    }
    
    it_change_types {
        uuid id PK
        uuid organization_id FK
        string name
        text description
        boolean requires_approval
        boolean requires_cab_review
        jsonb approval_workflow
    }
    
    it_change_approvals {
        uuid id PK
        uuid change_request_id FK
        uuid approver_id FK
        enum status
        text comments
        timestamp decided_at
    }
    
    it_change_tasks {
        uuid id PK
        uuid change_request_id FK
        string task_name
        text description
        uuid assigned_to FK
        integer task_order
        enum status
        timestamp completed_at
    }
```

## RLS Policy Coverage

| Table                     | SELECT | INSERT | UPDATE | DELETE | WITH CHECK | Status        |
| ------------------------- | ------ | ------ | ------ | ------ | ---------- | ------------- |
| it\_tickets               | ✅      | ✅      | ✅      | ✅      | ✅          | Complete      |
| it\_ticket\_comments      | ✅      | ✅      | ✅      | ✅      | ✅          | Complete      |
| it\_ticket\_history       | ✅      | ✅      | -      | -      | -          | History table |
| it\_sla\_policies         | ✅      | ✅      | ✅      | ✅      | ✅          | Complete      |
| it\_assets                | ✅      | ✅      | ✅      | ✅      | ✅          | Complete      |
| it\_software              | ✅      | ✅      | ✅      | ✅      | ✅          | Complete      |
| it\_kb\_articles          | ✅      | ✅      | ✅      | ✅      | ✅          | Complete      |
| it\_kb\_article\_versions | ✅      | ✅      | -      | -      | -          | Version table |
| it\_change\_requests      | ✅      | ✅      | ✅      | ✅      | ✅          | Complete      |

## Key Tables

### it\_tickets

Help desk tickets with full lifecycle tracking and SLA integration.

### it\_sla\_policies

Service Level Agreement definitions with priority-based targets.

### it\_assets

IT hardware and equipment inventory with assignment tracking.

### it\_kb\_articles

Self-service knowledge base articles with versioning.

### it\_change\_requests

ITIL-aligned change management workflow.

## Helper Functions

| Function                                 | Purpose                       |
| ---------------------------------------- | ----------------------------- |
| `it_has_org_access(org_id, user_id)`     | Check IT module access        |
| `it_calculate_sla_due(ticket_id)`        | Calculate SLA due date        |
| `it_get_available_licenses(software_id)` | Count available license seats |
| `it_get_open_tickets(user_id)`           | Get user's open tickets       |

## Cross-Module Dependencies

* **PF:** Organizations, profiles, notifications
* **FM:** Physical asset integration (hardware)
* **HR:** Employee assignment, onboarding IT setup
* **FW:** Change approval workflows