Universal Audit Log Specification

Document Version: 2.1 Date: 25 March 2026 Status: Proposed for implementation Scope: Constellation platform and all active/future apps Previous Version: v1.0 (25 March 2026)

---

Revision History

Version	Date	Summary
1.0	25 March 2026	Initial specification. Core architecture, schema, shared API, rollout plan.
2.0	25 March 2026	Multi-agent review incorporation. Adds partitioning, retention, GDPR pseudonymisation, tamper-evident chain design, keyset pagination, granular access control, operational monitoring, export/reporting, SIEM integration, redaction framework, developer ergonomics, index strategy, immutability hardening, IP handling, enforcement mechanisms. Restructured rollout phases.
2.1	25 March 2026	Architecture alignment review. Retention policy aligned with Constellation_Architecture_Spec_v1 (SaaS = indefinite). GDPR pseudonymisation replaced with actor-identity-retained position + future ADR path. Keyset pagination changed to dual-support migration. CI enforcement scoped to mutating tools only with integration tests as merge gate.

---

1. Purpose

This document defines the concrete implementation approach for a universal audit-log mechanism across Constellation applications.

It turns the already-approved architectural direction into an executable design:

• audit is a cross-cutting platform concern
• audit writes are transactional and immutable
• audit capture happens in the tool layer (and other defined capture points)
• every module uses the same storage, query, and UI conventions
• audit data is compliant by design with GDPR, SOC 2, ISO 27001, and ITAR retention requirements

This spec also records which ideas should be adopted from Open Mercato and which should not.

---

2. Decision Summary

2.1 Primary Decision

Constellation SHALL implement universal audit logging as a shared platform capability, not as a separate application.

2.2 Package Boundaries

• @constellation-platform/db
  • owns the canonical audit schema contract
  • owns low-level audit writes and audit queries
  • remains ORM-agnostic and transaction-oriented
• @constellation-platform/audit
  • owns higher-level audit helpers and conventions
  • provides wrappers for critical/security-sensitive audit behaviour
  • provides reusable utilities for diff generation, redaction, and audit metadata normalisation
