Auxiliary Tables¶

GPUStats, AuditLog, Jobs, Baselines, and other supporting tables.

Overview¶

Auxiliary tables support system monitoring, auditing, alerting, and machine learning baseline tracking.

Schema truth for every table below lives in its SQLAlchemy model under backend/models/ (alembic migrations were removed in PR #4465; see Migrations). Each Source: line cites the model definition.

graph TB
    subgraph Monitoring
        GPU[gpu_stats]
        LOGS[logs]
    end

    subgraph Auditing
        AUDIT[audit_logs]
        FEEDBACK[event_feedback]
        EAUDIT[event_audits]
    end

    subgraph Alerting
        RULES[alert_rules]
        ALERTS[alerts]
    end

    subgraph Baselines
        ACT[activity_baselines]
        CLASS[class_baselines]
    end

    subgraph Jobs
        JOB[jobs]
        ATTEMPTS[job_attempts]
        JLOGS[job_logs]
        TRANS[job_transitions]
    end

GPU Stats¶

Source: backend/models/gpu_stats.py

Table Name: gpu_stats

Schema Definition¶

Indexes¶

Source: backend/models/gpu_stats.py:86-90

Audit Logs¶

Source: backend/models/audit.py

Table Name: audit_logs

Schema Definition¶

Constraints¶

CHECK (status IN ('success', 'failure'))

Source: backend/models/audit.py:129-132

Indexes¶

Source: backend/models/audit.py:115-128

Application Logs¶

Source: backend/models/log.py

Table Name: logs

Schema Definition¶

Constraints¶

CHECK (level IN ('DEBUG', 'INFO', 'WARNING', 'ERROR', 'CRITICAL'))
CHECK (source IN ('backend', 'frontend'))

Source: backend/models/log.py:68-76

Indexes¶

Source: backend/models/log.py:54-67

Alert Rules¶

Source: backend/models/alert.py

Table Name: alert_rules

Schema Definition¶

Constraints¶

CHECK (risk_threshold IS NULL OR (risk_threshold >= 0 AND risk_threshold <= 100))
CHECK (min_confidence IS NULL OR (min_confidence >= 0.0 AND min_confidence <= 1.0))
CHECK (cooldown_seconds >= 0)

Source: backend/models/alert.py:366-377

Indexes¶

Source: backend/models/alert.py:361-364

Alerts¶

Source: backend/models/alert.py

Table Name: alerts

Schema Definition¶

Enum Values¶

-- Severity
CREATE TYPE alert_severity AS ENUM ('low', 'medium', 'high', 'critical');

-- Status
CREATE TYPE alert_status AS ENUM ('pending', 'delivered', 'acknowledged', 'dismissed');

Source: backend/models/alert.py:50-66, 102-107

Indexes¶

Source: backend/models/alert.py:141-148

Activity Baselines¶

Source: backend/models/baseline.py

Table Name: activity_baselines

Schema Definition¶

Constraints¶

UNIQUE (camera_id, hour, day_of_week)
CHECK (hour >= 0 AND hour <= 23)
CHECK (day_of_week >= 0 AND day_of_week <= 6)

Source: backend/models/baseline.py:70-77

Indexes¶

Source: backend/models/baseline.py:72-73

Class Baselines¶

Source: backend/models/baseline.py

Table Name: class_baselines

Schema Definition¶

Constraints¶

UNIQUE (camera_id, detection_class, hour)
CHECK (hour >= 0 AND hour <= 23)

Source: backend/models/baseline.py:123-129

Indexes¶

Source: backend/models/baseline.py:125-127

Camera Zones¶

Source: backend/models/camera_zone.py

Table Name: camera_zones

Schema Definition¶

Enum Values¶

CREATE TYPE camera_zone_type_enum AS ENUM ('entry_point', 'driveway', 'sidewalk', 'yard', 'other');
CREATE TYPE camera_zone_shape_enum AS ENUM ('rectangle', 'polygon');

Source: backend/models/camera_zone.py:78-94

Constraints¶

CHECK (priority >= 0)
CHECK (color ~ '^#[0-9A-Fa-f]{6}$')

Source: backend/models/camera_zone.py:136-137

Indexes¶

Source: backend/models/camera_zone.py:132-134

Jobs¶

Source: backend/models/job.py

Table Name: jobs

Schema Definition¶

Constraints¶

CHECK (status IN ('queued', 'running', 'completed', 'failed', 'cancelled'))
CHECK (progress_percent >= 0 AND progress_percent <= 100)
CHECK (priority >= 0 AND priority <= 4)
CHECK (attempt_number >= 1)
CHECK (max_attempts >= 1)
CHECK (completed_at IS NULL OR completed_at >= created_at)
CHECK (started_at IS NULL OR started_at >= created_at)

Source: backend/models/job.py:104-148

Indexes¶

Source: backend/models/job.py:105-118

Job Attempts¶

Source: backend/models/job_attempt.py

Table Name: job_attempts

Tracks individual execution attempts for each job. A job can have multiple attempts due to retries.

Schema Definition¶

Constraints¶

CHECK (status IN ('started', 'succeeded', 'failed', 'cancelled'))
CHECK (attempt_number >= 1)
CHECK (ended_at IS NULL OR ended_at >= started_at)

Indexes¶

Job Logs¶

Source: backend/models/job_log.py

Table Name: job_logs

Stores log entries generated during job execution for debugging and audit trails.

Schema Definition¶

Constraints¶

CHECK (level IN ('debug', 'info', 'warning', 'error'))
CHECK (attempt_number >= 1)

Indexes¶

Job Transitions¶

Source: backend/models/job_transition.py

Table Name: job_transitions

Records all state transitions for jobs, enabling audit trails and debugging of job lifecycle issues.

Schema Definition¶

Trigger Types¶

Indexes¶

Export Jobs¶

Source: backend/models/export_job.py

Table Name: export_jobs

Tracks background export jobs including events, alerts, and full backup exports with compliance features.

Schema Definition¶

Constraints¶

CHECK (progress_percent >= 0 AND progress_percent <= 100)
CHECK (processed_items >= 0)
CHECK (total_items IS NULL OR total_items >= 0)
CHECK (output_size_bytes IS NULL OR output_size_bytes >= 0)
CHECK (download_count >= 0)
CHECK (retention_days IS NULL OR retention_days > 0)

Indexes¶

Backup Jobs¶

Source: backend/models/backup_job.py

Table Name: backup_jobs

Tracks full system backup job progress and results.

Schema Definition¶

Constraints¶

CHECK (progress_percent >= 0 AND progress_percent <= 100)

Indexes¶

Restore Jobs¶

Source: backend/models/backup_job.py

Table Name: restore_jobs

Tracks system restore job progress from backup files.

Schema Definition¶

Status Flow¶

pending -> validating -> restoring -> completed
                     \            \-> failed
                      \-> failed

Constraints¶

CHECK (progress_percent >= 0 AND progress_percent <= 100)

Indexes¶

Event Feedback¶

Source: backend/models/event_feedback.py

Table Name: event_feedback

Schema Definition¶

Constraints¶

UNIQUE (event_id)  -- One feedback per event
CHECK (feedback_type IN ('accurate', 'correct', 'false_positive', 'missed_threat', 'severity_wrong'))
CHECK (expected_severity IS NULL OR expected_severity IN ('low', 'medium', 'high', 'critical'))

Source: backend/models/event_feedback.py:78, 113-121

Indexes¶

Source: backend/models/event_feedback.py:105-109

Event Audits¶

Source: backend/models/event_audit.py

Table Name: event_audits

Schema Definition¶

Constraints¶

UNIQUE (event_id)  -- One audit per event
CHECK (enrichment_utilization >= 0.0 AND enrichment_utilization <= 1.0)
CHECK (context_usage_score IS NULL OR (context_usage_score >= 1.0 AND context_usage_score <= 5.0))
CHECK (reasoning_coherence_score IS NULL OR (reasoning_coherence_score >= 1.0 AND reasoning_coherence_score <= 5.0))
CHECK (risk_justification_score IS NULL OR (risk_justification_score >= 1.0 AND risk_justification_score <= 5.0))
CHECK (consistency_score IS NULL OR (consistency_score >= 1.0 AND consistency_score <= 5.0))
CHECK (overall_quality_score IS NULL OR (overall_quality_score >= 1.0 AND overall_quality_score <= 5.0))

Source: backend/models/event_audit.py:84-107

Indexes¶

Source: backend/models/event_audit.py:81-83

Enrichment Result Tables¶

Pose Results¶

Table Name: pose_results

Source: backend/models/enrichment.py:37-88

Threat Detections¶

Table Name: threat_detections

Source: backend/models/enrichment.py:90-149

Demographics Results¶

Table Name: demographics_results

Source: backend/models/enrichment.py:151-208

ReID Embeddings¶

Table Name: reid_embeddings

Source: backend/models/enrichment.py:210-253

Action Results¶

Table Name: action_results

Source: backend/models/enrichment.py:255-301