wulfclaw/PULSE_DATABASE_SKILL.md

# Pulse Database Skill — Query Reference

> **Purpose:** This document describes the PostgreSQL database behind **Pulse**, an MSP operations platform built by Wulf Consulting. Use it to query Autotask PSA data, RMM alerts, security agents, backup status, IT documentation, engagement metrics, and more.

## Connection

- **Host:** `pulse-postgres` (Docker) or `localhost:5432`
- **Database:** `pulse_autotask`
- **User:** `pulse_user`
- **Read-only queries only** — no INSERT/UPDATE/DELETE
- **Timezone:** PostgreSQL server runs in **UTC**. All `timestamp without time zone` columns are UTC. Convert to ET with `AT TIME ZONE 'America/New_York'`.

---

## Data Domains at a Glance

| Domain | Key Tables | Approx Rows | Description |
|---|---|---|---|
| **Autotask PSA** | tickets, time_entries, companies, contacts, resources, configuration_items, contracts, projects, project_phases, tasks, ticket_notes | 152K tickets, 156K time entries, 7K CIs, 2.7K phases | Service desk, billing, contracts, clients |
| **Datto RMM** | datto_rmm_alerts, datto_rmm_devices, datto_rmm_sites | 21K alerts, 3.6K devices | Remote monitoring & management |
| **Auvik** | auvik_tenants, auvik_tenant_mappings | 17 tenants, 15 mappings | Network management — device inventory fetched live from API; tenant/company links stored in DB |
| **SentinelOne** | s1_agents, s1_threats, s1_sites | 2.8K agents, 4.1K threats | Endpoint security |
| **Veeam** | veeam_organizations, veeam_backup_jobs, veeam_backup_agents, veeam_alarms, veeam_protected_workloads, veeam_repositories, veeam_backup_servers | ~2.5K total | Backup & disaster recovery |
| **IT Glue** | itg_organizations, itg_configurations, itg_passwords, itg_flexible_assets, itg_contacts, itg_documents, itg_expirations, itg_domains, itg_locations | 14.7K configs | IT documentation |
| **Microsoft 365** | graph_users, teams_meetings, teams_meeting_attendees, engagement_snapshots | 14.4K meetings | Teams meetings, activity reports |
| **Zoom** | zoom_users, zoom_meetings, zoom_meeting_participants, zoom_calls | 2.8K calls | Zoom calls and meetings |
| **QuickBooks Online** | qbo_invoices, qbo_payments, qbo_deposits, qbo_transactions, qbo_reports | 6.9K invoices, 12.1K txns | Accounting — invoices, payments, deposits, financial reports |
| **Zabbix NMS** | zabbix_events, zabbix_wan_hosts | 231 events, 75 hosts | Network monitoring — WAN hosts, alert events |
| **Billing** | billing_items | 96K items | Invoice line items tied to tickets/projects/tasks |
| **Authentication** | "user", session, account, verification, two_factor | 3 users | Better Auth — Entra ID SSO, RBAC, sessions |

---

## 1. Autotask PSA — Core Service Desk

### tickets (~152K rows, 68 columns)

> **Timezone note:** `create_date`, `due_date_time`, `completed_date`, `resolved_date_time`, `first_response_date_time`, `last_activity_date` are all `timestamp without time zone` stored in **UTC**. To display in ET: `create_date AT TIME ZONE 'America/New_York'`.

The central table. Each row is a service ticket.

**Key columns:**
- `id` (bigint PK) — Autotask ticket ID
- `title` (varchar) — ticket subject line
- `description` (text) — full body/description
- `status` (int) — FK to `statuses.value`
- `priority` (int) — FK to `priorities.value`
- `queue_id` (int) — FK to `queues.value`
- `source` (int) — how the ticket was created (see Source Codes below)
- `company_id` (bigint) — FK to `companies.id`
- `contact_id` (bigint) — FK to `contacts.id`
- `assigned_resource_id` (bigint) — FK to `resources.id`
- `configuration_item_id` (bigint) — FK to `configuration_items.id`
- `contract_id` (bigint) — FK to `contracts.id`
- `project_id` (bigint) — FK to `projects.id`
- `issue_type` (int), `sub_issue_type` (int) — classification
- `ticket_type` (int) — 1=Incident, 2=Service Request, 5=Alert
- `create_date` (timestamp) — when opened
- `due_date_time` (timestamp) — SLA due
- `completed_date` (timestamp) — when closed
- `resolved_date_time` (timestamp) — when resolved
- `first_response_date_time` (timestamp) — first response SLA timestamp
- `last_activity_date` (timestamp) — most recent update
- `monitor_id` (bigint) — RMM monitor that created this ticket (if source=8)
- `monitor_type_id` (int) — type of monitor
- `is_deleted` (boolean) — soft delete flag

**Source codes (tickets.source):**
| Value | Meaning |
|---|---|
| 8 | Monitoring Alert (RMM/Datto) |
| 4 | Email |
| 21 | Portal |
| 2 | Phone/Voice |
| -1 | Insourced |
| -2 | Outsourced |
| 35 | Phish Alert |
| 6 | API |
| 17 | Internal Alert |

