Tracking Insights Dashboard

Purpose

GET /api/scribe/insights/dashboard turns the raw tracking_sessions/tracking_events/tracking_contacts tables into the one read a workspace's whole reporting surface is built on: summary stats, channel-classified traffic sources, top pages, engagement distribution, a four-stage conversion funnel, a day/hour activity heatmap, a device breakdown, and period-over-period trend deltas, plus a short list of generated text insights (TrackingInsightsService.get_dashboard_insights, tracking_insights_service.py 57 to 85). The invariant this recipe's users actually depend on, and the one the current code does not fully hold (see Security & tenancy and Gotchas), is that every number in the response is scoped to the sites the caller may see; as measured at this commit that is true for five of the eight aggregates and false for the other two.

When to use

Read it before touching get_dashboard_insights, _get_summary_stats, _classify_channel, _get_traffic_sources, _get_top_pages, _get_engagement_distribution, _get_conversion_funnel, _get_activity_heatmap, _get_device_breakdown, _get_trends, or _generate_dashboard_insights_from_data in tracking_insights_service.py; before touching _compute_insights_report, warm_scribe_reports, or the GET /insights/dashboard route in tracking_router.py; and when someone asks "why does the dashboard show data from another site", "why is engagement/funnel data wrong for my site", "why didn't the insights cache warm for tenant X", or "what counts as Paid vs Organic traffic". Trigger phrases: "insights dashboard", "traffic sources", "conversion funnel", "activity heatmap", "engagement distribution", "scribe warm job", _k_scribe_insights, pending_reconcile_ref is NOT this recipe (that is billing-credits).

Not this recipe: GET /api/scribe/stats (_compute_stats_aggregate, tracking_router.py 1546, 1680 to 1710), the lighter live totals + recent-activity feed the same dashboard polls alongside /insights/dashboard, a separate compute path with its own cache key (_k_scribe_stats); GET /api/scribe/campaigns and GET /api/scribe/landing-pages (2644 to 2817), per-campaign and per-landing-page tables that reuse the same session/event tables but are not part of get_dashboard_insights's payload, cited here only because they sit in the same file section; the filtered ad-hoc query/funnel/segments surface (api/routers/scribe_query_router.py, mounted separately at /api/scribe with tag "Scribe Query"), out of scope entirely; GET /insights/lead/{email} and TrackingInsightsService.get_lead_insights (tracking_insights_service.py 509 to 1028: _analyze_lead_engagement, _analyze_behavior_patterns, _detect_intent_signals, _build_journey_summary, _generate_predictions, _generate_recommendations, _build_complete_timeline), the same service class's PER-LEAD insights, a distinct read path with its own workspace-scoping logic (tracking_router.py 2922 to 2971), a separate recipe; event ingestion itself (POST /api/scribe/event, /batch, /identify) and the visitor-identification/session-merge logic that populates tracking_contacts, both out of scope, this recipe only reads what those write.

Stack assumptions

At 62ba4fc: fastapi==0.118.2, synchronous handlers on sqlalchemy.orm.Session (SQLAlchemy==2.0.43), pydantic==2.12.0 (Query(...) validation on the route), redis==6.4.0 behind services/cache_service.py for the response cache, APScheduler==3.11.0 for the hourly warm job, psycopg2-binary==2.9.10, Postgres 15 in prod (docker-compose.prod.yml 9) / 14 in the test harness (docker-compose.test.yml 6) (all versions from backend/requirements.txt). _get_summary_stats's "unique visitors" count uses .distinct(TrackingSession.fingerprint) with a column argument, i.e. Postgres's SELECT DISTINCT ON (...), a Postgres-specific construct (tracking_insights_service.py 97 to 99); a rebuild on another database needs a different unique-count query. Foundations assumed: S4 (fastapi-project-layout) for the app and mount_router; S5 (auth-dependency-pattern) for get_current_user, JWT bearer/cookie/API-key, resolved in api/dependencies.py 187 to roughly 230; S6 (workspace-scoping) for require_site_id_or_admin/get_user_site_ids, which resolve a caller's visible site_ids through workspace membership, not through any column on the tracking tables themselves; S12 (scheduler-and-background-work) for the APScheduler singleton run_scribe_report_warm registers on.

