wulf-pulse/LCI_FEATURE_DESIGN.md

# Lifecycle Insights Feature Replacement — Pulse Design Doc

> **Purpose**: Document how Lifecycle Insights (LCI) works across the four target features — Warranty, Assessments, Budget Forecast, and QBR/Business Reviews — so we can build equivalent functionality in Pulse, using Autotask (and Datto RMM) as the primary data source.
>
> **Reference client**: Hynes Industries (321 assets, multiple locations)
> **Source tool**: app.lifecycleinsights.io
> **Date captured**: 2026-03-16

---

## 1. Asset List & Warranty

### What LCI Shows

**Summary banner** (top of Asset List page):
- Total Assets: 321
- **EOL Status** breakdown:
  - 104 Good Standing
  - 60 Approaching EOL
  - 6 Past EOL
  - 151 Unknown
- **OS Status** breakdown:
  - 177 Supported
  - 27 Not Supported
  - 117 Unknown
- **Warranty Status** breakdown:
  - 91 Under Warranty
  - 20 Approaching Warranty Expiration (within ~90 days)
  - 97 Expired Warranty
  - 113 Unknown

**Per-asset table columns**:

| Column | Example |
|--------|---------|
| Device Name | YNGHYNLT030 |
| Status | Active / Retired / Hidden |
| Last User (RMM) | HYNES\shopfloor |
| Location | Youngstown (Main) / Painesville (Branch) / Kokomo (Branch) |
| Type | Workstation / Hypervisor Host / Network Device |
| Make | Dell / Lenovo |
| Model | Latitude 5591 / ThinkSystem SR630 |
| Serial Number | 2KNXWT2 |
| Operating System | Microsoft Windows 10 Pro 10.0.19044 |
| Purchase Date | 04-23-2019 |
| Warranty Expiration | 04-24-2023 |
| End of Life | 04-23-2024 |
| Replacement Cost | $3,000 |
| Age (years) | 6.9 |

**Table features**:
- Sortable columns (click header)
- Filter buttons: Show Only NON Hidden / Duplicate / Hidden / All Assets
- Duplicate detection badge
- Pagination (321 rows, 25/50/75/100/200 per page)
- Search box

**Warranty status logic** (inferred):
- **Under Warranty**: warranty expiration date > today
- **Approaching**: warranty expiration within ~90 days
- **Expired**: warranty expiration date < today
- **Unknown**: no warranty expiration date on record

**EOL logic** (inferred):
- EOL date is OS/hardware specific (LCI maintains a lookup table — shown on dashboard)
- **Good Standing**: EOL date > today + threshold (~1 year)
- **Approaching EOL**: EOL date within ~1 year
- **Past EOL**: EOL date < today
- **Unknown**: no EOL date derivable

