Design decisions

Key architectural choices and trade-offs in one place: summary table, job split (daily/repair/migration), trigger-driven repairs, billing-time anchor, and export-first design.

1. A summary table was chosen instead of live report aggregation

Antimicrobiogram could have been implemented as a live aggregation over:

billing
labReportRelation
report values
patient and organization joins

That would have made reads expensive and repetitive. Instead, the system stores a denormalized row set in OrganismAntibioticSummary.

Why this is a good choice

the same processed row can support both raw result browsing and grouped matrix rendering
the frontend stays fast because it reads from one purpose-built table
historical rebuilds become operationally straightforward
the feature is easier to export because the data is already shaped for reporting

This is exactly the kind of tradeoff you want in a reporting-heavy feature.

2. Organization-wise reporting was made the default

The feature is intentionally organization-wise. That is not just a UI preference. It is a domain decision.

Why it is the right call

local susceptibility patterns matter at organization level
combining all organizations too early can hide important variation
users usually want a clean, scoped answer they can trust immediately

The frontend reinforces this by auto-selecting the first organization with data.

3. Three job types were used instead of one generic batch

The feature uses different jobs because each job type solves a different operational need.

Daily jobs

standard freshness
small previous-day windows

Repair jobs

targeted historical corrections
automatically fixing old days after meaningful source updates

Migration jobs

first-time onboarding
long-window historical backfill

Trying to force all three use cases into a single generic runner would make the feature harder to operate and harder to reason about.

4. Repair is trigger-driven

The feature watches changes through SQL triggers on:

billing
labReportRelation

This is a very pragmatic design.

Why trigger-driven repair works well here

the system notices source-of-truth changes immediately
historical days can be repaired automatically
support does not need to manually detect every meaningful update
rebuilds stay focused on the exact day that changed

This is the kind of operational automation that makes a reporting feature feel dependable.

5. Present-day updates are handled differently from historical updates

The daily job owns the previous day. The repair triggers focus on prior days. That separation is clean because present-day data is still moving, while historical days should be stable and correctable. This keeps the system from doing unnecessary noisy repairs for data that has not yet rolled into the normal daily reporting window.

6. Billing date was chosen as the processing anchor

All rebuild windows are based on billing.billTime. This is a strong operational anchor because:

it aligns with historical day-based processing
it gives one consistent way to select the affected summary slice
it makes daily, repair, and migration jobs all speak the same window language

When a system has multiple job types, using one shared window anchor reduces confusion.

7. One endpoint serves both raw rows and grouped sensitivity

The read API returns:

results
sensitivity This is a very engineer-friendly contract.

Why it is good

raw views and heatmap views stay backed by the same source query
the frontend does not need separate orchestration logic for two different APIs
debugging is easier because the detailed rows and the grouped matrix come from the same request family

That is a strong interface design for a feature like this.

The source payloads are not identical:

microbiology rows come from microbiology
molecular rows come from organism and antibiotic resistance

But the feature still stores them in one summary table with a result_type discriminator.

Why this is a good design

users see one coherent reporting surface
the backend can reuse the same filtering and export behavior
the frontend can switch between raw and matrix views without jumping across totally different models

This is a great example of unifying different source shapes behind one reporting contract.

9. Timezone-aware windows are essential

The feature processes days in the lab's local timezone and then converts them to UTC for querying. This matters a lot. If reporting days were treated as plain UTC days, labs in different regions would see confusing edge behavior around midnight. By resolving each daily or repair window in lab-local time first, the feature stays aligned with business reality. That is the correct design for multi-timezone reporting.

10. Excel export is part of the core design, not an afterthought

Export is built into the main Antimicrobiogram experience. That is important because reports like this are often:

reviewed outside the app
shared across teams
discussed in operational or clinical meetings

The export includes matrix data, filter metadata, and legend styling, which makes it a true deliverable, not just a raw download.

11. Queue tables make operations transparent

Two separate operational trackers are used:

OrganismAntibioticSummaryRepairQueue
crelio_data_migrations

This separation is a good design choice.

Why it helps

engineers can inspect historical repairs without mixing them with long-window migrations
support can understand whether a lab is waiting on onboarding history or a one-day fix
schedulers and processors have simpler contracts Operational clarity is underrated in reporting features, and this design gets it right.

12. The overall architecture is balanced

The strongest thing about the feature is balance. It balances:

detail and performance
automation and control
raw exploration and grouped reporting
onboarding backfill and daily freshness
historical correctness and runtime simplicity

That balance is why the feature is not just technically complete, but also easy to operate and easy to explain.

Design Decisions