️🛠 - Mobile database schema and migration strategy

[maxwellmattryan]

Description

Define and implement the database schema strategy for the mobile app. The desktop schema (db/schema.rs) creates tables like tracks, device_exports, and export_checkpoints that are meaningless on mobile. Additionally, mobile needs new tables for sync state and per-field change tracking.

Scope:

• Decide between shared schema.rs (with unused tables on mobile) vs. separate mobile schema
• Define mobile-required tables:
  • Shared: discovery_releases, discovery_tracks, discovery_release_tags, tags, tag_categories, playlists, playlist_discovery_releases, settings, discovery_stream_cache, discovery_sc_client_id_cache, discovery_audio_cache
  • New (sync-related): local_changes (per-field change queue for sync push), sync_state (last_sync_timestamp, paired_desktop_id), paired_devices
• Implement per-field modification timestamp tracking for LWW conflict resolution:
  • Either add field_modified_at columns to existing tables (e.g., artist_modified_at, notes_modified_at on discovery_releases)
  • Or create a separate field_timestamps table: (entity_type, entity_id, field_name, modified_at)
• Define migration strategy for schema updates: how do desktop schema changes propagate to mobile?
• Add required migrations to schema.rs

Key files:

• src-tauri/src/db/schema.rs — migration additions
• src-tauri/src/models/ — new models for sync state, local changes

Open questions

• Shared schema (simpler, accepts overhead of empty unused tables) vs. separate mobile schema (cleaner, more maintenance)?
• Per-field timestamps as additional columns on existing tables vs. a separate field_timestamps table?
• How should schema version compatibility be tracked between desktop and mobile for sync?
• Should the local_changes table store full old/new values for undo capability, or just the change action?

Acceptance criteria

• Mobile schema strategy is documented and implemented
• Per-field modification timestamps are tracked for all editable fields on discovery releases
• local_changes table exists and can queue per-field changes for sync push
• sync_state and paired_devices tables exist
• Schema migrations are appended to schema.rs (not modifying existing migrations)
• Desktop build and schema are unaffected

Tasks

TBD