Auxiliary Tables¶

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

Overview¶

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

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/alembic/versions/e36700c35af6_initial_schema.py:251-253

Audit Logs¶

Source: backend/models/audit.py

Table Name: audit_logs

Schema Definition¶

Constraints¶

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

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:168

Indexes¶

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:170-178

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/alembic/versions/e36700c35af6_initial_schema.py:201-203

Indexes¶

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:205-211

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/alembic/versions/e36700c35af6_initial_schema.py:139-146

Indexes¶

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:148-150

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/alembic/versions/e36700c35af6_initial_schema.py:46-51

Indexes¶

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:1111-1121

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/alembic/versions/e36700c35af6_initial_schema.py:900-904

Indexes¶

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:906-909

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/alembic/versions/e36700c35af6_initial_schema.py:928-929

Indexes¶

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:931-935

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/alembic/versions/f1231ed7e32d_rename_zones_to_camera_zones.py:47-48

Constraints¶

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

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:758-759

Indexes¶

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:761-763

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/alembic/versions/e36700c35af6_initial_schema.py:281-296

Indexes¶

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:298-305

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/alembic/versions/e36700c35af6_initial_schema.py:1217-1224

Indexes¶

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:1226-1228

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/alembic/versions/e36700c35af6_initial_schema.py:1171-1191

Indexes¶

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:1196-1198

Enrichment Result Tables¶

Pose Results¶

Table Name: pose_results

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:1322-1366

Threat Detections¶

Table Name: threat_detections

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:1369-1426

Demographics Results¶

Table Name: demographics_results

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:1429-1482

ReID Embeddings¶

Table Name: reid_embeddings

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:1485-1518

Action Results¶

Table Name: action_results

Source: backend/alembic/versions/e36700c35af6_initial_schema.py:1521-1561