Audit Activity Daily Aggregation

Rpt_AuditActivityDaily looks like a completed report because it has a record, store Upsert, query, DTO, and endpoint. It is not an operational pipeline: nothing in production source calls its write port. This chapter separates the implemented storage contract from the aggregation system that still has to be designed.

1. Physical grain

The record contains:

The unique key is (TenantId, ActivityDate, UserId, ActionType). Because ActivityDate retains time-of-day, 2026-07-15T00:00 and 2026-07-15T08:00 are different rows even if both represent the same tenant business day.

2. Upsert means replacement

The store reads the unique key. It inserts a new row or assigns ActivityCount and LastUpdatedAt on the existing row.

// The lookup uses every column in the physical aggregation grain.
var existing = await records.FirstOrDefaultAsync(
    x => x.TenantId == projection.TenantId
         && x.ActivityDate == projection.ActivityDate
         && x.UserId == projection.UserId
         && x.ActionType == projection.ActionType,
    cancellationToken);

if (existing is null)
{
    // A missing grain becomes one absolute-count snapshot.
    await records.AddAsync(ToRecord(projection), cancellationToken);
}
else
{
    // The input is interpreted as a complete snapshot for the key.
    existing.ActivityCount = projection.ActivityCount;
    existing.LastUpdatedAt = projection.LastUpdatedAt;
    await records.UpdateAsync(existing, cancellationToken);
}

An aggregator that sends delta +1 will repeatedly store 1. A retry-safe producer should compute an absolute bucket count or the storage API should be redesigned as an atomic increment with a separate deduplication key. Do not mix these two contracts.

3. No producer exists

A repository-wide reference search for UpsertAuditActivityDailyAsync finds only IReportingMartStore and ReportingMartStore. There is no:

• Auditing integration-event consumer;
• scheduled aggregation executor;
• SQL group-by materializer;
• stream checkpoint;
• backfill or rebuild CLI;
• reconciliation job.

The query handler test configures a mocked store response. It verifies mapping and tenant delegation, not data production. Until a producer exists, the endpoint normally returns empty data or externally inserted rows.

4. Current query contract

Use QUERY /api/reports/activities/daily; clients that cannot send QUERY use POST /api/reports/activities/daily/_query. There is no GET route. The request body has required From/To values and defaults PageIndex to 1 and PageSize to 20.

Before the store runs, the handler requires:

• From no later than To and a range no longer than 366 days;
• PageIndex at least 1 and PageSize in 1..1000;
• an authenticated User/Delegated caller with a valid TenantId and positive UserId;
• reporting.activity.read and a final DataScope exactly equal to Tenant.

The response is a complete PagedResult<AuditActivityDailyRow>. The store applies an inclusive ActivityDate range and defaults to (ActivityDate descending, UserId descending, ActionType descending). With TenantId fixed by the handler, these fields cover every remaining dimension of the unique key. The public row contains ActivityDate, UserId, ActionType, and ActivityCount; it omits LastUpdatedAt and any aggregation watermark.

5. Date and timezone invariant

A “day” must be named precisely. Common choices are:

1. UTC day: simple and stable, but may not match tenant operations;
2. tenant business day: better for reports, requires a versioned tenant timezone;
3. actor-local day: rarely appropriate for tenant aggregation.

For a tenant business day, derive [startUtc,endUtc) from a local DateOnly and timezone. Persist the logical date separately from processing timestamp.

var localDate = DateOnly.FromDateTime(tenantLocalNow.DateTime);
var startLocal = localDate.ToDateTime(TimeOnly.MinValue, DateTimeKind.Unspecified);
var endLocal = localDate.AddDays(1).ToDateTime(TimeOnly.MinValue, DateTimeKind.Unspecified);

// Resolve through the tenant's IANA/Windows timezone; DST may make the UTC span 23 or 25 hours.
var startUtc = TimeZoneInfo.ConvertTimeToUtc(startLocal, tenantTimeZone);
var endUtc = TimeZoneInfo.ConvertTimeToUtc(endLocal, tenantTimeZone);

