wulf-pulse/docs/wulf-pulse-ticket-analyzer-runbook.md

# AI Ticket Analyzer — Operator Runbook

How to keep the analyzer healthy in production. Audience is whoever is on
call for Pulse. The feature spec is in
`wulf-pulse-ticket-analyzer-prompt.md`; the implementation log is in
`wulf-pulse-ticket-analyzer-build-notes.md`.

---

## At a glance

- **Pipeline**: Haiku triage → (optional) IT Glue retrieval → Sonnet deep
  analysis → (optional) Opus deep reasoning → persistence.
- **Trigger**: user clicks "Analyze" on a ticket page; that POSTs to
  `/api/analyzer/tickets/:ticketNumber/analyze`. The route preprocesses
  inline, short-circuits to the cached row when content hasn't changed,
  otherwise queues a job for the worker.
- **Worker**: `analyzerWorker` polls `analyzer_jobs` every 2 s in-process.
  Auto-starts in production (`NODE_ENV=production`). Opt in for dev with
  `ANALYZER_WORKER_AUTOSTART=1`.
- **Cost ceiling**: hard-coded `COST_CEILING_USD = 2.00` in
  `lib/services/analyzer/pipeline.ts`. Trips before Opus only — Sonnet runs
  unconditionally. When tripped, the analysis is flagged
  `needs_human_review = true` with reason "cost ceiling reached".

---

## Required environment

Add to `~/projects_env/wulf-pulse.env` (do not commit values):

```
ANTHROPIC_API_KEY=
ITGLUE_API_KEY=
ITGLUE_API_BASE=https://api.itglue.com
ALLOWED_SHARE_DOMAINS=wulfconsulting.com
# Optional in dev:
ANALYZER_WORKER_AUTOSTART=1
```

`SMTP_*` and `BETTER_AUTH_URL` are already set elsewhere in Pulse — share
emails reuse the existing magic-link transport.

---

## First-time setup on an existing database

Migration `069_create_analyzer_tables.sql` creates `analyzer_analyses`,
`analyzer_shares`, and `analyzer_jobs`. Postgres only re-applies migrations
on a fresh data volume — for an existing DB, run the migration directly:

```bash
psql "$DATABASE_URL" -f migrations/069_create_analyzer_tables.sql
```

The script is idempotent (`IF NOT EXISTS` on every object).

---

## Monitoring cost

Every completed analysis stores `estimated_cost_usd`, `total_input_tokens`,
`total_output_tokens`, and per-stage tier flags
(`haiku_used` / `sonnet_used` / `opus_used`). The number is a forward
estimate from `lib/services/llm/pricing.ts` and will drift if Anthropic
changes published rates — re-check that file before believing big spend
numbers.

### Daily spend (last 14 days)

```sql
SELECT date_trunc('day', triggered_at) AS day,
       count(*) FILTER (WHERE status = 'complete')        AS analyses,
       count(*) FILTER (WHERE opus_used)                  AS opus_runs,
       count(*) FILTER (WHERE needs_human_review)         AS needs_review,
       round(sum(estimated_cost_usd)::numeric, 2)         AS spend_usd
  FROM analyzer_analyses
 WHERE triggered_at > now() - interval '14 days'
 GROUP BY 1
 ORDER BY 1 DESC;
```

### Top 20 most expensive analyses (last 30 days)

```sql
SELECT id, ticket_number, analysis_version, estimated_cost_usd,
       opus_used, needs_human_review, triggered_at
  FROM analyzer_analyses
 WHERE triggered_at > now() - interval '30 days'
 ORDER BY estimated_cost_usd DESC
 LIMIT 20;
```

### Heaviest tickets (multiple re-runs against the same ticket)

```sql
SELECT ticket_number,
       count(*)                              AS analyses,
       round(sum(estimated_cost_usd)::numeric, 2) AS spend_usd,
       max(triggered_at)                     AS last_run
  FROM analyzer_analyses
 GROUP BY 1
HAVING count(*) > 1
 ORDER BY spend_usd DESC
 LIMIT 20;
```

### Cost ceiling — what to do when it trips

`needs_human_review = true` with `human_review_reasons` containing "cost
ceiling reached" means Sonnet ran but Opus was skipped. The Sonnet output
is still persisted and is usually fine. To re-run with Opus forced on,
trigger a new analysis (the route accepts `force=true`); the cost ceiling
will reapply, so check the prior `estimated_cost_usd` before committing.
To raise the ceiling, edit `COST_CEILING_USD` in
`lib/services/analyzer/pipeline.ts` and redeploy.

