Berkas

docs/SCHEMA.sql

Baris

1.318

Karakter

57.149

Sasaran mesin

PostgreSQL 13+

-- =====================================================================
--  MONEV PIDSUS — KEJAKSAAN TINGGI JAWA BARAT
--  Skema basis data kanonik untuk monitoring dan evaluasi tahap
--  penyelidikan (LID) dan penyidikan (DIK) bidang pidana khusus.
--
--  Sasaran mesin  : PostgreSQL 13 atau lebih baru (memakai gen_random_uuid()
--                   bawaan; tidak ada ekstensi pihak ketiga yang diperlukan).
--  Berkas ini     : SUMBER KEBENARAN skema. Layar "Skema Basis Data" pada
--                   aplikasi hanya menampilkan salinan berkas ini.
--  Data contoh    : TIDAK ADA pada berkas ini. Berkas ini murni definisi
--                   struktur; tidak satu pun baris perkara nyata dimuat.
--
-- ---------------------------------------------------------------------
--  DISIPLIN MIGRASI: EXPAND-CONTRACT (perluas dahulu, susutkan kemudian)
-- ---------------------------------------------------------------------
--  Perubahan skema pada sistem yang sudah berjalan TIDAK PERNAH dilakukan
--  dengan satu rilis yang mengganti kolom. Urutannya wajib:
--
--    1. EXPAND   : tambah kolom/tabel baru dalam keadaan NULLABLE, tanpa
--                  menyentuh yang lama. Aplikasi versi lama tetap jalan.
--    2. TULIS GANDA : aplikasi menulis ke kolom lama DAN kolom baru.
--    3. ISI ULANG   : isi kolom baru untuk baris lama secara bertahap
--                     (batch), bukan satu UPDATE besar yang mengunci tabel.
--    4. BACA BARU   : pembacaan dipindah ke kolom baru; kolom lama menjadi
--                     hanya-tulis. Verifikasi selama minimal satu siklus
--                     pelaporan penuh.
--    5. CONTRACT    : baru pada RILIS TERPISAH kolom lama dihapus.
--
--  Aturan yang menyertainya:
--    - Migrasi yang sudah diterapkan TIDAK PERNAH disunting. Koreksi
--      dilakukan dengan migrasi baru.
--    - Tidak ada DROP COLUMN atau DROP TABLE pada rilis yang sama dengan
--      penambahannya.
--    - Setiap migrasi wajib punya jalur mundur yang tertulis.
--
-- ---------------------------------------------------------------------
--  ATURAN TIPE DATA YANG TIDAK DAPAT DITAWAR
-- ---------------------------------------------------------------------
--  UANG   : disimpan sebagai numeric(20,2) DITAMBAH kode mata uang ISO 4217
--           (char(3), mis. 'IDR'). TIDAK PERNAH sebagai teks "Rp 1.000,-",
--           tidak pernah float, tidak pernah "juta" atau "miliar" sebagai
--           satuan tersirat. Teks asli dari dokumen disimpan terpisah pada
--           kolom *_raw agar jejak sumber tidak hilang. Angka yang tidak
--           membawa mata uangnya bukan jumlah uang, melainkan angka lepas.
--
--  TANGGAL: disimpan sebagai DATE sungguhan. Teks tanggal apa adanya dari
--           dokumen sumber disimpan pada kolom *_raw yang terpisah.
--           "12 Januari 2026" adalah bukti; 2026-01-12 adalah data.
--           Keduanya disimpan karena keduanya dibutuhkan: yang satu untuk
--           dihitung, yang satu untuk dipertanggungjawabkan.
--
--  WAKTU  : timestamptz, zona penyimpanan UTC, zona tampilan Asia/Jakarta.
--
--  ENUM   : nilai kanonik berbahasa Inggris (mis. 'LID', 'APPROVED').
--           Label bahasa Indonesia hanya ada di lapisan tampilan. Label
--           TIDAK PERNAH disimpan dan tidak pernah dipakai membandingkan.
--
--  KOSONG : NULL berarti "tidak diketahui". Nol berarti "sudah diukur dan
--           hasilnya nol". Ketiadaan baris berarti "belum ada". Ketiganya
--           berbeda dan skema ini menjaga perbedaan itu — lihat tabel
--           zero_declaration.
-- =====================================================================


-- =====================================================================
--  BAGIAN 1 — TIPE TERKENDALI (ENUM)
--  Setiap nilai di bawah ini sepadan persis dengan kontrak kosakata domain
--  (contracts/domain_vocabulary.yaml) dan dengan lib/domain.ts pada
--  aplikasi. Menambah nilai baru wajib dilakukan di kedua tempat pada
--  rilis yang sama.
-- =====================================================================

CREATE TYPE monev_org_level AS ENUM ('KEJATI', 'KEJARI', 'CABJARI');

CREATE TYPE monev_stage_type AS ENUM ('LID', 'DIK');

CREATE TYPE monev_stage_origin AS ENUM (
  'CURRENT_YEAR',      -- Tahun berjalan
  'OPENING_BACKLOG',   -- Tunggakan posisi awal tahun
  'LEGACY_UNKNOWN'     -- Warisan sistem lama, asal tidak dapat dipastikan
);

CREATE TYPE monev_stage_status AS ENUM (
  'ACTIVE',            -- Proses
  'PROMOTED',          -- Naik tahap (LID ke DIK, atau DIK ke TUT)
  'STOPPED',           -- Dihentikan
  'TRANSFERRED_OUT',   -- Diserahkan / dilimpahkan
  'TAKEN_OVER',        -- Diambil alih
  'CORRECTED'          -- Dikoreksi; tidak dihitung pada ember laporan mana pun
);

CREATE TYPE monev_stage_event_code AS ENUM (
  'STAGE_STARTED',
  'PROMOTED_TO_DIK',
  'PROMOTED_TO_TUT',
  'STOPPED',
  'TRANSFERRED_OUT',
  'TAKEN_OVER',
  'RESPONSIBILITY_TRANSFERRED',
  'REOPENED',
  'CORRECTED'
);

CREATE TYPE monev_change_state AS ENUM (
  'DRAFT',       -- Draf
  'SUBMITTED',   -- Diajukan
  'RETURNED',    -- Dikembalikan
  'APPROVED',    -- Disetujui
  'REJECTED',    -- Ditolak
  'CANCELLED',   -- Dibatalkan
  'SUPERSEDED'   -- Digantikan versi baru
);

CREATE TYPE monev_order_type AS ENUM (
  'ORIGINAL',     -- Asli
  'EXTENSION',    -- Perpanjangan
  'AMENDMENT',    -- Perubahan
  'REPLACEMENT',  -- Pengganti
  'SUCCESSOR'     -- Penerus
);

CREATE TYPE monev_signature_state AS ENUM (
  'NOT_ASSESSED',
  'APPEARS_UNSIGNED',
  'WET_SIGNATURE_VISIBLE',
  'ELECTRONIC_SIGNATURE_VISIBLE',
  'CRYPTOGRAPHICALLY_VALIDATED',
  'CRYPTOGRAPHIC_VALIDATION_FAILED',
  'MANUALLY_CONFIRMED_BY_ADMIN'
);

CREATE TYPE monev_classification AS ENUM (
  'INTERNAL',
  'RESTRICTED',
  'HIGHLY_RESTRICTED'
);

CREATE TYPE monev_sector_code AS ENUM (
  'FOOD_SELF_SUFFICIENCY',
  'ENERGY',
  'WATER',
  'CREATIVE_ECONOMY',
  'GREEN_ECONOMY',
  'BLUE_ECONOMY'
);

CREATE TYPE monev_target_source AS ENUM (
  'MANUAL',          -- Ditetapkan manusia, wajib beralasan
  'POLICY_DERIVED'   -- Diturunkan dari workflow_policy, tidak disimpan
);

CREATE TYPE monev_ocr_state AS ENUM (
  'TEXT_LAYER',    -- Ada lapisan teks, ekstraksi dapat berjalan
  'OCR_REQUIRED',  -- Hasil pindai; belum dapat diekstraksi
  'OCR_COMPLETE'   -- OCR sudah dijalankan
);

CREATE TYPE monev_submission_kind AS ENUM ('DOKUMEN', 'MANUAL', 'IMPOR');

