Database Schema: Financial Systems (OPD + IPD)

ระบบการเงินผู้ป่วยนอกและผู้ป่วยใน - MediTech HIS

Document Version: 1.0 Date: 24 กันยายน 2568 Database: PostgreSQL 15+ ORM: Prisma Encoding: UTF-8

📋 Table of Contents

Overview
Schema Design Principles
Master Schema Dependencies
Core Financial Tables
Billing & Payment Processing
Document Management
Integration Tables
Audit & Logging
Indexes & Performance
Views & Procedures
Data Migration
Backup & Recovery

Overview

Database schema สำหรับระบบการเงินรวม (Financial Systems) ที่รองรับทั้งผู้ป่วยนอก (OPD) และผู้ป่วยใน (IPD) โดยใช้โครงสร้างข้อมูลร่วมกันเพื่อความสอดคล้องและประสิทธิภาพ

Key Features

Unified Patient Financial Records: ข้อมูลการเงินรวมทุกประเภท
Insurance Rights Management: จัดการสิทธิ์การรักษา 43 แฟ้ม
Multi-Payment Support: รองรับการชำระหลายช่องทาง
Real-time Integration: เชื่อมต่อ NHSO, SSO, CGD APIs
Comprehensive Audit Trail: ติดตามการเปลี่ยนแปลงครบถ้วน
Scalable Architecture: รองรับโรงพยาบาลขนาดใหญ่

Schema Design Principles

1. Naming Conventions

-- Table Names: snake_case with prefix
financial_patients          -- Core tables
fin_insurance_rights        -- Financial specific
sys_audit_logs             -- System tables

-- Column Names: snake_case
patient_id, created_at, updated_at

-- Primary Keys: id (bigserial)
-- Foreign Keys: {table}_id
-- Timestamps: created_at, updated_at (timestamp with time zone)
-- Boolean: is_{condition} (e.g., is_active, is_deleted)

2. Data Types Standards

-- IDs: UUID (Universal Unique Identifier)
-- Money: decimal(15,2) (15 digits total, 2 decimal places)
-- Dates: date (YYYY-MM-DD)
-- Timestamps: timestamp with time zone
-- Text: varchar(n) with appropriate length
-- Large Text: text
-- JSON: jsonb (binary JSON for performance)
-- Boolean: boolean (not nullable with default)
-- Enums: create specific enum types

3. Constraints & Validation

-- All tables have created_at, updated_at
-- Soft delete with deleted_at timestamp
-- Check constraints for business rules
-- Foreign key constraints with appropriate cascade
-- Unique constraints for business keys

Master Schema Dependencies

Important: Financial Systems uses several tables from the Master Database Schema (MASTER_DATABASE_SCHEMA.md):

Referenced Master Tables

patients - Main patient registry
medical_visits - Patient visits/episodes (used instead of financial_episodes)
fin_insurance_types - Insurance types (43 แฟ้มสิทธิ์)
fin_patient_insurance - Patient insurance rights
fin_insurance_verifications - Real-time insurance verification logs

These tables are shared across multiple systems and should not be duplicated in this module schema.

Core Financial Tables

1. Financial Patients (ผู้ป่วยทางการเงิน)

Table: financial_patients

Field	Type	Constraints	คำอธิบาย
id	UUID	PRIMARY KEY	รหัสผู้ป่วยทางการเงิน (UUID)
patient_id	UUID	NOT NULL, FK to patients(id)	รหัสอ้างอิงผู้ป่วยจากระบบหลัก
financial_status	varchar(20)	DEFAULT 'active'	สถานะทางการเงิน (active, suspended, blocked)
credit_limit	decimal(15,2)	DEFAULT 0.00	วงเงินสินเชื่อ
outstanding_balance	decimal(15,2)	DEFAULT 0.00	ยอดค้างชำระ
payment_terms	integer	DEFAULT 0	เงื่อนไขการชำระเงิน (วัน)
discount_percentage	decimal(5,2)	DEFAULT 0.00	ส่วนลดพิเศษ (%)
financial_notes	text	NULL	หมายเหตุทางการเงิน
guarantor_name	varchar(200)	NULL	ชื่อผู้ค้ำประกัน
guarantor_relation	varchar(50)	NULL	ความสัมพันธ์กับผู้ค้ำ
guarantor_contact	jsonb	NULL	ข้อมูลติดต่อผู้ค้ำประกัน
is_vip	boolean	DEFAULT false	สถานะ VIP
vip_level	varchar(20)	NULL	ระดับ VIP (gold, platinum, diamond)
created_by	UUID	NOT NULL	ผู้สร้างข้อมูล
updated_by	UUID	NULL	ผู้แก้ไขข้อมูลล่าสุด
is_active	boolean	DEFAULT true	สถานะการใช้งาน
created_at	timestamp with time zone	DEFAULT now()	วันเวลาที่สร้างข้อมูล
updated_at	timestamp with time zone	DEFAULT now()	วันเวลาที่แก้ไขข้อมูลล่าสุด
deleted_at	timestamp with time zone	NULL	วันเวลาที่ลบข้อมูล (Soft delete)

CREATE TABLE financial_patients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    patient_id UUID NOT NULL REFERENCES patients(id),
    financial_status varchar(20) DEFAULT 'active',
    credit_limit decimal(15,2) DEFAULT 0.00,
    outstanding_balance decimal(15,2) DEFAULT 0.00,
    payment_terms integer DEFAULT 0,
    discount_percentage decimal(5,2) DEFAULT 0.00,
    financial_notes text,
    guarantor_name varchar(200),
    guarantor_relation varchar(50),
    guarantor_contact jsonb,
    is_vip boolean DEFAULT false,
    vip_level varchar(20),
    created_by UUID NOT NULL,
    updated_by UUID,
    is_active boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    deleted_at timestamp with time zone,

    -- Constraints
    CONSTRAINT chk_credit_limit CHECK (credit_limit >= 0),
    CONSTRAINT chk_outstanding_balance CHECK (outstanding_balance >= 0),
    CONSTRAINT chk_discount_percentage CHECK (discount_percentage >= 0 AND discount_percentage <= 100),
    CONSTRAINT chk_payment_terms CHECK (payment_terms >= 0),
    CONSTRAINT chk_vip_level CHECK (vip_level IS NULL OR vip_level IN ('gold', 'platinum', 'diamond')),
    CONSTRAINT chk_financial_status CHECK (financial_status IN ('active', 'suspended', 'blocked', 'closed'))
);

