c67d7ff139
Developer tab now includes: - Database Backup & Restore section with download/upload JSON backup - WebDAV Backup section to upload backups to Nextcloud/WebDAV servers with configurable server URL, username, and password Features: - Full JSON export of entries, settings, logos, users, license - Import with data replacement and transaction safety - WebDAV upload via cURL with PUT request - Automatic timestamped filenames (ipmanager-YYYY-MM-DD_HH-mm-ss.json) - Settings persistence for WebDAV credentials Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
237 lines
11 KiB
SQL
237 lines
11 KiB
SQL
-- Geofeed Manager Database Schema
|
|
-- For MariaDB/MySQL
|
|
|
|
CREATE DATABASE IF NOT EXISTS geofeed_manager;
|
|
USE geofeed_manager;
|
|
|
|
-- Main geofeed entries table with IP enrichment data
|
|
CREATE TABLE IF NOT EXISTS geofeed_entries (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
ip_prefix VARCHAR(50) NOT NULL,
|
|
country_code CHAR(2) DEFAULT NULL,
|
|
region_code VARCHAR(10) DEFAULT NULL,
|
|
city VARCHAR(255) DEFAULT NULL,
|
|
postal_code VARCHAR(50) DEFAULT NULL,
|
|
client_short_name VARCHAR(100) DEFAULT NULL,
|
|
notes TEXT DEFAULT NULL,
|
|
sort_order INT DEFAULT 0,
|
|
|
|
-- IP Registry enrichment data
|
|
ipr_enriched_at TIMESTAMP NULL DEFAULT NULL,
|
|
ipr_hostname VARCHAR(255) DEFAULT NULL,
|
|
ipr_isp VARCHAR(255) DEFAULT NULL,
|
|
ipr_org VARCHAR(255) DEFAULT NULL,
|
|
ipr_asn INT DEFAULT NULL,
|
|
ipr_asn_name VARCHAR(255) DEFAULT NULL,
|
|
ipr_connection_type VARCHAR(50) DEFAULT NULL,
|
|
ipr_country_name VARCHAR(100) DEFAULT NULL,
|
|
ipr_region_name VARCHAR(100) DEFAULT NULL,
|
|
ipr_timezone VARCHAR(100) DEFAULT NULL,
|
|
ipr_latitude DECIMAL(10, 7) DEFAULT NULL,
|
|
ipr_longitude DECIMAL(10, 7) DEFAULT NULL,
|
|
|
|
-- Security flags from IP Registry
|
|
flag_abuser TINYINT(1) DEFAULT 0,
|
|
flag_attacker TINYINT(1) DEFAULT 0,
|
|
flag_bogon TINYINT(1) DEFAULT 0,
|
|
flag_cloud_provider TINYINT(1) DEFAULT 0,
|
|
flag_proxy TINYINT(1) DEFAULT 0,
|
|
flag_relay TINYINT(1) DEFAULT 0,
|
|
flag_tor TINYINT(1) DEFAULT 0,
|
|
flag_tor_exit TINYINT(1) DEFAULT 0,
|
|
flag_vpn TINYINT(1) DEFAULT 0,
|
|
flag_anonymous TINYINT(1) DEFAULT 0,
|
|
flag_threat TINYINT(1) DEFAULT 0,
|
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY unique_prefix (ip_prefix),
|
|
INDEX idx_country (country_code),
|
|
INDEX idx_region (region_code),
|
|
INDEX idx_city (city),
|
|
INDEX idx_client (client_short_name),
|
|
INDEX idx_sort_order (sort_order),
|
|
INDEX idx_isp (ipr_isp),
|
|
INDEX idx_asn (ipr_asn)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Audit log for tracking changes
|
|
CREATE TABLE IF NOT EXISTS geofeed_audit_log (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
entry_id INT,
|
|
action ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
|
|
old_values JSON DEFAULT NULL,
|
|
new_values JSON DEFAULT NULL,
|
|
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
changed_by VARCHAR(255) DEFAULT NULL,
|
|
INDEX idx_entry (entry_id),
|
|
INDEX idx_action (action),
|
|
INDEX idx_changed_at (changed_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Settings table for app configuration
|
|
CREATE TABLE IF NOT EXISTS geofeed_settings (
|
|
setting_key VARCHAR(100) PRIMARY KEY,
|
|
setting_value TEXT,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Client logos table for storing logo URLs per shortname
|
|
CREATE TABLE IF NOT EXISTS client_logos (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
short_name VARCHAR(100) NOT NULL,
|
|
logo_url VARCHAR(500) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY unique_short_name (short_name),
|
|
INDEX idx_short_name (short_name)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Webhook queue table for debounced notifications
|
|
CREATE TABLE IF NOT EXISTS webhook_queue (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
webhook_type VARCHAR(50) NOT NULL DEFAULT 'geofeed_update',
|
|
trigger_reason VARCHAR(255) DEFAULT NULL,
|
|
entries_affected INT DEFAULT 0,
|
|
queued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
scheduled_for TIMESTAMP NULL,
|
|
processed_at TIMESTAMP NULL,
|
|
status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
|
|
response_code INT DEFAULT NULL,
|
|
response_body TEXT DEFAULT NULL,
|
|
INDEX idx_status (status),
|
|
INDEX idx_scheduled (scheduled_for)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- User sessions table for authentication
|
|
CREATE TABLE IF NOT EXISTS user_sessions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
session_token VARCHAR(64) NOT NULL,
|
|
username VARCHAR(100) NOT NULL,
|
|
ip_address VARCHAR(45) DEFAULT NULL,
|
|
user_agent TEXT DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
expires_at TIMESTAMP NOT NULL,
|
|
UNIQUE KEY unique_token (session_token),
|
|
INDEX idx_expires (expires_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Admin users table for RBAC
|
|
CREATE TABLE IF NOT EXISTS admin_users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
email VARCHAR(255) NOT NULL,
|
|
role ENUM('staff', 'admin') NOT NULL DEFAULT 'staff',
|
|
display_name VARCHAR(255) DEFAULT NULL,
|
|
active TINYINT(1) DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
created_by VARCHAR(255) DEFAULT NULL,
|
|
UNIQUE KEY unique_email (email),
|
|
INDEX idx_role (role),
|
|
INDEX idx_active (active)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- PTR records cache table for Route53 A records
|
|
CREATE TABLE IF NOT EXISTS ptr_records_cache (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
zone_id VARCHAR(50) NOT NULL,
|
|
zone_name VARCHAR(255) DEFAULT NULL,
|
|
hostname VARCHAR(255) NOT NULL,
|
|
ip_address VARCHAR(45) NOT NULL,
|
|
ttl INT DEFAULT NULL,
|
|
ptr_record VARCHAR(255) DEFAULT NULL,
|
|
ptr_status ENUM('unknown', 'match', 'mismatch', 'missing', 'error') DEFAULT 'unknown',
|
|
ptr_checked_at TIMESTAMP NULL DEFAULT NULL,
|
|
aws_synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY unique_zone_hostname (zone_id, hostname),
|
|
INDEX idx_zone (zone_id),
|
|
INDEX idx_ip (ip_address),
|
|
INDEX idx_status (ptr_status),
|
|
INDEX idx_synced (aws_synced_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Insert default settings
|
|
INSERT INTO geofeed_settings (setting_key, setting_value) VALUES
|
|
('bunny_cdn_storage_zone', ''),
|
|
('bunny_cdn_api_key', ''),
|
|
('bunny_cdn_file_path', '/geofeed.csv'),
|
|
('last_export_at', NULL),
|
|
('n8n_webhook_url', ''),
|
|
('n8n_webhook_enabled', '0'),
|
|
('n8n_webhook_delay_minutes', '3'),
|
|
('ipregistry_api_key', ''),
|
|
('ipregistry_enabled', '0'),
|
|
('aws_access_key_id', ''),
|
|
('aws_secret_access_key', ''),
|
|
('aws_region', 'us-east-1'),
|
|
('aws_hosted_zones', ''),
|
|
('whitelabel_app_name', 'ISP IP Manager'),
|
|
('whitelabel_company_name', ''),
|
|
('whitelabel_icon_url', ''),
|
|
('whitelabel_favicon_url', ''),
|
|
('whitelabel_default_import_url', ''),
|
|
('webdav_server_url', ''),
|
|
('webdav_username', ''),
|
|
('webdav_password', '')
|
|
ON DUPLICATE KEY UPDATE setting_key = setting_key;
|
|
|
|
-- ============================================
|
|
-- MIGRATION: Add columns for existing databases
|
|
-- These statements safely add columns if they don't exist
|
|
-- ============================================
|
|
|
|
-- Add sort_order column
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS sort_order INT DEFAULT 0 AFTER notes;
|
|
|
|
-- Add IP Registry enrichment columns
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS ipr_enriched_at TIMESTAMP NULL DEFAULT NULL AFTER sort_order;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS ipr_hostname VARCHAR(255) DEFAULT NULL AFTER ipr_enriched_at;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS ipr_isp VARCHAR(255) DEFAULT NULL AFTER ipr_hostname;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS ipr_org VARCHAR(255) DEFAULT NULL AFTER ipr_isp;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS ipr_asn INT DEFAULT NULL AFTER ipr_org;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS ipr_asn_name VARCHAR(255) DEFAULT NULL AFTER ipr_asn;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS ipr_connection_type VARCHAR(50) DEFAULT NULL AFTER ipr_asn_name;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS ipr_country_name VARCHAR(100) DEFAULT NULL AFTER ipr_connection_type;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS ipr_region_name VARCHAR(100) DEFAULT NULL AFTER ipr_country_name;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS ipr_timezone VARCHAR(100) DEFAULT NULL AFTER ipr_region_name;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS ipr_latitude DECIMAL(10, 7) DEFAULT NULL AFTER ipr_timezone;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS ipr_longitude DECIMAL(10, 7) DEFAULT NULL AFTER ipr_latitude;
|
|
|
|
-- Add security flag columns
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS flag_abuser TINYINT(1) DEFAULT 0 AFTER ipr_longitude;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS flag_attacker TINYINT(1) DEFAULT 0 AFTER flag_abuser;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS flag_bogon TINYINT(1) DEFAULT 0 AFTER flag_attacker;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS flag_cloud_provider TINYINT(1) DEFAULT 0 AFTER flag_bogon;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS flag_proxy TINYINT(1) DEFAULT 0 AFTER flag_cloud_provider;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS flag_relay TINYINT(1) DEFAULT 0 AFTER flag_proxy;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS flag_tor TINYINT(1) DEFAULT 0 AFTER flag_relay;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS flag_tor_exit TINYINT(1) DEFAULT 0 AFTER flag_tor;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS flag_vpn TINYINT(1) DEFAULT 0 AFTER flag_tor_exit;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS flag_anonymous TINYINT(1) DEFAULT 0 AFTER flag_vpn;
|
|
ALTER TABLE geofeed_entries ADD COLUMN IF NOT EXISTS flag_threat TINYINT(1) DEFAULT 0 AFTER flag_anonymous;
|
|
|
|
-- Add indexes if they don't exist (MariaDB 10.5+ supports IF NOT EXISTS for indexes)
|
|
-- For older versions, these will show warnings but won't fail
|
|
ALTER TABLE geofeed_entries ADD INDEX IF NOT EXISTS idx_sort_order (sort_order);
|
|
ALTER TABLE geofeed_entries ADD INDEX IF NOT EXISTS idx_isp (ipr_isp);
|
|
ALTER TABLE geofeed_entries ADD INDEX IF NOT EXISTS idx_asn (ipr_asn);
|
|
|
|
-- License information table
|
|
CREATE TABLE IF NOT EXISTS license_info (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
license_key VARCHAR(64) NOT NULL,
|
|
licensee_name VARCHAR(255) NOT NULL,
|
|
licensee_email VARCHAR(255) NOT NULL,
|
|
license_type ENUM('trial', 'basic', 'professional', 'enterprise') DEFAULT 'trial',
|
|
max_entries INT DEFAULT 100,
|
|
max_users INT DEFAULT 3,
|
|
features JSON DEFAULT NULL,
|
|
issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
expires_at TIMESTAMP NULL,
|
|
last_validated_at TIMESTAMP NULL,
|
|
is_active TINYINT(1) DEFAULT 1,
|
|
UNIQUE KEY unique_license (license_key)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|