🔧 Fix .env files

.env.dev

@@ -51,6 +51,7 @@ PAYLOAD_TIME_KEYS=timestamp,time,ts
 API_HOST_PORT=18080
 MQTT_HOST_PORT=2883
 PGADMIN_HOST_PORT=8092
+UI_HOST_PORT=18090
 
 # API: hard cap for the limit query parameter.
 API_MAX_LIMIT=10000

.env.example

@@ -39,6 +39,9 @@ INJECT_DEVICE_KEY=device
 # table has rows (and never on a migrated database).
 BOOTSTRAP_APP_TOKEN=
 
+# Label for the seeded bootstrap row (informational only).
+BOOTSTRAP_APP_NAME=bootstrap
+
 # First admin for the management UI. On the FIRST deployment the users
 # table is empty; on the first login attempt the api seeds this account
 # (role admin, scrypt-hashed password). Set a strong password, create real
@@ -50,8 +53,6 @@ BOOTSTRAP_ADMIN_PASSWORD=some_random_password
 # where nginx proxies /api to (service name:port inside the compose network).
 UI_HOST_PORT=18090
 API_UPSTREAM=api:8000
-# Label for the seeded bootstrap row (informational only).
-BOOTSTRAP_APP_NAME=bootstrap
 
 # Comma-separated payload keys searched for a device timestamp (first
 # parseable one wins). Accepts ISO 8601 strings, epoch seconds or epoch

README.md

@@ -240,7 +240,23 @@ Example response:
 - [x] Separate payload relative settings from auth settings. Sub-sections in menu? (→ sidebar grouped into **Data** / **Access** sections)
 - [x] Hide features from users not able to use them based on authorization. (→ auth-management links hidden for read-only `user` role; route guard blocks direct URL access; the API enforces 403 server-side regardless)
 - [x] Fix dashboard view, message expanding outside table. Make it look like the table in messages view. (→ payload cell now uses the same collapsed `<details>` + pretty-printed JSON + attributes block as the Messages view)
-- [ ] Make all tables able to sort on header. 
-- [ ] Remove clickable device links in tables for users that is not allowed to see the device view, or does not have enought authorization to be able to change anything. CLicking as a user ends up on a page with only some text.
+- [x] Update tables displaying messages so they also contain a column with the imei, and not only device. (→ IMEI column added to Dashboard + Messages; per the IMEI-first sweep, **links moved to the IMEI** — the device name is plain text everywhere, on the Devices list too)
+- [x] Add possbility to sort and filter on table headers. Filtering should be able to be performed with free text and by selecting/deselecting values that occures or might occure in each column. (→ click headers to sort (asc → desc → default); funnel icons open per-column filters with free-text search and value checkboxes. Devices/Users filter client-side; **Messages filters server-side** via `sort`, repeatable `imei`/`device`, and `imei_like`/`device_like` params — facet values come from the full device list, so filtering is correct across all pages)
+- [x] Remove clickable device links in tables for users that is not allowed to see the device view, or does not have enought authorization to be able to change anything. CLicking as a user ends up on a page with only some text. (→ resolved by the IMEI-first sweep: names are no longer links for anyone; the IMEI link leads to a legitimate read-only view for every role)
 - [ ] Change datetime localization. How is it handled now, from browser or static? I'm in sweden and not following swedish rules on layout.
 - [ ] Make it possible to manipulate a messages payload.
+- [ ] Make it possible, for superusers and admins, to manually inject new messages using json from an "insert message" view. It should allow array of json objects as well as a single message json object.
+- [ ] Change SESSION_TTL_DAYS to SESSION_TTL_MINUTES. Make SESSION_TTL_MINUTES being read from environmental variable. Default to 5 minutes.
+- [ ] Add user information. Name, mail address, localization
+- [ ] Add user settings view
+- [ ] Add audit logging. Audit log entries will contain actor(userid+username), timestamp, action type (login, database change etc.), target outcome(successful, failed, or generating a warning), source information (ip address, device ID, and/or location from which the action orginated) and before/after state.
+- [ ] Add view for viewing audit log. Place it with tokens and users under ACCESS menu section.
+- [ ] Change messages in tables to display information about the entire messege, not only the payload.
+- [X] Fix facet counts on Messages show 1 per device.
+- [ ] Add ability to select how many rows to show in tables except the last messages table in the dashboard. Make it possible to select 10, 25, 50, 100, 200 or manual entry. Default to 25.
+- [ ] Add operations button in messages view for import/export or deletion of selected messages.
+- [ ] Export option will export selected messages as they are stored in the database, without added parameters/features.
+- [ ] Delete option will, after warning and user confirmation, delete the selected messages. Or will they be moved to retention table and held for 30 days?
+- [ ] Add ability to export/import original messages from message view. Possible to select all with one header selection checkbox.
+- [ ] Add payload timestamp as column in messages view. The from/to field would be able to select if it is received or payload timestamp that is used for framing the loaded messages.
+- [ ] Add table navigastion buttons for jumping to first or last page. Add manual navigation textfield/dropdown to all tables exept dashboard last messages table.

