Data Model Reference¶

Complete documentation of all database models, relationships, and data patterns for the Home Security Intelligence system.

Time to read: ~15 min Prerequisites: Codebase Tour

Table of Contents¶

1. Model Inventory
2. Entity Relationship Diagram
3. Core Models
4. Camera
5. Detection
6. Event
7. Alert System Models
8. Alert
9. AlertRule
10. Zone and Baseline Models
11. Zone
12. ActivityBaseline
13. ClassBaseline
14. Supporting Models
15. GPUStats
16. Log
17. AuditLog
18. APIKey
19. Enumerations
20. Data Flow
21. Common Query Patterns

Model Inventory¶

The system uses 12 SQLAlchemy models across 12 PostgreSQL tables:

All models are exported from backend/models/__init__.py.

Entity Relationship Diagram¶

Database cascade delete diagram showing foreign key relationships and deletion behavior.

erDiagram
    cameras ||--o{ detections : "has many"
    cameras ||--o{ events : "has many"
    cameras ||--o{ zones : "has many"
    cameras ||--o{ activity_baselines : "has many"
    cameras ||--o{ class_baselines : "has many"
    events ||--o{ alerts : "triggers"
    alert_rules ||--o{ alerts : "generates"

    cameras {
        string id PK "UUID, normalized folder name"
        string name "Display name"
        string folder_path "FTP upload path"
        string status "online|offline|error"
        datetime created_at "Creation timestamp"
        datetime last_seen_at "Last activity (nullable)"
    }

    detections {
        int id PK "Auto-increment"
        string camera_id FK "References cameras.id"
        string file_path "Source image/video path"
        string file_type "MIME type"
        datetime detected_at "Detection timestamp"
        string object_type "person|car|dog|etc"
        float confidence "0.0-1.0"
        int bbox_x "Bounding box X"
        int bbox_y "Bounding box Y"
        int bbox_width "Bounding box width"
        int bbox_height "Bounding box height"
        string thumbnail_path "Cropped thumbnail"
        string media_type "image|video"
        float duration "Video duration (seconds)"
        string video_codec "h264|hevc|etc"
        int video_width "Video resolution width"
        int video_height "Video resolution height"
    }

    events {
        int id PK "Auto-increment"
        string batch_id "Batch grouping UUID"
        string camera_id FK "References cameras.id"
        datetime started_at "Event start time"
        datetime ended_at "Event end time"
        int risk_score "0-100 (LLM assigned)"
        string risk_level "low|medium|high|critical"
        text summary "LLM-generated summary"
        text reasoning "LLM reasoning"
        text detection_ids "JSON array"
        bool reviewed "User reviewed flag"
        text notes "User notes"
        bool is_fast_path "Fast path flag"
        text object_types "Cached object types"
        string clip_path "Generated video clip"
        tsvector search_vector "Full-text search"
    }

    alerts {
        uuid id PK "UUID"
        int event_id FK "References events.id"
        uuid rule_id FK "References alert_rules.id"
        enum severity "low|medium|high|critical"
        enum status "pending|delivered|acknowledged|dismissed"
        datetime created_at "Creation timestamp"
        datetime updated_at "Last update"
        datetime delivered_at "Delivery timestamp"
        json channels "Notification channels"
        string dedup_key "Deduplication key"
        json metadata "Additional context"
    }

    alert_rules {
        uuid id PK "UUID"
        string name "Rule name"
        text description "Rule description"
        bool enabled "Active status"
        enum severity "Alert severity level"
        int risk_threshold "Min risk score"
        json object_types "Object type filter"
        json camera_ids "Camera filter"
        json zone_ids "Zone filter"
        float min_confidence "Min confidence"
        json schedule "Time-based conditions"
        json conditions "Legacy conditions"
        string dedup_key_template "Dedup pattern"
        int cooldown_seconds "Alert cooldown"
        json channels "Notification channels"
        datetime created_at "Creation timestamp"
        datetime updated_at "Last update"
    }

    zones {
        string id PK "Zone identifier"
        string camera_id FK "References cameras.id"
        string name "Zone name"
        enum zone_type "entry_point|driveway|sidewalk|yard|other"
        jsonb coordinates "Polygon points [[x,y],...]"
        enum shape "rectangle|polygon"
        string color "Hex color (#3B82F6)"
        bool enabled "Active status"
        int priority "Display priority"
        datetime created_at "Creation timestamp"
        datetime updated_at "Last update"
    }

    activity_baselines {
        int id PK "Auto-increment"
        string camera_id FK "References cameras.id"
        int hour "0-23"
        int day_of_week "0-6 (Mon-Sun)"
        float avg_count "Activity average"
        int sample_count "Sample count"
        datetime last_updated "Last update"
    }

    class_baselines {
        int id PK "Auto-increment"
        string camera_id FK "References cameras.id"
        string detection_class "person|vehicle|etc"
        int hour "0-23"
        float frequency "Class frequency"
        int sample_count "Sample count"
        datetime last_updated "Last update"
    }

    gpu_stats {
        int id PK "Auto-increment"
        datetime recorded_at "Sample timestamp"
        string gpu_name "GPU model name"
        float gpu_utilization "0-100%"
        int memory_used "MB"
        int memory_total "MB"
        float temperature "Celsius"
        float power_usage "Watts"
        float inference_fps "FPS"
    }

    logs {
        int id PK "Auto-increment"
        datetime timestamp "Log timestamp"
        string level "DEBUG|INFO|WARNING|ERROR|CRITICAL"
        string component "Module name"
        text message "Log message"
        string camera_id "Associated camera"
        int event_id "Associated event"
        string request_id "Request correlation UUID"
        int detection_id "Associated detection"
        int duration_ms "Operation duration"
        jsonb extra "Additional context"
        string source "backend|frontend"
        text user_agent "Browser UA"
    }

    audit_logs {
        int id PK "Auto-increment"
        datetime timestamp "Action timestamp"
        string action "Action type"
        string resource_type "Resource category"
        string resource_id "Resource identifier"
        string actor "Who performed action"
        string ip_address "Client IP"
        text user_agent "Client UA"
        jsonb details "Action details"
        string status "success|failure"
    }

    api_keys {
        int id PK "Auto-increment"
        string key_hash UK "SHA256 hash"
        string name "Key display name"
        datetime created_at "Creation timestamp"
        bool is_active "Active/revoked status"
    }

Core Models¶

Camera¶

Table: cameras Source: backend/models/camera.py Purpose: Tracks registered security cameras and their configuration.

Schema¶

Relationships¶

• One-to-many with detections (cascade delete)
• One-to-many with events (cascade delete)
• One-to-many with zones (cascade delete)
• One-to-many with activity_baselines (cascade delete)
• One-to-many with class_baselines (cascade delete)

Camera ID Convention¶

The camera ID follows a strict normalization contract:

from backend.models.camera import normalize_camera_id

# Examples:
normalize_camera_id("Front Door")  # -> "front_door"
normalize_camera_id("back-yard")   # -> "back_yard"
normalize_camera_id("Garage")      # -> "garage"

This ensures consistent mapping between upload directories and camera records.

Example Data¶

{
  "id": "front_door",
  "name": "Front Door",
  "folder_path": "/export/foscam/Front Door",
  "status": "online",
  "created_at": "2025-12-01T10:00:00Z",
  "last_seen_at": "2025-12-31T14:30:00Z"
}

Common Queries¶

# Get all online cameras
cameras = await session.execute(
    select(Camera).where(Camera.status == "online")
)

# Get camera by folder path
camera = await session.execute(
    select(Camera).where(Camera.folder_path == "/export/foscam/Front Door")
)

# Update last_seen_at
camera.last_seen_at = datetime.now(UTC)
await session.commit()

Detection¶

Table: detections Source: backend/models/detection.py Purpose: Stores individual object detection results from YOLO26.

Schema¶

Indexes¶

Relationships¶

• Many-to-one with cameras

Example Data¶

{
  "id": 1234,
  "camera_id": "front_door",
  "file_path": "/export/foscam/Front Door/FI9805W_20251231143000.jpg",
  "file_type": "image/jpeg",
  "detected_at": "2025-12-31T14:30:05Z",
  "object_type": "person",
  "confidence": 0.92,
  "bbox_x": 150,
  "bbox_y": 200,
  "bbox_width": 80,
  "bbox_height": 180,
  "thumbnail_path": "/var/lib/security/thumbnails/1234.jpg",
  "media_type": "image"
}

Common Queries¶

# Get recent detections for a camera
detections = await session.execute(
    select(Detection)
    .where(Detection.camera_id == "front_door")
    .where(Detection.detected_at >= datetime.now(UTC) - timedelta(hours=24))
    .order_by(Detection.detected_at.desc())
)

# Get high-confidence person detections
persons = await session.execute(
    select(Detection)
    .where(Detection.object_type == "person")
    .where(Detection.confidence >= 0.8)
)

# Count detections by object type
counts = await session.execute(
    select(Detection.object_type, func.count(Detection.id))
    .group_by(Detection.object_type)
)

Event¶

Table: events Source: backend/models/event.py Purpose: Aggregated security events analyzed by Nemotron LLM for risk assessment.

Schema¶

Indexes¶

Relationships¶

• Many-to-one with cameras
• One-to-many with alerts (cascade delete)

Risk Score Mapping¶

Example Data¶

{
  "id": 567,
  "batch_id": "abc123-def456-789",
  "camera_id": "front_door",
  "started_at": "2025-12-31T14:30:00Z",
  "ended_at": "2025-12-31T14:31:30Z",
  "risk_score": 75,
  "risk_level": "high",
  "summary": "Unknown person approaching front door during daytime",
  "reasoning": "Person detected approaching entry point. Face not recognized. Medium-high risk due to proximity to door.",
  "detection_ids": "[1234, 1235, 1236]",
  "reviewed": false,
  "notes": null,
  "is_fast_path": false,
  "object_types": "person",
  "clip_path": "/var/lib/security/clips/567.mp4"
}

Common Queries¶

# Get unreviewed high-risk events
events = await session.execute(
    select(Event)
    .where(Event.reviewed == False)
    .where(Event.risk_score >= 60)
    .order_by(Event.started_at.desc())
)

# Full-text search
events = await session.execute(
    select(Event)
    .where(Event.search_vector.match("person door"))
)

# Get events with alerts
events = await session.execute(
    select(Event)
    .options(selectinload(Event.alerts))
    .where(Event.id == event_id)
)

Alert System Models¶

Alert¶

Table: alerts Source: backend/models/alert.py Purpose: Stores triggered alert notifications from security events.

Schema¶

Indexes¶

Relationships¶

• Many-to-one with events
• Many-to-one with alert_rules

Example Data¶

{
  "id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
  "event_id": 567,
  "rule_id": "rule-uuid-here",
  "severity": "high",
  "status": "delivered",
  "created_at": "2025-12-31T14:31:35Z",
  "updated_at": "2025-12-31T14:31:36Z",
  "delivered_at": "2025-12-31T14:31:36Z",
  "channels": ["email", "push"],
  "dedup_key": "front_door:rule-uuid:person",
  "metadata": { "object_type": "person", "confidence": 0.92 }
}

AlertRule¶

Table: alert_rules Source: backend/models/alert.py Purpose: Defines conditions for generating alerts from events.

Schema¶

Indexes¶

Rule Evaluation Logic¶

All conditions in a rule must match (AND logic):

1. risk_threshold: Event risk_score >= threshold
2. object_types: Detection object type in list (empty = all)
3. camera_ids: Event camera_id in list (empty = all)
4. zone_ids: Detection in specified zone (empty = any)
5. min_confidence: Detection confidence >= threshold
6. schedule: Current time within schedule window

Example Data¶

{
  "id": "rule-uuid-here",
  "name": "Person at Night",
  "description": "Alert when person detected at front door after 10pm",
  "enabled": true,
  "severity": "high",
  "risk_threshold": 50,
  "object_types": ["person"],
  "camera_ids": ["front_door"],
  "zone_ids": null,
  "min_confidence": 0.7,
  "schedule": {
    "days": ["mon", "tue", "wed", "thu", "fri", "sat", "sun"],
    "start_time": "22:00",
    "end_time": "06:00",
    "timezone": "America/Chicago"
  },
  "dedup_key_template": "{camera_id}:{rule_id}",
  "cooldown_seconds": 600,
  "channels": ["email", "push"]
}

Zone and Baseline Models¶

Zone¶

Table: zones Source: backend/models/zone.py Purpose: Defines regions of interest on camera views for detection context.

Schema¶

Indexes¶

Coordinate System¶

Coordinates are normalized (0-1 range) relative to image dimensions:

{
  "coordinates": [
    [0.1, 0.2], // Top-left
    [0.3, 0.2], // Top-right
    [0.3, 0.8], // Bottom-right
    [0.1, 0.8] // Bottom-left
  ]
}

Example Data¶

{
  "id": "front_door_entry",
  "camera_id": "front_door",
  "name": "Entry Point",
  "zone_type": "entry_point",
  "coordinates": [
    [0.3, 0.4],
    [0.7, 0.4],
    [0.7, 0.9],
    [0.3, 0.9]
  ],
  "shape": "rectangle",
  "color": "#EF4444",
  "enabled": true,
  "priority": 10
}

ActivityBaseline¶

Table: activity_baselines Source: backend/models/baseline.py Purpose: Tracks average activity rates per camera by hour and day-of-week.

Schema¶

Constraints¶

• Unique constraint: (camera_id, hour, day_of_week)

Indexes¶

ClassBaseline¶

Table: class_baselines Source: backend/models/baseline.py Purpose: Tracks frequency of specific object classes per camera and hour.

Schema¶

Constraints¶

• Unique constraint: (camera_id, detection_class, hour)

Indexes¶

Supporting Models¶

GPUStats¶

Table: gpu_stats Source: backend/models/gpu_stats.py Purpose: Time-series GPU performance metrics for monitoring AI inference.

Schema¶

Indexes¶

Example Data¶

{
  "id": 12345,
  "recorded_at": "2025-12-31T14:30:00Z",
  "gpu_name": "NVIDIA RTX A5500",
  "gpu_utilization": 45.5,
  "memory_used": 7168,
  "memory_total": 24576,
  "temperature": 62.0,
  "power_usage": 125.0,
  "inference_fps": 15.2
}

Log¶

Table: logs Source: backend/models/log.py Purpose: Structured application logs with rich metadata for debugging.

Schema¶

Indexes¶

AuditLog¶

Table: audit_logs Source: backend/models/audit.py Purpose: Security-sensitive operation audit trail.

Schema¶

Indexes¶

APIKey¶

Table: api_keys Source: backend/models/api_key.py Purpose: API key management for authentication (optional, disabled by default).

Schema¶

Notes¶

• API keys are stored as SHA256 hashes, never in plaintext
• Authentication is disabled by default (API_KEY_ENABLED=False)
• Keys are loaded from API_KEYS environment variable on startup

Enumerations¶

Severity¶

Source: backend/models/enums.py

class Severity(str, Enum):
    LOW = "low"        # 0-29: Routine activity
    MEDIUM = "medium"  # 30-59: Notable activity
    HIGH = "high"      # 60-84: Concerning activity
    CRITICAL = "critical"  # 85-100: Immediate attention

AlertSeverity¶

Source: backend/models/alert.py

class AlertSeverity(str, Enum):
    LOW = "low"
    MEDIUM = "medium"
    HIGH = "high"
    CRITICAL = "critical"

AlertStatus¶

Source: backend/models/alert.py

class AlertStatus(str, Enum):
    PENDING = "pending"        # Alert created, not delivered
    DELIVERED = "delivered"    # Notification sent
    ACKNOWLEDGED = "acknowledged"  # User acknowledged
    DISMISSED = "dismissed"    # User dismissed

ZoneType¶

Source: backend/models/zone.py

class ZoneType(str, Enum):
    ENTRY_POINT = "entry_point"  # Doors, gates
    DRIVEWAY = "driveway"        # Vehicle areas
    SIDEWALK = "sidewalk"        # Public walkways
    YARD = "yard"                # Property grounds
    OTHER = "other"              # Custom zones

ZoneShape¶

Source: backend/models/zone.py

class ZoneShape(str, Enum):
    RECTANGLE = "rectangle"  # 4-point axis-aligned
    POLYGON = "polygon"      # Arbitrary polygon

AuditAction¶

Source: backend/models/audit.py

class AuditAction(str, Enum):
    # Event actions
    EVENT_REVIEWED = "event_reviewed"
    EVENT_DISMISSED = "event_dismissed"

    # Settings actions
    SETTINGS_CHANGED = "settings_changed"

    # Media actions
    MEDIA_EXPORTED = "media_exported"

    # Alert rule actions
    RULE_CREATED = "rule_created"
    RULE_UPDATED = "rule_updated"
    RULE_DELETED = "rule_deleted"

    # Camera actions
    CAMERA_CREATED = "camera_created"
    CAMERA_UPDATED = "camera_updated"
    CAMERA_DELETED = "camera_deleted"

    # Authentication actions
    LOGIN = "login"
    LOGOUT = "logout"

    # API key actions
    API_KEY_CREATED = "api_key_created"
    API_KEY_REVOKED = "api_key_revoked"

    # Notification actions
    NOTIFICATION_TEST = "notification_test"

    # Admin actions
    DATA_CLEARED = "data_cleared"

AuditStatus¶

Source: backend/models/audit.py

class AuditStatus(str, Enum):
    SUCCESS = "success"
    FAILURE = "failure"

Data Flow¶

flowchart TB
    subgraph Input["Image Capture"]
        FTP[Foscam FTP Upload]
    end

    subgraph Pipeline["AI Pipeline"]
        FW[FileWatcher]
        DQ[(Redis: detection_queue)]
        DQW[DetectionQueueWorker]
        RT[YOLO26]
        BA[BatchAggregator]
        AQ[(Redis: analysis_queue)]
        AQW[AnalysisQueueWorker]
        NEM[Nemotron LLM]
    end

    subgraph Storage["PostgreSQL"]
        CAM[(cameras)]
        DET[(detections)]
        EVT[(events)]
        ALT[(alerts)]
    end

    subgraph AlertSystem["Alert Evaluation"]
        AE[AlertEngine]
        AR[(alert_rules)]
    end

    subgraph Broadcast["Real-time Updates"]
        PS[(Redis: security_events)]
        WS[WebSocket]
    end

    FTP --> FW
    FW --> DQ
    DQ --> DQW
    DQW --> RT
    RT --> DET
    DQW --> BA
    BA --> AQ
    AQ --> AQW
    AQW --> NEM
    NEM --> EVT
    EVT --> AE
    AE --> AR
    AR --> ALT
    EVT --> PS
    PS --> WS

    CAM -.-> DET
    CAM -.-> EVT
    EVT -.-> ALT

Data Flow Steps¶

1. Image Arrival: Foscam camera uploads via FTP to /export/foscam/{camera_name}/
2. FileWatcher: Detects new file, validates, queues for processing
3. Deduplication: SHA256 hash checked against Redis cache
4. Detection Queue: File path pushed to detection_queue
5. Object Detection: YOLO26 performs inference, creates Detection records
6. Batch Aggregation: Detections grouped by camera in 90s windows
7. Analysis Queue: Completed batch pushed to analysis_queue
8. LLM Analysis: Nemotron analyzes batch, generates risk score and summary
9. Event Creation: Event record created with LLM results
10. Alert Evaluation: AlertEngine checks event against AlertRules
11. Alert Creation: Matching rules generate Alert records
12. Real-time Broadcast: Event published via Redis pub/sub to WebSocket clients

Common Query Patterns¶

Events by Camera (Last 24h)¶

from datetime import datetime, timedelta, UTC
from sqlalchemy import select

cutoff = datetime.now(UTC) - timedelta(hours=24)
stmt = (
    select(Event)
    .where(Event.camera_id == "front_door")
    .where(Event.started_at >= cutoff)
    .order_by(Event.started_at.desc())
)
# Uses: idx_events_camera_id, idx_events_started_at

Unreviewed High-Risk Events¶

stmt = (
    select(Event)
    .where(Event.reviewed == False)
    .where(Event.risk_score >= 60)
    .order_by(Event.started_at.desc())
    .limit(50)
)
# Uses: idx_events_reviewed, idx_events_risk_score

Detection Timeline for Event¶

import json

# Get detection IDs from event
detection_ids = json.loads(event.detection_ids) if event.detection_ids else []

# Fetch detections
stmt = (
    select(Detection)
    .where(Detection.id.in_(detection_ids))
    .order_by(Detection.detected_at.asc())
)

Alerts by Status¶

stmt = (
    select(Alert)
    .where(Alert.status == AlertStatus.PENDING)
    .order_by(Alert.created_at.desc())
)
# Uses: idx_alerts_status, idx_alerts_created_at

Active Alert Rules¶

stmt = (
    select(AlertRule)
    .where(AlertRule.enabled == True)
    .order_by(AlertRule.severity.desc())
)
# Uses: idx_alert_rules_enabled

GPU Stats History¶

cutoff = datetime.now(UTC) - timedelta(hours=1)
stmt = (
    select(GPUStats)
    .where(GPUStats.recorded_at >= cutoff)
    .order_by(GPUStats.recorded_at.asc())
)
# Uses: idx_gpu_stats_recorded_at

Full-Text Event Search¶

from sqlalchemy import func

stmt = (
    select(Event)
    .where(Event.search_vector.match("person door"))
    .order_by(func.ts_rank(Event.search_vector, func.plainto_tsquery("person door")).desc())
)
# Uses: idx_events_search_vector (GIN)

Audit Log for Resource¶

stmt = (
    select(AuditLog)
    .where(AuditLog.resource_type == "camera")
    .where(AuditLog.resource_id == "front_door")
    .order_by(AuditLog.timestamp.desc())
)
# Uses: idx_audit_logs_resource

Next Steps¶

• Codebase Tour - Understanding project structure
• Pipeline Overview - Detection and analysis flow

See Also¶

• Database Management - PostgreSQL setup and maintenance
• Backup and Recovery - Database backup procedures
• Alerts - How the alert system uses these models
• Detection Service - How detections are created

Back to Developer Hub