Replace SQLite-backed ILookupCache with MemoryCache + swappable provider abstraction

[mforce]

Problem

The ILookupCache implementation (LookupCache) hits SQLite on every GetAsync/SetAsync call. For the single-instance deploy this is fine today, but:

• Every metadata lookup round-trips the DB for what should be a fast in-memory check
• SetAsync calls SaveChangesAsync on every write — unnecessary SQLite write amplification
• The LookupCacheEntry table is in the same DB as collection data, so a large cache competes with queries for WAL locks
• If we ever run multiple instances (Phase 4+), the per-process SQLite cache is useless

Goal

Replace the SQLite-backed lookup cache with a strategy pattern:

1. Default: IMemoryDistributedCache (in-process) — zero deploy cost, no persistence across restarts (acceptable for a 30-day TTL cache on a single-instance app)
2. Swappable: Redis — opt-in via config for multi-instance deployments
3. Interface stays ILookupCache — callers (TmdbMovieProvider, MusicBrainzMusicProvider, IgdbGameProvider, UpcItemDbClient) are unchanged

Decisions

Decision	Choice
Drop persistence for default?	Yes — cache is wiped on restart. 30-day TTL + rare restarts = negligible impact.
JsonSerializerOptions	Reuse current options (JsonStringEnumConverter(allowIntegerValues: true)) via a shared static (LookupCacheJson.Options)
Expiration mode	Absolute — matches current SQLite semantics (FetchedAt set on write only)
JsonSerializerOptions wiring	Extract to static holder, no DI
Deserialization failure	Delete bad entry + return default (same as today)
DI wiring location	Inside AddMetadataLookup() — AddStackExchangeRedisCache is an IServiceCollection extension
Adapter lifetime	AddSingleton — underlying IDistributedCache is singleton, adapter has no per-request state
.env.example	No change — memory is default, Redis config only needed for opt-in (documented in architecture.md)
Provider tests	Mock ILookupCache with Moq — no DbContext/SQLite needed. Cache mechanics tested in adapter tests.
One-way migration	Dropping LookupCacheEntries is irreversible. Cached data is ephemeral and rebuilt on next lookup — no user-facing data loss.

Proposed design

Interface (unchanged)

public interface ILookupCache
{
    Task<T?> GetAsync<T>(string provider, string key, TimeSpan ttl, CancellationToken ct = default);
    Task SetAsync<T>(string provider, string key, T value, CancellationToken ct = default);
}

Shared JSON options

// Infrastructure/Lookup/LookupCacheJson.cs
public static class LookupCacheJson
{
    public static readonly JsonSerializerOptions Options = new(JsonSerializerDefaults.Web)
    {
        Converters = { new JsonStringEnumConverter(allowIntegerValues: true) },
    };
}

DistributedCacheAdapter

Thin wrapper: ILookupCache(provider, key, ttl) → IDistributedCache.Set/Get("lookup:{provider}:{key}", bytes, options).

• Composite key: lookup:{provider}:{key}
• Serialize/deserialize via LookupCacheJson.Options
• AbsoluteExpirationRelativeToNow = ttl
• On JSON deserialization failure: RemoveAsync(key) + return default

DI wiring (MetadataLookupServiceCollectionExtensions.cs)

var cacheProvider = config.GetValue<string>("Collectify:Cache:Provider") ?? "memory";
cacheProvider.ToLowerInvariant() switch
{
    "redis" =>
    {
        var redisConfig = config["Collectify:Cache:Redis:Configuration"];
        services.AddStackExchangeRedisCache(opt => opt.Configuration = redisConfig!);
        services.AddSingleton<ILookupCache, DistributedCacheAdapter>();
    },
    _ =>
    {
        services.AddMemoryDistributedCache();
        services.AddSingleton<ILookupCache, DistributedCacheAdapter>();
    }
};

Config (opt-in only)

Collectify__Cache__Provider=redis
Collectify__Cache__Redis__Configuration=localhost:6379

Default (memory) requires no config.

Tasks

1. Add LookupCacheJson.cs (shared JsonSerializerOptions static)
2. Add DistributedCacheAdapter : ILookupCache in Infrastructure/Lookup/
3. Remove LookupCache class from ILookupCache.cs (keep interface)
4. Update DI registration in MetadataLookupServiceCollectionExtensions.cs
5. Remove LookupCacheEntry entity from Domain
6. Remove DbSet<LookupCacheEntry> + Fluent config from CollectifyDbContext
7. Add EF migration to drop LookupCacheEntries table
8. Replace LookupCacheTests with DistributedCacheAdapterTests (Moq)
9. Swap LookupCache → Moq ILookupCache in all four provider tests
10. Add <PackageReference Include="Moq" /> to test project
11. Update XML docs in TmdbMovieProvider.cs and UpcItemDbClient.cs ("cached in LookupCacheEntry table" → "cached in-memory")
12. Update docs/architecture.md — external providers section: "round-trips a JSON payload through the existing LookupCacheEntry table" → describes in-memory IDistributedCache with Redis opt-in
13. Update docs/data-model.md — remove reference to "keep the full provider response in LookupCacheEntry.JsonResponse so we can mine it later" (no longer persisted)
14. Update docs/superpowers/specs/2026-05-30-photo-snap-visual-lookup-design.md — references to LookupCache for image-hash caching

Blast radius

File	Change
Domain/Entities/LookupCacheEntry.cs	Delete
Infrastructure/Data/CollectifyDbContext.cs	Remove DbSet + Fluent config
Infrastructure/Lookup/ILookupCache.cs	Keep interface, delete LookupCache, add DistributedCacheAdapter + LookupCacheJson
Infrastructure/Lookup/MetadataLookupServiceCollectionExtensions.cs	Swap DI registration
Infrastructure/Lookup/Tmdb/TmdbMovieProvider.cs	XML doc only
Infrastructure/Lookup/Upc/UpcItemDbClient.cs	XML doc only
tests/.../LookupCacheTests.cs	Replace with adapter tests (Moq)
tests/.../*ProviderTests.cs (4 files)	LookupCache → Moq ILookupCache
tests/.../Collectify.Tests.csproj	Add Moq
New migration	Drop LookupCacheEntries
docs/architecture.md	Update external providers section
docs/data-model.md	Remove LookupCacheEntry.JsonResponse reference
docs/superpowers/specs/2026-05-30-photo-snap-visual-lookup-design.md	Update LookupCache references

Not doing

• No cache invalidation bus / pub-sub between instances
• No cache metrics / hit-rate endpoints
• No LRU eviction tuning beyond defaults
• No SQLite persistence for cold-start warmup