30 lines
1.0 KiB
SQL
30 lines
1.0 KiB
SQL
-- ©AngelaMos | 2026
|
|
-- 005_sessions.sql
|
|
|
|
-- +goose Up
|
|
CREATE TABLE sessions (
|
|
id TEXT NOT NULL,
|
|
sensor_id TEXT NOT NULL REFERENCES sensors(id),
|
|
started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
ended_at TIMESTAMPTZ,
|
|
service_type service_type NOT NULL,
|
|
source_ip TEXT NOT NULL,
|
|
source_port INTEGER NOT NULL,
|
|
dest_port INTEGER NOT NULL,
|
|
client_version TEXT,
|
|
login_success BOOLEAN NOT NULL DEFAULT FALSE,
|
|
username TEXT,
|
|
command_count INTEGER NOT NULL DEFAULT 0,
|
|
mitre_techniques TEXT[] NOT NULL DEFAULT '{}',
|
|
threat_score INTEGER NOT NULL DEFAULT 0,
|
|
tags TEXT[] NOT NULL DEFAULT '{}',
|
|
PRIMARY KEY (id, started_at)
|
|
) PARTITION BY RANGE (started_at);
|
|
|
|
CREATE INDEX idx_sessions_source_ip ON sessions (source_ip);
|
|
CREATE INDEX idx_sessions_service ON sessions (service_type);
|
|
CREATE INDEX idx_sessions_started ON sessions (started_at DESC);
|
|
|
|
-- +goose Down
|
|
DROP TABLE IF EXISTS sessions;
|