Environment, core/config.py: SCRIBE_WARM_ENABLED (404, default True, gates the whole warm job inside run_scribe_report_warm and its own add_job call, scheduled_jobs.py 418, 550); SCRIBE_WARM_INTERVAL_MINUTES (405, default 60); SCRIBE_WARM_TTL_SECONDS (408, default 7800, ~2h10m); SCRIBE_WARM_DAYS (412, default [7, 30], the day-ranges warmed per site); REDIS_ENABLED (388, default True; _scribe_cached and the warm job both wrap their cache calls in bare try/except: pass, tracking_router.py 58 to 63 and 67 to 70, so a Redis outage degrades to "compute on every request", never a 5xx). Fail behaviour: the route itself has no dedicated exception handling beyond FastAPI's default and the require_site_id_or_admin scoping call, which never raises (S6); _scribe_cached's cache read/write failures are swallowed, compute_fn() (i.e. _compute_insights_report) is not.

Data model

No table is owned by this recipe; it reads three, all defined in models/tracking_model.py, all workspace_id-scoped at the row level but queried here by the denormalized site_id string column instead (see Security & tenancy for why that distinction matters):

tracking_sessions (                              -- __tablename__ "tracking_sessions", 63–113
  id                 uuid PRIMARY KEY,
  workspace_id       uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  site_id            varchar,                    -- denormalized; no FK to a sites table on this model
  fingerprint        varchar,                    -- device/browser hash, used for unique-visitor counting
  contact_id         uuid REFERENCES tracking_contacts(id),
  utm_source, utm_medium, utm_campaign, utm_content, utm_term  varchar,
  referrer, landing_page, fbclid, gclid, li_fat_id  varchar,
  is_internal        boolean DEFAULT false,       -- admin/test sessions excluded from most queries here
  first_seen, last_seen  timestamptz
)
tracking_events (                                -- 221–277
  id                 uuid PRIMARY KEY,
  workspace_id       uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  session_id         uuid NOT NULL REFERENCES tracking_sessions(id),
  contact_id         uuid REFERENCES tracking_contacts(id),
  event_type         varchar NOT NULL,            -- 'page_view', 'form_submit'/'form_submitted', 'heartbeat', ...
  event_data         jsonb,
  page_url, page_path, page_title  varchar,
  site_id            varchar,                     -- denormalized here too, same caveat
  created_at         timestamptz
)
tracking_contacts (                              -- 131–212
  id                 uuid PRIMARY KEY,
  workspace_id       uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  email              varchar,
  total_sessions, total_events, total_page_views  integer,
  first_seen, last_seen, identified_at  timestamptz
  -- NOTE: no site_id column at all, see Security & tenancy
)

Nothing is written by this recipe: get_dashboard_insights and every _get_* helper it calls are read-only queries. What is NOT stored anywhere in this response: a page's full URL query string beyond page_path/page_url as already captured; an IP address (sessions carry ip_hash, not read by this service); any raw cardholder- or credential-like value, this table has none.

API surface

Route	Auth	Tenancy	Params	Codes	Notes
GET /api/scribe/insights/dashboard (tracking_router.py 2890 to 2919, router = APIRouter(prefix="/api/scribe", ...) at 260)	Depends(get_current_user), JWT bearer/cookie/API-key (S5)	effective_site_id = require_site_id_or_admin(db, current_user, site_id) (2914); non-admin gets exactly the sites their active workspace memberships resolve to, never None	site_id: Optional[str] = None (query, a single site to narrow to, only honoured if it is already one of the caller's own - S6's own contract), days: int = Query(7, ge=1, le=365)	200 {"success": true, "period_days": int, "site_id": ..., summary, traffic_sources, top_pages, engagement_distribution, conversion_funnel, activity_heatmap, device_breakdown, trends, insights}; 401 no/invalid credentials (from get_current_user)	Reads through _scribe_cached(_k_scribe_insights(effective_site_id, days), lambda: _compute_insights_report(db, effective_site_id, days)) - a warm cache hit (TTL up to 7800s) or a cold compute on a 90-second lazy TTL (SCRIBE_CACHE_TTL = 90, line 46); no structured error envelope, a raised exception surfaces as FastAPI's default 500, this route does not use the _shared/error-envelope pattern
POST route: none - this is the only HTTP entrypoint; everything below is called from it or from the scheduler, not routed

