Skip to content

API contract update & DB query mechanism to fetch Device Stats

Jump to bottom
ShradhaGupta31 edited this page Aug 18, 2026 · 1 revision

Goal: Return activatedCount and discoveredCount (alongside the existing totalCount) from the server so they are:

  • Consistent with totalCount (which is already computed server-side).
  • Correct across all pages — today the UI computes activated/discovered by filtering only the current page of devices, so the numbers are wrong past page 1.
  • Available to any consumer — headless/API/scripts, not just the web UI.

Counting rules:

  • activatedCount — devices whose currentMode is set and is not "not activated".
  • discoveredCount — devices flagged discovered = true AND not activated (an activated device drops out of the "discovered" bucket).

Related UI PR: device-management-toolkit/sample-web-ui#3417 — the web UI change that consumes these counts.

How the ALL (total) count is fetched today

The total device count is already computed server-side and exposed two ways. Both are backed by the same GetCount repository method (a plain SELECT COUNT(*) ... WHERE tenantid = ?).

1. Dedicated stats endpoint (currently returns only the total):

curl -s http://localhost:8181/api/v1/devices/stats \
  -H "Authorization: Bearer $TOKEN"
// 200 OK
{
  "totalCount": 42,
  "connectedCount": 0,      // declared in the DTO but NOT populated today
  "disconnectedCount": 0    // declared in the DTO but NOT populated today
}

2. Device list with the OData $count flag (returns the count alongside the page of data):

curl -s "http://localhost:8181/api/v1/devices?\$top=25&\$skip=0&\$count=true" \
  -H "Authorization: Bearer $TOKEN"
// 200 OK
{
  "totalCount": 42,
  "data": [ /* ...page of devices... */ ]
}

$top / $skip control paging; $count=true asks for the total. This is the call the web UI uses to render the "All (N)" tab today. Auth is a JWT bearer token from POST /api/v1/authorize (skipped when auth.disabled is set for local single-user runs).

Background: where the data lives today

The devices table stores most device telemetry inside a single JSON string column called deviceinfo. The activation/discovery fields we need (currentMode, discovered) exist only inside that JSON blob — they are not queryable columns.

Current devices table schema

CREATE TABLE IF NOT EXISTS devices
(
    guid             TEXT NOT NULL,
    tags             TEXT,
    hostname         TEXT,
    mpsinstance      TEXT,
    connectionstatus BOOLEAN NOT NULL,
    mpsusername      TEXT,
    tenantid         TEXT NOT NULL,
    friendlyname     TEXT,
    dnssuffix        TEXT,
    lastconnected    TEXT,
    lastseen         TEXT,
    lastdisconnected TEXT,
    deviceinfo       TEXT,          -- <-- JSON blob; currentMode + discovered live in here
    username         TEXT,
    password         TEXT,
    usetls           BOOLEAN NOT NULL,
    allowselfsigned  BOOLEAN NOT NULL,
    certhash         TEXT,
    PRIMARY KEY (guid, tenantid),
    UNIQUE (guid)
);

Shape of the deviceinfo JSON blob (relevant fields)

{
  "fwVersion": "...",
  "discovered": true,            // <-- boolean we need
  "firstDiscovered": "2026-...",
  "currentMode": "not activated" // <-- string we need ("" | "not activated" | admin/client control modes)
  // ...many other fields
}

In Go this blob is stored as an opaque string on the entity and only becomes typed after the DTO layer unmarshals it:

// entity (stored form) — both SQL and Mongo
DeviceInfo string `bson:"deviceinfo"`

// dto (after unmarshal)
type DeviceInfo struct {
    Discovered  *bool  `json:"discovered,omitempty"`
    CurrentMode string `json:"currentMode"`
    // ...
}

Why this matters: the "write-once query" design

The SQL layer (internal/usecase/sqldb) is written once and runs against both Postgres and SQLite via the squirrel query builder + Go's database/sql. The DB is chosen at startup by connection URL (postgres:// → pgx driver, otherwise embedded SQLite). MongoDB is a separate implementation in internal/usecase/nosqldb/mongo.

So any solution must work for three backends across two code packages:

Backend Package Notes
Postgres internal/usecase/sqldb shares code with SQLite
SQLite internal/usecase/sqldb shares code with Postgres
MongoDB internal/usecase/nosqldb deviceinfo stored as a string field

Standard SQL (SELECT, COUNT, CASE, WHERE) is portable across Postgres+SQLite. Engine-specific JSON functions are not.

Two separate concerns

The design has two orthogonal axes. Keeping them apart avoids conflating "what the API looks like" with "how the database answers it":

  1. API contract (Section 1) — how the counts are exposed to clients (the HTTP surface). Independent of which database is mounted.
  2. DB fetch logic (Section 2) — how the activated/discovered determination is actually produced/stored in the repository layer.

You pick one option from each section; they compose (e.g. API Option 1 backed by DB Option A). A short combination guide follows Section 2.

Section 1 — API contract (how counts are exposed)

API Option 1 — Extend /devices with activated / discovered filters

Add server-side filters to the existing device-list endpoint — exactly the way it already filters by hostname / friendlyName today — so each tab issues its own filtered, paged request and gets its count for free from the existing $count=true response. This also fixes a real bug: today the tabs filter only the current page client-side, so pagination past page 1 is wrong per tab.

GET /api/v1/devices?$count=true                    # All        -> totalCount + rows
GET /api/v1/devices?activated=true&$count=true     # Activated  -> count + correct rows
GET /api/v1/devices?discovered=true&$count=true    # Discovered -> count + correct rows