To reduce spend without code changes: discourage operators from clicking
Re-analyze unless content has changed (the idempotency short-circuit
already prevents charged re-runs against the same `content_hash`).

---

## Adding IT Glue org aliases

Stage 2 resolves an Autotask company name to an IT Glue organization in
two steps:

1. **Alias map** — `lib/services/analyzer/itglue-aliases.json`. Keys are
   normalized company names (lowercase, single-spaced); values are IT Glue
   organization IDs as **strings**. Exact-match wins immediately.
2. **Live IT Glue search** — fallback when the alias map misses.

When the live search misses or returns the wrong org, add an alias.

### Finding the IT Glue organization ID

In the IT Glue UI, navigate to the organization. The numeric segment of
the URL (`/organizations/12345`) is the ID. Or via API:

```bash
curl -s -H "x-api-key: $ITGLUE_API_KEY" \
  "$ITGLUE_API_BASE/organizations?filter[name]=Seubert%20and%20Associates" \
  | jq '.data[] | {id, name: .attributes.name}'
```

### Updating the alias file

Edit `lib/services/analyzer/itglue-aliases.json`. Keys must be **already
normalized** — lowercase, single-spaced — because the matcher applies the
same normalization to the Autotask company name and looks up an exact key.

```json
{
  "seubert": "1234567",
  "seubert and associates": "1234567",
  "acme insurance group": "7654321"
}
```

Strip the `_comment` / `_example` keys when adding the first real entry,
or leave them — they're ignored at match time (no real org name normalizes
to a leading underscore).

Changes require a redeploy. The file is bundled into the build by Next's
`import` — there is no runtime reload.

### Verifying an alias works

After deploy, run an analysis on a ticket from that company. Check the
`model_traces.itglue` field on the resulting row:

```sql
SELECT model_traces->'itglue'
  FROM analyzer_analyses
 WHERE id = '<analysis-id>';
```

`alias_used: true` confirms the alias hit. `org_id` should be the value
you put in the JSON.

---

## Triaging failed analyses

Two failure surfaces to know about:

### 1. Failed jobs (`analyzer_jobs.status = 'failed'`)

The job hit an exception before the analysis row was written. The error
message is in `analyzer_jobs.error_message`. Common causes:

| Error message | Cause | Fix |
|---|---|---|
| `Ticket T... not found in local mirror — confirm sync is current.` | Autotask sync hasn't pulled the ticket yet. | Wait for the next sync, or trigger a manual sync from `/admin`. The webhook usually fills the gap; if not, see `scripts/backfill-ticket-notes-gap.ts` for the reconciliation pattern. |
| `LLM stage on claude-... failed twice` | Two Anthropic call attempts both failed (network / parse / 5xx). | Check the API key is valid (`curl` Anthropic), and check Anthropic status. Re-queue by clicking Re-analyze. |
| `Anthropic API error: 429 ...` | Rate limit. | Wait and re-queue. |
| `IT Glue search failed: ...` should NOT appear here — the pipeline tolerates IT Glue failures and continues without context. | If you see one, the failure was outside the catch block and is a bug. |

```sql
-- Recent failures
SELECT id, ticket_number, error_message, queued_at, finished_at
  FROM analyzer_jobs
 WHERE status = 'failed'
   AND queued_at > now() - interval '7 days'
 ORDER BY queued_at DESC;
```

To retry a failed job, queue a fresh one — there is no in-place retry.
Either click Re-analyze in the UI or:

```sql
INSERT INTO analyzer_jobs (ticket_number, queued_by_user_id)
SELECT ticket_number, queued_by_user_id
  FROM analyzer_jobs
 WHERE id = '<failed-job-id>';
```

### 2. Completed but flagged for human review (`needs_human_review = true`)

The pipeline finished and produced an analysis, but the model itself
reported low confidence or the cost circuit breaker tripped before Opus.
`human_review_reasons` is a JSONB array of short strings — read it.

The UI exposes this queue at `/analyzer/queue`. SQL equivalent:

```sql
SELECT id, ticket_number, analysis_version,
       confidence_score, estimated_cost_usd,
       human_review_reasons
  FROM analyzer_analyses
 WHERE needs_human_review = true
   AND status = 'complete'
 ORDER BY triggered_at DESC
 LIMIT 50;
```

Reasons you'll see:

- `"cost ceiling reached"` — Opus skipped to save money. Sonnet output is
  what you have. Re-run with `force=true` to override (and accept the
  spend).
- `"low confidence: <score>"` — the model itself reports uncertainty. The
  Sonnet output may still be useful as a starting point; treat as
  guidance, not verdict.
