logbot-genesis 1 set search_path = public;
logbot-genesis 2
logbot-genesis 3 create extension if not exists plpgsql;
logbot-genesis 4 create extension if not exists pgcrypto;
logbot-genesis 5 create extension if not exists "uuid-ossp";
logbot-genesis 6
logbot-genesis 7 create table log (
logbot-genesis 8 id uuid primary key default gen_random_uuid(),
logbot-genesis 9 target text not null,
logbot-genesis 10 message text not null,
logbot-genesis 11 host text,
logbot-genesis 12 source text not null,
logbot-genesis 13 "user" text,
logbot-genesis 14 received_at timestamp without time zone not null default (now() at time zone 'utc')
logbot-genesis 15 );
logbot-genesis 16
logbot-genesis 17 create index log_received_at on log (received_at);
logbot-genesis 18 create index log_target on log (target);
logbot-genesis 19 create index log_source on log (source);
logbot-genesis 20
logbot-genesis 21 create or replace function log_insert_notify () returns trigger as $$
logbot-genesis 22 begin
logbot-genesis 23 perform pg_notify('log_new_message', NEW.id::text);
logbot-genesis 24 return NEW;
logbot-genesis 25 end;
logbot-genesis 26 $$ language plpgsql;
logbot-genesis 27
logbot-genesis 28 create trigger log_insert_notify_trigger
logbot-genesis 29 after insert on log
logbot-genesis 30 for each row execute procedure log_insert_notify ();
logbot-multiple-c... 31
logbot-genesis 32 create table outbox (
logbot-genesis 33 id serial primary key,
logbot-genesis 34 target text not null,
logbot-genesis 35 message text not null,
logbot-genesis 36 queued_at timestamp without time zone not null default (now() at time zone 'utc')
logbot-genesis 37 );
logbot-genesis 38
logbot-genesis 39 create or replace function outbox_insert_notify () returns trigger as $$
logbot-genesis 40 begin
logbot-multiple-c... 41 perform pg_notify('outbox_new_message', NEW.target::text);
logbot-genesis 42 return NEW;
logbot-genesis 43 end;
logbot-genesis 44 $$ language plpgsql;
logbot-genesis 45
logbot-genesis 46 create trigger outbox_insert_notify_trigger
logbot-genesis 47 after insert on outbox
logbot-genesis 48 for each row execute procedure outbox_insert_notify ();