> ## 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.

# Information Technology Database Tables

> The IT module provides IT Service Management (ITSM) capabilities including help desk ticketing, IT asset management, software licensing, change management, sec…

**Prefix:** `it_`\
**Table Count:** 35\
**Last Updated:** 2026-01-10

***

## Overview

The IT module provides IT Service Management (ITSM) capabilities including help desk ticketing, IT asset management, software licensing, change management, security incident response, and IT vendor management.

***

## Table Categories

### 1. Help Desk / Ticketing (4 tables)

| Table                   | Columns | Description             |
| ----------------------- | ------- | ----------------------- |
| `it_tickets`            | 38      | Support ticket records  |
| `it_ticket_comments`    | 12      | Ticket comments/updates |
| `it_ticket_attachments` | 12      | File attachments        |
| `it_sla_policies`       | 18      | SLA definitions         |

**Ticket Fields (38 columns):**

```typescript theme={null}
interface Ticket {
  id: string;
  ticket_number: string;
  subject: string;
  description: string;
  category: TicketCategory;
  subcategory?: string;
  priority: Priority;
  status: TicketStatus;
  // People
  requester_id: string;
  assigned_to?: string;
  team_id?: string;
  // Timing
  created_at: string;
  first_response_at?: string;
  resolved_at?: string;
  closed_at?: string;
  // SLA
  sla_policy_id?: string;
  sla_response_due?: string;
  sla_resolution_due?: string;
  sla_breached: boolean;
  // Links
  asset_id?: string;
  related_tickets?: string[];
  change_request_id?: string;
  // Custom
  custom_fields: Record<string, any>;
}
```

**Ticket Categories:**

* `hardware` - Hardware issues
* `software` - Software issues
* `network` - Network/connectivity
* `access` - Account/access requests
* `email` - Email issues
* `other` - General requests

**Ticket Status Flow:**

```
new → assigned → in_progress → pending → resolved → closed
                      ↓
                  on_hold
```

**SLA Metrics:**

* First Response Time
* Resolution Time
* Update Frequency

### 2. IT Asset Management (3 tables)

| Table                  | Columns | Description         |
| ---------------------- | ------- | ------------------- |
| `it_assets`            | 43      | IT asset records    |
| `it_asset_assignments` | 12      | Assignment history  |
| `it_asset_maintenance` | 14      | Maintenance records |

**IT Asset Types:**

* `desktop` - Desktop computers
* `laptop` - Laptops
* `server` - Servers
* `network` - Network equipment
* `mobile` - Mobile devices
* `peripheral` - Peripherals (monitors, printers)
* `other` - Other IT equipment

**Asset Fields (43 columns):**

```typescript theme={null}
interface ITAsset {
  id: string;
  asset_tag: string;
  asset_type: AssetType;
  name: string;
  manufacturer: string;
  model: string;
  serial_number: string;
  // Technical
  hostname?: string;
  ip_address?: string;
  mac_address?: string;
  os?: string;
  os_version?: string;
  // Hardware specs
  cpu?: string;
  ram_gb?: number;
  storage_gb?: number;
  // Assignment
  assigned_to?: string;
  department_id?: string;
  location_id?: string;
  // Lifecycle
  purchase_date?: string;
  warranty_expiration?: string;
  end_of_life_date?: string;
  status: AssetStatus;
}
```

### 3. Software Licensing (2 tables)

| Table                    | Columns | Description         |
| ------------------------ | ------- | ------------------- |
| `it_licenses`            | 24      | License records     |
| `it_license_assignments` | 12      | License allocations |

**License Types:**

* `perpetual` - One-time purchase
* `subscription` - Recurring subscription
* `volume` - Volume licensing
* `site` - Site license
* `concurrent` - Concurrent user license

**License Tracking:**

```typescript theme={null}
interface License {
  id: string;
  software_name: string;
  vendor: string;
  license_type: LicenseType;
  license_key?: string;  // Encrypted
  quantity_purchased: number;
  quantity_assigned: number;
  quantity_available: number;  // Computed
  purchase_date: string;
  expiration_date?: string;
  renewal_cost?: number;
  vendor_id?: string;
}
```

