Scalable platform metadata: user-configurable mappings & unified storage

[Darkkal]

Problem

The current approach to supporting gallery-dl extractors requires developer intervention across every layer of the codebase whenever a new platform is added:

1. Database: Dedicated post_details_* (and optional *_users) tables require schema migrations.
2. Processors: Custom TypeScript processor classes must be authored and registered in MetadataProcessorFactory.
3. TypeScript Types: Explicit per-platform optional fields must be added to GalleryRow in src/types/media.ts.
4. Repository Queries: Explicit LEFT JOINs must be added to getMediaItems() in src/lib/db/repositories/media.ts.
5. Metadata Merging: Platform-specific merge*Metadata() utilities must be maintained in src/lib/metadata.ts.
6. UI Components: Direct conditional checks on platform fields in Lightbox.tsx, PostCard.tsx, etc.

At 4 supported platforms (Twitter, Pixiv, Gelbooru, E-Hentai), the primary media query already performs 7 LEFT JOINs. With gallery-dl supporting hundreds of extractors, this hard-coded pattern does not scale.

Goal

Redesign the platform metadata system so that adding support for a new gallery-dl extractor does not require core codebase or database schema modifications.

Phased Execution Plan

Phase 1: Type & UI Decoupling (Sub-issue #54)

• Collapse per-platform fields on GalleryRow into generic platformDetails and platformUser slots.
• Retain existing database tables, but transform/flatten them at the repository boundary.
• Replace per-platform merge functions with a generic typed accessor.
• Outcome: The UI and type layers become platform-agnostic immediately.

Phase 2: Unified Metadata Storage

• Replace per-platform detail tables with a single, unified storage pattern.
• Option A: Single JSON column on posts (infinite flexibility, no per-platform migrations).
• Option B: Single platform_metadata table with core indexed columns + JSON overflow.
• Note: Evaluated against database capabilities (e.g. Postgres GIN indexing support if migrating away from SQLite).

Phase 3: User-Configurable Mapping Engine

• Replace hard-coded processor classes with a user-configurable mapping system.
• Allow users to define JSON path mappings for creator handles, tag arrays, creation dates, and custom metadata fields.
• Process tags into the central post_tags table while storing extra platform attributes in unified storage.

Phase 4: Community Mapping Repository

• Establish a repository of JSON/YAML platform mapping definitions that users can submit via PRs.
• Enable clients to fetch and validate mapping definitions without central server hosting.

Open Questions & Trade-offs

• Search & Filter Indexing: Moving platform-specific attributes out of dedicated columns into JSON blobs may impact SQL query performance if searching by platform-specific fields.
• Community Repo Lifecycle: Should mapping configs live in the main web-gallery repository (/mappings) or in a standalone repository?

Tracked Sub-issues

• Refactor GalleryRow to use generic platform metadata slots #54 — Phase 1: Refactor GalleryRow to use generic platform metadata slots