- A model-emitted reason from Stage 3 or Stage 4. The full string is in
  the JSONB — don't truncate before reading.

### 3. Worker not picking up jobs

If `analyzer_jobs` has rows in `status='queued'` for more than ~30s, the
worker is wedged or not running.

- Check the application log for `[ANALYZER-WORKER] starting` at boot. If
  missing in production, `NODE_ENV` may not be set to `production` —
  bring up `ANALYZER_WORKER_AUTOSTART=1` as a workaround.
- The worker is in-process; restarting the Pulse app restarts it.
- Concurrency: multiple replicas all run a worker, but `claimQueuedJob`
  uses `FOR UPDATE SKIP LOCKED` so each job is processed exactly once.

---

## Manual ops

### Trigger an analysis from psql

```sql
INSERT INTO analyzer_jobs (ticket_number, queued_by_user_id)
VALUES ('T20260424.0045', NULL);
```

The worker will pick it up within 2 seconds. `queued_by_user_id` may be
null (`ON DELETE SET NULL`).

### Force a re-run despite a cached analysis

The `force=true` flag on `POST /api/analyzer/tickets/:ticketNumber/analyze`
bypasses the idempotency short-circuit. The Re-analyze button in the UI
sends this flag.

### Inspect raw stage outputs for a debugging session

```sql
SELECT model_traces
  FROM analyzer_analyses
 WHERE id = '<analysis-id>';
```

Per-stage payload includes input/output tokens, attempt count, full parsed
model responses, and IT Glue resolution metadata. Don't paste this into
external systems — it can include redacted-but-still-sensitive snippets
of customer data.

### Ad hoc: how much have we spent this month?

```sql
SELECT round(sum(estimated_cost_usd)::numeric, 2) AS spend_usd,
       count(*)                                   AS analyses
  FROM analyzer_analyses
 WHERE triggered_at >= date_trunc('month', now());
```

---

## Things that are not implemented yet

- No automated retries on failed jobs.
- No `viewed_at` tracking on share rows (column exists, nothing writes
  to it).
- No `email_sent_at` column — share email send state lives only in the
  HTTP response and the server log.
- No pre-Sonnet cost gate. The circuit breaker only protects against
  Opus.
- No autocomplete on the share-recipient field.

If any of these become a real operational problem, file the work — the
stubs are intentional and called out in
`wulf-pulse-ticket-analyzer-build-notes.md`.

---

## Phase 2 additions

### Stage execution history (per-analysis forensics)

Migration 070 introduced `analyzer_stage_executions`. Every stage of the
pipeline (preprocess, triage, itglue, analyze, deep_review, fingerprint)
now writes a row including the input it saw and the output it produced.
Failed stages get a row with `error_message` populated.

Read it like this:

```sql
-- Full per-stage trace for one analysis
SELECT stage_order, stage, model_id,
       input_tokens, output_tokens, latency_ms,
       error_message
  FROM analyzer_stage_executions
 WHERE analysis_id = '<analysis-id>'
 ORDER BY stage_order;

-- Inspect a single stage's full input/output
SELECT input_payload, output_payload
  FROM analyzer_stage_executions
 WHERE analysis_id = '<analysis-id>' AND stage = 'analyze';
```

The legacy `analyzer_analyses.model_traces` JSONB column is preserved for
back-compat. New analyses double-write to both. A future migration will
drop `model_traces` once aggregate reports have soaked.

### Fingerprint stage (Stage 6)

After persistence, the worker runs Haiku-tier fingerprint extraction and
writes the result to `analyzer_analyses.aggregate_fingerprint`. Failure
is non-fatal — the analysis row stays usable, fingerprint stays NULL.

Re-fingerprint on demand:

```sql
SELECT id, ticket_number, analysis_version
  FROM analyzer_analyses
 WHERE aggregate_fingerprint IS NULL
   AND status = 'complete'
 ORDER BY triggered_at ASC;
```

Or run the backfill script (idempotent — the SQL filter skips already-fingerprinted rows):

```bash
npx tsx scripts/backfill-fingerprints.ts                # process all missing
npx tsx scripts/backfill-fingerprints.ts --limit=50     # cap work
npx tsx scripts/backfill-fingerprints.ts --dry-run      # show what would run
```

The script reconstructs the Stage 6 input from
`analyzer_analyses.model_traces.{triage_response, sonnet_response, opus_response}`.
If a row's model_traces is missing those fields (very old format) the script
logs `[skip]` for it and moves on.

