Remove calibre_id column from books table

[masonfox]

Context

Part of the Book Sources Refactor (Phase R1 completed in branch 003-non-calibre-books). This is a follow-up phase to complete the architectural transition from the single calibre_id field to the many-to-many book_sources table.

Dependencies

Blocked by: #185 (Support non-Calibre books)

• Must wait until Phase R1 (Book Sources Refactor) is released and stable in production
• This ensures we have sufficient production data to validate the transition works correctly

Goal

Remove the deprecated books.calibre_id column and fully transition all code to use bookSourceRepository for querying book sources.

Current State

After Phase R1:

• books.calibre_id marked as deprecated with comment
• All new code uses book_sources table via bookSourceRepository
• Legacy code still references calibre_id in some places (primarily for Calibre-specific operations)
• Migration 0022 populates both calibre_id and book_sources for backward compatibility

Scope

1. Code Audit

• Find all remaining references to books.calibre_id
• Document each usage and replacement strategy
• Identify any edge cases or risky migrations

2. Code Updates

• Replace bookRepository.findByCalibreId() with bookSourceRepository.findByExternalId("calibre", id)
• Update all queries that filter by calibre_id
• Update sync service to use book_sources for Calibre book lookups
• Update duplicate detection to use book_sources

3. Schema Changes

• Create new migration to drop books.calibre_id column
• Add NOT NULL constraint to book_sources.external_id (previously nullable)
• Update indexes if needed

4. Testing

• Update all tests referencing calibre_id
• Ensure full test suite passes (3880+ tests)
• Test migration on database copy
• Validate no performance regressions

5. Documentation

• Update repository pattern guide
• Update architecture docs
• Document migration rollback procedure

Risk Assessment

Medium Risk:

• Removing a core identifier field requires careful testing
• Any missed calibre_id references will cause runtime errors
• Migration is not easily reversible

Mitigation:

• Comprehensive code audit before starting
• Staged rollout: Test on staging environment first
• Keep backups before migration
• Monitor for errors after deployment

Timeline

Estimated: 8-12 hours

• Code audit: 2 hours
• Code updates: 4 hours
• Migration: 1 hour
• Testing: 3 hours
• Documentation: 2 hours

Success Criteria

• No references to books.calibre_id in codebase (except migration files)
• All queries use bookSourceRepository for source lookups
• Full test suite passes
• Migration runs successfully on production copy
• No performance regressions in common queries

[masonfox]

Blocked by: #185

This issue cannot be started until #185 (Support non-Calibre books - Phase R1) is released to production and has been stable for at least one release cycle. This ensures we have real-world validation of the book sources architecture before removing the legacy calibre_id field.