7.7 KiB
7.7 KiB
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-pagesfor 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
# 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
-- 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 |