⚡ Swarm Architecture

Buying Profile Explorer — Design

📅 Published August 12, 2026 ⏱️ 4 min read 🔬 Peer Verified

# Buying Profile Explorer — Design

Date: 2026-06-11 Status: Approved (brainstorm) — pending spec review Repo: lead-contagion-project (Lyréco tenant)

Goal

A browsable, paginated, sortable, account-filterable table of weekly buying profiles at account → user → product-line grain, grouped and indented. Its own dashboard panel; the existing single-account Buying Profile panel stays as the focused drill view.

Decisions (brainstorm)

  • Row = (account, user, product) line, grouped under user, under account.
  • Paginate by account, 20 accounts/page — groups are never split across pages.
  • Default account order = biggest buyers first (total window orders desc).
  • Own panel (not the static script report — pagination/sort/filter are interactive).

Layout

` ▸ Account 0060592578 — WINTER REFURBISHMENT LTD [cluster: Mixed Office Micro Solos] (3 users · 142 orders) ▸ jane@acme.co.uk (Jane Doe) 1 PK6 Lyreco Note 75x125 8 orders 24 units 2 Rigid Box File F/scap 5 orders 10 units ▸ tom@acme.co.uk 1 Brother LC-3219 Cyan 4 orders 4 units `

Account header carries name + cluster name + (n users · total orders). User sub-header carries email/name. Line rows: rank · product · orders · units.

Panel & mechanics

New route GET /panel/buying-profile-explorer (Customers tab), server-side HTMX, mirroring the existing /panel/buying-profile pattern. Query params:

  • q — account-number filter (exact, padded-or-bare). When set, the view
collapses to that one account's tree.
  • page — 0-based account page (20/page).
  • asort — account ordering: orders (default) | units | users.
  • lsort — line ordering within a user: rank (default) | orders | units.
  • dir — desc (default) | asc.
  • country — GB (default) | FR.

Data flow (two queries + Python grouping): 1. Page the accounts. Aggregate user-level buying_profile_weekly (scope_type='user') up to account via ecom_users, for the latest snapshot: per account → SUM(orders_count), SUM(units_qty), COUNT(DISTINCT ecom_user_id). Order by asort/dir, LIMIT 20 OFFSET page*20. (If q set, filter to that account instead of paging.) Returns the page's account_numbers + the totals. 2. Fetch the lines for those accounts: buying_profile_weekly (scope_type ='user') ⨝ ecom_users (account#, email, name) ⨝ ecom_products (label) for account_number = ANY(%s), latest snapshot. Attach each account's cluster name via account_firmographic_community ⨝ firmographic_community_labels. 3. Group in Python: account → users → lines, ordered by asort (accounts, preserving the SQL page order) then user (by their summed orders desc) then lines by lsort/dir.

All tables are Postgres — single-DB, no cross-DB join.

Note on totals: buying_profile_weekly stores each user's Top-N lines (not every line they bought), so account "total orders" here = sum over stored top products — a faithful proxy for ranking buyers, not a full ledger. Stated in the panel footer so it isn't mistaken for total account revenue.

Columns

account# · account name · cluster (account header) → user (email / name) (sub-header) → rank · product · orders · units (line rows). Sortable columns: account header sort (orders/units/users) via small controls; line columns (orders/units/rank) via clickable headers.

Components / files

| File | Responsibility | |---|---| | core/table_params.py | Pure parse_explorer_params(raw: dict) -> dict — clamp page≥0, whitelist asort/lsort/dir to safe values (defaults on bad input), pass q/country through. Prevents ORDER BY / OFFSET injection. | | tests/test_table_params.py | Hermetic tests for the param parser. | | dashboard/app.py | buying_profile_explorer_panel route — the two queries + grouping, using the parsed params. | | dashboard/templates/_buying_profile_explorer.html | Grouped/indented table, filter box, sort controls, prev/next pager. | | dashboard/templates/home.html | Panel container in the Customers tab. |

Error handling

  • Bad params (non-int page, unknown sort) → defaults via parse_explorer_params
(never reach SQL as raw strings).
  • q with no match → empty-state message ("no account …").
  • No snapshot yet → hint message.
  • Beyond last page → empty page (pager disables Next when fewer than 20 returned).

Testing

  • Hermetic: parse_explorer_params (clamping, whitelist, defaults) — the only
pure logic and the injection-prevention boundary.
  • Live: render verified in the dashboard (grouping, pagination, sort, filter)
after restart — UI behavior isn't unit-tested.

YAGNI cuts

No CSV export (the static report already exports), no free-text product search, no multi-account multiselect, no per-line drill beyond the existing detail panel, GB default (country-parameterized for FR later).