Freshservice reporting infrastructure

Two-track reporting and analytics layer on top of the Freshservice REST API. A Microsoft Fabric / Power BI track for executive and leadership reporting, plus a parallel local-cache + Claude skill + AI-enrichment track for the IT team and the broader internal AI-augmented IT operations platform. Both tracks pull from the same Freshservice API surface (analytics CSV exports plus REST endpoints) so the underlying data is consistent across audiences.

Why two tracks instead of one

Different audiences want different latency, interaction, and audit profiles.

• Executives, FLT, leadership Pre-built Power BI dashboards on Fabric, scheduled refresh, semantic-model-curated measures, Fabric workspace permissions and RLS where applicable.
• IT team, IT Director Ad-hoc natural-language queries via Claude against a local SQLite cache, with live API fallback for single-record time-sensitive lookups. File-system permissions on the OneDrive mount.

Same source of truth means the two tracks can be cross-validated against each other.

Track 1: Power BI on Microsoft Fabric

Freshservice data lands in the Fabric workspace where dashboards are built on the Fabric F64 capacity semantic model. Pages cover:

• Volume and trend Ticket counts by category, year-over-year trend, seasonality, regional distribution.
• SLA and resolution First-response time, resolution time, SLA attainment by priority and category.
• Category trending Which IT categories are growing, which are shrinking, where to focus.
• Asset reporting Warranty expiry pipeline, refresh-cycle visibility, vendor concentration, regional asset distribution.

This is the layer presented at FLT (Forum Leadership Team) and other executive forums. Sits alongside the broader Fabric F64 dataset estate the BI team maintains for the customer-facing multi-manufacturer multi-tenant reporting.

Track 2: Local SQLite cache + Claude skill

refresh_cache.py

Python script on Windows Task Scheduler at 9:05 AM every weekday. Reads configuration (API key, domain, report IDs) from freshservice.config.json, downloads five analytics CSV exports from the Freshservice API (tickets, users, computer_assets, other_assets, solutions), builds a SQLite database in /tmp (OneDrive does not tolerate SQLite's locking and journaling, so the build happens locally), then moves the finished SQLite file to FreshserviceDB/freshservice_cache.db on OneDrive so it syncs across machines. Updates a _meta table with refresh timestamps per source.

Total refresh runtime: roughly 6 seconds for ~14,000 tickets, ~4,200 users, ~4,000 assets, and ~240 solution articles. Database size: ~8 MB. Read-only client access via sqlite3.connect(f"file:{p}?mode=ro&immutable=1", uri=True) to avoid OneDrive write contention.

SQLite schema

Five core tables (tickets, users, computer_assets, other_assets, solutions) plus per-ticket enrichment tables (ticket_details, ticket_conversations), an enrichment-state tracking table (_enrichment_state), and a refresh-tracking meta table (_meta). All columns stored as TEXT for portability; consumers cast to numeric / date types as needed.

internal-itsm Claude skill plugin (Claude skill)

Custom Claude skill packaged as a Claude skill plugin exposes the cache to AI-augmented querying. Resolves the cache path on the user's machine, checks freshness against a configured stale-after threshold, opens SQLite in read-only immutable mode, lets Claude run SQL queries against the cache to answer arbitrary IT-team questions, and supports live API fallback for time-sensitive single-record lookups.

Track 2 extended: AI Platform Stage 1

Stage 1 of the internal AI-augmented IT operations platform runs on the same Freshservice cache. Covers the daily refresh, per-ticket enrichment (full conversation thread and custom fields via the REST API, stored in ticket_details and ticket_conversations), and AI-augmented triage / reply drafts / asset enrichment via the Claude API.

Architecture conventions: stdlib-first Python, idempotent and resumable jobs, rate-limit aware against the Freshservice API, secrets in Bitwarden delivered via environment variables, no raw SQL or arbitrary tool access exposed to techs (curated function set only).

What this demonstrates

• End-to-end ownership of a data pipeline from REST API source through ingest, storage, semantic modeling, and consumer surface.
• Audience-appropriate analytics: executives get curated dashboards on Fabric, the IT team gets ad-hoc natural-language querying via Claude, the AI-augmentation layer gets the same data plus enrichment.
• API integration discipline: rate-limit aware, idempotent, resumable, secrets-managed, with explicit caching where appropriate to avoid hammering the source API.
• OneDrive-aware SQLite handling: the /tmp build + atomic move pattern is the workaround for OneDrive's lack of tolerance for SQLite's journaling.
• Curated function exposure rather than raw access: the Claude skill exposes a structured query surface, the safer pattern for AI-augmented operational tools.
• Reusable foundation for the broader AI-augmentation platform: the same cache and enrichment patterns scale across the team's other source systems (Microsoft Graph, ScreenConnect, Meraki, Bitdefender, Action1) as the platform expands.