The service and warm-path functions this route (and the scheduler) call:

Function	Where	Returns	Raises
TrackingInsightsService.get_dashboard_insights(site_id, days=30)	tracking_insights_service.py 57 to 85	the eight-key dict above minus the success/period_days/site_id wrapper the router adds	yes - no internal try/except, any query error propagates
_compute_insights_report(db, effective_site_id, days)	tracking_router.py 2824 to 2831	{"success": True, "period_days", "site_id", **insights}	yes, propagates
warm_scribe_reports(db)	tracking_router.py 2834 to 2887	{"warmed": int, "errored": int, "sites": int, "days": [...]}	no - per-site, per-day try/except (2867 to 2883), see Gotchas for what that catches in practice

Application logic

get_dashboard_insights(site_id, days=30) (57 to 85), in call order: computes since = utcnow() - timedelta(days=days) once (65); calls _get_summary_stats, _get_traffic_sources, _get_engagement_distribution, _get_conversion_funnel up front and reuses those results (68 to 71, the docstring's own "compute expensive results once"), then calls _get_top_pages, _get_activity_heatmap, _get_device_breakdown, _get_trends inline in the returned dict literal, and finally _generate_dashboard_insights_from_data(summary, traffic_sources, engagement_distribution, conversion_funnel) (73 to 85) which does no further querying, only reads the four already-computed dicts.

Per-aggregate, and specifically which ones apply the site_id scope the caller was resolved to, verified by reading every query body at this commit:

1. _get_summary_stats (87 to 176): filters TrackingSession.site_id.in_(site_id) when site_id is truthy (94), same for the events subquery (117 to 120). Returns total_sessions, unique_visitors (via the Postgres DISTINCT ON fingerprint query, 97 to 99, or total_sessions if that count is falsy), identified_contacts, total_events, page_views, form_submissions, identification_rate, avg_events_per_session, avg_session_duration/_formatted, bounce_rate (% of sessions with exactly one page_view). Scoped.
2. _classify_channel(source, medium, has_gclid=False, has_fbclid=False, has_li_fat_id=False) (178 to 206, @staticmethod): pure function, checked in this exact order - cpc/ppc/paid substring in medium, or any click id present → Paid; social substring in medium or source in {facebook, instagram, linkedin, twitter, tiktok} → Social; email substring in medium → Email; medium exactly referral, or a non-null source that is not direct → Referral; organic substring in medium → Organic; else → Direct. Called twice per request with two different signatures: once per-row over the top-10 source/medium groups with click-id args omitted (234, "don't let a few click IDs override" the group-level label), and reimplemented independently as a SQL CASE expression (251 to 272) for the per-session channel totals - the SQL version is not a call into _classify_channel, it is a hand-duplicated copy of the same rules, so the two can drift (see Gotchas).
3. _get_traffic_sources (208 to 287): sources (94 to 227) top-10 (utm_source, utm_medium) groups by session count, each labelled via _classify_channel; channels (243 to 282) per-session channel totals via the duplicated SQL CASE. Both filter site_id when given. Scoped.
4. _get_top_pages (289 to 306): top-10 page_path by page_view count, filters site_id when given (299 to 300). Scoped.
5. _get_engagement_distribution (308 to 326): loads every TrackingContact with first_seen >= since (310 to 312) and buckets each via _calculate_engagement_level into {high, medium, low} counts and percentages. The site_id parameter is accepted (308) but never referenced in the method body - every contact in the date window is counted regardless of which site(s) the caller may see. Not scoped (Gotchas, Security & tenancy).
6. _get_conversion_funnel (328 to 365): four stages. Stage 1 "Visitors" (331 to 337) filters site_id when given and counts distinct fingerprint. Stage 2 "Engaged" (340 to 345, sessions with ≥2 page_view events), Stage 3 "High Intent" (348 to 351, sessions whose page_path matches any of HIGH_INTENT_PAGES via ilike), and Stage 4 "Converted" (354 to 356, TrackingContact.identified_at >= since) take no site_id filter at all, and Stages 2 to 3 do not exclude is_internal sessions either. Percentages for stages 2 to 4 are each stage's globally-scoped count divided by stage 1's correctly-scoped total_visitors, which can read as impossible values (a percentage over 100) when the caller's own site is smaller than the workspace-wide activity being counted in the numerator. Only stage 1 is scoped (Gotchas, Security & tenancy).
7. _get_activity_heatmap (367 to 389): func.extract('dow'/'hour', ...) grouped counts, filters site_id when given (376 to 377), maps dow (0 to 6, Postgres convention, Sunday = 0) to English day names. Scoped.
8. _get_device_breakdown (391 to 424): a SQL CASE on lower(user_agent) containing mobile/android/iphone → mobile, tablet/ipad → tablet, else desktop; filters site_id when given (399). No user-agent parsing library, substring matching only. Scoped.
9. _get_trends (426 to 451): session counts for [now-2d, now-d) vs [now-d, now), change_percent and a trend of up/down/stable; filters site_id in the inner get_counts closure when given (437 to 438). Scoped.
10. _generate_dashboard_insights_from_data (453 to 503): pure, no queries; reads summary['identification_rate'], traffic_data['sources'], engagement['percentages'], funnel['stages'] and appends up to four canned insight dicts (type, title, message, optional metric, action) when: identification rate < 5; the top traffic source is direct; high-engagement percentage > 20; or stage-1-to-stage-2 drop-off > 80%. Because it reads the (partly unscoped) engagement and funnel dicts verbatim, the "Strong Engagement" and "High Bounce Rate" text insights inherit the same scoping gap as items 5 and 6.

Invariants

• Every aggregate SHOULD read from the exact same site_id scope require_site_id_or_admin resolved for the caller. This does not hold at this commit: _get_engagement_distribution and three of _get_conversion_funnel's four stages ignore site_id entirely (items 5 and 6 above). A rebuild must scope TrackingContact queries the way _contact_visible_to/_workspace_ids_for_site do elsewhere in this router (via the sessions or workspace join, since TrackingContact carries no site_id column) before this invariant is true.
• _classify_channel's Python implementation and the hand-duplicated SQL CASE in _get_traffic_sources (item 2 above) must be kept in lockstep by hand; nothing in this file enforces that they agree, and no test in this repo compares their outputs (Tests).
• The warm job and the live request path must agree on what a "site scope" value looks like for the SAME cache key to hold the SAME shape of data. This does not hold - see Gotchas, "the warm job crashes on every real site."
• _scribe_cached's cache failures never become request failures (try/except: pass around both the read and the write, tracking_router.py 58 to 63, 67 to 70); only compute_fn() itself (this service's queries) can raise into the route.

