Database Enums

Purpose

This page documents the PostgreSQL enum types defined in the database schema. These enums provide controlled vocabularies and ensure data consistency across related tables.

Enum Definitions

boundary_scope

Defines the scope of boundary detection operations.

Values:

• single_signal - Boundary detection within a single signal type
• multi_signal - Boundary detection across multiple related signals
• all_signals - Boundary detection across all available signals

Used in:

• boundary_detections.boundaryScope

detection_method

Specifies the algorithmic method used for boundary detection.

Values:

• episodic_rule - Rule-based detection for episodic events
• ambient_change - Statistical change point detection in ambient data
• merged - Combination of multiple detection methods
• speed_threshold - Detection based on rate-of-change thresholds
• gap_fill - Detection and filling of data continuity gaps

Used in:

• boundary_detections.detectionMethod

detection_status

Tracks the execution status of detection processes.

Values:

• pending - Detection is scheduled but not yet started
• completed - Detection finished successfully
• failed - Detection encountered errors and failed

Used in:

• boundary_detection_runs.status

ingestion_status

Tracks the execution status of data ingestion processes.

Values:

• pending - Ingestion is scheduled but not yet started
• running - Ingestion is currently in progress
• completed - Ingestion finished successfully
• failed - Ingestion encountered errors and failed
• cancelled - Ingestion was cancelled before completion

Used in:

• ingestion_runs.status

Usage Examples

Query by enum values

-- Find all multi-signal boundary detections
SELECT * FROM boundary_detections 
WHERE boundaryScope = 'multi_signal';

-- Get failed ingestion runs
SELECT * FROM ingestion_runs 
WHERE status = 'failed';

Count by enum distribution

-- Analyze detection method usage
SELECT detectionMethod, COUNT(*) 
FROM boundary_detections 
GROUP BY detectionMethod;

-- Check ingestion status distribution
SELECT status, COUNT(*) 
FROM ingestion_runs 
GROUP BY status;

Filter by multiple enum values

-- Find completed or running ingestions
SELECT * FROM ingestion_runs 
WHERE status IN ('completed', 'running');

-- Get ambient change and merged detections
SELECT * FROM boundary_detections 
WHERE detectionMethod IN ('ambient_change', 'merged');

Best Practices

1. Always use enum values directly - Don’t store enum values as strings in application code
2. Handle enum expansion - When adding new enum values, ensure backward compatibility
3. Validate in application layer - Check enum values before database operations
4. Use meaningful names - Enum values should be self-documenting and consistent
5. Consider ordering - Enum values are ordered by definition order, which affects sorting