### 4. Change Management (6 tables)

| Table                 | Columns | Description            |
| --------------------- | ------- | ---------------------- |
| `it_change_requests`  | 34      | Change request records |
| `it_change_tasks`     | 16      | Implementation tasks   |
| `it_change_approvals` | 14      | Approval workflow      |
| `it_change_history`   | 12      | Change history         |
| `it_change_blackouts` | 12      | Blackout periods       |
| `it_change_templates` | 18      | Change templates       |

**Change Types:**

* `standard` - Pre-approved, low risk
* `normal` - Requires CAB approval
* `emergency` - Urgent, expedited approval

**Change Risk Levels:**

* `low` - Minimal impact
* `medium` - Moderate impact
* `high` - Significant impact
* `critical` - Major impact

**Change Status Flow:**

```
draft → submitted → under_review → approved → scheduled → implementing → completed
                         ↓
                    rejected
```

### 5. Security Management (6 tables)

| Table                        | Columns | Description                |
| ---------------------------- | ------- | -------------------------- |
| `it_security_incidents`      | 28      | Security incident records  |
| `it_security_patches`        | 18      | Patch tracking             |
| `it_patch_deployments`       | 14      | Deployment records         |
| `it_vulnerabilities`         | 22      | Vulnerability tracking     |
| `it_vulnerability_assets`    | 8       | Asset-vulnerability links  |
| `it_compliance_requirements` | 16      | IT compliance requirements |

**Incident Severity:**

* `critical` - Major breach/attack
* `high` - Significant security event
* `medium` - Moderate concern
* `low` - Minor issue

**Incident Categories:**

* `malware` - Malware/virus
* `phishing` - Phishing attempt
* `unauthorized_access` - Unauthorized access
* `data_breach` - Data breach
* `denial_of_service` - DoS attack
* `policy_violation` - Policy violation

### 6. IT Procurement (2 tables)

| Table                       | Columns | Description       |
| --------------------------- | ------- | ----------------- |
| `it_purchase_requests`      | 24      | Purchase requests |
| `it_purchase_request_items` | 14      | Line items        |

### 7. Vendor Management (2 tables)

| Table                 | Columns | Description       |
| --------------------- | ------- | ----------------- |
| `it_vendors`          | 22      | IT vendor records |
| `it_vendor_contracts` | 20      | Vendor contracts  |

### 8. User Onboarding/Offboarding (4 tables)

| Table                          | Columns | Description          |
| ------------------------------ | ------- | -------------------- |
| `it_onboarding_templates`      | 16      | Onboarding templates |
| `it_onboarding_tasks`          | 14      | Task definitions     |
| `it_onboarding_instances`      | 18      | Active onboarding    |
| `it_onboarding_task_instances` | 14      | Task tracking        |

**Onboarding Tasks:**

* Account creation (AD, email, apps)
* Hardware provisioning
* Software installation
* Access permissions
* Security training
* Policy acknowledgment

### 9. Knowledge Base (1 table)

| Table               | Columns | Description           |
| ------------------- | ------- | --------------------- |
| `it_knowledge_base` | 22      | IT knowledge articles |

**Article Categories:**

* `how_to` - How-to guides
* `troubleshooting` - Troubleshooting steps
* `policy` - IT policies
* `faq` - Frequently asked questions

### 10. Access Management (1 table)

| Table                | Columns | Description           |
| -------------------- | ------- | --------------------- |
| `it_access_accounts` | 18      | User account tracking |

### 11. Reporting (2 tables)

| Table                   | Columns | Description              |
| ----------------------- | ------- | ------------------------ |
| `it_report_definitions` | 18      | Report templates         |
| `it_report_runs`        | 14      | Report execution history |

### 12. User Preferences (1 table)

| Table                      | Columns | Description        |
| -------------------------- | ------- | ------------------ |
| `it_dashboard_preferences` | 12      | Dashboard settings |

