IT Inventory & Managed Assets

Overview

The IT Admin dashboard (it-admin-page) currently renders a flat grid of cards — Server Jobs, SQL Entities, Websites, Webhooks, File Watcher, Server Activity, Conversation Bots, API Docs — grouped only by loose sections (IT Inventory, IT Health, IT Development). Nothing tells you which server or site a given record runs on; the relationships exist in the data but the UI is a bag of peers.

The it.Server table already exists and holds the real assets (a web server row, a database server row), but there is no server list or detail page in the portal — the table is only used to populate a dropdown on Server Jobs. Confusingly, the module named server-inventory is not a server registry at all; it is the API activity-logs + IP-blocking module (see IA).

The goal of this initiative: turn IT Admin into a containment model of managed assets. You navigate to a server, see the websites and databases it hosts; navigate into the API website, see the functions it runs (watchers, webhooks, bots, activity, docs); navigate into a database, see its schemas and tables. Every current card becomes a view behind the asset it belongs to.

Two Kinds of Relationship

The organizing insight — and the answer to "which records get a foreign key" — is that the IT records relate to assets in two fundamentally different ways. Conflating them is what produces meaningless keys.

The Containment Model

Two tiers of containment. Server is the physical/virtual host; Website is an app hosted on a server. Most "API-tied" features hang off the API website (which itself hangs off the web server) — a two-hop path, not a direct server link.

Server (Role = Web) ──┬── Website (Role = Public) ── CMS Web Pages
                      │
                      └── Website (Role = Api, IsHost) ──┬── File Watchers   ┐
                                                         ├── Webhooks        │ intrinsic
                                                         ├── Conversation Bots│ functions
                                                         ├── Activity Logs   │ (no FK —
                                                         └── API Docs / OpenAPI┘ by definition)

Server (Role = Database) ── ServerDatabase ── ServerSchema ── ServerTable / ServerView   (= SQL Entities)

Server-scoped (placement FK to Server, not to a website):
      ServerJob (runs on a server)   ·   ServerLog (server events)

The API application has no entity of its own — it is it.Website row 11. That is convenient: "everything tied to the API" resolves to "the website identified as the API host," and its intrinsic functions render as that site's feature tabs.

Anchor Entities

Server — it.Server

PK Id. Fields: Name (FQDN), OperatingSystem, IPAddress, CompassComponentId (→ atlassian.CompassComponent), JSON Data. Navigations: Websites, ServerDatabases.

Website — it.Website

PK Id. Placement FK to Server: already wired (ServerId, required, FK_Website_Server). Fields: Name, Binding (IIS host, e.g. www.auburnal.gov), RootPath, LogPath, State, JSON Data, VDirs. Also the root of the CMS Webpage hierarchy (FK_Webpage_Website).

Keys Audit

What links exist today, and the gap. Only placement links appear here; intrinsic functions are deliberately keyless.

Information Architecture

Collapse the flat card grid into two drill-downs rooted on the anchor entities. The existing pages mostly survive — they get re-parented behind Server / Website detail instead of being top-level cards, and each list gains a server/website scope filter.

Servers module (new)

• Server list — the missing page. Rows show Role, OS, IP.
• Server dashboard — stat cards for the hosted assets (Websites, Databases, Jobs), the hosted-website list as the primary view, and a sidebar with the server's identity (edit-in-drawer sets Role) plus database/job summaries. The stat-card layout doubles as the relationship view and the drill-down; a plain tab strip showed the relationships but drilled poorly.

Website detail

• A per-website dashboard. For an Api-role site it surfaces the request-traffic KPIs (the current server-inventory activity dashboard — WebsiteLog is per-website data), a Recent Errors panel (this is the real home for ServerLog, which is effectively an API-app error log), and the site's intrinsic functions: File Watchers, Webhooks, Conversation Bots, API Docs. These are the current IT-Inventory / IT-Health cards, re-homed as the API website's functions. A public site gets a simpler view (bindings, pages, state).
• For a Public-role site: CMS web pages, bindings, state — no function tabs.

Database drill-down

• The server dashboard's stat cards are clickable (Websites/Databases scroll to their list; Jobs opens the server-jobs page), and Databases is a drillable list in the main column. From there the full catalog chain is navigable: Server → Database → Schema → Table/View — DatabaseDetailPage lists a ServerDatabase's schemas, SchemaDetailPage lists a ServerSchema's tables and views (the leaf). This supersedes the flat /admin/sql-entities list (all views/tables un-scoped). Each level's Data JSON is surfaced generically as key/value rows.
• Table usage stats answer "is this object utilized?" The catalog job now captures per-table row count / size (sys.dm_db_partition_stats) and last write / last read (sys.dm_db_index_usage_stats), promoted to real ServerTable columns so "biggest / never-read tables" are sortable. Views (no storage) get last-modified + a referenced-by count (sys.sql_expression_dependencies) as a static-utilization proxy. Because the index-usage DMV resets on SQL restart, last-read/last-write are accumulated as a running MAX across catalog runs — the recurring job turns a volatile snapshot into a durable last-used date. Real view runtime usage (Query Store) and a stale-object soft-delete are v2.

