twitch-eventtracker-plugin/data/tracker.sql

BEGIN;

-- Version dependency checks
DO $$
  ASSERT current_module_version('PROFILES') = 1, 'Dependency version mismatch: PROFILES';
$$ LANGUAGE plpgsql;

INSERT INTO version_history (component, from_version, to_version, author) VALUES ('EVENT_TRACKER', 0, 2, 'Initial Creation');

-- Twitch tracked event data {{{

CREATE TABLE tracking_channels(
  userid TEXT PRIMARY KEY REFERENCES bot_channels(userid) ON DELETE CASCADE,
  joined BOOLEAN,
  joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  _timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TRIGGER tracking_channels_timestamp BEFORE UPDATE ON tracking_channels
  FOR EACH ROW EXECUTE FUNCTION update_timestamp_column();

CREATE TABLE events(
  event_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  event_type TEXT NOT NULL,
  communityid INTEGER NOT NULL REFERENCES communities (communityid),
  channel_id TEXT NOT NULL,
  profileid INTEGER REFERENCES user_profiles (profileid),
  user_id TEXT,
  occurred_at TIMESTAMPTZ,
  event_payload JSONB,
  parent_event_id INTEGER REFERENCES events (event_id) ON DELETE SET NULL ON UPDATE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (event_id, event_type)
);

CREATE INDEX events_community_id ON events (communityid);

CREATE TABLE follow_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'follow' CHECK (event_type = 'follow'),
  follower_count INTEGER NOT NULL,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE bits_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'bits' CHECK (event_type = 'bits'),
  bits INTEGER NOT NULL,
  bits_type TEXT NOT NULL,
  message TEXT,
  powerup_type TEXT,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE subscribe_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'subscribe' CHECK (event_type = 'subscribe'),
  tier INTEGER NOT NULL,
  gifted BOOLEAN NOT NULL,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE gift_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'gift' CHECK (event_type = 'gift'),
  tier INTEGER NOT NULL,
  gifted_count INTEGER NOT NULL,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE subscribe_message_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'subscribe_message' CHECK (event_type = 'subscribe_message'),
  tier INTEGER NOT NULL,
  duration_months INTEGER NOT NULL,
  cumulative_months INTEGER NOT NULL,
  streak_months INTEGER,
  message TEXT,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE cheer_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'cheer' CHECK (event_type = 'cheer'),
  amount INTEGER NOT NULL,
  message TEXT,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE redemption_add_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'redemption_add' CHECK (event_type = 'redemption_add'),
  redeem_id TEXT NOT NULL,
  redeem_title TEXT NOT NULL,
  redeem_cost INTEGER NOT NULL,
  redemption_id TEXT NOT NULL,
  redemption_status TEXT NOT NULL,
  message TEXT,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE redemption_update_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'redemption_update' CHECK (event_type = 'redemption_update'),
  redeem_id TEXT NOT NULL,
  redeem_title TEXT NOT NULL,
  redeem_cost INTEGER NOT NULL,
  redemption_id TEXT NOT NULL,
  redemption_status TEXT NOT NULL,
  redeemed_at TIMESTAMPTZ NOT NULL,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE poll_end_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'poll_end' CHECK (event_type = 'poll_end'),
  poll_id TEXT NOT NULL,
  poll_title TEXT NOT NULL,
  poll_choices TEXT NOT NULL,
  poll_started TIMESTAMPTZ NOT NULL,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE stream_online_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'stream_online' CHECK (event_type = 'stream_online'),
  stream_id TEXT NOT NULL,
  stream_type TEXT NOT NULL,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE stream_offline_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'stream_offline' CHECK (event_type = 'stream_offline'),
  stream_id TEXT,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE channel_update_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'channel_update' CHECK (event_type = 'channel_update'),
  title TEXT,
  language TEXT,
  category_id TEXT,
  category_name TEXT,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE vip_add_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'vip_add' CHECK (event_type = 'vip_add'),
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE vip_remove_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'vip_remove' CHECK (event_type = 'vip_remove'),
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE message_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'message' CHECK (event_type = 'message'),
  message_id TEXT NOT NULL,
  message_type TEXT NOT NULL,
  content TEXT,
  source_channel_id TEXT,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE raid_out_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'raidout' CHECK (event_type = 'raidout'),
  target_id TEXT NOT NULL,
  target_name TEXT,
  viewer_count INTEGER NOT NULL,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE raid_in_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'raidin' CHECK (event_type = 'raidin'),
  source_id TEXT NOT NULL,
  source_name TEXT,
  viewer_count INTEGER NOT NULL,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE notice_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'notice',
  message_id TEXT NOT NULL,
  system_message TEXT NOT NULL,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE notice_sub_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'notice_sub',
  tier INTEGER NOT NULL,
  is_prime BOOLEAN NOT NULL,
  duration_months INTEGER NOT NULL,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);

CREATE TABLE notice_resub_events(
  event_id INTEGER PRIMARY KEY REFERENCES events (event_id),
  event_type TEXT NOT NULL DEFAULT 'notice_resub',
  tier INTEGER NOT NULL,
  is_prime BOOLEAN,
  is_gift BOOLEAN NOT NULL,
  cumulative_months INTEGER NOT NULL,
  duration_months INTEGER NOT NULL,
  streak_months INTEGER,
  gifter_is_anonymous BOOLEAN,
  gifter_user_id TEXT,
  FOREIGN KEY (event_id, event_type) REFERENCES events (event_id, event_type)
);


CREATE VIEW event_info AS
SELECT
  events.event_id AS event_id,
  events.event_type AS event_type,
  events.communityid AS event_communityid,
  events.channel_id AS event_channel_id,
  events.profileid AS user_profileid,
  events.user_id AS user_id,

  user_profiles.nickname AS user_nickname,
  user_profiles.timezone AS user_timezone,
  user_profiles.locale_hint AS user_localehint,
  user_profiles.locale AS user_locale,
  COALESCE(events.occurred_at, events.created_at) AS occurred_at,
  events.created_at AS created_at,

  follow_events.follower_count AS follower_count,

  bits_events.bits AS bits_count,
  bits_events.bits_type AS bits_type,
  bits_events.message AS bits_message,
  bits_events.powerup_type AS bits_powerup_type,

  subscribe_events.tier AS subscription_tier,
  subscribe_events.gifted AS subscription_gifted,

  gift_events.tier AS gifted_tier,
  gift_events.gifted_count AS gifted_count,

  subscribe_message_events.tier AS submessage_tier,
  subscribe_message_events.duration_months AS submessage_duration,
  subscribe_message_events.cumulative_months AS submessage_cumulative,
  subscribe_message_events.streak_months AS submessage_streak,
  subscribe_message_events.message AS submessage_message,

  cheer_events.amount AS cheer_amount,
  cheer_events.message AS cheer_message,

  redemption_add_events.redeem_id AS redeemed_redeemid,
  redemption_add_events.redeem_title AS redeemed_title,
  redemption_add_events.redeem_cost AS redeemed_cost,
  redemption_add_events.redemption_id AS redeemed_redemptionid,
  redemption_add_events.redemption_status AS redeemed_status,
  redemption_add_events.message AS redeemed_message,

  redemption_update_events.redeem_id AS redeemupdate_redeemid,
  redemption_update_events.redeem_title AS redeemupdate_title,
  redemption_update_events.redeem_cost AS redeemupdate_cost,
  redemption_update_events.redemption_id AS redeemupdate_redemptionid,
  redemption_update_events.redemption_status AS redeemupdate_status,
  redemption_update_events.redeemed_at AS redeemupdate_redeemedat,

  poll_end_events.poll_id AS pollend_pollid,
  poll_end_events.poll_title AS pollend_title,
  poll_end_events.poll_choices AS pollend_choices,
  poll_end_events.poll_started AS pollend_started,

  stream_online_events.stream_id AS online_streamid,
  stream_online_events.stream_type AS online_streamtype,

  channel_update_events.title AS update_title,
  channel_update_events.language AS update_language,
  channel_update_events.category_id AS update_catid,
  channel_update_events.category_name AS update_catname,

  message_events.message_id AS message_id,
  message_events.message_type AS message_type,
  message_events.content AS message_content,
  message_events.source_channel_id AS message_sourceid,

  raid_out_events.target_id AS raidout_targetid,
  raid_out_events.target_name AS raidout_targetname,
  raid_out_events.viewer_count AS raidout_viewers,

  raid_in_events.source_id AS raidin_sourceid,
  raid_in_events.source_name AS raidin_sourcename,
  raid_in_events.viewer_count AS raidin_viewers,

  notice_events.message_id AS notice_message_id,
  notice_events.system_message AS notice_system_message,

  notice_sub_events.tier AS notice_sub_tier,
  notice_sub_events.is_prime AS notice_sub_is_prime,
  notice_sub_events.duration_months AS notice_sub_duration_months,

  events.event_payload AS event_payload,
  events.parent_event_id AS parent_event_id,

  notice_resub_events.tier AS notice_resub_tier,
  notice_resub_events.is_prime AS notice_resub_is_prime,
  notice_resub_events.is_gift AS notice_resub_is_gift,
  notice_resub_events.cumulative_months AS notice_resub_cumulative_months,
  notice_resub_events.duration_months AS notice_resub_duration_months,
  notice_resub_events.streak_months AS notice_resub_streak_months,
  notice_resub_events.gifter_is_anonymous AS notice_resub_gifter_is_anonymous,
  notice_resub_events.gifter_user_id AS notice_resub_gifter_user_id

FROM events
LEFT JOIN user_profiles USING (profileid)
LEFT JOIN follow_events USING (event_id, event_type)
LEFT JOIN bits_events USING (event_id, event_type)
LEFT JOIN subscribe_events USING (event_id, event_type)
LEFT JOIN gift_events USING (event_id, event_type)
LEFT JOIN subscribe_message_events USING (event_id, event_type)
LEFT JOIN cheer_events USING (event_id, event_type)
LEFT JOIN redemption_add_events USING (event_id, event_type)
LEFT JOIN redemption_update_events USING (event_id, event_type)
LEFT JOIN poll_end_events USING (event_id, event_type)
LEFT JOIN stream_online_events USING (event_id, event_type)
LEFT JOIN stream_offline_events USING (event_id, event_type)
LEFT JOIN channel_update_events USING (event_id, event_type)
LEFT JOIN vip_add_events USING (event_id, event_type)
LEFT JOIN vip_remove_events USING (event_id, event_type)
LEFT JOIN message_events USING (event_id, event_type)
LEFT JOIN raid_out_events USING (event_id, event_type)
LEFT JOIN raid_in_events USING (event_id, event_type)
LEFT JOIN notice_events USING (event_id, event_type)
LEFT JOIN notice_sub_events USING (event_id, event_type)
LEFT JOIN notice_resub_events USING (event_id, event_type);


-- }}}

COMMIT;