squad/README-Database.md

# Database Implementation

This document describes the PostgreSQL database implementation for the Software Release Management Platform.

## Architecture Overview

The database layer uses **Entity Framework Core 8** with **PostgreSQL 15** and includes:

- **Soft Delete Pattern**: All entities support soft deletes with audit trail
- **Repository Pattern**: Clean abstraction layer for data access
- **Database Constraints**: Enforced data integrity at the database level
- **JSON Support**: PostgreSQL JSONB columns for flexible configuration storage
- **Migration Support**: Code-first database schema management
- **Connection Pooling**: Optimized for high-performance applications

## Entity Relationships

```mermaid
erDiagram
    Users {
        int Id PK
        string Username UK
        string PasswordHash
        string Role
        datetime CreatedAt
        datetime LastLogin
        bool IsDeleted
    }
    
    Projects {
        int Id PK
        string Name
        string CCNetProjectName UK
        string Status
        datetime CreatedAt
        bool IsDeleted
    }
    
    Builds {
        int Id PK
        int ProjectId FK
        string BuildNumber
        string Status
        datetime StartTime
        datetime EndTime
        bool IsDeleted
    }
    
    Packages {
        int Id PK
        string Title
        string Version
        int ProjectId FK
        int SourceBuildId FK
        string Status
        datetime PublishDate
        bool IsDeleted
    }
    
    PackageConfigurations {
        int Id PK
        int PackageId FK
        string BuildFolder
        bool ZipContents
        jsonb StorageSettings
        jsonb HelpCenterSettings
        bool IsDeleted
    }
    
    Publications {
        int Id PK
        int PackageId FK
        string Status
        datetime PublishedAt
        jsonb PublicationDetails
        bool IsDeleted
    }
    
    PublishingSteps {
        int Id PK
        int PublicationId FK
        string StepName
        string Status
        int ProgressPercent
        datetime StartedAt
        datetime CompletedAt
        bool IsDeleted
    }
    
    FogBugzCases {
        int Id PK
        int CaseId UK
        string Title
        string Status
        datetime LastUpdated
        bool IsOpen
        bool IsDeleted
    }
    
    FogBugzEvents {
        int Id PK
        int CaseId FK
        string EventType
        string User
        datetime EventDate
        string ReleaseNote
        int ZendeskNumber
        bool IsDeleted
    }
    
    BuildCommits {
        int Id PK
        int BuildId FK
        string CommitHash
        string FogBugzCaseId
        jsonb ModifiedFiles
        datetime CommitDate
        bool IsDeleted
    }
    
    StorageProviders {
        int Id PK
        string Name UK
        string Type
        jsonb Configuration
        bool IsActive
        bool IsDeleted
    }
    
    HelpCenterProviders {
        int Id PK
        string Name UK
        string Type
        jsonb Configuration
        bool IsActive
        bool IsDeleted
    }
    
    Projects ||--o{ Builds : "produces"
    Projects ||--o{ Packages : "contains"
    Builds ||--o{ BuildCommits : "includes"
    Packages ||--o{ PackageConfigurations : "configured by"
    Packages ||--o{ Publications : "published as"
    Publications ||--o{ PublishingSteps : "tracked by"
    FogBugzCases ||--o{ FogBugzEvents : "has history"
    BuildCommits }o--|| FogBugzCases : "references"
```

## Database Schema

### Core Tables

#### Users
- **Purpose**: User authentication and authorization
- **Key Features**: Role-based access control, password hashing, audit trail

#### Projects  
- **Purpose**: CruiseControl.NET project integration
- **Key Features**: Maps to CC.NET project names, tracks build history

#### Builds
- **Purpose**: Individual build records from CC.NET
- **Key Features**: Build status tracking, timing information, artifact paths

#### BuildCommits
- **Purpose**: Source code commits within builds
- **Key Features**: FogBugz case linking, modified files (JSON), commit metadata

### Package Management

#### Packages
- **Purpose**: User-defined software release configurations
- **Key Features**: Version tracking, publishing status, project association

#### PackageConfigurations
- **Purpose**: Package-specific publishing settings
- **Key Features**: Storage settings (JSON), help center settings (JSON), build options

### Publishing Workflow

#### Publications
- **Purpose**: Publishing execution records
- **Key Features**: Workflow status, publication details (JSON), timing

#### PublishingSteps
- **Purpose**: Individual workflow step tracking
- **Key Features**: Progress percentage, error handling, step ordering

### External Integration

#### FogBugzCases & FogBugzEvents
- **Purpose**: FogBugz/Manuscript integration
- **Key Features**: Case history, release note extraction, Zendesk linking

#### StorageProviders & HelpCenterProviders
- **Purpose**: External service configuration
- **Key Features**: Pluggable providers, encrypted configuration (JSON)

## Soft Delete Implementation

All entities inherit from `BaseEntity` with soft delete support:

```csharp
public abstract class BaseEntity
{
    public int Id { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime UpdatedAt { get; set; }
    public bool IsDeleted { get; set; }
    public DateTime? DeletedAt { get; set; }
    public string? DeletedBy { get; set; }
    public string? CreatedBy { get; set; }
    public string? UpdatedBy { get; set; }
}
```

