Database Schema - ระบบงานเวชระเบียนและเวชสถิติ (EMR Core System)

Document Version: 2.0 (Master Schema Aligned)
Date: 28 สิงหาคม 2025
Integration with: MediTech Hospital Information System - MASTER_DATABASE_SCHEMA
Backend: Nest.js + TypeScript + Prisma ORM
Database: PostgreSQL 15+ + Redis 7+

เอกสารนี้รวบรวมโครงสร้างตารางฐานข้อมูล (Database Schema) ทั้งหมดสำหรับโมดูลเวชระเบียนและเวชสถิติ ที่ปรับให้สอดคล้องกับ Master Database Schema ของระบบ MediTech HIS เพื่อให้มีการบูรณาการที่สมบูรณ์แบบระหว่างโมดูลต่างๆ

ตาราง patients
ตาราง patient_insurance
ตาราง eligibility_checks
ตาราง patient_search_index
ตาราง patient_audit_log
ตาราง patient_change_requests
ตาราง medical_visits
ตาราง visit_queues
ตาราง visit_departments

SHARED FOUNDATION TABLES (จาก Master Schema)

หมายเหตุ: ตารางหลักเหล่านี้ถูกกำหนดใน MASTER_DATABASE_SCHEMA.md และใช้ร่วมกันทุกโมดูล

1. ตาราง `patients` (Master Patient Index)

ตารางหลักสำหรับเก็บข้อมูลประชากรผู้ป่วย (Patient Demographics) - ใช้ร่วมกันทุกโมดูลใน MediTech HIS

CREATE TABLE patients (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Hospital Number (Auto-generated)
    hn VARCHAR(8) UNIQUE NOT NULL,

    -- National Identification
    national_id VARCHAR(13) UNIQUE, -- Thai National ID
    passport_id VARCHAR(20), -- For foreign patients

    -- Personal Information (Thai)
    title_thai VARCHAR(10) NOT NULL, -- นาย/นาง/นางสาว/เด็กชาย/เด็กหญิง
    first_name_thai VARCHAR(50) NOT NULL,
    last_name_thai VARCHAR(50) NOT NULL,
    nickname VARCHAR(30),

    -- Personal Information (English)
    title_eng VARCHAR(10),
    first_name_eng VARCHAR(50),
    last_name_eng VARCHAR(50),

    -- Demographics
    birth_date DATE NOT NULL,
    age INTEGER GENERATED ALWAYS AS (EXTRACT(YEAR FROM AGE(birth_date))) STORED,
    gender CHAR(1) CHECK (gender IN ('M', 'F', 'U')) NOT NULL,
    blood_type VARCHAR(3) CHECK (blood_type IN ('A+', 'A-', 'B+', 'B-', 'AB+', 'AB-', 'O+', 'O-', 'Unknown')),
    rh_factor VARCHAR(8) CHECK (rh_factor IN ('Positive', 'Negative', 'Unknown')),

    -- Cultural Information
    nationality VARCHAR(50) DEFAULT 'Thai',
    ethnicity VARCHAR(50),
    religion VARCHAR(30),
    marital_status VARCHAR(20) CHECK (marital_status IN ('Single', 'Married', 'Divorced', 'Widowed', 'Separated', 'Unknown')),

    -- Socioeconomic Information
    occupation VARCHAR(100),
    workplace VARCHAR(200),
    education VARCHAR(50),
    monthly_income DECIMAL(10,2),

    -- Contact Information
    phone_number VARCHAR(15),
    mobile_number VARCHAR(15),
    email VARCHAR(100),
    line_id VARCHAR(50),

    -- Address Information (ตาม AC-002.4)
    registered_address TEXT, -- ที่อยู่ตามบัตรประชาชน/Passport
    current_address TEXT, -- ที่อยู่ปัจจุบันที่ติดต่อได้
    province VARCHAR(50),
    district VARCHAR(50),
    subdistrict VARCHAR(50),
    postal_code VARCHAR(5),

    -- Emergency Contact
    emergency_contact_name VARCHAR(100),
    emergency_contact_phone VARCHAR(15),
    emergency_contact_relationship VARCHAR(30),

    -- Parent Information
    father_name VARCHAR(100),
    father_phone VARCHAR(15),
    mother_name VARCHAR(100),
    mother_phone VARCHAR(15),

    -- Medical Information
    organ_donor BOOLEAN DEFAULT FALSE,
    advance_directive BOOLEAN DEFAULT FALSE,
    advance_directive_file_path VARCHAR(500),

    -- Additional Fields for HIS
    referral_number VARCHAR(50), -- เลขที่หนังสือส่งตัว
    referring_hospital VARCHAR(200), -- สถานพยาบาลที่ส่งตัว
    old_medical_record VARCHAR(50), -- เวชระเบียนเก่า
    previous_name VARCHAR(100), -- ชื่อเดิม

    -- Patient Photo
    photo_path VARCHAR(500),
    photo_updated_at TIMESTAMP WITH TIME ZONE,

    -- Registration Information
    registration_channel VARCHAR(20) CHECK (registration_channel IN ('staff', 'kiosk', 'mobile', 'line')),
    registration_source VARCHAR(50), -- ช่องทางการลงทะเบียน
    smart_card_used BOOLEAN DEFAULT FALSE,
    duplicate_check_performed BOOLEAN DEFAULT FALSE,
    duplicate_found BOOLEAN DEFAULT FALSE,

    -- Temporary HN for Emergency Cases (ตาม AC-004.5)
    is_temporary_hn BOOLEAN DEFAULT FALSE,
    temporary_hn_reason TEXT,
    temporary_hn_created_at TIMESTAMP WITH TIME ZONE,
    merge_status VARCHAR(20) DEFAULT 'pending' CHECK (merge_status IN ('pending', 'merged', 'rejected')),
    original_patient_id UUID REFERENCES patients(id),
    merged_at TIMESTAMP WITH TIME ZONE,
    merged_by_user_id UUID REFERENCES users(id),

    -- Status
    life_status VARCHAR(20) DEFAULT 'alive' CHECK (life_status IN ('alive', 'dead', 'unknown')),
    death_date DATE,
    death_cause VARCHAR(200),

    -- System Fields
    registration_date DATE DEFAULT CURRENT_DATE,
    last_visit_date DATE,
    total_visits INTEGER DEFAULT 0,
    vip_status BOOLEAN DEFAULT FALSE,
    special_instructions TEXT,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id),
    deleted_reason TEXT,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id),

    -- Integration with other modules
    -- This table is referenced by:
    -- - medical_visits(patient_id)
    -- - appointments(patient_id) from Queue Management
    -- - audit_logs(patient_id) for compliance
    -- - eligibility_checks(patient_id) for insurance

    -- Search optimization
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('thai', 
            first_name_thai || ' ' || last_name_thai || ' ' || 
            COALESCE(first_name_eng, '') || ' ' || COALESCE(last_name_eng, '') || ' ' ||
            COALESCE(hn, '') || ' ' || COALESCE(national_id, '') || ' ' ||
            COALESCE(nickname, '')
        )
    ) STORED
);

