wulf-pulse/tasks/prd-duo-integration.md

# PRD: Duo Security Integration — Data Sync & Storage

## 1. Introduction / Overview

Pulse currently has no visibility into Duo Security 2FA data across managed clients. Wulf Consulting manages 32 child Duo accounts via the MSP parent account. This feature adds a full Duo data sync — pulling account inventory, users, phones/devices, authentication logs, groups, and integrations from every child account into Pulse's PostgreSQL database.

**Key architectural insight:** The parent Duo **Accounts API** credentials (`DUOACCOUNTS_*`) can be used to call **Admin API** endpoints on any child account by signing requests against the child's `api_hostname` and passing `account_id`. No per-child Admin API keys are needed.

### Problem Statement

- No centralized view of which clients have Duo deployed, how many users are enrolled, or which users lack 2FA.
- No way to audit 2FA adoption, device health, or authentication anomalies without logging into 32 separate Duo admin panels.
- Security compliance reporting requires manual data gathering across all child accounts.

---

## 2. Goals

| # | Goal | Measurable Outcome |
|---|------|--------------------|
| G1 | Sync all 32 child accounts and their metadata | `duo_accounts` table contains all child accounts with `account_id`, `api_hostname`, `name` |
| G2 | Sync users from every child account | `duo_users` table contains all users across all children, with enrollment status, last login, status |
| G3 | Sync 2FA devices (phones) per user | `duo_phones` table stores device model, OS, platform, activation status, last seen |
| G4 | Sync authentication logs | `duo_auth_logs` table stores auth events with result, device, location, application |
| G5 | Sync groups and integrations per child | `duo_groups` and `duo_integrations` tables |
| G6 | Map Duo child accounts to Autotask companies | `duo_accounts.autotask_company_id` FK to `companies.id` for cross-referencing |
| G7 | Expose sync via OpenClaw API + internal API route | Sync can be triggered on-demand or scheduled |
| G8 | Admin API (parent account) data synced separately | `duo_admin_users` or Wulf parent account users/summary stored alongside child data |

---

## 3. User Stories

- **As an MSP admin**, I want to see which clients have Duo deployed and how many users are enrolled, so I can identify clients with low 2FA adoption.
- **As a security analyst**, I want to query Duo authentication logs across all clients, so I can detect anomalies (failed auths, new device enrollments, locked-out users).
- **As an account manager**, I want to see per-client Duo user counts and enrollment rates, so I can include them in QBR reports.
- **As the NOC**, I want to see which Duo users have "bypass" or "disabled" status, so I can flag security risks.
- **As a developer**, I want Duo data queryable via SQL alongside Autotask, Datto RMM, and SentinelOne data, so I can build cross-platform dashboards.

---

## 4. Functional Requirements

### 4.1 Duo API Client (`lib/services/duo-client.ts`)

| # | Requirement |
|---|-------------|
| FR-1 | Create a `DuoClient` class that handles HMAC-SHA1 request signing per Duo's auth spec. |
| FR-2 | Support both GET and POST methods with URL-encoded parameters. |
| FR-3 | Accept `ikey`, `skey`, and `host` in constructor. For child account calls, accept an override `host` + `account_id` parameter. |
| FR-4 | Implement automatic pagination — Duo APIs return `metadata.next_offset`; the client must follow pages until all records are retrieved. |
| FR-5 | Implement rate-limit handling — Duo returns `429` with `Retry-After` header; client must respect it. |
| FR-6 | All API calls must have a configurable timeout (default 30s). |

### 4.2 Accounts API Methods

| # | Requirement |
|---|-------------|
| FR-7 | `listAccounts()` — calls `POST /accounts/v1/account/list` using parent creds to retrieve all child accounts (`account_id`, `api_hostname`, `name`). |
| FR-8 | No create/delete operations — read-only sync. |

### 4.3 Admin API Methods (per child account)

All Admin API calls use the **parent Accounts API credentials** signed against the **child's `api_hostname`** with `account_id` in the params.

| # | Requirement |
|---|-------------|
| FR-9 | `getAccountSummary(child)` — `GET /admin/v1/info/summary` — returns user_count, integration_count, telephony_credits. |
| FR-10 | `getUsers(child)` — `GET /admin/v1/users` — paginate all users. Returns username, email, status, is_enrolled, last_login, phones, groups, created, etc. |
| FR-11 | `getPhones(child)` — `GET /admin/v1/phones` — paginate all phones/devices. Returns model, platform, OS, activated, last_seen, capabilities, associated users. |
| FR-12 | `getGroups(child)` — `GET /admin/v1/groups` — all groups with member counts. |
| FR-13 | `getIntegrations(child)` — `GET /admin/v1/integrations` — all applications/integrations (name, type, enabled). |
| FR-14 | `getAuthLogs(child, mintime)` — `GET /admin/v2/logs/authentication` — auth events since `mintime`. Returns access_device, auth_device, result, reason, user, application, timestamp, location. |
| FR-15 | `getAdminLogs(child, mintime)` — `GET /admin/v1/logs/administrator` — admin activity events (optional/stretch). |

