193 lines
7.7 KiB
Markdown
193 lines
7.7 KiB
Markdown
# 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 |
|