Dashboard BSC — BigQuery architecture

Status: Schema + ETL + API read path + rolling sync (optional) + Final Report API/UI. Enable with BQ_DASHBOARD_ENABLED=true and BSC_ROLLING_SYNC_ENABLED=true in server environment configuration.
Mongo remains source of truth for scorecard CRUD; BigQuery holds pre-aggregated user-month metrics for the dashboard.

---

Sync policy (product intent)

Source data — scorecard entry tables

Managers submit entries over time. All calculations read from Mongo only:

Scorecard page	Mongo collection	Month filter field
SBS	tbl_sbs_entries	task_evaluated_date
Non-SBS	tbl_non_sbs_entries	task_evaluated_date
Attitude Towards Work	tbl_atw_entries	date_of_occurrence
Attendance Issues	tbl_attendance_issues	date_of_occurrence
Productivity Issues	tbl_productivity_issues	date_of_occurrence
Additional Points	tbl_additional_points_entries	date_of_occurrence
Incidents	tbl_incidents	incident_date (bonus qualification only — not KPI math)

Each sync run: load active entries for the target month (soft-deleted rows excluded) → apply User-month calculation per employee_id → evaluate bonus qualification (attendance, incidents, red flags, Goal band) → write user-month rows to BigQuery using the current Balance Scorecard Metrics config.

After entry delete, restore, or purge: a scoped BQ upsert (scorecard_entry_resync) updates affected employee-month rows without waiting for the next rolling sync.

Two modes: rolling (current month) vs final (closed month)

flowchart TB
  subgraph mongo [Mongo — source of truth]
    E[Scorecard entry tables — submissions + additional points + incidents]
    C[BSC metrics config]
    M[bsc_month_reports — draft / finalized]
  end

  subgraph rolling [Rolling sync ~every 10 min]
    R[Current calendar month only]
    R -->|overwrite BQ rows| BQ1[BQ snapshot: draft]
  end

  subgraph final [Generate Final Report — manual]
    F[Past or closed month]
    F -->|lock in Mongo| M
    F -->|overwrite BQ rows| BQ2[BQ snapshot: final]
  end

  E --> R
  E --> F
  C --> R
  C --> F
  R --> BQ1
  F --> BQ2

Mode	When	What it means
Rolling (draft)	Current month and prior month while still draft — scheduled ~every 10 minutes (+ optional manual “Sync now”)	Dashboard reflects new SBS/ATW/attendance/additional-points/etc. as they are submitted. BQ rows for that month are replaced each run.
Final	Past months (or current month after close) — Generate Final Report (admin/manager action)	One last full recompute from Mongo, then month marked finalized in Mongo. Rolling sync stops overwriting that month unless an explicit “Reopen & resync” (super admin).

Why finalize? Past months should not change every 10 minutes if someone edits an old entry or config weights shift. “Final Report” = agreed snapshot for leadership review.

Where finalized status is stored: Mongo collection tbl_bsc_month_reports:

{ _id: "2026-05", year: 2026, month: 5, status: "draft" | "final",
  finalized_at, finalized_by_employee_id, last_rolling_sync_at,
  reopened_at?, reopened_by_employee_id? }

BigQuery rows carry report_status (draft | final) and synced_at on each sync for audit.

What gets written each sync

One row per employee × calendar month in bsc_employee_month (partitioned by period_date, clustered on employee_id). Each row includes:

• total_score, working_days, nested kpis and kpms arrays
• Eligibility and bonus qualification flags + breakdown JSON
• report_status (draft | final), computed_at, synced_at

Ephemeral upserts use _bsc_employee_month_staging (delete + insert per affected period_date / employee scope).

Dashboard reads BQ for the selected month; if month is draft, numbers may change on next 10‑min run. If final, numbers are stable.

Dashboard month picker

GET /v1/dashboard/bsc/available-months builds the month dropdown from the union of:

Source	What it contributes
BigQuery	Distinct (year, month) with synced rows for employees in the user’s access pool
Scorecard entries	Distinct occurrence/evaluation months on entry collections (includes soft-deleted bin rows)
Month reports	Every _id in tbl_bsc_month_reports (e.g. 2026-07) — keeps finalized months selectable after BQ rows are removed

Months are capped at max_bsc_dashboard_month() (current EST month, or next month when BSC_TEST_FUTURE_MONTHS=true).

Current month with no entries: rolling sync still runs for the active calendar month. It can create draft month-report and BQ rows for employees in the sync scope even when Mongo has zero active scorecard entries for that month — so July appears in the picker at the start of July without any submissions yet.