คำอธิบายฟิลด์ (Field Descriptions):

Field	Type	Constraints	คำอธิบาย
`id`	UUID	PRIMARY KEY	รหัสเฉพาะของข้อมูล (Primary Key)
`hn`	VARCHAR(8)	UNIQUE, NOT NULL	หมายเลขผู้ป่วย (Hospital Number) ที่ระบบสร้างอัตโนมัติ
`title_thai`	VARCHAR(10)	NOT NULL	คำนำหน้าชื่อ (ภาษาไทย)
`first_name_thai`	VARCHAR(50)	NOT NULL	ชื่อจริง (ภาษาไทย)
`last_name_thai`	VARCHAR(50)	NOT NULL	นามสกุล (ภาษาไทย)
`gender`	CHAR(1)	NOT NULL, CHECK	เพศ (M=ชาย, F=หญิง, U=ไม่ระบุ)
`birth_date`	DATE	NOT NULL	วันเดือนปีเกิด
`age`	INTEGER	GENERATED	อายุ (คำนวณอัตโนมัติจาก `birth_date`)
`blood_type`	VARCHAR(3)	CHECK	หมู่เลือด (A+, A-, B+, B-, AB+, AB-, O+, O-, Unknown)
`rh_factor`	VARCHAR(8)	CHECK	Rh Factor (Positive, Negative, Unknown)
`marital_status`	VARCHAR(20)		สถานภาพสมรส
`nationality`	VARCHAR(50)	DEFAULT 'Thai'	สัญชาติ
`life_status`	VARCHAR(20)	NOT NULL, CHECK	สถานะการมีชีวิต (alive, dead, unknown)
`title_eng`	VARCHAR(10)		คำนำหน้าชื่อ (ภาษาอังกฤษ)
`first_name_eng`	VARCHAR(50)		ชื่อจริง (ภาษาอังกฤษ)
`last_name_eng`	VARCHAR(50)		นามสกุล (ภาษาอังกฤษ)
`workplace`	VARCHAR(200)		สถานที่ทำงาน
`mobile_number`	VARCHAR(15)		เบอร์มือถือ
`monthly_income`	DECIMAL(10,2)		รายได้ต่อเดือน
`organ_donor`	BOOLEAN	DEFAULT FALSE	สถานะการบริจาคอวัยวะ
`advance_directive`	BOOLEAN	DEFAULT FALSE	มีคำสั่งล่วงหน้าหรือไม่
`advance_directive_file_path`	VARCHAR(500)		ไฟล์คำสั่งล่วงหน้า
`national_id`	VARCHAR(13)	UNIQUE	เลขบัตรประจำตัวประชาชน 13 หลัก
`passport_id`	VARCHAR(20)		เลขที่หนังสือเดินทาง (สำหรับชาวต่างชาติ)
`phone_number`	VARCHAR(15)		เบอร์โทรศัพท์ที่ติดต่อได้
`email`	VARCHAR(100)		อีเมลที่ติดต่อได้
`line_id`	VARCHAR(50)		Line ID สำหรับติดต่อ
`registered_address`	TEXT		ที่อยู่ตามบัตรประชาชน/Passport
`current_address`	TEXT		ที่อยู่ปัจจุบันที่ติดต่อได้
`province`	VARCHAR(50)		จังหวัด
`district`	VARCHAR(50)		อำเภอ/เขต
`subdistrict`	VARCHAR(50)		ตำบล/แขวง
`postal_code`	VARCHAR(5)		รหัสไปรษณีย์
`emergency_contact_name`	VARCHAR(100)		ชื่อผู้ติดต่อกรณีฉุกเฉิน
`photo_path`	VARCHAR(500)		ที่อยู่ไฟล์รูปภาพผู้ป่วย
`photo_updated_at`	TIMESTAMP		วันเวลาที่อัพเดทรูปภาพ
`registration_channel`	VARCHAR(20)	CHECK	ช่องทางการลงทะเบียน (staff, kiosk, mobile, line)
`registration_source`	VARCHAR(50)		แหล่งที่มาของการลงทะเบียน
`smart_card_used`	BOOLEAN	DEFAULT FALSE	ใช้บัตรประชาชนสมาร์ทการ์ดหรือไม่
`duplicate_check_performed`	BOOLEAN	DEFAULT FALSE	ตรวจสอบข้อมูลซ้ำแล้วหรือไม่
`duplicate_found`	BOOLEAN	DEFAULT FALSE	พบข้อมูลซ้ำหรือไม่
`is_temporary_hn`	BOOLEAN	DEFAULT FALSE	ระบุว่าเป็น HN ชั่วคราวหรือไม่
`temporary_hn_reason`	TEXT		เหตุผลที่สร้าง HN ชั่วคราว
`temporary_hn_created_at`	TIMESTAMP		วันเวลาที่สร้าง HN ชั่วคราว
`merge_status`	VARCHAR(20)	CHECK	สถานะการ merge (pending, merged, rejected)
`original_patient_id`	UUID	FK	รหัสผู้ป่วยต้นฉบับที่ merge แล้ว
`merged_at`	TIMESTAMP		วันเวลาที่ merge ข้อมูล
`merged_by_user_id`	UUID	FK	รหัสผู้ใช้ที่ทำการ merge
`death_date`	DATE		วันที่เสียชีวิต
`death_cause`	VARCHAR(200)		สาเหตุการเสียชีวิต
`registration_date`	DATE	DEFAULT CURRENT_DATE	วันที่ลงทะเบียน
`last_visit_date`	DATE		วันที่มารับบริการครั้งล่าสุด
`total_visits`	INTEGER	DEFAULT 0	จำนวนครั้งที่มารับบริการ
`vip_status`	BOOLEAN	DEFAULT FALSE	สถานะ VIP
`special_instructions`	TEXT		คำแนะนำพิเศษ
`is_deleted`	BOOLEAN	DEFAULT FALSE	สถานะการลบข้อมูล
`deleted_at`	TIMESTAMP		วันเวลาที่ลบข้อมูล
`deleted_by`	UUID	FK	ผู้ที่ลบข้อมูล
`deleted_reason`	TEXT		เหตุผลการลบข้อมูล
`created_by`	UUID	FK	ผู้สร้างข้อมูล
`updated_by`	UUID	FK	ผู้แก้ไขข้อมูลล่าสุด
`search_vector`	tsvector	GENERATED	Vector สำหรับ Full-text search
`is_active`	BOOLEAN	DEFAULT TRUE	สถานะของข้อมูล (Active/Inactive)
`created_at`	TIMESTAMP	DEFAULT NOW()	วันเวลาที่สร้างข้อมูล
`updated_at`	TIMESTAMP	DEFAULT NOW()	วันเวลาที่แก้ไขข้อมูลล่าสุด