### Data Sources
- **Device inventory**: comes from RMM (Datto RMM in Hynes's case) — device name, make, model, serial, OS, last user, location
- **Purchase/warranty dates**: can come from RMM or be manually entered
- **EOL dates**: LCI maintains a global EOL lookup table by OS/software version (visible on dashboard — e.g., "Windows 10 Pro: 10-14-2025")
- **Replacement cost**: manually set per asset type, or inherited from category defaults
- **Locations**: mapped from RMM site groupings

### Pulse Implementation Notes

**Data model** (new migration needed):
```sql
-- assets table (core)
asset_id, autotask_id, datto_rmm_uid,
device_name, status, last_user,
location_id, asset_type,
make, model, serial_number,
os_name, os_version,
purchase_date, warranty_expiry_date, eol_date,
replacement_cost, age_years,
company_id (FK → companies),
is_hidden, is_duplicate,
created_at, updated_at, last_synced_at

-- eol_reference table (global)
software_name, eol_date

-- asset_locations table
location_id, company_id, location_name
```

**Warranty status computed field**:
```ts
function warrantyStatus(expiryDate: Date | null): 'under' | 'approaching' | 'expired' | 'unknown' {
  if (!expiryDate) return 'unknown';
  const daysUntil = differenceInDays(expiryDate, new Date());
  if (daysUntil < 0) return 'expired';
  if (daysUntil <= 90) return 'approaching';
  return 'under';
}
```

**Autotask as source**: Autotask has a Configuration Items module that maps to devices. Datto RMM syncs device details into Autotask CIs. Fields available via Autotask API: `warrantyExpirationDate`, `purchaseOrderNumber`, `purchaseDate`, `installDate`, `serialNumber`, `referenceNumber`, manufacturer, product (model), location, deviceType.

---

## 2. Budget Forecast

### What LCI Shows

**Purpose**: Project technology spend for the next 6–8 quarters, broken into categories.

**Views**:
1. **Detail View** — collapsible sections per time period, category breakdown
2. **Spreadsheet View** — bar chart + grid with rows per category, columns per quarter
3. **Budget Board** — (not fully captured; likely a Kanban/card layout)

**Filters available**:
- Location (site filter)
- Asset Type
- Asset Status
- "Generate Budget Forecast as of" date (defaults to today; can run future projections)

**Time buckets**:
- Overdue (past-due items)
- Recommendations Not Scheduled (approved recommendations with no date)
- Then rolling quarters: e.g., Jan-Mar 2026, Apr-Jun 2026, Jul-Sep 2026, etc. (6–8 quarters shown)

**Categories**:
| Category | What it represents |
|----------|--------------------|
| Workstation | Assets reaching EOL scheduled for replacement |
| Hypervisor Host | Server/hypervisor assets reaching EOL |
| Network Device | Network hardware replacements |
| Contracts/Subscriptions | Contract renewals (from Autotask contracts) |
| Recommendations | Open recommendations with cost + target date |

**Hynes Industries example data** (Spreadsheet View):

| Category | Overdue | Not Sched | Apr-Jun 26 | Jul-Sep 26 | Oct-Dec 26 | Jan-Mar 27 | Apr-Jun 27 | Total |
|----------|---------|-----------|------------|------------|------------|------------|------------|-------|
| Hypervisor Host | $22,500 | — | — | — | — | — | — | $22,500 |
| Workstation | — | — | — | — | — | $18,000 | $21,000 | $39,000 |
| Contracts/Subscriptions | — | — | $37,231 | $43,590 | $164,414 | $27,737 | $37,231 | $313,549 |
| Recommendations | $254,754 | $107,000 | $31,245 | $15,045 | $29,795 | — | — | $437,839 |
| **TOTAL** | **$277,254** | **$107,000** | **$68,476** | **$58,635** | **$194,209** | **$45,737** | **$58,231** | **$812,888** |

### How Each Category is Populated

**Workstation / Hypervisor / Network Device**:
- Source: assets with EOL date falling within the quarter
- Cost: `replacement_cost` per asset
- Scheduled quarter = quarter containing the asset's `eol_date`

**Contracts/Subscriptions**:
- Source: Autotask Contracts with `end_date` or renewal dates
- Cost: contract value
- Quarter = quarter of contract end/renewal date

**Recommendations**:
- Source: open Recommendations (see Recommendations module)
- Cost: `estimated_cost` on the recommendation
- Quarter = recommendation's `planned_date` (or "Not Scheduled" if no date)
- "Overdue" = planned_date < today

### Pulse Implementation Notes

**Budget Forecast is computed at query time** — no separate stored table needed beyond assets + contracts + recommendations with dates and costs.

```sql
-- Pseudo-query for budget forecast
SELECT
  quarter_bucket,
  category,
  COUNT(*) AS quantity,
  SUM(cost) AS budget_cost
FROM (
  -- EOL assets
  SELECT date_trunc('quarter', eol_date) AS quarter_bucket,
         asset_type AS category,
         replacement_cost AS cost
  FROM assets WHERE company_id = $1 AND eol_date IS NOT NULL AND status = 'active'

  UNION ALL

  -- Contracts
  SELECT date_trunc('quarter', end_date), 'Contracts/Subscriptions', contract_value
  FROM contracts WHERE company_id = $1

  UNION ALL

  -- Recommendations
  SELECT date_trunc('quarter', COALESCE(planned_date, NULL)), 'Recommendations', estimated_cost
  FROM recommendations WHERE company_id = $1 AND status NOT IN ('Completed', 'Declined')
) combined
GROUP BY quarter_bucket, category
ORDER BY quarter_bucket;
```

**API route**: `GET /api/budget-forecast/[companyId]?asOf=DATE&location=X&assetType=Y`

---

## 3. Assessments

### What LCI Shows

**Structure** (from Assessments Menu page):

1. **Assessment Events** — each time you run an assessment for a client = one "event"
2. **Assessment Templates** — define *what* you're measuring (items + categories)
3. **Assessment Items** — individual questions/checklist items
4. **Event Types** — classify events (e.g., baseline, annual review, gap assessment)
5. **Analytics by Customer** — score history, trends, category breakdowns
6. **Analytics All Customers** — cross-client comparison

**Wulf Consulting uses**: "Wulf Consulting: Default Assessment" template

**Hynes Industries history** (from dashboard):
- Latest: completed 01-29-2025 (411 days ago) by Jake Hammel
- Score type: "baseline assessment with a score of --" (baseline = no numeric score, just captures state)
- Assessment Event Score History chart shows one data point at 2025-01

**Data Quality Scores** (dashboard, related to assessments):
- EOL: 53/100
- Warranty: 65/100
- Make: 92/100
- Serial: 83/100
- Model: 95/100
- Overall: 67/100

### Assessment Data Model (inferred)

```
AssessmentTemplate
  ├── id, name, company_id (MSP-level)
  ├── items[] → AssessmentItem
  │     ├── id, category, question_text, weight, scoring_type
  │     └── possible_scores: [0,1,2,3,4,5] or pass/fail
  └── event_type_config

AssessmentEvent
  ├── id, template_id, client_id (company)
  ├── event_type (baseline / annual / gap)
  ├── completed_date, completed_by (technician)
  ├── overall_score (numeric or null for baseline)
  └── responses[] → AssessmentResponse
        ├── item_id, score, notes
        └── linked_recommendation_id (optional)

AssessmentEventScore (for history/chart)
  ├── event_id, category, score, max_score
```

**Key behaviors**:
- Templates are MSP-defined (not per-client)
- One client can have many events over time (score history chart)
- Assessment items can generate Recommendations (linked)
- Scores tracked by category (enables radar/bar chart per category)
- "Baseline" events don't produce a numeric score — they capture current state only

### Pulse Implementation Notes

**Migration**: Create `assessment_templates`, `assessment_items`, `assessment_events`, `assessment_responses` tables.

**Admin UI**: Template builder (drag-drop items into categories), Event runner (per-client form), Analytics view (score history chart per client).

**Autotask link**: Assessment items that result in action → create Autotask ticket or recommendation.

---

## 4. Business Reviews / QBR

### What LCI Shows — FULLY CAPTURED

**Page**: `Business Review Meetings for Hynes Industries`

**Meeting table columns**:
| Column | Values |
|--------|--------|
| Meeting Date | e.g., 03-03-2026 |
| Meeting Type | Business Review / Virtual Meeting / (blank) |
| Primary Contact | Ryan Day |
| Account Manager | Jake Hammel |
| Status | Completed / Scheduled / Auto-Scheduled / Preparation / Skipped/Cxl |
| Notes | Rich text notes (Pre-Meeting / During Meeting / Post-Meeting sections) |
| Recommendations | (linked recommendations column) |
| Reports / Artifacts | (PDF download column) |
| Delete | delete action |

**Status types explained**:
- **Completed** — meeting happened and notes are finalized
- **Scheduled** — manually scheduled upcoming meeting
- **Auto-Scheduled** — system automatically created this slot based on cadence
- **Preparation** — notes are being drafted before meeting
- **Skipped/Cxl** — meeting was skipped or cancelled

**Cadence for Hynes**: Bi-weekly (every 2 weeks), Ryan Day as primary contact, Jake Hammel as account manager. Running since September 2023.

**Notes structure** (rich text, per meeting):
```
Pre-Meeting:
  [prep notes, project status, talking points]

Meeting / During Meeting:
  [live notes taken during call]

Post-Meeting:
  [follow-up actions, decisions made]
```

**Real example notes** (from Hynes 09-19-2023):
> "Follow up with Ryan on 2024 CAPX Budget... Updated Ryan that moving forward new workstations won't have the BitLocker Password Prompt... Amy Young - Executive Admin - Get her to view details on the Hynes conference room calendars..."

**Scale**: Hynes has ~56 business review records from 09-2023 to 03-2026 (bi-weekly cadence = ~2/month × 30 months). The "47 overdue" from the dashboard is because many Auto-Scheduled meetings were never formally completed.

**Filters on page**:
- "Show Meetings with Date in Past" toggle

**Actions**:
- ADD NEW — create a new meeting record
- Each row has: delete, reports/artifacts (PDF generation)

### Business Reviews Data Model (confirmed)

```sql
business_reviews
  id, company_id,
  meeting_date DATE,
  meeting_type VARCHAR,           -- 'Business Review', 'Virtual Meeting', etc.
  primary_contact_id INT,         -- FK → contacts
  account_manager_id INT,         -- FK → users (MSP staff)
  status VARCHAR,                 -- Completed/Scheduled/Auto-Scheduled/Preparation/Skipped-Cxl
  notes_pre_meeting TEXT,
  notes_during_meeting TEXT,
  notes_post_meeting TEXT,
  created_at, updated_at

business_review_artifacts          -- linked PDFs / reports
  id, business_review_id, artifact_type, file_url, created_at

business_review_recommendations    -- join table
  business_review_id, recommendation_id
```

**Key behaviors**:
- System auto-generates meeting slots based on cadence (configurable per client)
- Each meeting has pre/during/post note sections
- Meetings can link to specific recommendations (show progress at meeting)
- PDF reports can be attached or generated per meeting
- "47 overdue" = count of Auto-Scheduled meetings with date in past and no Completed status
- Agenda links (from dashboard widget) are separate from meeting notes — persistent links per client

### Pulse Implementation Notes

**QBR = structured meeting log** with recurring cadence + notes + linked recommendations.

- Auto-generate meeting slots via a cron job based on `meeting_cadence_days` per company
- The "Overdue" count = `SELECT COUNT(*) FROM business_reviews WHERE company_id = $1 AND meeting_date < NOW() AND status != 'Completed'`
- PDF generation: render current EOL health + budget + recommendations at time of export
- Agenda links widget: simple per-client URL bookmarks (`business_review_links` table)

---

## 5. Recommendations — FULLY CAPTURED

### What LCI Shows

**Page**: `Recommendations for Hynes Industries` — 26 total records

**Table columns**:
| Column | Notes |
|--------|-------|
| Name | Free-text project/recommendation name |
| Location | Youngstown (Main) / Painesville (Branch) / Kokomo (Branch) / blank (all sites) |
| Start | Target start date (optional) |
| End | Target end date (optional) |
| Status | New / Proposed / Planned / In Progress / On Hold / Completed / Declined |
| Tags | (free-form tags) |
| Priority | Low / Medium / High |
| Description | Rich text description of scope |
| Labor Cost | $ |
| Materials Cost | $ |
| Replacement Cost | $ |
| Recurring Cost | Monthly $X → shown as "Monthly / $X" |
| Total Cost | Sum of all cost components |
| Delete | delete action |

**Filters**:
- Search box
- Include Completed toggle
- Include Declined toggle
- "SHOW AUTOTASK OPPS" button — links to Autotask opportunities

**Views**: List (default) — table view

**Hynes Industries full recommendation list** (real data):

| Name | Status | Priority | Total Cost |
|------|--------|----------|------------|
| 2024 - Fish Bowl Renovation | Completed | Medium | $11,000 |
| 2024 - Pepwave Licensing Renewal | Completed | Medium | $0 |
| 2024 - Planned Multiple Switch Lifecycle | Completed | Medium | $24,000 |
| 2024 - Planned Workstation Lifecycle | In Progress | Medium | $9,800 |
| 2024 - VLAN Cleanup | Completed | Medium | $10,000 |
| 2024: O365 Backup | Completed | Medium | $3,560 (monthly) |
| 2024: Password Management | Completed | Medium | $8,340 (monthly) |
| 2024: SEIM Tool | Completed | High | $20,020 (monthly) |
| 2024: ZTNA - Phase 1 | Completed | High | $16,350 (monthly) |
| 2025 - Painesville Zoom Room | Declined | Low | $11,000 |
| 2025 - Planned Infrastructure Lifecycle | Completed | High | $6,830 (monthly) |
| 2025 - Planned Workstation Lifecycle | Completed | High | $3,000 |
| 2025 - PV Planned Multiple Switch Lifecycle | In Progress | High | $5,000 |
| 2025 - YNG Planned Multiple Switch Lifecycle | Completed | High | $2,900 |
| 2025: Windows 10 to 11 Upgrade | Completed | High | $40,400 |
| 2026 - Exterior Cameras - Partial Build | On Hold | Low | $57,000 |
| 2026 - PEN Test | On Hold | High | $20,000 |
| 2026 - Pepwave Licensing Renewal | Planned | High | $350 |
| 2026 - Planned Host/SAN Lifecycle | Proposed | High | $120,000 |
| 2026 - Planned Multiple Switch Lifecycle | Proposed | High | $31,000 |
| 2026 - Planned Workstation Budget | Proposed | Medium | $86,600 |
| 2026 - Zebra Printers Lifecycle | Proposed | High | $80,534 (monthly) |
| 2026: ZTNA: Phase 2 | On Hold | High | $30,000 |
| Auvik SaaS | Declined | Medium | $1,760 (monthly) |
| Physical Switch & IDF Cleanup | On Hold | Medium | $0 |
| Receptionist Hardware | Completed | Medium | $1,800 |

**How recommendations feed Budget Forecast**:
- Status `Proposed`, `Planned`, `In Progress`, `On Hold` → included in budget forecast
- `start` date (or `end` date) → determines which quarter they appear in
- No date → "Not Scheduled" bucket
- Past date → "Overdue" bucket
- `Completed` / `Declined` → excluded from forecast

### Recommendations Data Model

```sql
recommendations
  id, company_id,
  name VARCHAR,
  location_id INT (nullable),
  start_date DATE (nullable),
  end_date DATE (nullable),
  status VARCHAR,              -- New/Proposed/Planned/In Progress/On Hold/Completed/Declined
  tags TEXT[],
  priority VARCHAR,            -- Low/Medium/High
  description TEXT,
  labor_cost DECIMAL,
  materials_cost DECIMAL,
  replacement_cost DECIMAL,
  recurring_cost DECIMAL,      -- monthly amount
  recurring_period VARCHAR,    -- 'Monthly'
  total_cost DECIMAL,          -- computed or stored
  autotask_opportunity_id INT (nullable),
  created_at, updated_at

recommendation_assets          -- assets linked to this recommendation
  recommendation_id, asset_id
```

### Pulse Implementation Notes

- Recommendations are **Pulse-native** (not pulled from Autotask) but can push/link to Autotask Opportunities
- `total_cost = labor_cost + materials_cost + replacement_cost + (recurring_cost × months_in_period)`
- Budget Forecast query filters: `status NOT IN ('Completed', 'Declined')`
- Dashboard widget shows count + total cost grouped by status
- The `SHOW AUTOTASK OPPS` button = link to Autotask opportunities filtered to this company

---

## 6. Dashboard Summary

The LCI dashboard aggregates all four features for a client into one view:

| Widget | Data |
|--------|------|
| CSAT Score | From Crewhu integration (external) |
| Budget Forecast chart | 6-quarter bar chart |
| EOL Health | % OK / Past EOL / Near EOL donut |
| Data Quality Score | Per-field completeness scores |
| Microsoft License Summary | From M365 / CSP data |
| EOL Replacement Cost | Bar chart by status |
| Latest Assessment | Score + date |
| Recommendation Status | Count + cost per status |
| Assessment Score History | Line/bar chart |
| Software/OS EOL Table | Global reference table |
| Tech Debt by Asset Type | Stacked bar |
| Missing Data Fields | Counts of missing fields |
| Total Technology Debt | $ value of overdue/EOL assets |
| Headlines | Alerts: overdue contracts, overdue QBRs, latest assessment age |

---

## 6. Integrations & Data Flow

```
Datto RMM ──────────────────┐
                             ▼
Autotask CIs ───────────► LCI Asset Sync
                             │
Autotask Contracts ──────────┤
                             │
Autotask Recommendations ────┤
  (or LCI-native)            │
                             ▼
                    LCI PostgreSQL
                             │
                    ┌────────┼────────┐
                    ▼        ▼        ▼
               Budget    Assessments  QBR
               Forecast   Events     Reports
```

**For Pulse**:
- Assets: Autotask Configuration Items API → `assets` table
- Contracts: Autotask Contracts API → `contracts` table (already partially done)
- Recommendations: Either Autotask Opportunities/Quotes or Pulse-native table
- CSAT: Not currently integrated (Crewhoo is external) — can be skipped initially
- EOL Reference: Static lookup table (maintained manually or seeded from LCI's public list)
- Warranty dates: Come from Autotask CI `warrantyExpirationDate` field

---

## 7. Build Priority Order

1. **Asset List + Warranty** — highest value, data already in Autotask CIs
2. **Budget Forecast** — derived from assets + contracts (both already syncing)
3. **QBR / Business Reviews** — report generation + scheduling
4. **Assessments** — most complex, requires template builder + event runner

---

## 9. Pages Still to Explore

**Fully captured**: Asset List, Budget Forecast (detail + spreadsheet), Recommendations (all 26), Business Reviews (all ~56 meetings), Dashboard.

**Still needed for complete picture**:
- `app.lifecycleinsights.io/assessmentevents` — see an actual scored assessment event (items, categories, scores per item). The Hynes event from 01-29-2025 was a baseline with no numeric score.
- `app.lifecycleinsights.io/assessmentanalytics` — analytics/trends view per client
- `app.lifecycleinsights.io/managetemplates` — what a template looks like with items + categories
- MDS client — verify data model generalizes (different asset mix, different contact structure)

These can be explored via Claude_in_Chrome (already connected to local Chrome where you're logged in) without needing another MFA login.