-- Indexes for financial_patients
CREATE INDEX idx_financial_patients_patient ON financial_patients(patient_id);
CREATE INDEX idx_financial_patients_status ON financial_patients(financial_status);
CREATE INDEX idx_financial_patients_vip ON financial_patients(is_vip, vip_level);
CREATE INDEX idx_financial_patients_active ON financial_patients(is_active) WHERE is_active = true;
CREATE INDEX idx_financial_patients_balance ON financial_patients(outstanding_balance) WHERE outstanding_balance > 0;

2. Visit Financial Extensions (ข้อมูลการเงินของการมารับบริการ)

Note: Uses medical_visits table from Master Schema instead of creating separate episodes table.

Table: visit_financial_extensions

Field	Type	Constraints	คำอธิบาย
id	UUID	PRIMARY KEY	รหัสข้อมูลการเงินการมารับบริการ
visit_id	UUID	NOT NULL, FK to medical_visits(id)	รหัสการมารับบริการจากระบบหลัก
financial_patient_id	UUID	NOT NULL, FK to financial_patients(id)	รหัสผู้ป่วยทางการเงิน
billing_category	varchar(30)	NOT NULL	ประเภทการเรียกเก็บเงิน
payment_method_preferred	varchar(20)	NULL	ช่องทางชำระเงินที่ต้องการ
deposit_required	decimal(15,2)	DEFAULT 0.00	เงินมัดจำที่ต้องการ
deposit_received	decimal(15,2)	DEFAULT 0.00	เงินมัดจำที่ได้รับ
estimated_cost	decimal(15,2)	NULL	ค่าใช้จ่ายโดยประมาณ
cost_ceiling	decimal(15,2)	NULL	เพดานค่าใช้จ่าย
authorization_code	varchar(50)	NULL	รหัสอนุมัติ (สำหรับข้าราชการ)
authorization_expires	timestamp with time zone	NULL	วันหมดอายุการอนุมัติ
billing_notes	text	NULL	หมายเหตุการเรียกเก็บเงิน
is_emergency_billing	boolean	DEFAULT false	เป็นการเรียกเก็บฉุกเฉิน
requires_approval	boolean	DEFAULT false	ต้องการอนุมัติ
approved_by	UUID	NULL	ผู้อนุมัติ
approved_at	timestamp with time zone	NULL	วันเวลาที่อนุมัติ
created_by	UUID	NOT NULL	ผู้สร้างข้อมูล
updated_by	UUID	NULL	ผู้แก้ไขข้อมูลล่าสุด
created_at	timestamp with time zone	DEFAULT now()	วันเวลาที่สร้างข้อมูล
updated_at	timestamp with time zone	DEFAULT now()	วันเวลาที่แก้ไขข้อมูลล่าสุด

CREATE TABLE visit_financial_extensions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    financial_patient_id UUID NOT NULL REFERENCES financial_patients(id),
    billing_category varchar(30) NOT NULL,
    payment_method_preferred varchar(20),
    deposit_required decimal(15,2) DEFAULT 0.00,
    deposit_received decimal(15,2) DEFAULT 0.00,
    estimated_cost decimal(15,2),
    cost_ceiling decimal(15,2),
    authorization_code varchar(50),
    authorization_expires timestamp with time zone,
    billing_notes text,
    is_emergency_billing boolean DEFAULT false,
    requires_approval boolean DEFAULT false,
    approved_by UUID,
    approved_at timestamp with time zone,
    created_by UUID NOT NULL,
    updated_by UUID,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),

    -- Constraints
    CONSTRAINT chk_deposit_amounts CHECK (deposit_required >= 0 AND deposit_received >= 0),
    CONSTRAINT chk_estimated_cost CHECK (estimated_cost IS NULL OR estimated_cost >= 0),
    CONSTRAINT chk_cost_ceiling CHECK (cost_ceiling IS NULL OR cost_ceiling >= 0),
    CONSTRAINT chk_billing_category CHECK (billing_category IN ('opd', 'ipd', 'emergency', 'surgery', 'special')),
    CONSTRAINT chk_payment_method CHECK (payment_method_preferred IS NULL OR payment_method_preferred IN ('cash', 'card', 'transfer', 'insurance', 'installment'))
);

-- Unique constraint: one financial extension per visit
CREATE UNIQUE INDEX idx_visit_financial_unique ON visit_financial_extensions(visit_id);

-- Other indexes
CREATE INDEX idx_visit_financial_patient ON visit_financial_extensions(financial_patient_id);
CREATE INDEX idx_visit_financial_category ON visit_financial_extensions(billing_category);
CREATE INDEX idx_visit_financial_approval ON visit_financial_extensions(requires_approval, approved_at);
CREATE INDEX idx_visit_financial_deposit ON visit_financial_extensions(deposit_required) WHERE deposit_required > 0;

Billing & Payment Processing

3. Financial Charges (รายการค่าใช้จ่าย)

CREATE TYPE charge_category AS ENUM (
    'room', 'meal', 'nursing', 'doctor', 'medication', 'laboratory',
    'radiology', 'surgery', 'anesthesia', 'equipment', 'supplies',
    'rehabilitation', 'other'
);