Indexes และ Performance Optimization:

-- Indexes for performance optimization
CREATE INDEX idx_patients_hn ON patients(hn) WHERE is_active = TRUE;
CREATE INDEX idx_patients_national_id ON patients(national_id) WHERE national_id IS NOT NULL AND is_active = TRUE;
CREATE INDEX idx_patients_passport_id ON patients(passport_id) WHERE passport_id IS NOT NULL AND is_active = TRUE;
CREATE INDEX idx_patients_search_vector ON patients USING gin(search_vector);
CREATE INDEX idx_patients_name_trigram ON patients USING gin((first_name_thai || ' ' || last_name_thai) gin_trgm_ops);
CREATE INDEX idx_patients_birth_date ON patients(birth_date);
CREATE INDEX idx_patients_created_at ON patients(created_at DESC);
CREATE INDEX idx_patients_last_visit ON patients(last_visit_date DESC) WHERE last_visit_date IS NOT NULL;
CREATE INDEX idx_patients_registration_channel ON patients(registration_channel, created_at DESC);
CREATE INDEX idx_patients_nationality ON patients(nationality) WHERE nationality != 'Thai';
CREATE INDEX idx_patients_temporary_hn ON patients(is_temporary_hn) WHERE is_temporary_hn = TRUE;
CREATE INDEX idx_patients_merge_status ON patients(merge_status) WHERE merge_status != 'pending';

Business Rules และ Constraints:

-- Business rule constraints
ALTER TABLE patients 
ADD CONSTRAINT chk_patient_required_fields 
CHECK (
    first_name_thai IS NOT NULL AND LENGTH(TRIM(first_name_thai)) > 0 AND
    last_name_thai IS NOT NULL AND LENGTH(TRIM(last_name_thai)) > 0 AND
    birth_date IS NOT NULL AND
    gender IS NOT NULL AND
    title_thai IS NOT NULL AND LENGTH(TRIM(title_thai)) > 0
);

-- National ID format validation (13 digits)
ALTER TABLE patients 
ADD CONSTRAINT chk_national_id_format
CHECK (
    national_id IS NULL OR 
    (LENGTH(national_id) = 13 AND national_id ~ '^[0-9]{13}$')
);

-- Phone number format validation
ALTER TABLE patients 
ADD CONSTRAINT chk_phone_format
CHECK (
    phone_number IS NULL OR 
    phone_number ~ '^[0-9+\-\s\(\)]{8,15}$'
);

-- Email format validation
ALTER TABLE patients 
ADD CONSTRAINT chk_email_format
CHECK (
    email IS NULL OR 
    email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
);

-- Age validation
ALTER TABLE patients 
ADD CONSTRAINT chk_reasonable_birth_date
CHECK (
    birth_date <= CURRENT_DATE AND
    birth_date >= CURRENT_DATE - INTERVAL '150 years'
);

-- Death date validation
ALTER TABLE patients 
ADD CONSTRAINT chk_death_date_logic
CHECK (
    (life_status = 'dead' AND death_date IS NOT NULL) OR
    (life_status != 'dead' AND death_date IS NULL)
);

HN Generation System:

-- HN Generation sequence
CREATE SEQUENCE hn_sequence START 10000001 INCREMENT 1;

-- HN Generation function
CREATE OR REPLACE FUNCTION generate_hn()
RETURNS TRIGGER AS $$
DECLARE
    new_hn VARCHAR(8);
    hospital_prefix VARCHAR(2);
