architecture-decision-records

**Owner**: Ines Duarte ## Context ADR-0007 (2024-03-12) put audit events in a dedicated MongoDB Atlas cluster. The reasons were that event shapes varied per feature and that Postgres schema changes were slow to get through review. Several of those conditions have changed: - **Retention**: Compliance now requires 7-year retention. Atlas storage at that volume is quoted at $4,100/month. - **Joins**: Finance needs to join audit events to invoices for disputes. Today this is a manual export. - **Schema stability**: Event shapes have settled into 11 types with a common envelope, so the flexibility we chose Mongo for is no longer needed. ## Considered Options ### Option 1: Keep MongoDB Atlas (status quo) - **Pros**: No migration work; flexible schema retained. - **Cons**: Quoted at $4,100/month for 7-year retention; joins to invoices stay a manual export; a second database remains to operate. ### Option 2: Monthly-partitioned PostgreSQL, with older partitions archived to S3 as Parquet - **Pros**: Audit events can be joined to invoices directly; one fewer database to operate; cold data moves to cheaper storage. - **Cons**: Migration effort; Postgres schema changes go back through review; data older than 13 months is no longer in the primary database. <!-- Add any other options discussed in the 2026-10-19 review. --> ## Decision Write audit events to a monthly-partitioned PostgreSQL table. Move partitions older than 13 months to S3 as Parquet. Backfill existing events from Atlas, then decommission the Atlas cluster by **2027-03-31**. ## Consequences ### Positive - Finance can join audit events to invoices in SQL, replacing the manual export. - Removes the Atlas cost and one database from operations. - Retention of 7 years is met through the S3 archive tier. ### Negative - Adding or changing an event type now needs a Postgres schema change, which was the original pain behind ADR-0007. This is acceptable because the shapes have settled into 11 types with a common envelope. - Events older than 13 months can't be queried alongside live data without separate tooling. - Backfill and cut-over carry a risk of data loss or inconsistency. ## Implementation Notes - Backfill from Atlas, validate completeness, then decommission the cluster by 2027-03-31. - Automate monthly partition creation and the 13-month archive job. ## Open Questions - [ ] Table design: typed columns for the common envelope plus JSONB for per-type payloads, or something else? - [ ] How will archived Parquet data be queried (and by whom) for compliance requests? - [ ] Cut-over approach: dual-write period or a single switch? - [ ] Verifying that S3 retention and immutability meet the compliance requirements. ## Related Decisions - Supersedes [ADR-0007](0007-mongodb-for-audit-log.md) ``` ## 3. Update `docs/adr/README.md` index ```markdown | [0007](0007-mongodb-for-audit-log.md) | Use MongoDB for the audit log | Superseded | 2024-03-12 | | [NNNN](NNNN-audit-log-to-postgresql.md) | Move audit log from MongoDB to partitioned PostgreSQL | Accepted | 2026-10-19 | ``` ## Things to check - I only used facts from your message. The Open Questions and the Negative consequences about archived-data access and schema review are my inferences, so please confirm or trim them. - If the review considered other options (e.g. Atlas-side archiving), add them to Considered Options. A fair record of rejected alternatives is valuable here. - Once the ADR is merged, create tickets for the backfill, the cut-over, and the Atlas decommission (due 2027-03-31).