CREATE TYPE monev_provenance_source AS ENUM ('EKSTRAKSI', 'MANUAL', 'IMPOR');

CREATE TYPE monev_policy_status AS ENUM (
  'DRAFT_BASELINE',  -- Nilai dasar, sebagian belum dikonfirmasi pemilik proses
  'ACTIVE',          -- Berlaku
  'SUPERSEDED'       -- Digantikan versi yang lebih baru
);

-- Kunci medan hasil ekstraksi. Sepadan dengan FieldKey pada lib/extract.ts
-- dan dengan schemas/field-mapping-dictionary.csv. Karena tipe ini terkendali,
-- sebuah usulan ekstraksi tidak dapat menyebut medan yang tidak ada di kamus.
CREATE TYPE monev_field_key AS ENUM (
  'sprin_number_raw',
  'sprin_issued_on',
  'stage_type',
  'organization_id',
  'lapdu_number_raw',
  'case_position',
  'obstacle',
  'target_on',
  'recommendation',
  'sector_codes'
);

CREATE TYPE monev_import_state AS ENUM (
  'RECEIVED',
  'PARSED',
  'PARTIALLY_RESOLVED',
  'RESOLVED',
  'REJECTED'
);

CREATE TYPE monev_import_row_class AS ENUM (
  'DATA_ROW',           -- Satu baris data perkara
  'HEADER',             -- Baris judul kolom
  'SUBHEADER',          -- Judul kelompok di tengah tabel
  'SUBTOTAL',           -- Jumlah antara; TIDAK PERNAH dihitung sebagai perkara
  'GRAND_TOTAL',        -- Jumlah keseluruhan; TIDAK PERNAH dihitung
  'NIHIL_DECLARATION',  -- Pernyataan nihil satu satker
  'NOTE',               -- Catatan kaki atau keterangan
  'BLANK',              -- Baris kosong
  'UNRESOLVED'          -- Tidak dapat diklasifikasikan; wajib beralasan
);

CREATE TYPE monev_finding_severity AS ENUM (
  'BLOCKER',  -- Menghentikan persetujuan sampai diselesaikan
  'ERROR',    -- Wajib ditindaklanjuti, tidak menghentikan
  'WARNING'   -- Ditampilkan sebagai peringatan
);


-- =====================================================================
--  BAGIAN 2 — IDENTITAS DAN ORGANISASI
-- =====================================================================

-- ---------------------------------------------------------------------
-- organization — data induk satuan kerja.
-- 21 baris pada lingkup Jawa Barat: 1 Kejati sebagai induk dan 20 Kejari.
-- ---------------------------------------------------------------------
CREATE TABLE organization (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Kode satker yang stabil dan terkendali. Inilah kunci bisnis; nama dapat
  -- berubah karena pemekaran wilayah, kode tidak.
  code          text NOT NULL,

  level         monev_org_level NOT NULL,
  name          text NOT NULL,

  -- Hierarki: Kejari bernaung pada Kejati, Cabjari pada Kejari.
  parent_id     uuid NULL REFERENCES organization (id) ON DELETE RESTRICT,

  -- Ejaan lain yang muncul pada dokumen sumber, mis.
  -- {'Kejaksaan Negeri Kota Bandung','Kejari Bandung','KN Bandung'}.
  -- Dipakai untuk MERESOLUSI teks dokumen menjadi organization.id.
  -- Nama satker pada perkara TIDAK PERNAH disimpan sebagai teks bebas.
  aliases       text[] NOT NULL DEFAULT '{}',

  active        boolean NOT NULL DEFAULT true,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT uq_organization_code UNIQUE (code),
  CONSTRAINT uq_organization_name UNIQUE (name),

  -- Tepat satu tingkat yang tidak berinduk, yaitu Kejati.
  CONSTRAINT ck_organization_parent_by_level
    CHECK ((level = 'KEJATI') = (parent_id IS NULL)),

  CONSTRAINT ck_organization_not_self_parent
    CHECK (parent_id IS DISTINCT FROM id),

  CONSTRAINT ck_organization_code_shape
    CHECK (code ~ '^[A-Z0-9-]{3,40}$')
);

CREATE INDEX ix_organization_parent ON organization (parent_id);

-- Pencarian alias saat meresolusi nama satker dari dokumen.
CREATE INDEX ix_organization_aliases ON organization USING gin (aliases);

COMMENT ON TABLE organization IS
  'Data induk satuan kerja. Setiap penyebutan satker pada perkara wajib mengacu ke sini.';
COMMENT ON COLUMN organization.aliases IS
  'Ejaan alternatif pada dokumen sumber; dipakai untuk resolusi, bukan untuk tampilan.';


-- ---------------------------------------------------------------------
-- reporting_cycle — periode pelaporan beserta tanggal potongnya.
-- Setiap angka rekap hanya bermakna bila disertai tanggal potongnya.
-- ---------------------------------------------------------------------
CREATE TABLE reporting_cycle (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  year          integer NOT NULL,
  cycle_start   date NOT NULL,
  cycle_end     date NOT NULL,

  -- Tanggal potong: keadaan dunia yang dipotret laporan ini.
  cutoff_date   date NOT NULL,

  -- Siklus beku tidak menerima perubahan lagi. Koreksi setelah pembekuan
  -- dilakukan lewat penggantian (supersession) yang terlihat, bukan lewat
  -- penyuntingan diam-diam atas angka yang sudah diterbitkan.
  frozen        boolean NOT NULL DEFAULT false,
  frozen_by     text NULL,
  frozen_at     timestamptz NULL,

  created_at    timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT uq_reporting_cycle_year_start UNIQUE (year, cycle_start),
  CONSTRAINT ck_reporting_cycle_range CHECK (cycle_end >= cycle_start),
  CONSTRAINT ck_reporting_cycle_cutoff
    CHECK (cutoff_date >= cycle_start AND cutoff_date <= cycle_end),
  CONSTRAINT ck_reporting_cycle_year CHECK (year BETWEEN 2000 AND 2100),
  CONSTRAINT ck_reporting_cycle_frozen_fields
    CHECK (frozen = false OR (frozen_by IS NOT NULL AND frozen_at IS NOT NULL))
);

COMMENT ON TABLE reporting_cycle IS
  'Periode pelaporan dan tanggal potongnya. Angka tanpa tanggal potong tidak dapat dibandingkan.';


-- ---------------------------------------------------------------------
-- workflow_policy — nilai kebijakan berversi (batas waktu SLA, ambang
-- urgensi, sakelar penghitungan). Nilai kebijakan TIDAK PERNAH ditanam
-- pada kode program: mengubah SLA tidak boleh diam-diam mengubah laporan
-- tahun lalu. Setiap laporan menyebut versi kebijakan yang dipakainya.
-- ---------------------------------------------------------------------
CREATE TABLE workflow_policy (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code            text NOT NULL,               -- mis. 'MONEV_DEFAULT'
  version         text NOT NULL,               -- mis. '3.0-draft'
  status          monev_policy_status NOT NULL,
  effective_from  date NOT NULL,
  effective_to    date NULL,

  -- Nama kolom sengaja BUKAN "values": VALUES adalah kata kunci SQL yang
  -- dicadangkan, sehingga kolom bernama values memaksa tanda kutip ganda
  -- pada setiap kueri dan menjadi sumber kesalahan ketik yang senyap.
  -- Isi contoh: {"lidSlaDays":83,"dikSlaDays":120,"approachingRatio":0.8,
  --              "urgentRatio":0.9,"criticalAgeDays":365}
  policy_values   jsonb NOT NULL,

  -- Daftar kunci yang masih menunggu konfirmasi pemilik proses. Ditampilkan
  -- apa adanya di antarmuka; nilai yang belum dikonfirmasi tidak boleh
  -- disajikan seolah-olah sudah final.
  unconfirmed_keys text[] NOT NULL DEFAULT '{}',

  created_by      text NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT uq_workflow_policy_code_version UNIQUE (code, version),
  CONSTRAINT ck_workflow_policy_range
    CHECK (effective_to IS NULL OR effective_to >= effective_from),
  CONSTRAINT ck_workflow_policy_values_object
    CHECK (jsonb_typeof(policy_values) = 'object')
);

-- Hanya boleh ada satu versi berstatus ACTIVE untuk tiap kode kebijakan.
CREATE UNIQUE INDEX uq_workflow_policy_one_active
  ON workflow_policy (code)
  WHERE status = 'ACTIVE';

