Reporting Mart Model, Paging, and Data Scope

The Reporting Mart is an explicit ADR 0305 read-model exception. It may be denormalized and written by events or aggregation jobs, but it is accessed through the narrow IReportingMartStore. It is not an OLTP aggregate and must never become the source of truth for Ticket or Audit commands.

1. Owner-local Mart exception

Earlier framework-level Rpt*Entity classes and manual EF configurations were removed. Reporting Infrastructure owns two records:

• RptTicketSummaryRecord;
• RptAuditActivityDailyRecord.

Both use [BitzTable] and generated metadata with IEntitySet<T>, allowing SqlSugar and EF Core adapters without a direct ORM reference. Architecture tests guard that isolation and prevent deleted framework rows from returning.

Provider neutrality is a source dependency property. It does not prove behavioral parity; that requires the same contract suite against both databases.

2. Ticket Mart shape

The physical row contains tenant and ticket identity, requester/assignee IDs, subject, priority/status names, opened/closed timestamps, LastEventId, and LastUpdatedAt. The unique key is (TenantId, TicketId) and the query index starts with TenantId, StatusName, and OpenedAt.

Important cross-module differences:

• Ticket requester/assignee columns support 64 characters, while the Mart currently allocates 36; ticket.assigned writes AssigneeId into that narrower column;
• the Mart Subject allows 500 characters, while the source Ticket Subject is 255;
• status and priority are copied display-like names, not catalog IDs or versions;
• LastEventId is persisted but not exposed;
• LastUpdatedAt is persisted but omitted from both response DTOs.

Identity width is a correctness contract. A provider may truncate, reject, or behave differently if a valid 64-character source ID is projected into a 36-character Mart column.

3. Ticket Upsert snapshot behavior

On update, the store replaces requester, assignee, subject, priority, status, ClosedAt, LastEventId, and LastUpdatedAt. It preserves the OpenedAt value from the first insert. ClosedAt is a snapshot field: a null input clears the previous close time, which is how ticket.reopened is represented.

existing.RequesterId = summary.RequesterId;
existing.AssigneeId = summary.AssigneeId;
existing.Subject = summary.Subject;
existing.PriorityName = summary.PriorityName;
existing.StatusName = summary.StatusName;

// OpenedAt remains the value from the first insert; a later summary.OpenedAt is ignored.
existing.ClosedAt = summary.ClosedAt;
// LastEventId and LastUpdatedAt form the current duplicate/stale-event boundary.
existing.LastEventId = summary.LastEventId;
existing.LastUpdatedAt = summary.LastUpdatedAt;

This represents Reopened correctly, but it does not solve event ordering. An older snapshot can still replace newer state. LastEventId only supports a direct duplicate check; it is not a uniquely constrained Inbox, and LastUpdatedAt is not part of an atomic compare-and-set condition.

4. Ticket filtering and stable paging

The public handler validates the request before invoking the Query Shape:

• PageIndex must be at least 1, and PageSize must be in 1..1000;
• Status is trimmed, limited to 64 characters, and cannot contain control characters; whitespace means no filter;
• Status is not validated against the TicketStatus enum, so an unknown short value runs an exact query and normally returns an empty page;
• From and To apply inclusively to OpenedAt, and a range with both endpoints cannot exceed 366 days.

TicketSummaryListInput.MapFrom applies PagingLimits again as a store-level defensive boundary. The default ordering is (OpenedAt descending, TicketId descending). Because (TenantId, TicketId) is unique, ordering is deterministic within a tenant. Status case sensitivity still depends on the database collation/provider.

Offset paging is still vulnerable to inserts between page requests: rows may shift and appear twice or be skipped. For large or frequently changing Marts, prefer a cursor carrying both sort keys:

nextCursor = base64(openedAtTicks + ":" + ticketId)
next predicate = OpenedAt < cursor.openedAt
                 OR (OpenedAt == cursor.openedAt AND TicketId < cursor.ticketId)

PageWindow caps the default offset at 100000. A deeper request still returns the total and requested page metadata but has an empty Items collection. Clients must not interpret that empty page as proof that the filter has no matches.

5. Audit ordering uses the complete stable key

The Audit Query Shape orders by (ActivityDate descending, UserId descending, ActionType descending). Every query has a TenantId predicate, so these fields cover all remaining dimensions of the unique key. Do not reorder the result in the handler or store wrapper, because doing so would discard this guarantee.

