Technical Evaluation: Storage Architecture for Secret Rotation Attributes

Summary

This issue addresses the architectural decision needed for storing and querying secret rotation attributes in the Secret Rotation API implementation (#499945 (closed)).

Problem Statement

The current architecture stores all secrets in OpenBao without using ActiveRecord models (per ADR 008). However, implementing secret rotation functionality requires tracking rotation-related attributes (rotation dates, statuses, notification timestamps) which raises architectural questions about optimal storage and query performance.

Objective

Conduct a time-boxed technical evaluation (2 days) to assess the two viable approaches for storing rotation attributes, resulting in an ADR (Architecture Decision Record).

Options to Evaluate

Option 1: OpenBao Metadata Storage

  • Store all rotation attributes in OpenBao secret metadata
  • Query using OpenBao SCAN/LIST secrets API to identify secrets needing rotation
  • Concern: Performance implications when scanning large numbers of secrets

Option 2: Rails ActiveRecord Database

  • Create dedicated table(s) for rotation tracking linked to secrets by identifier
  • Use efficient database queries for rotation status checks
  • Concern: Potential "split brain" problem with data synchronization

Evaluation Criteria

  • Performance: Query efficiency for rotation checks at scale
  • Data Consistency: Synchronization and integrity risks
  • Implementation Complexity: Development and maintenance overhead
  • Architecture Alignment: Compliance with existing ADRs

Deliverables

  • Performance analysis for both approaches
  • Data consistency risk assessment
  • Implementation complexity comparison
  • Recommendation with technical rationale
  • ADR documenting the architectural decision

Acceptance Criteria

  • Both storage approaches are thoroughly analyzed
  • Performance implications are quantified where possible
  • Clear recommendation is provided with supporting evidence
  • ADR is created and reviewed

This evaluation will directly inform the implementation approach for #499945 (closed).