architecture-decision-records

ADR-0007 selected a dedicated MongoDB Atlas cluster because audit events had varied shapes and PostgreSQL schema changes were slow to review. This provided schema flexibility but introduced a second database to operate. Since that decision: - Compliance now requires seven-year retention. Atlas storage at the required volume is quoted at $4,100 per month. - Finance needs to join audit events to invoices for disputes. Today, this requires a manual export. - Event shapes have stabilized into 11 types with a common envelope. These changes reduce the value of a separate document database and favor relational access for recent events, with lower-cost archival storage for older events. ## Decision Write audit events to a monthly-partitioned PostgreSQL table. Move partitions older than 13 months to S3 as Parquet, retaining audit events for the required seven years across PostgreSQL and S3. Backfill existing events from MongoDB Atlas, cut over to the new storage arrangement, and decommission the Atlas cluster by 2027-03-31. This decision was agreed in architecture review on 2026-10-19. ## Alternatives Considered ### Retain MongoDB Atlas Preserves the existing implementation and schema flexibility, but retains the quoted storage cost and manual export workflow for invoice joins. ### Retain all seven years in PostgreSQL Keeps all audit history directly accessible through SQL joins, but places the full retention volume in the operational database. The chosen approach limits that footprint by archiving older partitions to S3. ## Consequences ### Positive - Recent audit events can be joined to invoices directly in PostgreSQL. - Monthly partitions provide a practical unit for archival and retention. - Parquet in S3 provides a lower-cost archival tier. - Decommissioning Atlas removes a separately operated database cluster. ### Negative - Audit storage and retrieval span PostgreSQL and S3. - Events archived to S3 will not be available through ordinary PostgreSQL table joins; historical dispute investigations need a documented query or restore workflow. - Backfill, cutover, and archival introduce implementation and validation work. - PostgreSQL capacity, indexing, and partition maintenance must accommodate the audit workload. ### Risks and Required Safeguards - Exported partitions must be validated before their PostgreSQL copies are removed. - Migration must account for events arriving during the backfill and prevent lost or duplicated events at cutover. - Retention and deletion controls must preserve the required seven-year history across both storage tiers. - Access controls, integrity protections, and recovery procedures must cover both PostgreSQL and S3. ## Implementation Plan Owner: Ines Duarte. Atlas decommissioning deadline: 2027-03-31. 1. Define the PostgreSQL schema for the common envelope and 11 event types, monthly partition management, and indexes needed for invoice joins. 2. Implement the S3 Parquet archival process, including export validation, retrieval procedures, and retention controls. 3. Backfill Atlas history into the appropriate storage tier and reconcile completeness and correctness. 4. Cut over event writers and readers using a documented process that accounts for writes during migration. 5. Validate ingestion, invoice joins, historical retrieval, and recovery. 6. Decommission Atlas after validation and confirmation that it is no longer needed for rollback, no later than 2027-03-31. ## Follow-up Details to Resolve - Historical query or restore workflow for archived events. - Exact retention cutoff and partition-boundary handling. - Migration reconciliation criteria and rollback procedure. - PostgreSQL and S3 capacity and cost estimates. ``` ## 2. Update `docs/adr/0007-mongodb-for-audit-log.md` Replace only its status line with: ```markdown Status: Superseded by [ADR-0008](0008-postgresql-and-s3-for-audit-log.md) (2026-10-19). Originally accepted 2024-03-12. ``` Keep its original context, decision, and consequences unchanged. They explain why the original choice was made. ## 3. Update `docs/adr/README.md` Preserve the existing index and guidelines. Update ADR-0007’s row and add ADR-0008: ```markdown | ADR | Title | Status | Date | | --- | --- | --- | --- | | [0007](0007-mongodb-for-audit-log.md) | Use MongoDB for the audit log | Superseded by ADR-0008 | 2024-03-12 | | [0008](0008-postgresql-and-s3-for-audit-log.md) | Replace MongoDB audit storage with PostgreSQL and S3 | Accepted | 2026-10-19 | ``` Use **Superseded**, rather than merely **Deprecated**, because the new ADR explicitly replaces the old decision.