Waiting for input...
Star SPIKE on GitHub

ADR-0011: PostgreSQL as SPIKE’s Backing Store


Context

SPIKE needs a reliable, secure, and performant backing store to maintain various types of encrypted data including:

  • Root keys (encrypted with admin password)
  • Admin tokens (encrypted with root key)
  • Secrets (encrypted with root key)

The system requires:

  1. Strong data consistency and ACID transactions
  2. Support for binary data storage (for encrypted keys and secrets)
  3. Advanced data types and indexing capabilities
  4. Robust access control and security features
  5. Audit logging capabilities
  6. Schema management and version control

Decision

We will use PostgreSQL as the exclusive backing store for SPIKE, with SPIKE Nexus being the only client with direct database access.

Rationale

Security Features

  • Native support for encryption at rest
  • Fine-grained access control
  • SSL support for encrypted connections
  • Built-in audit logging capabilities

Data Integrity:

  • ACID compliance
  • Strong consistency model
  • Native support for UUID, JSONB, and bytea types
  • Constraints and triggers for data validation

Performance:

  • Efficient indexing for binary and text data
  • Good performance with both read and write operations
  • Mature query optimizer
  • Support for concurrent access

Operational Benefits:

  • Wide industry adoption
  • Extensive tooling ecosystem
  • Strong community support
  • Well-documented backup and recovery procedures
  • Free and open-source

Consequences

Positive

  1. Simplified architecture with a single source of truth
  2. Reliable data persistence and consistency
  3. Built-in support for all required data types
  4. Easy integration with existing backup and monitoring tools
  5. No additional licensing costs

Negative

  1. Limited to PostgreSQL-specific features
  2. Team needs to maintain PostgreSQL expertise
  3. Single database deployment could be a bottleneck
  4. Migration complexity if database change is needed in future

Mitigations

  1. Use database-agnostic SQL where possible
  2. Implement connection pooling
  3. Plan for regular maintenance windows
  4. Document all PostgreSQL-specific features used

Implementation Notes

  • SPIKE Nexus can use prepared statements to prevent SQL injection
  • All sensitive data will be encrypted before storage
  • Regular backups will be configured
  • Monitor database performance metrics
  • Implement connection pooling for scalability

Consequences

Positive

  1. Simplified architecture with a single source of truth
  2. Reliable data persistence and consistency
  3. Built-in support for all required data types
  4. Easy integration with existing backup and monitoring tools
  5. No additional licensing costs

Negative

  1. Limited to PostgreSQL-specific features
  2. Team needs to maintain PostgreSQL expertise
  3. Single database deployment could be a bottleneck
  4. Migration complexity if database change is needed in future

Mitigations

  1. Use database-agnostic SQL where possible
  2. Implement connection pooling
  3. Plan for regular maintenance windows
  4. Document all PostgreSQL-specific features used

Implementation Notes

  • SPIKE Nexus will use prepared statements to prevent SQL injection
  • All sensitive data will be encrypted before storage
  • Regular backups will be configured
  • Monitor database performance metrics
  • Implement connection pooling for scalability