Portable SignalQL analytics data model

Adapters map warehouse-specific schemas to this minimal logical model. Field names below are defaults; adapters may rename columns when compiling queries.

user_id and session_id links are nullable in the portable model, so event/session rows may exist without a matching parent row.

Tables

events

Column	Type	Null	Semantics
event_name	text	no	Canonical event identifier
user_id	text	yes	Stable user key when known
session_id	text	yes	Session bucket
timestamp	timestamptz	no	Event time in UTC
properties	jsonb	yes	Arbitrary payload; access rules below

users

Column	Type	Null	Semantics
user_id	text	no	Primary key
traits	jsonb	yes	Profile attributes

sessions

Column	Type	Null	Semantics
session_id	text	no	Primary key
user_id	text	yes	Owner
started_at	timestamptz	no	Session start
ended_at	timestamptz	yes	Optional explicit end

JSON property access

• Dot paths refer to properties on events or traits on users, e.g. properties.plan = 'pro'.
• Missing keys compare false for equality filters; adapters document JSON coercion.
• Only literal comparisons are required in v0.1; nested objects use documented paths.

Mapping custom schemas

Adapters provide column maps and optional expression maps for derived fields. Required capabilities:

• Resolve event_name, timestamp, and user_id (or anonymous surrogate).
• Expose properties as JSON or map to a flat view.

Required vs optional

• Required: events.event_name, events.timestamp; one of user linkage or anonymous handling.
• Optional: session_id, properties, full users / sessions tables when not used.

The sample seed dataset in this repository matches this model for tests and demos.

Graph model (v0.4)

v0.4 graph queries bind two additional logical sources, resolved through the source map (entities, edges keys; defaults entities and edges):

entities

Column	Required	Meaning
entity_id	yes	Stable identifier.
kind	yes	Entity type, e.g. decision, ticket, assumption.
properties	no	JSON payload; bare predicate names resolve here.
updated_at	for AS OF	Version effective time (bitemporal anchor).
version / superseded	no	Revision metadata.

edges (relationship store)

Column	Required	Meaning
from_entity	yes	Source entity id.
relationship	yes	Edge label, e.g. derived_from, supports (vocab is external).
to_entity	yes	Target entity id.
properties	no	JSON payload; confidence is read for lineage.
observed_at	for AS OF	Edge effective time.

Provenance edges — derived_from plus source/confidence — form the lineage substrate that TRACE walks. Required: entity_id, kind, and the three edge endpoint columns. updated_at/observed_at are required only when using bitemporal AS OF.