{"id":"auth-dual-write-audit-logging","slug":"auth-dual-write-audit-logging","title":"Authentication Audit Logging Pipeline","description":"After a brute-force campaign against a government agency's platform accounts, the security team needs to reconstruct exactly what happened: which accounts were targeted, which IPs were involved, when lockouts triggered, ","category":"modules","tags":["modules","real-time","compliance"],"lastModified":"2026-04-02","source_ref":"content/modules/auth-dual-write-audit-logging.md","url":"/developers/auth-dual-write-audit-logging","htmlPath":"/developers/auth-dual-write-audit-logging","jsonPath":"/api/docs/modules/auth-dual-write-audit-logging","markdownPath":"/api/docs/modules/auth-dual-write-audit-logging?format=markdown","checksum":"285b6b60ef9e117439695bae9e017fa2e7b703106f6fe54cb9ee1cf0871fc702","headings":[{"id":"overview","text":"Overview","level":2},{"id":"key-features","text":"Key Features","level":2},{"id":"use-cases","text":"Use Cases","level":2},{"id":"integration","text":"Integration","level":2}],"markdown":"# Authentication Audit Logging Pipeline\n\n## Overview\n\nAfter a brute-force campaign against a government agency's platform accounts, the security team needs to reconstruct exactly what happened: which accounts were targeted, which IPs were involved, when lockouts triggered, and whether any successful logins followed a series of failures. That reconstruction is only possible if the authentication event record is complete, consistent, and queryable. The Authentication Audit Logging Pipeline guarantees that by writing every authentication event to both the document store and PostgreSQL in the same operation.\n\nThe dual-write design closes a practical gap: lockout state fields can fall out of sync when only one persistence layer is updated. With every event mirrored to PostgreSQL, structured queries against account lockout status always return current information, not a stale snapshot from a partially-completed write.\n\n```mermaid\nsequenceDiagram\n    participant U as User\n    participant A as Auth Service\n    participant D as Document Store\n    participant P as PostgreSQL\n    participant S as Security Dashboard\n\n    U->>A: Login attempt (web or mobile path)\n    A->>A: Validate credentials\n    A->>D: Write auth log record\n    A->>P: Mirror lockout state (upsert on conflict)\n    alt Login failed\n        A-->>U: Return error\n        Note over D,P: failed_login_attempts incremented in both stores\n    else Account locked\n        A-->>U: Return lockout response\n        Note over D,P: account_locked_until written to both stores\n    else Login success\n        A->>D: Write login_success record\n        A->>P: Clear lockout state\n        A-->>U: Return session token\n    end\n    P->>S: Feeds real-time authentication metrics\n```\n\n**Last Reviewed:** 2026-04-02\n**Last Updated:** 2026-04-14\n\n## Key Features\n\n- **Dual-Write Consistency**: Authentication events are written to both the document store and PostgreSQL in every operation. Lockout state fields are mirrored to PostgreSQL after every update, so structured queries always reflect current state.\n- **Structured Auth Log Records**: Each entry captures event type, user identifier, tenant identifier, IP address, user agent, timestamp, and extensible metadata. Event types include `login_failed`, `account_locked`, `login_success`, and `lockout_reset`.\n- **Account Lockout Mirroring**: When a failed login triggers a lockout, the lockout state is persisted to PostgreSQL in the same operation. When lockout state is cleared, the cleared state is mirrored immediately.\n- **Cross-Path Coverage**: Both the standard web authentication path and the mobile authentication path generate identical auth log records, ensuring consistent audit coverage regardless of authentication method.\n- **Organisation-Scoped Queries**: All auth log queries are scoped by tenant and organisation, preventing cross-tenant visibility into authentication events and satisfying data sovereignty requirements.\n- **Upsert Semantics**: Auth log persistence uses upsert (`ON CONFLICT UPDATE`) semantics on the log identifier, preventing duplicate entries from retry logic or transient failures.\n\n## Use Cases\n\n- **Security Incident Investigation**: Query the `auth_logs` table for all failed login attempts against a specific user or from a specific IP to identify brute-force attacks, credential stuffing, or compromised accounts\n- **Compliance Reporting**: Generate audit reports showing all authentication events for a tenant over a defined period, with structured fields suitable for SOC 2, ISO 27001, and EDF compliance evidence\n- **Lockout Diagnostics**: Trace the sequence of failed login attempts that triggered a lockout, including IP addresses and user agents, to support help desk resolution and forensic review\n- **Operational Monitoring**: Track authentication event volumes and failure rates across the platform to detect anomalous patterns indicative of systemic issues or coordinated attack campaigns\n\n## Integration\n\nThe auth logging pipeline feeds into the platform-wide audit trail for unified event visibility, the security monitoring dashboard for real-time authentication metrics, and the compliance reporting system for regulatory evidence packages. All auth log entries are accessible through the admin interface and the platform API, scoped to the requesting organisation.\n"}