CREATE TABLE fin_charges (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    charge_date timestamp with time zone NOT NULL,
    charge_category charge_category NOT NULL,
    service_code varchar(20), -- Hospital service code
    service_name_th varchar(200) NOT NULL,
    service_name_en varchar(200),
    department_id bigint, -- FK to departments
    doctor_id bigint, -- FK to doctors
    quantity decimal(10,3) NOT NULL DEFAULT 1.000,
    unit_price decimal(15,2) NOT NULL,
    total_amount decimal(15,2) NOT NULL,
    discount_amount decimal(15,2) DEFAULT 0.00,
    net_amount decimal(15,2) NOT NULL,
    cost_center varchar(20),
    revenue_code varchar(20),
    is_billable boolean DEFAULT true,
    is_covered boolean DEFAULT true, -- Covered by insurance
    coverage_percentage decimal(5,2) DEFAULT 100.00,
    patient_responsibility decimal(15,2) DEFAULT 0.00,
    notes text,
    source_system varchar(50), -- 'opd-cpoe', 'ipd-cpoe', 'pharmacy', 'lab', etc.
    source_reference varchar(100), -- Reference to source record
    posted_by_user_id bigint,
    posted_at timestamp with time zone DEFAULT now(),
    is_cancelled boolean DEFAULT false,
    cancelled_by_user_id bigint,
    cancelled_at timestamp with time zone,
    cancellation_reason text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),

    CONSTRAINT chk_amounts CHECK (
        total_amount = quantity * unit_price AND
        net_amount = total_amount - discount_amount AND
        discount_amount >= 0 AND
        patient_responsibility >= 0
    ),
    CONSTRAINT chk_coverage CHECK (coverage_percentage >= 0 AND coverage_percentage <= 100)
);

-- Indexes for charges
CREATE INDEX idx_fin_charges_episode ON fin_charges(episode_id);
CREATE INDEX idx_fin_charges_date ON fin_charges(charge_date);
CREATE INDEX idx_fin_charges_category ON fin_charges(charge_category);
CREATE INDEX idx_fin_charges_department ON fin_charges(department_id);
CREATE INDEX idx_fin_charges_doctor ON fin_charges(doctor_id);
CREATE INDEX idx_fin_charges_billable ON fin_charges(is_billable) WHERE is_billable = true;
CREATE INDEX idx_fin_charges_active ON fin_charges(episode_id, is_cancelled) WHERE is_cancelled = false;

4. Insurance Coverage Calculation (การคำนวณสิทธิ์)

CREATE TABLE fin_insurance_coverage (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    patient_insurance_id UUID NOT NULL REFERENCES fin_patient_insurance(id),
    calculation_date timestamp with time zone NOT NULL,
    total_charges decimal(15,2) NOT NULL,
    covered_amount decimal(15,2) NOT NULL,
    non_covered_amount decimal(15,2) NOT NULL,
    copayment_amount decimal(15,2) NOT NULL,
    deductible_amount decimal(15,2) DEFAULT 0.00,
    patient_responsibility decimal(15,2) NOT NULL,
    insurance_pays decimal(15,2) NOT NULL,
    coverage_rules_applied jsonb, -- Rules used for calculation
    calculation_details jsonb, -- Detailed breakdown
    approved_amount decimal(15,2),
    approve_code varchar(50),
    approved_by varchar(100),
    approved_at timestamp with time zone,
    claim_number varchar(50),
    is_final boolean DEFAULT false,
    calculated_by_user_id bigint,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),

    CONSTRAINT chk_coverage_calculation CHECK (
        total_charges = covered_amount + non_covered_amount AND
        patient_responsibility = copayment_amount + non_covered_amount + deductible_amount AND
        insurance_pays = covered_amount - copayment_amount - deductible_amount
    )
);

-- Indexes for insurance coverage
CREATE INDEX idx_fin_insurance_coverage_episode ON fin_insurance_coverage(episode_id);
CREATE INDEX idx_fin_insurance_coverage_insurance ON fin_insurance_coverage(patient_insurance_id);
CREATE INDEX idx_fin_insurance_coverage_date ON fin_insurance_coverage(calculation_date);
CREATE INDEX idx_fin_insurance_coverage_final ON fin_insurance_coverage(is_final) WHERE is_final = true;

5. Payments (การรับชำระเงิน)

CREATE TYPE payment_method AS ENUM ('cash', 'credit_card', 'debit_card', 'qr_code', 'bank_transfer', 'cheque', 'insurance_direct');
CREATE TYPE payment_status AS ENUM ('pending', 'completed', 'cancelled', 'refunded', 'failed');

CREATE TABLE fin_payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    payment_date timestamp with time zone NOT NULL,
    payment_type varchar(20) NOT NULL, -- 'deposit', 'partial', 'final', 'refund'
    payment_method payment_method NOT NULL,
    amount decimal(15,2) NOT NULL,
    received_amount decimal(15,2), -- Actual amount received (for cash)
    change_amount decimal(15,2) DEFAULT 0.00,
    payment_status payment_status DEFAULT 'pending',
    reference_number varchar(50), -- Bank reference, card transaction ID, etc.
    authorization_code varchar(50), -- For credit card
    bank_name varchar(100),
    card_last_four varchar(4),
    qr_code_data jsonb, -- QR payment details
    cashier_id bigint NOT NULL, -- User who received payment
    cash_drawer_id bigint, -- Cash drawer/till
    receipt_number varchar(30),
    receipt_printed_at timestamp with time zone,
    notes text,
    is_advance_payment boolean DEFAULT false,
    advance_payment_for varchar(50), -- What this advance is for
    processed_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),

    CONSTRAINT chk_payment_amounts CHECK (
        amount > 0 AND
        (received_amount IS NULL OR received_amount >= amount) AND
        change_amount >= 0
    )
);

-- Indexes for payments
CREATE INDEX idx_fin_payments_episode ON fin_payments(episode_id);
CREATE INDEX idx_fin_payments_date ON fin_payments(payment_date);
CREATE INDEX idx_fin_payments_method ON fin_payments(payment_method);
CREATE INDEX idx_fin_payments_status ON fin_payments(payment_status);
CREATE INDEX idx_fin_payments_cashier ON fin_payments(cashier_id);
CREATE INDEX idx_fin_payments_receipt ON fin_payments(receipt_number);

