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
- Extension Setup
- Users & RBAC
- Site Infrastructure
- Person Management
- Face Recognition
- Events & Detections
- Alerts & Rules
- Watchlists
- Notifications
- Media Storage
- ML Training Pipeline
- Review & Audit
- System Monitoring
- Views
- Triggers & Functions
- ER Diagram
- Retention Policies
- Row-Level Security
- Operational Notes
1. Extension Setup
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.).
-- =============================================================================
-- 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).
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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").
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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."
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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).
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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.
-- =============================================================================
-- 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).
-- =============================================================================
-- 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).
-- =============================================================================
-- 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
-- =============================================================================
-- 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
-- =============================================================================
-- 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
-- =============================================================================
-- 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
-- =============================================================================
-- 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
-- =============================================================================
-- 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
-- =============================================================================
-- 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
-- =============================================================================
-- 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
-- =============================================================================
-- 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
-- =============================================================================
-- 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
-- =============================================================================
-- 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
-- =============================================================================
-- 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
-- =============================================================================
-- 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
-- =============================================================================
-- 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:
- Extensions — Install pgvector, pgcrypto, uuid-ossp, pg_partman
- Custom Types — Create all ENUM types
- Independent Tables — roles, sites, users (after roles)
- Infrastructure — dvrs, cameras, camera_zones
- Persons — persons, person_aliases, person_labels
- Face Recognition — face_embeddings, person_clusters
- Add Deferred FK — persons.primary_face_embedding_id
- Events — events, suspicious_activity_events (with partitions)
- Alerts — alert_rules, alerts
- Watchlists — watchlists, watchlist_entries
- Notifications — notification_channels, telegram_configs, whatsapp_configs
- Media — media_files, video_clips
- ML Pipeline — training_datasets, training_jobs, model_versions
- Review & Audit — review_actions, audit_logs (with partitions)
- System Logs — system_health_logs, device_connectivity_logs (with partitions)
- RLS — user_site_assignments
- Views — All views
- 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
-- 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
- All passwords encrypted with bcrypt (cost >= 12)
- All API tokens encrypted at rest with AES-256
- DVR credentials encrypted at rest
- MFA secrets encrypted at rest
- Audit logging on all sensitive tables
- Row-level security on events, alerts, media
- Role-based access control with permissions matrix
- Sensitive fields redacted from audit logs
- Foreign key constraints prevent orphaned data
- Partitioning prevents unbounded table growth
- Retention policies enforce data lifecycle
- pgvector indexes use appropriate dimensions (512)
- Prepared statements required for all application queries
- Connection encryption (SSL/TLS) enforced
Maintenance Procedures
-- 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+