### 4.4 Admin API Methods (parent Wulf account)

| # | Requirement |
|---|-------------|
| FR-16 | Use the separate `DUOADMIN_*` credentials for the Wulf parent account. |
| FR-17 | Sync users, phones, groups, integrations, and summary from the parent account using the same Admin API methods. |
| FR-18 | Store parent account data in the same tables with a sentinel `duo_account_id` (e.g., the parent's own account or a well-known marker). |

### 4.5 Database Tables (Migration)

#### `duo_accounts`
Stores child account metadata from the Accounts API, plus the Wulf parent account.

| Column | Type | Description |
|--------|------|-------------|
| `id` | SERIAL PK | Internal ID |
| `account_id` | VARCHAR(20) UNIQUE | Duo account ID (e.g., `DAS7Y6UP7EQHTHTAI9R0`) |
| `name` | VARCHAR(255) | Account name |
| `api_hostname` | VARCHAR(255) | Per-account API hostname |
| `autotask_company_id` | BIGINT FK → companies(id) | Matched Autotask company (nullable) |
| `user_count` | INTEGER | Last synced user count |
| `integration_count` | INTEGER | Last synced integration count |
| `edition` | VARCHAR(50) | Duo edition (PERSONAL/ENTERPRISE/PLATFORM/BEYOND) if available |
| `is_parent` | BOOLEAN DEFAULT false | True for the Wulf parent account row |
| `synced_at` | TIMESTAMP | Last sync time |
| `created_at` | TIMESTAMP DEFAULT NOW() | |

#### `duo_users`
All Duo users across all accounts.

| Column | Type | Description |
|--------|------|-------------|
| `id` | SERIAL PK | Internal ID |
| `user_id` | VARCHAR(30) UNIQUE | Duo user ID (e.g., `DULLBCPMRNNORZW1NKBF`) |
| `duo_account_id` | VARCHAR(20) FK → duo_accounts(account_id) | Which child account |
| `username` | VARCHAR(255) | |
| `email` | VARCHAR(255) | |
| `realname` | VARCHAR(255) | |
| `status` | VARCHAR(50) | active, bypass, disabled, locked_out, pending_deletion |
| `is_enrolled` | BOOLEAN | |
| `last_login` | TIMESTAMP | |
| `last_directory_sync` | TIMESTAMP | |
| `created` | TIMESTAMP | Duo creation timestamp |
| `notes` | TEXT | |
| `phones_count` | INTEGER | Number of associated phones |
| `groups` | JSONB | Array of group names/IDs |
| `aliases` | JSONB | Alias fields |
| `enable_auto_prompt` | BOOLEAN | |
| `synced_at` | TIMESTAMP | |

#### `duo_phones`
2FA devices (phones, hardware tokens, etc.).

| Column | Type | Description |
|--------|------|-------------|
| `id` | SERIAL PK | Internal ID |
| `phone_id` | VARCHAR(30) UNIQUE | Duo phone ID |
| `duo_account_id` | VARCHAR(20) FK → duo_accounts(account_id) | |
| `name` | VARCHAR(255) | Device name |
| `number` | VARCHAR(50) | Phone number |
| `type` | VARCHAR(50) | Mobile, Landline, etc. |
| `platform` | VARCHAR(50) | Google Android, Apple iOS, etc. |
| `model` | VARCHAR(255) | Device model |
| `os_version` | VARCHAR(50) | |
| `app_version` | VARCHAR(50) | Duo Mobile app version |
| `activated` | BOOLEAN | |
| `last_seen` | TIMESTAMP | |
| `capabilities` | JSONB | Array: auto, push, sms, phone, mobile_otp |
| `users` | JSONB | Array of associated user_ids |
| `synced_at` | TIMESTAMP | |

#### `duo_auth_logs`
Authentication events.

| Column | Type | Description |
|--------|------|-------------|
| `id` | SERIAL PK | Internal ID |
| `txid` | VARCHAR(50) UNIQUE | Duo transaction ID |
| `duo_account_id` | VARCHAR(20) FK → duo_accounts(account_id) | |
| `timestamp` | TIMESTAMP | Event time |
| `user_name` | VARCHAR(255) | |
| `user_id` | VARCHAR(30) | Duo user ID |
| `factor` | VARCHAR(50) | push, phone, sms, passcode, etc. |
| `result` | VARCHAR(50) | success, denied, fraud, locked_out |
| `reason` | VARCHAR(255) | Detailed reason |
| `application_name` | VARCHAR(255) | |
| `application_key` | VARCHAR(50) | |
| `access_device_ip` | INET | |
| `access_device_location` | JSONB | {city, state, country} |
| `auth_device_ip` | INET | |
| `auth_device_name` | VARCHAR(255) | |
| `event_type` | VARCHAR(50) | authentication, enrollment |
| `synced_at` | TIMESTAMP | |

#### `duo_groups`
Groups per account.

| Column | Type | Description |
|--------|------|-------------|
| `id` | SERIAL PK | Internal ID |
| `group_id` | VARCHAR(30) UNIQUE | Duo group ID |
| `duo_account_id` | VARCHAR(20) FK → duo_accounts(account_id) | |
| `name` | VARCHAR(255) | |
| `description` | TEXT | |
| `member_count` | INTEGER | |
| `status` | VARCHAR(50) | |
| `synced_at` | TIMESTAMP | |

#### `duo_integrations`
Applications/integrations per account.

| Column | Type | Description |
|--------|------|-------------|
| `id` | SERIAL PK | Internal ID |
| `integration_key` | VARCHAR(50) UNIQUE | Duo integration key |
| `duo_account_id` | VARCHAR(20) FK → duo_accounts(account_id) | |
| `name` | VARCHAR(255) | |
| `type` | VARCHAR(100) | azure-ca, rdp, websdk, etc. |
| `enabled` | BOOLEAN | |
| `notes` | TEXT | |
| `synced_at` | TIMESTAMP | |

### 4.6 Sync Service (`lib/services/duo-sync-service.ts`)

| # | Requirement |
|---|-------------|
| FR-19 | `syncAll()` — Full sync: (1) list child accounts, (2) for each child sync users, phones, groups, integrations, auth logs. Also sync parent account. |
| FR-20 | `syncAccounts()` — Sync child account list only. |
| FR-21 | `syncAccountData(accountId)` — Sync all data for a single child account. |
| FR-22 | `syncAuthLogs(accountId?, since?)` — Incremental auth log sync. Default `since` = last synced `timestamp` in `duo_auth_logs` for that account. |
| FR-23 | All sync operations use upsert (INSERT ... ON CONFLICT DO UPDATE) to handle re-syncs cleanly. |
| FR-24 | Sync should be non-blocking (fire-and-forget with sync ID tracking, same pattern as Autotask sync). |
| FR-25 | Sync should process child accounts sequentially (not in parallel) to respect Duo rate limits. |
| FR-26 | Log sync progress and errors. Track per-account sync stats (records added/updated). |

### 4.7 Company Matching

| # | Requirement |
|---|-------------|
| FR-27 | After syncing child accounts, attempt to match each `duo_accounts.name` to `companies.company_name` using fuzzy/exact matching. |
| FR-28 | Store the match as `duo_accounts.autotask_company_id`. Allow manual override. |
| FR-29 | Matching logic: exact match first, then case-insensitive containment, then skip (leave null for manual mapping). |

### 4.8 API Routes

| # | Route | Method | Description |
|---|-------|--------|-------------|
| FR-30 | `/api/duo/sync` | POST | Trigger full Duo sync (requires auth). |
| FR-31 | `/api/duo/sync` | GET | Return sync status (in progress, last sync time). |
| FR-32 | `/api/duo/accounts` | GET | List all Duo child accounts with stats. |
| FR-33 | `/api/duo/accounts/[id]/users` | GET | List users for a specific account. |
| FR-34 | `/api/openclaw/sync/duo` | POST | OpenClaw trigger for Duo sync (API key auth). |

### 4.9 Environment Variables

Already configured in `.env.local`:

```
DUOACCOUNTS_INTEGRATION_KEY=DIS1RH7M3TQ69QTD0GLW
DUOACCOUNTS_SECRET_KEY=xYlwR5aBby7HtAFV2UdLFpLJ24kONrDbr85DLzek
DUOACCOUNTS_API_HOSTNAME=api-98372575.duosecurity.com

DUOADMIN_INTEGRATION_KEY=DI8DKH1EK4JJJKGJNV01
DUOADMIN_SECRET_KEY=o4sE0E7PcKXdl1K2nwPH1ixD9XHZyce2V1JmLqJH
DUOADMIN_API_HOSTNAME=api-98372575.duosecurity.com
```

---

## 5. Non-Goals (Out of Scope)

- **Write operations** — No creating/deleting Duo users, accounts, or devices via Pulse. Read-only sync.
- **Real-time webhooks** — Duo does not support push webhooks; this is poll-based sync only.
- **Duo Auth API (2FA verification)** — We are not performing 2FA challenges, only reading admin data.
- **UI dashboards** — This PRD covers data sync and storage only. Dashboard/reporting UI is a separate feature.
- **Admin log sync** — Administrator activity logs (`/admin/v1/logs/administrator`) are a stretch goal, not required for v1.

---

## 6. Design Considerations

- Follow existing Pulse patterns: sync service class, entity upsert, OpenClaw route, background execution.
- Duo API rate limits: 20 requests/second per account. Sequential child processing with small delays should stay well under limits.
- Auth logs can be large — use incremental sync (`mintime` = last synced timestamp) to avoid re-pulling history.
- Auth log `timestamp` is milliseconds since epoch — convert to PostgreSQL `TIMESTAMP` on insert.

---

## 7. Technical Considerations

### Dependencies
- No new npm packages required — HMAC-SHA1 signing uses Node.js built-in `crypto` module, HTTPS via built-in `https`.
- PostgreSQL `INET` type for IP storage (native, no extension needed).

### Duo API Auth Pattern
All requests signed with HMAC-SHA1:
```
canon = date + "\n" + method + "\n" + host + "\n" + path + "\n" + sorted_params
sig = HMAC-SHA1(skey, canon)
header = "Basic " + base64(ikey + ":" + sig)
```

### Child Account Access Pattern
- Use **parent Accounts API creds** (`DUOACCOUNTS_*`).
- Sign against **child's `api_hostname`** (not parent's).
- Pass `account_id` as a query/body parameter.
- No per-child credentials needed.