Document Management

6. Financial Documents (เอกสารทางการเงิน)

CREATE TYPE document_type AS ENUM ('receipt', 'invoice', 'deposit_slip', 'refund_slip', 'statement', 'insurance_claim', 'credit_note', 'debit_note');
CREATE TYPE document_status AS ENUM ('draft', 'issued', 'cancelled', 'void');

CREATE TABLE fin_documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_number varchar(30) NOT NULL UNIQUE,
    document_type document_type NOT NULL,
    visit_id UUID REFERENCES medical_visits(id),
    payment_id UUID REFERENCES fin_payments(id),
    patient_id UUID NOT NULL REFERENCES patients(id),
    issue_date timestamp with time zone NOT NULL,
    due_date date, -- For invoices
    total_amount decimal(15,2) NOT NULL,
    tax_amount decimal(15,2) DEFAULT 0.00,
    discount_amount decimal(15,2) DEFAULT 0.00,
    net_amount decimal(15,2) NOT NULL,
    document_status document_status DEFAULT 'draft',
    currency varchar(3) DEFAULT 'THB',
    exchange_rate decimal(10,4) DEFAULT 1.0000,
    template_id varchar(50), -- Document template used
    document_data jsonb NOT NULL, -- Complete document data for regeneration
    print_count integer DEFAULT 0,
    last_printed_at timestamp with time zone,
    pdf_path varchar(500), -- Path to generated PDF
    pdf_size_bytes bigint,
    issued_by_user_id bigint NOT NULL,
    cancelled_by_user_id bigint,
    cancelled_at timestamp with time zone,
    cancellation_reason text,
    replacement_document_id bigint REFERENCES fin_documents(id), -- If this document replaces another
    notes text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),

    CONSTRAINT chk_document_amounts CHECK (
        net_amount = total_amount + tax_amount - discount_amount AND
        total_amount >= 0 AND tax_amount >= 0 AND discount_amount >= 0
    )
);

-- Indexes for documents
CREATE INDEX idx_fin_documents_number ON fin_documents(document_number);
CREATE INDEX idx_fin_documents_type ON fin_documents(document_type);
CREATE INDEX idx_fin_documents_episode ON fin_documents(episode_id);
CREATE INDEX idx_fin_documents_payment ON fin_documents(payment_id);
CREATE INDEX idx_fin_documents_patient ON fin_documents(patient_id);
CREATE INDEX idx_fin_documents_date ON fin_documents(issue_date);
CREATE INDEX idx_fin_documents_status ON fin_documents(document_status);

7. Document Line Items (รายการในเอกสาร)

CREATE TABLE fin_document_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID NOT NULL REFERENCES fin_documents(id) ON DELETE CASCADE,
    charge_id UUID REFERENCES fin_charges(id),
    line_number integer NOT NULL,
    item_code varchar(20),
    description_th varchar(200) NOT NULL,
    description_en varchar(200),
    quantity decimal(10,3) NOT NULL DEFAULT 1.000,
    unit_price decimal(15,2) NOT NULL,
    discount_rate decimal(5,2) DEFAULT 0.00,
    discount_amount decimal(15,2) DEFAULT 0.00,
    line_total decimal(15,2) NOT NULL,
    tax_rate decimal(5,2) DEFAULT 0.00,
    tax_amount decimal(15,2) DEFAULT 0.00,
    created_at timestamp with time zone DEFAULT now(),

    CONSTRAINT chk_document_item_amounts CHECK (
        line_total = (quantity * unit_price) - discount_amount AND
        discount_amount >= 0 AND tax_amount >= 0
    )
);

-- Ensure line numbers are sequential within document
CREATE UNIQUE INDEX idx_fin_document_items_line ON fin_document_items(document_id, line_number);
CREATE INDEX idx_fin_document_items_charge ON fin_document_items(charge_id);

Integration Tables

8. External System Integration (การเชื่อมต่อระบบภายนอก)

CREATE TYPE integration_type AS ENUM ('nhso', 'sso', 'cgd', 'payment_gateway', 'bank', 'his_module');
CREATE TYPE sync_status AS ENUM ('pending', 'success', 'failed', 'partial');

CREATE TABLE fin_system_integrations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    integration_type integration_type NOT NULL,
    visit_id UUID REFERENCES medical_visits(id),
    patient_id UUID REFERENCES patients(id),
    external_system_name varchar(100) NOT NULL,
    external_reference_id varchar(100),
    integration_direction varchar(10) NOT NULL, -- 'inbound', 'outbound', 'bidirectional'
    data_type varchar(50) NOT NULL, -- 'eligibility', 'claim', 'payment', 'patient_data'
    request_data jsonb,
    response_data jsonb,
    sync_status sync_status NOT NULL,
    sync_started_at timestamp with time zone NOT NULL,
    sync_completed_at timestamp with time zone,
    error_message text,
    retry_count integer DEFAULT 0,
    max_retries integer DEFAULT 3,
    next_retry_at timestamp with time zone,
    processed_by_user_id bigint,
    created_at timestamp with time zone DEFAULT now(),

    CONSTRAINT chk_retry_logic CHECK (retry_count <= max_retries)
);

-- Indexes for system integrations
CREATE INDEX idx_fin_system_integrations_type ON fin_system_integrations(integration_type);
CREATE INDEX idx_fin_system_integrations_episode ON fin_system_integrations(episode_id);
CREATE INDEX idx_fin_system_integrations_status ON fin_system_integrations(sync_status);
CREATE INDEX idx_fin_system_integrations_retry ON fin_system_integrations(next_retry_at) WHERE sync_status = 'failed' AND retry_count < max_retries;

9. Daily Financial Reconciliation (การปิดรอบบัญชีรายวัน)