Frontend

analytics-tracking/ui/tracking-dashboard   the module that renders this response: summary tiles, the
                                            traffic-source/channel breakdown, top pages, the engagement
                                            distribution, the conversion funnel, the activity heatmap,
                                            device breakdown, and the trend deltas, per `GET
                                            /insights/dashboard`'s eight top-level keys; also the
                                            surface that polls `GET /stats` (not this recipe) alongside
                                            it, per `get_tracking_stats`'s own docstring
                                            ("TrackingDashboard frontend")

Minimum admin/debug surface at this commit: no dedicated debug route exists for cache state or warm-job history; the only visibility into whether warming is working is the scheduler's own INFO log line SCHEDULER: Scribe report warm done - {summary} (scheduled_jobs.py 557, summary being warm_scribe_reports's {"warmed", "errored", "sites", "days"} dict) and, per iteration, the WARNING lines scribe warm: stats failed for site=%s: %s / scribe warm: insights failed for site=%s days=%s: %s (tracking_router.py 2873, 2882) - the second of which, per Gotchas, fires for essentially every real (non-None) site at every warm cycle.

Security & tenancy

Auth dependency: Depends(get_current_user) (S5), JWT bearer token, then httpOnly cookie, then X-API-Key header, in that priority (api/dependencies.py 187 to roughly 230). Tenancy: effective_site_id = require_site_id_or_admin(db, current_user, site_id) (tracking_router.py 2914) resolves the caller's visible sites through workspace membership (get_user_site_ids, 131 to 174: a tracking_sites → workspaces → workspace_users join, not through any column on tracking_sessions/tracking_events/tracking_contacts themselves); admins get None (unrestricted), non-admins always get a non-empty list, a caller with no reachable site gets [NO_TRACKING], a sentinel string that matches no real site_id (110, 177 to 207). This same helper backs every Scribe endpoint, not just this one; test_tracking_scope.py is its dedicated coverage (see Tests).