**Ticket types (tickets.ticket_type):**
| Value | Meaning |
|---|---|
| 1 | Incident |
| 2 | Service Request |
| 5 | Alert |
| NULL | Unclassified |

### companies (~246 rows, 41 columns)

Client/customer organizations.

**Key columns:**
- `id` (bigint PK)
- `company_name` (varchar) — display name
- `company_number` (varchar) — short code
- `is_active` (boolean)
- `company_type` (int) — 1=Customer, 2=Lead, 3=Prospect, 4=Dead, 6=Cancellation, 7=Vendor, etc.
- `owner_resource_id` (bigint) — account manager, FK to `resources.id`
- `classification` (varchar) — e.g. "Platinum", "Gold", etc.
- Address fields: `address1`, `city`, `state`, `postal_code`
- `last_activity_date` (timestamp)

### contacts (~4.2K rows, 37 columns)

People at client companies.

**Key columns:**
- `id` (bigint PK)
- `first_name`, `last_name`, `email_address`, `phone` (varchar)
- `company_id` (bigint) — FK to `companies.id`
- `is_active` (boolean)
- `title` (varchar) — job title

### resources (~40 columns)

Internal staff / technicians.

**Key columns:**
- `id` (bigint PK)
- `first_name`, `last_name`, `email` (varchar)
- `email_address` (varchar) — primary email
- `is_active` (boolean)
- `resource_type` (varchar)
- `default_service_desk_role_id` (bigint)
- `hire_date` (date)
- `location_id` (bigint)

### time_entries (~154K rows, 45 columns)

Work logged against tickets, tasks, or projects.

**Key columns:**
- `id` (bigint PK)
- `resource_id` (bigint) — who did the work, FK to `resources.id`
- `ticket_id` (bigint) — FK to `tickets.id` (NULL if task/project entry)
- `task_id` (bigint) — FK to `tasks.id`
- `project_id` (bigint) — FK to `projects.id`
- `company_id` (bigint) — FK to `companies.id`
- `entry_date` (timestamp) — date of work
- `hours_worked` (numeric) — actual hours
- `hours_to_bill` (numeric) — billable hours
- `start_date_time`, `end_date_time` (timestamp) — clock in/out
- `title` (varchar), `notes` (text), `internal_notes` (text)
- `billable` (boolean), `non_billable` (boolean)
- `billing_rate` (numeric), `cost_rate` (numeric), `revenue` (numeric)
- `contract_id` (bigint), `contract_service_id` (bigint)
- `role_id` (bigint)
- `is_deleted` (boolean)

### ticket_notes (~41K rows, 14 columns)

Notes/comments on tickets.

**Key columns:**
- `id` (bigint PK)
- `ticket_id` (bigint) — FK to `tickets.id`
- `title` (varchar), `description` (text) — note content
- `note_type` (int) — internal, external, etc.
- `publish` (int) — visibility
- `creator_resource_id` (bigint) — who wrote it
- `create_date_time` (timestamptz)

### configuration_items (~7K rows, 95 columns)

Devices/assets tracked in Autotask.

**Key columns:**
- `id` (bigint PK)
- `reference_title` (varchar) — device name (e.g. "DT037", "SRV-DC01")
- `reference_number` (varchar) — often a GUID from RMM
- `serial_number` (varchar)
- `company_id` (bigint) — FK to `companies.id`
- `contact_id` (bigint) — FK to `contacts.id`
- `is_active` (boolean)
- `device_type` (varchar)

**Note:** `reference_title` follows a naming convention per client (e.g. DT037 exists at multiple companies as separate CIs). Always filter by both `reference_title` AND `company_id` when searching.

### contracts (~44 columns)

Service agreements with clients.

**Key columns:**
- `id` (bigint PK)
- `company_id` (bigint) — FK to `companies.id`
- `contract_name` (varchar), `contract_number` (varchar)
- `contract_type` (int), `status` (int)
- `start_date`, `end_date` (date)
- `estimated_hours` (numeric), `estimated_revenue` (numeric)

### contract_services (~8.2K rows)

Line items on contracts.

- `contract_id` → `contracts.id`
- `company_id` → `companies.id`
- `service_name` (text), `unit_price`, `quantity`

### projects (~314 rows, 36 columns)

Planned engagements for client work — distinct from reactive service tickets. A project has a defined scope, timeline, and budget. Think of projects as the planned/scheduled side of the business (switch deployments, onboarding, infrastructure upgrades) while tickets are the reactive/support side.

**Key columns:**
- `id` (bigint PK) — Autotask project ID
- `company_id` (bigint) — FK to `companies.id`
- `project_name` (varchar) — display name
- `project_number` (varchar) — unique identifier string
- `description` (text)
- `status` (int) — project status code
- `type` (int) — project type
- `project_lead_resource_id` (bigint) — FK to `resources.id`
- `owner_resource_id` (bigint) — FK to `resources.id`
- `start_date_time`, `end_date_time` (timestamp) — scheduled window
- `completed_date_time` (timestamp)
- `estimated_time` (numeric) — estimated hours
- `actual_hours` (numeric) — hours logged so far
- `estimated_sale_cost`, `labor_estimated_revenue` (numeric) — financial estimates
- `completed_percentage` (numeric) — 0–100
- `last_activity_date_time` (timestamp)
- `is_deleted` (boolean)

