Sergei Poljanski
1a5506c77d
asxpio now probes public hosts tied to each datacenter once a minute and stores 5-minute rows (bingo_monitor_* tables, granted to the read-only bingo role). Show them: /:locale/status lists every monitored site with its current state and a 24-hour bar, /status.json has the same data, and monitored datacenter pages get a small block. A site is down only when nothing answered for two buckets. Everything reads as unknown when the worker is stale or fewer than half of the control hosts in Russia answer, since probes run from Finland and filtering can look like an outage. The page says plainly that this is not an incident record. Refresh test/schema.sql for asxpio's migration 017. Assisted-by: Claude:opus-5-5
134 lines
4.3 KiB
SQL
134 lines
4.3 KiB
SQL
-- bingo tables as created by the asxpio migrations (004–017), for tests.
|
||
-- Refresh when asxpio adds a bingo migration: run them into an empty DB and
|
||
-- pg_dump -s -O -x -t bingo_datacenters -t bingo_incidents -t 'bingo_monitor_*'.
|
||
|
||
CREATE TABLE public.bingo_datacenters (
|
||
id integer NOT NULL,
|
||
slug text NOT NULL,
|
||
"position" integer NOT NULL,
|
||
name_ru text NOT NULL,
|
||
name_en text,
|
||
name_uk text,
|
||
city_ru text NOT NULL,
|
||
city_en text,
|
||
city_uk text,
|
||
operator text NOT NULL,
|
||
mw text,
|
||
status text DEFAULT 'operational'::text NOT NULL,
|
||
year integer,
|
||
photo_key text,
|
||
photo_source_url text,
|
||
photo_credit text,
|
||
info_url text,
|
||
created_at timestamp without time zone NOT NULL,
|
||
photo_variants boolean DEFAULT false NOT NULL,
|
||
mw_type text,
|
||
photo_og boolean DEFAULT false NOT NULL,
|
||
updated_at timestamp without time zone,
|
||
about_ru text,
|
||
about_en text,
|
||
about_uk text,
|
||
provider text
|
||
);
|
||
|
||
ALTER TABLE public.bingo_datacenters ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
|
||
SEQUENCE NAME public.bingo_datacenters_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1
|
||
);
|
||
|
||
CREATE TABLE public.bingo_incidents (
|
||
id integer NOT NULL,
|
||
datacenter_id integer NOT NULL,
|
||
occurred_on date NOT NULL,
|
||
kind text NOT NULL,
|
||
title_ru text NOT NULL,
|
||
title_en text,
|
||
title_uk text,
|
||
url text,
|
||
created_at timestamp without time zone NOT NULL,
|
||
updated_at timestamp without time zone
|
||
);
|
||
|
||
ALTER TABLE public.bingo_incidents ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
|
||
SEQUENCE NAME public.bingo_incidents_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1
|
||
);
|
||
|
||
CREATE TABLE public.bingo_monitor_buckets (
|
||
bucket_at timestamp without time zone NOT NULL,
|
||
rounds integer DEFAULT 0 NOT NULL,
|
||
control_checks integer DEFAULT 0 NOT NULL,
|
||
control_ok integer DEFAULT 0 NOT NULL
|
||
);
|
||
|
||
CREATE TABLE public.bingo_monitor_samples (
|
||
datacenter_id integer NOT NULL,
|
||
bucket_at timestamp without time zone NOT NULL,
|
||
checks integer DEFAULT 0 NOT NULL,
|
||
ok integer DEFAULT 0 NOT NULL,
|
||
rtt_ms_sum integer DEFAULT 0 NOT NULL
|
||
);
|
||
|
||
CREATE TABLE public.bingo_monitor_targets (
|
||
id integer NOT NULL,
|
||
datacenter_id integer,
|
||
host text NOT NULL,
|
||
port integer DEFAULT 443 NOT NULL,
|
||
label text,
|
||
source_url text,
|
||
active boolean DEFAULT true NOT NULL,
|
||
last_ok boolean,
|
||
last_rtt_ms integer,
|
||
last_checked_at timestamp without time zone,
|
||
last_change_at timestamp without time zone,
|
||
created_at timestamp without time zone NOT NULL
|
||
);
|
||
|
||
ALTER TABLE public.bingo_monitor_targets ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
|
||
SEQUENCE NAME public.bingo_monitor_targets_id_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1
|
||
);
|
||
|
||
ALTER TABLE ONLY public.bingo_datacenters
|
||
ADD CONSTRAINT bingo_datacenters_pkey PRIMARY KEY (id);
|
||
|
||
ALTER TABLE ONLY public.bingo_datacenters
|
||
ADD CONSTRAINT bingo_datacenters_slug_key UNIQUE (slug);
|
||
|
||
ALTER TABLE ONLY public.bingo_incidents
|
||
ADD CONSTRAINT bingo_incidents_pkey PRIMARY KEY (id);
|
||
|
||
ALTER TABLE ONLY public.bingo_monitor_buckets
|
||
ADD CONSTRAINT bingo_monitor_buckets_pkey PRIMARY KEY (bucket_at);
|
||
|
||
ALTER TABLE ONLY public.bingo_monitor_samples
|
||
ADD CONSTRAINT bingo_monitor_samples_pkey PRIMARY KEY (datacenter_id, bucket_at);
|
||
|
||
ALTER TABLE ONLY public.bingo_monitor_targets
|
||
ADD CONSTRAINT bingo_monitor_targets_pkey PRIMARY KEY (id);
|
||
|
||
CREATE INDEX bingo_incidents_datacenter_id_index ON public.bingo_incidents USING btree (datacenter_id);
|
||
|
||
CREATE INDEX bingo_monitor_targets_datacenter_id_index ON public.bingo_monitor_targets USING btree (datacenter_id);
|
||
|
||
ALTER TABLE ONLY public.bingo_incidents
|
||
ADD CONSTRAINT bingo_incidents_datacenter_id_fkey FOREIGN KEY (datacenter_id) REFERENCES public.bingo_datacenters(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE ONLY public.bingo_monitor_samples
|
||
ADD CONSTRAINT bingo_monitor_samples_datacenter_id_fkey FOREIGN KEY (datacenter_id) REFERENCES public.bingo_datacenters(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE ONLY public.bingo_monitor_targets
|
||
ADD CONSTRAINT bingo_monitor_targets_datacenter_id_fkey FOREIGN KEY (datacenter_id) REFERENCES public.bingo_datacenters(id) ON DELETE CASCADE;
|
||
|