Fail direction, and where it is NOT closed in this specific recipe: require_site_id_or_admin itself fails closed (a non-admin can only narrow within, never expand beyond, their own resolved set; NO_TRACKING on zero access). But two of the eight aggregates this route returns do not apply that scope at all - _get_engagement_distribution (Application logic item 5) and three of _get_conversion_funnel's four stages (item 6) query TrackingContact/TrackingEvent with only a date filter, no site_id filter, no workspace join. A non-admin restricted to one site still receives, inside a 200 response their own token legitimately produced, engagement_distribution and conversion_funnel.stages[1..3] counts drawn from every workspace's contacts and events. This is a real cross-tenant data disclosure in the response body of an authenticated, correctly-scoped-looking endpoint, not a hypothetical: verified by reading the query bodies at 310 to 312, 340 to 345, 348 to 351, and 354 to 356, none of which reference site_id or effective_site_id at all, against the working tree at 62ba4fc41009b454e1c033e5036c7e8577db2061 on 2026-09-12. TrackingContact has no site_id column to filter on directly (Data model); the fix pattern already exists in this same router as _contact_visible_to/_workspace_ids_for_site (210 to 257), which resolve contact visibility via the owning session's site_id or via the workspace a TrackingSite row belongs to - neither is reused inside tracking_insights_service.py.

Secrets and PII at rest: none newly touched by this recipe; it reads page_path/page_url/utm_*/user_agent substrings only, no card data, no raw IP (ip_hash exists on the model but is not read here). Rate limits: none dedicated to this route; it sits behind the same get_current_user gate as the rest of /api/scribe, and the router is mounted with surfaces=surface_exempt("public", "Scribe beacon - no auth by design") at the app level (app_fastapi.py 956) - that surface_exempt governs the mount's own surface-gating (per-route auth is still enforced by each route's own Depends(get_current_user)), it does not mean this specific route is unauthenticated; the comment directly above the mount call ("Include tracking router for local development (no auth required)", 954) is stale relative to what actually ships (TRACKING_ROUTER_AVAILABLE is a plain import-success guard, not an environment flag - Stack assumptions, Deploy & ops). Attacker-controlled inputs: site_id (query string, only ever narrows within the caller's own resolved set, never expands it - S6's contract, exercised by test_requesting_someone_elses_site_does_not_grant_it); days (bounded 1..365 by FastAPI's Query(..., ge=1, le=365) at the route, but TrackingInsightsService.get_dashboard_insights's own signature default is unbounded days: int = 30 with no validation, so a direct/internal caller of the service - not the route - could pass an arbitrary or negative value; the warm job always passes a value from the bounded SCRIBE_WARM_DAYS list, so this is unreached in practice at this commit).