Cost: Haiku at ~$0.001 per fingerprint. 1000 analyses ≈ $1.

### Aggregate reports

Migration 071 added `analyzer_aggregate_reports` and relaxed
`analyzer_stage_executions.analysis_id` to be nullable; rows now carry
either an `analysis_id` or an `aggregate_report_id` (CHECK enforces
exactly one).

Workflow:

1. User selects tickets on `/analyzer/tickets` and clicks
   **Generate aggregate report**.
2. POST `/api/analyzer/aggregate-reports` validates: ≤100 tickets, all
   have a fingerprint, none are stale. Inserts a 'pending' row, fires
   the runner via `void runAggregateReport(id)`, returns immediately.
3. Runner does:
   - Loads fingerprints
   - Computes SQL distributions (categories, clients, root cause,
     resolution path) and writes them to the row immediately
   - Fetches IT Glue doc titles for affected clients (if requested)
   - Calls Sonnet (Opus opt-in) with a structured payload
   - Writes the LLM-derived fields, marks `status='complete'`
4. UI polls `GET /api/analyzer/aggregate-reports/:id` every 3s while
   pending/running.

Inspect a report's sub-stage trace (linked via `aggregate_report_id`):

```sql
SELECT stage_order, stage, model_id, latency_ms, error_message
  FROM analyzer_stage_executions
 WHERE aggregate_report_id = '<report-id>'
 ORDER BY stage_order;
```

Daily spend on aggregate reports:

```sql
SELECT date_trunc('day', generated_at) AS day,
       count(*) AS reports,
       round(sum(estimated_cost_usd)::numeric, 2) AS spend_usd
  FROM analyzer_aggregate_reports
 WHERE generated_at > now() - interval '14 days'
 GROUP BY 1
 ORDER BY 1 DESC;
```

### Cost guards (Phase 2.7)

Three thresholds enforce per-user daily spend:

- **$5 per request** — a single aggregate report estimated above $5
  triggers a confirmation modal. Frontend re-POSTs with
  `confirmedCost: true` if the user clicks through.
- **$20 per user/day** — soft warn. Surfaced via `softWarn:true` in the
  cost evaluation; no enforcement.
- **$50 per user/day** — hard block. POST returns 403 unless the user
  is in `ANALYZER_DAILY_COST_OVERRIDE_USERS` (comma-separated list of
  Better Auth user ids).

Daily spend is computed as the trailing 24h sum of
`analyzer_analyses.estimated_cost_usd` + `analyzer_aggregate_reports.estimated_cost_usd`
for the user.

Every gating decision writes a row to `analyzer_cost_audit`:

```sql
SELECT created_at, user_id, action, estimated_cost,
       daily_spend_before, decision, decision_reason
  FROM analyzer_cost_audit
 ORDER BY created_at DESC
 LIMIT 50;
```

To grant a user override capability:

```bash
# in ~/projects_env/wulf-pulse.env
ANALYZER_DAILY_COST_OVERRIDE_USERS=user_id_1,user_id_2
```

Restart `pulse-app` to pick up the change.

### Browse / filter ticket list

`/analyzer/tickets` rebuilt as the primary entry point in Phase 2.5:

- Multi-select for client, issue type, queue, status, priority, assignee
- Sticky filter bar with period chips (today/yesterday/this+last week,
  30d/60d, custom range, all time)
- Active-filter chips below the bar; click to remove
- Bulk row selection persisted in localStorage
  (`analyzer:ticket-selection:v1`) — survives pagination but is
  session-scoped (no user id baked in)
- Bulk "Analyze N selected" sequentially queues jobs for each ticket
  (forces re-analyze on stale; analyzes from scratch on un-analyzed)
- Bulk "Generate aggregate report" routes selected tickets to
  `/analyzer/reports/new`; only enabled when all selected are
  `analyzedState === 'current'`

**Staleness heuristic**: a ticket's `analyzedState` is computed as
`stale` when `tickets.last_activity_date > latest_analysis.completed_at`.
The Phase 2 spec calls for content-hash-based comparison; that requires
either caching the current hash on the tickets row (sync change) or
computing it on read for the visible page (slow). The date heuristic
gets ~95% of the value at zero compute cost; revisit when there's real
load signal.

---

## Link-aware bundles (Phase 3)

A "bundle" is an aggregate report launched from a single ticket page —
typically a master/problem ticket that names other tickets in its
description. Instead of forcing the user to analyze each constituent
manually and then visit `/analyzer/reports/new`, the bundle endpoint
fans out per-ticket analyses and chains them into an aggregate report
automatically.