To fully remove a month from the picker after a data reset, delete or reopen the month report, remove leftover soft-deleted entries, and delete BQ rows for that period_date (see ops scripts below).

Operational jobs

Job	Trigger	Scope
Rolling BSC sync	BSC_ROLLING_SYNC_ENABLED=true — background task every 600s (configurable) while worker runs	Current month (EST) plus immediately previous month when still draft; skip months with status: final in Mongo
Manual sync	POST /v1/dashboard/bsc/sync or UI Refresh dashboard data	Access level 5; current or past draft month; optional employee_ids for scoped sync; async=true queues background job
Sync job poll	GET /v1/dashboard/bsc/sync-jobs/{job_id}	Progress for async sync, finalize, or cycle refresh
Active sync job	GET /v1/dashboard/bsc/sync-jobs/active?year=&month=	Resume in-progress job for a month
Employee cycle refresh	POST /v1/dashboard/bsc/refresh-employee-cycle	Access level 5; re-sync one employee across all cycle months through anchor month
Generate Final Report	POST /v1/dashboard/bsc/finalize or UI button	Access level 5; past months only → compute with eval ratios → BQ final → lock Mongo (background job)
Reopen month	POST /v1/dashboard/bsc/reopen or UI Reopen Month	Access level 5; past final month → draft (allows scorecard edits again)
Re-generate Final Report	POST /v1/dashboard/bsc/finalize?regenerate=true	Access level 5; past final month → recompute and overwrite BQ
Purge soft-deleted entries	Worker job purge_soft_deleted	Hard-delete bin rows whose occurrence month is finalized
Scoped entry resync	After soft delete / restore / purge on scorecard	Upsert affected employee-month rows in BQ when enabled

Not in scope for rolling sync: re-copying raw entry tables to BQ (optional later for analytics). Phase 1 is summarized user-month only.

Edge cases

• Edit after finalize: Scorecard create/update/delete for entry dates in a finalized month returns 409 for access levels 1–4. Level 5 may edit; use Reopen month for everyone else, then sync and re-generate final report. See Fiscal cycles & locks §7.
• Config change: Rolling month picks up new weights on next sync; finalized months keep scores from finalize run unless admin re-finalizes.
• Month-close window: when July starts but June is not yet finalized, June remains in rolling sync until Generate Final Report (often run in the first week of the new month). Older draft months (e.g. April still open in July) are not auto-synced — use manual sync or finalize.
• Fiscal vs calendar: Finalize/sync use calendar months (EST). Fiscal BSC cycle (Jul–Jun) determines which metrics config applies — see Fiscal cycles & locks.

---

Goal

1. Materialize each employee’s KPI scores and total BSC per calendar month (grain: employee_id + year + month).
2. Compute using the same rules as BSC metrics and balance_score_card_calculation.py (driven by config in Mongo today).
3. Dashboard API resolves access scope in Mongo, then reads aggregates from BigQuery (fast). If BQ is off or empty, falls back to live Mongo computation.

KPM weights and KPI source types come from Balance Scorecard Metrics config (balance-score-card-metrics). The ETL stores kpi_key, kpi_source, and kpi_score so you can audit and rebuild charts even if labels change in config.

---

Data flow

flowchart LR
  subgraph mongo [MongoDB — operational]
    E[SBS / Non-SBS / ATW / Attendance / Productivity / Additional Points]
    C[Scorecard config — KPM/KPI tree]
    S[Employee schedules — working_days]
  end

  subgraph etl [ETL — Python job]
    J[sync_bsc_to_bigquery.py]
    J -->|same logic as API| CALC[bsc_employee_compute]
  end

  subgraph bq [BigQuery — analytics]
    M[bsc_employee_month]
    S[_bsc_employee_month_staging]
  end

  subgraph api [FastAPI dashboard]
    SC[resolve_dashboard_bsc_scope]
    Q[fetch from BQ or Mongo]
  end

  E --> J
  C --> J
  S --> J
  J --> M
  SC --> Q
  M --> Q

---

BigQuery tables

DDL: api/scripts/bigquery_bsc_schema.sql in the Ascendly repository.

Table	Grain	Purpose
bsc_employee_month	employee_id, period_date	One row per employee-month: nested KPI/KPM scores, totals, eligibility, report_status
_bsc_employee_month_staging	(ephemeral)	Buffer for scoped delete+insert upserts during sync