**Relationship to tickets:** A ticket can optionally be linked to a project via `tickets.project_id`. This is typically used when a ticket was generated as part of project work (e.g. a change request within a project).

### project_phases (~2.7K rows, 19 columns)

Phases group tasks within a project (e.g. "Equipment Selection", "Installation", "Testing"). Every phase belongs to exactly one project.

**Key columns:**
- `id` (bigint PK) — Autotask phase ID (shares namespace with tasks)
- `project_id` (bigint) — FK to `projects.id` (no FK constraint — may reference archived projects)
- `parent_phase_id` (bigint) — parent phase for nested phase trees
- `title` (varchar) — phase name
- `description` (text)
- `phase_number` (varchar) — e.g. `T20251222.0116`
- `estimated_hours` (numeric)
- `start_date_time`, `due_date` (timestamp) — scheduled window
- `create_date_time`, `last_activity_date_time` (timestamp)
- `is_scheduled` (boolean)
- `creator_resource_id` (bigint) — FK to `resources.id`
- `is_deleted` (boolean)

**Why this table matters:** The Autotask Tasks bulk API omits `projectID` from its response. After each `project_phases` sync, an automatic backfill updates `tasks.project_id` by joining `tasks.phase_id = project_phases.id`. This is the only way to resolve which project a task belongs to.

**Join to get a task's project via phase:**
```sql
SELECT t.id, t.title, pp.title AS phase, p.project_name
FROM tasks t
JOIN project_phases pp ON pp.id = t.phase_id
LEFT JOIN projects p ON p.id = pp.project_id
WHERE t.is_deleted IS NOT TRUE;
```

### tasks (~5K rows, 34 columns)

Tasks are discrete units of work that live **inside** a project or a ticket. They are NOT standalone items — every task belongs to either a project (`project_id`) or a ticket (`ticket_id`), not both simultaneously.

**Key difference from tickets:**
- **Tickets** = reactive service desk items. Created by clients, RMM alerts, email, or portal. Queued and triaged.
- **Tasks** = planned steps within a project (or checklist items on a ticket). Created by technicians and project managers. Have structured phases and scheduling.

**Real-world examples of tasks:**
- A "Switch Deployment" project might have tasks: `Switch [Config] - ynghynswp27`, `Switch [Build] - ynghynswp27`, `Switch [Deploy] - ynghynswp27`
- Each task represents a discrete phase of work, assigned to a resource with estimated hours

**Key columns:**
- `id` (bigint PK) — Autotask task ID
- `title` (varchar) — task name
- `description` (text)
- `project_id` (bigint) — FK to `projects.id` (if this is a project task)
- `ticket_id` (bigint) — FK to `tickets.id` (if this is a ticket sub-task)
- `phase_id` (bigint) — project phase grouping (project tasks only)
- `assigned_resource_id` (bigint) — FK to `resources.id`
- `creator_resource_id` (bigint) — FK to `resources.id`
- `status` (int), `priority` (int)
- `task_type` (int)
- `task_number` (varchar) — human-readable number within the project
- `estimated_hours` (numeric), `remaining_hours` (numeric)
- `start_date_time`, `end_date_time` (timestamp) — scheduled window
- `completed_date_time` (timestamp)
- `task_is_billable` (boolean)
- `is_deleted` (boolean)

**Time entries on tasks:** `time_entries.task_id` links work logs to specific tasks. Task time entries also carry `project_id` and `company_id` for quick aggregation without joins.

**Known sync behaviour — `project_id` population:**
The Autotask bulk Tasks API does not return `projectID` in its response. `project_id` is instead populated post-sync via a backfill that joins `tasks.phase_id → project_phases.id → project_phases.project_id`. This backfill runs automatically every time `project_phases` is synced. Tasks whose parent project is completed/archived (and therefore absent from our `projects` table) will still have `project_id = NULL` — use `phase_id → project_phases.project_id` directly in those cases.

**Finding project tasks vs ticket tasks:**
```sql
-- Project tasks only (project_id populated via phase backfill)
SELECT t.*, p.project_name FROM tasks t
JOIN projects p ON p.id = t.project_id
WHERE t.project_id IS NOT NULL AND t.is_deleted IS NOT TRUE;

-- Tasks whose project is archived (project_id NULL but phase_id resolvable)
SELECT t.*, pp.project_id AS phase_project_id, pp.title AS phase_title
FROM tasks t JOIN project_phases pp ON pp.id = t.phase_id
WHERE t.project_id IS NULL AND t.is_deleted IS NOT TRUE;

-- Ticket sub-tasks only
SELECT t.*, tk.title AS ticket_title FROM tasks t
JOIN tickets tk ON tk.id = t.ticket_id
WHERE t.ticket_id IS NOT NULL AND t.is_deleted IS NOT TRUE;
```

### billing_items (~96K rows)

Invoice line items linked to tickets, tasks, or projects.

- `ticket_id` → `tickets.id`, `task_id` → `tasks.id`, `project_id` → `projects.id`
- `company_id` → `companies.id`
- `quantity`, `rate`, `total_amount`, `unit_cost`, `unit_price`

---

## 2. Lookup / Picklist Tables

These map integer codes to human-readable labels. Join on `value`.