### How it works end-to-end

1. User loads `/analyzer/ticket/<ticket-number>`.
2. `<RelatedTicketsPanel/>` calls `GET /api/analyzer/tickets/:tn/links`
   (cheap, no LLM) — regex extraction over the description and retained
   notes for `T\d{8}\.\d{4}` references, plus the structured
   `RELATED TICKETS:` block detector and `problem_ticket_id` resolution.
   The panel renders only when refs exist or the ticket looks like a
   problem ticket.
3. (Optional) User flips the "AI-suggest more" Switch — this POSTs the
   same endpoint with `includeSuggested: true`, runs one Haiku pass over
   recent same-company tickets (±30 days, capped at 50 candidates), and
   returns up to 5 suggestions with one-line reasons.
4. User clicks **"Analyze with N linked tickets"**. The panel POSTs to
   `/api/analyzer/tickets/:tn/analyze-bundle` with
   `linkedTicketNumbers: [...]`.
5. The bundle endpoint:
   - Verifies every ticket exists locally (one SQL round-trip).
   - Per ticket: idempotency-checks via content hash. If a complete
     analysis exists, it's reused; otherwise a fresh `analyzer_jobs` row
     is queued.
   - Cost-guard runs against **new work only** plus the aggregate-reduce
     step. $5 confirmation threshold and $50 daily hard block are the
     same gates as standalone aggregate reports.
   - Inserts an `analyzer_aggregate_reports` row in `'pending_analyses'`
     state with `expected_ticket_numbers` populated (or straight to
     `'pending'` and immediately fires the runner if everything was
     already complete).
6. Worker polls and runs each queued job. After each successful
   analysis, `chainTriggerForCompletedAnalysis()` looks up bundles
   waiting on that ticket, appends the new analysis_id, and (if the full
   set is now satisfied) flips status to `'pending'` and fires
   `runAggregateReport()`.
7. Frontend polls `GET /api/analyzer/aggregate-reports/:id` every 3s and
   navigates to `/analyzer/reports/:id` on completion.

### Status state machine

```
pending_analyses ──── all expected analyses complete ────► pending
                                                              │
                                                              ▼
                                                            running
                                                              │
                                                              ▼
                                                       complete | failed
```

`'pending_analyses'` is the new state Phase 3 introduces.
Manual-multi-select reports created via `/analyzer/reports/new` skip it
and start at `'pending'` (their analyses must already be complete to
even submit).

### Inspecting a bundle

```sql
SELECT id, status, ticket_count,
       array_length(expected_ticket_numbers, 1)        AS expected,
       array_length(analysis_ids, 1)                   AS collected,
       triggered_by_ticket_number,
       generated_at
  FROM analyzer_aggregate_reports
 WHERE expected_ticket_numbers IS NOT NULL
 ORDER BY generated_at DESC
 LIMIT 20;
```

Find which expected tickets a stuck `pending_analyses` bundle is still
waiting on:

```sql
WITH r AS (
  SELECT id, expected_ticket_numbers, analysis_ids
    FROM analyzer_aggregate_reports
   WHERE id = '<report-id>'
)
SELECT etn.ticket_number,
       (SELECT bool_or(aa.id = ANY(r.analysis_ids))
          FROM analyzer_analyses aa
         WHERE aa.ticket_number = etn.ticket_number
           AND aa.status = 'complete') AS has_collected_analysis
  FROM r,
       LATERAL UNNEST(r.expected_ticket_numbers) AS etn(ticket_number);
```

The `false` rows are the tickets we're still waiting on. Cross-reference
with `analyzer_jobs` filtered by those ticket numbers to see whether the
job is queued, in-flight, or failed.

### Cost shape

For a typical 4-ticket problem bundle on fresh tickets:

| Step | Model | Approx cost |
|---|---|---|
| Link discovery (explicit) | none | ~free |
| AI-suggested arm (if toggled) | Haiku | ~$0.005 |
| Per-ticket pipeline × 4 | Haiku → Sonnet (+ optional Opus) | $0.20 – $1.20 |
| Aggregate reduce | Opus | ~$0.50 |
| **Total** | | **~$1 – $2** |

The bundle endpoint's per-ticket estimate is a flat $0.15 (pessimistic
Sonnet) used purely for the cost guard. Real spend is captured per row
on `analyzer_analyses.estimated_cost_usd` once each pipeline run
completes.

### When the panel doesn't render

The panel is intentionally invisible on tickets that aren't candidates
for bundling:

- No `T<YYYYMMDD>.<####>` references found in description or retained
  notes
