memory/postgres/init.sql

-- =================================================================
-- XARVIONEX MEMORY BLUEPRINT (PHASE 1.5)
-- Canonical Source of Truth for Data Structure
-- Location: /opt/xarvionex/memory/postgres/init.sql
-- =================================================================

-- 1. EXTENSIONS
-- Mengaktifkan UUID generator untuk ID yang unik secara global
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =================================================================
-- 2. IMMUTABLE HISTORY (The Write Model)
-- Tabel ini mencatat setiap kejadian tanpa pernah diubah (Append Only).
-- =================================================================
CREATE TABLE IF NOT EXISTS event_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    
    -- Identitas Event
    event_type VARCHAR(100) NOT NULL,    -- Contoh: 'SOCIAL_SIGNAL', 'CONVERSION'
    source_organ VARCHAR(50) NOT NULL,   -- Contoh: 'SENSORY_NODE_01', 'VEXA_01'
    actor_xar_id VARCHAR(100) NOT NULL,  -- Stable Anonymized ID: 'VX-USR-[HASH]'
    
    -- Polymer Energy Matrix (Phase 1.5 Spec)
    -- Menyimpan nilai energi dalam format JSONB fleksibel
    -- Contoh: {"financial": {"val": 10, "curr": "USD"}, "social": {"val": 0.8}}
    energy_matrix JSONB DEFAULT '{}'::jsonb,
    
    -- Context Data (Payload Mentah)
    payload JSONB NOT NULL,
    
    -- Tracing & Forensics
    external_ref_id VARCHAR(100),        -- ID dari sistem luar (Telegram ID / Order ID)
    correlation_id UUID,                 -- ID pelacakan lintas layanan
    
    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP WITH TIME ZONE -- Kapan Worker selesai memproses
);

-- 3. INDEXING (Performance & Safety)
-- Mempercepat pencarian berdasarkan tipe event dan waktu
CREATE INDEX IF NOT EXISTS idx_event_type ON event_logs(event_type);
CREATE INDEX IF NOT EXISTS idx_created_at ON event_logs(created_at);

-- GUARD: Hard Idempotency (Sistem Imun Level DB)
-- Mencegah satu event eksternal yang sama masuk dua kali ke dalam sejarah.
-- Jika Worker gagal memfilter di Redis, DB akan menolaknya di sini.
CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_event_ref 
ON event_logs (source_organ, external_ref_id) 
WHERE external_ref_id IS NOT NULL;


-- =================================================================
-- 4. MUTABLE STATE (The Read Model)
-- Tabel ini menyimpan status terkini sistem (Agregat).
-- Worker memperbarui tabel ini setiap kali ada event baru.
-- =================================================================
CREATE TABLE IF NOT EXISTS system_state (
    metric_key VARCHAR(50) PRIMARY KEY, -- 'financial_total', 'social_mood'
    metric_value JSONB,                 -- Nilai saat ini (Snapshot)
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 5. INITIAL STATE SEEDING
-- Menanamkan nilai awal agar Worker tidak error saat mencoba membaca/update.
INSERT INTO system_state (metric_key, metric_value) 
VALUES 
    ('financial_total', '{"value": 0.0, "currency": "USD"}'::jsonb),
    ('social_sentiment', '{"value": 0.0, "label": "NEUTRAL"}'::jsonb)
ON CONFLICT (metric_key) DO NOTHING;