### statuses (ticket statuses)

Join: `statuses.value = tickets.status`

| Value | Label |
|---|---|
| 1 | New |
| 5 | Complete |
| 7 | Waiting Customer |
| 8 | In Progress |
| 9 | Waiting Materials |
| 10 | Dispatched |
| 11 | Escalate |
| 12 | Waiting Vendor |
| 13 | Waiting Approval |
| 14 | Resource Assigned |
| 15 | Ready to Deploy |
| 16 | Reopened |
| 17 | Info Req |
| 19 | End User Note Added |
| 20 | Equipment Pulled |
| 21 | Waiting Verification |
| 25 | On Hold |
| 27 | Resolution Plan |
| 30 | Service Call Scheduled |
| 37 | Client Non-Responsive |
| 45 | Pending Next Site Visit |
| 46 | Burn-in In Progress |
| 47 | Resource Requested |
| 48 | Escalate to MC |
| 51 | Reconcile Billing |
| 54 | Resolved \<CSAT Survey\> |
| 55 | Huddle Review |
| 56 | Tracking Shipment |
| 57 | Escalate to Wulf |
| 58 | Pre-Pick Order |
| 59 | Need to Order/Fulfill |
| 60 | Loading Customer Config |
| 61 | Escalate to CSM |
| 62 | Escalate to Customer HD |
| 64 | Waiting Trivium Employee |
| 65 | Waiting Procurement |
| 66 | Internal Note Added |
| 67 | Quote Delivered |
| 68 | Ready for Customer Config |
| 69 | Outsourced |
| 70 | Waiting Quote Acceptance |
| 71 | Waiting Project/Ticket |

### priorities

Join: `priorities.value = tickets.priority`

| Value | Label |
|---|---|
| 1 | Standard |
| 2 | Medium |
| 3 | Standard |
| 4 | Critical |
| 6 | High |
| 7 | Security Event |
| 8 | Minor Service |
| 9 | Major Service |
| 10 | Installation |
| 11 | Fast Track |

### queues (46 active)

Join: `queues.value = tickets.queue_id`

| Value | Label |
|---|---|
| 5 | Client Triage |
| 6 | Post Sale |
| 8 | Monitoring Alert |
| 29682833 | Level 1 Support |
| 29682969 | Level 2 Support |
| 29703428 | Level 3 Support |
| 29749490 | Client Success |
| 29793481 | App-Care |
| 29807035 | Alert II |
| 29807036 | Alert I |
| 29832283 | Operations Triage |
| 29853695 | Follow Up |
| 29853697 | Trivium Packaging - Help Desk |
| 29853698 | Project Delivery |
| 29853699 | Mission Control |
| 29853700 | Deployment |
| 29853701 | IT Operations |
| 29853702 | Recurring Tickets |
| 29853706 | Premier Automation Help Desk |
| 29853710 | TTG Help Desk |
| 29853714 | Glunt Help Desk |
| 29853719 | TNT Pizza Help Desk |
| 29853720 | TTG Machines |
| 29853721 | TTG NetOps |
| 29853723 | TTG Finance |
| 29853724 | TTG Safety |
| 29853726 | TTG Remote |
| 29853727 | Purchasing |
| 29853728 | TTG New User Access |
| 29853729 | TTG Term User Access |
| 29853731 | Trivium Packaging - MES |
| 29853738 | TTG Security |
| 29853740 | TTG Change Mgmt |
| 29853741 | Subcontractor |
| 29853750 | PER Service Desk |
| 29853751 | PER Network Operations Center |
| 29853752 | PER Security Operations Center |
| 29853753 | LEC Service Desk |
| 29853754 | LEC Network Operations Center |
| 29853755 | LEC Security Operations Center |
| 29853756 | VCF Service Desk |
| 29853757 | VCF Network Operations |
| 29853758 | VCF Security Operations Center |
| 29853766 | Sales |
| 29780304 | TaskFire |
| 29753333 | Waiting Verification |

### issue_types

Join: `issue_types.value = tickets.issue_type`

| Value | Label |
|---|---|
| 4 | Upgrade |
| 6 | New Install |
| 7 | Monitoring Alert |
| 10 | Break/Fix |
| 11 | Maintenance |
| 12 | Help Desk |
| 13 | Email |
| 14 | Vendor |
| 15 | User Education |
| 16 | Provision |
| 18 | Purchase |
| 19 | Deploy |
| 20 | Reactive |
| 21 | MAC |
| 22 | Centralized Services |
| 23 | NOC Services |
| 25 | Networking |
| 27 | WiFi |
| 28 | Client Success |
| 29 | Automation |
| 30 | Server |
| 31 | Hardware |
| 32 | LOB Software |
| 33 | Workstation |
| 36 | 24-Hour Emergency Support |

---

## 3. Datto RMM

### datto_rmm_alerts (~20.7K rows, 61 columns)

- `id` (int PK), `uid` (text) — alert identifiers
- `alert_category`, `alert_type`, `alert_message_en` — what triggered
- `priority` (text) — Critical, High, Moderate, Low, Information
- `resolved` (boolean), `resolved_on` (timestamptz)
- `muted` (boolean)
- `ticket_number` (text) — linked Autotask ticket
- `device_hostname`, `device_ip`, `device_os`, `device_id`
- `site_id` (text) — FK to `datto_rmm_sites`
- `timestamp` (timestamptz) — when alert fired