- `tickets.problem_ticket_id` is null
- Title contains neither "master problem ticket" nor "problem ticket"

If a user expects to see the panel and doesn't, the most common reason
is that the referenced tickets aren't in our local mirror yet (sync
gap) — `discoverExplicitLinks` filters refs against
`tickets.ticket_number` to keep ghost links out of the UI. Run the
ticket sync and reload.

### Suggested-arm limits

The Haiku call drops any suggestion whose ticket_number isn't in the
candidate list it was given (hallucination guard). Suggestions are
capped at 5 and never auto-included — the user has to tick the
checkbox. If the LLM call throws, the failure is logged and the panel
still shows the explicit refs (the suggestion arm is opportunistic, not
load-bearing).

---

## IT Glue asset audits (Phase 4)

The asset-audit pipeline analyzes one IT Glue Application record at a time
against ticket history + IT Glue's own field schema, surfaces documentation
gaps and "promote-from-Notes" suggestions, and lets admins push approved
changes back to IT Glue. Every change is recorded in three audit layers
(see Phase 4 build notes).

Permissions:

- Read audit / run audit → any authenticated user (cheap, ~$0.01).
- Apply or Revert → `requirePermission('itglue', 'write')` — admin or
  super-admin only.
- `/admin/itglue-writes` → admin-only.

### Inspecting audits

```sql
-- Latest audit per asset, lowest scoring first
SELECT a.asset_id,
       fa.name                                   AS application_name,
       fa.organization_name,
       a.overall_score,
       a.ticket_count,
       jsonb_array_length(a.field_gaps)          AS gap_count,
       jsonb_array_length(a.notes_promotions)    AS promo_count,
       a.provider, a.model_used,
       a.estimated_cost_usd,
       a.generated_at
  FROM itglue_asset_audits a
  JOIN itg_flexible_assets fa ON fa.id = a.asset_id::bigint
 WHERE a.status = 'complete'
 ORDER BY a.asset_id, a.generated_at DESC;
```

```sql
-- Failed audits (forensics)
SELECT id, asset_id, generated_at, provider, model_used,
       LEFT(error_message, 200) AS error
  FROM itglue_asset_audits
 WHERE status = 'failed'
 ORDER BY generated_at DESC
 LIMIT 20;
```

### Inspecting writes

```sql
-- All committed writes in the last 7 days, with provenance
SELECT w.performed_at,
       w.field_name,
       w.before_value, w.after_value,
       w.performed_by_user_id,
       w.audit_id,
       fa.name AS application_name,
       fa.organization_name
  FROM itglue_writes w
  JOIN itg_flexible_assets fa ON fa.id = w.asset_id::bigint
 WHERE w.status = 'committed'
   AND w.performed_at >= NOW() - INTERVAL '7 days'
 ORDER BY w.performed_at DESC;
```

```sql
-- Failed writes (admin should investigate)
SELECT id, asset_id, field_name, error_message, performed_at
  FROM itglue_writes
 WHERE status = 'failed'
 ORDER BY performed_at DESC
 LIMIT 20;
```

### How a revert works

Reverts produce a brand-new `itglue_writes` row whose `before_value` /
`after_value` are swapped from the original, and mark the original row
`status='reverted'`. The chain is always traceable:

```sql
-- Find every write + its revert (if any) for one asset
SELECT id, field_name, status,
       before_value, after_value,
       performed_at,
       audit_id,
       source_evidence ->> 'reverts_write_id' AS reverts_id
  FROM itglue_writes
 WHERE asset_id = '17096940'
 ORDER BY performed_at;
```

### Cost-guard

Audit runs charge ~$0.01 (DeepSeek) or ~$0.10 (Claude) per call. Same
guard primitives as ticket analyses:

```sql
SELECT created_at, user_id, action, estimated_cost,
       decision, decision_reason
  FROM analyzer_cost_audit
 WHERE action = 'itglue_audit'
 ORDER BY created_at DESC
 LIMIT 50;
```

### When the data-builder returns no ticket evidence

`buildAssetAuditContext` joins via `companies.company_name = itg_organizations.name`
(case-insensitive) to map IT Glue org → Autotask company id, then matches
ticket fingerprints whose summary or fingerprint mention the asset name. Two
common reasons for an empty `ticket_evidence` array:

1. The IT Glue org's `name` doesn't match any `companies.company_name`
   exactly — fix by aligning the names, or extend the join.
2. The asset's `name` is too generic ("Office", "Email") and the ILIKE match
   pulls nothing distinctive — accept the audit will rely on field schema +
   peer exemplars only, no per-ticket grounding.