Tests

test_tracking_scope.py (136 lines, run 2026-09-12 from alchemy_labs/backend with .venv/bin/python3 -m pytest tests/test_tracking_scope.py -q, output 14 passed) covers the shared scoping primitives every Scribe endpoint including this one depends on: require_site_id_or_admin, _k_sites, _session_visible_to, imported directly from tracking_router.py with a MagicMock database (36 to 47, no real DB, hermetic). Named tests: test_admin_with_no_request_is_unrestricted, test_admin_can_narrow_to_one_site, test_non_admin_is_never_unrestricted (parametrized on (), ("a",), ("a","b","c")), test_non_admin_sees_every_site_in_their_workspaces, test_non_admin_may_narrow_to_one_of_their_own_sites, test_requesting_someone_elses_site_does_not_grant_it, test_no_reachable_sites_yields_the_sentinel, test_session_visible_only_within_scope, test_session_visible_to_admin, test_cache_key_is_order_independent, test_cache_key_distinguishes_admin_from_everyone_else, test_cache_key_tolerates_a_stray_single_value.

What this file does NOT cover, stated plainly rather than invented: no test in this repository exercises TrackingInsightsService itself. grep -rl "tracking_insights_service\|get_dashboard_insights\|_classify_channel\|TrackingInsightsService" backend/tests/ (run 2026-09-12) returns no results. Concretely untested: get_dashboard_insights end to end; _classify_channel's branch order, including that its Python version and the hand-duplicated SQL CASE in _get_traffic_sources agree; _get_engagement_distribution and _get_conversion_funnel's missing site_id scoping (Security & tenancy); warm_scribe_reports and the bare-string-vs-list argument mismatch that crashes it for every real site (Gotchas); _generate_dashboard_insights_from_data's four threshold rules. A rebuild should add, at minimum, a known-answer table for _classify_channel (source/medium/click-id combinations mapping to each of the six channel labels) and a scoping test that asserts _get_engagement_distribution/_get_conversion_funnel exclude another site's data, on the model of test_tracking_scope.py's own test_session_visible_only_within_scope. Hermetic vs live: test_tracking_scope.py needs no database; nothing else in this recipe's own test coverage exists to classify.

Deploy & ops

Config keys per app: SCRIBE_WARM_ENABLED, SCRIBE_WARM_INTERVAL_MINUTES, SCRIBE_WARM_TTL_SECONDS, SCRIBE_WARM_DAYS, REDIS_ENABLED (all above, core/config.py). No migration ships with this recipe; tracking_sessions/tracking_events/tracking_contacts already exist (event-ingestion/visitor-identification recipes own that schema). Scheduler: run_scribe_report_warm registered on IntervalTrigger(minutes=SCRIBE_WARM_INTERVAL_MINUTES) (default 60), coalesce=True, max_instances=1, first run 30 seconds after boot (scheduled_jobs.py 418 to 428); its whole purpose per its own comment (409 to 417) is to move the heavy aggregation this recipe performs off the request path, because an open dashboard polling /stats + /insights every 60 seconds had previously exhausted the app's 5-connection pool. Mount: mount_router(app, tracking_router.router, tags=["Tracking"], surfaces=surface_exempt("public", ...)) (app_fastapi.py 956), gated only by TRACKING_ROUTER_AVAILABLE, a plain "did the module import" boolean (199 to 202), not an environment or feature flag - this route ships in every environment where the import succeeds. Argus-style checks: none written specifically for this route at this commit; the closest existing signal is the scheduler's own SCHEDULER: Scribe report warm done - {summary} INFO line and its per-site WARNING lines (Frontend). Rollback: nothing stateful to unwind, this recipe writes no rows; the Redis cache it reads/writes has a bounded TTL (≤ 7800s) and self-heals on the next warm cycle or next cold request.

Gotchas

