Bruno Charest 2866d74b32 first commit

2025-09-14 23:05:30 -04:00

14 KiB

Raw Permalink History

NewTube User Management (SQLite)

This document defines a simple, secure, and extensible user management module for NewTube, optimized for a private setup with a small user base and a SQLite backend.

Goals

Simple: small schema, clear endpoints, easy local setup.
Secure: strong password hashing, short-lived access token + rotating refresh token in HttpOnly cookie, rate limiting.
Extensible: flexible provider support, versioned preferences, optional/JSON fields.
Portable: single SQLite DB, easy migrations and backups; works for Web + Desktop (Electron/Tauri).

Data Model (SQLite)

Types: TEXT, INTEGER, REAL, BLOB. Timestamps: ISO8601 strings in UTC.

-- Users & identity ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY,                 -- UUID
  username TEXT NOT NULL UNIQUE,
  email TEXT UNIQUE,
  password_hash TEXT NOT NULL,         -- Argon2id or bcrypt
  is_active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  last_login_at TEXT
);

CREATE TABLE IF NOT EXISTS user_preferences (
  user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  language TEXT DEFAULT 'en',            -- 'fr','en','es', ...
  default_provider TEXT,                 -- 'youtube','twitch','dailymotion','vimeo','peertube','odysee','rumble', ...
  theme TEXT DEFAULT 'system',           -- 'light','dark','black','system'
  video_quality TEXT DEFAULT 'auto',     -- '720p','1080p','auto'
  region TEXT,                           -- 'FR','US','CA', ...
  version INTEGER NOT NULL DEFAULT 1,
  updated_at TEXT NOT NULL
);