### Generic audit_log surfaces every change too

`audit.log()` is called after every successful Apply/Revert with action
`itglue.write` or `itglue.revert`. `/admin/audit-log` shows it. So admins
have two views: domain-specific at `/admin/itglue-writes` (with diffs +
revert button), and the generic admin feed at `/admin/audit-log`.

---

## Phase 4.1: ticket-first capture, Configurations, xrefs

### Inspecting the cross-reference index

```sql
-- Every ticket↔asset linkage in the last 7 days
SELECT created_at,
       ticket_number,
       relationship,
       asset_type,
       asset_id,
       source,
       confidence,
       details
  FROM itglue_ticket_xrefs
 WHERE created_at >= NOW() - INTERVAL '7 days'
 ORDER BY created_at DESC
 LIMIT 100;
```

```sql
-- Most-referenced IT Glue assets across all tickets (heat-map for which
-- docs the analyzer leans on most)
SELECT asset_type,
       asset_id,
       COUNT(*) AS reference_count,
       COUNT(DISTINCT ticket_number) AS distinct_tickets,
       MAX(created_at) AS last_referenced
  FROM itglue_ticket_xrefs
 WHERE relationship = 'referenced'
 GROUP BY asset_type, asset_id
 ORDER BY reference_count DESC
 LIMIT 30;
```

```sql
-- Assets that have been UPDATED via ticket-driven audits but never
-- REFERENCED — possibly newly-introduced docs that haven't proven their
-- worth yet, or docs the analyzer's retrieval stage isn't finding.
SELECT u.asset_type, u.asset_id, COUNT(*) AS update_count
  FROM itglue_ticket_xrefs u
 WHERE u.relationship = 'updated'
   AND NOT EXISTS (
     SELECT 1 FROM itglue_ticket_xrefs r
      WHERE r.asset_type = u.asset_type
        AND r.asset_id = u.asset_id
        AND r.relationship = 'referenced'
   )
 GROUP BY u.asset_type, u.asset_id
 ORDER BY update_count DESC;
```

### Ticket-scoped audits

```sql
-- All audits triggered from a specific ticket
SELECT a.id, a.asset_type, a.asset_id,
       fa.name AS asset_name,
       a.overall_score,
       jsonb_array_length(a.field_gaps) AS gap_count,
       a.provider, a.estimated_cost_usd,
       a.generated_at
  FROM itglue_asset_audits a
  LEFT JOIN itg_flexible_assets fa
    ON fa.id = a.asset_id::bigint AND a.asset_type = 'flexible_asset'
 WHERE a.triggered_by_ticket_number = 'T20260502.0033'
 ORDER BY a.generated_at DESC;
```

```sql
-- Every write a given ticket drove (denormalized for one-query lookup)
SELECT w.performed_at, w.asset_type, w.asset_id, w.field_name,
       w.before_value, w.after_value, w.status
  FROM itglue_writes w
 WHERE w.triggered_by_ticket_number = 'T20260502.0033'
 ORDER BY w.performed_at;
```

### Configuration audits

Same shape as Application audits but `asset_type='configuration'`:

```sql
SELECT a.id, c.name, c.hostname, c.configuration_type_name,
       a.overall_score, jsonb_array_length(a.field_gaps) AS gap_count,
       a.provider, a.generated_at
  FROM itglue_asset_audits a
  JOIN itg_configurations c ON c.id = a.asset_id::bigint
 WHERE a.asset_type = 'configuration'
   AND a.status = 'complete'
 ORDER BY a.generated_at DESC
 LIMIT 30;
```

### When asset-matcher returns nothing

`matchAssetsForAnalysis(analysisId)` joins `companies → itg_organizations`
on `LOWER(name)`. If a ticket comes back with `flexibleAssets: []` and
`configurations: []`, two common causes:

1. The IT Glue org name doesn't match the Autotask company name (case-
   insensitive exact). Fix by aligning the names in either system, or
   extend the join in `lib/services/analyzer/asset-audit/asset-matcher.ts`.
2. The fingerprint's `applications_involved` / `device_classes` don't
   contain any term that substring-matches a real asset name. The audit
   panel will show "No matches" — the user can still manually navigate
   to the relevant asset's detail page and run an asset-first audit.

### Cost shape for ticket-first audits

Per-asset audit on a ticket-scoped run:

| Provider | Cost | Latency |
|---|---|---|
| Anthropic (Sonnet) | ~$0.05 | 30-60s |
| OpenRouter (DeepSeek V4 Pro) | ~$0.005 | 60-180s |

