Information Technology Database Tables

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>;
}

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

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;
}

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;
}

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

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');

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

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');

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());

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']);

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

it_assets ↔ fm_assets (IT assets subset)