Design Decisions & Architecture

---

Key Design Decisions & Constraints

Pre-Aggregation Over Real-Time Queries

Decision: Store pre-computed aggregates in operation_historical_summary rather than querying billing and labReportRelation at request time.

Why: The billing table can have tens of millions of rows for a large lab. Doing a GROUP BY orgId, testId, DATE(billTime) across a 6-month range on request would be unacceptably slow, taking seconds to minutes. By pre-aggregating overnight, the frontend query hits a small, well-indexed table and responds in milliseconds.

Trade-off: We accept eventual consistency. Today's data won't appear in Historical View until tomorrow's nightly job runs. The frontend displays a notification: "All activities related to orders & samples performed today will appear in the Historical View on the next day."

Trigger-Based Corrections vs. Nightly Idempotency

Decision: Two MySQL triggers (trg_lab_report_relation_after_update and trg_billing_after_update) perform real-time delta corrections to the summary table.

Why: The nightly job handles aggregation for yesterday's data. But what about today's corrections to historical records? If a lab cancels a bill from last Tuesday, the nightly job won't touch it again. The triggers catch these corrections and apply +1 or -1 deltas immediately.

Constraint: Triggers only fire for UPDATE operations (not INSERT). New records created during the day are handled by the next nightly run. This is intentional since the insert volume is too high for trigger-based aggregation.

Dimension A / Dimension B Generic Model

Decision: Use generic dimension_a_id and dimension_b_id columns instead of explicit organization_id, test_id, etc.

Why: The summary table needs to serve 10 event types across 4 primary dimensions and 2 secondary dimensions. With explicit FK columns, you'd need 12 separate columns (most of which would be NULL for any given row) or 10 separate tables. The generic dimension model collapses all of this into one table with one set of indexes.

Trade-off: You lose referential integrity at the DB level. dimension_a_id = 42 could refer to an organization, a referral, or a branch, depending on the event_type. The application layer must ensure correctness. This is acceptable because the data is derived (not user-entered) and fully controlled by the trigger/procedure code.

Year-Based Partitioning

Decision: The table is partitioned by RANGE COLUMNS (ordered_at) with yearly boundaries.

Why:

• Nightly job performance - each run only inserts into yesterday's partition. Other partitions are completely untouched.
• Historical data pruning - if you need to drop 2021 data, ALTER TABLE DROP PARTITION summary_table_2021 is near-instant vs. a DELETE FROM that locks the whole table.
• Query performance - a weekly query for Jan 2026 only scans summary_table_2026 partition.

Constraint: A new partition needs to be added proactively each year. The summary_table_future MAXVALUE partition acts as a safety net, but data landing there would miss out on partition pruning optimizations.

Timezone-Aware Date Bucketing

Decision: ordered_at stores the date in the lab's local timezone, not UTC.

Why: Labs think in local time. When a lab in IST (UTC+05:30) processes a bill at 23:00 IST (17:30 UTC), they expect it counted as "today" (the IST date), not "tomorrow" (which is what the UTC date would show after midnight IST). Bill-time bucketing must match the lab's business-day concept.

Implementation: Every path that writes to ordered_at does DATE(CONVERT_TZ(billTime, 'UTC', labTimeZone)). The nightly scheduler groups labs by labTimeZone and computes each timezone's "yesterday" independently.

Access Status as Separate Event Types

Decision: Rather than adding an access_status column to the summary table, accessed and not-accessed counts are stored as separate event types (e.g., organization_sample_accessed, organization_sample_not_accessed).

Why:

• Index reuse - the same composite index (lab_id, event_type, ordered_month, ...) serves all queries. Adding a column would require a new index or a wider covering index.
• Trigger simplicity - when a sample's access status changes, the trigger just does event_value -= 1 on one event type and event_value += 1 on another. No conditional column updates needed.
• Query simplicity - to get "total accessed samples for org X", just query WHERE event_type = 'organization_sample_accessed'. No CASE WHEN needed.

Trade-off: The operation_historical_summary table has more rows (roughly 2× for sample events, since each sample counts toward both *_sample and *_sample_accessed or *_sample_not_accessed).

Correlated Subqueries for Access Filtering

Decision: When the user applies an "Accessed" or "Not Accessed" filter, the backend swaps SUM(event_value) with a correlated subquery that counts directly from labReportRelation.

Why: The pre-aggregated event_value includes all records regardless of access status. To filter by access status while keeping the primary dimension grouping (e.g., org-wise), we need to re-count from the source table with the access condition applied.

Trade-off: Access-filtered queries are slower than unfiltered ones because they hit the transactional tables via subqueries. This is acceptable because:

1. The outer query still uses the indexed summary table for scoping (lab, event_type, date range).
2. The filter is optional and used less frequently than the default view.

Bucket Filling on the Backend

Decision: The backend fills in zero-value rows for missing date buckets rather than having the frontend handle gaps.

Why: AG Grid's pivot table rendering assumes a complete matrix. If "Apollo Hospitals" had tests on Mon and Wed but not Tue, the frontend would either show a missing column or misalign subsequent columns. By filling zeros server-side, every entity has a row for every date bucket, and the grid renders correctly.

---

Architectural Rationale

Why a Hybrid System (Triggers + Nightly Job)?

Each component handles a different temporal concern:

No single approach handles all three. The nightly job can't handle retroactive changes (triggers). The triggers would create too much overhead for bulk inserts (nightly job). And neither handles the initial data load for a new lab (migration procedure).

Why Raw SQL Instead of Django ORM?

The query builder (_prepare_fetch_sql_query) uses raw SQL throughout. This is deliberate:

1. Generated columns - ordered_month isn't in the Django model and can't be queried via ORM.
2. Dynamic JOINs - The JOIN target changes based on event_type (organization, doctors, branch, etc.). The ORM's select_related / prefetch_related can't do conditional JOINs.
3. Granularity expressions - The bucketing SQL uses DATE_ADD with FLOOR(TIMESTAMPDIFF(...)), which is MySQL-specific and not expressible via ORM.
4. Correlated subqueries - Access-filtered queries use (SELECT COUNT(...) FROM ... WHERE ... = outer.column), not supported via ORM.

This is consistent with the pattern used elsewhere in the Operations Dashboard, which is primarily a reporting/analytics feature.

---

Extensibility Guide

---

Known Constraints & Gotchas