### datto_rmm_devices (~3.6K rows, 42 columns)

- `id` (int PK), `uid` (text), `hostname`
- `device_type_category` (text) — Server, Desktop, Laptop, Network Device
- `operating_system`, `domain`, `int_ip_address`, `ext_ip_address`
- `online` (boolean), `last_seen` (timestamptz)
- `last_logged_in_user` (text)
- `antivirus_product`, `antivirus_status`, `patch_status`
- `site_id` (int) — FK to `datto_rmm_sites.id`
- `udf` (jsonb) — custom fields

### datto_rmm_sites (~16 columns)

- `id` (int PK), `uid`, `name`
- `autotask_company_id` (int) — **FK to `companies.id`** (links RMM sites to Autotask clients)
- `autotask_company_name`
- `number_of_devices`, `number_of_online_devices`

**Join pattern:** `datto_rmm_sites.autotask_company_id = companies.id`

---

## 4. Auvik Network Management

Auvik is the network device management platform used to monitor and manage client network infrastructure (switches, routers, firewalls, APs, etc.). It organizes clients as **tenants** — one tenant per client organization.

**Important:** Auvik **device inventory is NOT stored in the Pulse database.** Device data (hostnames, IPs, device types, configs, firmware) is fetched **live from the Auvik API** at query time. Only the tenant list and their mappings to Autotask companies are persisted in the DB.

### auvik_tenants (~17 rows)

The list of Auvik tenants (client organizations) known to this Pulse instance.

**Key columns:**
- `id` (int PK) — internal DB ID
- `tenant_id` (varchar) — Auvik's unique tenant identifier (numeric string, e.g. `"197643669426944326"`)
- `tenant_name` (varchar) — short name / domain prefix (e.g. `"seubert"`, `"wulfconsulting"`)
- `domain_prefix` (varchar) — same as tenant_name; used in Auvik URLs
- `device_count` (int) — last known device count from sync
- `last_sync_at` (timestamptz) — when this tenant was last synced
- `created_at`, `updated_at` (timestamptz)

**Sample tenants:** vollmer, poha, premierautomation, wulfconsulting, vorteqcoilfinishers, seubert, hynesindustries, greco, therla, and ~8 others.

### auvik_tenant_mappings (~15 rows)

Maps each Auvik tenant to its corresponding Autotask company. This is the cross-platform link between network management and the PSA.

**Key columns:**
- `id` (int PK)
- `auvik_tenant_id` (varchar) — Auvik tenant ID, matches `auvik_tenants.tenant_id`
- `auvik_tenant_name` (varchar) — short name
- `autotask_company_id` (int) — FK to `companies.id`
- `autotask_company_name` (varchar) — denormalized for convenience
- `created_at`, `updated_at` (timestamptz)

**Join pattern — Auvik tenant → Autotask company:**
```sql
SELECT atm.auvik_tenant_name, c.company_name, c.id AS autotask_company_id
FROM auvik_tenant_mappings atm
JOIN companies c ON c.id = atm.autotask_company_id;
```

**What Auvik tracks (via live API, not DB):**
- Network device inventory: switches, routers, firewalls, access points, printers, UPS units
- Device details: hostname, IP addresses, MAC addresses, model, firmware version, vendor
- Online/offline status and last seen time
- Device configurations (running config snapshots — see `/api/auvik/device-config` endpoint)
- Network topology and interface relationships

**Auvik device naming convention:** Hostnames in Auvik follow the pattern `{client_prefix}{device_type}{sequence}`, e.g. `ynghynswp27` = `ynghy` (client) + `nsw` (network switch) + `p27` (port count / unit). These hostnames also appear in Autotask ticket notes and task titles when work is performed on the device.

---

## 6. SentinelOne

### s1_agents (~2.8K rows, 42 columns)

Endpoint security agents.

- `id` (varchar PK) — S1 agent ID
- `computer_name`, `os_name`, `os_type`
- `site_id` → `s1_sites.id`, `site_name`
- `is_active`, `is_decommissioned`
- `infected` (boolean), `active_threats` (int)
- `network_status`, `mitigation_mode`, `detection_state`
- `external_ip`, `last_active_date`, `last_logged_in_user_name`
- `firewall_enabled` (boolean)

### s1_threats (~4.1K rows, 25 columns)

Detected threats.

- `id` (varchar PK)
- `threat_name`, `classification`, `confidence_level`
- `mitigation_status`, `analyst_verdict`, `incident_status`
- `agent_id` → `s1_agents.id`
- `agent_computer_name`, `agent_os_name`
- `site_id` → `s1_sites.id`

### s1_sites (~22 columns)

- `id` (varchar PK), `name`, `account_name`
- `health_status`, `active_licenses`, `total_licenses`

### s1_company_mappings

Maps S1 sites to Autotask companies for cross-referencing.

---

## 7. Veeam Backup

### veeam_organizations (~17 columns)

- `instance_uid` (PK), `name`, `company_id`
- All other Veeam tables FK to `veeam_organizations.instance_uid`

### veeam_backup_jobs (~234 rows)

