wulf-pulse/docs/itglue-sync.md

# IT Glue Data Sync

Full backup of all IT Glue data into local PostgreSQL tables prefixed `itg_`.

## Overview

The IT Glue sync pulls all data from the IT Glue REST API and upserts it into Postgres. This gives Pulse a local, queryable copy of all IT documentation for use in pipelines, AI context, reporting, and cross-referencing with RMM/PSA data.

- **40,000+ records** synced across 23 entity types
- Full sync takes ~7–10 minutes
- All tables use `ON CONFLICT DO UPDATE` — fully idempotent
- Sync history tracked in `itg_sync_history`

---

## Environment Variables

| Variable | Description |
|---|---|
| `ITGLUE_API_KEY` | IT Glue API key (from IT Glue → Account → API Keys) |

Set in `/opt/stacks/pulse/.env` and `docker-compose.yml` under the `app` service environment.

---

## API Details

- **Base URL:** `https://api.itglue.com`
- **Auth:** `x-api-key: <key>` header
- **Content-Type:** `application/vnd.api+json` (JSON:API format)
- **Pagination:** `page[size]` + `page[number]`, `meta.total-pages` for total
- **Attribute keys:** hyphenated (`organization-type-id`, `created-at`, etc.)

---

## Files

| File | Purpose |
|---|---|
| `lib/services/itglue-client.ts` | IT Glue API client — typed methods + `getRaw`/`getRawAllPages` for sync |
| `lib/services/itglue-sync-service.ts` | Full sync service — iterates all entities, upserts to Postgres |
| `app/api/itglue/status/route.ts` | `GET /api/itglue/status` — connection test |
| `app/api/itglue/sync/route.ts` | `POST /api/itglue/sync` — trigger sync; `GET` — status + history + counts |
| `app/admin/sync/itglue/page.tsx` | Admin UI page for IT Glue sync |
| `migrations/037_create_itglue_tables.sql` | Creates all `itg_*` tables |

---

## Database Tables

### Reference / Lookup Tables
| Table | Description |
|---|---|
| `itg_organization_types` | Org type definitions |
| `itg_organization_statuses` | Org status definitions |
| `itg_configuration_types` | Config type definitions (Server, Workstation, etc.) |
| `itg_configuration_statuses` | Config status definitions (Active, Inactive, etc.) |
| `itg_contact_types` | Contact type definitions |
| `itg_password_categories` | Password category definitions |
| `itg_manufacturers` | Hardware manufacturers |
| `itg_models` | Hardware models (linked to manufacturer) |
| `itg_operating_systems` | OS definitions |
| `itg_platforms` | Platform definitions |
| `itg_countries` | Country list with ISO codes |

### Core Tables
| Table | Key Columns | Notes |
|---|---|---|
| `itg_organizations` | `id`, `name`, `short_name`, `organization_type_id/name`, `organization_status_id/name`, `psa_integration`, `psa_id`, `parent_id` | 330 orgs |
| `itg_locations` | `id`, `organization_id`, `name`, `primary_location`, `address_*`, `city`, `region_name`, `postal_code`, `country_name`, `phone` | 751 locations |
| `itg_contacts` | `id`, `organization_id`, `first_name`, `last_name`, `title`, `contact_type_id/name`, `location_id`, `emails` (JSONB), `phones` (JSONB) | 7,113 contacts |
| `itg_configurations` | `id`, `organization_id`, `name`, `hostname`, `primary_ip`, `mac_address`, `serial_number`, `asset_tag`, `configuration_type_id/name`, `configuration_status_id/name`, `manufacturer_id/name`, `model_id/name`, `operating_system_id/name`, `rmm_id`, `rmm_integration_type` | 14,712 configs |
| `itg_flexible_asset_types` | `id`, `name`, `description`, `icon`, `enabled`, `builtin` | 41 types |
| `itg_flexible_asset_fields` | `id`, `flexible_asset_type_id`, `name`, `kind`, `required`, `use_for_title` | 1,140 fields |
| `itg_flexible_assets` | `id`, `organization_id`, `flexible_asset_type_id/name`, `name`, `traits` (JSONB), `archived` | 3,161 assets |
| `itg_password_folders` | `id`, `organization_id`, `name`, `inherited` | Per-org |
| `itg_passwords` | `id`, `organization_id`, `name`, `username`, `password`, `url`, `password_category_id/name`, `password_folder_id`, `otp_enabled`, `archived` | 270 passwords |
| `itg_documents` | `id`, `organization_id`, `name`, `content`, `draft`, `archived` | 537 documents |
| `itg_domains` | `id`, `organization_id`, `name`, `expires_at`, `registrar_name`, `whois_updated_at` | 212 domains |
| `itg_expirations` | `id`, `organization_id`, `resource_id`, `resource_type`, `resource_name`, `expiration_type`, `expiration_date`, `notify` | 9,770 expirations |
| `itg_sync_history` | `id`, `sync_type`, `status`, `triggered_by`, `started_at`, `completed_at`, `duration_ms`, `entities` (JSONB), `total_upserted` | Sync audit log |

