49 lines
1.7 KiB
SQL
49 lines
1.7 KiB
SQL
-- +goose Up
|
|
-- +goose StatementBegin
|
|
CREATE TABLE public.autoscan_events (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
source_id uuid REFERENCES public.autoscan_sources(id) ON DELETE SET NULL,
|
|
installation_id integer NOT NULL,
|
|
capability_id text NOT NULL,
|
|
started_at timestamptz NOT NULL,
|
|
completed_at timestamptz NOT NULL,
|
|
duration_ms bigint NOT NULL DEFAULT 0,
|
|
status text NOT NULL,
|
|
changes_returned integer NOT NULL DEFAULT 0,
|
|
changes_resolved integer NOT NULL DEFAULT 0,
|
|
targets_claimed integer NOT NULL DEFAULT 0,
|
|
scans_created integer NOT NULL DEFAULT 0,
|
|
scans_reused integer NOT NULL DEFAULT 0,
|
|
scans_suppressed integer NOT NULL DEFAULT 0,
|
|
error_message text NOT NULL DEFAULT '',
|
|
marker_before text,
|
|
marker_after text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT autoscan_events_status_check CHECK (status = ANY (ARRAY['success'::text, 'error'::text, 'unresolved'::text]))
|
|
);
|
|
|
|
CREATE INDEX idx_autoscan_events_source_completed
|
|
ON public.autoscan_events (source_id, completed_at DESC);
|
|
|
|
CREATE INDEX idx_autoscan_events_completed
|
|
ON public.autoscan_events (completed_at DESC);
|
|
|
|
ALTER TABLE public.scan_runs
|
|
ADD COLUMN autoscan_event_id bigint REFERENCES public.autoscan_events(id) ON DELETE SET NULL;
|
|
|
|
CREATE INDEX idx_scan_runs_autoscan_event
|
|
ON public.scan_runs (autoscan_event_id);
|
|
-- +goose StatementEnd
|
|
|
|
-- +goose Down
|
|
-- +goose StatementBegin
|
|
DROP INDEX IF EXISTS public.idx_scan_runs_autoscan_event;
|
|
|
|
ALTER TABLE public.scan_runs
|
|
DROP COLUMN IF EXISTS autoscan_event_id;
|
|
|
|
DROP INDEX IF EXISTS public.idx_autoscan_events_completed;
|
|
DROP INDEX IF EXISTS public.idx_autoscan_events_source_completed;
|
|
DROP TABLE IF EXISTS public.autoscan_events;
|
|
-- +goose StatementEnd
|