- `instance_uid`, `name`, `type`, `status`, `last_run`, `next_run`
- `organization_uid` → `veeam_organizations`
- `backup_server_uid` → `veeam_backup_servers`

### veeam_backup_agents (~727 rows)

- Backup agents installed on endpoints
- `organization_uid` → `veeam_organizations`

### veeam_alarms (~581 rows)

- Active alarms/alerts
- `organization_uid` → `veeam_organizations`

### veeam_protected_workloads, veeam_repositories, veeam_backup_servers

Supporting tables for backup infrastructure.

---

## 8. IT Glue Documentation

### itg_organizations (~330 rows)

- `id` (bigint PK), `name`, `short_name`, `organization_type_name`
- `psa_id` (varchar) — Autotask company ID (string). Join: `itg_organizations.psa_id::bigint = companies.id`

### itg_configurations (~14.7K rows)

Hardware/software assets documented in IT Glue.

- `id`, `organization_id` → `itg_organizations.id`
- `name`, `hostname`, `serial_number`, `asset_tag`
- `configuration_type_name`, `configuration_status_name`
- `primary_ip`, `mac_address`, `operating_system`
- `warranty_expires_at`, `installed_at`

### itg_passwords (~17 columns)

- `id`, `organization_id`, `name`, `username`, `password_category_name`
- `url`, `notes`

### itg_flexible_assets (~3.2K rows)

Custom documentation (e.g. Backup configs, Email configs, LAN/VLAN, Voice/PBX).

- `id`, `organization_id`, `flexible_asset_type_id`, `flexible_asset_type_name`
- `traits` (jsonb) — all custom field values

### itg_contacts, itg_documents, itg_expirations, itg_domains, itg_locations

Supporting IT documentation tables.

---

## 9. Engagement & Communications

### graph_users

Microsoft 365 users synced from Azure AD.

- `id` (varchar PK), `display_name`, `email`, `job_title`, `department`
- `account_enabled` (boolean)

### teams_meetings (~14.3K rows)

Teams calendar events / meetings.

- `id` (int PK), `user_email`, `subject`
- `start_time`, `end_time` (timestamptz), `duration_minutes`
- `attendee_count`, `client_attendee_count`, `has_client_attendees` (boolean)

### teams_meeting_attendees (~12.6K rows)

- `meeting_id` → `teams_meetings.id`
- `attendee_email`, `attendee_name`
- `matched_contact_id` → `contacts.id`
- `matched_company_id` → `companies.id`

### engagement_snapshots (~1K rows)

Weekly/monthly aggregates of M365 activity per user.

- `user_email`, `period_type` (D7, D30, D90, D180)
- `teams_chat_messages`, `teams_calls`, `teams_meetings_attended`, `teams_meetings_organized`
- `emails_sent`, `emails_received`, `emails_read`

### zoom_meetings, zoom_meeting_participants

- `host_email`, `topic`, `start_time`, `end_time`, `duration_minutes`
- Participants with `matched_contact_id` → `contacts.id`, `matched_company_id` → `companies.id`
- `is_internal` (boolean) — internal vs external attendee

### zoom_calls (~2.8K rows)

- `resource_email`, `direction` (inbound/outbound), `call_status`
- `other_party_number`, `other_party_name`
- `matched_contact_id`, `matched_company_id`

---

## 10. QuickBooks Online (Accounting)

### qbo_invoices (~6.9K rows)

- `id` (text PK) — QBO invoice ID
- `doc_number` (text) — invoice number (e.g. "1042")
- `txn_date` (date), `due_date` (date)
- `customer_ref_id`, `customer_ref_name` — QBO customer
- `total_amt` (numeric), `balance` (numeric) — amounts
- `status` (text) — Paid, Overdue, etc.
- `line_items` (jsonb) — invoice line detail
- `linked_txns` (jsonb) — linked payments

### qbo_payments (~4.5K rows)

- `id` (text PK)
- `txn_date` (date), `total_amt` (numeric)
- `customer_ref_id`, `customer_ref_name`
- `payment_method_ref` (text), `deposit_account_ref` (text)
- `unapplied_amt` (numeric)
- `linked_txns` (jsonb) — linked invoices

### qbo_deposits (~1.8K rows)

- `id` (text PK)
- `txn_date` (date), `total_amt` (numeric)
- `deposit_to_account_ref_id`, `deposit_to_account_ref_name`
- `line_items` (jsonb)

### qbo_transactions (~12.1K rows)

General ledger transactions (expenses, bills, journal entries, etc.).

- `id` (text), `txn_type` (text) — composite PK
- `txn_date` (date), `doc_number` (text)
- `entity_ref_id`, `entity_ref_name`, `entity_type` — vendor/customer
- `account_ref_id`, `account_ref_name` — GL account
- `total_amt` (numeric)
- `line_items` (jsonb)

**Transaction types:** Bill, BillPayment, Expense, JournalEntry, Transfer, VendorCredit, CreditMemo, SalesReceipt, Estimate, PurchaseOrder, etc.

### qbo_reports (~36 rows)

Periodic financial reports stored as JSON.

