repos / pico

20251226_add_pipe_monitoring.sql

 1CREATE TABLE IF NOT EXISTS pipe_monitors (
 2  id uuid NOT NULL DEFAULT uuid_generate_v4(),
 3  user_id uuid NOT NULL,
 4  topic text NOT NULL,
 5  window_dur interval NOT NULL,
 6  window_end timestamp without time zone NOT NULL DEFAULT NOW(),
 7  last_ping timestamp,
 8  created_at timestamp without time zone NOT NULL DEFAULT NOW(),
 9  updated_at timestamp without time zone NOT NULL DEFAULT NOW(),
10  CONSTRAINT pipe_monitors_unique_topic UNIQUE (user_id, topic),
11  CONSTRAINT pipe_monitoring_pkey PRIMARY KEY (id),
12  CONSTRAINT fk_pipe_monitoring_app_users
13    FOREIGN KEY(user_id)
14  REFERENCES app_users(id)
15  ON DELETE CASCADE
16  ON UPDATE CASCADE
17);
18
19CREATE TABLE IF NOT EXISTS pipe_monitors_history (
20  id uuid NOT NULL DEFAULT uuid_generate_v4(),
21  monitor_id uuid NOT NULL,
22  window_dur interval NOT NULL,
23  window_end timestamp without time zone NOT NULL DEFAULT NOW(),
24  last_ping timestamp,
25  created_at timestamp without time zone NOT NULL DEFAULT NOW(),
26  updated_at timestamp without time zone NOT NULL DEFAULT NOW(),
27  CONSTRAINT pipe_monitor_history_pkey PRIMARY KEY (id),
28  CONSTRAINT fk_pipe_monitor_history_pipe_monitors
29    FOREIGN KEY(monitor_id)
30  REFERENCES pipe_monitors(id)
31  ON DELETE CASCADE
32  ON UPDATE CASCADE
33);
34
35CREATE INDEX IF NOT EXISTS idx_pipe_mon_hist_monitor_last_ping ON pipe_monitors_history (monitor_id, last_ping);
36CREATE INDEX IF NOT EXISTS idx_pipe_mon_hist_monitor_window_end ON pipe_monitors_history (monitor_id, window_end);