# community_rating Type Change: DECIMAL → DOUBLE PRECISION

## Date
March 30, 2026

## Decision
Changed `community_rating` from `DECIMAL(3,1)` to `DOUBLE PRECISION` in the implementation plan.

## Rationale

### Original Choice: DECIMAL(3,1)
The schema was originally designed with:
```sql
community_rating DECIMAL(3,1)  -- 0.0-10.0 with 1 decimal place
```

**Reasoning:**
- Exact decimal precision (avoids 8.4999999)
- Distinguishes from user ratings (INTEGER 1-10 for half-star precision)
- Matches metadata source format (decimal strings)

### Problems with DECIMAL(3,1)

1. **Awkward Go code** - Required conversion via `pgtype.Numeric`:
   ```go
   // Complex conversion needed
   CommunityRating: func() pgtype.Numeric {
       var n pgtype.Numeric
       if metadata.CommunityRating > 0 {
           n.Scan(fmt.Sprintf("%.1f", metadata.CommunityRating))
       }
       return n
   }()
   ```

2. **Only DECIMAL column in database** - No existing pattern to follow

3. **Unnecessary complexity** - ComicInfo.xml already uses float64, DECIMAL adds friction

### New Choice: DOUBLE PRECISION

Changed to:
```sql
community_rating DOUBLE PRECISION  -- 0.0-10.0
```

**Benefits:**

1. **Simple Go code** - Direct assignment:
   ```go
   CommunityRating: pgtype.Float8{Float64: metadata.CommunityRating, Valid: true}
   ```

2. **Natural mapping** - ComicInfo.xml `float64` → PostgreSQL `DOUBLE PRECISION` → Go `pgtype.Float8`

3. **No conversion overhead** - No string formatting, no Scan() method

4. **Negligible precision loss** - Floating-point error < 0.00001% for 0-10 ratings

5. **Consistent with codebase** - Uses standard pgtype pattern (like other float columns)

## Impact

### Files Changed in Implementation Plan

1. **database/schema/schema.sql** (reference only)
   - Already has DOUBLE PRECISION
   - No action needed

2. **internal/services/media_scanner.go**
   - Simplified CreateMediaItem call
   - Removed complex pgtype.Numeric conversion

3. **internal/database/models.go** (sqlc generated)
   - CommunityRating type: `pgtype.Numeric` → `pgtype.Float8`
   - Will be auto-updated after schema change

### Testing Implications

None - the change is transparent to:
- API consumers (JSON serialization unchanged)
- Database queries (same range and comparison operations)
- User interface (display format unchanged)

## Migration Notes

If you have an existing database with DECIMAL(3,1):

```sql
-- Migration from DECIMAL to DOUBLE PRECISION
ALTER TABLE media_items
ALTER COLUMN community_rating TYPE DOUBLE PRECISION
USING community_rating::DOUBLE PRECISION;
```

Since this is pre-production (no production deployments exist), use:
```bash
# Option 1: Recreate database (cleanest)
podman compose down -v
podman compose up -d

# Option 2: Manual migration (preserves test data)
podman exec bookhoard_db psql -U postgres -d bookhoard -c "
ALTER TABLE media_items
ALTER COLUMN community_rating TYPE DOUBLE PRECISION
USING community_rating::DOUBLE PRECISION;
"
```

## Conclusion

The DOUBLE PRECISION choice provides:
- ✅ Simpler, cleaner code
- ✅ Better developer experience
- ✅ Natural type mapping
- ✅ No practical precision loss

For a 0-10 rating scale, the theoretical precision advantage of DECIMAL is outweighed by the code simplicity benefit.