- `id` (serial PK)
- `report_type` (text) — ProfitAndLoss, BalanceSheet, CashFlow
- `period_start` (date), `period_end` (date)
- `report_data` (jsonb) — full QBO report payload
- Unique on `(realm_id, report_type, period_start, period_end)`

---

## 11. Zabbix NMS

### zabbix_wan_hosts (~75 rows)

WAN monitoring hosts auto-synced from RMM site public IPs.

- `host_id` (text) — Zabbix host ID
- `host` (text) — hostname in Zabbix
- `name` (text) — display name
- `ip` (text) — WAN IP address
- `status` (int) — 0=enabled, 1=disabled
- `company_id` (bigint) — FK to `companies.id`
- `rmm_site_id` (int) — FK to `datto_rmm_sites.id`

### zabbix_events (~228 rows)

Zabbix alert events.

- `event_id` (text PK)
- `host_id` (text), `host_name` (text)
- `trigger_id` (text), `trigger_name` (text)
- `severity` (int) — 0=Not classified, 1=Info, 2=Warning, 3=Average, 4=High, 5=Disaster
- `value` (int) — 0=OK, 1=Problem
- `clock` (timestamptz) — event time

---

## 12. Authentication (Better Auth + Entra ID)

### "user" table (~3 rows)

**Note:** Table name is `"user"` (quoted) — must be quoted in SQL.

- `id` (text PK)
- `name` (text), `email` (text UNIQUE)
- `"emailVerified"` (boolean), `image` (text)
- `role` (text) — `super-admin`, `admin`, `user`
- `banned` (boolean), `"bannedReason"` (text), `"banExpires"` (timestamp)
- `requires_setup` (boolean), `"twoFactorEnabled"` (boolean)
- `"createdAt"`, `"updatedAt"` (timestamp)

**Note:** Better Auth uses **camelCase** column names — must be double-quoted in raw SQL.

### session

- `id` (text PK), `"userId"` → `"user".id`
- `token` (text UNIQUE), `"expiresAt"` (timestamp)
- `"ipAddress"`, `"userAgent"` (text)

### account

OAuth provider links (Microsoft Entra ID).

- `id` (text PK), `"userId"` → `"user".id`
- `"providerId"` (text) — e.g. `microsoft`
- `"accountId"` (text) — provider-specific user ID
- `"accessToken"`, `"refreshToken"`, `"idToken"` (text)

---

## 13. Common Join Patterns

```sql
-- Ticket with company, resource, and status label
SELECT t.id, t.title, c.company_name, 
       r.first_name || ' ' || r.last_name AS technician,
       s.label AS status_label, p.label AS priority_label
FROM tickets t
LEFT JOIN companies c ON c.id = t.company_id
LEFT JOIN resources r ON r.id = t.assigned_resource_id
LEFT JOIN statuses s ON s.value = t.status
LEFT JOIN priorities p ON p.value = t.priority
WHERE t.is_deleted IS NOT TRUE;

-- Time entries for a ticket
SELECT te.entry_date, te.hours_worked, te.notes,
       r.first_name || ' ' || r.last_name AS technician
FROM time_entries te
JOIN resources r ON r.id = te.resource_id
WHERE te.ticket_id = $1 AND te.is_deleted IS NOT TRUE;

-- RMM device → Autotask company
SELECT d.hostname, d.device_type_category, d.operating_system,
       s.autotask_company_name, d.online, d.last_seen
FROM datto_rmm_devices d
JOIN datto_rmm_sites s ON s.id = d.site_id;

-- Config item lookup (always filter by company too)
SELECT ci.id, ci.reference_title, ci.serial_number, c.company_name
FROM configuration_items ci
JOIN companies c ON c.id = ci.company_id
WHERE ci.reference_title = 'DT037' AND ci.company_id = $1;

-- IT Glue org → Autotask company
SELECT ig.name, ig.id AS itg_org_id, c.id AS autotask_company_id, c.company_name
FROM itg_organizations ig
JOIN companies c ON ig.psa_id::bigint = c.id;

-- Meetings with client attendees
SELECT tm.subject, tm.start_time, tm.duration_minutes,
       tma.attendee_name, c.company_name
FROM teams_meetings tm
JOIN teams_meeting_attendees tma ON tma.meeting_id = tm.id
LEFT JOIN companies c ON c.id = tma.matched_company_id
WHERE tm.has_client_attendees = true;

-- QBO: Revenue by month (from invoices)
SELECT DATE_TRUNC('month', txn_date) AS month, 
       SUM(total_amt) AS total_invoiced, COUNT(*) AS invoice_count
FROM qbo_invoices
GROUP BY 1 ORDER BY 1 DESC;

-- QBO: Outstanding balances by customer
SELECT customer_ref_name, SUM(balance) AS outstanding
FROM qbo_invoices WHERE balance > 0
GROUP BY 1 ORDER BY 2 DESC;

-- Zabbix: Active WAN problems with company
SELECT ze.host_name, ze.trigger_name, ze.severity, ze.clock,
       c.company_name
FROM zabbix_events ze
JOIN zabbix_wan_hosts zwh ON zwh.host_id = ze.host_id
LEFT JOIN companies c ON c.id = zwh.company_id
WHERE ze.value = 1 ORDER BY ze.clock DESC;

-- Project with all its tasks and hours logged
SELECT p.project_name, p.status, p.actual_hours,
       t.title AS task_title, t.status AS task_status,
       t.estimated_hours, t.remaining_hours,
       r.first_name || ' ' || r.last_name AS assigned_to
FROM projects p
LEFT JOIN tasks t ON t.project_id = p.id AND t.is_deleted IS NOT TRUE
LEFT JOIN resources r ON r.id = t.assigned_resource_id
WHERE p.id = $1 AND p.is_deleted IS NOT TRUE;

-- All time entries for a project (direct + via tasks)
SELECT te.entry_date, te.hours_worked, te.notes,
       r.first_name || ' ' || r.last_name AS technician,
       t.title AS task_title,
       te.billable
FROM time_entries te
JOIN resources r ON r.id = te.resource_id
LEFT JOIN tasks t ON t.id = te.task_id
WHERE te.project_id = $1 AND te.is_deleted IS NOT TRUE
ORDER BY te.entry_date;

-- Hours by technician across all projects this month
SELECT r.first_name || ' ' || r.last_name AS technician,
       SUM(te.hours_worked) AS total_hours,
       SUM(CASE WHEN te.billable THEN te.hours_worked ELSE 0 END) AS billable_hours
FROM time_entries te
JOIN resources r ON r.id = te.resource_id
WHERE te.project_id IS NOT NULL
  AND te.entry_date >= DATE_TRUNC('month', NOW())
  AND te.is_deleted IS NOT TRUE
GROUP BY 1 ORDER BY 2 DESC;

-- Auvik tenant → Autotask company → open tickets
SELECT atm.auvik_tenant_name, c.company_name,
       COUNT(tk.id) AS open_tickets
FROM auvik_tenant_mappings atm
JOIN companies c ON c.id = atm.autotask_company_id
LEFT JOIN tickets tk ON tk.company_id = c.id
  AND tk.status NOT IN (5) -- not Complete
  AND tk.is_deleted IS NOT TRUE
GROUP BY 1, 2 ORDER BY 3 DESC;
```