CREATE TYPE reconciliation_status AS ENUM ('open', 'reconciled', 'variance', 'closed');

CREATE TABLE fin_daily_reconciliation (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    reconciliation_date date NOT NULL UNIQUE,
    cashier_id bigint,
    cash_drawer_id bigint,
    opening_balance decimal(15,2) DEFAULT 0.00,
    total_receipts decimal(15,2) NOT NULL DEFAULT 0.00,
    total_refunds decimal(15,2) NOT NULL DEFAULT 0.00,
    total_voids decimal(15,2) NOT NULL DEFAULT 0.00,
    expected_closing_balance decimal(15,2) NOT NULL,
    actual_closing_balance decimal(15,2),
    variance_amount decimal(15,2) DEFAULT 0.00,
    reconciliation_status reconciliation_status DEFAULT 'open',
    payment_summary jsonb, -- Summary by payment method
    transaction_count integer NOT NULL DEFAULT 0,
    notes text,
    reconciled_by_user_id bigint,
    reconciled_at timestamp with time zone,
    approved_by_user_id bigint,
    approved_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),

    CONSTRAINT chk_variance CHECK (
        variance_amount = actual_closing_balance - expected_closing_balance OR actual_closing_balance IS NULL
    )
);

-- Indexes for daily reconciliation
CREATE INDEX idx_fin_daily_reconciliation_date ON fin_daily_reconciliation(reconciliation_date);
CREATE INDEX idx_fin_daily_reconciliation_cashier ON fin_daily_reconciliation(cashier_id);
CREATE INDEX idx_fin_daily_reconciliation_status ON fin_daily_reconciliation(reconciliation_status);

Audit & Logging

10. Financial Audit Trail (บันทึกการตรวจสอบทางการเงิน)

CREATE TYPE audit_action AS ENUM ('CREATE', 'UPDATE', 'DELETE', 'APPROVE', 'CANCEL', 'VOID', 'REFUND');

CREATE TABLE fin_audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name varchar(50) NOT NULL,
    record_id UUID NOT NULL,
    audit_action audit_action NOT NULL,
    old_values jsonb,
    new_values jsonb,
    changed_fields text[], -- Array of field names that changed
    user_id bigint NOT NULL,
    user_name varchar(100) NOT NULL,
    user_role varchar(50),
    ip_address inet,
    user_agent text,
    session_id varchar(100),
    reason text,
    approval_required boolean DEFAULT false,
    approved_by_user_id bigint,
    approved_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now(),

    -- Ensure we have either old or new values (or both)
    CONSTRAINT chk_audit_values CHECK (old_values IS NOT NULL OR new_values IS NOT NULL)
);

-- Indexes for audit logs
CREATE INDEX idx_fin_audit_logs_table_record ON fin_audit_logs(table_name, record_id);
CREATE INDEX idx_fin_audit_logs_user ON fin_audit_logs(user_id);
CREATE INDEX idx_fin_audit_logs_action ON fin_audit_logs(audit_action);
CREATE INDEX idx_fin_audit_logs_date ON fin_audit_logs(created_at);
-- Partition by date for better performance
CREATE INDEX idx_fin_audit_logs_date_month ON fin_audit_logs(date_trunc('month', created_at));

11. Financial Error Logs (บันทึกข้อผิดพลาดทางการเงิน)

CREATE TYPE error_severity AS ENUM ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL');
CREATE TYPE error_status AS ENUM ('NEW', 'ACKNOWLEDGED', 'IN_PROGRESS', 'RESOLVED', 'CLOSED');

CREATE TABLE fin_error_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    error_code varchar(20),
    error_message text NOT NULL,
    error_severity error_severity NOT NULL,
    error_category varchar(50), -- 'payment', 'insurance', 'billing', 'integration'
    table_name varchar(50),
    record_id UUID,
    visit_id UUID REFERENCES medical_visits(id),
    patient_id UUID REFERENCES patients(id),
    context_data jsonb,
    stack_trace text,
    user_id bigint,
    session_id varchar(100),
    ip_address inet,
    error_status error_status DEFAULT 'NEW',
    assigned_to_user_id bigint,
    resolution_notes text,
    resolved_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);

-- Indexes for error logs
CREATE INDEX idx_fin_error_logs_severity ON fin_error_logs(error_severity);
CREATE INDEX idx_fin_error_logs_category ON fin_error_logs(error_category);
CREATE INDEX idx_fin_error_logs_status ON fin_error_logs(error_status);
CREATE INDEX idx_fin_error_logs_episode ON fin_error_logs(episode_id);
CREATE INDEX idx_fin_error_logs_date ON fin_error_logs(created_at);

Indexes & Performance

Performance Indexes

-- Composite indexes for common queries
CREATE INDEX idx_financial_episodes_patient_type_date ON financial_episodes(patient_id, episode_type, visit_date);
CREATE INDEX idx_fin_charges_episode_date_category ON fin_charges(episode_id, charge_date, charge_category);
CREATE INDEX idx_fin_payments_episode_status_date ON fin_payments(episode_id, payment_status, payment_date);
CREATE INDEX idx_fin_patient_insurance_patient_active ON fin_patient_insurance(patient_id, is_active, priority_order) WHERE deleted_at IS NULL;

-- Partial indexes for better performance
CREATE INDEX idx_financial_episodes_active_opd ON financial_episodes(patient_id, visit_date)
    WHERE episode_type = 'opd' AND episode_status = 'active' AND deleted_at IS NULL;

CREATE INDEX idx_financial_episodes_active_ipd ON financial_episodes(patient_id, admission_date)
    WHERE episode_type = 'ipd' AND episode_status IN ('active', 'discharged') AND deleted_at IS NULL;

-- Indexes for financial reporting
CREATE INDEX idx_fin_charges_reporting ON fin_charges(charge_date, charge_category, department_id, is_cancelled)
    WHERE is_billable = true AND is_cancelled = false;