### Parent Account Access Pattern
- Use **Admin API creds** (`DUOADMIN_*`) for the Wulf parent account directly.
- No `account_id` parameter needed.

### Migration Number
- Next available: `058_create_duo_tables.sql`

### Existing Patterns to Follow
- `lib/services/sentinelone-sync-service.ts` — similar external API sync pattern
- `lib/services/entity-sync.ts` — upsert pattern, sync stats tracking
- `app/api/openclaw/sync/sentinelone/route.ts` — OpenClaw trigger route pattern

---

## 8. Success Metrics

| Metric | Target |
|--------|--------|
| All 32 child accounts synced | `duo_accounts` row count = 32 + 1 parent |
| All users across all accounts stored | `duo_users` count matches sum of `user_count` across accounts |
| Phones/devices stored | `duo_phones` populated for all accounts |
| Auth logs incrementally synced | `duo_auth_logs` grows with each sync, no duplicates (upsert on `txid`) |
| Company matching | >80% of `duo_accounts` matched to `companies` by name |
| Sync completes in <5 minutes | Full sync across 32 accounts finishes within timeout |
| OpenClaw can trigger sync | `POST /api/openclaw/sync/duo` returns 200 with syncId |

---

## 9. Open Questions

1. **Auth log retention** — How far back should we pull auth logs on initial sync? 30 days? 90 days? Duo retains up to 180 days.
2. **Sync schedule** — Daily is assumed. Should auth logs sync more frequently (e.g. every 6 hours) for near-real-time anomaly detection?
3. **Stale data cleanup** — If a child account is removed from Duo, should we soft-delete its data in Pulse? Or leave it as historical?
4. **Parent account Admin API users** — The parent has 25 users (Wulf internal). Should these be stored in the same `duo_users` table or kept separate?
5. **Company name matching** — Some Duo account names may not exactly match Autotask company names (e.g., "Terry's Plumbing & Heating" vs "Terry's Plumbing, Inc."). Should we provide a manual mapping UI, or is fuzzy matching sufficient for v1?

---

## Appendix: Verified API Access

Confirmed working via `scripts/test-duo.mjs` (March 27, 2026):

| API | Endpoint | Status | Data |
|-----|----------|--------|------|
| Accounts API | `POST /accounts/v1/account/list` | ✅ 200 | 32 child accounts |
| Admin API (child) | `GET /admin/v1/info/summary` | ✅ 200 | user_count, integration_count |
| Admin API (child) | `GET /admin/v1/users` | ✅ 200 | Full user objects with phones, groups |
| Admin API (child) | `GET /admin/v1/phones` | ✅ 200 | Device model, OS, last_seen, capabilities |
| Admin API (child) | `GET /admin/v2/logs/authentication` | ✅ 200 | Auth events with location, result, device |
| Admin API (child) | `GET /admin/v1/groups` | ✅ 200 | Groups with member counts |
| Admin API (child) | `GET /admin/v1/integrations` | ✅ 200 | Application name, type |
| Admin API (child) | `GET /admin/v1/webauthncredentials` | ✅ 200 | WebAuthn keys |
| Admin API (parent) | `GET /admin/v1/info/summary` | ✅ 200 | 25 users, 19 integrations |
| Admin API (parent) | `GET /admin/v1/users` | ✅ 200 | Full user list |