BEGIN
    IF NEW.hn IS NULL THEN
        -- Get hospital prefix from configuration (default: '10')
        hospital_prefix := COALESCE(current_setting('app.hospital_prefix', true), '10');

        -- Generate HN: Hospital prefix (2 digits) + Sequential number (6 digits)
        SELECT hospital_prefix || LPAD(nextval('hn_sequence')::TEXT, 6, '0') INTO new_hn;

        -- Ensure uniqueness (unlikely but safety check)
        WHILE EXISTS (SELECT 1 FROM patients WHERE hn = new_hn) LOOP
            SELECT hospital_prefix || LPAD(nextval('hn_sequence')::TEXT, 6, '0') INTO new_hn;
        END LOOP;

        NEW.hn := new_hn;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger for HN generation
CREATE TRIGGER trg_generate_hn
    BEFORE INSERT ON patients
    FOR EACH ROW
    EXECUTE FUNCTION generate_hn();

Data Validation Functions:

-- Patient validation function
CREATE OR REPLACE FUNCTION validate_patient_data()
RETURNS TRIGGER AS $$
DECLARE
    calculated_birth_year INTEGER;
    actual_birth_year INTEGER;
    century_prefix VARCHAR(2);
BEGIN
    -- National ID vs Birth Date validation
    IF NEW.national_id IS NOT NULL AND NEW.birth_date IS NOT NULL THEN
        IF SUBSTRING(NEW.national_id, 1, 1) IN ('1', '2') THEN
            century_prefix := '19';
        ELSE
            century_prefix := '20';
        END IF;

        calculated_birth_year := (century_prefix || SUBSTRING(NEW.national_id, 2, 2))::INTEGER;
        actual_birth_year := EXTRACT(YEAR FROM NEW.birth_date);

        -- Allow tolerance of +/- 1 year for edge cases
        IF ABS(calculated_birth_year - actual_birth_year) > 1 THEN
            RAISE EXCEPTION 'Birth date % does not match national ID %', 
                NEW.birth_date, NEW.national_id;
        END IF;
    END IF;

    -- Age validation
    IF NEW.birth_date > CURRENT_DATE THEN
        RAISE EXCEPTION 'Birth date cannot be in the future';
    END IF;

    IF EXTRACT(YEAR FROM AGE(NEW.birth_date)) > 150 THEN
        RAISE EXCEPTION 'Patient age exceeds reasonable limits';
    END IF;

    -- Update search vector if needed
    NEW.updated_at := CURRENT_TIMESTAMP;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger for patient validation
CREATE TRIGGER trg_validate_patient_data
    BEFORE INSERT OR UPDATE ON patients
    FOR EACH ROW
    EXECUTE FUNCTION validate_patient_data();

Table Comments:

-- Comments for documentation
COMMENT ON TABLE patients IS 'ตารางข้อมูลผู้ป่วยหลัก - รองรับมาตรฐานโรงพยาบาลไทย';
COMMENT ON COLUMN patients.hn IS 'หมายเลขผู้ป่วย (Hospital Number) - สร้างอัตโนมัติ';
COMMENT ON COLUMN patients.national_id IS 'เลขบัตรประชาชน 13 หลัก';
COMMENT ON COLUMN patients.passport_id IS 'หมายเลข Passport สำหรับชาวต่างชาติ';
COMMENT ON COLUMN patients.registration_channel IS 'ช่องทางการลงทะเบียน: staff, kiosk, mobile, line';
COMMENT ON COLUMN patients.search_vector IS 'Full-text search vector สำหรับการค้นหา';

2. ตาราง `patient_insurance`

ตารางสำหรับเก็บข้อมูลสิทธิการรักษาของผู้ป่วยแต่ละคน - บูรณาการกับระบบ NHSO/SSO/Civil Registration