---

## 14. Important Notes

1. **Soft deletes:** Most Autotask tables have `is_deleted` (boolean) and `deleted_at`. Always add `WHERE is_deleted IS NOT TRUE` unless you want deleted records.

2. **Picklist joins:** `status`, `priority`, `queue_id`, `source` on tickets are integer codes. Join to `statuses`, `priorities`, `queues` on `.value` for labels.

3. **Configuration item names are NOT unique globally.** Names like "DT037" are a per-client naming convention. Always pair with `company_id`.

4. **Timestamps — all UTC:** The PostgreSQL server timezone is UTC. All `timestamp without time zone` columns (Autotask) are stored in UTC. `timestamp with time zone` columns (Teams, Zoom, QBO) are also UTC-normalized. To display in Eastern Time: `column AT TIME ZONE 'America/New_York'`. Example: `create_date AT TIME ZONE 'America/New_York'`.

5. **Monitor tickets:** `tickets.source = 8` indicates RMM-generated tickets. `monitor_id` links to the specific Datto RMM monitor. These represent ~74% of all tickets.

6. **Cross-platform linking:**
   - RMM → Autotask: `datto_rmm_sites.autotask_company_id = companies.id`
   - IT Glue → Autotask: `itg_organizations.psa_id::bigint = companies.id`
   - S1 → Autotask: via `s1_company_mappings`
   - Auvik → Autotask: `auvik_tenant_mappings.autotask_company_id = companies.id`
   - Zoom/Teams → Contacts: `matched_contact_id` / `matched_company_id` columns
   - Config Items → RMM: `configuration_items.reference_number` sometimes matches RMM device UIDs
   - Auvik hostnames → Autotask tickets/tasks: search `ticket_notes.description` and `tasks.title` for the device hostname (e.g. `ynghynswp27`) to find all work done on a network device

7. **Tickets vs Projects vs Tasks — when to use which:**
   - Query `tickets` when asking about: support requests, break/fix, helpdesk, RMM alerts, client-reported issues, SLA performance, response times, queue volumes
   - Query `projects` when asking about: planned work, deployments, onboarding, infrastructure upgrades, project status, project hours/budget
   - Query `tasks` when asking about: individual steps within a project, task completion rates, who is assigned to what within a project, task-level time tracking. Tasks have both `project_id` (project tasks) and `ticket_id` (ticket sub-tasks) — check which is populated.
   - Query `time_entries` for actual hours worked — it spans all three: `ticket_id`, `task_id`, and `project_id` can all be non-null depending on context

8. **Row counts** (as of March 24, 2026): tickets 152K, time_entries 156K, billing_items 96K, ticket_notes 42K, teams_meetings 14.4K, itg_configurations 14.7K, configuration_items 7K, tasks 5K, projects 314, companies 246, contacts 4.2K, datto_rmm_devices 3.6K, datto_rmm_alerts 21K, s1_agents 2.8K, s1_threats 4.1K, auvik_tenants 17, auvik_tenant_mappings 15, zoom_calls 2.8K, qbo_invoices 6.9K, qbo_transactions 12.1K, qbo_payments 4.5K, qbo_deposits 1.8K, qbo_reports 36, zabbix_wan_hosts 75, zabbix_events 231.