CREATE INDEX idx_fin_payments_reporting ON fin_payments(payment_date, payment_method, cashier_id, payment_status)
    WHERE payment_status = 'completed';

-- Full-text search indexes for patient search
CREATE INDEX idx_financial_patients_search ON financial_patients
    USING gin(to_tsvector('thai', first_name || ' ' || last_name));

Triggers for Automatic Updates

-- Trigger to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply to all tables with updated_at
CREATE TRIGGER update_financial_patients_updated_at BEFORE UPDATE ON financial_patients
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_financial_episodes_updated_at BEFORE UPDATE ON financial_episodes
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_fin_patient_insurance_updated_at BEFORE UPDATE ON fin_patient_insurance
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Trigger to automatically create audit logs
CREATE OR REPLACE FUNCTION create_audit_log()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO fin_audit_logs (table_name, record_id, audit_action, old_values, user_id, user_name)
        VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', to_jsonb(OLD),
                coalesce(current_setting('app.current_user_id', true)::bigint, 0),
                coalesce(current_setting('app.current_user_name', true), 'SYSTEM'));
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO fin_audit_logs (table_name, record_id, audit_action, old_values, new_values, user_id, user_name)
        VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW),
                coalesce(current_setting('app.current_user_id', true)::bigint, 0),
                coalesce(current_setting('app.current_user_name', true), 'SYSTEM'));
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO fin_audit_logs (table_name, record_id, audit_action, new_values, user_id, user_name)
        VALUES (TG_TABLE_NAME, NEW.id, 'CREATE', to_jsonb(NEW),
                coalesce(current_setting('app.current_user_id', true)::bigint, 0),
                coalesce(current_setting('app.current_user_name', true), 'SYSTEM'));
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Apply audit triggers to critical financial tables
CREATE TRIGGER audit_financial_patients AFTER INSERT OR UPDATE OR DELETE ON financial_patients
    FOR EACH ROW EXECUTE FUNCTION create_audit_log();

CREATE TRIGGER audit_fin_payments AFTER INSERT OR UPDATE OR DELETE ON fin_payments
    FOR EACH ROW EXECUTE FUNCTION create_audit_log();

CREATE TRIGGER audit_fin_charges AFTER INSERT OR UPDATE OR DELETE ON fin_charges
    FOR EACH ROW EXECUTE FUNCTION create_audit_log();

Views & Procedures

Useful Views

-- View: Current patient financial summary
CREATE VIEW v_patient_financial_summary AS
SELECT
    fp.id AS patient_id,
    fp.hn,
    fp.first_name,
    fp.last_name,
    fp.outstanding_balance,
    COUNT(DISTINCT fe.id) AS total_episodes,
    COUNT(DISTINCT CASE WHEN fe.episode_status = 'active' THEN fe.id END) AS active_episodes,
    COALESCE(SUM(fc.net_amount), 0) AS total_charges,
    COALESCE(SUM(fpay.amount), 0) AS total_payments,
    COALESCE(SUM(fc.net_amount), 0) - COALESCE(SUM(fpay.amount), 0) AS current_balance,
    MAX(fe.visit_date) AS last_visit_date
FROM financial_patients fp
LEFT JOIN financial_episodes fe ON fp.id = fe.patient_id AND fe.deleted_at IS NULL
LEFT JOIN fin_charges fc ON fe.id = fc.episode_id AND fc.is_cancelled = false
LEFT JOIN fin_payments fpay ON fe.id = fpay.episode_id AND fpay.payment_status = 'completed'
WHERE fp.deleted_at IS NULL
GROUP BY fp.id, fp.hn, fp.first_name, fp.last_name, fp.outstanding_balance;

-- View: Episode billing summary
CREATE VIEW v_episode_billing_summary AS
SELECT
    fe.id AS episode_id,
    fe.episode_number,
    fe.patient_id,
    fp.hn,
    fp.first_name || ' ' || fp.last_name AS patient_name,
    fe.episode_type,
    fe.visit_date,
    fe.admission_date,
    fe.discharge_date,
    fe.episode_status,
    COALESCE(SUM(fc.net_amount), 0) AS total_charges,
    COALESCE(SUM(fpay.amount), 0) AS total_payments,
    COALESCE(SUM(fc.net_amount), 0) - COALESCE(SUM(fpay.amount), 0) AS balance_due,
    COUNT(DISTINCT fc.id) AS charge_count,
    COUNT(DISTINCT fpay.id) AS payment_count,
    MAX(fc.charge_date) AS last_charge_date,
    MAX(fpay.payment_date) AS last_payment_date
FROM financial_episodes fe
JOIN financial_patients fp ON fe.patient_id = fp.id
LEFT JOIN fin_charges fc ON fe.id = fc.episode_id AND fc.is_cancelled = false
LEFT JOIN fin_payments fpay ON fe.id = fpay.episode_id AND fpay.payment_status = 'completed'
WHERE fe.deleted_at IS NULL
GROUP BY fe.id, fe.episode_number, fe.patient_id, fp.hn, fp.first_name, fp.last_name,
         fe.episode_type, fe.visit_date, fe.admission_date, fe.discharge_date, fe.episode_status;

-- View: Insurance coverage summary
CREATE VIEW v_insurance_coverage_summary AS
SELECT
    fic.episode_id,
    fe.episode_number,
    fp.hn,
    fp.first_name || ' ' || fp.last_name AS patient_name,
    fit.insurance_name_th AS insurance_name,
    fic.total_charges,
    fic.covered_amount,
    fic.non_covered_amount,
    fic.copayment_amount,
    fic.patient_responsibility,
    fic.insurance_pays,
    fic.is_final,
    fic.calculation_date
FROM fin_insurance_coverage fic
JOIN financial_episodes fe ON fic.episode_id = fe.id
JOIN financial_patients fp ON fe.patient_id = fp.id
JOIN fin_patient_insurance fpi ON fic.patient_insurance_id = fpi.id
JOIN fin_insurance_types fit ON fpi.insurance_type_id = fit.id
WHERE fe.deleted_at IS NULL;

