Skip to content

Database Layer #200

@jmgilman

Description

@jmgilman

Database Layer

Overview

Implement the database layer using GORM with the repository pattern for certificate metadata storage, audit trail implementation, and CA certificate caching, ensuring proper data persistence and retrieval.

Objective

Create a robust database abstraction layer that handles all certificate-related data operations, maintains comprehensive audit trails, and provides efficient caching for CA certificates while ensuring data integrity and performance.

Canonical Scope

  • This document is the canonical source for:
    • GORM models and table/schema mappings
    • Audit log storage, event taxonomy, and retention policy
    • CA certificate caching mechanism and TTL configuration
  • For logging and health/readiness behavior, see 06 Monitoring & Observability. For field/CSR validation and error schema, see 07 Security & Validation.

Tasks

Repository Pattern Implementation

  • Create repository interface in internal/database/repository.go:
    type CertificateRepository interface {
      Create(cert *Certificate) error
      GetBySerial(serial string) (*Certificate, error)
      GetByRequester(requester string) ([]*Certificate, error)
      UpdateStatus(serial string, status string) error
      ListExpiring(days int) ([]*Certificate, error)
      CountActive() (int64, error)
      CountByCA(caName string) (int64, error)
    }
    
    type AuditRepository interface {
      LogEvent(event *AuditEvent) error
      GetEvents(filter AuditFilter) ([]*AuditEvent, error)
      GetEventsByActor(actor string, limit int) ([]*AuditEvent, error)
    }
    
    type CARepository interface {
      Store(ca *CertificateAuthority) error
      GetByName(name string) (*CertificateAuthority, error)
      GetAll() ([]*CertificateAuthority, error)
      UpdateCertificate(name string, cert string) error
      GetExpiring(days int) ([]*CertificateAuthority, error)
    }
  • Implement repositories with GORM backing
  • Use dependency injection for database connection
  • Implement proper error handling and logging

Certificate Metadata Operations

  • Implement certificate storage with GORM:
    func (r *certificateRepo) Create(cert *Certificate) error {
      return r.db.Create(cert).Error
    }
  • Handle serial number uniqueness constraint
  • Implement certificate status updates:
    • Active → Expired (based on expiry time)
    • Active → Superseded (on renewal)
    • Active → Revoked (if revocation implemented)
  • Create indexes for common queries:
    • By serial number (unique)
    • By expiration date
    • By status
    • By requester
  • Implement certificate lifecycle queries:
    • Find certificates expiring soon
    • Count active certificates per CA
    • List certificates by requester

Audit Trail Implementation

  • Create comprehensive audit logging for all operations
  • Implement audit event creation:
    func LogCertificateIssued(repo AuditRepository, actor string, serial string, details map[string]interface{}) error {
      event := &AuditEvent{
        EventType:          "certificate_issued",
        ActorIdentity:      actor,
        ResourceType:       "certificate",
        ResourceIdentifier: serial,
        Outcome:           "success",
        Details:           details,
        Timestamp:         time.Now(),
      }
      return repo.LogEvent(event)
    }
  • Define audit event types:
    • certificate_issued
    • certificate_renewed
    • certificate_requested
    • authentication_failed
    • authorization_failed
    • ca_certificate_refreshed
    • system_error
  • Implement audit query capabilities:
    • Filter by event type
    • Filter by actor
    • Filter by date range
    • Filter by outcome (success/failure)
  • Ensure audit logs are immutable (no updates/deletes)
  • Implement retention policy (1 year minimum as per spec)

CA Certificate Caching

  • Implement CA certificate storage and retrieval
  • Create caching mechanism with 1-hour TTL:
    type CACache struct {
      mu         sync.RWMutex
      cache      map[string]*CachedCA
      repository CARepository
    }
    
    type CachedCA struct {
      CA          *CertificateAuthority
      CachedAt    time.Time
      TTL         time.Duration
    }
  • Implement cache refresh logic:
    • Check cache validity on retrieval
    • Refresh from database if expired
    • Update database from AWS PCA if needed
  • Monitor CA certificate expiration
  • Create alerts for CAs expiring within 30 days

