Endpoint Guardian

-- Enable UUID generation if not already enabled
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- users table for authentication
CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  email text UNIQUE NOT NULL,
  password_hash text NOT NULL, -- Stored securely, e.g., bcrypt hash
  stripe_customer_id text, -- For Stripe billing integration
  subscription_status text DEFAULT 'free', -- 'free', 'pro', 'canceled'
  created_at timestamp WITH time zone DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp WITH time zone DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE users IS 'Stores user authentication and subscription details.';

-- monitors table to define what API endpoints to check
CREATE TABLE monitors (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name text NOT NULL,
  url text NOT NULL,
  method text NOT NULL DEFAULT 'GET', -- e.g., 'GET', 'POST', 'PUT'
  headers jsonb DEFAULT '{}', -- Custom headers for the request
  body text, -- Request body for POST/PUT requests
  expected_status integer DEFAULT 200, -- Expected HTTP status code
  expected_body_regex text, -- Regex to match in the response body
  check_interval_seconds integer DEFAULT 60, -- How often to check (e.g., 60 for 1 minute)
  is_active boolean DEFAULT TRUE,
  last_checked_at timestamp WITH time zone,
  created_at timestamp WITH time zone DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp WITH time zone DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (user_id, name) -- User can't have two monitors with the same name
);
CREATE INDEX idx_monitors_user_id ON monitors(user_id);
CREATE INDEX idx_monitors_is_active_interval ON monitors(is_active, check_interval_seconds);
COMMENT ON TABLE monitors IS 'Defines API endpoints to be monitored.';

-- checks table to store the results of each monitor run
CREATE TABLE checks (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  monitor_id uuid NOT NULL REFERENCES monitors(id) ON DELETE CASCADE,
  status_code integer NOT NULL,
  response_time_ms integer NOT NULL,
  is_successful boolean NOT NULL,
  error_message text, -- Detailed error message if check failed
  checked_at timestamp WITH time zone DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_checks_monitor_id ON checks(monitor_id);
CREATE INDEX idx_checks_checked_at ON checks(checked_at DESC);
COMMENT ON TABLE checks IS 'Records the results of each monitor check.';

-- alert_channels table to store user's preferred notification methods
CREATE TABLE alert_channels (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type text NOT NULL, -- e.g., 'email', 'slack', 'discord'
  config jsonb NOT NULL, -- e.g., {'email': 'user@example.com'} or {'webhook_url': 'https://hooks.slack.com/...', 'channel': '#alerts'}
  is_active boolean DEFAULT TRUE,
  created_at timestamp WITH time zone DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp WITH time zone DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_alert_channels_user_id ON alert_channels(user_id);
COMMENT ON TABLE alert_channels IS 'Stores user notification channels (email, webhooks).';

-- alerts table to log sent alerts
CREATE TABLE alerts (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  monitor_id uuid NOT NULL REFERENCES monitors(id) ON DELETE CASCADE,
  check_id uuid REFERENCES checks(id) ON DELETE SET NULL, -- Link to the specific check that triggered the alert
  alert_channel_id uuid NOT NULL REFERENCES alert_channels(id) ON DELETE CASCADE,
  message text NOT NULL,
  status text NOT NULL DEFAULT 'sent', -- e.g., 'sent', 'failed'
  sent_at timestamp WITH time zone DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_alerts_monitor_id ON alerts(monitor_id);
CREATE INDEX idx_alerts_alert_channel_id ON alerts(alert_channel_id);
COMMENT ON TABLE alerts IS 'Logs all triggered and sent alerts.';