COMMENT ON TABLE workflow_policy IS
  'Nilai kebijakan berversi. Laporan menyebut versi yang dipakai sehingga angka lama tetap dapat direproduksi.';


-- =====================================================================
--  BAGIAN 3 — PERKARA DAN TAHAP
-- =====================================================================

-- ---------------------------------------------------------------------
-- kase — satu materi perkara.
--
-- MENGAPA NAMANYA "kase" DAN BUKAN "case":
--   CASE adalah kata kunci SQL yang dicadangkan (ekspresi CASE ... WHEN).
--   Tabel bernama case memaksa penulisan "case" dengan tanda kutip ganda
--   pada SETIAP kueri, view, dan migrasi selamanya. Sekali seorang penulis
--   kueri lupa tanda kutipnya, galat yang muncul menyesatkan dan jauh dari
--   penyebabnya. Nama "kase" dipilih karena bebas kutip, mendekati istilah
--   aslinya, dan tidak akan pernah bertabrakan dengan kata kunci baru.
--   Alternatif yang sama sahnya adalah monev_case; yang penting adalah
--   TIDAK memakai kata yang dicadangkan.
--
-- Satu perkara tetap satu baris di sini walaupun melewati LID lalu DIK.
-- Naik tahap TIDAK membuat perkara baru — ia membuat stage_record baru.
-- ---------------------------------------------------------------------
CREATE TABLE kase (
  id                       uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Kode perkara yang stabil dan dapat diucapkan manusia, mis. 'PS-2026-0041'.
  code                     text NOT NULL,

  title                    text NOT NULL,

  -- Nomor laporan pengaduan masyarakat. Opsional pada MVP: banyak perkara
  -- tidak berasal dari lapdu. Kosong berarti tidak ada, bukan gagal.
  lapdu_number_raw         text NULL,
  lapdu_number_normalized  text NULL,

  organization_id          uuid NOT NULL REFERENCES organization (id) ON DELETE RESTRICT,
  classification           monev_classification NOT NULL DEFAULT 'RESTRICTED',
  created_on               date NOT NULL,
  created_by               text NOT NULL,
  created_at               timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT uq_kase_code UNIQUE (code),
  CONSTRAINT ck_kase_title_not_blank CHECK (btrim(title) <> ''),

  -- Bila nomor mentah ada, bentuk ternormalisasinya wajib ada juga.
  -- Tanpa aturan ini pencarian duplikat akan melewatkan baris secara senyap.
  CONSTRAINT ck_kase_lapdu_pair
    CHECK ((lapdu_number_raw IS NULL) = (lapdu_number_normalized IS NULL))
);

CREATE INDEX ix_kase_organization ON kase (organization_id);
CREATE INDEX ix_kase_lapdu_normalized ON kase (lapdu_number_normalized)
  WHERE lapdu_number_normalized IS NOT NULL;

COMMENT ON TABLE kase IS
  'Satu materi perkara lintas tahap. Dinamai kase karena CASE adalah kata kunci SQL yang dicadangkan.';


-- ---------------------------------------------------------------------
-- stage_record — SATUAN HITUNG SELURUH LAPORAN.
-- Satu tahap penyelidikan atau penyidikan yang ditangani satu satker pada
-- satu tahun pelaporan. Semua metrik rekap menghitung baris tabel ini.
-- ---------------------------------------------------------------------
CREATE TABLE stage_record (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  case_id             uuid NOT NULL REFERENCES kase (id) ON DELETE RESTRICT,

  stage_type          monev_stage_type NOT NULL,
  origin              monev_stage_origin NOT NULL,

  -- Satker penanggung jawab pada saat ini.
  organization_id     uuid NOT NULL REFERENCES organization (id) ON DELETE RESTRICT,

  -- Tanggal surat perintah (Sprin) asli. Awal penghitungan usia dan batas waktu.
  started_on          date NOT NULL,

  -- Status TERPROYEKSI dari stage_event. Kolom ini adalah hasil turunan yang
  -- disimpan demi kecepatan kueri, BUKAN medan yang boleh diketik operator.
  -- Sumber kebenarannya tetap stage_event; proyektor menghitung ulang kolom
  -- ini setiap kali sebuah peristiwa direkam.
  projected_status    monev_stage_status NOT NULL DEFAULT 'ACTIVE',

  -- Cermin boolean dari projected_status = 'ACTIVE'.
  -- Ada bukan sebagai kemudahan, melainkan supaya keunikan nomor surat pada
  -- tahap yang masih aktif dapat dijamin oleh indeks (lihat official_order).
  -- CHECK di bawah membuat kolom ini mustahil melenceng dari projected_status.
  is_active           boolean NOT NULL DEFAULT true,

  reporting_year      integer NOT NULL,
  reporting_cycle_id  uuid NULL REFERENCES reporting_cycle (id) ON DELETE RESTRICT,

  -- Versi kebijakan yang berlaku saat catatan ini disetujui, sehingga angka
  -- lama tetap dapat direproduksi walau kebijakan berubah.
  workflow_policy_id  uuid NULL REFERENCES workflow_policy (id) ON DELETE RESTRICT,

  change_state        monev_change_state NOT NULL DEFAULT 'DRAFT',
  submitted_by        text NOT NULL,
  approved_by         text NULL,
  approved_on         date NULL,

  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),

  -- Diperlukan sebagai sasaran kunci asing gabungan dari official_order.
  CONSTRAINT uq_stage_record_id_active UNIQUE (id, is_active),

  CONSTRAINT ck_stage_record_active_mirror
    CHECK (is_active = (projected_status = 'ACTIVE')),

  CONSTRAINT ck_stage_record_year CHECK (reporting_year BETWEEN 2000 AND 2100),

  -- Disetujui berarti ada penyetuju dan tanggalnya; dan sebaliknya.
  CONSTRAINT ck_stage_record_approval_pair
    CHECK ((change_state = 'APPROVED')
           = (approved_by IS NOT NULL AND approved_on IS NOT NULL)),

  -- MAKER-CHECKER PADA LAPISAN DATA.
  -- Penyetuju wajib berbeda dari pengaju. Aturan ini ditegakkan di basis
  -- data, bukan hanya di antarmuka, karena kontrol yang hanya hidup di layar
  -- akan hilang begitu ada satu skrip yang menulis langsung ke tabel.
  CONSTRAINT ck_stage_record_approver_differs
    CHECK (approved_by IS NULL
           OR lower(btrim(approved_by)) <> lower(btrim(submitted_by))),

  CONSTRAINT ck_stage_record_approved_on_order
    CHECK (approved_on IS NULL OR approved_on >= started_on)
);

-- Indeks pada kolom yang benar-benar dipakai menyaring pada layar Rekap,
-- Daftar Perkara, dan rekap per satker.
CREATE INDEX ix_stage_record_organization ON stage_record (organization_id);
CREATE INDEX ix_stage_record_started_on ON stage_record (started_on);
CREATE INDEX ix_stage_record_status ON stage_record (projected_status);
CREATE INDEX ix_stage_record_reporting_year ON stage_record (reporting_year);
CREATE INDEX ix_stage_record_case ON stage_record (case_id);

-- Kombinasi yang dipakai hampir setiap kueri rekap: satker + tahun + tahap.
CREATE INDEX ix_stage_record_recap
  ON stage_record (reporting_year, organization_id, stage_type, projected_status);

-- Tahap yang masih berjalan adalah subhimpunan kecil yang paling sering
-- ditanyakan (pemantauan batas waktu).
CREATE INDEX ix_stage_record_active_started
  ON stage_record (started_on)
  WHERE is_active;

COMMENT ON TABLE stage_record IS
  'Satuan hitung seluruh laporan: satu tahap LID atau DIK pada satu satker dan satu tahun pelaporan.';
COMMENT ON COLUMN stage_record.projected_status IS
  'Hasil proyeksi dari stage_event. Tidak pernah disunting manual.';
COMMENT ON COLUMN stage_record.is_active IS
  'Cermin dari projected_status = ACTIVE, dijaga CHECK; menjadi sasaran kunci asing gabungan official_order.';


