LDAPWeb/GeoNetAgent/docs/database.md

Database Documentation

Terakhir Diperbarui: 2026-06-29

---

Overview

Item	Value
DBMS	PostgreSQL 12+
Host	10.100.1.25
DB Dev	geonetagent_dev
DB Prod	geonetagent
Timezone	Asia/Jakarta
PK Convention	UUID (gen_random_uuid())
Timestamp	timestamptz
Credentials	/root/geonetagent_dev.credentials di server 10.100.1.25

Ownership:

• FastAPI Collector = write ingress otomatis (agent reports, Proxmox sync)
• Laravel Portal = read + manual CRUD terbatas

---

ERD (MVP)

erDiagram
    locations ||--o{ assets : has
    assets ||--o{ asset_snapshots : has
    assets ||--o{ software_installations : has
    assets ||--o{ asset_user_assignments : has
    users ||--o{ asset_user_assignments : assigned
    users ||--o{ license_assignments : has
    licenses ||--o{ license_assignments : has
    assets ||--o{ license_assignments : assigned
    assets ||--o{ alerts : triggers
    assets ||--o{ assets : parent_host

---

Tabel Detail

assets

Entitas utama — setiap device/VM/server.

Kolom	Tipe	Keterangan
id	UUID PK	gen_random_uuid()
asset_type	varchar	endpoint, server, vm, proxmox_host, network_device, nas, gis_server
hostname	varchar UNIQUE	Hostname device
machine_id	varchar	OS machine-id (tidak lagi UNIQUE sejak migration 003)
smbios_uuid	varchar	BIOS UUID
serial_number	varchar	Serial number hardware
primary_physical_mac	varchar	MAC address utama (untuk identity fallback)
zabbix_host_id	varchar nullable	ID host di Zabbix
location_id	UUID FK → locations	Lokasi fisik
parent_asset_id	UUID FK → assets	Untuk VM: parent Proxmox host
status	varchar	active, inactive, unknown
metadata	jsonb	Field fleksibel/rare
last_seen_at	timestamptz	Terakhir laporan masuk
deleted_at	timestamptz nullable	Soft delete

---

asset_snapshots

Point-in-time snapshot dari laporan agent.

Kolom	Tipe	Keterangan
id	UUID PK
asset_id	UUID FK → assets
payload	jsonb	Full payload agent (system, cpu, gpu, disks, network, dll)
agent_version	varchar	Versi agent saat collect
collected_at	timestamptz	Waktu collect di endpoint

Idempotent berdasarkan (hostname, collected_at) — laporan duplikat diabaikan.

---

asset_change_events

Audit log per field — baris baru hanya jika nilai berubah (old_value → new_value).

Kolom	Tipe	Keterangan
id	UUID PK
asset_id	UUID FK → assets
snapshot_id	UUID FK nullable	Snapshot pemicu (ingest agent)
event_at	timestamptz	Waktu kejadian
source	varchar	agent, manual, system, support
field_key	varchar	Kunci internal, mis. gpu, owner
field_label	varchar	Label UI
change_type	varchar	added, removed, updated
old_value	text	Nilai sebelum
new_value	text	Nilai sesudah
detail	jsonb	Detail tambahan (mis. software ±)

Field dilacak: hostname, display name, owner, agent version, MAC, network, software, device status, asset for/on, notes, OS, CPU, RAM, BIOS, GPU, physical disks, volumes, manufacturer, model, serial.

API: GET /api/v1/assets/{id}/changes · Migration: 008_asset_change_events.sql

---

asset_support_cases

Tiket troubleshooting IT Support per asset (SSOT manual — bukan dari agent). Lihat ADR-004.

Kolom	Tipe	Keterangan
id	UUID PK
asset_id	UUID FK → assets	Device yang ditangani
case_number	varchar unique	GNA-SUP-YYYYMMDD-NNNN
status	varchar	open, in_progress, waiting_parts, waiting_user, resolved, closed, cancelled
priority	varchar	low, normal, high, critical
category	varchar	Kategori kendala (mati, ganti komponen, service, dll.)
title	varchar(200)	Judul singkat
symptom	text	Gejala / laporan awal
resolution	text nullable	Ringkasan perbaikan
root_cause	text nullable	Akar masalah
reported_by	varchar	Username teknisi pembuka
device_status_at_open	varchar nullable	Status device saat kasus dibuka
device_status_applied	varchar nullable	Status terakhir diterapkan via support
opened_at / resolved_at / closed_at	timestamptz	Milestone waktu

Enum SSOT: collector/app/schemas/support.py · Migration: 009_asset_support.sql

---

asset_support_events

Timeline append-only per tiket support (catatan, aksi teknis, ganti komponen, perubahan status).

Kolom	Tipe	Keterangan
id	UUID PK
case_id	UUID FK → asset_support_cases
event_type	varchar	opened, note, status_change, action, parts, handover, resolution
summary	varchar(500)	Ringkasan baris timeline
detail	text nullable	Detail panjang
old_status / new_status	varchar nullable	Untuk status_change
metadata	jsonb	Mis. daftar parts
actor	varchar	Username teknisi
created_at	timestamptz

API: /api/v1/support/* · Panduan: docs/guide/asset-support-troubleshooting.md

Lampiran (migration 010): foto troubleshooting, nota parts, PDF service report. Binary di QNAP/local; metadata di tabel ini. Kind SSOT: schemas/attachments.py.

---

software_installations

Daftar software terinstall dari scan agent.

Kolom	Tipe	Keterangan
id	UUID PK
asset_id	UUID FK
name	varchar	Nama software
version	varchar nullable
publisher	varchar nullable
install_date	date nullable
collected_at	timestamptz

---

users

User/karyawan (dari AD atau manual entry Laravel).

Kolom	Tipe
id	UUID PK
username	varchar UNIQUE
display_name	varchar
email	varchar nullable
department	varchar nullable

---

locations

Lokasi fisik asset.

Kolom	Tipe
id	UUID PK
name	varchar
code	varchar
address	varchar nullable

---

alerts

Alert dari Zabbix webhook (Fase 2).

Kolom	Tipe	Keterangan
id	UUID PK
asset_id	UUID FK nullable
zabbix_event_id	varchar UNIQUE	ID event Zabbix
severity	varchar	info, warning, average, high, disaster
message	text
status	varchar	problem, resolved
fired_at	timestamptz
resolved_at	timestamptz nullable

---

agent_tokens

Token autentikasi agent (jika AGENT_TOKEN_CHECK_DB=true).

Kolom	Tipe
id	UUID PK
token_hash	varchar UNIQUE
description	varchar
is_active	boolean
created_at	timestamptz

---

asset_invoices, tickets, document_attachments (Fase 3 — Belum Apply)

Draft di infra/db/005_asset_documents.sql. Belum dijalankan di DB.

Tabel	Domain
asset_invoices	Procurement / pembelian
tickets	Service request + complain
document_attachments	File di QNAP Object Storage (ADR-003)

---

View

v_asset_hardware_latest (migration 004, dev DB)

View yang merangkum hardware terbaru per asset dari asset_snapshots.

-- Contoh query
SELECT hostname, cpu_model, ram_gb, gpu_model, os_name
FROM v_asset_hardware_latest
ORDER BY hostname;

---

Migration History

File	Tanggal	Keterangan
001_initial_schema.sql	2026-06-17	Schema awal: assets, snapshots, software, users, licenses, alerts, agent_tokens
002_disk_model.sql	2026-06-17	Tambah kolom disk_model
003_drop_machine_id_unique.sql	2026-06-22	Hapus UNIQUE constraint machine_id (asset campur hardware)
004_view_hardware_latest.sql	2026-06-22	Buat view v_asset_hardware_latest
005_asset_documents.sql	2026-06-22	Draft: asset_invoices, tickets, document_attachments — belum apply

Cara apply migration: lihat infra/db/README.md

---

Catatan Penting

• Split asset: Satu laptop bisa punya dua asset record (ADR-002). smbios_uuid tidak lagi UNIQUE per laptop dual-boot.
• Snapshot flatten: Field hardware di payload ada di root object sekarang, bukan nested .system lagi untuk data baru.
• Soft delete: Tabel master punya deleted_at — jangan DELETE langsung.