Partition bsc_employee_month by period_date (first day of month). Cluster on employee_id.

DDL: api/scripts/bigquery_bsc_schema.sql · setup: python scripts/setup_bigquery_bsc_tables.py.

---

KPI calculation (per user-month)

Implemented in Python (bsc_employee_compute.py) today; the sync job reuses it so BQ matches the app. Full step-by-step: User-month calculation.

source (config)	Inputs (Mongo, that month)
sbs_non_sbs_quantity	Mean quantity_score (SBS + Non-SBS)
sbs_integrity	Mean SBS score
sbs_non_sbs_accuracy	Mean auditors_score
sbs_compliance	Mean SBS % minus 1 pt per productivity issue
attendance_tardiness	(working_days − tardiness count) / working_days
attendance_absenteeism	(working_days − absenteeism count) / working_days
atw_by_impact	Sum ATW final_score per impact → min(100, points)

Additional points (tbl_additional_points_entries): after base KPI scores are computed, KPI-target entries add points directly to that KPI’s score (cap 100). KPM-target entries add points to the KPM total (cap 100) without changing individual KPI scores; total_score uses the boosted KPM totals. See User-month calculation § Additional points.

working_days from resolve_working_days_for_employee() (schedule or weekday fallback).

Total score: weighted sum of KPI scores using KPM/KPI weights from config at ETL run time (aggregate_total_score), with optional KPM boosts applied to KPM totals before the final weighted sum.

---

Eligibility flags (user-month summary)

Stored on bsc_employee_month alongside total_score. Computed at sync from employment_data.regularization_date on the employee document (Mongo source of truth for the date).

Field	Rule	UI label
bsc_eligible	Regularized on or before July 1 of the BSC fiscal cycle that contains the month	BSC Eligible / BSC Ineligible
bonus_eligible	Regularized on or before the first calendar day of that user-month	Bonus Eligible / Bonus Ineligible
bonus_qualified	All bonus qualification gates pass (Goal band, zero attendance issues, zero incidents, no red-flag No on SBS/checklist Non-SBS) when bonus_eligible is true	⭐ Bonus Qualified / Not Bonus Qualified
bonus_qualification_breakdown	JSON string of per-check pass/fail detail for dashboard hover	(not exported as separate column by default)

Missing regularization date → both eligibility flags false. When not bonus-eligible, bonus_qualified is false and breakdown may be empty.

When BigQuery is enabled, the dashboard reads scores and qualification from synced BQ rows only; Mongo live compute is used when BQ is off (local dev). Filters and CSV/Excel export include eligibility and qualification labels on Balanced Scorecard and Monthly performance (Target Score Report excludes eligibility filters). Balanced Scorecard detailed export adds Not Qualified Reason from bonus_qualification_breakdown (failed checks only; - when qualified).

Re-sync affected months after correcting regularization dates, attendance/incident entries, or scorecard data that affects qualification. New columns are added via schema migration on next setup/sync (see below).

Code: app/domains/bsc/bsc_eligibility.py, app/domains/bsc/bonus_qualification.py, sync_service.py, bq_employee_month.py.

---

What belongs in BigQuery vs Mongo

Not all dashboard data lives in BigQuery. Use this split when adding features:

Store in BQ (compute at sync)	Keep in Mongo (read at request)
Per employee-month KPI/KPM scores, total score, working days	Who is in the user’s access pool (auth + scope)
Eligibility and bonus qualification flags + breakdown JSON	Employee name, department, cluster — filter dropdowns
Additional-points summary for the month	Month report draft/finalized status
Sync metadata (report_status, computed_at, synced_at)	Scorecard config (KPM/KPI tree) for the month
	Performance band (employee level/tenure + goals config)

When to add a BQ column: only if the field is derived from scorecard entries and config and is expensive or stable enough to pre-compute during sync. If it changes with org structure, permissions, or employee profile alone, keep it in Mongo and join after the BQ read.

Existing rows: new columns are NULL until that employee-month is re-synced. That is expected — show sensible UI defaults for NULL; use “Sync now” or rolling sync to backfill. Do not treat NULL the same as a computed false for booleans.

Adding a field (checklist):

1. app/domains/bsc/bigquery_store.py — _employee_month_table_schema()
2. api/scripts/bigquery_bsc_schema.sql
3. sync_service.py / bq_employee_month.py — write at sync
4. bigquery_store.py — parse on read; dashboard_snapshot.py — BQ path only
5. Web reads snapshot API only (no live recompute when BQ is enabled)

Scalar columns are auto-added on worker sync and dashboard read (ALTER TABLE … ADD COLUMN IF NOT EXISTS). Optional one-time: python scripts/setup_bigquery_bsc_tables.py.

---

ETL — populate BigQuery

From the API project directory, with Python virtual environment active and BigQuery environment variables configured (GCP_PROJECT_ID, service account credentials, BQ_DATASET):

# One month, all BSC-active employees
python scripts/sync_bsc_to_bigquery.py --year 2026 --month 5

# Rebuild last 6 months
python scripts/sync_bsc_to_bigquery.py --year 2026 --month 5 --backfill-months 6

# Single employee (debug)
python scripts/sync_bsc_to_bigquery.py --year 2026 --month 5 --employee-id EMP001

One-shot month purge (ops)

Hard-delete scorecard entries for one calendar month by evaluation / occurrence date (not createdAt), optionally including task delivery, then scoped BQ resync:

cd api
MONGO_DB=ascendly-production BQ_DATASET=ascendly_production BQ_DASHBOARD_ENABLED=true \
  python scripts/delete_scorecard_month_entries.py --year 2026 --month 7          # dry-run
  python scripts/delete_scorecard_month_entries.py --year 2026 --month 7 --apply --hard-delete

Use --skip-bq-resync to skip the end-of-run BQ upsert. To remove the month from the dashboard picker entirely, also delete the tbl_bsc_month_reports document for that month and BQ rows for that period_date (see Month picker).

Staging → production one-shot data migration: api/scripts/migrate_staging_to_production.py (see design-log/2026-07-03-staging-to-production-data-migration.md).

When to run (today — CLI only)

Until scheduled jobs exist, use the script manually. Planned behavior:

• Every 10 min: current month only (--rolling flag TBD).
• Final Report: --finalize for a specific past month (writes BQ + sets Mongo status).

Idempotency: script deletes existing BQ rows for (period_date, employee_id) then inserts fresh rows. Finalized months are skipped by rolling unless --force.

---

Dashboard API read path

GET /v1/dashboard/bsc (unchanged contract):

1. Scope — access pool and filters → list of employee_ids (Mongo).
2. Self view (view_mode=self, one employee — web employee dashboard, desktop Home): always live Mongo via compute_kpi_scores_for_employees. Soft-deleted entries are excluded. Numbers update immediately after entry changes.
3. Aggregate view (managers, multiple employees): if BQ_DASHBOARD_ENABLED=true and GCP configured, query BQ for scoped employees and month; fallback to Mongo if BQ off or read fails. Scoped BQ resync runs after scorecard delete/restore/purge.
4. Performance band — computed in API from Mongo employee level/tenure + goals config (self view).

Cycle performance: GET /v1/dashboard/bsc/performance?cycle_id= returns Total BSC, remark bands, and per-KPM scores for every month in the fiscal cycle. When BigQuery is enabled, month rows are read from BQ the same way as the month snapshot; scope and band classification still use Mongo employee metadata and goals config. See Monthly performance.

Variable	Default	Meaning
GCP_PROJECT_ID	—	Required for BQ
BQ_DATASET	ascendly	Dataset (e.g. ascendly staging, ascendly_production production)
BQ_BSC_EMPLOYEE_MONTH_TABLE	bsc_employee_month	User-month fact table
BQ_DASHBOARD_ENABLED	false	Set true after first sync

---

Why not pure SQL in BigQuery?

• KPI tree and weights are config-driven and change in Mongo; duplicating all rules in SQL is brittle.
• Recommended: Python ETL = single source of calculation truth; BQ stores results at user-month grain.
• Later: optional dbt models on flat entry tables if you sync raw entries to BQ for ad-hoc analysis (see Scorecard Phase 2).

---

Setup checklist

1. Run bigquery_bsc_schema.sql in BigQuery (replace YOUR_PROJECT with your GCP project).
2. Service account with BigQuery Data Editor on the dataset.
3. Set API environment variables; restart the API service.
4. Run sync_bsc_to_bigquery.py for recent months.
5. Set BQ_DASHBOARD_ENABLED=true.
6. Open Dashboard — data should load from BQ (check API logs for Dashboard BSC served from BigQuery).

---

Related docs

• BSC metrics — formulas
• Fiscal cycles & locks — fiscal cycles, task delivery, month locks
• Total score goals — bands (still applied in API for self view)
• Scorecard — operational scorecard pages
• Monthly performance — full-cycle dashboard
• Progress report — stakeholder status