Django ORM model, SQL query builder internals, and date bucket filling logic for the Historical Summary backend

Data Model

This page covers the Django model, the SQL query builder, the date-bucket filling logic, and the internal query pipeline that powers the Historical Summary feature.

Django Model: `OperationSummary`

Source: operation/models/operation_summary.py

class OperationSummary(models.Model):
    EVENT_TYPES = (
        "organization_test", "organization_sample", "organization_bill",
        "referral_test", "referral_sample", "referral_bill",
        "branch_test", "branch_sample", "branch_bill",
        "department_test",
    )
    id = models.AutoField(primary_key=True)
    dimension_a_id = models.BigIntegerField(null=False)
    dimension_b_id = models.BigIntegerField(default=None, null=True)
    event_type = models.CharField(max_length=255)
    event_value = models.IntegerField(default=0)
    lab = models.ForeignKey("account.Labs", on_delete=models.CASCADE)
    ordered_at = models.DateField(null=False)

    class Meta:
        db_table = "operation_historical_summary"
        unique_together = ("lab", "dimension_a_id", "dimension_b_id", "event_type", "ordered_at")

Field Semantics

Field	Type	Role
`dimension_a_id`	`BigIntegerField`	FK to the primary entity (org, referral, branch, or department table). Not a Django FK - intentionally an integer for flexibility across multiple destination tables.
`dimension_b_id`	`BigIntegerField (nullable)`	FK to the secondary entity (test or sample). `NULL` for `*_bill` event types where there's no secondary grouping.
`event_type`	`CharField(250)`	One of the 10 base event types, or one of the 6 access-status variants (e.g., `organization_sample_accessed`).
`event_value`	`IntegerField`	The count for this combination. Updated via `INSERT ON DUPLICATE KEY UPDATE` in the nightly job and via delta `UPDATE` in triggers.
`ordered_at`	`DateField`	The date (in the lab's timezone) when the billing activity occurred. Not the UTC date - the column stores the "local business day."

Note: ordered_month is a MySQL stored generated column (DATE_FORMAT(ordered_at, '%Y-%m-01')) that exists in the DB but is not represented in the Django model. The model accesses it directly in raw SQL queries.

Query Builder Pipeline

The main entry point for fetching data is OperationSummary.fetch_summary_data(payload, lab_id). Here's the method chain:

Step 1: `validate_payload()`

Validates and normalizes the incoming request parameters:

Parameter	Required	Validation Rule
`start_date`	✅	ISO-8601 format (`YYYY-MM-DD`)
`end_date`	✅	Must be ≥ `start_date`
`event_type`	✅	Must be one of `EVENT_TYPES`
`summary_granularity`	✅	`daily` / `weekly` / `monthly`
`detailed_summary`	❌	Parses `"true"/"false"` → `bool`
`secondaryGrouping`	❌	Parses `"true"/"false"` → `bool`
`accessed`	❌	`"true"/"false"`, mutually exclusive with `not_accessed`
`not_accessed`	❌	`"true"/"false"`, mutually exclusive with `accessed`

Date range limits:

Granularity	Max Days
Daily	14
Weekly	62
Monthly	No limit

Step 2: `_prepare_fetch_sql_query()`

Constructs the raw SQL. There are two query modes:

Primary Grouping Mode (default)

Used when the user selects a tab like "Organization Wise" with "Test Count":

SELECT dimension_a_id,
       SUM(event_value) as total_value,
       DATE_FORMAT(ops_summary.ordered_at, '%Y-%m-%d') AS ordered_bucket,
       organization.orgFullName as dimension_a_name
FROM operation_historical_summary AS ops_summary
INNER JOIN organization ON dimension_a_id = organization.orgId
WHERE ops_summary.lab_id = 12345
  AND ops_summary.event_type = 'organization_test'
  AND ops_summary.ordered_at BETWEEN '2026-01-01' AND '2026-01-14'
GROUP BY dimension_a_id, ordered_bucket
ORDER BY ordered_bucket ASC

Secondary Grouping Mode (Test Wise tab)

When the user selects "Test Wise", the query flips to group by dimension_b instead:

SELECT dimension_b_id,
       SUM(event_value) as total_value,
       allTests.testName as dimension_a_name,
       allTests.testName as dimension_b_name,
       DATE_FORMAT(...) AS ordered_bucket
FROM operation_historical_summary AS ops_summary
INNER JOIN allTests ON dimension_b_id = allTests.testID
WHERE ...
GROUP BY dimension_b_id, ordered_bucket

Access Status Filtering

When accessed or not_accessed is specified, the query replaces SUM(event_value) with a correlated subquery that counts directly from labReportRelation:

-- For sample events with accessed filter:
(SELECT COUNT(DISTINCT cs.id)
 FROM collectedSample cs
 INNER JOIN labReportRelation lrr ON lrr.collectedSampleId_id = cs.id
 INNER JOIN billing b ON b.Id = lrr.billId_id
 WHERE cs.sampleForId_id = ops_summary.dimension_b_id
   AND lrr.labId_id = ops_summary.lab_id
   AND lrr.dismissed = 0
   AND lrr.sampleRedrawFlag = 0
   AND DATE(b.billTime) = ops_summary.ordered_at
   AND (lrr.sampleCollected = 1 OR lrr.sampleArrivedAndCollected = 1))

This is necessary because the pre-aggregated event_value in the summary table doesn't distinguish between accessed and not-accessed records. That breakdown requires going back to the source data.

Step 3: `_get_granularity_sql()`

Returns the SQL expression for the ordered_bucket column:

Granularity	SQL Expression
Daily	`DATE_FORMAT(ops_summary.ordered_at, '%Y-%m-%d')`
Weekly	`DATE_ADD(start_date, INTERVAL FLOOR(TIMESTAMPDIFF(SECOND, ...) / 604800) * 7 DAY)`
Monthly	`DATE_FORMAT(ops_summary.ordered_month, '%Y-%m-%d')` (uses the stored generated column)

Step 4: `_fill_missing_buckets()`

After the SQL returns data, there may be gaps. If a particular organization had zero tests on Wednesday, there's no row for that day. The frontend's AG Grid needs every bucket to have a value (even if it's 0) to render the pivot table correctly.

This method:

Generates all possible date buckets for the range using _generate_date_buckets()
Groups SQL results by dimension key (primary or primary+secondary)
For each dimension × bucket combination, either uses the existing data or inserts a zero-filled placeholder row

Migrations

Migration	Description
`0095_labfeatures_enable_operation_summary`	Adds the `enable_operation_summary` field to `LabFeatures`
`0002_operationsummary`	Creates the `OperationSummary` Django model (maps to `operation_historical_summary` table)
`0003_add_generated_ordered_month`	Adds the `ordered_month` stored generated column

Data Model

On this page