matteoscrugli
8c4a555b88
Backend: - Add 2FA authentication with TOTP support - Add API keys management system - Add audit logging for security events - Add file upload/management system - Add notifications system with preferences - Add session management - Add webhooks integration - Add analytics endpoints - Add export functionality - Add password policy enforcement - Add new database migrations for core tables Frontend: - Add module position system (top/bottom sidebar sections) - Add search and notifications module configuration tabs - Add mobile logo replacing hamburger menu - Center page title absolutely when no tabs present - Align sidebar footer toggles with navigation items - Add lighter icon color in dark theme for mobile - Add API keys management page - Add notifications page with context - Add admin analytics and audit logs pages 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
262 lines
9.5 KiB
Python
262 lines
9.5 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Database migration script - Adds missing columns for new features.
|
|
Run this script to update an existing database without losing data.
|
|
|
|
Usage:
|
|
python migrate_db.py [database_path]
|
|
|
|
If no path provided, uses the default /config/config.db
|
|
"""
|
|
|
|
import sqlite3
|
|
import sys
|
|
from pathlib import Path
|
|
|
|
|
|
def get_existing_columns(cursor, table_name):
|
|
"""Get list of existing column names for a table."""
|
|
cursor.execute(f"PRAGMA table_info({table_name})")
|
|
return {row[1] for row in cursor.fetchall()}
|
|
|
|
|
|
def get_existing_tables(cursor):
|
|
"""Get list of existing tables."""
|
|
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
|
|
return {row[0] for row in cursor.fetchall()}
|
|
|
|
|
|
def migrate(db_path):
|
|
"""Run migrations."""
|
|
print(f"Migrating database: {db_path}")
|
|
|
|
conn = sqlite3.connect(db_path)
|
|
cursor = conn.cursor()
|
|
|
|
existing_tables = get_existing_tables(cursor)
|
|
print(f"Existing tables: {existing_tables}")
|
|
|
|
# ====================
|
|
# USERS TABLE MIGRATIONS
|
|
# ====================
|
|
if "users" in existing_tables:
|
|
existing_cols = get_existing_columns(cursor, "users")
|
|
print(f"Users columns: {existing_cols}")
|
|
|
|
# Add 2FA columns
|
|
if "totp_secret" not in existing_cols:
|
|
print(" Adding totp_secret column...")
|
|
cursor.execute("ALTER TABLE users ADD COLUMN totp_secret VARCHAR(32)")
|
|
|
|
if "totp_enabled" not in existing_cols:
|
|
print(" Adding totp_enabled column...")
|
|
cursor.execute("ALTER TABLE users ADD COLUMN totp_enabled BOOLEAN DEFAULT 0")
|
|
|
|
if "totp_backup_codes" not in existing_cols:
|
|
print(" Adding totp_backup_codes column...")
|
|
cursor.execute("ALTER TABLE users ADD COLUMN totp_backup_codes TEXT")
|
|
|
|
# ====================
|
|
# CREATE NEW TABLES IF MISSING
|
|
# ====================
|
|
|
|
# Audit Logs
|
|
if "audit_logs" not in existing_tables:
|
|
print("Creating audit_logs table...")
|
|
cursor.execute("""
|
|
CREATE TABLE audit_logs (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
user_id VARCHAR(36),
|
|
username VARCHAR(100),
|
|
action VARCHAR(50) NOT NULL,
|
|
resource_type VARCHAR(50),
|
|
resource_id VARCHAR(255),
|
|
details TEXT,
|
|
ip_address VARCHAR(45),
|
|
user_agent VARCHAR(500),
|
|
status VARCHAR(20) DEFAULT 'success',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
|
|
)
|
|
""")
|
|
cursor.execute("CREATE INDEX ix_audit_logs_action ON audit_logs(action)")
|
|
cursor.execute("CREATE INDEX ix_audit_logs_resource_type ON audit_logs(resource_type)")
|
|
cursor.execute("CREATE INDEX ix_audit_logs_created_at ON audit_logs(created_at)")
|
|
|
|
# API Keys
|
|
if "api_keys" not in existing_tables:
|
|
print("Creating api_keys table...")
|
|
cursor.execute("""
|
|
CREATE TABLE api_keys (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
key_hash VARCHAR(64) NOT NULL UNIQUE,
|
|
key_prefix VARCHAR(8) NOT NULL,
|
|
scopes TEXT,
|
|
is_active BOOLEAN DEFAULT 1,
|
|
expires_at DATETIME,
|
|
last_used_at DATETIME,
|
|
last_used_ip VARCHAR(45),
|
|
usage_count INTEGER DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
)
|
|
""")
|
|
cursor.execute("CREATE INDEX ix_api_keys_key_hash ON api_keys(key_hash)")
|
|
|
|
# Notifications
|
|
if "notifications" not in existing_tables:
|
|
print("Creating notifications table...")
|
|
cursor.execute("""
|
|
CREATE TABLE notifications (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
title VARCHAR(200) NOT NULL,
|
|
message TEXT NOT NULL,
|
|
type VARCHAR(20) DEFAULT 'info',
|
|
link VARCHAR(500),
|
|
extra_data TEXT,
|
|
is_read BOOLEAN DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
read_at DATETIME,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
)
|
|
""")
|
|
cursor.execute("CREATE INDEX ix_notifications_user_id ON notifications(user_id)")
|
|
cursor.execute("CREATE INDEX ix_notifications_is_read ON notifications(is_read)")
|
|
|
|
# User Sessions
|
|
if "user_sessions" not in existing_tables:
|
|
print("Creating user_sessions table...")
|
|
cursor.execute("""
|
|
CREATE TABLE user_sessions (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
token_hash VARCHAR(64) NOT NULL UNIQUE,
|
|
device_name VARCHAR(200),
|
|
device_type VARCHAR(50),
|
|
browser VARCHAR(100),
|
|
os VARCHAR(100),
|
|
user_agent VARCHAR(500),
|
|
ip_address VARCHAR(45),
|
|
location VARCHAR(200),
|
|
is_active BOOLEAN DEFAULT 1,
|
|
is_current BOOLEAN DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
last_active_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
expires_at DATETIME,
|
|
revoked_at DATETIME,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
)
|
|
""")
|
|
cursor.execute("CREATE INDEX ix_user_sessions_token_hash ON user_sessions(token_hash)")
|
|
cursor.execute("CREATE INDEX ix_user_sessions_user_id ON user_sessions(user_id)")
|
|
|
|
# Webhooks
|
|
if "webhooks" not in existing_tables:
|
|
print("Creating webhooks table...")
|
|
cursor.execute("""
|
|
CREATE TABLE webhooks (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
url VARCHAR(500) NOT NULL,
|
|
secret VARCHAR(64),
|
|
events TEXT DEFAULT '["*"]',
|
|
is_active BOOLEAN DEFAULT 1,
|
|
retry_count INTEGER DEFAULT 3,
|
|
timeout_seconds INTEGER DEFAULT 30,
|
|
created_by VARCHAR(36),
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
last_triggered_at DATETIME,
|
|
success_count INTEGER DEFAULT 0,
|
|
failure_count INTEGER DEFAULT 0,
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
|
|
)
|
|
""")
|
|
|
|
# Webhook Deliveries
|
|
if "webhook_deliveries" not in existing_tables:
|
|
print("Creating webhook_deliveries table...")
|
|
cursor.execute("""
|
|
CREATE TABLE webhook_deliveries (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
webhook_id VARCHAR(36) NOT NULL,
|
|
event_type VARCHAR(50) NOT NULL,
|
|
payload TEXT NOT NULL,
|
|
status VARCHAR(20) DEFAULT 'pending',
|
|
status_code INTEGER,
|
|
response_body TEXT,
|
|
error_message TEXT,
|
|
attempt_count INTEGER DEFAULT 0,
|
|
next_retry_at DATETIME,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
delivered_at DATETIME,
|
|
FOREIGN KEY (webhook_id) REFERENCES webhooks(id) ON DELETE CASCADE
|
|
)
|
|
""")
|
|
|
|
# Stored Files
|
|
if "stored_files" not in existing_tables:
|
|
print("Creating stored_files table...")
|
|
cursor.execute("""
|
|
CREATE TABLE stored_files (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
original_filename VARCHAR(255) NOT NULL,
|
|
content_type VARCHAR(100),
|
|
size_bytes BIGINT NOT NULL,
|
|
storage_path VARCHAR(500) NOT NULL,
|
|
storage_type VARCHAR(20) DEFAULT 'local',
|
|
description TEXT,
|
|
tags TEXT,
|
|
is_public BOOLEAN DEFAULT 0,
|
|
uploaded_by VARCHAR(36),
|
|
file_hash VARCHAR(64),
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
is_deleted BOOLEAN DEFAULT 0,
|
|
deleted_at DATETIME,
|
|
FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
|
|
)
|
|
""")
|
|
cursor.execute("CREATE INDEX ix_stored_files_file_hash ON stored_files(file_hash)")
|
|
|
|
conn.commit()
|
|
conn.close()
|
|
print("Migration completed successfully!")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
# Default path for Docker container
|
|
default_path = "/config/config.db"
|
|
|
|
if len(sys.argv) > 1:
|
|
db_path = sys.argv[1]
|
|
else:
|
|
# Check common locations
|
|
paths_to_try = [
|
|
Path(default_path),
|
|
Path("./config.db"),
|
|
Path("./data/config.db"),
|
|
Path("../config/config.db"),
|
|
]
|
|
|
|
db_path = None
|
|
for p in paths_to_try:
|
|
if p.exists():
|
|
db_path = str(p)
|
|
break
|
|
|
|
if not db_path:
|
|
print(f"Database not found. Please provide path as argument.")
|
|
print(f"Usage: python migrate_db.py /path/to/config.db")
|
|
sys.exit(1)
|
|
|
|
if not Path(db_path).exists():
|
|
print(f"Error: Database file not found: {db_path}")
|
|
sys.exit(1)
|
|
|
|
migrate(db_path)
|