30 lines
948 B
SQL
30 lines
948 B
SQL
-- ©AngelaMos | 2026
|
|
-- 004_attackers.sql
|
|
|
|
-- +goose Up
|
|
CREATE TABLE attackers (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
ip TEXT NOT NULL UNIQUE,
|
|
first_seen TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_seen TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
total_events BIGINT NOT NULL DEFAULT 0,
|
|
total_sessions INTEGER NOT NULL DEFAULT 0,
|
|
country_code TEXT,
|
|
country TEXT,
|
|
city TEXT,
|
|
latitude DOUBLE PRECISION,
|
|
longitude DOUBLE PRECISION,
|
|
asn INTEGER,
|
|
org TEXT,
|
|
threat_score INTEGER NOT NULL DEFAULT 0,
|
|
tool_family TEXT,
|
|
tags TEXT[] NOT NULL DEFAULT '{}'
|
|
);
|
|
|
|
CREATE INDEX idx_attackers_last_seen ON attackers (last_seen DESC);
|
|
CREATE INDEX idx_attackers_threat_score ON attackers (threat_score DESC);
|
|
CREATE INDEX idx_attackers_country ON attackers (country_code);
|
|
|
|
-- +goose Down
|
|
DROP TABLE IF EXISTS attackers;
|