A typical ticket with 1 Application match + 1 Configuration match audited
on DeepSeek runs ~$0.01 and ~3 minutes total.


## Phase 4.3: LogLift event-log evidence pipeline

### Required env vars

```
B2_KEY_ID=…                 # Backblaze B2 application key id
B2_APP_KEY=…                # Backblaze B2 application key secret
B2_BUCKET=wulf-audits       # default; matches existing n8n bucket
B2_REGION=us-west-002       # default
B2_ENDPOINT=s3.us-west-002.backblazeb2.com   # default; no scheme
OPENCLAW_API_KEY=…          # webhook auth + collector variable
BETTER_AUTH_URL=…           # base URL the collector POSTs back to
```

### One-time discovery

```bash
# Sign in as admin → /admin/rmm-overshell → click "Re-discover LogLift"
# Or via curl with an admin session cookie:
curl -X POST "$BETTER_AUTH_URL/api/admin/rmm/settings/discover-loglift" \
  -H "Cookie: better-auth.session_token=…" -i
```

Confirm:

```sql
SELECT loglift_component_uid, loglift_component_name, loglift_discovered_at
  FROM rmm_settings WHERE id = true;
```

### Inspecting LogLift uploads

```sql
-- Recent LogLift executions, with match status
SELECT e.id, e.run_id, e.target_hostname, e.status,
       e.evidence_object_key,
       e.parsed_evidence -> 'event_count_total' AS event_count,
       e.parsed_evidence -> 'webhook_summary' -> 'criticalEvents' AS critical_events,
       e.queued_at, e.completed_at
  FROM rmm_executions e
 WHERE e.transport = 'b2_upload'
 ORDER BY e.queued_at DESC
 LIMIT 25;
```

```sql
-- LogLift uploads that didn't match a Configuration (review for hostname
-- typos or unmapped Configurations)
SELECT e.run_id, e.target_hostname, e.target_company_id,
       e.queued_at
  FROM rmm_executions e
 WHERE e.transport = 'b2_upload'
   AND e.status = 'complete'
   AND e.asset_id IS NULL
 ORDER BY e.queued_at DESC;
```

```sql
-- LogLift uploads that auto-fired an audit
SELECT a.id AS audit_id, a.asset_id AS configuration_id,
       a.overall_score, a.generated_at,
       l.action, l.created_at AS triggered_at
  FROM audit_log l
  JOIN itglue_asset_audits a ON a.id::text = (l.details ->> 'audit_id')
 WHERE l.action = 'rmm.loglift.audit_triggered'
 ORDER BY l.created_at DESC LIMIT 25;
```

### Manual replay of a B2 object

If a webhook came in but Pulse was down, you can replay by re-POSTing
the original webhook payload (the collector keeps the metadata; if not,
build it from the object key + B2's metadata API). The receiver is
idempotent on `run_id` — a duplicate replay with the same `run_id` will
update the existing row rather than insert a duplicate.

### Inspecting a stuck b2_upload row

```sql
SELECT id, run_id, target_hostname, status, started_at, timeout_at,
       evidence_object_key
  FROM rmm_executions
 WHERE transport = 'b2_upload'
   AND status = 'running'
 ORDER BY started_at;
```

After 5 minutes, the Overshell worker's timeout sweep flips stuck rows
to `timeout`. If the upload arrives later, the receiver still updates
the same row by `run_id` (the unique index makes this safe).

### Force a one-off audit replay from existing evidence

If the auto-audit failed at upload time (e.g. LLM timeout) and the
evidence is already in `rmm_executions`, you can re-fire the audit:

```sql
-- Find the configuration_id from the most recent LogLift upload
SELECT asset_id::text AS configuration_id
  FROM rmm_executions
 WHERE transport = 'b2_upload'
   AND target_hostname ILIKE 'YNGHYNWNP01'
 ORDER BY completed_at DESC LIMIT 1;
```

Then trigger via the existing audit endpoint:

```bash
curl -X POST "$BETTER_AUTH_URL/api/itglue/asset-audit/run" \
  -H "Content-Type: application/json" \
  -H "Cookie: better-auth.session_token=…" \
  -d '{"assetType":"configuration","assetId":"<config_id>","provider":"anthropic"}'
```

### Why no Telegram summary?

Out of scope for v1 — that flow was a notification, not a data path.
Audits show up on the Configuration page automatically. If you want a
Slack/Teams ping when an auto-audit completes, hook it off the
`rmm.loglift.audit_triggered` audit_log entry.