Analytics Pre-Aggregation

Last Updated: 2026-07-08

The pre-aggregation layer maintains two daily summary tables that are populated by Celery tasks rather than computed on-demand. Analytical queries for historical date ranges read from these tables via the replica database, eliminating full-table scans on high-volume transaction and inventory data.

Models

DailyRevenueSummary

Table: daily_revenue_summary (transactions app, migration 0020) Unique constraint: (store, date)

Field	Type	Description
`store`	FK → Store	Store this summary belongs to
`date`	DateField	Calendar date (one row per store per day)
`total_revenue`	Decimal(12,2)	Sum of `SaleTransaction.totalAmount` for completed orders
`total_tax_collected`	Decimal(12,2)	Sum of `SaleTransaction.taxAmount` for completed orders
`total_discounts_given`	Decimal(12,2)	Sum of `SaleTransaction.orderLevelDiscount` for completed orders
`average_order_value`	Decimal(12,2)	Average `totalAmount` across completed orders
`order_count`	PositiveInteger	Count of completed orders. Safe for F() increments from signals.
`items_sold`	PositiveInteger	Sum of `SaleLineItem.quantity` across completed orders. Safe for F() increments.
`last_computed_at`	DateTimeField	When the rollup task last wrote to this row

Ownership rule: Monetary fields (total_revenue, total_tax_collected, total_discounts_given, average_order_value) are owned exclusively by the Celery rollup via update_or_create. Only order_count and items_sold receive real-time F() increments from transaction completion signals.

DailyInventorySnapshot

Table: daily_inventory_snapshot (products app, migration 0017) Unique constraint: (store, date)

Field	Type	Description
`store`	FK → Store	Store this snapshot belongs to
`date`	DateField	Calendar date (one row per store per day)
`total_sku_count`	PositiveInteger	Count of active InventoryLevel records
`total_units_on_hand`	PositiveInteger	Sum of `stock_on_hand` across active InventoryLevel records
`total_inventory_value`	Decimal(14,2)	Sum of `stock_on_hand × current_cost` across active records
`low_stock_count`	PositiveInteger	Count where `0 < stock_on_hand <= reorder_point`
`out_of_stock_count`	PositiveInteger	Count where `stock_on_hand = 0`
`snapshot_at`	DateTimeField	Auto-set when rollup last wrote this row

Celery Tasks

All tasks are registered in Celery Beat. Do not trigger these manually in production unless backfilling, since they are idempotent but may temporarily lock rows.

Task	Schedule	Description
`transactions.rollup_daily_revenue`	Daily at 00:05	Recomputes previous day's `DailyRevenueSummary` for all stores
`transactions.refresh_today_revenue_summary`	Every 15 min	Recomputes today's `DailyRevenueSummary` for all stores
`products.rollup_daily_inventory_snapshot`	Daily at 00:05	Recomputes previous day's `DailyInventorySnapshot` for all stores

Tasks use update_or_create on (store, date). Errors per store are isolated: one failing store does not abort the rollup for others.

Query Routing

TransactionAnalyticsService.get_sales_summary() routes queries automatically:

Completed calendar days (end date before today) → reads from DailyRevenueSummary on the replica database
Today's window (date range includes today) → falls back to live ORM aggregation on completed SaleTransaction records

The routing key is whether end_date < today. When a request spans historical + today, the live path is taken for the entire request.

Signal Integration

On SaleTransaction status transition to completed, a post-save signal performs F() increments on the matching DailyRevenueSummary row:

DailyRevenueSummary.objects.filter(store=instance.store, date=today).update(
    order_count=F('order_count') + 1,
    items_sold=F('items_sold') + item_count,
)

A _pre_save_status snapshot is stored on the instance before save to prevent double-counting if the signal fires on a subsequent status update (e.g., completed → completed on re-save). Only transitions into completed trigger the increment.

Backfill Command

Use the management command to populate historical rows after a new store is created, or to repair rows after a data incident:

# Preview affected date range without writing
python manage.py backfill_daily_revenue --start 2026-01-01 --end 2026-03-01 --dry-run

# Write rows for all stores
python manage.py backfill_daily_revenue --start 2026-01-01 --end 2026-03-01

The command processes one store at a time and reports progress. Rows are written with update_or_create, so running it twice is safe.

Composite Indexes

Migration 0021 (transactions app) adds composite indexes to three models to accelerate fulfillment analytics joins:

Model	Index fields
`Fulfillment`	`(transaction, status)`
`PickupSchedule`	`(store, scheduled_at)`
`Shipment`	`(transaction, carrier_status)`