ADR 008 Audit and Usage Analytics Store

ADR-008: Audit and Usage Analytics Store

Status

Accepted 2026-08-12, merged as #90. Deployed in test and prod (verified 2026-08-26): demi-audit-<env> workspaces hold DemiAudit_CL, DemiEvents_CL and DemiEventsHourly_CL, and demi-api-<env> carries AUDIT_DCR_ENDPOINT and AUDIT_DCR_IMMUTABLE_ID.

Since the eagle-analytics repoint (2026-09), auditEvent() writes to EagleAudit_CL in analytics-logs-<env> instead — the EPIC-wide audit table owned by eagle-analytics, with SourceApp: eagle-demi on every row. DemiAudit_CL is read-only from that date: nothing writes to it, its existing rows keep their 2556-day retention, and GET /admin/audit unions both tables until the old rows age out. DemiEvents_CL and its hourly rollup are unchanged; only the audit writer moved. No rows were migrated: the Logs Ingestion API overwrites a TimeGenerated older than two days with the time of ingest, which would have dated every historical row to the migration itself.

Context

DEMI had no audit trail: no record of who published a document, deleted a project or was issued an API key, and no usage data either. The requirement arrived as two things at once, and treating them as one was the first mistake available:

	Audit	Usage analytics
Volume	thousands/day	millions/month
Query	exact rows, "who touched X"	aggregates, trends
Retention	7 years, compliance-driven	13 months
Identity	must be recorded	must be minimised
Deletion	must not be possible	must be honoured on request

The last two rows are irreconcilable in one table. Everything below follows from taking them seriously.

Decision

Azure Monitor Logs, two custom tables, written through a Direct data collection rule.

demi-api (UAMI demi-identity-<env>)
  │  auditEvent() / analyticsEvent() → in-memory buffer → flush 1s / 100 rows / 800KB
  ▼  POST, managed-identity bearer token, no secret anywhere
Direct DCR ── transform masks SourceIp to two octets
  ├─ DemiAudit_CL    Analytics plan,  730d queryable / 2556d total
  └─ DemiEvents_CL   Auxiliary plan,  400d
                          │ two summary rules, hourly
                          ├─▶ DemiEventsHourly_CL     Analytics plan, 730d  (events, users)
                          └─▶ DemiDownloadsHourly_CL  Analytics plan, 730d  (downloads, bytes per document)

What gets a row

Audit records authenticated create, update and delete. That is the whole rule. It is written down because the way this trail acquired holes was not a broken audit call — it was handlers added later that nobody thought to check against anything. Applied as a checklist against every route carrying authMiddleware + requireWrite, it found seven missing at once, including all three boundary mutations. Check the next handler against the rule rather than against memory.

Action	Written by
project.create / .update / .delete / .push	controllers/nosql/project.js
document.create	controllers/nosql/document.js — twice: the JSON route, and the upload route with detail.via = 'upload'
document.update, .publish / .unpublish, .delete, .push	controllers/nosql/document.js
document.ingest	both chunk-ingest paths, detail.streamed distinguishing them
document.download	only when the document is unpublished
boundary.create / .update / .delete	controllers/nosql/boundary.js
apikey.create / .revoke	controllers/nosql/api-key.js
wildfire.sync	controllers/wildfire.js

Two shapes worth keeping consistent when adding the next one:

• One row per request, never per item. document.ingest writes once for a document, not once per chunk; wildfire.sync writes once for a job that patches every project. The counts go in detail, and a test asserts rows == requests.
• A second door onto the same action reuses the action name, with a detail field separating them — document.create with via: 'upload'. Two names would make "who created documents" a query the reader has to remember to union.

Deliberately not recorded: reads (a download is the moment content leaves, and that one is audited); denied and failed requests; unauthorised reads, which return 404 by design, so a row would announce the existence of the thing being hidden.

Why not a database

The obvious alternative was a Cosmos container, which is what an earlier attempt in the repository built. Rejected on what it costs to finish, not on storage price:

Option	Year-7 steady state	Still to build
Azure Monitor (this)	~$1.60/mo	nothing — query engine, RBAC, retention, rollups, REST API included
Cosmos serverless	~$3.50/mo audit alone	schema, indexes, TTL job, query endpoint, paging, dashboards
Postgres Flexible B1ms	~$13/mo floor	same, plus patching and HA
Blob and parquet	~$0.30/mo	writer, compaction, and a query engine

At DEMI's volume the storage price is decision noise; engineering time is not. Second-order: every Cosmos query burns RU forever, while Analytics-plan queries cost nothing however hard a future UI hits them.

Why two tables and two plans

DemiAudit_CL is small, identity-bearing and kept seven years. The Analytics plan makes interactive queries free, which is what lets a UI read it without a per-query bill.

DemiEvents_CL is large and low-value per row. The Auxiliary plan ingests at roughly a twentieth of the Analytics rate, and an hourly summary rule rolls it into a small Analytics table so dashboards never scan the cheap tier. This is the mechanism that makes one workspace serve both workloads.

Why its own workspace

demi-logs-<env> sets workspaceCapping.dailyQuotaGb, which stops collection for the rest of the day once hit. That is the right backstop for application logs and unacceptable for a compliance record — a chatty error path would take the audit trail down with it. Removing the cap to make room trades one safety for the other; a second, uncapped workspace keeps both.

Diagnostic settings: the authenticated changes the app cannot see

Two changes are authenticated, consequential, and invisible to the app's own trail because they never pass through it:

Signal	Resource	Destination	Answers
AppServiceAuditLogs	demi-api-<env>	demi-audit-<env>	who signed in to Kudu/SCM and deployed
ControlPlaneRequests	demi-cosmos-<env>	demi-audit-<env>	who changed containers, throughput, firewall or RBAC

Both land in demi-audit-<env>, for the reason above: a record a daily cap can drop is not an audit record. Cosmos needs logAnalyticsDestinationType: 'Dedicated', or the rows disappear into the shared AzureDiagnostics column soup and CDBControlPlaneRequests never appears as a table at all. Name the settings distinctly from the landing zone's own setByPolicy-*; a colliding name has the two overwrite each other on every deploy.

Deliberately off, all three of the "switch on for the length of an incident" kind: AppServiceHTTPLogs (middleware/http-logger.js already ships method, path, status, duration, IP and principal per request, and the platform version carries no principal — more cost, less information), Cosmos DataPlaneRequests (one row per operation), and APIM gateway request logs (GatewayLogs / ApiManagementGatewayLogs) — not available at all on the Consumption tier (verified 2026-09-08; Microsoft Learn lists Azure Monitor/Log Analytics request logs as unsupported on Consumption). The supported path on Consumption is an Application Insights logger on the APIM instance (Microsoft.ApiManagement/service/loggers + service/diagnostics), not adopted yet. Storage data-plane logs are also off: the document.download audit row names the person, which a storage log cannot.

Why anonymous identity expires instead of being deleted

For anonymous callers, AnonId and SessionId in DemiEvents_CL are HMACs under a salt that rotates every 24 hours. No deletion path is needed for a table plan that has no cheap targeted delete — identity stops being linkable by construction rather than by anyone remembering to act.

SessionId is hashed, not stored raw. It is caller-supplied and stable for as long as the client reuses it, so joining on a raw value re-linked a person's searches straight across the salt rotation, which would defeat the whole argument.

Why signed-in activity is deliberately not anonymous

The rotation above is a privacy property for the public, not a blanket policy.

A signed-in caller is attributable. DemiEvents_CL and DemiAudit_CL both carry ActorId (Keycloak sub, stable across a rename), ActorName (preferred_username, readable without a Keycloak lookup) and ActorType. Anonymous rows leave all three empty and keep only the rotating hash. Which of our people searched for this is exactly the question this data will be asked, and a table that cannot answer it is not worth keeping.

Stated plainly, because it is a real trade: DemiEvents_CL holds staff identity for 400 days. That is a deliberate choice about accountability for privileged access, and it does not extend to the public, whose rows are unlinkable after 24 hours.

