177 Zeilen
6.8 KiB
PL/PgSQL
177 Zeilen
6.8 KiB
PL/PgSQL
-- License Server Database Schema
|
|
-- Following best practices: snake_case for DB fields, clear naming conventions
|
|
|
|
-- Enable UUID extension
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- License tokens for offline validation
|
|
CREATE TABLE IF NOT EXISTS license_tokens (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
license_id UUID REFERENCES licenses(id) ON DELETE CASCADE,
|
|
token VARCHAR(512) NOT NULL UNIQUE,
|
|
hardware_id VARCHAR(255) NOT NULL,
|
|
valid_until TIMESTAMP NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
last_validated TIMESTAMP,
|
|
validation_count INTEGER DEFAULT 0
|
|
);
|
|
|
|
CREATE INDEX idx_token ON license_tokens(token);
|
|
CREATE INDEX idx_hardware ON license_tokens(hardware_id);
|
|
CREATE INDEX idx_valid_until ON license_tokens(valid_until);
|
|
|
|
-- Heartbeat tracking with partitioning support
|
|
CREATE TABLE IF NOT EXISTS license_heartbeats (
|
|
id BIGSERIAL,
|
|
license_id UUID REFERENCES licenses(id) ON DELETE CASCADE,
|
|
hardware_id VARCHAR(255) NOT NULL,
|
|
ip_address INET,
|
|
user_agent VARCHAR(500),
|
|
app_version VARCHAR(50),
|
|
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
session_data JSONB,
|
|
PRIMARY KEY (id, timestamp)
|
|
) PARTITION BY RANGE (timestamp);
|
|
|
|
-- Create partitions for the current and next month
|
|
CREATE TABLE license_heartbeats_2025_01 PARTITION OF license_heartbeats
|
|
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
|
|
|
|
CREATE TABLE license_heartbeats_2025_02 PARTITION OF license_heartbeats
|
|
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
|
|
|
|
CREATE INDEX idx_heartbeat_license_time ON license_heartbeats(license_id, timestamp DESC);
|
|
CREATE INDEX idx_heartbeat_hardware_time ON license_heartbeats(hardware_id, timestamp DESC);
|
|
|
|
-- Activation events tracking
|
|
CREATE TABLE IF NOT EXISTS activation_events (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
license_id UUID REFERENCES licenses(id) ON DELETE CASCADE,
|
|
event_type VARCHAR(50) NOT NULL CHECK (event_type IN ('activation', 'deactivation', 'reactivation', 'transfer')),
|
|
hardware_id VARCHAR(255),
|
|
previous_hardware_id VARCHAR(255),
|
|
ip_address INET,
|
|
user_agent VARCHAR(500),
|
|
success BOOLEAN DEFAULT true,
|
|
error_message TEXT,
|
|
metadata JSONB,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_license_events ON activation_events(license_id, created_at DESC);
|
|
CREATE INDEX idx_event_type ON activation_events(event_type, created_at DESC);
|
|
|
|
-- API rate limiting
|
|
CREATE TABLE IF NOT EXISTS api_rate_limits (
|
|
id SERIAL PRIMARY KEY,
|
|
api_key VARCHAR(255) NOT NULL UNIQUE,
|
|
requests_per_minute INTEGER DEFAULT 60,
|
|
requests_per_hour INTEGER DEFAULT 1000,
|
|
requests_per_day INTEGER DEFAULT 10000,
|
|
burst_size INTEGER DEFAULT 100,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Anomaly detection
|
|
CREATE TABLE IF NOT EXISTS anomaly_detections (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
license_id UUID REFERENCES licenses(id),
|
|
anomaly_type VARCHAR(100) NOT NULL CHECK (anomaly_type IN ('multiple_ips', 'rapid_hardware_change', 'suspicious_pattern', 'concurrent_use', 'geo_anomaly')),
|
|
severity VARCHAR(20) NOT NULL CHECK (severity IN ('low', 'medium', 'high', 'critical')),
|
|
details JSONB NOT NULL,
|
|
detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
resolved BOOLEAN DEFAULT false,
|
|
resolved_at TIMESTAMP,
|
|
resolved_by VARCHAR(255),
|
|
action_taken TEXT
|
|
);
|
|
|
|
CREATE INDEX idx_unresolved ON anomaly_detections(resolved, severity, detected_at DESC);
|
|
CREATE INDEX idx_license_anomalies ON anomaly_detections(license_id, detected_at DESC);
|
|
|
|
-- API clients for authentication
|
|
CREATE TABLE IF NOT EXISTS api_clients (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
client_name VARCHAR(255) NOT NULL,
|
|
api_key VARCHAR(255) NOT NULL UNIQUE,
|
|
secret_key VARCHAR(255) NOT NULL,
|
|
is_active BOOLEAN DEFAULT true,
|
|
allowed_endpoints TEXT[],
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Feature flags for gradual rollout
|
|
CREATE TABLE IF NOT EXISTS feature_flags (
|
|
id SERIAL PRIMARY KEY,
|
|
feature_name VARCHAR(100) NOT NULL UNIQUE,
|
|
is_enabled BOOLEAN DEFAULT false,
|
|
rollout_percentage INTEGER DEFAULT 0 CHECK (rollout_percentage >= 0 AND rollout_percentage <= 100),
|
|
whitelist_license_ids UUID[],
|
|
blacklist_license_ids UUID[],
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Insert default feature flags
|
|
INSERT INTO feature_flags (feature_name, is_enabled, rollout_percentage) VALUES
|
|
('anomaly_detection', true, 100),
|
|
('offline_tokens', true, 100),
|
|
('advanced_analytics', false, 0),
|
|
('geo_restriction', false, 0)
|
|
ON CONFLICT (feature_name) DO NOTHING;
|
|
|
|
-- Session management for concurrent use tracking
|
|
CREATE TABLE IF NOT EXISTS active_sessions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
license_id UUID REFERENCES licenses(id) ON DELETE CASCADE,
|
|
hardware_id VARCHAR(255) NOT NULL,
|
|
session_token VARCHAR(512) NOT NULL UNIQUE,
|
|
ip_address INET,
|
|
started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
expires_at TIMESTAMP NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_session_license ON active_sessions(license_id);
|
|
CREATE INDEX idx_session_expires ON active_sessions(expires_at);
|
|
|
|
-- Update trigger for updated_at columns
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
CREATE TRIGGER update_api_rate_limits_updated_at BEFORE UPDATE ON api_rate_limits
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_api_clients_updated_at BEFORE UPDATE ON api_clients
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_feature_flags_updated_at BEFORE UPDATE ON feature_flags
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Function to automatically create monthly partitions for heartbeats
|
|
CREATE OR REPLACE FUNCTION create_monthly_partition()
|
|
RETURNS void AS $$
|
|
DECLARE
|
|
start_date date;
|
|
end_date date;
|
|
partition_name text;
|
|
BEGIN
|
|
start_date := date_trunc('month', CURRENT_DATE + interval '1 month');
|
|
end_date := start_date + interval '1 month';
|
|
partition_name := 'license_heartbeats_' || to_char(start_date, 'YYYY_MM');
|
|
|
|
EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF license_heartbeats FOR VALUES FROM (%L) TO (%L)',
|
|
partition_name, start_date, end_date);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Create a scheduled job to create partitions (requires pg_cron extension)
|
|
-- This is a placeholder - actual scheduling depends on your PostgreSQL setup
|
|
-- SELECT cron.schedule('create-partitions', '0 0 1 * *', 'SELECT create_monthly_partition();'); |