-- View: Daily financial statistics
CREATE VIEW v_daily_financial_stats AS
SELECT
    payment_date::date AS transaction_date,
    payment_method,
    COUNT(*) AS transaction_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS average_amount,
    MIN(amount) AS min_amount,
    MAX(amount) AS max_amount
FROM fin_payments
WHERE payment_status = 'completed'
GROUP BY payment_date::date, payment_method
ORDER BY transaction_date DESC, payment_method;

Stored Procedures

-- Function: Calculate insurance coverage
CREATE OR REPLACE FUNCTION calculate_insurance_coverage(
    p_episode_id BIGINT,
    p_patient_insurance_id BIGINT
) RETURNS TABLE (
    total_charges DECIMAL(15,2),
    covered_amount DECIMAL(15,2),
    non_covered_amount DECIMAL(15,2),
    copayment_amount DECIMAL(15,2),
    patient_responsibility DECIMAL(15,2),
    insurance_pays DECIMAL(15,2)
) AS $$
DECLARE
    v_total_charges DECIMAL(15,2) := 0;
    v_covered_amount DECIMAL(15,2) := 0;
    v_non_covered_amount DECIMAL(15,2) := 0;
    v_copayment_rate DECIMAL(5,4);
    v_copayment_amount DECIMAL(15,2) := 0;
    v_patient_responsibility DECIMAL(15,2) := 0;
    v_insurance_pays DECIMAL(15,2) := 0;
BEGIN
    -- Get total charges
    SELECT COALESCE(SUM(net_amount), 0) INTO v_total_charges
    FROM fin_charges
    WHERE episode_id = p_episode_id AND is_cancelled = false AND is_billable = true;

    -- Get covered and non-covered amounts
    SELECT
        COALESCE(SUM(CASE WHEN is_covered THEN net_amount ELSE 0 END), 0),
        COALESCE(SUM(CASE WHEN NOT is_covered THEN net_amount ELSE 0 END), 0)
    INTO v_covered_amount, v_non_covered_amount
    FROM fin_charges
    WHERE episode_id = p_episode_id AND is_cancelled = false AND is_billable = true;

    -- Get copayment rate
    SELECT
        COALESCE(fpi.copayment_override, fit.copayment_rate, 0)
    INTO v_copayment_rate
    FROM fin_patient_insurance fpi
    JOIN fin_insurance_types fit ON fpi.insurance_type_id = fit.id
    WHERE fpi.id = p_patient_insurance_id;

    -- Calculate copayment
    v_copayment_amount := v_covered_amount * v_copayment_rate;

    -- Calculate patient responsibility and insurance pays
    v_patient_responsibility := v_copayment_amount + v_non_covered_amount;
    v_insurance_pays := v_covered_amount - v_copayment_amount;

    RETURN QUERY SELECT
        v_total_charges,
        v_covered_amount,
        v_non_covered_amount,
        v_copayment_amount,
        v_patient_responsibility,
        v_insurance_pays;
END;
$$ LANGUAGE plpgsql;

-- Function: Get patient outstanding balance
CREATE OR REPLACE FUNCTION get_patient_outstanding_balance(p_patient_id BIGINT)
RETURNS DECIMAL(15,2) AS $$
DECLARE
    v_balance DECIMAL(15,2) := 0;
BEGIN
    SELECT
        COALESCE(SUM(fc.net_amount), 0) - COALESCE(SUM(fp.amount), 0)
    INTO v_balance
    FROM financial_episodes fe
    LEFT JOIN fin_charges fc ON fe.id = fc.episode_id AND fc.is_cancelled = false
    LEFT JOIN fin_payments fp ON fe.id = fp.episode_id AND fp.payment_status = 'completed'
    WHERE fe.patient_id = p_patient_id AND fe.deleted_at IS NULL;

    RETURN COALESCE(v_balance, 0);
END;
$$ LANGUAGE plpgsql;

-- Function: Process payment
CREATE OR REPLACE FUNCTION process_payment(
    p_episode_id BIGINT,
    p_payment_method payment_method,
    p_amount DECIMAL(15,2),
    p_cashier_id BIGINT,
    p_reference_number VARCHAR(50) DEFAULT NULL
) RETURNS BIGINT AS $$
DECLARE
    v_payment_id BIGINT;
    v_receipt_number VARCHAR(30);
BEGIN
    -- Generate receipt number
    SELECT 'RC' || TO_CHAR(NOW(), 'YYYYMMDD') || '-' || LPAD(NEXTVAL('receipt_sequence')::TEXT, 6, '0')
    INTO v_receipt_number;

    -- Insert payment record
    INSERT INTO fin_payments (
        episode_id, payment_date, payment_type, payment_method,
        amount, payment_status, reference_number, cashier_id, receipt_number
    ) VALUES (
        p_episode_id, NOW(), 'final', p_payment_method,
        p_amount, 'completed', p_reference_number, p_cashier_id, v_receipt_number
    ) RETURNING id INTO v_payment_id;

    -- Update episode status if fully paid
    UPDATE financial_episodes
    SET episode_status = 'discharged'
    WHERE id = p_episode_id
    AND (
        SELECT COALESCE(SUM(fc.net_amount), 0) - COALESCE(SUM(fp.amount), 0)
        FROM fin_charges fc
        LEFT JOIN fin_payments fp ON fc.episode_id = fp.episode_id AND fp.payment_status = 'completed'
        WHERE fc.episode_id = p_episode_id AND fc.is_cancelled = false
    ) <= 0;

    RETURN v_payment_id;
END;
$$ LANGUAGE plpgsql;

Data Migration

Migration Scripts

-- Create sequence for receipt numbers
CREATE SEQUENCE receipt_sequence START 1;

-- Create sequence for episode numbers
CREATE SEQUENCE episode_opd_sequence START 1;
CREATE SEQUENCE episode_ipd_sequence START 1;

