PostgreSQL Schema (Nexa OLTP)
├── 1. Master Taksonomi & Entitas Wilayah
│   ├── taxonomy_nodes                # Pohon taksonomi 5-level (L1 - L5) & 5 pilar
│   ├── master_sectors                # Data induk 4 peran pemangku kepentingan
│   ├── master_regions                # Hierarki wilayah 38 provinsi, kab/kota, kecamatan
│   └── organization_nodes            # Entitas K/L, Pemda, TNI, POLRI beserta alias pencarian
│
├── 2. Konfigurasi Ingestion & Target Scraper
│   ├── sources                       # Direktori portal media, RSS, adaptor, cron, & rate limit
│   ├── scraping_targets              # Konfigurasi target scraping domain & link ke organisasi
│   ├── taxonomy_keywords             # Frasa kata kunci spesifik simpul taksonomi
│   ├── scraping_target_keywords      # Tabel relasi (junction) target scraping & kata kunci
│   ├── keywords                      # Master kata kunci pencarian umum
│   └── social_keywords               # Kata kunci pantauan percakapan media sosial
│
├── 3. Artikel Berita, Ekstraksi AI, & Kurasi Manusia
│   ├── articles                      # Master metadata artikel berita teranalisis AI
│   ├── article_spoks                 # Fakta terstruktur SPOK (Subjek, Predikat, Objek, Keterangan)
│   ├── article_currations            # Log status kurasi editorial manusia
│   ├── validation_decisions          # Rekam jejak koreksi label & keputusan validasi
│   └── gold_dataset_state            # Metrik akumulasi dataset acuan (gold dataset)
│
├── 4. Media Sosial & Phone Farm (Pipeline B3)
│   ├── social_trending_topics        # Master topik tren media sosial X/Twitter termutakhir
│   ├── social_trending_snapshots     # Time-series 15-menit peringkat & laju lonjakan tren
│   ├── postings                      # Arsip postingan multichannel (X, FB, IG, TikTok)
│   └── post_author                   # Profil pengunggah & metrik pengaruh akun medsos
│
├── 5. Pengadaan Barang & Jasa Pemerintah (LPSE / SIRUP)
│   ├── fact_procurement              # Master paket lelang & pengadaan per RUP ID
│   ├── fact_procurement_regions      # Relasi jangkauan wilayah multi-kabupaten paket pengadaan
│   └── fact_procurement_ai           # Hasil evaluasi inferensi AI (anomali & pemborosan)
│
├── 6. Data Sektoral Pangan, Pendidikan, & Static Datasets (B2)
│   ├── master_commodities            # Katalog komoditas pangan pokok nasional & nama alias
│   ├── fact_indicator                # Deret waktu harga pangan harian & indikator ketahanan
│   ├── master_schools                # Registri sekolah nasional Kemendikbudristek & koordinat GPS
│   ├── static_datasets               # Registri berkas bulk ingest (CSV/Excel via MinIO)
│   └── static_dataset_records        # Rekaman baris payload data statis terhashing
│
└── 7. Keamanan, Observabilitas, & Registry Sistem
    ├── users                         # Pengguna sistem & kontrol akses berbasis peran (RBAC)
    ├── audit_log                     # Log jejak audit aktivitas pengguna dan analis
    ├── module_registry               # Konfigurasi dinamis modul dashboard & navigasi UI
    └── kafka_topic_offset_samples    # Time-series sampling offset Kafka per menit