The actor types, and why the distinction is not cosmetic

Type	Credential	Recorded as
user	Keycloak bearer token with sub	the person, by sub and username
service	Keycloak service account (client_credentials)	the client, by clientId / service-account-<clientId>
api-key	registry key, own roles and expiry	the key id
break-glass	shared ADMIN_API_KEY	the credential, never a person
anonymous	none	nothing

Two defects here were found only by looking at real rows, and both wrote a false audit record rather than a missing one — which is worse, because a gap is visible and a wrong name is not:

• Typing on keyId || azp filed a real person signing in through eagle-admin-console as a service, because they carry azp, and filed the break-glass key as a person, because it carries neither.
• Keycloak service accounts carry a sub like any token, so testing sub first recorded a shared client secret as a human being, in a table kept for seven years. Service accounts are classified before the human branch, and break-glass detection requires sub to be absent so an identified caller cannot be laundered into the shared bucket.

Why masking lives in the DCR, not the app

The transform masks SourceIp to two octets at ingest, so it cannot be bypassed by whatever calls the ingestion endpoint. The one path around it is the failure path — when a flush is abandoned the rows go to the application logger instead — so that path redacts by hand: audit rows keep everything but the IP, analytics rows keep only their shape.

Why fire-and-forget rather than fail-closed

The writer never blocks or fails a request. On giving up it drops the payload into the error log and a severity-1 alert fires on that: NIST SP 800-53 AU-5's "alert and continue", which is also what CloudTrail and Azure Activity Log do.

Failing closed was rejected on two grounds. The audit call happens after the mutation, so a 500 would report an already-applied publish as failed and the caller would retry. And a separate store can never be atomic with the entity write anyway, so awaiting it buys a probability, not a guarantee. If a hard regulatory gate ever appears, the answer is the outbox pattern — audit row in the same container and partition as the entity, one transactional batch — not awaiting a remote API.

Consequences

Accepted:

• Not legally tamper-proof. A workspace Contributor can purge rows or shorten retention. True immutability needs an export to an immutable-policy blob container.
• Ingest latency is 20 seconds to several minutes, so a UI cannot show an action "just now".
• Auxiliary tables have no alert rules, no data export rules, single-table KQL, and queries billed per GB scanned.
• Not a product-analytics tool: funnels are hand-written KQL, with no session stitching.
• The buffer is in memory, so a forced kill loses up to one flush interval. app.hook.appTerminate covers graceful recycles, which is every recycle observed.

Traps, each one paid for once:

• Total retention past two years takes only whole years from a fixed list — 2555 is rejected, 2556 is correct.
• A summary rule creates its destination table at the workspace default retention if the table does not exist yet. Declare the table, and make the rule depend on it.
• Same failure, second form: a built-in table fed by a diagnostic setting also sits at the workspace default. AppServiceAuditLogs and CDBControlPlaneRequests were both at 30/30 in demi-audit-test, measured, so both are declared at 90/730. They take no plan and no schema — Azure owns those, and only retention is ours. A what-if shows them as Modify rather than Create, because Azure pre-declares built-in table definitions in every workspace, which is also why the retention PUT works on the first deploy rather than needing data to land first.
• DemiEvents_CL returns zero rows to a direct query long after ingestion, while the summary rule reads it happily. Trust the DCR metrics and the rollup, not a direct count. This looks exactly like a broken pipeline and is not.
• host.json's logging.applicationInsights governs the Functions host process. Application logs ship from the worker via useAzureMonitor() at samplingRatio 1.0 and are unaffected by it. Sampling settings there cannot protect audit lines, and excluding Trace removes the only backpressure valve on the telemetry class that scales with traffic.

References

• PR #90 — implementation and review thread
• azure/modules/audit-logs.bicep, src/utils/audit.js
• Environment-Reality-and-Operational-Gotchas — deploy verification and the Power BI licensing gate
• Azure-Environments — spend and budgets
• Operating-DEMI-Data-Plane — GET /admin/audit and GET /admin/analytics, the read path off this store