Auditing storage, sharding, and query APIs

The write side uses the full storage-neutral AuditLogEntry; the read side returns only the 16-field AuditEnvelope. Query APIs do not expose request/response bodies, entity diffs, or exception details. Resource fields that need additional display are processed by module-owned IAuditEnvelopeProjector implementations—for example, Identity user data remains subject to Field Security.

1. Six SqlSugar split-table families

SqlSugarAuditTableInitializer creates the current buckets during --init-schema; any initialization exception propagates and fails the command. Future buckets are created by SqlSugar split-table writes according to event time.

Two configuration boundaries remain. SchemaInitializationCommand does not currently inspect AutoCreateTables=false. AuditShardingSchedule is validated, but GetShardingType does not participate in entity mapping, so physical granularity still comes from the [SplitTable] declarations above.

2. SqlSugar write routing

The Store groups by category and writes all six groups in one database transaction:

• Activity uses HttpMethod plus Module=Http to distinguish request from business activity;
• CapConsumer/BackgroundJob share a table with a stable marker in RunPars;
• Security/Exception share a table with Security or Exception in Level;
• Exception stores a bounded public error summary and does not project an arbitrary stack into the list API;
• each group deduplicates by stable ID in the target split table and rejects different evidence under the same ID.

Six physical families are not six logical categories. Query and retention must retain the category markers rather than inferring CAP, Job, Security, or Exception from table name alone.

3. Seven Mongo collections and indexes

Mongo stores the categories in:

audit_activity, audit_entity_change, audit_security, audit_exception, audit_external_request, audit_cap_consumer, and audit_background_job.

Read, write, and retention share MongoAuditIndexInitializer before first collection access. It:

• removes the legacy global ttl_occurred_at index;
• creates common query indexes for every collection;
• resets initialization state after cancellation or failure so a later call can retry;
• treats old AuditTtlEnabled/AuditTtlDays as a migration warning and no longer creates a category-independent TTL;
• fails closed when the connection or database configuration is incomplete.

Audit:
  # Switch query, write, retention, and durable entity-change storage together.
  StoreProvider: Mongo
Mongo:
  # Inject from a secret provider in production; do not commit credentials.
  ConnectionString: "secret-ref:mongodb-audit"
  Database: "bitzorcas"

Mongo uses $setOnInsert with _id=AuditId, then rereads and compares the complete document. Existing different evidence under the same ID is not overwritten.

4. Dapper read adapter

Dapper replaces only IAuditQueryPort; it is not the general writer or retention adapter. It discovers SQL Server split tables from shared definitions with these bounds:

• at most 512 physical tables per prefix;
• tenant, time, user, module, TraceId, category, and cursor predicates are pushed into SQL where the table supports them;
• each table returns only the candidates required by AuditPageWindow.FetchCount, rather than materializing every match;
• table and category results use the same stable merge;
• names come from controlled prefixes and the system catalog, never a client-provided physical table.

It still depends on SQL Server’s catalog and dialect. Do not treat the Dapper audit reader as a cross-database replacement for MySQL or SQLite.

5. AuditEnvelope and projection

The public fields are AuditId, OccurredAt, Category, TenantId, CallerType, UserId, ClientId, CorrelationId, TraceId, Module, ResourceType, ResourceId, Action, Result, DurationMs, and SensitiveFieldsMasked.

Store mappers bound any legacy payload-like column. The query handler then applies every matching IAuditEnvelopeProjector in sequence. A projection failure fails the query instead of returning the ungoverned value.

6. Query and export routes

The list accepts Category, UserId, Module, TraceId, From, To, PageIndex, and PageSize. The handler normalizes PageIndex to at least one; an invalid PageSize or one above 200 falls back to 20. TenantId is never public input: it comes from trusted CurrentUser, and the route provides no Host cross-tenant bypass.

Export reuses the same query port and projector chain and reads batches with an OccurredAt + AuditId cursor. It permits csv/json only and requires a non-platform tenant, authenticated user, and an idempotency key of at most 128 characters. The common Export subsystem still governs download authorization, file retention, and DLP.

7. Global page contract

All three providers use AuditPageWindow and AuditCandidateWindow:

1. each physical source returns skip + pageSize candidates from the global beginning;
2. candidates are linearly merged by OccurredAt desc, AuditId desc and continuously trimmed to the window limit;
3. one global Skip/Take runs only after every source is merged;
4. total counts are accumulated independently.

This fixes the old per-category-skip error on later pages. Offset mode materializes at most 10,000 candidates; beyond that window Items is empty while TotalCount remains available. Deep or continuous reads use the internal cursor predicate:

OccurredAt < cursorTime OR (OccurredAt = cursorTime AND AuditId < cursorId).

Cursor mode reads only PageSize from each source, keeps memory bounded, and avoids crossing the same boundary again when newer records arrive between pages.

8. Provider filter differences

GET /api/audit?traceId=... does not currently narrow results in the default SqlSugar composition. Until that gap is fixed, do not treat the parameter as a reliable investigation filter; first constrain by time, category, and module and then inspect correlation values.

9. Tenant and impersonation boundary

List, export, and retention policy use the authenticated owner’s TenantId. They do not automatically switch to Effective Tenant while a user operates as another tenant. This prevents an impersonated session from using ordinary view permission to read the target tenant’s entire audit history.

A real Host cross-tenant investigation requires a separate command with purpose, approval, result limits, and its own audit trail. It must not be smuggled through a TenantId parameter, cleared ORM filter, or platform tenant 0.

10. Query acceptance contract

Investigations should first narrow candidates with authoritative time, category, and module filters, then continue with the cursor returned by the response:

# Do not use traceId as the sole filter until the default SqlSugar gap is fixed.
curl -fsS --get -H "Authorization: Bearer <TOKEN>" \
  --data-urlencode "category=Security" --data-urlencode "module=Authorization" \
  --data-urlencode "from=2026-08-10T00:00:00Z" --data-urlencode "pageSize=100" \
  https://<HOST>/api/audit

• equal category, outcome, and stable order across all three providers for the same fixture;
• Activity’s two tables, seven categories, first/second/final pages, and equal-timestamp AuditId order;
• offset 10,000 boundary, empty out-of-window page, and continuous cursor reads;
• strict Security/Exception and CAP/Job separation;
• provider-specific Tenant, User, Module, From/To, and TraceId tests;
• From>To, invalid tenant/identifier/time/cursor, cancellation, and timeout;
• fail-closed Identity Field Security projection;
• export retry, idempotency, DLP, file expiry, and download authorization;
• retryable Mongo index initialization and the Dapper physical-table cap.

Previous: Queue and delivery · Next: Retention and compliance