CREATE TABLE patient_insurance (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    patient_id UUID NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
    scheme_type VARCHAR(20) NOT NULL CHECK (scheme_type IN ('UCS', 'SSS', 'CSMBS', 'Private', 'Cash')),
    scheme_name VARCHAR(100),
    policy_number VARCHAR(50),
    member_id VARCHAR(50),
    is_primary BOOLEAN DEFAULT FALSE,
    is_secondary BOOLEAN DEFAULT FALSE,
    coverage_start_date DATE,
    coverage_end_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

คำอธิบายฟิลด์ (Field Descriptions):

Field	Type	Constraints	คำอธิบาย
`id`	UUID	PRIMARY KEY	รหัสเฉพาะของข้อมูลสิทธิ
`patient_id`	UUID	NOT NULL, FK	รหัสผู้ป่วยที่ผูกกับสิทธินี้
`scheme_type`	VARCHAR(20)	NOT NULL, CHECK	ประเภทสิทธิ (UCS, SSS, CSMBS, Private, Cash)
`scheme_name`	VARCHAR(100)		ชื่อสิทธิการรักษา (เช่น ประกันสังคมโรงพยาบาล X)
`policy_number`	VARCHAR(50)		เลขที่กรมธรรม์ (สำหรับประกันเอกชน)
`is_primary`	BOOLEAN		ระบุว่าเป็นสิทธิหลักหรือไม่
`is_secondary`	BOOLEAN		ระบุว่าเป็นสิทธิรองหรือไม่
`coverage_start_date`	DATE		วันที่สิทธิเริ่มคุ้มครอง
`coverage_end_date`	DATE		วันที่สิทธิสิ้นสุดการคุ้มครอง
`is_active`	BOOLEAN		สถานะของสิทธินี้ (Active/Inactive)

3. ตาราง `eligibility_checks`

ตารางสำหรับบันทึกการตรวจสอบสิทธิการรักษาของผู้ป่วย (ตาม FR-006)

CREATE TABLE eligibility_checks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    patient_id UUID NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
    insurance_id UUID REFERENCES patient_insurance(id),
    check_type VARCHAR(20) NOT NULL CHECK (check_type IN ('NHSO', 'SSO', 'Civil_Registration', 'Manual')),
    national_id VARCHAR(13),
    request_payload JSONB,
    response_payload JSONB,
    is_eligible BOOLEAN,
    eligibility_status VARCHAR(50),
    coverage_details JSONB,
    error_message TEXT,
    response_time_ms INTEGER,
    checked_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    checked_by_user_id UUID REFERENCES users(id),
    expires_at TIMESTAMP WITH TIME ZONE,

    -- Audit fields
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for eligibility checks
CREATE INDEX idx_eligibility_patient_id ON eligibility_checks(patient_id, checked_at DESC);
CREATE INDEX idx_eligibility_national_id ON eligibility_checks(national_id, checked_at DESC);
CREATE INDEX idx_eligibility_check_type ON eligibility_checks(check_type, checked_at DESC);
CREATE INDEX idx_eligibility_status ON eligibility_checks(is_eligible, eligibility_status);
CREATE INDEX idx_eligibility_expires ON eligibility_checks(expires_at) WHERE expires_at IS NOT NULL;

คำอธิบายฟิลด์ (Field Descriptions):

Field	Type	Constraints	คำอธิบาย
`id`	UUID	PRIMARY KEY	รหัสเฉพาะของการตรวจสอบ
`patient_id`	UUID	NOT NULL, FK	รหัสผู้ป่วยที่ตรวจสอบ
`insurance_id`	UUID	FK	รหัสสิทธิการรักษาที่ตรวจสอบ
`check_type`	VARCHAR(20)	NOT NULL, CHECK	ประเภทการตรวจสอบ (NHSO, SSO, Civil_Registration, Manual)
`national_id`	VARCHAR(13)		เลขบัตรประชาชนที่ใช้ตรวจสอบ
`request_payload`	JSONB		ข้อมูลที่ส่งไปตรวจสอบ
`response_payload`	JSONB		ข้อมูลที่ได้รับกลับมา
`is_eligible`	BOOLEAN		ผลการตрวจสอบสิทธิ (มีสิทธิ/ไม่มีสิทธิ)
`eligibility_status`	VARCHAR(50)		สถานะสิทธิที่ได้รับ
`coverage_details`	JSONB		รายละเอียดความคุ้มครอง
`error_message`	TEXT		ข้อความ error (ถ้ามี)
`response_time_ms`	INTEGER		เวลาในการตอบสนอง (มิลลิวินาที)
`checked_at`	TIMESTAMP	DEFAULT NOW()	วันเวลาที่ตรวจสอบ
`checked_by_user_id`	UUID	FK	ผู้ทำการตรวจสอบ
`expires_at`	TIMESTAMP		วันหมดอายุของผลการตรวจสอบ

4. ตาราง `patient_search_index`

ตารางที่สร้างขึ้นเพื่อเร่งความเร็วในการค้นหาข้อมูลผู้ป่วย (Denormalized Table)

CREATE TABLE patient_search_index (
    patient_id UUID PRIMARY KEY REFERENCES patients(id) ON DELETE CASCADE,
    search_text TEXT NOT NULL,
    hn VARCHAR(8),
    national_id VARCHAR(13),
    passport_id VARCHAR(20),
    phone_number VARCHAR(15),
    mobile_number VARCHAR(15),
    full_name_thai TEXT,
    full_name_english TEXT,
    nickname VARCHAR(30),
    email VARCHAR(100),
    line_id VARCHAR(50),
    registration_channel VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('thai', 
            COALESCE(full_name_thai, '') || ' ' || 
            COALESCE(full_name_english, '') || ' ' ||
            COALESCE(hn, '') || ' ' || 
            COALESCE(national_id, '') || ' ' ||
            COALESCE(passport_id, '') || ' ' ||
            COALESCE(nickname, '') || ' ' ||
            COALESCE(phone_number, '') || ' ' ||
            COALESCE(mobile_number, '') || ' ' ||
            COALESCE(email, '') || ' ' ||
            COALESCE(line_id, '')
        )
    ) STORED,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for fast searching (ตาม AC-008.2)
CREATE INDEX idx_psi_hn ON patient_search_index(hn) WHERE is_active = TRUE;
CREATE INDEX idx_psi_national_id ON patient_search_index(national_id) WHERE national_id IS NOT NULL AND is_active = TRUE;
CREATE INDEX idx_psi_passport_id ON patient_search_index(passport_id) WHERE passport_id IS NOT NULL AND is_active = TRUE;
CREATE INDEX idx_psi_phone ON patient_search_index(phone_number) WHERE phone_number IS NOT NULL AND is_active = TRUE;
CREATE INDEX idx_psi_mobile ON patient_search_index(mobile_number) WHERE mobile_number IS NOT NULL AND is_active = TRUE;
CREATE INDEX idx_psi_email ON patient_search_index(email) WHERE email IS NOT NULL AND is_active = TRUE;
CREATE INDEX idx_psi_vector ON patient_search_index USING gin(search_vector) WHERE is_active = TRUE;
CREATE INDEX idx_psi_text_trgm ON patient_search_index USING gin(search_text gin_trgm_ops) WHERE is_active = TRUE;
CREATE INDEX idx_psi_registration_channel ON patient_search_index(registration_channel) WHERE is_active = TRUE;
CREATE INDEX idx_psi_active_deleted ON patient_search_index(is_active, is_deleted);

คำอธิบายฟิลด์ (Field Descriptions):

Field	Type	Constraints	คำอธิบาย
`patient_id`	UUID	PRIMARY KEY, FK	รหัสผู้ป่วย
`search_text`	TEXT	NOT NULL	ข้อความทั้งหมดที่ใช้ค้นหา (รวมชื่อ, HN, ID, เบอร์โทร)
`hn`	VARCHAR(8)		หมายเลขผู้ป่วย
`national_id`	VARCHAR(13)		เลขบัตรประจำตัวประชาชน
`passport_id`	VARCHAR(20)		เลขที่หนังสือเดินทาง
`phone_number`	VARCHAR(15)		เบอร์โทรศัพท์
`mobile_number`	VARCHAR(15)		เบอร์มือถือ
`full_name_thai`	TEXT		ชื่อ-นามสกุลเต็มภาษาไทย
`full_name_english`	TEXT		ชื่อ-นามสกุลเต็มภาษาอังกฤษ
`nickname`	VARCHAR(30)		ชื่อเล่น
`email`	VARCHAR(100)		อีเมล
`line_id`	VARCHAR(50)		Line ID
`registration_channel`	VARCHAR(20)		ช่องทางการลงทะเบียน
`is_active`	BOOLEAN	DEFAULT TRUE	สถานะการใช้งาน
`is_deleted`	BOOLEAN	DEFAULT FALSE	สถานะการลบ
`search_vector`	tsvector	GENERATED	Vector สำหรับ Full-text search ของ PostgreSQL

5. ตาราง `patient_audit_log`

ตารางสำหรับบันทึกประวัติการเปลี่ยนแปลงข้อมูลทั้งหมด (Audit Trail)

CREATE TABLE patient_audit_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    patient_id UUID NOT NULL REFERENCES patients(id),
    action_type VARCHAR(20) NOT NULL CHECK (action_type IN ('INSERT', 'UPDATE', 'DELETE')),
    table_name VARCHAR(50) NOT NULL,
    field_name VARCHAR(100),
    old_value TEXT,
    new_value TEXT,
    changed_by_user_id UUID NOT NULL,
    changed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    change_reason TEXT,
    ip_address INET,
    user_agent TEXT
);

