Bernt
e5623d2f84
- Add Infrastructure Health section with CPU/Memory/Disk panels - Add Service Status section with PM2/Docker panels - Create GrafanaPanel component for iframe embedding - Build passes successfully
101 lines
4.3 KiB
SQL
101 lines
4.3 KiB
SQL
-- =====================================================
|
|
-- Migration 003: Multi-Tenancy Enhancement
|
|
-- =====================================================
|
|
|
|
-- Utöka boc_tenants med koncernstöd
|
|
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS org_number VARCHAR(20);
|
|
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS vat_number VARCHAR(20);
|
|
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS address TEXT;
|
|
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS phone VARCHAR(20);
|
|
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS email VARCHAR(255);
|
|
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS currency VARCHAR(3) DEFAULT 'SEK';
|
|
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS language VARCHAR(5) DEFAULT 'sv';
|
|
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS timezone VARCHAR(50) DEFAULT 'Europe/Stockholm';
|
|
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS fiscal_year_start DATE DEFAULT '2026-01-01';
|
|
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS parent_id UUID REFERENCES boc_tenants(id);
|
|
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS ledger_db VARCHAR(255);
|
|
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS settings JSONB DEFAULT '{}';
|
|
|
|
-- Skapa index för snabb lookup
|
|
CREATE INDEX IF NOT EXISTS idx_tenants_slug ON boc_tenants(slug);
|
|
CREATE INDEX IF NOT EXISTS idx_tenants_parent ON boc_tenants(parent_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tenants_status ON boc_tenants(status);
|
|
|
|
-- Tenant-avgränsning på alla tabeller
|
|
-- Lägg till tenant_id där det saknas
|
|
DO $$
|
|
BEGIN
|
|
-- Kolla vilka tabeller som saknar tenant_id
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'boc_tickets' AND column_name = 'tenant_id') THEN
|
|
ALTER TABLE boc_tickets ADD COLUMN tenant_id UUID REFERENCES boc_tenants(id);
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'boc_deals' AND column_name = 'tenant_id') THEN
|
|
ALTER TABLE boc_deals ADD COLUMN tenant_id UUID REFERENCES boc_tenants(id);
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'boc_projects' AND column_name = 'tenant_id') THEN
|
|
ALTER TABLE boc_projects ADD COLUMN tenant_id UUID REFERENCES boc_tenants(id);
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Uppdatera befintliga poster med default tenant
|
|
UPDATE boc_tickets SET tenant_id = '11111111-1111-1111-1111-111111111111' WHERE tenant_id IS NULL;
|
|
UPDATE boc_deals SET tenant_id = '11111111-1111-1111-1111-111111111111' WHERE tenant_id IS NULL;
|
|
UPDATE boc_projects SET tenant_id = '11111111-1111-1111-1111-111111111111' WHERE tenant_id IS NULL;
|
|
|
|
-- Skapa tenant-isolerade vyer
|
|
CREATE OR REPLACE VIEW v_tenant_summary AS
|
|
SELECT
|
|
t.id as tenant_id,
|
|
t.name as tenant_name,
|
|
t.slug,
|
|
t.status,
|
|
COUNT(DISTINCT u.id) as user_count,
|
|
COUNT(DISTINCT c.id) as customer_count,
|
|
COUNT(DISTINCT d.id) as deal_count,
|
|
COUNT(DISTINCT e.id) as employee_count,
|
|
COALESCE(SUM(d.value), 0) as total_pipeline
|
|
FROM boc_tenants t
|
|
LEFT JOIN boc_users u ON u.tenant_id = t.id
|
|
LEFT JOIN boc_customers c ON c.tenant_id = t.id
|
|
LEFT JOIN boc_deals d ON d.tenant_id = t.id AND d.status = 'open'
|
|
LEFT JOIN boc_employees e ON e.tenant_id = t.id
|
|
WHERE t.status = 'active'
|
|
GROUP BY t.id, t.name, t.slug, t.status;
|
|
|
|
-- Koncern-vy (hierarkisk)
|
|
CREATE OR REPLACE VIEW v_tenant_hierarchy AS
|
|
WITH RECURSIVE tenant_tree AS (
|
|
-- Root tenants (koncerner)
|
|
SELECT
|
|
id, name, slug, parent_id, 0 as level,
|
|
id as root_id, name as root_name
|
|
FROM boc_tenants
|
|
WHERE parent_id IS NULL AND status = 'active'
|
|
|
|
UNION ALL
|
|
|
|
-- Child tenants (dotterbolag)
|
|
SELECT
|
|
t.id, t.name, t.slug, t.parent_id, tt.level + 1,
|
|
tt.root_id, tt.root_name
|
|
FROM boc_tenants t
|
|
JOIN tenant_tree tt ON t.parent_id = tt.id
|
|
WHERE t.status = 'active'
|
|
)
|
|
SELECT * FROM tenant_tree;
|
|
|
|
-- Audit log för tenant-ändringar
|
|
CREATE TABLE IF NOT EXISTS boc_tenant_audit (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES boc_tenants(id),
|
|
action VARCHAR(50) NOT NULL, -- created, updated, deleted, switched
|
|
performed_by UUID REFERENCES boc_users(id),
|
|
details JSONB,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_tenant_audit_tenant ON boc_tenant_audit(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tenant_audit_created ON boc_tenant_audit(created_at);
|