6. Time-range semantics

Ticket queries use optional DateTimeOffset endpoints against OpenedAt. Audit queries require both DateTime endpoints against ActivityDate. Both ranges are inclusive, must be ordered, and cannot exceed 366 days. The contract still does not define:

• UTC normalization and accepted offsets;
• semantics of unspecified DateTime;
• tenant-local day boundaries;
• timestamp precision and provider rounding;
• whether to=2026-08-01 means the beginning or end of that day.

Use a documented UTC half-open interval [from,to) for timestamp queries. Use DateOnly or a normalized date key for daily buckets. Test the exact database precision rather than assuming .NET tick precision survives persistence.

7. Total and page consistency

Reporting executes the whole page through a generated Query Shape. The EF Core executor runs LongCountAsync and then reads the page; the SqlSugar executor uses ToPageListAsync to return rows and a total. The code does not promise snapshot-equivalent behavior across providers, so concurrent Mart writes can still make TotalCount and Items reflect different visible moments.

Interactive reports can use this as an eventual-consistency contract. Export batches are separate: default take 5000, maximum take 100000, and maximum offset 10000000. EstimateTotalRows and batch reads do not share a declared watermark. An audit-grade export therefore needs a fixed Mart watermark or database snapshot.

8. Atomicity and unique conflicts

Both Upserts are check-then-add/update. Concurrent first writes can race on a unique key; there is no unique-conflict recovery, compare-and-set, or aggregate-version condition. Last writer wins even when it carries an older business event.

public enum ProjectionWriteResult
{
    Applied,
    Duplicate,
    Stale,
    GapDetected
}

// The update succeeds only when the stored version is lower than the incoming version.
var result = await martStore.TryApplyTicketSnapshotAsync(
    snapshot,
    expectedLowerThanVersion: snapshot.AggregateVersion,
    cancellationToken);

// Callers can acknowledge duplicate/stale events and retry real infrastructure failures.

An atomic provider Upsert still needs an ordering predicate. “No unique exception” and “correct final state” are different guarantees.

9. Index and plan review

Review indexes against real predicates and distributions:

• Ticket: tenant + status + opened time, with TicketId as a paging tie-breaker;
• Audit: tenant + activity date, then user/action filters when supported;
• uniqueness must include TenantId;
• include/projected columns should reflect the provider and measured plan, not guesswork.

Capture query plans at production-scale cardinality. Status may be low-cardinality; a single wide covering index can cost more writes and storage than it saves.

10. Retention and derived-data governance

Projection data does not escape source retention or erasure duties. Define how long closed tickets and per-user activity remain, whether subject is archived or masked, which legal holds apply, and how cleanup advances without blocking hot queries.

For Ticket Subject and person IDs, erasure may delete the row, pseudonymize selected columns, or keep a legally required metric. Prevent replay of old events from reintroducing erased values after cleanup.

11. Dual-ORM store test matrix

1. Identical insert/update/query snapshots in SqlSugar and EF Core.
2. Tenant isolation for count, page, find, and Upsert.
3. Status trim, unknown values, and case/collation parity.
4. The 366-day limit, UTC, offsets, precision, and inclusive boundaries.
5. Invalid pages, the three-key Audit order, and the 100000 offset boundary.
6. Concurrent writes while a Query Shape obtains total and page rows.
7. Concurrent insert unique conflicts and update compare-and-set.
8. 64-character identities, long subject, unknown status/priority.
9. Reopen clears ClosedAt and stale events are rejected.
10. Replay after retention or erasure.

12. Review commands

# Review physical columns, indexes, and query predicates as one contract.
rg -n "BitzTable|BitzIndex|BitzColumn|CountAsync|PageByDescendingAsync" \
  src/Platform/Reporting -g '*.cs'

# Expose check-then-write and the ClosedAt snapshot assignment.
rg -n "FirstOrDefaultAsync|AddAsync|UpdateAsync|ClosedAt =" \
  src/Platform/Reporting/BitzOrcas.Platform.Reporting.Infrastructure -g '*.cs'

# Inspect Query Shapes, paging limits, and stable ordering.
rg -n "ExecutePageAsync|PagingLimits|ReadModelSort|MaximumPeriod" \
  src/Platform/Reporting src/Framework/BitzOrcas.Domain/Abstractions/Queries -g '*.cs'

Back to Reporting · HTTP contracts · Testing and GA