goldbrain/.source-references/references-only/reference vault/memory/projects/unifi-access-dashboard.md

---
type: project
status: active
tags:
  - unifi
  - access
  - flask
  - docker
  - attendance
  - multi-tenant
  - mpm
updated: 2026-05-28
---

# UniFi Access Badge-In Dashboard

Repo: `F:\CODING\unifi-access-dashboard` · Unraid IP `10.2.0.11:8000` · Data at `/mnt/user/appdata/unifi-access-dashboard/`. See [[unraid-deployment]] for the deploy pipeline.

Flask + SQLite app that ingests UniFi Access `access.door.unlock` webhooks and shows a daily attendance table (first/latest badge per person, ON TIME / LATE vs cutoff).

## Current status

Multi-controller support shipped 2026-05-28. The app now supports adding N UniFi Access controllers from the UI (⚙ Controllers button); each controller's webhook is auto-registered via the Access API. Driven by a client engagement where one on-site server has LAN reach to multiple Access controllers on a meshed network and wants a unified attendance view.

## Architecture decisions

- **Storage model**: tables `controllers`, `badge_events` (with `controller_id`), `user_cache` (composite PK `(controller_id, actor_id)`). The same physical person on two controllers shows as two rows distinguished by the Source column — no cross-controller identity merge (out of scope).
- **Webhook URLs**: `/api/unifi-access/<controller_id>` per-controller. Legacy `/api/unifi-access` kept as alias that routes to the oldest controller, so pre-multi-controller installs keep working without re-registering.
- **Auth & encryption**: explicitly NOT added. Jason picked LAN-only trust + plaintext tokens in SQLite. Filesystem permissions on `./data/dashboard.db` are the only thing protecting API tokens. Any future external exposure must put a reverse proxy with auth in front.
- **Webhook auto-registration**: adding a controller via UI POSTs to `/api/v1/developer/webhooks/endpoints` on that controller and stores the returned `secret` + `id`. Removing a controller calls Access to delete the webhook.
- **`DASHBOARD_BASE_URL` env var**: optional override for the URL the dashboard tells controllers to call back to. Defaults to `request.host_url` (the browser's origin when admin adds a controller). Set this if the controller can't reach that address.

**Why:** these were active choices Jason made during the 2026-05-28 update &mdash; not omissions. Re-proposing auth or encryption needs new context, not a do-over.
**How to apply:** if future work touches admin endpoints or token storage, don't silently bolt on auth or encryption &mdash; flag it as a scope change first.

## Configuration model

- **Fresh installs**: leave `UNIFI_HOST`/`UNIFI_API_TOKEN`/`UNIFI_PORT`/`WEBHOOK_SECRET` blank in the Unraid template. Only set `TZ` and the `/data` mapping. Add every controller via the UI &mdash; that path auto-registers webhooks.
- **Upgrades from single-controller installs**: leave the env vars alone. Migration on first boot seeds a "Default" controller from them and backfills existing `badge_events` rows. After verifying the UI shows Default, the env vars are dead weight and can be removed, but keeping them is harmless and defensive (re-seeds if `./data` is wiped).
- **Gotcha**: a seeded Default controller has no `webhook_id` stored (the webhook was registered manually before the multi-controller refactor). If someone removes Default in the UI and re-adds via the form, the OLD webhook on the Access side is not deleted (we don't know its id) and the NEW one is added on top &mdash; every badge event arrives twice until the old webhook is manually deleted on the Access controller.

## Operational details

- User-cache auto-sync runs hourly (`scheduler.add_job(sync_all_controllers, "interval", hours=1)`). Badge events themselves are real-time via webhook &mdash; this interval only affects how fast renamed users show new display names.
- HMAC-SHA256 signature verification is per-controller (each controller's `webhook_secret` is enforced on its own endpoint). A controller with an empty stored secret accepts unsigned posts &mdash; intentional for LAN-trust mode.
- Cutoff comparison is string-based on `HH:MM:SS`; cutoff input is sanitized to `HH:MM` and compared as `<= "{cutoff}:59"`. Don't change this to time-object parsing without verifying the SQL still groups correctly.

---

## Update 2026-05-28 (pm): tenant filtering + identity merging

Same-day follow-up to the multi-controller ship. Two related features added together. The earlier "Architecture decisions" note that cross-controller identity merging was out of scope is **superseded** — it's now in scope and shipped.

### Tenant filtering

- New column `user_cache.filtered INTEGER NOT NULL DEFAULT 0`. Additive migration via `ALTER TABLE` for existing DBs.
- Sync code at `app.py:194-203` already only updates `full_name`/`updated_at` on conflict, so the filter flag survives every hourly sync naturally — no defensive logic needed.
- `/api/first-badge-status` excludes filtered actors by default; pass `?include_filtered=1` to include them (UI uses this for the "Show filtered" toggle that dims filtered rows and tags them with a FILTERED pill).
- Webhook ingest is **unchanged** — filtering is purely display-time. Unfiltering instantly restores history with no gaps.
- New endpoints: `GET /api/users`, `PATCH /api/users/<controller_id>/<actor_id>` (`{filtered: bool}`). The PATCH upserts a placeholder cache row if the actor isn't yet known so the flag has somewhere to live (covers the "Unknown user" case).

### Identity merging across controllers

Driver: same physical person on two controllers gets two UUIDs, so badging into Controller A at 8:45 and Controller B at 9:15 produces one ON TIME row + one LATE row even though arrival was 8:45. Merging fixes this.

**Schema** — two new tables, both purely additive:
```sql
CREATE TABLE persons (
    id           TEXT PRIMARY KEY,
    display_name TEXT NOT NULL,
    filtered     INTEGER NOT NULL DEFAULT 0,
    created_at   TEXT NOT NULL
);
CREATE TABLE person_members (
    person_id     TEXT NOT NULL,
    controller_id TEXT NOT NULL,
    actor_id      TEXT NOT NULL,
    PRIMARY KEY (controller_id, actor_id),  -- one actor → one person
    FOREIGN KEY (person_id) REFERENCES persons(id) ON DELETE CASCADE
);
```

**Why two tables, not a `merge_group` column on `user_cache`:** chosen for headroom. `user_cache` is a sync cache (overwritten from UniFi); `persons` is the editorial layer where the user owns display name, filter flag, and any future per-person fields (notes, department, photo). Picked Option A from the design brainstorm; rejected Options B (shared group_id column) and C (self-referential `merged_into`).

**Query change** in `/api/first-badge-status`: groups by `COALESCE(pm.person_id, b.controller_id || '|' || b.actor_id)` so unmerged rows still group by `(controller, actor)`. Sources column becomes a `GROUP_CONCAT(DISTINCT c.name)` list. Filter promotes via `COALESCE(p.filtered, u.filtered, 0)`.

**Hide button dispatch**: if the row is merged, UI calls `PATCH /api/persons/<id>` with `{filtered: bool}`; if not, it calls `PATCH /api/users/<cid>/<aid>` (old behavior).

**Filter-on-unmerge decision**: when a member is split off from a merged person, the member returns **unfiltered**. Person-level filter dies with the merge. Picked for simpler mental model — re-hide individually if needed. Alternative (inherit filter on split) was rejected as surprising.

**Auto-suggestions** (`GET /api/persons/suggestions`): groups `user_cache` rows by exact-name match (case-insensitive, trimmed, excluding "User xxxxx" placeholders) where the same name spans ≥2 distinct controllers and none of the members are already part of a person. UI shows these at the top of the People modal with a one-click confirm per suggestion — never auto-applied.

**UI**: per-row Merge button (becomes "Manage" on already-merged rows, opens the People modal scrolled to that person); a new "👥 People" header button opens a modal with suggested merges + management actions (Rename, Split off, Dissolve). Merged rows in the attendance table get multiple Source chips and a "MERGED" pill.

### New endpoints (full list)

- `GET /api/users` — list cached actors with filter flag
- `PATCH /api/users/<cid>/<aid>` — hide/unhide one actor
- `GET /api/persons` — list merged people with members
- `POST /api/persons` — create with `{display_name, members:[{controller_id, actor_id},...]}`
- `PATCH /api/persons/<id>` — rename or toggle filtered
- `DELETE /api/persons/<id>` — dissolve
- `POST /api/persons/<id>/members` — add a member
- `DELETE /api/persons/<id>/members/<cid>/<aid>` — split off (auto-dissolves on last member)
- `GET /api/persons/suggestions` — exact-name matches across controllers

### Sharp edges to remember

- **Source chip is now a list per row.** Anything that consumed `row.source` as a single string still works (we kept the first chip there for backwards compat), but the new field is `row.sources` (array) plus `row.merged` (bool).
- **Hide button on a merged row toggles `persons.filtered`**, not member-level flags. Splitting a member off later returns it unfiltered by design.
- **Merge can't span 3+ identities in one shot from the inline picker** — only pairwise. To merge a third controller into an existing person, use the People modal → that person → add member. (Or split + remerge.)
- **Webhook ingest is still untouched.** Both filtering and merging are display-time only. The DB never loses an event; unmerging/unfiltering always restores complete history.

**Repo path note:** Jason was working from `D:\REMOTE CODING\unifi-access-dashboard` this session, not the `F:\CODING\` path recorded at the top of this file. Not changing the canonical path automatically — flag if this is a permanent move.

### Repo path clarification (per Jason, 2026-05-28)

The earlier note about working from `D:\REMOTE CODING\unifi-access-dashboard` vs the canonical `F:\CODING\` is **expected** — this repo is checked out on multiple machines with different folder structures. Don't treat divergent local paths as a red flag; the canonical reference is the Gitea repo and the deployed container, not any one machine's checkout location.