• 2026-09-12 (measured at 62ba4fc, tracking_router.py 2857 to 2883, confirmed by direct reproduction against the real ORM model at alchemy_labs/backend/.venv with SQLAlchemy==2.0.43): the warm job crashes for every real (non-admin, non-None) site. warm_scribe_reports builds site_ids = [None] + [r[0] for r in db.query(TrackingSite.site_id)...] - each non-None entry is a bare Python str, e.g. "dth.zuerich". It is passed straight into _compute_insights_report(db, sid, d) → TrackingInsightsService.get_dashboard_insights(site_id=sid, ...), whose queries call TrackingSession.site_id.in_(site_id) (and the equivalent on TrackingEvent) at ten call sites across the service. Column.in_() in SQLAlchemy 2.0 does not treat a bare string as "one value to match" - it raises: reproduced live with TrackingSession.site_id.in_('dth.zuerich') → sqlalchemy.exc.ArgumentError: IN expression list, SELECT construct, or bound parameter object expected, got 'dth.zuerich'. The warm job's own per-site try/except (2876 to 2883) catches this, increments errored, logs a WARNING, and moves on - so warmed only ever reflects the single site_id=None (admin "all") entry succeeding; every per-site entry in SCRIBE_WARM_DAYS fails every hour. The live route path (require_site_id_or_admin) always passes a List[str] for a non-admin, so the route itself does not hit this; but _k_sites computes the identical cache key for a one-element list and for the bare string (_k_sites(["dth.zuerich"]) == _k_sites("dth.zuerich") == "dth.zuerich"), so the entry the warm job MEANT to populate for that key is simply never written, and every non-admin's /insights/dashboard request runs the full cold _compute_insights_report on the request path's 90-second TTL, every time it misses - which is exactly the QueuePool-exhaustion failure mode the warm job exists to prevent (Deploy & ops), silently un-fixed for every tenant except the admin "all" view. Fix shape: wrap sid in [sid] (or pass the list form require_site_id_or_admin already produces) before calling _compute_insights_report in the per-site loop.
• 2026-09-12 (measured at 62ba4fc, tracking_insights_service.py 308 to 326 and 328 to 365): _get_engagement_distribution and three of _get_conversion_funnel's four stages accept a site_id parameter and never use it - see Security & tenancy for the cross-tenant disclosure this causes in a real, authenticated, correctly-scoped-looking response.
• 2026-09-12 (measured at 62ba4fc, tracking_insights_service.py 178 to 206 vs 251 to 272): _classify_channel's channel rules exist twice, once as a Python function and once hand-duplicated as a SQL CASE inside _get_traffic_sources, with no shared source of truth and no test comparing them (Tests). A rule change applied to one and not the other silently makes the per-source channel label (Python path) disagree with the per-session channels totals (SQL path) in the same response.
• 2026-09-12 (measured at 62ba4fc, app_fastapi.py 954 to 957): the comment above the tracking router's mount call still reads "Include tracking router for local development (no auth required)"; at this commit the mount is unconditional in every environment (gated only by whether the module imported, 199 to 202) and every route under it that matters for this recipe requires get_current_user. The comment is stale, not a description of current behaviour.

Reference implementation

Paths relative to the repo root; all at main 62ba4fc41009b454e1c033e5036c7e8577db2061 unless noted.