// Query the half-open interval so adjacent days never double count a boundary instant.
var absoluteCount = await auditSource.CountAsync(
    tenantId,
    userId,
    actionType,
    startUtc,
    endUtc,
    cancellationToken);

If tenant timezone can change, store the timezone/version used for a bucket or define that historical buckets are never reinterpreted.

6. ActionType contract

ActionType is an arbitrary string in Reporting. The documentation must define whether it stores endpoint permission, domain action, audit operation, or normalized reporting category. Otherwise renames fragment a time series.

A durable model uses a canonical code catalog and optional display localization. Unknown source actions should map to an explicit other bucket and emit a metric rather than disappear silently.

7. User and system activity

The current UserId column is required. Audit sources can include scheduled jobs, service accounts, anonymous attempts, deleted users, or impersonation. Establish stable bucket rules such as:

• actor identity plus actor type;
• a documented system pseudo-actor;
• effective actor and original actor for impersonation;
• a pseudonymized value after erasure, when policy permits aggregate retention.

Do not encode null semantics as an undocumented empty string because it will collide across unrelated actors.

8. Recommended incremental job

Process closed buckets after a lateness window, and reopen/recompute a bounded recent period for late audit records. Advance the checkpoint only after all bucket writes for the slice commit. Record source high-watermark, aggregation version, started/finished times, processed rows, and errors.

9. Checkpoint and idempotency

At-least-once scheduling means the same slice will run more than once. Absolute recomputation plus deterministic bucket keys makes retry safe. A checkpoint should be scoped by tenant, aggregation name/version, and source partition.

Never advance it in a finally block. A partial write followed by an advanced checkpoint creates a permanent gap. If the source supports consistent snapshots, record the snapshot high-watermark first and aggregate exactly to that position.

10. Backfill and policy changes

Backfill needs explicit tenant range, date range, aggregation version, dry-run, concurrency limit, rate limit, and approval/audit fields. Write to a shadow table or versioned partition, compare counts and samples, then promote.

When action classification or timezone policy changes, do not silently mix versions in the same time series. Recompute the affected range under a new aggregation version or declare a visible semantic boundary.

11. Privacy and retention

An aggregate can still be personal data when keyed by UserId. Define:

• retention for raw audit versus daily Mart;
• whether erasure deletes, pseudonymizes, or preserves regulated audit evidence;
• who may view person-level rows versus only tenant totals;
• how old backups and rebuilt projections honor erasure;
• whether low counts require suppression to reduce re-identification.

LastUpdatedAt should support operations, not extend personal-data retention indefinitely.

12. Required aggregation tests

1. UTC and tenant-day boundaries, including 23/25-hour DST days.
2. Same local date from different offsets maps to one bucket.
3. Absolute snapshot retry does not double count.
4. Crash before/after bucket write does not skip a checkpoint.
5. Late events recompute the bounded open period.
6. Duplicate source audit records follow source identity rules.
7. Unknown action and system actor mapping.
8. Cross-tenant isolation in source query, checkpoint, and Mart.
9. Concurrent backfill/live job conflict behavior.
10. Erasure, retention, and replay interaction.
11. SqlSugar/EF Core date precision parity.
12. Query watermark matches the completed aggregation position.

13. Review commands

# Production source currently has only the interface and Store; a producer must add a third call site.
rg -n "UpsertAuditActivityDailyAsync" src -g '*.cs'

# Inspect the physical date/key and public DTO freshness fields together.
rg -n "ActivityDate|ActivityCount|LastUpdatedAt|AuditActivity" \
  src/Platform/Reporting -g '*.cs'

# Target state: checkpoint, job, backfill, and reconciliation tests should be discoverable.
rg -n "Reporting.*(Checkpoint|Aggregation|Backfill|Reconciliation)|AuditActivityDaily" \
  tests -g '*.cs'

# Inspect the route, Read action, request boundaries, and three-field stable ordering.
rg -n "HttpRoute.Query|AuthorizationAction.Read|ValidateDateRange|ValidatePage|ReadModelSort" \
  src/Platform/Reporting -g '*.cs'

Back to Reporting · HTTP contracts · Testing and GA