-- ---------------------------------------------------------------------
-- official_order — surat perintah (Sprin) yang menopang sebuah tahap.
-- Satu tahap dapat memiliki beberapa surat: asli, perpanjangan, perubahan,
-- pengganti, penerus. Membedakan asli dari susulan adalah keharusan hukum.
-- ---------------------------------------------------------------------
CREATE TABLE official_order (
  id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  stage_record_id    uuid NOT NULL,

  -- Salinan status aktif tahap induk. Tidak diisi aplikasi secara bebas:
  -- kunci asing gabungan di bawah memaksa nilainya selalu sama dengan
  -- stage_record.is_active, dan ON UPDATE CASCADE memperbaruinya otomatis
  -- ketika tahap ditutup. Inilah yang membuat indeks unik parsial di bawah
  -- benar-benar berbicara tentang "tahap yang masih aktif".
  stage_is_active    boolean NOT NULL,

  order_type         monev_order_type NOT NULL,

  -- Nomor persis seperti tertulis pada dokumen, termasuk spasi dan garis
  -- miringnya. Ini bukti, jangan pernah dirapikan di tempat.
  number_raw         text NOT NULL,

  -- Bentuk ternormalisasi untuk pencocokan dan deteksi duplikat
  -- (huruf besar, tanpa spasi, pemisah dibakukan).
  number_normalized  text NOT NULL,

  issued_on          date NOT NULL,
  -- Teks tanggal apa adanya dari dokumen, mis. '12 Januari 2026'.
  issued_on_raw      text NULL,

  signature_state    monev_signature_state NOT NULL DEFAULT 'NOT_ASSESSED',

  created_at         timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT fk_official_order_stage
    FOREIGN KEY (stage_record_id, stage_is_active)
    REFERENCES stage_record (id, is_active)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,

  CONSTRAINT ck_official_order_number_raw_not_blank
    CHECK (btrim(number_raw) <> ''),
  CONSTRAINT ck_official_order_number_normalized_shape
    CHECK (number_normalized = upper(number_normalized)
           AND number_normalized !~ '[[:space:]]')
);

-- ATURAN MUTU DATA DQ_DUPLICATE_ORDER_EXACT (tingkat BLOCKER).
-- Satu nomor surat ternormalisasi tidak boleh menempel pada dua tahap yang
-- sama-sama aktif. Bila terjadi, satu perkara akan terhitung dua kali pada
-- rekap. Ini ditegakkan indeks, bukan pemeriksaan aplikasi, karena dua
-- proses yang menulis bersamaan dapat lolos dari pemeriksaan aplikasi.
-- Nomor yang sama tetap boleh muncul pada tahap yang SUDAH ditutup, mis.
-- ketika sebuah tahap dikoreksi lalu dicatat ulang.
CREATE UNIQUE INDEX uq_official_order_number_active
  ON official_order (number_normalized)
  WHERE stage_is_active;

-- Satu tahap hanya boleh punya satu surat perintah ASLI.
CREATE UNIQUE INDEX uq_official_order_one_original
  ON official_order (stage_record_id)
  WHERE order_type = 'ORIGINAL';

CREATE INDEX ix_official_order_stage ON official_order (stage_record_id);
CREATE INDEX ix_official_order_number ON official_order (number_normalized);
CREATE INDEX ix_official_order_issued_on ON official_order (issued_on);

COMMENT ON TABLE official_order IS
  'Surat perintah (Sprin) pendukung tahap. Asli dan susulan wajib dibedakan lewat order_type.';


-- ---------------------------------------------------------------------
-- evidence_document — berkas bukti yang menempel pada sebuah tahap.
-- ---------------------------------------------------------------------
CREATE TABLE evidence_document (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  stage_record_id  uuid NOT NULL REFERENCES stage_record (id) ON DELETE RESTRICT,

  document_type    text NOT NULL,        -- mis. 'SPRIN', 'LAPORAN_PERKEMBANGAN'
  number_raw       text NULL,
  issued_on        date NULL,
  issued_on_raw    text NULL,

  pages            integer NOT NULL,
  size_bytes       bigint NOT NULL,

  signature_state  monev_signature_state NOT NULL DEFAULT 'NOT_ASSESSED',
  classification   monev_classification NOT NULL DEFAULT 'RESTRICTED',
  ocr_state        monev_ocr_state NOT NULL,

  -- Kunci objek pada penyimpanan berkas. Berkas TIDAK disimpan di dalam
  -- basis data; yang disimpan di sini adalah rujukan dan sidik jarinya.
  storage_key      text NOT NULL,

  -- Sidik jari isi berkas. Membuktikan berkas yang diunduh hari ini sama
  -- dengan berkas yang disetujui dahulu.
  checksum_sha256  text NULL,

  uploaded_by      text NOT NULL,
  uploaded_at      timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT uq_evidence_document_storage_key UNIQUE (storage_key),
  CONSTRAINT ck_evidence_document_pages CHECK (pages > 0),
  CONSTRAINT ck_evidence_document_size CHECK (size_bytes > 0),
  CONSTRAINT ck_evidence_document_checksum
    CHECK (checksum_sha256 IS NULL OR checksum_sha256 ~ '^[0-9a-f]{64}$')
);

CREATE INDEX ix_evidence_document_stage ON evidence_document (stage_record_id);
CREATE INDEX ix_evidence_document_ocr_state ON evidence_document (ocr_state);

COMMENT ON TABLE evidence_document IS
  'Berkas bukti yang menempel pada tahap. Isi berkas ada di penyimpanan objek; di sini rujukan dan checksum-nya.';


-- ---------------------------------------------------------------------
-- stage_event — riwayat peristiwa. SUMBER KEBENARAN status tahap.
-- Status tidak pernah diubah dengan menimpa; ia berubah karena sebuah
-- peristiwa direkam, dan peristiwa itu menyebut tanggal berlaku serta
-- dokumen yang mendasarinya.
-- ---------------------------------------------------------------------
CREATE TABLE stage_event (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  stage_record_id  uuid NOT NULL REFERENCES stage_record (id) ON DELETE RESTRICT,

  code             monev_stage_event_code NOT NULL,

  -- Tanggal peristiwa BERLAKU menurut dokumen, bukan tanggal pengetikan.
  effective_on     date NOT NULL,

  -- Tujuan pelimpahan. Wajib untuk peristiwa penyerahan: laporan yang
  -- menyebut sebuah perkara diserahkan tanpa menyebut kepada siapa tidak
  -- dapat dipertanggungjawabkan.
  destination      text NULL,

  reason           text NULL,

  -- Dokumen yang mendasari peristiwa ini.
  evidence_id      uuid NULL REFERENCES evidence_document (id) ON DELETE RESTRICT,

  recorded_by      text NOT NULL,
  recorded_at      timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT ck_stage_event_destination_required
    CHECK (code <> 'TRANSFERRED_OUT' OR btrim(coalesce(destination, '')) <> ''),

  -- Peristiwa yang menutup atau membalik keadaan wajib menyebut alasan.
  CONSTRAINT ck_stage_event_reason_required
    CHECK (code NOT IN ('STOPPED', 'CORRECTED', 'REOPENED')
           OR btrim(coalesce(reason, '')) <> '')
);

CREATE INDEX ix_stage_event_stage_effective ON stage_event (stage_record_id, effective_on);
CREATE INDEX ix_stage_event_code ON stage_event (code);

-- Sebuah tahap hanya dimulai sekali.
CREATE UNIQUE INDEX uq_stage_event_one_start
  ON stage_event (stage_record_id)
  WHERE code = 'STAGE_STARTED';

COMMENT ON TABLE stage_event IS
  'Riwayat peristiwa tahap. Sumber kebenaran status; stage_record.projected_status adalah proyeksinya.';