-- Sessions: refresh tokens are stored hashed; per-device visibility ----------
CREATE TABLE IF NOT EXISTS sessions (
  id TEXT PRIMARY KEY,                   -- UUID (session id)
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  refresh_token_hash TEXT NOT NULL,
  user_agent TEXT,
  device_info TEXT,
  ip_address TEXT,
  is_remember INTEGER NOT NULL DEFAULT 0, -- remember-me checkbox
  created_at TEXT NOT NULL,
  last_seen_at TEXT,
  expires_at TEXT NOT NULL,
  revoked_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_lastseen ON sessions(last_seen_at);

-- Login audit (success and failed attempts) ---------------------------------
CREATE TABLE IF NOT EXISTS login_audit (
  id TEXT PRIMARY KEY,
  user_id TEXT,
  username TEXT,
  ip_address TEXT,
  user_agent TEXT,
  success INTEGER NOT NULL,
  reason TEXT,
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_login_audit_user_time ON login_audit(user_id, created_at DESC);

-- Search & Watch History ----------------------------------------------------
CREATE TABLE IF NOT EXISTS search_history (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  query TEXT NOT NULL,
  filters_json TEXT,                     -- { duration:'short', sort:'relevance' }
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_search_history_user_time ON search_history(user_id, created_at DESC);

CREATE TABLE IF NOT EXISTS watch_history (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider TEXT NOT NULL,
  video_id TEXT NOT NULL,
  title TEXT,
  watched_at TEXT NOT NULL,
  progress_seconds INTEGER DEFAULT 0,
  duration_seconds INTEGER DEFAULT 0,
  last_position_seconds INTEGER DEFAULT 0,
  last_watched_at TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS uq_watch_history_user_video ON watch_history(user_id, provider, video_id);
CREATE INDEX IF NOT EXISTS idx_watch_history_user_time ON watch_history(user_id, watched_at DESC);

-- Subscriptions & Categories ------------------------------------------------
CREATE TABLE IF NOT EXISTS categories (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL,
  UNIQUE(user_id, name)
);

CREATE TABLE IF NOT EXISTS subscriptions (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider TEXT NOT NULL,
  channel_id TEXT NOT NULL,
  channel_name TEXT,
  notify INTEGER NOT NULL DEFAULT 0,
  subscribed_at TEXT NOT NULL,
  UNIQUE(user_id, provider, channel_id)
);

CREATE TABLE IF NOT EXISTS subscription_categories (
  subscription_id TEXT NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
  category_id TEXT NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
  PRIMARY KEY (subscription_id, category_id)
);
CREATE INDEX IF NOT EXISTS idx_subscriptions_user ON subscriptions(user_id);

-- Playlists -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS playlists (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  UNIQUE(user_id, name)
);

CREATE TABLE IF NOT EXISTS playlist_items (
  id TEXT PRIMARY KEY,
  playlist_id TEXT NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,
  provider TEXT NOT NULL,
  video_id TEXT NOT NULL,
  title TEXT,
  added_at TEXT NOT NULL,
  position INTEGER NOT NULL,
  UNIQUE(playlist_id, provider, video_id)
);
CREATE INDEX IF NOT EXISTS idx_playlist_items_order ON playlist_items(playlist_id, position);

-- Personal tags (optional/bonus) -------------------------------------------
CREATE TABLE IF NOT EXISTS tags (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL,
  UNIQUE(user_id, name)
);

CREATE TABLE IF NOT EXISTS video_tags (
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider TEXT NOT NULL,
  video_id TEXT NOT NULL,
  tag_id TEXT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (user_id, provider, video_id, tag_id)
);

API (REST) Dev base URL

Prod base URL: https://<your-domain>/api
Dev via Angular proxy: http://localhost:4200/proxy/api
- Add to proxy.conf.json:

{
  "/proxy/api": {
    "target": "http://localhost:4000",
    "secure": false,
    "changeOrigin": true,
    "logLevel": "debug",
    "pathRewrite": { "^/proxy/api": "/api" }
  }
}

You will need to restart npm run dev after editing the proxy.

Auth

POST /auth/register → { username, password, email? }
POST /auth/login → { username, password, rememberMe? }
POST /auth/refresh (cookie) → rotates refresh, returns new access token
POST /auth/logout → { allDevices? }
GET /auth/sessions → list active sessions
DELETE /auth/sessions/:id → revoke a session

Responses typically include: { user: {id, username, email}, accessToken, sessionId } and set a refreshToken cookie (HttpOnly, Secure, SameSite=Strict).

User & Preferences

GET /user/me
PATCH /user/me → { email? }
GET /user/preferences
PATCH /user/preferences → { language?, defaultProvider?, theme?, videoQuality?, region? }

History

GET /user/history/search?limit=&before=
POST /user/history/search → { query, filters? }
DELETE /user/history/search/:id | /user/history/search?all=1
GET /user/history/watch?limit=&before=
POST /user/history/watch → { provider, videoId, title?, watchedAt, progressSeconds?, durationSeconds? }
PATCH /user/history/watch/:id → { progressSeconds?, lastPositionSeconds? }
DELETE /user/history/watch/:id | /user/history/watch?all=1

Subscriptions & Categories

GET /user/subscriptions?category=&provider=
POST /user/subscriptions → { provider, channelId, channelName?, notify? }
DELETE /user/subscriptions/:id
POST /user/subscriptions/:id/categories → { add:[categoryId], remove:[categoryId] }
GET /user/categories
POST /user/categories → { name }
DELETE /user/categories/:id

Playlists

GET /user/playlists
POST /user/playlists → { name }
PATCH /user/playlists/:id → { name }
DELETE /user/playlists/:id
GET /user/playlists/:id/items
POST /user/playlists/:id/items → { provider, videoId, title?, position? }
PATCH /user/playlists/:id/items/reorder → { items:[{id, position}] }
DELETE /user/playlists/:id/items/:itemId
Export/Import:
- GET /user/playlists/:id/export?format=json|csv
- POST /user/playlists/import (multipart)

Tags (optional)

GET /user/tags
POST /user/tags → { name }
DELETE /user/tags/:id
POST /user/tags/apply → { provider, videoId, tagIds:[...] }
GET /user/tags/videos?tagId=...

Sync & Export

GET /sync/changes?since=ISO8601 → changeset with tombstones
POST /sync/changes → upserts a client changeset (LWW)
GET /user/export?scope=all|profile|history|subscriptions|playlists&format=json

Security

Passwords: Argon2id (preferred) or bcrypt (cost 9, e.g., 12+). Store only the hash.
Access token: JWT (15 min), in Authorization header. Claims: sub, sid, iat, exp.
Refresh token: random 256-bit, stored server-side as a hash in sessions. Sent as HttpOnly cookie (Secure, SameSite=Strict). Rotate on every /auth/refresh.
CSRF: JWT in header mitigates CSRF; if you expose refresh cookie endpoints, also use Double-Submit Cookie (X-CSRF-Token header).
XSS: Never put tokens in localStorage. Use Content Security Policy (CSP), escape outputs, sanitize HTML.
Rate limiting: strict on /auth/login by IP and by username (e.g., 5/min), with exponential backoff.
CORS: whitelist origins; cookies Secure in production; HSTS via reverse proxy.

Frontend Integration (Angular)

Services and utilities to add in src/services/:

auth.service.ts
- login(username, password, rememberMe), register(...), logout(allDevices?), refresh()
- Holds in-memory accessToken and a currentUser signal.
user.service.ts
- loadMe(), loadPreferences(), updatePreferences(partial)
- Emits preferences signal.
history.service.ts
- recordSearch(query, filters?)
- recordWatchStart(provider, videoId, title?)
- recordWatchProgress(id, progressSeconds) and recordWatchEnd(...)
subscriptions.service.ts, playlists.service.ts (CRUD + helpers)
auth.interceptor.ts
- Attaches Authorization: Bearer <accessToken> when present.
- Handles 401 by attempting a transparent refresh() then retries once.
auth.guard.ts
- Protects /account/** routes.

Wiring into current UI:

Bootstrap (e.g., in index.tsx or app initializer):
- If a refresh cookie exists, call /auth/refresh to obtain an access token on startup.
- Fetch /user/preferences and apply:
  - Set default provider, region via InstanceService (e.g., instances.setSelectedProvider(prefs.default_provider) and instances.setRegion(...)).
  - Apply theme.
Search:
- On successful query (search.component.ts), call historyService.recordSearch(q, filters?).
Watch:
- On page load, call recordWatchStart(...).
- Optionally, add event listeners in VideoPlayerComponent to periodically call recordWatchProgress(...) and on end recordWatchEnd(...).
Account UI (routes to add):
- /account (container), with tabs:
  - Profile (username readonly, email, change password)
  - Preferences (language, provider, theme, quality, region)
  - History (Search/Watch, filters + delete)
  - Subscriptions (with categories, notify toggle)
  - Playlists (CRUD, reorder, export/import)
  - Sessions (active sessions list, revoke)
  - Data & Sync (export, import, sync now)

Dev Proxy (Angular)

Add the following entry to proxy.conf.json (do not remove existing ones):

"/proxy/api": {
  "target": "http://localhost:4000",
  "secure": false,
  "changeOrigin": true,
  "logLevel": "debug",
  "pathRewrite": { "^/proxy/api": "/api" }
}

Then you can call API endpoints from the frontend at /proxy/api/....

Backend Implementation Notes

Runtime: Node.js.
Framework: NestJS (recommended) or Express + Zod/class-validator.
ORM: Prisma (SQLite provider) or Drizzle ORM. Use migrations.
Security: helmet, express-rate-limit, hpp, cors (strict), argon2.
Tokens: jsonwebtoken with EdDSA/RS256; cookie for setting refresh.
Logs: pino or winston.
Prod: reverse proxy (nginx/Traefik), TLS, backups of SQLite. Use PRAGMA journal_mode=WAL.

Roadmap (Prioritized)

High Backend MVP with /auth, /user/preferences, /auth/refresh, sessions and audit.
High Angular auth.service, auth.interceptor, bootstrap refresh, load preferences and apply to InstanceService.
High History endpoints and history.service + instrumentation in SearchComponent and WatchComponent.
Medium Subscriptions model + UI (basic list + categories, notify toggle).
Medium Playlists CRUD + reorder + export/import.
Medium Sessions page (list + revoke), and Data & Sync page (export + manual sync).
Low Tags and personal stats (time watched per provider, top channels, activity by day).
Low Offline-first (desktop): local queue and /sync/changes.

Example JSON Shapes

// User
{
  "id": "c73b...",
  "username": "alice",
  "email": "alice@example.com"
}

// Preferences
{
  "language": "fr",
  "defaultProvider": "youtube",
  "theme": "dark",
  "videoQuality": "auto",
  "region": "CA",
  "version": 1
}

// Subscription
{
  "id": "sub_123",
  "provider": "youtube",
  "channelId": "UC_x5XG1OV2P6uZZ5FSM9Ttw",
  "channelName": "Google Developers",
  "notify": true,
  "subscribedAt": "2025-04-05T10:00:00Z"
}

// Watch history item
{
  "id": "wh_123",
  "provider": "youtube",
  "videoId": "dQw4w9WgXcQ",
  "title": "Rick Astley - Never Gonna Give You Up",
  "watchedAt": "2025-04-05T10:05:00Z",
  "progressSeconds": 215,
  "durationSeconds": 212,
  "lastPositionSeconds": 0,
  "lastWatchedAt": "2025-04-05T10:09:00Z"
}

Minimal UI Mock (routes)

/login, /register
/account (tabs: profile, preferences, history, playlists, subscriptions, sessions, data)
In header: display username + menu; in guest mode: Login/Register buttons

Notes specific to NewTube

Integrate preferences with InstanceService:
- On bootstrap, set selected provider and region from server preferences.
- Apply theme globally (e.g., HTML data-theme attribute).
The current components (Home, Search, Watch) stay unchanged; just add history recording calls.
Keep using /proxy/* pattern consistently in dev; call your API with /proxy/api/....
Start with MVP endpoints (auth + preferences) before playlists/subscriptions to deliver value early.

If you need, I can also provide a NestJS skeleton (modules, entities, DTOs, guards) tailored to this schema and ready to run against SQLite.

14 KiB Raw Permalink History Unescape Escape

NewTube  User Management (SQLite)