-- Migration function for existing patient data
CREATE OR REPLACE FUNCTION migrate_existing_patients() RETURNS VOID AS $$
BEGIN
    -- This would contain logic to migrate from existing HIS tables
    -- to the new financial tables structure

    -- Example: Insert existing patients
    /*
    INSERT INTO financial_patients (
        hn, patient_id, first_name, last_name, national_id,
        date_of_birth, gender, phone, created_at
    )
    SELECT
        hn, id, first_name, last_name, national_id,
        date_of_birth, gender, phone, created_at
    FROM existing_patients_table
    WHERE active = true;
    */

    RAISE NOTICE 'Patient migration would be implemented here';
END;
$$ LANGUAGE plpgsql;

Backup & Recovery

Backup Strategy

-- Backup important financial data
CREATE OR REPLACE FUNCTION backup_financial_data(backup_date DATE DEFAULT CURRENT_DATE)
RETURNS VOID AS $$
BEGIN
    -- Create backup tables with date suffix
    EXECUTE format('CREATE TABLE financial_patients_backup_%s AS SELECT * FROM financial_patients',
                   TO_CHAR(backup_date, 'YYYYMMDD'));

    EXECUTE format('CREATE TABLE financial_episodes_backup_%s AS SELECT * FROM financial_episodes WHERE visit_date::date = %L',
                   TO_CHAR(backup_date, 'YYYYMMDD'), backup_date);

    EXECUTE format('CREATE TABLE fin_payments_backup_%s AS SELECT * FROM fin_payments WHERE payment_date::date = %L',
                   TO_CHAR(backup_date, 'YYYYMMDD'), backup_date);

    RAISE NOTICE 'Financial data backup completed for %', backup_date;
END;
$$ LANGUAGE plpgsql;

-- Archive old data
CREATE OR REPLACE FUNCTION archive_old_financial_data(cutoff_date DATE)
RETURNS VOID AS $$
BEGIN
    -- Archive old episodes (older than cutoff_date)
    CREATE TABLE IF NOT EXISTS financial_episodes_archive (LIKE financial_episodes);

    WITH archived_episodes AS (
        DELETE FROM financial_episodes
        WHERE visit_date < cutoff_date
        AND episode_status IN ('discharged', 'cancelled')
        AND deleted_at IS NULL
        RETURNING *
    )
    INSERT INTO financial_episodes_archive SELECT * FROM archived_episodes;

    RAISE NOTICE 'Archived % episodes older than %',
                 (SELECT COUNT(*) FROM financial_episodes_archive WHERE visit_date < cutoff_date),
                 cutoff_date;
END;
$$ LANGUAGE plpgsql;

Data Dictionary

Key Relationships

patients (master) → financial_patients (1:1)
medical_visits (master) → visit_financial_extensions (1:1)
medical_visits (master) → fin_charges (1:N)
medical_visits (master) → fin_payments (1:N)
patients (master) → fin_patient_insurance (master) (1:N)
fin_insurance_types (master) → fin_patient_insurance (master) (1:N)
medical_visits (master) → fin_documents (1:N)
fin_payments → fin_documents (1:1 for receipts)

Business Rules Implemented

Patient can have multiple insurance rights but only one primary (managed in master schema)
Visits can be OPD or IPD with different financial requirements
Charges must belong to a visit and cannot be negative
Payments are always positive and linked to visits
Documents are immutable once issued (can only be cancelled)
Audit trail is mandatory for all financial transactions
Insurance verification expires and must be renewed (managed in master schema)
Outstanding balances are calculated in real-time
Financial patient records extend master patient data with financial-specific fields
Visit financial extensions provide billing context for each medical visit

Performance Considerations

Partitioning: Consider partitioning large tables by date
Archiving: Archive old completed episodes regularly
Indexing: Comprehensive indexes for common query patterns
Caching: Use Redis for frequent lookups (insurance types, etc.)
Connection Pooling: Use pgBouncer for database connections

Security & Compliance

Row Level Security (RLS)

-- Enable RLS on sensitive tables
ALTER TABLE financial_patients ENABLE ROW LEVEL SECURITY;
ALTER TABLE fin_payments ENABLE ROW LEVEL SECURITY;

-- Example policy: Users can only see patients from their department
CREATE POLICY patient_department_policy ON financial_patients
    FOR ALL TO application_user
    USING (department_id = current_setting('app.user_department_id')::bigint);

-- Example policy: Cashiers can only see their own transactions
CREATE POLICY cashier_payment_policy ON fin_payments
    FOR ALL TO cashier_role
    USING (cashier_id = current_setting('app.current_user_id')::bigint);

Data Encryption

-- Encrypt sensitive data at application level
-- Store encrypted national_id, phone numbers in bytea fields
-- Use pgcrypto extension for database-level encryption if needed

-- Example: Encrypt patient national ID
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Function to encrypt data
CREATE OR REPLACE FUNCTION encrypt_sensitive_data(data TEXT, key TEXT)
RETURNS BYTEA AS $$
BEGIN
    RETURN pgp_sym_encrypt(data, key);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Conclusion

This comprehensive database schema provides a solid foundation for the MediTech Financial Systems, supporting both OPD and IPD operations with:

Scalable Architecture: Handles high transaction volumes
Data Integrity: Comprehensive constraints and validation
Audit Compliance: Complete transaction tracking
Integration Ready: APIs for external systems
Performance Optimized: Strategic indexing and views
Security Focused: Encryption and access control

The unified approach eliminates data duplication while maintaining the flexibility to handle different patient types and billing scenarios efficiently.

Database Administrator: [Name] Date Created: 24 กันยายน 2568 Version: 1.0 Status: Development Ready

Next Steps: 1. Review and approve schema design 2. Set up development database environment 3. Implement Prisma ORM models 4. Create initial seed data 5. Develop API endpoints 6. Implement comprehensive testing