ADR-019: Polymorphic Change Events & Shared Audit Trail Service

Status: 🟢 Accepted Triggered by: Level 1 Review — Domain 1 Backoffice (Finding #11), cross-schema audit trail analysis Supersedes: The previous wide-nullable-FK pattern on all four change_events tables

---

Context

Every bounded context maintains a local change_events table for GoBD-compliant audit trailing. The original design used per-entity nullable FK columns — one column per trackable entity type. This pattern has four structural problems:

1. Schema churn. Backoffice change_events already has 16 nullable FK columns. Every new entity requires ALTER TABLE ADD COLUMN. Operations has 8, Commerce has 8, Communications has 3.
2. No cross-context tracing. Sagas like SwapVehicle write audit entries to both operations.change_events and commerce.change_events, but there is no correlation mechanism linking them.
3. Inconsistent scope enums. Each schema defined its own: Backoffice (CONFIG, GENERAL, DSGVO), Operations (GOBD, GENERAL, COMPLIANCE), Commerce (GOBD, GENERAL), Communications (CONFIG, GENERAL). No canonical enum.
4. No shared enforcement. Each Hasura Action handler must manually construct and INSERT the change_events row. No reusable service, no consistent old_values capture strategy.

---

Decisions

1. Polymorphic Entity Reference

Replace all per-entity nullable FK columns with two columns:

entity_type  VARCHAR NOT NULL,   -- e.g. 'vehicle', 'crew_member', 'onboard_sale'
entity_id    UUID    NOT NULL

The CHECK constraint enforcing "exactly one FK set" is removed — the polymorphic pair inherently represents exactly one entity.

Hasura relationship resolution uses a computed field backed by a SQL function:

CREATE FUNCTION operations.change_event_entity(ce operations.change_events)
RETURNS JSONB AS $$
  SELECT CASE ce.entity_type
    WHEN 'service_leg'    THEN (SELECT row_to_json(sl) FROM operations.service_legs sl    WHERE sl.id = ce.entity_id)
    WHEN 'leg_assignment'  THEN (SELECT row_to_json(la) FROM operations.leg_assignments la WHERE la.id = ce.entity_id)
    WHEN 'incident'        THEN (SELECT row_to_json(i)  FROM operations.incidents i       WHERE i.id  = ce.entity_id)
    -- ... one WHEN per entity type in the schema
  END;
$$ LANGUAGE sql STABLE;

Each schema defines its own computed field function with its own entity type mappings. Hasura registers these as computed fields on the change_events type, restoring GraphQL relationship resolution without requiring FK columns.

Index: CREATE INDEX ON change_events (tenant_id, entity_type, entity_id) — covers all audit trail queries. Replaces the 16+ partial indexes the old pattern would need.

Valid entity_type values per schema:

Schema	entity_type values
Backoffice	operator, costing_sheet, tour_template, tour_departure, vehicle, supplier, allotment, crew_member, passenger_profile, reseller, notification_template, crew_qualification, crew_absence, vehicle_inspection, price_matrix, operator_integration
Operations	service_leg, leg_assignment, expense_receipt, onboard_sale, incident, issue_report, crew_duty_log, boarding_event
Commerce	tour_offering, booking, payment, ticket, invoice, financial_ledger, tax_ledger_entry, ledger_period_lock
Communications	channel_account, contact, conversation

2. Correlation ID for Cross-Context Sagas

All four change_events tables gain:

correlation_id  UUID  NULLABLE

When a saga spans bounded contexts (e.g., SwapVehicle writes Operations + Commerce entries), the handler generates one correlation_id and passes it to all audit entries. This creates a queryable cross-context timeline:

SELECT 'operations' AS schema_name, * FROM operations.change_events WHERE correlation_id = :id
UNION ALL
SELECT 'commerce' AS schema_name, * FROM commerce.change_events WHERE correlation_id = :id
ORDER BY created_at;

Index: CREATE INDEX ON change_events (correlation_id) WHERE correlation_id IS NOT NULL — partial index since most entries are single-context.

3. Standardized Scope Enum

All four schemas use the same canonical enum:

Scope	Meaning	Typical entities
GOBD	GoBD-relevant financial/tax mutations. Immutable once written.	expense_receipt, onboard_sale, invoice, financial_ledger, tax_ledger_entry, payment
COMPLIANCE	Regulatory compliance (e.g., EU-561 driving time).	crew_duty_log
DSGVO	Data privacy / GDPR-relevant personal data mutations.	passenger_profile, contact
CONFIG	Tenant configuration changes.	operator, notification_template, channel_account, operator_integration
GENERAL	All other operational mutations. Default.	Everything else

Each schema supports the full enum but uses the relevant subset at runtime. This enables cross-schema audit queries without scope translation.

4. Shared AuditTrailService & Hasura Event Triggers (NestJS)

To support rapid frontend development without forcing all mutations through custom NestJS Actions, the audit pipeline leverages Hasura Event Triggers. This provides the best of both worlds: direct GraphQL mutations for speed, and native PostgreSQL trigger accuracy for old_values capture.

The Pipeline

1. Direct Mutation: Frontend performs a standard GraphQL mutation (e.g., update_backoffice_vehicles_by_pk).
2. Postgres Trigger: Hasura's underlying PostgreSQL trigger captures the transaction's OLD and NEW row states with 100% ACID accuracy.
3. Event Delivery: Hasura delivers the event payload asynchronously to the NestJS webhook endpoint (managed by @golevelup/nestjs-hasura).
4. Idempotent Write: The AuditTrailService maps the event to the change_events schema and performs the INSERT.

@Injectable()
export class AuditTrailService {
  constructor(private readonly client: GraphQLClient) {}

  /**
   * Generic handler to process incoming Hasura events for audit logging
   */
  async recordFromEvent(event: HasuraEvent, overrideEntityType?: string) {
    // 1. Extract session variables (x-hasura-user-id)
    // 2. Extract tenant_id and entity_id from event.data.new (or old)
    // 3. Map table name to scope enum (e.g. 'operator_settings' -> 'CONFIG')
    // 4. Perform GraphQL mutation: insert_change_events_one
    // 5. Handle PostgreSQL unique constraint violations gracefully (Idempotency)
  }

  /**
   * For complex cross-schema sagas that orchestrate multiple mutations from NestJS.
   */
  async record(params: { /* ... */ }): Promise<void> {
    // Perform manual GraphQL mutation to insert audit log
  }
}

Key design properties:

• Transactional Accuracy, Asynchronous Delivery: The old_values and new_values are captured natively by PostgreSQL at the exact millisecond of the mutation. The write to change_events happens asynchronously (Eventual Consistency).
• Idempotency via Event ID: Hasura guarantees "at-least-once" delivery. The AuditTrailService uses the event.id (UUID) as the Primary Key for change_events.id. If Hasura delivers a duplicate, Postgres rejects it safely via Unique Constraint.
• Auto-generated Metadata: Developers annotate NestJS methods with @TrackedHasuraEventHandler. The integration automatically creates the Hasura Event Triggers during schema sync.
• Coexistence with Sagas: Complex Sagas running in NestJS can still call record() directly to pass a shared correlation_id.

---

Consequences

• All four change_events tables lose their per-entity nullable FK columns and gain entity_type, entity_id, correlation_id.
• Scope enums are unified to a canonical 5-value set.
• Hasura metadata requires computed field definitions for entity resolution (one per schema).
• All existing protocols referencing change_events FK columns (vehicle-swap-protocol, storno-workflow, offline-sync-protocol) must reference the polymorphic pattern.
• The AuditTrailService becomes the webhook consumer for Event Triggers, relying on event.id for idempotency and Eventual Consistency for writes.

---

Schema Cross-References

Document	Update Required
schema-backoffice.md	ERD + table definition
schema-operations.md	ERD + table definition
schema-commerce.md	ERD + table definition
schema-communications.md	ERD + table definition
vehicle-swap-protocol.md	Audit section
storno-workflow.md	Audit trail section
offline-sync-protocol.md	Change event generation section