Docs are not available for mobile use. Please use a desktop computer to view the documentation.

Ambient Signals

Continuous sensor and metric data points with geospatial support

Purpose

The ambient_signals table stores continuous, time-series data points from various sensors and metrics. This includes data like heart rate, step count, temperature, location, and other continuously monitored values that change over time.

Schema Definition

Field	Type	Description	Constraints
`id`	uuid	Unique data point identifier	Primary key, auto-generated
`userId`	uuid	Data owner	Foreign key to users.id
`signalId`	uuid	Associated signal definition	Foreign key to signals.id
`sourceName`	varchar	Data source name	Foreign key to sources.name
`signalName`	varchar	Signal type name	Not null
`signalValue`	varchar	Measured value	Not null
`timestamp`	timestamptz	When measurement was taken	Not null
`confidence`	real	Data confidence score (0-1)	Not null
`coordinates`	geometry(point)	Geospatial location	Optional
`sourceEventId`	uuid	Source system event ID	Not null
`sourceMetadata`	json	Additional source metadata	Optional
`createdAt`	timestamptz	Record creation timestamp	Auto-generated
`updatedAt`	timestamptz	Last update timestamp	Auto-updated

Relationships

References:

users.id via userId - Data owner (cascade delete)
signals.id via signalId - Signal definition (cascade delete)
sources.name via sourceName - Data source (restrict delete)

Indexes

idx_ambient_signals_user_timestamp - B-tree index on (userId, timestamp) for time-series queries
idx_ambient_signals_signal_name - B-tree index on signalName for signal filtering
idx_ambient_signals_source_event - B-tree index on sourceEventId for deduplication
idx_ambient_signals_coordinates - GiST index on coordinates for geospatial queries

Constraints

ambient_signals_confidence_check - Confidence must be between 0.0 and 1.0
unique_source_signal - Unique constraint on (sourceName, signalName, sourceEventId) to prevent duplicates

Usage Examples

Get recent ambient data for a user

SELECT signalName, signalValue, timestamp, confidence
FROM ambient_signals 
WHERE userId = 'user-uuid' 
  AND timestamp >= NOW() - INTERVAL '24 hours'
ORDER BY timestamp DESC;

Find ambient signals by location

SELECT signalName, signalValue, timestamp, 
       ST_X(coordinates) as longitude, 
       ST_Y(coordinates) as latitude
FROM ambient_signals 
WHERE userId = 'user-uuid'
  AND coordinates IS NOT NULL
  AND ST_DWithin(coordinates, ST_Point(-122.4194, 37.7749), 1000); -- Within 1km of SF

Get signal value trends over time

SELECT 
  DATE_TRUNC('hour', timestamp) as hour,
  signalName,
  AVG(signalValue::float) as avg_value,
  COUNT(*) as data_points
FROM ambient_signals 
WHERE userId = 'user-uuid' 
  AND signalName = 'heart_rate'
  AND timestamp >= NOW() - INTERVAL '7 days'
GROUP BY hour, signalName
ORDER BY hour;

Find high-confidence recent data

SELECT signalName, signalValue, timestamp, confidence
FROM ambient_signals 
WHERE userId = 'user-uuid'
  AND confidence >= 0.8
  AND timestamp >= NOW() - INTERVAL '1 hour'
ORDER BY confidence DESC, timestamp DESC;

Check for duplicate source events

SELECT sourceName, signalName, sourceEventId, COUNT(*)
FROM ambient_signals 
GROUP BY sourceName, signalName, sourceEventId
HAVING COUNT(*) > 1;