### Query Filters
Entity Framework automatically filters out soft-deleted records:
```csharp
modelBuilder.Entity<User>().HasQueryFilter(e => !e.IsDeleted);
```

### Manual Override
To include soft-deleted records:
```csharp
var allUsers = context.Users.IgnoreQueryFilters().Where(u => u.IsDeleted);
```

## Repository Pattern

### Interface Example
```csharp
public interface IUserRepository
{
    Task<User?> GetByIdAsync(int id);
    Task<User?> GetByUsernameAsync(string username);
    Task<List<User>> GetAllAsync();
    Task<User> CreateAsync(User user);
    Task<User> UpdateAsync(User user);
    Task DeleteAsync(int id, string deletedBy);
    Task<bool> ExistsAsync(int id);
}
```

### Benefits
- Clean separation of concerns
- Testable data access layer
- Consistent error handling
- Transaction management

## Database Configuration

### Connection String
Production connection strings stored in `secrets.yaml`:
```yaml
Database:
  ConnectionString: "Host=localhost;Port=5432;Database=software_release_management;Username=postgres;Password=your_secure_password"
```

### Performance Settings
```json
{
  "Database": {
    "CommandTimeout": 30,
    "Pooling": {
      "MinPoolSize": 5,
      "MaxPoolSize": 100,
      "ConnectionIdleLifetime": 300
    },
    "Retry": {
      "EnableRetryOnFailure": true,
      "MaxRetryCount": 5,
      "MaxRetryDelay": 30
    }
  }
}
```

## Setup Instructions

### 1. Prerequisites
- Docker and Docker Compose
- .NET 8 SDK
- PostgreSQL client tools (optional)

### 2. Start Database
```bash
# Start PostgreSQL with Docker Compose
docker compose up postgres -d

# Optional: Start with PgAdmin
docker compose --profile dev up -d
```

### 3. Run Migrations
```bash
cd src/Database
dotnet ef migrations add InitialCreate
dotnet ef database update
```

### 4. Seed Data
```bash
# Development environment automatically seeds test data
ASPNETCORE_ENVIRONMENT=Development dotnet run
```

## Development Workflow

### Creating Migrations
```bash
# Navigate to Database project
cd src/Database

# Create new migration
dotnet ef migrations add MigrationName

# Review generated migration files
# Apply migration
dotnet ef database update
```

### Database Scripts
```bash
# Generate SQL script for production deployment
dotnet ef migrations script --output migrations.sql

# Generate script for specific migration range
dotnet ef migrations script PreviousMigration TargetMigration
```

### Data Seeding
The database automatically seeds:
- Default users (admin, user, readonly)
- Storage providers (AWS S3, Box.com, FTP)
- Help center providers (Zendesk, Salesforce)
- Test projects and packages (development only)

## Security Features

### Password Hashing
- BCrypt with work factor 12
- Automatic salt generation
- Secure password verification

### Secrets Management
- Production secrets in `secrets.yaml`
- Environment variable overrides
- Docker secrets support

### Database Security
- Connection pooling limits
- SQL injection protection (parameterized queries)
- Role-based access control
- Audit logging

## Performance Considerations

### Indexing Strategy
- Primary keys (clustered indexes)
- Foreign key relationships
- Query optimization indexes:
  - `Users.Username` (unique)
  - `Projects.CCNetProjectName` (unique)
  - `Builds.StartTime`, `Builds.Status`
  - `FogBugzCases.LastUpdated`
  - `Publications.Status`, `Publications.PublishedAt`

### JSON Column Usage
PostgreSQL JSONB columns provide:
- Efficient storage and querying
- Flexible configuration schemas
- GIN index support for complex queries

### Connection Pooling
- Configured connection limits
- Idle connection cleanup
- Health check monitoring

## Monitoring and Maintenance

### Health Checks
```csharp
services.AddHealthChecks()
    .AddDbContextCheck<DatabaseContext>("database");
```

### Logging
- Entity Framework query logging
- Migration execution logs
- Performance monitoring
- Error tracking

### Backup Strategy
```bash
# Database backup
pg_dump -h localhost -p 5432 -U postgres -d software_release_management > backup.sql

# Restore from backup
psql -h localhost -p 5432 -U postgres -d software_release_management < backup.sql
```

## Troubleshooting

### Common Issues

1. **Connection Issues**
   ```bash
   # Check PostgreSQL status
   docker compose logs postgres
   
   # Test connection
   psql -h localhost -p 5432 -U postgres -d software_release_management
   ```

2. **Migration Failures**
   ```bash
   # Reset migrations (development only)
   dotnet ef database drop --force
   dotnet ef database update
   ```

3. **Performance Issues**
   ```sql
   -- Check slow queries
   SELECT query, mean_time, calls 
   FROM pg_stat_statements 
   ORDER BY mean_time DESC LIMIT 10;
   ```

### Support
- Check Entity Framework documentation
- Review PostgreSQL logs
- Monitor application performance
- Use PgAdmin for database administration

## Next Steps
- Set up monitoring and alerting
- Implement database backup automation
- Configure read replicas for scaling
- Set up connection string encryption