179 lines
7.9 KiB
SQL
179 lines
7.9 KiB
SQL
-- ---------------------------------------------------------------------------
|
|
-- Initial schema for CDP Ingestion control plane.
|
|
--
|
|
-- This database stores configuration, not events. Events live in ClickHouse.
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- workspaces
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE workspaces (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
tier TEXT NOT NULL DEFAULT 'default'
|
|
CHECK (tier IN ('default', 'pro', 'enterprise')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE INDEX idx_workspaces_slug ON workspaces (slug) WHERE deleted_at IS NULL;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- users (console operators)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
email TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
name TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE workspace_members (
|
|
workspace_id UUID NOT NULL REFERENCES workspaces (id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users (id) ON DELETE CASCADE,
|
|
role TEXT NOT NULL DEFAULT 'member'
|
|
CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (workspace_id, user_id)
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- sources -- each source is something that pushes events (web, mobile, server)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE sources (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL REFERENCES workspaces (id) ON DELETE CASCADE,
|
|
slug TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
kind TEXT NOT NULL
|
|
CHECK (kind IN ('web', 'mobile', 'server', 'segment', 'webhook')),
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
settings JSONB NOT NULL DEFAULT '{}',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
deleted_at TIMESTAMPTZ,
|
|
UNIQUE (workspace_id, slug)
|
|
);
|
|
|
|
CREATE INDEX idx_sources_workspace ON sources (workspace_id) WHERE deleted_at IS NULL;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- write_keys -- API auth tokens, scoped to a source
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE write_keys (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL REFERENCES workspaces (id) ON DELETE CASCADE,
|
|
source_id UUID NOT NULL REFERENCES sources (id) ON DELETE CASCADE,
|
|
key_hash TEXT NOT NULL UNIQUE, -- store hash, never raw
|
|
key_prefix TEXT NOT NULL, -- first ~8 chars for display
|
|
label TEXT,
|
|
revoked_at TIMESTAMPTZ,
|
|
last_used_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_write_keys_workspace ON write_keys (workspace_id) WHERE revoked_at IS NULL;
|
|
CREATE INDEX idx_write_keys_source ON write_keys (source_id) WHERE revoked_at IS NULL;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- destinations -- where events are forwarded (clickhouse, snowflake, bq, s3...)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE destinations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL REFERENCES workspaces (id) ON DELETE CASCADE,
|
|
slug TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
kind TEXT NOT NULL
|
|
CHECK (kind IN ('clickhouse', 'postgres', 'snowflake', 'bigquery',
|
|
'redshift', 's3', 'webhook')),
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
config JSONB NOT NULL DEFAULT '{}', -- credentials encrypted at rest
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
deleted_at TIMESTAMPTZ,
|
|
UNIQUE (workspace_id, slug)
|
|
);
|
|
|
|
CREATE INDEX idx_destinations_workspace ON destinations (workspace_id) WHERE deleted_at IS NULL;
|
|
|
|
-- source -> destination wiring
|
|
CREATE TABLE source_destination_links (
|
|
source_id UUID NOT NULL REFERENCES sources (id) ON DELETE CASCADE,
|
|
destination_id UUID NOT NULL REFERENCES destinations (id) ON DELETE CASCADE,
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (source_id, destination_id)
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- functions -- JS transformation code run by rotor
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE functions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL REFERENCES workspaces (id) ON DELETE CASCADE,
|
|
slug TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
code TEXT NOT NULL,
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
version INTEGER NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
deleted_at TIMESTAMPTZ,
|
|
UNIQUE (workspace_id, slug)
|
|
);
|
|
|
|
CREATE TABLE function_attachments (
|
|
source_id UUID REFERENCES sources (id) ON DELETE CASCADE,
|
|
destination_id UUID REFERENCES destinations (id) ON DELETE CASCADE,
|
|
function_id UUID NOT NULL REFERENCES functions (id) ON DELETE CASCADE,
|
|
position INTEGER NOT NULL DEFAULT 0,
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
CHECK (
|
|
(source_id IS NOT NULL AND destination_id IS NULL) OR
|
|
(source_id IS NULL AND destination_id IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
CREATE INDEX idx_function_attachments_source ON function_attachments (source_id);
|
|
CREATE INDEX idx_function_attachments_destination ON function_attachments (destination_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- schema_fields -- discovered field types per (workspace, event_type, field)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE schema_fields (
|
|
workspace_id UUID NOT NULL REFERENCES workspaces (id) ON DELETE CASCADE,
|
|
event_type TEXT NOT NULL,
|
|
field TEXT NOT NULL,
|
|
data_type TEXT NOT NULL
|
|
CHECK (data_type IN ('string', 'number', 'boolean',
|
|
'object', 'array', 'timestamp', 'null')),
|
|
first_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
sample_count BIGINT NOT NULL DEFAULT 1,
|
|
PRIMARY KEY (workspace_id, event_type, field)
|
|
);
|
|
|
|
CREATE INDEX idx_schema_fields_event ON schema_fields (workspace_id, event_type);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- audit_log -- security-relevant operations
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE audit_log (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
workspace_id UUID REFERENCES workspaces (id) ON DELETE SET NULL,
|
|
actor_id UUID REFERENCES users (id) ON DELETE SET NULL,
|
|
action TEXT NOT NULL,
|
|
target_type TEXT,
|
|
target_id TEXT,
|
|
metadata JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_audit_log_workspace ON audit_log (workspace_id, created_at DESC);
|