GORM Model Refinements

  • Ensure all GORM models have proper tags:
    type Certificate struct {
      ID              uint           `gorm:"primaryKey"`
      SerialNumber    string         `gorm:"uniqueIndex;size:40;not null"`
      CommonName      string         `gorm:"size:255;not null"`
      SAN             pq.StringArray `gorm:"type:text[]"`
      Profile         string         `gorm:"size:50;not null"`
      IssuerCA        string         `gorm:"size:100;not null"`
      Status          string         `gorm:"size:20;default:'active'"`
      IssuedAt        time.Time      `gorm:"not null"`
      ExpiresAt       time.Time      `gorm:"not null"`
      RequestedBy     string         `gorm:"size:255;not null"`
      RequestMetadata datatypes.JSON `gorm:"type:jsonb"`
      CreatedAt       time.Time
      UpdatedAt       time.Time
    }
  • Configure GORM hooks for automatic timestamp management:
    func (c *Certificate) BeforeCreate(tx *gorm.DB) error {
      c.CreatedAt = time.Now()
      c.UpdatedAt = time.Now()
      return nil
    }
  • Set up proper table names with schemas:
    func (Certificate) TableName() string {
      return "certificates.certificates"
    }

Database Connection Management

  • Implement connection pool configuration:
    type DBConfig struct {
      MaxOpenConns    int           // 25 as per spec
      MaxIdleConns    int           // 5 as per spec
      ConnMaxLifetime time.Duration
      ConnMaxIdleTime time.Duration
    }
  • Create database connection factory
  • Implement health check for database connectivity
  • Handle connection errors gracefully
  • Implement retry logic for transient failures

Transaction Support

  • Implement transaction wrapper for complex operations:
    func WithTransaction(db *gorm.DB, fn func(*gorm.DB) error) error {
      tx := db.Begin()
      defer func() {
        if r := recover(); r != nil {
          tx.Rollback()
        }
      }()
    
      if err := fn(tx); err != nil {
        tx.Rollback()
        return err
      }
    
      return tx.Commit().Error
    }
  • Use transactions for:
    • Certificate issuance (metadata + audit)
    • Certificate renewal (update old + create new + audit)
    • Multi-record updates

Query Optimization

  • Implement query builders for complex queries
  • Use preloading where appropriate
  • Implement pagination for list operations:
    type Pagination struct {
      Limit  int
      Offset int
      Sort   string
      Order  string
    }
  • Add query logging for performance monitoring
  • Optimize N+1 query problems

Acceptance Criteria

  • All repository interfaces implemented
  • Certificate CRUD operations working
  • Audit logging functional for all event types
  • CA certificate caching with 1-hour TTL working
  • Database indexes created and used
  • Connection pooling configured (25 max, 5 idle)
  • Transactions used for multi-step operations
  • GORM models properly configured with schemas
  • Audit retention policy implemented
  • Database health checks working

Technical Considerations

  • Use GORM's built-in features where possible
  • Implement proper SQL injection prevention (parameterized queries)
  • Use context for timeout control
  • Handle database migrations separately (from issue feat: adds cuetools package #1)
  • Consider read/write splitting if needed for performance
  • Use database-specific features appropriately (PostgreSQL arrays, JSONB)
  • Ensure timezone handling is consistent (UTC)

Dependencies

Testing Requirements

  • Unit tests for all repository methods
  • Unit tests for cache operations
  • Integration tests with test database
  • Test transaction rollback scenarios
  • Test concurrent access patterns
  • Test connection pool behavior
  • Test audit log immutability
  • Benchmark critical queries
  • Test cache expiration and refresh

Definition of Done

  • Code reviewed and approved
  • All tests passing
  • Database queries optimized
  • No N+1 query problems
  • Audit trail comprehensive
  • Caching working correctly
  • Connection pooling verified
  • Documentation complete

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions