LYRO

lyro/
├── apps/
│   ├── desktop/        Tauri 2 · React 19 · Vite      → Win, macOS, Linux
│   │   └── src-tauri/  Rust : symphonia, cpal, rubato, chiffrement offline
│   ├── mobile/         React Native 0.8x (Fabric/TurboModules)
│   │   ├── android/    Kotlin · Media3 · MediaSessionService
│   │   └── ios/        Swift · AVAudioEngine · AVAudioSession
│   └── web/            Lecteur web réduit (SEO, partage, invitations Party)
├── packages/
│   ├── core/           State (Zustand) · client API · file d'attente · sync
│   ├── ui/             Design system : tokens, primitives, icônes
│   ├── player-api/     Interface commune vers les 3 moteurs de lecture natifs
│   └── db/             Schéma SQLite local + migrations (partagé mobile/desktop)
└── services/           Backend (voir §08)

Code partagé effectif : ~68 % · UI spécifique par plateforme : ~32 %

  CLIENTS         Tauri (Win/mac/Linux)   React Native (iOS/Android)   Web léger
                          │                        │                      │
                          └────────────┬───────────┘──────────────────────┘
                                       │  HTTPS/2 · REST + WebSocket
                          ┌────────────▼────────────┐
                          │   API Gateway / BFF     │  Kong · rate-limit, JWT,
                          │   Node 22 · NestJS      │  agrégation d'étagères
                          └────────────┬────────────┘
             ┌──────────┬─────────┬────┴────┬─────────┬──────────┬──────────┐
             ▼          ▼         ▼         ▼         ▼          ▼          ▼
        ┌────────┐ ┌────────┐ ┌───────┐ ┌───────┐ ┌────────┐ ┌───────┐ ┌────────┐
        │identity│ │catalog │ │playbk │ │entitl.│ │perso   │ │social │ │billing │
        │  Go    │ │  Go    │ │  Go   │ │  Go   │ │ Python │ │ Go/WS │ │  TS    │
        └───┬────┘ └───┬────┘ └───┬───┘ └───┬───┘ └───┬────┘ └───┬───┘ └───┬────┘
            │          │          │         │        │          │         │
   ┌────────┴──────────┴──────────┴─────────┴────────┴──────────┴─────────┴──────┐
   │  Postgres 16 (primaire + 2 réplicas)   Redis 7 (cache, présence, verrous)    │
   │  OpenSearch (recherche)   ClickHouse (événements)   Qdrant (vecteurs)        │
   │  Kafka (bus d'événements)   S3 / R2 (objets)                                 │
   └──────────────────────────────────────────────────────────────────────────────┘
                                       │
   INGESTION ──────────────────────────┴─────────────────────────────────────────
   Livraison label (DDEX ERN) → validation → transcodage (FFmpeg sur k8s jobs)
   → empreinte audio + embedding (CLAP) → packaging CMAF → S3 → invalidation CDN

   DIFFUSION ─────────────────────────────────────────────────────────────────────
   S3/R2 (origine) → Multi-CDN (CloudFront + Bunny, bascule par mesure RUM)
   → URL signée 5 min, liée à l'appareil → client

Demande utilisateur
   └─▶ Vérification entitlement (palier, pays, quota appareil, quota titres)
         └─▶ Attribution d'un slot dans la file (3 téléchargements simultanés max)
               └─▶ GET segments (URL signées, reprise par Range HTTP)
                     └─▶ Réassemblage en fichier unique
                           └─▶ Chiffrement AES-256-GCM · clé aléatoire par titre
                                 └─▶ Clé enveloppée par la clé d'appareil (Keychain/
                                     Keystore/DPAPI) et écrite dans SQLite
                                       └─▶ Enregistrement offline_license (expire J+30)

  J+23  ─▶ renouvellement silencieux en tâche de fond
  J+30  ─▶ sans réseau depuis 30 j : lecture bloquée, fichier conservé,
           message explicite « Reconnectez-vous une fois pour réactiver »
  Résiliation ─▶ révocation immédiate, purge au prochain démarrage

  Quotas : 5 appareils · 10 000 titres / appareil · délier un appareil = 1× / 30 j