All tables include `synced_at TIMESTAMPTZ` updated on every upsert.

---

## API Quirks & Workarounds

### Flexible Assets — require per-type filter
The `/flexible_assets` endpoint **requires** `filter[flexible-asset-type-id]`. Without it, the API returns 422. The sync iterates over all 41 flexible asset types and fetches assets per type.

### Password Folders, Documents, Expirations — no flat endpoint
These endpoints only exist as nested routes:
- `/organizations/:id/relationships/password_folders`
- `/organizations/:id/relationships/documents`
- `/organizations/:id/relationships/expirations`

The sync iterates over all 330 organizations for each of these.

### Configuration Interfaces — skipped
`/configuration_interfaces` has no flat endpoint. Per-config calls across 14,712 configurations would require 14,712+ API requests and take hours. This entity is intentionally excluded from the sync.

---

## Triggering a Sync

### Via Admin UI
Navigate to **Admin → IT Glue Sync** (or **Admin → Integrations & Sync → IT Glue**) and click **Full Sync**.

### Via API
```bash
# Trigger sync
curl -X POST http://localhost:3100/api/itglue/sync \
  -H "Content-Type: application/json" \
  -d '{"triggeredBy": "manual"}'

# Check status / history
curl http://localhost:3100/api/itglue/sync

# Test connection
curl http://localhost:3100/api/itglue/status
```

---

## Querying the Data

```sql
-- All organizations
SELECT id, name, organization_type_name, organization_status_name FROM itg_organizations;

-- Configurations for a specific org
SELECT name, hostname, primary_ip, configuration_type_name, configuration_status_name
FROM itg_configurations
WHERE organization_name ILIKE '%acme%';

-- Flexible assets by type
SELECT o.name AS org, fa.name, fa.traits
FROM itg_flexible_assets fa
JOIN itg_organizations o ON o.id = fa.organization_id
WHERE fa.flexible_asset_type_name = 'Servers';

-- Expiring domains (next 90 days)
SELECT organization_name, name, expires_at, registrar_name
FROM itg_domains
WHERE expires_at BETWEEN NOW() AND NOW() + INTERVAL '90 days'
ORDER BY expires_at;

-- Upcoming expirations
SELECT organization_name, resource_name, resource_type, expiration_type, expiration_date
FROM itg_expirations
WHERE expiration_date > NOW()
ORDER BY expiration_date
LIMIT 50;

-- Last sync summary
SELECT status, total_upserted, duration_ms,
       started_at, completed_at
FROM itg_sync_history
ORDER BY started_at DESC
LIMIT 5;
```

---

## Sync Performance (Initial Run)

| Entity | Records | Notes |
|---|---|---|
| organization_types | 10 | |
| organization_statuses | 2 | |
| configuration_types | 51 | |
| configuration_statuses | 2 | |
| contact_types | 9 | |
| password_categories | 11 | |
| manufacturers | 183 | |
| operating_systems | 365 | |
| platforms | 22 | |
| countries | 243 | |
| models | 1,803 | Per-manufacturer iteration |
| flexible_asset_types | 41 | |
| flexible_asset_fields | 1,140 | Per-type iteration |
| organizations | 330 | |
| locations | 751 | |
| contacts | 7,113 | |
| configurations | 14,712 | Largest entity |
| flexible_assets | 3,161 | Per-type iteration (41 API calls) |
| password_folders | 1 | Per-org iteration |
| passwords | 270 | |
| documents | 537 | Per-org iteration |
| domains | 212 | |
| expirations | 9,770 | Per-org iteration |
| **Total** | **40,739** | ~7.7 minutes |