mystuff_frontend/.opencode/plans/MIGRATION_PLAN.md

Omnyx Backend Migrationsplan

Ziel

Migration von PHP + MySQL zu Bun + Hono + Drizzle ORM für eine Hybrid-Architektur:

• Lokal: SQLite (Desktop/Mobile, offline-fähig)
• Server: PostgreSQL (Docker, Multi-User)
• Desktop: Tauri + Bun-Sidecar
• Mobile: Capacitor + SQLite via WASM

Tech Stack

Komponente	Technologie
Runtime	Bun (TypeScript-nativ, bun:sqlite built-in)
Server	Hono (leicht, Zod-Validation)
ORM	Drizzle ORM (bun:sqlite / node-postgres)
DB lokal	bun:sqlite
DB Server	PostgreSQL (Docker)
Desktop	Tauri + Bun-Sidecar
Mobile	Capacitor + @libsql/client-web (WASM)

Projektstruktur (separates Repo)

omnyx-backend/
├── src/
│   ├── index.ts               ← Hono-Server + Router
│   ├── db/
│   │   ├── schema.ts          ← Drizzle-Schema (alle Tabellen)
│   │   ├── index.ts           ← DB-Connection (bun:sqlite / pg)
│   │   └── migrate.ts         ← Migration runner
│   ├── routes/
│   │   ├── media.ts           ← CRUD /api/media
│   │   ├── cast.ts            ← CRUD /api/cast
│   │   └── settings.ts        ← CRUD /api/settings
│   ├── middleware/
│   │   ├── cors.ts
│   │   └── error.ts
│   └── lib/
│       └── json-fields.ts     ← Helper für JSON-Array-Felder
├── scripts/
│   └── migrate-from-php.ts    ← Import-Skript von alter MySQL-DB
├── package.json
├── tsconfig.json
├── Dockerfile
└── docker-compose.yml

Datenbank-Tabellen

media

Spalte	Typ	Anmerkung
id	integer	PK autoIncrement
title	text	NOT NULL
year	integer	NOT NULL
poster	text	nullable
banner	text	nullable
description	text	nullable
rating	real	nullable
category	text	nullable
type	text	default 'Movie'
status	text	default 'Released'
aspect_ratio	text	nullable
runtime	integer	nullable
director	text	nullable
writer	text	nullable
release_date	text	nullable
source	text	nullable
created_at	text	default datetime()
updated_at	text	default datetime()
genres	text	JSON-Array
tags	text	JSON-Array
studios	text	JSON-Array
categories	text	JSON-Array
series	text	JSON-Array
platforms	text	JSON-Array
developers	text	JSON-Array
completion_status	text	nullable
play_count	integer	default 0
last_activity	text	nullable
playtime	integer	default 0

episodes

Spalte	Typ
id	integer PK
media_id	integer FK → media
season	integer
episode_number	integer
title	text
description	text
air_date	text
duration	integer
thumbnail	text

tracks

Spalte	Typ
id	integer PK
media_id	integer FK → media
track_number	integer
title	text
duration	text (format: "M:SS")
artist	text

cast (staff)

Spalte	Typ
id	integer PK
name	text NOT NULL
cleanname	text nullable
photo	text nullable
bio	text nullable
birth_date	text nullable
birth_place	text nullable
occupations	text (JSON-Array)
created_at	text
updated_at	text
media_types	text (JSON-Array)
bust_size	integer nullable
cup_size	text nullable
waist_size	integer nullable
hip_size	integer nullable
height	integer nullable
weight	integer nullable
hair_color	text nullable
eye_color	text nullable
ethnicity	text nullable

adult_specifics

Spalte	Typ
id	integer PK
cast_id	integer FK → cast
tattoos	text nullable
piercings	text nullable
measurements	text nullable
shoe_size	integer nullable

media_cast_staff (Junction)

Spalte	Typ
media_id	integer FK
cast_id	integer FK
role	text
character_name	text nullable
character_image	text nullable
PK	(media_id, cast_id)

cast_media_filmography (Junction)

Spalte	Typ
cast_id	integer FK
media_id	integer FK
role	text
character_name	text nullable

settings

Spalte	Typ
id	integer PK
enabled_categories	text (JSON-Array)
items_per_page	integer default 20
grid_item_size	integer default 5
default_view	text default 'grid'
show_adult_content	integer (boolean) default 0
auto_play_trailers	integer (boolean) default 0
language	text default 'en'
theme	text default 'system'
jellyfin_library_mappings	text nullable
page_title	text nullable
favicon	text nullable
custom_colors	text nullable (JSON)
created_at	text
updated_at	text

API-Endpoints (1:1 von PHP)

Media

GET    /api/media?page=1&limit=10000  → PaginatedResponse<ApiMediaItem>
GET    /api/media/:id                 → ApiResponse<ApiMediaItem>
POST   /api/media                     → ApiResponse<ApiMediaItem>
PUT    /api/media/:id                  → ApiResponse<ApiMediaItem>
DELETE /api/media/:id                  → ApiResponse<{message}>

Cast