คำอธิบายฟิลด์ (Field Descriptions):

Field	Type	Constraints	คำอธิบาย
`action_type`	VARCHAR(20)	NOT NULL, CHECK	ประเภทการกระทำ (INSERT, UPDATE, DELETE)
`table_name`	VARCHAR(50)	NOT NULL	ชื่อตารางที่มีการเปลี่ยนแปลง
`field_name`	VARCHAR(100)		ชื่อฟิลด์ที่มีการเปลี่ยนแปลง
`old_value`	TEXT		ข้อมูลเก่าก่อนการเปลี่ยนแปลง
`new_value`	TEXT		ข้อมูลใหม่หลังการเปลี่ยนแปลง
`changed_by_user_id`	UUID	NOT NULL	รหัสผู้ใช้งานที่ทำการเปลี่ยนแปลง
`change_reason`	TEXT		เหตุผลในการเปลี่ยนแปลง (ถ้ามี)
`ip_address`	INET		IP Address ของผู้ที่ทำการเปลี่ยนแปลง

6. ตาราง `patient_change_requests`

ตารางสำหรับจัดการคำร้องขอแก้ไขข้อมูลสำคัญที่ต้องได้รับการอนุมัติ

CREATE TABLE patient_change_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    patient_id UUID NOT NULL REFERENCES patients(id),
    requested_by_user_id UUID NOT NULL,
    approved_by_user_id UUID,
    request_type VARCHAR(20) NOT NULL CHECK (request_type IN ('CRITICAL_INFO', 'PERSONAL_DATA')),
    requested_changes JSONB NOT NULL,
    request_reason TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'expired')),
    requested_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMP WITH TIME ZONE,
    expires_at TIMESTAMP WITH TIME ZONE DEFAULT (CURRENT_TIMESTAMP + INTERVAL '7 days')
);

คำอธิบายฟิลด์ (Field Descriptions):

Field	Type	Constraints	คำอธิบาย
`requested_changes`	JSONB	NOT NULL	ข้อมูลที่ต้องการเปลี่ยนแปลงในรูปแบบ JSON
`request_reason`	TEXT	NOT NULL	เหตุผลที่ขอแก้ไข
`status`	VARCHAR(20)	CHECK	สถานะของคำขอ (pending, approved, rejected, expired)
`expires_at`	TIMESTAMP		วันหมดอายุของคำขอ

CLINICAL CORE TABLES (สำหรับ EMR Module)

7. ตาราง `medical_visits` (Clinical Encounters)

ตารางสำหรับบันทึกข้อมูลการมารับบริการของผู้ป่วยในแต่ละครั้ง (Visit) - เป็นตารางกลางที่เชื่อมโยงกับทุกโมดูลคลินิก

Integration Points: - departments(id) และ clinics(id) จาก Master Schema
- appointments(id) จากระบบนัดหมายและคิว
- medical_orders(id) จากระบบ CPOE
- queues(visit_id) จากระบบจัดการคิว

