[FEATURE] Add a headless Windows Service collector mode for Lite (gMSA-compatible), with the existing UI as a read-only viewer

[gotqn]

Which component(s) does this affect?

• Full Dashboard
• Lite
• SQL collection scripts
• Installer
• Documentation

Problem Statement

Lite is a WPF GUI app whose collectors only run inside the GUI (CollectionBackgroundService is started from MainWindow.Loaded; Program.Main always calls app.Run()). Unattended collection therefore requires a dedicated user with autologon and a persistent interactive desktop session — a security/compliance concern. There's no way to run Lite collection under a service identity, including a gMSA with auto-rotating credentials. Full edition's headless option (SQL Agent jobs) requires deploying objects into each monitored instance, which is exactly what Lite's agentless model avoids.

Proposed Solution

1. Extract the collection core (CollectionBackgroundService, RemoteCollectorService, DuckDbInitializer, ServerManager, ScheduleManager, archival/retention) into a shared library + a new headless host (.NET Worker) registered as a Windows Service via UseWindowsService(), runnable under LocalSystem / a service account / a gMSA.
2. Keep the WPF app as a viewer: remove its in-process collector startup; have it read the store. Resolve the DuckDB single-writer constraint by opening read-only (custom connection factory + WAL reader isolation) or by querying the service's MCP/localhost endpoint.
3. Ship as an optional service package so the current single-process Lite stays supported (backward compatible).

Use Case

Acceptance criteria: service installs and runs under LocalSystem and a gMSA, starts at boot with no logon, survives reboot; reads servers.json + Credential Manager under the service identity (ACLs verified); the viewer renders dashboards against a store the service is actively writing, with no corruption; schedule/retention edits are honored by the service; existing desktop mode unchanged.

Alternatives Considered

Currently, my setup is:

• dedicated account running Lite
• additional settings for surviving restart and auto-start
• additional settings (ssh) to expose the MCP server to remote users
• if more then one dev wants to see the dashboard, a team call can be made as only one can log into the dedicated account

Additional Context

Change size: Medium — new host project + refactor to a shared collection assembly; the hard parts are cross-process DuckDB access and credential/ACL access under a service account, both needing careful testing.