-- ---------------------------------------------------------------------
-- monitoring_assessment — penilaian tim monev atas sebuah tahap.
-- Berversi: koreksi membuat baris baru dan menandai yang lama digantikan.
-- Baris lama tidak pernah ditimpa, sehingga laporan lama tetap dapat
-- dijelaskan dengan penilaian yang berlaku saat itu.
-- ---------------------------------------------------------------------
CREATE TABLE monitoring_assessment (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  stage_record_id     uuid NOT NULL REFERENCES stage_record (id) ON DELETE RESTRICT,

  assessed_on         date NOT NULL,

  case_position       text NOT NULL,   -- Kasus posisi
  obstacle            text NULL,       -- Kendala / hambatan

  -- Batas waktu penyelesaian.
  -- MANUAL         : target_on WAJIB terisi dan WAJIB beralasan.
  -- POLICY_DERIVED : target_on WAJIB kosong. Tanggalnya dihitung ulang dari
  --                  started_on + SLA pada workflow_policy setiap kali
  --                  dibutuhkan. Menyimpan hasil hitungan akan basi diam-diam
  --                  begitu kebijakan berubah.
  target_on           date NULL,
  target_source       monev_target_source NOT NULL,
  target_reason       text NULL,

  recommendation      text NOT NULL,
  other_note          text NULL,

  -- Taksiran kerugian keuangan negara.
  -- Uang selalu berpasangan: jumlah numerik DAN kode mata uang ISO 4217.
  -- Tidak pernah teks "Rp 1.000,-". Teks asli dokumen disimpan pada kolom
  -- _raw agar sumbernya tetap dapat ditelusuri.
  -- Kosong berarti BELUM DIUKUR, bukan nol rupiah. MVP belum mengumpulkan
  -- medan ini; kolomnya disediakan supaya angka yang menyusul tidak
  -- terpaksa masuk sebagai teks bebas.
  estimated_loss_amount    numeric(20,2) NULL,
  estimated_loss_currency  char(3) NULL,
  estimated_loss_raw       text NULL,

  assessed_by         text NOT NULL,
  change_state        monev_change_state NOT NULL DEFAULT 'DRAFT',
  superseded_by_id    uuid NULL REFERENCES monitoring_assessment (id) ON DELETE RESTRICT,

  created_at          timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT ck_monitoring_assessment_target
    CHECK ((target_source = 'MANUAL') = (target_on IS NOT NULL)),

  -- Batas waktu yang ditetapkan manusia wajib menyebut alasannya, supaya
  -- tanggal yang menyimpang dari kebijakan dapat dipertanggungjawabkan.
  CONSTRAINT ck_monitoring_assessment_target_reason
    CHECK (target_source <> 'MANUAL' OR btrim(coalesce(target_reason, '')) <> ''),

  CONSTRAINT ck_monitoring_assessment_money_pair
    CHECK ((estimated_loss_amount IS NULL) = (estimated_loss_currency IS NULL)),
  CONSTRAINT ck_monitoring_assessment_money_sign
    CHECK (estimated_loss_amount IS NULL OR estimated_loss_amount >= 0),
  CONSTRAINT ck_monitoring_assessment_currency_shape
    CHECK (estimated_loss_currency IS NULL OR estimated_loss_currency ~ '^[A-Z]{3}$'),

  CONSTRAINT ck_monitoring_assessment_case_position_not_blank
    CHECK (btrim(case_position) <> ''),
  CONSTRAINT ck_monitoring_assessment_not_self_superseded
    CHECK (superseded_by_id IS DISTINCT FROM id)
);

CREATE INDEX ix_monitoring_assessment_stage
  ON monitoring_assessment (stage_record_id, assessed_on DESC);

-- Penilaian yang masih berlaku untuk sebuah tahap: tepat satu.
CREATE UNIQUE INDEX uq_monitoring_assessment_current
  ON monitoring_assessment (stage_record_id)
  WHERE change_state = 'APPROVED' AND superseded_by_id IS NULL;

COMMENT ON TABLE monitoring_assessment IS
  'Penilaian tim monev, berversi. Koreksi membuat baris baru; baris lama ditandai digantikan.';
COMMENT ON COLUMN monitoring_assessment.target_on IS
  'Hanya terisi bila target_source = MANUAL. Batas turunan kebijakan dihitung ulang, tidak disimpan.';


-- ---------------------------------------------------------------------
-- stage_sector_tag — penandaan sektor strategis, nol-ke-banyak.
-- Satu tahap boleh tidak bersektor, boleh bersektor lebih dari satu.
-- ---------------------------------------------------------------------
CREATE TABLE stage_sector_tag (
  stage_record_id      uuid NOT NULL REFERENCES stage_record (id) ON DELETE RESTRICT,
  sector_code          monev_sector_code NOT NULL,

  -- Penandaan otomatis berdasarkan kata kunci TIDAK PERNAH dianggap final.
  -- Sepanjang kolom ini false, penandaan berstatus usulan.
  confirmed_by_human   boolean NOT NULL DEFAULT false,

  tagged_by            text NOT NULL,
  tagged_at            timestamptz NOT NULL DEFAULT now(),

  -- Kunci utama gabungan: sebuah sektor tidak dapat ditempelkan dua kali
  -- pada tahap yang sama.
  CONSTRAINT pk_stage_sector_tag PRIMARY KEY (stage_record_id, sector_code)
);

CREATE INDEX ix_stage_sector_tag_sector ON stage_sector_tag (sector_code);

COMMENT ON TABLE stage_sector_tag IS
  'Penandaan sektor strategis per tahap, nol-ke-banyak, nilai terkendali.';
-- CATATAN KEJUJURAN: tidak adanya baris di sini berarti "belum ditandai".
-- Pernyataan sadar bahwa sebuah tahap memang tidak masuk sektor mana pun
-- belum punya medan tersendiri pada MVP dan sementara dicatat pada
-- monitoring_assessment.other_note. Perbedaan "belum ditandai" dan
-- "ditetapkan tanpa sektor" adalah kekurangan yang diketahui, bukan
-- kekurangan yang tersembunyi.


-- =====================================================================
--  BAGIAN 4 — DOKUMEN SUMBER DAN PROVENANCE
-- =====================================================================

-- ---------------------------------------------------------------------
-- source_document — berkas yang diunggah untuk diekstraksi.
-- Berbeda dari evidence_document: dokumen sumber adalah bahan mentah
-- proses masukan. Ia baru menjadi bukti setelah dipromosikan.
-- ---------------------------------------------------------------------
CREATE TABLE source_document (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  original_filename   text NOT NULL,
  mime_type           text NOT NULL,
  pages               integer NOT NULL,
  size_bytes          bigint NOT NULL,
  storage_key         text NOT NULL,
  checksum_sha256     text NULL,

  -- Berkas terkunci sandi DITOLAK oleh kebijakan, bukan dibuka paksa.
  encrypted           boolean NOT NULL DEFAULT false,

  -- Hasil pengukuran, bukan dugaan: jumlah karakter pada lapisan teks.
  -- Tanpa lapisan teks, ekstraksi tidak dijalankan dan berkas dilaporkan
  -- apa adanya sebagai perlu OCR. Menebak isi dokumen pindaian dilarang.
  has_text_layer      boolean NOT NULL,
  chars_found         integer NOT NULL DEFAULT 0,
  ocr_state           monev_ocr_state NOT NULL,

  uploaded_by         text NOT NULL,
  uploaded_at         timestamptz NOT NULL DEFAULT now(),

  -- Terisi bila dokumen ini kemudian dijadikan bukti resmi.
  evidence_document_id uuid NULL REFERENCES evidence_document (id) ON DELETE RESTRICT,

  CONSTRAINT uq_source_document_storage_key UNIQUE (storage_key),
  CONSTRAINT ck_source_document_pages CHECK (pages >= 0),
  CONSTRAINT ck_source_document_size CHECK (size_bytes > 0),
  CONSTRAINT ck_source_document_chars CHECK (chars_found >= 0),
  -- IMPLIKASI SATU ARAH, BUKAN KESETARAAN.
  -- "Ada lapisan teks" berarti "ada karakter yang terbaca". Kebalikannya
  -- TIDAK berlaku: ambang lapisan teks ada pada lib/extract.ts
  -- (hasTextLayer = charsFound >= 40), sehingga berkas pindaian yang hanya
  -- menyisakan beberapa karakter nyasar sah tercatat sebagai
  -- chars_found > 0 DAN has_text_layer = false. Menyalin ambang itu ke sini
  -- akan menaruh satu angka di dua tempat dan membuat basis data menolak
  -- baris yang benar-benar dihasilkan aplikasi. Ambangnya tetap milik
  -- pengekstraksi; yang dijaga di sini hanya yang selalu benar.
  CONSTRAINT ck_source_document_text_layer_consistent
    CHECK (has_text_layer = false OR chars_found > 0),
  CONSTRAINT ck_source_document_checksum
    CHECK (checksum_sha256 IS NULL OR checksum_sha256 ~ '^[0-9a-f]{64}$')
);