erDiagram
    master_sectors ||--o{ organization_nodes : "dikelompokkan ke"
    master_regions ||--o{ organization_nodes : "berlokasi di"
    master_regions ||--o{ fact_procurement : "lokasi utama"
    master_regions ||--o{ fact_procurement_regions : "wilayah cakupan"
    master_regions ||--o{ master_schools : "wilayah sekolah"
    master_regions ||--o{ fact_indicator : "wilayah data"

    taxonomy_nodes ||--o{ taxonomy_nodes : "induk hierarki (L1-L5)"
    taxonomy_nodes ||--o{ taxonomy_keywords : "memiliki kata kunci"
    taxonomy_nodes ||--o{ social_keywords : "memetakan"
    taxonomy_nodes ||--o{ articles : "klasifikasi taksonomi"

    sources ||--o{ articles : "menerbitkan artikel"
    organization_nodes ||--o{ scraping_targets : "memiliki target portal"
    scraping_targets ||--o{ scraping_target_keywords : "relasi kata kunci"
    taxonomy_keywords ||--o{ scraping_target_keywords : "diasosiasikan ke"

    articles ||--o{ article_spoks : "menghasilkan fakta"
    articles ||--o{ article_currations : "dikurasi pada"
    articles ||--o{ validation_decisions : "divalidasi oleh"

    fact_procurement ||--o{ fact_procurement_regions : "mencakup wilayah"
    fact_procurement ||--|| fact_procurement_ai : "dianalisa oleh AI"
    organization_nodes ||--o{ fact_procurement : "K/L/D pemilik paket"

    social_trending_topics ||--o{ social_trending_snapshots : "riwayat 15 menit"
    social_trending_topics ||--o{ postings : "dikaitkan ke tren"
    post_author ||--o{ postings : "membuat konten"

CREATE TABLE article_spoks (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    article_id TEXT NOT NULL REFERENCES articles(id::text) ON DELETE CASCADE,
    subjek JSONB NOT NULL DEFAULT '[]',     -- Contoh: ["KPK", "Penyidik"]
    predikat JSONB NOT NULL DEFAULT '[]',   -- Contoh: ["Menyita", "Menggeledah"]
    objek JSONB NOT NULL DEFAULT '[]',      -- Contoh: ["Aset Rumah Mewah", "Uang Tunai"]
    keterangan TEXT,                        -- Contoh: "di kawasan Menteng, Jakarta Pusat"
    confidence REAL DEFAULT 0.85,
    extracted_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_article_spoks_article ON article_spoks (article_id);

-- 1. Master Paket Pengadaan Nasional (SIRUP & Tender Inaproc)
CREATE TABLE fact_procurement (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    rup_id TEXT NOT NULL UNIQUE,                  -- Kode RUP dari portal LKPP
    package_name TEXT NOT NULL,                   -- Nama paket kegiatan pengadaan
    package_url TEXT,
    pagu NUMERIC(20, 2),                          -- Nilai pagu anggaran (Rupiah)
    procurement_type TEXT,                        -- Barang, Pekerjaan Konstruksi, Jasa Konsultansi
    procurement_scheme TEXT,                      -- Penyedia vs Swakelola
    method TEXT,                                  -- Tender, Pengadaan Langsung, E-Purchasing
    selection_period TEXT,
    domesticProduct BOOLEAN,                      -- Produk Dalam Negeri (PDN)
    small_business BOOLEAN,                       -- Alokasi UMKM/Koperasi
    klpd TEXT,                                    -- Nama Kementerian / Lembaga / Pemda
    work_unit TEXT,                               -- Satuan Kerja (Satker) pelaksana
    location TEXT,
    fiscal_year INTEGER,                          -- Tahun Anggaran (misal: 2026)
    source_org_node_id INTEGER REFERENCES organization_nodes(id),
    sector_id INTEGER REFERENCES master_sectors(id),
    region_id INTEGER REFERENCES master_regions(id),
    payload JSONB NOT NULL DEFAULT '{}',          -- Rincian spesifikasi lelang lengkap
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Wilayah Jangkauan Paket Multi-Kabupaten
CREATE TABLE fact_procurement_regions (
    procurement_id BIGINT NOT NULL REFERENCES fact_procurement(id) ON DELETE CASCADE,
    region_id INTEGER NOT NULL REFERENCES master_regions(id),
    source TEXT NOT NULL,                         -- manual | parsed_text
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (procurement_id, region_id)
);

-- 3. Evaluasi Anomali & Rekomendasi AI per Paket
CREATE TABLE fact_procurement_ai (
    procurement_id BIGINT PRIMARY KEY REFERENCES fact_procurement(id) ON DELETE CASCADE,
    classification TEXT NOT NULL,                 -- WAJAR | ANOMALI_TENDER | POTENSI_PEMBOROSAN
    reason TEXT,                                  -- Argumen penalaran logis dari model AI
    confidence NUMERIC(5, 4),                     -- Skor probabilitas (0.0000 s/d 1.0000)
    waste_amount NUMERIC(20, 2),                  -- Estimasi nilai pemborosan anggaran (Rp)
    model TEXT,                                   -- Nama model (misal: gpt-4o / claude-3-5-sonnet)
    prompt_version TEXT,
    evaluated_at TIMESTAMPTZ DEFAULT NOW(),
    payload JSONB NOT NULL DEFAULT '{}'
);

import {
  pgTable, uuid, text, integer, boolean, real,
  numeric, jsonb, timestamp, doublePrecision, pgEnum, primaryKey
} from "drizzle-orm/pg-core";

export const humanValidationStatusEnum = pgEnum("human_validation_status", [
  "pending", "approved", "rejected"
]);

export const articles = pgTable("articles", {
  id: uuid("id").primaryKey(),
  code: text("code").notNull().unique(),
  title: text("title").notNull(),
  dataType: text("data_type").notNull(),
  channel: text("channel"),
  sourceName: text("source_name"),
  url: text("url"),
  summary: text("summary"),
  pillar: text("pillar").notNull(),
  primaryLabel: jsonb("primary_label").notNull(),
  labels: jsonb("labels").notNull(),
  sentiment: text("sentiment"),
  confidence: real("confidence"),
  severity: integer("severity"),
  flagged: boolean("flagged").notNull().default(false),
  entities: jsonb("entities"),
  locationProvince: text("location_province"),
  locationCity: text("location_city"),
  locationLat: doublePrecision("location_lat"),
  locationLng: doublePrecision("location_lng"),
  humanValidationStatus: humanValidationStatusEnum("human_validation_status")
    .notNull()
    .default("pending"),
  publishedAt: timestamp("published_at", { withTimezone: true }),
  processedAt: timestamp("processed_at", { withTimezone: true }),
});