59 lines
2.4 KiB
SQL
59 lines
2.4 KiB
SQL
CREATE TABLE users (
|
|
id INTEGER PRIMARY KEY,
|
|
telegram_username TEXT,
|
|
first_name TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'user'
|
|
CHECK (role IN ('user', 'admin', 'banned')),
|
|
accepted_terms_at DATETIME,
|
|
created_at DATETIME NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE contents (
|
|
id TEXT PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id),
|
|
status TEXT NOT NULL DEFAULT 'active'
|
|
CHECK (status IN ('staged', 'active', 'deleted', 'blacklisted')),
|
|
view_count INTEGER NOT NULL DEFAULT 0,
|
|
max_views INTEGER,
|
|
allow_download INTEGER NOT NULL DEFAULT 1,
|
|
password_hash TEXT,
|
|
created_at DATETIME NOT NULL DEFAULT (datetime('now')),
|
|
deleted_at DATETIME
|
|
);
|
|
|
|
CREATE TABLE content_files (
|
|
content_id TEXT NOT NULL REFERENCES contents(id),
|
|
file_index INTEGER NOT NULL DEFAULT 0,
|
|
original_name TEXT NOT NULL,
|
|
stored_path TEXT NOT NULL UNIQUE,
|
|
mime_type TEXT NOT NULL,
|
|
size_bytes INTEGER NOT NULL,
|
|
ciphertext_size_bytes INTEGER NOT NULL,
|
|
encrypted_key_wrapped BLOB NOT NULL,
|
|
encrypted_hash BLOB NOT NULL,
|
|
render_flags INTEGER NOT NULL DEFAULT 0,
|
|
created_at DATETIME NOT NULL DEFAULT (datetime('now')),
|
|
PRIMARY KEY (content_id, file_index)
|
|
);
|
|
|
|
CREATE TABLE reports (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
content_id TEXT NOT NULL REFERENCES contents(id),
|
|
reporter_user_id INTEGER NOT NULL REFERENCES users(id),
|
|
reason TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'open'
|
|
CHECK (status IN ('open', 'dismissed', 'actioned')),
|
|
created_at DATETIME NOT NULL DEFAULT (datetime('now')),
|
|
resolved_at DATETIME,
|
|
resolver_id INTEGER REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE admin_actions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
admin_user_id INTEGER NOT NULL REFERENCES users(id),
|
|
target_type TEXT NOT NULL CHECK (target_type IN ('content', 'user')),
|
|
target_id TEXT NOT NULL,
|
|
action TEXT NOT NULL CHECK (action IN ('delete', 'blacklist', 'ignore')),
|
|
created_at DATETIME NOT NULL DEFAULT (datetime('now'))
|
|
);
|