CREATE INDEX ix_source_document_uploaded_at ON source_document (uploaded_at DESC);

COMMENT ON TABLE source_document IS
  'Berkas mentah untuk ekstraksi. Bukan bukti sampai dipromosikan menjadi evidence_document.';


-- ---------------------------------------------------------------------
-- extraction_proposal — PROVENANCE SETIAP MEDAN HASIL EKSTRAKSI.
--
-- Inilah tabel yang membedakan sistem ini dari penyalinan manual: setiap
-- nilai yang diusulkan mesin membawa halaman, cuplikan sumber, dan angka
-- keyakinan. Usulan tanpa provenance TIDAK BOLEH ditampilkan, apalagi
-- diterima. Dan usulan kosong wajib menyebut alasannya, karena "tidak
-- ditemukan" berbeda dari "gagal membaca".
-- ---------------------------------------------------------------------
CREATE TABLE extraction_proposal (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  source_document_id  uuid NOT NULL REFERENCES source_document (id) ON DELETE CASCADE,

  field_key           monev_field_key NOT NULL,

  -- Teks apa adanya seperti tertulis pada dokumen.
  value_raw           text NULL,
  -- Nilai siap simpan setelah normalisasi. NULL berarti tidak ditemukan.
  value_normalized    text NULL,

  -- 0..1, dihitung dari kekuatan pola yang cocok. Bukan angka karangan.
  confidence          numeric(4,3) NULL,

  page_number         integer NULL,
  snippet             text NULL,

  -- Wajib terisi tepat ketika tidak ada nilai. Kosong tanpa alasan adalah
  -- kegagalan senyap, dan kegagalan senyap adalah cacat.
  not_found_reason    text NULL,

  accepted_by         text NULL,
  accepted_at         timestamptz NULL,

  created_at          timestamptz NOT NULL DEFAULT now(),

  -- Satu usulan per medan per dokumen. Bila kelak dibutuhkan beberapa
  -- kandidat per medan, tambahkan kolom rank lewat expand-contract dan
  -- pindahkan keunikan ke (source_document_id, field_key, rank).
  CONSTRAINT uq_extraction_proposal_field UNIQUE (source_document_id, field_key),

  CONSTRAINT ck_extraction_proposal_confidence
    CHECK (confidence IS NULL OR (confidence >= 0 AND confidence <= 1)),
  CONSTRAINT ck_extraction_proposal_page
    CHECK (page_number IS NULL OR page_number > 0),

  -- Ada nilai berarti wajib ada provenance lengkap.
  CONSTRAINT ck_extraction_proposal_provenance
    CHECK (value_normalized IS NULL
           OR (page_number IS NOT NULL
               AND snippet IS NOT NULL
               AND confidence IS NOT NULL)),

  -- Tepat salah satu: ada nilai, atau ada alasan mengapa tidak ada nilai.
  CONSTRAINT ck_extraction_proposal_empty_has_reason
    CHECK ((value_normalized IS NULL) = (not_found_reason IS NOT NULL)),

  CONSTRAINT ck_extraction_proposal_accept_pair
    CHECK ((accepted_by IS NULL) = (accepted_at IS NULL))
);

CREATE INDEX ix_extraction_proposal_document ON extraction_proposal (source_document_id);

COMMENT ON TABLE extraction_proposal IS
  'Provenance per medan hasil ekstraksi: nilai, keyakinan, halaman, cuplikan. Tanpa ini usulan tidak boleh tampil.';


-- =====================================================================
--  BAGIAN 5 — PERNYATAAN NIHIL, IMPOR, DAN MUTU DATA
-- =====================================================================

-- ---------------------------------------------------------------------
-- zero_declaration — PERNYATAAN NIHIL.
--
-- NIHIL ADALAH PERNYATAAN NOL, BUKAN PERKARA.
-- Sebuah satker yang tidak menangani perkara pada satu tahap dan satu
-- periode menyatakannya di sini, dengan nama penyatanya dan tanggalnya.
--
-- JANGAN PERNAH membuat baris kase atau stage_record palsu untuk mewakili
-- nihil. Baris palsu itu akan ikut terhitung pada rekap dan mencemari
-- setiap angka turunannya.
--
-- Tanpa tabel ini, "satker belum melapor" dan "satker sudah melapor nol"
-- tampak sama persis di layar, yaitu sama-sama kosong. Perbedaan itu
-- justru yang paling ingin diketahui pimpinan.
-- ---------------------------------------------------------------------
CREATE TABLE zero_declaration (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id     uuid NOT NULL REFERENCES organization (id) ON DELETE RESTRICT,
  reporting_year      integer NOT NULL,
  reporting_cycle_id  uuid NULL REFERENCES reporting_cycle (id) ON DELETE RESTRICT,
  stage_type          monev_stage_type NOT NULL,

  declared_by         text NOT NULL,
  declared_on         date NOT NULL,
  note                text NULL,

  -- Pernyataan nihil pun melewati persetujuan orang kedua.
  approved_by         text NULL,
  approved_at         timestamptz NULL,

  created_at          timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT uq_zero_declaration_scope
    UNIQUE (organization_id, reporting_year, stage_type),
  CONSTRAINT ck_zero_declaration_year CHECK (reporting_year BETWEEN 2000 AND 2100),
  CONSTRAINT ck_zero_declaration_approval_pair
    CHECK ((approved_by IS NULL) = (approved_at IS NULL)),
  CONSTRAINT ck_zero_declaration_approver_differs
    CHECK (approved_by IS NULL
           OR lower(btrim(approved_by)) <> lower(btrim(declared_by)))
);

CREATE INDEX ix_zero_declaration_org_year
  ON zero_declaration (organization_id, reporting_year);

COMMENT ON TABLE zero_declaration IS
  'Pernyataan NIHIL satu satker untuk satu tahap dan satu periode. Bukan perkara, tidak pernah dihitung sebagai perkara.';


-- ---------------------------------------------------------------------
-- import_batch — satu berkas spreadsheet yang diunggah untuk diimpor.
-- ---------------------------------------------------------------------
CREATE TABLE import_batch (
  id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  original_filename  text NOT NULL,
  sheet_name         text NULL,
  storage_key        text NOT NULL,
  checksum_sha256    text NULL,

  organization_id    uuid NULL REFERENCES organization (id) ON DELETE RESTRICT,
  reporting_cycle_id uuid NULL REFERENCES reporting_cycle (id) ON DELETE RESTRICT,

  state              monev_import_state NOT NULL DEFAULT 'RECEIVED',

  -- Jumlah baris yang benar-benar dibaca dari berkas. Dipakai sebagai
  -- penyebut: setiap baris wajib dapat dipertanggungjawabkan, entah sebagai
  -- data, sebagai judul, sebagai subtotal, atau sebagai tidak terselesaikan.
  total_rows         integer NOT NULL DEFAULT 0,

  uploaded_by        text NOT NULL,
  uploaded_at        timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT uq_import_batch_storage_key UNIQUE (storage_key),
  CONSTRAINT ck_import_batch_total_rows CHECK (total_rows >= 0),
  CONSTRAINT ck_import_batch_checksum
    CHECK (checksum_sha256 IS NULL OR checksum_sha256 ~ '^[0-9a-f]{64}$')
);

COMMENT ON TABLE import_batch IS
  'Satu berkas impor. total_rows adalah penyebut: tidak ada baris yang boleh hilang tanpa keterangan.';