api/app.py

@@ -1472,10 +1472,60 @@ def create_app(
         count = row[0] if row is not None else 0
         return {"count": count}
 
+    @app.get(
+        "/messages/facet-counts", tags=["meta"], dependencies=[Depends(authenticate)]
+    )
+    def message_facet_counts() -> dict[str, Any]:
+        """Occurrence counts per IMEI and per device name over ALL messages
+        (not just a page), for the Messages view's filter popovers. Values
+        with zero messages still appear when a settings row exists (merged
+        from imei_map), so the facets list devices that "might occur"."""
+        try:
+            conn = psycopg.connect(config.dsn)
+            try:
+                with conn.cursor() as cur:
+                    cur.execute(
+                        "SELECT imei, count(*) FROM mqtt_messages GROUP BY imei"
+                    )
+                    imei_counts = {row[0]: row[1] for row in cur.fetchall()}
+                    cur.execute(
+                        "SELECT device, count(*) FROM mqtt_messages GROUP BY device"
+                    )
+                    device_counts = {row[0]: row[1] for row in cur.fetchall()}
+                    # devices that exist but have no messages yet: count 0
+                    cur.execute("SELECT imei, meta->>'device' FROM imei_map")
+                    for imei, name in cur.fetchall():
+                        imei_counts.setdefault(imei, 0)
+                        if name:
+                            device_counts.setdefault(name, 0)
+            finally:
+                conn.close()
+        except psycopg.Error as exc:
+            raise _db_unavailable(exc) from exc
+        return {"imei": imei_counts, "device": device_counts}
+
     @app.get("/messages", tags=["data"], dependencies=[Depends(authenticate)])
     def list_messages(
-        imei: str | None = Query(None, description="Filter by IMEI (15 digits)"),
-        device: str | None = Query(None, description="Filter by device name"),
+        imei: list[str] | None = Query(
+            None, description="Filter by IMEI (repeatable for a multi-select)"
+        ),
+        device: list[str] | None = Query(
+            None, description="Filter by device name (repeatable for a multi-select)"
+        ),
+        imei_like: str | None = Query(
+            None, description="Case-insensitive contains filter on IMEI"
+        ),
+        device_like: str | None = Query(
+            None, description="Case-insensitive contains filter on device name"
+        ),
+        sort: str | None = Query(
+            None,
+            description=(
+                "Sort column: id, received_at, imei, device or payload_time; "
+                "prefix with - for descending (e.g. -received_at). Default: "
+                "payload_time DESC when framing on it, else id DESC (newest first)."
+            ),
+        ),
         from_time: datetime | None = Query(
             None,
             alias="from",
@@ -1532,12 +1582,23 @@ def create_app(
         # the query joins imei_map for the read-time settings merge.
         conditions: list[sql.Composable] = []
         params: list[Any] = []
+        # Repeatable exact-match filters: ?imei=a&imei=b -> = ANY(...).
+        # A single value keeps working (list of one).
         if imei:
-            conditions.append(sql.SQL("m.imei = %s"))
+            conditions.append(sql.SQL("m.imei = ANY(%s)"))
             params.append(imei)
         if device:
-            conditions.append(sql.SQL("m.device = %s"))
+            conditions.append(sql.SQL("m.device = ANY(%s)"))
             params.append(device)
+        # Free-text contains filters for the column filter boxes. The whole
+        # pattern is ONE parameter - psycopg forbids literal % inside SQL
+        # strings (placeholder parsing).
+        if imei_like:
+            conditions.append(sql.SQL("m.imei ILIKE %s"))
+            params.append(f"%{imei_like}%")
+        if device_like:
+            conditions.append(sql.SQL("m.device ILIKE %s"))
+            params.append(f"%{device_like}%")
         if from_time is not None:
             conditions.append(sql.SQL("m.received_at >= %s"))
             params.append(_as_utc(from_time))
@@ -1555,9 +1616,35 @@ def create_app(
         if conditions:
             where = sql.SQL(" WHERE ") + sql.SQL(" AND ").join(conditions)
 
-        # Order by device timestamp when framing on it (NULLS LAST matches the
-        # idx_mqtt_messages_ptime index); otherwise newest-first by insert id.
-        if payload_from_time is not None or payload_to_time is not None:
+        # Sort: explicit ?sort= wins; default stays "payload_time DESC when
+        # framing on it, else id DESC". The whitelist maps to pre-built sql
+        # fragments - user input is never interpolated into the ORDER BY.
+        SORTABLE = {
+            "id": sql.SQL("m.id"),
+            "received_at": sql.SQL("m.received_at"),
+            "imei": sql.SQL("m.imei"),
+            "device": sql.SQL("m.device"),
+            "payload_time": sql.SQL("m.payload_time"),
+        }
+        if sort is not None:
+            desc = sort.startswith("-")
+            name = sort[1:] if desc else sort
+            column = SORTABLE.get(name)
+            if column is None:
+                raise HTTPException(
+                    status_code=status.HTTP_422_UNPROCESSABLE_ENTITY,
+                    detail=(
+                        f"unknown sort column {sort!r}; available: "
+                        + ", ".join(SORTABLE)
+                    ),
+                )
+            order_by = (
+                sql.SQL(" ORDER BY ")
+                + column
+                + (sql.SQL(" DESC") if desc else sql.SQL(""))
+                + sql.SQL(" NULLS LAST LIMIT %s OFFSET %s")
+            )
+        elif payload_from_time is not None or payload_to_time is not None:
             order_by = sql.SQL(" ORDER BY m.payload_time DESC NULLS LAST LIMIT %s OFFSET %s")
         else:
             order_by = sql.SQL(" ORDER BY m.id DESC LIMIT %s OFFSET %s")

docker-compose.yml

@@ -137,7 +137,7 @@ services:
     ports:
       - "${UI_HOST_PORT:-18090}:80"
     healthcheck:
-      test: ["CMD", "wget", "-O", "-", "http://localhost/"]
+      test: ["CMD", "wget", "-O", "-", "http://127.0.0.1:80/"]
       interval: 30s
       timeout: 10s
       retries: 3

frontend/src/components/TableHeader.vue

@@ -0,0 +1,146 @@
+<script setup lang="ts">
+// Sortable + filterable table header: click the label to cycle
+// asc -> desc -> default; the funnel opens a popover with free-text search
+// and (for facet columns) checkboxes over the occurring values.
+import { onBeforeUnmount, onMounted } from 'vue'
+import { ArrowDown, ArrowUp, ArrowUpDown, ListFilter, X } from 'lucide-vue-next'
+
+const props = defineProps<{
+  label: string
+  name: string
+  // sorting (optional): current state + toggle
+  sortable?: boolean
+  sortKey?: string | null
+  sortDir?: 'asc' | 'desc'
+  onSort?: (name: string) => void
+  // filtering (optional): 'text', 'facet' or 'text+facet'
+  filterKind?: 'text' | 'facet' | 'text+facet'
+  filterText?: string
+  onFilterText?: (name: string, text: string) => void
+  filterChecked?: Set<string>
+  onFilterCheck?: (name: string, value: string, checked: boolean) => void
+  // occurring values with counts (facets); may be filtered by the text box
+  facetValues?: { value: string; count: number }[]
+  open?: boolean
+  onToggleOpen?: (name: string) => void
+}>()
+
+const emit = defineEmits<{
+  (e: 'update:open', name: string): void
+}>()
+
+function togglePopover() {
+  props.onToggleOpen?.(props.name)
+}
+
+function onCheckbox(value: string, event: Event) {
+  props.onFilterCheck?.(props.name, value, (event.target as HTMLInputElement).checked)
+}
+
+// close on outside click
+function outside(e: MouseEvent) {
+  const pop = document.getElementById(`filter-pop-${props.name}`)
+  const btn = document.getElementById(`filter-btn-${props.name}`)
+  if (pop && !pop.contains(e.target as Node) && btn && !btn.contains(e.target as Node)) {
+    if (props.open) props.onToggleOpen?.(props.name)
+  }
+}
+
+onMounted(() => document.addEventListener('click', outside))
+onBeforeUnmount(() => document.removeEventListener('click', outside))
+</script>
+
+<template>
+  <th style="position: relative">
+    <div class="row" style="gap: 0.3rem; flex-wrap: nowrap">
+      <span
+        v-if="sortable"
+        style="cursor: pointer; user-select: none"
+        @click="onSort?.(name)"
+      >
+        {{ label }}
+        <ArrowUp v-if="sortKey === name && sortDir === 'asc'" :size="13" />
+        <ArrowDown v-else-if="sortKey === name && sortDir === 'desc'" :size="13" />
+        <ArrowUpDown v-else :size="13" style="opacity: 0.4" />
+      </span>
+      <span v-else>{{ label }}</span>
+
+      <button
+        v-if="filterKind"
+        :id="`filter-btn-${name}`"
+        class="ghost th-filter-btn"
+        style="padding: 0.1rem 0.25rem"
+        :title="`Filter ${label}`"
+        @click.stop="togglePopover"
+      >
+        <ListFilter :size="13" />
+      </button>
+    </div>
+
+    <div v-if="open" :id="`filter-pop-${name}`" class="filter-pop">
+      <input
+        v-if="filterKind === 'text' || filterKind === 'text+facet'"
+        class="mono"
+        :value="filterText"
+        placeholder="contains..."
+        style="width: 100%; margin-bottom: 0.4rem"
+        @input="onFilterText?.(name, ($event.target as HTMLInputElement).value)"
+      />
+      <div
+        v-if="filterKind === 'facet' || filterKind === 'text+facet'"
+        class="facet-list"
+      >
+        <label
+          v-for="v in facetValues"
+          :key="v.value"
+          class="row facet-row"
+          style="gap: 0.4rem; flex-wrap: nowrap"
+        >
+          <input
+            type="checkbox"
+            style="width: auto"
+            :checked="filterChecked?.has(v.value) ?? false"
+            @change="onCheckbox(v.value, $event)"
+          />
+          <span class="mono" style="flex: 1">{{ v.value }}</span>
+          <span class="muted">{{ v.count }}</span>
+        </label>
+      </div>
+      <div class="row" style="justify-content: flex-end; margin-top: 0.4rem">
+        <button class="ghost" style="padding: 0.15rem 0.5rem" @click="onFilterText?.(name, '')">
+          <X :size="12" /> clear
+        </button>
+      </div>
+    </div>
+  </th>
+</template>
+
+<style scoped>
+.th-filter-btn {
+  color: var(--muted);
+}
+.filter-pop {
+  position: absolute;
+  top: calc(100% + 4px);
+  left: 0;
+  z-index: 30;
+  background: var(--card);
+  border: 1px solid var(--border);
+  border-radius: 8px;
+  box-shadow: var(--shadow-md);
+  padding: 0.6rem;
+  min-width: 14rem;
+  max-width: 20rem;
+  text-align: left;
+}
+.facet-list {
+  max-height: 14rem;
+  overflow-y: auto;
+  display: flex;
+  flex-direction: column;
+  gap: 0.25rem;
+}
+.facet-row {
+  font-size: 0.82rem;
+}
+</style>

frontend/src/composables/useTable.ts

@@ -0,0 +1,112 @@
+// Client-side table sorting + filtering, shared by the fully-loaded tables
+// (Devices, Users). The paginated Messages view talks to the API instead —
+// only the popover/header components are shared with it.
+//
+// A column is configured with:
+//   get:    row -> value
+//   sort:   'string' | 'number' (default 'string')
+//   filter: 'text' | 'facet' | 'text+facet' (default: no filtering)
+
+import { computed, reactive, ref, type Ref } from 'vue'
+
+export type FilterKind = 'text' | 'facet' | 'text+facet'
+
+export interface ColumnConfig<T> {
+  get: (row: T) => string | number
+  sort?: 'string' | 'number'
+  filter?: FilterKind
+}
+
+export interface ColumnState {
+  text: string
+  checked: Set<string>
+  open: boolean
+}
+
+export function useTable<T>(rows: Ref<T[]>, columns: Record<string, ColumnConfig<T>>) {
+  const state = reactive<Record<string, ColumnState>>({})
+  const sortKey = ref<string | null>(null)
+  const sortDir = ref<'asc' | 'desc'>('asc')
+
+  function col(name: string): ColumnState {
+    if (!state[name]) state[name] = { text: '', checked: new Set(), open: false }
+    return state[name]
+  }
+
+  function toggleSort(name: string): void {
+    if (sortKey.value === null || sortKey.value !== name) {
+      sortKey.value = name
+      sortDir.value = 'asc'
+    } else if (sortDir.value === 'asc') {
+      sortDir.value = 'desc'
+    } else {
+      sortKey.value = null // third click: back to default order
+    }
+  }
+
+  function distinctValues(name: string): { value: string; count: number }[] {
+    const cfg = columns[name]
+    const counts = new Map<string, number>()
+    for (const row of rows.value) {
+      const v = String(cfg.get(row))
+      counts.set(v, (counts.get(v) ?? 0) + 1)
+    }
+    return [...counts.entries()]
+      .map(([value, count]) => ({ value, count }))
+      .sort((a, b) => a.value.localeCompare(b.value))
+  }
+
+  function passesFilter(name: string, value: string): boolean {
+    const cfg = columns[name]
+    const st = state[name]
+    if (!cfg.filter || !st) return true
+    if (st.text && !value.toLowerCase().includes(st.text.toLowerCase())) return false
+    if (st.checked.size > 0 && !st.checked.has(value)) return false
+    return true
+  }
+
+  const sortedFilteredRows = computed(() => {
+    let out = rows.value.filter((row) => {
+      for (const name of Object.keys(columns)) {
+        const cfg = columns[name]
+        if (!cfg.filter) continue
+        if (!passesFilter(name, String(cfg.get(row)))) return false
+      }
+      return true
+    })
+    if (sortKey.value !== null) {
+      const cfg = columns[sortKey.value]
+      const dir = sortDir.value === 'asc' ? 1 : -1
+      out = [...out].sort((a, b) => {
+        const va = cfg.get(a)
+        const vb = cfg.get(b)
+        if (cfg.sort === 'number') return ((va as number) - (vb as number)) * dir
+        return String(va).localeCompare(String(vb)) * dir
+      })
+    }
+    return out
+  })
+
+  function clearColumn(name: string): void {
+    const st = state[name]
+    if (!st) return
+    st.text = ''
+    st.checked = new Set()
+  }
+
+  function activeFilterCount(): number {
+    return Object.values(state).filter((st) => st.text !== '' || st.checked.size > 0).length
+  }
+
+  return {
+    col,
+    state,
+    sortKey,
+    sortDir,
+    toggleSort,
+    distinctValues,
+    sortedFilteredRows,
+    clearColumn,
+    activeFilterCount,
+  }
+}

frontend/src/views/Dashboard.vue

@@ -60,6 +60,7 @@ onMounted(async () => {
       <thead>
         <tr>
           <th>Received</th>
+          <th>IMEI</th>
           <th>Device</th>
           <th>Payload + attributes</th>
         </tr>
@@ -67,9 +68,10 @@ onMounted(async () => {
       <tbody>
         <tr v-for="m in latest" :key="m.id">
           <td class="mono">{{ formatTime(m.received_at) }}</td>
-          <td>
-            <router-link :to="`/devices/${m.imei}`">{{ m.device }}</router-link>
+          <td class="mono">
+            <router-link :to="`/devices/${m.imei}`">{{ m.imei }}</router-link>
           </td>
+          <td>{{ m.device }}</td>
           <td>
             <details>
               <summary class="muted">payload</summary>

frontend/src/views/DeviceDetail.vue

@@ -208,7 +208,10 @@ onMounted(async () => {
 <template>
   <h1>
     <router-link to="/devices">Devices</router-link> /
-    {{ device?.device ?? imei }}
+    <span class="mono">{{ imei }}</span>
+    <span v-if="device?.device" class="muted" style="font-weight: 400">
+      · {{ device.device }}
+    </span>
   </h1>
 
   <h2>Settings</h2>

frontend/src/views/Devices.vue

@@ -3,6 +3,8 @@ import { onMounted, ref } from 'vue'
 import { Cpu, Plus, Trash2 } from 'lucide-vue-next'
 import { api } from '../api'
 import type { Device } from '../api'
+import { useTable } from '../composables/useTable'
+import TableHeader from '../components/TableHeader.vue'
 import { canEdit, showError, toast } from '../store'
 
 const devices = ref<Device[]>([])
@@ -10,6 +12,23 @@ const loaded = ref(false)
 const newImei = ref('')
 const newName = ref('')
 
+const table = useTable(devices, {
+  imei: { get: (d) => d.imei, filter: 'text' },
+  device: { get: (d) => d.device, filter: 'text+facet' },
+})
+
+function onFilterText(name: string, text: string) {
+  table.col(name).text = text
+}
+function onFilterCheck(name: string, value: string, checked: boolean) {
+  const st = table.col(name)
+  if (checked) st.checked.add(value)
+  else st.checked.delete(value)
+}
+function onToggleOpen(name: string) {
+  table.col(name).open = !table.col(name).open
+}
+
 async function load() {
   try {
     devices.value = await api<Device[]>('/api/devices')
@@ -69,17 +88,44 @@ async function removeDevice(d: Device) {
     <table v-if="devices.length">
       <thead>
         <tr>
-          <th>IMEI</th>
-          <th>Device</th>
+          <TableHeader
+            label="IMEI"
+            name="imei"
+            sortable
+            :sort-key="table.sortKey.value"
+            :sort-dir="table.sortDir.value"
+            :on-sort="table.toggleSort"
+            filter-kind="text"
+            :filter-text="table.col('imei').text"
+            :on-filter-text="onFilterText"
+            :open="table.col('imei').open"
+            :on-toggle-open="onToggleOpen"
+          />
+          <TableHeader
+            label="Device"
+            name="device"
+            sortable
+            :sort-key="table.sortKey.value"
+            :sort-dir="table.sortDir.value"
+            :on-sort="table.toggleSort"
+            filter-kind="text+facet"
+            :filter-text="table.col('device').text"
+            :on-filter-text="onFilterText"
+            :facet-values="table.distinctValues('device')"
+            :filter-checked="table.col('device').checked"
+            :on-filter-check="onFilterCheck"
+            :open="table.col('device').open"
+            :on-toggle-open="onToggleOpen"
+          />
           <th></th>
         </tr>
       </thead>
       <tbody>
-        <tr v-for="d in devices" :key="d.imei">
-          <td class="mono">{{ d.imei }}</td>
-          <td>
-            <router-link :to="`/devices/${d.imei}`">{{ d.device }}</router-link>
+        <tr v-for="d in table.sortedFilteredRows.value" :key="d.imei">
+          <td class="mono">
+            <router-link :to="`/devices/${d.imei}`">{{ d.imei }}</router-link>
           </td>
+          <td>{{ d.device }}</td>
           <td style="text-align: right">
             <button v-if="canEdit()" class="danger" @click="removeDevice(d)">
               <Trash2 :size="15" />

frontend/src/views/Messages.vue

@@ -2,18 +2,34 @@
 import { onMounted, ref } from 'vue'
 import { Inbox } from 'lucide-vue-next'
 import { api } from '../api'
-import type { MessagePage } from '../api'
+import type { Device, MessagePage } from '../api'
 import { formatTime } from '../format'
 import { showError } from '../store'
+import TableHeader from '../components/TableHeader.vue'
 
 const page = ref<MessagePage | null>(null)
 const loading = ref(false)
 const offset = ref(0)
 const limit = 25
 
-// filters
-const imei = ref('')
-const device = ref('')
+// the full device population for the facet lists (not just the loaded page)
+const devices = ref<Device[]>([])
+// real occurrence counts per value, from GET /messages/facet-counts
+const counts = ref<{ imei: Record<string, number>; device: Record<string, number> }>({
+  imei: {},
+  device: {},
+})
+
+// server-side sort + filters (mirrored into query params on load)
+const sort = ref('')
+const imeiChecked = ref(new Set<string>())
+const deviceChecked = ref(new Set<string>())
+const imeiText = ref('')
+const deviceText = ref('')
+const popOpen = ref<string | null>(null)
+let textTimer: number | undefined
+
+// date filters (the Received column's filter)
 const from = ref('')
 const to = ref('')
 
@@ -21,8 +37,11 @@ function buildQuery(): string {
   const params = new URLSearchParams()
   params.set('limit', String(limit))
   params.set('offset', String(offset.value))
-  if (imei.value.trim()) params.set('imei', imei.value.trim())
-  if (device.value.trim()) params.set('device', device.value.trim())
+  if (sort.value) params.set('sort', sort.value)
+  for (const v of imeiChecked.value) params.append('imei', v)
+  for (const v of deviceChecked.value) params.append('device', v)
+  if (imeiText.value.trim()) params.set('imei_like', imeiText.value.trim())
+  if (deviceText.value.trim()) params.set('device_like', deviceText.value.trim())
   if (from.value) params.set('from', new Date(from.value).toISOString())
   if (to.value) params.set('to', new Date(to.value).toISOString())
   return `/api/messages?${params.toString()}`
@@ -44,6 +63,47 @@ function applyFilters() {
   load()
 }
 
+function toggleSort(name: string) {
+  // cycle asc -> desc -> default for the given column key
+  const current = sort.value
+  if (current !== name && current !== `-${name}`) sort.value = name
+  else if (current === name) sort.value = `-${name}`
+  else sort.value = ''
+  applyFilters()
+}
+
+function sortState(name: string): '' | 'asc' | 'desc' {
+  if (sort.value === name) return 'asc'
+  if (sort.value === `-${name}`) return 'desc'
+  return ''
+}
+
+function facetValues(kind: 'imei' | 'device'): { value: string; count: number }[] {
+  // real occurrence counts; devices with zero messages keep count 0 so the
+  // list covers values that "might occur"
+  const map = counts.value[kind]
+  const entries = Object.entries(map)
+  // most-occurring first, then alphabetical
+  entries.sort((a, b) => b[1] - a[1] || a[0].localeCompare(b[0]))
+  return entries.map(([value, count]) => ({ value, count }))
+}
+
+function onCheck(kind: 'imei' | 'device', value: string, checked: boolean) {
+  const target = kind === 'imei' ? imeiChecked : deviceChecked
+  if (checked) target.value.add(value)
+  else target.value.delete(value)
+  applyFilters()
+}
+
+function onText(kind: 'imei' | 'device', text: string) {
+  if (kind === 'imei') imeiText.value = text
+  else deviceText.value = text
+  // server-side filtering: reload shortly after typing stops (the
+  // client-side tables filter reactively; this one needs a query)
+  window.clearTimeout(textTimer)
+  textTimer = window.setTimeout(applyFilters, 400)
+}
+
 function prev() {
   offset.value = Math.max(0, offset.value - limit)
   load()
@@ -54,20 +114,33 @@ function next() {
   load()
 }
 
-onMounted(load)
+onMounted(async () => {
+  load()
+  try {
+    devices.value = await api<Device[]>('/api/devices')
+  } catch {
+    // facet lists just stay empty; the text filters still work
+  }
+  try {
+    counts.value = await api<{
+      imei: Record<string, number>
+      device: Record<string, number>
+    }>('/api/messages/facet-counts')
+  } catch {
+    // counts stay zero; the facet lists still render from the devices
+  }
+})
 </script>
 
 <template>
   <h1>Messages</h1>
 
   <form class="card row" @submit.prevent="applyFilters">
-    <input v-model="imei" placeholder="IMEI" style="max-width: 12rem" />
-    <input v-model="device" placeholder="Device name" style="max-width: 12rem" />
     <label class="muted">from</label>
     <input v-model="from" type="datetime-local" style="max-width: 14rem" />
     <label class="muted">to</label>
     <input v-model="to" type="datetime-local" style="max-width: 14rem" />
-    <button class="primary" type="submit">Filter</button>
+    <button class="primary" type="submit">Apply</button>
   </form>
 
   <div class="card">
@@ -84,17 +157,56 @@ onMounted(load)
       <table>
         <thead>
           <tr>
-            <th>Received</th>
-            <th>Device</th>
+            <TableHeader
+              label="Received"
+              name="received_at"
+              sortable
+              :sort-key="sortState('received_at') ? 'received_at' : null"
+              :sort-dir="sortState('received_at') === 'desc' ? 'desc' : 'asc'"
+              :on-sort="() => toggleSort('received_at')"
+            />
+            <TableHeader
+              label="IMEI"
+              name="imei"
+              sortable
+              :sort-key="sortState('imei') ? 'imei' : null"
+              :sort-dir="sortState('imei') === 'desc' ? 'desc' : 'asc'"
+              :on-sort="() => toggleSort('imei')"
+              filter-kind="text+facet"
+              :filter-text="imeiText"
+              :on-filter-text="(_n, t) => onText('imei', t)"
+              :facet-values="facetValues('imei')"
+              :filter-checked="imeiChecked"
+              :on-filter-check="(n, v, c) => onCheck('imei', v, c)"
+              :open="popOpen === 'imei'"
+              :on-toggle-open="() => (popOpen = popOpen === 'imei' ? null : 'imei')"
+            />
+            <TableHeader
+              label="Device"
+              name="device"
+              sortable
+              :sort-key="sortState('device') ? 'device' : null"
+              :sort-dir="sortState('device') === 'desc' ? 'desc' : 'asc'"
+              :on-sort="() => toggleSort('device')"
+              filter-kind="text+facet"
+              :filter-text="deviceText"
+              :on-filter-text="(_n, t) => onText('device', t)"
+              :facet-values="facetValues('device')"
+              :filter-checked="deviceChecked"
+              :on-filter-check="(n, v, c) => onCheck('device', v, c)"
+              :open="popOpen === 'device'"
+              :on-toggle-open="() => (popOpen = popOpen === 'device' ? null : 'device')"
+            />
             <th>Payload + attributes</th>
           </tr>
         </thead>
         <tbody>
           <tr v-for="m in page.items" :key="m.id">
             <td class="mono">{{ formatTime(m.received_at) }}</td>
-            <td>
-              <router-link :to="`/devices/${m.imei}`">{{ m.device }}</router-link>
+            <td class="mono">
+              <router-link :to="`/devices/${m.imei}`">{{ m.imei }}</router-link>
             </td>
+            <td>{{ m.device }}</td>
             <td>
               <details>
                 <summary class="muted">payload</summary>

frontend/src/views/Users.vue

@@ -2,6 +2,8 @@
 import { onMounted, ref } from 'vue'
 import { Plus, Trash2, UserRound } from 'lucide-vue-next'
 import { api } from '../api'
+import { useTable } from '../composables/useTable'
+import TableHeader from '../components/TableHeader.vue'
 import { canEdit, isAdmin, showError, toast, ui } from '../store'
 
 const users = ref<{ username: string; role: string }[]>([])
@@ -11,6 +13,23 @@ const newPassword = ref('')
 // superusers may only create 'user' accounts (server-enforced); admins any.
 const newRole = ref('user')
 
+const table = useTable(users, {
+  username: { get: (u) => u.username, filter: 'text' },
+  role: { get: (u) => u.role, filter: 'facet' },
+})
+
+function onFilterText(name: string, text: string) {
+  table.col(name).text = text
+}
+function onFilterCheck(name: string, value: string, checked: boolean) {
+  const st = table.col(name)
+  if (checked) st.checked.add(value)
+  else st.checked.delete(value)
+}
+function onToggleOpen(name: string) {
+  table.col(name).open = !table.col(name).open
+}
+
 async function load() {
   try {
     users.value = await api<{ username: string; role: string }[]>('/api/users')
@@ -93,13 +112,38 @@ async function removeUser(u: { username: string; role: string }) {
     <table v-if="users.length">
       <thead>
         <tr>
-          <th>Username</th>
-          <th>Role</th>
+          <TableHeader
+            label="Username"
+            name="username"
+            sortable
+            :sort-key="table.sortKey.value"
+            :sort-dir="table.sortDir.value"
+            :on-sort="table.toggleSort"
+            filter-kind="text"
+            :filter-text="table.col('username').text"
+            :on-filter-text="onFilterText"
+            :open="table.col('username').open"
+            :on-toggle-open="onToggleOpen"
+          />
+          <TableHeader
+            label="Role"
+            name="role"
+            sortable
+            :sort-key="table.sortKey.value"
+            :sort-dir="table.sortDir.value"
+            :on-sort="table.toggleSort"
+            filter-kind="facet"
+            :facet-values="table.distinctValues('role')"
+            :filter-checked="table.col('role').checked"
+            :on-filter-check="onFilterCheck"
+            :open="table.col('role').open"
+            :on-toggle-open="onToggleOpen"
+          />
           <th></th>
         </tr>
       </thead>
       <tbody>
-        <tr v-for="u in users" :key="u.username">
+        <tr v-for="u in table.sortedFilteredRows.value" :key="u.username">
           <td class="mono">{{ u.username }}</td>
           <td>
             <span class="badge" :class="{ ok: u.role === 'admin' }">{{ u.role }}</span>