Note for reviewers: Full edition already offers headless collection via SQL Agent with a read-only Dashboard; weigh pointing service-account users there vs. investing in a Lite service mode (the latter preserves Lite's agentless advantage).

[erikdarlingdata]

Thanks for writing this up so carefully — you've independently described something I already have a detailed internal design for, after three sessions of work on it. Since you clearly understand the tradeoffs, I'm going to share the whole thing: the vision, the research (including a load-bearing finding that changes the difficulty), the hard problems, rough effort, and where it's actually parked.

Short answer

Yes — "headless Lite" is a planned direction, and this issue describes it almost exactly. The full Dashboard is deprecated going forward: it stays buildable, shipped, and supported with security and bug fixes so existing deployments keep working, but it gets no new features and is no longer a parity target. New investment flows to the Lite + headless-Lite line. So for your use case the answer isn't "go use Full" — headless Lite is the intended answer.

The vision

1. Lite stays a portable app — a WPF viewer you can run from a folder, no install (the portable ZIP already ships).
2. A new headless collector runs 24/7, independent of any logged-in session — the thing that makes it behave more like an enterprise centralized monitor. Windows Service vs. scheduled task is still an open implementation choice (I say "service" as shorthand).
3. The WPF app becomes a viewer over the same store.

The forcing function is exactly your problem statement: Lite is a WPF app, so it lives and dies with its Windows session. When the VM logs the user off, the OS tears down the session and every process in it — there's no code Lite can run to refuse that. Two paths:

• Path 1 (config only, works today): disable disconnected-session timeout, disable sleep, auto-logon to a console session that never logs off. Costs nothing but is fragile — a reboot/patch/policy push silently stops collection. This is your current setup, and it's the documented stopgap.
• Path 2 (the architecture change): decouple the collector from the UI so it collects 24/7 regardless of session state. This is the real fix, and it's conceptually how the Full edition already behaves with server-side Agent jobs — but without pushing anything onto the monitored instances.

The key research finding (this changes the difficulty)

The scariest technical risk — "the viewer can't read the live DuckDB while the service is writing it" — is not real. I verified it empirically on a live database:

With Lite holding the read-write connection and actively collecting, a second process opened the same monitor.duckdb with read_only=True and succeeded in ~40 ms, saw all tables, and read the current collection_log timestamp. Repeated robustly (4 opens, ~23–40 ms each).

A DuckDB 1.5.x writer (Lite pins DuckDB.NET 1.5.2 today) permits one read-write process + concurrent cross-process read-only opens. So your proposal 2 resolves to the simplest of the options you listed: the viewer just opens the store read-only — no IPC, no multi-writer, and no waiting on a future DuckDB release for the read path.

Important caveat I found by testing: this is writer-version-dependent. A controlled two-process test with a DuckDB 1.4.4 writer blocked the read-only open entirely — concurrent RO is a property of the 1.5.x engine's relaxed write lock, not all DuckDB. So the rule is: pin the writer at 1.5.x and re-verify across any DuckDB upgrade.

Two narrow, non-blocking residuals (both already exist within the single app today):

• Reopen to refresh: a read-only connection sees a snapshot as of open, so the viewer opens→query→close per refresh (cheap at ~40 ms). This is exactly what the bundled DuckDB MCP tooling already does.
• Parquet-archive glob race: the v_* views UNION the hot table with read_parquet() over the archive dir, and the archiver moves/deletes those files during compaction. A reader can occasionally hit a vanishing file → transient error, retry next refresh. Already handled inside the app; harden with a retry on the external reader.

Why this is a split, not a rewrite

Your proposal 1 is well-founded — the collector engine is already cleanly separable:

• Collection already runs as CollectionBackgroundService : BackgroundService, started off the UI thread. (It's currently constructed and owned by the viewer, so the service host's first job is to take over that lifecycle.)
• The collector layer — RemoteCollectorService, ScheduleManager, ArchiveService, RetentionService, ServerManager, DuckDbInitializer — has zero WPF/Dispatcher references, with one small exception to decouple first (ServerManager reaches into a dialog's static IsDialogOpen flag).
• The MCP host is already a BackgroundService on a fixed loopback port, and it already uses the generic Host builder — so it's the model for the worker host.

The hard problems (what actually gates this — and it isn't DuckDB)

Because the read path is solved, the remaining work is relocation and lifecycle, all well-understood:

1. Data location + existing-user migration. Data home resolves under %LocalAppData% today. A service running as LocalSystem (or a service account) resolves that to a different profile — so a naive service writes a brand-new empty DB the viewer never sees, and a non-admin viewer can't even read the LocalSystem profile dir. Fix: a machine-scoped home (%ProgramData%) the service writes and the viewer opens read-only, with an ACL grant and a one-time migration of existing data. There's already a precedent for this in the codebase (the shared servers.json relocation).
2. The viewer still writes today. Mute rules, alert dismiss, finding mute/unmute, and persisting on-demand analyses are writes. Under a read-only viewer those need a small write-only IPC command channel to the service (or get dropped, per action). Reads don't need it, so this channel is narrow.
3. Continuous alerting must move into the service. Alerts currently fire from a UI timer — so if the viewer is closed, alerts stop, which defeats the whole point. Alert evaluation + email/webhook delivery move into the service as the sole sender, with the persisted alert log as the cross-process dedup.
4. MCP hosting moves to the service, so the endpoint is up whenever collection runs (no viewer required) — which also directly serves your "expose the MCP server to remote users" goal without a dedicated logged-in account.
5. Service identity & auth (see gMSA below).
6. Packaging / elevation / updates. Service register/unregister needs elevation, and updating a running service means stop→swap→start (the SCM holds the exe locked) — different from the per-user, non-elevated app update flow.
7. Operability. Machine-scoped + viewer-readable logs, Windows Event Log for start/stop/crash, and a real liveness/heartbeat signal (a crashed service must not let the viewer keep reporting "healthy").

On gMSA specifically

A gMSA is a great fit and the cleanest credential story for the on-prem case. Server passwords currently live in Windows Credential Manager per-user (DPAPI), which a service identity wouldn't see — the classic headless problem. Running the service as a gMSA gives you auto-rotating credentials with no stored secret, and for any SQL Server reachable via Windows/integrated auth it removes the per-user Credential Manager dependency entirely — the gMSA itself is the SQL login. (This is the on-prem analog to Azure Managed Identity, which similarly stores no secret.) One constraint to call out: interactive Entra MFA auth is incompatible with headless collection — a session-0 service has no desktop to show the browser prompt — so headless mode would steer Azure targets to Service Principal / Managed Identity and refuse/skip interactive-MFA servers.

Rough effort

Core class-library extraction ~1–2 days; worker host ~1 day; install/lifecycle plumbing ~2–4 days; viewer-reads-health rework ~1–2 days; plus the alerting/MCP relocation, data-home + migration, identity, and packaging work above. Broader than a weekend, but it's well-understood engineering — not blocked on any external dependency.

Status

Parked on decisions, not technology — where the data lives (+ one-time migration), service identity, and moving alerting + MCP into the service. The technology to build all of it exists today. Your issue is a strong demand signal, and your current workaround (dedicated account + autostart + SSH-exposed MCP + one-viewer-at-a-time) is exactly the Path-1 pain that Path 2 removes. I'll use this issue to track it.