• alchemy_labs/backend/services/tracking_insights_service.py (1028 lines): module docstring (1 to 9); constants ENGAGEMENT_THRESHOLDS, HIGH_INTENT_PAGES, HIGH_INTENT_ACTIONS (30 to 44); get_dashboard_insights (57 to 85); _get_summary_stats (87 to 176); _classify_channel (178 to 206); _get_traffic_sources (208 to 287); _get_top_pages (289 to 306); _get_engagement_distribution (308 to 326); _get_conversion_funnel (328 to 365); _get_activity_heatmap (367 to 389); _get_device_breakdown (391 to 424); _get_trends (426 to 451); _generate_dashboard_insights_from_data (453 to 503); helper methods _calculate_engagement_level, _format_duration (964 to 1028, shared with the out-of-scope per-lead insights path).
• alchemy_labs/backend/api/routers/tracking_router.py (3576 lines, whole file in scope for the pieces cited): router = APIRouter(prefix="/api/scribe", ...) (260); _scribe_cached (49 to 71); _k_sites/_k_scribe_stats/_k_scribe_stats_feed/_k_scribe_insights (78 to 103); NO_TRACKING (110); get_user_site_ids (131 to 174); require_site_id_or_admin (177 to 207); _session_visible_to/_contact_visible_to/_workspace_ids_for_site (210 to 257, cited as the existing scoping pattern tracking_insights_service.py does not reuse); get_campaign_performance and get_landing_page_performance (2644 to 2817, cited for context, out of scope); _compute_insights_report (2824 to 2831); warm_scribe_reports (2834 to 2887); get_dashboard_insights route (2890 to 2919); get_tracking_stats (1680 to roughly 1710, cited for context, out of scope).
• alchemy_labs/backend/models/tracking_model.py (358 lines): TrackingSession (55 to 113), TrackingContact (117 to 212), TrackingEvent (214 to 277).
• alchemy_labs/backend/services/scheduled_jobs.py: the scribe_report_warm job registration (409 to 428); run_scribe_report_warm (544 to 562).
• alchemy_labs/backend/app_fastapi.py: TRACKING_ROUTER_AVAILABLE (199 to 202); the router mount (954 to 957).
• alchemy_labs/backend/core/config.py: SCRIBE_WARM_ENABLED (404), SCRIBE_WARM_INTERVAL_MINUTES (405), SCRIBE_WARM_TTL_SECONDS (408), SCRIBE_WARM_DAYS (412), REDIS_ENABLED (388).
• Test as listed under Tests: alchemy_labs/backend/tests/test_tracking_scope.py (136 lines).

Deviations of the reference from an ideal implementation, all dated 2026-09-12: the warm job's site_id argument shape mismatch (bare string vs list) crashes silently for every real site, defeating the cache-warming this job exists to provide, for every tenant but the admin "all" view; _get_engagement_distribution and three of four _get_conversion_funnel stages leak cross-tenant data into an otherwise correctly-scoped authenticated response; _classify_channel's rules are duplicated by hand between Python and SQL with no test holding them equal; no test exercises TrackingInsightsService at all.

Acceptance checks

Run from alchemy_labs/backend at 62ba4fc (or an app built to this recipe), using the .venv this recipe was measured against. Expected observations are exact, as measured 2026-09-12.

1. .venv/bin/python3 -m pytest tests/test_tracking_scope.py -q prints 14 passed.
2. grep -n "def _get_engagement_distribution" -A5 services/tracking_insights_service.py shows the method signature takes site_id and its body's first executable line queries TrackingContact with no reference to site_id; confirms the scoping gap in Security & tenancy without needing a live database.
3. .venv/bin/python3 -c "import sys; sys.path.insert(0,'.'); from models.tracking_model import TrackingSession; TrackingSession.site_id.in_('a.b')" raises sqlalchemy.exc.ArgumentError: IN expression list, SELECT construct, or bound parameter object expected, got 'a.b'., confirming the warm-job crash mechanism in Gotchas independent of any database content.
4. grep -c "^def test_" tests/test_tracking_scope.py prints 12 (11 plain def test_ plus the one @pytest.mark.parametrized test_non_admin_is_never_unrestricted, which pytest expands into 3 cases - 12 minus that 1 plus 3 is the 14 passed in step 1).
5. grep -n "TrackingSession.site_id.in_\|TrackingEvent.site_id.in_" services/tracking_insights_service.py | wc -l prints 10 (_get_summary_stats alone accounts for 3, at lines 94, 112, 120), the scoped call sites Application logic enumerates as individually checked for site_id.
6. grep -n "def warm_scribe_reports" -A25 api/routers/tracking_router.py | grep "site_ids = " prints 2857- site_ids = [None] + [, the list built from TrackingSite.site_id without ever wrapping entries in a list, confirming the shape mismatch cited in Gotchas by inspection.