ayurishchevandClaude Sonnet 5
582b44f314
Adds POST /api/v1/admin/ips/scan (plus an optional periodic ticker) to
discover free Floating IPs in the OpenStack project and feed them straight
into the check queue. More importantly, decouples check/event history from
ip_queue's lifecycle: a new ip_registry table (migration 0007) gives every
address ever submitted a durable identity, so deleting it from the queue no
longer destroys its history — it's still reachable via the new
GET /api/v1/admin/registry[/{ip}] endpoints and the dashboard's /registry
pages, with retention depth configurable in check cycles per address
(history_retention_cycles, 0 = unlimited).
Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
81 lines
4.3 KiB
SQL
81 lines
4.3 KiB
SQL
-- Durable per-address registry, decoupled from ip_queue's lifecycle: today
|
|
-- deleting an address from ip_queue (DeleteIP/DeleteIPs/ClearQueue) cascades
|
|
-- to a hard DELETE of its checks/events, so history is lost forever if an
|
|
-- address is removed and later re-added. ip_registry gives every address
|
|
-- ever submitted a durable identity that check/event history attaches to
|
|
-- instead, surviving ip_queue row deletion and recreation.
|
|
|
|
CREATE TABLE ip_registry (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
ip_address TEXT NOT NULL UNIQUE,
|
|
first_seen_at TIMESTAMP NOT NULL,
|
|
last_seen_at TIMESTAMP NOT NULL,
|
|
next_cycle INTEGER NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Backfill: one registry row per address currently (or ever) in ip_queue.
|
|
-- ip_queue.ip_address is already UNIQUE, so this is a straight 1:1 copy.
|
|
-- next_cycle starts past the current attempt_number so the first future
|
|
-- resubmission of an address gets a cycle_id that has never been used
|
|
-- before, even though it's seeded from attempt_number here.
|
|
INSERT INTO ip_registry (ip_address, first_seen_at, last_seen_at, next_cycle, created_at, updated_at)
|
|
SELECT ip_address, created_at, updated_at, attempt_number + 1, created_at, updated_at FROM ip_queue;
|
|
|
|
ALTER TABLE ip_queue ADD COLUMN registry_id INTEGER REFERENCES ip_registry(id);
|
|
ALTER TABLE ip_queue ADD COLUMN cycle_id INTEGER NOT NULL DEFAULT 1;
|
|
UPDATE ip_queue SET
|
|
registry_id = (SELECT id FROM ip_registry r WHERE r.ip_address = ip_queue.ip_address),
|
|
cycle_id = attempt_number;
|
|
|
|
-- checks.ip_id is today NOT NULL + REFERENCES ip_queue(id), which is exactly
|
|
-- what forces the cascading DELETE on ip_queue row removal (foreign_keys=ON
|
|
-- would otherwise block the delete once ip_queue's row disappears out from
|
|
-- under a referencing row). To let history outlive its ip_queue row, ip_id
|
|
-- must become nullable and checks must carry their own durable registry_id.
|
|
-- SQLite has no ALTER to relax a column's NOT NULL/REFERENCES, so the table
|
|
-- is rebuilt.
|
|
CREATE TABLE checks_new (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
registry_id INTEGER NOT NULL REFERENCES ip_registry(id),
|
|
cycle_id INTEGER NOT NULL,
|
|
ip_id INTEGER REFERENCES ip_queue(id),
|
|
ip_address TEXT NOT NULL,
|
|
attempt_number INTEGER NOT NULL,
|
|
validator_id TEXT NOT NULL DEFAULT '',
|
|
source TEXT NOT NULL,
|
|
check_type TEXT NOT NULL,
|
|
target TEXT NOT NULL DEFAULT '',
|
|
success BOOLEAN NOT NULL,
|
|
latency_ms INTEGER NOT NULL DEFAULT 0,
|
|
detail TEXT NOT NULL DEFAULT '',
|
|
checked_at TIMESTAMP NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(registry_id, cycle_id, source, check_type, target)
|
|
);
|
|
INSERT INTO checks_new (id, registry_id, cycle_id, ip_id, ip_address, attempt_number, validator_id,
|
|
source, check_type, target, success, latency_ms, detail, checked_at, created_at)
|
|
SELECT c.id, iq.registry_id, iq.cycle_id, c.ip_id, c.ip_address, c.attempt_number, c.validator_id,
|
|
c.source, c.check_type, c.target, c.success, c.latency_ms, c.detail, c.checked_at, c.created_at
|
|
FROM checks c JOIN ip_queue iq ON iq.id = c.ip_id;
|
|
DROP TABLE checks;
|
|
ALTER TABLE checks_new RENAME TO checks;
|
|
CREATE INDEX idx_checks_registry_cycle ON checks(registry_id, cycle_id);
|
|
CREATE INDEX idx_checks_ip_attempt ON checks(ip_id, attempt_number);
|
|
|
|
-- events.ip_id is already nullable, so no rebuild is needed there — just
|
|
-- add the durable registry_id (plus cycle_id, so retention pruning can cut
|
|
-- events at the same cycle boundary as checks) alongside it.
|
|
ALTER TABLE events ADD COLUMN registry_id INTEGER REFERENCES ip_registry(id);
|
|
ALTER TABLE events ADD COLUMN cycle_id INTEGER NOT NULL DEFAULT 0;
|
|
UPDATE events SET
|
|
registry_id = (SELECT registry_id FROM ip_queue WHERE ip_queue.id = events.ip_id),
|
|
cycle_id = (SELECT cycle_id FROM ip_queue WHERE ip_queue.id = events.ip_id)
|
|
WHERE ip_id IS NOT NULL;
|
|
CREATE INDEX idx_events_registry ON events(registry_id, cycle_id);
|
|
|
|
-- Configurable history retention depth, in check cycles per address. 0 (the
|
|
-- default, matching today's unbounded behavior) means keep everything.
|
|
ALTER TABLE settings ADD COLUMN history_retention_cycles INTEGER NOT NULL DEFAULT 0;
|