-
Notifications
You must be signed in to change notification settings - Fork 0
Open
Description
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
- GORM and PostgreSQL driver (from issue feat: adds cuetools package #1)
- Goose migrations (from issue feat: adds cuetools package #1)
- Database schema (from issue feat: adds cuetools package #1)
- Certificate and audit models (from issues feat: adds blueprint package #2 and chore: resizes README logo #3)
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
Labels
No labels