# AI Surveillance Platform — Complete Database Schema

## Document Information
- **Database**: PostgreSQL 15+
- **Extensions Required**: `pgvector`, `uuid-ossp`, `pgcrypto`, `pg_partman`
- **Purpose**: Core data model for an AI-powered video surveillance and facial recognition platform
- **Estimated Tables**: 29 core tables + 4 views + 8 trigger functions
- **Partitioning**: Monthly range partitioning on time-series tables (Events, Suspicious Activity, Audit Logs, System Health, Device Connectivity)
- **Vector Storage**: pgvector for 512-dimensional face embeddings with cosine similarity indexing

---

## Table of Contents

1. [Extension Setup](#1-extension-setup)
2. [Users & RBAC](#2-users--rbac)
3. [Site Infrastructure](#3-site-infrastructure)
4. [Person Management](#4-person-management)
5. [Face Recognition](#5-face-recognition)
6. [Events & Detections](#6-events--detections)
7. [Alerts & Rules](#7-alerts--rules)
8. [Watchlists](#8-watchlists)
9. [Notifications](#9-notifications)
10. [Media Storage](#10-media-storage)
11. [ML Training Pipeline](#11-ml-training-pipeline)
12. [Review & Audit](#12-review--audit)
13. [System Monitoring](#13-system-monitoring)
14. [Views](#14-views)
15. [Triggers & Functions](#15-triggers--functions)
16. [ER Diagram](#16-er-diagram)
17. [Retention Policies](#17-retention-policies)
18. [Row-Level Security](#18-row-level-security)
19. [Operational Notes](#19-operational-notes)

---

## 1. Extension Setup

```sql
-- =============================================================================
-- EXTENSION SETUP
-- =============================================================================

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";       -- UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";        -- Encryption/hashing utilities
CREATE EXTENSION IF NOT EXISTS "pgvector";        -- Vector similarity search (face embeddings)
CREATE EXTENSION IF NOT EXISTS "pg_partman";      -- Automated partition management

-- Verify pgvector version (requires 0.5.0+ for HNSW index support)
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'vector') THEN
        RAISE EXCEPTION 'pgvector extension is required. Install with: CREATE EXTENSION vector;';
    END IF;
END $$;

-- =============================================================================
-- CUSTOM TYPES & ENUMS
-- =============================================================================

-- User roles enum
CREATE TYPE user_role_enum AS ENUM ('super_admin', 'admin', 'operator', 'viewer', 'api_client');

-- Device status enum
CREATE TYPE device_status_enum AS ENUM ('online', 'offline', 'degraded', 'unknown', 'maintenance');

-- Event type enum
CREATE TYPE event_type_enum AS ENUM (
    'human_detected',
    'face_detected',
    'person_recognized',
    'motion_detected',
    'object_detected'
);

-- Activity type enum for suspicious activity
CREATE TYPE activity_type_enum AS ENUM (
    'intrusion',
    'loitering',
    'running',
    'crowding',
    'fall',
    'abandoned_object',
    'after_hours',
    'zone_breach',
    'repeated_entry',
    'dwell_time'
);

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

-- Alert status enum
CREATE TYPE alert_status_enum AS ENUM ('pending', 'sent', 'acknowledged', 'resolved', 'ignored');

-- Person status enum
CREATE TYPE person_status_enum AS ENUM ('known', 'unknown', 'blacklisted', 'vip');

-- Person label enum
CREATE TYPE person_label_enum AS ENUM (
    'employee',
    'visitor',
    'vendor',
    'driver',
    'staff',
    'contractor',
    'blacklisted',
    'vip',
    'custom'
);

-- Zone type enum
CREATE TYPE zone_type_enum AS ENUM ('restricted', 'interest', 'entry', 'exit');

-- Watchlist type enum
CREATE TYPE watchlist_type_enum AS ENUM ('vip', 'blacklist', 'suspect', 'custom');

-- Channel type enum for notifications
CREATE TYPE channel_type_enum AS ENUM ('telegram', 'whatsapp', 'email', 'webhook');

-- Media file type enum
CREATE TYPE media_file_type_enum AS ENUM ('image', 'video');

-- Training status enum
CREATE TYPE training_status_enum AS ENUM ('building', 'ready', 'training', 'archived');

-- Training job status enum
CREATE TYPE training_job_status_enum AS ENUM ('queued', 'running', 'completed', 'failed', 'rollback');

-- Review action enum
CREATE TYPE review_action_enum AS ENUM ('confirm', 'reject', 'correct_name', 'merge', 'delete');

-- Device connectivity event enum
CREATE TYPE connectivity_event_enum AS ENUM ('connected', 'disconnected', 'reconnect_failed');

-- Cluster status enum
CREATE TYPE cluster_status_enum AS ENUM ('active', 'merged', 'reviewed');
```

---

## 2. Users & RBAC

---

### Table: `roles`

**Purpose**: Defines role-based access control (RBAC) groups. Each role carries a JSON permissions matrix that specifies fine-grained access to platform resources.

**Rationale**: Separating roles from users enables flexible permission assignment. The JSON permissions matrix allows extensible permission definitions without schema migrations.

```sql
-- =============================================================================
-- TABLE: roles
-- =============================================================================

CREATE TABLE roles (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name            VARCHAR(50) NOT NULL,
    description     TEXT,
    permissions     JSONB NOT NULL DEFAULT '{}',
    -- Expected permissions structure:
    -- {
    --   "cameras": {"read": true, "write": true, "delete": false},
    --   "events": {"read": true, "acknowledge": true},
    --   "persons": {"read": true, "write": true, "merge": false},
    --   "alerts": {"read": true, "configure": true},
    --   "sites": {"read": true, "write": false},
    --   "system": {"read": true, "configure": false},
    --   "training": {"read": true, "trigger": false},
    --   "users": {"read": true, "write": false}
    -- }
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_roles_name UNIQUE (name)
);

-- Indexes
CREATE INDEX idx_roles_name ON roles (name);

-- Comments
COMMENT ON TABLE roles IS 'RBAC role definitions with JSON permissions matrix';
COMMENT ON COLUMN roles.permissions IS 'JSONB permissions matrix: resource -> {action: boolean}';

-- Insert default roles
INSERT INTO roles (name, description, permissions) VALUES
('super_admin', 'Full platform access', '{"cameras":{"read":true,"write":true,"delete":true},"events":{"read":true,"acknowledge":true,"delete":true},"persons":{"read":true,"write":true,"delete":true,"merge":true},"alerts":{"read":true,"configure":true},"sites":{"read":true,"write":true,"delete":true},"system":{"read":true,"configure":true},"training":{"read":true,"trigger":true,"approve":true},"users":{"read":true,"write":true,"delete":true},"audit":{"read":true},"media":{"read":true,"delete":true}}'::JSONB),
('admin', 'Administrative access', '{"cameras":{"read":true,"write":true,"delete":false},"events":{"read":true,"acknowledge":true,"delete":false},"persons":{"read":true,"write":true,"delete":false,"merge":true},"alerts":{"read":true,"configure":true},"sites":{"read":true,"write":true,"delete":false},"system":{"read":true,"configure":false},"training":{"read":true,"trigger":true,"approve":false},"users":{"read":true,"write":true,"delete":false},"audit":{"read":true},"media":{"read":true,"delete":false}}'::JSONB),
('operator', 'Day-to-day operations', '{"cameras":{"read":true,"write":false,"delete":false},"events":{"read":true,"acknowledge":true,"delete":false},"persons":{"read":true,"write":true,"delete":false,"merge":false},"alerts":{"read":true,"configure":false},"sites":{"read":true,"write":false,"delete":false},"system":{"read":false,"configure":false},"training":{"read":true,"trigger":false,"approve":false},"users":{"read":false,"write":false,"delete":false},"audit":{"read":false},"media":{"read":true,"delete":false}}'::JSONB),
('viewer', 'Read-only access', '{"cameras":{"read":true,"write":false,"delete":false},"events":{"read":true,"acknowledge":false,"delete":false},"persons":{"read":true,"write":false,"delete":false,"merge":false},"alerts":{"read":true,"configure":false},"sites":{"read":true,"write":false,"delete":false},"system":{"read":false,"configure":false},"training":{"read":false,"trigger":false,"approve":false},"users":{"read":false,"write":false,"delete":false},"audit":{"read":false},"media":{"read":true,"delete":false}}'::JSONB);
```

---

### Table: `users`

**Purpose**: Platform administrator and operator accounts. All passwords are bcrypt-hashed; MFA secrets are encrypted at rest.

**Rationale**: The `role_id` foreign key links to the RBAC system. `mfa_secret` stores the TOTP secret in encrypted form. The `last_login` field enables stale account detection and audit trails.

```sql
-- =============================================================================
-- TABLE: users
-- =============================================================================

CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username        VARCHAR(50) NOT NULL,
    email           VARCHAR(255) NOT NULL,
    password_hash   TEXT NOT NULL,              -- bcrypt hash, never store plaintext
    role_id         UUID NOT NULL,
    mfa_enabled     BOOLEAN NOT NULL DEFAULT FALSE,
    mfa_secret      TEXT,                       -- encrypted TOTP secret
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    last_login      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT uq_users_username UNIQUE (username),
    CONSTRAINT uq_users_email UNIQUE (email),
    CONSTRAINT fk_users_role
        FOREIGN KEY (role_id)
        REFERENCES roles (id)
        ON DELETE RESTRICT                          -- Prevent deleting roles with users
        ON UPDATE CASCADE
);

-- Indexes
CREATE INDEX idx_users_username ON users (username);
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_role_id ON users (role_id);
CREATE INDEX idx_users_is_active ON users (is_active) WHERE is_active = TRUE;
CREATE INDEX idx_users_last_login ON users (last_login);

-- Comments
COMMENT ON TABLE users IS 'Platform admin and operator accounts';
COMMENT ON COLUMN users.password_hash IS 'bcrypt hashed password - minimum cost 12';
COMMENT ON COLUMN users.mfa_secret IS 'AES-256 encrypted TOTP secret key';

-- Trigger: auto-update updated_at
CREATE TRIGGER trg_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION fn_update_updated_at();
```

---

### Table: `permissions_matrix` (Normalization of Role Permissions)

**Purpose**: Normalized permissions for applications that require SQL-level permission queries (as opposed to JSONB filtering). Provides fast lookups for middleware permission checks.

**Rationale**: While `roles.permissions` JSONB is convenient, a normalized table enables indexed queries for permission checks in application middleware without JSONB parsing overhead.

```sql
-- =============================================================================
-- TABLE: permissions_matrix
-- =============================================================================

CREATE TABLE permissions_matrix (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    role_id         UUID NOT NULL,
    resource        VARCHAR(50) NOT NULL,       -- cameras, events, persons, alerts, etc.
    action          VARCHAR(50) NOT NULL,       -- read, write, delete, configure, etc.
    granted         BOOLEAN NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT uq_permissions_role_resource_action UNIQUE (role_id, resource, action),
    CONSTRAINT fk_permissions_role
        FOREIGN KEY (role_id)
        REFERENCES roles (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Indexes
CREATE INDEX idx_permissions_matrix_role_id ON permissions_matrix (role_id);
CREATE INDEX idx_permissions_matrix_resource ON permissions_matrix (resource);
CREATE INDEX idx_permissions_matrix_lookup ON permissions_matrix (role_id, resource, action);

-- Comments
COMMENT ON TABLE permissions_matrix IS 'Normalized permission grants for fast middleware lookups';
```

---

## 3. Site Infrastructure

---

### Table: `sites`

**Purpose**: Physical surveillance locations. Each site can host multiple DVRs (future-proofed for NVR expansion). The timezone field ensures correct local-time event display.

**Rationale**: The `vpn_config` JSONB stores site-specific VPN parameters for secure remote DVR access. This keeps network configuration colocated with the site record.

```sql
-- =============================================================================
-- TABLE: sites
-- =============================================================================

CREATE TABLE sites (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name            VARCHAR(100) NOT NULL,
    address         TEXT,
    timezone        VARCHAR(50) NOT NULL DEFAULT 'UTC',
    -- Valid timezones: 'America/New_York', 'Europe/London', 'Asia/Singapore', etc.
    vpn_config      JSONB,                      -- {"type": "wireguard|openvpn", "endpoint": "...", "config": "..."}
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_sites_name UNIQUE (name)
);

-- Indexes
CREATE INDEX idx_sites_name ON sites (name);
CREATE INDEX idx_sites_timezone ON sites (timezone);

-- Comments
COMMENT ON TABLE sites IS 'Physical surveillance locations hosting DVR/NVR devices';
COMMENT ON COLUMN sites.vpn_config IS 'Site-specific VPN configuration for secure DVR access';
```

---

### Table: `dvrs`

**Purpose**: Digital Video Recorder device records. Stores encrypted credentials for automated RTSP stream management.

**Rationale**: Credentials are encrypted using `pgcrypto` with a server-side key. The `ports` JSONB allows flexible port configuration per device. `last_seen` enables dead device detection.

```sql
-- =============================================================================
-- TABLE: dvrs
-- =============================================================================

CREATE TABLE dvrs (
    id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    site_id             UUID NOT NULL,
    name                VARCHAR(100) NOT NULL,
    brand               VARCHAR(50),            -- Hikvision, Dahua, Axis, etc.
    model               VARCHAR(100),
    ip_address          INET NOT NULL,           -- IPv4 or IPv6 address
    ports               JSONB NOT NULL DEFAULT '{}',
    -- Expected: {"rtsp": 554, "http": 80, "https": 443, "sdk": 8000}
    firmware_version    VARCHAR(50),
    username_encrypted  BYTEA,                    -- AES-256 encrypted username
    password_encrypted  BYTEA,                    -- AES-256 encrypted password
    status              device_status_enum NOT NULL DEFAULT 'unknown',
    last_seen           TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_dvrs_site
        FOREIGN KEY (site_id)
        REFERENCES sites (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Indexes
CREATE INDEX idx_dvrs_site_id ON dvrs (site_id);
CREATE INDEX idx_dvrs_ip_address ON dvrs (ip_address);
CREATE INDEX idx_dvrs_status ON dvrs (status);
CREATE INDEX idx_dvrs_last_seen ON dvrs (last_seen);

-- Comments
COMMENT ON TABLE dvrs IS 'DVR/NVR device records with encrypted credentials';
COMMENT ON COLUMN dvrs.username_encrypted IS 'AES-256 encrypted DVR username';
COMMENT ON COLUMN dvrs.password_encrypted IS 'AES-256 encrypted DVR password';
COMMENT ON COLUMN dvrs.ports IS 'JSON port mapping: {protocol: port_number}';

-- Trigger: auto-update updated_at
CREATE TRIGGER trg_dvrs_updated_at
    BEFORE UPDATE ON dvrs
    FOR EACH ROW
    EXECUTE FUNCTION fn_update_updated_at();
```

---

### Table: `cameras`

**Purpose**: Individual camera channels connected to DVRs. Stores RTSP connection details, stream parameters, and per-camera configuration.

**Rationale**: The composite index on `(dvr_id, channel_number)` enforces uniqueness at the application level (one channel per DVR). The `config` JSONB stores camera-specific detection parameters (sensitivity, detection zones enabled, etc.).

```sql
-- =============================================================================
-- TABLE: cameras
-- =============================================================================

CREATE TABLE cameras (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    dvr_id          UUID NOT NULL,
    channel_number  INTEGER NOT NULL,           -- Channel/port number on the DVR
    name            VARCHAR(100) NOT NULL,
    location        VARCHAR(200),               -- Physical location description
    rtsp_url        TEXT,                       -- Full RTSP connection URL
    resolution      VARCHAR(20),                -- 1920x1080, 1280x720, etc.
    fps             INTEGER DEFAULT 25,         -- Frames per second
    status          device_status_enum NOT NULL DEFAULT 'unknown',
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    last_frame_time TIMESTAMPTZ,               -- Last successful frame capture
    config          JSONB NOT NULL DEFAULT '{}',
    -- Expected: {"detection_enabled": true, "sensitivity": 0.8, "min_face_size": 80,
    --            "night_mode": false, "recording_enabled": true, "codec": "h264"}
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_cameras_dvr
        FOREIGN KEY (dvr_id)
        REFERENCES dvrs (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT uq_cameras_dvr_channel UNIQUE (dvr_id, channel_number)
);

-- Indexes
CREATE INDEX idx_cameras_dvr_id ON cameras (dvr_id);
CREATE INDEX idx_cameras_channel_number ON cameras (channel_number);
CREATE INDEX idx_cameras_status ON cameras (status);
CREATE INDEX idx_cameras_is_active ON cameras (is_active) WHERE is_active = TRUE;
CREATE INDEX idx_cameras_config ON cameras USING GIN (config);

-- Comments
COMMENT ON TABLE cameras IS 'Individual camera channels with stream configuration';
COMMENT ON COLUMN cameras.config IS 'Camera-specific detection and recording parameters';
COMMENT ON COLUMN cameras.last_frame_time IS 'Timestamp of last successful frame capture for health monitoring';

-- Trigger: auto-update updated_at
CREATE TRIGGER trg_cameras_updated_at
    BEFORE UPDATE ON cameras
    FOR EACH ROW
    EXECUTE FUNCTION fn_update_updated_at();
```

---

### Table: `camera_zones`

**Purpose**: Polygon-defined zones within camera views for rule-based detection. Zones define areas of interest, restricted areas, entry/exit points.

**Rationale**: The `polygon_points` JSONB stores an ordered array of `{x, y}` normalized coordinates (0-1 range). Multiple zone types per camera enable sophisticated detection rules (e.g., alert on restricted zone intrusion but not on nearby public areas).

```sql
-- =============================================================================
-- TABLE: camera_zones
-- =============================================================================

CREATE TABLE camera_zones (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    camera_id       UUID NOT NULL,
    name            VARCHAR(100) NOT NULL,
    zone_type       zone_type_enum NOT NULL,
    polygon_points  JSONB NOT NULL,
    -- Format: [{"x": 0.1, "y": 0.2}, {"x": 0.5, "y": 0.1}, ...]
    -- Coordinates are normalized (0.0 to 1.0) relative to frame dimensions
    color           VARCHAR(7) DEFAULT '#FF0000',  -- Hex color for UI overlay
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_camera_zones_camera
        FOREIGN KEY (camera_id)
        REFERENCES cameras (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Indexes
CREATE INDEX idx_camera_zones_camera_id ON camera_zones (camera_id);
CREATE INDEX idx_camera_zones_zone_type ON camera_zones (zone_type);
CREATE INDEX idx_camera_zones_active ON camera_zones (is_active) WHERE is_active = TRUE;

-- Comments
COMMENT ON TABLE camera_zones IS 'Polygon zones within camera FOV for detection rules';
COMMENT ON COLUMN camera_zones.polygon_points IS 'Normalized polygon vertices [{x, y}] in 0.0-1.0 range';
```

---

## 4. Person Management

---

### Table: `persons`

**Purpose**: Central registry for all detected and known people. Links to face embeddings for recognition and supports categorization via status and role fields.

**Rationale**: `primary_face_embedding_id` establishes the default face used for recognition queries. The `status` field drives alert rules (blacklisted persons trigger alerts, VIPs trigger notifications). `created_by` tracks who added the person to the system.

```sql
-- =============================================================================
-- TABLE: persons
-- =============================================================================

CREATE TABLE persons (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name                    VARCHAR(200),       -- NULL for unknown persons
    status                  person_status_enum NOT NULL DEFAULT 'unknown',
    role                    VARCHAR(100),       -- Job role / title
    department              VARCHAR(100),
    company                 VARCHAR(200),
    notes                   TEXT,
    primary_face_embedding_id UUID,             -- Reference to preferred embedding
    created_by              UUID,               -- User who registered this person
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_persons_creator
        FOREIGN KEY (created_by)
        REFERENCES users (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
    -- Note: primary_face_embedding_id FK added after face_embeddings table creation
);

-- Indexes
CREATE INDEX idx_persons_name ON persons (name) WHERE name IS NOT NULL;
CREATE INDEX idx_persons_status ON persons (status);
CREATE INDEX idx_persons_role ON persons (role) WHERE role IS NOT NULL;
CREATE INDEX idx_persons_created_by ON persons (created_by);
CREATE INDEX idx_persons_status_name ON persons (status, name);

-- Comments
COMMENT ON TABLE persons IS 'Known and unknown person registry';
COMMENT ON COLUMN persons.primary_face_embedding_id IS 'Preferred face embedding for recognition queries';
COMMENT ON COLUMN persons.status IS 'known/unknown/blacklisted/vip - drives alert behavior';

-- Trigger: auto-update updated_at
CREATE TRIGGER trg_persons_updated_at
    BEFORE UPDATE ON persons
    FOR EACH ROW
    EXECUTE FUNCTION fn_update_updated_at();
```

---

### Table: `person_aliases`

**Purpose**: Alternative names for the same person to improve recognition accuracy (nicknames, name variations, different-language names).

**Rationale**: Face recognition alone may not capture contextual identity. Aliases enable searching by any known name variant.

```sql
-- =============================================================================
-- TABLE: person_aliases
-- =============================================================================

CREATE TABLE person_aliases (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    person_id       UUID NOT NULL,
    alias_name      VARCHAR(200) NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_aliases_person
        FOREIGN KEY (person_id)
        REFERENCES persons (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT uq_aliases_person_name UNIQUE (person_id, alias_name)
);

-- Indexes
CREATE INDEX idx_aliases_person_id ON person_aliases (person_id);
CREATE INDEX idx_aliases_alias_name ON person_aliases (alias_name);

-- Comments
COMMENT ON TABLE person_aliases IS 'Alternative names for the same person';
```

---

### Table: `person_labels`

**Purpose**: Categorization labels for persons enabling filtered views and rule targeting (e.g., alert on all "contractor" entries after hours).

**Rationale**: A person can have multiple labels. The enum provides standard categories while 'custom' allows organization-specific extensions. Labels are independent of status (a person can be both "employee" and "vip").

```sql
-- =============================================================================
-- TABLE: person_labels
-- =============================================================================

CREATE TABLE person_labels (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    person_id       UUID NOT NULL,
    label           person_label_enum NOT NULL,
    created_by      UUID,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_labels_person
        FOREIGN KEY (person_id)
        REFERENCES persons (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_labels_creator
        FOREIGN KEY (created_by)
        REFERENCES users (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT uq_labels_person_label UNIQUE (person_id, label)
);

-- Indexes
CREATE INDEX idx_labels_person_id ON person_labels (person_id);
CREATE INDEX idx_labels_label ON person_labels (label);
CREATE INDEX idx_labels_person_label ON person_labels (person_id, label);

-- Comments
COMMENT ON TABLE person_labels IS 'Categorization labels for person filtering and rule targeting';
```

---

## 5. Face Recognition

---

### Table: `face_embeddings`

**Purpose**: Stores face recognition vectors generated by deep learning models. Uses pgvector for efficient similarity search.

**Rationale**: The `embedding` column uses pgvector's `vector(512)` type for 512-dimensional face descriptors. HNSW index enables sub-millisecond approximate nearest neighbor queries. The `is_primary` flag marks the best-quality face for each person. `model_version` ensures embeddings are only compared against same-model vectors.

```sql
-- =============================================================================
-- TABLE: face_embeddings
-- =============================================================================

CREATE TABLE face_embeddings (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    person_id       UUID NOT NULL,
    embedding       vector(512) NOT NULL,       -- 512-d face descriptor from recognition model
    source_image_path TEXT NOT NULL,            -- Path to source face crop image
    face_quality    DECIMAL(4,3) NOT NULL DEFAULT 0.0,
    -- Quality score: 0.000 to 1.000 (blur, pose, illumination)
    confidence      DECIMAL(4,3) NOT NULL DEFAULT 0.0,
    -- Detection confidence: 0.000 to 1.000
    model_version   VARCHAR(20) NOT NULL,       -- Model that generated this embedding
    is_primary      BOOLEAN NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_embeddings_person
        FOREIGN KEY (person_id)
        REFERENCES persons (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT chk_embedding_quality CHECK (face_quality BETWEEN 0.0 AND 1.0),
    CONSTRAINT chk_embedding_confidence CHECK (confidence BETWEEN 0.0 AND 1.0)
);

-- Indexes
CREATE INDEX idx_embeddings_person_id ON face_embeddings (person_id);
CREATE INDEX idx_embeddings_primary ON face_embeddings (is_primary) WHERE is_primary = TRUE;
CREATE INDEX idx_embeddings_model ON face_embeddings (model_version);

-- HNSW index for fast approximate nearest neighbor search
-- ef_construction=64: build-time quality vs speed tradeoff
-- m=16: number of bi-directional links per node
CREATE INDEX idx_embeddings_hnsw ON face_embeddings
    USING hnsw (embedding vector_cosine_ops)
    WITH (ef_construction = 64, m = 16);

-- IVFFlat index alternative for larger datasets (commented, choose one)
-- CREATE INDEX idx_embeddings_ivfflat ON face_embeddings
--     USING ivfflat (embedding vector_cosine_ops)
--     WITH (lists = 100);

-- Comments
COMMENT ON TABLE face_embeddings IS 'Face recognition vectors with pgvector similarity indexing';
COMMENT ON COLUMN face_embeddings.embedding IS '512-dimensional face descriptor. Use <-> operator for L2, <=> for cosine distance';
COMMENT ON COLUMN face_embeddings.is_primary IS 'Primary embedding used for one-to-many recognition';

-- Add deferred foreign key from persons.primary_face_embedding_id
ALTER TABLE persons
    ADD CONSTRAINT fk_persons_primary_embedding
    FOREIGN KEY (primary_face_embedding_id)
    REFERENCES face_embeddings (id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
    DEFERRABLE INITIALLY DEFERRED;
```

---

### Table: `person_clusters`

**Purpose**: Groups unknown person detections by visual similarity. Enables operators to review and potentially identify recurring unknown visitors.

**Rationale**: When face recognition fails (unknown person), embeddings are clustered. `representative_embedding_id` points to the cluster's centroid face. `merged_into_cluster_id` supports cluster consolidation when two clusters are determined to be the same person.

```sql
-- =============================================================================
-- TABLE: person_clusters
-- =============================================================================

CREATE TABLE person_clusters (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    cluster_label           VARCHAR(50) NOT NULL,   -- Auto-generated label: "Unknown-001"
    representative_embedding_id UUID,               -- Best face representing this cluster
    sample_count            INTEGER NOT NULL DEFAULT 0,
    first_seen              TIMESTAMPTZ NOT NULL,
    last_seen               TIMESTAMPTZ NOT NULL,
    status                  cluster_status_enum NOT NULL DEFAULT 'active',
    merged_into_cluster_id  UUID,                   -- Self-reference for cluster merges
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT uq_clusters_label UNIQUE (cluster_label),
    CONSTRAINT fk_clusters_representative
        FOREIGN KEY (representative_embedding_id)
        REFERENCES face_embeddings (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT fk_clusters_merged_into
        FOREIGN KEY (merged_into_cluster_id)
        REFERENCES person_clusters (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- Indexes
CREATE INDEX idx_clusters_status ON person_clusters (status);
CREATE INDEX idx_clusters_merged_into ON person_clusters (merged_into_cluster_id) WHERE merged_into_cluster_id IS NOT NULL;
CREATE INDEX idx_clusters_first_seen ON person_clusters (first_seen);
CREATE INDEX idx_clusters_last_seen ON person_clusters (last_seen);

-- Comments
COMMENT ON TABLE person_clusters IS 'Unknown person clustering for operator review';
COMMENT ON COLUMN person_clusters.merged_into_cluster_id IS 'Reference to consolidated cluster when duplicates are merged';
```

---

## 6. Events & Detections

---

### Table: `events` (Partitioned: Monthly by `timestamp`)

**Purpose**: Core event log for all AI detections. Partitioned monthly for query performance and data lifecycle management.

**Rationale**: Time-series data at scale requires partitioning for efficient queries and retention. The `metadata` JSONB stores detection-specific data (pose, attributes, track_id) without schema changes. Monthly partitioning balances granularity with manageability.

```sql
-- =============================================================================
-- TABLE: events (MONTHLY PARTITIONED BY timestamp)
-- =============================================================================

CREATE TABLE events (
    id              UUID NOT NULL,
    camera_id       UUID NOT NULL,
    event_type      event_type_enum NOT NULL,
    person_id       UUID,                       -- NULL if unknown person
    cluster_id      UUID,                       -- Reference to person_clusters for unknowns
    confidence      DECIMAL(4,3) NOT NULL,
    bounding_box    JSONB NOT NULL,
    -- Format: {"x": 120, "y": 80, "width": 200, "height": 250, "frame_width": 1920, "frame_height": 1080}
    image_path      TEXT,                       -- Path to detection snapshot
    video_clip_path TEXT,                       -- Path to associated clip (if generated)
    timestamp       TIMESTAMPTZ NOT NULL,
    frame_number    BIGINT,                     -- Frame number in source video
    metadata        JSONB NOT NULL DEFAULT '{}',
    -- Format: {"pose": "frontal", "attributes": {"glasses": true, "mask": false},
    --          "track_id": "uuid", "processing_time_ms": 45}
    reviewed        BOOLEAN NOT NULL DEFAULT FALSE,
    review_action   review_action_enum,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Partition key: timestamp (monthly ranges)
    PRIMARY KEY (id, timestamp),

    -- Constraints
    CONSTRAINT fk_events_camera
        FOREIGN KEY (camera_id)
        REFERENCES cameras (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_events_person
        FOREIGN KEY (person_id)
        REFERENCES persons (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT fk_events_cluster
        FOREIGN KEY (cluster_id)
        REFERENCES person_clusters (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT chk_events_confidence CHECK (confidence BETWEEN 0.0 AND 1.0)
) PARTITION BY RANGE (timestamp);

-- Create monthly partitions (example: current year + next year)
-- Use pg_partman for automated partition management
-- Or manually create partitions:
CREATE TABLE events_y2024m01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_y2024m02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE events_y2024m03 PARTITION OF events
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
-- ... (additional partitions created via pg_partman or cron job)

-- Default partition catches overflow
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- Indexes (per partition automatically)
CREATE INDEX idx_events_camera_id ON events (camera_id);
CREATE INDEX idx_events_timestamp ON events (timestamp DESC);
CREATE INDEX idx_events_event_type ON events (event_type);
CREATE INDEX idx_events_person_id ON events (person_id) WHERE person_id IS NOT NULL;
CREATE INDEX idx_events_cluster_id ON events (cluster_id) WHERE cluster_id IS NOT NULL;
CREATE INDEX idx_events_reviewed ON events (reviewed) WHERE reviewed = FALSE;
CREATE INDEX idx_events_metadata ON events USING GIN (metadata);

-- Comments
COMMENT ON TABLE events IS 'All AI detection events - partitioned monthly for performance and retention';
COMMENT ON COLUMN events.metadata IS 'Detection-specific data: pose, attributes, track_id, processing metrics';
```

---

### Table: `suspicious_activity_events` (Partitioned: Monthly by `timestamp`)

**Purpose**: Anomaly and suspicious behavior events detected during night mode or by behavioral analysis. Separate from base events to enable different retention and alert rules.

**Rationale**: These events are higher-severity and require different handling. The `details` JSONB stores activity-specific data (dwell duration, zone entry/exit times, crowd count). Separate partitioning allows longer retention for security-relevant events.

```sql
-- =============================================================================
-- TABLE: suspicious_activity_events (MONTHLY PARTITIONED BY timestamp)
-- =============================================================================

CREATE TABLE suspicious_activity_events (
    id              UUID NOT NULL,
    camera_id       UUID NOT NULL,
    event_id        UUID,                       -- Link to base events table (if derived)
    activity_type   activity_type_enum NOT NULL,
    zone_id         UUID,                       -- Associated camera zone (if applicable)
    confidence      DECIMAL(4,3) NOT NULL,
    severity        severity_enum NOT NULL,
    details         JSONB NOT NULL DEFAULT '{}',
    -- Format varies by activity_type:
    -- intrusion: {"zone_name": "Loading Dock", "entry_point": {"x": 0.3, "y": 0.5}}
    -- loitering: {"duration_seconds": 320, "location": {"x": 0.4, "y": 0.6}}
    -- crowding:  {"person_count": 12, "cluster_center": {"x": 0.5, "y": 0.5}}
    -- fall:      {"floor_location": {"x": 0.6, "y": 0.8}, "impact_severity": 0.7}
    image_path      TEXT,
    video_clip_path TEXT,
    timestamp       TIMESTAMPTZ NOT NULL,
    alert_sent      BOOLEAN NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Partition key: timestamp (monthly ranges)
    PRIMARY KEY (id, timestamp),

    -- Constraints
    CONSTRAINT fk_suspicious_camera
        FOREIGN KEY (camera_id)
        REFERENCES cameras (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_suspicious_zone
        FOREIGN KEY (zone_id)
        REFERENCES camera_zones (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT chk_suspicious_confidence CHECK (confidence BETWEEN 0.0 AND 1.0)
) PARTITION BY RANGE (timestamp);

-- Monthly partitions
CREATE TABLE suspicious_activity_y2024m01 PARTITION OF suspicious_activity_events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE suspicious_activity_y2024m02 PARTITION OF suspicious_activity_events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- ... (additional via pg_partman)
CREATE TABLE suspicious_activity_default PARTITION OF suspicious_activity_events DEFAULT;

-- Indexes
CREATE INDEX idx_suspicious_camera_id ON suspicious_activity_events (camera_id);
CREATE INDEX idx_suspicious_timestamp ON suspicious_activity_events (timestamp DESC);
CREATE INDEX idx_suspicious_activity_type ON suspicious_activity_events (activity_type);
CREATE INDEX idx_suspicious_severity ON suspicious_activity_events (severity);
CREATE INDEX idx_suspicious_alert_sent ON suspicious_activity_events (alert_sent) WHERE alert_sent = FALSE;
CREATE INDEX idx_suspicious_zone_id ON suspicious_activity_events (zone_id) WHERE zone_id IS NOT NULL;

-- Comments
COMMENT ON TABLE suspicious_activity_events IS 'Anomaly and suspicious behavior events - partitioned monthly';
COMMENT ON COLUMN suspicious_activity_events.details IS 'Activity-specific details: duration, counts, locations';
```

---

## 7. Alerts & Rules

---

### Table: `alert_rules`

**Purpose**: Configurable conditions that trigger alerts. Rules can target specific cameras, persons, zones, or event types.

**Rationale**: The flexible schema allows complex rule definitions. `schedule` JSONB defines when the rule is active (days of week, hours). `quiet_hours` prevents alert fatigue during known busy periods. NULL values in constraint fields mean "match any."

```sql
-- =============================================================================
-- TABLE: alert_rules
-- =============================================================================

CREATE TABLE alert_rules (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name                    VARCHAR(200) NOT NULL,
    camera_id               UUID,               -- NULL = all cameras
    person_id               UUID,               -- NULL = any person
    person_role             VARCHAR(100),       -- Match person role field
    zone_id                 UUID,               -- NULL = any zone
    event_type              event_type_enum,    -- NULL = any event type
    schedule                JSONB NOT NULL DEFAULT '{"days": [0,1,2,3,4,5,6], "start_time": "00:00", "end_time": "23:59"}',
    -- Format: {"days": [1,2,3,4,5], "start_time": "18:00", "end_time": "06:00", "timezone": "Asia/Singapore"}
    confidence_threshold    DECIMAL(4,3) NOT NULL DEFAULT 0.75,
    severity                severity_enum NOT NULL DEFAULT 'medium',
    is_active               BOOLEAN NOT NULL DEFAULT TRUE,
    quiet_hours             JSONB,              -- {"start": "12:00", "end": "13:00", "enabled": true}
    created_by              UUID NOT NULL,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_alert_rules_camera
        FOREIGN KEY (camera_id)
        REFERENCES cameras (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_alert_rules_person
        FOREIGN KEY (person_id)
        REFERENCES persons (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_alert_rules_zone
        FOREIGN KEY (zone_id)
        REFERENCES camera_zones (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT fk_alert_rules_creator
        FOREIGN KEY (created_by)
        REFERENCES users (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT chk_alert_rules_confidence CHECK (confidence_threshold BETWEEN 0.0 AND 1.0)
);

-- Indexes
CREATE INDEX idx_alert_rules_camera_id ON alert_rules (camera_id) WHERE camera_id IS NOT NULL;
CREATE INDEX idx_alert_rules_is_active ON alert_rules (is_active) WHERE is_active = TRUE;
CREATE INDEX idx_alert_rules_person_id ON alert_rules (person_id) WHERE person_id IS NOT NULL;
CREATE INDEX idx_alert_rules_severity ON alert_rules (severity);

-- Comments
COMMENT ON TABLE alert_rules IS 'Configurable conditions that trigger alerts';
COMMENT ON COLUMN alert_rules.schedule IS 'Active schedule: days (0=Sunday), start/end times, timezone';
COMMENT ON COLUMN alert_rules.quiet_hours IS 'Suppression window to prevent alert fatigue';

-- Trigger: auto-update updated_at
CREATE TRIGGER trg_alert_rules_updated_at
    BEFORE UPDATE ON alert_rules
    FOR EACH ROW
    EXECUTE FUNCTION fn_update_updated_at();
```

---

### Table: `alerts`

**Purpose**: Alert records generated when an event matches an alert rule. Tracks the full lifecycle from creation through resolution.

**Rationale**: The status field drives the alert management workflow. Acknowledgment and resolution fields create a complete audit trail of who handled each alert and when.

```sql
-- =============================================================================
-- TABLE: alerts
-- =============================================================================

CREATE TABLE alerts (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_id        UUID NOT NULL,
    alert_rule_id   UUID,
    person_id       UUID,
    camera_id       UUID NOT NULL,
    alert_type      VARCHAR(50) NOT NULL,       -- Human-readable type: "Person_Detected", "Zone_Intrusion"
    severity        severity_enum NOT NULL,
    message         TEXT NOT NULL,
    status          alert_status_enum NOT NULL DEFAULT 'pending',
    sent_at         TIMESTAMPTZ,               -- When notifications were dispatched
    acknowledged_by UUID,                       -- User who acknowledged
    acknowledged_at TIMESTAMPTZ,
    resolved_by     UUID,                       -- User who resolved
    resolved_at     TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_alerts_camera
        FOREIGN KEY (camera_id)
        REFERENCES cameras (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_alerts_rule
        FOREIGN KEY (alert_rule_id)
        REFERENCES alert_rules (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT fk_alerts_person
        FOREIGN KEY (person_id)
        REFERENCES persons (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT fk_alerts_ack_by
        FOREIGN KEY (acknowledged_by)
        REFERENCES users (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT fk_alerts_resolved_by
        FOREIGN KEY (resolved_by)
        REFERENCES users (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- Indexes
CREATE INDEX idx_alerts_status ON alerts (status);
CREATE INDEX idx_alerts_camera_id ON alerts (camera_id);
CREATE INDEX idx_alerts_created_at ON alerts (created_at DESC);
CREATE INDEX idx_alerts_person_id ON alerts (person_id) WHERE person_id IS NOT NULL;
CREATE INDEX idx_alerts_rule_id ON alerts (alert_rule_id) WHERE alert_rule_id IS NOT NULL;
CREATE INDEX idx_alerts_severity ON alerts (severity);
CREATE INDEX idx_alerts_status_created ON alerts (status, created_at DESC);

-- Comments
COMMENT ON TABLE alerts IS 'Alert records with full lifecycle tracking';
```

---

## 8. Watchlists

---

### Table: `watchlists`

**Purpose**: Named lists of persons to monitor. Enables grouping persons by monitoring priority (VIP guests, blacklisted individuals, suspects).

**Rationale**: Watchlists decouple the monitoring concept from person status, allowing multiple lists and custom groupings. A person can appear on multiple watchlists.

```sql
-- =============================================================================
-- TABLE: watchlists
-- =============================================================================

CREATE TABLE watchlists (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name            VARCHAR(100) NOT NULL,
    description     TEXT,
    watch_type      watchlist_type_enum NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_by      UUID NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_watchlists_creator
        FOREIGN KEY (created_by)
        REFERENCES users (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- Indexes
CREATE INDEX idx_watchlists_watch_type ON watchlists (watch_type);
CREATE INDEX idx_watchlists_is_active ON watchlists (is_active) WHERE is_active = TRUE;
CREATE INDEX idx_watchlists_created_by ON watchlists (created_by);

-- Comments
COMMENT ON TABLE watchlists IS 'Named lists of persons to monitor (VIP, blacklist, suspect, custom)';
```

---

### Table: `watchlist_entries`

**Purpose**: Association table linking persons to watchlists.

**Rationale**: Simple many-to-many junction with audit metadata (who added, when, notes).

```sql
-- =============================================================================
-- TABLE: watchlist_entries
-- =============================================================================

CREATE TABLE watchlist_entries (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    watchlist_id    UUID NOT NULL,
    person_id       UUID NOT NULL,
    added_by        UUID NOT NULL,
    added_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    notes           TEXT,

    -- Constraints
    CONSTRAINT uq_watchlist_entry UNIQUE (watchlist_id, person_id),
    CONSTRAINT fk_entries_watchlist
        FOREIGN KEY (watchlist_id)
        REFERENCES watchlists (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_entries_person
        FOREIGN KEY (person_id)
        REFERENCES persons (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_entries_added_by
        FOREIGN KEY (added_by)
        REFERENCES users (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- Indexes
CREATE INDEX idx_watchlist_entries_watchlist_id ON watchlist_entries (watchlist_id);
CREATE INDEX idx_watchlist_entries_person_id ON watchlist_entries (person_id);

-- Comments
COMMENT ON TABLE watchlist_entries IS 'Persons assigned to watchlists';
```

---

## 9. Notifications

---

### Table: `notification_channels`

**Purpose**: Notification endpoint definitions. Each channel represents a configured destination for alerts.

**Rationale**: The `config` JSONB stores channel-type-specific settings. Channel-specific tables (telegram_configs, whatsapp_configs) store encrypted credentials separately.

```sql
-- =============================================================================
-- TABLE: notification_channels
-- =============================================================================

CREATE TABLE notification_channels (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name            VARCHAR(100) NOT NULL,
    channel_type    channel_type_enum NOT NULL,
    config          JSONB NOT NULL DEFAULT '{}',
    -- Common config: {"timeout_seconds": 30, "retry_count": 3, "rate_limit_per_min": 60}
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_by      UUID NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_channels_creator
        FOREIGN KEY (created_by)
        REFERENCES users (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- Indexes
CREATE INDEX idx_channels_channel_type ON notification_channels (channel_type);
CREATE INDEX idx_channels_is_active ON notification_channels (is_active) WHERE is_active = TRUE;

-- Comments
COMMENT ON TABLE notification_channels IS 'Notification endpoint definitions';
```

---

### Table: `telegram_configs`

**Purpose**: Telegram Bot API configuration with encrypted bot token.

**Rationale**: Bot tokens are sensitive credentials requiring encryption at rest.

```sql
-- =============================================================================
-- TABLE: telegram_configs
-- =============================================================================

CREATE TABLE telegram_configs (
    id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    channel_id          UUID NOT NULL,
    bot_token_encrypted BYTEA NOT NULL,         -- AES-256 encrypted bot token
    chat_id             VARCHAR(50) NOT NULL,   -- Telegram chat/channel ID
    message_template    TEXT NOT NULL DEFAULT '{{alert_type}}: {{message}} at {{timestamp}}. Camera: {{camera_name}}',
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_telegram_channel
        FOREIGN KEY (channel_id)
        REFERENCES notification_channels (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT uq_telegram_channel UNIQUE (channel_id)
);

-- Comments
COMMENT ON TABLE telegram_configs IS 'Telegram Bot API configuration with encrypted credentials';
```

---

### Table: `whatsapp_configs`

**Purpose**: WhatsApp Business API configuration with encrypted API key.

**Rationale**: API keys are sensitive and stored encrypted. `recipient_groups` enables sending to multiple predefined groups.

```sql
-- =============================================================================
-- TABLE: whatsapp_configs
-- =============================================================================

CREATE TABLE whatsapp_configs (
    id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    channel_id          UUID NOT NULL,
    api_key_encrypted   BYTEA NOT NULL,         -- AES-256 encrypted API key
    phone_number_id     VARCHAR(50) NOT NULL,   -- WhatsApp Business phone number ID
    recipient_groups    JSONB NOT NULL DEFAULT '[]',
    -- Format: ["security-team", "management", "reception"]
    message_template    TEXT NOT NULL DEFAULT '*Alert*: {{alert_type}} at {{location}}. Time: {{timestamp}}. Confidence: {{confidence}}%',
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_whatsapp_channel
        FOREIGN KEY (channel_id)
        REFERENCES notification_channels (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT uq_whatsapp_channel UNIQUE (channel_id)
);

-- Comments
COMMENT ON TABLE whatsapp_configs IS 'WhatsApp Business API configuration with encrypted credentials';
```

---

## 10. Media Storage

---

### Table: `media_files`

**Purpose**: Registry of stored images and video clips. Tracks file metadata, camera source, associated events, and retention policy.

**Rationale**: `retention_until` enables automated lifecycle management. `checksum` (SHA-256) ensures file integrity. The table does not store the actual file blob — files are kept on object storage (S3/MinIO) with this table as the index.

```sql
-- =============================================================================
-- TABLE: media_files
-- =============================================================================

CREATE TABLE media_files (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    file_type       media_file_type_enum NOT NULL,
    storage_path    TEXT NOT NULL,              -- Object storage path: s3://bucket/camera_id/2024/01/15/filename.jpg
    file_size       BIGINT NOT NULL,            -- Size in bytes
    checksum        VARCHAR(64) NOT NULL,       -- SHA-256 hex digest
    camera_id       UUID NOT NULL,
    event_id        UUID,                       -- Associated event (if applicable)
    timestamp       TIMESTAMPTZ NOT NULL,
    retention_until TIMESTAMPTZ NOT NULL,       -- Auto-delete after this date
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_media_camera
        FOREIGN KEY (camera_id)
        REFERENCES cameras (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Indexes
CREATE INDEX idx_media_files_camera_id ON media_files (camera_id);
CREATE INDEX idx_media_files_timestamp ON media_files (timestamp DESC);
CREATE INDEX idx_media_files_event_id ON media_files (event_id) WHERE event_id IS NOT NULL;
CREATE INDEX idx_media_files_retention ON media_files (retention_until) WHERE retention_until < NOW() + INTERVAL '7 days';

-- Comments
COMMENT ON TABLE media_files IS 'Registry of stored media with retention management';
COMMENT ON COLUMN media_files.storage_path IS 'Object storage URI - files stored externally (S3/MinIO)';
```

---

### Table: `video_clips`

**Purpose**: Video clip metadata with temporal boundaries and encoding information.

**Rationale**: Clips are typically generated around event timestamps. This table enables fast lookup of clips by time range and camera — essential for incident review workflows.

```sql
-- =============================================================================
-- TABLE: video_clips
-- =============================================================================

CREATE TABLE video_clips (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    media_file_id   UUID NOT NULL,
    duration        DECIMAL(8,2) NOT NULL,      -- Duration in seconds
    start_time      TIMESTAMPTZ NOT NULL,
    end_time        TIMESTAMPTZ NOT NULL,
    camera_id       UUID NOT NULL,
    event_id        UUID,                       -- Triggering event
    format          VARCHAR(10) NOT NULL DEFAULT 'mp4',  -- mp4, mkv, avi
    size_bytes      BIGINT NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_clips_media
        FOREIGN KEY (media_file_id)
        REFERENCES media_files (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_clips_camera
        FOREIGN KEY (camera_id)
        REFERENCES cameras (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT chk_clips_duration CHECK (duration > 0),
    CONSTRAINT chk_clips_time_range CHECK (end_time > start_time)
);

-- Indexes
CREATE INDEX idx_video_clips_camera_id ON video_clips (camera_id);
CREATE INDEX idx_video_clips_start_time ON video_clips (start_time DESC);
CREATE INDEX idx_video_clips_event_id ON video_clips (event_id) WHERE event_id IS NOT NULL;
CREATE INDEX idx_video_clips_camera_time ON video_clips (camera_id, start_time DESC);

-- Comments
COMMENT ON TABLE video_clips IS 'Video clip metadata for incident review';
```

---

## 11. ML Training Pipeline

---

### Table: `training_datasets`

**Purpose**: Datasets assembled for model training, containing curated face samples.

**Rationale**: `person_ids` JSONB tracks which persons are included. `status` drives the dataset lifecycle from assembly through training to archival.

```sql
-- =============================================================================
-- TABLE: training_datasets
-- =============================================================================

CREATE TABLE training_datasets (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name            VARCHAR(200) NOT NULL,
    description     TEXT,
    person_ids      JSONB NOT NULL DEFAULT '[]',  -- UUID array of included persons
    sample_count    INTEGER NOT NULL DEFAULT 0,
    version         VARCHAR(20) NOT NULL,
    status          training_status_enum NOT NULL DEFAULT 'building',
    created_by      UUID NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_datasets_creator
        FOREIGN KEY (created_by)
        REFERENCES users (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- Indexes
CREATE INDEX idx_datasets_status ON training_datasets (status);
CREATE INDEX idx_datasets_created_by ON training_datasets (created_by);

-- Comments
COMMENT ON TABLE training_datasets IS 'Curated face datasets for model training';
```

---

### Table: `training_jobs`

**Purpose**: Model training job tracking with status lifecycle and performance metrics.

**Rationale**: Links a dataset to a training run. `metrics` JSONB stores training results (accuracy, loss curves, validation metrics). Approval workflow ensures only reviewed models go to production.

```sql
-- =============================================================================
-- TABLE: training_jobs
-- =============================================================================

CREATE TABLE training_jobs (
    id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    dataset_id          UUID NOT NULL,
    model_version_from  VARCHAR(20),            -- Base model version (NULL = from scratch)
    model_version_to    VARCHAR(20) NOT NULL,   -- Target/new model version
    status              training_job_status_enum NOT NULL DEFAULT 'queued',
    started_at          TIMESTAMPTZ,
    completed_at        TIMESTAMPTZ,
    metrics             JSONB NOT NULL DEFAULT '{}',
    -- Format: {"accuracy": 0.982, "precision": 0.975, "recall": 0.968,
    --          "f1_score": 0.971, "epoch_count": 50, "training_time_seconds": 7200,
    --          "validation_loss": [0.45, 0.32, 0.21, ...]}
    approved_by         UUID,
    approved_at         TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_jobs_dataset
        FOREIGN KEY (dataset_id)
        REFERENCES training_datasets (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_jobs_approved_by
        FOREIGN KEY (approved_by)
        REFERENCES users (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- Indexes
CREATE INDEX idx_training_jobs_status ON training_jobs (status);
CREATE INDEX idx_training_jobs_dataset_id ON training_jobs (dataset_id);

-- Comments
COMMENT ON TABLE training_jobs IS 'Model training job tracking and approval workflow';
```

---

### Table: `model_versions`

**Purpose**: Registry of trained model versions with deployment status and rollback capability.

**Rationale**: Only one model can be `is_production` at a time. `is_rollback_available` flags models that can be quickly restored. This table serves as the model registry for the MLOps pipeline.

```sql
-- =============================================================================
-- TABLE: model_versions
-- =============================================================================

CREATE TABLE model_versions (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    version_string          VARCHAR(20) NOT NULL,       -- Semantic: "2.3.1", "2024.01.15-v1"
    training_job_id         UUID,
    metrics                 JSONB NOT NULL DEFAULT '{}',
    -- Format: {"top1_accuracy": 0.982, "top5_accuracy": 0.997,
    --          "inference_time_ms": 12, "model_size_mb": 45.2}
    is_production           BOOLEAN NOT NULL DEFAULT FALSE,
    is_rollback_available   BOOLEAN NOT NULL DEFAULT TRUE,
    deployed_at             TIMESTAMPTZ,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT uq_model_versions_string UNIQUE (version_string),
    CONSTRAINT fk_model_versions_job
        FOREIGN KEY (training_job_id)
        REFERENCES training_jobs (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- Indexes
CREATE INDEX idx_model_versions_version ON model_versions (version_string);
CREATE INDEX idx_model_versions_production ON model_versions (is_production) WHERE is_production = TRUE;
CREATE INDEX idx_model_versions_rollback ON model_versions (is_rollback_available) WHERE is_rollback_available = TRUE;

-- Comments
COMMENT ON TABLE model_versions IS 'Model registry with deployment and rollback tracking';
COMMENT ON COLUMN model_versions.is_production IS 'Only one model should be production at a time (enforced by application)';

-- Constraint: Only one production model (partial unique index approach)
CREATE UNIQUE INDEX idx_model_versions_one_production
    ON model_versions (is_production)
    WHERE is_production = TRUE;
```

---

## 12. Review & Audit

---

### Table: `review_actions`

**Purpose**: Records of operator review decisions on detection events. Enables feedback loop for model improvement.

**Rationale**: Every operator action on an event is logged. `from_person_id` and `to_person_id` track identity corrections (merging unknown detections to known persons). This data feeds back into training datasets.

```sql
-- =============================================================================
-- TABLE: review_actions
-- =============================================================================

CREATE TABLE review_actions (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_id        UUID NOT NULL,
    reviewer_id     UUID NOT NULL,
    action          review_action_enum NOT NULL,
    from_person_id  UUID,                       -- Original person assignment
    to_person_id    UUID,                       -- Corrected person assignment (for correct_name, merge)
    notes           TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT fk_review_reviewer
        FOREIGN KEY (reviewer_id)
        REFERENCES users (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT fk_review_from_person
        FOREIGN KEY (from_person_id)
        REFERENCES persons (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT fk_review_to_person
        FOREIGN KEY (to_person_id)
        REFERENCES persons (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- Indexes
CREATE INDEX idx_review_actions_event_id ON review_actions (event_id);
CREATE INDEX idx_review_actions_reviewer ON review_actions (reviewer_id);
CREATE INDEX idx_review_actions_created ON review_actions (created_at DESC);

-- Comments
COMMENT ON TABLE review_actions IS 'Operator review decisions on detection events';
```

---

### Table: `audit_logs` (Partitioned: Monthly by `timestamp`)

**Purpose**: Comprehensive audit trail of all user actions. Partitioned monthly for performance and compliance retention.

**Rationale**: Required for security compliance and forensic analysis. Captures who did what, when, and from where. `details` JSONB stores action-specific context.

```sql
-- =============================================================================
-- TABLE: audit_logs (MONTHLY PARTITIONED BY timestamp)
-- =============================================================================

CREATE TABLE audit_logs (
    id              UUID NOT NULL,
    user_id         UUID,                       -- NULL for system/service actions
    action          VARCHAR(100) NOT NULL,      -- login, person_create, alert_acknowledge, etc.
    resource_type   VARCHAR(50) NOT NULL,       -- person, camera, alert, event, system
    resource_id     UUID,                       -- UUID of affected resource
    details         JSONB NOT NULL DEFAULT '{}',
    -- Format varies by action:
    -- login: {"ip": "192.168.1.100", "mfa_used": true, "success": true}
    -- person_create: {"name": "John Doe", "status": "known"}
    -- alert_acknowledge: {"alert_id": "uuid", "previous_status": "pending"}
    ip_address      INET,
    user_agent      TEXT,
    timestamp       TIMESTAMPTZ NOT NULL,

    -- Partition key
    PRIMARY KEY (id, timestamp),

    -- Constraints
    CONSTRAINT fk_audit_user
        FOREIGN KEY (user_id)
        REFERENCES users (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) PARTITION BY RANGE (timestamp);

-- Monthly partitions
CREATE TABLE audit_logs_y2024m01 PARTITION OF audit_logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE audit_logs_y2024m02 PARTITION OF audit_logs
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- ... (additional via pg_partman)
CREATE TABLE audit_logs_default PARTITION OF audit_logs DEFAULT;

-- Indexes
CREATE INDEX idx_audit_logs_user_id ON audit_logs (user_id) WHERE user_id IS NOT NULL;
CREATE INDEX idx_audit_logs_timestamp ON audit_logs (timestamp DESC);
CREATE INDEX idx_audit_logs_resource_type ON audit_logs (resource_type);
CREATE INDEX idx_audit_logs_action ON audit_logs (action);
CREATE INDEX idx_audit_logs_ip ON audit_logs (ip_address);

-- Comments
COMMENT ON TABLE audit_logs IS 'Comprehensive audit trail - partitioned monthly for compliance';
```

---

## 13. System Monitoring

---

### Table: `system_health_logs` (Partitioned: Monthly by `timestamp`, TTL: 90 days)

**Purpose**: System component health metrics for operational monitoring and alerting.

**Rationale**: High-volume time-series data with short retention. The `metrics` JSONB stores component-specific measurements (CPU, memory, GPU utilization, inference latency, queue depths).

```sql
-- =============================================================================
-- TABLE: system_health_logs (MONTHLY PARTITIONED BY timestamp, TTL 90 DAYS)
-- =============================================================================

CREATE TABLE system_health_logs (
    id              UUID NOT NULL,
    component       VARCHAR(50) NOT NULL,       -- detector, recognizer, stream_manager, api, db
    status          device_status_enum NOT NULL,
    metrics         JSONB NOT NULL DEFAULT '{}',
    -- Format varies by component:
    -- detector: {"fps_processed": 25, "inference_time_ms": 15, "gpu_utilization": 0.75}
    -- recognizer: {"faces_detected_per_min": 120, "embedding_time_ms": 8}
    -- stream_manager: {"active_streams": 48, "buffer_health_ms": 45, "dropped_frames": 2}
    -- api: {"requests_per_second": 150, "p99_latency_ms": 45, "error_rate": 0.001}
    -- db: {"connections_active": 25, "query_time_p99_ms": 5, "replication_lag_ms": 0}
    message         TEXT,                       -- Human-readable status message
    timestamp       TIMESTAMPTZ NOT NULL,

    -- Partition key
    PRIMARY KEY (id, timestamp)
) PARTITION BY RANGE (timestamp);

-- Monthly partitions (with 90-day TTL, keep only 3-4 months)
CREATE TABLE health_logs_y2024m01 PARTITION OF system_health_logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE health_logs_y2024m02 PARTITION OF system_health_logs
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE health_logs_y2024m03 PARTITION OF system_health_logs
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE health_logs_y2024m04 PARTITION OF system_health_logs
    FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');
CREATE TABLE health_default PARTITION OF system_health_logs DEFAULT;

-- Indexes
CREATE INDEX idx_health_logs_component ON system_health_logs (component);
CREATE INDEX idx_health_logs_timestamp ON system_health_logs (timestamp DESC);
CREATE INDEX idx_health_logs_status ON system_health_logs (status);

-- Comments
COMMENT ON TABLE system_health_logs IS 'System component health metrics - 90 day TTL';
COMMENT ON COLUMN system_health_logs.metrics IS 'Component-specific performance metrics';
```

---

### Table: `device_connectivity_logs` (Partitioned: Monthly by `timestamp`, TTL: 90 days)

**Purpose**: Tracks DVR and camera connectivity events for availability reporting and troubleshooting.

**Rationale**: Connectivity flapping detection, MTTR calculation, and root cause analysis all depend on this data. `details` JSONB stores event context (error codes, retry counts).

```sql
-- =============================================================================
-- TABLE: device_connectivity_logs (MONTHLY PARTITIONED BY timestamp, TTL 90 DAYS)
-- =============================================================================

CREATE TABLE device_connectivity_logs (
    id              UUID NOT NULL,
    device_type     VARCHAR(20) NOT NULL,       -- dvr, camera, edge
    device_id       UUID NOT NULL,              -- Reference to dvrs.id or cameras.id
    event           connectivity_event_enum NOT NULL,
    details         JSONB NOT NULL DEFAULT '{}',
    -- Format: {"error_code": "ECONNREFUSED", "retry_count": 3,
    --          "previous_event": "connected", "duration_seconds": 1200}
    timestamp       TIMESTAMPTZ NOT NULL,

    -- Partition key
    PRIMARY KEY (id, timestamp)
) PARTITION BY RANGE (timestamp);

-- Monthly partitions (90-day TTL)
CREATE TABLE connectivity_y2024m01 PARTITION OF device_connectivity_logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE connectivity_y2024m02 PARTITION OF device_connectivity_logs
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE connectivity_y2024m03 PARTITION OF device_connectivity_logs
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE connectivity_y2024m04 PARTITION OF device_connectivity_logs
    FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');
CREATE TABLE connectivity_default PARTITION OF device_connectivity_logs DEFAULT;

-- Indexes
CREATE INDEX idx_connectivity_device_type ON device_connectivity_logs (device_type);
CREATE INDEX idx_connectivity_device_id ON device_connectivity_logs (device_id);
CREATE INDEX idx_connectivity_timestamp ON device_connectivity_logs (timestamp DESC);
CREATE INDEX idx_connectivity_event ON device_connectivity_logs (event);

-- Comments
COMMENT ON TABLE device_connectivity_logs IS 'Device connectivity events - 90 day TTL';
```

---

## 14. Views

### Dashboard Statistics View

```sql
-- =============================================================================
-- VIEW: vw_dashboard_stats
-- Purpose: Real-time dashboard metrics
-- =============================================================================

CREATE OR REPLACE VIEW vw_dashboard_stats AS
WITH
    camera_stats AS (
        SELECT
            COUNT(*) AS total_cameras,
            COUNT(*) FILTER (WHERE status = 'online') AS online_cameras,
            COUNT(*) FILTER (WHERE status = 'offline') AS offline_cameras
        FROM cameras
        WHERE is_active = TRUE
    ),
    event_stats_today AS (
        SELECT
            COUNT(*) AS events_today,
            COUNT(*) FILTER (WHERE event_type = 'person_recognized') AS recognized_today,
            COUNT(*) FILTER (WHERE event_type = 'face_detected') AS faces_today,
            COUNT(*) FILTER (WHERE event_type = 'human_detected') AS humans_today,
            COUNT(*) FILTER (WHERE reviewed = FALSE) AS unreviewed_events
        FROM events
        WHERE timestamp >= DATE_TRUNC('day', NOW())
    ),
    alert_stats AS (
        SELECT
            COUNT(*) FILTER (WHERE status = 'pending') AS pending_alerts,
            COUNT(*) FILTER (WHERE status = 'acknowledged') AS acknowledged_alerts,
            COUNT(*) FILTER (WHERE severity = 'critical' AND status IN ('pending', 'sent')) AS critical_alerts
        FROM alerts
        WHERE created_at >= DATE_TRUNC('day', NOW())
    ),
    person_stats AS (
        SELECT
            COUNT(*) AS total_persons,
            COUNT(*) FILTER (WHERE status = 'known') AS known_persons,
            COUNT(*) FILTER (WHERE status = 'unknown') AS unknown_persons,
            COUNT(*) FILTER (WHERE status = 'blacklisted') AS blacklisted_persons,
            COUNT(*) FILTER (WHERE status = 'vip') AS vip_persons
        FROM persons
    ),
    suspicious_stats AS (
        SELECT
            COUNT(*) AS suspicious_today,
            COUNT(*) FILTER (WHERE severity = 'critical') AS critical_suspicious
        FROM suspicious_activity_events
        WHERE timestamp >= DATE_TRUNC('day', NOW())
    )
SELECT
    cs.total_cameras,
    cs.online_cameras,
    cs.offline_cameras,
    ROUND(100.0 * cs.online_cameras / NULLIF(cs.total_cameras, 0), 1) AS camera_uptime_pct,
    es.events_today,
    es.recognized_today,
    es.faces_today,
    es.humans_today,
    es.unreviewed_events,
    als.pending_alerts,
    als.acknowledged_alerts,
    als.critical_alerts,
    ps.total_persons,
    ps.known_persons,
    ps.unknown_persons,
    ps.blacklisted_persons,
    ps.vip_persons,
    ss.suspicious_today,
    ss.critical_suspicious
FROM camera_stats cs
CROSS JOIN event_stats_today es
CROSS JOIN alert_stats als
CROSS JOIN person_stats ps
CROSS JOIN suspicious_stats ss;

COMMENT ON VIEW vw_dashboard_stats IS 'Real-time dashboard statistics snapshot';
```

---

### Recent Events View

```sql
-- =============================================================================
-- VIEW: vw_recent_events
-- Purpose: Recent detection events with enriched camera and person information
-- =============================================================================

CREATE OR REPLACE VIEW vw_recent_events AS
SELECT
    e.id AS event_id,
    e.event_type,
    e.confidence,
    e.bounding_box,
    e.image_path,
    e.timestamp AS event_timestamp,
    e.reviewed,
    e.review_action,
    e.metadata,
    c.id AS camera_id,
    c.name AS camera_name,
    c.location AS camera_location,
    c.resolution,
    d.name AS dvr_name,
    s.name AS site_name,
    p.id AS person_id,
    p.name AS person_name,
    p.status AS person_status,
    p.role AS person_role,
    cl.cluster_label AS cluster_label
FROM events e
LEFT JOIN cameras c ON e.camera_id = c.id
LEFT JOIN dvrs d ON c.dvr_id = d.id
LEFT JOIN sites s ON d.site_id = s.id
LEFT JOIN persons p ON e.person_id = p.id
LEFT JOIN person_clusters cl ON e.cluster_id = cl.id
WHERE e.timestamp >= NOW() - INTERVAL '24 hours'
ORDER BY e.timestamp DESC;

COMMENT ON VIEW vw_recent_events IS 'Last 24 hours of events with camera, site, and person details';
```

---

### Active Alerts View

```sql
-- =============================================================================
-- VIEW: vw_active_alerts
-- Purpose: Pending and acknowledged alerts with full context
-- =============================================================================

CREATE OR REPLACE VIEW vw_active_alerts AS
SELECT
    a.id AS alert_id,
    a.alert_type,
    a.severity,
    a.message,
    a.status,
    a.created_at,
    a.sent_at,
    a.acknowledged_at,
    a.resolved_at,
    ack.username AS acknowledged_by_user,
    res.username AS resolved_by_user,
    c.name AS camera_name,
    c.location AS camera_location,
    s.name AS site_name,
    p.name AS person_name,
    p.status AS person_status,
    ar.name AS rule_name,
    ar.confidence_threshold AS rule_threshold
FROM alerts a
LEFT JOIN cameras c ON a.camera_id = c.id
LEFT JOIN dvrs d ON c.dvr_id = d.id
LEFT JOIN sites s ON d.site_id = s.id
LEFT JOIN persons p ON a.person_id = p.id
LEFT JOIN alert_rules ar ON a.alert_rule_id = ar.id
LEFT JOIN users ack ON a.acknowledged_by = ack.id
LEFT JOIN users res ON a.resolved_by = res.id
WHERE a.status IN ('pending', 'sent', 'acknowledged')
ORDER BY
    CASE a.severity
        WHEN 'critical' THEN 1
        WHEN 'high' THEN 2
        WHEN 'medium' THEN 3
        WHEN 'low' THEN 4
    END,
    a.created_at DESC;

COMMENT ON VIEW vw_active_alerts IS 'All non-resolved alerts with full context for operators';
```

---

### Person Recognition Summary View

```sql
-- =============================================================================
-- VIEW: vw_person_recognition_summary
-- Purpose: Recognition frequency and confidence statistics per person
-- =============================================================================

CREATE OR REPLACE VIEW vw_person_recognition_summary AS
SELECT
    p.id AS person_id,
    p.name,
    p.status,
    p.role,
    p.company,
    COUNT(e.id) AS total_detections,
    COUNT(e.id) FILTER (WHERE e.timestamp >= NOW() - INTERVAL '7 days') AS detections_last_7d,
    COUNT(e.id) FILTER (WHERE e.timestamp >= NOW() - INTERVAL '24 hours') AS detections_last_24h,
    ROUND(AVG(e.confidence)::NUMERIC, 3) AS avg_confidence,
    MIN(e.timestamp) AS first_seen,
    MAX(e.timestamp) AS last_seen,
    COUNT(DISTINCT e.camera_id) AS cameras_seen_on,
    ARRAY_AGG(DISTINCT l.label) FILTER (WHERE l.label IS NOT NULL) AS labels,
    fe.embedding IS NOT NULL AS has_embedding
FROM persons p
LEFT JOIN events e ON p.id = e.person_id AND e.event_type = 'person_recognized'
LEFT JOIN person_labels l ON p.id = l.person_id
LEFT JOIN face_embeddings fe ON p.id = fe.person_id AND fe.is_primary = TRUE
GROUP BY p.id, p.name, p.status, p.role, p.company, fe.embedding
ORDER BY last_seen DESC NULLS LAST;

COMMENT ON VIEW vw_person_recognition_summary IS 'Recognition statistics per person for review and reporting';
```

---

### Unreviewed Unknown Clusters View

```sql
-- =============================================================================
-- VIEW: vw_unknown_clusters_for_review
-- Purpose: Unknown person clusters pending operator review
-- =============================================================================

CREATE OR REPLACE VIEW vw_unknown_clusters_for_review AS
SELECT
    pc.id AS cluster_id,
    pc.cluster_label,
    pc.sample_count,
    pc.first_seen,
    pc.last_seen,
    EXTRACT(EPOCH FROM (pc.last_seen - pc.first_seen)) / 3600 AS span_hours,
    pc.status,
    fe.source_image_path AS representative_face,
    fe.face_quality AS representative_quality,
    COUNT(e.id) AS total_events,
    ARRAY_AGG(DISTINCT c.name) FILTER (WHERE c.name IS NOT NULL) AS seen_on_cameras,
    ARRAY_AGG(DISTINCT s.name) FILTER (WHERE s.name IS NOT NULL) AS seen_at_sites
FROM person_clusters pc
LEFT JOIN face_embeddings fe ON pc.representative_embedding_id = fe.id
LEFT JOIN events e ON pc.id = e.cluster_id
LEFT JOIN cameras c ON e.camera_id = c.id
LEFT JOIN dvrs d ON c.dvr_id = d.id
LEFT JOIN sites s ON d.site_id = s.id
WHERE pc.status = 'active'
GROUP BY pc.id, pc.cluster_label, pc.sample_count, pc.first_seen, pc.last_seen,
         pc.status, fe.source_image_path, fe.face_quality
ORDER BY pc.sample_count DESC, pc.last_seen DESC;

COMMENT ON VIEW vw_unknown_clusters_for_review IS 'Unknown person clusters pending operator identification';
```

---

## 15. Triggers & Functions

### Auto-Update Timestamp Function

```sql
-- =============================================================================
-- FUNCTION: fn_update_updated_at
-- Purpose: Automatically update the updated_at column on row modification
-- =============================================================================

CREATE OR REPLACE FUNCTION fn_update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION fn_update_updated_at() IS 'Trigger function to auto-update updated_at timestamp';
```

---

### Audit Logging Trigger

```sql
-- =============================================================================
-- FUNCTION: fn_audit_log
-- Purpose: Generic audit logging trigger for table changes
-- Usage: CREATE TRIGGER ... AFTER INSERT OR UPDATE OR DELETE ON table
--        FOR EACH ROW EXECUTE FUNCTION fn_audit_log('table_name');
-- =============================================================================

CREATE OR REPLACE FUNCTION fn_audit_log()
RETURNS TRIGGER AS $$
DECLARE
    v_user_id UUID;
    v_action VARCHAR(100);
    v_resource_type VARCHAR(50);
    v_resource_id UUID;
    v_details JSONB;
    v_ip_address INET;
BEGIN
    -- Get current user from session variable (set by application)
    v_user_id := NULLIF(current_setting('app.current_user_id', TRUE), '')::UUID;
    v_ip_address := NULLIF(current_setting('app.client_ip', TRUE), '')::INET;

    v_resource_type := TG_ARGV[0];

    IF TG_OP = 'INSERT' THEN
        v_action := TG_ARGV[0] || '_create';
        v_resource_id := NEW.id;
        v_details := jsonb_build_object('new', to_jsonb(NEW));
    ELSIF TG_OP = 'UPDATE' THEN
        v_action := TG_ARGV[0] || '_update';
        v_resource_id := NEW.id;
        v_details := jsonb_build_object(
            'old', to_jsonb(OLD),
            'new', to_jsonb(NEW),
            'changed_fields', (
                SELECT jsonb_object_agg(key, value)
                FROM jsonb_each(to_jsonb(NEW))
                WHERE to_jsonb(OLD) -> key IS DISTINCT FROM value
            )
        );
    ELSIF TG_OP = 'DELETE' THEN
        v_action := TG_ARGV[0] || '_delete';
        v_resource_id := OLD.id;
        v_details := jsonb_build_object('old', to_jsonb(OLD));
    END IF;

    -- Redact sensitive fields from audit details
    v_details := v_details - '{new.password_hash, new.mfa_secret, new.password_encrypted, new.username_encrypted, new.bot_token_encrypted, new.api_key_encrypted}'::TEXT[];

    INSERT INTO audit_logs (id, user_id, action, resource_type, resource_id, details, ip_address, timestamp)
    VALUES (uuid_generate_v4(), v_user_id, v_action, v_resource_type, v_resource_id, v_details, v_ip_address, NOW());

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

COMMENT ON FUNCTION fn_audit_log() IS 'Generic audit trigger - pass table name as TG_ARGV[0]';

-- Apply audit triggers to key tables
CREATE TRIGGER trg_audit_persons
    AFTER INSERT OR UPDATE OR DELETE ON persons
    FOR EACH ROW EXECUTE FUNCTION fn_audit_log('person');

CREATE TRIGGER trg_audit_cameras
    AFTER INSERT OR UPDATE OR DELETE ON cameras
    FOR EACH ROW EXECUTE FUNCTION fn_audit_log('camera');

CREATE TRIGGER trg_audit_dvrs
    AFTER INSERT OR UPDATE OR DELETE ON dvrs
    FOR EACH ROW EXECUTE FUNCTION fn_audit_log('dvr');

CREATE TRIGGER trg_audit_sites
    AFTER INSERT OR UPDATE OR DELETE ON sites
    FOR EACH ROW EXECUTE FUNCTION fn_audit_log('site');

CREATE TRIGGER trg_audit_users
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION fn_audit_log('user');

CREATE TRIGGER trg_audit_alerts
    AFTER INSERT OR UPDATE OR DELETE ON alerts
    FOR EACH ROW EXECUTE FUNCTION fn_audit_log('alert');

CREATE TRIGGER trg_audit_alert_rules
    AFTER INSERT OR UPDATE OR DELETE ON alert_rules
    FOR EACH ROW EXECUTE FUNCTION fn_audit_log('alert_rule');

CREATE TRIGGER trg_audit_watchlists
    AFTER INSERT OR UPDATE OR DELETE ON watchlists
    FOR EACH ROW EXECUTE FUNCTION fn_audit_log('watchlist');
```

---

### Event Alert Generation Trigger

```sql
-- =============================================================================
-- FUNCTION: fn_generate_alert_from_event
-- Purpose: Automatically generate alerts when events match active alert rules
-- =============================================================================

CREATE OR REPLACE FUNCTION fn_generate_alert_from_event()
RETURNS TRIGGER AS $$
DECLARE
    v_rule RECORD;
    v_severity severity_enum;
    v_message TEXT;
    v_camera_name VARCHAR(100);
    v_person_name VARCHAR(200);
BEGIN
    -- Find matching alert rules
    FOR v_rule IN
        SELECT *
        FROM alert_rules
        WHERE is_active = TRUE
          AND (camera_id IS NULL OR camera_id = NEW.camera_id)
          AND (event_type IS NULL OR event_type = NEW.event_type)
          AND (person_id IS NULL OR person_id = NEW.person_id)
          AND confidence_threshold <= NEW.confidence
    LOOP
        -- Get camera name
        SELECT name INTO v_camera_name FROM cameras WHERE id = NEW.camera_id;

        -- Get person name if recognized
        SELECT name INTO v_person_name FROM persons WHERE id = NEW.person_id;

        -- Build alert message
        v_message := format('%s detected on camera "%s" at %s',
            COALESCE(v_person_name, 'Unknown person'),
            v_camera_name,
            NEW.timestamp::TEXT
        );

        -- Determine severity
        IF v_rule.severity IS NOT NULL THEN
            v_severity := v_rule.severity;
        ELSIF NEW.person_id IS NOT NULL THEN
            SELECT CASE status
                WHEN 'blacklisted' THEN 'critical'::severity_enum
                WHEN 'vip' THEN 'medium'::severity_enum
                ELSE 'low'::severity_enum
            END INTO v_severity FROM persons WHERE id = NEW.person_id;
        ELSE
            v_severity := 'low';
        END IF;

        -- Insert alert
        INSERT INTO alerts (event_id, alert_rule_id, person_id, camera_id,
                           alert_type, severity, message, status, created_at)
        VALUES (NEW.id, v_rule.id, NEW.person_id, NEW.camera_id,
                NEW.event_type::TEXT, v_severity, v_message, 'pending', NOW());
    END LOOP;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION fn_generate_alert_from_event() IS 'Auto-generate alerts from events matching active rules';

CREATE TRIGGER trg_event_generate_alert
    AFTER INSERT ON events
    FOR EACH ROW
    EXECUTE FUNCTION fn_generate_alert_from_event();
```

---

### Suspicious Activity Alert Trigger

```sql
-- =============================================================================
-- FUNCTION: fn_generate_alert_from_suspicious_activity
-- Purpose: Generate alerts for suspicious/anomalous activity detections
-- =============================================================================

CREATE OR REPLACE FUNCTION fn_generate_alert_from_suspicious_activity()
RETURNS TRIGGER AS $$
DECLARE
    v_camera_name VARCHAR(100);
    v_zone_name VARCHAR(100);
    v_message TEXT;
BEGIN
    SELECT name INTO v_camera_name FROM cameras WHERE id = NEW.camera_id;
    SELECT name INTO v_zone_name FROM camera_zones WHERE id = NEW.zone_id;

    v_message := format('%s detected on "%s"%s at %s. Confidence: %s%%. Severity: %s',
        NEW.activity_type::TEXT,
        v_camera_name,
        CASE WHEN v_zone_name IS NOT NULL THEN ' in zone "' || v_zone_name || '"' ELSE '' END,
        NEW.timestamp::TEXT,
        ROUND(NEW.confidence * 100, 1),
        NEW.severity::TEXT
    );

    INSERT INTO alerts (event_id, person_id, camera_id, alert_type, severity,
                       message, status, created_at)
    VALUES (NEW.event_id, NULL, NEW.camera_id,
            'suspicious_activity_' || NEW.activity_type::TEXT,
            NEW.severity, v_message, 'pending', NOW());

    -- Mark alert as sent in the suspicious activity record
    NEW.alert_sent = TRUE;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION fn_generate_alert_from_suspicious_activity() IS 'Auto-generate alerts for suspicious activity events';

CREATE TRIGGER trg_suspicious_generate_alert
    AFTER INSERT ON suspicious_activity_events
    FOR EACH ROW
    WHEN (NEW.alert_sent = FALSE)
    EXECUTE FUNCTION fn_generate_alert_from_suspicious_activity();
```

---

### Partition Management Function

```sql
-- =============================================================================
-- FUNCTION: fn_create_monthly_partition
-- Purpose: Create a monthly partition for partitioned tables
-- =============================================================================

CREATE OR REPLACE FUNCTION fn_create_monthly_partition(
    p_table_name TEXT,
    p_year INTEGER,
    p_month INTEGER
)
RETURNS TEXT AS $$
DECLARE
    v_partition_name TEXT;
    v_start_date TEXT;
    v_end_date TEXT;
    v_sql TEXT;
BEGIN
    v_partition_name := p_table_name || '_y' || p_year || 'm' || LPAD(p_month::TEXT, 2, '0');
    v_start_date := p_year || '-' || LPAD(p_month::TEXT, 2, '0') || '-01';
    v_end_date := CASE
        WHEN p_month = 12 THEN (p_year + 1) || '-01-01'
        ELSE p_year || '-' || LPAD((p_month + 1)::TEXT, 2, '0') || '-01'
    END;

    v_sql := format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
        v_partition_name, p_table_name, v_start_date, v_end_date
    );

    EXECUTE v_sql;
    RETURN v_partition_name;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION fn_create_monthly_partition(TEXT, INTEGER, INTEGER) IS 'Create a monthly range partition for the specified table';
```

---

### Media Retention Cleanup Function

```sql
-- =============================================================================
-- FUNCTION: fn_cleanup_expired_media
-- Purpose: Remove references to media past retention period
-- Schedule: Run daily via pg_cron
-- =============================================================================

CREATE OR REPLACE FUNCTION fn_cleanup_expired_media()
RETURNS INTEGER AS $$
DECLARE
    v_deleted_count INTEGER := 0;
BEGIN
    -- Mark expired media (actual file deletion handled by application/Object Storage lifecycle)
    WITH expired AS (
        SELECT id, storage_path
        FROM media_files
        WHERE retention_until < NOW() - INTERVAL '7 days'
    ),
    deleted_media AS (
        DELETE FROM media_files
        WHERE id IN (SELECT id FROM expired)
        RETURNING id
    ),
    deleted_clips AS (
        DELETE FROM video_clips
        WHERE media_file_id IN (SELECT id FROM deleted_media)
        RETURNING media_file_id
    )
    SELECT COUNT(*) INTO v_deleted_count FROM deleted_media;

    -- Log cleanup action
    INSERT INTO audit_logs (id, action, resource_type, resource_id, details, timestamp)
    VALUES (
        uuid_generate_v4(),
        'media_cleanup',
        'system',
        NULL,
        jsonb_build_object('expired_media_deleted', v_deleted_count),
        NOW()
    );

    RETURN v_deleted_count;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION fn_cleanup_expired_media() IS 'Daily cleanup of expired media references - schedule with pg_cron';
```

---

## 16. ER Diagram

### Entity Relationship Diagram (Text-Based)

```
+==========================================================================================+
|                         AI SURVEILLANCE PLATFORM - ENTITY RELATIONSHIPS                    |
+==========================================================================================+

  [roles] 1:N [users]
  |              |
  |              | 1:N
  |         [audit_logs] (partitioned monthly)
  |
  [sites] 1:N [dvrs] 1:N [cameras] 1:N [camera_zones]
  |              |         |    |         |
  |              |         |    | 1:N     | 1:N
  |              |         |    |         |
  |              |         |    |    [events]---------+ (partitioned monthly)
  |              |         |    |    | (person_id,    |
  |              |         |    |    |  cluster_id)    |
  |              |         |    |    |                 |
  |              |         |    | [suspicious_activity_events] (partitioned monthly)
  |              |         |    |
  |              |         |    +--< [alerts] <--+ [alert_rules]
  |              |         |    |    |    |      |    |
  |              |         |    |    |    |      | [notification_channels]
  |              |         |    |    |    |      |    | 1:1 [telegram_configs]
  |              |         |    |    |    |      |    | 1:1 [whatsapp_configs]
  |              |         |    |    |    |      |
  |              |         |    |    | [media_files]
  |              |         |    |    |    | 1:1 [video_clips]
  |              |         |    |    |
  |              |         |    | [review_actions]
  |              |         |    |
  +--------------+---------+----+------------------+
                 |         |
            [persons] 1:N [face_embeddings]
                 |         ^     | (HNSW index)
                 |         |     |
                 |    [person_clusters] (merged_into_cluster_id self-ref)
                 |
                 | 1:N [person_aliases]
                 | 1:N [person_labels]
                 |
                 | M:N [watchlist_entries] N:1 [watchlists]
                 |
                 | 1:N [training_datasets] --< [training_jobs] 1:1 [model_versions]
                 |
                 +--< referenced by events, alerts, alert_rules

  [system_health_logs] (partitioned monthly, 90d TTL)
  [device_connectivity_logs] (partitioned monthly, 90d TTL)

+==========================================================================================+
|                                  KEY RELATIONSHIPS                                         |
+==========================================================================================+

  1. users.role_id -> roles.id                    (RBAC)
  2. dvrs.site_id -> sites.id                     (Site hierarchy)
  3. cameras.dvr_id -> dvrs.id                    (Device hierarchy)
  4. camera_zones.camera_id -> cameras.id         (Zone definition)
  5. face_embeddings.person_id -> persons.id      (Face storage)
  6. persons.primary_face_embedding_id -> face_embeddings.id  (Primary face)
  7. events.camera_id -> cameras.id               (Event source)
  8. events.person_id -> persons.id               (Known person event)
  9. events.cluster_id -> person_clusters.id      (Unknown person event)
  10. person_clusters.representative_embedding_id -> face_embeddings.id  (Cluster rep)
  11. person_clusters.merged_into_cluster_id -> person_clusters.id  (Cluster merge)
  12. alerts.camera_id -> cameras.id              (Alert source)
  13. alerts.alert_rule_id -> alert_rules.id      (Rule match)
  14. alerts.person_id -> persons.id              (Alert subject)
  15. alert_rules.camera_id -> cameras.id         (Rule scope)
  16. alert_rules.zone_id -> camera_zones.id      (Rule zone)
  17. suspicious_activity_events.camera_id -> cameras.id  (Anomaly source)
  18. suspicious_activity_events.zone_id -> camera_zones.id  (Zone breach)
  19. media_files.camera_id -> cameras.id         (Media source)
  20. video_clips.media_file_id -> media_files.id (Clip storage)
  21. training_jobs.dataset_id -> training_datasets.id  (Training pipeline)
  22. model_versions.training_job_id -> training_jobs.id  (Model registry)
  23. watchlist_entries.watchlist_id -> watchlists.id  (Watchlist membership)
  24. watchlist_entries.person_id -> persons.id   (Watched person)
  25. telegram_configs.channel_id -> notification_channels.id  (TG config)
  26. whatsapp_configs.channel_id -> notification_channels.id  (WA config)

+==========================================================================================+
```

---

## 17. Retention Policies

### Summary Table

| Table | Partitioning | Retention | Archive Strategy |
|-------|-------------|-----------|------------------|
| `events` | Monthly | 12 months | Compress after 3 months, drop after 12 |
| `suspicious_activity_events` | Monthly | 24 months | Compress after 6 months, archive to cold storage at 12 months |
| `audit_logs` | Monthly | 36 months | Required for compliance, archive at 36 months |
| `system_health_logs` | Monthly | 90 days | Drop partitions after 90 days |
| `device_connectivity_logs` | Monthly | 90 days | Drop partitions after 90 days |
| `media_files` | N/A | Configurable per camera | Delete object storage files when record deleted |
| `video_clips` | N/A | Linked to media_files | Cascade delete with media_files |
| `alerts` | N/A | Indefinite | Lightweight, keep for historical reference |
| `review_actions` | N/A | Indefinite | Training feedback data, keep permanently |
| All other tables | N/A | Indefinite | Core configuration data |

### Automated Retention Implementation

```sql
-- =============================================================================
-- RETENTION POLICY IMPLEMENTATION
-- =============================================================================

-- 1. Events: 12-month retention with pg_partman
-- Install pg_partman and configure:
SELECT partman.create_parent('public.events', 'timestamp', 'native', 'monthly',
    p_premake := 2, p_start_partition := '2024-01-01');
SELECT partman.create_partition_id('public.events', 'retention', '12 month');

-- 2. Suspicious Activity: 24-month retention
SELECT partman.create_parent('public.suspicious_activity_events', 'timestamp', 'native', 'monthly',
    p_premake := 2);

-- 3. Audit Logs: 36-month retention (compliance requirement)
SELECT partman.create_parent('public.audit_logs', 'timestamp', 'native', 'monthly',
    p_premake := 2);

-- 4. System Health Logs: 90-day TTL
-- Run via cron job daily:
-- psql -c "SELECT partman.run_maintenance('public.system_health_logs');"

-- 5. Device Connectivity Logs: 90-day TTL
-- Same approach as system_health_logs

-- 6. Media file cleanup (run daily)
-- SELECT fn_cleanup_expired_media();

-- Manual partition cleanup for tables without pg_partman:
CREATE OR REPLACE FUNCTION fn_drop_old_partitions(
    p_table_name TEXT,
    p_retention_interval INTERVAL
)
RETURNS INTEGER AS $$
DECLARE
    v_partition RECORD;
    v_dropped INTEGER := 0;
BEGIN
    FOR v_partition IN
        SELECT inhrelid::regclass::TEXT AS partition_name
        FROM pg_inherits
        WHERE inhparent = p_table_name::regclass
          AND inhrelid::regclass::TEXT LIKE p_table_name || '\_y%'
          AND pg_catalog.pg_get_expr(c.relpartbound, c.oid) <
              format('FOR VALUES FROM (%L)', NOW() - p_retention_interval)
        FROM pg_class c
        WHERE c.oid = inhrelid
    LOOP
        EXECUTE format('DROP TABLE IF EXISTS %I', v_partition.partition_name);
        v_dropped := v_dropped + 1;
    END LOOP;
    RETURN v_dropped;
END;
$$ LANGUAGE plpgsql;
```

---

## 18. Row-Level Security

### RLS Policies

```sql
-- =============================================================================
-- ROW-LEVEL SECURITY (RLS) POLICIES
-- =============================================================================

-- Enable RLS on key tables
ALTER TABLE events ENABLE ROW LEVEL SECURITY;
ALTER TABLE suspicious_activity_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE alerts ENABLE ROW LEVEL SECURITY;
ALTER TABLE persons ENABLE ROW LEVEL SECURITY;
ALTER TABLE media_files ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;

-- Force RLS for table owners too
ALTER TABLE events FORCE ROW LEVEL SECURITY;
ALTER TABLE alerts FORCE ROW LEVEL SECURITY;

-- Policy: Users can only see events from cameras at their assigned sites
-- (Assumes a user_site_assignments junction table exists)
CREATE POLICY events_site_isolation ON events
    FOR ALL
    TO PUBLIC
    USING (
        camera_id IN (
            SELECT c.id FROM cameras c
            JOIN dvrs d ON c.dvr_id = d.id
            JOIN user_site_assignments usa ON d.site_id = usa.site_id
            WHERE usa.user_id = current_setting('app.current_user_id', TRUE)::UUID
        )
        OR current_setting('app.user_role', TRUE) = 'super_admin'
    );

-- Policy: Users can only see alerts for their accessible cameras
CREATE POLICY alerts_site_isolation ON alerts
    FOR ALL
    TO PUBLIC
    USING (
        camera_id IN (
            SELECT c.id FROM cameras c
            JOIN dvrs d ON c.dvr_id = d.id
            JOIN user_site_assignments usa ON d.site_id = usa.site_id
            WHERE usa.user_id = current_setting('app.current_user_id', TRUE)::UUID
        )
        OR current_setting('app.user_role', TRUE) = 'super_admin'
    );

-- Policy: All authenticated users can view persons (with restrictions on blacklisted details)
CREATE POLICY persons_read_access ON persons
    FOR SELECT
    TO PUBLIC
    USING (TRUE);

CREATE POLICY persons_write_access ON persons
    FOR ALL
    TO PUBLIC
    USING (
        current_setting('app.user_role', TRUE) IN ('super_admin', 'admin', 'operator')
    );

-- Policy: Users can only see media from their accessible cameras
CREATE POLICY media_site_isolation ON media_files
    FOR ALL
    TO PUBLIC
    USING (
        camera_id IN (
            SELECT c.id FROM cameras c
            JOIN dvrs d ON c.dvr_id = d.id
            JOIN user_site_assignments usa ON d.site_id = usa.site_id
            WHERE usa.user_id = current_setting('app.current_user_id', TRUE)::UUID
        )
        OR current_setting('app.user_role', TRUE) = 'super_admin'
    );

-- Policy: Users can only see their own audit logs (or all for admins)
CREATE POLICY audit_logs_user_access ON audit_logs
    FOR SELECT
    TO PUBLIC
    USING (
        user_id = current_setting('app.current_user_id', TRUE)::UUID
        OR current_setting('app.user_role', TRUE) IN ('super_admin', 'admin')
    );

-- =============================================================================
-- SUPPLEMENTARY: User-Site Assignment Table (for RLS policies)
-- =============================================================================

CREATE TABLE user_site_assignments (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id     UUID NOT NULL,
    site_id     UUID NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_user_site UNIQUE (user_id, site_id),
    CONSTRAINT fk_usa_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_usa_site FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
);

CREATE INDEX idx_usa_user ON user_site_assignments (user_id);
CREATE INDEX idx_usa_site ON user_site_assignments (site_id);

COMMENT ON TABLE user_site_assignments IS 'Junction table for user-to-site access control (RLS)';
```

---

## 19. Operational Notes

### Database Setup Order

Execute the DDL in this order:

1. **Extensions** — Install pgvector, pgcrypto, uuid-ossp, pg_partman
2. **Custom Types** — Create all ENUM types
3. **Independent Tables** — roles, sites, users (after roles)
4. **Infrastructure** — dvrs, cameras, camera_zones
5. **Persons** — persons, person_aliases, person_labels
6. **Face Recognition** — face_embeddings, person_clusters
7. **Add Deferred FK** — persons.primary_face_embedding_id
8. **Events** — events, suspicious_activity_events (with partitions)
9. **Alerts** — alert_rules, alerts
10. **Watchlists** — watchlists, watchlist_entries
11. **Notifications** — notification_channels, telegram_configs, whatsapp_configs
12. **Media** — media_files, video_clips
13. **ML Pipeline** — training_datasets, training_jobs, model_versions
14. **Review & Audit** — review_actions, audit_logs (with partitions)
15. **System Logs** — system_health_logs, device_connectivity_logs (with partitions)
16. **RLS** — user_site_assignments
17. **Views** — All views
18. **Triggers & Functions** — All trigger functions and trigger bindings

### Performance Considerations

| Component | Strategy |
|-----------|----------|
| Face similarity search | HNSW index with ef_search=64 for ~99% recall |
| Event time-range queries | Monthly partitioning + timestamp DESC index |
| Media cleanup | Batch deletion in chunks of 10,000 rows |
| Audit log writes | Async insert via application queue to avoid blocking |
| Connection pooling | PgBouncer with transaction pool mode, max 100 connections |
| Embedding inserts | Batch insert 100+ embeddings per transaction |

### pgvector Query Patterns

```sql
-- Find similar faces (cosine similarity)
SELECT person_id, embedding <=> query_embedding AS distance
FROM face_embeddings
WHERE model_version = '2.3.1'
ORDER BY embedding <=> query_embedding
LIMIT 10;

-- Set HNSW search quality (trade speed for accuracy)
SET hnsw.ef_search = 128;  -- Higher = better recall, slower

-- Find exact matches within threshold
SELECT person_id, 1 - (embedding <=> query_embedding) AS similarity
FROM face_embeddings
WHERE embedding <=> query_embedding < 0.3   -- Cosine distance threshold
  AND model_version = '2.3.1'
ORDER BY embedding <=> query_embedding
LIMIT 5;

-- Update cluster representative after new samples
UPDATE person_clusters
SET representative_embedding_id = (
    SELECT id FROM face_embeddings
    WHERE person_id = cluster_person_id
    ORDER BY face_quality DESC, confidence DESC
    LIMIT 1
)
WHERE id = target_cluster_id;
```

### Security Checklist

- [x] All passwords encrypted with bcrypt (cost >= 12)
- [x] All API tokens encrypted at rest with AES-256
- [x] DVR credentials encrypted at rest
- [x] MFA secrets encrypted at rest
- [x] Audit logging on all sensitive tables
- [x] Row-level security on events, alerts, media
- [x] Role-based access control with permissions matrix
- [x] Sensitive fields redacted from audit logs
- [x] Foreign key constraints prevent orphaned data
- [x] Partitioning prevents unbounded table growth
- [x] Retention policies enforce data lifecycle
- [x] pgvector indexes use appropriate dimensions (512)
- [x] Prepared statements required for all application queries
- [x] Connection encryption (SSL/TLS) enforced

### Maintenance Procedures

```sql
-- Daily: Create upcoming partitions
SELECT partman.run_maintenance();

-- Daily: Clean expired media
SELECT fn_cleanup_expired_media();

-- Weekly: Analyze partitioned tables
ANALYZE events;
ANALYZE suspicious_activity_events;
ANALYZE face_embeddings;

-- Weekly: Vacuum old partitions
VACUUM ANALYZE events_y2024m01;

-- Monthly: Review and archive old audit logs
-- (Export to cold storage before dropping)

-- Quarterly: Reindex HNSW if recall degrades
REINDEX INDEX idx_embeddings_hnsw;

-- Quarterly: Refresh materialized views if created
```

---

## Appendix: Complete Table Count

| # | Table Name | Partitioned | Rows (est.) |
|---|-----------|-------------|-------------|
| 1 | `roles` | No | ~10 |
| 2 | `users` | No | ~100 |
| 3 | `permissions_matrix` | No | ~200 |
| 4 | `sites` | No | ~50 |
| 5 | `dvrs` | No | ~200 |
| 6 | `cameras` | No | ~2,000 |
| 7 | `camera_zones` | No | ~5,000 |
| 8 | `persons` | No | ~50,000 |
| 9 | `person_aliases` | No | ~20,000 |
| 10 | `person_labels` | No | ~75,000 |
| 11 | `face_embeddings` | No | ~500,000 |
| 12 | `person_clusters` | No | ~100,000 |
| 13 | `events` | **Monthly** | ~100M/year |
| 14 | `suspicious_activity_events` | **Monthly** | ~5M/year |
| 15 | `alert_rules` | No | ~500 |
| 16 | `alerts` | No | ~2M/year |
| 17 | `watchlists` | No | ~100 |
| 18 | `watchlist_entries` | No | ~10,000 |
| 19 | `notification_channels` | No | ~50 |
| 20 | `telegram_configs` | No | ~25 |
| 21 | `whatsapp_configs` | No | ~25 |
| 22 | `media_files` | No | ~50M/year |
| 23 | `video_clips` | No | ~10M/year |
| 24 | `training_datasets` | No | ~100 |
| 25 | `training_jobs` | No | ~500 |
| 26 | `model_versions` | No | ~200 |
| 27 | `review_actions` | No | ~2M/year |
| 28 | `audit_logs` | **Monthly** | ~10M/year |
| 29 | `system_health_logs` | **Monthly** | ~50M/year |
| 30 | `device_connectivity_logs` | **Monthly** | ~20M/year |
| 31 | `user_site_assignments` | No | ~500 |
| - | `vw_dashboard_stats` | View | - |
| - | `vw_recent_events` | View | - |
| - | `vw_active_alerts` | View | - |
| - | `vw_person_recognition_summary` | View | - |
| - | `vw_unknown_clusters_for_review` | View | - |

**Total: 31 tables + 5 views + 8 functions/triggers**

---

*Document Version: 1.0*
*Last Updated: 2024*
*Database: PostgreSQL 15+ with pgvector 0.5+*