PostgreSQL Extensions in Supabase

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Schedule a job to run every hour
SELECT cron.schedule(
  'cleanup-expired-sessions',       -- job name
  '0 * * * *',                      -- every hour (cron syntax)
  $$ DELETE FROM sessions WHERE expires_at < NOW() $$
);

-- Refresh a materialized view every 15 minutes
SELECT cron.schedule(
  'refresh-post-stats',
  '*/15 * * * *',
  $$ REFRESH MATERIALIZED VIEW CONCURRENTLY post_stats $$
);

-- Run a daily report at midnight
SELECT cron.schedule(
  'daily-report',
  '0 0 * * *',
  $$ INSERT INTO daily_stats (date, total_users, total_posts)
     SELECT CURRENT_DATE, COUNT(DISTINCT user_id), COUNT(*)
     FROM posts WHERE created_at >= CURRENT_DATE $$
);

-- List all scheduled jobs
SELECT * FROM cron.job;

-- Remove a scheduled job
SELECT cron.unschedule('cleanup-expired-sessions');

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_net;

-- Send a webhook when a new user signs up
CREATE OR REPLACE FUNCTION notify_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  PERFORM net.http_post(
    url := 'https://api.example.com/webhooks/new-user',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer your-webhook-secret'
    ),
    body := jsonb_build_object(
      'user_id', NEW.id,
      'email', NEW.email,
      'created_at', NEW.created_at
    )
  );
  RETURN NEW;
END;
$$;

CREATE TRIGGER on_new_user
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION notify_new_user();

-- Enable PostGIS
CREATE EXTENSION IF NOT EXISTS postgis;

-- Create a table with geospatial data
CREATE TABLE stores (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  location geography(POINT, 4326) NOT NULL
);

-- Insert a store with lat/lng
INSERT INTO stores (name, location)
VALUES ('Downtown Store', ST_Point(-73.9857, 40.7484));

-- Find stores within 5km of a point
SELECT name,
  ST_Distance(location, ST_Point(-73.9800, 40.7500)::geography) AS distance_m
FROM stores
WHERE ST_DWithin(
  location,
  ST_Point(-73.9800, 40.7500)::geography,
  5000  -- 5000 meters = 5km
)
ORDER BY distance_m;

-- Create a spatial index for fast queries
CREATE INDEX idx_stores_location ON stores USING GIST (location);

-- Enable ltree for tree structures
CREATE EXTENSION IF NOT EXISTS ltree;

-- Categories with hierarchical paths
CREATE TABLE categories (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  path ltree NOT NULL
);

-- Insert hierarchical data
INSERT INTO categories (name, path) VALUES
  ('Electronics', 'electronics'),
  ('Phones', 'electronics.phones'),
  ('Smartphones', 'electronics.phones.smartphones'),
  ('Laptops', 'electronics.laptops');

-- Find all descendants of Electronics
SELECT * FROM categories WHERE path <@ 'electronics';

-- Find all ancestors of Smartphones
SELECT * FROM categories WHERE path @> 'electronics.phones.smartphones';

-- Create an index for fast tree queries
CREATE INDEX idx_categories_path ON categories USING GIST (path);

-- pgcrypto for encryption
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Hash sensitive data
SELECT crypt('my-password', gen_salt('bf'));

-- Generate random bytes
SELECT encode(gen_random_bytes(32), 'hex') AS api_key;

-- uuid-ossp for UUID generation (often already enabled)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();