GET    /api/cast?page=1&limit=100000  → PaginatedResponse<ApiCastItem>
GET    /api/cast/:id                  → ApiResponse<ApiCastItem>
GET    /api/cast/:id/media            → PaginatedResponse<ApiMediaItem>
POST   /api/cast                      → ApiResponse<ApiCastItem>
PUT    /api/cast/:id                  → ApiResponse<ApiCastItem>
DELETE /api/cast/:id                  → ApiResponse<{message}>

Settings

GET    /api/settings   → ApiResponse<ApiSettingsItem>
POST   /api/settings   → ApiResponse<ApiSettingsItem>
PUT    /api/settings   → ApiResponse<ApiSettingsItem>

Response-Format (identisch zu PHP)

{
  "success": true,
  "data": { ... }
}

DB-Connection (Driver-Abstraktion)

if (process.env.DATABASE_URL) {
  // Server-Modus → PostgreSQL
  const { Pool } = await import('pg');
  const { drizzle } = await import('drizzle-orm/node-postgres');
  db = drizzle(new Pool({ connectionString: process.env.DATABASE_URL }));
} else {
  // Lokaler Modus → bun:sqlite
  const { Database } = await import('bun:sqlite');
  const { drizzle } = await import('drizzle-orm/bun-sqlite');
  const sqlite = new Database('./data/omnyx.db', { create: true });
  sqlite.exec('PRAGMA journal_mode=WAL;');
  db = drizzle(sqlite);
}

Desktop-Bundle (Tauri)

• Bun-Binary via bun build --compile (~50MB Single-File)
• Tauri startet Binary als Sidecar
• React-Frontend (produktions-Build) in Tauri-WebView
• API auf localhost:3001

Mobile (Capacitor + WASM SQLite)

• SQLite läuft direkt im WebView via @libsql/client/web
• Drizzle-Queries identisch zum Backend
• Kein separater Server nötig
• Optional: Sync mit Server bei Internet-Verbindung

Docker-Deployment

services:
  backend:
    build: .
    environment:
      DATABASE_URL: postgres://user:pass@db:5432/omnyx
  db:
    image: postgres:16-alpine

Status (23.05.2026)

Phase	Beschreibung	Status
P1	Backend-Projekt (Bun + Hono + Drizzle)	✅ Fertig
P2	Hono-Routen (Media, Cast, Settings)	✅ Fertig
P3	Tests + TypeScript-Lint	✅ Fertig
P4	Docker-Config aktualisiert	✅ Fertig
P5	Tauri Desktop (Bun-Sidecar)	✅ Fertig
P6	PostgreSQL-Support + Deployment	⏳ Ausstehend
P7	Sync-Logik (Hybrid)	⏳ Optional
P8	Capacitor Mobile (lokales SQLite)	⏳ Ausstehend

Aktuelle Struktur

G:\kyoo\
├── backend/           ← Bun + Hono + Drizzle
│   ├── src/
│   │   ├── index.ts           ← Hono-Server (Port 3001)
│   │   ├── db/
│   │   │   ├── schema.ts      ← Drizzle-Schema (9 Tabellen)
│   │   │   ├── index.ts       ← DB-Connection (bun:sqlite / pg-fähig)
│   │   │   └── migrate.ts     ← Auto-Migration (CREATE TABLE IF NOT EXISTS)
│   │   ├── routes/
│   │   │   ├── media.ts       ← CRUD /api/media
│   │   │   ├── cast.ts        ← CRUD /api/cast + POST /adult
│   │   │   ├── settings.ts    ← CRUD /api/settings
│   │   │   └── images.ts      ← Proxy-/Cache-Endpunkt /api/images/proxy
│   │   ├── lib/converters.ts  ← Type-Converter
│   │   └── types.ts           ← API-Types
│   ├── uploads/               ← Gecachte Bilder (offline-fähig)
│   ├── dist-exe/              ← Compiled binary (~50MB)
│   ├── Dockerfile             ← Bun-basiert
│   ├── package.json
│   └── README.md
├── frontend/          ← React SPA + Tauri-Desktop-Shell
│   ├── src-tauri/             ← Tauri v2 Desktop-Konfiguration
│   │   ├── src/lib.rs         ← Sidecar-Spawn (omnyx-backend)
│   │   ├── binaries/          ← Kompilierte Binaries pro Target
│   │   ├── tauri.conf.json    ← Window 1280x800, externalBin
│   │   ├── capabilities/      ← shell:default, shell:allow-spawn
│   │   └── Cargo.toml         ← tauri-plugin-shell
│   └── .env.tauri             ← VITE_API_URL=http://localhost:3001
└── docker-compose.yml

Nächste Schritte

1. PostgreSQL-Support (P6) — pg-Treiber für Drizzle, docker-compose mit Postgres
2. Capacitor Mobile (P8) — Android-App mit embedded SQLite via WASM
3. Sticky Footer Bug — Footer klebt nicht am unteren Rand bei kurzen Seiten
4. PUT /api/cast/:id um adult-Felder erweitern (height, weight etc. aktuell nicht updatbar)