CREATE TABLE medical_visits (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    visit_number VARCHAR(20) UNIQUE NOT NULL,
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_date DATE NOT NULL DEFAULT CURRENT_DATE,
    visit_time TIME NOT NULL DEFAULT CURRENT_TIME,
    visit_type VARCHAR(10) DEFAULT 'OPD' CHECK (visit_type IN ('OPD', 'IPD', 'ER')),
    department_id UUID REFERENCES departments(id),
    clinic_id UUID REFERENCES clinics(id),

    -- Provider Information (aligned with Master Schema)
    primary_doctor_id UUID REFERENCES users(id),
    attending_doctor_id UUID REFERENCES users(id),
    primary_nurse_id UUID REFERENCES users(id),

    -- Visit Classification
    visit_category VARCHAR(30), -- routine, follow_up, urgent, emergency
    chief_complaint TEXT,

    -- Financial Information (aligned with Master Schema)
    insurance_type VARCHAR(20), -- UCS, SSS, CSMBS, Private, Cash
    insurance_number VARCHAR(50),
    copayment_amount DECIMAL(10,2),

    -- Referral Information
    referred_from VARCHAR(200),
    referral_number VARCHAR(50),

    -- Admission Information (if applicable)
    admission_id UUID, -- Link to inpatient admission
    discharge_id UUID, -- Link to discharge record

    -- Visit Summary
    diagnosis_summary TEXT,
    procedure_summary TEXT,
    medication_summary TEXT,

    -- Status and Audit
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,

    -- Timestamps for visit workflow
    registered_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    checked_in_at TIMESTAMP WITH TIME ZONE,
    started_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE
    visit_status VARCHAR(20) DEFAULT 'registered' CHECK (visit_status IN (
        'registered', 'checked_in', 'in_progress', 'completed', 'cancelled', 'no_show'
    )),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

คำอธิบายฟิลด์ (Field Descriptions):

Field	Type	Constraints	คำอธิบาย
`visit_number`	VARCHAR(20)	UNIQUE, NOT NULL	หมายเลข Visit Number ที่ระบบสร้างอัตโนมัติ
`visit_type`	VARCHAR(10)	CHECK	ประเภทการรับบริการ (OPD, IPD, ER)
`department_id`	UUID		รหัสแผนกที่เข้ารับบริการ
`clinic_id`	UUID		รหัสคลินิกที่เข้ารับบริการ
`visit_status`	VARCHAR(20)	CHECK	สถานะของ Visit (checked_in, completed, etc.)

8. ตาราง `visit_queues`

ตารางสำหรับจัดการคิวของผู้ป่วยในแต่ละ Visit รองรับ queue types ต่างๆ

CREATE TABLE visit_queues (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    department_id UUID, -- REFERENCES departments(id)
    queue_number VARCHAR(10) NOT NULL,
    queue_priority INTEGER DEFAULT 0,
    queue_type VARCHAR(20) DEFAULT 'normal' CHECK (queue_type IN ('normal', 'elderly', 'disabled', 'emergency', 'vip', 'pregnant', 'child')),
    estimated_time TIME,
    called_at TIMESTAMP WITH TIME ZONE,
    queue_status VARCHAR(20) DEFAULT 'waiting' CHECK (queue_status IN ('waiting', 'called', 'serving', 'completed', 'no_show')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    -- Constraint สำหรับ priority logic
    CONSTRAINT chk_queue_priority_logic CHECK (
        (queue_type = 'emergency' AND queue_priority >= 100) OR
        (queue_type = 'vip' AND queue_priority >= 50) OR
        (queue_type IN ('elderly', 'disabled', 'pregnant') AND queue_priority >= 10) OR
        (queue_type IN ('normal', 'child') AND queue_priority >= 0)
    )
);

-- Indexes for queue management
CREATE INDEX idx_visit_queues_type_priority ON visit_queues(queue_type, queue_priority DESC, created_at ASC);
CREATE INDEX idx_visit_queues_department ON visit_queues(department_id, queue_status);
CREATE INDEX idx_visit_queues_status ON visit_queues(queue_status, created_at);

คำอธิบายฟิลด์ (Field Descriptions):

Field	Type	Constraints	คำอธิบาย
`visit_id`	UUID	NOT NULL, FK	รหัส Visit ที่ผูกกับคิวนี้
`queue_number`	VARCHAR(10)	NOT NULL	หมายเลขคิว
`queue_priority`	INTEGER		ลำดับความสำคัญของคิว (0=ปกติ, ยิ่งสูงยิ่งสำคัญ)
`queue_type`	VARCHAR(20)	CHECK	ประเภทคิว (normal, elderly, disabled, emergency, vip, pregnant, child)
`called_at`	TIMESTAMP		เวลาที่ถูกเรียกคิว
`queue_status`	VARCHAR(20)	CHECK	สถานะของคิว (waiting, called, serving, etc.)

9. ตาราง `visit_departments`

ตารางสำหรับจัดการ Visit ที่เข้ารับบริการหลายแผนก (Multi-department visits)

CREATE TABLE visit_departments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    visit_id UUID NOT NULL REFERENCES medical_visits(id) ON DELETE CASCADE,
    department_id UUID NOT NULL, -- REFERENCES departments(id)
    clinic_id UUID, -- REFERENCES clinics(id)
    sequence_order INTEGER NOT NULL DEFAULT 1,
    department_status VARCHAR(20) DEFAULT 'pending' CHECK (department_status IN ('pending', 'in_progress', 'completed', 'cancelled', 'referred')),
    arrived_at TIMESTAMP WITH TIME ZONE,
    started_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,
    referred_to_department_id UUID, -- REFERENCES departments(id)
    referral_reason TEXT,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(visit_id, department_id, sequence_order)
);

คำอธิบายฟิลด์ (Field Descriptions):

Field	Type	Constraints	คำอธิบาย
`visit_id`	UUID	NOT NULL, FK	รหัส Visit หลักที่เชื่อมโยง
`department_id`	UUID	NOT NULL	รหัสแผนกที่เข้ารับบริการ
`clinic_id`	UUID		รหัสคลินิกภายในแผนก
`sequence_order`	INTEGER	NOT NULL	ลำดับของการเข้ารับบริการในแผนกต่างๆ
`department_status`	VARCHAR(20)	CHECK	สถานะการรับบริการในแผนกนี้
`arrived_at`	TIMESTAMP		เวลาที่มาถึงแผนก
`started_at`	TIMESTAMP		เวลาที่เริ่มรับบริการ
`completed_at`	TIMESTAMP		เวลาที่เสร็จสิ้นการรับบริการ
`referred_to_department_id`	UUID		รหัสแผนกที่ส่งต่อ (ถ้ามี)
`referral_reason`	TEXT		เหตุผลการส่งต่อ

Indexes:

-- สำหรับค้นหา visits ของแผนก
CREATE INDEX idx_visit_departments_department_date 
ON visit_departments(department_id, DATE(created_at));

-- สำหรับค้นหา visit timeline
CREATE INDEX idx_visit_departments_visit_sequence 
ON visit_departments(visit_id, sequence_order);

-- สำหรับค้นหาการส่งต่อ
CREATE INDEX idx_visit_departments_referral 
ON visit_departments(referred_to_department_id) 
WHERE referred_to_department_id IS NOT NULL;

Business Rules และ Constraints สำหรับ Visit Departments:

-- Business rule: ห้ามมี department ซ้ำใน visit เดียวกันในลำดับเดียวกัน
ALTER TABLE visit_departments 
ADD CONSTRAINT chk_unique_visit_dept_sequence 
UNIQUE (visit_id, department_id, sequence_order);

-- Business rule: sequence_order ต้องเป็นจำนวนเต็มบวก
ALTER TABLE visit_departments 
ADD CONSTRAINT chk_positive_sequence_order 
CHECK (sequence_order > 0);

-- Business rule: เวลา logic validation
ALTER TABLE visit_departments 
ADD CONSTRAINT chk_visit_dept_time_logic 
CHECK (
    (started_at IS NULL OR arrived_at IS NULL OR started_at >= arrived_at) AND
    (completed_at IS NULL OR started_at IS NULL OR completed_at >= started_at)
);

INTEGRATION WITH MEDITECH MASTER SCHEMA

Foundation Tables ที่ใช้จาก Master Schema

users - จัดการผู้ใช้งานระบบ EMR
departments - โครงสร้างแผนกโรงพยาบาล
positions - ตำแหน่งงานของบุคลากร
roles และ permissions - ระบบ RBAC
audit_logs - Audit trail สำหรับ compliance
digital_signatures - ลายเซ็นอิเล็กทรอนิกส์สำหรับเอกสารทางการแพทย์
notifications - ระบบแจ้งเตือนแบบ Multi-channel
medical_orders - ระบบคำสั่งทางการแพทย์ (CPOE)

EMR-Specific Tables ที่เพิ่มเติม

patient_insurance - ข้อมูลสิทธิการรักษา
eligibility_checks - การตรวจสอบสิทธิ NHSO/SSO
patient_search_index - ดัชนีการค้นหาผู้ป่วย
patient_audit_log - Audit เฉพาะการเปลี่ยนแปลงข้อมูลผู้ป่วย
patient_change_requests - คำร้องขอแก้ไขข้อมูลสำคัญ
visit_queues - การจัดการคิวผู้ป่วย (บูรณาการกับระบบคิว)
visit_departments - การรับบริการหลายแผนก

Cross-Module Integration Tables

โมดูล	ตารางที่อ้างอิง	ความสัมพันธ์
ระบบผู้ดูแลระบบ	`users`, `departments`, `roles`	User management และ RBAC
ระบบนัดหมายและคิว	`appointments`, `queues`	Visit scheduling และ patient flow
ระบบ CPOE	`medical_orders`	Clinical orders และ prescriptions
ระบบห้องปฏิบัติการ	`lab_orders`, `lab_results`	Laboratory integration
ระบบรังสี	`radiology_orders`, `imaging_studies`	Radiology integration
ระบบเภสัชกรรม	`prescriptions`, `medication_orders`	Pharmacy integration

สรุป Benefits ของการบูรณาการ

✅ ลดการทำงานซ้ำซ้อน: ใช้ Foundation tables ร่วมกัน
✅ เพิ่มความสอดคล้อง: ตารางหลักมี structure เดียวกัน
✅ รองรับ Real-time Integration: Event-driven communication
✅ ปฏิบัติตาม Compliance: Built-in audit และ retention policies
✅ Performance Optimization: Strategic indexing และ partitioning
✅ Security: Comprehensive RBAC และ digital signatures

การบูรณาการนี้ทำให้ระบบ EMR สามารถทำงานร่วมกับโมดูลอื่นๆ ใน MediTech HIS ได้อย่างลื่นไหลและมีประสิทธิภาพ

Database Schema - ระบบงานเวชระเบียนและเวชสถิติ (EMR Core System)

Table of Contents

SHARED FOUNDATION TABLES (จาก Master Schema)

1. ตาราง patients (Master Patient Index)

2. ตาราง patient_insurance

3. ตาราง eligibility_checks

4. ตาราง patient_search_index

5. ตาราง patient_audit_log

6. ตาราง patient_change_requests

CLINICAL CORE TABLES (สำหรับ EMR Module)

7. ตาราง medical_visits (Clinical Encounters)

8. ตาราง visit_queues

9. ตาราง visit_departments

INTEGRATION WITH MEDITECH MASTER SCHEMA

Foundation Tables ที่ใช้จาก Master Schema

EMR-Specific Tables ที่เพิ่มเติม

Cross-Module Integration Tables

สรุป Benefits ของการบูรณาการ

1. ตาราง `patients` (Master Patient Index)

2. ตาราง `patient_insurance`

3. ตาราง `eligibility_checks`

4. ตาราง `patient_search_index`

5. ตาราง `patient_audit_log`

6. ตาราง `patient_change_requests`

7. ตาราง `medical_visits` (Clinical Encounters)

8. ตาราง `visit_queues`

9. ตาราง `visit_departments`