How the tab counts work: each tab's total comes from the $count=true metadata of its own list call — no new counting code, and the rows shown are correctly the filtered, paginated set.

Pros

  • Fixes per-tab server-side pagination (current client-side page-only filter is buggy past page 1).
  • Per-tab count is free from the existing $count=true — no separate counting logic.
  • Gives API/headless consumers a real, reusable capability ("list all activated devices").

Cons

  • Populating all three tab labels at once costs 3 requests (one per filter), refetched on tab change — see API Option 2 for the one-call alternative.
  • The filter predicate must live in a SQL WHERE, so it depends on DB Option A (real columns) to paginate/count correctly and portably.

API Option 2 — Dedicated /devices/stats endpoint (all counts in one call)

Instead of three filtered list calls, return every count from a single endpoint. Best when you need all tab labels (All / Activated / Discovered, plus connected/disconnected) at once, or for dashboards / non-UI consumers.

GET /api/v1/devices/stats
# -> { totalCount, connectedCount, disconnectedCount, activatedCount, discoveredCount }

Pros

  • All counts in one round trip; three tab labels populated from a single call.
  • Counts are consistent with each other (one query); decoupled from paging.

Cons

  • Doesn't itself fix per-tab row pagination — it only returns numbers (pair with API Option 1's filter for correct rows).

API Options 1 and 2 are complementary, not exclusive. Recommended shape: use API Option 1's filter for correct per-tab rows + the active tab's count, and optionally expose API Option 2's /devices/stats to fill all three labels in one call.

Section 2 — DB fetch logic (how activated/discovered is evaluated)

Independent of the API shape above. This is where the currentMode / discovered determination actually comes from in the repository layer, and it must work for all three backends (Postgres, SQLite, Mongo).

DB Option A — Denormalized real columns

Promote the two fields out of the JSON blob into real, queryable columns, populated from deviceInfo on every insert/update. The blob stays the source of truth for the API; the columns are a queryable mirror. (The existing hostname / friendlyName filters already work on real columns via GetByColumn.)

Schema change:

ALTER TABLE devices ADD COLUMN currentmode TEXT;
ALTER TABLE devices ADD COLUMN discovered  BOOLEAN;

Resulting devices table (new columns highlighted at the bottom):

CREATE TABLE IF NOT EXISTS devices
(
    guid             TEXT NOT NULL,
    tags             TEXT,
    hostname         TEXT,
    mpsinstance      TEXT,
    connectionstatus BOOLEAN NOT NULL,
    mpsusername      TEXT,
    tenantid         TEXT NOT NULL,
    friendlyname     TEXT,
    dnssuffix        TEXT,
    lastconnected    TEXT,
    lastseen         TEXT,
    lastdisconnected TEXT,
    deviceinfo       TEXT,          -- JSON blob; remains the source of truth for the API
    username         TEXT,
    password         TEXT,
    usetls           BOOLEAN NOT NULL,
    allowselfsigned  BOOLEAN NOT NULL,
    certhash         TEXT,
    currentmode      TEXT,          -- NEW: queryable mirror of deviceinfo.currentMode
    discovered       BOOLEAN,       -- NEW: queryable mirror of deviceinfo.discovered
    PRIMARY KEY (guid, tenantid),
    UNIQUE (guid)
);

The two new columns are a queryable mirror of values already inside the deviceinfo JSON blob. They are nullable so existing rows migrate cleanly.

Pros

  • Portable standard SQL — no per-engine JSON functions; works on all three backends; Mongo uses real fields, not a string.
  • Indexable → scales to large fleets; O(1)-ish per query.
  • Enables both API options (filter + aggregate) from the same groundwork.

Cons / cost

  • Requires a migration + write-path sync (populate the columns from deviceInfo on insert/update) + backfill for existing rows (they read NULL until their next sync).

DB Option B — Query the JSON blob directly (NOT SUITABLE HERE)

Use each engine's native JSON functions to read fields out of the deviceinfo string with no schema change.

-- Postgres
COUNT(CASE WHEN (deviceinfo::jsonb ->> 'currentMode') <> 'not activated' THEN 1 END)
-- SQLite (different syntax!)
COUNT(CASE WHEN json_extract(deviceinfo, '$.currentMode') <> 'not activated' THEN 1 END)

Why it does not fit this codebase

  • Not portable: Postgres (::jsonb ->>) and SQLite (json_extract) use different syntax, but they share one code path. This forces per-engine branching — exactly what the current design avoids.
  • Mongo can't do it: deviceinfo is stored as a string, not a sub-document, so there is no native accessor for inner fields. It would need in-DB JS/$function parsing or a schema change — so this technique can't give one consistent implementation across all three backends.
  • No index → full scan on every stats call (unless per-engine expression indexes are added, defeating the "no migration" appeal).

DB Option C — Compute in Go (simple fallback)

Load devices, unmarshal deviceInfo (reusing existing entityToDTO), and tally in a loop. Backend-agnostic — identical logic for all three DBs.

for _, d := range devices {
    activated := d.DeviceInfo != nil &&
        d.DeviceInfo.CurrentMode != "" &&
        d.DeviceInfo.CurrentMode != "not activated"
    switch {
    case activated:
        stats.ActivatedCount++
    case d.DeviceInfo != nil && d.DeviceInfo.Discovered != nil && *d.DeviceInfo.Discovered:
        stats.DiscoveredCount++
    }
}

Pros

  • Zero schema/migration change; no write-path change.
  • One implementation, identical across all three backends.
  • Trivial to match the exact rules and to unit-test.

Cons

  • O(n) load into memory per stats call — fine for typical fleets, not ideal for very large fleets or frequent polling.

Console Wiki

ADRs

Clone this wiki locally