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

Episodic Signals

Event-based signals with structured semantic metadata

Purpose

The episodic_signals table stores event-based data that has defined start and end times, along with rich semantic metadata. These signals represent discrete events or activities with structured information about what, where, who, when, how, and why.

Schema Definition

Field	Type	Description	Constraints
`id`	uuid	Unique event identifier	Primary key, auto-generated
`userId`	uuid	Event 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
`startTimestamp`	timestamptz	Event start time	Not null
`endTimestamp`	timestamptz	Event end time	Not null
`summary`	text	Brief event description	Optional
`confidence`	real	Event detection confidence (0-1)	Not null
`whatIds`	uuid[]	Referenced entity IDs for “what”	Optional
`whereIds`	uuid[]	Referenced entity IDs for “where”	Optional
`whoIds`	uuid[]	Referenced entity IDs for “who”	Optional
`whenIds`	uuid[]	Referenced entity IDs for “when”	Optional
`howIds`	uuid[]	Referenced entity IDs for “how”	Optional
`whyIds`	uuid[]	Referenced entity IDs for “why”	Optional
`targetIds`	uuid[]	Referenced entity IDs for targets	Optional
`whatText`	text[]	Textual “what” descriptions	Optional
`whereText`	text[]	Textual “where” descriptions	Optional
`whoText`	text[]	Textual “who” descriptions	Optional
`whenText`	text[]	Textual “when” descriptions	Optional
`howText`	text[]	Textual “how” descriptions	Optional
`whyText`	text[]	Textual “why” descriptions	Optional
`targetText`	text[]	Textual target descriptions	Optional
`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 - Event owner (cascade delete)
signals.id via signalId - Signal definition (cascade delete)
sources.name via sourceName - Data source (restrict delete)

Indexes

idx_episodic_signals_user_timeline - B-tree index on (userId, startTimestamp, endTimestamp) for timeline queries

Constraints

episodic_signals_confidence_check - Confidence must be between 0.0 and 1.0
episodic_signals_timestamp_check - End timestamp must be >= start timestamp

Usage Examples

Get recent episodic events for a user

SELECT summary, startTimestamp, endTimestamp, confidence
FROM episodic_signals 
WHERE userId = 'user-uuid' 
  AND startTimestamp >= NOW() - INTERVAL '7 days'
ORDER BY startTimestamp DESC;

Find events by duration

SELECT summary, 
       startTimestamp, 
       endTimestamp,
       EXTRACT(EPOCH FROM (endTimestamp - startTimestamp))/60 as duration_minutes
FROM episodic_signals 
WHERE userId = 'user-uuid'
  AND (endTimestamp - startTimestamp) > INTERVAL '1 hour'
ORDER BY duration_minutes DESC;

Search events by semantic content

SELECT summary, startTimestamp, whatText, whereText
FROM episodic_signals 
WHERE userId = 'user-uuid'
  AND (
    'meeting' = ANY(whatText) OR 
    'office' = ANY(whereText) OR
    summary ILIKE '%meeting%'
  )
ORDER BY startTimestamp DESC;

Get events during a specific time range

SELECT summary, startTimestamp, endTimestamp, confidence
FROM episodic_signals 
WHERE userId = 'user-uuid'
  AND startTimestamp <= '2024-01-15 18:00:00+00'
  AND endTimestamp >= '2024-01-15 09:00:00+00'
ORDER BY startTimestamp;

Analyze event patterns

SELECT 
  DATE_TRUNC('day', startTimestamp) as day,
  COUNT(*) as event_count,
  AVG(EXTRACT(EPOCH FROM (endTimestamp - startTimestamp))/60) as avg_duration_minutes
FROM episodic_signals 
WHERE userId = 'user-uuid'
  AND startTimestamp >= NOW() - INTERVAL '30 days'
GROUP BY day
ORDER BY day;

Find events with specific participants

SELECT summary, startTimestamp, whoText
FROM episodic_signals 
WHERE userId = 'user-uuid'
  AND 'john' = ANY(whoText)
ORDER BY startTimestamp DESC;