dd0c/products/05-aws-cost-anomaly/migrations/001_init.sql

-- dd0c/cost V1 schema

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Tenants
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,
    tier TEXT NOT NULL DEFAULT 'free' CHECK (tier IN ('free', 'pro')),
    governance_mode TEXT NOT NULL DEFAULT 'shadow' CHECK (governance_mode IN ('shadow', 'audit', 'enforce')),
    governance_started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    stripe_customer_id TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- AWS accounts linked to tenants
CREATE TABLE aws_accounts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    account_id TEXT NOT NULL,
    account_name TEXT,
    role_arn TEXT NOT NULL,
    regions TEXT[] NOT NULL DEFAULT '{us-east-1}',
    enabled BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(tenant_id, account_id)
);

-- Cost baselines (Welford running stats)
CREATE TABLE baselines (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    account_id TEXT NOT NULL,
    resource_type TEXT NOT NULL,
    welford_count INT NOT NULL DEFAULT 0,
    welford_mean NUMERIC(12,6) NOT NULL DEFAULT 0,
    welford_m2 NUMERIC(20,6) NOT NULL DEFAULT 0,
    version INT NOT NULL DEFAULT 1,  -- Optimistic locking for concurrent updates
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(tenant_id, account_id, resource_type)
);

-- Anomaly events
CREATE TABLE anomalies (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    account_id TEXT NOT NULL,
    resource_type TEXT NOT NULL,
    region TEXT NOT NULL,
    hourly_cost NUMERIC(10,4) NOT NULL,
    score NUMERIC(5,2) NOT NULL,
    baseline_mean NUMERIC(12,6) NOT NULL,
    baseline_stddev NUMERIC(12,6) NOT NULL,
    status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'acknowledged', 'snoozed', 'expected', 'resolved')),
    snoozed_until TIMESTAMPTZ,
    tags JSONB NOT NULL DEFAULT '{}',
    detected_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_anomalies_tenant ON anomalies(tenant_id, status, detected_at DESC);

-- Remediation actions
CREATE TABLE remediation_actions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    anomaly_id UUID NOT NULL REFERENCES anomalies(id) ON DELETE CASCADE,
    action_type TEXT NOT NULL CHECK (action_type IN ('stop_instance', 'resize', 'snooze', 'mark_expected')),
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'executing', 'completed', 'failed')),
    requested_by TEXT NOT NULL,  -- Slack user ID
    requested_by_role TEXT NOT NULL DEFAULT 'viewer',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at TIMESTAMPTZ
);

-- Notification configs
CREATE TABLE notification_configs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    channel TEXT NOT NULL CHECK (channel IN ('slack', 'email')),
    config JSONB NOT NULL DEFAULT '{}',
    min_score NUMERIC(5,2) NOT NULL DEFAULT 50,
    enabled BOOLEAN NOT NULL DEFAULT true,
    UNIQUE(tenant_id, channel)
);

-- RLS
ALTER TABLE baselines ENABLE ROW LEVEL SECURITY;
ALTER TABLE anomalies ENABLE ROW LEVEL SECURITY;
ALTER TABLE remediation_actions ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_iso_baselines ON baselines
    USING (tenant_id::text = current_setting('app.tenant_id', true));
CREATE POLICY tenant_iso_anomalies ON anomalies
    USING (tenant_id::text = current_setting('app.tenant_id', true));
CREATE POLICY tenant_iso_remediation ON remediation_actions
    USING (tenant_id::text = current_setting('app.tenant_id', true));