-- ---------------------------------------------------------------------
-- import_row — SETIAP baris berkas impor, tanpa kecuali.
--
-- Baris yang tidak dikenali TIDAK dibuang diam-diam. Ia disimpan dengan
-- klasifikasi UNRESOLVED beserta alasannya, sehingga jumlah baris masuk
-- selalu sama dengan jumlah baris yang dipertanggungjawabkan.
--
-- SUBTOTAL dan GRAND_TOTAL wajib dikenali dan TIDAK PERNAH menjadi perkara.
-- Inilah cara angka rekap tidak menggandakan dirinya sendiri: pada berkas
-- rekapitulasi buatan tangan, baris jumlah tampak persis seperti baris data.
-- ---------------------------------------------------------------------
CREATE TABLE import_row (
  id                          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  import_batch_id             uuid NOT NULL REFERENCES import_batch (id) ON DELETE CASCADE,

  -- Nomor baris pada berkas asli, sehingga operator dapat membuka berkasnya
  -- dan melihat baris yang dimaksud.
  row_number                  integer NOT NULL,

  classification              monev_import_row_class NOT NULL,

  -- Isi baris apa adanya, kolom demi kolom.
  raw                         jsonb NOT NULL,

  resolved_stage_id           uuid NULL REFERENCES stage_record (id) ON DELETE RESTRICT,
  resolved_zero_declaration_id uuid NULL REFERENCES zero_declaration (id) ON DELETE RESTRICT,
  unresolved_reason           text NULL,

  created_at                  timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT uq_import_row_number UNIQUE (import_batch_id, row_number),
  CONSTRAINT ck_import_row_number CHECK (row_number > 0),
  CONSTRAINT ck_import_row_raw_object CHECK (jsonb_typeof(raw) = 'object'),

  -- Hanya baris data yang boleh menjadi tahap.
  CONSTRAINT ck_import_row_stage_only_data
    CHECK (resolved_stage_id IS NULL OR classification = 'DATA_ROW'),

  -- Hanya baris pernyataan nihil yang boleh menjadi pernyataan nihil.
  CONSTRAINT ck_import_row_zero_only_nihil
    CHECK (resolved_zero_declaration_id IS NULL
           OR classification = 'NIHIL_DECLARATION'),

  -- Satu baris tidak dapat sekaligus menjadi perkara dan pernyataan nihil.
  CONSTRAINT ck_import_row_single_resolution
    CHECK (NOT (resolved_stage_id IS NOT NULL
                AND resolved_zero_declaration_id IS NOT NULL)),

  -- Baris yang tidak terselesaikan wajib menyebut mengapa.
  CONSTRAINT ck_import_row_unresolved_reason
    CHECK (classification <> 'UNRESOLVED'
           OR btrim(coalesce(unresolved_reason, '')) <> '')
);

CREATE INDEX ix_import_row_batch_class ON import_row (import_batch_id, classification);
CREATE INDEX ix_import_row_stage ON import_row (resolved_stage_id)
  WHERE resolved_stage_id IS NOT NULL;

COMMENT ON TABLE import_row IS
  'Setiap baris berkas impor beserta klasifikasinya. Baris subtotal dan jumlah tidak pernah menjadi perkara.';


-- ---------------------------------------------------------------------
-- data_quality_finding — temuan mutu data.
-- Temuan tidak pernah dihapus; ia DISELESAIKAN, dan penyelesaiannya
-- menyebut siapa dan kapan.
-- ---------------------------------------------------------------------
CREATE TABLE data_quality_finding (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Kode temuan yang stabil, mis. 'DQ_DUPLICATE_ORDER_EXACT',
  -- 'DQ_MISSING_SPRIN_DATE', 'DQ_ORG_UNRESOLVED'.
  code             text NOT NULL,

  severity         monev_finding_severity NOT NULL,

  -- Sasaran temuan, disimpan sebagai pasangan (nama tabel, id baris) supaya
  -- satu tabel temuan dapat menunjuk entitas mana pun. Konsekuensi yang
  -- disadari: tidak ada kunci asing di sini; pembersihan sasaran yang hilang
  -- adalah tugas proses pemeriksaan berkala.
  entity           text NOT NULL,
  entity_id        uuid NOT NULL,

  detail           text NOT NULL,
  detected_at      timestamptz NOT NULL DEFAULT now(),

  resolved_by      text NULL,
  resolved_at      timestamptz NULL,
  resolution_note  text NULL,

  CONSTRAINT ck_dq_finding_resolution_pair
    CHECK ((resolved_by IS NULL) = (resolved_at IS NULL)),
  CONSTRAINT ck_dq_finding_resolution_note
    CHECK (resolved_at IS NULL OR btrim(coalesce(resolution_note, '')) <> ''),
  CONSTRAINT ck_dq_finding_entity_not_blank CHECK (btrim(entity) <> '')
);

-- Satu temuan terbuka per kode per entitas; pemeriksaan yang berjalan
-- berulang kali tidak boleh menumpuk duplikat.
CREATE UNIQUE INDEX uq_dq_finding_open
  ON data_quality_finding (code, entity, entity_id)
  WHERE resolved_at IS NULL;

CREATE INDEX ix_dq_finding_open_severity
  ON data_quality_finding (severity)
  WHERE resolved_at IS NULL;

CREATE INDEX ix_dq_finding_entity ON data_quality_finding (entity, entity_id);

COMMENT ON TABLE data_quality_finding IS
  'Temuan mutu data. BLOCKER menghentikan persetujuan; temuan diselesaikan, tidak dihapus.';


-- =====================================================================
--  BAGIAN 6 — TATA KELOLA: PENGAJUAN DAN JEJAK AUDIT
-- =====================================================================

-- ---------------------------------------------------------------------
-- submission — PENGAJUAN. Tulang punggung alur ujung ke ujung.
--
--   masukan (dokumen / manual / impor)
--     -> verifikasi manusia
--     -> PENGAJUAN (payload tetap)
--     -> persetujuan oleh ORANG BERBEDA
--     -> catatan kanonik (stage_record dan kerabatnya)
--     -> rekap
--
-- Angka rekap tidak pernah naik karena seseorang mengetik angka. Ia naik
-- karena sebuah pengajuan disetujui orang kedua.
-- ---------------------------------------------------------------------
CREATE TABLE submission (
  id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  kind                 monev_submission_kind NOT NULL,

  -- Nama berkas atau label kumpulan asal pengajuan ini, untuk ditampilkan.
  source_label         text NOT NULL,

  source_document_id   uuid NULL REFERENCES source_document (id) ON DELETE RESTRICT,
  import_batch_id      uuid NULL REFERENCES import_batch (id) ON DELETE RESTRICT,

  submitted_by         text NOT NULL,
  submitted_at         timestamptz NOT NULL DEFAULT now(),

  state                monev_change_state NOT NULL DEFAULT 'DRAFT',

  -- Isi kanonik yang diusulkan, satu objek JSON. Disimpan utuh supaya yang
  -- disetujui adalah persis yang dilihat penyetuju, bukan hasil perakitan
  -- ulang di kemudian hari.
  payload              jsonb NOT NULL,

  -- Larik provenance per medan: {"field","source","page","confidence",
  -- "snippet","sourceRow"}. Menjawab "dari mana angka ini" untuk tiap medan.
  provenance           jsonb NOT NULL DEFAULT '[]',

  approved_by          text NULL,
  approved_at          timestamptz NULL,
  returned_reason      text NULL,

  -- Catatan kanonik yang lahir dari pengajuan ini.
  resulting_stage_id   uuid NULL REFERENCES stage_record (id) ON DELETE RESTRICT,

  -- Koreksi atas pengajuan yang sudah disetujui dilakukan dengan pengajuan
  -- BARU yang menggantikan yang lama. Yang lama tidak pernah disunting.
  supersedes_id        uuid NULL REFERENCES submission (id) ON DELETE RESTRICT,

  created_at           timestamptz NOT NULL DEFAULT now(),

  -- ATURAN INTI: PENYETUJU WAJIB BERBEDA DARI PENGAJU.
  -- Inilah satu-satunya kontrol yang membedakan sistem ini dari lembar
  -- kerja bersama. Ditegakkan di basis data supaya tetap berlaku bagi
  -- skrip, impor massal, dan perbaikan darurat sekalipun.
  CONSTRAINT ck_submission_approver_differs
    CHECK (approved_by IS NULL
           OR lower(btrim(approved_by)) <> lower(btrim(submitted_by))),

  CONSTRAINT ck_submission_payload_object CHECK (jsonb_typeof(payload) = 'object'),
  CONSTRAINT ck_submission_provenance_array CHECK (jsonb_typeof(provenance) = 'array'),

  -- Disetujui berarti lengkap: penyetuju, waktunya, dan catatan yang lahir.
  CONSTRAINT ck_submission_approved_complete
    CHECK (state <> 'APPROVED'
           OR (approved_by IS NOT NULL
               AND approved_at IS NOT NULL
               AND resulting_stage_id IS NOT NULL)),

  -- Dikembalikan wajib beralasan; pengaju berhak tahu apa yang harus diperbaiki.
  CONSTRAINT ck_submission_returned_reason
    CHECK (state <> 'RETURNED' OR btrim(coalesce(returned_reason, '')) <> ''),

  -- Pengajuan dari dokumen wajib menunjuk dokumennya; dari impor wajib
  -- menunjuk berkas impornya. Sumber yang tidak dapat ditunjuk bukan sumber.
  CONSTRAINT ck_submission_source_present
    CHECK ((kind <> 'DOKUMEN' OR source_document_id IS NOT NULL)
           AND (kind <> 'IMPOR' OR import_batch_id IS NOT NULL)),

  CONSTRAINT ck_submission_not_self_supersede
    CHECK (supersedes_id IS DISTINCT FROM id)
);

