Sentra Healthcare — Database Design¶

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 1. Table Patients
CREATE TABLE patients (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    rm_number VARCHAR(20) UNIQUE NOT NULL,
    nik CHAR(16) UNIQUE NOT NULL,
    ihs_id VARCHAR(50) UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    gender VARCHAR(10) CHECK (gender IN ('Laki-laki', 'Perempuan')) NOT NULL,
    birth_date DATE NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    allergies JSONB DEFAULT '[]'::jsonb NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_patients_rm ON patients(rm_number);
CREATE INDEX idx_patients_nik ON patients(nik);
CREATE INDEX idx_patients_ihs ON patients(ihs_id);

-- 2. Table Encounters
CREATE TABLE encounters (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    patient_id UUID NOT NULL REFERENCES patients(id) ON DELETE RESTRICT,
    doctor_id UUID NOT NULL,
    branch_id UUID NOT NULL,
    check_in_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    check_out_time TIMESTAMP WITH TIME ZONE,
    status VARCHAR(20) CHECK (status IN (
        'REGISTERED', 'TRIAGE', 'CONSULTATION', 'PHARMACY', 'BILLING', 'COMPLETED', 'CANCELLED'
    )) DEFAULT 'REGISTERED' NOT NULL,
    billing_type VARCHAR(20) CHECK (billing_type IN (
        'CASH', 'BPJS', 'INSURANCE', 'COMPLIMENTARY'
    )) NOT NULL,
    satusehat_encounter_id VARCHAR(50) UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_encounters_patient ON encounters(patient_id);
CREATE INDEX idx_encounters_status ON encounters(status);

-- 3. Table Vital Signs
CREATE TABLE vital_signs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    encounter_id UUID UNIQUE NOT NULL REFERENCES encounters(id) ON DELETE CASCADE,
    blood_pressure_systolic NUMERIC(5,2) NOT NULL,
    blood_pressure_diastolic NUMERIC(5,2) NOT NULL,
    body_temperature NUMERIC(4,2) NOT NULL
        CHECK (body_temperature > 30.00 AND body_temperature < 45.00),
    heart_rate NUMERIC(5,2) NOT NULL,
    respiratory_rate NUMERIC(5,2) NOT NULL,
    oxygen_saturation NUMERIC(5,2) NOT NULL
        CHECK (oxygen_saturation >= 0.00 AND oxygen_saturation <= 100.00),
    recorded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 4. Table SOAP Notes
-- Kolom digital_signature_hash menyimpan hash BSrE dari Balai Sertifikasi Elektronik
-- Setelah signed_at terisi, record ini bersifat LOCKED (immutable sesuai Permenkes 24/2022)
-- Modifikasi setelah lock hanya diizinkan melalui tabel soap_notes_history (Addendum)
CREATE TABLE soap_notes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    encounter_id UUID UNIQUE NOT NULL REFERENCES encounters(id) ON DELETE RESTRICT,
    subjective TEXT NOT NULL,
    objective TEXT NOT NULL,
    assessment_icd10 JSONB DEFAULT '[]'::jsonb NOT NULL,
    -- Format: [{"code": "I10", "name": "Essential Hypertension"}]
    plan TEXT NOT NULL,
    signed_at TIMESTAMP WITH TIME ZONE,
    signed_by_doctor_id UUID,
    digital_signature_hash TEXT, -- BSrE certificate hash (SHA-256)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Addendum table: append-only revisi RME setelah lock
CREATE TABLE soap_notes_history (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    soap_note_id UUID NOT NULL REFERENCES soap_notes(id) ON DELETE RESTRICT,
    version INTEGER NOT NULL,
    subjective TEXT NOT NULL,
    objective TEXT NOT NULL,
    assessment_icd10 JSONB DEFAULT '[]'::jsonb NOT NULL,
    plan TEXT NOT NULL,
    addendum_reason TEXT NOT NULL,
    amended_by_doctor_id UUID NOT NULL,
    amended_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    digital_signature_hash TEXT NOT NULL -- BSrE hash on amendment
);

-- 5. Table Medicines Master & Batches
CREATE TABLE medicines (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    unit VARCHAR(20) NOT NULL,
    min_stock_alert INTEGER DEFAULT 100 NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- FEFO: ordered by expiry_date ASC when allocating stock
CREATE TABLE medicine_batches (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    medicine_id UUID NOT NULL REFERENCES medicines(id) ON DELETE RESTRICT,
    batch_number VARCHAR(50) NOT NULL,
    expiry_date DATE NOT NULL,
    stock_qty INTEGER NOT NULL CHECK (stock_qty >= 0),
    purchase_price NUMERIC(12,2) NOT NULL,
    selling_price NUMERIC(12,2) NOT NULL,
    UNIQUE (medicine_id, batch_number)
);

CREATE INDEX idx_batches_expiry ON medicine_batches(expiry_date ASC);

-- 6. Table Prescriptions & Items
CREATE TABLE prescriptions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    encounter_id UUID UNIQUE NOT NULL REFERENCES encounters(id) ON DELETE RESTRICT,
    prescription_number VARCHAR(50) UNIQUE NOT NULL,
    status VARCHAR(20) CHECK (status IN (
        'PENDING', 'PREPARING', 'DISPENSED', 'CANCELLED'
    )) DEFAULT 'PENDING' NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE prescription_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    prescription_id UUID NOT NULL REFERENCES prescriptions(id) ON DELETE CASCADE,
    medicine_id UUID NOT NULL REFERENCES medicines(id) ON DELETE RESTRICT,
    allocated_batch_id UUID REFERENCES medicine_batches(id) ON DELETE RESTRICT,
    dosage_instruction VARCHAR(150) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    is_racikan BOOLEAN DEFAULT FALSE NOT NULL,
    racikan_group_id VARCHAR(50)
);

-- 7. Table Billing Invoices
CREATE TABLE billing_invoices (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    encounter_id UUID UNIQUE NOT NULL REFERENCES encounters(id) ON DELETE RESTRICT,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    total_amount NUMERIC(12,2) NOT NULL CHECK (total_amount >= 0.00),
    bpjs_coverage NUMERIC(12,2) DEFAULT 0.00 NOT NULL CHECK (bpjs_coverage >= 0.00),
    insurance_coverage NUMERIC(12,2) DEFAULT 0.00 NOT NULL CHECK (insurance_coverage >= 0.00),
    patient_copayment NUMERIC(12,2) DEFAULT 0.00 NOT NULL CHECK (patient_copayment >= 0.00),
    payment_status VARCHAR(20) CHECK (payment_status IN (
        'UNPAID', 'PAID', 'REFUNDED'
    )) DEFAULT 'UNPAID' NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 8. Table Audit Logs (Immutable Logging for UU PDP Privacy Compliance)
-- Hak akses PostgreSQL: READ + INSERT only. UPDATE & DELETE dinonaktifkan untuk semua app users.
CREATE TABLE audit_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    action_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    user_id UUID NOT NULL,
    action_type VARCHAR(20) CHECK (action_type IN (
        'INSERT', 'UPDATE', 'DELETE', 'READ_EMR'
    )) NOT NULL,
    table_name VARCHAR(50) NOT NULL,
    record_id UUID NOT NULL,
    old_values JSONB,
    new_values JSONB,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT NOT NULL
);

CREATE INDEX idx_audit_record ON audit_logs(record_id);
CREATE INDEX idx_audit_time ON audit_logs(action_time DESC);