Server Jobs & SQL Agent Sync

The it.ServerJob table acts as the configuration source of truth and metadata cache for tasks that execute as Microsoft SQL Server Agent jobs. Because execution happens in the SQL Agent, state is synchronized bi-directionally between the application database and the msdb system database.

Table Schema & SQL Agent Mapping

Synchronization Triggers

Data moves between the application and SQL Agent through two primary pathways:

• App ➔ SQL Agent (Synchronous Push): Whenever a job is created, updated, deleted, enabled, or disabled via the API, the changes are instantly pushed to the SQL Agent via ISqlAgentService.UpsertSqlAgentJobAsync. The background service also ensures definitions stay aligned every 15 minutes.
• SQL Agent ➔ App (Metadata Pull): A background worker (ServerJobSyncService) polls the SQL Agent every 15 minutes to fetch the latest LastRun, NextRun, and outcome status for all jobs. Additionally, when a workflow job is executed manually via the API, the API synchronously fetches the updated NextRun immediately upon completion.

IIS Inventory & Per-Website Logs

Web-server sites are inventoried and their access logs captured by a single workflow ServerJob. This section covers that pipeline and how per-website traffic and certificate data reach the dashboards.

The inventory pipeline

One ServerJob (WebDevelopment.IT.IIS-Discovery), actions in order:

The ps1 parses, the handler post-processes — complementary, not double-parsing (the handler's own parse-logs action is dormant in this flow).

Three log sources — don't conflate them

The per-website dashboards are powered by IISLogEntry (every site); the API site additionally has the richer WebsiteLog view. Before this work, IISLogEntry had no read path — which is why every site but the API showed a blank page.

Per-website logs

• Site link. IISLogEntry was keyed only by the SiteName string. Added a nullable, indexed WebsiteId — a plain column, not an enforced FK, for the same reason as the loose log-table columns above (a constraint on a high-volume log table is operational cost for little gain). The parser stamps it on new rows; the existing ~2.5M rows were backfilled from SiteName = Website.Name.
• Read path. GET /it/website/{id}/iis-logs returns recent rows plus a small aggregate (status mix, bot share, top paths), matched by WebsiteId with a SiteName fallback for the transition window. The website dashboard shows a traffic panel (tiles + Recent Requests + Top Paths) for every site.

Expanded site info & certificates

Get-IIS-Inventory.ps1 now also captures, per site: per-binding certificate detail (thumbprint → subject / issuer / NotAfter / DNS names, resolved from the local store), log configuration (format / period / enabled / truncate), and app-pool info (CLR / pipeline / identity / state) — all folded into the Data JSON column.

Deferred to v2

• One ingest path. Retire Audit-IISLogs.ps1 in favour of the C# handler's parse-logs (in-process, no external LogParser, already does the CMS linkage) — keeping both is redundant once WebsiteId stamping is everywhere.
• IIS control from the API. A dedicated PowerShellHandler workflow action (start / stop / recycle a site), modelled on NetworkConnectionHandler.execute (impersonated pwsh under a credential). Turns Website.State from daily-informational into an action, and enables on-demand inventory refresh. Guardrails: Admin policy, confirm-before-stop, audit. The API runs on the web server, so this is feasible without elevating its app-pool identity.
• In-app Role/IsHost editing. A PATCH /it/website/{id} endpoint (the controller is GET-only today), so the website identity card's edit drawer can set them instead of SQL.

Phased Plan

Decisions Settled

• Two-tier grain. API-tied features relate to the API website, and the website relates to the server — a two-hop path. Features do not carry a direct ServerId.
• Intrinsic functions carry no FK. File watchers, webhooks, and bots are functions of the API code; a WebsiteId on them would be a constant, so it is omitted. They are grouped by resolving the API host, not by a join.
• Server and Website both get a Role; Website additionally gets IsHost. "The API" = Role = Api AND IsHost, resolved to exactly one row — replacing the hardcoded WebsiteId = 11.
• Keys before UI. Phase 1 wires the relationships; the drill-down pages are straightforward once the model is real.
• Log-table site links are plain indexed columns, not FKs. IISLogEntry.WebsiteId (like the deferred WebsiteLog/ServerLog links) is a nullable indexed column — a constraint on a high-volume log table isn't worth the write/validation cost.
• Cert expiry is promoted to a column. Website.EarliestCertExpiry makes "expiring soon" queryable; per-binding detail stays in the merged Data JSON.
• Data backfills never live in an EF migration. Schema in the migration, backfill in a separate batched script — EF's 30s migration timeout + transaction turns a heavy UPDATE into a full rollback.
• Keep both IIS parsers in v1. The ps1 parses and the C# handler post-processes (CMS linkage); consolidating onto one path is a v2 cleanup, not a prerequisite.