28 lines
1.2 KiB
MySQL
28 lines
1.2 KiB
MySQL
|
-- Zombie resource detection + composite scoring
|
||
|
|
|
||
|
|
-- Zombie resources table
|
||
|
|
CREATE TABLE zombie_resources (
|
||
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
resource_id TEXT NOT NULL,
|
||
|
|
resource_type TEXT NOT NULL CHECK (resource_type IN ('ec2', 'rds', 'ebs', 'eip', 'nat_gateway')),
|
||
|
|
region TEXT NOT NULL,
|
||
|
|
account_id TEXT NOT NULL,
|
||
|
|
estimated_monthly_waste NUMERIC(10,2) NOT NULL DEFAULT 0,
|
||
|
|
last_activity TIMESTAMPTZ,
|
||
|
|
recommendation TEXT NOT NULL,
|
||
|
|
status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'dismissed', 'remediated')),
|
||
|
|
detected_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
UNIQUE(tenant_id, resource_id, resource_type)
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_zombie_resources_tenant ON zombie_resources(tenant_id, status, detected_at DESC);
|
||
|
|
|
||
|
|
-- RLS
|
||
|
|
ALTER TABLE zombie_resources ENABLE ROW LEVEL SECURITY;
|
||
|
|
CREATE POLICY tenant_iso_zombies ON zombie_resources
|
||
|
|
USING (tenant_id::text = current_setting('app.tenant_id', true));
|
||
|
|
|
||
|
|
-- Composite scoring columns on anomalies
|
||
|
|
ALTER TABLE anomalies ADD COLUMN IF NOT EXISTS composite_score NUMERIC(5,2);
|
||
|
|
ALTER TABLE anomalies ADD COLUMN IF NOT EXISTS score_breakdown JSONB;
|