# 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 = ''; ``` `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 = ''; ``` ### 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: "` — 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 = ''; ``` 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 = '' ORDER BY stage_order; -- Inspect a single stage's full input/output SELECT input_payload, output_payload FROM analyzer_stage_executions WHERE 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 = '' 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/`. 2. `` 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 = '' ) 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.<####>` 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":"","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.