96 lines
4.3 KiB
SQL
96 lines
4.3 KiB
SQL
-- ---------------------------------------------------------------------------
|
|
-- Initial schema for CDP Analytics (data-layer).
|
|
--
|
|
-- Tables owned by this service. Read-only access to ingestion-owned tables
|
|
-- (workspaces, profiles, sources, destinations, schema_fields) is assumed.
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- trait_definitions -- declarative computed-trait specs maintained per workspace.
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE trait_definitions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL,
|
|
key TEXT NOT NULL, -- column name on profile_traits
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
-- spec drives how the worker computes the trait (e.g. aggregation over
|
|
-- ClickHouse events). Format is open during prototyping.
|
|
spec JSONB NOT NULL,
|
|
refresh_every INTERVAL NOT NULL DEFAULT '1 hour',
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (workspace_id, key)
|
|
);
|
|
|
|
CREATE INDEX idx_trait_definitions_workspace ON trait_definitions (workspace_id) WHERE enabled;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- profile_traits -- computed values per profile, refreshed by the worker.
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE profile_traits (
|
|
workspace_id UUID NOT NULL,
|
|
profile_id UUID NOT NULL,
|
|
trait_key TEXT NOT NULL,
|
|
trait_value JSONB NOT NULL,
|
|
computed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (workspace_id, profile_id, trait_key)
|
|
);
|
|
|
|
CREATE INDEX idx_profile_traits_workspace_key ON profile_traits (workspace_id, trait_key);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- segment_definitions -- audience segment specs.
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE segment_definitions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL,
|
|
slug TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
-- spec is the filter tree evaluated against profiles + events + traits.
|
|
spec JSONB NOT NULL,
|
|
refresh_every INTERVAL NOT NULL DEFAULT '1 hour',
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
last_refreshed_at TIMESTAMPTZ,
|
|
UNIQUE (workspace_id, slug)
|
|
);
|
|
|
|
CREATE INDEX idx_segment_definitions_workspace ON segment_definitions (workspace_id) WHERE enabled;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- segment_memberships -- history table powering delta Reverse ETL.
|
|
-- exited_at NULL means the profile is currently a member.
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE segment_memberships (
|
|
segment_id UUID NOT NULL REFERENCES segment_definitions (id) ON DELETE CASCADE,
|
|
profile_id UUID NOT NULL,
|
|
entered_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
exited_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE INDEX idx_segment_memberships_active ON segment_memberships (segment_id, profile_id) WHERE exited_at IS NULL;
|
|
CREATE INDEX idx_segment_memberships_profile ON segment_memberships (profile_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- saved_queries -- user-saved query specs from the Explore / SQL UI.
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE saved_queries (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL,
|
|
owner_id UUID,
|
|
name TEXT NOT NULL,
|
|
kind TEXT NOT NULL
|
|
CHECK (kind IN ('events', 'sql', 'funnel', 'retention', 'session')),
|
|
spec JSONB NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_saved_queries_workspace ON saved_queries (workspace_id, kind);
|