Device Discovery Filter and Sort

Design notes: server-side filter & sort on GET /api/v1/devices

Status: Draft — needs decisions on the open questions

Summary

• Ask: which filter/sort params are needed beyond the ones available today (hostname, friendlyName, tags, activated, discovered)? #1077 proposes skuType, currentMode, lmsInstalled, osName, plus sort/order.

Background

Issue / PR	What it is
#858	Original device-discovery request.
#1093	Parent issue: dashboard + filter/sort + export. Says sort should work "by any column".
#1077	This task. Adds filter[skuType], filter[currentMode], filter[lmsInstalled], filter[osName], sort, order.
#1210	Same problem for currentMode/discovered. Solved by denormalizing into columns.
#1211, #1214	#1210 implementation (new columns; stats + activated/discovered filters).

Problem

The new filter fields are not columns on devices; they are nested inside deviceInfo. We support three databases (Postgres, SQLite, MongoDB) behind one Repository interface, and deviceinfo is stored as a plain string on all three (not a native document even on Mongo). There is no single portable way to filter or sort on values inside it.

Current state: filters on /devices today

Source: internal/controller/httpapi/v1/devices.go (get), odata.go, export.go.

GET /api/v1/devices

Query param	Values	Behaviour
hostname	string	Exact match on the hostname column (GetByColumn).
friendlyName	string	Exact match on the friendlyname column (GetByColumn).
tags	comma-separated list, e.g. tags=lab,floor2	Matches devices whose tags contain the given tags (GetByTags).
activated	true	Managed devices: discovered is not true, or currentmode has a value other than "not activated" (GetActivated). Includes legacy devices with no recorded mode.
discovered	true	Discovered-but-not-activated devices: discovered = true and currentmode is empty/NULL/not activated (GetDiscovered).

Notes / current limitations:

• Filters don't combine. The handler checks them in a fixed order and uses only the first one that is set: hostname → friendlyName → tags → activated=true → discovered=true → no filter. Every other filter param is ignored.
• activated / discovered only react to the literal value true; any other value (including false) falls through to the unfiltered list.
• There is no sort / order support yet. Results are ordered by guid.

GET /api/v1/devices/export

No filter params. Returns every device for the tenant, capped at 500 records.

GET /api/v1/devices/stats

No filter params. Returns totalCount, activatedCount, and discoveredCount for the tenant, using the same activated / discovered rules as above.

GET /api/v1/devices/tags

No filter params. Returns the distinct tags used by the tenant's devices.

Scope: what #1077 adds

Query param	Backed by	Type
filter[skuType]	new skutype column, derived from the fwSku bitmask	string (vPro / ISM / …)
filter[currentMode]	currentmode column (from #1210)	string (ACM / CCM / …)
filter[lmsInstalled]	new lmsinstalled column	bool
filter[osName]	new osname column	string
sort + order	any of the above, plus existing top-level columns (hostname, friendlyName, …)	—

Provisioned State (from #1093) is covered by currentMode.

Options considered

The same three options were weighed for #1210 in API contract update & DB query mechanism to fetch Device Stats. They apply equally here.

Option	Pros	Cons	Verdict
Denormalize — copy each field from deviceInfo into its own column on write	Plain queries, same behaviour on all 3 DBs, indexable, DB-side paging	One migration + write-path code to keep columns in sync	Recommended (used by #1210)
Query the JSON blob directly	No new columns	Postgres (::jsonb ->>) and SQLite (json_extract) need different SQL; not possible on Mongo (blob is a string)	Rejected
Filter in Go — load rows, parse deviceInfo, filter in memory	No schema change	Loads every device into memory; paging can't be pushed to the DB; doesn't scale	Rejected

Proposed approach

Follow the #1210 pattern:

1. Add nullable columns to devices: skutype, lmsinstalled, osname (currentmode and discovered already exist).
2. Fill them from deviceInfo on every insert/update. deviceInfo stays the source of truth; the columns are a queryable copy.
3. Derive skutype from the fwSku bitmask (below).
4. Implement filter/sort twice behind the same Repository method: SQL WHERE/ORDER BY in internal/usecase/sqldb/device.go, bson.M filter in internal/usecase/nosqldb/mongo/device.go.

skuType derivation

fwSku is a bitmask stored as a decimal string. The frontend already decodes it this way (sample-web-ui devices.component.ts, branch device-type-column):

func skuTypeFromFWSku(fwSku string) string {
    n, err := strconv.Atoi(fwSku)
    if err != nil {
        return ""
    }
    switch {
    case n&0x10 != 0:
        return "ISM"
    case n&0x08 != 0:
        return "vPro"
    default:
        return "" // or "Non-vPro" — see open question below
    }
}

This runs once when deviceInfo is written and the result is stored in the skutype column.

Open questions

Product

• Is the #1077 list (skuType, currentMode, lmsInstalled, osName, sort, order) the full set, or do we need any more filter/sort params (e.g. Provisioned State, AMT enabled status from #1093) in this same pass?
• sort=currentMode — alphabetical, or lifecycle order (Pre-Prov → CCM → ACM)? Lifecycle order needs an explicit rank mapping; plain alphabetical can also differ between databases.
• Do multiple filter[...] params combine with AND? Should they also combine with the existing tags/hostname/friendlyName params (today only one filter is applied)?

Technical

• Denormalize every filter/sort field, or only the ones inside deviceInfo?
• Invalid sort column or order value — return 400, or fall back to the default sort?