CREATE INDEX ix_submission_state ON submission (state);
CREATE INDEX ix_submission_submitted_at ON submission (submitted_at DESC);
CREATE INDEX ix_submission_queue ON submission (submitted_at)
  WHERE state = 'SUBMITTED';

COMMENT ON TABLE submission IS
  'Pengajuan perubahan. Payload yang disetujui bersifat tetap; koreksi lewat pengajuan pengganti.';
COMMENT ON CONSTRAINT ck_submission_approver_differs ON submission IS
  'Maker-checker: penyetuju tidak boleh sama dengan pengaju.';


-- ---------------------------------------------------------------------
-- audit_log — jejak audit, HANYA-TAMBAH.
-- ---------------------------------------------------------------------
CREATE TABLE audit_log (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  actor      text NOT NULL,   -- Siapa yang bertindak
  action     text NOT NULL,   -- mis. 'SUBMISSION_APPROVED', 'POLICY_UPDATED'
  entity     text NOT NULL,   -- Nama tabel sasaran
  entity_id  text NOT NULL,   -- Id baris sasaran, sebagai teks agar sanggup
                              -- menampung kunci uuid maupun bigint.

  -- Nama kolom "at" mengikuti kontrak. AT bukan kata kunci yang dicadangkan
  -- pada PostgreSQL sehingga tidak memerlukan tanda kutip.
  at         timestamptz NOT NULL DEFAULT now(),

  before     jsonb NULL,      -- Keadaan sebelum; NULL pada penciptaan
  after      jsonb NULL,      -- Keadaan sesudah; NULL pada penghapusan

  request_id text NULL,       -- Penghubung ke log aplikasi

  -- Baris audit yang tidak menyebut keadaan sebelum maupun sesudah tidak
  -- membuktikan apa pun.
  CONSTRAINT ck_audit_log_state_present
    CHECK (before IS NOT NULL OR after IS NOT NULL),
  CONSTRAINT ck_audit_log_actor_not_blank CHECK (btrim(actor) <> '')
);

CREATE INDEX ix_audit_log_entity ON audit_log (entity, entity_id, at DESC);
CREATE INDEX ix_audit_log_at ON audit_log (at DESC);
CREATE INDEX ix_audit_log_actor ON audit_log (actor, at DESC);

COMMENT ON TABLE audit_log IS
  'Jejak audit hanya-tambah. Perubahan dan penghapusan ditolak oleh pemicu, bukan sekadar oleh kesepakatan.';


-- =====================================================================
--  BAGIAN 7 — PEMICU YANG MENEGAKKAN INVARIAN
--  Aturan di bawah ini tidak dapat dinyatakan sebagai CHECK karena
--  membandingkan keadaan lama dengan keadaan baru. Ditegakkan di basis
--  data supaya tetap berlaku bagi kode mana pun yang menulis ke tabel.
-- =====================================================================

-- Jejak audit hanya-tambah: UBAH dan HAPUS ditolak.
CREATE FUNCTION monev_audit_log_append_only() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION
    'audit_log bersifat hanya-tambah; operasi % ditolak', TG_OP
    USING ERRCODE = 'restrict_violation';
END
$$;

CREATE TRIGGER trg_audit_log_append_only
  BEFORE UPDATE OR DELETE ON audit_log
  FOR EACH ROW EXECUTE FUNCTION monev_audit_log_append_only();

-- Payload pengajuan yang sudah disetujui bersifat tetap.
-- Koreksi dilakukan dengan pengajuan pengganti (supersedes_id), bukan
-- dengan menyunting yang lama. Tanpa aturan ini, angka yang sudah
-- diterbitkan dapat berubah tanpa jejak.
CREATE FUNCTION monev_submission_payload_immutable() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF OLD.state = 'APPROVED' AND NEW.payload IS DISTINCT FROM OLD.payload THEN
    RAISE EXCEPTION
      'Payload pengajuan yang sudah disetujui tidak dapat diubah; buat pengajuan pengganti'
      USING ERRCODE = 'restrict_violation';
  END IF;
  RETURN NEW;
END
$$;

CREATE TRIGGER trg_submission_payload_immutable
  BEFORE UPDATE ON submission
  FOR EACH ROW EXECUTE FUNCTION monev_submission_payload_immutable();

-- Siklus pelaporan yang sudah dibekukan tidak dapat digeser tanggalnya.
-- Membekukan lalu menggeser tanggal potong sama dengan menerbitkan ulang
-- laporan lama dengan angka baru tanpa mengatakannya.
CREATE FUNCTION monev_reporting_cycle_frozen_guard() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF OLD.frozen
     AND (NEW.cutoff_date IS DISTINCT FROM OLD.cutoff_date
          OR NEW.cycle_start IS DISTINCT FROM OLD.cycle_start
          OR NEW.cycle_end IS DISTINCT FROM OLD.cycle_end
          OR NEW.year IS DISTINCT FROM OLD.year) THEN
    RAISE EXCEPTION
      'Siklus pelaporan sudah dibekukan; tanggalnya tidak dapat diubah'
      USING ERRCODE = 'restrict_violation';
  END IF;
  RETURN NEW;
END
$$;

CREATE TRIGGER trg_reporting_cycle_frozen_guard
  BEFORE UPDATE ON reporting_cycle
  FOR EACH ROW EXECUTE FUNCTION monev_reporting_cycle_frozen_guard();


-- =====================================================================
--  BAGIAN 8 — YANG SENGAJA BELUM ADA DI SKEMA INI
--  Disebutkan supaya tidak disangka kelalaian. Menyembunyikan batas
--  cakupan jauh lebih mahal daripada menuliskannya.
-- =====================================================================
--   1. DIREKTORI PENGGUNA. Kolom submitted_by, approved_by, assessed_by,
--      uploaded_by dan sejenisnya bertipe text. Pada penerapan penuh,
--      kolom-kolom itu menjadi kunci asing ke direktori pengguna
--      (SSO Kejaksaan). Perubahannya mengikuti expand-contract: tambah
--      kolom *_user_id, tulis ganda, isi ulang, baru susutkan.
--   2. ROW LEVEL SECURITY. Pembatasan baris per satker belum dipasang di
--      sini; klasifikasi informasi sudah tersedia pada kase dan
--      evidence_document sebagai bahannya.
--   3. KEBIJAKAN RETENSI DAN PENGARSIPAN belum dimodelkan.
--   4. PARTISI menurut reporting_year belum diperlukan pada volume Jawa
--      Barat. Bila kelak diperlukan, stage_record adalah kandidat pertama
--      dan kuncinya sudah memuat reporting_year.
--   5. TABEL RIWAYAT MIGRASI dikelola perkakas migrasi, bukan berkas ini.
-- =====================================================================

Verifikasi mandiri. Pada basis data kosong, perintah psql -v ON_ERROR_STOP=1 -f docs/SCHEMA.sql wajib berakhir dengan kode keluar 0. Skema ini dijalankan pada PostgreSQL 16 dan berhasil tanpa galat maupun peringatan; batasan-batasan pada Bagian 3 diuji dua arah, yaitu satu kasus yang wajib ditolak dan satu kendali positif yang wajib diterima.

Aplikasi peragaan ini diekspor sebagai berkas statis tanpa peladen, sehingga isi berkas disematkan pada app/(app)/skema/ddl.ts agar yang tampil adalah DDL yang sesungguhnya, bukan ringkasan yang ditulis ulang. Salinan itu dihasilkan mesin dari docs/SCHEMA.sql dan tidak disunting dengan tangan.