crowdNFO
Konzept in Stichpunkten:
- Es gibt user mit verschiedenen trust-grades
- Neue User können sich bewerben(Bewerbungsformular) und müssen freigeschaltet werden
- Adminpanel (Userverwaltung, Release-Verwaltung, Bewerbungsverwaltung, Bewerbungsformular-Verwaltung, NFO/MediaInfo Reportverwaltung)
- User haben Rollen(Admins, Member, Trusted, VIP). Nur Admins können auf den Adminbereich zugreifen
- Jede Rolle hat einen Unterschiedlichen SubmissionTrustGrade (default: 1)
- User Felder u.A: username, passwordhash, email, role,
- User haben einen Memberbereich (Passwort ändern, Ticket erstellen)
- Es gibt Releases
- Releases können sowohl eine NFO als auch MediaInfos haben
- User können eine NFO und/oder MediaInfo submitten
- Wurde eine NFO/MediaInfo von genügend verschiedenen Usern submitted wird sie akzeptiert (sobald der kumulative SubmissionTrustGrade der User >= 5 ist)
- NFOs/MediaInfos können reported werden inkl. Kommentar
- Releases können von Usern kommentiert werden
- ASP.NET API
- PostgreSQL
- Userprofil mit Anzahl submitteter NFOs/MediaInfos
- Highscore
----------------------------------------------------------------------------------------
## Projektkonzept: crowdNFO Datenbank + App
### Projektübersicht
Die crowdNFO Datenbank + App ist eine Plattform, die .NFO-Dateien und MediaInfos zu Releases von Warez-Release-Groups durch Crowdsourcing sammelt. Benutzer können NFO-Dateien und MediaInfos zu Releases einreichen und diese kommentieren. Das System basiert auf Benutzerrollen und Vertrauensstufen, um die Qualität der eingereichten Dateien zu gewährleisten.
Folgend: Dateien bezeichnet sowohl NFO-Dateien als auch MediaInfo JSON Dateien
Ein Release hat kann beliebig viele Dateien haben. Die Datei mit dem höchsten kumulativen SubmissionTrustGrade wird als Standard angesehen.
### Hauptfunktionen
1. **Benutzerverwaltung**
- Benutzer können sich über ein Bewerbungsformular registrieren.
- Neue Benutzer müssen von Administratoren freigeschaltet werden.
- Benutzerrollen: Admin, Member, Trusted, VIP.
- Unterschiedliche Rollen haben verschiedene SubmissionTrustGrades (Standard: 1).
- Benutzerprofile enthalten: Benutzername, Passwort-Hash, E-Mail, Rolle.
2. **Admin-Panel**
- Verwaltung der Benutzer (Zuweisung von Rollen, Freischaltung neuer Benutzer).
- Verwaltung der Dateien (Annahme, Ablehnung, Bearbeitung).
- Verwaltung der Bewerbungen (Überprüfung und Entscheidung).
- Verwaltung des Bewerbungsformulars (Anpassung und Aktualisierung).
- Verwaltung der Reports (Überprüfung und Entscheidung).
3. **Mitgliederbereich**
- Benutzer können ihr Passwort ändern.
- Benutzer können ihr Profil auf die Sichtbarkeit privat/nur Mitglieder/öffentlich ändern
- Benutzer können Tickets erstellen und Support anfordern.
- Benutzer können Dateien(NFO/MediaInfo) einreichen.
- Benutzer können Dateien melden
- Benutzer können Releases kommentieren
- Benutzer haben ein Profil, auf dem die Anzahl der eingereichten Dateien sichtbar ist.
- Es gibt eine Highscore-API, welche die 100 Nutzer mit den meisten Einreichungen anzeigt (je nach Sichtbarkeitseinstellung wird der Name zensiert)
4. **NFO/MediaInfo-Management**
- Einreichung von Dateien durch Benutzer.
- Akzeptanz von Dateien erfolgt, wenn der kumulative SubmissionTrustGrade der einreichenden Benutzer ≥ 5 ist.
- Dateien können von Benutzern gemeldet werden
- Kommentare können von Benutzern gemeldet werden
### Technische Spezifikationen
1. **Backend**
- **ASP.NET API**: Bereitstellung von Endpunkten für alle CRUD-Operationen und Authentifizierungsmechanismen.
- **PostgreSQL**: Datenbank zur Speicherung aller Benutzerdaten, NFO-Dateien und Berichte.
2. **Frontend**
- **Web-Anwendung**: Benutzerfreundliche Oberfläche für Admin- und Mitgliederbereich.
- **Responsive Design**: Unterstützung für Desktop und mobile Geräte.
### Datenbankstruktur
```
-- Enum Definitionen
CREATE TYPE profile_visibility_enum AS ENUM ('public', 'members_only', 'private');
CREATE TYPE application_status_enum AS ENUM ('pending', 'approved', 'rejected');
CREATE TYPE file_type_enum AS ENUM ('NFO', 'MediaInfo');
CREATE TYPE file_status_enum AS ENUM ('pending', 'accepted', 'rejected');
CREATE TYPE report_status_enum AS ENUM ('pending', 'reviewed', 'resolved');
CREATE TYPE ticket_status_enum AS ENUM ('open', 'closed');
CREATE TYPE report_type_enum AS ENUM ('file', 'comment');
CREATE TYPE release_category_enum AS ENUM (
'Movies',
'TV',
'Games',
'Software',
'Music',
'Books',
'Other'
);
-- Tabelle Rollen
CREATE TABLE Roles (
RoleId SERIAL PRIMARY KEY,
RoleName VARCHAR(50) UNIQUE NOT NULL,
SubmissionTrustGrade INTEGER DEFAULT 1 CHECK (SubmissionTrustGrade > 0),
CanAccessAdmin BOOLEAN DEFAULT FALSE,
Description TEXT,
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Tabelle Benutzer
CREATE TABLE Users (
UserId SERIAL PRIMARY KEY,
Username VARCHAR(50) UNIQUE NOT NULL CHECK (LENGTH(Username) >= 3),
PasswordHash VARCHAR(500) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
RoleId INTEGER REFERENCES Roles(RoleId) ON DELETE RESTRICT,
ProfileVisibility profile_visibility_enum DEFAULT 'public',
IsActivated BOOLEAN DEFAULT FALSE,
LastLoginAt TIMESTAMP,
FailedLoginAttempts INTEGER DEFAULT 0,
IsLocked BOOLEAN DEFAULT FALSE,
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Tabelle Bewerbungen
CREATE TABLE Applications (
ApplicationId SERIAL PRIMARY KEY,
UserId INTEGER REFERENCES Users(UserId) ON DELETE CASCADE,
ApplicationData JSONB NOT NULL,
ReviewedBy INTEGER REFERENCES Users(UserId) ON DELETE SET NULL,
ReviewComment TEXT,
ReviewedAt TIMESTAMP,
Status application_status_enum DEFAULT 'pending',
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Tabelle Releases
CREATE TABLE Releases (
ReleaseId SERIAL PRIMARY KEY,
ReleaseName VARCHAR(500) NOT NULL,
ReleaseGroup VARCHAR(100),
Category release_category_enum NOT NULL,
CreatedBy INTEGER REFERENCES Users(UserId) ON DELETE SET NULL,
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Tabelle Dateien
CREATE TABLE Files (
FileId UUID DEFAULT gen_random_uuid(),
ReleaseId INTEGER REFERENCES Releases(ReleaseId) ON DELETE CASCADE,
SubmittedBy INTEGER REFERENCES Users(UserId) ON DELETE SET NULL,
FileType file_type_enum NOT NULL,
OriginalFileName VARCHAR(500) NOT NULL,
StoredFileName VARCHAR(500) NOT NULL,
FileHash VARCHAR(64),
Status file_status_enum DEFAULT 'pending',
AcceptedAt TIMESTAMP,
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (FileId, CreatedAt),
UNIQUE (StoredFileName, CreatedAt)
) PARTITION BY RANGE (CreatedAt);
-- Create quarterly partitions
CREATE TABLE Files_2025_Q2 PARTITION OF Files
FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
CREATE TABLE Files_2025_Q3 PARTITION OF Files
FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');
CREATE TABLE Files_2025_Q4 PARTITION OF Files
FOR VALUES FROM ('2025-10-01') TO ('2026-01-01');
-- File Submissions tracking
CREATE TABLE FileSubmissions (
SubmissionId UUID DEFAULT gen_random_uuid(),
FileId UUID NOT NULL,
FileCreatedAt TIMESTAMP NOT NULL,
UserId INTEGER NOT NULL REFERENCES Users(UserId) ON DELETE CASCADE,
SubmissionTrustGrade INTEGER NOT NULL,
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (SubmissionId, CreatedAt),
FOREIGN KEY (FileId, FileCreatedAt) REFERENCES Files(FileId, CreatedAt) ON DELETE CASCADE
) PARTITION BY RANGE (CreatedAt);
-- Create quarterly partitions
CREATE TABLE FileSubmissions_2025_Q2 PARTITION OF FileSubmissions
FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
CREATE TABLE FileSubmissions_2025_Q3 PARTITION OF FileSubmissions
FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');
CREATE TABLE FileSubmissions_2025_Q4 PARTITION OF FileSubmissions
FOR VALUES FROM ('2025-10-01') TO ('2026-01-01');
-- Pre-calculated trust grades for files
CREATE MATERIALIZED VIEW mv_file_trust_grades AS
SELECT
f.FileId,
f.CreatedAt as FileCreatedAt,
f.ReleaseId,
f.FileType,
COALESCE(SUM(fs.SubmissionTrustGrade), 0) as CumulativeTrustGrade,
COUNT(fs.UserId) as SubmissionCount,
f.Status
FROM Files f
LEFT JOIN FileSubmissions fs ON f.FileId = fs.FileId AND f.CreatedAt = fs.FileCreatedAt
GROUP BY f.FileId, f.CreatedAt, f.ReleaseId, f.FileType, f.Status;
-- Unique index on materialized view
CREATE UNIQUE INDEX ON mv_file_trust_grades (FileId, FileCreatedAt);
-- Tabelle Kommentare
CREATE TABLE Comments (
CommentId SERIAL PRIMARY KEY,
ReleaseId INTEGER REFERENCES Releases(ReleaseId) ON DELETE CASCADE,
UserId INTEGER REFERENCES Users(UserId) ON DELETE SET NULL,
CommentText TEXT NOT NULL CHECK (LENGTH(CommentText) > 0),
IsEdited BOOLEAN DEFAULT FALSE,
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Tabelle Meldungen
CREATE TABLE Reports (
ReportId SERIAL PRIMARY KEY,
ReportType report_type_enum NOT NULL,
FileId UUID,
FileCreatedAt TIMESTAMP,
CommentId INTEGER REFERENCES Comments(CommentId) ON DELETE CASCADE,
ReportedBy INTEGER REFERENCES Users(UserId) ON DELETE SET NULL,
ReportReason TEXT NOT NULL,
Status report_status_enum DEFAULT 'pending',
ReviewedBy INTEGER REFERENCES Users(UserId) ON DELETE SET NULL,
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (FileId, FileCreatedAt) REFERENCES Files(FileId, CreatedAt) ON DELETE CASCADE,
CHECK ((FileId IS NOT NULL AND FileCreatedAt IS NOT NULL AND CommentId IS NULL) OR
(FileId IS NULL AND FileCreatedAt IS NULL AND CommentId IS NOT NULL))
);
-- Tabelle Tickets
CREATE TABLE Tickets (
TicketId SERIAL PRIMARY KEY,
UserId INTEGER REFERENCES Users(UserId) ON DELETE SET NULL,
Subject VARCHAR(255) NOT NULL,
Message TEXT NOT NULL,
Status ticket_status_enum DEFAULT 'open',
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Tabelle Highscore
CREATE TABLE Highscore (
HighscoreId SERIAL PRIMARY KEY,
UserId INTEGER REFERENCES Users(UserId) ON DELETE CASCADE,
SubmissionCount INTEGER DEFAULT 0,
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Insert predefined roles
INSERT INTO Roles (RoleName, SubmissionTrustGrade) VALUES
('Admin', 100),
('Member', 15),
('Trusted', 40),
('VIP', 50);
---TODO: Create indexes and further optimization, do that later after having code to see which queries are used
-- Insert predefined roles
INSERT INTO Roles (RoleName, SubmissionTrustGrade, CanAccessAdmin) VALUES
('Admin', 100, true),
('Member', 15, false),
('Trusted', 40, false),
('VIP', 50, false);
---TODO: Create indexes and further optimization, do that later after having code to see which queries are used
```
### Rollen und Berechtigungen
1. **Admins**
- Voller Zugriff auf das Admin-Panel.
- Verwaltung von Benutzern, Dateien, Bewerbungen und Berichten.
2. **Member**
- Kann Dateien einreichen, kommentieren und melden.
- Zugriff auf den Mitgliederbereich.
3. **Trusted**
- Höherer SubmissionTrustGrade.
- Kann Dateien einreichen, kommentieren und melden.
4. **VIP**
- Noch höherer SubmissionTrustGrade.
- Kann Dateien einreichen, kommentieren und melden.
### Workflow
1. **Registrierung und Bewerbung**
- Neue Benutzer registrieren sich über ein Bewerbungsformular.
- Bewerbungen werden von Admins geprüft und freigeschaltet.
2. **NFO und/oder MediaInfo-Einreichung und Überprüfung**
- Benutzer reichen Dateien ein.
- Dateien werden akzeptiert, wenn der kumulative SubmissionTrustGrade der einreichenden Benutzer ≥ 5 ist.
3. **Release-Kommentierung und Berichtserstellung**
- Benutzer können Releases kommentieren
- Benutzer können Dateien(NFO/MediaInfo) von Releases melden (mit Kommentar)
- Reports werden von Admins geprüft und entsprechende Maßnahmen ergriffen.