74 lines
3.1 KiB
MySQL
74 lines
3.1 KiB
MySQL
|
-- dd0c/portal V1 schema — PostgreSQL with RLS + Meilisearch for search
|
||
|
|
|
||
|
|
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')),
|
||
|
|
service_count INT NOT NULL DEFAULT 0,
|
||
|
|
max_services INT NOT NULL DEFAULT 50, -- Free tier: 50
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Service catalog
|
||
|
|
CREATE TABLE services (
|
||
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
type TEXT NOT NULL DEFAULT 'unknown',
|
||
|
|
owner TEXT NOT NULL DEFAULT 'unknown',
|
||
|
|
owner_source TEXT NOT NULL DEFAULT 'heuristic' CHECK (owner_source IN ('config', 'codeowners', 'aws-tag', 'heuristic')),
|
||
|
|
description TEXT,
|
||
|
|
tier TEXT DEFAULT 'medium' CHECK (tier IN ('critical', 'high', 'medium', 'low')),
|
||
|
|
lifecycle TEXT DEFAULT 'active' CHECK (lifecycle IN ('active', 'deprecated', 'decommissioned')),
|
||
|
|
links JSONB NOT NULL DEFAULT '{}',
|
||
|
|
tags JSONB NOT NULL DEFAULT '{}',
|
||
|
|
metadata JSONB NOT NULL DEFAULT '{}',
|
||
|
|
last_discovered_at TIMESTAMPTZ,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
UNIQUE(tenant_id, name)
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_services_tenant ON services(tenant_id);
|
||
|
|
CREATE INDEX idx_services_owner ON services(tenant_id, owner);
|
||
|
|
CREATE INDEX idx_services_type ON services(tenant_id, type);
|
||
|
|
|
||
|
|
-- Staged updates (partial scan results awaiting review)
|
||
|
|
CREATE TABLE staged_updates (
|
||
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
service_name TEXT NOT NULL,
|
||
|
|
source TEXT NOT NULL CHECK (source IN ('aws', 'github', 'manual')),
|
||
|
|
changes JSONB NOT NULL,
|
||
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'applied', 'rejected')),
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_staged_tenant ON staged_updates(tenant_id, status);
|
||
|
|
|
||
|
|
-- Discovery scan history
|
||
|
|
CREATE TABLE scan_history (
|
||
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
scanner TEXT NOT NULL CHECK (scanner IN ('aws', 'github')),
|
||
|
|
status TEXT NOT NULL CHECK (status IN ('success', 'partial_failure', 'failed')),
|
||
|
|
discovered INT NOT NULL DEFAULT 0,
|
||
|
|
errors TEXT[] NOT NULL DEFAULT '{}',
|
||
|
|
started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
completed_at TIMESTAMPTZ
|
||
|
|
);
|
||
|
|
|
||
|
|
-- RLS
|
||
|
|
ALTER TABLE services ENABLE ROW LEVEL SECURITY;
|
||
|
|
ALTER TABLE staged_updates ENABLE ROW LEVEL SECURITY;
|
||
|
|
ALTER TABLE scan_history ENABLE ROW LEVEL SECURITY;
|
||
|
|
|
||
|
|
CREATE POLICY tenant_iso_services ON services
|
||
|
|
USING (tenant_id::text = current_setting('app.tenant_id', true));
|
||
|
|
CREATE POLICY tenant_iso_staged ON staged_updates
|
||
|
|
USING (tenant_id::text = current_setting('app.tenant_id', true));
|
||
|
|
CREATE POLICY tenant_iso_scans ON scan_history
|
||
|
|
USING (tenant_id::text = current_setting('app.tenant_id', true));
|