Something went wrong. Try again.
A Haskell Webserver Template
Something went wrong. Try again.
3.0 kB · 110 lines
SQL
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111-- Deploy webserver:00000000 to pgBEGIN;
CREATE EXTENSION IF NOT EXISTS pg_cron;
CREATE SCHEMA IF NOT EXISTS audit;
CREATE TYPE audit.operation_type AS ENUM ('insert', 'update', 'delete');
CREATE TABLE IF NOT EXISTS public.users ( id UUID PRIMARY KEY DEFAULT uuidv7(), name VARCHAR(32) NOT NULL UNIQUE, email VARCHAR(64) NOT NULL UNIQUE);
CREATE TABLE IF NOT EXISTS audit.users_history ( id UUID PRIMARY KEY DEFAULT uuidv7(), entity_id UUID NOT NULL, operation audit.operation_type NOT NULL, value JSONB, changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), changed_by UUID);
-- Generic trigger function that records row changes to a paired history table.-- The history table and schema are passed via TG_ARGV[0] and TG_ARGV[1].CREATE OR REPLACE FUNCTION audit.record_history()RETURNS TRIGGERLANGUAGE plpgsqlAS $$DECLARE history_schema TEXT := TG_ARGV[0]; history_table TEXT := TG_ARGV[1]; history_id UUID := uuidv7(); entity_row RECORD; op audit.operation_type; v_user UUID;BEGIN BEGIN v_user := current_setting('app.current_user_id', true)::UUID; EXCEPTION WHEN OTHERS THEN v_user := NULL; END;
op := lower(TG_OP); IF TG_OP = 'DELETE' THEN entity_row := OLD; ELSE entity_row := NEW; END IF;
EXECUTE format( 'INSERT INTO %I.%I (id, entity_id, operation, value, changed_at, changed_by) VALUES ($1, $2, $3, $4, NOW(), $5)', history_schema, history_table ) USING history_id, entity_row.id, op, row_to_json(entity_row)::jsonb, v_user;
IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;END;$$;
-- Trigger on public.users -> audit.users_historyCREATE TRIGGER trg_users_history AFTER INSERT OR UPDATE OR DELETE ON public.users FOR EACH ROW EXECUTE FUNCTION audit.record_history('audit', 'users_history');
CREATE SCHEMA IF NOT EXISTS auth;
CREATE TABLE IF NOT EXISTS auth.auth ( id UUID PRIMARY KEY DEFAULT uuidv7(), user_id UUID NOT NULL REFERENCES users ON DELETE CASCADE, password VARCHAR NOT NULL);
CREATE SCHEMA IF NOT EXISTS cache;
CREATE UNLOGGED TABLE IF NOT EXISTS cache.sessions ( id UUID PRIMARY KEY DEFAULT uuidv7(), token VARCHAR NOT NULL, expires_on TIMESTAMPTZ NOT NULL, user_id UUID REFERENCES users ON DELETE CASCADE);
CREATE UNLOGGED TABLE IF NOT EXISTS cache.rate_limits ( id UUID PRIMARY KEY DEFAULT uuidv7(), key TEXT NOT NULL UNIQUE, hits INT NOT NULL DEFAULT 1, window_start TIMESTAMPTZ NOT NULL DEFAULT NOW());
CREATE OR REPLACE PROCEDURE expire_caches ()LANGUAGE plpgsqlAS$$BEGIN DELETE FROM cache.sessions WHERE expires_on < NOW();
DELETE FROM cache.rate_limits WHERE window_start < NOW() - INTERVAL '1 hour';
COMMIT;END;$$;
SELECT cron.schedule('cache_cleanup', '0 * * * *', $$CALL expire_caches();$$);
COMMIT;