• apps/*
  • call shared audit APIs from their tool layer
  • do not define their own audit storage models
  • may expose module-local views and adapters over the shared audit APIs

2.3 Why This Is Not Another App

A separate app would be wrong for the primary write path because audit rows must commit in the same transaction as the domain mutation they describe. That requires the audit write to happen inside the same database transaction opened by the module tool.

Separate UI surfaces are acceptable. Separate write ownership is not.

---

3. Current State in Constellation

Constellation already has the correct foundation:

• shared audit schema and helpers in packages/platform/db
• immutable audit table in audit.audit_entries
• auditCritical() in packages/platform/audit
• audit query tool in Directory
• project-tracker audit UI and API stubs awaiting platform integration

Relevant current files:

• packages/platform/db/src/audit.ts
• packages/platform/db/migrations/001_audit_entries.sql
• packages/platform/audit/src/audit-critical.ts
• apps/directory/src/server/tools/audit.tools.ts
• apps/project-tracker/src/app/api/audit/route.ts
• apps/project-tracker/src/lib/entity-audit.ts
• apps/project-tracker/src/components/audit/AuditTrail.tsx

The largest gap is not storage. The gap is consistent capture and usage across apps, retention and compliance, and developer ergonomics.

---

4. Goals

1. Every state-changing business operation produces exactly one canonical audit entry.
2. Audit entries are written atomically with the state mutation they describe.
3. Audit is reusable across all modules without per-app schema duplication.
4. Audit history can be queried by tenant, actor, resource, organisation, and related resource.
5. Security-sensitive actions can trigger guaranteed durable downstream processing.
6. UI consumers can render a per-resource version history from shared APIs.
7. The design supports defence-tier tamper-evident chaining with a concrete implementation path.
8. Audit data minimises PII surface (IP truncation, context_json PII prohibition) while retaining actor identity for investigations per GDPR Article 17(3) exemptions.
9. Failed and denied operations are captured alongside successes.
10. Operational health of the audit subsystem is continuously monitored.

---

5. Non-Goals

1. This spec does not create a standalone audit microservice or app.
2. This spec does not replace domain events with audit events.
3. This spec does not require all read-access events to be logged immediately; access logging is defined as a separate extension path.
4. This spec does not introduce a generic CRUD framework for Constellation.
5. This spec does not require undo/redo support.

Open Mercato's undo/redo-capable action log is informative, but Constellation should not inherit that as a platform requirement.

---

6. Core Architecture

6.1 Capture Points

The primary capture point SHALL be the tool layer.

Rules:

• route handlers and server actions stay thin
• services apply domain rules and persistence orchestration
• repositories remain persistence-only
• tools open the transaction, call the service, compute audit input, write audit, and publish domain events if needed

Audit MUST NOT rely primarily on:

• Prisma middleware
• Prisma query extensions
• repository-side implicit hooks
• fire-and-forget background writes

Those mechanisms may exist for development diagnostics, but they are not authoritative enough for universal audit.

Additional Capture Points

The tool layer is the primary capture point, but it does not cover all auditable operations. The following table defines the complete set of capture points:

Capture Point	Actor Type	Audit Method	Example
Tool function (interactive)	USER	createAuditor(ctx) / withAuditedMutation()	User updates a task
Background job (cron/queue)	SYSTEM	Direct auditAction() with actor_type: 'SYSTEM'	Nightly status recalculation
Event-driven handler	SYSTEM	Direct auditAction() with correlation from source event	Cascade update from upstream event
Bulk operation	USER or SYSTEM	auditBatch(tx, entries[])	CSV import of 500 catalogue items
Migration script	SYSTEM	Migration audit record with script identifier	Data migration changing resource states

auditBatch(tx, entries[]) SHALL be provided in the platform API for bulk operations. It MUST write all entries within the supplied transaction and enforce the same validation as single-entry writes.

6.2 Transaction Pattern

All auditable writes SHALL follow this pattern:

1. open withTenantContext(...)
2. load current state when a before/after diff is required
3. execute domain mutation
4. compute normalised changes
5. call auditAction(tx, ...) or auditCritical(tx, ...)
6. publish domain event if required
7. commit transaction

If any step fails, neither the state change nor the audit row is committed.

6.3 Storage Pattern

Audit data remains in the shared audit schema, not in per-module schemas.

This matches the approved architecture: cross-cutting concerns such as tenancy, audit, and events live in platform schemas.

6.4 Partitioning Strategy

The audit.audit_entries table SHALL be range-partitioned on created_at with monthly partitions.

CREATE TABLE audit.audit_entries (
  -- columns defined in Section 7
) PARTITION BY RANGE (created_at);

-- Example: create partition for March 2026
CREATE TABLE audit.audit_entries_2026_03
  PARTITION OF audit.audit_entries
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

Partition management: automated creation via pg_partman or cron, at least 3 months ahead. Old partitions MAY be detached and archived (Section 6.5). A default partition MUST exist to prevent write failures. At high scale (>100M rows/month), composite partitioning (range + hash by tenant_id) SHOULD be evaluated.

6.5 Data Retention Policy

The approved Constellation architecture (Constellation_Architecture_Spec_v1.md §Retention) specifies:

Audit entries are retained indefinitely in the SaaS tier. Dedicated Cloud and On-Prem tiers allow configurable retention policies per tenant. Entries are never deleted — they are archived to cold storage after the retention window.

This spec conforms to that policy:

Tier	Online Retention	Archival	Notes
SaaS (default)	Indefinite	N/A — entries remain online	Matches approved architecture
Dedicated Cloud	Configurable per tenant	Cold storage after retention window	Tenant-configured
On-Prem	Configurable per tenant	Tenant-managed	Tenant-configured

Archival (Dedicated/On-Prem only): detach partition older than the tenant-configured online threshold, export to Parquet in cold storage (S3/GCS), record metadata in audit.archive_manifest, drop the detached partition after verification. Archived data MUST remain queryable on request within 48 hours.

Partitioning (Section 6.4) is still a Phase 1 requirement — it is an operational necessity for table management (VACUUM, backup, index performance) regardless of retention policy. Archival automation is Phase 6 (Dedicated/On-Prem tiers only).

Note: If a future ADR changes SaaS retention to a fixed window, the partition-based archival mechanism is ready. No schema changes would be needed.

---

7. Canonical Audit Model

7.1 Existing Fields to Keep

The existing audit.audit_entries contract remains the base:

• id
• tenant_id
• actor_id
• actor_type
• action
• resource_type
• resource_id
• module
• changes
• classification
• ip_address
• correlation_id
• created_at

7.2 Required Additions

The table SHALL be extended with:

• organisation_id UUID NULL
  • for organisation-scoped filtering inside a tenant
• parent_resource_type TEXT NULL
  • for related history views
• parent_resource_id TEXT NULL
  • allows non-UUID resource ids where needed
• context_json JSONB NULL
  • stores bounded metadata such as reason, status transition metadata, workflow instance ids, approval notes, import source, or system trigger info
  • MUST NOT contain PII (see Section 9.5)
• entry_hash TEXT NULL
  • reserved for tamper-evident chain
• previous_hash TEXT NULL
  • reserved for tamper-evident chain
• session_id TEXT NULL
  • for session correlation (NIST 800-171 requirement)
• user_agent TEXT NULL
  • for forensic analysis
• outcome TEXT NOT NULL DEFAULT 'SUCCESS'
  • values: 'SUCCESS' | 'FAILURE' | 'DENIED'
  • critical for security audit — failed and denied attempts MUST be captured
• duration_ms INTEGER NULL
  • operation duration for performance audit
• changed_fields TEXT[] NULL
  • array of top-level field names present in changes
  • enables efficient "which entries changed field X" queries via GIN index

7.3 Semantics

• resource_type
  • canonical domain resource type, e.g. directory.organisation, projects.task, catalog.entry
• resource_id
  • identifier of the direct subject of the change
• parent_resource_type / parent_resource_id
  • optional owning or related aggregate for unified history views
• changes
  • normalised before/after field diff, not arbitrary full payload dumps
• context_json
  • bounded operational context; never a replacement for changes; never contains PII
• outcome
  • reflects whether the operation succeeded, failed due to an error, or was denied by authorisation

7.4 Immutability

The append-only rule remains unchanged:

• no UPDATE
• no DELETE
• triggers reject mutation attempts
• application roles never receive mutation rights on existing audit rows

Immutability Hardening

PostgreSQL row-level triggers can be bypassed by superusers via DISABLE TRIGGER ALL. Mitigations:

1. Revoke pg_trigger from all application database roles. Only the migration role may alter triggers.
2. DDL event trigger to block TRUNCATE on audit tables (row-level triggers do not fire on TRUNCATE):

CREATE EVENT TRIGGER no_audit_truncate ON ddl_command_start
  WHEN TAG IN ('TRUNCATE') EXECUTE FUNCTION audit.prevent_truncate();

3. pg_audit extension (Phase 6) to log superuser actions including trigger state changes.
4. Monitor immutability trigger rejection count (Section 14). Any non-zero count indicates a bug or attack.

---

8. Index Strategy

Indexes SHALL be created to support the defined query patterns without over-indexing.

8.1 Required Indexes

#	Columns	Purpose
1	(tenant_id, created_at DESC)	Tenant time-range queries
2	(tenant_id, resource_type, resource_id, created_at DESC)	Per-resource history
3	(tenant_id, actor_id, created_at DESC)	Actor activity queries
4	(tenant_id, correlation_id)	Correlation lookup (tenant-scoped to prevent cross-tenant correlation leakage)
5	(tenant_id, organisation_id, created_at DESC)	Organisation-scoped queries
6	(tenant_id, parent_resource_type, parent_resource_id)	Related resource history
7	(tenant_id, module, created_at DESC)	Module filtering
8	GIN on changed_fields	"Which entries changed field X" queries

8.2 Index Guidance

• Do NOT add GIN indexes on changes or context_json JSONB columns. These columns are write-heavy and rarely queried by internal structure.
• All indexes are tenant-scoped (leading tenant_id) to align with RLS and prevent cross-tenant data leakage through index scans.
• Indexes are defined on the partitioned parent table; PostgreSQL propagates them to partitions automatically.

---

9. GDPR / Privacy — PII in Audit Entries

9.1 Problem

actor_id and ip_address are personally identifiable information subject to GDPR right-to-erasure (Article 17). Audit entries are immutable and must be retained for compliance and investigation purposes. These two requirements are in tension.

9.2 Current Position: Actor Identity is Retained

The approved architecture and requirements mandate that audit entries capture "actor (who)" and support tenant/platform investigations (requirements.md §3, Constellation_Architecture_Spec_v1.md §Retention). Audit rows SHALL continue to store actor_id directly — the "who did what" guarantee is non-negotiable for security investigations, compliance evidence, and SOC 2 Type II audit trails.

GDPR exemption basis: GDPR Article 17(3)(b) provides an exemption from erasure for "compliance with a legal obligation" and Article 17(3)(e) for "establishment, exercise or defence of legal claims." Constellation relies on these exemptions for audit data.

9.3 Future: Pseudonymisation Requires Its Own ADR

If legal or privacy counsel determines that the Article 17(3) exemptions are insufficient for certain jurisdictions or data categories, a pseudonymisation strategy (e.g., HMAC-based actor references with a deletable reverse-mapping table) MAY be adopted. However:

1. This would be a policy change affecting investigation capability, not a spec refinement.
2. It MUST be documented in a dedicated Architecture Decision Record (ADR) covering: regulatory analysis, jurisdiction scope, impact on investigation workflows, data controller obligations, and DPA (Data Processing Agreement) implications.
3. It MUST NOT be implemented without explicit sign-off from legal/privacy and the platform architect.

The schema reserves no columns for pseudonymisation at this time. The partition-based architecture makes a future migration feasible without rewriting existing rows.

9.4 IP Address Handling

IP addresses SHOULD be truncated at write time to reduce PII surface: IPv4 to /24, IPv6 to /48. If forensic precision is required for a specific tenant tier (e.g., defence), full IP MAY be retained with documented justification.

IP extraction MUST use a trusted proxy chain: maintain a list of trusted reverse proxy IPs, extract the client IP from X-Forwarded-For by walking right to left and stopping at the first untrusted hop, and store only the (optionally truncated) result.

9.5 context_json PII Prohibition

context_json MUST NOT contain PII. CI linting SHOULD flag known PII field names in context payloads during tests.

---

10. Shared Package API

10.1 @constellation-platform/db

@constellation-platform/db SHALL remain the canonical low-level API.

It SHALL own:

• auditAction(tx, opts)
• auditBatch(tx, entries[])
• queryAuditTrail(tx, opts)
• countAuditEntries(tx, opts)

AuditActionOptions SHALL be extended to include:

• organisationId?: string
• parentResourceType?: string
• parentResourceId?: string
• context?: Record<string, unknown>
• sessionId?: string
• userAgent?: string
• outcome?: 'SUCCESS' | 'FAILURE' | 'DENIED'
• durationMs?: number

10.2 @constellation-platform/audit

@constellation-platform/audit SHALL evolve into the shared high-level layer.

It SHALL provide:

• auditCritical(tx, opts) — current behaviour retained; MUST redact before publishing to outbox (see Section 12.3)
• buildAuditDiff(before, after, opts?) — computes normalised diffs (see Section 10.4)
• createAuditor(ctx) — factory for scoped audit helpers (see Section 10.3)
• withAuditedMutation(tx, opts, mutationFn) — higher-order ceremony wrapper (see Section 10.3)
• extractRequestAuditMeta(request) — normalised ipAddress (truncated), userAgent, sessionId, optional request context

10.3 Developer Ergonomics

The 7-step audit ceremony (Section 6.2) requires many repeated parameters. Two helpers SHALL reduce boilerplate:

createAuditor(ctx)

Factory that closes over common context, returning a scoped helper:

const auditor = createAuditor({
  tenantId: ctx.tenantId,
  actorId: ctx.actorId,
  organisationId: ctx.organisationId,
  correlationId: ctx.correlationId,
  ipAddress: ctx.ipAddress,
  sessionId: ctx.sessionId,
  userAgent: ctx.userAgent,
});

// Scoped helper — only action-specific params needed
await auditor.mutation(tx, {
  action: 'UPDATE',
  module: 'projects',
  resourceType: 'projects.task',
  resourceId: task.id,
  parentResourceType: 'projects.project',
  parentResourceId: task.projectId,
  before,
  after,
});

This reduces per-call parameters from ~13 to ~5.

withAuditedMutation(tx, opts, mutationFn)

Higher-order helper that wraps the full 7-step ceremony:

await withAuditedMutation(
  tx,
  {
    auditor,
    action: 'UPDATE',
    resourceType: 'projects.task',
    resourceId: task.id,
  },
  async (tx) => {
    const before = await repo.findById(tx, task.id);
    const result = await repo.update(tx, task.id, data);
    return { before, after: result };
  },
);

The helper calls mutationFn to obtain { before, after }, computes the diff via buildAuditDiff(), writes the audit entry, and returns the mutation result. If the mutation throws, the transaction rolls back normally with no audit row.

10.4 buildAuditDiff Specification

buildAuditDiff(before, after, opts?) computes a normalised field-level diff.

Output format:

{ [fieldName: string]: { before: unknown; after: unknown } }

Behaviour rules:

• Only changed fields are included in the output.
• Nested objects are flattened with dot notation up to configurable depth (default: 3). Example: address.city.
• Arrays are stored as before/after of the full array, not per-element diffs.
• Maximum diff size: 64 KB after JSON serialisation. If exceeded, the diff is truncated and a _truncated: true flag is added.
• The diff populates the changed_fields column with the top-level keys present.

Options:

interface BuildAuditDiffOptions {
  redact?: RedactionPolicy;
  ignoreFields?: string[];
  maxDepth?: number; // default: 3
  maxSize?: number; // default: 65536 (64 KB)
}

10.5 What Stays Out of the Platform Package

The platform package SHALL NOT own:

• module-specific display strings
• module-specific permission rules
• module-specific route handlers
• module-specific field redaction logic beyond generic hooks

Those remain app/module concerns built on top of the shared audit primitives.

---

11. Tamper-Evident Chain Design

11.1 Overview

Tamper-evident chaining provides cryptographic proof that audit entries have not been modified, reordered, or deleted after creation. The chain is per-tenant to avoid global serialisation bottlenecks.

11.2 Hash Algorithm

SHA-256 SHALL be used for all chain hashes.

11.3 Hash Computation

entry_hash = SHA256(
  previous_hash ||
  tenant_id ||
  actor_id ||
  action ||
  resource_type ||
  resource_id ||
  SHA256(changes) ||
  created_at
)

The changes field is hashed separately to bound the input size.

11.4 Chain Head Management

An audit.chain_heads table tracks the latest hash per tenant:

CREATE TABLE audit.chain_heads (
  tenant_id UUID PRIMARY KEY, last_hash TEXT NOT NULL,
  last_entry_id UUID NOT NULL, updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Concurrency: SELECT ... FOR UPDATE on the tenant's chain_heads row serialises hash computation per tenant. Alternative for high-throughput tenants: assign a per-tenant sequence number at write time, then compute hashes in a background job that processes entries in sequence order (decouples chaining from the write path).

11.5 Integrity Verification

A nightly job SHALL walk each tenant's chain: load entries in created_at order, recompute each entry_hash, and compare against the stored value. On mismatch, alert immediately and record the break point.

11.6 Chain Recovery After PITR

After point-in-time recovery, identify the last verified hash ("known-good" point), re-seal all subsequent entries by recomputing hashes forward, and record the re-seal event in the audit log.

---

12. Query and UI Model

12.1 Shared Query Contract

Every app SHALL be able to query audit entries through the shared query API with filters for:

• tenant
• organisation
• resource type and resource id
• parent resource type and parent resource id
• actor id
• module
• action
• outcome
• date range
• changed field (via changed_fields array)
• pagination (dual: offset/limit + keyset cursor)

12.2 Pagination: Dual Support with Keyset Migration Path

The existing audit query contract uses limit and offset (tasks.md §18.2, AuditTrailResult in audit.tools.ts). This spec does NOT silently replace that contract.

Phase 1 — Dual support: queryAuditTrail() SHALL accept BOTH pagination styles:

interface AuditQueryOptions {
  // ... filters ...
  // Existing contract (retained for backward compatibility)
  limit?: number; // default: 50, max: 200
  offset?: number;
  // New keyset pagination (preferred for new consumers)
  cursor?: { createdAt: Date; id: string };
}

interface AuditQueryResult {
  entries: AuditEntry[];
  // Existing contract fields (retained)
  total: number;
  limit: number;
  offset: number;
  // New keyset fields (added alongside)
  nextCursor?: { createdAt: Date; id: string } | null;
}

• When cursor is provided, it takes precedence over offset. The response includes both nextCursor and the legacy offset/total fields for backward compatibility.
• total always uses exact COUNT(*) with the same tenant-scoped WHERE clause as the main query. pg_class.reltuples is NOT suitable here because it reflects unfiltered table/partition cardinality, not tenant-filtered counts. For performance on large result sets, the count query benefits from the (tenant_id, created_at DESC) index and partition pruning. If count performance becomes a bottleneck, a per-tenant count cache (updated via trigger or background job) MAY be introduced as an optimisation.
• Existing consumers (Directory queryAudit, API routes) continue to work unchanged.

Phase 3+ — Migration: New consumers SHOULD use keyset pagination. Existing consumers SHOULD migrate when their UI supports cursor-based "load more" instead of page numbers. Offset support MAY be deprecated via a future ADR once all consumers have migrated.

The API cap of 200 rows per page applies to interactive queries. Streaming export (Section 15) handles bulk extraction.

12.3 Two Shared Read Patterns

Constellation SHALL support two standard audit views:

1. Per-resource history

   • used on entity detail pages
   • filters by resource_type/resource_id
   • may optionally include related children via parent resource fields

2. Tenant-wide audit search

   • used by admins, compliance officers, and investigators
   • filters by actor, module, action, date, resource, organisation, and outcome

12.4 UI Reuse

A shared audit UI package or shared component set SHOULD be introduced after the API contract is stable.

It should provide:

• AuditTrail / VersionHistory panel
• diff renderer for field changes
• pagination / load-more behaviour (keyset-aware)
• resource- and actor-display adapters
• outcome badge (success / failure / denied)

This is a reusable component pattern, not a standalone app.

---

13. Audit Access Control

13.1 Granular Permissions

Access to audit data SHALL be controlled by granular permissions, not a single read:audit flag:

Permission	Scope	Description
audit:read:own	Actor's own entries	User can view their own audit trail
audit:read:org	Organisation	User can view audit entries within their organisation
audit:read:tenant	Full tenant	Admin can view all audit entries for the tenant
audit:read:classified	Classified entries (RESTRICTED and above)	Security officer can view entries with classification in (RESTRICTED, CONFIDENTIAL, SECRET) per Constellation_Architecture_Spec_v1 §Classification
audit:export	Tenant	Permission to trigger bulk export (Section 15)

13.2 RLS Enforcement

RLS policies SHALL enforce these scopes at the database level. Default: tenant isolation. audit:read:own adds actor filter; audit:read:org adds organisation filter; audit:read:classified is required for rows where classification is RESTRICTED, CONFIDENTIAL, or SECRET (the Constellation classification set defined in Constellation_Architecture_Spec_v1). UNCLASSIFIED entries are visible to all scopes that pass tenant isolation.

13.3 Audit-of-Audit-Access

Audit data access SHALL itself be logged. Query parameters and result counts are recorded in audit.access_log_entries (Section 16). Required for SOC 2 Type II evidence.

---

14. Operational Monitoring

14.1 Required Metrics

The audit subsystem SHALL expose the following metrics:

Metric	Type	Alert Threshold
Audit write latency (p50, p95, p99)	Histogram	p99 > 10ms
Table size (heap + TOAST + indexes)	Gauge, daily	Configurable per environment
Rows per minute by module	Counter	Anomaly detection (>3 sigma deviation)
Immutability trigger rejection count	Counter	Any non-zero value (immediate alert)
Chain integrity status	Gauge (per tenant)	Any failure (immediate alert)
VACUUM age / transaction ID wraparound distance	Gauge	< 50M transactions remaining
Partition count and next auto-create date	Gauge	< 2 future partitions remaining

14.2 SLOs

SLO	Target
Audit write latency p99	< 10ms
Audit availability (writes succeed)	99.99%
Chain integrity verification	100% of tenants verified nightly

Metrics SHALL be exposed via the platform metrics interface (Prometheus-compatible) with Grafana dashboard templates.

---

15. Export and Reporting

15.1 Streaming Export

A streaming export endpoint SHALL be provided for compliance auditors, not subject to the 200-row interactive API cap. Formats: CSV, JSON Lines. Filters: date range, module, actor, resource type, organisation, outcome. Requires audit:export permission. Uses chunked transfer encoding. Rate-limited to one concurrent export per tenant.

15.2 Export Auditing

Export events are themselves audited with action: 'EXPORT', resourceType: 'audit.audit_entries', and context containing the format, filters, and row count.

---

16. Access Logging Extension

Mutation audit and read/access audit SHALL be separated.

16.1 Phase 1

Phase 1 covers state-changing operations only plus audit-access logging (Section 13.3).

16.2 Phase 2

Phase 2 MAY add a separate access-log path for:

• document access
• downloads
• print events
• classified record views
• external share access

If implemented, access logs SHOULD use a separate table:

• audit.access_log_entries

This follows the useful Open Mercato split between action logs and access logs, but without adopting their full application structure.

---

17. SIEM Integration

17.1 Purpose

Security-classified audit entries SHALL be forwardable to an external Security Information and Event Management (SIEM) system for centralised security monitoring.

17.2 Integration Path

Forward via the existing outbox mechanism: auditCritical() already writes to the transactional outbox. A SIEM adapter consumes outbox events and forwards them in CEF or structured JSON as a background job.

17.3 Configuration

SIEM forwarding is configurable per-tenant (endpoint URL, format, authentication, classification filter). Some tenants may require real-time forwarding; others may not use SIEM at all.

---

18. Redaction Framework

18.1 RedactionPolicy Type

interface RedactionPolicy {
  paths: string[];
  strategy: 'omit' | 'hash' | 'mask';
}

• omit: remove the field entirely from the diff
• hash: replace the value with SHA256(value) — presence is recorded, content is not
• mask: replace with '***REDACTED***'

18.2 Default Sensitive Patterns

The platform SHALL ship with a default redaction policy that matches fields containing:

password, secret, token, key, credential, ssn, authorization

Modules MAY extend this list but MUST NOT reduce it.

18.3 Application in buildAuditDiff

buildAuditDiff() accepts an optional redact parameter. When provided, matching field paths are redacted according to the policy before the diff is returned.

18.4 Double Exposure Prevention

auditCritical() MUST apply redaction before publishing the entry to the outbox. The outbox event is consumed by downstream processors (SIEM, notifications) that may have weaker access controls than the audit table itself.

---

19. Open Mercato Reference

19.1 What Open Mercato Does Well

Open Mercato implements audit/history as a shared core module with useful patterns: central shared module, reusable history UI, explicit snapshots and computed diffs, separate action/access logs, and related-resource history support.

19.2 What Constellation Should Not Copy

Constellation SHOULD NOT copy Open Mercato's command-bus-centric audit design. Constellation is tool-layer-centric, uses Prisma in apps / raw SQL in platform, and does not need undo/redo coupling. Borrow structural ideas, not the command bus.

---

20. Usage Rules for Constellation Apps

20.1 Mandatory Rules

1. Every state-changing tool MUST write an audit entry.
2. Audit writes MUST happen inside the same transaction as the domain mutation.
3. Apps MUST use shared platform audit APIs.
4. Apps MUST NOT create app-local audit tables for domain mutations.
5. Repositories MUST NOT write audit rows directly.
6. Route handlers MUST NOT bypass tools for auditable mutations.
7. Failed and denied operations MUST be audited with outcome: 'FAILURE' or outcome: 'DENIED'.
8. Background jobs and event handlers MUST use audit capture points defined in Section 6.1.

20.2 Diff Rules

• CREATE
  • changes contains { after: ... } or a normalised field map
• UPDATE
  • changes contains only fields that actually changed
• DELETE
  • changes contains { before: ... } or a normalised deletion snapshot
• sensitive fields
  • MUST be redacted via the redaction framework (Section 18) before persistence

20.3 IP Address Rules

• Raw IP addresses MUST NOT be stored (see Section 9.4).
• IP extraction MUST use the trusted proxy chain, not raw X-Forwarded-For.
• Truncation to /24 (IPv4) or /48 (IPv6) is applied at write time.

20.4 Enforcement

Audit coverage SHALL be enforced via automated checks that target mutating tools only. Read-only tools (e.g., queryAudit in audit.tools.ts) are explicitly exempt — they do not produce state changes and MUST NOT be forced to include meaningless audit calls.

1. Typed wrapper convention: Mutating tool functions SHOULD use withAuditedMutation() which makes audit capture structurally unavoidable. Tools that use this wrapper are automatically compliant.
2. Integration tests (primary enforcement): After running each mutating tool in the test suite, verify that an audit row exists with correct action, resource_type, resource_id, tenant_id, and outcome. This is the authoritative check — it validates behaviour, not file contents.
3. Optional static check (advisory): A CI lint rule MAY flag mutating tool files that contain no audit call as a warning, but it MUST NOT block merges. The integration test in (2) is the merge gate. The static check uses a naming convention or annotation (e.g., @audit-exempt comment for read-only tools) to avoid false positives.
4. These checks SHALL be part of the CI pipeline.

---

21. Rollout Plan

Phase 1: Foundation

1. Extend audit.audit_entries migration with all new columns (organisation_id, parent_resource_type, parent_resource_id, context_json, entry_hash, previous_hash, session_id, user_agent, outcome, duration_ms, changed_fields).
2. Apply range partitioning on created_at with monthly partitions.
3. Create all indexes defined in Section 8.
4. Define and document retention policy (Section 6.5); automate partition creation.
5. Implement IP truncation at write time (Section 9.4); document retention alignment with architecture spec (Section 6.5).
6. Implement createAuditor(), withAuditedMutation(), and auditBatch().
7. Implement buildAuditDiff() with full specification (Section 10.4).
8. Implement RedactionPolicy and default sensitive patterns (Section 18).
9. Add keyset cursor support to queryAuditTrail() alongside existing offset/limit (Section 12.2).
10. Establish operational monitoring baseline (Section 14).
11. Add integration test enforcement for mutating tools (Section 20.4).
12. Add tests for new schema fields, query filters, diff generation, and redaction.

Phase 2: Directory as Reference Implementation

1. Keep Directory as the reference implementation.
2. Update audit-producing tools to use createAuditor() and the richer contract.
3. Expand apps/directory/src/server/tools/audit.tools.ts for parent-resource, organisation, and outcome filters.
4. Implement granular audit access control (Section 13): audit:read:own, audit:read:org, audit:read:tenant, audit:read:classified.
5. Implement tamper-evident chain (Section 11) with per-tenant chaining.
6. Implement audit-of-audit-access logging (Section 13.3).
7. Validate RLS and permission behaviour for tenant-scoped audit queries.

Phase 3: Catalog Adoption

1. Update catalog tools to use normalised resourceType naming.
2. Add before/after diffs to update and delete flows.
3. Expose per-entry audit trail in Catalog UI once shared UI is ready.

Phase 4: Project-Tracker Migration

1. Remove stub/no-op audit paths.
2. Replace src/lib/entity-audit.ts as the primary mechanism with explicit tool-layer writes.
3. Implement /api/audit on top of shared platform queries.
4. Connect existing AuditTrail UI to real shared audit data.

Phase 5: Shared UI Extraction

1. Generalise the audit trail component for reuse across apps.
2. Introduce standard display adapters for actor/resource labels.
3. Add related-history support where parent resource fields are present.
4. Implement streaming export for compliance (Section 15).

Phase 6: Defence-Tier Hardening

1. SIEM integration (Section 17).
2. Chain integrity verification job with alerting (Section 11.5).
3. Archival automation: detach old partitions, export to cold storage (Section 6.5).
4. Data residency controls for EU vs US defence tenants.
5. pg_audit extension for superuser action logging (Section 7.4).

---

22. Testing Requirements

22.1 Platform Tests

• Schema migration: all columns, partitioning, immutability triggers, JSON validation.
• Query filters: all fields including organisation, outcome, changed_fields; keyset pagination stability.
• buildAuditDiff(): property tests, nested flattening, array diffs, 64 KB truncation, redaction, ignoreFields.
• Pseudonymisation: HMAC generation, actor mapping CRUD, GDPR deletion flow.
• Ergonomic helpers: createAuditor(), withAuditedMutation(), auditBatch().
• Redaction: default patterns, custom policies, double-exposure prevention in auditCritical().

22.2 Module Integration Tests

For each module: every tool creates an audit row matching actor/action/resource/module/outcome; updates persist only changed fields; deletes preserve before-state; failed and denied operations produce appropriate outcome values; tenant isolation holds; organisation-scoped queries return only matching entries.

22.3 UI Tests

Audit panel loads and paginates (keyset); diffs render correctly; related-resource history toggles; outcome badges display; empty and error states are stable.

22.4 Enforcement Tests

CI check confirms every tool file contains an audit call. Integration suite verifies audit row existence after each tool execution.

---

23. Known Cleanup Required

The following repo state should be corrected during rollout:

1. apps/project-tracker/src/app/api/audit/route.ts
   • currently returns a stubbed empty result
2. apps/project-tracker/src/lib/audit.ts
   • currently contains stub auth audit logging
3. apps/project-tracker/src/lib/entity-audit.ts
   • currently logs debug output instead of canonical audit rows
4. packages/platform/testing/README.md
   • still references identity.audit_log in an example; canonical table is audit.audit_entries

---

24. Spelling Consistency

This specification and all implementation code SHALL use British spelling consistently, matching the existing Constellation codebase:

• organisation (not organization)
• normalised (not normalized)
• behaviour (not behavior)
• serialisation (not serialization)

Database columns, API fields, and TypeScript types SHALL all follow this convention: organisation_id, organisationId.

---

25. Final Recommendation

Constellation should implement universal audit logging as:

• a shared platform library capability
• backed by the shared audit schema with range partitioning and defined retention
• captured explicitly in each module's tool layer and other defined capture points
• protecting PII surface via IP truncation and context_json prohibition, with actor identity retained per GDPR Article 17(3) exemptions
• secured by granular access control with audit-of-audit-access
• queried through shared platform APIs with keyset pagination
• rendered through reusable audit-history UI components
• hardened with tamper-evident per-tenant hash chains
• monitored with operational metrics and SLOs
• exportable for compliance reporting and SIEM integration

This is aligned with the approved Constellation architecture and uses Open Mercato appropriately as a pattern reference rather than a framework to copy.

---

Review Acknowledgments

This v2 specification incorporates findings from a multi-agent review panel covering database architecture, privacy/compliance (GDPR, SOC 2, ITAR), security (tamper-evident chains, access control, SIEM), developer experience, operations, and consistency. All findings have been addressed. The phase plan has been restructured to front-load compliance-critical items into Phase 1.