### 13. Module Settings (1 table)

| Table                | Columns | Description      |
| -------------------- | ------- | ---------------- |
| `it_module_settings` | 63      | IT configuration |

**Key Settings:**

| Setting                    | Type    | Default |
| -------------------------- | ------- | ------- |
| `ticket_auto_number`       | boolean | true    |
| `ticket_prefix`            | text    | 'INC-'  |
| `default_sla_policy_id`    | uuid    | null    |
| `change_requires_approval` | boolean | true    |
| `change_blackout_check`    | boolean | true    |
| `asset_auto_discovery`     | boolean | false   |
| `license_alert_days`       | integer | 30      |

***

## Common Query Patterns

### Get Open Tickets by Priority

```typescript theme={null}
const { data } = await supabase
  .from('it_tickets')
  .select(`
    *,
    requester:pf_profiles!requester_id(first_name, last_name),
    assigned_to:pf_profiles!assigned_to(first_name, last_name),
    asset:it_assets(asset_tag, name)
  `)
  .eq('organization_id', orgId)
  .in('status', ['new', 'assigned', 'in_progress'])
  .order('priority')
  .order('created_at');
```

### Get SLA Breached Tickets

```typescript theme={null}
const { data } = await supabase
  .from('it_tickets')
  .select(`
    *,
    sla:it_sla_policies(name)
  `)
  .eq('organization_id', orgId)
  .eq('sla_breached', true)
  .neq('status', 'closed');
```

### Get Asset Inventory

```typescript theme={null}
const { data } = await supabase
  .from('it_assets')
  .select(`
    *,
    assigned_to:pf_profiles(first_name, last_name),
    department:pf_departments(name),
    location:pf_sites(name)
  `)
  .eq('organization_id', orgId)
  .eq('status', 'in_service')
  .order('asset_type');
```

### Get Expiring Licenses

```typescript theme={null}
const { data } = await supabase
  .from('it_licenses')
  .select(`
    *,
    vendor:it_vendors(name),
    assignments:it_license_assignments(
      user:pf_profiles(first_name, last_name)
    )
  `)
  .eq('organization_id', orgId)
  .lte('expiration_date', addDays(new Date(), 30))
  .gt('expiration_date', new Date().toISOString());
```

### Get Pending Changes

```typescript theme={null}
const { data } = await supabase
  .from('it_change_requests')
  .select(`
    *,
    requester:pf_profiles(first_name, last_name),
    tasks:it_change_tasks(*),
    approvals:it_change_approvals(
      approver:pf_profiles(first_name, last_name),
      status
    )
  `)
  .eq('organization_id', orgId)
  .in('status', ['submitted', 'under_review', 'approved', 'scheduled']);
```

***

## RLS Policies

IT uses role-based RLS:

* IT staff see all tickets
* Users see their own tickets
* Change approvals restricted to CAB members

**Helper Functions:**

* `it_user_is_it_staff()` - IT team check
* `it_user_can_view_ticket()` - Ticket visibility
* `it_user_is_cab_member()` - CAB membership

***

## ITIL Alignment

| ITIL Process        | IT Tables                      |
| ------------------- | ------------------------------ |
| Incident Management | `it_tickets`, `it_ticket_*`    |
| Problem Management  | `it_tickets` (type: 'problem') |
| Change Management   | `it_change_*`                  |
| Asset Management    | `it_assets`, `it_asset_*`      |
| Service Catalog     | `it_knowledge_base`            |
| SLA Management      | `it_sla_policies`              |

***

## Cross-Module Integration

**IT → HR Integration:**

```
it_onboarding_instances → hr_onboarding_instances
it_assets.assigned_to → hr_employees
it_tickets.requester_id → hr_employees
```

**IT → FM Integration:**

```
it_assets ↔ fm_assets (IT assets subset)
```

***

## See Also

* [IT Module Specs](https://github.com/Encore-OS/encoreos/tree/development/specs/it)
* ITSM Implementation
* [Database Schema Overview](/architecture/DATABASE_SCHEMA)