rinkplanner/hub/sql/schema.sql
torsten 055a2919c3 feat: Kür-Hub mit Einladungen, Uploads und API
- PHP-App unter hub/ für rinkplanner-hub.programmschmiede.de
- MariaDB-Schema, Argon2id, Sessions, CSRF und Ratenbegrenzung
- Einladungscodes, Admin-Bereich und Kür-Pakete mit Audiodatei
- JSON-API v1 mit Bearer-Tokens für die Godot-App
- Deployment und Erstinstallation über tools/deploy_hub.sh
- Doku und Credentials-Vorlage ergänzt
2026-11-11 13:14:26 +01:00

172 lines
6.8 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

-- Rinkplanner-Hub – Datenbankschema (MariaDB 10.11, utf8mb4)
--
-- Wird vom Installer (install.php) angewendet. Alle Zeitstempel sind UTC.
-- Änderungen am Schema brauchen eine neue Migrationsnummer in schema_migrations.
CREATE TABLE IF NOT EXISTS schema_migrations (
version INT UNSIGNED NOT NULL,
applied_at DATETIME NOT NULL,
PRIMARY KEY (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS users (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(32) NOT NULL,
display_name VARCHAR(64) NOT NULL,
email VARCHAR(190) NULL,
password_hash VARCHAR(255) NOT NULL,
role ENUM('admin', 'user') NOT NULL DEFAULT 'user',
status ENUM('active', 'disabled') NOT NULL DEFAULT 'active',
must_change_password TINYINT(1) NOT NULL DEFAULT 0,
auth_epoch INT UNSIGNED NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL,
last_login_at DATETIME NULL,
password_changed_at DATETIME NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_users_username (username),
UNIQUE KEY uq_users_email (email),
KEY idx_users_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS invites (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
code_hash CHAR(64) NOT NULL,
note VARCHAR(190) NULL,
email VARCHAR(190) NULL,
created_by BIGINT UNSIGNED NULL,
created_at DATETIME NOT NULL,
expires_at DATETIME NULL,
used_by BIGINT UNSIGNED NULL,
used_at DATETIME NULL,
revoked_at DATETIME NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_invites_code (code_hash),
KEY idx_invites_created (created_at),
CONSTRAINT fk_invites_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
CONSTRAINT fk_invites_used_by FOREIGN KEY (used_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS routines (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
owner_id BIGINT UNSIGNED NOT NULL,
title VARCHAR(190) NOT NULL,
skater_name VARCHAR(190) NULL,
category VARCHAR(40) NULL,
music_title VARCHAR(190) NULL,
music_artist VARCHAR(190) NULL,
description TEXT NULL,
status ENUM('published', 'hidden') NOT NULL DEFAULT 'published',
current_revision_id BIGINT UNSIGNED NULL,
download_count INT UNSIGNED NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY (id),
KEY idx_routines_list (status, updated_at),
KEY idx_routines_owner (owner_id, updated_at),
CONSTRAINT fk_routines_owner FOREIGN KEY (owner_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS routine_revisions (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
routine_id BIGINT UNSIGNED NOT NULL,
revision INT UNSIGNED NOT NULL,
uploaded_by BIGINT UNSIGNED NOT NULL,
routine_original_name VARCHAR(190) NOT NULL,
routine_path VARCHAR(255) NOT NULL,
routine_bytes INT UNSIGNED NOT NULL,
routine_sha256 CHAR(64) NOT NULL,
file_routine_id CHAR(36) NULL,
audio_original_name VARCHAR(190) NULL,
audio_path VARCHAR(255) NULL,
audio_bytes INT UNSIGNED NULL,
audio_sha256 CHAR(64) NULL,
audio_mime VARCHAR(100) NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_revision_number (routine_id, revision),
KEY idx_revisions_uploader (uploaded_by),
CONSTRAINT fk_revisions_routine FOREIGN KEY (routine_id) REFERENCES routines (id) ON DELETE CASCADE,
CONSTRAINT fk_revisions_uploader FOREIGN KEY (uploaded_by) REFERENCES users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE routines
ADD CONSTRAINT fk_routines_current_revision
FOREIGN KEY (current_revision_id) REFERENCES routine_revisions (id) ON DELETE SET NULL;
CREATE TABLE IF NOT EXISTS downloads (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
routine_id BIGINT UNSIGNED NOT NULL,
revision_id BIGINT UNSIGNED NULL,
user_id BIGINT UNSIGNED NULL,
kind ENUM('routine', 'audio') NOT NULL DEFAULT 'routine',
ip VARCHAR(45) NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id),
KEY idx_downloads_routine (routine_id, created_at),
KEY idx_downloads_user (user_id, created_at),
CONSTRAINT fk_downloads_routine FOREIGN KEY (routine_id) REFERENCES routines (id) ON DELETE CASCADE,
CONSTRAINT fk_downloads_revision FOREIGN KEY (revision_id) REFERENCES routine_revisions (id) ON DELETE SET NULL,
CONSTRAINT fk_downloads_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS api_tokens (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
name VARCHAR(64) NOT NULL,
prefix CHAR(12) NOT NULL,
token_hash CHAR(64) NOT NULL,
created_at DATETIME NOT NULL,
last_used_at DATETIME NULL,
expires_at DATETIME NULL,
revoked_at DATETIME NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_tokens_hash (token_hash),
KEY idx_tokens_user (user_id, created_at),
CONSTRAINT fk_tokens_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS remember_tokens (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
selector CHAR(24) NOT NULL,
validator_hash CHAR(64) NOT NULL,
created_at DATETIME NOT NULL,
last_used_at DATETIME NULL,
expires_at DATETIME NOT NULL,
user_agent VARCHAR(255) NULL,
ip VARCHAR(45) NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_remember_selector (selector),
KEY idx_remember_user (user_id, expires_at),
CONSTRAINT fk_remember_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS login_attempts (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(190) NOT NULL,
ip VARCHAR(45) NOT NULL,
success TINYINT(1) NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL,
PRIMARY KEY (id),
KEY idx_attempts_ip (ip, created_at),
KEY idx_attempts_username (username, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS audit_log (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
actor_id BIGINT UNSIGNED NULL,
action VARCHAR(64) NOT NULL,
subject_type VARCHAR(32) NULL,
subject_id BIGINT UNSIGNED NULL,
detail VARCHAR(255) NULL,
ip VARCHAR(45) NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id),
KEY idx_audit_created (created_at),
KEY idx_audit_actor (actor_id, created_at),
CONSTRAINT fk_audit_actor FOREIGN KEY (actor_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO schema_migrations (version, applied_at)
VALUES (1, UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE version = version;