73 lines
3.3 KiB
MySQL
73 lines
3.3 KiB
MySQL
|
-- ============================================================================
|
||
|
|
-- Mimecast Email Integration Tables
|
||
|
|
-- 120-day rolling retention for message logs, threat events, and message bodies
|
||
|
|
-- ============================================================================
|
||
|
|
|
||
|
|
-- Core message tracking log
|
||
|
|
CREATE TABLE IF NOT EXISTS mimecast_messages (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
sender_address TEXT,
|
||
|
|
sender_domain TEXT,
|
||
|
|
recipient_address TEXT,
|
||
|
|
subject TEXT,
|
||
|
|
direction TEXT, -- 'inbound' | 'outbound'
|
||
|
|
status TEXT, -- 'delivered' | 'rejected' | 'bounced' | 'held' | 'spam'
|
||
|
|
action TEXT,
|
||
|
|
spam_score NUMERIC,
|
||
|
|
size_bytes BIGINT,
|
||
|
|
attachment_count INT DEFAULT 0,
|
||
|
|
sent_datetime TIMESTAMPTZ,
|
||
|
|
received_datetime TIMESTAMPTZ,
|
||
|
|
delivery_datetime TIMESTAMPTZ,
|
||
|
|
route TEXT,
|
||
|
|
reject_reason TEXT,
|
||
|
|
held_reason TEXT,
|
||
|
|
source_ip TEXT,
|
||
|
|
raw JSONB, -- Full API response for forward-compat
|
||
|
|
synced_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Separate table for message bodies (large, for LLM use only)
|
||
|
|
CREATE TABLE IF NOT EXISTS mimecast_message_bodies (
|
||
|
|
message_id TEXT PRIMARY KEY REFERENCES mimecast_messages(id) ON DELETE CASCADE,
|
||
|
|
body_text TEXT,
|
||
|
|
body_html TEXT,
|
||
|
|
headers JSONB,
|
||
|
|
fetched_at TIMESTAMPTZ DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- SIEM/threat events
|
||
|
|
CREATE TABLE IF NOT EXISTS mimecast_threat_events (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
message_id TEXT REFERENCES mimecast_messages(id) ON DELETE SET NULL,
|
||
|
|
event_type TEXT, -- 'url_click' | 'attachment_sandbox' | 'impersonation' | 'spam' | 'virus'
|
||
|
|
threat_level TEXT, -- 'high' | 'medium' | 'low' | 'info'
|
||
|
|
url TEXT,
|
||
|
|
file_name TEXT,
|
||
|
|
verdict TEXT,
|
||
|
|
actor_email TEXT,
|
||
|
|
event_datetime TIMESTAMPTZ,
|
||
|
|
details JSONB, -- Full raw event payload
|
||
|
|
synced_at TIMESTAMPTZ DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Cursor-based pagination state for resumable syncs
|
||
|
|
CREATE TABLE IF NOT EXISTS mimecast_sync_state (
|
||
|
|
sync_type TEXT PRIMARY KEY, -- 'messages' | 'threats' | 'held'
|
||
|
|
last_synced_at TIMESTAMPTZ,
|
||
|
|
cursor TEXT,
|
||
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_mimecast_messages_sent_datetime ON mimecast_messages (sent_datetime DESC);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_mimecast_messages_sender_domain ON mimecast_messages (sender_domain);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_mimecast_messages_status ON mimecast_messages (status);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_mimecast_messages_direction ON mimecast_messages (direction);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_mimecast_messages_recipient ON mimecast_messages (recipient_address);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_mimecast_threat_events_datetime ON mimecast_threat_events (event_datetime DESC);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_mimecast_threat_events_type ON mimecast_threat_events (event_type);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_mimecast_threat_events_level ON mimecast_threat_events (threat_level);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_mimecast_threat_events_details ON mimecast_threat_events USING GIN (details);
|