Client ──▶ Kafka ──▶ Flink ──┬─▶ ClickHouse   (analytique, tableaux de bord)
 événements   topic    fenêtres  ├─▶ Feast/Redis  (features temps réel, < 5 ms)
 de lecture  "plays"   glissantes└─▶ S3 Parquet   (entraînement nocturne)

Événements collectés : play_start, play_progress (25/50/75/95 %), skip (+ position),
like, add_to_playlist, download, search_query, shelf_impression, shelf_click.
Un skip avant 15 s est un signal négatif fort. Une écoute complétée à 95 % vaut
plus qu'un like — les gens likent peu et écoutent beaucoup.

-- ══ IDENTITÉ ═════════════════════════════════════════════════════════════
CREATE TABLE accounts (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email           citext UNIQUE NOT NULL,
  password_hash   text,                        -- NULL si passkey/OAuth seulement
  country         char(2) NOT NULL,            -- gouverne les droits de diffusion
  status          text NOT NULL DEFAULT 'active',
  created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE profiles (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id      uuid NOT NULL REFERENCES accounts ON DELETE CASCADE,
  display_name    text NOT NULL,
  avatar_url      text,
  kind            text NOT NULL DEFAULT 'adult',   -- adult | kid
  birth_year      smallint,
  pin_hash        text,                            -- verrou parental
  explicit_ok     boolean NOT NULL DEFAULT true,
  daily_limit_min smallint,                        -- profils enfants
  theme           jsonb NOT NULL DEFAULT '{"mode":"dark","accent":"amber"}',
  created_at      timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT kid_needs_pin CHECK (kind <> 'kid' OR pin_hash IS NOT NULL)
);
CREATE INDEX ON profiles (account_id);

CREATE TABLE devices (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id      uuid NOT NULL REFERENCES accounts ON DELETE CASCADE,
  platform        text NOT NULL,              -- windows|macos|linux|ios|android
  name            text NOT NULL,
  fingerprint     text NOT NULL,
  public_key      bytea,                      -- enveloppe des clés hors-ligne
  last_seen_at    timestamptz,
  unlinked_at     timestamptz,
  UNIQUE (account_id, fingerprint)
);

-- ══ CATALOGUE ════════════════════════════════════════════════════════════
CREATE TABLE artists (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name            text NOT NULL,
  sort_name       text NOT NULL,
  bio             text,
  image_url       text,
  verified        boolean NOT NULL DEFAULT false,
  external_ids    jsonb NOT NULL DEFAULT '{}'   -- musicbrainz, isni, spotify…
);

CREATE TABLE albums (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title           text NOT NULL,
  primary_artist  uuid NOT NULL REFERENCES artists,
  album_type      text NOT NULL,               -- album|single|ep|compilation
  release_date    date,
  label           text,
  upc             text,
  cover_url       text,
  cover_palette   jsonb,                       -- 3 couleurs OKLCH, thème Immersif
  total_tracks    smallint
);
CREATE INDEX ON albums (primary_artist, release_date DESC);

CREATE TABLE tracks (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  album_id        uuid REFERENCES albums ON DELETE CASCADE,
  title           text NOT NULL,
  disc_no         smallint NOT NULL DEFAULT 1,
  track_no        smallint NOT NULL,
  duration_ms     integer NOT NULL,
  isrc            char(12),
  explicit        boolean NOT NULL DEFAULT false,
  bpm             numeric(5,2),
  musical_key     smallint,                    -- 0-11, pour l'enchaînement DJ
  loudness_lufs   numeric(5,2),                -- EBU R128, normalisation
  peak_dbfs       numeric(5,2),
  popularity      smallint NOT NULL DEFAULT 0,
  is_local        boolean NOT NULL DEFAULT false,  -- import utilisateur
  owner_profile   uuid REFERENCES profiles,        -- non NULL si is_local
  available_in    char(2)[] NOT NULL DEFAULT '{}', -- restriction territoriale
  created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON tracks (album_id, disc_no, track_no);
CREATE INDEX ON tracks (isrc) WHERE isrc IS NOT NULL;
CREATE INDEX ON tracks (owner_profile) WHERE is_local;

-- Un rendu encodé par palier de qualité
CREATE TABLE track_assets (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  track_id        uuid NOT NULL REFERENCES tracks ON DELETE CASCADE,
  tier            text NOT NULL,               -- normal|high|hifi|master
  codec           text NOT NULL,               -- aac_lc | flac
  bitrate_kbps    integer,
  sample_rate     integer NOT NULL,
  bit_depth       smallint,
  channels        smallint NOT NULL DEFAULT 2,
  size_bytes      bigint NOT NULL,
  manifest_key    text NOT NULL,               -- clé S3 du manifeste CMAF
  encoder_delay   integer NOT NULL DEFAULT 0,  -- échantillons — gapless
  encoder_padding integer NOT NULL DEFAULT 0,
  drm_scheme      text,                        -- widevine|fairplay|aes128|null
  UNIQUE (track_id, tier)
);

-- Crédits complets : notre différenciateur
CREATE TABLE credits (
  track_id        uuid NOT NULL REFERENCES tracks ON DELETE CASCADE,
  artist_id       uuid NOT NULL REFERENCES artists,
  role            text NOT NULL,   -- performer|producer|composer|mixing|
                                   -- mastering|featured|engineer|studio
  PRIMARY KEY (track_id, artist_id, role)
);
CREATE INDEX ON credits (artist_id, role);

-- ══ BIBLIOTHÈQUE ═════════════════════════════════════════════════════════
CREATE TABLE library_items (
  profile_id      uuid NOT NULL REFERENCES profiles ON DELETE CASCADE,
  item_type       text NOT NULL,   -- track|album|artist|playlist|show|book
  item_id         uuid NOT NULL,
  added_at        timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (profile_id, item_type, item_id)
);
CREATE INDEX ON library_items (profile_id, added_at DESC);

CREATE TABLE playlists (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_profile   uuid NOT NULL REFERENCES profiles ON DELETE CASCADE,
  name            text NOT NULL,
  description     text,
  cover_url       text,
  visibility      text NOT NULL DEFAULT 'private', -- private|unlisted|public
  collaborative   boolean NOT NULL DEFAULT false,
  share_slug      text UNIQUE,
  updated_at      timestamptz NOT NULL DEFAULT now()
);

-- position : index fractionnaire LSEQ → réordonnancement hors-ligne sans conflit
CREATE TABLE playlist_tracks (
  playlist_id     uuid NOT NULL REFERENCES playlists ON DELETE CASCADE,
  track_id        uuid NOT NULL REFERENCES tracks ON DELETE CASCADE,
  position        numeric(20,10) NOT NULL,
  added_by        uuid REFERENCES profiles,
  added_at        timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (playlist_id, track_id)
);
CREATE INDEX ON playlist_tracks (playlist_id, position);

-- ══ PAROLES ══════════════════════════════════════════════════════════════
CREATE TABLE lyrics (
  track_id        uuid PRIMARY KEY REFERENCES tracks ON DELETE CASCADE,
  language        char(2) NOT NULL,
  sync_level      text NOT NULL,          -- none|line|word
  provider        text NOT NULL,
  lines           jsonb NOT NULL,         -- [{t:12340,d:2100,x:"…",w:[…]}]
  translations    jsonb NOT NULL DEFAULT '{}',
  updated_at      timestamptz NOT NULL DEFAULT now()
);

-- ══ HORS-LIGNE ═══════════════════════════════════════════════════════════
CREATE TABLE downloads (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  profile_id      uuid NOT NULL REFERENCES profiles ON DELETE CASCADE,
  device_id       uuid NOT NULL REFERENCES devices ON DELETE CASCADE,
  track_id        uuid NOT NULL REFERENCES tracks ON DELETE CASCADE,
  tier            text NOT NULL,
  size_bytes      bigint NOT NULL,
  state           text NOT NULL,          -- queued|active|done|failed|revoked
  completed_at    timestamptz,
  UNIQUE (device_id, track_id, tier)
);
CREATE INDEX ON downloads (profile_id, state);

CREATE TABLE offline_licenses (
  device_id       uuid NOT NULL REFERENCES devices ON DELETE CASCADE,
  track_id        uuid NOT NULL REFERENCES tracks ON DELETE CASCADE,
  wrapped_key     bytea NOT NULL,         -- clé titre chiffrée par la clé appareil
  issued_at       timestamptz NOT NULL DEFAULT now(),
  expires_at      timestamptz NOT NULL,   -- issued_at + 30 j
  revoked_at      timestamptz,
  PRIMARY KEY (device_id, track_id)
);
CREATE INDEX ON offline_licenses (expires_at) WHERE revoked_at IS NULL;

-- ══ ABONNEMENT ═══════════════════════════════════════════════════════════
CREATE TABLE subscriptions (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id        uuid NOT NULL REFERENCES accounts ON DELETE CASCADE,
  plan              text NOT NULL,   -- free|premium|hifi|duo|family|student
  status            text NOT NULL,   -- trialing|active|past_due|canceled
  seats             smallint NOT NULL DEFAULT 1,
  provider          text NOT NULL,   -- stripe|apple|google
  provider_ref      text,
  current_period_end timestamptz,
  cancel_at_period_end boolean NOT NULL DEFAULT false
);
CREATE UNIQUE INDEX ON subscriptions (account_id) WHERE status IN ('trialing','active');

-- ══ PARTY ════════════════════════════════════════════════════════════════
CREATE TABLE party_sessions (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  host_profile    uuid NOT NULL REFERENCES profiles ON DELETE CASCADE,
  join_code       char(6) UNIQUE NOT NULL,
  mode            text NOT NULL DEFAULT 'sync',  -- sync | shared_queue
  voting_enabled  boolean NOT NULL DEFAULT true,
  clock_epoch_ms  bigint NOT NULL,               -- référence de synchronisation
  ended_at        timestamptz
);

CREATE TABLE party_queue (
  session_id      uuid NOT NULL REFERENCES party_sessions ON DELETE CASCADE,
  track_id        uuid NOT NULL REFERENCES tracks,
  added_by        uuid NOT NULL REFERENCES profiles,
  position        numeric(20,10) NOT NULL,
  votes           integer NOT NULL DEFAULT 0,
  played_at       timestamptz,
  PRIMARY KEY (session_id, track_id)
);

CREATE TABLE play_events (
  ts              DateTime64(3),
  profile_id      UUID,
  account_id      UUID,
  device_id       UUID,
  track_id        UUID,
  artist_id       UUID,
  event           LowCardinality(String), -- start|p25|p50|p75|p95|complete|skip
  position_ms     UInt32,
  tier            LowCardinality(String),
  source          LowCardinality(String), -- shelf|search|radio|playlist|library
  source_id       Nullable(UUID),
  shuffle         UInt8,
  offline         UInt8,
  country         LowCardinality(FixedString(2))
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (profile_id, ts)
TTL ts + INTERVAL 26 MONTH;   -- au-delà : agrégats seulement (RGPD)

-- Vue matérialisée alimentant le calcul de royalties centré utilisateur
CREATE MATERIALIZED VIEW mv_monthly_streams
ENGINE = SummingMergeTree ORDER BY (month, account_id, artist_id) AS
SELECT toStartOfMonth(ts) AS month, account_id, artist_id, count() AS streams
FROM play_events WHERE event = 'p95' GROUP BY month, account_id, artist_id;